聚合函数、分组查询、嵌套查询、连表查询、分页查询、子查询

一、聚合函数

sum：求和 / avg：平均值 / count：数数，可以跟distinct组合使用 / max / min/ variance：方差 / std：标准差，stddev

1.查询年龄最大的学生的出生日期（聚合函数）

select min(stu_birth) as 出生日期 from tb_student;

2.查询年龄最小的学生的出生日期（聚合函数）

select max(stu_birth) as 出生日期 from tb_student;

3.查询编号为1111的课程考试成绩的最高分

select max(score) from tb_record where cid = '1111';

4.查询学号为1001的学生考试成绩的最低分

select min(score) from tb_record where sid = '1001';

5.查询学号为1001的学生成绩的平均分（round函数确定小数点后位数）

select round(avg(score), 1) from tb_record where sid = '1001';

6.查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分

select round(sum(score) / count(*), 1) as 平均分 from tb_record where sid = '1001';

7.查询学号为1001的学生考试成绩的标准差

select stddev(score) from tb_record where sid = '1001';

二、分组查询

1.查询男女学生的人数（分组和聚合函数）

select stu_sex as 性别, count(*) as 人数
from tb_student
group by stu_sex;

2.查询每个学院学生人数（with roolup：总计）

select col_id as 学院编号, count(*) as 人数
from tb_student
group by col_id
with rollup;

3.查询每个学院男女学生人数

select col_id as 学院编号, stu_sex as 性别, count(*) as 人数
from tb_student 
group by col_id, stu_sex;

4.查询每个学生的学号和平均成绩

select sid as 学号, round(avg(score), 1) as 平均成绩 
from tb_record
group by sid;

5.查询平均成绩大于等于90分的学生的学号和平均成绩

select sid as 学号, round(avg(score), 1) as 平均成绩
from tb_record
group by stu_id
having 平均成绩 >= 90;

注意：分组前的数据筛选用where子句，分组后的数据筛选用having子句

6.查询1111、105、106三门课程平均成绩大于等于90分的学生的学号和平均成绩

select sid as 学号, avg(score) as 平均成绩 
from tb_record
where cid in (1111, 105, 106)
group by sid
having 平均成绩 >= 90;

三、嵌套查询

subquery：把一个查询的结果作为另外一个查询的一部分来使用

1.查询年龄最大的学生的姓名（子查询/嵌套查询）

select stu_name
from tb_student
where stu_birth = (select min(stu_birth) from tb_student);

2.查询选了两门以上的课程的学生姓名（子查询/分组条件/集合运算）

select stu_name
from tb_student
where stu_id in (select sid from tb_record group by sid having count(*) > 2);

四、连表查询

1.查询学生的姓名、生日和所在学院名称

a.三表连接

select stu_name, stu_birth, col_name
from tb_student, tb_college
where tb_student.col_id = tb_college.col_id;

b.类连接

select stu_name, stu_birth, col_name
from tb_student inner join tb_college
on tb_student.col_id = tb_college.col_id;

c.自然连接

select stu_name, stu_birth, col_name
from tb_student natural join tb_college;

2.查询学生姓名、课程名称以及成绩（连接查询/联结查询）

a.三表连接

select stu_name, cou_name, score
from tb_student, tb_course, tb_record
where tb_student.stu_id = tb_record.sid
and tb_course.cou_id = tb_record.cid
and score is not null;

b.类连接

select stu_name, cou_name, score
from tb_student 
inner join tb_record
on tb_student.stu_id = tb_record.sid
inner join tb_course
on tb_course.cou_id = tb_record.cid
where score is not null;

c.自然连接

select stu_name, cou_name, score
from tb_student
natural join tb_record
natural join tb_course
where score is not null;

五、分页查询

limit：只取前条数据，offset：前几条数据不取

1.上面的查询结果取前5条数据（分页查询）

select stu_name, cou_name, score
from tb_student
natural join tb_record
natural join tb_course
where score is not null
order by cou_id asc, score desc
limit 5;

2.上面的查询结果取第6-10条数据（分页查询）

select stu_name, cou_name, score
from tb_student
natural join tb_record
natural join tb_course
where score is not null
order by cou_id asc, score desc
limit 5
offset 5;

3.上面的查询结果取第11-15条数据（分页查询）

select stu_name, cou_name, score
from tb_student
natural join tb_record
natural join tb_course
where score is not null
order by cou_id asc, score desc
limit 10, 5;

注意：limit也可以进行跳过操作，但是limit只能在mysql里操作

六、子查询

1.查询学生的姓名和平均成绩（子查询和连接查询）

select stu_name, avg_score
from tb_student
natural join
(select sid, round(avg(score), 1) as avg_score
from tb_record
group by sid) as tmp;

2.查询学生的姓名和选课的数量

select stu_name, total
from tb_student
natural join (select sid, count(*) as total 
from tb_record 
group by sid) as tmp;

3.查询每个学生的姓名和选课数量（左外连接和子查询）

外连接：左外连接 / 右外连接 / 全外连接（full outer join，MySQL不支持）

select stu_name, total
from tb_student as t1
left outer join (select sid, count(*) as total
from tb_record
group by sid) as t2
on t1.stu_id = t2.sid;

4.coalesce：返回第一个不为空的值

select coalesce(null, null, 0, null);