MySQL约束

DQL 查询语句

排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认值
DESC: 降序

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

组合排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

聚合函数

五个聚合函数

SQL 中的聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

SELECT 聚合函数(列名) FROM 表名;
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;

我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL也统计进去呢？

select count(ifnull(id,0)) from student;

-- 查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;

分组

SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

having 与 where 的区别

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2) where 后面不可以使用聚合函数
having 子句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2) having 后面可以使用聚合函数

limit 语句

LIMIT offset,length;
offset：起始行数，从 0 开始计数，如果省略，默认就是 0
length： 返回的行数

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子
句][LIMIT 子句];

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

开始的索引=(当前的页码-1)*每页显示的条数

数据库表的约束

约束的作用：

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束种类：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check 注：mysql 不支持

主键约束

主键的作用
用来唯一标识数据库中的每一条记录
哪个字段应该作为表的主键？
通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。
如：身份证，学号不建议做成主键
创建主键

• 主键关键字： primary key
• 主键的特点：
  1. 非空 not null
  2. 唯一

在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY

在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

create table st5 (
 id int primary key, -- id 为主键
 name varchar(20),
 age int
)

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

主键自增

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

在创建表时，添加主键约束，并且完成主键自增长
create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);

修改自增长的默认值起始值

创建表时指定起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;

-- 指定起始值为 1000
create table st4 (
 id int primary key auto_increment,
 name varchar(20)
) auto_increment = 1000;

创建好以后修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
alter table st4 auto_increment = 2000;

删除自动增长
ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

DELETE 和 TRUNCATE 对自增长的影响

• DELETE：删除所有的记录之后，自增长没有影响。
• TRUNCATE：删除以后，自增长又重新开始。

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

字段名 字段类型 UNIQUE
-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
create table st7 (
 id int,
 name varchar(20) unique
)

删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
在表创建完后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

非空约束
什么是非空约束：某一列不能为 null。

字段名 字段类型 NOT NULL
-- 创建表学生表 st, 包含字段(id,name,gender)其中 name 不能为 NULL
create table st (
id int,
name varchar(20) not null,
gender char(1)
)

创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

默认值

字段名 字段类型 DEFAULT 默认值
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
 id int,
 name varchar(20),
 address varchar(20) default '广州'
)

-- 添加一条记录,使用默认地址
insert into st9 values (1, '李四', default);

外键约束

什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id

• 主表： 一方，用来约束别人的表
• 从表： 多方，被别人约束的表

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

-- 创建从表 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)
)

删除外键

ALTER TABLE 从表 drop foreign key 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

-- 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);

外键的级联

什么是级联操作：
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联操作语法	描述
ON UPDATE CASCADE	级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新
ON DELETE CASCADE	级联删除

-- 创建 employee 表，添加级联更新和级联删除
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) on update cascade on delete cascade
)

表与表之间的关系

表关系的概念

表与表之间的三种关系
一对多：最常用的关系 部门和员工
多对多：学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程
一对一：相对使用比较少。员工表 简历表， 公民表 护照表

一对多
一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
多对多
多对多（m:n） 例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
一对一
一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则：

一对一的建表原则	说明
外键唯一	主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键	主表的主键和从表的主键，形成主外键关系

数据库设计

数据规范化

什么是范式：

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

三大范式：

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

1NF

概念：
数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

2NF

概念：
在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。
第二范式的特点：

• 一张表只描述一件事情。
• 表中的每一列都完全依赖于主键

3NF：

概念：
在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。
简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
三大范式小结：

范式	特点
1NF	原子性：表中每列不可再拆分。
2NF	不产生局部依赖，一张表只描述一件事情
3NF	不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。