Mysql面试50题

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

CREATE TABLE `t_student` (
  `sid` bigint(22) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '' ,
   PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into t_student
values
('0001','张三'), 
('0002','李四'),
('0003','王五'),
('0004','钱六');

CREATE TABLE `t_score` (
  `sid` bigint(22) NOT NULL DEFAULT '0',
  `cid` bigint(22) NOT NULL DEFAULT '0',
  `score` bigint(22) NOT NULL DEFAULT '0',
   PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into t_score
values
('0001' , '1' , 80),
('0001' , '2' , 90),
('0001' , '3' , 99),
('0002' , '2' , 60),
('0002' , '3' , 80),
('0003' , '1' , 80),
('0003' , '2' , 80),
('0003' , '3' , 80);


CREATE TABLE `t_course` (
  `cid` bigint(22) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '' ,
  `tid` bigint(22) NOT NULL DEFAULT '0',
   PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into t_course
values
('1' , '语文' , '100'),
('2' , '数学' , '101'),
('3' , '英语' , '102');

CREATE TABLE `t_teacher` (
  `tid` bigint(22) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '' ,
   PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


insert into t_teacher
values
('100' , '小小'),
('101' , '大大'),
('102' , '中中');

1. 查询课程编号为‘1’的课程比课程编号为‘2’的课程成绩低的所有学生的学号

解题思路：

学生号码

课程1成绩

课程2成绩

-- 从成绩表t_socre中，分别查询选修课程1/2的信息
select sid,cid,score from t_score where cid=1;
select sid,cid,score from t_score where cid=2;

-- 两个子查询的结果进行inner join
select A.sid, A.score "课程1", B.score "课程2" from (
  (select sid,cid,score from t_score where cid=1) as A -- 课程1
  inner join
  (select sid,cid,score from t_score where cid=2) as B -- 课程2
  on A.sid=B.sid  -- 连接条件
) where A.score<B.score

join使用场景

1. 既可以连接“多个子查询的结果”        

(select xxx from 表名) as A
inner join 
(select xxx from 表名) as B
on A.xxx=B.xxx

2. 又可以连接“多个表”

表1 inner join 表2
on 表1.xxx=表2.xxx

2. 查询平均成绩大于60分的学生的学号和平均成绩

-- 题目要求
select sid,avg(score) as avg_score 
from t_score 
group by sid 
having avg_score>60;

-- 扩展：若还想输出学生信息 （采用Join）
方法1. 先过滤，再join得到所有信息
select A.sid,name,avg_score from 
    (select sid,avg(score) as avg_score
     from t_score
     group by sid 
     having avg_score>60
    ) as A 
  inner join
    (select sid,name from t_student) as B 
  on A.sid=B.sid; 

select A.sid,name,avg_score from 
    -- 查询结果
    (select sid,avg(score) as avg_score 
     from t_score
     group by sid 
     having avg_score>60
    ) as A 
  inner join 
    -- 表
    t_student as B 
  on A.sid=B.sid; 


方法2. 先join得到所有的信息，在过滤
select A.sid,name,avg(score) from 
    -- 查询结果
        (select sid,score from t_score) as A 
    inner join 
    -- 表
        t_student as B 
    on A.sid=B.sid 
group by sid,name having avg(score)>60;

3. 查询所有学生的学号、姓名、选课数、总成绩（重点）

1. left join：左连接

        1. 左表中出现的元素一定出现在结果集中

        2. 右表中出现的元素，有可能不出现在结果集中

        2. 左表中出现，右表中未出现的元素，右表的结果用NULL填充

2. 关于NULL的函数

        1. isnull(exper) 判断exper是否为空，是则返回1，否则返回0

        2. ifnull(exper1, exper2) 判断exper1是否为空，是则用exper2代替

        3. nullif(exper1, exper2) 如果expr1= expr2 成立，那么返回值为NULL，否则返回值为  expr1。

-- 查询学生ID，选课数，总分数（在t_score一张表中就可以获取到）
select sid,count(cid),sum(score) from t_score group by sid;
-- 如何查询姓名呢？（要在t_student表中查询）
1. 先join查询出所有需要的值
select * from 
    -- 两张表join
    t_student as st left join t_score as sc on sc.sid=st.sid;
2. 再查询输出结果：此处使用left join
select st.sid,name,count(cid),sum(score) from 
    t_student as st left join t_score as sc on sc.sid=st.sid
group by st.sid;

3. 优化：因为select中包含name，所以在group-by中加入name
select st.sid,name,count(cid),IFNULL(sum(score),0) from      
    t_student as st left join t_score as sc on sc.sid=st.sid 
group by st.sid,name;

group by的select语句，select的属性列包含什么呢？

① 在group by出现

②使用聚合函数

4. 查询姓‘张’的老师的个数

select count(t_id) from t_teacher where name like '小%';

扩展：查询姓‘张’的老师的名字个数

-- 名字可能重复，用distinct去重
select count(distinct name) from t_teacher where name like '小%';

5. 查询没学过张三老师课的学生的学号和姓名（重点）

-- 方法1：子查询
select * from t_student where sid not in  -- 取反：未选修cid的学生
    (select sid from t_score where cid in ( -- 成绩表：查询选修cid的所有的sid
        select cid from t_course where tid in ( -- 课程表：查询tid带的所有的课程cid
            select tid from t_teacher where name='小小') -- 教师表：根据name查tid
        )
    );

-- 方法2：join
sid, 学生name, cid, sid, 老师name

1. 子查询join起来
select sid from 
    t_score as sc 
    inner join t_course as co on sc.cid=co.cid
    inner join t_teacher as tc on tc.tid=co.tid
    where tc.name="小小"

2. 查询没有选修“小小”老师教的学生
select sid,name from t_student where sid not in (
    select sid from 
        t_score as sc 
        inner join t_course as co on sc.cid=co.cid
        inner join t_teacher as tc on tc.tid=co.tid
        where tc.name="小小"
)

7. 查询学过‘1’课程并且学过‘2’课程的学生的学号和姓名（重点）

题意：学生既学过‘1’课程，又学过‘2’课程

select A.sid,name from 
(select sid from t_score where cid=1) as A
inner join 
(select sid from t_score where cid=2) as B
on A.sid=B.sid
inner join t_student as C on A.sid=C.sid;

select sid,name from t_student
where sid in (
    select A.sid from 
    (select sid from t_score where cid=1) as A
    inner join 
    (select sid from t_score where cid=2) as B
    on A.sid=B.sid
)

8. 查询课程编号为‘2’的总成绩

select sum(score) from t_score where cid=2;

扩展：查询课程编号未‘1’，‘2’，‘3’的总成绩

select cid,sum(score) from t_score group by cid having cid in (1,2,3);

9. 查询所有课程成绩小于60分的学号和姓名

问题转化为：查询学生的所有课程乘积的最大值 && 最大值<60分

select sid,max(score) from t_score group by sid having max(score)<60;

10. 查询没有学全所有课的学生的学号和姓名（重点）

问题转换

        1. 查询所有课程数的总数

        2. 查询每个学生的学科数

        3. join：拼接学生信息

select A.sid,name from 
    ( 
      select sid,count(distinct cid) from t_score  -- 查询:学生,学科数
      group by sid having count(distinct cid) < 
        (select count(distinct cid) from t_score)  -- 查询所有课程数目
    ) as A 
    inner join t_student as B on A.sid=B.sid;

11. 查询至少有一门课与学号为'0001'的学生所学课程相同的学号和姓名

select distinct(sid) from t_score where cid in (
    select cid from t_score where sid='0001'
) and sid!='0001' 

12. 查询和‘0001’号同学所学课程完全相同的其他同学的学号

15. 查询2门及其以上不及格课程的同学的学号、姓名、平均成绩（重点）

-- 学号
select sid,avg(score) from t_score where sid in (
    select sid from t_score where score<60 group by sid having count(sid)>=2
) group by sid;

-- 拼接学生信息
select A.sid,name,avg_score from 
(
    select sid,avg(score) as avg_score from t_score where sid in (
        select sid from t_score where score<60 
        group by sid having count(sid)>=2
    ) group by sid
) as A
inner join t_student as B on A.sid=B.sid;

16. 查询课程编号为‘1’的分数小于60，按分数降序排列的学生信息

select sc.sid,name,score from 
t_score as sc inner join t_student as st 
on sc.sid=st.sid 
where score>60 and cid='1' 
order by score DESC;

17. 按平均成绩从高到低，显示所有学生的所有课程的成绩以及平均成绩（重点）

select A.sid,B.cid,B.score,avg_score from 
  ( -- 查询每个学生的平均分
    select sid,avg(score) as avg_score from t_score 
    group by sid
  ) as A 
  inner join t_score as B on A.sid=B.sid
  order by avg_score DESC;

-- 输出结果：这样输出结果不是很友好，一个主播每个课程都对应一个行（怎么实现行转列）
+-----+-----+-------+-----------+
| sid | cid | score | avg_score |
+-----+-----+-------+-----------+
|   1 |   2 |    90 |   89.6667 |
|   1 |   3 |    99 |   89.6667 |
|   1 |   1 |    80 |   89.6667 |
|   3 |   1 |    80 |   80.0000 |
|   3 |   2 |    80 |   80.0000 |
|   3 |   3 |    80 |   80.0000 |
|   2 |   2 |    60 |   70.0000 |
|   2 |   3 |    80 |   70.0000 |
+-----+-----+-------+-----------+

在同一行输出结果：一个主播，平均值，[课程1的分数, 课程2的分数, 课程3的分数, ... ...]

select st.sid, 
    avg(score) "平均成绩",
    MAX(IF(sc.cid=1,sc.score,NULL)) as"语文",  -- 采用MAX()函数，适配group-by
    MAX(IF(sc.cid=2,sc.score,NULL)) as"数学",
    MAX(IF(sc.cid=3,sc.score,NULL)) as"英语"
from 
    t_student as st 
    left join t_score as sc on st.sid=sc.sid  -- left join，有的学生没选课，输出为NULL
group by st.sid
order by avg(score) DESC;

-- 输出结果
+-----+--------------+--------+--------+--------+
| sid | 平均成绩      | 语文    | 数学    | 英语   |
+-----+--------------+--------+--------+--------+
|   1 |      89.6667 |     80 |     90 |     99 |
|   3 |      80.0000 |     80 |     80 |     80 |
|   2 |      70.0000 |   NULL |     60 |     80 |
|   4 |         NULL |   NULL |   NULL |   NULL |
+-----+--------------+--------+--------+--------+

18. 查询各科成绩最高分、最低分、平均分，以如下格式输出：课程id、课程名、最高分、最低分、平均分、及格率、中等率、优秀率（重点）

IF(expr, 值1, 值2)  --- 三目表达式: expr ? 值1 : 值2

select
    co.cid,co.name,max(score),min(score),avg(score),
    sum(IF(score>=60,1,0)) / count(sid) "及格率",
    sum(IF(score>=60 and score<85,1,0)) / count(sid) "中等率",
    sum(IF(score>=85,1,0)) / count(sid) "优秀率"
from
t_course as co left join t_score as sc on co.cid=sc.cid
group by co.cid,co.name;

-- 输出
+-----+--------+------------+------------+------------+-----------+-----------+-----------+
| cid | name   | max(score) | min(score) | avg(score) | 及格率    | 中等率    | 优秀率    |
+-----+--------+------------+------------+------------+-----------+-----------+-----------+
|   1 | 语文   |         80 |         80 |    80.0000 |    1.0000 |    1.0000 |    0.0000 |
|   2 | 数学   |         90 |         60 |    76.6667 |    1.0000 |    0.6667 |    0.3333 |
|   3 | 英语   |         99 |         80 |    86.3333 |    1.0000 |    0.6667 |    0.3333 |
|   4 | 历史   |       NULL |       NULL |       NULL |      NULL |      NULL |      NULL |
+-----+--------+------------+------------+------------+-----------+-----------+-----------+

题目[19,22,24]: 窗口函数的应用

窗口函数	排序情况
row_number()	1,2,3,4
rank()	1,2,2,3
dense_rank()	1,2,2,4

19. 按照各科成绩排序，并显示排名（重点）

22. 查询所有课程的成绩第2名到第3名的学生信息及改课程成绩

24. 查询学生平均成绩以及名次

26. 查询每门课程被选修的学生数

select co.cid,co.name,count(sid) from
t_course as co left join t_score as sc on co.cid=sc.cid 
group by co.cid,co.name;

27. 查询只选修了2门课程的全部学生的学号和姓名

-- 1. 查询每个学生选修的课程数
select sid,count(cid) as cnt_cid from t_score 
group by sid having cnt_cid=2

-- 2. 拼接学生信息
select sc.sid,name,count(cid) as cnt_cid from t_score as sc 
inner join t_student as st on sc.sid=st.sid
group by sid having cnt_cid=2

31. 查询1990年出生的学生名单

select * from t_student
where YEAR(birth)=1990;

时间处理函数

select YEAR('2022-09-20');  -- 年
+--------------------+
| YEAR('2022-09-20') |
+--------------------+
|               2022 |
+--------------------+

select MONTH('2022-09-20'); -- 月
+---------------------+
| MONTH('2022-09-20') |
+---------------------+
|                   9 |
+---------------------+

select CURDATE(); -- 当前时间
+------------+
| CURDATE()  |
+------------+
| 2022-03-21 |
+------------+

select NOW();     -- 当前时间
+---------------------+
| NOW()               |
+---------------------+
| 2022-03-21 18:11:58 |
+---------------------+

select CURTIME(); 
+-----------+
| CURTIME() |
+-----------+
| 18:13:10  |
+-----------+

select UNIX_TIMESTAMP('2022-03-21 18:11:58');
+---------------------------------------+
| UNIX_TIMESTAMP('2022-03-21 18:11:58') |
+---------------------------------------+
|                            1647857518 |
+---------------------------------------+

select FROM_UNIXTIME(1647857518, '%Y-%m-%d %H:%i:%s');
+------------------------------------------------+
| from_unixtime(1647857518, '%Y-%m-%d %H:%i:%s') |
+------------------------------------------------+
| 2022-03-21 18:11:58                            |
+------------------------------------------------+

32. 查询平均成绩大于等于85的所有学生的学号、姓名、平均成绩

select sc.sid,name,avg(score) 
from t_score as sc inner join t_student as st
on sc.sid=st.sid   
group by sc.sid 
having avg(score)>85;

33. 查询每门课的平均成绩，按照平均成绩升序排序，成绩相同的再按照cid降序排序

select co.cid, IFNULL(avg(score) ,0) as avg_score from  
t_course as co left join t_score as sc on co.cid=sc.cid    
group by cid ASC, cid DESC;

34. 查询课程名称为"数学",且分数低于60的学生姓名和分数