MySQL练习

–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

--建表

--学生表

--建表

--学生表

CREATE TABLE `Student`( `s_id` VARCHAR(20), `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`) );

--课程表

CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) );

--教师表

CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) );

--成绩表

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' , '男'); 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' , '女');

--课程表测试数据

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

--教师表测试数据

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

--成绩表测试数据

insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);

表数据：

学生表Student

s_id	s_name	s_birth	s_sex
01	赵雷	1990-01-01	男
02	钱电	1990-12-21	男
03	孙凤	1990-05-20	男
04	李云	1990-08-06	男
05	周梅	1991-12-12	女
06	吴兰	2017-12-13	女
07	郑竹	1989-07-01	女
08	王菊	1990-01-20	女
09	赵雷	1990-01-21	女
10	赵雷	1990-01-22	男

 分数表Score：

s_id	c_id	s_score
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	03	87
06	01	31
06	03	34
07	03	89
07	01	98

课程表Course： 

c_id	c_name	t_id
01	语文	02
02	数学	01
03	英语	03

 

教师表Teacher：

t_id	t_name
01	张三
02	李四
03	王五

练习：*

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

select st.*,sc.s_score as '语文' ,sc2.s_score '数学' from student st left join score sc on sc.s_id=st.s_id and sc.c_id='01' left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02' where sc.s_score>sc2.s_score

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select st.*,sc.s_score '语文',sc2.s_score '数学' from student st left join score sc on sc.s_id=st.s_id and sc.c_id='01' left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02' where sc.s_score<sc2.s_score

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

select st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore from student st left join score sc on sc.s_id=st.s_id group by st.s_id having AVG(sc.s_score)>=60

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

select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st left join score sc on sc.s_id=st.s_id group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

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

select st.s_id,st.s_name,count(c.c_id),( case when SUM(sc.s_score) is null or sum(sc.s_score)="" then 0 else SUM(sc.s_score) end) from student st left join score sc on sc.s_id =st.s_id left join course c on c.c_id=sc.c_id group by st.s_id

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

select t.t_name,count(t.t_id) from teacher t group by t.t_id having t.t_name like "李%";

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

select st.* from student st left join score sc on sc.s_id=st.s_id left join course c on c.c_id=sc.c_id left join teacher t on t.t_id=c.t_id where t.t_name="张三"

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

select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三" -- 有张三老师课成绩的st.s_id select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三") -- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息 select st.* from student st where st.s_id not in( select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name="张三") )

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

select st.* from student st inner join score sc on sc.s_id = st.s_id inner join course c on c.c_id=sc.c_id and c.c_id="01" where st.s_id in ( select st2.s_id from student st2 inner join score sc2 on sc2.s_id = st2.s_id inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02" ) 网友提供的思路(厉害呦~): SELECT st.* FROM student st INNER JOIN score sc ON sc.`s_id`=st.`s_id` GROUP BY st.`s_id` HAVING SUM(IF(sc.`c_id`="01" OR sc.`c_id`="02" ,1,0))>1

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

select st.* from student st inner join score sc on sc.s_id = st.s_id inner join course c on c.c_id=sc.c_id and c.c_id="01" where st.s_id not in ( select st2.s_id from student st2 inner join score sc2 on sc2.s_id = st2.s_id inner join course c2 on c2.c_id=sc2.c_id and c2.c_id="02" )

-- 11、查询没有学全所有课程的同学的信息 -- 太复杂,下次换一种思路,看有没有简单点方法 -- 此处思路为查学全所有课程的学生id,再内联取反面

select * from student where s_id not in ( select st.s_id from student st inner join score sc on sc.s_id = st.s_id and sc.c_id="01" where st.s_id in ( select st2.s_id from student st2 inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="02" ) and st.s_id in ( select st2.s_id from student st2 inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id="03" )) -- 来自一楼网友的思路，左连接，根据学生id分组过滤掉 数量小于 课程表中总课程数量的结果(show me his code),简洁不少。 select st.* from Student st left join Score S on st.s_id = S.s_id group by st.s_id having count(c_id)<(select count(c_id) from Course)

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

select distinct st.* from student st left join score sc on sc.s_id=st.s_id where sc.c_id in ( select sc2.c_id from student st2 left join score sc2 on sc2.s_id=st2.s_id where st2.s_id ='01' )