1.整合三个以上的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table t1 (id char(1), g number(3));
create table t2 (id char(1), g number(3));
create table t3 (id char(1), g number(3));
INSERT INTO t1 VALUES('A',70);
INSERT INTO t1 VALUES('B',80);
INSERT INTO t1 VALUES('C',75);
INSERT INTO t1 VALUES('D',90);
INSERT INTO t2 VALUES('B',70);
INSERT INTO t2 VALUES('D',50);
INSERT INTO t2 VALUES('E',60);
INSERT INTO t3 VALUES('A',90);
INSERT INTO t3 VALUES('B',90);
INSERT INTO t3 VALUES('E',55);
INSERT INTO t3 VALUES('F',93);
commit;
1
select nvl(t1.id,t2.id),t1.g g1 ,t2.g g2 from t1 full outer join t2 on t1.id=t2.id;
1
2
select nvl(temp.id,t3.id),temp.g1 g1,temp.g2 g2,t3.g g3 from t3 full outer join 
(select nvl(t1.id,t2.id) id,t1.g g1 ,t2.g g2 from t1 full outer join t2 on t1.id=t2.id) temp on t3.id = temp.id;

image-20220326174422463

2.SQL语句练习

列出平均总收入（工资+提成）最高的部门名称

1
2
3
select d.dname from 
(select deptno,avg(sal) num from emp group by deptno) t, dept d 
where t.deptno=d.deptno and t.num=(select max(avg(NVL(COMM,0)+SAL)) num from emp group by deptno);

image-20220326175804268

列出部门工资中位数比公司工资中位数高的部门名称

1
2
3
select d.dname from 
(select deptno, median(sal) num from emp group by deptno) t, dept d 
where t.deptno=d.deptno and t.num>(select  median(sal) num from emp);

image-20220326180155008

求底层员工（即没有直属下属）中工资最高的员工名字

​ 先查出底层员工名单

1
select * from emp where empno not in(select distinct mgr from emp where mgr is not null );
1
2
3
4
5
select t.ename
from
(select * from emp where empno not in(select distinct mgr from emp where mgr is not null )) t
where
t.sal = (select max(sal) from (select * from emp where empno not in(select distinct mgr from emp where mgr is not null )));

image-20220326183454944

列出部门的名称和部门内员工的不同工种数

1
2
3
select d.dname,t.jobcount from 
(select deptno, count(distinct job) jobcount from emp group by deptno) t, dept d 
where t.deptno=d.deptno ;

image-20220326183851619

求每年进入公司工作的员工数

1
select to_char(hiredate,'yyyy') year,count(distinct(ename)) count from emp group by to_char(hiredate,'yyyy');

image-20220326184311762

把每个部门工资最低的员工的工资调整到与该部门倒数第 2 一致（注意有并列的情况）

​ 分组排序结果

1
select ename,sal,row_number() over (partition by deptno order by sal asc) sal_order,deptno from scott.emp;

image-20220326202315698

​ 复制了一份新的emp表emp3

1
2
3
4
5
6
update emp3
set sal = (select sal from (select * from (select ename,sal,row_number() over (partition by deptno order by sal asc) sal_order,deptno from emp) t where t.sal_order = 2) t1 where t1.deptno = emp3.deptno )
where ename  
in
(select ename from (select ename,sal,row_number() over (partition by deptno order by sal asc) sal_order,deptno from emp) t2 where t2.sal_order = 1)
;

image-20220326204258685

3.实验

​ 对 emp 表中的某些行 select…for update观察其它会话对 emp 表进行的读写操作受到什么影响

image-20220326205342040

​ 会发现出现表锁，如果设置ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE时候，对deptno = 20进行修改也会出现表锁，若使用默认配置，则可以修改

image-20220326205643714

image-20220326205736409

​ 由于oracle采用RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，通过 where 条件走非索引列过滤之后，即使不符合where条件的记录，也是会加行锁。所以从锁方面来看，RC(mysql)的并发应该要好于RR；可以减少一部分锁竞争，减少死锁和锁超时的概率。