mysql 表

文档地址：https://dev.mysql.com/doc/refman/8.0/en/create-table.html

红色部分表示可以选配；

一、创建表语法

创建语法如下，红色部分为可选项；

创建：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

复制方式创建：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

复制方式创建：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

二、 表名：

可以将表名指定为db_name.tbl_name，以便在特定数据库中创建表。无论是否有默认数据库，只要数据库存在，这都能起作用。如果使用带引号的标识符，请分别引用数据库和表的名称。例如，写入“mydb”`mytbl`，而不是`mydb.mytbl`。

例1：不带引号 只有表名；

CREATE TABLE demo_treeinfo (
`ID` int(11) not null AUTO_INCREMENT,
PRIMARY KEY (`ID`))

例2：不带引号 数据库名称.表名；

CREATE TABLE learn_index.demo_treeinfo (
`ID` int(11) not null AUTO_INCREMENT,
PRIMARY KEY (`ID`))

例3：带引号 只有表名；

CREATE TABLE `demo_treeinfo` (
`ID` int(11) not null AUTO_INCREMENT,
PRIMARY KEY (`ID`))

例4：带引号 数据库名称.表名；

CREATE TABLE `learn_index`.`demo_treeinfo` (
`ID` int(11) not null AUTO_INCREMENT,
PRIMARY KEY (`ID`))

 

[IF NOT EXISTS]:如果表存在，则防止发生错误。可选项；

TEMPORARY：TEMPORARY表仅在当前会话中可见，并在会话关闭时自动删除；一般不使用

创建表时可以使用TEMPORARY关键字。

 三、列：

例：

CREATE TABLE `demo_treeinfo` (
`ID` int(11) not null AUTO_INCREMENT,-- 类型为 int 长度为11 不能为空 自增
`PID` VARCHAR(50) NOT NULL,
`NAME` VARCHAR(50) DEFAULT NULL,,-- 类型为  VARCHAR长度为50 默认为空 如果不为空 则是`NAME` VARCHAR(50) DEFAULT ‘111’
`OPEN` CHAR(2) DEFAULT NULL,
`CREATOR` VARCHAR(50) DEFAULT NULL,
`MODIFIER` VARCHAR(50) DEFAULT NULL,
`CREATE_DATE` datetime DEFAULT NULL,
`MODIFY_DATE` datetime DEFAULT NULL,
`IS_DELETED` CHAR(2) DEFAULT NULL,
PRIMARY KEY (`ID`))

创建定义：create_definition:

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

 列的定义 column_definition:

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

data_type:
    (see
mysql 常用的数据类型 

)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

关于data_type

可参官方文档：https://dev.mysql.com/doc/refman/8.0/en/data-types.html 

或者：

mysql 常用的数据类型 

NOT NULL | NULL

如果既没有指定NULL也没有指定NOT NULL，则该列将被视为已指定NULL。

  

DEFAULT（默认值）

指定列的默认值。

VISIBLE, INVISIBLE

指定列可见性。如果两个关键字都不存在，则默认值为VISIBLE。表必须至少有一个可见列。

VISIBLE和INVISIBLE关键字从MySQL 8.0.23开始提供。在MySQL 8.0.23之前，所有列都是可见的。

 

AUTO_INCREMENT：应用场景为主键自增

整数或浮点列可以具有额外的属性AUTO_INCREMENT。在索引AUTO_INCREMENT列中插入NULL（推荐）或0的值时，该列将被设置为下一个序列值。通常情况下，这是值+1，其中值是表中当前列的最大值。AUTO_INCREMENT序列以1开头。

COMMENT（注释）

可以使用comment选项指定列的注释，最长可达1024个字符。注释由SHOW CREATE TABLE和SHOW FULL COLUMNS语句显示。它也显示在Information Schema COLUMNS表的COLUMN_COMMENT列中。

 

COLUMN_FORMAT

在NDB集群中，也可以使用COLUMN_format为NDB表的各个列指定数据存储格式。允许的列格式有FIXED、DYNAMIC和DEFAULT。FIXED用于指定固定宽度存储，DYNAMIC允许列为可变宽度，DEFAULT使列使用由列的数据类型（可能被ROW_FORMAT说明符重写）确定的固定宽度或可变宽度存储。

 

四、表的克隆和复制：

LIKE

复制 orig_tbl 表结构；不包括数据；

CREATE TABLE new_tbl LIKE orig_tbl;

query_expression

复制 orig_tbl 表结构和数据；

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

 

五、索引

{INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...

mysql 索引(InnoDB)

 

六、表列的操作：

 

添加列

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST|AFTER existing_column]; 

 

ALTER TABLE VendorsCopy
ADD vend_phone CHAR(20) DEFAULT 'a';

• 首先，在ALTER TABLE子句后指定表名。
• 其次，将新列及其定义放在ADD COLUMN子句之后。请注意，COLUMN关键字是可选的，因此可以省略它。
• 第三，MySQL允许您通过指定FIRST关键字将新列添加为表的第一列。它还允许您使用AFTER existing_column子句在现有列之后添加新列。如果您没有明确指定新列的位置，MySQL会将其添加为最后一列。

 

添加多列

 

ALTER TABLE table
ADD [COLUMN] column_name_1 column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_name_2 column_2_definition [FIRST|AFTER existing_column],
...; 

 

删除列：

 

ALTER TABLE Vendors DROP COLUMN vend_phone;

 

修改列的数据类型

 

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

 

ALTER TABLE tbl_test MODIFY IS_DELETED char(2)