MySQL基础篇——第10章 DDL(数据定义)：创建和管理表_mysql ddl

MySQL基础篇——第10章 DDL(数据定义)：创建和管理表

1. 基础知识

1.1 一条数据存储的过程

• 存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析

• MySQ的数据存储过程：创建数据库、确认字段、创建数据表、插入数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yK50yFjv-1652660526961)(MySQL基础篇——第10章 创建和管理表.assets/image-20220424181558529.png)]

• 我们要先创建一个数据库(本质是一个文件系统)，而不是直接创建数据表呢？
  • 因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是：数据库服务器、数据库、数据表、数据表的行与列

1.2 标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用 ``（着重号）引起来
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

2. 创建数据库 & 管理数据库

2.1 创建数据库 CREATE DATABASE

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的

• 方式1：创建数据库，使用默认字符集

CREATE DATABASE 数据库名;
1

• 方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET '字符集';
1

• 方式3（推荐）：判断该数据库名的数据库是否已存在。若不存在则创建数据库；若已存在则忽略该指令，且不会报错

CREATE DATABASE IF NOT EXISTS 数据库名 [CHARACTER SET '字符集'];
1

2.2 使用和查看数据库

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上 数据库名.

• 使用 / 切换数据库

USE 数据库名;
1

• 查看当前所有的数据库

SHOW DATABASES;  # 复数，代表多个数据库
1

• 查看当前正在使用的数据库

SELECT DATABASE();  # DATABASE()：mysql中的全局函数
或者：
SELECT DATABASE() FROM DUAL;
1
2
3

• 查看指定数据库下所有的表

SHOW TABLES FROM 数据库名;
1

• 查看当前正在使用的数据库下所有的表

SHOW TABLES;
1

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
# 或
SHOW CREATE DATABASE 数据库名\G
1
2
3

2.3 修改数据库 ALTER DATABASE

• 更改指定数据库的字符集

ALTER DATABASE 数据库名 CHARACTER SET '字符集';
1

2.4 删除数据库 DROP DATABASE

• 方式1：删除指定的数据库

DROP DATABASE 数据库名;
1

• 方式2（ 推荐 ）：删除指定的数据库。若存在则删除数据库；若不存在则忽略该指令，且不会报错

DROP DATABASE IF EXISTS 数据库名;
1

3. 创建表 & 查看表结构

3.1 创建表 CREATE TABLE

3.1.1 创建方式1：创建一个空表

• 必须具备：CREATE TABLE 权限和存储空间
• 语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
字段名1 数据类型 [约束条件] [默认值],
字段名2 数据类型 [约束条件] [默认值],
字段名3 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
1
2
3
4
5
6
7

1. 加上了 IF NOT EXISTS 关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表

2. 如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集

3. 必须指定新创建表的：

   • 表名
   • 字段名(列名)，数据类型，长度

4. 可选指定：

   • 约束条件
   • 默认值

---

举例：

CREATE TABLE emp (
emp_id INT,  # int类型
emp_name VARCHAR(20),  # 最多保存20个中英文字符
salary DOUBLE,  # 总位数不超过15位
birthday DATE  # 日期类型
);
1
2
3
4
5
6

3.1.2 创建方式2：基于已有的表

• 使用 AS + SELECT 语句 的结构，将创建表和插入数据结合起来

• 语法格式：

CREATE TABLE 表名
AS
SELECT 字段列表
FROM 已有的表;
1
2
3
4

1. 将子查询的结果集复制为一张新表，同时将结果集中的数据插入到新表中

2. 新创建表的字段的数据类型取决于子查询的结果集中字段的数据类型

3. 子查询结果集中字段的名称（或别名），作为新创建表的字段的名称

---

举例1：创建一个表 emp_copy，实现对employees的复制，包括表数据

CREATE TABLE emp_copy
AS
SELECT *
FROM employees;
1
2
3
4

举例2：创建一个表 emp_blank，实现对employees的复制，不包括表数据

CREATE TABLE emp_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;  # 创建的emp_blank是空表
1
2
3
4
5

举例3：

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary * 12 "ANNSAL", hire_date
FROM employees
WHERE department_id = 80; 
1
2
3
4
5

3.2 查看表结构 DESC / SHOW CREATE TABLE

• MySQL支持使用 DESCRIBE / DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构：

