PL/SQL基础（二）

6 函数 
字符型函数 
initcap(ename);将ename中每个词的第一个字母改为大写。 
如：jack smith--Jack Smith 

length(ename);计算字符串的长度。 

substr(job,1,4); 

其它 
lower 
upper 
least    取出字符串列表中按字母排序排在最前面的一个串 
greatest 取出字符串列表中按字母排序排在最后的一个串 

日期函数 
add_month(hiredate,5) 在雇佣时间上加5个月 
month_between(sysdate,hiredate) 计算雇佣时间与系统时间之间相差的月数 
next_day(hiredate,'FRIDAY') 计算受雇日期之后的第一个星期五的日期 

例 
select ename,sal,next_day(sysdate,'FRIDAY') as_of  from emp where deptno=20;（as_of是别名） 

如果不用to_char函数，日期在ORACLE中的缺省格式是'DD_MON_YY' 
to_char(date,date picture) 

select ename,to_char(hiredate,'Dy Mon dd,yyyy') hired from emp  where deptno=10; 

to_date(字符串,格式) 

insert into emp(empno,ename,hiredate) 
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS')); 

日期型数据的格式 
dd 12 
dy fri 
day friday 
ddspth twelfth 

mm 03 
mon mar 
month march 

yy 87 
yyyy 1987 

例 
Mar 12,1987    'Mon dd,yyyy' 
MAR 12,1987    'MON dd,yyyy' 
Thursday MARCH 12    'Day MONTH dd' 
Mar 12 11:00am    'Mon dd hh:miam' 
Thu,the twelfth    'Dy,"the"ddspth' 

算术函数 
least(v1,v2) 

select ename,empno,mgr,least(empno,mgr) lownum  from emp  where empno0 

trunc(sal,0) 
取sal的近似值（截断） 

空值函数 
nvl(v1,v2) 
v1为列名，如果v1不是空值，nvl返回其列值。 
v1为空值，返回v2的值。 

聚组函数 
select sum(comm) 
from emp; 
（返回一个汇总信息） 
不能把sum用在select语句里除非用group by 

字符型、日期型、数字型的聚组函数 
min max count可用于任何数据类型 

select min(ename)  from emp; 

select min(hiredate)  from emp; 

select min(sal)  from emp; 

有多少人有工作？ 
select count(job)  from emp; 

有多少种不同的工种？ 
select count(distinct job)  from emp; 

count distinct 计算某一字段中不同的值的个数 

其它聚组函数（只用于数字型数据） 
avg 计算平均工资 
select avg(sal)  from emp; 

stddev 计算工资的平均差 
select stddev(sal)  from emp; 

sum 计算总工资 
select sum(sal)  from emp; 

group by子句 
select deptno,sum(sal),avg(sal)  from emp group by deptno; 

按多个条件分组 
每个部门的雇员数 
select deptno,count(*)  from emp  group by deptno; 

每个部门的每个工种的雇员数 
select deptno,job,count(*)  from emp  group by deptno,job; 

满足条件的分组 
（where是针对select的，having是针对group by的） 
哪些部门的工资总和超过了9000 
select deptno,sum(sal)  from emp  group by deptno  having sum(sal)>9000; 

select小结 
除去职员，哪些部门的工资总和超过了8000 
select deptno,sum(sal)  from emp  where job!='clerk' group by deptno  having sum(sal)>8000 
order by sum(sal); 

 

7　高级查询　
等值联接　
select　empno,ename,job,emp.deptno,dname　from　emp,dept　where　emp.deptno=dept.deptno;　

外联接　
select　ename,dept.deptno,loc　from　emp,dept　where　emp.deptno(+)=dept.deptno;　
如果在dept.deptno中有的数值，在emp.deptno中没有（如deptno=40），　
则作外联接时，结果中会产生一个空值　

自联接：同一基表的不同行要做联接，可使用自联接　
指出每个雇员的经理名字　
select　worker.ename,manager.ename　manager　from　emp　worker,emp　manager　where　worker.mgr=manager.empno;　

非等值联接　
哪些雇员的工资属于第三级别　
select　ename,sal　from　emp,salgrade　where　grade=3　and　sal　between　losal　and　hisal;　
（基表salgrade：grade　losal　hisal）　

集合运算　
行的连接　
集合运算把2个或多个查询结果合并为一个　
union-set　union　
Rows　of　first　query　plus　of　second　query,　less　duplicate　rows　

intersect-set　intersection　
Rows　both　queries　have　in　common　

minus-set　difference　
rows　unique　to　the　first　query　

介绍几个视图　
account　view　
ename　sal　job　

sales　view　
ename　sal　job　

research　view　
ename　sal　job　

union运算　
返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起　
所有部门中有哪些雇员工资超过2000　
对应列的数据类型必须相同　
select　ename,sal　 from　account　where　sal>2000　union　
select　ename,sal　 from　research　where　sal>2000　union　
select　ename,sal　 from　sales　where　sal>2000;　

intersect运算　
返回查询结果中相同的部分　
各个部门中有哪些相同的工种　
select　job　 from　account　intersect　

select　job　 from　research　intersect　 select　job　 from　sales;　

minus运算　
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。　
有哪些工种在财会部中有，而在销售部中没有？　
select　job　from　account　minus　select　job　from　sales;　

子查询　
slect　ename,deptno　from　emp　where　deptno=(select　deptno　 from　emp　where　ename='smith');

多级子查询　
select　ename,job,sal　from　emp　 where　job=(select　job　from　emp　where　ename='clark')　or　sal>(select　sal　from　emp　where　ename='clark');　

多个基表与子查询　
select　ename,job,sal　 from　emp,dept　where　loc='new　york'　and　emp.deptno=dept.deptno　and　sal>(select　sal　from　emp　where　ename='scott');　

子查询中使用聚组函数　
select　ename,hiredate　from　emp　 where　hiredate=(select　min(hiredate)from　emp);　

 

8　授权　
系统权限　
DBA　      所有权限　
RESOURCE　 注册，创建新的基表　
CONNECT，  注册，查询　

只有DBA才有权创建新的用户　
grant　connect　to　scott　identified　by　tiger;　

DBA或用户自己可以改变用户口令　
grant　connect　to　scott　identified　by　leopard;　

基表权限1　
有两种方法获得对基表操作的权限　

创建自己的基表　
获得基表创建用户的许可　
grant　select,insert　on　emp　to　scott;　

这些权限有　
select　insert　update　delete　alter　index　

把所有权限授于他人　
grant　all　on　emp　to　scott;　

同义词　
select　*　from　scott.emp　

创建同义词　
为用户allen的EMP基表创建同义词employee　
create　synonym　employee　for　allen.emp　

基表权限2　
你可以使其他用户有这样的权力，即其他用户可以把你的基表权限授予他人　
grant　all　on　emp　to　scott　with　grant　option;　

收回权限　
系统权限　只有被DBA收回　

基表权限　随时都可以收回　

revoke　insert　on　emp　from　scott;　

 

9　索引　
建立索引　
create　index　emp_ename　on　emp(ename);　

删除索引　
drop　index　emp_ename;　

关于索引　
只对较大的基表建立索引（至少50条记录）　
建立索引之前插入数据　
对一个基表可建立任意多个索引　
一般是在作为主键的列上建立索引　
建立索引之后，不影响SQL命令的执行　
建立索引之后，ORACLE自动维护和使用索引　

保证数据唯一性　
提高执行速度的同时，索引还可以保证每个记录中的每个列值是不重复的。　
create　unique　index　emp_empno　on　emp(empno);