SQL/Oracle——第五章 SQL语言基础及调优 连接部分(作业2）

自连接

SQL> select * from emp;     [上司表]

 EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
 
  7369 SMITH      CLERK           7902 17-12月-80            800                    20
  7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
  7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
  7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
  7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
  7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
  7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
  7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
  7839 KING       PRESIDENT            17-11月-81           5000                    10
  7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
  7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
  7900 JAMES      CLERK           7698 03-12月-81            950                    30
  7902 FORD       ANALYST         7566 03-12月-81           3000                    20
  7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
  已选择14行。

SQL> select * from emp;    [下属表]

 EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

  7369 SMITH      CLERK           7902 17-12月-80            800                    20
  7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
  7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
  7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
  7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
  7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
  7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
  7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
  7839 KING       PRESIDENT            17-11月-81           5000                    10
  7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
  7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
  7900 JAMES      CLERK           7698 03-12月-81            950                    30
  7902 FORD       ANALYST         7566 03-12月-81           3000                    20
  7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
  已选择14行。

--左外连接：
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang left join emp exia on eshang.empno=exia.mgr;

上司 下属

SMITH //诸如SMITH没有下属
ALLEN
WARD
JONES FORD
JONES SCOTT
MARTIN
BLAKE TURNER
BLAKE ALLEN
BLAKE WARD
BLAKE JAMES
BLAKE MARTIN
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING JONES
KING CLARK
TURNER
ADAMS
JAMES
FORD SMITH
MILLER
已选择21行。

--右外连接：
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang right join emp exia on eshang.empno=exia.mgr;

上司 下属
JONES FORD
JONES SCOTT
BLAKE JAMES
BLAKE TURNER
BLAKE MARTIN
BLAKE WARD
BLAKE ALLEN
CLARK MILLER
SCOTT ADAMS
KING CLARK
KING BLAKE
KING JONES
FORD SMITH
KING //KING没有上司
已选择14行。

--完全外连接：
SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang full join emp exia on eshang.empno=exia.mgr;

//所有没有上司、没有下属的也都列出来

上司 下属

---

FORD SMITH
BLAKE ALLEN
BLAKE WARD
KING JONES
BLAKE MARTIN
KING BLAKE
KING CLARK
JONES SCOTT
KING
BLAKE TURNER
SCOTT ADAMS
BLAKE JAMES
JONES FORD
CLARK MILLER
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES

已选择22行。

--简单连接
SQL> select eshang.ename 上司,exia.ename 下属
  2  from emp eshang,emp exia
  3  where eshang.empno=exia.mgr;   //上司表里的员工编号在下属表里是领导(mgr)编号

上司 下属

---

JONES FORD
JONES SCOTT
BLAKE TURNER
BLAKE ALLEN
BLAKE WARD
BLAKE JAMES
BLAKE MARTIN
CLARK MILLER
SCOTT ADAMS
KING BLAKE
KING JONES
KING CLARK
FORD SMITH

已选择13行。

等价于

SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang inner join emp exia on eshang.empno=exia.mgr;

上司 下属

---

FORD SMITH
BLAKE ALLEN
BLAKE WARD
KING JONES
BLAKE MARTIN
KING BLAKE
KING CLARK
JONES SCOTT
BLAKE TURNER
SCOTT ADAMS
BLAKE JAMES
JONES FORD
CLARK MILLER

已选择13行。

5.3.1简单查询:select子句

5.3.2筛选查询：where子句
1.比较筛选
!=,<>
!>,!<
any,all
where sal<all(1000,900,1200);
where sal<any(1000,900,1200);

2.使用关键字
(1)like:_,%；not like

(2)in, not in

(3)[not]between...and...

(4)is null,is not null

3.逻辑筛选:and,or,not

5.3.3分组查询
（1）分组后，能够查询什么，select子句写什么？--select 聚合类函数,分组字段
（2）select...from...where...group by...having...
where...group by...having...执行顺序：
先where，后group by，再having。
(3)聚合类函数使用位置：select.聚合类函数..from...where.不能用聚合类函数..group by...having.聚合类函数..

5.3.4排序：多字段排序

5.3.5多表关联查询
1.简单连接查询
select  from  a,b where 字段1.a=字段1.b;
 select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;

2.用join字段的连接
（1）内连接：[inner] join
 select empno,ename,emp.deptno,dname from emp inner join dept on emp.deptno=dept.deptno;(等价于简单连接查询)

（2）外连接：[outer] join
左外连接：左表 left [outer] join 右表
 select empno,ename,dept.deptno,dname from emp left join dept on emp.deptno=dept.deptno;
右外连接: 左表 right [outer] join 右表
 select empno,ename,dept.deptno,dname from emp right join dept on emp.deptno=dept.deptno;
完全外连接：左表 full [outer] join 右表
 select empno,ename,dept.deptno,dname from emp full join dept on emp.deptno=dept.deptno;

3.两表的笛卡尔积运算
select ename,dept.deptno,dname from emp,dept;

4.自然连接：natural join(自动查找连接条件)
select ename,dname from emp natural join dept;

5.交叉连接:cross join(笛卡尔积)

6.自连接：上司表里的员工编号empno是下属表里的领导编号mgr
select eshang.ename 上司,exia.ename 下属 from emp eshang,emp exia where eshang.empno=exia.mgr;(简单连接)

根据Orecle数据库so方案下的emp表和dept表,完成下列操作:

(1)查询所有工种为CLERK的员工的姓名及其部门名称。

select ename,dname from emp,dept where job='CLERK';

(2)查询所有部门及其员工信息，包括那些没有员工的部门。

select dept.dname,ename from dept left join emp on emp.deptno=dept.deptno;

(3)查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

select dept.dname,ename from emp left join dept on emp.deptno=dept.deptno;

(4)查询在SALES部门工作的员工的姓名信息。

select ename from emp left join dept on dname='SALES';

(5)查询所有员工的姓名及其直接上级的姓名。

select x.ename 下属,s.ename 上级 from emp x inner join emp s on x.mgr=s.empno;

(6)查询人职日期早于其上级领导的所有员工信息。

select * from emp x inner join emp s on x.mgr=s.empno where x.hiredate <s.hiredate;

(7)查询从事同一种工作但不属于同一部门的员工信息。

select t1.ename,t1.job,t1.deptno,t2.ename,t2.job,t2.deptno from scott.emp t1 cross join scott.emp t2 where t1.job=t2.job and t1.deptno != t2.deptno ;

(8)查询10号部门员工及其领导的信息。

select y.ename 员工,l.ename 领导 from emp y inner join emp l on y.deptno = 10 and y.empno=l.mgr;

(9)使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。

select * from emp where sal > 2500 union select * from emp where job = 'ANALYST';

(10)通过INTERSECT集合运算，查询工资大于2500,并且工作为ANALYST的雇员信息。

select * from emp where sal > 2500 intersect select * from emp where job = 'ANALYST';

(11)使用MINUS集合查询工资大于2500,但工作不是ANALYST的雇员信息。

select * from emp where sal > 2500 minus select * from emp where job = 'ANALYST';

(12)查询工资高于公司平均工资的所有员工信息。

select * from emp where sal > (select avg(sal) from emp);

(13)查询与SMITH员工从事相同工作的所有员工信息。

select * from emp where job = (select job from emp where ename = 'SMITH');

(14)查询工资比SMITH员工工资高的所有员工信息。

select * from emp where sal > (select sal from emp where ename = 'SMITH');

(15)查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。

select ename,sal from emp where sal > (select max(sal) from emp where deptno = 30);

(16)查询部门人数大于5的部门的员工信息。

select * from emp  where deptno in (select deptno from emp group by deptno having count(*)>5);

(17)查询所有员工工资都大于299的部门的信息。

select * from dept where deptno in(select deptno from emp group by deptno having min(sal)>900);

(18)查询人数最多的部门信息。

select * from dept where deptno in (select deptno from (select deptno,count(*) as 人数 from emp group by deptno) where 人数=(select max(人数) from(select deptno,count(*) as 人数 from emp group by deptno)));    

(19)查询至少有一个员工的部门信息。

select * from emp  where deptno in (select deptno from emp group by deptno having count(*)>=4);

(20)查询工资高于本部门平均工资的员工信息。

select * from emp e where sal>(select avg(sal) from emp group by deptno having e.deptno=deptno);

(21)查询工资高于本部门平均工资的员工信息及其部门的平均工资。

select * from((select * from emp e where sal>(select avg(sal) from emp  group by deptno having e.deptno=deptno)) t1 inner join (select avg(sal),deptno from emp group by deptno) t2 on t1.deptno=t2.deptno);

(22)查询每个员工的领导所在部门的信息。

select * from dept where deptno in(select distinct deptno from emp where empno in(select distinct mgr from emp));

(23)查询平均工资低于2000的部门及其员工信息。

select * from emp t1,dept t2 where t1.deptno=t2.deptno and t1.deptno in(select deptno from emp group by deptno having avg(sal)<2000);

where…group by…having的练习：

1.统计scott方案下的emp表中各种工作的雇员人数。

select job,count(ename) from emp group by job;
答案：select job,count(*)  as 人数 from emp group by job;

2.统计scott方案下的emp表中各个部门中的各种工作的雇员人数。

select deptno,job,count(ename) from emp group by job,deptno order by deptno;
答案：select deptno,job,count(*) 人数  from emp group by deptno,job order by deptno,job;

3.统计scott方案下的emp表中平均工资大于2500的工作。

select deptno as 部门编号,avg(sal) as 平均工资 from emp group by deptno having avg(sal) >2500;
答案：select job,avg(sal) from emp group by job having avg(sal)>2500;

4.统计scott方案下的emp表中1982年后参加工作的、雇员人数2人以上（含2人）的部门编号。

答案：select deptno,count(*) 人数 from emp where hiredate>'1-1月-1982' group by deptno having count(*)>=2;