Oracle学习

条件表达式
case表达式---sql99
case when 'MANAGER'  then sal+800
  else sal+400
expr when comparison_expr1 then return expr1
 when comparison_expr2 then return expr2
end 涨后
from emp;
//when相当于'='
case  when grad<60 then ***
 when grad>60 then ***
 grad ***
end


select ename,job,sal 涨前,case job when 'PRESIDENT' then sal+1000
 end 涨后
from emp;


decode函数---oracle中函数
select ename, job, sal 涨前, decode(job,'PRESIDENT'，sal+1000,
'MANAGER',sal+800,
sal+400
) 涨后
from emp;




多行函数
分组函数
avg,count,max,min
select sum(sal) from emp;
selct count(sal) from emp;
select avg(sal) from emp;
count(*) 与 count(sal)，count(nvl(comm,0))
   自动滤空 针对null的处理，具体针对需求来处理
select avg(distinct(sal)) from emp;
分组数据 groupby
***在select列表中所有未包含在组函数中的列都应该包含在group by子句中
***包含在group by子句中的列不必包含在select列表中
select a,b,c,组函数(x)
from emp  
group by a,b,c，d;
多个列的分组
select deptno,job,sum(sal)
from emp
group by deptno,job
order by 1;
过滤分组
where与having最大区别：where后不可以使用组函数。 其他可以通用
select deptno,avg(sal)
from emp
where deptno=10
group by deptno;

select deptno avg(sal)
from emp
group by deptno
having deptno=10
注意上面两句位置 where 与  having
如果where与having都可以用，尽量使用where；
where先过滤再分组
group by语句增强 报表中常用
分组后的小结， 以及整个分组的总和
   select deptno,job,sum(sal) from emp group by deptno,job
+
select deptno,sum(sal) from emp group by deptno
+ 
select sum(sal) from emp
=
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

group by rollup(a,b)
=
group by a,b 
+ 
group by a
+ 
group by null;
还可以按照格式输出，   同一组只显示一个组号
break on可以设置格式

***多表查询
笛卡尔集 多表查询的数学基础
连接条件 emp.deptno = dept.deptno;
oracle的连接 sql99连接
等值连接 cross join
不等值连接
外连接
自连接

外连接 按部门统计员工人数
select d.deptno 部门号, d.dname 部门名称,count(e.empno) 人数
from emp e , dept d
where e.deptno = d.deptno;
左外连接 等号左边的仍然被包含在结果中
写法：e.deptno = d.deptno(+)
右外连接
e.deptno(+) = d.deptno
自连接 通过表的别名将同一张表视为多张表
效率问题， 不适合操作大表
层次查询————————一张表  
员工姓名 员工老板  类似一棵树深度 level（伪列）
select * 
from emp 
connect by prior empno=mgr上一层的员工号等于下一层的老板号 /*相当于where*/
start with empno = 7566 /*从哪一个节点开始*/
//mgr is null 根节点开始


子查询 解决的问题：不能一步求解，需要多步求解
查询工资比Scott高的员工信息
select * 
from emp
where sal > (select sal 
from emp 
where ename = 'scott'
);
1.合理的书写风格
2.括号
3.可以在主查询的where select from having 后面放自查询
select ename, empno,(select job from emp where empno = 78) 职位
from emp; /*只能是单行*/

select * 
from (select ename,sal,sal*12 annual from emp);  //from 后放的自查询

4.不可以在group by后面放置子查询
5.强调from后面的子查询
6.主查询和自查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可
select * 
from emp
where deptno = (select deptno 
from dept
where dname='sales'
);
优化4，如果多表查询和自查询都可以，理论上，尽量使用多表查询
7.子查询在主查询之前一次执行完成
8.一般先执行子查询，再执行主查询，但相关子查询例外
9.一般不在子查询中使用order by；但在top-n分析问题中，必须使用order by
10.单行子查询只能使用单行操作符，多行子查询只能查询多行操作符
  返回单条数据的叫做单行子查询
11.子查询中的null  

