PLSQL复习笔记2014/2/16

一、数据库环境及历史
1、历史
文件管理系统
网状数据库管理系统
关系型数据库
nosql
2、常用数据库
sqlserver(ms) :办公室级别或部门级别的数据
mysql:中小型数据库；开源
oracle db2 sysbase:


二、数据库设计基础
1、设计和建模的必要性
节约存储空间
数据完整性
方便开发
2、操作流程
收集信息
对象识别
数据模型
信息类型
关系
3、建模
 a 三种模式
 b 设计模型
e-r图
4、规范化
关系型数据库的特点：数据以表格的形式呈现
范式


三、oracle的使用
环境：
本地使用sqlplus
1、sqlplus概念
作用：用于管理oracle数据库，默认形式为指令
2、登录 (scott/tiger 使用案例)

sqlplus scott/tiger

这个账户在安装时没有解锁，那么不能登录
3、解锁账户
以管理员的身份登录(本机)

sqlplus /  as sysdba

解锁指令

alter user scott account unlock;

4、查看身份
show user;
5、切换账户
conn 账户名/密码
6、查看当前用户所有表

select table_name from user_tables;

emp:员工表
dept:部门表
查看表结构
desc 表名
7、查看当前实例(管理员)

select instance_name from v$instance;

8、使用外部sql脚本
edit c:\\a.sql
@c:\\a.sql


9、管理及使用网络服务名
D:\dev_env\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
listener.ora:监听配置文件（服务端）
在该文件中添加

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.254.102)(PORT = 1521)) ) ) 

tnsnames.ora:网络服务配置文件（客户端）
mylion:网络服务名（可以自定义）
host:服务器的IP地址
service_name:服务器数据库名称

mylion =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.254.102)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = lion)))

一、数据库sql操作
ddl
dml
tcl
dcl
1、新建账户进行测试

drop user test cascade;--删除已有的test账户

//创建账户

create user test identified by 123;

//赋予操作权限（登录，资源权限）

grant create session,resource to test;

2、ddl语句

drop table dept

drop table lalala

create table dept(deptno number(2),dname varchar2(33),loc varchar2(33),primary key (deptno)--列级约束
)

--插入数据
insert into dept values(1,'教学部门','天河软件园'); 
insert into dept(deptno,dname)
values (2,'销售部门')
insert into dept(dname,deptno)
values ('研发部门',3)
select * from tbldept
--修改表名
rename dept to tbldept
--修改表结构
--修改字段类型
alter table tbldept modify loc char(1024)
--添加字段
alter table tbldept add mgr number(33)
--修改字段名称
alter table tbldept 
rename column mgr to manager
--删除表字段
alter table tbldept drop column manager--拷贝表
drop table dept;
--拷贝表结构及所有数据
create table dept 
as select * from tbldept
--拷贝部分表结构及数据
create table dept
as select deptno,dname from tbldept;
--拷贝表结构但不拷贝数据
create table dept
as select * from tbldept where 1=2;select * from dept;
--完整性约束测试
drop table dept;
create table dept(deptno number(2) /*primary key*/,--行级约束dname varchar2(33),sex varchar2(4)
)
--添加主键
alter table dept 
add constraint pk_dept primary key (deptno)
--域完整约束 check
alter table dept
add constraint c_sex_dept
check(sex='男' or sex='女')
insert into dept(deptno,dname,sex)
values (5,'技术','男')
select * from dept;
--唯一约束(注意：null和任何值都不等，包括自己)
alter table dept add constraint
uq_dname_dept unique(dname) --创建员工表
create table emp(empno number(2) primary key,ename varchar2(4),deptno number(2)/*使用行级约束生成外键foreign key (deptno) referencesdept(deptno)*/
)
alter table emp add constraint
fk_emp foreign key(deptno) 
references dept(deptno)
--外键的使用

3、dml

--插入数据
insert into emp
values(2,'lion',1)
select * from emp--删除
delete /*from没有该关键字只能用在oracle*/ emp
where empno=1;
--截断表
truncate table emp;--修改数据
update emp set
ename='see' 

4、事务
事务是用于保证数据安全性而设计
事务的特性：
commit:提交事务
rollback:回滚事务
注意：事务具有锁的特性
操作的是某一个条数据，那么锁的内容为该条数据
操作的是全表的数据，那么会锁住整个表


5、备份及还原
备份：
使用pl/sql developer->tools->export tables->第二个tab->
选择哪些表需要备份
还原：
使用pl/sql developer->tools->import tables->第二个tab->
选择备份文件

