多表联合查询练习

时间：2021-09-29 00:26:26

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

多表联合查询练习多表联合查询练习
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', '三八');
View Code

就四张表。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

 

标签：

相关文章