select  * 
from emp
where deptno in (select.....);
in: 集合中范围
any: 和集合中的任意一个值比较min
where sal > any(select sal from emp where deptno = 30)
all:集合中所有值比较 max
多行查询中的空值问题
select * 
from emp
where empno not in (select mgr from emp);集合中含有空值不可使用not in ，可以使用in
a  not in (10,20,null);
等价于 不等于所有
a!=10 and a!=20 and a!=null;
***************************
集合中没有null可以使用not in
若有in
select * 
from emp 
where empno not in (select mgr from emp where mgr is not null);
数据是核心****

集合运算
交A  intersect，并AUB， 差 A-B  minus
select * from emp where empno = 10
union 
select * from emp where empno = 20;
1.参与运算的各个集合必须列数相同且类型一致
2.最终得到的结果采用第一个集合的表头作为最后的表头
3.如果排序，必须在最后一个集合后使用
4.使用括号改变
列数不一样的时候，使用to_char()来处理
空值的时候 select  deptno,to_char(null),sum(sal) from emp;
优化5： 尽量不要使用集合运算

rownum:
select rownum,ename,eno from emp;
关于rownum，永远按照默认顺序生成；
rownum只能使用<,<=,不能使用>,>=;
练习：
select 
from emp
where rownum<=3
order by sal desc;
临时表： 只要对其排序，就会产生临时表
create temporary table ***; 
特点：会话或者事务结束的时候，临时表中数据自动清除
select * ,rownum
from (select * from emp order by sal desc)
where rownum < 3;
oracle中的分页
select * 
from  (select rownum r,el.* 
  from (select * from emp order by sal) el
  where rownum <= 8 
 )
where r >= 5;



select e.empno,e.ename,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) decode
where e.deptno = d.deptno and e.sal > d.avgsal;

相关子查询: 将主查询中的某个值作为参数传递给子查询
select empno,ename,sal,(select avg(sal) from emp where deptno=e.dptno)avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno)

数据处理
DML:  insert/update/delete/select 
DDL:  drop/view/index/sequence
DCL:  GRANT/revoke


插入语句：
insert into table[(column)] values(...) ;
insert into emp(empno,ename,sal,deptno)
values(1001,'Tom',3000,10);

/*输入值来插入*/
insert into table(empno,ename,sal,deptno)
values(&empno,&ename,&sal,&deptno);


拷贝表结构：
create table eee as select * from emp where 1=2;


批处理：
insert into emp10 
select * from emp where deptno = 10; //插入一个集合，注意没有values


大量插入数据
1.数据泵(plsql---并行插入)
2.sql*Loader加载同时加载多个txt中数据
3.备份和恢复


更新操作update：
update table set column = value [,column2 = value2]
where condition;
update employees 
set job_id = (select job_id 
 from employees 
 where employee_id = 205
),
salary = (select salary 
 from employees
 where employee_id= 205
)
where employee_id = 114;


删除数据：
delete from table
where condition;


delete 与 truncate区别
1.delete 逐条删， truncate先摧毁表，再重建表
2.delete是DML语句（可以回滚）， truncate语句是DDL语句(不可以回滚)
3.delete不释放空间，truncate会；
4.delete会产生碎片，truncate会；hwm(高水位线)，碎片会影响select的操作
去掉碎片：
1.alter table emp move；
2.导入导出 24.avi
数据量小，delete快，数据量大，truncate快
闪回flashback: 撤销一个错误的并且提交了的操作


数据库事务：
1.起始标志：事务中的第一条DML语句
2.结束标志：显式提交 commit
隐式提交 正常退出exit，DDL

oracle事务隔离级别： 2种  read commited， serializable 
***自己的隔离级别： read only
savepoint 
rollback


使用DDL语句创建和管理表
数据库对象：表 视图 序列  索引index 同义词
存储过程，存储函数，触发器，包， 快照，数据库链路databaselink(应用在分布式数据库)

create table [schema.]table 表名
(column datatype [,...]);数据类型
schema方案,一个用户下所有数据库对象的统称；
create table test1(tid number,
  tname varchar2(20),
  
)
数据类型：varchar2() char() number() date long clob blob rowid 
行地址  伪列 相当于指针
    创建表, 插入数据 子查询创建表
