MySQL数据库——多表查询

一、多表查询的两种方式

1.1连表操作

'''
inner join	内连接
只连接两张表中公有的数据部分
'''
select * from emp inner join dep on emp.dep_id=dep.id;
'''
left join	左连接
以左表为基准，展示左表所有的数据，如果没有对应的项用NULL填充
'''
select * from emp left join dep on emp.dep_id=dep.id;
'''
right join	右连接
以右表为基准，展示右表所有的数据，如果没有对应项则用NULL填充
'''
select * from emp right join dep on emp.dep.id=dep.id;
'''
union	全连接
以左右为基准，展示所有的数据，各自没有的都以NULL填充
'''
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
'''
学会了链表操作之后也就可以连接N多张表
	思路：
		将拼接之后的表起别名，当成一张表再去和其他表拼接，再起别名，再去和其他的表拼接，依次循环往复即可
'''

1.2子查询

'''
将一条SQL语句用（）括起来，当成另外一条SQL语句的查询的条件
要求：
	求姓名是jason的员工所在的部门名称
思想：
	分步操作
'''
# 步骤1：现根据jason获取部门编号
select dep_id from emp where name='jason';
# 步骤2：再根据部门编号获取部门名称
select name from dep where id=(select dep_id from emp where name='jason');
'''
总结：
	很多时候多表查询需要结合实际情况判断用那种，更多的时候是互相配合使用
'''

二、多表查询练习

2.1 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;

2.2练习内容

'''
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级
'''
# 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.1 先确定需要用的几张表（课程表、老师表）
# 1.2 预览表中数据、做到心中有数
SELECT * FROM course;
SELECT * FROM teacher;
# 1.3 确定多表查询的思路、连表、子查询、混合操作
SELECT
	teacher.tname,
	course.cname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid
# 2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 2.1 先确定需要用的几张表（学生表、分数表）
# 2.2 预览表中数据
SELECT * FROM student;
SELECT * FROM score;
# 2.3 根据已知条件，先求出每个同学的平均成绩
SELECT
	student_id,
	AVG( num ) 
FROM
	score 
GROUP BY
	student_id 
HAVING
	AVG( num )> 80;
# 2.4 确定最终的结果需要几张表（学生表、分数表）多表查询
SELECT
	student.sname,
	student_score.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, AVG( num ) AS avg_num FROM score GROUP BY student_id HAVING avg_num > 80 ) AS student_score ON student.sid = student_score.student_id;
# 3、查询没有报李平老师课的学生姓名
# 3.1 先确定需要用到几张表（老师表、课程表、学生表）
# 3.2 预览每张表的数据
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM student;
'''
3.3 确定思路：
	1:正向筛选
	2：筛选所有报了李平老师课程学生的id，然后取反
'''
# 获取李平老师教授的课程id
SELECT
	tid 
FROM
	teacher 
WHERE
	tname = '李平老师';

SELECT
	cid 
FROM
	course 
WHERE
	teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' );
# 根据课程id筛选出所有报了李平老师的学生id （distinct 去重）
SELECT DISTINCT student_id FROM score WHERE score.course_id IN (
SELECT
	cid 
FROM
	course 
WHERE
	teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ));
# 根据学生id在学生表中取反获取学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
	WHERE
	teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' )));
# 4、查询没有同时选修物理课程和体育课程的学生姓名
# 4.1 先确定需要的表（学生表、课程表）
# 4.2 预览表数据
SELECT * FROM student;
SELECT * FROM course;
# 4.3 筛选出物理和体育的cid 
SELECT cid FROM course WHERE cname IN ('物理','体育')
# 4.4 根据cid筛选出学生id
SELECT
	student_id 
FROM
	score 
WHERE
	course_id IN (
	SELECT
		cid 
	FROM
		course 
WHERE
	cname IN ( '物理', '体育' ))
# 4.4统计每个学生报的课程数，筛选出等于2的
SELECT
	student_id 
FROM
	score 
WHERE
	course_id IN (
	SELECT
		cid 
	FROM
		course 
WHERE
	cname IN ( '物理', '体育' ))
	GROUP BY student_id
	HAVING COUNT(course_id) = 2;
# 4.5 子查询获取学生姓名
SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
		cname IN ( '物理', '体育' )) 
	GROUP BY
		student_id 
	HAVING
	COUNT( course_id ) = 2 
	);
# 5、查询挂科超过两门(包括两门)的学生姓名和班级
# 5.1 先确定涉及到的表（学生表、班级表、分数表）
# 5.2 预览表数据
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM class;
# 5.3 筛选分数小于60的数据
SELECT num FROM score WHERE num<60;
# 5.4 统计每个学生小于60的次数
SELECT
	student_id,
	COUNT( course_id ) 
FROM
	score 
WHERE
	num < 60 
GROUP BY
	student_id;
# 5.5 筛选次数大于等于2的学生id
SELECT
	student_id,
	COUNT( course_id ) 
FROM
	score 
WHERE
	num < 60 
	GROUP BY student_id 
	HAVING COUNT( course_id ) >= 2;
# 5.6 连接班级与学生表，基于学生id筛选
SELECT
	student.sname,
	class.caption 
FROM
	student
	INNER JOIN class ON student.class_id = class.cid 
WHERE
	student.sid IN (SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT( course_id ) >= 2 );

三、小知识点补充

3.1 concat与concat_ws

'''
concat用于分组之前的字段拼接操作
'''
select concat(name,'|',sex) from emp;
'''
concat_ws用于拼接多个字段并且中间的连接符一致
'''
select concat_wa('|',name,sex,age) from emp;

3.2 exists

'''
判断是否存在
	sql2有结果的情况下才会执行sql1，否则不会执行sql1，返回空数据
	语法

SELECT column1 FROM t1 WHERE [conditions] and EXISTS (SELECT * FROM t2 );
说明

    括号中的子查询并不会返回具体的查询到的数据，只是会返回true或者false，如果外层sql的字段在子查询中存在则返回true，不存在则返回false
    即使子查询的查询结果是null，只要是对应的字段是存在的，子查询中则返回true，下面有具体的例子
'''
sql1 exists sql2;

3.3 表相关的SQL补充

# 重命名表名
alter table 表名 rename 新表名;
# 添加字段
alter table 表名 add 字段名 字段类型(长度) 约束条件;
# 修改字段
alter table 表名 add 字段名 字段类型(长度) 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型(长度) 约束条件 first;
# 删除字段
alter table 表名 change 旧字段名 新字段名 字段类型(长度) 约束条件;
alter table 表名 drop 字段名;