Oracle SQL语法笔记

1.intersect

取的是两个结果的交集并且去重。

select * from 表 where 。。。

intersect

select * from 表 where 。。。

2.minus  语法 A minus B

  A结果集存在但是B结果集不存在的数据，经过去重，按第一列进行升序后返回。

select * from表 where

minus

select * from 表 where

3.in和exists

都是用于返回匹配子句的结果集。oracle嵌套循环效率而言，保证外层循环数据量小于内存循环即可。

两者相互转换的前提，保证子查询返回的是非空数据。（null在oracle中表示未知。）

    in关键字，优先查询 子句，然后在跟主句进行依次匹配。

    exists关键字，优先查询 主句，而后在跟子句进行匹配操作。

select * from A where BH in(

 select BH from A where 。。

);

select * from A   where exists (select 1 from A  where 。。。);

4、merge into

语句是insert 与update语句的结合，可以同时实现update和insert的功能。

一、merge into语句的语法。

MERGE INTO schema. table alias

USING { schema. table | views | query} alias

ON {(condition) }

WHEN MATCHED THEN

  UPDATE SET {clause}

WHEN NOT MATCHED THEN

  INSERT VALUES {clause};

--解析

INTO 子句

用于指定你所update或者Insert目的表。

USING 子句

用于指定你要update或者Insert的记录的来源，它可能是一个表，视图，子查询。

ON Clause

用于目的表和源表（视图，子查询）的关联，如果匹配（或存在），则更新，否则插入。

merge_update_clause

用于写update语句

merge_insert_clause

用于写insert语句

Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出。我们在上面的Merge语句后加入OUTPUT子句

6.rollup 和 cube 都是求统计和。

  rollup是产生小计的和（rollup(a,b) -> (a,b)  (a) ()这三类分组计算聚合）

eg:

--按部门和工作进行分组，按部门分组，按空分组进行展示数据。

select min(sal),deptno,job from emp  group by  rollup(deptno,job)

cube是列联交叉表（cube(a,b,c)  abc,ab,ac,bc,a,b,c,null）

eg:

select sum(sal),deptno,job from emp  group by  cube(deptno,job)

7.merge into的使用

语法：merge into 目标表 a

           using 源表 b

           on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……) 

           when matched then 操作1（update set a.更新字段=b.字段）

           when  not macthed then 操作2 （ insert (字段1,字段2……) values (值1,值2……) ）

作用：通常用于判断一个表中是否拥有源表相应匹配的字段值，用以进行相应的数据库操作

范例：构建dept表的复制表dept_test,如果查到复制表的部门id和源表的部门id相同则更新数据，如果复制表的部门id与源表部门id不匹配，则将新的数据插入至复制表中。

8.递归查询

语法：select * | 字段 from 表名

start with 字段=值

connect by prior 父字段=子字段（子字段=父字段）

作用：通常用于判断一个表中存在自连接的情况，查询出整个的树状结构

范例：查询emp表中smith这个人的层级关系。

9、数据库锁

当多个用户同时操作同一个资源的时候，会产生并发的问题。解决的办法就是在一个事务中加锁，阻止其他的事务进行操作。

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：   

1、DML锁（data locks，数据锁），用于保护数据的完整性；   

2、DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；    

3、内部锁和闩（internal locks and latches），保护数据库的内部结构。

我们基本上经常见到的都是DML锁。

DML锁

DML锁主要包括TM锁和TX锁，其中TM锁称为意向锁或表级锁，TX锁称为行级锁或事务锁。我们可以认为Oracle只有如下6种LMODE的锁，只是根据锁定的对象不同而有不同的名称，如6号的X锁，既可以是用于锁表的TM锁，也可以是TX锁，也可以是DDL锁。

范例1：查询锁

当一个用户对一个数据行执行更新操作且没有提交的时候，其他用户进行相关操作将进行等待，因为oracle已自动加锁

范例2：强制解锁

如果发生一个用户进行update等操作且事务未及时提交或回滚的时候，其他用户的操作将陷入无休止的等待，为了避免这种情况的发生，可以采用系统管理员账户强制解锁。

10、存储过程调用的三种不同方式：

 EXEC[UTE]  过程名(参数1，参数2);

 EXECUTE   过程名;

 过程名; --只需要输入一个过程名字即可

11、Oracle中序列的使用：Sequence

 a. 其维护一个整数值，每次取值会自动增量(+1),其保证每次取值是唯一。

 a. 该整数的最大值：28个9

 a. 缓存（cache）：默认不使用，当使用时，会提前取值N个，效率高。如缓存后未使用，则取出的值，不可再用。

 a. 序列Sequence的消费与使用

         i. select 序列名.nextval from dual;  --取值增量

         i. select 序列名.currval from dual;  --仅取当前值(如未取值，首次执行将失败)

12、锁的类型分为：

   a. 行级锁

   b. 表级锁

   c. 排他锁，独占锁，X锁（禁止会话读取，修改）

   d. 共享锁，S锁（share locks）  允许其他会话读取，但禁止修改

   e. 一旦死锁发生，一般需要dba去手动解锁（杀死引发锁的SQL语句）

13、索引的优劣：

   a. 好处：索引会提升数据的select查询速度

   b. 劣处：索引会降低数据的insert,update,delete处理速度，因为当执行这些操作时，不仅要维护数据，还的维护索引表，增加了工作量

14、NVL（字段，默认值）：当字段为空时，赋默认值；

15、HAVING：过滤分组函数，用在group by后的数据过滤；

16、pivot函数：行转列；pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值))；