MySQL数据库综合练习4（1-12题）
一、准备数据
1.class表
#创建表及插入记录
CREATE TABLE class (
cid int(11) NOT NULL AUTO_INCREMENT,
caption varchar(32) NOT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;
INSERT INTO class VALUES
(1, '三年二班'),
(2, '三年三班'),
(3, '一年二班'),
(4, '二年九班');
| cid | caption |
|---|---|
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 二年九班 |
2.student表
CREATE TABLE student(
sid int(11) NOT NULL AUTO_INCREMENT,
gender char(1) NOT NULL,
class_id int(11) NOT NULL,
sname varchar(32) NOT NULL,
PRIMARY KEY (sid),
KEY fk_class (class_id),
CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES
(1, '男', 1, '理解'),
(2, '女', 1, '钢蛋'),
(3, '男', 1, '张三'),
(4, '男', 1, '张一'),
(5, '女', 1, '张二'),
(6, '男', 1, '张四'),
(7, '女', 2, '铁锤'),
(8, '男', 2, '李三'),
(9, '男', 2, '李一'),
(10, '女', 2, '李二'),
(11, '男', 2, '李四'),
(12, '女', 3, '如花'),
(13, '男', 3, '刘三'),
(14, '男', 3, '刘一'),
(15, '女', 3, '刘二'),
(16, '男', 3, '刘四');
| sid | gender | class_id | sname |
|---|---|---|---|
| 1 | 男 | 1 | 理解 |
| 2 | 女 | 1 | 钢蛋 |
| 3 | 男 | 1 | 张三 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
| 6 | 男 | 1 | 张四 |
| 7 | 女 | 2 | 铁锤 |
| 8 | 男 | 2 | 李三 |
| 9 | 男 | 2 | 李一 |
| 10 | 女 | 2 | 李二 |
| 11 | 男 | 2 | 李四 |
| 12 | 女 | 3 | 如花 |
| 13 | 男 | 3 | 刘三 |
| 14 | 男 | 3 | 刘一 |
| 15 | 女 | 3 | 刘二 |
| 16 | 男 | 3 | 刘四 |
3.teacher表
CREATE TABLE teacher(
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(32) NOT NULL,
PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES
(1, '张磊老师'),
(2, '李平老师'),
(3, '刘海燕老师'),
(4, '朱云海老师'),
(5, '李杰老师');
| tid | tname |
|---|---|
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
4.course表
CREATE TABLE course(
cid int(11) NOT NULL AUTO_INCREMENT,
cname varchar(32) NOT NULL,
teacher_id int(11) NOT NULL,
PRIMARY KEY (cid),
KEY fk_course_teacher (teacher_id),
CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO course VALUES
(1, '生物', 1),
(2, '物理', 2),
(3, '体育', 3),
(4, '美术', 2);
| cid | cname | teacher_id |
|---|---|---|
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
5.score表
CREATE TABLE score (
sid int(11) NOT NULL AUTO_INCREMENT,
student_id int(11) NOT NULL,
course_id int(11) NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (sid),
KEY fk_score_student (student_id),
KEY fk_score_course (course_id),
CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);
| sid | student_id | course_id | num |
|---|---|---|---|
| 1 | 1 | 1 | 10 |
| 2 | 1 | 2 | 9 |
| 5 | 1 | 4 | 66 |
| 6 | 2 | 1 | 8 |
| 8 | 2 | 3 | 68 |
| 9 | 2 | 4 | 99 |
| 10 | 3 | 1 | 77 |
| 11 | 3 | 2 | 66 |
| 12 | 3 | 3 | 87 |
| 13 | 3 | 4 | 99 |
| 14 | 4 | 1 | 79 |
| 15 | 4 | 2 | 11 |
| 16 | 4 | 3 | 67 |
| 17 | 4 | 4 | 100 |
| 18 | 5 | 1 | 79 |
| 19 | 5 | 2 | 11 |
| 20 | 5 | 3 | 67 |
| 21 | 5 | 4 | 100 |
| 22 | 6 | 1 | 9 |
| 23 | 6 | 2 | 100 |
| 24 | 6 | 3 | 67 |
| 25 | 6 | 4 | 100 |
| 26 | 7 | 1 | 9 |
| 27 | 7 | 2 | 100 |
| 28 | 7 | 3 | 67 |
| 29 | 7 | 4 | 88 |
| 30 | 8 | 1 | 9 |
| 31 | 8 | 2 | 100 |
| 32 | 8 | 3 | 67 |
| 33 | 8 | 4 | 88 |
| 34 | 9 | 1 | 91 |
| 35 | 9 | 2 | 88 |
| 36 | 9 | 3 | 67 |
| 37 | 9 | 4 | 22 |
| 38 | 10 | 1 | 90 |
| 39 | 10 | 2 | 77 |
| 40 | 10 | 3 | 43 |
| 41 | 10 | 4 | 87 |
| 42 | 11 | 1 | 90 |
| 43 | 11 | 2 | 77 |
| 44 | 11 | 3 | 43 |
| 45 | 11 | 4 | 87 |
| 46 | 12 | 1 | 90 |
| 47 | 12 | 2 | 77 |
| 48 | 12 | 3 | 43 |
| 49 | 12 | 4 | 87 |
| 52 | 13 | 3 | 87 |
二、题目
1.查询所有的课程的名称以及对应的任课老师姓名
课程名称 – course表
老师姓名 – teacher表
通过老师的id连接两张表（course.teacher_id = teacher.tid)查询
-- 查询所有的课程的名称以及对应的任课老师姓名
select c.cname, t.tname from course c
join teacher t on t.tid = c.teacher_id;
2.查询学生表中男女各有多少人
学生性别 – student表
以性别分组查询每个性别的数量
-- 查询学生表中男女各有多少人
select gender, count(gender) from student
group by gender;
3.查询物理成绩等于100的学生的姓名
学生姓名 – student表
成绩分数 – score表
课程名 – course表
连接三张表，查询课程名（course.cname）为“物理”且分数（score.num）为100的学生姓名（student.sname）
-- 查询物理成绩等于100的学生的姓名
select s.sname from student s,
join score sc on sc.student_id = s.sid
join course c on c.cid = sc.course_id
where sc.num = 100
and c.cname = '物理';
4.查询平均成绩大于八十分的同学的姓名和平均成绩
学生姓名 – student表
成绩分数 – score表
每个同学的平均成绩 – 按照学生分组（group by student.sid），求成绩的平均值（avg(score.num)）
平均成绩大于八十分 – 聚合函数的结果筛选使用having（having avg(score.num) > 80）
-- 查询平均成绩大于八十分的同学的姓名和平均成绩
select s.sname， avg(sc.num) avg_num from student s
join score sc on sc.student_id = s.sid
group by s.sid
having avg_num > 80;
5.查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
学号，姓名 – student表
选课，成绩 – score表
“对于那些没有选任何课程的学生也算在内” – 没有选课的学生在score表中没有对应的选课和成绩
所以使用left join – student为主表，score为从表
-- 查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
select s.sid, s.sname, count(sc.course_id) course_num, sum(sc.num) score_num from student s
left join score sc on sc.student_id = s.sid
group by s.sid;
6.查询姓李老师的个数
使用聚合函数count统计teacher表中姓李的老师（tname like ‘李%’）的个数
-- 查询姓李老师的个数
select count(tname) teacher_num from teacher
where tname like '李%';
7.查询没有报李平老师课的学生姓名
a. 首先找到李平老师有哪些课
b. 再查询选了李平老师课的学生
c. 最后查询没有选李平老师课的学生（not in）
老师姓名 – teacher表
课程 – course表
学生选课 – score表
学生姓名 – student表
-- 首先找到李平老师有哪些课
select course.cid from course
join teacher on teacher.tid = course.teacher_id
where teacher.tname = '李平老师';
-- 再查询选了李平老师课的学生
select score.student_id from score
where score.course_id in (
select course.cid from course
join teacher on teacher.tid = course.teacher_id
where teacher.tname = '李平老师'
);
-- 查询没有报李平老师课的学生姓名
select s.sname from student s
where s.sid not in (
select score.student_id from score
where score.course_id in (
select course.cid from course
join teacher on teacher.tid = course.teacher_id
where teacher.tname = '李平老师'
)
);
8.查询物理课程比生物课程高的学生的学号
查询物理成绩 – 得到物理成绩表
查询生物成绩 – 得到生物成绩表
两表联查物理成绩大于生物成绩的学生学号
-- 查询物理成绩
select student_id, num from score
join course on course.cid = score.course_id
where course.cname = '物理';
-- 查询生物成绩
select student_id, num from score
join course on course.cid = score.course_id
where course.cname = '生物';
-- 查询物理课程比生物课程高的学生的学号
select sc1.student_id from (
select student_id, num from score
join course on course.cid = score.course_id
where course.cname = '物理'
) sc1 join (
select student_id, num from score
join course on course.cid = score.course_id
where course.cname = '生物'
) sc2 on sc1.student_id = sc2.student_id
where sc1.num > sc2.num;
9.查询没有同时选修物理课程和体育课程的学生姓名
选修课程 – score表
课程名称 – course表
学生姓名 – student表
“没有同时选修物理课程和体育课程” – 只选了其中一门
查询出选修了物理或者体育的表，找出只选了其中一门课的学生（使用聚合函数count统计选课数等于1的学生），学号 --> 姓名
-- 查询选修了物理或者体育的学生
select score.student_id from score
join course on course.cid = score.course_id
where course.cname = '物理'
or course.cname = '体育'
group by score.student_id
having count(score.course_id) = 1;
-- 查询没有同时选修物理课程和体育课程的学生姓名
select s.sname from student s
join (
select score.student_id from score
join course on course.cid = score.course_id
where course.cname = '物理'
or course.cname = '体育'
group by score.student_id
having count(score.course_id) = 1
) sc on sc.student_id = s.sid;
10.查询挂科超过两门（包括两门）的学生姓名和班级
成绩 – score表
班级 – class表
姓名 – student表
按照学生分组（group by score.student_id），查询每个学生低于六十分（score.num < 60）的成绩的个数（使用聚合函数count），筛选出大于等于2（having count(score.course_id) >= 2）
-- 查询挂科超过两门（包括两门）的学生姓名和班级
select s.sname, cl.caption from student s
join class cl
join score sc
on cl.cid = s.class_id
and sc.student_id = s.sid
where sc.num < 60
group by sc.student_id
having count(sc.course_id) >= 2;
--
select s.sname, cl.caption from student s
join class cl on cl.cid = s.class_id
join score sc on sc.student_id = s.sid
where sc.num < 60
group by sc.student_id
having count(sc.course_id) >= 2;
11.查询选修了所有课程的学生姓名
课程 – course表
选课数 – score表
学生姓名 – student表
查询每个学生选修课程的数量（按照学生分组使用聚合函数count），再和总的课程数比较，相同即为选修了所有课程
-- 查询每个学生选修课程的数量
select s.sname, count(sc.course_id) course_num from student s
join score sc on sc.student_id = s.sid
group by s.sid;
-- 总课程数
select count(*) from course;
-- 查询选修了所有课程的学生姓名
select s.sname from student s
join score sc on sc.student_id = s.sid
group by s.sid
having count(sc.course_id) = (
select count(*) from course
);
12.查询李平老师教的课程的所有成绩记录
老师 – teacher表
课程 – course表
成绩 – score表
通过课程id连接score表和course表（course.cid = score.course_id）
再通过老师id连接course表和teacher表（teacher_tid = course.teacher_id）
-- 查询李平老师教的课程的所有成绩记录
select sc.* from score sc
join course c on c.cid = sc.course_id
join teacher t on t.tid = c.teacher_id
where t.tname = '李平老师';
扫一扫
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
