Mysql面试热身题集总结

╰半橙微兮° 2022-01-20 12:19 220阅读 0赞

### 文章目录 ###

*  前言
 *   *  1、热身题实践
     *  其他

# 前言 #

一直有个想法：把面试需要的知识点全都总结一下，包括数据库，语言，算法，数据结构等知识，形成一个面试总结笔记，这样以后面试的时候只看这些文章回顾下就行了。今天就先总结下Mysql的面试热身题吧，后续会总结其他方面的点。当然，文章同样会不定时更新。

## 1、热身题实践 ##

说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。
    问题及描述：
    --1.学生表
    Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
    --2.课程表 
    Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
    --3.教师表 
    Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
    --4.成绩表 
    SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
    */
    --创建测试数据
    create table student(sno varchar(10),sname varchar(10),sage datetime,ssex varchar(10));
    insert into student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into student values('02' , '钱电' , '1990-12-21' , '男');
    insert into student values('03' , '孙风' , '1990-05-20' , '男');
    insert into student values('04' , '李云' , '1990-08-06' , '男');
    insert into student values('05' , '周梅' , '1991-12-01' , '女');
    insert into student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into student values('08' , '王菊' , '1990-01-20' , '女');
    
    create table course(cno varchar(10),cname varchar(10),tno varchar(10));
    insert into course values('01' , '语文' , '02');
    insert into course values('02' , '数学' , '01');
    insert into course values('03' , '英语' , '03');
    
    create table teacher(tno varchar(10),tname varchar(10));
    insert into teacher values('01' , '张三');
    insert into teacher values('02' , '李四');
    insert into teacher values('03' , '王五');
    
    create table sc(sno varchar(10),cno varchar(10),score int(4));
    insert into sc values('01' , '01' , 80);
    insert into sc values('01' , '02' , 90);
    insert into sc values('01' , '03' , 99);
    insert into sc values('02' , '01' , 70);
    insert into sc values('02' , '02' , 60);
    insert into sc values('02' , '03' , 80);
    insert into sc values('03' , '01' , 80);
    insert into sc values('03' , '02' , 80);
    insert into sc values('03' , '03' , 80);
    insert into sc values('04' , '01' , 50);
    insert into sc values('04' , '02' , 30);
    insert into sc values('04' , '03' , 20);
    insert into sc values('05' , '01' , 76);
    insert into sc values('05' , '02' , 87);
    insert into sc values('06' , '01' , 31);
    insert into sc values('06' , '03' , 34);
    insert into sc values('07' , '02' , 89);
    insert into sc values('07' , '03' , 98);

**1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数**

