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;