前面我們講解的mysql表的查詢都是對一張表進行查詢，連結為這裡寫連結内容
今天來學習多表查詢。
我們用一個簡單的公司管理系統，有三張表EMP ,DEPT,SALGRADE來示範如何進行多表查詢。
表内容如下：
建立部門表
drop table if exists dept;
create table if not exists dept
(
deptno INT(2) zerofill not null comment ‘部門編号’,
dname VARCHAR(14) comment ‘部門名稱’,
loc VARCHAR(13) comment ‘部門所在地點’
);
建立雇員表
drop table if exists emp;
create table if not exists emp
(
empno INT(6) zerofill not null comment ‘雇員編号’,
ename VARCHAR(10) comment ‘雇員姓名’,
job VARCHAR(9) comment ‘雇員職位’,
mgr INT(4) zerofill comment ‘雇員上司編号’,
hiredate DATETIME comment ‘雇傭時間’,
sal DECIMAL(7,2) comment ‘工資月薪’,
comm DECIMAL(7,2) comment ‘獎金’,
deptno INT(2) zerofill comment ‘部門編号’
);
建立薪資等級表
drop table if exists salgrade;
create table if not exists salgrade
(
grade INT comment ‘等級’,
losal INT comment ‘此等級最低工資’,
hisal INT comment ‘此等級最高工資’
);
插入部門資料
insert into dept (deptno, dname, loc) values (10, ‘ACCOUNTING’, ‘NEW YORK’);
insert into dept (deptno, dname, loc) values (20, ‘RESEARCH’, ‘DALLAS’);
insert into dept (deptno, dname, loc) values (30, ‘SALES’, ‘CHICAGO’);
insert into dept (deptno, dname, loc) values (40, ‘OPERATIONS’, ‘BOSTON’);
插入雇員資料
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, ‘KING’, ‘PRESIDENT’, null, ‘1981-11-17’, 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, ‘TURNER’, ‘SALESMAN’, 7698,’1981-09-08’, 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23’, 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03’, 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-12-03’, 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300, null, 10);
插入薪資等級資料
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
單表查詢
先将前面的基本查詢進行一下複習：
1、工資大于500或者崗位為Manager的雇員，同時滿足他們姓名為首字母大寫J;
select ename,sal,deptno
from emp where sal>500 or job=’manager’ and ename like ‘J%’;
2、按照部門編号升序而雇員工資降序排序（order by）
select *from emp
order by deptno,sal desc;
3、使用年薪排序
select ename,sal*12+ifnull(comm,0) as ‘年薪’ from emp order by ‘年薪’;
4、按照empno升序排序，每頁隻顯示3條記錄，顯示第一頁。
select *from emp
order by empno
limit 0,3;
5、顯示工資最高的員工姓名和工作崗位
select ename,job
from emp
where sal=(select max(sal) from emp);
6、顯示工資高于平均工資的員工資訊(ename,job,sal0
select ename,job,sal
from emp
where sal>(select avg(sal) from emp);
7、顯示每個部門的平均工資和最高工資
（1）select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;
（2）select deptno,avg(sal) as ‘平均工資’,max(sal) as ‘最高工資’ from emp group by deptno;
多表查詢
為什麼需要多表查詢呢？
比如我們要求顯示雇員名、雇員工資以及所在部門的名字，因為上面的資料來自EMP和DEPT表，是以要聯合查詢：
select *from emp,dept;
但是這樣查詢的資料是按笛卡爾積輸出的，我們完全不需要這麼多的資料。
練習：
1、查詢雇員名、工資以及所在部門的名字
select ename sal，dname
from emp,dept where emp.deptno=dept.deptno;
2、顯示部門編号為10的部門名，員工名和他的工資
select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;
自連接配接
自連接配接是指在同一張表連接配接查詢
1、顯示員工FORD的上級上司的姓名
（1）單表查詢：
select ename from emp
where empno=(select mgr from emp where ename=’FORD’);
（2）多表查詢
select leader.ename
from emp worker,emp leader
where worker.mgr=leader.empno
and worker.ename=’FORD’;
子查詢
子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢
單行子查詢:結果隻有一行
1、顯示SMITH同一部門的員工資訊(ename,job,sal);
select ename,job,sal
from emp
where deptno=(select deptno from emp where ename=’smith’);
多行子查詢（in all any）：傳回多條記錄的子查詢
使用關鍵字in
1、查詢和10号部門工作相同的ename，job,sal,deptno，但是不包含10号部門自己；
select ename,job,sal,deptno
from emp
where job in(select job from emp where deptno=10)
and deptno<>10;
使用關鍵字all
2、顯示工資比部門編号為30的所有員工的工資高的員工的姓名、工資和部門号
select ename,sal,deptno from emp
where sal > all(select sal from emp where deptno=30);
//where sal > (select max(sal) from emp where deptno=30);
使用關鍵字any
3、顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門号
select ename,sal,deptno from emp
where sal > any(select sal from emp where deptno=30);
多列子查詢
查詢傳回多個列資料的子查詢語句
1、查詢和SMITH的部門和崗位完全相同的所有雇員資訊，不含SMITH本人
select ename,job,sal from emp
where (deptno,job)=(select deptno,job from emp where ename = ‘smith’)
and ename <> ‘smith’;
多表子查詢
子查詢當做臨時表
1、如何顯示高于自己部門平均工資的員工的資訊
擷取各個部門的平均工資，将其看作臨時表tmp
select ename,job,sal
from emp,
(select avg(sal) as avg_sal,deptno from emp group by deptno) as tmp
where emp.deptno = tmp.deptno
and sal > tmp.avg_sal;
2、查找每個部門工資高的人的ename,job,sal
select ename,job,sal from emp,
(select max(sal) as max_sal,deptno from EMP group by deptno) as tmp
where EMP.deptno=tmp.deptno and sal=tmp.max_sal;
3、顯示每個部門的資訊（部門名，編号，位址）和人員數量。
//多表查詢
select dname,dept.deptno,loc,count(*) as “人員數量”
from emp,dept
where emp.deptno=dept.deptno
group by deptno;
//使用子查詢
(1)對EMP表進行人員統計
select count(*), deptno from EMP group by deptno;
(2) 将上面的表看作臨時表
select DEPT.deptno, dname, mycnt, loc from DEPT,
(select count(*) mycnt, deptno from EMP group by deptno) tmp
where DEPT.deptno=tmp.deptno;
自我複制
上面使用了多表和子查詢兩種方式進行查詢，到底哪個效率高呢？我們需要弄大量資料來進行測試。可以使用自我複制建立海量資料。
舉例如下：
1、create table temp like emp; //複制表結構
2、insert into temp select *from emp; //将emp中的數複制到temp
3、insert into temp select *from temp; //自我複制，…..直到表有20多萬
…..直到表有20多萬
from子查詢效率高于多表查詢
删除表的重複記錄
我們先建一個有重複資料的表：
插入資料：
共需要4步：
1、建立空表使得結構與原表相同
2、導入不重複資料
3、把原表删除
drop table t1;
4、将新表改為原表
檢視表，成功：
合并查詢
在實際應用中，為了合并多個select的執行結果，可以使用集合操作符 union，union all
查找工資大于2500和職位為Manager的人（enamel，sal，job）
1、union（用于取得兩個結果集的并集，并且會自動去掉結果集中重複行）
select ename,job,sal from emp where sal > 2500
union
select ename,job,sal from emp where job = ‘manager’;
2、union all(該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重複行)
select ename,job,sal from emp where sal > 2500
union all
select ename,job,sal from emp where job = ‘manager’;
外鍵（消除備援）
- 外鍵用于定義主表和從表之間的關系;
- 外鍵限制主要定義在從表上，主表則必須是有主鍵限制或unique限制;
-
當定義外鍵後，要求外鍵列資料必須在主表的主鍵列存在或為null。
文法如下：
foreign key(字段名） references 主表（列）
eg：建立主表
再建立從表
正常插入資料
注意：可以讓班級id為null，比如來了一個學生，目前還沒有配置設定班級。
不能插入一個班級号不存在的學生，因為沒有這個班級，是以插入不成功。