MySQL50题-练习

SQL面试50题
注意： 在本地编辑的时候用的是Typora编辑器，但CSDN竟然不支持里面的表格语句。所以，大家可以到github上面看。两边代码一样，但github上的结果看起来更舒服些。
看了一些SQL面试的题目，这里做个记录。本文代码为MySql。

这个练习题目里面共有四张表：

• Student 学生表

  字段名 字段含义 字段类型
  sid 学生编号 varchar(10)
  sname 学生姓名 varchar(10)
  sage 学生年龄 datetime
  ssex 学生性别 varchar(10)

  Student表数据

  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’ , ‘女’);

• Courese 课程表

  字段名 字段含义 字段类型
  cid 课程编号 varchar(10)
  cname 课程名字 varchar(10)
  tid 教师姓名 varchar(10)

  Course表数据

  insert into Course values(‘01’ , ‘语文’ , ‘02’);
  insert into Course values(‘02’ , ‘数学’ , ‘01’);
  insert into Course values(‘03’ , ‘英语’ , ‘03’);

• Teacher 教师表

  字段名 字段含义 字段类型
  tid 教师编号 varchar(10)
  tname 教师姓名 varchar(10)

  Teacher表数据

  insert into Teacher values(‘01’ , ‘张三’);
  insert into Teacher values(‘02’ , ‘李四’);
  insert into Teacher values(‘03’ , ‘王五’);

• SC 成绩表

  字段名 字段含义 字段类型
  sid 学生编号 varchar(10)
  cid 课程编号 varchar(10)
  score 学生成绩 decimal(18, 1)

  SC表数据

  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 DISTINCT
	SC1.sid 
FROM
	( SELECT sid, score FROM SC WHERE cid = '01' ) AS SC1
	JOIN ( SELECT sid, score FROM SC WHERE cid = '02' ) AS SC2 ON SC1.sid = SC2.sid 
WHERE
	SC1.score > SC2.score

结果：

sid
2
4

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

思路： 平均成绩，需要GROUP BY；大于60，需要having（where在group by之前执行）

代码：

SELECT
	sid,
	AVG( score ) AS avg_grade 
FROM
	SC 
GROUP BY
	sid 
HAVING
	avg_grade >= 60

结果：

sid avg_grade
1 89.66667
2 70.00000
3 80.00000
5 81.50000
7 93.50000

3. 查询所有同学的学号、姓名、选课数、总成绩

思路： 需要连接Student和SC两张表；选课数和总成绩需要聚合函数；Student表中有全部学生ID，使用左连接

代码

SELECT
	stu.sid,
	stu.sname,
	COUNT( DISTINCT cid ) AS course_total,
	sum( score ) AS total_sum 
FROM
	student AS stu
	LEFT JOIN SC ON stu.sid = SC.sid 
GROUP BY
	stu.sid,
	stu.sname

结果：

sid sname course_total total_sum
1 赵雷 3 269.0
2 钱电 3 210.0
3 孙风 3 240.0
4 李云 3 100.0
5 周梅 2 163.0
6 吴兰 2 65.0
7 郑竹 2 187.0
8 王菊 0 null

4. 查询姓“李”的老师的个数

思路： 个数使用count(distincnt)；姓使用Like搜索

代码：

SELECT COUNT(DISTINCT Tid) AS sum_li
FROM teacher
WHERE tname LIKE '李%'

结果：

sum_li
1

5. 查询没学过“张三”老师课的同学的学号、姓名

思路： 根据Teacher表查询Tid，根据Tid在courese表中查询cid，在SC表中查询sid，在student表中查sname

代码：

SELECT
	sid,
	sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		sid 
	FROM
		sc 
WHERE
	sc.cid IN ( SELECT cid FROM course JOIN teacher ON course.tid = teacher.tid WHERE teacher.tname = '张三' ))

结果：

sid sname
6 吴兰
8 王菊

6. 查询学过“01”并且也学过编号“02”课程的同学的学号、姓名

思路： 连接cid=