oracle 总结

Sql语言
文档说明
每一条的记录，绿色代表的是总结，熟练后记录它就可以，通过它回忆知识点。
敲代码习惯经验
先敲上表，这样敲字段的时候会有提示会比较快
如下：select from emp where ;
层次查询、外链接、子查询top-n问题，关联子查询、from后边子查询有啥意义、数据泵、
Sql优化
--Oracle是自动开启事务
--SQL 优化 2.  where解析顺序：右--》左
--SQL 优化 4. 尽量使用多表查询
--SQL 优化 5. 尽量不要使用集合运算
数据查询慢问题
两个方案：建立索引，或者是扔到缓存。
群主得回答：几百万条数据30s差不多。
在sql service查询中，遇到了查询慢的问题，应为在查询出库单，关联了5张表，之后，加上时间范围，或者加入一条子查询，查不出数据了。
VB群里，让我用，链表查询。
工作中积累的SQL语句
数据延后一年
主任要求将6月份涉及到有问题的，延后一年
首先，数据做备份：
create table t_20170605_kc41 as select * from hbcxjm.kc41 where to_char(aae031,'yyyymm') = '201706';
然后：将6月份延后一年：
update table hbcxjm.kc41 set aae031 = add_months(aae031,12) where to_char(aae031,'yyyymm') = '201706';
十天之内入职的人员
select ename from emp where hiredate > sysdate – 10;
oracle创建主键自增
--首先在plsql创建表
 


--创建序列
create sequence zhanglq_sec
minvalue 1
nomaxvalue
start with 1
increment by 1
nocache;


--创建触发器
create or replace trigger zhanglq_insert
before insert on zhanglq_test
for each row 
  begin 
    select zhanglq_sec.nextval into :new.id from dual;
  end zhanglq_insert; 


--插入数据
insert into zhanglq_test(name,age,birthday) values('李四',24,to_date('2017-03-15','yyyy-mm-dd'));
insert into zhanglq_test(name,age,birthday) values('王五',25,to_date('2017-03-15','yyyy-mm-dd'));


--查询
select * from zhanglq_test;
 
Where/group by/having顺序
-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于1000，没有管理者的员工不计算在内
select mgr,min(sal) from emp 
where mgr is not null 
group by mgr 
having min(sal) > 1000 ;
子查询
--3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select ename,sal,deptno from emp e where sal > (select avg(sal) from emp where e.deptno = deptno);
查询某一日期的数据
select * from ac01 where to_char(aac006,'yyyymmdd') = '19660115';
select * from ac01 where aac006 = to_date('19660115','yyyymmdd');
逗号像句号一样
 
基本查询
select * from emp;
SQL中的null值
加减算法里不能包含null，comm is null，排序时null最大


1.包含null的表达式都为null
2.null永远!=null
3.排序中，null值最大，大于数字，字段有null会显示在首位
select empno,job,comm,sal*10 + nvl(comm,0) from emp;
select * from emp where comm is null; -------comm = null 不对
列的别名
数字不可以做别名，别名：字母、汉字
select empno 员工编号 from emp；
distinct去掉重复记录
distinct放在列的前边，作用于所有列


select deptno from emp;
select distinct deptno from emp;
distinct 作用于后边所有列
select deptno,job from emp;
select distinct deptno,job from emp;
concat(…)+dual
字符窜连接符 ||  更好用 


select concat('Hello','  World') 字符串 from dual;
select concat('3','2') from dual;
simple method：
select 'Hello' || '  World' 字符串 from dual;
select 1 || 2 合并 from dual;
select ename || '的工资是' || sal from emp;
select 2 + 3 from dual; ------5  结果是5，字符串不能这么用。
过滤和排序
过滤
Where 按照日期查询
查询日期是日月年的顺序，这是国外的顺序。---注意多打一个月字


select * from emp where  hiredate = '17-11月-81';
 
注意：默认是这样写可以查到日期。
否则需要设置：
altersessionset NLS_DATE_FORMAT='yyyy-mm-dd';
select * from emp where hiredate = '1980-12-17';


