理论复习: where， group by ， having ， order by 使用总结
1.它们四者的执行顺序：where > group by > having > order by;
2. 因为where是在group by之前执行，所以where子句中不能包含聚合函数，而having就可以；
3. having用于过滤分组后的结果集，所以不能使用输出字段的别名进行判读。而执行order by时，结果集已经查询完成，所以在order by中能使用输出字段的别名进行排序。
4. 子查询的分类：
–相关子查询
执行依赖于外部查询的数据。
外部查询返回一行，子查询就执行一次。
–非相关子查询
独立于外部查询的子查询。
子查询总共执行一次，执行完毕后后将值传递给外部查询。
5. SQL的执行顺序：
–第一步：执行FROM
–第二步：WHERe条件过滤
–第三步：GROUP BY分组
–第四步：执行SELECt投影列
–第五步：HAVINg条件过滤
–第六步：执行ORDER BY 排序
SQL实战开始！直接把下面的建库语句拷贝到MYSQL,执行我的SQL语句，就可以了解到有规律的高级查询奥秘！
;
;
;
;
;
;
CREATE DATABASE `test` ;
USE `test`;
DROP TABLE IF EXISTS `attendance`;
CREATE TABLE `attendance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_id` int(11) DEFAULT NULL,
`attendance_time` float DEFAULT NULL COMMENT '考勤时间（小时）',
`date_mark` varchar(30) DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
insert into `attendance`(`id`,`teacher_id`,`attendance_time`,`date_mark`) values (1,1,8.5,'2020-1-1'),(2,2,8,'2020-1-1'),(3,3,9.1,'2020-1-1'),(4,1,7,'2020-1-4'),(5,2,9.2,'2020-1-5'),(6,3,6.8,'2020-1-6'),(7,1,8,'2020-1-8'),(8,2,7,'2020-1-9'),(9,3,8,'2020-1-11'),(10,1,7,'2020-1-15'),(11,2,6,'2020-1-18'),(12,3,9,'2020-1-19'),(13,1,8,'2020-1-22'),(14,2,6,'2020-1-25'),(15,3,8,'2020-1-29');
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '课程ID',
`course_name` varchar(100) DEFAULT NULL,
`teacher_id` int(10) DEFAULT NULL COMMENT '老师名字',
PRIMARY KEY (`id`),
KEY `teacher_id` (`teacher_id`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into `course`(`id`,`course_name`,`teacher_id`) values (1,'语文',1),(2,'数学',2),(3,'英语',3);
DROP TABLE IF EXISTS `extra`;
CREATE TABLE `extra` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL COMMENT '课外活动名称',
`type` int(10) DEFAULT NULL COMMENT '课外活动类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
insert into `extra`(`id`,`name`,`type`) values (1,'科学',1),(2,'绘画',1),(3,'围棋',1),(4,'舞蹈',2),(5,'唱歌',2),(6,'足球',2),(7,'乒乓球',2),(8,'跳绳',2);
DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`position` varchar(100) DEFAULT NULL COMMENT '活动地点',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
insert into `position`(`id`,`position`) values (1,'第一教室'),(2,'第二教室'),(3,'第三教室'),(4,'实验室'),(5,'体育室');
DROP TABLE IF EXISTS `rel_extra_position`;
CREATE TABLE `rel_extra_position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`extra_id` int(11) DEFAULT NULL,
`position_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
insert into `rel_extra_position`(`id`,`extra_id`,`position_id`) values (1,1,1),(2,2,1),(3,4,1),(4,5,1),(5,6,1),(6,1,2),(7,2,2),(8,3,2),(9,4,2),(10,2,3),(11,4,3),(12,8,3),(13,1,4),(14,4,4),(15,5,4),(16,2,5),(17,3,5),(18,6,5),(19,7,5),(20,8,5);
DROP TABLE IF EXISTS `rule`;
CREATE TABLE `rule` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) DEFAULT NULL COMMENT '学生id',
`min` int(11) DEFAULT NULL COMMENT '最少课外活动数量',
`max` int(11) DEFAULT NULL COMMENT '最多课外活动数量',
`type` int(11) DEFAULT NULL COMMENT '课外活动类型',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
insert into `rule`(`id`,`sid`,`min`,`max`,`type`) values (1,1,2,2,1),(2,1,3,4,2),(3,2,2,3,1),(4,2,2,3,2),(5,3,1,2,1),(7,3,2,3,2);
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '成绩ID',
`sid` int(10) DEFAULT NULL COMMENT '外键',
`cid` int(10) DEFAULT NULL COMMENT '外键',
`score` int(10) DEFAULT NULL COMMENT '成绩',
`month` int(10) DEFAULT NULL COMMENT '月份',
PRIMARY KEY (`id`),
KEY `sid` (`sid`),
KEY `cid` (`cid`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `student` (`id`),
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
insert into `score`(`id`,`sid`,`cid`,`score`,`month`) values (1,1,1,80,1),(2,2,1,90,1),(3,3,1,100,1),(4,1,2,88,1),(5,2,2,45,1),(6,3,2,34,1),(7,1,3,46,1),(8,2,3,76,1),(9,3,3,57,1),(10,1,1,86,2),(11,2,1,56,2),(12,3,1,75,2),(13,1,2,77,2),(14,2,2,46,2),(15,3,2,75,2),(16,1,3,65,2),(17,2,3,46,2),(18,3,3,87,2),(19,1,1,95,3),(20,2,1,56,3),(21,3,1,75,3),(22,1,2,86,3),(23,2,2,45,3),(24,3,2,45,3),(25,1,3,65,3),(26,2,3,45,3),(27,3,3,78,3),(28,1,1,56,4),(29,2,1,87,4),(30,3,1,57,4),(31,1,2,89,4),(32,2,2,76,4),(33,3,2,45,4),(34,1,3,56,4),(35,2,3,66,4),(36,3,3,35,4);
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` varchar(50) DEFAULT NULL COMMENT '学生名字',
`class` int(10) DEFAULT NULL,
`bir_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
insert into `student`(`id`,`name`,`class`,`bir_date`) values (1,'小王',1,NULL),(2,'小李',1,NULL),(3,'小张',2,NULL),(4,'小明',3,NULL),(5,'小赵',6,NULL),(6,'小贾',5,NULL),(7,'小戴',4,NULL),(8,'小红',7,NULL),(9,'小洪',2,NULL),(10,'小曹',6,NULL);
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`teacher_name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
insert into `teacher`(`id`,`teacher_name`) values (1,'王老师'),(2,'李老师'),(3,'赵老师'),(4,'黄老师');
;
;
;
;
表结构图：
1、查询选修“王”老师所授课程的学生中，及格的学生姓名及其成绩
SELECT st.`name`,sc.score FROM student st
LEFT JOIN score sc ON sc.sid = st.`id`
WHERe st.`id` IN
(
SELECt sc.sid FROM score sc
LEFT JOIN course c ON sc.cid = c.id
LEFT JOIN teacher t ON c.teacher_id = t.id
WHERe t.teacher_name='王老师' AND sc.score>60
)
ORDER BY Score DESC
2、查询选修“王”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECt st.`name`,MAX(sc.score) AS max_score,SUM(sc.score)AS sum_score FROM score sc
LEFT JOIN course c ON sc.cid = c.id
LEFT JOIN teacher t ON c.teacher_id = t.id
LEFT JOIN student st ON sc.sid = st.id
WHERe t.teacher_name='王老师' AND sc.score>60
ORDER BY Score DESC
SELECt st.`name`,MAX(sc.score) FROM course c,score sc ,teacher t,student st
WHERe sc.cid = c.id AND c.teacher_id = t.id AND sc.sid = st.id AND t.teacher_name='王老师' AND sc.score>60
ORDER BY Score DESC
3、查询出现连续三个月数学成绩不及格的学生
SELECt DISTINCT s.* FROM student s, score sc1 WHERe sc1.sid=s.id AND sc1.cid=2 AND sc1.score<60 AND EXISTS(SELECt 1 FROM score sc2 WHERe sc2.sid=s.id AND sc2.cid=2 AND sc2.score<60 AND sc2.month=sc1.month+1) AND EXISTS(SELECt 1 FROM score sc3 WHERe sc3.sid=s.id AND sc3.cid=2 AND sc3.score<60 AND sc3.month=sc1.month+2);
注：如果上题使用"in"命令，不能获得全部分数
4、查询出现连续三个月数学成绩不及格中，每次成绩都有进步的学生
SELECt s.* ,sc1.score ,sc1.month FROM student s, score sc1 WHERe sc1.sid=s.id AND sc1.cid=2 AND sc1.score<60 AND
sc1.score NOT IN(SELECt sc2.score FROM score sc2 WHERe sc2.sid=s.id AND sc2.cid=2 AND sc2.score<60 AND sc2.month=sc1.month+1) AND
sc1.score NOT IN(SELECt sc3.score FROM score sc3 WHERe sc3.sid=s.id AND sc3.cid=2 AND sc3.score<60 AND sc3.month=sc1.month+2)
ORDER BY sc1.score DESC
5、查询各个月总成绩最高的学生及总分（成绩并列则都显示,包括所在班级）
SELECt m.* FROM (SELECt sc.month,sc.sid,SUM(sc.score) AS score FROM score sc GROUP BY sc.month,sc.sid ORDER BY sc.month) m,
(SELECt m.month,MAX(m.score) AS score FROM (SELECt sc.month,sc.sid,SUM(sc.score) AS score FROM score sc GROUP BY sc.month,sc.sid ORDER BY sc.month) m GROUP BY m.month) mx
WHERe m.month=mx.month AND m.score=mx.score ORDER BY m.month;
6、查询各个月总成绩最高低的学生及总分（成绩并列则都显示,包括所在班级）
SELECt m.* FROM (SELECt sc.month,sc.sid,SUM(sc.score) AS score FROM score sc GROUP BY sc.month,sc.sid ORDER BY sc.month) m,
(SELECt m.month,MIN(m.score) AS score FROM (SELECt sc.month,sc.sid,SUM(sc.score) AS score FROM score sc GROUP BY sc.month,sc.sid ORDER BY sc.month) m GROUP BY m.month) mx
WHERe m.month=mx.month AND m.score=mx.score ORDER BY m.month
7、学生可以报名参加课外活动（extra），课外活动和地点（position）是多对多的关系（rel_extra_position），课外活动分为两类（extra.type），学生允许参加课外活动数量有最大和最小限制（rule），要求同一学生的所有课外活动在同一地点，查询每个学生课外活动可能的地点
SELECt re.sid,re.position_id FROM (SELECt s.`id` AS sid,r.`type`,vc.position_id FROM student s, rule r, (SELECt v.type, v.position_id, COUNT(*) AS ct FROM(SELECt e.id AS extra_id,e.type,p.id AS position_id FROM extra e,POSITION p,rel_extra_position r WHERe e.id=r.extra_id AND p.id=r.position_id) v GROUP BY v.type, v.position_id) vc WHERe s.`id`=r.`sid` AND r.type=vc.type AND vc.ct>= r.min) re GROUP BY re.sid,re.position_id HAVINg COUNT(*)>1;
8、查询每个老师每周的平均考勤时间（attendance）
SELECt a.teacher_id, WEEK(STR_TO_DATE(a.date_mark,'%Y-%m-%d')),AVG(a.attendance_time) FROM attendance a GROUP BY a.teacher_id, WEEK(STR_TO_DATE(a.date_mark,'%Y-%m-%d'));
9、查询总成绩
SELECt s.id, COUNT(*) AS 选课总数, SUM(sc.score) AS 总成绩,
SUM(CASE WHEN sc.cid = 01 THEN score ELSE NULL END) AS score_01,
SUM(CASE WHEN sc.cid = 02 THEN score ELSE NULL END) AS score_02,
SUM(CASE WHEN sc.cid = 03 THEN score ELSE NULL END) AS score_03
FROM student s, score sc
WHERe s.id = sc.sid
GROUP BY s.id
10、查询各科成绩最高分、最低分和平均分
SELECt c.id AS 课程号, c.course_name AS 课程名称, COUNT(*) AS 选修人数,
MAX(sc.score) AS 最高分, MIN(sc.score) AS 最低分, AVG(sc.score) AS 平均分,
SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,
SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,
SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,
SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率
FROM score sc, course c
WHERe sc.cid = c.id
GROUP BY c.id
ORDER BY COUNT(*) DESC, c.id ASC
11、检索至少选修两门课程的学生学号
SELECt sc.sid, COUNT(sc.cid) AS cc FROM score sc
GROUP BY sc.sid
HAVINg cc>=12;
12、检索各科前三名的同学
SELECt sid,cid,score
FROM score sc_1
WHERe (
SELECt COUNT(100) FROM score sc_2
WHERe sc_1.cid = sc_2.cid
AND sc_2.score>=sc_1.score)<=3
ORDER BY sc_1.cid,sc_1.score DESC
