MySQL数据库练习

1.数据准备

以下为db文件，通过Navicat Premium导入数据库

/*
 数据导入：
 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;

2.练习题

-- 1、查询所有的课程的名称以及对应的任课老师姓名
select cname,tname from course inner join teacher on course.teacher_id = teacher.tid

-- 2、查询学生表中男女生各有多少人
select gender,count(gender) from student group by gender

-- 3、查询物理成绩等于100的学生的姓名
-- 第四步：与学生表拼接，拿到学生姓名
select student.sid,gender,sname,num from student inner join(
select * from (
-- 第一步：查询分数为100的表
select * from score where num = 100 )as t1 
-- 第三步：条件筛选出物理分数100的表
where t1.course_id = (
-- 第二步：查询物理的id
select cid from course where cname = '物理')
# 拼接
) as t2 on t2.student_id = student.sid

-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 第二步：拼表
select sname, num from student inner join (
-- 第一步：以学生id为分组，筛选分数表中的成绩
select student_id,avg(num)as 'num' from score group by student_id having avg(num)>80
)as t1 on student.sid = t1.student_id

-- 5、查询所有学生的学号，姓名，选课数，总成绩
-- 第三步：拼表筛选
select t1.student_id,t2.sname,t1.数量,t1.总成绩 from (
-- 第二部筛选分数表，并计数
select student_id,count(num)as '数量',sum(num)as '总成绩' from score group by student_id)as t1
inner join (
-- 第一步：筛选学生表
select sid,sname from student)as t2 on t1.student_id = t2.sid

-- 6、 查询姓李老师的个数
select count(tname)as '姓李的数量' from teacher where tname like '李%'

-- 7、 查询没有报李平老师课的学生姓名
-- 第四步：根据学生id取反拿到没有选择李平老师课程的学生
select sname from student where sid not in (
-- 第三步：拿到选择李平老师课程的学生id
select distinct student_id from score where course_id in (
-- 第二步：根据老师id拿到老师教的课程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老师的id
select tid from teacher where tname = '李平老师')))

-- 8、 查询物理课程比生物课程高的学生的学号
-- 第五步：拼接两张表
select * from (
-- 第二步：查询物理的所有成绩
select student_id as '学生物理id',num as '物理成绩' from score where course_id = (
-- 第一步：查询物理的id
select cid from course where cname = '物理' ))as t1
inner join (
-- 第四步：查询生物的所有成绩
select student_id as '学生生物id',num as '生物成绩' from score where course_id = (
-- 第三步：查询生物的id
select cid from course where cname = '生物' ))as t2
on t1.学生物理id = t2.学生生物id
-- 第六步：筛选物理大于生物
where 物理成绩 > 生物成绩

-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
-- 第四步：根据拿到的学号从学生表中获取想要的数据
select sid,sname from student where sid in (
-- 第三步：以学号为组，计数为1，意思是物理体育只有一门，筛选得到学号
select t1.student_id from (
-- 第一步，简单粗暴，直接把相关的表拼起来
select * from score inner join course on score.course_id = course.cid
-- 第二步，筛选课程
where cname in ('物理','体育')
)as t1
group by t1.student_id
having count(student_id)=1
)

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
-- 第四步：和班级表拼接，拿到想要的字段
select caption,sname,挂科数 from class inner join (
-- 第三步：与学生表拼接，拿出想要的字段
select * from student inner join (
-- 第一步：筛选出小于60分的所有成绩名单
select student_id,count(student_id)as'挂科数'  from score where num < 60 
-- 第二步: 以学生为分组，查看这个学生60分以下出现了多少次，就是多少门不及格
group by student_id having count(student_id)>=1
-- 拼接
)as t1 on student.sid = t1.student_id 
) as t2 on class.cid = t2.class_id


-- 11、查询选修了所有课程的学生姓名
-- 第四步：和学生表拼接，拿到想要的字段
select sname,课程数 from student inner join (
-- 第二步：以学生为分组，计算所有学生选择的课程数
select student_id,count(student_id)as'课程数' from score group by student_id 
-- 第三步：筛选学生选择的课程数=课程总数
having count(student_id) = (
-- 第一步：拿到所有课程的id
select count(cid)as'总课程数' from course )
-- 拼接
)as t1 on student.sid = t1.student_id

-- 12、查询李平老师教的课程的所有成绩记录
-- 第三步：在与分数表拼接，拿到想要的字段信息
select tname,student_id,cname,num from score inner join (
-- 第二步：课程表和李平老师信息表拼接
select * from course inner join (
-- 第一步：拿到李平老师的信息
select * from teacher where tname = '李平老师'
-- 拼接
)as t1 on course.teacher_id = t1.tid
-- 拼接
)as t2 on score.course_id = t2.cid

-- 13、查询全部学生都选修了的课程号和课程名
-- 第二步：在和课程表拼接起来，选择想要的字段
select student_id,sname,gender,cid,cname,num from course inner join (
-- 第一步：把分数表和学生表拼起来，选择想要的字段
select score.student_id,student.gender,student.sname,score.course_id,score.num from score inner join student on score.student_id = student.sid
-- 拼接
)as t1 on course.cid = t1.course_id 

-- 14、查询每门课程被选修的次数
第二步：筛选过的表与课程表拼接，获取课程名
select cid,cname, 被选次数 from course inner join (
-- 第一步：以课程id为组，查询被选的次数
select course_id,count(course_id)as'被选次数' from score group by course_id
) as t1 on course.cid = t1.course_id

-- 15、查询只选修了一门课程的学生姓名和学号
-- 第二步：拼接学生表，获取信息
select sid,sname,选课门数 from student inner join(
-- 第一步：以学生为分组，查询选课数为1的
select student_id,count(student_id)as '选课门数' from score group by student_id 
having count(student_id) = 1
-- 拼接
) as t1 on student.sid = t1.student_id

-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
-- 第二步：和学生表拼接，获取想要的字段信息
select sid,sname,总成绩 from student inner join (
-- 第一步：查询所有学生的总成绩
select student_id,sum(num)as '总成绩' from score group by student_id
-- 拼接
)as t1 on student.sid = t1.student_id
-- 降序排名
order by 总成绩 desc


-- 17、查询平均成绩大于85的学生姓名和平均成绩
-- 第二步：和学生表拼接，获取想要的字段
select sname,avg as '平均成绩' from student inner join (
-- 第一步：以学生id为分组 查询大于85分的信息
select student_id,avg(num)as 'avg' from score group by student_id having avg(num)>85
) as t1 on student.sid = t1.student_id


-- 18、查询生物成绩不及格的学生姓名和对应生物分数
-- 第四步：拼接学生表获取想要的信息
select sname,num as '不及格生物分数' from student inner join (
-- 第二步：查询所有的生物成绩
select * from score where course_id = (
-- 第一步：查询生物的id
select cid from course where cname = '生物')
-- 第三步：筛选分数不及格的
and num<60
)as t1 on student.sid = t1.student_id


-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select sname,平均值 from student inner join (
-- 第五步：根据所有学生的平均成绩选出最大成绩，排序，取第一名
select t2.student_id,平均值 from (
-- 第四步：求出李平老师所有学生的平均成绩
select t1.student_id ,avg(num)as'平均值' from (
-- 第三步：拿到选择李平老师课程的学生id
select * from score where course_id in (
-- 第二步：根据老师id拿到老师教的课程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老师的id
select tid from teacher where tname = '李平老师'))
-- 接第四步
)as t1 group by t1.student_id  
-- 接第五步
)as t2 order by 平均值 desc limit 1
)as t3 on student.sid = t3.student_id;


-- 20、查询每门课程成绩最好的前两名学生姓名
-- 错误思路：按照课程id分组，排序，去前两名。分组之后，组里面的数据不能取出来
-- 正确思路：把每一课的成绩分别拎出来，分别取前两名。最后合并表

# 最最后：和学生表拼接，拿到学生姓名
select student.sid,student.sname,t1.num from student inner join (
# 最后一步，用union all,不去重连接所有表
# 生物前两名
-- 第二步：查看生物的所有成绩
(select * from score where course_id in (
-- 第一步：获取所有生物的id号
select cid from course where cname= '生物'
)
-- 第三步：排序,并只显示前两条
order by num desc limit 2)
union all
# 物理前两名
-- 第二步：查看生物的所有成绩
(select * from score where course_id in (
-- 第一步：获取所有生物的id号
select cid from course where cname= '物理'
)
-- 第三步：排序,并只显示前两条
order by num desc limit 2)
union all
# 体育前两名
-- 第二步：查看生物的所有成绩
(select * from score where course_id in (
-- 第一步：获取所有生物的id号
select cid from course where cname= '体育'
)
-- 第三步：排序,并只显示前两条
order by num desc limit 2)
union all
# 美术前两名
-- 第二步：查看生物的所有成绩
(select * from score where course_id in (
-- 第一步：获取所有生物的id号
select cid from course where cname= '美术'
)
-- 第三步：排序,并只显示前两条
order by num desc limit 2)

) as t1 on student.sid = t1.student_id

-- 21、查询不同课程但成绩相同的学号，课程号，成绩

-- 第二步：再次和分数表拼接，获取学号
select student_id,course_id,t1.num,相同数量 from score inner join (
-- 第一步：以分数为分组，筛选出分数重复的
select num,count(num)as'相同数量' from score group by num having count(num) >=2
)as t1 on score.num = t1.num

-- 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称； 
-- 第四步：根据学生id取反拿到没有选择李平老师课程的学生
select sname from student where sid not in (
-- 第三步：拿到选择李平老师课程的学生id
select distinct student_id from score where course_id in (
-- 第二步：根据老师id拿到老师教的课程id
select cid from course where teacher_id = (
-- 第一步:拿到李平老师的id
select tid from teacher where tname = '李平老师')))
    
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
# 这题考小学语文
-- 第三步：在学生表中筛选出学生
select sid,sname from student where sid in (
-- 第二步：查询选修了学号为1的学生，选修过的课程的学生id
select student_id from score where course_id in (
-- 第一步：查询学号为1的同学，选修的课程id
select course_id from score where student_id = 1
))
    
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
# 这题考小学语文


-- 实现思路：1.获取授课老师最多的老师id
--                   2.查看该老师教授的课程id
--                   3.查询教授课程对应的所有分数
--       (难点)4.从查询出的分数表，选出最大值的分数
-- (难点,分数相同)5.拿到最大分数再到第3分数表中，查询一致的分数对应的所有信息
--                   6.再和学生表拼接，拿到学生姓名
# 这是第3步筛选之后的表
select * from score where course_id in (
-- 第四步：查看该老师教授的课程id
select cid from course where teacher_id in(
-- 第三步：获取任课数量最多的老师id
select t1.teacher_id from (
-- 第一步：在课程表当中，给选择的课程数量计数
select teacher_id,count(teacher_id)as '数量' from course group by teacher_id 
-- 第二步：排序
order by 数量 desc limit 1
)as t1
))

# 这是最大分数
select max(num) from score where course_id in (
-- 第四步：查看该老师教授的课程id
select cid from course where teacher_id in(
-- 第三步：获取任课数量最多的老师id
select t3.teacher_id from (
-- 第一步：在课程表当中，给选择的课程数量计数
select teacher_id,count(teacher_id)as '数量' from course group by teacher_id 
-- 第二步：排序
order by 数量 desc limit 1
)as t3
))



# 最终答案
# 与学生表拼接
select student.sid,student.sname,t4.num from student inner join (
# 获取最大分数对应的信息
select * from (
select * from score where course_id in (
-- 第四步：查看该老师教授的课程id
select cid from course where teacher_id in(
-- 第三步：获取任课数量最多的老师id
select t1.teacher_id from (
-- 第一步：在课程表当中，给选择的课程数量计数
select teacher_id,count(teacher_id)as '数量' from course group by teacher_id 
-- 第二步：排序,获取最大数量
order by 数量 desc limit 1
)as t1
))
)as t2 where t2.num = (
-- 第五步：查询分数表，教授课程对应的所有分数
# 这里获取最大值分数
select max(num) from score where course_id in (
-- 第四步：查看该老师教授的课程id
select cid from course where teacher_id in(
-- 第三步：获取任课数量最多的老师id
select t3.teacher_id from (
-- 第一步：在课程表当中，给选择的课程数量计数
select teacher_id,count(teacher_id)as '数量' from course group by teacher_id 
-- 第二步：排序,获取最大数量
order by 数量 desc limit 1
)as t3
)))
)as t4 on student.sid = t4.student_id