altersessionset NLS_DATE_FORMAT='DD-MON-RR';还原默认的格式
between …and…
between  and: 1. 含有边界  2. 小值在前大值在后
select * from emp where sal between 2000 and 3000;
between … and …是包含边界，当大于、小于，一边有等于号。参考下边即可。
查询某一段时间内的数据
select * from emp where hiredate > to_date('19850101','yyyymmdd') and hiredate <= to_date('19880101','yyyymmdd');
 
select * from emp where hiredate between to_date('19850101','yyyymmdd') and to_date('19880101','yyyymmdd');


 
select * from emp where hiredate > '20-2月-1981' and hiredate < '28-12月-1987';
select * from emp where hiredate between '1-1月-1980' and '30-12月-1981';
（注意：这里的日期，月字前边一定不要空格，否则无效）
in(…..)集合中
in可以含null，not in不可以。 
3. 如果集合中含有null，不能使用not in; 但可以使用in
select * from emp where empno in(10,20,30);
select * from emp where empno  not in (10,20,30);


select * from emp where empno in (10,20,null);
select * from emp where empno not in (10,20,null);----------error
like模糊查询
字符 %、_、\_三类，数据库里一定是单引号
select * from emp where ename like ‘s%’;
--查询名字是4个字的员工
select * from emp where ename like ‘____’; ------四个下划线
--转意字符查询名字里含有_的人名
select * from emp where ename like ‘%\_%’; -----一撇一捺
排序
--order by后面  + 列，表达式，别名，序号 只会用到desc降序从大到小；默认从小到大
select * from emp order by sal;列
select ename,job,sal*12 年薪 from emp order by sal*12 desc;表达式
select ename,job,sal*12 年薪 from emp order by 年薪 desc;别 名
select ename,job,sal*12 年薪 from emp order by  3   desc;序 号


order by 同destinct一样：作用于后面所有的列；desc只作用于离他最近的列
order by 可以添加好几个列！
select * from emp order by sal,empno desc;
select * from emp order by sal desc,empno desc;


select * from emp order by comm desc;-----null最大，数字会显示在下边。
select * from emp order by comm desc nulls last;
 










函数


单行函数
字符函数
Lower(‘’)/upper(‘’)/initcap(‘’)
Select 
lower(‘Hello World’)小写,
 upper(‘Hello World’)大写,
initcap(‘hello  world’) 首字母大写  from  dual；
Substr(‘’)
select substr('helloworld',6) world from dual;   -----------------world
select substr('helloworld',6,10) world from dual; -----------------world
Instr(‘’)
Instart---instr()
select instr('Helloworld','ll') 位置 from dual;-------3
Length(‘’)/lengthb(‘’)
select length('HelloWorld') 字符,lengthb('HelloWorld') 字节 from dual;
10/10
select length('你好') 字符,lengthb('你好') 字节 from dual;
2/4
lpad 左填充  rpad 右填充
参数10是按照字节一共10位！
select lpad('你好',10,'*') 左,rpad('nihao',10,'*') 右 from dual;
 
trim 去掉前后指定的字符
select trim('a' from 'aHelloworlda') from dual;
 
replace(3)
参数：字符窜，需要替换的字符（窜），替换的字符（窜）
select replace('h*llo world','*','e') helloworld from dual;
 
round(,)/trunc(,)
select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1) from dual;
 
select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1) from dual;
 
select round(sysdate,'month'),round(sysdate,'year') from dual;
 
 
笔记next_day下。
sysdate
select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual;
select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;


日期可以相减，但不可以相加
select
(sysdate-hiredate),(sysdate-hiredate)/7,
(sysdate-hiredate)/30月,(sysdate-hiredate)/365年
from emp;
months_between（）
select months_between(sysdate,hiredate) from emp;


这个和日期相减差一点点
select
(sysdate-hiredate)/30 相减,months_between(sysdate,hiredate) 函数
from emp;
add_months（）
select add_months(sysdate,1) from dual;
这个是多加1个月
last_day()
select last_day(sysdate) from dual;
next_day()
select next_day(sysdate,'星期一') from dual;
select next_day(sysdate,’星期日’) from dual；
to_char()
年
select to_char(sysdate,'yyyy') 年 from dual;
月
select to_char(sysdate,'mm') 月 from dual;     ----06
select to_char(sysdate,'month') 月 from dual; ----6月
日
select to_char(sysdate,'dd') 日 from dual;
星期
select to_char(sysdate,'day') 星期 from dual;
整个日期
select to_char(sysdate,'yyyy-mm-dd hh:mm:ss') 日期 from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') 日期 from dual;