DESCRIBE 表名;
# 或
DESC 表名;
# 或
SHOW CREATE TABLE 表名;
# 或
SHOW CREATE TABLE 表名\G
1
2
3
4
5
6
7

• 使用 SHOW CREATE TABLE 语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码

4. 修改表 ALTER TABLE

• 凡是对表中字段(列)进行操作，即修改表的结构，都使用 ALTER TABLE

• 使用 ALTER TABLE 语句可以实现：

  • 向已有的表中增加字段(列)
  • 修改现有表中的字段(列)
  • 删除现有表中的字段(列)
  • 重命名现有表中的字段(列)

4.1 增加一个字段(列) ALTER TABLE ... ADD

• 语法格式：

ALTER TABLE 表名 
ADD 字段名1 数据类型 [FIRST/AFTER 字段名2];
1
2

---

举例1：默认添加到当前表的最后一个字段之后

ALTER TABLE myemp1 
ADD salary DOUBLE(10, 2);
1
2

举例2：添加到当前表的第一个字段之前

ALTER TABLE myemp1 
ADD phone_number VARCHAR(20) FIRST;
1
2

举例3：添加到当前表的emp_name字段之后

ALTER TABLE myemp1 
ADD email VARCHAR(45) AFTER emp_name;
1
2

4.2 修改一个字段(列) ALTER TABLE ... MODIFY

• 可以修改当前表中字段的数据类型，长度、默认值和位置
• 语法格式：

ALTER TABLE 表名 
MODIFY 字段名1 新数据类型 [DEFAULT 默认值] [FIRST/AFTER 字段名2];
1
2

---

举例1：修改myemp1表中emp_name字段的数据类型

ALTER TABLE myemp1 
MODIFY emp_name VARCHAR(25);
1
2

举例2：修改myemp1表中emp_name字段的数据类型和默认值

ALTER TABLE myemp1 
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
1
2

4.3 重命名一个字段(列) ALTER TABLE ... CHANGE

• 可以重命名当前表中的字段，还可以同时修改该字段的数据类型和长度

• 语法格式：

ALTER TABLE 表名 
CHANGE 旧字段名 新字段名 新数据类型;
1
2

---

举例：

ALTER TABLE myemp1 
CHANGE salary monthly_salary DOUBLE(10, 2);
1
2

4.4 删除一个字段(列) ALTER TABLE ... DROP COLUMN

• 语法格式：

ALTER TABLE 表名 
DROP COLUMN 字段名;
1
2

---

举例：

ALTER TABLE myemp1 
DROP COLUMN my_email;
1
2

5. 重命名表 RENAME TABLE ... TO ...

• 方式一：使用 RENAME

RENAME TABLE 旧表名
TO 新表名;
1
2

• 方式二：ALTER table ... RENAME

ALTER table 旧表名
RENAME [TO] 新表名;  # TO可以省略
1
2

6. 删除表 DROP TABLE

1. 当一张数据表 没有与其他任何数据表形成关联关系 时，可以使用 DROP TABLE 语句将当前数据表删除：

   ①删除表中所有数据(记录)和结构(字段)；②所有正在运行的相关事务被提交；③所有相关索引被删除；④释放表的存储空间

2. DROP TABLE 语句不能回滚，即不能撤销

• 语法格式：

DROP TABLE [IF EXISTS] 表名1 [, 表名2, …, 表名n];  # 可以同时删除多张表
1

---

举例：

DROP TABLE dept80;
1

7. 清空表(的数据) TRUNCATE TABLE

1. TRUNCATE TABLE 语句：仅删除表的所有数据(记录)，仍保留表的结构(字段)
2. 使用 TRUNCATE TABLE 语句清空表中数据不能回滚

• 语法格式：

TRUNCATE TABLE 表名;
1

8. DDL(数据定义语言)总结

8.1 DCL(数据控制语言)中 COMMIT & ROLLBACK 的使用

• COMMIT：提交数据。一旦执行 COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚
• ROLLBACK：回滚数据。一旦执行 ROLLBACK，则可以实现数据的回滚，回滚到最近的一次 COMMIT 之后（不一定成功）

8.2 TRUNCATE TABLE & DELETE FROM

