【Mysql学习之旅-2】经典sql面试题及答案分析

比眉伴天荒 2023-10-06 11:41 40阅读 0赞

## 前言 ##

在学习了Mysql的基础知识后，我们用一套sql练习题来实战一下。

关于练习所需要的数据，让我们继续玩坏倚天屠龙的江湖。  
![在这里插入图片描述][20200621101824968.png]

1、学生表 `student`（s\_id：学生id，s\_name：学生姓名，s\_birth：学生生日，s\_sex：学生性别）：  
![在这里插入图片描述][20200619204826382.png]  
2、教师表teacher（t\_id：教师id，t\_name：教师姓名)  
![在这里插入图片描述][20200619204949405.png]  
3、课程表 `course`（c\_id：课程id，c\_name：课程名称，t\_id：教师id）：  
![在这里插入图片描述][20200619204847788.png]  
4、成绩表 score（s\_id：学生id，c\_id：课程id，score：分数）  
![在这里插入图片描述][20200619202517448.png]  
初始化数据的sql附在文章末尾

题目开始，先上几道硬菜，磕到牙齿的可以移步进阶题目和基础题目区压压惊。

## 挑战 ##

**1、查询各科成绩前三名的记录**

--方法1：
    SELECT 
    	s_name,a.s_id,a.c_id,a.score 
    FROM 
    	score a 
    LEFT JOIN 
    	score b 
    ON 
    	a.c_id = b.c_id AND a.score<b.score
    JOIN 
    	student
    ON
    	a.s_id=student.s_id
    GROUP BY 
    	a.s_id,a.c_id,a.score HAVING COUNT(b.s_id)<3
    ORDER BY 
    	a.c_id,a.score DESC
    
    --方法2：
    SELECT 
    	a.s_id,a.c_id,a.score 
    FROM 
    	score a
    WHERE 
    	(SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id 
    	AND b.score>=a.score)<=2 ORDER BY a.c_id

> 简评：非常难以理解的一道题目，即使想透彻了也不容易记住，堪称劝退型难题。面试中，偶尔会考察。  
> 难度：⭐⭐⭐⭐⭐  
> 易考率：⭐⭐  
> 思路：  
> 1、SELECT \* FROM score a JOIN score b ON a.c\_id=b.c\_id WHERE a.c\_id=‘03’ 这一句，形成了03课程的所有学员的两两组合（带上03，是为了简化数据，便于分析）  
> ![在这里插入图片描述][20200620203115186.png]  
> 2、SELECT \* FROM score a JOIN score b ON a.c\_id=b.c\_id WHERE a.c\_id=‘03’ AND a.score<b.score又筛选出每个学员03课程比其他人低的情况（某个学员分越低，记录越多，没记录说明是最高分）  
> ![在这里插入图片描述][20200620203236714.png]  
> 3、将这些记录按s\_id分组，筛选出记录数小于3的（说明最多有两个人比自己分数高），即是前3名

**2、查询“01”课程比“02”课程成绩高的所有学生的学号**

SELECT 
    	*
    FROM 
    	(SELECT s_id,score,c_id FROM score WHERE c_id='01') a
    JOIN
    	(SELECT s_id,score,c_id FROM score WHERE c_id='02') b
    ON 
    	a.s_id=b.s_id 
    WHERE 
    	a.score>b.score

> 简评：不容易想到，但答案相对第1题来说，就好理解多了。  
> 难度：⭐⭐⭐⭐  
> 频率：⭐⭐  
> 思路：通过SELECT s\_id,score,c\_id FROM score WHERE c\_id='01’和SELECT s\_id,score,c\_id FROM score WHERE c\_id='02’的两个临时表联查，得到每个学员的01、02课程成绩情况：  
> ![在这里插入图片描述][20200620204612652.png]  
> 接下来，a.score>b.score条件直接筛选就可以了。

**3、查询和"04"号的同学学习的课程完全相同的其他同学的信息**

