mysql 多表查询

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

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

Thinking：
课程名称和老师姓名存放在不同表中所以用到多表查询
每个课程都有一个对应老师的id，so course.teacher_id = teacher.tid 来过滤数据

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

SELECT
	student.sname,
	avg_num 
FROM
	student
INNER JOIN ( 
	SELECT 
		student_id, 
		avg( num ) AS avg_num 
	FROM 
		score 
		ROUP BY 
		student_id 
	HAVING avg( num ) > 80 ) AS t1 
ON t1.student_id = student.sid;

Thinking：
在成绩表中查询每个学生的平均成绩，然后取平均的成绩，so need to use group by and having，avg()
在成绩表中有对应学生成绩的id，通过学生的id分组，并且分组后的数据，再取每个学生成绩的平均值，并且返回平均成绩大于80的学生id和平均成绩
既然得到了平均成绩大于80的学生id，接下来就是从学生表中取出对应的学生姓名通过学生的id

3.查询没有报李平老师课的学生姓名

SELECT
	student.sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id IN ( 
			SELECT 
				course.cid 
			FROM 
				course 
			INNER JOIN 
				teacher 
			ON course.teacher_id = teacher.tid 
			WHERE tname = '李平老师' )
	);

Thinking~
第一查：查询出李平老师的课程并返回课程的所有id 返回的是多个课程id
第二查：从返回的多个课程id中，查找出报这几个课程的学生id，返回的是多个学生的id,这学生有重复的，给一个去重复的操作，⚠️返回的是报名学生的id
第三查：通过学生表的id从返回的学生id数据中查询出不存在的id即可

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

SELECT
	student.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 
	);

Thinking~
1.通过物理和体育关键词，查询出两门课程的ID
2.成绩表中有每个学生对应的没门课程的成绩，so 需要学生进行分组查询，并且过滤出选择了其中1们课程的学生id，并返回学生的id
3.在学生表中查询出相对应的学生的姓名即可

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

SELECT
	student.sname,
	class.caption 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	sid IN ( 
		SELECT student_id 
		FROM score 
		WHERE num < 60 
		GROUP BY student_id 
		HAVING COUNT( course_id ) >= 2 );

Thinking~
1.查询出出分数小于60的学生id，每个学生的多门课程，so 对分数小于60进行分组查询，并且过滤出挂科超过2门的学生id
2.我们需要用到两个表内的内容，so 多表查询，先更具sid查询出不及格的学生，然后通过这个学生的sid查询出他所在的班级名称

6.查询学生表中男女生各有多少人

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

Thinking~
男女分组查询，并且统计count()人数

7.查询物理成绩等于100的学生的姓名

-- one
SELECT
	student.sname 
FROM
	student 
WHERE
	sid IN ( 
		SELECT student_id 
		FROM score 
		WHERE course_id = ( 
			SELECT cid 
			FROM course 
			WHERE cname = '物理' ) 
		AND num >= 100 );
-- two
SELECT sname FROM student WHERE sid IN (
	SELECT student_id
	FROM score
	INNER JOIN course on score.course_id = course.cid
	WHERE
		course.cname = '物理'
	AND score.num = 100
);

Thinking~
先查询出分数100的物理课程学生的id
然后通过返回的id，查询出学生的姓名

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

SELECT
	student.sid,
	student.sname,
	t1.course_num,
	t1.total_num 
FROM
	student
LEFT JOIN ( 
	SELECT student_id, COUNT( course_id ) AS course_num, sum( num ) AS total_num 
	FROM score 
	GROUP BY student_id ) 
AS t1 ON student.sid = t1.student_id;

Thinking~
查询已经选择了课程学生，并进行分组查询对学生的id，同时用聚合函数算出课程数和总成绩
学生表中还有没有选择课程的学生，所以这里就用左连接来查询

待更新……

用到的sql

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;