Oralce笔记02

22、什么是索引
(1)一种能够提供查询效率的数据库对象。
(2)通过快速定位数据的方法，减少磁盘I/O操作。
(3)索引信息与表独立存放。
(4)Oralce数据库自动使用和维护索引。

索引的分类：唯一性索引、非唯一性索引

创建索引的两种方式：
(1)自动创建：在定义主键或唯一键约束的时候，系统会自动在相应字段创建唯一性索引。
(2)手动创建：用户可以自己在其他字段上创建非唯一性索引，以提高查询效率。

23、创建/删除索引
使用create index 创建索引：
create index [schema.]index
on table(column[,columns]....);

举例：
create index myindex on emp(ename);

删除索引：
(1)使用drop index 删除索引.
(2)操作者必须是索引的所有者,或者拥有drop index的权限。
(3)删除表时，相关的索引和约束会被删除，但是视图和序列会被保存。
举例：
drop index myindex

23、创建索引的原则
下述情况可以创建索引：
(1)字段取值分布范围很广.
(2)字段中包含大量空值
(3)字段经常出现的where字句或连接条件中.
(4)表经常被访问，而且数据量很大，并且每次数据的访问量不超过数据总量的2% ~ 4%;

下列情况不适合创建索引：
(1)表很小
(2)字段不经常出现在where字句中
(3)访问的数据量不超过总量的2% ~ 4%;
(4)表经常更新。
(5)被索引字段作为表达式的一部分被引用(此时索引是不起作用的);

24、查看约束

