MySQL约束与设计

1 DQL查询语句

1.1排序

# 通过 ORDER BY ⼦句，可以将查询出的结果进⾏排序(排序只是显示⽅式，不会影响数据库中
数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
# ASC: 升序，默认值
# DESC: 降序
1234

1.11 单列排序

-- 查询所有数据,使⽤年龄降序排序
select * from student order by age desc;

1.22 组合排序

-- 查询所有数据, 在年龄降序排序的基础上, 如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

1.2集合函数

之前我们做的查询都是横向查询，它们都是根据条件⼀⾏⼀⾏的进⾏判断，⽽使⽤聚合函数查询
是纵向查询，它是对⼀列的值进⾏计算，然后返回⼀个结果值。聚合函数会忽略空值 NULL。

-- 查询学⽣总数
select count(id) as 总⼈数 from student;
select count(*) as 总⼈数 from student;

# IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认
值。
-- 查询 id 字段，如果为 null，则使⽤ 0 代替
select IFNULL(id, 0) from student;

1.3分组

# 分组查询是指使⽤ GROUP BY 语句对查询信息进⾏分组，相同数据作为⼀组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

1.31 having与where的区别

1.4limit语句

LIMIT 是限制的意思，所以 LIMIT 的作⽤就是限制查询记录的条数

-- 查询学⽣表中数据，从第 3 条开始显示，显示 6 条。
select * from student limit 2, 6;

LIMIT 的使⽤场景
分⻚：⽐如我们登录京东，淘宝，返回的商品信息可能有⼏万条，不是⼀次全部显示出来。是⼀
⻚显示固定的条数。 假设我们每⻚显示 5 条记录的⽅式来分⻚。

-- 如果第⼀个参数是 0 可以省略写
select * from student3 limit 5;
-- 最后如果不够 5 条，有多少显示多少
select * from student3 limit 10, 5;

2 数据库表的约束

约束种类

2.1主键约束

创建主键

主键关键字：primary key
主键的特点：

●⾮空 not null

●唯⼀

●创建主键的⽅式：

1. 在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

2. 在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除主键

-- 删除 st5 表的主键
alter table st5 drop primary key;
-- 添加主键
alter table st5 add primary key(id);

主键自增

主键如果让我们⾃⼰添加很有可能重复，我们通常希望在每次插⼊新记录时，数据库⾃动⽣成主
键字段的值

创建表时指定起始值
CREATE TABLE 表名(
 列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
创建好以后修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

2.2唯一约束

什么是唯⼀约束：表中某⼀列不能出现重复的值

字段名 字段类型 UNIQUE

2.3非空约束

什么是⾮空约束：某⼀列不能为null

字段名 字段类型 NOT NULL

默认值

字段名 字段类型 DEFAULT 默认值

疑问：如果⼀个字段设置了⾮空与唯⼀约束，该字段与主键的区别？

1. 主键数在⼀个表中，只能有⼀个。不能出现多个主键。主键可以单列，也可以是多列。
2. ⾃增⻓只能⽤在主键上。

2.4外键约束

●什么是外键：在从表中与主表主键对应的那⼀列，如：员⼯表中的 dep_id
●主表：⼀⽅，⽤来约束别⼈的表
●从表：多⽅，被别⼈约束的表

已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名)
REFERENCES 主表(主键字段名);

-- 1) 删除副表/从表 employee
drop table employee;
-- 2) 创建从表employee并添加外键约束emp_depid_fk
-- 多⽅，从表
create table employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dep_id int, -- 外键对应主表的主键
 -- 创建外键约束
 constraint emp_depid_fk foreign key (dep_id) references
department(id)
);
-- 3) 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⽼王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⼤王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('⼩王', 18, 2);
select * from employee;
-- 4) 部⻔错误的数据添加失败
-- 插⼊不存在的部⻔
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('⽼张', 18, 6);

删除外键
ALTER TABLE 从表 drop foreign key 外键名称;

2.5数据约束⼩结

3 表与表之间的关系

例子（多对多）
学生选课

4 范式

4.1三大范式

⽬前关系数据库有六种范式：第⼀范式(1NF)、第⼆范式(2NF)、第三范式(3NF)、巴斯-科德范式
(BCNF)、 第四范式(4NF)和第五范式(5NF，⼜称完美范式)。
满⾜最低要求的范式是第⼀范式(1NF)。在第⼀范式的基础上进⼀步满⾜更多规范要求的称为第
⼆范式(2NF)， 其余范式以次类推。⼀般说来，数据库只需满⾜第三范式(3NF)就⾏了。

4.2 1NF

数据库表的每⼀列都是不可分割的原⼦数据项，不能是集合、数组等⾮原⼦数据项。即表中的某
个列有多个值时，必须拆分为不同的列。简⽽⾔之，第⼀范式每⼀列不可再拆分，称为原⼦性。

4.3 2NF

在满⾜第⼀范式的前提下，表中的每⼀个字段都完全依赖于主键。
所谓完全依赖是指不能存在仅依赖主键⼀部分的列。简⽽⾔之，第⼆范式就是在第⼀范式的基础
上所有列完全依赖于主键列。当存在⼀个复合主键包含多个主键列的时候，才会发⽣不符合第⼆
范式的情况。⽐如有⼀个主键有两个列，不能存在这样的属性，它只依赖于其中⼀个列，这就是
不符合第⼆范式。

特点：

1. ⼀张表只描述⼀件事情。
2. 表中的每⼀列都完全依赖于主键。

4.3 3NF

在满⾜第⼆范式的前提下，表中的每⼀列都直接依赖于主键，⽽不是通过其它的列来间接依赖于
主键。
简⽽⾔之，第三范式就是所有列不依赖于其它⾮主键列，也就是在满⾜ 2NF 的基础上，任何⾮主
列不得传递依赖于主键。所谓传递依赖，指的是如果存在 “A → B → C” 的决定关系，则 C 传递依
赖于 A。因此，满⾜第三范式的数据库表应该不存在如下依赖关系：主键列 → ⾮主键列 x → ⾮
主键列 y

4.4三⼤范式⼩结