星云-全栈知识体系

DDL【数据定义语言】

2023/8/29 数据库MySQL

MySQL命名规则

  • 数据库名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 不能在对象名的字符间留空格
  • 必须不能和用户定义的其他对象重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
  • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

更多规范可参考：阿里MySQL数据库行为规范

# 1、库的管理

# 1.1、创建库

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

# 1.2、修改库

# 此句已作废，非要修改关闭MySQL，到data里修改文件名 ❌
RENAME DATABASE 旧库名 TO 新库名;

# 更改字符集 ✔
ALTER DATABASE 库名 CHARACTER SET 字符集;
1
2
3
4
5

# 1.3、删除库

DROP DATABASE [IF EXISTS] 库名;
1
例子练习 
#1、库的创建
#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;

#2、库的修改
RENAME DATABASE books TO 新库名;❌
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除
DROP DATABASE IF EXISTS books;
1
2
3
4
5
6
7
8
9
10
11

# 2、表的管理

# 2.1、创建表

# 切库
USE 库名;

# 创建表
CREATE TABLE 表名(
    列名 列的类型 [(长度) 约束],
    ...
    列名 列的类型 [(长度) 约束]
);

# 查看表结构
DESC 表名;
1
2
3
4
5
6
7
8
9
10
11
12

# 2.2、修改表

# 可以设置新加字段所在表的位置
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 [约束 first|after 字段名]
# 删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
# 修改字段
ALTER TABLE 表名 MODIFY COLUMN 字段名 新字段类型 新约束;
ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 字段类型 [约束];
# 删除表名
ALTER TABLE 表名 RENAME TO 新表名;
1
2
3
4
5
6
7
8
9

# 2.3、删除表

DROP TABLE [IF EXISTS] 表名;
1

# 2.4、表的复制

# 只复制表结构
CREATE TABLE 表名 LIKE 被复制表名;
# 复制表结构+数据
CREATE TABLE 表名 查询语句;
1
2
3
4
例子练习 
#表的管理
#案例：创建表Book
CREATE TABLE book (
    id          INT,					#编号
    bName       VARCHAR(20),	#图书名
    price       DOUBLE,				#价格
    authorId    INT,					#作者编号
    publishDate DATETIME			#出版日期
);
DESC book;

#案例：创建表author
CREATE TABLE IF NOT EXISTS author (
    id      INT,
    au_name VARCHAR(20),
    nation  VARCHAR(10)
)
DESC author;

#2.表的修改

#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列并设置位置
ALTER TABLE 表名 ADD COLUMN 字段名 类型 [first|after 字段名];
ALTER TABLE author ADD COLUMN annual DOUBLE;

#④删除列
ALTER TABLE book_author DROP COLUMN annual;

#⑤修改表名
ALTER TABLE author RENAME TO book_author;
DESC book;

#3.表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;

#通用的写法：
DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;
DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名 (
  ...
);

#4.表的复制
INSERT INTO author
VALUES (1, '村上春树', '日本'),
       (2, '莫言', '中国'),
       (3, '冯唐', '中国'),
       (4, '金庸', '中国');

SELECT * FROM Author;

#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2.复制表的结构 + 数据
CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT id, au_name
FROM author
WHERE nation = '中国';

#仅仅复制某些字段
CREATE TABLE copy4
SELECT id, au_name
FROM author
WHERE 0;
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

# 3、数据类型

数据库设计表时字段如何进行选择？可参考：数据库字段类型选择

# 3.1、分类

  • 数值型

  1. 整数型
  2. 浮点型：定点数、浮点数
  • 字符型

  1. 较短的文本：char、varchar
  2. 较长的文本：text、blob-较长的二进制数据
  • 日期型

# 3.2、整型

类型 大小（字节）
tinyint 1
smallint 2
mediumint 3
int/integer 4
bigint 8

特点

  • 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
  • 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
  • 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，并不是范围，如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变为无符号整型
例子练习 
#1.如何设置无符号和有符号
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);
DESC tab_int;

INSERT INTO tab_int VALUES(-123456, 0);
INSERT INTO tab_int VALUES(-123456, -123456);	#-123456,0❌
INSERT INTO tab_int VALUES(2147483648, 4294967296);	#❌

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) UNSIGNED ZEROFILL
);
INSERT INTO tab_int VALUES(123, 123);
SELECT * FROM tab_int;	# 0000123, 0000123
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

小数

类型 小数类
float(M, D) 浮点型
double(M, D) 浮点型
dec(M, D) 或 decimal(M, D) 定点型

特点

  • M：整数部位+小数部位，D：小数部位，如果超过范围，MySQL5.7不执行
  • M和D都可以省略，如果是decimal，则M默认为10，D默认为0，如果是float和double，则会根据插入的数值的精度来决定精度
  • 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
例子练习 
#测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT(5, 2),
	f2 DOUBLE(5, 2),
	f3 DECIMAL(5, 2)
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523, 123.4523, 123.4523);
INSERT INTO tab_float VALUES(123.456, 123.456, 123.456);	#均四舍五入
INSERT INTO tab_float VALUES(123.4, 123.4, 123.4);	#第三个后面补0
INSERT INTO tab_float VALUES(1523.4, 1523.4, 1523.4);	#三个都报错
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 3.3、字符型

