SQL5多表查询.docx

5多表查询create database sdpb1create table dept(deptno int primary key,dname nvarchar(30),loc nvarchar(30))create table emp(empno int primary key,ename nvarchar(30),job nvarchar(30),mgr int,hiredate datetime,sal numeric(10,2),comm numeric(10,2),--创建外键deptno int foreign key references dept(deptno))--两张表关连--显示所有部门的员工信息select * from emp,dept where emp.deptno = dept.deptno ---如何显示销售部门员工的信息 select * from emp,dept where dept.dname = sales and emp.deptno = dept.deptno--显示销售部门员工的名字部门工资和工作地--1如果多张标有相同名字的字段 需要带上表名(或表的别名)select ename,emp.deptno,loc,sal from emp,dept where dept.dname = sales and emp.deptno = dept.deptno --2别名 select ename,雇员.deptno,loc,sal from emp as 雇员,dept as 部门 where 部门.dname = sales and 雇员.deptno = 部门.deptno --如何显示部门为10 的员工名字和工资并按工资排名 select ename,sal from emp ,dept where dept.deptno = 10 and dept.deptno = emp.deptno order by sal--显示雇员名,工资及所在部门名按部门名排序部门内部用sal排序 select ename,sal,dept.deptno from emp ,dept where dept.deptno = emp.deptno order by dept.deptno,sal--2自连接(同一张表)--显示某个员工的上级领导的的姓名 --1 显示每个员工的姓名和他的上级 select ename from emp where empno =(select mgr from emp where ename = miller) --分析将表看成两张表 员工emp与上级emp select 员工.ename 员工名,上级.ename as 上级名 from emp as 员工 ,emp as 上级 where 员工.mgr = 上级.empno--3子查询 --1)单行子查询 :返回结果为单行 用 = 或 in均可 --显示与smith同一个部门的员工select * from emp where deptno =--用 = 或 in均可(select deptno from emp where ename = smith)--单行是指这里 --2)多行子查询 :返回结果为多行 用 in --显示10号部门的工作岗位相符的员工信息 --如何排除10号部门本身 select * from emp where job in--只可以用in (select distinct job from emp where deptno = 10) and ( deptno != 10) --3)在from子句中使用子查询 --显示高于部门平均工资的人 --1 找出每个部门的平均工资 select AVG(sal),deptno from emp group by deptno --2 把上面的查询结果当作一个临时表 select ename,myavg,sal from emp , (select AVG(sal) as myavg,deptno from emp group by deptno) as temp--临时表 where emp.deptno = temp.deptno and emp.saltemp.myavg order by sal desc外连接(左外连接 与 右外连接) --左外连 : 如果左边的表的记录全部要显示 如果没有匹配的记录就用空来填 --右外连 : 如果右边的表的记录全部要显示 如果没有匹配的记录就用空来填 --显示每个员工的姓名