• 相同点：二者都可以实现对表中所有数据(记录)的删除，同时保留表的结构(字段)
• 不同点：
  • TRUNCATE TABLE：一旦执行此操作，则会清空表中所有数据(记录)，被删除的数据不能回滚
  • DELETE FROM：一旦执行此操作，则会删除表中指定数据(记录)，被删除的数据可以回滚

阿里开发规范：

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

8.3 总结：DDL(数据定义语言) & DML(数据操作语言)

• DDL的操作一旦执行，就不可回滚。因为在执行完DDL操作之后，一定会自动执行一次 COMMIT 操作，而此 COMMIT 操作并不受 SET autocommit = FALSE 的影响
• 默认情况下，DML操作是不可回滚的。因为在执行完DML操作之后，默认会执行一次 COMMIT 操作。但如果在执行DML操作前，执行了 SET autocommit = FALSE，则此时再执行DML操作，不会自动 COMMIT，DML操作可以实现回滚

COMMIT;  # 先提交一次数据

SELECT * FROM myemp3;

SET autocommit = FALSE;

DELETE FROM emp2;
#TRUNCATE TABLE emp2;

SELECT * FROM myemp3;

ROLLBACK;  # 回滚数据

SELECT * FROM myemp3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

9. 课后练习

练习1

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';

USE test01_office;

#2. 创建表dept01
/*
字段 		类型
id 		INT(7)
NAME 	VARCHAR(25)
*/
CREATE TABLE IF NOT EXISTS dept01(
id INT(7),
`NAME` VARCHAR(25)
);

#3. 将表departments中的数据插入新表dept02中
CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.`departments`;

#4. 创建表emp01
/*
字段 			类型
id 			INT(7)
first_name 	VARCHAR (25)
last_name 	VARCHAR(25)
dept_id 	INT(7)
*/
CREATE TABLE emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

DESC emp01;

#6. 根据表employees创建emp02
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.`employees`;

#7. 删除表emp01
DROP TABLE IF EXISTS emp01;

SHOW TABLES;
ROLLBACK; # DDL操作执行完后一定会自动COMMIT，无法回滚

#8. 将表emp02重命名为emp01
RENAME TABLE emp02 
TO emp01;

SHOW TABLES;

#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02
ADD test_column VARCHAR(25);

SELECT test_column
FROM dept02;

ALTER TABLE emp01
ADD test_column VARCHAR(25);

SELECT test_column
FROM emp01;

#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

DESC emp01;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

练习2

# 1、创建数据库 test02_market
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';

USE test02_market;

# 2、创建数据表 customers
/*
字段名 	数据类型
c_num 		INT
c_name 		VARCHAR(50)
c_contact 	VARCHAR(50)
c_city 		VARCHAR(50)
c_birth 	DATE
 */
CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth DATE
);

DESC customers;

# 3、将 c_contact 字段移动到 c_birth 字段后面
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

DESC customers;

# 4、将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY c_name VARCHAR(70);

DESC customers;

# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

DESC customers;

# 6、增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;

DESC customers;

# 7、将表名改为customers_info
RENAME TABLE customers
TO customers_info;

SHOW TABLES;

# 8、删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;

DESC customers_info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

练习3

# 1、创建数据库test03_company
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';

USE test03_company;

# 2、创建表offices
/*
字段名 		数据类型
officeCode 	int
city 		varchar(30)
address 	varchar(50)
country 	varchar(50)
postalCode 	varchar(25)
 */
CREATE TABLE IF NOT EXISTS offices(
officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);

SHOW TABLES;
DESC offices;

# 3、创建表employees
/*
字段名 		数据类型
empNum 		int
lastName 	varchar(50)
firstName 	varchar(50)
mobile 		varchar(25)
code 		int
jobTitle 	varchar(50)
birth 		date
note 		varchar(255)
sex 		varchar(5)
 */
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
); 
 
# 4、将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;

DESC employees;

# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;

DESC employees;

# 6、修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);

# 7、删除字段note
ALTER TABLE employees
DROP COLUMN note;

# 8、增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees
TO employees_info;

SHOW TABLES;
,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
); 
 
# 4、将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;

DESC employees;

# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthday DATE;

DESC employees;

# 6、修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);

# 7、删除字段note
ALTER TABLE employees
DROP COLUMN note;

# 8、增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees
TO employees_info;

SHOW TABLES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115