Detailed introduction to mysql table definition syntax

First of all, we all know that a data table can only be created after the database is successfully created. The data table is a collection of fields, and the data in the table is stored in the format of rows and columns.

Create table

MySQL uses CREATE TABLE to create a table. There are multiple options, mainly consisting of table creation definition (create definition), table option definition (table options), and partition options (partition options).

Table creation definition

consists of the name of the table column, a possible null value statement for the column definition set, an integrity constraint or a table index entry. The table index Items mainly define the indexes, primary keys, foreign keys, etc. of the table.

(Recommended online learning video tutorial: mysql video tutorial)

Grammar structure

CREATE[TEMPORARY]TABLE tbl_name
(
    字段名|数据类型[列级完整性约束条件][默认值]
    [,字段名2 数据类型[列级完整性约束条件][默认值]]
    [,....]
    [,表级完整性约束条件]
)[ENGINE=引擎类型]

Example:
New A customer information

mysql> USE mysql_test
Database changed
mysql> CRATE TABLE customers
    ->(
    -> cust_id INT NOT NULL AUTO_INCREMENT,
    -> cust_name CHAR(50) NOT NULL,
    -> cust_sex CHAR(1) NOT NULL DEFAULT 0,
    -> cust_address CHAR(50) NULL
    -> cust_contact CHAR(50) NULL
    -> PRIMARY KEY(CUST_ID)
    ->)
Query OK, 0 rows affected(0.11 sec)

Temporary table and persistent table

TEMPORARY: represents a temporary table, if not selected, it will be a persistent table.

The persistent table always exists. Multiple users or applications can use the persistent table at the same time. If you only need to temporarily store data, you can add the TEMPORARY keyword. The temporary table can only be visible to the user who created it. Disconnect the database connection. , the table will be cleared automatically.

Data type

Data type refers to the type of data allowed in the system. Each column should have an appropriate data type that limits or allows data for that column. When creating a table, you must specify the correct data type and data length (CHAR(50)) for each column

MySQL main data type:

Numeric type: integer int , floating point double, Boolean bool

Date and time types: date type, timestamp timestamp, time type time

String type: fixed-length character type char, variable-length character type varchrar

Spatial data type: single geometry type GEOMETRY, etc.

Keyword AUTO_INCREMENT

AUTO_INCREMENT: Column settings with integer data types in the table are set to increment Attribute (i), starting from the current index or 1, there can only be one AUTO_INCREMENT in the table.

When a table column is specified as AUTO_INCREMENT, its value can be overwritten. You can specify a value (must be unique) for the column in the table data insertion statement, and the value will replace the automatically generated one by the system. value, subsequent increments are based on the inserted value

Specifies the default value

DEFAULT: Used to specify the default value of MySQL if no value is given (DEFAULT 0 )

If no default value is specified, a value is automatically assigned to it. If the column can take the value NULL, the default is NULL. If NOT NULL is defined, the default depends on the type of the column:

An AUTO_INCREMENT column is not declared as a numeric type, and the default is 0

An AUTO_INCREMENT column defaults to the next value in the sequence

For date and time types other than TIMESTAMP, the default is this Properly typed 'zero' value

For the first TIMESTAMP column in the table, the default value is the current date and time

NULL value

NULL : No value or missing value. For columns that allow NULL, the value of the column does not need to be given when inserting a row; for columns that do not allow NULL values, the column must have data
NULL and '' are not equivalent NOT NULL columns Medium Allowed'' NULL is not allowed

PRIMARY KEY

PRIMARY KEY: Specify the primary key. The primary key must be unique and cannot be NULL. If it is a single column, the value must be unique. If it is Combining columns, the combined value must be unique

Update table

Modify the database by using ALTER TABLE

ADD[COLUMN]: New Table columns, you can add more columns separated by commas

Example:

mysql> ALTER TABLE mysqle_test.customers
    -> ADD COLUMN cust_city char(10) NOT NULL DEFAULT'ShenZhen' AFTER cust_sex;
