多表联合查询练习

面试的时候被多表查询考倒了，回来练习一下，以作备用。

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL COMMENT '课程编号',
  `cname` varchar(10) NOT NULL COMMENT '课程名称',
  `tid` varchar(10) NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
INSERT INTO `course` VALUES ('04', '物理', '04');

-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` varchar(10) NOT NULL COMMENT '学生编号',
  `cid` varchar(10) NOT NULL COMMENT '课程编号',
  `score` decimal(18,1) NOT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('01', '01', '80.0');
INSERT INTO `score` VALUES ('01', '02', '90.0');
INSERT INTO `score` VALUES ('01', '03', '99.0');
INSERT INTO `score` VALUES ('02', '01', '70.0');
INSERT INTO `score` VALUES ('02', '02', '60.0');
INSERT INTO `score` VALUES ('02', '03', '80.0');
INSERT INTO `score` VALUES ('03', '01', '80.0');
INSERT INTO `score` VALUES ('03', '02', '80.0');
INSERT INTO `score` VALUES ('03', '03', '80.0');
INSERT INTO `score` VALUES ('04', '01', '50.0');
INSERT INTO `score` VALUES ('04', '02', '30.0');
INSERT INTO `score` VALUES ('04', '03', '20.0');
INSERT INTO `score` VALUES ('05', '01', '76.0');
INSERT INTO `score` VALUES ('05', '02', '87.0');
INSERT INTO `score` VALUES ('06', '01', '31.0');
INSERT INTO `score` VALUES ('06', '03', '34.0');
INSERT INTO `score` VALUES ('07', '02', '89.0');
INSERT INTO `score` VALUES ('07', '03', '98.0');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` varchar(10) NOT NULL COMMENT '学生学号',
  `sname` varchar(10) NOT NULL COMMENT '学生姓名',
  `sage` datetime NOT NULL COMMENT '学生年龄',
  `ssex` varchar(10) NOT NULL COMMENT '学生性别',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-06 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` varchar(10) NOT NULL COMMENT '教师编号',
  `tname` varchar(10) DEFAULT NULL COMMENT '教师姓名',
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
INSERT INTO `teacher` VALUES ('04', '三八');

就四张表。course，student，teacher，score。

。

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

1.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score,c.score from student a , score b , score c where a.sid = b.sid and a.sid = c.sid and b.cid = '01' and c.cid = '02' and b.score > c.score

1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程 的情况(不存在时显示为 null)

select a.* , b.score ,c.score from student a left join score b on a.sid = b.sid and b.cid = '01' left join score c on a.sid = c.sid and c.cid = '02' where b.score>IFNULL(c.score,0)

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.sid , a.sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , score b where a.sid = b.sid group by a.sid , a.sname having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.sid

--group by 和having 解释：前提必须了解sql语言中一种特殊的函数：聚集函数，
--例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。 
--WHERE关键字在使用集合函数时不能使用，所以在集合函数中加上了HAVING来起到测试查询结果是否符合条件的作用。

--需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

--cast(avg(b.score) as decimal(18,2))解释：Cast(字段名 as 转换的类型 )

 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（注意成绩为null的学生，使用ifnull（）函数）

select a.sid , a.Sname , IFNULL(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join score b on a.sid = b.sid group by a.sid , a.sname having ifnull(cast(avg(b.score) as decimal(18,2)),0) < 60 order by a.Sid

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.sid AS 学生编号, a.Sname as 学生姓名, count(b.cid) 选课总数, sum(score) 所有课程的总成绩 from Student a left join score b on a.sid = b.sid group by a.sid,a.Sname order by a.sid