MySQL练习题

创建表

创建学生表 - id、姓名、生日、性别

CREATE TABLE student(
    id INT(3) PRIMARY KEY auto_increment,
    name VARCHAR(32),
    birth VARCHAR(32),
    sex VARCHAR(32)
) 

创建老师表 - id、姓名

CREATE TABLE teacher(
	id INT(3) PRIMARY KEY auto_increment,
    name VARCHAR(32)
)

创建课程表 - id、课程名、老师id

CREATE TABLE course(
	id INT(3) PRIMARY KEY auto_increment,
    name VARCHAR(32),
    t_id INT(3)
)

成绩表 - id、学生id、课程id、成绩

CREATE TABLE score(
    id INT(3) PRIMARY KEY auto_increment,
    s_id INT(3),
    c_id INT(3),
    score INT(3)
)

插入数据

插入学生数据

insert into student(name,birth,sex) values('aaa' , '1998-01-01' , '男'); 
insert into student(name,birth,sex) values('bbb' , '2001-12-21' , '男'); 
insert into student(name,birth,sex) values('ccc' , '1999-05-20' , '男'); 
insert into student(name,birth,sex) values('ddd' , '1998-08-06' , '男'); 
insert into student(name,birth,sex) values('eee' , '1999-12-01' , '女'); 
insert into student(name,birth,sex) values('fff' , '1998-03-01' , '女'); 
insert into student(name,birth,sex) values('ggg' , '2001-07-01' , '女'); 
insert into student(name,birth,sex) values('hhh' , '2000-01-20' , '女'); 

插入老师数据

insert into teacher(name) values('何翰宇'); 
insert into teacher(name) values('苍井空'); 
insert into teacher(name) values('波多野结衣');
insert into teacher(name) values('波波');
insert into teacher(name) values('波波');

插入课程数据

insert into course(name,t_id) values('语文' , 2); 
insert into course(name,t_id) values('数学' , 1); 
insert into course(name,t_id) values('英语' , 3); 

插入成绩数据

insert into score(s_id,c_id,score) values(1 , 1 , 80);
insert into score(s_id,c_id,score) values(1 , 2 , 90);
insert into score(s_id,c_id,score) values(1 , 3 , 99);
insert into score(s_id,c_id,score) values(2 , 1 , 70);
insert into score(s_id,c_id,score) values(2 , 2 , 60);
insert into score(s_id,c_id,score) values(2 , 3 , 80);
insert into score(s_id,c_id,score) values(3 , 1 , 80);
insert into score(s_id,c_id,score) values(3 , 2 , 80);
insert into score(s_id,c_id,score) values(3 , 3 , 80);
insert into score(s_id,c_id,score) values(4 , 1 , 50);
insert into score(s_id,c_id,score) values(4 , 2 , 30);
insert into score(s_id,c_id,score) values(4 , 3 , 20);
insert into score(s_id,c_id,score) values(5 , 1 , 76);
insert into score(s_id,c_id,score) values(5 , 2 , 87);
insert into score(s_id,c_id,score) values(6 , 1 , 31);
insert into score(s_id,c_id,score) values(6 , 3 , 34);
insert into score(s_id,c_id,score) values(7 , 2 , 89);
insert into score(s_id,c_id,score) values(7 , 3 , 98);

问题1

思路：看要查询那几张表，和中间表

需求：查询课程id为1 比 课程id为2 成绩高的学生的信息及课程分数

SELECT stu.*,sco1.score AS '语文',sco2.score AS '数学' FROM student stu 
	LEFT JOIN score sco1 ON sco1.c_id=1 AND sco1.s_id=stu.id 
	LEFT JOIN score sco2 ON sco2.c_id=2 AND sco2.s_id=stu.id 
	WHERE sco1.score > sco2.score;

student stu LEFT JOIN score sco1 ON sco1.c_id=1 AND sco1.s_id=stu.id：查询学生表和成绩表 成绩表中的课程为1的学生信息

问题2

需求：查询课程id为1 比 课程id为2 成绩低的学生的信息及课程分数