整个日期+星期几
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss day') from dual;
 
select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss"  今天是："day') from dual;
 


to_char()处理字符窜（不仅可以处理时间）
查询员工薪水：两位小数千位符货币代码
select to_char(sal,'L9,999.99')员工薪水 from emp;--加上L会多显式一个￥符号。
 
todate()
查询某一段时间内的数据
select *  from emp 
where hiredate 
between to_date('19800101','yyyymmdd') 
and to_date('19811212','yyyymmdd');


查询某一年的人员信息
select * from ac01 where aac006 = to_date('1964','yyyy');
select * from emp1 where hiredate = (‘201706’,’yyyymm’);
nvl2(a,b,c) 
当a不为null，返回b，为null，返回c；
select sal*12+nvl(comm,0) from dual;
select sal*12+nvl2(comm,comm,0) from dual;
nullif(a,b)
用来返回null的吧，a=b，返回null，a！=b，不返回null，返回a
select nullif('abc','abc') from dual;
select nullif('abc','abcd') from dual;
coalesce()
选择列里，不为null的值，出来！
select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;
函数是从comm，sal列的值中，从左到右，选择第一个不为null的值
 
Case...end
并不能改变数据库数据，只是可以查询。
注意：sal 涨后 + 逗号
select ename,job,sal 涨前,
       case job
         when 'president' then sal + 100
         when 'manager' then sal + 200
         else sal +20
       end 涨后
from emp;
decode（）
并不能改变数据库数据，只是可以查询。


比上边更好记，好用一点
select ename,
job,
sal 涨前,
decode(job, 'present', sal + 200, 'manager', sal + 100, sal + 20) 涨后
from emp;


select count(*) total,
     sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) 1980,--1980输出1
     sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) 1981,
     sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) 1982
from emp;
这里不仅可以学到decode()函数，还可以学到sum()函数。
多行函数
sum()
select  sum(sal)  from  emp;
count()
select count(*) from emp;
select count(distinct name) from emp;
avg()
select avg(comm) from emp;
组函数自动滤空
select count(*) from emp; ====== select count(nvl()) 
多表查询
等值查询
where …=…
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;
不等值查询
where …. between … and …
select e.ename,e.empno,s.grade
from emp e,salgrade s 
where e.sal between s.losal and s.hisal;
连接
1. 内连接(自然连接)（效果与多表查询一样，直接用多表查询）
2. 外连接
（1）左外连接 (左边的表不加限制)
       （2）右外连接(右边的表不加限制)
        （3）全外连接(左右两表都不加限制)
3. 自连接（同一张表内的连接）


内连接
直接用多表查询就好了，不需要特意用内链接
内连接：
select d.dname,d.loc,count(ename),avg(sal) from emp e join dept d on e.deptno = d.deptno group by dname,loc;
 


多表查询：
select dname,loc,count(ename),avg(sal) from emp e,dept d where e.deptno = d.deptno group by dname,loc; 
 
外链接
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from emp e,dept d 
where e.deptno = d.deptno 
group by d.deptno,d.dname;
注意：在查询列中查询了组函数（），就必须group by列。
左、右外链接
希望：对于某些不成立的记录，任然希望包含在最后的结果中
SQL>左外连接：写法:where e.deptno=d.deptno(+)（一定包含左边表所有内容）
SQL>右外连接：写法: where e.deptno(+)=d.deptno（一定包含右边表所有内容）




自连接
通过表的别名，把一张表当做两张表用
比如查询同一张表，同一条数据的员工姓名和老板姓名
select e.ename,d.ename from emp e,emp d where e.mgr = d.empno;
 
自连接不适合操作大表
Select count(*) from emp e;            15条数据
Select count(*) from emp e,emp d;
自连接和层次查询
自连接查询和层次查询，都是解决查询同一张表的问题。


