# DQL查询语言
## 子查询
按照结果集的行列数不同，子查询可以分为以下几类：
- 标量子查询：结果集只有一行一列（单行子查询）
- 列子查询：结果集有一列多行
- 行子查询：结果集有一行多列
- 表子查询：结果集多行多列```sql
-- 查询比小虎年龄大的所有学生
-- 标量子查询
SELECT
*
FROM
student
WHERE
age > ( SELECT age FROM student WHERE NAME = '小虎' );
``````sql
-- 查询有一门学科分数大于90分的学生信息
-- 列子查询
SELECT
*
FROM
student
WHERE
id IN (
SELECT
s_id
FROM
scores
WHERE
score > 90);
``````sql
-- 查询男生且年龄最大的学生
-- 行子查询
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 优化
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
```> 总结：
>
> - where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。
> - where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。```sql
-- 取排名数学成绩前五的学生，正序排列
SELECT
*
FROM
(
SELECT
s.*,
sc.score score,
科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON = sc.c_id
WHERE
= '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';
```> 经验分享：
>
> 1. 分析需求
> 2. 拆步骤
> 3. 分步写sql
> 4. 整合拼装sql```sql
-- 查询每个老师的代课数
SELECT , ,( SELECT count(*) FROM course c WHERE = ) AS 代课的数量
FROM
teacher t;
----------------------------------------------------------------------------
SELECT
,
,
count(*) '代课的数量'
FROM
teacher t
LEFT JOIN course c ON c.t_id =
GROUP BY
,
;
``````sql
-- exists
SELECT
*
FROM
teacher t
WHERE
EXISTS ( SELECT * FROM course c WHERE c.t_id = );
----------------------------------------------------------------------------SELECT
t.*,
c.`name`
FROM
teacher t
INNER JOIN course c ON = c.t_id;
```> 总结：如果一个需求可以不用子查询，尽量不使用。
>
> `sql可读性太低。`除了一些理论知识的学习，今天还有大量的实训案例，来看一下今天的实训成果吧！
-- 1.查询'01'号学生的姓名和各科成绩 **
SELECT
s.id sid,
s.`name` sname,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON = sc.c_id
WHERE
s.id = 1;
-- 2.查询各个学科的平均成绩和最高成绩**
SELECT
,
c.`name`,
AVG( sc.score ),
max( sc.score )
FROM
course c
LEFT JOIN scores sc ON = sc.c_id
GROUP BY
,
c.`name`;
-- 3.查询每个同学的最高成绩和科目名称
-- 4.查询所有姓张的同学的各科成绩**
SELECT
s.id,
s.`name`,
c.`name` cname,
sc.score
FROM
student s
LEFT JOIN scores sc ON sc.s_id = s.id
LEFT JOIN course c ON = sc.c_id
WHERE
s.`name` LIKE '张%';
-- 5.查询每个课程的最高分的学生信息
SELECT
*
FROM
student s
WHERE
id IN (
SELECT DISTINCT
r.s_id
FROM
(
SELECT
,
,
max( score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON = r.c_id
GROUP BY
,
) t
LEFT JOIN scores r ON r.c_id =
AND t.score = r.score
);
-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
select
student.`name`,
scores.`score`,
course.`name`
FROM
student
LEFT JOIN scores ON studen = scores.s_id
LEFT JOIN course ON course.id = scores.c_id
where
studen = 1
and
studen LIKE '张%'
OR studen LIKE '李%';
-- 7.查询平均成绩及格的同学的信息。
SELECT
*
FROM
student
WHERE
id IN (
SELECT
sc.s_id
FROM
scores sc
GROUP BY
sc.s_id
HAVING
avg( sc.score ) >= 70
);
-- 8.将学生按照总分数进行排名。
SELECT
studen,student.`name`,SUM(scores.score) sum
FROM
student
LEFT JOIN scores on studen = scores.s_id
GROUP BY scores.s_id
ORDER BY sum DESC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
course.`name`,MAX(scores.score),MIN(scores.score),avg(scores.score)
FROM
student
LEFT JOIN scores ON studen = scores.s_id
LEFT JOIN course ON course.id = scores.c_id
WHERE
= '数学';
-- 10.将各科目按照平均分排序。
SELECT
course.`name`,avg(scores.score) avg
FROM
scores
LEFT JOIN course ON course.id = scores.c_id
GROUP BY scores.c_id
ORDER BY avg DESC;
-- 11.查询老师的信息和他所带的科目的平均分
select
t.*,,,avg(sc.score)
from
teacher t
left join course c on =c.t_id
left join scores sc on sc.c_id=
group by
,;
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
select
t.*,,,max(sc.score),min(sc.score)
from
teacher t
left join course c on =c.t_id
left join scores sc on sc.c_id=
group by
,
having
in ('Tom','Jerry');
-- 14.查询所有学生的课程及分数
SELECT
s.id,
,
,
,
r.score
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
LEFT JOIN course c ON = r.c_id;
-- 13.查询每个学生的最好成绩的科目名称
SELECT
,
t.sname,
r.c_id,
,
t.score
FROM
(
SELECT
s.id,
sname,
max( r.score ) score
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
GROUP BY
s.id,
) t
LEFT JOIN scores r ON r.s_id =
AND r.score = t.score
LEFT JOIN course c ON r.c_id = ;
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名
SELECT
*
FROM
student s
WHERE
s.id IN (
SELECT
r.s_id
FROM
scores r
WHERE
r.c_id = 1
AND r.score > 60);
SELECT
s.*,
r.*
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
WHERE
r.c_id = 1
AND r.score > 60;
-- 16. 查询平均成绩大于等于70的所有学生学号、姓名和平均成绩
select
,,avg(sc.score)
from
student stu
left join course c on =
left join scores sc on sc.s_id=sc.c_id
group by
,
having
avg(sc.score)>=70;
-- 17.查询有不及格课程的学生信息
select
stu.*,,sc.score
from
student stu
left join course c on =
left join scores sc on =sc.c_id
where
sc.score<60;
-- 18.查询每门课程有成绩的学生人数
select count() ,
from
( select
,
from
course c
left join scores sc on sc.c_id=
where
sc.score>0)t
left join course c on =
group by
,;
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
select
,,avg(sc.score)
from
course c
left join scores sc on sc.c_id=
group by
,
order by
avg(sc.score) desc,
asc;
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
select
,,avg(sc.score)
from
student stu
left join course c on =
left join scores sc on sc.s_id=sc.c_id
group by
,
having
avg(sc.score)>60;
-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
select
,
from
student stu
left join scores sc on =sc.s_id
where
sc.score>80
group by
,
having
count(*)=1;
-- 22.查询出只有三门课程的学生的学号和姓名
select
,
from
student stu
left join scores sc on =sc.s_id
group by
,
having
count(*)=3;
-- 23.查询有不及格课程的课程信息
select
,,sc.score
from
course c
left join scores sc on sc.c_id=
group by
,
having
min(sc.score)<60;
-- 24.查询至少选择4门课程的学生信息
SELECT
s.id,
FROM
student s
LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
s.id,
HAVING
count(*) >= 4;
-- 25.查询没有选全所有课程的同学的信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
r.s_id
FROM
scores r
GROUP BY
r.s_id
HAVING
count(*) != 5
);
-- 26.查询选全所有课程的同学的信息
select
stu.*
from
student stu
left join course c on =
group by
,
having
(select count(id)from course)=5;
-- 27.查询各学生都选了多少门课
select
stu.*,count(*)
from
student stu
left join scores sc on =sc.s_id
group by
,;
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
select
,,sc.score
from
student stu
left join course c on =
left join scores sc on sc.c_id=
where
='java'
and
sc.score<60;
-- 29.查询学过"Tony"老师授课的同学的信息
select
stu.*
from
student stu
left join scores sc on sc.s_id =
left join course c on sc.c_id=
left join teacher t on c.t_id=
where
='Tony';
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT
*
FROM
student
WHERE
id NOT IN (
SELECT DISTINCT
s.id
FROM
student s
LEFT JOIN scores r ON r.s_id = s.id
LEFT JOIN course c ON = r.c_id
LEFT JOIN teacher t ON = c.t_id
WHERE
= 'Tom'
);
-- 1根据学生学号 查询 2020年-1-1之后借过哪些书籍
select
stu.sid ,stu.sname,br.bdate
from
student1 stu
left join borrowrecored br on stu.cid=br.cid
left join book b on br.bid=b.bid
where
br.bdate>'2020-1-1';
-- 2根据书籍编号 查询2020-1-1之前都有那哪些学生借阅过
select
b.bid,b.bname,br.bdate,stu.sname
from
book b
left join borrowrecored br on b.bid=br.bid
left join student stu on br.bid=stu.sid
where
br.bdate<'2020-1-1';
-- 3根据学生编号查询该生一共借阅过多少本书
select
stu.sid,stu.sname,count(*)
from
student stu
left join borrowrecored br on br.bid=stu.sid
group by
stu.sid,stu.sname;
-- 4根据书籍编号查询书籍一共被借阅过多少次
select
b.bid,b.bname,count(*)
from
book b
left join borrowrecored br on br.bid=b.bid
group by
b.bid,b.bname;
-- 5根据学院名称查询该学院的学生一共借过哪些书籍
select
stu.sdepartment,b.bname,count(*)
from
student1 stu
left join book b on stu.sid=b.bid
left join borrowrecored br on b.bid=br.bid
group by
stu.sdepartment,b.bname;
-- 6查询哪个学生最爱看书
select
stu.sid,stu.sname ,count(*)
from
student stu
left join book b on stu.sid=b.bid
left join borrowrecored br on b.bid=br.bid
group by
stu.sid
order by
count(*) desc;
-- 7查询那本书最受欢迎
select
b.bid,b.bname,count(*)
from
book b
left join borrowrecored br on br.bid=b.bid
group by
b.bid,b.bname
order by
count(*) desc
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
