分页查询
将一个DQL语句执行后的查询结果集分段查询出来。
当一个查询结果记录数非常多时，通常都采取分页查询的方式来分段分批的查询减少不必要的系统开销。
分页查询在SQL92标准中没有定义。意味着不同的数据库有完全不同的分页写法。
在MySQL中是通过在ORDER BY子句后面追加LIMIT来完成分页的。
语法
ORDER BY 字段 LIMIT 跳过的记录数,每页显示的条目数
LIMIT中两个数字的换算公式:
- LIMIT (页数-1)*每页显示的条目数 , 每页显示的条目数
例如:每页显示5条，显示第三页?
LIMIT (3-1)*5,5 ==> LIMIT 10,5
每页显示8条，显示第9页?
LIMIT (9-1)*8,8 ==> LIMIT 64,8
例
- 查看老师工资排名的前5名? 分析:按工资降序，分页查询，每页5条，显示第一页
SELECT name,salary
FROM teacher
ORDER BY salary DESC
LIMIT 0,5
在DQL中可以使用表达式或者函数的结果进行查询
在SELECT子句中使用函数或表达式
- 查看每个老师的年薪是多少? 月薪*12
SELECT name,salary,salary*12
FROM teacher - 查看每个老师的工资+奖金的总和是多少? salary+comm
任何数字与NULL进行运算结果都是NULL
SELECT name,salary,comm,salary+comm
FROM teacher
IFNULL(arg1,arg2):如果arg1不为NULL函数直接返回arg1,若为NULL函数返回arg2
IFNULL的逻辑示意:
IFNULL(arg1,arg2){
if(arg1==null){
return arg2;
}else{
return arg1;
}
}
SELECT name,comm,IFNULL(comm,0)
FROM teacher
查看老师的工资+奖金
SELECT name,salary,comm,salary+IFNULL(comm,0)
FROM teacher
在WHERE子句中使用函数或表达式作为过滤条件
- 查看年薪小于5万的老师都有谁?
SELECT name,salary,salary*12
FROM teacher
WHERE salary*12<50000
分组查询
GROUP BY子句可以将其所在的DQL语句中的查询结果集上按照指定的字段值相同的记录进行分组，搭配聚合函数可以进行组内统计。
分组是配合统计的，如果SELECT子句中没有聚合函数时，无需使用GROUP BY子句
例
- 查看每个科目的老师的平均工资分别是多少?
SELECT name,salary,subject_id
FROM teacher - 在DQL上添加GROUP BY子句
SELECT salary,subject_id
FROM teacher
GROUP BY subject_id
意思是将:
SELECT salary,subject_id
FROM teacher
查询出的结果集按照subject_id字段值相同的记录分组 - 最终配合聚合函数，可以对结果集中五组数据产生五个统计结果
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
在SELECT子句中如果包含聚合函数,那么凡是不在聚合函数中的字段都应当出现在GROUP BY子句中
- 查看每个班各有多少学生?提示:student表的class_id为班级编号,该字段值相同的为同一个班
SELECT COUNT(*),class_id
FROM student
GROUP BY class_id
- 查看学校每种职位的学生各多少人?以及每个职位最大的生日和最小生日?
SELECT COUNT(*) 总人数,MAX(birth) 最小生日,MIN(birth) 最大生日,job
FROM student
GROUP BY job
GROUP BY可以按照多列分组。结果集中指定的这些列值的组合相同的记录看作一组
在分组统计中使用过滤条件
例
SELECT AVG(salary),subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id
聚合函数不能被应用于WHERE子句中
本质原因是过滤时机不同，WHERE子句中添加的过滤条件是在第一次从表中逐条检索数据时生效的，从而产生查询结果集。
实际的过滤实际应当是在从表中查询出结果集并针对该结果集分组统计得出统计结果后再进行过滤
HAVING子句
HAVING子句是紧跟在GROUP BY子句之后的子句，它的作用是添加过滤条件针对分组统计结果后的数据进行筛选
HAVING子句可以利用聚合函数的统计的结果进行过滤
- 查看每个科目老师的平均工资，前提是之查看平均工资高于6000的?
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
HAVING AVG(salary)>6000 - 查看每个科目老师的平均工资，前提是该科目老师的最高工资高于5000?
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary)>5000
子查询
嵌套在其他SQL语句中的一条DQL语句，那个这个DQL就称为是子查询。
子查询常被应用于:
- DQL中 ： 可以基于一个查询结果集进行查询(最常被用于DQL语句)
- DML中 : 可以基于一个查询结果集进行增删改操作
- DDL中 ： 可以基于一个查询结果集进行数据对象操作(创建表，视图等)
在DQL语句中使用子查询
子查询在DQL中使用时必须使用"()"括起来
- 查看高于老师平均工资的那些老师的工资分别是多少?
1:确定条件=>老师的平均工资
SELECT AVG(salary) FROM teacher
2:基于条件查询
SELECT name,salary
FROM teacher
WHERE salary>(SELECT AVG(salary) FROM teacher) - 查看工资最高的那个老师的工资及奖金是多少?
1:查看老师的最高工资是多少?
SELECT MAX(salary) FROM teacher
2:查看工资最高的那个老师的工资及奖金是多少?
SELECT name,salary,comm
FROM teacher
WHERE salary=(SELECT MAX(salary) FROM teacher)
子查询分类
- 单行单列子查询:查询结果集为一行一列，即:只有一个值
通常用于过滤条件，可以搭配:=,>,>=,<,<=,<>使用 - 多行单列子查询:查询结果集为多行一列，即:有多个值
通常用于过滤条件，但是要搭配:IN,ALL,ANY使用
IN等效于"="做等于判断，只不过是判断等于列表其中之一
ALL和ANY用于">,>=,<,<="使用:
- >ANY：大于列表之一(大于列表最小的即可)
- >ALL: 大于列表所有(大于列表最大的)
- 多行多列子查询:查询结果集为一个表.
- 常被应用于DQL语句中的FROM子句中当作一张表看待
- 被用作DDL语句中将一个结果集当作一张表创建出来
多行单列子查询
- 查看比科目2和科目3老师工资都高的老师都有谁?
1:知道科目2和科目4老师的工资分别是多少?
SELECT salary FROM teacher WHERE subject_id IN (2,4)
2:大于上述查询所有工资即可
SELECT name,salary
FROM teacher
WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN (2,4))
多行多列子查询
- 在DDL语句中使用
创建一张表，表明:teacher_salary_info。该表字段:max_sal,min_sal,avg_sal,sum_sal,subject_id
表中记录了每个科目老师的最高工资，最低工资，平均工资和工资总和以及对应的科目
表中数据对应的查询语句:
SELECT
MAX(salary) max_sal,MIN(salary) min_sal,
AVG(salary) avg_sal,SUM(salary) sum_sal,subject_id
FROM teacher
GROUP BY subject_id
上述DQL查询结果集就是我们目标要创建的表teacher_salary_info希望的样子与数据
CREATE TABLE teacher_salary_info
AS
SELECT
MAX(salary) max_sal,MIN(salary) min_sal,
AVG(salary) avg_sal,SUM(salary) sum_sal,subject_id
FROM teacher
GROUP BY subject_id
关联查询
查询结果集中的数据来自于多张表，而表与表中的数据之间存在的对应关系被称为关联关系。
两张表中的数据就可以产生关联关系，常见的关联关系有三种
- 一对一:A表中的一条记录仅唯一对应B表中的一条记录
- 一对多:A表中的一条记录可以对应B表中的多条记录
- 多对多:A表与B表双向都是一对多关系就称为多对多关系
关联查询就是指联合多张表查询数据形成一个查询结果集的过程。
在关联查询中的重中之重就是指定关联条件，也叫连接条件
原则上，N张表关联查询要有至少N-1个连接条件
例如:3张表关联查询至少要有2个连接条件
笛卡尔积
缺少连接条件时会产生笛卡尔积，会将A表每条记录与B表每条记录都建立一次连接，产生巨量数据，这通常是一个无意义的结果集，要尽量避免。
数据量计算:
以两张表为例:若A表10条记录，B表10条记录，笛卡尔积会产生两张表数据量乘积的条数。
即:10*10等于100条记录
关联查询语法:
SELECT 字段
FROM 表A,表B[,表C,...]
WHERE 连接条件
AND 过滤条件
注意:表与表之间的连接条件必须与过滤条件同时满足！否则会产生笛卡尔积
例：
- 查看每个学生的名字，年龄以及其所在的班级名称和所在楼层
1:先确定数据来自于哪些表?
学生信息来自于student表，班级信息来自于class表
明确FROM子句:
FROM student,class
2:确定两张表之间的数据关联关系
学生表中某条记录的class_id的值与班级表中某条记录的id值相同的建立连接关系
明确WHERE子句中的连接条件:
WHERE student.class_id=class.id
3:查询相应的字段并指定对应的过滤条件...
查询数据时要指定表名.列名来明确数据来自哪张表的字段，此时由于表名可能较长，导致SQL编写可读性差:
SELECT student.name,student.age,class.name,class.floor
FROM student,class
WHERE student.class_id=class.id
解决办法，为表取别名:
SELECT s.name,s.age,c.name,c.floor
FROM student s,class c
WHERE s.class_id=c.id
关联关系通常是建立在主外键等值连接的基础上的
以学生表Student和班级表Class为例:
班级表中id字段作为班级表的主键:primary key(pk). 主键:非空且唯一
学生表中的学生为了与班级表的记录产生对应关系，因此在学生表中以class_id字段记录了其所在的班级的主键字段的值。那么class_id就称为是class表主键所对应的外键:foreign key(fk)
保存外键字段值的表在关联关系(一对多)中处于"多"的一方
多对多关系
两张表之间双向都是一对多就是多对多关系。多对多关系需要依靠一张关联关系表维系。
关联关系表中有两个字段分别记录了这两张表的主键字段。
t_stu_subject_score表，该表有两个字段stu_id，subject_id分别记录了student表的id和subject表的id。因此依靠这张表就可以实现student与subject表的多对多关系。
实际查询时，写法就是三张表关联查询即可
- 查看'李费水'每门课程的成绩是多少?
结果集中需要体现:学生名字，科目名称，考试成绩
1:数据来自哪些表?
学生名字来自student表，科目名称来自subject表，考试成绩来自t_stu_subject_scroe
2:student表与subject表本身没有关联关系，它们都与t_stu_subject_score有关系
关联条件:student别名s t_stu_subject_score别名sss subject别名su
s.id=sss.stu_id
sss.subject_id=su.id
SELECT s.name,su.name,sss.score
FROM student s,subject su,t_stu_subject_score sss
WHERE s.id=sss.stu_id
AND sss.subject_id=su.id
AND s.name='李费水'
内连接
语法
SELECT t1.xxx,t2.xxx,...
FROM 表1 t1
JOIN 表2 t2 ON 与表1的连接条件
[JOIN 表3 t3 ON 与表1或表2的连接条件]
内连接的查询与关联查询一致，却别在于我们将关联关系(连接条件)单独定义在JOIN后面跟的ON子句上。
优点:关联查询时结构清晰，连接条件与过滤条件分开在不同的子句定义。ON子句定义连接条件，WHERE子句定义过滤条件
例
- 查看每个班的班主任是谁?
- 原来的写法
SELECT c.name,t.name
FROM class c,teacher t
WHERE c.teacher_id=t.id - 内连接的写法
SELECT c.name,t.name
FROM class c
JOIN teacher t ON c.teacher_id=t.id
- 查看2年级每个班的学生的名字以及其班主任的名字?
SELECT s.name,c.name,t.name
FROM student s
JOIN class c ON s.class_id=c.id
JOIN teacher t ON c.teacher_id=t.id
WHERE c.name LIKE '2年级%' - 查看4年级每个班的数学平均分是多少?
SELECT AVG(sss.score),su.name,c.name
FROM class c
JOIN student s ON s.class_id=c.id
JOIN t_stu_subject_score sss ON s.id=sss.stu_id
JOIN subject su ON sss.subject_id=su.id
WHERE c.name LIKE '4年级%'
AND su.name='数学'
GROUP BY c.name,su.name 班级名相同且科目相同的记录看作一组
GROUP BY子句中如果出现了多个字段，则是将结果集中指定的这些字段值的组合相同的记录看作一组。
外连接
外连接的作用是将关联查询中不满足关联条件的记录显示在结果集中。
外连接分类:
- 左外连接 LEFT JOIN
左外连接是将LEFT JOIN左侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于LEFT JOIN右侧表的字段时，不满足连接条件的记录值全为NULL - 右外连接 RIGHT JOIN
右外连接是将RIGHT JOIN右侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于RIGHT JOIN左侧表的字段时，不满足连接条件的记录值全为NULL
例
- 查看每个班级名字和班主任的名字
SELECT c.name,t.name
FROM class c
JOIN teacher t ON c.teacher_id=t.id
class表中如:1年级4班没有被列出在结果集中
teacher表中如:韩少云没有被列出在结果集中
原因:这些记录不满足连接条件 c.teacher_id=t.id
结论:不满足连接条件的记录不会在关联查询和内连接中被查询出来 - 查看每个班级名字和班主任的名字,如果该老师不带班也要把老师显示出来
需求:当teacher表中有不满足连接条件的记录时，也要将其显示出来
SELECT c.name,t.name
FROM class c
RIGHT JOIN teacher t ON c.teacher_id=t.id - UNION取并集，将左连接与右连接并集，可以达到全连接效果
union可以连接两个DQL语句的结果集，并将它们并为一个结果集，其中重复的记录仅显示一次。
SELECT c.name,t.name
FROM class c
LEFT JOIN teacher t ON c.teacher_id=t.id
UNION
SELECT c.name,t.name
FROM class c
RIGHT JOIN teacher t ON c.teacher_id=t.id
自连接
同一张表中的一条数据可以对应多条数据。
自连接通常用于保存具有相同属性且存在上下级关系的树状结构数据使用
例如
例
- 查看每个老师和他的领导是谁?
关联查询写法:
SELECT t.name,m.name
FROM teacher t,teacher m
WHERE t.manager=m.id
内连接
SELECT t.name,m.name
FROM teacher t
JOIN teacher m ON t.manager=m.id - 把所有老师和他的上司列出来，没有上司的也要把该老师列出来
SELECT t.name,m.name
FROM teacher t
LEFT JOIN teacher m ON t.manager=m.id - 查看3年级2班的班长是谁?
SELECT s.name
FROM student s
JOIN class c ON c.id=s.class_id
WHERE c.name='3年级2班'
AND s.id=s.team_leader - 年龄最大的学生所在班的班主任的上司是谁?
SELECT
s.name '学生的名字',c.name '所在班级',
t.name '班主任',m.name '班主任的上司'
FROM student s
JOIN class c ON s.class_id=c.id
JOIN teacher t ON c.teacher_id=t.id
JOIN teacher m ON t.manager=m.id
WHERE s.birth=(SELECT MIN(birth) FROM student)
关联查询中未指定连接条件，或指定的连接条件无效时会产生笛卡尔积
结果集的数据量是参与查询表中记录数的乘积,除非应用需要，否则应尽量避免