首先我们要引入自连接和层次查询的概念：
          自连接：利用表名的别名，将同一张表视为多张表。
          层次查询：对同一张表的前后两次操作，进行连接。
       自连接缺点：自连接不太适合操作大表。
            解决方案：利用层次查询。
      自连接优点：如下图，姓名可以显示在一块，方便查看员工，


层次查询
下面是我将表中的数据整理后画出的树形图：
 
通过上图可以看出各个数据之间的层次关系，level有4级，现在我们通过sql来实现这一关系：
例如：查询员工信息：员工的老板是谁？
LEVEL是一个“伪列”，代表树的第几层。
通过以上sql查询即得到所需要查询员工老板信息，图中KING的MGR（老板）为空，表示他上级没有老板，其中order by 1,这里1代表LEVEL。
sql中的prior empno = mgr表示前一个员工号等于后一个的老板号。


select level, empno, ename, mgr from emp
connect by prior empno = mgr
start with mgr is null
order by 1;
 
纵表和横表
问题：纵表是为了增加字段，plsql developer 直接edit 表，增加字段，需要纵表吗？
 
 


子查询
说明举例
子查询所要解决的问题：不能一步求解
select * from emp where sal > (select sal from emp where ename = 'SCOTT');
注意事项
1.括号
2.合理的书写方式
3.可以在select .. from .. where .. having 后边使用子查询语句
4.可以在 =、>、> any(…)、> all(…)、in(…)、not in(…)使用子查询。(not in 需要注意null问题)
5.不可以在group by 后边使用子查询
6.强调from后面的子查询
7.主查询和子查询可以不是同一张表，只要子查询查出来的数据，主查询可以用就可以了
8.一般不在子查询中排序，但是在top-n问题中必须对子查询排序
9.一般先执行子查询，再执行主查询，但关联子查询例外
10.单行子查询使用单行操作符，多行子查询使用多行操作符
11.子查询中的null
select---子查询
select ename,empno,sal,(select job from emp where empno = 7839) 第四列from emp;
 
主要目的：子查询的出现主要是为了解决多表查询之中的性能问题。
次要目的：很多时候在From子句里面使用子查询，是因为在外部查询中无法再继续使用统计函数操作的时候。
From—后边的子查询（强调）
select * from (select ename,empno,sal from emp);
select ename,empno,sal from emp；
上边两个查询数据一样。
很多时候在From子句里面使用子查询，是因为在外部查询中无法再继续使用统计函数操作的时候。
Where后边的子查询
= 子查询
主查询、子查询可以不是一张表
等号后边可以使用子查询
select * from emp where deptno = (select deptno from dept where dname = 'SALES');
这个子查询和下边多表查询是一样的结果
select e.* from emp e,dept d where e.deptno = d.deptno and d.dname = 'SALES';


>大于号后-子查询
select * from emp where sal>(select min(sal) from emp where deptno = 30 );
select * from emp where sal>(select max(sal) from emp where deptno = 30);
这里边有min（sal）max（sal）函数，积累。
>any(子查询)
any: 和集合中的任意一个值比较
select * from emp where sal >any(select sal from emp where deptno = '30');
>all(子查询)
all和集合中的所有值比较
select * from emp where sal >all(select sal from emp where deptno = 30);
in（子查询）
in()里边可以使用子查询
select * from emp where deptno in(select deptno from dept where dname = 'SALES'or dname = 'ACCOUNTING' );
not in(子查询)
多行子查询中的null-------------not in (10,20,null)
null可以在in（）使用，但是不可以在not in（）使用
查询是老板的员工
select * from emp where empno in (select mgr from emp);
查询不是老板的员工
第一句不好使，第二句不才好使。
select * from emp where empno not in (select mgr from emp);----错
select * from emp where empno not in (select mgr from emp where mgr is not null);
集合运算
简单的实例说明
查询10和20号部门的员工（这三种方式是一样的）
select * from emp where deptno = 20
union
select * from emp where deptno = 30;


select * from emp where deptno in(20,30);
select * from emp where deptno = 20 or deptno = 30;
group by rollup（）
ROLLUP：为每个分组返回小计记录。
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
 
select deptno,job,sum(sal) from emp group by deptno,job;
--这个就很乱上边很好看。
 


