PLSQL复习笔记2014/2/16

复习笔记

Day01

一、数据库环境及历史
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)    )  )

Day02

一、数据库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 deptas select deptno,dname from tbldept;--拷贝表结构但不拷贝数据create table deptas 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)--域完整约束 checkalter table deptadd constraint c_sex_deptcheck(sex='男' or sex='女')insert into dept(deptno,dname,sex)values (5,'技术','男')select * from dept;--唯一约束(注意：null和任何值都不等，包括自己)alter table dept add constraintuq_dname_dept unique(dname) --创建员工表create table emp(       empno number(2) primary key,       ename varchar2(4),       deptno number(2)       /*使用行级约束生成外键       foreign key (deptno) references       dept(deptno)       */)alter table emp add constraintfk_emp foreign key(deptno) references dept(deptno)--外键的使用

3、dml

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

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


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

Day03

一、事务保留点

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'--nullselect * 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 emporder 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 empwhere deptno=20) ewhere e.salsum>10;--查询公司所有的非部门经理的员工的编号和名称--in 是或者关系  not in是与关系select * from empwhere empno not in (select distinct mgr from emp where mgr is not null);--查询工资大于4000的所有部门经理的信息select * from empwhere empno in(select mgr from emp) and  sal >4000

五、表连接

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

六、高级查询

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

Day04

--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 empwhere 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编程基础

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

Day05

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;--查询多个字段declare  cursor cur_emp is   select empno,ename from emp;  type myemp is record(       myempno emp.empno%type,       myename emp.ename%type  );  mi myemp;begin   open 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 as       cursor cur_emp is        select * from emp;       rec_emp emp%rowtype;begin  open 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_sourcewhere 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 ) is begin   dbms_output.put_line(name||'-'||empno);   empno :=3; end;

--使用过程declare   x number:=1;   myempno number:=2;begin  check_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%typeasname emp.ename%type;begin  --没有满足条件的数据，会中断函数执行  select ename into name from emp   where empno=no;  if name is null then    name:='没有';  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 declare       ecount number;begin  select 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 rowdeclare  cnt number(2);begin select 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 pageEmpas 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_deptstart 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'