create table emp20
as
select * from emp where empno = 10;
修改表
alter table test1 add photo blob;
alter table test1 modity tname varchar2();
alter table test1 drop column tname;
删除表：
drop table test；
oracle回收站： show recyclebin； 当删除表后， 会放到回收站， 外部使用回收站内的名字还可以查询，注意加引号
约束：表一级的限制
 not null
 unique
 primary key
 foreign key
 check   （男， 女）
 create table test2
 (tid number,
  tname  varchar(20),
  gender varchar2(2) constraint student_gender_check check(gender in ('a','b'))
     constraint student_gender_notnull not null on delete set null;
 );
级联删除：
 on delete cascade  级联删除字表记录
 ****on delete set null 相关依赖外键设置为null  一般使用这个方法


其他数据库对象  
表：基本数据存储集合 物理概念
视图：表中抽出逻辑上相关数据集合逻辑概念
序列：提供有规律的数值
索引：提高查询效率
同义词：给对象起别名
视图：简化复杂的查询， 不可以提高性能
***不建议通过视图对表进行修改，更新，删除
视图类似上面， 将table改为view
create or replace view emp20
as
select * from emp where empno = 10;
select * from emp20;
[with read only]一般屏蔽DML操作
[with check option] 只可操作自己可以看得到的数据， 限定某个人可以操作的数据
银行系统中，告诉外界的基本都是视图，不是真正的表；为了安全。

序列：sequence
数组[1,2,3,4....20默认]，事先被放到内存，可以提高访问效率
create sequence sequencename
[increment by n]
[start with n]
[{maxvalue n|nomaxvalue}]
[{cycle|nocycle}]
[{cache n | nocache}]

create sequence myseq increment by 10 nocache ;
nextval返回序列中下一个有效的值
select myseq.nextval from dual;
   .currval

索引：index 可以提高访问效率 目录类似找一本书，通过目录来找指定页
基于deptno创建目录----索引
create index myindex 
on emp(deptno，job);有两个索引表 目录与子目录区别
索引表----存的是rowid行地址
自动会维护
不要建索引：
表很小
列不经常作为连接条件或出现在where子句中
查询数据大于2%到4%
表经常更新


授权 grant select on employee to hr;


同义词：synoym 就是别名 可以代表任何一个数据库对象
create synonym hremp for hr.employee
select count(*) from hremp;另一个用户下的表， 在银行系统中常见





PL/SQL: 对sql语句的扩展，对oracle操作最快
sql developer oracle公司出品，java写的
SID理解为数据库名
给员工涨工资 总裁1000 经理800 其他400 


plsql程序设计 oracle中写程序， 对sql语言的过程化扩展
declare 
--说明部分
begin
--程序
dbms_output.put_line('hello world');/*程序包*/
end
/

文档位置： books---PL/--- packages and types reference198个
 users and reference 

sql语言的数据操纵能力与过程语言处理能力结合起来。
oracle（pl/sql）sql server(T-sql)

var1 char(15)
married boolean :=true
psal number(7,2)
my_name emp.ename%type 引用型变量
emp_rec emp%rowtype 记录型变量

引用型变量
declare 
pename emp.ename%type
psal emp.sal%type
begin 
select ename,sal into pename, psal from emp where empno = 7839;--打印
dbms_output.put_line(pename||'的薪水'||psal)
end 
/
记录型变量 理解为数组
declare
--定义一个记录型变量，代表一行
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno=7839;
end
/

IF语句
end if；

if
else
endif

if
elsif
else
endif;


--接收键盘输入
accept num prompt '请输入一个数字';
--num是一个地址值，在该地址上保存了键盘输入的值

declare
--定义变量保存键盘输入的数字：隐式转换
pnum number := &num;
begin
if pnum=0 then dbms_output.put_line('你输入的是0');
elsif pnum = 1 then dbms_output.put_line('你输入的是1');
else dbms_output.put_line('其他数字');
end;
/


循环
while total <= 25000
Loop
end Loop;

Loop
exit [when ];
end loop

for i in 1..30
loop
语句序列
end loop;



打印1-10 
declare
pnum number :=1;
begin 
loop 
exit when pnum > 10;
dbms_output.put_line(pnum);
pnum := pnum + 1 ;
end loop ;
end;
/


