Oracle数据库第二天

Oracle多表查询、子查询、集合运算

多表查询、子查询、集合运算
1、笛卡尔积：两张表的乘积
格式：select * from 表1，表2;

-- Create table
create table EMP
(
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredata DATE,
  sal      NUMBER(10,2),
  comm     NUMBER(10,2),
  deptno   NUMBER(2)
)
tablespace USER_TS_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table EMP
  is '工资表';

--查询员工编号，员工编号，经理的编号，经理的名字
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

2、外连接
左外连接：left （outer）join 左表中所有的记录，如果右表没有对应记录，显示空。
右外连接：right (outer) join 右表中的所有记录，如果左表没有对应记录，显示空。
ps:outer可以省略。

--内连接查询
select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1 inner join emp e2 on e1.mgr = e2.empno;

select * from emp e1 left join emp e2 on e1.mgr = e2.mgr;

3、子查询：查询语句中嵌套查询语句

--查询最高工资的员工信息（两部分解）
--1、首先查询出最高工资
select max(sal) from emp
--2、最高工资的员工信息
select * from emp where sal = (select max(sal) from emp);

--查询比7782工资高，同事和7698从事相同job的员工信息
select * from emp where sal > (select sal from emp where empno = '7782')
and job = (select job from emp where empno = '7698');  

4、exists（查询语句）：存在的意思。
当做布尔值来处理：查询有结果返回true，否则false；
适用：处理大数据（高效）。
rowid：伪列，系统自动生成的一列，表示列号。
rownum：oracle特有表示列号的，默认值1，查询结果出来后，增加1；不做大于号判断，做小于号判断。

--工资最高的前三名员工信息
select * from (select e.* from emp e order by (sal) desc) where rownum < 4;
--员工表中薪水大于本部门平均薪水的员工信息
select * from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) t1 
where e1.deptno = t1.deptno
and e1.sal > t1.avgsal;
--显示各个年份入职的员工数量
select
	sum(cc)  "Total",
	sum(case yy when '2015' then cc end) "2015",
	sum(case yy when '2016' then cc end) "2016",
	sum(case yy when '2017' then cc end) "2017",
	sum(case yy when '2018' then cc end) "2018",
	sum(case yy when '2019' then cc end) "2019"
from
	(select to_char(hiredata,'yyyy') yy,count(1) cc from emp group by to_char(hiredata,'yyyy'));