MySQL_多表查询练习题
---
操作软件: Navicat
"""
资料准备:
导入SQL文件(新建txt文件,复制内容,保存时将后缀改为.sql)就可以将其导入Navicat中了
/* 数据导入： Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `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 AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; 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'); COMMIT; -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `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 AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; 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', '刘四'); COMMIT; -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
然后把表关系展示出来
表关系图及分别展示出表之间来连接的字段
连接字段1
连接字段2
连接字段3
连接字段4
1-1 查询所有课程名称以及对应的任课老师姓名
分析:
涉及哪些表: 课程名称一定有课程表; 老师姓名一定要有了老师表;
# 涉及表: 课程表 老师表 # 需求: 课程名称 老师姓名 select course.cname,teacher.tname FROM course inner join teacher on course.teacher_id = teacher.tid;
1-2 查询平均成绩大于八十分的同学姓名和平均成绩
分析:
先分析设计几张表: 平均成绩是涉及成绩表; 同学姓名涉及学生表;
另"平均成绩"是要用到聚合函数中的平均函数avg(),那么又想到需要按照分数分组(关键字group by),大于八十: 是分组之后的筛选条件用到having.
-- 分析语句: -- 涉及关键词: 平均成绩 avg() 分组group by -- 涉及表: score表 student表 -- 需求: 平均成绩 同学姓名 -- 思路: 想要获取sname需知道sid,而sid又需要从score表student_id获取 -- 所以先操作score表获取student_id是关键.而刚好需要题目中的 -- "平均分大于80" 这个需求是操作score表,所以先操作score表. -- 先操作score表: 可以获取student_id及对应的平均成绩 -- select score.student_id from score -- group by score.student_id -- having avg(score.num)>80; -- 又因平均成绩与学生需一起输出,所以需将上述结果作为新表与student连表 -- 连完先看下结果,再进行后续操作 -- select * from student inner join -- (select score.student_id from score -- group by score.student_id -- having avg(score.num)>80) as t1 on student.sid = t1.student_id; -- 最后获取对应数据 select student.sname,t1.avg_num from student inner join (select score.student_id,avg(score.num) as avg_num from score group by score.student_id having avg(score.num)>80) as t1 on student.sid = t1.student_id;
1-3 查询没有报李平老师课的学生姓名
方法1: 分布操作
--- 分析语句: -- 查询没有报李平老师课的学生姓名 -- 语义分析: 没有报,可以取反用关键字not -- 涉及表: teacher student score -- 需求: 学生姓名 -- 思路: -- 学生姓名sname需要知道sid; 而sid 又依赖于score表的student_id -- student_id 需要course表的cid ,cid 又需要teacher表的tid -- 所以应该从teacher表起步,而从题目可以获取一个信息: 李平老师-即tname -- >>>:本次分布操作 -- 查出李平老师对应id -- select teacher.tid from teacher where teacher.tname='李平老师'; -- 查出李老师教的课程id -- select course.cid from course where course.teacher_id in -- (select teacher.tid from teacher where teacher.tname='李平老师'); -- 查出课程id对应的学生id,注意学生表与成课程是一对多关系,记得去重. -- select distinct score.student_id from score where score.course_id in -- (select course.cid from course where course.teacher_id in -- (select teacher.tid from teacher where teacher.tname='李平老师')); -- 根据学生id查取反的学生姓名 select student.sname from student where student.sid not in (select distinct score.student_id from score where score.course_id in (select course.cid from course where course.teacher_id in (select teacher.tid from teacher where teacher.tname='李平老师')));
方法2: 连表操作(感觉这里用起来复杂,不推荐)
-- 分析语句: -- 查询没有报李平老师课的学生姓名 -- 语义分析: 没有报,可以取反用关键字not -- 涉及表: teacher student score -- 需求: 学生姓名 -- 思路: -- 学生姓名sname需要知道sid; 而sid 又依赖于score表的student_id -- student_id 需要course表的cid ,cid 又需要teacher表的tid -- 所以应该从teacher表起步,而从题目可以获取一个信息: 李平老师-即tname -- 可以连表三次,获取相关信息; -- -- 第一次练course表: 获取course中cid -- select course.cid from teacher inner join -- course on teacher.tid = course.teacher_id -- where teacher.tname in ('李平老师'); -- -- 第二次连score表: 获取表中student_id -- select DISTINCT score.student_id from score inner join -- (select course.cid from teacher inner join -- course on teacher.tid = course.teacher_id -- where teacher.tname in ('李平老师')) as t1 -- on score.course_id = t1.cid; -- -- 第三次连student表:获取表中学生姓名 -- select student.sname from student inner join -- (select DISTINCT score.student_id from score inner join -- (select course.cid from teacher inner join -- course on teacher.tid = course.teacher_id -- where teacher.tname in ('李平老师')) as t1 -- on score.course_id = t1.cid) as t2 -- on student.sid = t2.student_id; -- -- 上述都是按李平老师教的学生筛选的,根据题目要求,取反 select student.sname from student where student.sname not in (select student.sname from student inner join (select DISTINCT score.student_id from score inner join (select course.cid from teacher inner join course on teacher.tid = course.teacher_id where teacher.tname in ('李平老师')) as t1 on score.course_id = t1.cid) as t2 on student.sid = t2.student_id);
1-4 查询没有同时选修物理课程和体育课程的学生姓名
-- 分析语句: -- 查出 同时选修 物理和体育课程学生姓名 -- 取反 -- 涉及: 课程表 学生表,但看关系图,发现中间还涉及一个score表 -- 需求: 查询没有同时选修物理课程和体育课程的学生姓名 -- 学生姓名 需要学生sid,sid需要sid,sid需要course_id,course_id就是cid在course表 -- 已知条件: 物理和体育课程. -- 连表分组 -- select * from score inner join -- course on score.course_id = course.cid; -- 根据科目筛选 -- 连表分组 -- select * from score inner join -- course on score.course_id = course.cid -- where course.cname in ('物理','体育'); -- 思考: 在上述情况下,学生选修物理、体育课,有两种情况 -- : 1) 选其中一门 2) 两门都报,所以 可以统计学生id来筛出 -- 同时报物理和体育的学生id(统计函数count,需联想到分组关键字) -- select score.student_id from score inner join -- course on score.course_id = course.cid -- where course.cname in ('物理','体育') -- group by score.student_id -- having count(score.student_id)=2; -- 从学生表中选出不在上述学生id的学生名字 select student.sname from student where student.sid not in (select score.student_id from score inner join course on score.course_id = course.cid where course.cname in ('物理','体育') group by score.student_id having count(score.student_id)=2);
1-5 查询挂科超过两门(包含两门)的学生姓名和班级
思路1
-- 需求: 查询挂科超过两门(包含两门)的学生姓名和班级 -- 语义分析: -- 挂科: 分数<60 ; 超过两门 count函数 学生id分组后筛选>=2 -- 涉及表: 挂科-分数表; 学生姓名-学生表; 班级-班级表; -- 最终需要学生姓名和班级一起输出,而这两者在两个表,所以最后需要连表 -- 从已知条件入手,从分数表到学生表最后再连班级表 -- 先连学生表和分数表,筛选出不及格的,然后按照学生id分组(或课程id分组),之后筛选挂 -- 了两门的学生id -- select * from student inner join -- score on student.sid = score.student_id -- where score.num<60 -- group by student.sid -- having count(score.num<60)>=2; -- 根据上表结果获取可以获取的信息 -- select student.sid,student.sname,student.class_id from student inner join -- score on student.sid = score.student_id -- where score.num<60 -- group by student.sid -- having count(score.num<60)>=2; -- 连班级表(获取学生对应的班级,并使其与学生姓名一起输出) select t1.sname,class.caption from class inner join (select student.sid,student.sname,student.class_id from student inner join score on student.sid = score.student_id where score.num<60 group by student.sid having count(score.num<60)>=2) as t1 on class.cid = t1.class_id;
思路2
-- 需求: 查询挂科超过两门(包含两门)的学生姓名和班级 -- 语义分析: -- 挂科: 分数<60 ; 超过两门 count函数 学生id分组后筛选>=2 -- 涉及表: 挂科-分数表; 学生姓名-学生表; 班级-班级表; -- select * from score -- where score.num <60 -- group by score.student_id -- having count(score.course_id)>=2; -- 讲* 换成 要筛的数据 -- select score.student_id from score -- where score.num <60 -- group by score.student_id -- having count(score.course_id)>=2; -- 连接class和student表 -- select * from class inner join -- student on class.cid = student.class_id; -- 根据成员运算, 学生id在...里筛选 -- select * from class inner join -- student on class.cid = student.class_id -- where student.sid in -- (select score.student_id from score -- where score.num <60 -- group by score.student_id -- having count(score.course_id)>=2); -- 获取最终数据: 将*替换成最终需要的数据 select student.sname,class.caption from class inner join student on class.cid = student.class_id where student.sid in (select score.student_id from score where score.num <60 group by score.student_id having count(score.course_id)>=2);