Cursor 光标 == ResultSet
光标属性：
%isopen 
%rowcount 返回的行数
%notfound
%found 
默认，一次性打开300个光标；
declare 
--定义光标
cursor cemp is select ename,sal from emp;
pename  emp.ename%type;
psal emp.sal%type;
begin 
open cemp;
loop
--取一条数据
fetch cemp into pename,psal;
exit when cemp%notfound;

dbms_output.put_line(pename,psal);
end loop;
close cemp;
end;
/




存储过程，函数和触发器
java中不可以直接调用pl/sql，只能调用存储过程或存储函数
指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数
可以通过return语句将结果返回
存储过程
create [or replace] procedure 过程名(参数名)
AS 
PL/SQL子程序体；

create or replace procedure sayHelloWorld
as
--说明部分
begin
dbms_output.put_line('hello world');
end;
/
调用存储过程的两种方式：
exec sayHelloWorld();
begin
sayHelloWorld();
end；
/

带参数的存储过程
create or replace procedure raiseSalary(eno in number) 
as 
psal emp.sal%type;
begin 
select sal into psal from emp where empno=eno;
update emp set sal=sal+100 where empno=eno;
--一般不要在存储过程中提交，回滚  由谁调的来操作
dbms_output.put_line('涨前：'||psal);
end;
/
外部调用：
begin 
raiseSalary(1);
raiseSalary(2);
commit;
end;
/

存储函数 可有返回值
create or replace function queryEmpIncom(eno in number)
return number 
as
psal emp.sal%type;
pcomm emp.comm%type;
begin 
select sal,comm into psal,pcomm from emp where empno = eno;
return psal*12+nvl(pcom,0);
end;
/

利用out参数，返回多个值

create or replace procedure queryEmpinfo(eno in number,
    pename out varchar2,
pjob out varchar2,
psal out number
)
as 
begin 
select ename,empjob,sal into pename,pjob,psal from emp where empno = eno,
end;
/

java程序调用存储过程
if(rs != null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
rs=null;
}
}
java的垃圾回收，不受代码控制; 不可手动释放


package demo.oracle.utils;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.56.101:1521:orcl";
private static String user = "scott";
private static String password = "tiger";

static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
} 
}

public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}


/*
* 执行java程序
* java -Xms100m -Xmx200m HelloWorld
* 
* 技术方向:
* 1. 性能调优
* 2. 故障诊断--> 死锁  --> JDK 提供 ThreadDump
*/
public static void release(Connection conn, Statement st,ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null;//Java GC
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}

}
}












触发器
每当一个特定的数据操作语句(insert update delete)在指定的表上发出时，oracle自动地执行触发器的语句序列；
行级触发器
列级触发器
成功插入新员工后，自动打印一句话
create trigger sayNewEmp 
after insert 
on emp
declare
dbms_output.put_line('插入');
end;
/
用于数据确认
安全性检查
做审计，跟踪表上数据操作 日志
数据的备份同步 主 从数据库的备份 分布式数据库的例子

create [or replace] trigger 触发器名称
{before | after}
{delete | insert | update[of 列名]}
on 表名
[for each row [when(条件)]]
PLSQL块


语句级触发器 针对的是表
行级触发器 针对的是行

实施复杂的安全性检查
禁止在非工作时间插入新员工
to_char(sysdata,'day') in ('星期六','星期日')
to_number(to_char(sysdata,'hh24')) not between 9 and 18;
raise_application_error(-2001,'禁止在非工作时间插入新员工');

在做插入操作时，会出现ora-20001的错误提示

：new 操作这一行之前的记录
: old 操作这一行之后的记录

begin
if :new.sal < :old.sal then 
raise_application_error(-2002,'涨后少于涨前');
end;
/
先编译---再执行  

数据字典 dictionary
自己可以查询哪些表等
基本表
用户表

select * from dictionary;
select * from user_tables;

命名规则：
user 用户自己的
all 用户可以访问的
dba 管理员视图
V$ 性能相关数据


常用的数据库对象:
user_object 
all_object 
user_constraints
all_cons_columns
user_views
all_views