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, '二年九班');
cidcaption
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, '刘四');
sidgenderclass_idsname
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, '李杰老师');
tidtname
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);
cidcnameteacher_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);
sidstudent_idcourse_idnum
11110
2129
51466
6218
82368
92499
103177
113266
123387
133499
144179
154211
164367
1744100
185179
195211
205367
2154100
22619
2362100
246367
2564100
26719
2772100
287367
297488
30819
3182100
328367
338488
349191
359288
369367
379422
3810190
3910277
4010343
4110487
4211190
4311277
4411343
4511487
4612190
4712277
4812343
4912487
5213387

二、题目

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 = '李平老师';
# mysql # 数据库 # java
Logo
亚马逊云科技技术品牌专区

更多推荐

STM32节点移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）

STM32移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）前言前言在移植协议之前，先给大家科普一下Lora 和 lorawan 的区别。LoRa 是LPWAN通信技术中的一种，是美国Semtech公司采用和推广的一种基于扩频技术的超远距离无线传输方案。这一方案改变了以往关于传输距离与功耗的折衷考虑方式为用户提供一种简单的能实现远距离、长电池寿命、大容量的系统，进而扩...

avatar 亚马逊云科技技术品牌专区
cover

物联网主机E6000引领工业自动化的新篇章

avatar 亚马逊云科技技术品牌专区

搞 IoT 物联网，你居然要懂这么多种协议...

物联网协议是指在物联网环境中用于设备间通信和数据传输的协议。根据不同的作用，物联网协议可分为传输协议、通信协议和行业协议。传输协议：一般负责子网内设备间的组网及通信。例如 Wi-Fi、Ethernet、NFC、 Zigbee、Bluetooth、GPRS、3G/4G/5G等。这些协议能够确保在网络上传输的数据的安全性和可靠性。通讯协议：主要是运行在传统互联网TCP/IP协议之上的设备通讯协议，负责

avatar 亚马逊云科技技术品牌专区