1 --union 并集
2 select * from emp where ename like '%A%' union
3 select * from emp where ename like '%M%';
4 --union all 集并 公共部分 会包含二次
5 select * from emp where ename like '%A%'
6 union all
7 select * from emp where ename like '%M%';
8 --intersert交集
9 select * from emp where ename like '%A%'
10 intersect
11 select * from emp where ename like '%M%';
12 --minus求差集 S1: A - (S1&S2 union)1100-1200
13 select * from emp where sal between 700 and 1200 --700-1100;
14 minus
15 select * from emp where sal between 1100 and 1500;
16
17 --联合与全联合运算
18 --union
19 create table emp_history as select * from emp ;--辅助
20
21 select empno,ename,sal,hiredate,deptno
22 from emp where deptno =20
23 union
24 select empno,ename,sal,hiredate,deptno
25 from emp_history where deptno =30
26 order by deptno;
27
28 -- union all 不能消除重复行,不能输出排序 使用DISTINCT关键字
29 select empno,ename,sal,hiredate,deptno
30 from emp where deptno =20
31 union all
32 select empno,ename,sal,hiredate,deptno
33 from emp_history where deptno =30
34 order by deptno;
35
36 --相交运算 1.列数和数据类型与select语句一样,但列名可以不同
37
38 select empno,ename,sal,hiredate,deptno from emp
39 where deptno =20 intersect
40 select empno,ename,sal,hiredate,deptno from emp_history
41 where deptno=20;
42
43 --相减运算
44 --查询在第一个表中而不再第二个表中的行
45 select empno,ename,sal,hiredate,deptno from emp
46 where deptno=20
47 minus
48 select empno,ename,sal,hiredate,deptno from emp
49 where deptno=20;
50 --结构化查询 实现递归表的查询
51 select * from emp;
52 select level,lpad(' ',2*(level-1))||ename, empno,mgr,hiredate,sal from emp
53 start with mgr is null --start with以manager_id is null作为跟节点
54 connect by prior empno=mgr;
55 --根据connect by prior规则,继续向下寻找,形成树状结构查询
56
57 --用子查询插入数据
58 create table emp_copy as select * from emp where 1=2;
59 insert into emp_copy select * from emp where deptno=20;
60 --insert插入多表数据
61 create table emp_dept_10 as select * from emp where 1=2;
62 create table emp_dept_20 as select * from emp where 1=2;
63 create table emp_dept_30 as select * from emp where 1=2;
64 insert first
65 when deptno =10
66 then
67 into emp_dept_10
68 when deptno=20
69 then
70 into emp_dept_20
71 when deptno=30
72 then
73 into emp_dept_30
74 else
75 into emp_copy
76 select * from emp;
77
78 select * from emp_dept_10;
79 --Merge语句
80 merge into emp_copy c --目标表
81 using emp e on (c.empno=e.empno) --源表,可以是表,视图或查询
82 when MATCHED then update --当匹配时,进行update操作
83 set c.ename=e.ename,c.job=e.job,c.mgr=e.mgr,
84 c.hiredate=e.hiredate,c.sal=e.sal,c.comm=e.comm,
85 c.deptno =e.deptno
86 when not matched then
87 insert
88 values(e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
89 e.deptno);
90
91
92 create table dept60_bonuses
93 (
94 empno number,bonus_amt number
95 );
96 insert into dept60_bonuses values(7369,0);
97 insert into dept60_bonuses values(7788,2);
98 insert into dept60_bonuses values(7876,3);
99 select empno,sal,ename from emp;
100 select * from dept60_bonuses;
101 --合并两张表,根据不同的语句删除,更新
102 merge into dept60_bonuses b
103 using ( select empno,sal,deptno from emp where deptno=20) e
104 on (b.empno=e.empno)
105 when matched then
106 update set b.bonus_amt =e.sal*0.2
107 where b.bonus_amt=0
108 delete where (e.sal>2500)
109 when not matched then
110 insert (b.empno,b.bonus_amt)
111 values (e.empno,e.sal*0.1)
112 where (e.sal<4000);
113 使用TRUNCATE清除表数据
114 与delete语句相比,使用truncate命令速度更快,原因 DTL
115 1不会激活表的删除触发器
116 2 属于数据定义语言,不会产生撤销信息
117 3 主外键关系无法清除表内容,必须禁用约束
118 不能使用PLSQL语句块 直接调用
119 --禁用约束
120 alter table dept disable constraint pk_dept cascade;
121 提交 commit; 回滚 rollback;
122 --sql>
123 create table jobs (adds varchar2(10),jname varchar2(20),sal number(10),comm number(10));
124 delete from jobs;
125 insert into jobs values('OFFICE','办公文员',3000,5000);
126 savepoint sp;
127 insert into jobs values('FINANCE','财务人员',4000,8000);
128 select * from jobs;
129 rollback to savepoint sp;
130 使用集合方法
131 --exits 方法 集合的坐标元素是否存在
132 declare
133 type projectlist is varray (50) of varchar2(16);
134 project_list projectlist:=projectlist('网站','ERP','CRM','CMS');
135 begin
136 if project_list.exists(5)
137 then
138 dbms_output.put_line('元素存在,其值为:'||project_list(5));
139 else
140 dbms_output.put_line('元素不存在');
141 end if;
142 end;