如下图所示。一共四个表student、score、course、teacher。其中student表中有字段s_id,s_name,s_birth,s_sex；score表中有字段s_id,c_id,s_score；course表中有字段c_id,c_name,t_id；teacher表中有字段t_id,t_name；各表两两相互关联。
DROP DATABASE IF EXISTS my_test50; CREATE DATABASE my_test50;
#建学生表 DROP TABLE IF EXISTS student; CREATE TABLE student( s_id VARCHAR(20) PRIMARY KEY, s_name VARCHAR(20) NOT NULL DEFAULT ‘’, s_birth VARCHAR(20) NOT NULL DEFAULT ‘’, s_sex VARCHAR(10) NOT NULL DEFAULT ‘’ );
#建课程表 DROP TABLE IF EXISTS course; CREATE TABLE course( c_id VARCHAR(20) PRIMARY KEY, c_name VARCHAR(20) NOT NULL DEFAULT ‘’, t_id VARCHAR(20) NOT NULL );
#建教师表 DROP TABLE IF EXISTS teacher; CREATE TABLE teacher( t_id VARCHAR(20) PRIMARY KEY, t_name VARCHAR(20) NOT NULL DEFAULT ‘’
);
#建成绩表 DROP TABLE IF EXISTS score; CREATE TABLE score( s_id VARCHAR(20), c_id VARCHAR(20), s_score INT(3), PRIMARY KEY(s_id,c_id) );
#插入学生表数据 INSERT INTO student VALUES(‘01’,‘赵雷’,‘1990-01-01’,‘男’), (‘02’,‘钱电’,‘1990-12-21’,‘男’), (‘03’,‘孙风’,‘1990-05-20’,‘男’), (‘04’,‘李云’,‘1990-08-06’,‘男’), (‘05’,‘周梅’,‘1991-12-01’,‘女’), (‘06’,‘吴兰’,‘1992-03-01’,‘女’), (‘07’,‘郑竹’,‘1989-07-01’,‘女’), (‘08’,‘王菊’,‘1990-01-20’,‘女’);
#插入课程表数据 INSERT INTO course VALUES(‘01’,‘语文’,‘02’), (‘02’,‘数学’,‘01’), (‘03’,‘英语’,‘03’);
#插入教师表数据 INSERT INTO teacher VALUES(‘01’,‘张三’), (‘02’,‘李四’), (‘03’,‘王五’);
#插入成绩表数据 INSERT INTO score VALUES(‘01’,‘01’,‘80’), (‘01’,‘02’,‘90’), (‘01’,‘03’,‘99’), (‘02’,‘01’,‘70’), (‘02’,‘02’,‘60’), (‘02’,‘03’,‘80’), (‘03’,‘01’,‘80’), (‘03’,‘02’,‘80’), (‘03’,‘03’,‘80’), (‘04’,‘01’,‘50’), (‘04’,‘02’,‘30’), (‘04’,‘03’,‘20’), (‘05’,‘01’,‘76’), (‘05’,‘02’,‘87’), (‘06’,‘01’,‘31’), (‘06’,‘03’,‘34’), (‘07’,‘02’,‘89’), (‘07’,‘03’,‘98’);
1.查询课程编号为01的课程比02的课程成绩高的所有学生的学号（重点）
#step1.在score表中查询课程编号为01的学生编号和分数 SELECT s_id,s_score FROM score WHERE c_id=‘01’;
#step2.在score表中查询课程编号为02的学生编号和分数 SELECT s_id,s_score FROM score WHERE c_id=‘02’;
#step3.将student表和step1与step2得出的表连接 SELECT st.s_id,st.s_name,a.s_score,b.s_score FROM student st LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id=‘01’) a ON st.s_id=a.s_id LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id=‘02’) b ON st.s_id=b.s_id WHERE a.s_score>b.s_score;
2.查询平均成绩大于60分的学生的学号和平均成绩（重点）
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id HAVING AVG(s_score)>60;
3.查询所有学生的学号、姓名、选课数、总成绩（不重要）
SELECT st.s_id,st.s_name,COUNT(sc.c_id),SUM(sc.s_score) FROM student st LEFT JOIN score sc ON st.s_id=sc.s_id GROUP BY st.s_id;
4.查询姓“猴”的老师的个数（不重要）
SELECT COUNT(t_id) FROM teacher t WHERE t_name LIKE ‘猴%’;
5.查询没学过“张三”老师课程的学生的学号、姓名（重点）
SELECT st.s_id,st.s_name FROM student st WHERE st.s_id NOT IN ( SELECT sc.s_id FROM score sc LEFT JOIN course co ON sc.c_id=co.c_id LEFT JOIN teacher te ON co.t_id =te.t_id WHERE te.t_name=‘张三’ );
6.查询学过“张三”老师课程的学生的学号、姓名（重点）
SELECT st.s_id,st.s_name FROM student st WHERE st.s_id IN ( SELECT sc.s_id FROM score sc LEFT JOIN course co ON sc.c_id=co.c_id LEFT JOIN teacher te ON co.t_id =te.t_id WHERE te.t_name=‘张三’ );
7.查询学过编号为’01’课程并且也学过’02’课程的学生的学号、姓名（重点）
#step1.查询学过编号为’01’课程的学生编号 SELECT sc.s_id FROM score sc WHERE sc.c_id=‘01’;
#step2.查询学过编号为’02’课程的学生编号 SELECT sc.s_id FROM score sc WHERE sc.c_id=‘02’;
#step3.查询出step1与step2的交集 SELECT st.s_id, st.s_name FROM student st LEFT JOIN score s ON st.s_id = s.s_id WHERE s.c_id = “01” UNION SELECT st.s_id, st.s_name FROM student st LEFT JOIN score s ON st.s_id = s.s_id WHERE s.c_id = “02”
7.1.查询学过编号为’01’课程但没学过’02’课程的学生的学号、姓名（重点） SELECT st.s_id, st.s_name FROM student st LEFT JOIN score s ON st.s_id = s.s_id WHERE s.c_id = “01” UNION SELECT st.s_id, st.s_name FROM student st WHERE st.s_id NOT IN ( SELECT st.s_id FROM st LEFT JOIN score s ON st.s_id = s.s_id WHERE s.c_id = “02”)
8.查询课程编号为’02’的总成绩（不重点） SELECT SUM(sc.s_score) FROM score sc WHERE sc.c_id=‘02’;
