SQL经典问题四表查询(教师，学生，成绩，课程表)---MySQL版
一. 数据库表结构
/*
Navicat Premium Data Transfer
Source Server : local
Source Server Type : MySQL
Source Server Version : 50722
Source Host : localhost:3306
Source Schema : default
Target Server Type : MySQL
Target Server Version : 50722
File Encoding : 65001
Date: 04/03/2019 18:25:56
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(20) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`),
KEY `tid` (`tid`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;;
-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '外语', 3);
COMMIT;
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
UNIQUE KEY `sid、cid共同唯一性` (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;
-- ----------------------------
-- Records of sc
-- ----------------------------
BEGIN;
INSERT INTO `sc` VALUES (1, 1, 90);
INSERT INTO `sc` VALUES (1, 2, 80);
INSERT INTO `sc` VALUES (1, 3, 90);
INSERT INTO `sc` VALUES (2, 1, 70);
INSERT INTO `sc` VALUES (2, 2, 60);
INSERT INTO `sc` VALUES (2, 3, 80);
INSERT INTO `sc` VALUES (3, 1, 80);
INSERT INTO `sc` VALUES (3, 2, 80);
INSERT INTO `sc` VALUES (3, 3, 80);
INSERT INTO `sc` VALUES (4, 1, 50);
INSERT INTO `sc` VALUES (4, 2, 30);
INSERT INTO `sc` VALUES (4, 3, 20);
INSERT INTO `sc` VALUES (5, 1, 76);
INSERT INTO `sc` VALUES (5, 2, 87);
INSERT INTO `sc` VALUES (6, 1, 31);
INSERT INTO `sc` VALUES (6, 3, 34);
INSERT INTO `sc` VALUES (7, 2, 89);
INSERT INTO `sc` VALUES (7, 3, 98);
COMMIT;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(20) DEFAULT NULL,
`sage` date DEFAULT NULL,
`ssex` enum('男','女') DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;;
-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '赵雷', '1992-01-01', '男');
INSERT INTO `student` VALUES (2, '钱电', '1993-12-21', '男');
INSERT INTO `student` VALUES (3, '孙风', '1994-05-20', '男');
INSERT INTO `student` VALUES (4, '李云', '1993-08-06', '男');
INSERT INTO `student` VALUES (5, '周梅', '1995-12-01', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1993-07-01', '女');
INSERT INTO `student` VALUES (8, '王菊', '1995-01-20', '女');
COMMIT;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;;
-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
二. Q&A 所有结果均通过验证
/* mysql四表查询 */
-- no.1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT
s.sid,
s.sname,
s.sage,
s.ssex,
sc1.score 语文成绩,
sc2.score 数学成绩
FROM
student s,
sc sc1,
sc sc2
WHERE
s.sid = sc1.sid
AND sc1.sid = sc2.sid
AND sc1.cid = 1
AND sc2.cid = 2
AND sc1.score > sc2.score;
-- no.2 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT
sc.sid,
s.sname,
avg( sc.score ) 平均成绩
FROM
student s,
sc
WHERE
s.sid = sc.sid
GROUP BY
sc.sid
HAVING
avg( sc.score ) > 60;
-- no.3 查询名字中含有"风"字的学生信息 done
SELECT
sid,
sname,
sage,
ssex
FROM
student
WHERE
sname LIKE '%风%';
-- no.4 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
s.sname,
sc.score
FROM
student s,
sc,
course
WHERE
s.sid = sc.sid
AND sc.cid = ( SELECT cid FROM course WHERE cname = '数学' )
AND sc.score < 60
AND sc.cid = course.ci;
-- no.5 查询没学过"张三"老师授课的同学的信息
SELECT
s.*
FROM
student s
WHERE
s.sid NOT IN ( SELECT sid FROM sc WHERE cid = ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE tname = '张三' ) ) );
-- no.6 查询学过"张三"老师授课的同学的信息
SELECT
s.*
FROM
student s
WHERE
s.sid IN ( SELECT sid FROM sc WHERE cid = ( SELECT cid FROM course WHERE tid = ( SELECT tid FROM teacher WHERE tname = '张三' ) ) );
SELECT
s.*
FROM
student s,
course c,
sc,
teacher t
WHERE
tname = '张三'
AND c.tid = t.tid
AND sc.cid = c.cid
AND s.sid = sc.sid;
-- no.7 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
s.*
FROM
student s,
sc sc1,
sc sc2
WHERE
s.sid = sc1.sid
AND sc1.sid = sc2.sid
AND sc1.cid = 1
AND sc2.cid = 2;
-- no.8 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT
s.*
FROM
student s,
sc
WHERE
s.sid = sc.sid
AND sc.cid = 1
AND s.sid NOT IN ( SELECT sid FROM sc WHERE cid = 2 );
-- no.9 查询没有学全所有课程的同学的信息
SELECT
s.*
FROM
student s,
sc
WHERE
s.sid = sc.sid
GROUP BY
sc.sid
HAVING
count( sc.sid ) < ( SELECT count( cid ) FROM course );
-- no.10 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT
s.*
FROM
student s,
sc
WHERE
s.sid = sc.sid
AND sc.cid IN ( SELECT cid FROM sc WHERE sid = 1 )
AND sc.sid <> 1;
-- no.11 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT
s.*
FROM
student s,
sc
WHERE
s.sid = sc.sid
AND sc.cid IN ( SELECT cid FROM sc WHERE sid = 1 )
AND sc.sid <> 1
GROUP BY
sc.sid
HAVING
count( sc.cid ) = ( SELECT count( 1 ) FROM sc WHERE sid = 1 );
-- or
SELECT
*
FROM
student
WHERE
sid IN (
SELECT
sc.sid
FROM
sc
WHERE
sc.cid IN ( SELECT cid FROM sc WHERE sid = 1 )
AND sc.sid <> 1
GROUP BY
sc.sid
HAVING
count( sc.cid ) = ( SELECT count( 1 ) FROM sc WHERE sid = 1 )
);
-- no.12 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
*
FROM
student
WHERE
sid NOT IN (
SELECT
sc.sid
FROM
sc,
course c
WHERE
sc.cid = c.cid
AND c.tid = ( SELECT tid FROM teacher WHERE tname = '张三' )
);
-- no.13 查询出只有两门课程的全部学生的学号和姓名
SELECT
s.sid,
s.sname
FROM
student s,
sc
WHERE
s.sid = sc.sid
GROUP BY
sc.sid
HAVING
count( sc.cid ) = 2;
-- no.14 查询1993年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT
*
FROM
student
WHERE
sage > '1993-01-01'
AND sage < '1993-12-31';
-- or
SELECT
*
FROM
student
WHERE
sage LIKE '1993%';
-- no.15 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
cid,
avg( score )
FROM
sc
GROUP BY
cid
ORDER BY
avg( score ) DESC,
cid;
-- no.16 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
SELECT
s.sid,
s.sname,
c.cname,
sc.score
FROM
student s,
course c,
sc
WHERE
s.sid = sc.sid
AND sc.cid = c.cid
AND s.sid IN ( SELECT sid FROM sc GROUP BY sid HAVING min( score ) > 70 );
-- no.17 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
s.sid,
s.sname,
avg( score )
FROM
student s,
sc
WHERE
s.sid = sc.sid
GROUP BY
sc.sid
HAVING
avg( score ) > 85;
-- no.18 查询不及格的课程以及学生信息
SELECT
s.*,
c.cname,
sc.score
FROM
student s,
course c,
sc
WHERE
s.sid = sc.sid
AND sc.cid = c.cid
AND sc.score < 60; -- no.19 查询选修了课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT s.sid, s.sname FROM sc, student s WHERE cid = 1 AND score > 80
AND sc.sid = s.sid;
-- no.20 求每门课程的学生人数
SELECT
cid,
count( sid ) 课程人数
FROM
sc
GROUP BY
cid;
-- no.21 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
cid,
count( sid ) 课程人数
FROM
sc
GROUP BY
cid
HAVING
count( sid ) > 5
ORDER BY
count( sid ) DESC,
cid;
-- no.22 检索至少选修两门课程的学生学号
SELECT
sid
FROM
sc
GROUP BY
sid
HAVING
count( cid ) >= 2;
-- no.23 查询选修了全部课程的学生信息
SELECT
s.*
FROM
student s,
sc
WHERE
sc.sid = s.sid
GROUP BY
sc.sid
HAVING
count( sc.cid ) = ( SELECT count( 1 ) FROM course );
-- no.24 查询各学生的年龄
SELECT
sname,
TIMESTAMPDIFF( YEAR, sage, CURDATE( ) ) age
FROM
student;
-- no.25 查询本月过生日的学生
SELECT
*
FROM
student
WHERE
MONTH ( sage ) = MONTH ( now( ) );
-- no.26 查询下月过生日的学生
SELECT
*
FROM
student
WHERE
MONTH ( sage ) = MONTH ( now( ) ) + 1;