一、事务保留点

select * from emp;
savepoint a;
update emp set ename='lion' 
savepoint b;
update emp set ename='see'
rollback to b;
commit;

二、操作符

--操作符
select * from emp where sal>2000;
select * 
from emp 
where sal between 2000 and 4000
--集合操作符
select * from emp 
where /*sal=2000 or sal=3000*/
sal in(2000,3000,4000)
--sql注入
select * from emp 
where ename='SCOTT' 
and empno='' or '1'='1'
--null
select * from emp 
where comm  is not null;
--查询名称包含A字母的员工
--注意：字符串区分大小写，
--     关键字和表结构名不区分大小写
SELECT * FROM emp 
WHERE ename LIKE '%A%'select * from emp 
where ename like '%#_%' escape '#'
--查询名称中只有一个下划线的员工--字符串拼接
select '员工姓名：'||ename from emp;

三、查询语句

--排序
asc:升序
desc:降序
--如果是综合排序，写在前面的字段优先排序
select * from emp
order by sal desc,hiredate desc;select * from emp order by sal desc;--统计函数（集合函数）
--注意：null不参与统计
select avg(sal),min(sal),max(sal),
sum(sal),count(1)
from emp;
select sum(sal)/count(1) from emp;
select count(*) from emp;
select 1 from emp;
select * from emp where sal is null;

四、子查询

--统计20号部门总工资
select * from
(select sum(sal) salsum from emp
where deptno=20) e
where e.salsum>10;--查询公司所有的非部门经理的员工的编号和名称
--in 是或者关系  not in是与关系
select * from emp
where empno not in (
select distinct mgr from emp 
where mgr is not null);
--查询工资大于4000的所有部门经理的信息
select * from emp
where empno in(select mgr from emp) 
and  sal >4000

五、表连接

--查询工资大于2000的人和其对应的部门信息
select e.ename,d.dname ,e.sal 
from emp e ,dept d
where e.deptno = d.deptno and sal>2000
--查询所有员工对应的部门信息
select * from dept
select e.ename,d.dname from emp e ,dept d
where e.deptno = d.deptno
--查询所有员工及对应的部门（包含所有员工）
select * from emp e left join  dept d
on e.deptno = d.deptno
--oracle连接语法
select * from emp e,dept d 
where e.deptno(+) = d.deptno;
--全连接
select * from emp e full join dept d
on e.deptno = d.deptno
--交叉连接
select * from emp e cross join dept d
where e.deptno = d.deptno

六、高级查询

--分组查询
--在select 子句中出现的所有非集合函数 项必须出现在group by子句中
--select子句中只能写存放一个值的字段
select deptno,min(sal),max(sal),avg(sal) 
from emp
group by deptno
--查询员工数大于5的部门
select deptno,count(1)
from emp 
group by deptno
having count(1)>5

--dual虚表，放单个东西
一、函数

--数值函数
select abs(-10) from dual;
select sign(100) from dual;
--字符函数
select initcap('hello, world你好') from dual;
--字符转换函数
--第二个参数：将要替换的字符（不是当作整体）
select translate('jcak','ja','blxyz') 
from dual;
--第二个参数：将要替换的字符（当作整体）
select replace('b ack','b a','j')
from dual;
--日期函数
select last_day(sysdate) from dual;
--未来最近的星期的第几天
select next_day(sysdate,1) from dual;
select next_day(sysdate,4) from dual;
select next_day('2014/2/12',1)from dual;
--将日期转为字符串
select to_char(sysdate,'yyyy-month-dd')
from dual;
--将字符串转为日期
select to_date('2014-2-12','yyyy-mm-dd HH:MI:SS') 
from dual;
select * from v$nls_parameters 
--以天作为日期的算法运算单位
select sysdate-to_date('2014-2-12','yyyy-mm-dd')from dual;
--数字格式转为字符串
select to_char(10,'0999') from dual;
select to_char(1234,'L0999') from dual;
select length(to_char(12345)) from dual;
--字符串转数字
select 1+'2a' from dual;
select 1+to_number('$123','L999') 
from dual;--nvl空值处理函数
select 1+null from dual;
select ename,nvl(sal,0) from emp;
select count(nvl(sal,0)) from emp;
select avg(nvl(sal,0)) from emp;

二、伪列

