java多表联合查询_多表联合查询练习

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

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