12.表的创建、修改与删除

1、表的创建、修改与删除

• 1.1 直接创建表：

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 (column_name1 data_type1 -- 列名和类型必选   [ PRIMARY KEY -- 可选的约束，主键    | FOREIGN KEY -- 外键，引用其他表的键值    | AUTO_INCREMENT -- 自增ID    | COMMENT comment -- 列注释（评论）    | DEFAULT default_value -- 默认值    | UNIQUE -- 唯一性约束，不允许两条记录该列值相同    | NOT NULL -- 该列非空   ], ... ) [CHARACTER SET charset] -- 字符集编码 [COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

示例如下：

• 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

• 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

• 1.4 修改表：ALTER TABLE 表名 修改选项 。选项集合：

1 2 3 4 5 6 7 8

{ ADD COLUMN <列名> <类型>  -- 增加列  | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型  | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值  | MODIFY COLUMN <列名> <类型> -- 修改列类型  | DROP COLUMN <列名> -- 删除列  | RENAME TO <新表名> -- 修改表名  | CHARACTER SET <字符集名> -- 修改字符集  | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
• ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
• ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
• ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
• ALTER TABLE 表名 RENAME TO/AS 新表名;
• ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
• ALTER TABLE 表名 CHANGE 列名和定义都可以改变。

• 1.5 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

细节剖析：

• 自增ID：AUTO_INCREMENT；
• 设置主键：PRIMARY KEY；
• 唯一性约束：UNIQUE KEY;
• 非空约束：NOT NULL
• 设置默认值：DEFAULT 0
• 当前时间戳：CURRENT_TIMESTAMP
• 评论/注释：COMMENT
• 如果该表已创建过，正常返回：IF NOT EXISTS

2、索引创建、删除与使用

        2.1 使用Alter创建索引

1.  添加主键索引

> 特点：数据列不允许重复，不能为null,一张表只能有一个主键；Mysql主动将该字段进行排序

1	ALTER TABLE 表名 ADD Primary key (col);

        2. 添加唯一索引

> 特点：索引列是唯一的，可以null；Mysql主动将该字段进行排序

1	ALTER TABLE 表名 ADD unique <索引名> (col1, col2, ...col3);

        3. 添加普通索引

> 特点：添加普通索引， 索引值不唯一，可为null

1	Alter table 表名 ADD index <索引名> (col1, col2, ...,);

        4. 添加全文索引

> 特点：只能在文本类型CHAR，VARCHAR, TEXT类型字段上创建全文索引；

1	ALTER TABLE 表名 ADD Fulltext <索引名> (col)

        5. 添加多列索引

> 特点：多列是唯一的

1	ALTER TABLE 表名 ADD UNIQUE (col1, col2, ..., )

        2.2 使用Create创建索引

> 语法：create index 索引名 on 表名(字段)

        1. 添加普通索引

1	create index 索引名 on 表名(col1, col2, ..., )

        2. 添加唯一索引

1	create unique index 索引名 on 表名(col1, col2, ..., )

        2.3 两种创建索引方式的区别

1. Alter可以省略索引名。如果省略索引名，数据库会默认根据第一个索引列赋予一个名称；Create必须指定索引名称。

2. Create不能用于创建Primary key索引；

3. Alter允许一条语句同时创建多个索引；Create一次只能创建一个索引

1	ALTER TABLE 表名 ADD Primary key (id), ADD index <索引名> (col1, col2, ...,)

        2.4 索引执行效率分析

> 主键索引 > 唯一性索引 > 普通索引

        2.5 删除索引

1. 第一种方式

1	drop index 索引名 on 表名;

1. 第二种方式

1	Alter table 表名 drop index 索引名;

1. 第三种方式

1	Alter table 表名 drop primary key

> 分析： > > 1. 第三种方式只在删除primary key中使用。因一个表只能存在一个primary key索引，则不需要指定索引名； > 2. 对于第三种方式，若没有创建primary key索引，但表中具有一个或多个unique索引，则默认删除第一个unique索引； > 3. 若删除表中的某列，索引会受到影响。对于多列组合的索引，如果删除其中的某一列，则该列会从对应的索引中被删除(删除列，不删除索引)；多删除组成索引的所有列，则索引将被删除(不仅删除列，还删除索引)。</索引名></索引名></索引名></索引名>

3、创建外键的方法

方法）创建外键

1 2 3

ALTER TABLE audit ADD CONSTRAINT FOREIGN KEY (emp_no) REFERENCES employees_test(id);

创建外键语句结构：

ALTER TABLE <表名>

ADD CONSTRAINT FOREIGN KEY (<列名>)

REFERENCES <关联表>（关联列）