多表查询2.docx
《多表查询2.docx》由会员分享，可在线阅读，更多相关《多表查询2.docx（28页珍藏版）》请在冰豆网上搜索。
多表查询2
1.1.1多表查询
（1）多表查询的基本语法（重点）
多表查询的语法如下:
SELECT{DISTINCT}*|查询列1别名1，查询列2别名2……
FORM表名称`1别名1,表名称2别名2,……
{WHERE条件表达式}
{ORDERBY排序字段ASC|DESC,排序字段ASC|DESC,……}
例:
用多表查询查询emp表和dept表中的信息
SELECT*FROMemp,dept
查看emp表
SELECT*FROMemp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
------------------------------------------------------------------------------
7369SMITHCLERK790217-12月-8080020
7499ALLENSALESMAN769820-2月-81160030030
7521WARDSALESMAN769822-2月-81125050030
7566JONESMANAGER783902-4月-81297520
7654MARTINSALESMAN769828-9月-811250140030
7698BLAKEMANAGER783901-5月-81285030
7782CLARKMANAGER783909-6月-81245010
7788SCOTTANALYST756619-4月-87300020
7839KINGPRESIDENT17-11月-81500010
7844TURNERSALESMAN769808-9月-811500030
7876ADAMSCLERK778823-5月-87110020
7900JAMESCLERK769803-12月-8195030
7902FORDANALYST756603-12月-81300020
7934MILLERCLERK778223-1月-82130010
查看dept表
SQL>SELECT*FROMdept;
DEPTNODNAMELOC
---------------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
例:
查询出雇员的编号，雇员姓名，部门编号，部门名称以及部门的位置
SELECTe.empno,e.ename,d.deptno,d.dname,d.locFROMempe,deptd
WHEREe.deptno=d.deptno
结果如下:
EMPNOENAMEDEPTNODNAMELOC
------------------------------------------------
7369SMITH20RESEARCHDALLAS
7499ALLEN30SALESCHICAGO
7521WARD30SALESCHICAGO
7566JONES20RESEARCHDALLAS
7654MARTIN30SALESCHICAGO
7698BLAKE30SALESCHICAGO
7782CLARK10ACCOUNTINGNEWYORK
7788SCOTT20RESEARCHDALLAS
7839KING10ACCOUNTINGNEWYORK
7844TURNER30SALESCHICAGO
7876ADAMS20RESEARCHDALLAS
7900JAMES30SALESCHICAGO
7902FORD20RESEARCHDALLAS
7934MILLER10ACCOUNTINGNEWYORK
例:
查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名
SELECTe.ename,e.job,s.enameFROMemps,empe
WHEREs.empno=e.mgr
其中别名e为雇员关系，s为经理关系
ENAMEJOBENAME
-------------------------
SMITHCLERKFORD
ALLENSALESMANBLAKE
WARDSALESMANBLAKE
JONESMANAGERKING
MARTINSALESMANBLAKE
BLAKEMANAGERKING
CLARKMANAGERKING
SCOTTANALYSTJONES
TURNERSALESMANBLAKE
ADAMSCLERKSCOTT
JAMESCLERKBLAKE
FORDANALYSTJONES
MILLERCLERKCLARK
例:
查询出每个雇员的姓名，工作，雇员的直接上级领导的姓名,雇员所在的部门名称
SELECTe.ename,e.job,s.enamemgr_name,d.dname
FROMemps,empe,deptd
WHEREe.mgr=s.empnoande.deptno=d.deptno
结果如下:
ENAMEJOBMGR_NAMEDNAME
-----------------------------------------
SMITHCLERKFORDRESEARCH
ALLENSALESMANBLAKESALES
WARDSALESMANBLAKESALES
JONESMANAGERKINGRESEARCH
MARTINSALESMANBLAKESALES
BLAKEMANAGERKINGSALES
CLARKMANAGERKINGACCOUNTING
SCOTTANALYSTJONESRESEARCH
TURNERSALESMANBLAKESALES
ADAMSCLERKSCOTTRESEARCH
JAMESCLERKBLAKESALES
FORDANALYSTJONESRESEARCH
MILLERCLERKCLARKACCOUNTING
思考题:
查询出每个雇员的姓名，工资，部门名称，工资在公司的等级（salgrade）
及其领导的姓名,领导的工资,以及领导工资所对应的等级
分析:
先明确工资等级表（salgrade）中的内容
SELECT*FROMsalgrade;
GRADELOSALHISAL
-------------------------
17001200
212011400
314012000
420013000
530019999
再查询查询出每个雇员的姓名，工资，部门名称，工资在公司的等级（salgrade）
SELECTe.ename,e.sal,d.dname,g.gradee_grade,FROMempe,deptd,salgradeg,emps,
WHERE（e.deptno=d.deptno）AND（e.salBETWEENg.losalANDg.hisal）
ENAMESALDNAMEGRADE
--------------------------------------------
SMITH800RESEARCH1
JAMES950SALES1
ADAMS1100RESEARCH1
WARD1250SALES2
MARTIN1250SALES2
MILLER1300ACCOUNTING2
TURNER1500SALES3
ALLEN1600SALES3
CLARK2450ACCOUNTING4
BLAKE2850SALES4
JONES2975RESEARCH4
SCOTT3000RESEARCH4
FORD3000RESEARCH4
KING5000ACCOUNTING5
在以上基础上增加查找上司姓名
SELECTe.ename,e.sal,d.dname,g.grade,s.ename
FROMempe,deptd,salgradeg,emps
WHERE（e.deptno=d.deptno）AND（e.salBETWEENlosalANDhisal）AND（s.empno=e.mgr）
结果为:
ENAMESALDNAMEGRADEENAME
-------------------------------------------------
SMITH800RESEARCH1FORD
JAMES950SALES1BLAKE
ADAMS1100RESEARCH1SCOTT
WARD1250SALES2BLAKE
MARTIN1250SALES2BLAKE
MILLER1300ACCOUNTING2CLARK
TURNER1500SALES3BLAKE
ALLEN1600SALES3BLAKE
CLARK2450ACCOUNTING4KING
BLAKE2850SALES4KING
JONES2975RESEARCH4KING
FORD3000RESEARCH4JONES
SCOTT3000RESEARCH4JONES
最后确定雇员的经理姓名,经理的工资,以及经理工资所对应的等级
SELECTe.ename,e.sal,d.dname,g.gradee_grade,s.enamemgr_name,g2.gradem_grade
FROMempe,deptd,salgradeg,emps,salgradeg2
WHERE（e.deptno=d.deptno）AND（e.salBETWEENg.losalANDg.hisal）AND（s.empno=e.mgr）AND（s.salBETWEENg2.losalANDg2.hisal）
结果
ENAMESALDNAMEE_GRADEMGR_NAMEM_GRADE
----------------------------------------------------------------
SMITH800RESEARCH1FORD4
JAMES950SALES1BLAKE4
ADAMS1100RESEARCH1SCOTT4
WARD1250SALES2BLAKE4
MARTIN1250SALES2BLAKE4
MILLER1300ACCOUNTING2CLARK4
TURNER1500SALES3BLAKE4
ALLEN1600SALES3BLAKE4
CLARK2450ACCOUNTING4KING5
BLAKE2850SALES4KING5
JONES2975RESEARCH4KING5
FORD3000RESEARCH4JONES4
SCOTT3000RESEARCH4JONES4
进一步思考:
如果要将上面求得的结果按如样式显示工资的等级，应该如何实现
1:
第五等工资
2:
第四等工资
3:
第三等工资
4:
第二等工资
5:
第一等工资
此时只能用DECODE（）函数来实现
SELECTe.ename,
e.sal,
d.dname,
decode（g.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资'）e_grade,
s.enamemgr_name,
s.salmgr_sal,
decode（g2.grade,5,'第一等工资',4,'第二等工资',3,'第三等工资',2,'第四等工资',1,'第五等工资'）m_grade
FROMempe,deptd,salgradeg,emps,salgradeg2
WHERE（e.deptno=d.deptno）AND（e.salBETWEENg.losalANDg.hisal）AND（s.empno=e.mgr）AND（s.salBETWEENg2.losalANDg2.hisal）
结果为:
ENAMESALDNAMEE_GRADEMGR_NAMEMGR_SALM_GRADE
-------------------------------------------------------------------------
SMITH800RESEARCH第五等工资FORD3000第二等工资
JAMES950SALES第五等工资BLAKE2850第二等工资
ADAMS1100RESEARCH第五等工资SCOTT3000第二等工资
WARD1250SALES第四等工资BLAKE2850第二等工资
MARTIN1250SALES第四等工资BLAKE2850第二等工资
MILLER1300ACCOUNTING第四等工资CLARK2450第二等工资
TURNER1500SALES第三等工资BLAKE2850第二等工资
ALLEN1600SALES第三等工资BLAKE2850第二等工资
CLARK2450ACCOUNTING第二等工资KING5000第一等工资
BLAKE2850SALES第二等工资KING5000第一等工资
JONES2975RESEARCH第二等工资KING5000第一等工资
FORD3000RESEARCH第二等工资JONES2975第二等工资
SCOTT3000RESEARCH第二等工资JONES2975第二等工资
（2）左、右连接（重点）
查看dept表中的记录的记录
SELECT*FROMdept;
DEPTNODNAMELOC
----------------------------
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
例：
利用emp和dept做一个连接查询，查询结果包括雇员编号，雇员姓名，部门编号，部门名称，部门所在位置
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno;
结果:
EMPNOENAMEDEPTNODNAMELOC
------------------------------------------------
7369SMITH20RESEARCHDALLAS
7499ALLEN30SALESCHICAGO
7521WARD30SALESCHICAGO
7566JONES20RESEARCHDALLAS
7654MARTIN30SALESCHICAGO
7698BLAKE30SALESCHICAGO
7782CLARK10ACCOUNTINGNEWYORK
7788SCOTT20RESEARCHDALLAS
7839KING10ACCOUNTINGNEWYORK
7844TURNER30SALESCHICAGO
7876ADAMS20RESEARCHDALLAS
7900JAMES30SALESCHICAGO
7902FORD20RESEARCHDALLAS
7934MILLER10ACCOUNTINGNEWYORK
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno（+）=d.deptno
结果为:
EMPNOENAMEDEPTNODNAMELOC
--------------------------------------------------
7782CLARK10ACCOUNTINGNEWYORK
7839KING10ACCOUNTINGNEWYORK
7934MILLER10ACCOUNTINGNEWYORK
7566JONES20RESEARCHDALLAS
7902FORD20RESEARCHDALLAS
7876ADAMS20RESEARCHDALLAS
7369SMITH20RESEARCHDALLAS
7788SCOTT20RESEARCHDALLAS
7521WARD30SALESCHICAGO
7844TURNER30SALESCHICAGO
7499ALLEN30SALESCHICAGO
7900JAMES30SALESCHICAGO
7698BLAKE30SALESCHICAGO
7654MARTIN30SALESCHICAGO
40OPERATIONSBOSTON
部门40就出现在结果中了.此时我们使用的是右连接
（+）在=左边表示右连接
（+）在=右边表示左连接
左连接:
SELECTe.empno,e.ename,d.deptno,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno（+）
EMPNOENAMEDEPTNODNAMELOC
------------------------------------------------
7369SMITH20RESEARCHDALLAS
7499ALLEN30SALESCHICAGO
7521WARD30SALESCHICAGO
7566JONES20RESEARCHDALLAS
7654MARTIN30SALESCHICAGO
7698BLAKE30SALESCHICAGO
7782CLARK10ACCOUNTINGNEWYORK
7788SCOTT20RESEARCHDALLAS
7839KING10ACCOUNTINGNEWYORK
7844TURNER30SALESCHICAGO
7876ADAMS20RESEARCHDALLAS
7900JAMES30SALESCHICAGO
7902FORD20RESEARCHDALLAS
7934MILLER10ACCOUNTINGNEWYORK
左连接以左表为基准，所以40不会出现在结果中
例.查询雇员的编号、姓名及其领导的编号、姓名
SELECTe.empno,e.ename,s.empnomgr_no,s.enamemgr_name
FROMempe,emps
WHEREs.empno=e.mgr;
结果为:
EMPNOENAMEMGR_NOMGR_NAME
---------------------------------------
7369SMITH7902FORD
7499ALLEN7698BLAKE
7521WARD7698BLAKE
7566JONES7839KING
7654MARTIN7698BLAKE
7698BLAKE7839KING
7782CLARK7839KING
7788SCOTT7566JONES
7844TURNER7698BLAKE
7876ADAMS7788SCOTT
7900JAMES7698BLAKE
7902FORD7566JONES
7934MILLER7782CLARK
已选择13行。
用左/右连接来实现
SELECTe.empno,e.ename,s.empnomgr_no,s.enamemgr_name
FROMempe,emps
WHEREs.empno（+）=e.mgr
结果为:
EMPNOENAMEMGR_NOMGR_NAME
---------------------------------
7369SMITH7902FORD
7499ALLEN7698BLAKE
7521WARD7698BLAKE
7566JONES7839KING
7654MARTIN7698BLAKE
7698BLAKE7839KING
7782CLARK7839KING
7788SCOTT7566JONES
7839KING
7844TURNER7698BLAKE
7876ADAMS7788SCOTT
7900JAMES7698BLAKE
7902FORD7566JONES
7934MILLER7782CLARK
这样就可以完整地把KING显示出来了
1.1.2组函数及分组统计（重点）
分组组函数
SQL中常用的分组函数
Count（）:
计数
Max（）：
求最大值
Min（）：
求最小值
Avg（）:
求平均值
Sum（）:
求和
Count（）:
计数
例:
统计emp表中的人数
SQL>SELECTcount（empno）FROMemp;
结果为:
14
例:
统计获得奖金的人数
SELECTcount（comm）FROMemp;
结果为:
4
从结果可以看出，组函数count（）对comm统计时，如果comm不是空值,计数为1,为空值时计数0
思考：
统计部门编号为10的人数
Max（）：
求最大值\Min（）：
求最小值主要应用在对数字求最大值或最小值
例：
求所雇员的最低工资
SELECTmin（sal）FROMemp
升级会员 