SELECT stu.*,sco1.score AS '语文',sco2.score AS '数学' FROM student stu 
	LEFT JOIN score sco1 ON sco1.c_id=1 AND sco1.s_id=stu.id 
	LEFT JOIN score sco2 ON sco2.c_id=2 AND sco2.s_id=stu.id 
	WHERE sco1.score > sco2.score;

问题3

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

SELECT stu.id,stu.name,ROUND(avg(sco.score),2) FROM student stu 
	LEFT JOIN score sco ON stu.id=sco.s_id 
	GROUP BY stu.id 
	HAVING avg(sco.score)>=60;

问题4

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

判断语句

(CASE

WHEN avg(sco.score) IS NULL THEN 0

ELSE

ROUND(avg(sco.score),2)

END)

SELECT stu.id,stu.name,
	(CASE WHEN avg(sco.score) IS NULL THEN 0 ELSE ROUND(avg(sco.score),2) END) 
	FROM student stu LEFT JOIN score sco ON stu.id=sco.s_id 
	GROUP BY stu.id 
	HAVING avg(sco.score) IS NULL OR avg(sco.score)<60;

问题5

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

SELECT stu.id,stu.name,count(sco.c_id),
	(CASE WHEN sum(sco.score) IS NULL THEN 0 ELSE sum(sco.score) END) 
	FROM student stu LEFT JOIN score sco ON stu.id=sco.s_id 
	GROUP BY stu.id;

问题6

需求：查询"波"姓老师的数量

SELECT t.name,count(t.id) FROM teacher t
	GROUP BY t.name having t.name LIKE "波%"; 

问题7

需求： 查询学过"何翰宇"老师授课的同学的信息

SELECT stu.* FROM student stu 
	LEFT JOIN score sco ON stu.id=sco.s_id
	LEFT JOIN course cou on sco.c_id=cou.id
	LEFT JOIN teacher tea ON cou.t_id=tea.id 
	WHERE tea.name='何翰宇';

问题8

需求：何翰宇老师授课相关信息

	# 何翰宇老师教的课
	SELECT cou.* FROM course cou 
		LEFT JOIN teacher tea ON tea.id=cou.t_id 
		WHERE tea.name="何翰宇";
		
	SELECT * FROM course WHERE t_id = (SELECT id FROM teacher WHERE name='何翰宇');

	#有何翰宇老师课成绩的学生id
	SELECT s_id FROM score WHERE c_id = 
		(SELECT id FROM course WHERE t_id = (SELECT id FROM teacher WHERE name='何翰宇'));
	
	SELECT sco.s_id FROM score sco WHERE sco.c_id IN 
		(SELECT cou.id FROM course cou 
         LEFT JOIN teacher tea ON tea.id=cou.t_id 
         WHERE tea.name="何翰宇");

  
 	# 没学过何翰宇老师授课的同学信息
	SELECT * FROM student WHERE id NOT IN 
		(SELECT s_id FROM score WHERE score.c_id in 
         	(SELECT cou.id FROM course cou 
             	LEFT JOIN teacher tea ON cou.t_id=tea.id 
             		WHERE tea.name='何翰宇'));

问题9

需求： 查询学过编号为1并且也学过编号为2的课程的同学的信息

SELECT stu.* FROM student stu INNER JOIN score sco ON stu.id=sco.s_id AND sco.c_id=1
	WHERE stu.id IN (
		SELECT stu.id FROM student stu 
        INNER JOIN score sco ON stu.id=sco.s_id AND sco.c_id=2
	)

SELECT stu.* FROM student stu
	INNER JOIN score sco ON sco.s_id=stu.id
	GROUP BY stu.id
	HAVING SUM(IF(sco.c_id=1 OR sco.c_id=2 ,1,0))>1

问题10

需求：查询学过编号为1但是没有学过编号为2的课程的同学的信息

SELECT stu.* FROM student stu INNER JOIN score sco ON stu.id=sco.s_id AND sco.c_id=1
	WHERE stu.id NOT IN (
		SELECT stu.id FROM student stu 
        INNER JOIN score sco ON stu.id=sco.s_id AND sco.c_id=2
	)