Oracle数据库易遗漏的知识点（二）
1.exists和in
我们区分in和exists主要是驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
2.merge into
create table test1(eid number(10), name varchar2(20),birth varchar(255),salary number(8,2));
insert into test1 values (1001, '张三', '2015-1-8', 2300);
insert into test1 values (1002, '李四', '2013-1-18', 6600);
select * from test1;
create table test2(eid number(10), name varchar2(20),birth varchar(255),salary number(8,2));
select * from test2;
merge into test2
using test1
on (test1.eid = test2.eid)
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert
(eid, name, birth, salary)
values
(test1.eid, test1.name, test1.birth, test1.salary);3.参照完整性约束foreign key
建立如下两张表
CREATE TABLE person
(
pid NUMBER primary key,
name VARCHAR(30) NOT NULL ,
tel VARCHAR(50) ,
age NUMBER
) ;
CREATE TABLE book
(
bid NUMBER ,
name VARCHAR(50) ,
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
) ;person表中有如下数据：
参照完整性约束要求：如果某个字段是外键 那么该值要么为空 要么在主键表中 有对应的主键值
所以往book中插入的数据pid要么是12，要么为null
可见，当插入的pid为13时报错
4.视图View
视图的数据来源于基础表，修改视图的数据当然就会修改基础表，建议不要修改视图数据，要修改数据直接在基础表修改。 如果要把视图设置成只读，后面加上With READ ONLY
5.索引
建立索引的优点
1.大大加快数据的检索速度; 2.创建唯一性索引，保证数据库表中每一行数据的唯一性; 3.加速表和表之间的连接; 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
索引的缺点
1.索引需要占物理空间。 2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
索引的创建和效率
create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
这两种索引方式是不一样的
索引abc对Select * from student where sid=1; 这样的查询语句更有效
索引abc1对Select * from student where sname=‟louis‟; 这样的查询语句更有效
另外，如果经常查询x=?和y=?，那推荐使用组合index(x,y)，这种情况下组合索引的效率是远高于两个单独的索引的。
6.SQL优化
SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，执行过程中访问尽量少的数据块，减少表扫描的I/O次数，尽量避免全表扫描和其他额外开销。
1.正确使用exist和in（本文第一点有说明）
2.尽量用NOT EXISTS或者外连接替代NOT IN操作符，因为NOT IN不能应用表的索引
3.尽量不用“<>”或者“!=”操作符，不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。比如：a<>0 改为 a>0 or a<0
4.在设计表时，把索引列设置为NOT NULL，判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
5.尽量不用通配符“%”或者“_”作为查询字符串的第一个字符。当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用T表中Column1 LIKE "%5400%‟ 这个条件会产生全表扫描，如果改成Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用Column1的索引进行两个范围的查询，性能肯定大大提高。
6.WHERE后面的条件顺序要求，WHERE后面的条件，表连接语句写在最前，可以过滤掉最大数量记录的条件居后。
7.使用表的别名，并将之作为每列的前缀，这样可以减少解析时间
8.如果数据本身重复行存在可能性较小时，用union all会比用union效率高很多！
9.尽量使用cached sequences 来生成primary key ：提高主键生成速度和使用性能。
10.很好地利用空间：如用VARCHAR2 数据类型代替CHAR等
7.序列SEQUENCE
CREATE SEQUENCE numberIndex
START WITH 1 -- 从1开始计数
INCREMENT BY 10-- 每次加几个
MAXvalue 50-- 不设置最大值NOMaxvalue
CYCLE<span style="white-space:pre"> </span>--NOCYCLE
NOCache;--NOCache8.PLSQL代码块
------------------declare if-else
DECLARE
SCORE DECIMAL;
BEGIN
SCORE := 75;
IF (SCORE < 60) THEN
DBMS_OUTPUT.PUT_LINE('及格');
ELSE
DBMS_OUTPUT.PUT_LINE('不及格');
END IF;
END;
------------------declare case-when
DECLARE
SCORE INTEGER;
BEGIN
SCORE := TRUNC(DBMS_RANDOM.VALUE(0, 100));
CASE
WHEN SCORE < 60 THEN
DBMS_OUTPUT.PUT_LINE('及格');
ELSE
DBMS_OUTPUT.PUT_LINE('不及格');
END CASE;
END;
----------------while
DECLARE
T INTEGER;
BEGIN
T := 0;
WHILE T < 5 LOOP
DBMS_OUTPUT.PUT_LINE(T);
T := T + 1;
END LOOP;
END;也可以选择手动输入
T := &en;
9.游标
create table emp(
empno integer,
ename varchar2(20)
);
insert into emp values(1, 'll');
insert into emp values(2, 'mm');DECLARE
CURSOR mycur IS SELECT * FROM emp;
empInfo emp%ROWTYPE;
BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开
FOR empInfo IN mycur LOOP
DBMS_OUTPUT.put_line('雇员编号：' || empInfo.empno);
DBMS_OUTPUT.put_line('雇员姓名：' || empInfo.ename);
END LOOP;
END;