SELECT 
    	score.s_id,COUNT(DISTINCT c_id) 
    FROM 
    	student JOIN score ON student.s_id=score.s_id 
    GROUP BY 
    	score.s_id 
    HAVING COUNT(DISTINCT c_id)= 
    	(SELECT COUNT(c_id) FROM score WHERE s_id='04') 
    AND 
    	s_id 
    NOT IN
    	-- 筛选出学过（04号同学未学课程）的学生，通过not in排除掉
    	(SELECT s_id FROM score WHERE c_id IN
    	(SELECT DISTINCT(c_id) FROM course WHERE c_id 
    	NOT IN
    	(SELECT c_id FROM score WHERE s_id='04'))) 
    AND score.s_id!='04'

> 简评：难在思路的迂回性。面试中，很多有难度的sql也大多难在直接去考虑，难以获得答案。  
> 难度：⭐⭐⭐⭐  
> 频率：⭐⭐  
> 思路：通常思路，我们先获取04号同学的课程，再遍历其他同学，以此筛选。这种方式适用于编程，单纯的sql实现不了，我们应该从下面这个思路去考虑------>我和04号同学学习的课程数目一样多，且04号同学没学过的我也没学过，那么我不就是和04号同学学习的课程一样吗？

**4、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩**

SELECT 
    	a.s_id,b.s_name,
    	MAX(CASE a.c_id WHEN '01' THEN a.score END ) 内功, 
    	MAX(CASE a.c_id WHEN '02' THEN a.score END ) 剑法, 
    	MAX(CASE a.c_id WHEN '03' THEN a.score END ) 拳法, 
    	AVG(a.score) 
    FROM 
    	score a 
    JOIN 
    	student b ON a.s_id=b.s_id 
    GROUP BY a.s_id ORDER BY AVG(a.score) DESC

> 简评：思路不难，关键在于理解透彻case when语法。  
> 难度：⭐⭐⭐  
> 频率：⭐⭐

