insert into t_stu(stu_id,stu_name) values (1,'zhangsan');
2010-05-20
oracle pl/sql语句大全
文章分类:数据库
例
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);
--------
练习和答案
有没有工资比奖金多的雇员？如果有，按工资的降序排列。
如果有两个以上的雇员工资相同，按他们的名字排序。
select ename employee,sal salary,comm commision
from emp
where sal>comm
order by sal desc,ename;
列出有关雇员姓名、奖金占收百分比的信息。
要求显示时列名意义明确，按雇员姓名排序，不包括奖金未知的雇员。
select ename employee,(comm/(comm+sal))*100 incentive
from emp
where comm is not null
order by ename;
在chicago（部门30）工作的所有雇员的工资上涨10%。
update emp
set sal=1.1*sal
where deptno=30;
update emp
set sal=1.1*sal
where deptno=(select deptno
from dept
where loc='chicago');
为hitech公司新建一个部门，编号为50，其它信息均不可知。
insert into dept(dname,deptno)
values('faclities',50);
创建视图，三个列名，其中不包括职员信息
create view employee("employee name",
"employee number",
"employee job")
as select ename,empno,job
from emp
where job!='clerk';
制作工资报表，包括雇员姓名、受雇时间（按星期计算），工资和部门编号，
一页显示一个部门的信息，每页尾，显示该页的工资之和以及受雇时间之和，
报表结尾处，显示所有雇员的工资总和以及受雇时间总和，
工资按美元计算，受雇时间按星期计算，每页的上方应有标题。
ttitle 'service'
break on deptno on page on report
compute sum of sal on deptno
compute sum of sal on report
compute sum of service_length on deptno
compute sum of service_length on report
column sal format $99,999.00
column service_length format 9999
select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal
from emp
order by deptno;
制作报表，包括雇员姓名、总收入和受佣日期，
且：姓名的第一个字母必须大写，雇佣日期格式为MM/DD/YYYY，
