MySQL必知必会-第21章 创建和操作表【CREATE、ALTER、DROP】

创建和操作表

MySQL不仅用于表数据的操纵，还用于执行数据库和表的所有操作，包括表本身的【创建】和【处理】
1）使用具有交互式创建和管理表的工具；
2）使用MySQL语句操作。

1.创建表

1.1 表创建基础

-- 1.创建表的总体语法
CREATE TABLE 新表名 [IF NOT EXISTS](
	列名1 类型 其他要求,
	列名2 类型 其他要求,
	...
)ENGINE=InnoDB; // 要指定创建表使用的数据库引擎
-- 2.创建表的具体例子
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;

【说明】
1）新表明紧跟在CREATE TABLE关键字之后；
2）实际表的列的定义都放在 “圆括号” 之中；
3）各列定义 采用 “列名 列数据类型 列的其他要求” 的格式，各列之间用 “逗号” 隔开；
4）创建表时，要求指定的表名必须不存在；为了防止覆盖已有的表，SQL要求首先手工删除原有的表，然后在重建它，而不是简单地用创建语句覆盖它
5）创建表时，尽量使用 IF NOT EXISTS关键字，仅在指定的表名不存在的时候创建表；

1.2 使用NULL值

1）NOT NULL 表示该列不允许为空，阻止该列为空的值插入；
2）NULL 表示设置该列的默认值，当不指定NOT NULL和NULL的时候，默认是NULL；
3）【区别NULL和空串’’】
a.NULL 是没有值，它不是空串；
b.如果指定为’’，这样的值在NOT NULL列是允许的，’'也算是有效值；

1.3 介绍主键

主键必须唯一，如果主键由多个列构成，那么这些列的组合必须唯一

-- 用多个列组合成主键
PRIMARY KEY(列名1,列名2, ...)

主键唯一标识表中的每个行，因此，只能使用NOT NULL的列；

1.4 使用AUTO_INCREMENT

1）每次执行一个INSERT操作时，MySQL自动对该列增量；
2）一个表只允许一个AUTO_INCREMENT列，且这个列必须被索引（让列成为主键可以使列被索引）
3）【自增值可覆盖】设置为AUTO_INCREMENT的字段可以在INSERT语句中指定一个值val，只要它是唯一的，就可以用来代替/覆盖自动生成的值；后续的增量将从val开始计算；
4）【自增值可获得】可以通过**select LAST_INSERT_ID()**来获得最新的增量数值，假如使用一条INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行数据时产生的值；

1.5 指定默认值

-- 使用default设置默认值
	列名 列数据类型 NOT NULL DEFAULT 默认值;

【说明】
1）MySQL和大多数DBMS不一样，只允许使用常量作为默认值，不允许使用函数作为默认值；
2）应该尽量使用 默认值列-“DEFAULT 默认值”，避免使用 NULL列；

1…6 数据库引擎类型

1）MySQL打包了多种数据库引擎，根据具体任务选择正确的引擎能够获得更好的性能和灵活性；
2）几个需要知道的数据库引擎
a.InnoDB：可靠的事务处理引擎，不支持全文本搜索；
b.MyISAM：不支持事务处理，但性能极高，支持全文本搜索；
c.MEMORY：功能与MyISAM类似，数据存储在内存（而不是硬盘），速度很快，适合存储临时表；
3）一个数据库里的多个表可以使用多个不同的数据库引擎，根据需要而定（支持事务处理 或是 支持全文本搜索）；在混合引擎的情况下，外键不能跨引擎
4）更多有关引擎的知识参考：
中文连接：https://www.mysqlzh.com/doc/213.html
英文连接：https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

2.更新表

2.1 更新表的列

尽量在设计表的时候就考虑完善，避免对表进行较大的改动；
更多详细参考：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

-- 1.更新表基本语法
ALTER TABLE 表名
ADD COLUMN 新增列名 新列的数据类型 新列的其他要求, 			--增加列
DROP COLUMN 要删除的列名,									--删除列
RENAME COLUMN 旧列名TO 新列名,							--只修改列名
MODIFY COLUMN 要修改的列名 新列的数据类型 新列的其他要求,		--只修改列的类型和要求
CHANGE COLUMN 旧列名 新列名 新列名的数据类型 新列名的其他要求;--同时更改列名和列属性

-- 2.简单的例子
-- 增加列
ALTER TABLE students
ADD COLUMN stu_address char(255);
-- 删除列
ALTER TABLE students
DROP COLUMN stu_address;
-- 【增加外键约束】
ALTER TABLE student_scores
ADD CONSTRAINT fk_ss_s -- 增加约束 ADD CONSTRAINT 约束名
-- FOREIGN KEY(该表某列) REFERENCES 另一张表(另一张表的主键); 
FOREIGN KEY(scor_stu_id) REFERENCES students(stu_id); 

【说明】
1）更改不同的表需要用到多个ALTER TABLE子句，修改一个表的多个列，可以只使用一个AlTER TABLE语句，然后用逗号将每个更改分隔开；
2）复杂表结构的更改一般需要手动删除的过程：
a.用新的列布局创建一个新表； – 【这里已经更新了列】
b.使用INSERT SELECT语句导入旧表数据到新表中；-- 【旧表相当于备份】
c.检验包含所有数据的新表是否符合需要；-- 【检验新表的正确性】
d.重命名旧表（确定时可删除旧表），用旧表原来的名字重命名新表；
e.根据需要，重新创建【触发器】【存储过程】【索引】【外键】；

2.2 更新表（重命名表）

-- 可以一次重命名一张表，也可以一次重命名多张表，用逗号分隔开
RENAME TABLE 旧表名1 TO 新表名1,
			 旧表名2 TO 新表明2,
			 ....   TO ...;

3.删除表

-- 删除表 可以一次性删除一张表，也可以一次性删除多张表，用逗号分隔开
DROP TABLE 表名1, 表明2,...;

【说明】
1）删除表，是删除整张表，包括表的结构和内部的数据；
2）删除表，不会提示用户是否确认删除，也不能撤销！！！