DQL 查询语句

排序

关键字说明:

asc 升序,默认
desc 降序

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

组合排序

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

聚合函数

使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

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

— 查询学Th总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;

— 查询 id字段，如果为 null，则使用 0代替
select ifnull(id,0) from student;
select count(ifnull(id,0)) from student;

分组

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

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

— 对分组查询的结果再进行过滤
SELECT sex, COUNT() FROM student3 WHERE age > 25 GROUP BY sex having COUNT() >2;

having 与 where 的区别

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

limit 语句

limit 的作用：
limit是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。

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

LIMIT 的使用场景：
分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示 5 条记录的方式来分页。

数据库备份和还原

备份数据库中的数据
1) 选中数据库，右键 ”备份/导出”
2) 指定导出路径，保存成.sql文件即可。

还原数据库中的数据
1) 删除数据库
2) 数据库列表区域右键“执行 SQL脚本”， 指定要执行的 SQL文件，执行即可

数据库表的约束

约束种类:

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

主键约束

主键的作用:
用来唯一标识数据库中的每一条记录

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

创建主键
主键关键字： primary key

主键的特点：
1) 非空 not null
2) 唯一

1.创建表时就创建主键
— 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
id int primary key , — id 为主键
name varchar(20),
age int
)
2. 在已有表中添加主键约束
alter table st5 add primary key (id);

删除主键
— 删除 st5表的主键约束
alter table st5 drop primary key;

主键自增
— 指定起始值为 1000, 如果不指定起始值默认从1开始
create table st4 (
id int primary key auto_increment,
name varchar(20)
) auto_increment = 1000;

创建好后修改起始值
alter table st4 auto_increment = 2000;

delete from st4 ;
# DELETE：删除所有的记录之后，自增长没有影响

TRUNCATE：删除以后，自增长又重新开始
truncate table st4;

唯一约束

什么是唯一约束： 表中某一列不能出现重复的值
关键字: unique
— 创建学Th表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学Th
create table st7 (
id int,
name varchar(20) unique # 唯一约束
)
&主键约束在一张表中只能有一个,但是not null unique 在一张表中可以有多个&

非空约束

什么是非空约束：某一列不能为 null。
关键字: not null
— 创建表学Th表 st8, 包含字段(id,name,gender)其中 name不能为 NULL create table st8 (
id int,
name varchar(20) not null, gender char(1)
)

默认值

— 创建一个学Th表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 ( id int,
name varchar(20),
addressvarchar(20) default ‘广州’
)

— 添加一条记录,使用默认地址
insert into st9 values (1, ‘李四’, default); select * from st9;

— 不填也是使用默认值
insert into st9 (id,name) values (2, ‘李白’);

— 添加一条记录,不使用默认地址
insert into st9 values (3, ‘李四光’, ‘深圳’);

外键约束

什么是外键约束
什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id
主表： 一方，用来约束别人的表
从表： 多方，被别人约束的表

1.已经建好表后创建外键约束
alter table [从表名字] constraint emp_depid_fk foreign key (dep_id) references department(id))

2.创建表时添加外键约束
— 多方，从表
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)) 或
foreign key (dep_id) references department(id))
)

删除外键约束
alter table employee drop foreign key emp_dept_fk;

外键的级联

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

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

级联操作风险性太大,强烈建议不使用级联操作!!!

数据约束小结

约束名 关键字 说明
主键 primary key 1) 唯一
2) 非空
默认 default 如果一列没有值，使用默认值
非空 not null 这一列必须有值
唯一 unique 这一列不能有重复值
外键 foreign key 主表中主键列，在从表中外键列

表与表之间的关系

员工表(多)

Id(主键) Name Age Dep_id(外键)(唯一)
1 小明 20 1
2 小花 21 2
3 小周 22 2
4 小虎 20 4(错)

一对多关系
部门表(一)

Id(主键) name loc
1 研发部 黄石
2 测试部 香港
3 产品部 大冶

一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

用户表

U_id(主键) Username password
1 老王 123
2 张强 123
3 王五 123

多对多关系
中间表

U_id(外键)(主键) R_id(外键)(主键)
1 1
1 2
1 3
2 1
3 1

多对多（m:n） 例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

角色表

R_id(主键) rolename
1 项目经理
2 技术总监
3 程序员

一对一关系
一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。
(在一对多的多表也就是从表中的外键添加唯一约束,就成了一对一关系)

三大范式

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

1NF

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

2NF

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

3NF

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

三大范式小结

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

总结

1.where要在group by 前面使用,且where后面不能使用聚合函数
2.使用了group by 后,要查询的字段只能是group by子句或者聚合函数
3.having不能单独使用,和group by 搭配使用
4.外键没有唯一与非空要求
5.聚合函数会忽略null值,有相关需求是要搭配ifnull使用