上下是相等的，尽量不要使用集合运算。
select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp;
注意：集合查询列个数需要对应起来。
积累：to_number(),to_char()这两个函数。还有to_date()函数。
oracle分页(Pageing  Query)
迅速敲代码习惯：
Select * from … where r >= 5 and r <= 8;
Select rownum r,e.* from
Select * from emp order by sal


select * from 
(select rownum r,e.* from 
(select * from emp order by sal) e ) 
where r >= 5 and r <= 8 ;
总结记忆：套两层子查询，都是在from后的子查询，外边的子查询查rownum(伪列)，内部的子查询需要按照一个列排序。


百度的图片：
 
处理数据
select count(*) total,
       sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) 1980,
       sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) 1981,
       sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) 1982
from emp;
这里有一个函数：decode（）需要处理一下。
decode()--函数和case job 
when  then 
when  then
 end;


SQL类型
1， DML：数据操作语言  insert ,delete,update,select (增删改查)
2， DDL：数据库定义语言 create table,drop table,alter table,truncate table
create view,      drop view
create sequence,  drop sequence
create index,     drop index
create synonym,  drop synonym
3， DCL: 数据控制语言grant(授权)  revoke(撤销权限)
Insert
insert into emp(ename,job,deptno,sal) values('张三','java工程师',10,3000);
insert into emp values('李四','vb工程师'，10，2500);
批处理：
insert into emp10 select * from emp where deptno = 10;


delete
delete from emp where ename = ‘张三’;
注意：这里是直接删除，没有列。
Truncate
truncate table emp_copy;  这个是删除的整个表的内容，delete可以有条件的删除一些数据。
update
update emp set ename = 'SMIHHHHH' where ename = 'SMITH';
这个一般不怎么用，用下边的语句,安全级别高。
select t.*,t.rowid from emp t where ename = 'SMITH';
create
create table emp10 as select * from emp where 1 = 2;
drop
drop table emp purge;  直接删除表


批处理
举例
--创建emp的表结构，复制给emp10
create table emp10 as select * from emp where 1 = 2;
--一次性将emp中，所有10号部门的员工插入到emp10中
insert into emp10 select * from emp where deptno = 10;
海量拷贝数据
1. 数据泵（datapmp） plsql
2. 不记啦


Delete和truncate的区别
1. delete是逐条删除数据，truncate是摧毁表，再重建
2. delete可以回滚（DML），truncate不可以回滚（DDL）
3. delete可以闪回（flahback），truncate不可以闪回
4. delete 会产生碎片，truncate不会
5. delete 不释放空间，truncate会释放空间
6. delete可以删除表中的一条记录，使用where条件。
如：delete from emp where ename = ‘SMITH’;


delete from testdelete；  --------这个更快一点
truncate table testdelete；
事物的标志
1.起始标志：事物中第一条DML语句
2.结算标志：提交  显式  commit
                  隐式 正常退出（exit）DDL DCL
           回滚   显式 rollback
 隐式 非正常退出，掉电，宕机
查看所有的表
select * from user_tables;
 


drop table test;
 
定义保存点
savepoint a;
rollback to savepoint a;
举例：
create table testsavepoint1
(
       tid number,
       tname varchar2(20)
);
insert into testsavepoint1 values('1','tom');
insert into testsavepoint1 values('2','mary');
select * from testsavepoint1;
 
savepoint a;
insert into testsavepoint1 values('3','gerd');
rollback to savepoint a;
select * from testsavepoint1;
 
set transaction read only;这个在plsql好像不管事，需要确定
创建和管理表
举例
create table test(
tid number,
tname varchar2(20),
hiredate date default sysdate
);
select * from test;
insert into test(tid,tname) values(1,'zhangsan');
 
行地址（行id）
select rowid,empno,ename,job from emp;
增加表
从一个表获取数据
保留20部门的数据+表结构
create table emp20 as select * from emp where deptno = 20;
select * from emp20;


保留emp的所有数据+表结构
create table emp21 as select * from emp where 1 = 2;
select * from emp21;
从两个表里获取数据
create table empinfo as 
select e.empno,e.ename,e.sal*12 年薪,d.dname from emp e,dept d where e.deptno = d.deptno;


