Detailed tutorial on creating data tables using mysql statements

MySQL is not only used for table data manipulation, but can also be used to perform all operations on databases and tables, including the creation and processing of the table itself.

Generally there are two ways to create a table:

1. Use tools with interactive creation and management of tables;

2. Tables are also It can be manipulated directly using MySQL statements.

In order to create a table programmatically, you can use SQL's CREATE TABLE statement. It is worth noting that when using the interactive tool, you are actually using MySQL statements. However, these statements are not written by the user, the interface tool automatically generates and executes the corresponding MySQL statements (the same is true when changing existing tables).

Table creation basics

To create a table using CREATE TABLE, the following information must be given:

1. The name of the new table, given after the keyword CREATE TABLE;

2. The name and definition of the table columns, separated by commas.

The CREATE TABLE statement may also include other keywords or options, but at least the table name and column details must be included. The following MySQL statement creates the customers table used in this book:

Input:

create table customers
(
cust_id                int               NOT NULL AUTO_INCREMENT,
cust_name         char(50)     NOT NULL,
cust_address     char(50)     NULL,
cust_city             char(50)     NULL,
cust_state          char(5)       NULL,
cust_zip             char(10)     NULL,
cust_country      char(50)    NULL,
cust_contact      char(50)    NULL,
cust_email         char(255)  NULL
PRIMARY KEY (cust_id)
)ENGINE = InnoDB;

Analysis: As you can see from the above example, the table name follows the CREATE TABLE keyword. The actual table definition (all columns) is enclosed in parentheses. Separate columns with commas. This table consists of 9 columns. The definition of each column begins with the column name (which must be unique within the table), followed by the column's data type (see Chapter 1 for an explanation of data types. Additionally, Appendix D lists the data types supported by MySQL) . The primary key of a table can be specified using the PRIMARY KEY keyword when creating the table. Here, the column cust_id is designated as the primary key column. The entire statement ends with a semicolon after the right parenthesis. (Ignore ENGINE=InnoDB and AUTO_INCREMENT for now, they will be introduced later.)

Statement formatting Recall that it was said before that spaces are ignored in MySQL statements. Statements can be entered on one long line or divided into many lines. They all serve the same purpose. This allows you to format the statement in a way that works best for you. The preceding CREATE TABLE statement is a good example of statement formatting, being arranged on multiple lines with the column definitions properly indented for easier reading and editing. There are no rules for indenting SQL statements, but I strongly recommend using some indentation format.

Processing existing tables When creating a new table, the specified table name must not exist, otherwise an error will occur. If you want to prevent accidental overwriting of an existing table, SQL requires first manually dropping the table and then rebuilding it, rather than simply overwriting it with a CREATE TABLE statement. If you only want to create a table if it does not exist, you should give IF NOT EXISTS after the table name. This does not check whether the schema of the existing table matches the schema of the table you intend to create. It just checks to see if the table name exists and only creates it if the table name doesn't exist.

The above is the detailed content of Detailed tutorial on creating data tables using mysql statements. For more information, please follow other related articles on the PHP Chinese website!