发如雪SQL Based on practice（二）

发如雪 - 周杰伦

作词：方文山

作曲：周杰伦

编曲：林迈可

狼牙月 伊人憔悴

我举杯 饮尽了风雪

是谁打翻前世柜 惹尘埃是非

缘字诀 几番轮回

你锁眉 哭红颜唤不回

纵然青史已经成灰 我爱不灭

繁华如三千东流水

我只取一瓢爱了解

只恋你化身的蝶

你发如雪 凄美了离别

我焚香感动了谁

邀明月 让回忆皎洁

爱在月光下完美

你发如雪 纷飞了眼泪

我等待苍老了谁

红尘醉 微醺的岁月

我用无悔 刻永世爱你的碑

你发如雪 凄美了离别

我焚香感动了谁

邀明月 让回忆皎洁

爱在月光下完美

你发如雪 纷飞了眼泪

我等待苍老了谁

红尘醉 微醺的岁月

狼牙月 伊人憔悴

我举杯 饮尽了风雪

是谁打翻前世柜 惹尘埃是非

缘字诀 几番轮回

你锁眉 哭红颜唤不回

纵然青史已经成灰 我爱不灭

繁华如三千东流水

我只取一瓢爱了解

只恋你化身的蝶

你发如雪 凄美了离别

我焚香感动了谁

邀明月 让回忆皎洁

爱在月光下完美

你发如雪 纷飞了眼泪

我等待苍老了谁

红尘醉 微醺的岁月

我用无悔 刻永世爱你的碑

你发如雪 凄美了离别

我焚香感动了谁

邀明月 让回忆皎洁

爱在月光下完美

你发如雪 纷飞了眼泪

我等待苍老了谁

红尘醉 微醺的岁月

你发如雪 凄美了离别

我焚香感动了谁

邀明月 让回忆皎洁

爱在月光下完美

你发如雪 纷飞了眼泪

我等待苍老了谁

红尘醉 微醺的岁月

我用无悔 刻永世爱你的碑

啦儿啦儿啦

啦儿啦 啦儿啦 啦儿啦儿啊

铜镜映无邪 扎马尾

你若撒野 今生我把酒奉陪

啦儿啦 啦儿啦 啦儿啦儿啦

啦儿啦 啦儿啦 啦儿啦儿啊

铜镜映无邪 扎马尾

你若撒野 今生我把酒奉陪

---

/*学生表：student [学号,学生姓名,出生年月,性别]
成绩表：score [学号,课程号,成绩]
课程表：course [课程号,课程名称,教师号]
教师表：teacher [教师号,教师姓名)*/

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '数学老师-杰斯');
INSERT INTO `teacher` VALUES ('02', '语文老师-盲僧');
INSERT INTO `teacher` VALUES ('03', '英语老师-菲欧娜');
 
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '流浪法师-瑞兹', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '探险家-EZ', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '疾风剑豪-亚瑟', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '迅捷斥候-提莫', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '黑暗之女-安妮', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '战争女神-希维尔', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '光辉女郎-拉克丝', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '放逐之刃-锐雯', '1990-01-20', '女');
 
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `s_score` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', 80);
INSERT INTO `score` VALUES ('01', '02', 90);
INSERT INTO `score` VALUES ('01', '03', 99);
INSERT INTO `score` VALUES ('02', '01', 70);
INSERT INTO `score` VALUES ('02', '02', 60);
INSERT INTO `score` VALUES ('02', '03', 80);
INSERT INTO `score` VALUES ('03', '01', 80);
INSERT INTO `score` VALUES ('03', '02', 80);
INSERT INTO `score` VALUES ('03', '03', 80);
INSERT INTO `score` VALUES ('04', '01', 50);
INSERT INTO `score` VALUES ('04', '02', 30);
INSERT INTO `score` VALUES ('04', '03', 20);
INSERT INTO `score` VALUES ('05', '01', 76);
INSERT INTO `score` VALUES ('05', '02', 87);
INSERT INTO `score` VALUES ('06', '01', 31);
INSERT INTO `score` VALUES ('06', '03', 34);
INSERT INTO `score` VALUES ('07', '02', 89);
INSERT INTO `score` VALUES ('07', '03', 98);
 
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
 

-- 6道SQL面试题
-- 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

-- 2、查询平均成绩大于60分的学生的学号和平均成绩

-- 3、查询所有学生的学号、姓名、选课数、总成绩

-- 4、查询姓“刘”的老师的个数

-- 5、查询没学过“数学老师-杰斯”老师课的学生的学号、姓名

-- 6、查询课程编号为“02”的总成绩

--各个表的数据情况
SELECT * from student;
select * from score;
SELECT * from course;
select * from teacher;
-- 先全数据关联一下看看
select * from student stu,score sc,course c,teacher t 
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id;

1.SELECT a.c_id from 
(select * from score where c_id ='01') a,
(select * from score where c_id ='02') b
where a.s_id = b.s_id and a.s_score > b.s_score;
 
2.SELECT score.s_id,avg(s_score) from score group by s_id HAVING  avg(s_score)>60;
 
3.SELECT b.s_name,sum(s_score),count(c_id) from score a, student b where a.s_id = b.s_id group by a.s_id 
 
4.select count(*) from teacher where t_name like '刘%'
 
5.select * from student where s_id not in(
select DISTINCT(stu.s_id) from student stu,score sc,course c,teacher t 
where stu.s_id=sc.s_id and sc.c_id =c.c_id and c.t_id = t.t_id and t.t_name != '数学老师-杰斯');
 
6.select SUM(sc.s_score) from score sc where c_id = '02';

 

1、SQL语句完整结构：
SELECT...,...,...(存在聚合函数)
FROM...（LEFT / RIGHT）JOIN ...ON 多表的连接条件
(LEFT / RIGHT)JOIN ...ON
WHERE ...(WHERE子句不能使用聚合函数)
GROUP BY ...,...
HAVING （HAVING子句可以包含聚合函数的过滤条件也可以不包含）
ORDER BY ...,...(ASC / DESC)
LIMIT ...,...;
 
2、SQL语句的执行顺序
FROM …,…-> ON -> (LEFT/RIGHT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT->ORDER BY. -> LIMIT
 
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）select: 展示哪些字段列表
（7）order by：排序
（8）limit：分页
 
2.1 笛卡尔积产生的条件
1、省略了on的连接条件
2、所有表的所有行数据都相连
3、连接条件无效
 
3、WHERE和HAVING的区别
HAVING
1、HAVING子句可以包含聚合函数的过滤条件也可以不包含，通常与GROUP BY连用。
2、HAVING先进行计算，然后再过滤。
 
WHERE
1、WHERE子句不能使用聚合函数，可以与GROUP BY连用也可以不连用
2、WHERE是先进行约束，然后再进行计算。
 
3.1、优化HAVING的处理速度
当HAVING的逻辑中有可以使用WHERE筛选，优先使用WHERE筛选，这样可以减少HAVING的过滤查询。
 
4、ON和WHERE的区别
首先，内连接情况下，ON与WHERE子句是等效的。
外连接情况下有以下区别：
1、ON限制条件的发生时间比WHERE早。
2、ON在进行外部连接前，根据约束条件筛选数据。
3、WHERE在外部连接后，根据约束条件筛选数据。