select * from empinfo;
删除表
drop table test2;
修改表
修改表：追加新列，修改列，删除列，重命名列，重命名表
重命名表
rename test to test5;
追加新列
alter table test add photo blob;
删除列
alter table test drop column photo; 
修改列
修改列的格式
alter table test modify tname varchar2(40);
修改列的名字
alter table test drop column photo; 


查看表
查看所有表
select * from tab;
视图
说明
视图有两个作用：减少查询语句的复杂性；增加查询时的安全性。


比如说图书馆有图书book（id,name,prise....）
读者reader(id.....)
借阅关系 borrow( bookid,readerid,date)
显然存在一张表很不方便操作，分开又不方便查看
那就建立个视图，view1:
select * from book,reader,borrow where book.id=bookid and reader.id=readerid
这样只要查询select * from view1 就可以看到谁借了什么书了，包括所有的详细内容。
有时后会有非常复杂的逻辑，视图可以用来方便理解，可以当作中间层简化逻辑
减少复杂性
复杂：
select d.dname, count(*) num_eaps
  from emp e, dept d
  where e.deptno = d.deptno
  group by dname;
创建视图：
create  view  my_view as
                 select d.dname, count(*) num_eaps
                 from emp e, dept d
                 where e.deptno = d.deptno
group by dname;
简单查询：
Select dname,num_emps from my_view;
增加安全性
我只想要用户BOB看到EMP表中的ENAME 和DEPNO两个列。
not safe
grant select on emp to bob;------bob可以看到emp中的所有列。
safe
create view my_emp as select ename,job from emp;
grant select on my_emp to bob;----bob只可以看到emp中两列。
安全性进一步说明
假设我们想要使得每个人都可以查询EMP表，但是只是他们自己的记录。
create view my_view as select * from emp where ename = user;
grant select on my_emp to public;
system无密码
SQL plus 登录
用户名：scott/password登陆上去。
输入命令：conn /as sysdba 
显示：连接上
输入命令：alter user system identified by system;
这样密码就修改成了system
创建视图
权限不足，需要system登录，给权限。


分配创建视图权限
grant create view to scott;
创建语句
视图可修改
create view my_view as select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;


select t.*,t.rowid from my_view t;------------------修改语句一样
视图只读
create view my_view as select e.ename,e.sal*12 nianxin,d.dname from emp e,dept d where e.deptno = d.deptno with read only;
删除视图
drop view my_view;
序列
说明：序列是一个计数器。
作用：实现表的主键自增，或者作为计数器。
创建序列
实现主键自增
create sequence my_seq;


create sequence seqEmp 
increment by 1 
start with 1 
maxvalue 3 
minvalue 1;------最多传入3条数据
两个对象
select my_seq.nextval from dual;
select my_seq.currval from dual;
使用
insert into test_seq values(my_seq.nextval,'aaa');
 
回退操作
当我们insert插入数据，执行命令：rollback，插入的数据回退了。
回退对序列不管用，序列，这次是5，下次就是6。
删除序列
drop sequence seqEmp;
索引
索引，使用索引可快速访问数据库表中的特定信息。
有了索引，查询及更新速度可能会快很多倍！
create index myindex on emp(deptno);
同义词
说明：就是表的别名
作用：在开发中，数据库常常不是一个用户，比如scott，scott1，Scott2。当我在scott中，已经得到授权，访问Scott1中的表emp，需要这样 scott1.emp，当为表创建别名的时候，就可以直接用别名访问了。
权限语句
grant create synonym to scott;
创建语句
create synonym myemp for emp;


下边这个需要确定一下：
create synonym myemp1 for hr.employees;
plsql
它是面向过程的语言，当处理复杂逻辑的问题时，需要用到plsql语句。


例如：职工每人涨10%的工资，update就可以完成。
 职工，经理涨1000，主任涨800，员工涨400，这种情况就得用plsql语言。


Plsql和jdbc的作用一样,我们不是把增删改查写到Java代码里，而是调用plsql程序，效率更高。
一点经验：
在医保系统里面，是将数据表结构写在了.xml文件中，具体如何调用的数据库，需要学习。
入门案例
declare 
  
begin
  dbms_output.put_line('HelloWorld');-----------记住这个语句
  
  dbms_output.put_line('HelloWorld');
  
end;
变量
Varchar2() number 需要把ppt上的变量写来！




