班级表
-- 班级表
mysql> create table class(
-> cid int auto_increment primary key,
-> caption int not null
-> )charset utf8;
Query OK, 0 rows affected (0.41 sec)
-- 学生表
mysql> create table student(
-> sid int not null primary key,
-> sname varchar(255) not null default 1,
-> gender enum('male', 'female'),
-> class_id int not null,
-> CONSTRAINT fk_id FOREIGN KEY (class_id) REFERENCES class(cid)
-> )charset utf8;
Query OK, 0 rows affected (0.41 sec)
-- 教师表
mysql> create table teacher(
-> tid int not null primary key,
-> tname varchar(255) not null
-> )charset utf8;
Query OK, 0 rows affected (0.34 sec)
-- 课程表
mysql> create table course(
-> cid int not null primary key,
-> cname varchar(255) not null,
-> teacher_id int not null,
-> constraint fk_tch_id foreign key (teacher_id) references teacher(tid)
-> )charset utf8;
Query OK, 0 rows affected (0.37 sec)
-- 成绩表
mysql> create table score(
-> sid int not null primary key,
-> student_id int not null,
-> course_id int not null,
-> number tinyint not null,
-> constraint fk_stu_id foreign key (student_id) references student(sid),
-> constraint fk_crs_id foreign key (course_id) references course(cid)
-> )charset utf8;
Query OK, 0 rows affected (0.53 sec)
mysql> alter table score add unique key(student_id,course_id);
Query OK, 0 rows affected (0.58 sec)
-- 插入班级
mysql> insert into class values
-> (1, '三年二班'),
-> (2, '三年三班'),
-> (3, '一年二班'),
-> (4, '三年七班');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from class;
+-----+----------+
| cid | capiton |
+-----+----------+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 三年七班 |
+-----+----------+
4 rows in set (0.00 sec)
-- 插入学生
mysql> insert into student values
-> (1, '张三', 'male', 1),
-> (2, '李四', 'male', 2),
-> (3, '王五', 'male', 3),
-> (4, '赵六', 'male', 4),
-> (5, '钢蛋', 'female', 4);
mysql> insert into student values
-> (6, '铁锤', 'female', 1),
-> (7, '如花', 'male', 2),
-> (8, '锅炉', 'female', 3),
->
-> (9, '小乔', 'female', 4),
-> (10, '公孙离 ', 'female',1);
Query OK, 5 rows affected (0.05 sec)
mysql> insert into student values
-> (11, '沈雷锴', 'male', 1);
Query OK, 1 row affected (0.06 sec)
mysql> insert into student values
-> (12, 'xzn', 'male', 2),
-> (13, 'cjl', 'male', 3),
-> (14, 'xwj', 'male', 4),
-> (15, 'egon', 'male', 2);
Query OK, 4 rows affected (0.04 sec)
-- 插入老师
mysql> insert into teacher values
-> (1, '张磊'),
-> (2, '李平'),
-> (3, '刘海燕'),
-> (4, '朱云海'),
-> (5, '李杰');
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- 插入课程
mysql> insert into course values
-> (1, '生物', 1),
-> (2, '物理', 2),
-> (3, '体育', 3),
-> (4, '美术', 2);
Query OK, 4 rows affected (0.13 sec)
-- 插入成绩
mysql> insert into score values
-> (1, 1, 1, 10),
-> (2, 1, 2, 9),
-> (3, 1, 4, 66),
-> (4, 2, 1, 8),
-> (5, 2, 3, 68),
-> (6, 2, 4, 88),
-> (7, 3, 2, 8),
-> (8, 3, 3, 55),
-> (9, 3, 4, 66),
-> (10, 4, 1, 6),
-> (11, 4, 3, 89),
-> (12, 4, 4, 96),
-> (13, 5, 1, 33),
-> (14, 5, 2, 34),
-> (15, 5, 4, 34);
Query OK, 15 rows affected (0.11 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> insert into score values
-> (16,6,1,44),
-> (17,6,2,33),
-> (18,7,3,66),
-> (19,7,4,88),
-> (20,8,1,78),
-> (21,8,2,42),
-> (22,9,3,99),
-> (23,9,4,100),
-> (24,10,1,12),
-> (25,10,4,66),
-> (26,11,1,56),
-> (27,12,1,68),
-> (28,12,2,33);
Query OK, 13 rows affected (0.19 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql> insert into score values
-> (29,13,1,33),
-> (30,14,1,58),
-> (31,15,1,100);
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from class;
+-----+----------+
| cid | capiton |
+-----+----------+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
| 4 | 三年七班 |
+-----+----------+
mysql> select * from student;
+-----+---------+--------+----------+
| sid | sname | gender | class_id |
+-----+---------+--------+----------+
| 1 | 张三 | male | 1 |
| 2 | 李四 | male | 2 |
| 3 | 王五 | male | 3 |
| 4 | 赵六 | male | 4 |
| 5 | 钢蛋 | female | 4 |
| 6 | 铁锤 | female | 1 |
| 7 | 如花 | male | 2 |
| 8 | 锅炉 | female | 3 |
| 9 | 小乔 | female | 4 |
| 10 | 公孙离 | female | 1 |
| 11 | 沈雷锴 | male | 1 |
| 12 | xzn | male | 2 |
| 13 | cjl | male | 3 |
| 14 | xwj | male | 4 |
| 15 | egon | male | 2 |
+-----+---------+--------+----------+
mysql> select * from teacher;
+-----+--------+
| tid | tname |
+-----+--------+
| 1 | 张磊 |
| 2 | 李平 |
| 3 | 刘海燕 |
| 4 | 朱云海 |
| 5 | 李杰 |
+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from course;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+-------+------------+
4 rows in set (0.00 sec)
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 10 |
| 2 | 1 | 2 | 9 |
| 3 | 1 | 4 | 66 |
| 4 | 2 | 1 | 8 |
| 5 | 2 | 3 | 68 |
| 6 | 2 | 4 | 88 |
| 7 | 3 | 2 | 8 |
| 8 | 3 | 3 | 55 |
| 9 | 3 | 4 | 66 |
| 10 | 4 | 1 | 6 |
| 11 | 4 | 3 | 89 |
| 12 | 4 | 4 | 96 |
| 13 | 5 | 1 | 33 |
| 14 | 5 | 2 | 34 |
| 15 | 5 | 4 | 34 |
| 16 | 6 | 1 | 44 |
| 17 | 6 | 2 | 33 |
| 18 | 7 | 3 | 66 |
| 19 | 7 | 4 | 88 |
| 20 | 8 | 1 | 78 |
| 21 | 8 | 2 | 42 |
| 22 | 9 | 3 | 99 |
| 23 | 9 | 4 | 100 |
| 24 | 10 | 1 | 12 |
| 25 | 10 | 4 | 66 |
| 26 | 11 | 1 | 56 |
| 27 | 12 | 1 | 68 |
| 28 | 12 | 2 | 33 |
| 29 | 13 | 1 | 33 |
| 30 | 14 | 1 | 58 |
| 31 | 15 | 1 | 100 |
+-----+------------+-----------+--------+
31 rows in set (0.00 sec)
一、数据准备
/*
数据导入：
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、查询物理成绩等于100的学生的姓名
--
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
--
-- 5、查询所有学生的学号，姓名，选课数，总成绩
--
-- 6、 查询姓李老师的个数
--
-- 7、 查询没有报李平老师课的学生姓名
--
-- 8、 查询物理课程比生物课程高的学生的学号
--
-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
-- 11、查询选修了所有课程的学生姓名
-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
--
-- 12、查询李平老师教的课程的所有成绩记录
--
-- 13、查询全部学生都选修了的课程号和课程名
--
-- 14、查询每门课程被选修的次数
--
-- 15、查询之选修了一门课程的学生姓名和学号
--
-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
--
-- 17、查询平均成绩大于85的学生姓名和平均成绩
--
-- 18、查询生物成绩不及格的学生姓名和对应生物分数
--
-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
--
-- 20、查询每门课程成绩最好的前两名学生姓名
--
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
--
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
--
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
--
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
-- 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 sname,num as '物理成绩' from student inner join (
-- 第一步：查询分数为100的表
select * from score where num = 100
having course_id = (
-- 第二步：查询物理的id
select cid from course where cname = '物理')
-- 拼表
) as t1 on student.sid = t1.student_id
-- 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 sid 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
-- 第六步：筛选物理大于生物
having 物理成绩 > 生物成绩
-- 9、 查询没有同时选修物理课程和体育课程的学生姓名
select sname from student where sid in(
-- 第三步：以学生id为分组，筛选课程等于1，拿到学生id
select t1.student_id from (
-- 第一步：简单粗暴，直接把两张相关的表拼起来
select * from score inner join course on score.course_id = course.cid
-- 第二部：筛选数据
having cname in ('物理','体育'))as t1
-- 分组，筛选
group by t1.student_id having count(num) = 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
-- 第三步：排序
order by student_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 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 = '生物')
-- 第三步：筛选
having num < 60
)as t1 on student.sid = t1.student_id
-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select sname,最大值 from student inner join (
-- 第五步：根据所有学生的平均成绩选出最大成绩
select student_id,max(t2.avg)as '最大值' from (
-- 第四步：求出李平老师所有学生的平均成绩
select student_id,avg(num)as'avg' 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
)as t3 on student.sid = t3.student_id
-- 20、查询每门课程成绩最好的前两名学生姓名
# 各科第一名成绩
select course_id,student_id,max(num) from score group by course_id
select max(num) from score group by course_id
select * from (
select course_id,student_id,max(num)as 'max1' from score group by course_id
)as t1 where t1.max1 < (select max(num) from score group by course_id)
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
select student_id,num,count(num) from score group by num having count(num)>=2