类型 大小（字节） 说明
char 1 固定长度的字符，比较耗费空间，效率高，最大的字符数，可以省略，默认为1
varchar 可变长度的字符，比较节省空间，效率较char低，最大的字符数，不可以省略
binary和varbinary 用于保存较短的二进制
enum 用于保存枚举，只能n选1
set 用于保存集合，与enum的不同是可以多选
text 较长的文本保存
blob 较大的二进制
例子练习 
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');	#报错不执行
INSERT INTO tab_char VALUES('A');	#a 不区分大小写

CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');	#a,b
INSERT INTO tab_set VALUES('a,c,d');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 3.4、日期型

类型 说明
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间，大小：8字节，范围：1000—9999，不受时区影响
timestamp 保存日期+时间，大小：4字节，范围：1970—2038，受时区影响
例子练习 
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(), NOW());

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';	# 设置时区
SELECT * FROM tab_date;	#timestamp的时间会随着时区改变
1
2
3
4
5
6
7
8
9
10
11

# 4、约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

# 4.1、六大约束

  • NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号等
  • DEFAULT：默认，用于保证该字段有默认值。比如性别
  • PRIMARY KEY：主键，用于保证该字段的值非空唯一。比如学号、员工编号等
  • UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
  • CHECK：检查约束【mysql中不支持】。比如年龄、性别
  • FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机

  • 创建表时
  • 修改表时
  • 标识列

# 4.2、创建表时

CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型,
    表级约束
);
1
2
3
4
5

约束的添加分类

  • 列级约束：直接在字段名和类型后面追加 约束类型即可，只支持：默认、非空、主键、唯一，外键没效果
  • 表级约束：在各个字段的最下面，[constraint 约束名] 约束类型(字段名)，支持：主键、唯一、外键

主键和唯一的对比

  • 主键：保证唯一性，不允许为空，至多有1个主键，允许组合但不推荐
  • 唯一：保证唯一性，允许为空，可以有多个唯一，允许组合但不推荐

外键（重要）

  • 要求在从表设置外键关系
  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  • 主表的关联列必须是一个key（一般是主键或唯一）
  • 插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表

  • 级联删除：alter table语句添加外键结尾追加 ON DELETE CASCADE，则可直接删除主表，从表相应行同时被删除
  • 级联制空：alter table语句添加外键结尾追加 ON DELETE SET NULL，则可直接删除主表，从表相应行外键同时被置null
例子练习 
CREATE DATABASE students;
USE students;

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
		id      INT PRIMARY KEY,																#主键
    stuName VARCHAR(20) NOT NULL,														#非空
    gender  CHAR(1) CHECK (gender = '男' OR gender = '女'),	#检查❌
    seat    INT UNIQUE,																			#唯一
    age     INT DEFAULT 18,																	#默认约束
    majorId INT REFERENCES major (id)												#外键❌
);

CREATE TABLE major(
		id        INT PRIMARY KEY,
    majorName VARCHAR(20)
);

# 查看stuinfo中的所有索引，包括主键、外键、唯一会生成索引
SHOW INDEX FROM stuinfo;	#查看表中所有的索引

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo (
    id      INT,
    stuname VARCHAR(20),
    gender  CHAR(1),
    seat    INT,
    age     INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY (id),		#主键
    CONSTRAINT uq UNIQUE (seat),			#唯一键
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)	#外键
);
SHOW INDEX FROM stuinfo;

#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo (
    id      INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex     CHAR(1),
    age     INT DEFAULT 18,
    seat    INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
);
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

# 4.3、修改表时

  • 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
1
  • 添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
1
  • 修改表时删除约束
ALTER TABLE 表名 DROP  [constraint 约束名];
1
例子练习 
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo (
    id      INT,
    stuname VARCHAR(20),
    gender  CHAR(1),
    seat    INT,
    age     INT,
    majorid INT
);
DESC stuinfo;

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY (id);

#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE (seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id);


# 删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一，这里seat是唯一键名
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键，这里fk_stuinfo_major是外键名
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;
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

# 4.4、标识列

又称为自增长列：可以不用手动的插入值，系统提供默认的序列值

注意点

  • 标识列必须和主键搭配吗？不一定，但要求是一个key
  • 一个表至多一个标识列
  • 标识列的类型只能是数值型
  • 标识列可以通过手动插入值，设置起始值
# 设置起始序号
SET auto_increment_offset = 100;
# 设置步长
SET auto_increment_increment = 3;
1
2
3
4

创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) UNIQUE,
    seat INT
);

TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id, NAME) VALUES (NULL, 'john');
INSERT INTO tab_identity(NAME) VALUES ('lucy');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

set auto_increment_offset  = 100;
INSERT INTO tab_identity(id, NAME) VALUES (100, 'john');	#也可以在插入首个数据时写上
SET auto_increment_increment = 3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

修改表是设置标识列

#修改
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#删除
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;
1
2
3
4

TCL【事务控制语言】