示例
declare 
  pname varchar2(20);
  psal number;
begin
  select ename,sal into pname,psal from emp where empno = 7839 ;
  dbms_output.put_line(pname || '的工资是' || psal);
end;
引用性变量
declare 
  pname emp.ename%type;
  psal emp.sal%type;
begin
  select ename,sal into pname,psal from emp where empno = 7839 ;
  dbms_output.put_line(pname || '的工资是' || psal);
end;
记录型变量
定义记录型变量：代表一行
declare 
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7839;
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
光标
把大量的变量放大光标里边
1、光标的属性：
%isopen  %rowcount  %found  %notfound
2、默认一个会话中只能打开400个光标。


declare
  cursor cemp is select ename,sal from emp;
  pname emp.ename%type;
  psal  emp.sal%type;
begin
  open cemp;
    loop
      fetch cemp into pname,psal;
      exit when cemp%notfound;
      dbms_output.put_line(pname ||' ++' ||psal);
    end loop;
  close cemp; 
end;
带参数的光标
declare 
  -- Local variables here
  cursor cemp(dep number) is select ename from emp where deptno = dep;
  pname emp.ename%type;
begin
  -- Test statements here
  open cemp(20);
    loop
      fetch cemp into pname;
      exit when cemp%notfound;
      dbms_output.put_line(pname);
    end loop;
  close cemp;
end;
循坏
declare
 pnum number := 1;
begin
  loop
    exit when pnum > 10;
    dbms_output.put_line(pnum);
    
    pnum := pnum + 1;
  end loop;
end;
系统例外
declare
 pnum number;
begin
  pnum := 3/3;
  exception
    when zero_divide then dbms_output.put_line('1:0零不能做分母');
                          dbms_output.put_line('2:0零不能做分母');
    when value_error then dbms_output.put_line('算术或者转换错误');
    when others       then dbms_output.put_line('其他例外');
end;
自定义例外
declare
 cursor cemp is select ename from emp where deptno = 50; 
 pname emp.ename%type;
 --自定义例外
 no_found_emp exception;
begin
  open cemp;
   fetch cemp into pname;
     if cemp%notfound then
       --抛出异常
       raise no_found_emp;
     end if;
  close cemp;
  
  exception
    when no_found_emp then dbms_output.put_line('没有找到其他员工');
    when others then dbms_output.put_line('其他例外');
end;


deptno = 50   这边输出’ 没有找到其他员工’;
deptno = 1/0  这边输出’ 其他例外’;
触发器
create trigger abcd after insert on emp 
declare 
  -- Local variables here
  i integer;  
begin
  -- Test statements here
  dbms_output.put_line(' 成功插入一条数据');
end;




declare 
  -- Local variables here
  i integer;  
begin
  -- Test statements here
  insert into emp(empno,ename,job) values(111,'张三','程序员');
end;
触发器应用一
实施复杂的安全性检查：禁止在非工作时间插入新员工
create or replace trigger security before insert on emp
declare 
  -- Local variables here
begin
  -- Test statements here
 if to_char(sysdate,'day') in('星期一','星期三','星期日') or to_number(to_char(sysdate,'hh24')) between 9 and 17
     then raise_application_error(-20001,'禁止在工作时间外插入数据');
---这个需要看视频咋说的，抛出错误提示。
 end if; 
end;
插入数据然后有提示
declare 
  -- Local variables here
begin
  -- Test statements here
  insert into emp(empno,ename,job) values(20,'测试','测试');
end;
 
触发器应用二
数据的确认：涨后的工资不能少于涨前的工资
create or replace trigger checksal before update on emp for each row 
declare 
  -- Local variables here
begin
  -- Test statements here
  if
    :new.sal < :old.sal then raise_application_error(-20002,'张倩的工资：'||:old.sal||',不能低于涨后的工资:'||:new.sal);
  end if;
end;
更新数据:
declare 
  -- Local variables here
begin
  -- Test statements here
  update emp set sal = sal - 100 ;
end;
 


第一个存储过程
创建存储过程
create or replace procedure sayHelloWorld as 
begin
  dbms_output.put_line('HelloWorld'); 
end;


1. exec sayHelloWorld(); ----------------------------------这句话不管事
2. begin
    -- Test statements here
    sayHelloWorld();
    sayHelloWorld();