Query OK,0 rows affected(0.61 sec)
Records:0 Duplicates:0 Warning:0

AFTER: Add the new column to the cut_sexl column
FIRST: Add the new column to The first column of the table

If you use the above keywords, add the new column to the end of the table

Similarly, you can use ADDPRIMARY KEY, ADDFOREIGN KEY, ADD INDEX to add the corresponding primary key, foreign Key, index

CHANGE[COLUMN]: Modify the name or data type of the column in the table. Multiple columns can be modified and separated by commas

mysql> ALTER TABLE mysqle_test.customers
    -> CHANGE COLUMN cust_sex sex char(1) NULL DEFAULT 'M'
Query OK,0 rows affected(0.66 sec)
Records:0 Duplicates:0 Warning:0

If the data type is changed, the data may be lost. Column original data, if the data type changed by the view is incompatible with the original data type, the SQL command will not be executed and an error will be thrown.
In the case of further compatibility, the data in this column may be truncated. For example, if the data type of a column is varchart(10) and is changed to char(1), then the data 'ShenZhen' in the column will become 'S '

ALTER [COLUMN]: 修改或删除指定列的默认值

mysql> ALTER TABLE mysqle_test.customers
    -> ALTER COLUMN cust_city SET  DEFAULT 'ShangHai'
Query OK,0 rows affected(0.36 sec)
Records:0 Duplicates:0 Warning:0

MODIFY [COLUMN]: 修改指定列的数据类型，通过 'FIRST' 或 'AFTER' 修改列的位置

mysql> ALTER TABLE mysqle_test.customers
    -> MODIFY COLUMN cust_name char(30)  FIRST
Query OK,0 rows affected(0.20 sec)
Records:0 Duplicates:0 Warning:0

DROP [COLUMN]: 删除列，该列所有数据一并删除

mysql> ALTER TABLE mysqle_test.customers
    -> DROP COLUMN cust_city
Query OK,0 rows affected(0.42 sec)
Records:0 Duplicates:0 Warning:0

同样 可使用 DROP PRIMARY KEY 、DROP FOREIGN KEY、DROP INDEX 删除对应的主键、外键、索引

RENAME[TO]:表重命名

mysql> ALTER TABLE mysqle_test.customers
    -> RENAME TOQuery OK,0 rows affected(0.42 sec)

重命名表

除了 ALTER TABLE 中的 RENAME TO 修改表名，还可通过 RENAME TABLE 来修改单张和多张表（以逗号分隔）

mysql> RENAME TABLE mysql_test.back.customers TO mysqle_test.customers

删除表

DROP[TEMPORARY]TABLE[IF EXISTS]删除一个已存在的表,可以删除多张表，前提操作人必须有权限，但是操作人在该张表上的权限不会被删除

查看表

SHOW [FULL] TABLES [{FROM|IN}db_name] [LIKE'pattern'|WHERE expr]： 显示指定数据库中所有表名

Example：

mysql> USE mysql_testDatabase changedmysql> SHOW TABLES:
 Tables_in_mysql_test
 customers 1 row in set <0.01 sec>

SHOW [FULL] COLUMNS {FROM|IN}tb_name[{FROM|IN}db_name] 或 {DESCRIBE|DESC} tbl_name[col_name|wild]: 显示指定数据库表结构。

MySQL 支持使用 DESCRIBE 代替 SHOW COLUMNS FROM 来查看表结构

Example:

mysql> DESC mysql_test.custormes
Field         Type       Null key  Default Extra
cust_id       int<11>    NO   PRI  NULL    auto_increment
cust_name     char<50>   NO        Null
cust_sex      int<1>     NO        0

3 row in set <1.56 sec>

相关文章教程推荐：mysql教程

The above is the detailed content of Detailed introduction to mysql table definition syntax. For more information, please follow other related articles on the PHP Chinese website!