MySQL CREATE TABLE

the basic syntax for the CREATE TABLE statement in MySQL is:

1. CREATE [temporary] TABLE [IF not EXISTS] tbl_name [(create_definition,...)]
2. [Table_options] [Select_statement]

Temporary: This keyword indicates that a new table created with MySQL CREATE TABLE is a temporary table that will automatically disappear after the end of the current session. Temporary tables are primarily used in stored procedures, which are generally not required for MySQL, which currently does not support stored procedures.

If not EXISTS: is actually a judgment before the table is built, and the CREATE table operation is performed only if the table does not yet exist. Use this option to avoid errors where the table already exists and cannot be created again.

Tbl_name: The table name of the table you want to create. The table name must conform to the rules for identifiers. It is common practice to use only letters, numbers, and underscores in the table name. For example, titles, Our_sales, My_user1, and so on, should be considered the table names of the comparative specification.

Create_definition: This is where the key part of the MySQL CREATE TABLE statement is located. The properties of the columns in the table are specifically defined in this section.

The basic statements of create_definition are:

2. Col_name type [Not NULL | NULL] [DEFAULT default_value] [auto_increment]
4. [PRIMARY KEY] [Reference_definition]
6. or PRIMARY KEY (index_col_name,...)
8. or KEY [index_name] (Index_col_name,...)
10. or INDEX [index_name] (Index_col_name,...)
12. or UNIQUE [INDEX] [index_name] (Index_col_name,...)
14. or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
16. [Reference_definition]
18. or CHECK (expr)

Col_name: The name of the column in the table. Must conform to the rules for identifiers and be unique in the table.

Type: The data type of the column. Some data types need to indicate the length n, enclosed in parentheses. Currently MySQL provides data types as described in MySQL Advanced _ column type article.

Not NULL | Null: Specifies whether the column is allowed to be empty. If neither NULL nor NULL is specified, the column is considered to have null specified.

Default Default_value: Specify a value for the column. If no default value is specified for the column, MySQL automatically assigns one. If the column can take null as the value, the default value is null. If the column is declared as not NULL, the default value depends on the column type:

1. For numeric types that do not declare the Auto_increment property, the default value is 0. For a auto_increment column, the default value is the next value in the order.

2. For date and time types except timestamp, the default value is the appropriate "0" value for that type. For the first timestamp column in a table, the default value is the current date and time.

3. For a string type other than Enum, the default is an empty string. For an enum, the default value is the first enumeration value.

Auto_increment: Set this column to have an auto-increment property, only an integer column to set this property. When you insert a null value or 0 into a auto_increment column, the columns are set to Value+1, where value is the maximum value for that column in the previous table. Auto_increment Order starting from 1. Each table can have only one auto_increment column, and it must be indexed.

Another turn:

Typically, an attempt to create a table that already exists will produce an error. In both cases, however, there is no error. In the first case, if the IF not EXISTS clause is given, the table that already exists is not created, but there is no error. In the second case, given temporary, the original table named Tbl_name is hidden from the client during the existence of the temporary table, so there is no error. However, the original table remains visible to other clients. The original table is visible again after the next client session or when the drop table is explicitly published on the staging table.

The Createdefinition list specifies the columns and indexes that you want to create. If you create this table with a method followed by a SELECT statement, this list can be omitted. The Table_options clause allows you to specify various properties of the table. If a later select_statement is given (in the form of any SELECT statement), the table is created with the result returned by the SELECT statement. These clauses are described in more detail in the subsequent paragraphs.

The TEMPORARY keyword, IF not EXISTS clause, table_options clause, and the ability to create a table with the result of a SELECT statement are all introduced in MySQL 3.23. Create_definition can be a column definition, an index definition, a FOREIGN KEY clause, a referencedefinition, or a check clause. The latter three are compatible in their database system, but the rest are not.

The column definition coldeclaration begins with the column name col_name and type Col_type, followed by several optional keywords. The column type can be any of the types listed in Appendix B. See type-specific properties in this appendix. The following optional keywords can follow the column type:

Null or NOT NULL indicates whether the column can contain null values. If both are not indicated, the default is N U l L.
Default DefaultValue gives the column defaults. This property cannot be used with a blob or text type.

Automatically assigns a default value if none is indicated. For columns with nullable values, the default value is N U l L.

For columns that cannot be NULL, the default values are as follows:

For numeric columns, the default value is 0, except for the Auto_increment column. For auto_increment columns, the default value is the next number in the column's sequence.

For non-timestamp date and time types, the default value is the "0" value of the type (for example, the "0" value for the date type is "0000-00-00"). For the timestamp type, the default value is the current date and time.

