数据库第五周练习

构造一个很大的表（几百万行以上级别），用 delete 删除里面的大部分行，比较 rollback 和 commit 所花时间，哪个更快？（提示：通过设置 sqlplus 环境变量观察语句运行时间）

set timing on;
delete from simuhw2 where rownum <= 200000;
roll back;
delete from simuhw2 where rownum <= 200000;
commit;

可以看到，rollb ack的时间明显比commit长。

给在波士顿，纽约，芝加哥，达拉斯的员工分别加薪 300，500，380，210 美元（要求 1 条语句完成）

1
2
3

SELECT E.* ,
       DECODE(E.DEPTNO,10,E.SAL+500,20,E.SAL+210,30,E.SAL+380,E.SAL+380) SAL2
FROM emp E;

建立一个有（多种）重复行的表，然后把每种重复的行删除到只剩下一行。

1
2
3

create table repeattest(
id number not null ,
day date not null);

insert into repeattest(id, day) values(4, to_date('2022-3-18','yyyy-mm-dd'));  
insert into repeattest(id, day) values(5, to_date('2022-3-18','yyyy-mm-dd'));  
insert into repeattest(id, day) values(6, to_date('2022-3-17','yyyy-mm-dd'));  
insert into repeattest(id, day) values(7, to_date('2022-3-17','yyyy-mm-dd'));  
insert into repeattest(id, day) values(4, to_date('2022-3-18','yyyy-mm-dd'));

删除重复

delete from repeattest a 
where (a.day in (select day from repeattest group by day having count(*) > 1)
and 
rowid not in (select min(rowid) from repeattest group by day having count(*)>1))
or(
a.id in (select id from repeattest group by id having count(*) > 1)  
and
rowid not in (select min(rowid) from repeattest group by day having count(*)>1))
;

给 EMP 表增加一列 LOC，然后记录每位员工所在城市

先复制表结构

1	create table emp1 as select * from emp where 1=2;

新建列LOC

1 2	ALTER TABLE emp1 ADD LOC varchar2(20);

1	insert into emp1 select emp.*,dept.loc from emp,dept where emp.deptno = dept.deptno;

通过 ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; 改变会话的隔离级别，观察事务读写隔离逻辑的影响（缺省的隔离级别是 READ COMMITTED）提示：打开 2 个 sqlplus 窗口，都用 scott 登录，在其中一个进行 update 或 commit，在另外一个窗口观察数据的变化

隔离级别的分类
1. 读未提交 Read Uncommitted（在本次事务中可以读到其他事务中没有提交的数据 - 脏数据）
2. 读已提交 Read Committed （只能读到其他事务提交过的数据。如果在当前事务中，其他事务有提交，则两次读取结果不同）
3. 可重复读 Repeatable Read （默认，保证了事务中每次读取结果都相同，而不管其他事物是否已经提交。会出现幻读）
4. 序列化 Serializable （隔离级别中最严格的，开启一个 serializable 事务，那么其他事务对数据表的写操作都会被挂起）

默认情况下为

    当修改为`Serializable `,开启一个 serializable 事务，那么其他事务对数据表的写操作都会被挂起

把工资（从高到低）排名 10-12 名的员工加薪 300 美元（要求 1 条语句完成）

update emp1
set sal = sal + 300
where 
ename in (select ename from (select ename,rownum abc from(select emp.* from emp order by sal desc)) where abc between 10 and 12);

找出工资比下属低的员工，把他的工资加到和该下属相同（要求 1 条语句完成）

下属表的MGR（上司编号）字段对应上司表的EMPNO（员工编号）,先观察一下哪个上司工资比下属低

1	select * from (select a.ename mgrname,b.ename empname,a.sal mgrsal,b.sal empsal from emp a,emp b where a.empno=b.mgr) t where t.empsal>t.mgrsal;

一条语句修改

update emp2
set sal = (select max(empsal) from (select a.ename mgrname,b.ename empname,a.sal mgrsal,b.sal empsal from emp a,emp b where a.empno=b.mgr) t where (t.empsal>t.mgrsal) and (t.mgrname = ename))
where 
ename in (select mgrname from (select a.ename mgrname,b.ename empname,a.sal mgrsal,b.sal empsal from emp a,emp b where a.empno=b.mgr) t where t.empsal>t.mgrsal);

表 A 有 C1，C2 两列，分别记录了所有商品编号（唯一）和商品价格，表 B 也有 C1 和 C2 列，记录了部分商品（非全部）的新价格，请用 B 的数据更新 A 表中的商品价格

create table A(
C1 number not null primary key,
C2 number not null); 
create table B(
C1 number not null primary key,
C2 number not null);

采用sqlldr命令导入表格，有:

利用 B 的数据更新 A 表中的商品价格

merge into A  
using B  
on(A.C1=B.C1)  
when matched then  
update set A.C2 = B.C2 
;