MySQL多表查询语法
MySQL多表查询语法
多表查询准备
数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
笛卡尔积
select * from emp,dep;
- 运行得到下面的表格
id name sex age dep_id id name 1 jason male 18 200 200 技术 1 jason male 18 200 201 人力资源 1 jason male 18 200 202 销售 1 jason male 18 200 203 运营 1 jason male 18 200 205 财务 2 dragon female 48 201 200 技术 2 dragon female 48 201 201 人力资源 2 dragon female 48 201 202 销售 2 dragon female 48 201 203 运营 2 dragon female 48 201 205 财务 3 kevin male 18 201 200 技术 3 kevin male 18 201 201 人力资源 3 kevin male 18 201 202 销售 3 kevin male 18 201 203 运营 3 kevin male 18 201 205 财务 4 nick male 28 202 200 技术 4 nick male 28 202 201 人力资源 4 nick male 28 202 202 销售 4 nick male 28 202 203 运营 4 nick male 28 202 205 财务 5 owen male 18 203 200 技术 5 owen male 18 203 201 人力资源 5 owen male 18 203 202 销售 5 owen male 18 203 203 运营 5 owen male 18 203 205 财务 6 jerry female 18 204 200 技术 6 jerry female 18 204 201 人力资源 6 jerry female 18 204 202 销售 6 jerry female 18 204 203 运营 6 jerry female 18 204 205 财务
可以看到这个表格十分的庞大，相当于将两表中所有的记录都两两组合起来
当然我们可以通过筛选条件where来让emp.dep_id与dep.id相匹配，但是这个筛选是在这个庞大的表的基础上得到的，如果表数据很多，那么这个笛卡尔集就是记录数量级平方的执行次数，十分容易导致崩溃。
所以我们还有其他两种拼接表的操作。
连表查询
-
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 全连接
将左连接表和右连接表的结果并起来。
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;
ps：union是通过将两个select的结果并集得到了一张表，而select语法就是会返回它的表结果，这个表结果还可以和别的表连接。
子查询
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件，这个条件常用于
- 作为一个单列集合被拿来成员运算
- 作为一个表来被from查询
# 将员工表中等于名字jason的dep_id作为查询结果与dep表中的id值做比对，最终拿dep.name字段
# 于是拿到jason所在部门的名字
select name from dep where id=(select dep_id from emp where name='jason');
多表查询实战
数据准备
/*
数据导入：
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;
查询所有的课程的名称以及对应的任课老师姓名
分析：涉及两张有直接联系的表，直接使用连表查询即可。
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON teacher_id = teacher.tid;
查询平均成绩大于七十分的同学的姓名和平均成绩
分析：学生表和成绩表也是直接联系的，直接用连表查询，后续可以按照姓名划分求平均分。
# 连表操作
SELECT
sname,
avg( num ) AS avg_num
FROM
student
INNER JOIN score ON student.sid = score.student_id
GROUP BY
sname
HAVING
avg( num ) > 70;
查询没有报李平老师课的学生名
分析：学生表和老师表不是直接联系的，适合用子查询逐步的接近一侧。
SELECT sname FROM student
WHERE sid NOT IN
(SELECT student_id FROM score WHERE course_id IN
(SELECT cid FROM course WHERE teacher_id IN
(SELECT tid FROM teacher WHERE tname = '李平老师'
)
)
);
查询没有同时选择物理和生物的学生姓名
SELECT
student.sname
FROM
student
INNER JOIN score ON student.sid = score.student_id
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname IN ( '物理', '生物' )
GROUP BY
student.sname
HAVING
COUNT( score.course_id )= 1;
# 答案2
SELECT
sname
FROM
student
WHERE
sid in(
SELECT student_id FROM score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname IN ( '物理', '生物' )
GROUP BY
student_id
HAVING
COUNT( score.course_id )= 1);
# 答案3
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 )= 1);
查询挂科超过两门的学生及所在班级
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);