方法一：连表查询
     SELECT * 
     FROM student s
     LEFT JOIN sc on s.sno=sc.sno
     LEFT JOIN sc sc1 on s.sno=sc1.sno
     WHERE sc.cno='01'
     and sc1.cno='02'
     and sc.score>sc1.score
     方法二：形成子表然后再连表查询
     select a.S# from 
     (select s#,score from SC where C#='001') a,
     (select s#,score from SC where C#='002') b 
     where a.score>b.score 
     and a.s#=b.s#;(注意子查询的使用场景)
     方式三：
     注意：限制条件添加join条件中对左表没有约束！左表没有被限制条件限制！
     SELECT * 
     FROM student s
     LEFT join sc on s.Sno=sc.Sno and sc.cno='01'
     LEFT join sc sc1 on s.Sno=sc1.Sno and sc1.cno='02'
     WHERE sc.score>sc1.score

2、查询同时存在"01"课程和"02"课程的情况

SELECT * 
    FROM student s
    LEFT join sc on s.Sno=sc.Sno and sc.cno='01'
    LEFT join sc sc1 on s.Sno=sc1.Sno and sc1.cno='02'
    WHERE sc.score is not NULL and sc1.score is not null

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.sno,s.sname,avg(sc.score)
    FROM student s 
    LEFT join sc on s.sno=sc.sno
    GROUP BY s.sno
    HAVING avg(sc.score)>=60
    ORDER BY avg(sc.score) desc

4、查询在sc表存在成绩的学生信息的SQL语句。

SELECT * FROM Student s 
    LEFT JOIN SC on s.Sno=SC.Sno
    GROUP BY s.Sno
    HAVING count(score)>0

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.Sno , Sname, count(Cno),sum(score)
    from Student s left join SC on s.Sno=SC.Sno
    GROUP BY s.Sno

6、查询"李"姓老师的数量

SELECT count(*) FROM Teacher t WHERE t.Tname LIKE '李%'

7、查询学过"张三"老师授课的同学的信息

方法一：连表查询
    SELECT *
    FROM student s 
    LEFT join sc on s.sno=sc.sno
    left join course c on c.cno=sc.cno
    LEFT join teacher t on c.tno=t.tno
    WHERE t.tname="张三"
    方法二：利用子查询
    SELECT sname,sno
    FROM student 
    WHERE sno in
    (SELECT distinct sno
    FROM sc 
    WHERE cno in(
    SELECT c.cno
    FROM course c 
    LEFT JOIN teacher t on c.tno=t.tno
    WHERE t.tname='张三'))

**8、查询没学过"张三"老师授课的同学的信息**

#注意子查询的使用场景
    SELECT * FROM Student WHERE Sno not in 
    (SELECT s.Sno FROM 
    Student s LEFT join SC on s.Sno=SC.Sno
    LEFT join Course c on SC.Cno=c.Cno
    LEFT join Teacher t on c.Tno=t.Tno
    WHERE t.Tname = '张三')

**9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息**

方法一：连表查询
       SELECT DISTINCT s.sno,s.sname
       FROM student s 
       LEFT JOIN sc on s.sno = sc.sno 
       LEFT JOIN sc s1 on s1.sno=s.sno
       WHERE sc.cno='01' 
       and s1.cno!='02'
       方法二： #注意子查询的灵活使用
        SELECT s.*
        FROM student s
        LEFT join sc on s.sno=sc.sno
        WHERE sc.cno='01'
        and s.sno in
        (SELECT DISTINCT s1.sno 
        FROM student s1
        LEFT join sc sc1 on s1.sno=sc1.sno
        WHERE sc1.cno!="02"
        )

**10、查询没有学全所有课程的同学的信息**

SELECT s.* 
    FROM Student s 
    LEFT join SC on s.Sno=SC.Sno 
    GROUP BY s.Sno
    HAVING count(Cno)<
    (select count(C#) from SC)
    #注意group by之后是对相应的属性做限制，之后将满足限制的所有数据筛选出来(分成多少组有多少数据)

11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT student.sno,sname
    FROM student 
    LEFT JOIN sc on student.sno=sc.sno
    WHERE sc.cno in 
    (SELECT cno 
    FROM sc
    WHERE sno='01')
    and sc.sno!='01'

**12、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩**

#注意不能在where子句中限制组函数如：where  max(score)>60
    SELECT sc.sno,sname,avg(score)
    FROM student s  
    LEFT JOIN sc on s.sno=sc.sno
    where s.sno in (
    SELECT sc.sno
    FROM student s  
    LEFT JOIN sc on s.sno=sc.sno
    WHERE sc.score<60
    GROUP BY sc.sno
    HAVING count(*)>=2)

group by sno  
13、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT *
    FROM student s LEFT JOIN sc
    on s.sno=sc.sno
    WHERE sc.cno='01' 
    and sc.score<60
    ORDER BY score desc

14、查询每门课程被选修的学生数

SELECT cno,count(sno)
     FROM sc
     GROUP BY cno

15、查询出只有两门课程的全部学生的学号和姓名

SELECT s.sno,sname
    FROM student s LEFT JOIN sc
    on s.sno=sc.sno
    GROUP BY s.sno
    HAVING count(*)=2

16、查询1990年出生的学生名单

SELECT s.* 
    FROM Student s
    WHERE s.Sage BETWEEN'1990-01-01' and '1990-12-31'

17、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT cno,avg(score)
    FROM sc
    GROUP BY cno
    ORDER BY avg(score) desc,cno asc

**18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数。**

SELECT s.sname,c.cname,score
     FROM student s LEFT join sc
     on s.sno=sc.sno
     LEFT JOIN course c 
     on sc.cno=c.cno
     GROUP BY s.sno
     HAVING min(score)>=70

**19、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩**

SELECT s1.*,score
    FROM student s1 LEFT JOIN sc
    on s1.sno=sc.sno
    WHERE sc.cno in(
    SELECT c.cno
    FROM teacher t LEFT JOIN course c
    on t.tno=c.tno
    WHERE t.tname="张三")
    ORDER BY score desc
    LIMIT 1

20、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT sc.cno,sc.score,sc.sno
    	 FROM sc LEFT join sc sc1
    	 on sc.sno=sc1.sno
    	 WHERE sc.cno!=sc1.cno
    	 and sc.score=sc1.score

**21、查询各科成绩最好的前两/三名**\#重点注意

方法一：连表查询（重点参考）
    SELECT *
    FROM sc a
    LEFT join sc b on a.cno=b.cno and a.score<b.score#筛选我的成绩比别人的成绩差的数据
    GROUP BY a.cno,a.sno
    HAVING count(1)<2#如果我的成绩比别人的成绩差的数据条数小于2，也就代表有0或1个人的成绩比我好，则对应的用户就被筛选出来了
    直观的意义是取分组后的TOPN,实际操作时候，只能是从每组里面取第一条数据，只是利用多重分组来将满足条件的TopN条数据给筛选出来而已！
    
    #方法二：使用子查询
    SELECT s1.* 
    FROM SC s1 
    WHERE
    ( SELECT COUNT(1) 
    FROM SC s2 
    WHERE s1.Cno=s2.Cno
     AND s2.score>=s1.score
    )<=2
    ORDER BY s1.Cno,s1.score DESC
    分析下这个sql：
    select * from test1 a where 2 > (select count(*) from test1 where course=a.course and score>a.score)
    相关子查询的特点就是子查询依赖与外部查询，在这里面其实是 select * from test 已经先执行了一遍了，查出了所有的数据
    然后相关子查询针对每一行数据进行select count(*) from test1 where course=a.course and score>a.score
    例如：
    　　第一行是张三，数学77，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99
    　　然后where条件score>77，查询出王五，99，count=1，这时候外部条件2>1,符合。
    　　第二行是李四，数学68，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99
    　　然后where条件score>68，查询出张三，77,王五，99，count=2，这时候外部条件2>2,不符合。
    　　第三行是王五，数学99，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99
    　　然后where条件score>99，没有数据，这时候外部条件2>0,符合。
    　　那么就筛选出了数学最大的2个人，张三和王五。
    其实这里的子查询就是找出和当前行类型能匹配上的比他大的有多少，没有比他大的他就是最大
    那么找top1就是 1>(xxx)，topN就是N>(xxxxx)

**\#以下几个题目要了解下子查询在select字段时的原理**  
**22、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率  
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90**

SELECT sc.cno as "课程ID",c.cname as "课程name",
    max(sc.score) as "最高分",min(sc.score) as "最低分",avg(sc.score) as "平均分",
    CAST(((select count(1) from sc s WHERE s.cno=sc.cno and score>=60)*100/(select count(1) from sc s WHERE s.cno=sc.cno)) as DECIMAL(18,2)) as "及格率",
    cast((select count(1) from sc where cno = c.cno and score >= 60)*100.0 / (select count(1) from sc where cno = c.cno) as decimal(18,2)) as "[及格率(%)]"
    FROM sc 
    LEFT JOIN course c  on sc.cno=c.cno
    GROUP BY sc.cno
    #这里的s指的就是外表s按照sql的执行顺序，从from到where->group by->having->select时
    对应的表的形态。即整个查询没有select子查询作为字段时对应的输出。

**23、按各科成绩进行排序，并显示排名**

--Score重复时合并名次（利用select字段里的子查询，利用两张表的关联之后通过score的大小的个数来生成序列）
    select t.* , (select count(distinct score) 
    from sc 
    where cno = t.cno 
    and score >= t.score) as px 
    from sc t 
    order by t.cno, px
    #这里的s指的就是外表s按照sql的执行顺序，从from到where->group by->having->select时对应的表的形态（这里即
    Select *
    FROM sc s
    ORDER BY s.cno,s.score desc
    的输出结果！）
    之后在select子查询中利用连表进行连接

**24、查询学生平均成绩及其名次**

SELECT a.*,(SELECT count(1) FROM 
    (select s.*,avg(sc.score) as avg2
    FROM student s
    left JOIN sc on sc.sno=s.sno
    WHERE avg2>=a.avg1
    GROUP BY s.sno
    ORDER BY avg(sc.score) desc) b
    )
    FROM 
    (select s.*,avg(sc.score) as avg1
    FROM student s
    left JOIN sc on sc.sno=s.sno
    GROUP BY s.sno
    ORDER BY avg(sc.score) desc) a

**\# 实例说明原理：**  
查询学生成绩及其名次

select t.*
    FROM sc t
    GROUP BY t.cno
    ORDER BY cno,score desc

**主查询的输出结果**  
![在这里插入图片描述][20190707175207781.jpg]

select t.*,
    (SELECT count(DISTINCT score) 
    FROM sc 
    WHERE cno=t.cno 
    and score>=t.score)
    FROM sc t
    GROUP BY t.cno
    ORDER BY cno,score desc

**最终输出结果**  
![在这里插入图片描述][20190707175253846.jpg]  
**解释：这里在子查询的t就是主查询的输出结果（如上图所示），然后拿t和sc做连接查询再加上score>=t.score的限制条件，得到最终结果（即sc中对应的各科的成绩要大于t.score）**

**25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列**

#注意cast函数的应用
    select cno,(CAST(avg(score) as DECIMAL(18,2))) as avg_score
    FROM sc
    GROUP BY cno
    ORDER BY avg_score desc,cno desc

**26、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩**

SELECT sno	
    FROM sc
    WHERE cno='01'
    ORDER BY score desc
    LIMIT 1
    （查询topn的另一种方法，通过orderby排序后利用limit来实现）

**27、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩**

#注意下几种join的链接结果
    SELECT *
    FROM sc a
    join sc b
    WHERE a.cno!=b.cno
    and a.score=b.score
    and a.sno!=b.sno

**总结：**  
**内连接是最常见的一种连接，只连接匹配的行**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70]  
**LEFT JOIN返回左表的全部行和右表满足ON条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 1]  
**FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 2]

## 其他 ##

累加求和套路（求截止当前月份的累计消费额）  
练习数据：  
建表语句：

create table  test
    (
           user              VARCHAR(3),
           month             DATE,
           salary            NUMERIC(6)
    );
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-03 15:20:52', 33);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-05 15:20:52', 23);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-01-08 15:20:52', 66);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-02-08 15:20:52', 55);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('A', '2015-02-02 15:20:52', 35);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-02-02 15:20:52', 62);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-02-12 15:20:52', 52);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-01-12 15:20:52', 52);
    INSERT INTO `test`(`user`, `month`, `salary`) VALUES ('B', '2015-01-16 15:20:52', 23);

