表查询之综合练习

综合练习

练习准备工作

init.sql文件内容

/*
 数据导入：
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50624
 Source Host           : localhost
 Source Database       : sqlexam

 Target Server Type    : MySQL
 Target Server Version : 50624
 File Encoding         : utf-8

 Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

从init.sql文件中导入数据

#准备表、记录
mysql> create database db1;
mysql> use db1;
mysql> source W:MYSQLmysql-5.6.47-winx64init.sql;  -- source 后面接sql文件的绝对路径

查看存在的表

mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| class         |
| course        |
| score         |
| student       |
| teacher       |
+---------------+

表内容

mysql> select * from class;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from score;
+-----+------------+-----------+-----+
| sid | student_id | course_id | num |
+-----+------------+-----------+-----+
|   1 |          1 |         1 |  10 |
|   2 |          1 |         2 |   9 |
|   5 |          1 |         4 |  66 |
|   6 |          2 |         1 |   8 |
|   8 |          2 |         3 |  68 |
|   9 |          2 |         4 |  99 |
|  10 |          3 |         1 |  77 |
|  11 |          3 |         2 |  66 |
|  12 |          3 |         3 |  87 |
|  13 |          3 |         4 |  99 |
|  14 |          4 |         1 |  79 |
|  15 |          4 |         2 |  11 |
|  16 |          4 |         3 |  67 |
|  17 |          4 |         4 | 100 |
|  18 |          5 |         1 |  79 |
|  19 |          5 |         2 |  11 |
|  20 |          5 |         3 |  67 |
|  21 |          5 |         4 | 100 |
|  22 |          6 |         1 |   9 |
|  23 |          6 |         2 | 100 |
|  24 |          6 |         3 |  67 |
|  25 |          6 |         4 | 100 |
|  26 |          7 |         1 |   9 |
|  27 |          7 |         2 | 100 |
|  28 |          7 |         3 |  67 |
|  29 |          7 |         4 |  88 |
|  30 |          8 |         1 |   9 |
|  31 |          8 |         2 | 100 |
|  32 |          8 |         3 |  67 |
|  33 |          8 |         4 |  88 |
|  34 |          9 |         1 |  91 |
|  35 |          9 |         2 |  88 |
|  36 |          9 |         3 |  67 |
|  37 |          9 |         4 |  22 |
|  38 |         10 |         1 |  90 |
|  39 |         10 |         2 |  77 |
|  40 |         10 |         3 |  43 |
|  41 |         10 |         4 |  87 |
|  42 |         11 |         1 |  90 |
|  43 |         11 |         2 |  77 |
|  44 |         11 |         3 |  43 |
|  45 |         11 |         4 |  87 |
|  46 |         12 |         1 |  90 |
|  47 |         12 |         2 |  77 |
|  48 |         12 |         3 |  43 |
|  49 |         12 |         4 |  87 |
|  52 |         13 |         3 |  87 |
+-----+------------+-----------+-----+
47 rows in set (0.00 sec)

mysql> select * from student;
+-----+--------+----------+--------+
| sid | gender | class_id | sname  |
+-----+--------+----------+--------+
|   1 | 男     |        1 | 理解   |
|   2 | 女     |        1 | 钢蛋   |
|   3 | 男     |        1 | 张三   |
|   4 | 男     |        1 | 张一   |
|   5 | 女     |        1 | 张二   |
|   6 | 男     |        1 | 张四   |
|   7 | 女     |        2 | 铁锤   |
|   8 | 男     |        2 | 李三   |
|   9 | 男     |        2 | 李一   |
|  10 | 女     |        2 | 李二   |
|  11 | 男     |        2 | 李四   |
|  12 | 女     |        3 | 如花   |
|  13 | 男     |        3 | 刘三   |
|  14 | 男     |        3 | 刘一   |
|  15 | 女     |        3 | 刘二   |
|  16 | 男     |        3 | 刘四   |
+-----+--------+----------+--------+
16 rows in set (0.00 sec)

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
5 rows in set (0.00 sec)

练习集

1、查询所有的课程的名称以及对应的任课老师姓名

SELECT
	c.cname,
	t.tname
FROM
	course AS c
INNER JOIN teacher AS t ON c.teacher_id = t.tid;

2、查询学生表中男女生各有多少人

SELECT
	gender,
	count(gender)
FROM
	student
GROUP BY
	gender;

3、查询物理成绩等于100的学生的姓名

SELECT
	st.sname
FROM
	student AS st
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score sc
		INNER JOIN course c ON sc.course_id = c.cid
		WHERE
			c.cname = "物理"
		AND sc.num = 100
	);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT
	st.sname,
	avg(num)
FROM
	student AS st
INNER JOIN score AS sc ON st.sid = sc.student_id
GROUP BY
	sc.student_id
HAVING
	avg(num) > 80;

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

SELECT
	st.sid,
	st.sname,
	count(course_id),
	sum(num)
FROM
	student AS st
INNER JOIN score AS sc ON st.sid = sc.student_id
GROUP BY
	sc.student_id;

SELECT
	st.sid,
	st.sname,
	sc.cc,
	sc.sn
FROM
	student AS st
LEFT JOIN (
	SELECT
		student_id,
		count(course_id) cc,
		sum(num) sn
	FROM
		score
	GROUP BY
		student_id
) sc ON st.sid = sc.student_id;

6、 查询姓李老师的个数

SELECT
	count(tname)
FROM
	teacher
WHERE
	tname LIKE "李%";

7、 查询没有报李平老师课的学生姓名

SELECT
	sname
FROM
	student
WHERE
	sid NOT IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				WHERE
					teacher_id = (
						SELECT
							tid
						FROM
							teacher
						WHERE
							tname = "李平老师"
					)
			)
	);

8、 查询物理课程比生物课程高的学生的学号

# 先拿到选择了生物和物理俩个课程的学生id
# 再拿到该学生的生物成绩 和 物理成绩
# 再进行比较,物理成绩比生物成绩高的过滤出来
# 最后返回该学生的id
SELECT
	t1.student_id
FROM
	(
		SELECT
			student_id,
			num AS snum
		FROM
			score
		WHERE
			course_id = (
				SELECT
					cid
				FROM
					course
				WHERE
					cname = "生物"
			)
	) AS t1
INNER JOIN (
	SELECT
		student_id,
		num AS wnum
	FROM
		score
	WHERE
		course_id = (
			SELECT
				cid
			FROM
				course
			WHERE
				cname = "物理"
		)
) AS t2 ON t2.student_id = t1.student_id
WHERE
	t1.snum < t2.wnum;

9、 查询没有同时选修物理课程和体育课程的学生姓名

SELECT
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				WHERE
					cname = "物理"
				OR cname = "体育"
			)
		GROUP BY
			student_id
		HAVING
			count(course_id) = 1
	)
OR sid NOT IN (SELECT student_id FROM score);

10、查询挂科超过两门(包括两门)的学生姓名和班级

SELECT
	st.sname,
	c.caption
FROM
	student AS st
INNER JOIN class AS c ON st.class_id = c.cid
INNER JOIN (
	SELECT
		student_id
	FROM
		score
	WHERE
		num < 60
	HAVING
		count(student_id) >= 2
) AS t1 ON t1.student_id = st.sid;

11 、查询选修了所有课程的学生姓名

SELECT
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		GROUP BY
			student_id
		HAVING
			count(course_id) = (SELECT count(cid) FROM course)
	);

12、查询李平老师教的课程的所有成绩记录

SELECT
	*
FROM
	score
WHERE
	course_id IN (
		SELECT
			cid
		FROM
			course
		WHERE
			teacher_id = (
				SELECT
					tid
				FROM
					teacher
				WHERE
					tname = "李平老师"
			)
	);

13、查询全部学生都选修了的课程号和课程名

SELECT
	cid,
	cname
FROM
	course
WHERE
	cid = ALL (
		SELECT
			course_id
		FROM
			score
		GROUP BY
			student_id
	);

14、查询每门课程被选修的次数

SELECT
	count(course_id)
FROM
	score
GROUP BY
	course_id;

15、查询只选修了一门课程的学生姓名和学号

SELECT
	sname,
	sid
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score
		GROUP BY
			student_id
		HAVING
			count(course_id) = 1
	);

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

SELECT DISTINCT
	num
FROM
	score
ORDER BY
	num DESC;

17、查询平均成绩大于85的学生姓名和平均成绩

SELECT
	st.sname,
	avg(num)
FROM
	student AS st
INNER JOIN score AS sc ON st.sid = sc.student_id
GROUP BY
	sc.student_id
HAVING
	avg(num) > 85;

18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT
	st.sname,
	sc.num
FROM
	student AS st
INNER JOIN (
	SELECT
		*
	FROM
		score
	WHERE
		course_id IN (
			SELECT
				cid
			FROM
				course
			WHERE
				cname = "生物"
		)
) AS sc ON st.sid = sc.student_id
WHERE
	sc.num < 60;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT
	st.sname
FROM
	student AS st
INNER JOIN (
	SELECT
		*
	FROM
		score
	WHERE
		course_id IN (
			SELECT
				cid
			FROM
				course
			WHERE
				teacher_id IN (
					SELECT
						tid
					FROM
						teacher
					WHERE
						tname = "李平老师"
				)
		)
) AS sc ON st.sid = sc.student_id
GROUP BY
	st.sid
ORDER BY
	avg(num) DESC
LIMIT 1;

20、查询每门课程成绩最好的前两名s学生姓名(!!!)

SELECT
	sname
FROM
	student
WHERE
	sid IN (
		SELECT
			student_id
		FROM
			score sco
		INNER JOIN (
			SELECT
				sc.sid ss
			FROM
				score sc
			LEFT JOIN (
				SELECT
					sid,
					(
						SELECT
							num
						FROM
							score AS s2
						WHERE
							s2.course_id = s1.course_id
						ORDER BY
							num DESC
						LIMIT 0,
						1
					) AS first_num,
					(
						SELECT
							num
						FROM
							score AS s2
						WHERE
							s2.course_id = s1.course_id
						ORDER BY
							num DESC
						LIMIT 1,
						1
					) AS second_num
				FROM
					score AS s1
			) AS t1 ON sc.sid = t1.sid
			WHERE
				sc.num <= t1.first_num
			AND sc.num >= t1.second_num
		) AS t1 ON t1.ss = sco.sid
	);

21、查询不同课程但成绩相同的学号，课程号，成绩

SELECT DISTINCT
	sc1.student_id,
	sc2.student_id,
	sc1.course_id,
	sc2.course_id,
	sc1.num
FROM
	score AS sc1
INNER JOIN score AS sc2 ON sc1.num = sc2.num
AND sc1.course_id != sc2.course_id;

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称； 1 3

SELECT
	t2.ts,
	co.cname
FROM
	course AS co
RIGHT JOIN (
	SELECT
		t1.sname AS ts,
		sc1.course_id AS sco
	FROM
		score AS sc1
	RIGHT JOIN (
		SELECT
			sname,
			sid
		FROM
			student
		WHERE
			sid NOT IN (
				SELECT DISTINCT
					student_id
				FROM
					score
				WHERE
					score.course_id IN (
						SELECT
							cid
						FROM
							course
						INNER JOIN teacher ON course.teacher_id = teacher.tid
						WHERE
							tname = '李平老师'
					)
			)
	) AS t1 ON t1.sid = sc1.student_id
) AS t2 ON t2.sco = co.cid;

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名个选修课程的个数；

SELECT
	student_id,
	sname,
	count(course_id)
FROM
	score
LEFT JOIN student ON score.student_id = student.sid
WHERE
	student_id != 1
AND course_id IN (
	SELECT
		course_id
	FROM
		score
	WHERE
		student_id = 1
)
GROUP BY
	student_id;

24、任课最多的老师中学生单科成绩最高的学生姓名

SELECT
	sname
FROM
	student
WHERE
	sid = (
		SELECT
			student_id
		FROM
			score
		WHERE
			course_id IN (
				SELECT
					cid
				FROM
					course
				WHERE
					teacher_id = (
						SELECT
							teacher_id
						FROM
							course
						GROUP BY
							teacher_id
						ORDER BY
							count(teacher_id) DESC
						LIMIT 1
					)
			)
		ORDER BY
			num DESC
		LIMIT 1
	);