韩顺平 oracle听课笔记 经典.docx

复杂查询-单表查询：查询部门中各个岗位的平均工资和最高工资。select avg(sal),max(sal),deptno,job from emp group by deptno,job;group by 用于对查询的结果分组统计；select avg(sal),max(sal),deptno,job from emp group by deptno,job having avg(sal)2000;having 子句用于限制分组显示结果;select avg(sal),max(sal),deptno,job from emp group by deptno,job having avg(sal)2000 order by deptno desc;order by 用于排序;复杂查询-多表查询：select ?,? from emp a1,dept a2 where a1.deptno=a2.deptno;自连接(同一张表的关联查询)：select ?,? from emp worker,emp boss where woker.?=boss.?;子查询：1.单行子查询：返回单行数据2.多行子查询：返回多行数据 select * from emp where job in(select();all=嵌套select(max();any=嵌套select(min();3.多列子查询：select ?,? from emp where (?,?)=(select ?,?); 注意对应关系4.from子句中使用子查询：select ?,? from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.mysal;(a2叫做内嵌视图) 表指定别名不可加as，列可以加as；5.用查询结果创建表：create m1(?,?,?) as select ?,?,? from emp;复杂查询-分页查询：1.rownum分页：(select * from emp)2.显示rownum（oracle分配的）select a1. * ,rownum rn from (select * from emp) a1;3.指定范围：6到10条记录select a1. * ,rownum rn from (select * from emp) a1 where rownum=10; 前10条记录select * from (select a1. * ,rownum rn from (select * from emp) a1 where rownum=6 ;4.几个查询变化：a.指定查询列，(只需要修改最里层的子查询)select * from (select a1. * ,rownum rn from (select ?,? from emp) a1 where rownum=6 ;b.如何排序,(只需要修改最里层的子查询)select * from (select a1. * ,rownum rn from (select ?,? from emp order by ?) a1 where rownum=6 ;复杂查询- 合并查询：unoin:并集且去除重复的union all：并集且不去除重复的，也不排序intersect：取交集minus:取差集sql函数的使用：lower(char),upper(char),length(char),substr(char,m,n)从m开始取n个字符。select upper(substr(ename,1,1) from emp;(首字母大写)select lower(substr(ename,2,length(ename)-1) from emp;(除首字母大写后面字母小写)replace(char,m,n) 选择字段名 将m替换成nround(n,m) 四舍五入 m为小数点后面的的m位trunc(n,m) 截取数字 m为小数点后面的的m位mod(m,n)取模（余数）floor(n)返回小于或者等于n的最大整数ceil(n)返回大于或者等于n的最小整数例：显示一个月（30天）的情况下所有员工的日薪，忽略余数：select trunc(sal/30),ename from emp; 或者select floor(sal/30),ename from emp;显示员工的入职天数：Select trunc(sysdate-hiredate) 入职天数,ename from emp;显示当月倒数第三天入职的员工：last_day(d):返回指定日期所在月份的最后一天select hiredate,ename from emp where last_day(hiredate)-2=hiredate;转换函数：to_char(char, data):将字符串转换成date类型的数据to_date(1988-12-12,yyyy-mm-dd) 将日期格式强制转换select ename,to_char(hiredate,yyyy-mm-dd hh24:mi:ss) from emp;显示1980年入职的员工：select * from emp where to_char(hiredate,yyyy)=1980;数据库的逻辑备份与恢复：导出：表，方案，数据库 (DOS下)（多个方案的导出就是导出数据库）导出表exp userid=scott/m123orcl table=(emp,dept) file=d:aa.dmp导出表结构：exp userid=scott/m123orcl table=(emp,dept) file=d:aa.dmp rows=n快速导出：exp userid=scott/m123orcl table=(emp,dept) file=d:aa.dmp direct=y导出方案：exp scott/m123orcl owner=scott file=d:aa.dmp导出其他用户方案：需要dba权限或者exp_full_database权限exp system/managerorcl owner=(scott) file=d:system.dmp导出数据库：(增量全部)exp system/managerorcl full=y inctype=complete file=d:aa.dmp导入：表，方案，数据库 (DOS下)（多个方案的导出就是导出数据）导入表：imp scott/m123orcl table=(emp) file=:daa.dmp数据字典（存放静态信息）：视图集合 ，sys用户只能查询数据字典=基表 +动态性能视图（记载经常变化）user（当前用户自己的）_xxx（对象）,all_xxx当前用户可以访问的所有dba_xxx（dba权限）显示所有方案的数据库表三种类型用户名：权限（显示用户所拥有的系统权限dba_sys_privs，对象权限dba_tab_privs：）1.查询一个角色的所有权限:a.系统权限：select * from dba_sys_privs where grantee=CONNECT;b.对象权限：select * from dba_tab_privs where grantee=CONNECT;2.oracle的所有角色：select * from dba_roles;3.用户包含的所有角色：select * from dba_role_privs where grantee=SCOTT;表空间：数据库逻辑组成部分从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，则存放在表空间上。表空间由一个或多个数据文件组成：oracle逻辑结构：表空间段区块。建立表空间：create tablespace 由dba执行建立了data01表空间并且建立了data01.dbf数据文件，区的大小为128k：create tablespace data01 datafiled:data01.dbf size 20m uniform size 128k;在表空间中建表：create table xx(?,?,?) tablespace data01;alter tablespace data01 offline;(脱机)维护时数据库时alter tablespace data01 online;(联机)alter tablespace data01 read only;(只读表空间)只能selectalter tablespace data01 read write;(可读可写)drop tablespace data01 including contents and datafiles;(彻底删除)扩展，设置文件自动增长：alter tablespace data01 d:data01.dbf autoextend on next 10m maxsize 500m;例：移动数据文件：(所在磁盘毁坏)alter tablespace a01 offline;使表空间脱机host move d:testa01.dbf c:testa01.dbf;移动数据文件(物理上)alter tablespace a01 rename datafile d:testa01.dbf to c:testa01.dbf;（逻辑上）alter tablespace a01 online;使表空间联机数据的完整性：数据遵从一定的商业和逻辑规则约束， 触发器 ，（过程，函数）三种方法实现约束：not null, unique, primary key, foreign key，check.alter table emp modify deptno not null;alter table emp add constraint aa unique(cardid);将约束aa加到cardid字段上。alter table emp add constraint bb check(address in (?,?)alter table emp drop constraint aa;alter table emp drop primary key cascade;主键约束列级定义：定义列的时候同时定义约束表级定义：在列定义结束后再定义约束索引：单列索引：create index aa on emp(deptno);复合索引：create index aa on emp(deptno,ename);缺点：占用系统空间资源大（表的1.2倍）；降低系统性能，花费大量时间。角色：预定义（connect ,resource ,dba），自定义角色(dba或者create role的系统权限)create role myrole1 not identified;(不验证)角色授权：systemgrant create session to 角色 with admin option;grant 角色 to 用户 with admin option;删除角色 用户还能登陆吗？（否）显示角色信息：select * from dba_roles;事务：DML语句 insert，update，delete。锁：当执行事务操作时（dml语句），oracle会在被作用的表上加锁。commit;提交事务(之前的保存点无效)savepoint a;创建保存点rollback to a;在java中使用事务:ct.setAutoCommit(false);PLSQL块mit();只读事务(select)：set transaction aa read only;设置只读事务取得特定点时间点的数据信息。（订票系统）plsql编程:procedural language /sql过程 函数 触发器在oracle中由plsql编写，非常强大的数据库过程语言，可以由java调用缺点：移植性不好；存储过程：create or replace procedure sp_pro1(?,?) is 【可以定义变量】begin-执行部分 insert into aa values(?,?,?);end;/如何查看错误信息：show error;如何调用：1.exec sp_pro1(参数值1.); 2.call sp_pro1(参数值1.);过程：（多个返回值）修改员工工资create procedure sp_pro3(spname varchar2,newsal number) isbegin-执行update emp set sal=newsal where ename=spname;end;/exec sp_pro3(SCOTT,4678);函数：（返回值单一）create function sp_fun1(spname varchar2) returnnumber yearsal is number(7,2);beginselect sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spname;return yearsal;end;/调用：var abc number;call sp_fun2(SCOTT) into :abc;块(block)是pl/sql基本程序单元块的范例包含（定义， 执行，异常处理）declare -定义变量v_ename varchar2(5);v_sal number(7,2);begin-执行部分select ename,sal into v_ename,v_sal from emp where empno=&no;dbms_output.put_line(用户名是:|v_ename|工资:|v_sal);-在控制台显示-异常处理exceptionwhen no_data_found then dbms_output.put_line(输入有误);end;/包：逻辑上组合过程和函数 由包规范和包体组成。create package sp_package is/asprocedure update_salfunction annual_income-声明end;/ create package body sp_package isprocedure update_salfunction annual_income -实现end;/调用包的过程和函数：exec sp_package .update_sal(SCOTT,120);编写分页过程：调用有返回值的过程:(非列表)创建callablestatementCallableStatement cs=ct.prepareCall(call sp_pro3(?,?) );cs.setInt(1,7788);-给第一个?赋值cs.registerOutparameter(2,oracle.jdbc.OracleTypes.VARCHAR);-给第二个?赋值(注意varchar与?的类型一致)cs.execute();-执行-取返回值，注意？顺序String name=cs.getString(2);System.out.println(7788的名字+name);调用有返回值的过程:(列表【结果集】)-创建一个包 定义类型test_cursor,create package testpackage as/istype test_cursor is ref cursor;end testpackage;-建立存储过程create procedure sp_pro4(spNo in number,p_cursor out testpackage.test_cursor) isbeginopen p_cursor for select * from emp where deptno=spNo;end;/-在java中调用该过程：创建callablestatementCallableStatement cs=ct.prepareCall(call sp_pro4(?,?) );cs.setInt(1,10);-给第一个?赋值cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);-给第二个?赋值(注意varchar与? 类型一致)cs.execute();-执行得到结果集ResultSet rs=(ResultSet)cs.getObject(2);while (rs.next()System.out.println(rs.getInt(1)+rs.getString(2);分页模板：通用注意红体字的变换-oracle 分页，截取6到10条记录：select t1.* ,rownum rn from (select * from emp) t1;-第一步select t1.* ,rownum rn from (select * from emp) t1 where rownum=10;-第二步select * from (select t1.* ,rownum rn from (select * from emp) t1 where rownum=6;-第三步（成品，直接使用此模板）综合案例：编写一个存储过程，可以输入表名，每页显示记录数，当前页；返回总记录数，总页数，和返回的结果集。-开发一个包create or replace package testpackage astype test_cursor is ref cursor;end testpackage;-开始编写分页过程：create or replace procedure fenye(tablename in varchar2,- 输入表名pagesize in number,- 每页显示记录数pagenow in number,- 当前页myrows out number,- 返回总记录数myPageCount out number,-总页数p_cursor out testpackage.test_cursor-返回的结果集) is-定义部分：-定义sql语句 v_sql varchar2(1000);-定义两个整数v_begin number:=(pagenow-1)*pagesize+1; -注意 := 赋值符。v_end number:=pagenow*pagesize;begin-执行部分v_sql:=select * from (select t1.* ,rownum rn from (select * from tablename) t1 where rownum=v_begin;-把游标和sql语句关联：open p_cursor for v_sql;-计算m