--伪列的使用
select rownum,rowid,ename from emp
where sal>2000;
--查询工资最多的前三个人
select rownum oldrow,e.* from(select  ename ,nvl(sal,0) from emp order by nvl(sal,0) desc)e where rownum<=3--查询工资排名第4-10位的员工
--可以转换成分页表达式
select x.* from (select rownum oldrow,e.* from(select  ename ,nvl(sal,0) from emp order by nvl(sal,0) desc)e
)x where x.oldrow>=(3-1)*5+1 
and x.oldrow<=3*5

三、pl/sql编程基础

declarex constant number(2) default 10;
begin--x := 11;不能改变常量的值dbms_output.put_line(x);
end;
--将数据结果放入变量declareg_count number(10);
begin--select count(1) into :g_count from emp;dbms_output.put_line('员工总数为：'||:g_count);
end;--循环
--loop 相当于do while 
declaretotal number:=1;n number:=1;temp number;
beginlooptemp :=total;total:=total*n;dbms_output.put_line(temp||'*'||n||'阶乘的结果:'||total);exit when n=10;n :=n+1;end loop;
end;
--for
declaretotal number:=1;n number:=0;
beginfor n in 1.6..100 looptotal :=total*n;dbms_output.put_line('n='||n);end loop;dbms_output.put_line('阶乘的结果:'||total);
end;

select * from emp;

--游标的使用
--查询一个字段
declare  --声明游标变量cursor cur_emp is select empno from emp;myempno emp.empno%type;
begin--打开游标open cur_emp;fetch cur_emp into myempno;dbms_output.put_line('第一个人no：'||myempno);close cur_emp;--关闭游标
end;
--查询多个字段
declarecursor cur_emp is select empno,ename from emp;type myemp is record(myempno emp.empno%type,myename emp.ename%type);mi myemp;
beginopen cur_emp;dbms_output.put_line('员工号   员工名');loop fetch cur_emp into mi;exit when cur_emp%notfound;dbms_output.put_line(cur_emp%rowcount||' '||mi.myempno||'   '||mi.myename);end loop;close cur_emp;
end;

--存储过程
create or replace procedure view_emp ascursor cur_emp is select * from emp;rec_emp emp%rowtype;
beginopen cur_emp;fetch cur_emp into rec_emp;dbms_output.put_line('员工号  员工名');dbms_output.put_line(rec_emp.empno||' '||rec_emp.ename);close cur_emp;
end;

--查看过程
select * from user_source
where name='VIEW_EMP';
--执行过程(command)
begin view_emp end;
exec view_emp;

--参数传递
create or replace  procedure check_emp(name in emp.ename%type,empno in out emp.empno%type,sal out emp.sal%type)isbegindbms_output.put_line(name||'-'||empno);empno :=3;end;

--使用过程
declarex number:=1;myempno number:=2;
begincheck_emp(x,empno=>myempno);dbms_output.put_line(myempno);
end;

--函数
select '-'||trim(' abc ')||'-' from dual;
create or replace function getName(no emp.empno%type)
return emp.ename%type
as
name emp.ename%type;
begin--没有满足条件的数据，会中断函数执行select ename into name from emp where empno=no;if name is null thenname:='没有';end if;dbms_output.put_line(name);return name;
end;select getName(7778) from dual;select * from emp where empno=7779;

--创建触发器
create or replace trigger emp_before_del 
before delete on emp 
declareecount number;
beginselect count(1) into ecount from emp;dbms_output.put_line('删除前有'||ecount||'个人');
end; 
--触发器被调用实例
delete from emp where empno=7369;
select * from emp;

--执行语句成功与否控制
create or replace trigger del_dept  
before delete  on dept  for each row
declarecnt number(2);
beginselect count(*) into cnt from emp where deptno= :old.deptno;if(cnt > 0) then--使用抛出错误终止操作运行raise_application_error(-20000,'编号为'||:old.deptno||'不能删除');end if;
end ; 
--测试实例
delete from dept where deptno=10 ;
select * from dept

--视图
create or replace view emp1 
as select * from emp where sal>2000;

select * from emp1;

--分页视图
create or replace view pageEmp
as 
select rownum oldrow,e.* from(select  ename ,nvl(sal,0) from emp order by nvl(sal,0) desc)eselect * from pageEmp 
where oldrow>5 and oldrow <=10;

--序列
drop sequence seq_dept;
create sequence seq_dept
start with 10;
select seq_dept.nextval from dual;insert into dept(deptno,dname)
values (seq_dept.nextval,'技术部');select * from dept;
--在移植完成数据表后，为每个表重建序列
select table_name from user_tables;
select * from user_tab_cols 
where table_name='DEPT'