Select之多表查询
前面我們講解的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’;
外鍵（消除冗余）
語法如下：
foreign key(字段名） references 主表（列）
eg：創建主表
再創建從表
正常插入數據
注意：可以讓班級id為null，比如來了一個學生，目前還沒有分配班級。
不能插入一個班級號不存在的學生，因為沒有這個班級，所以插入不成功。
總結
以上是生活随笔為你收集整理的Select之多表查询的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Android 安全与防护策略
- 下一篇： Java大数乘法