（6）多表查询

表之间关系

一对一

一对多关系

多对多关系

一个学生有多个老师，一个老师也可以有多个学生
创建老师表

CREATE TABLE teacher(
	tid int PRIMARY KEY,
	name VARCHAR(50)
);

创建学生表

CREATE TABLE stu (
	sid INT PRIMARY KEY,
	name VARCHAR(50)
);

创建关系表

CREATE TABLE tea_stu_rel (
	tid INT,
	sid	INT
);

添加外键

ALTER TABLE tea_stu_rel ADD CONSTRAINT FOREIGN KEY(tid) REFERENCES teacher(tid);
ALTER TABLE tea_stu_rel ADD CONSTRAINT FOREIGN KEY(sid) REFERENCES stu(sid);

为什么要拆分表，避免大量冗余数据的出现

多表查询

合并结果集

合并结果就是把两个select语句查的结果合并在一起
方式
UNION 合并时去除重复
  select * FROM 表1 UNIION SELECT * FROM 表2;
UNION ALL 合并时不去除重复
  select * FROM 表1 UNIION ALL SELECT * FROM 表2;
注意：被合并的两个结果，列数，列类型必须相同
举例：

create table A(name VARCHAR(10),score int);
create table B(name VARCHAR(10),score int);
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO B VALUES('a',10),('b',20),('c',30);

连接查询

连接查询，也叫跨表查询，需要关联多个表进行查询
笛卡尔集
假设 集合A={a,b},集合B={0,1,2}集合A=\{{} {a,b} \},集合B=\{{} {0,1,2} \}集合A={a,b},集合B={0,1,2}
那么两个集合的笛卡尔积是 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}\{{} (a,0),(a,1),(a,2),(b,0),(b,1),(b,2) \}{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
同时查询两张表select * from A,B;，就会出现笛卡尔集现象

select * from A,B;

查询时给表起别名

select * from 表名1 新名，表名2 新名2;

多表查询保存数据正确
查询时把主键和外键保持一致
99写法

select * from stu,tea_stu_rel where stu.sid = tea_stu_rel.sid;

内连接

等值连接

SELECT * from stu st 
INNER JOIN 
tea_stu_rel cfk ON st.sid = cfk.sid;

加入条件

SELECT * from stu st 
INNER JOIN tea_stu_rel cfk ON st.sid = cfk.sid WHERE cfk.tid <=1002;

更多条件

SELECT * from stu st 
INNER JOIN tea_stu_rel cfk ON st.sid = cfk.sid WHERE cfk.tid <=1002 AND cfk.tid > 999;

外连接

两张表 如下
stu_a   score_b

先看一下内连接返回结果

SELECT * FROM stu_a INNER JOIN score_b ON stu_a.id=score_b.sid;

左外连接 (LEFT JOIN)
两表满足条件相同的数据查出来，如果左边当中有不同的数据，也把左边表中的数据查出来

SELECT * FROM stu_a LEFT JOIN score_b ON stu_a.id=score_b.sid;

右连接(RIGHT JOIN)
后边表里的数据全部查出，左边表只查出满足的数据

多对多查询

插入第三张表

create TABLE course (
	cid int PRIMARY KEY,
	name varchar(50)
);

第一种写法 (99法)

SELECT st.`name`,sc.score,c.`name` 
FROM stu_a st, score_b sc,course c 
where st.id=sc.sid 
AND sc.cid=c.cid;

第二种写法 （内联法）

SELECT st.`name`,sc.score,c.`name` FROM stu_a st 
INNER JOIN score_b sc ON st.id=sc.sid
INNER JOIN course c on sc.cid=c.cid;

---

准备工作

创建emp表

CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` varchar(255) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `mgr` varchar(255) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `comm` double DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `emp` VALUES (7369, '孙悟空', '职员', '7902', '2010-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, '孙尚香', '销售人员', '7698', '2011-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, '李白', '销售人员', '7698', '2011-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, '程咬金', '经理', '7839', '2011-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, '妲己', '销售人员', '7698', '2011-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, '兰陵王', '经理', '7839', '2011-5-1', 2854, NULL, 30);
INSERT INTO `emp` VALUES (7782, '虞姬', '经理', '7839', '2011-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, '项羽', '检查员', '7566', '2017-4-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, '张飞', '总裁', NULL, '2010-6-12', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, '蔡文姬', '销售人员', '7698', '2011-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, '阿珂', '职员', '7788', '2017-5-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, '刘备', '职员', '7698', '2011-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, '诸葛亮', '检查员', '7566', '2011-12-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, '鲁班', '职员', '7782', '2012-1-23', 1300, NULL, 10);

创建 dept 表

CREATE TABLE `dept` (
  `deptno` bigint(2) NOT NULL AUTO_INCREMENT COMMENT '表示部门编号，由两位数字所组成',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称，最多由14个字符所组成',
  `local` varchar(13) DEFAULT NULL COMMENT '部门所在的位置',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `dept` VALUES (10, '财务部', '北京');
INSERT INTO `dept` VALUES (20, '调研部', '上海');
INSERT INTO `dept` VALUES (30, '销售部', '王者峡谷');
INSERT INTO `dept` VALUES (40, '运营部', '腾讯大楼');

创建分级表

CREATE TABLE `salgrade` (
  `grade` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '工资等级',
  `lowSalary` int(11) DEFAULT NULL COMMENT '此等级的最低工资',
  `highSalary` int(11) DEFAULT NULL COMMENT '此等级的最高工资',
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

COMMENT 相当于备注的含义
插入数据

INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);

  

---

非等值连接

查询所有员工的姓名，工资，所在部门的名称以及工资的等级
99写法

SELECT e.ename,e.salary,d.dname,s.grade FROM emp e, dept d,salgrade s 
WHERE e.deptno=d.deptno 
AND e.salary >= s.lowSalary AND e.salary <= s.highSalary ;

法2：

SELECT e.ename,e.salary,d.dname,s.grade FROM emp e, dept d,salgrade s 
WHERE e.deptno=d.deptno 
AND e.salary BETWEEN s.lowSalary AND s.highSalary ;

内联写法

SELECT e.ename,d.dname,s.grade FROM emp e
INNER JOIN dept d on e.deptno = d.deptno
INNER JOIN salgrade s on e.salary BETWEEN s.lowSalary and s.highSalary;

自然连接 （NATUAL JOIN）

连接查询会产生笛卡尔集，通常我们使用主外键关系来去除它。
而自然连接无须你作出主外键等式，它会自动找到这一等式，也就是说不用去写条件

要求：
两张连接的表中列名称和类型完全一致
会去除相同的列

SELECT stu_a.`name`,score_b.score FROM stu_a 
NATURAL JOIN score_b;