数据展示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 3]

1、求每个用户每月的消费总和：

SELECT user,date_format(month, '%Y-%m'),sum(salary) 
    from test 
    GROUP BY user,date_format(month, '%Y-%m')

![在这里插入图片描述][20190610222840950.jpg]  
2、将月总金额表 自己连接 自己连接

SELECT * FROM 
    (SELECT user,date_format(month, '%Y-%m'),sum(salary) 
    from test 
    GROUP BY user,date_format(month, '%Y-%m')) a 
    join 
    (SELECT user,date_format(month, '%Y-%m'),sum(salary) 
    from test 
    GROUP BY user,date_format(month, '%Y-%m')) b 
    on a.user=b.user

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 4]

3、从上一步的结果中  
进行分组查询，分组的字段是a.username a.month  
求月累计值： 将b.month <= a.month的所有b.salary求和即可  
最终求用户A和B每月累计消费总和：

SELECT a.user,a.dt,max(a.money),sum(b.money) FROM 
    (SELECT user,date_format(month, '%Y-%m') dt,sum(salary) money 
    from test 
    GROUP BY user,date_format(month, '%Y-%m')) a 
    join 
    (SELECT user,date_format(month, '%Y-%m') dt,sum(salary) money 
    from test 
    GROUP BY user,date_format(month, '%Y-%m')) b 
    on a.user=b.user
    WHERE a.dt>=b.dt
    GROUP BY a.user,a.dt

![在这里插入图片描述][20190610223047543.jpg]

[20190707175207781.jpg]: /images/20220120/70c7c845fa584270be0657d86c95e56e.png
[20190707175253846.jpg]: /images/20220120/1022d3381f56472cbe63949fa0755dd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70]: /images/20220120/15ea86ec876b418581d586560db8085b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 1]: /images/20220120/f64b18532033457f9d692580a899153c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 2]: /images/20220120/5869341744d64337a7a07a34c405b5ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 3]: /images/20220120/76b7f7a8d014470fa9fcb9858950e595.png
[20190610222840950.jpg]: /images/20220120/46678a00a91e401b8cf12e0b51595b8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2NjMzNDA1_size_16_color_FFFFFF_t_70 4]: /images/20220120/ffbda87229d74de8a19e64e6c613afbe.png
[20190610223047543.jpg]: /images/20220120/347321980f094038bc50f7272ed1cbc1.png