(1)得到当前用户下的所有索引：select * from user_indexes;
(2）查看索引建立在哪些字段上：select * from user_ind_columns;

25、创建基于函数的索引
基于表达式的索引被称为基于函数的索引-索引表达式由表中的字段、常量、sql函数和自定义函数构建而成。

语法：
create index [schema.] index_name on table(function(column_name));
举例：
create index my index on emp(lower(ename));

使用函数索引：
select * from emp where lower(ename) = 'king';

25、序列
(1)序列是系统自动生成，不重复的整数值
(2)序列是一种数据库对象，可以被多个用户共享
(3)经典用途是作为主键值，它对于每一行都必须是唯一的
(4)序列可以代替应用程序编号
(5)可以对序列进行缓冲存储，提高访问效率

创建序列
可以使用create sequence语句创建序列
create sequence [schema.] sequence
[increment by n]
[strat with n]
[{maxvalue n |nomaxvalue  }]
[{minvalue n | nominvalue}]
[{cycle | nocycle}] --是否循环:如果达到序列的最大值，启用循环时会从1开始，而不是start with中指定的值,如果不循环则报错。
[{cache n |nocache}] --为了提高效率可以一次生成多n序列值,提高访问效率
[{order | noorder}]; --是否按照请求的顺序分配序列(多线程环境下可能会不连续但唯一);


举例：
create sequence mysequence1 increament by 1 start with 1 nocache nocycle;

select * from user_sequences:获取用户序列信息

26、使用序列
伪列介绍
(1)nextval:用于从指定的序列值中取出下一个序列值
(2)currval:用于从指定的序列中获取序列的当前值,

语法格式：
select mysequence1.nextval from dual;
select mysequence1.currval from dual;
insert into test (mysequence1.nextval,'Tom');
说明：
(1)使用缓存(cache n)可以提高访问效率
(2)序列在下列情况下可能会出现不连续的情况
   ☻：回滚
   ☻：系统崩溃
   ☻：多个表使用同一个序列
(3)使用nocache和 order 会降低运行效率

27、修改序列
语法格式：
alter sequence [schema.]sequence
[increament by n]
[{maxvalue n | nomaxvalalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}]
[{order | noorder}]

注意事项：
(1)操作者必须是该序列的所有者，或者拥有alter sequence的权限
(2)只有未来在生成的序列数会受到影响
(3)序列的初始值不能修改即：修改序列是不允许出现 start with 选项
(4)更改中会有一些验证，比如修改后的maxvalue如果小于序列的当前值就会报错。

28、删除序列
drop sequence sequence_name  必须拥有删除权限

29、同义词
同义词相当于对对象的别名，使用同义词可以：
(1)方便访问其他用户的对象
(2)缩短对象名字的长度
创建同义词：
create [public] synonym synonym  for object;

create synonym gt1 for emp;

使用同义词：
select * from gt1;
删除同义词：
drop synonym gt1;

说明：如果在创建时加上public 选项，就说明该同义词是公用的，可以在其他任何一个用户下使用。如果不加则只能在本用户下使用
例如：在sys用户下创建scott用户下对象的同义词
create public synonym gt2 for scott.emp;

for 后面必须加上[方案名]scott，gt2前面不能加方案名，因为该同义词是public的。

30、常用函数总结
(1)NVL
NVL函数：用于将控制null替换成指定的缺省值，适用于字符、数字、日期等类型数据
语法格式：NVL(exp1,exp2)
说明：如果表达式exp1的值为null，则返回exp2的值，否则返回exp1的值
举例：
select empno,ename,sal,comm, sal + NVL(comm,0) from emp;
select empno,ename, hiredate, NVL(hiredate,sysdate) from emp;
select empno,ename, job,NVL(job,'not job ye') from emp;

(2)NVL2
NVL2函数用于实现条件表达式功能
语法格式：NVL2(exp1,exp2,exp3)
说明：如果exp1的值不为null则返回exp2的值，否则返回exp3的值。
举例：
select empno,ename,sal,comm,NVL2(comm,comm+sal,sal) total from emp;

(3)NULLIF
NULLIF函数用于数据等价比较，并根据比较结果返回null或其中一个被比较的值。
语法格式：NULLIF(exp1,exp2)
说明：如果exp1与exp2的值相等则返回null,否则返回exp1的值。
用法举例：
select name 原名,NULLIF(open_name,name) 化名 from author;

(4)COALESCE
COALESCE函数用于实现数据的"结合"功能
语法格式：coalesce(exp1,exp2)
说明：以此考察各参数表达式，遇到非null值即停止并立即返回该值。
用法举例：
select empno,ename,coalesce(sal+comm,sal,0)total from emp;

(5)CASE表达式用于实现多路分支结构
语法格式：
case when comparsion_exp1 then return_exp1
    [when comparsion_exp2 then return_exp2
     when comparsion_expn then return_expn
     else else_exp]
end [as 别名]

用法举例：
select empno,empname,sal
   case deptno when 10 then '财务部'
               when 20 then '研发部'
               when 30 then '销售部'
               else '其他部门'
   end [as] 部门
from emp;

(6)DECODE()函数
跟CASE表达式类似，decode函数也用于实现多路分支结构。
语法格式：
decode(col |expression, search1,result1
                       [,search2 result2,.......]
                       [,default])

举例：
select empno,ename,sal,
  decode(deptno,10,'财务部',
                20,'研发部',
                30,'销售部'
                '未知部门'
  )部门
from emp;


(7)函数的嵌套
☺：单行函数可以嵌套使用，嵌套层次无限制
☺：嵌套函数的执行顺序是由内到外;
举例：
select empno,lpad(initcap(trim(ename)),10,'') name, job,sal from emp;

31、分组函数总结
分组函数对一组数据进行计算，针对一组数据(多行记录)只返回一个结果，也称多行函数。
常用分组函数：

[img]http://dl.iteye.com/upload/attachment/549139/21e09445-7859-3ee4-9f35-a154ce9f0294.jpg[/img]

对于分组函数需要注意的是它会忽略值为空的数据
例如：
select count(comm) from emp;
这条sql语句统计出来的结果是4条,会忽略comm等于空的记录;
求平均薪水：
(1)select avg(sal + comm) from emp;
(2)select avg(sal + nvl(comm,0)) from emp;
比较一下两句的不同之处。


32、关于分组函数中group by 字句一些说明
(1)出现在select列表中的字段，如果不不是包含在组函数中，则必须同时在group by 子句中出现。
(2)包含在group by字句中的字段，则不必须出现在select的列表中。

33、组函数的错误用法
(1)如果没有group by字句，则在select 列表中不允许出现字段(单行函数)与分组函数混用的情况。
   select ename,empno from emp; //合法
   select avg(sal) from emp; //合法
   select empno,avg(sal) from emp;  //非法

(2)不允许在where条件中使用分组函数

select deptno,avg(sal) from emp 
    where avg(sal) > 2000 
group by deptno;

说明：上面这句执行会报错，此处涉及到sql语句执行顺序的问题。需要对数据库服务器解析sql的顺序有关。数据库服务器首先会执行where字句，将需要过滤的数据过滤掉然后执行group by进行分组 然后再分组后的数据中筛选出select字句中需要查询 的字段。此句中avg(sal)的值还无法进行计算，所以会报错。

想要实现上句所表达的意思 可以使用having字句：
select deptno,avg(sal) from emp 
  group by deptno;
  having avg(sal) > 2000;