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
●唯⼀
●创建主键的⽅式:
- 在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY
- 在已有表中添加主键
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 默认值
疑问:如果⼀个字段设置了⾮空与唯⼀约束,该字段与主键的区别?
- 主键数在⼀个表中,只能有⼀个。不能出现多个主键。主键可以单列,也可以是多列。
- ⾃增⻓只能⽤在主键上。
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
在满⾜第⼀范式的前提下,表中的每⼀个字段都完全依赖于主键。
所谓完全依赖是指不能存在仅依赖主键⼀部分的列。简⽽⾔之,第⼆范式就是在第⼀范式的基础
上所有列完全依赖于主键列。当存在⼀个复合主键包含多个主键列的时候,才会发⽣不符合第⼆
范式的情况。⽐如有⼀个主键有两个列,不能存在这样的属性,它只依赖于其中⼀个列,这就是
不符合第⼆范式。
特点:
- ⼀张表只描述⼀件事情。
- 表中的每⼀列都完全依赖于主键。
4.3 3NF
在满⾜第⼆范式的前提下,表中的每⼀列都直接依赖于主键,⽽不是通过其它的列来间接依赖于
主键。
简⽽⾔之,第三范式就是所有列不依赖于其它⾮主键列,也就是在满⾜ 2NF 的基础上,任何⾮主
列不得传递依赖于主键。所谓传递依赖,指的是如果存在 “A → B → C” 的决定关系,则 C 传递依
赖于 A。因此,满⾜第三范式的数据库表应该不存在如下依赖关系:主键列 → ⾮主键列 x → ⾮
主键列 y