20220817 第一组 于芮 mysql数据库查询（第三十四天）

 

小白成长记——第三十四天

 

   今天主要学习了mysql数据库的查询语句，对于一个合格的程序猿来说，掌握数据库的查询语句是非常重要的，所以今天不仅学习了理论知识，还作了大量的案例，进行实训，以达到熟练掌握mysql数据库查询语句的技能，所以，来看一下今天的学习笔记吧！

# 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,
c.NAME 科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
c.NAME = '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';
```

> 经验分享：
>
> 1. 分析需求
> 2. 拆步骤
> 3. 分步写sql
> 4. 整合拼装sql

```sql
-- 查询每个老师的代课数
SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量
FROM
teacher t;
----------------------------------------------------------------------------
SELECT
t.id,
t.NAME,
count(*) '代课的数量'
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.id
GROUP BY
t.id,
t.NAME;
```

```sql
-- exists
SELECT
*
FROM
teacher t
WHERE
EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
----------------------------------------------------------------------------SELECT
t.*,
c.`name`
FROM
teacher t
INNER JOIN course c ON t.id = 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 c.id = sc.c_id
WHERE
    s.id = 1;
    -- 2.查询各个学科的平均成绩和最高成绩**
    SELECT
    c.id,
    c.`name`,
    AVG( sc.score ),
    max( sc.score )
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
    c.id,
    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 c.id = sc.c_id
WHERE
    s.`name` LIKE '张%';   
    -- 5.查询每个课程的最高分的学生信息
    SELECT
    *
FROM
    student s
WHERE
    id IN (
    SELECT DISTINCT
        r.s_id
    FROM
        (
        SELECT
            c.id,
            c.NAME,
            max( score ) score
        FROM
            student s
            LEFT JOIN scores r ON r.s_id = s.id
            LEFT JOIN course c ON c.id = r.c_id
        GROUP BY
            c.id,
            c.NAME
        ) t
        LEFT JOIN scores r ON r.c_id = t.id
    AND t.score = r.score
    );
    -- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
select
 student.`name`,
 scores.`score`,
 course.`name`
FROM
 student
 LEFT JOIN scores ON student.id = scores.s_id
 LEFT JOIN course ON course.id = scores.c_id
where
student.id = 1
and
student.name LIKE '张%'
OR student.name 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
 student.id,student.`name`,SUM(scores.score) sum
FROM
 student
 LEFT JOIN scores on student.id = 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 student.id = scores.s_id
 LEFT JOIN course ON course.id = scores.c_id
WHERE
 course.NAME = '数学';
 -- 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.*,c.id,c.name,avg(sc.score)
 from
 teacher t
  left join course c  on  t.id=c.t_id
  left join scores sc on  sc.c_id=c.id
  group by
  t.id,t.name;
  -- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
  select
  t.*,c.name,c.id,max(sc.score),min(sc.score)
  from
  teacher t
  left join course c  on  t.id=c.t_id
  left  join  scores sc  on  sc.c_id=c.id
  group by
  t.id,t.name
  having
  t.name in ('Tom','Jerry');
 -- 14.查询所有学生的课程及分数
 SELECT
    s.id,
    s.NAME,
    c.id,
    c.NAME,
    r.score
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
    LEFT JOIN course c ON c.id = r.c_id;
   -- 13.查询每个学生的最好成绩的科目名称
  SELECT
    t.id,
    t.sname,
    r.c_id,
    c.NAME,
    t.score
FROM
    (
    SELECT
        s.id,
        s.NAME sname,
        max( r.score ) score
    FROM
        student s
        LEFT JOIN scores r ON r.s_id = s.id
    GROUP BY
        s.id,
        s.NAME
    ) t
    LEFT JOIN scores r ON r.s_id = t.id
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = 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
stu.id,stu.name,avg(sc.score)
from
student stu
left join course c on stu.id=c.id
left join scores sc on sc.s_id=sc.c_id
group by
stu.id,stu.name
having
avg(sc.score)>=70;
 -- 17.查询有不及格课程的学生信息
 select
 stu.*,c.name,sc.score
 from
 student  stu
 left  join course c on stu.id=c.id
 left  join scores sc  on  c.id=sc.c_id
 where
 sc.score<60;
-- 18.查询每门课程有成绩的学生人数
 select count(t.id) ,c.name
 from
 ( select
 c.id,c.name
 from
 course c
 left join scores  sc  on  sc.c_id=c.id
 where
 sc.score>0)t
 left join  course c  on  t.id=c.id
 group by
 t.id,t.name;
  -- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
 select
 c.id,c.name,avg(sc.score)
 from
 course c
 left join scores  sc  on  sc.c_id=c.id
 group by
 c.id,c.name
order by
 avg(sc.score) desc,
 c.id asc;
 -- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
 select
stu.id,stu.name,avg(sc.score)
from
student stu
left join course c on stu.id=c.id
left join scores sc on sc.s_id=sc.c_id
group by
stu.id,stu.name
having
avg(sc.score)>60;
-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
select
stu.id,stu.name
from
student stu
left join scores sc on stu.id =sc.s_id
where
sc.score>80
group by
stu.id,stu.name
having
count(*)=1;
-- 22.查询出只有三门课程的学生的学号和姓名
select
stu.id,stu.name
from
student stu
left join scores sc on stu.id =sc.s_id
group by
stu.id,stu.name
having
count(*)=3;
-- 23.查询有不及格课程的课程信息
select
c.id,c.name,sc.score
from
course c
left join scores sc on sc.c_id=c.id
group by
c.id,c.name
having
min(sc.score)<60;
-- 24.查询至少选择4门课程的学生信息
SELECT
    s.id,
    s.NAME
FROM
    student s
    LEFT JOIN scores r ON s.id = r.s_id
GROUP BY
    s.id,
    s.NAME
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 stu.id=c.id
    group by
    stu.id,stu.name
    having
    (select count(id)from course)=5;
   
  -- 27.查询各学生都选了多少门课
  select
  stu.*,count(*)
  from
  student stu
  left join  scores sc on stu.id=sc.s_id
  group by
  stu.id,stu.name;
 
  -- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
  select
  stu.id,stu.name,sc.score
  from
  student stu
  left join course c on stu.id=c.id
  left join scores sc on sc.c_id=c.id
  where
  c.name='java'
  and
  sc.score<60;
 
  -- 29.查询学过"Tony"老师授课的同学的信息
  select
  stu.*
  from
  student stu
  left join scores sc on sc.s_id = stu.id
  left join course c on sc.c_id=c.id
  left join teacher t on c.t_id=t.id
  where
  t.name='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 c.id = r.c_id
        LEFT JOIN teacher t ON t.id = c.t_id
    WHERE
    t.NAME = '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