For a string type other than enum, the default value is an empty string. For an enum type, the default value is the first enumeration element.

A U to_increment This keyword is used only for integer column types. A U-to_increment column is special when inserting null, when the value inserted is actually an integer greater than the current maximum in the column. The Auto_increment value defaults from 1, and the first value is specified by using the Auto_increment table option.

Such a column must also be specified as a unique index or primary KEY, and should be not a NULL column. There is at least one auto_increment column in each table.

PRIMARY key specifies that the column is a PRIMARY key.

Unique specifies that this column is a unique index. This property can be specified since MySQL version 3.23.

The PRIMARY key, UNIQUE, index, and key clauses define the index. PRIMARY KEY and unique specify an index that must contain unique values. Index and key have the same meanings, both of which specify an index that can contain duplicate values. The index is based on the column specified in Index_columns, where each column must be a column in Tblname.
If you have more than one column, you must separate them with commas. for char and varchar columns, the prefix of the column can be indexed. If the index name index_name is not given, the system will automatically select one based on the name of the first indexed column. In previous versions of MySQL 3.23, the index column must be defined as not NULL. Since MySQL 3.23, only the columns in primary KEY must be defined as not NULL.
You can use the table options clause since MySQL 3.23. If this clause is given, it can contain one or more of the options in the following list. If multiple options are given, they are not separated by commas. Unless otherwise noted, each descriptor is available for all table types.

Auto_increment =n The first auto_increment value generated for the table (for MyISAM tables only).

Avg_row_length = Approximate average governor of the N table. MySQL uses this option to determine the maximum size of the data file, and only tables with a BLOB or text column require this option, and tables with both columns may increase by more than 4GB.

CHECKSUM = {0 | 1} If set to 1,mysql, a checksum will be maintained for each table row. This will have some slight loss for updates to the table, but it improves the validation of the table (only for MyISAM tables).

COMMENT = comment for the "string" table. The maximum length is 60 characters.

D E L ay_key_write = {0 | 1} If set to 1, the index cache of the table is only occasionally refreshed and not refreshed after each insert operation (only for MyISAM tables).
Max_Rows = n the maximum number of rows planned to be stored in the table.

Min_rows = n the minimum number of rows that are scheduled to be stored in the table.

Pack_keys = {0 | 1} If set to 1, the index block is compressed at a higher percentage than normal. The general effect is that the update performance is compromised, but the retrieval performance is improved. If set to 0, the index block does not have special compression. In this case, only char or varchar key values with 8 or more characters are compressed (only for MyISAM and ISAM tables).

PASSWORD = "string" Specifies a password for the description file of the encrypted table. generally does not work; available only to customers with a certain support agreement.

TYPE = {ISAM | MYISAM | HEAP} Specifies the storage format for the table. The characteristics of these storage formats are described in the section "Table storage Type Specifiers" in chapter 3rd. The default format for MySQL since version 3.23 is MyISAM. For MySQL versions prior to 3.23, CREATE table always creates tables in the ISAM format.

If a SELECT statement clause is given (such as a select query), the table is created with the contents of the query results. The rows of duplicate values in a unique index are based on the specified ignore or replace, or ignore or replace existing rows. If neither is specified, duplicate values will be faulted and the remaining records ignored.

The following creates a table with three columns. The ID is listed as primary KEY, and the remaining two columns form an index:

CREATE TABLE name
(
ID int NOT NULL auto_increment,
first_name varchar (+) NOT NULL,
last_name varchar (+) NOT NULL,
Primary key (ID),
Index (LAST_NAME,FIRST_NAME)
)

The following creates a temporary table and makes it an H-E a P (in-memory) table for faster acceleration:

Create temporary table Tem_tbl
(
ID mediumint NOT NULL unique,
Name Char (40)
)
Type=heap

Create a table below as a copy of another table:

CREATE TABLE prez_copy SELECT * from TBL

Create a table with only the parts of another table below:

CREATE TABLE Prez_newcopy Select Id,first_name,last_name from user where id<10

If you specify the creation definition of the table you want to create and populate it with the suffix SELECT statement, this definition is used when the contents of the table are already inserted into the table. For example, you can define a selected column to be made into a primary KEY:

CREATE TABLE NEW_TBL (primary key (a)) Select Id,first_name,last_name from user

Excellent Yue Group (Shenzhen) Co., Ltd.

Enjoy Life with technology

: Yojoygroup

Weibo: Http://weibo.com/YOJOYGROUP

Official website:http://www.yojoygroup.com/

Contact & submission: [Email protected]

MySQL CREATE TABLE