SELECT a.c_id,b.c_name,MAX(score),MIN(score),ROUND(AVG(score),2),
    	ROUND(100*(SUM(case when a.score>=60 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 及格率,
    	ROUND(100*(SUM(case when a.score>=70 and a.score<=80 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 中等率,
    	ROUND(100*(SUM(case when a.score>=80 and a.score<=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优良率,
    	ROUND(100*(SUM(case when a.score>=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优秀率
    FROM 
    	score a 
    LEFT JOIN 
    	course b 
    ON a.c_id = b.c_id GROUP BY a.c_id,b.c_name

> 简评：在case when语法的基础上，进一步掌握sql语句中进行逻辑运算的技巧  
> 难度：★★★☆  
> 频率：★★★

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

SELECT a.s_id,a.c_id,
    	@i:=@i + 1 AS 排名,
        @j:=(CASE WHEN @score=a.score THEN @j ELSE @i END) AS 并排排名,
        @score:=a.score AS score
    FROM 
    	(SELECT s_id,c_id,score FROM score GROUP BY s_id,c_id ORDER BY score DESC) a,
    	(SELECT @i:= 0,@j:= 0,@score:= 0) s

> 简评：Mysql中没有rank函数，只能使用伪列的概念去实现排名算法。  
> 难度：★★★★  
> 频率：★★  
> 思路：  
> 1、SELECT s\_id,c\_id,score FROM score GROUP BY s\_id,c\_id ORDER BY score DESC先进行了排序  
> 2、(SELECT @i:=0,@j:=0,@score:=0) s对@i、@j、@score进行了初始化，初始值都为0  
> 3、@i:=@i + 1，遍历每一行，@i逐行自增  
> 4、@j:=(CASE WHEN @score=a.score THEN @j ELSE @i END)，遍历每一行，逻辑判断，@score与上一行的score相等则@j=@i，否则则自增一次

**6、查询学生的总成绩并进行排名**

SELECT a.s_id,
    	@i:=@i+1 as i,
    	@j:=(CASE WHEN @score=a.sum_score THEN @j ELSE @i END) AS rank,
    	@score:=a.sum_score AS score
    FROM 
    	(SELECT s_id,SUM(score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a,
    	(SELECT @i:=0,@j:=0,@score:=0) s

> 简评：与上一题基本差不多，不同的是a这个临时表的数据内容而已。  
> 难度：★★★★  
> 频率：★★

**7、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩**

SELECT 
    	student.*,c.rank,c.score,c.c_id 
    FROM 
    	(SELECT a.s_id,a.score,a.c_id,@i:=@i+1 as rank from score a,(SELECT @i:=0)s 
    	WHERE a.c_id='01' ORDER BY a.score DESC) c
    LEFT JOIN 
    	student ON c.s_id=student.s_id
    WHERE rank BETWEEN 2 AND 3
    UNION ALL 
    SELECT 
    	student.*,c.rank,c.score,c.c_id 
    FROM 
    	(SELECT a.s_id,a.score,a.c_id,@j:=@j+1 as rank from score a,(SELECT @j:=0)s 
    	WHERE a.c_id='02' ORDER BY a.score DESC) c
    LEFT JOIN 
    	student ON c.s_id=student.s_id
    WHERE 
    	rank BETWEEN 2 AND 3
    UNION ALL 
    SELECT 
    	student.*,c.rank,c.score,c.c_id 
    FROM 
    	(SELECT a.s_id,a.score,a.c_id,@k:=@k+1 as rank from score a,(SELECT @k:=0)s 
    	WHERE a.c_id='03'  ORDER BY a.score DESC) c
    LEFT JOIN 
    	student ON c.s_id=student.s_id
    WHERE rank BETWEEN 2 AND 3

> 简评：sql很长，不要被吓到，其实是3个相同的部分（where条件不同）通过UNION ALL合并而已。另外，筛选2、3名也是在排名的基础上进一步筛选。  
> 难度：★★★★☆  
> 频率：★★  
> 思路：  
> 1、先将01课程的所有人的分数排序，再进一步根据`rank BETWEEN 2 AND 3`筛选出2、3名  
> 2、使用同样方法，筛选02、03课程数据，使用 `UNION ALL`合并查询结果

**8、统计各科成绩各分数段人数：课程编号、课程名称、\[80-100\]，\[60-80\]，\[0-60\]及所占百分比**

SELECT 
    	DISTINCT e.c_name,a.c_id,b.`80-100`,b.百分比,c.`60-80`,c.百分比,d.`0-60`,d.百分比 
    FROM 
    	score a
    LEFT JOIN 
    	(SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS `80-100`,
    	ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 end)/count(*)),2)
    	AS 百分比 FROM score GROUP BY c_id)b ON a.c_id=b.c_id
    LEFT JOIN 
    	(SELECT c_id,SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 END) AS `60-80`,
    	ROUND(100*(SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 end)/count(*)),2) 
    	AS 百分比 FROM score GROUP BY c_id)c ON a.c_id=c.c_id
    LEFT JOIN 
    	(SELECT c_id,SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END) AS `0-60`,
    	ROUND(100*(SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END)/count(*)),2) 
    	AS 百分比 FROM score GROUP BY c_id)d ON a.c_id=d.c_id
    LEFT JOIN 
    	course e ON a.c_id = e.c_id

> 简评：`CASE WHEN`和sql语句内进行逻辑运算的又一次应用。  
> 难度：★★★★  
> 频率：★★  
> 思路：`SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS`80-100`, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END)/count(*)),2) AS 百分比 FROM score GROUP BY c_id`得到所有课程80-100分成绩的统计临时表：  
> ![在这里插入图片描述][20200621113553841.png]  
> 然后再用同样的方式，得到0-60、60-80的统计临时表，三表联查。

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

SELECT a.s_id,
    	@i:=@i+1 as '不保留空缺排名',
    	@k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名',
    	@avg_score:=avg_s AS '平均分'
    FROM 
    	(SELECT s_id,ROUND(AVG(score),2) AS avg_s FROM 
    	score GROUP BY s_id ORDER BY avg_s DESC)a,
    	(SELECT @avg_score:=0,@i:=0,@k:=0)b;

> 简评：与第5题类似，没什么特别需要说的。  
> 难度：★★★★  
> 频率：★★

**10、查询各学生的年龄**

SELECT 
    	s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 
    	(CASE WHEN DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') THEN 0 ELSE 1 END)) 
    	AS age
    FROM student;

> 简评：本身的实现思路并不难，`DATE_FORMAT`掌握其用法即可。

**11、分别查询本周、下周、本月、下月过生日的学生**

--本周
    SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)
    --下周
    SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = WEEK(s_birth)
    --本月
    SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)
    --下月
    SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)

## 进阶 ##

**1、查询及格学生（每门课程的分数>=60）的学生姓名**

SELECT s_name FROM 
    	student 
    WHERE s_id NOT IN
    	(SELECT DISTINCT(s_id) FROM score WHERE score<60)

或者：

SELECT s_name FROM 
    	student a JOIN score b ON a.s_id=b.s_id 
    GROUP BY b.s_id HAVING MIN(score)>60

**2、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)**

SELECT 
    	s_name,ROUND(avg(score),2) AS avg 
    FROM 
    	student 
    JOIN 
    	score ON student.s_id=score.s_id 
    GROUP BY 
    	score.s_id HAVING avg<60 
    UNION ALL 
    SELECT 
    	s_name,0 AS avg 
    FROM 
    	student 
    WHERE s_id NOT IN(SELECT DISTINCT(s_id) FROM score)
    
    -- 方法2
    SELECT 
    	s_name,IFNULL(ROUND(avg(score),2),0) AS avg 
    FROM 
    	student 
    LEFT JOIN 
    	score ON student.s_id=score.s_id 
    GROUP BY score.s_id HAVING avg<60 OR avg is NULL

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

SELECT 
    	* 
    FROM 
    	score JOIN student ON score.s_id=student.s_id 
    WHERE c_id IN
    	(SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰')

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

SELECT 
    	* 
    FROM 
    	student 
    WHERE s_id NOT IN
    	(SELECT s_id FROM score WHERE c_id 
    IN
        (SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰'))

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

SELECT 
    	s_name,COUNT(c_id) AS count 
    FROM 
    	student 
    LEFT JOIN 
    	score ON student.s_id=score.s_id 
    GROUP BY 
    	student.s_id HAVING count<(SELECT COUNT(1) FROM course);

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

SELECT * FROM 
    	student 
    WHERE s_id IN
    	(SELECT DISTINCT a.s_id FROM score a WHERE a.c_id 
    	IN
    	(SELECT a.c_id FROM score a WHERE a.s_id='01'));

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

SELECT 
    	a.s_id,a.s_name,ROUND(AVG(b.score)) 
    FROM  
    	student a 
    LEFT JOIN 
    	score b ON a.s_id = b.s_id
    WHERE a.s_id IN
    	(SELECT s_id from score WHERE score<60 GROUP BY s_id HAVING count(1)>=2)
    GROUP BY a.s_id,a.s_name

**8、查询所有学生的课程及分数情况；**

SELECT 
    	a.s_id,a.s_name,
    	SUM(CASE c.c_name WHEN '内功' THEN b.score ELSE 0 END) AS '内功',
    	SUM(CASE c.c_name WHEN '剑法' THEN b.score ELSE 0 END) AS '剑法',
    	SUM(CASE c.c_name WHEN '拳法' THEN b.score ELSE 0 END) AS '拳法',
    	SUM(b.score) as  '总分'
    FROM 
    	student a 
    LEFT JOIN 
    	score b ON a.s_id = b.s_id 
    LEFT JOIN 
    	course c ON b.c_id = c.c_id 
    GROUP BY a.s_id,a.s_name

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

SELECT 
    	s_id,MAX(score) 
    FROM 
    	score 
    WHERE 
    	c_id 
    IN
    	(SELECT c_id FROM teacher JOIN course 
    	ON teacher.t_id=course.t_id WHERE t_name='张三丰')

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

SELECT * FROM score a,score b where a.c_id != b.c_id and a.score = b.score

**11、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列**

SELECT 
    	c_id,COUNT(*) AS total 
    FROM 
    	score 
    GROUP BY c_id HAVING total>4 ORDER BY total DESC,c_id ASC

**12、查询选修了全部课程的学生信息**

SELECT 
    	* FROM student WHERE s_id 
    IN
    	(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course))

## 基础 ##

**1、查询不及格的课程**

SELECT 
    	a.s_id,a.c_id,b.c_name,a.score 
    FROM 
    	score a 
    LEFT JOIN 
    	course b ON a.c_id = b.c_id
    WHERE a.score<60

**2、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名**

SELECT 
    	a.s_id,b.s_name 
    FROM 
    	score a 
    LEFT JOIN 
    	student b ON a.s_id = b.s_id
    WHERE a.c_id = '01'	AND a.score>80

**3、查询每个同学的姓名、选课数、总成绩**

SELECT 
    	s_name,COUNT(c_id),SUM(score) 
    FROM 
    	student a 
    LEFT JOIN 
    	score b ON a.s_id=b.s_id 
    GROUP BY b.s_id

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

SELECT 
    	s_name,ROUND(avg(score),2) AS avg 
    FROM 
    	student 
    JOIN 
    	score ON student.s_id=score.s_id 
    GROUP BY score.s_id HAVING avg>60

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

SELECT student.s_name,COUNT(c_id),IFNULL(SUM(score),0) AS sum FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id ORDER BY sum DESC

**6、查询"张"姓老师的数量**

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '张%'

**7、查询"01"课程分数小于60，按分数降序排列的学生信息**

SELECT 
    	a.*,b.c_id,b.score
    FROM 
    	student a,score b
    WHERE 
    	a.s_id=b.s_id 
    AND
    	b.c_id='01' AND b.score<60
    ORDER BY
    	b.score DESC;

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

SELECT 
    	a.s_name,b.c_name,c.score 
    FROM 
    	course b 
    LEFT JOIN 
    	score c ON b.c_id = c.c_id
    LEFT JOIN 
    	student a ON a.s_id=c.s_id 
    WHERE c.score>=70

**9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息**

SELECT a.* FROM 
    	student a,score b,score c 
    WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id='01' AND c.c_id='02';

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

SELECT a.* FROM 
    	student a 
    WHERE 
    	a.s_id 
    IN 
    	(SELECT s_id FROM score WHERE c_id='01' ) 
    AND 
    	a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')

**11、查询男生、女生人数**

SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex

**12、查询名字中含有"圆"字的学生信息**

SELECT * FROM student WHERE s_name LIKE '%圆%';

**13、查询同名同性学生名单，并统计同名人数**

SELECT 
    	a.s_name,a.s_sex,count(*) 
    FROM 
    	student a JOIN student b 
    ON 
    	a.s_id !=b.s_id and a.s_name = b.s_name 
    AND 
    	a.s_sex = b.s_sex
    GROUP BY a.s_name,a.s_sex

**14、查询1990年出生的学生名单**

SELECT s_name FROM student WHERE s_birth LIKE '1337%'

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

SELECT 
    	c_id,ROUND(AVG(score),2) AS avg_score 
    FROM 
    	score 
    GROUP BY c_id ORDER BY avg_score DESC,c_id ASC

**16、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩**

SELECT 
    	a.s_id,b.s_name,ROUND(avg(a.score),2) AS avg 
    FROM 
    	score a
    LEFT JOIN 
    	student b on a.s_id=b.s_id 
    GROUP BY s_id HAVING avg>=85

**17、查询课程名称为"内功"，且分数低于60的学生姓名和分数**

SELECT 
    	a.s_name,b.score 
    FROM 
    	score b 
    JOIN 
    	student a ON a.s_id=b.s_id 
    WHERE 
    	b.c_id=(SELECT c_id FROM course WHERE c_name ='内功') 
    AND b.score<60

**18、求每门课程的学生人数**

SELECT c_id,count(*) FROM score GROUP BY c_id;

**19、检索至少选修两门课程的学生学号**

SELECT s_id,count(*) AS count FROM score GROUP BY s_id HAVING count>=2

## 数据初始化 ##

-- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `s_id` varchar(20) NOT NULL,
      `s_name` varchar(20) NOT NULL DEFAULT '',
      `s_birth` varchar(20) NOT NULL DEFAULT '',
      `s_sex` varchar(10) NOT NULL DEFAULT '',
      PRIMARY KEY (`s_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('01', '张无忌', '1337-06-17', '男');
    INSERT INTO `student` VALUES ('02', '宋青书', '1333-12-21', '男');
    INSERT INTO `student` VALUES ('03', '丁敏君', '1336-06-21', '女');
    INSERT INTO `student` VALUES ('04', '周芷若', '1340-09-26', '女');
    INSERT INTO `student` VALUES ('05', '圆真', '1320-12-01', '男');
    INSERT INTO `student` VALUES ('06', '圆觉', '1316-03-01', '男');
    INSERT INTO `student` VALUES ('07', '殷梨亭', '1310-07-01', '男');
    INSERT INTO `student` VALUES ('08', '纪晓芙', '1310-01-20', '女');
    
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course` (
      `c_id` varchar(20) NOT NULL,
      `c_name` varchar(20) NOT NULL DEFAULT '',
      `t_id` varchar(20) NOT NULL,
      PRIMARY KEY (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES ('01', '内功', '03');
    INSERT INTO `course` VALUES ('02', '剑法', '02');
    INSERT INTO `course` VALUES ('03', '拳法', '01');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher` (
      `t_id` varchar(20) NOT NULL,
      `t_name` varchar(20) NOT NULL DEFAULT '',
      PRIMARY KEY (`t_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES ('01', '张三丰');
    INSERT INTO `teacher` VALUES ('02', '灭绝师太');
    INSERT INTO `teacher` VALUES ('03', '空见');
    
    -- ----------------------------
    -- Table structure for score
    -- ----------------------------
    DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score` (
      `s_id` varchar(20) NOT NULL,
      `c_id` varchar(20) NOT NULL,
      `score` int(3) DEFAULT NULL,
      PRIMARY KEY (`s_id`,`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of score
    -- ----------------------------
    INSERT INTO `score` VALUES ('01', '01', '99');
    INSERT INTO `score` VALUES ('01', '02', '96');
    INSERT INTO `score` VALUES ('01', '03', '90');
    INSERT INTO `score` VALUES ('02', '01', '59');
    INSERT INTO `score` VALUES ('02', '02', '75');
    INSERT INTO `score` VALUES ('02', '03', '68');
    INSERT INTO `score` VALUES ('03', '01', '55');
    INSERT INTO `score` VALUES ('03', '02', '50');
    INSERT INTO `score` VALUES ('03', '03', '60');
    INSERT INTO `score` VALUES ('04', '01', '77');
    INSERT INTO `score` VALUES ('04', '02', '88');
    INSERT INTO `score` VALUES ('05', '01', '88');
    INSERT INTO `score` VALUES ('05', '02', '90');
    INSERT INTO `score` VALUES ('06', '01', '86');
    INSERT INTO `score` VALUES ('06', '03', '99');
    INSERT INTO `score` VALUES ('07', '02', '58');
    INSERT INTO `score` VALUES ('07', '03', '98');

[20200621101824968.png]: https://img-blog.csdnimg.cn/20200621101824968.png
[20200619204826382.png]: https://img-blog.csdnimg.cn/20200619204826382.png
[20200619204949405.png]: https://img-blog.csdnimg.cn/20200619204949405.png
[20200619204847788.png]: https://img-blog.csdnimg.cn/20200619204847788.png
[20200619202517448.png]: https://img-blog.csdnimg.cn/20200619202517448.png
[20200620203115186.png]: https://img-blog.csdnimg.cn/20200620203115186.png
[20200620203236714.png]: https://img-blog.csdnimg.cn/20200620203236714.png
[20200620204612652.png]: https://img-blog.csdnimg.cn/20200620204612652.png
[20200621113553841.png]: https://img-blog.csdnimg.cn/20200621113553841.png