end;


Oracle 导出导入数据 
　　最近用到Oracle导出导入数据，在网上看了别人的一些文章，总结如下：
　　Oracle导出导出有两中方式：一、利用exp imp导出导入；二、利用Oracle数据泵expdp impdp导出导入。
　　一、利用exp imp导出导入
　　exp imp 语法如下：
　　exp：
　　1) 将数据库orcl完全导出
　　　　exp system/manager@orcl file=d:\orcl_bak.dmp full=y
　　2) 将数据库中system用户的表导出
　　　　exp system/manager@orcl file=d:\system_bak.dmp owner=system
　　3) 将数据库中表table1，table2导出
　　　　exp system/manager@orcl file=d:\table_bak.dmp tables=(table1,table2)
　　4) 将数据库中的表customer中的字段mobile以"139"开头的数据导出
　　　　exp system/manager@orcl file=d:\mobile_bak.dmp tables=customer query=\"where mobile like '139%' \"
　　imp：
　　1) 将备份文件bak.dmp导出数据库
　　　　imp system/manager@orcl file=d:\bak.dmp
　　　　如果数据表中表已经存在，会提示错误，在后面加上ignore=y就可以了。　　
　　2) 将备份文件bak.dmp中的表table1导入
　　　　imp system/manager@orcl file=d:\bak.dmp tables=(table1)
　　exp imp导出导入数据方式的好处是只要你本地安装了Oracle客户端，你就可以将服务器中的数据导出到你本地计算机。同样也可以将dmp文件从你本地导入到服务器数据库中。但是这种方式在Oracle11g版本中会出现一个问题：不能导出空表。Oracle11g新增了一个参数deferred_segment_creation，含义是段延迟创建，默认是true。当你新建了一张表，并且没用向其中插入数据时，这个表不会立即分配segment。
　　解决办法：
　　1、设置deferred_segment_creation参数为false后，无论是空表，还是非空表，都分配segment。
　　在sqlplus中，执行如下命令：
　　SQL>alter system set deferred_segment_creation=false;
　　查看：
　　SQL>show parameter deferred_segment_creation;
　　该值设置后，只对后面新增的表起作用，对之前建立的空表不起作用，并且注意要重启数据库让参数生效。
　　2、使用 ALLOCATE EXTEN
　　使用 ALLOCATE EXTEN可以为数据库对象分配Extent，语法如下：
　　alter table table_name allocate extent
　　构建对空表分配空间的SQL命令：
　　
　　SQL>select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
　　批量生成要修改的语句。
　　然后执行这些修改语句，对所有空表分配空间。
　　此时用exp命令，可将包括空表在内的所有表导出。
　　
　　二、利用expdp impdp导出导入
　　在Oracle10g中exp imp被重新设计为Oracle Data Pump（保留了原有的 exp imp工具）
　　
　　数据泵与传统导出导入的区别;
　　1) exp和imp是客户端工具，他们既可以在客户端使用，也可以在服务端使用。　　
　　2) expdp和impdp是服务端工具，只能在Oracle服务端使用。
　　3) imp只适用于exp导出文件，impdp只适用于expdp导出文件。
　　
　　expdp导出数据：
　　1、为输出路径建立一个数据库的directory对象。
　　　　create or replace directory dumpdir as 'd:\';
　　　　可以通过：select * from dba_directories;查看。
　　2、给将要进行数据导出的用户授权访问。
　　　　grant read,write on directory dumpdir to test_expdp;
　　3、将数据导出
　　　　expdp test_expdp/test_expdp directory=dumpdir dumpfile=test_expdp_bak.dmp logfile=test_expdp_bak.log schemas=test_expdp
　　　　注意：这句话在cmd窗口中运行，并且最后不要加分号，否则会提示错误。因为这句话是操作系统命令而不是SQL。
　　impdp导入数据：
　　　　1、给将要进行数据导入的用户授权访问。
　　　　　　grant read,write on directory dumpdir to test_impdp;
　　　　2、将数据导入
　　　　　　impdp test_impdp/impdp directory=dumpdir dumpfile=test_expdp_bak.dmp remap_schema=test_expdp:test_impdp