–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);##练习题和sql语句
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select stu.*, sc1.s_score 01_score, sc2.s_score 02_score
from student stu,
score sc1,
score sc2
where stu.s_id = sc1.s_id
and stu.s_id = sc2.s_id
and sc1.c_id = '01'
and sc2.c_id = '02'
and sc1.s_score > sc2.s_score;
# 也可以用这种
select a.*, b.s_score as 01_score, c.s_score as 02_score
from student a
join score b on a.s_id = b.s_id and b.c_id = '01'
left join score c on a.s_id = c.s_id and c.c_id = '02'
where b.s_score > c.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*, b.s_score 01_score, c.s_score 02_score
from student a
join score b on a.s_id = b.s_id and b.c_id = '01'
left join score c on c.s_id = a.s_id and c.c_id = '02'
where b.s_score < c.s_score;
# 也可以这种
select a.*, b.s_score 01_score, c.s_score c_score
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'
and b.s_score < c.s_score;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id, a.s_name, avg(b.s_score)
from student a
left join score b on a.s_id = b.s_id
group by a.s_id, a.s_name
having avg(b.s_score) > 60;
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
# 这一句只能查出成绩表中有成绩的学生的数据
# select a.s_id, a.s_name, avg(b.s_score)
# from student a
# left join score b on a.s_id = b.s_id
# group by a.s_id
# having avg(b.s_score) < 60;
# 用 union
# UNION运算符用于组合两个或更多SELECT语句的结果集。
# UNION中的每个SELECT语句必须具有相同的列数
#
select a.s_id, a.s_name, avg(b.s_score)
from student a
left join score b on a.s_id = b.s_id
group by a.s_id
having avg(b.s_score) < 60
union
select a.s_id, a.s_name, 0
from student a
where a.s_id not in (select distinct s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id, a.s_name, count(b.s_id) sum_course, sum(b.s_score)
from student a
left join score b on a.s_id = b.s_id
group by a.s_id;
-- 6、查询"李"姓老师的数量
select count(*)
from teacher t
where t.t_name like '李%';
-- 7、查询学过"张三"老师授课的同学的信息
-- 先查出姓名为“张三”的老师的t_id 01
-- 再查出t_id为01的课程c_id
-- 再判断score表 c_id
select a.*
from student a
left join questions.score b on a.s_id = b.s_id
where b.c_id in (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));
-- 用这种好像也可以查出来
select a.*
from student a
left join questions.score b on a.s_id = b.s_id
where b.c_id = (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'));
-- 用这种好像也可以查出来
select a.*
from student a
left join questions.score b on a.s_id = b.s_id
left join questions.course c on b.c_id = c.c_id
left join questions.teacher t on c.t_id = t.t_id
where t_name = '张三';
-- 8、查询没学过"张三"老师授课的同学的信息
-- 先查出姓名为“张三”的老师的t_id 01
-- 再查出t_id为01的课程c_id
-- 再判断score表 c_id
select *
from student c
where c.s_id not in (select a.s_id
from student a
join score b on a.s_id = b.s_id
where b.c_id in (select a.c_id
from course a
join teacher b on a.t_id = b.t_id
where t_name = '张三'));
-- 可以这么写
select c.*
from student c
where c.s_id not in (select a.s_id
from student a
left join questions.score b on a.s_id = b.s_id
where b.c_id =
(select c_id from course where t_id = (select t_id from teacher where t_name = '张三')));
-- 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';
-- 也可以这么写
select a.*
from student a
left join score b on a.s_id = b.s_id
left join score s on a.s_id = s.s_id
where b.c_id = '01' && s.c_id = '02';
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 自己想的逻辑，多此一举了，条件里不需要连接查询，
-- select * from student a, score b where a.s_id = b.s_id and b.c_id = '01' ;
-- select * from student a, score b where a.s_id = b.s_id and b.c_id = '02';
-- select c.* from student c where c.s_id not in (select a.s_id from student a, score b where a.s_id = b.s_id and b.c_id = '02') and c.s_id in (select a.s_id from student a, score b where a.s_id = b.s_id and b.c_id = '01') ;
-- 直接查score表查出满足条件的s_id即可
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 count(*) from course;
-- 根据s_id分组查出课程数量小于全部课程数量的学生
select a.*
from student a
left join score b on a.s_id = b.s_id
group by a.s_id
having count(b.c_id) < (select count(*) from course);
-- 也可以这么写，查出学所有课程学生的s_id，判断不在里面的
select *
from student
where s_id not in (select s_id from score t1 group by s_id having count(*) = (select count(distinct c_id) from course));
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- 查询学号为01的同学信息：select * from student a left join score b on a.s_id = b.s_id where a.s_id = '01';
select distinct c.*
from student c
left join score d on c.s_id = d.s_id
where d.c_id in (select b.c_id
from student a
left join score b on a.s_id = b.s_id
where a.s_id = '01')
and d.s_id != '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'))
and s_id != '01';
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT *
FROM Student
WHERE s_id IN (SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(s_id) = (
#下面的语句是找到'01'同学学习的课程数
SELECT COUNT(c_id)
FROM Score
WHERE s_id = '01'))
AND s_id NOT IN (
#下面的语句是找到学过‘01’同学没学过的课程，有哪些同学。并排除他们
SELECT s_id
FROM Score
WHERE c_id IN (
#下面的语句是找到‘01’同学没学过的课程
SELECT DISTINCT c_id
FROM Score
WHERE c_id NOT IN (
#下面的语句是找出‘01’同学学习的课程
SELECT c_id
FROM Score
WHERE s_id = '01'))
GROUP BY s_id) #下面的条件是排除01同学
AND s_id NOT IN ('01');
-- 也可以这样
SELECT t3.*
FROM (SELECT s_id,
group_concat(c_id ORDER BY c_id) group1
FROM score
WHERE s_id > '01'
GROUP BY s_id) t1
INNER JOIN (SELECT group_concat(c_id ORDER BY c_id) group2
FROM score
WHERE s_id = '01'
GROUP BY s_id) t2 ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.s_id = t3.s_id;
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 先查张三老师的t_id,在course表的c_id
-- select t_id from teacher where t_name = '张三';
-- select c_id from course where t_id = '01';
-- select s_id from score where c_id = '02';
select s_name
from student
where s_id not in (select s_id
from score
where c_id =
(select c_id from course where t_id = (select t_id from teacher where t_name = '张三')));
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 查询有两门或两门以上不及格的s_id: select s_id from score where s_score < 60 GROUP BY s_id having count(*) >= 2;
select a.s_id, a.s_name, AVG(b.s_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 s_score < 60 GROUP BY s_id having count(*) >= 2)
GROUP BY a.s_id;