oracle最全最有价值的知识点.doc

sql 1.组函数：max , min , avg , sum , count计算行；*重点；select count(*) from emp where deptno = 10; 计算部门10有多少人。select count(*) from emp ;计算一共多少列2.执行顺序 ：select - where -group by - having - order by;3.一些命令conn guanghua ; 用户guanghua登陆；conn guanghua as sysdba; 用guanghua以dba的身份来登陆grant create table,create view to jay; 将创建表和视图的权限分配给用户jay；4.创建视图命令：create view v$视图名称 as (select 语句);5.创建新用户，并将一个已有的用户的所有资源赋给新用户 *-备份原有用户的资源exp 命令 *-创建新用户create user jay identified by jay default tablespace users quota 10M on users/创建用户jay 密码为 jay 缺省的表 空 间 为users 并在users上分配10M的配额 *-为用户分配权限grant create session,create table ,create view to liuchao/分配 登陆 ，创建表 ，创建视图的权利 *-导入原有用户的资源 imp 命令6.roolback 回退命令。撤消前一步的操作7.简单的备份：如：备份emp表create table emp2 as select * from emp;8.UPDATE EMP SET EMPNO = 7902 WHERE ENAME = FORD; 将ename为ford的empno更新为7902；9.DELETE FROM EMP2;删除emp2表的所有行 DELETE FROM DEPT2 WHERE DEPTNO 40; 删除dept2表中deptno40 的所有行。10.删除表：drop table t；删除表t 。11.transaction 事务； 起始于第一条dml语句。（insert，delete，update） 结束于 commit 或rollback 语句； 当执行一条ddl语句（create table ） dcl语句（grant 授权）事务自动提交。roolback失效 正常断开连接事务自动提交。非正常断开则事务不被提交12，约束条件：*not null -非空；*default 1； *unique 唯一； 主键约束：primary key; 外键约束：references 表(字段)可以为约束条件取名字 如 constraint stu_name_not_null not noll; 将非空限定命名为stu_name_not_null; 可以同时约束两个值的组合为unique， 如 constraint stu_name_email_nui unique(email,name); 将email和name的组合设置为unique 此为表集约束。 主键必须非空，唯一。我们也可以为主键约束命名，也可以用两个字段作为主键。什么字段能够重复那么它肯定不能作为主键例：constraint stu_id_pk primary key(id); 外键约束：例：class number(4) references class（id）； 还可这样定义 ：constraint stu_class_fk foreign key(class) references class(id);外键约束中被参考的字段必须是主键。如 class 表中的id字段就是被参考字段，那么它就必须是主键。13.修改表结构命令 alter table student add(address varchar2(100); 为student表添加字段address ； alter table student drop(address) 删除student表中的address字段; alter table student modify(address varchar2(150); 将student表中的address字段的修改精度，但要保证能容纳已有数据 alter table student drop constraint stu_class_fk; 删除约束条件 alter table student add constraint sut_class_fk foreign key(class) references class(id);14.数据字典表 oracle将所有的表信息记录到一张表中，user_tables。 select table_name from user_tables;查看数据库中的所有的表名； select view_name from user_views; 查看数据库中的所有的视图名； select constraint_name,table_name from user_constraints;查看数据库中的所有的约束名及对应表名；15.所有的数据字典表又存在一张表中叫dictionary 16.索引:建立索引可以提高读取数据时的速度。但是当插入数据时，则速度会更慢，因为不仅要在表中写入数据，还要在索引表中插入数据 create index idx_stu_email on stu(email); 建立索引，则用户在查询student的email的时候速度会更快一些。 drop index idx_stu_email ;删除索引 当此字段数据量大，访问量大则建议索引，但建立索引时要谨慎，它不仅占用很多空间，而且还降低插入数据时的效率。17.视图就是一个子查询，视图会增加维护的开销。视图可以更新数据，但不常这么做。18.序列：oracle中使用sequence。 序列：产生一个唯一的不间断序列 如1，2，3，4，5，6. create sequence seq ;创佳一个序列。 应用： insert into student (sno,sname) values(seq.nextval,jay); 为student表插入一行数据，其中sno由序列产生。 删除序列：drop sequence seq；19.当提出优化表的时候，首先想到索引20.范式：就是数据库设计时的一些规则。它所追求的是减少冗余数据。 第一范式：.要有主键,列不可分 第二范式：不能存在部分依赖，非主键要依赖整个主键，而不是部分依赖主键。用于检查多个字段作为主键的情况。 第三范式：不能存在传递依赖；要直接依赖主键；21.PL/SQL在oracle中执行的程序语言。 在sqlplus下显示输出必须 set serveroutput on; 程序举例： 1* begin dbms_output.putline(HelloWorld!); end; / 2* declare v_name varchar2(20); begin v_name :=guanghua; dbms_output.put_line(v_name); end; 3*declare num number :=0;begin num := 2/num; dbms_output.put_line(num);exception when others then dbms_output.put_line(error:被0除);end; 变量声明的规则 1. 变量名不能够使用保留字，如from，select等 2. 第一个字符必须是字母 3. 变量名最多包含30个字符 4. 不要与数据库的表或者列同名 5. 每一行只能声明一个变量 变量类型： * binary_integer: 整数，用来记数而不是用来表示字段类型。* number:数字类型(包含整数和小数)* char:定长字符串* varchar2:变长字符串* date:日期* long:长字符串，最长2GB* boolean:布尔类型，可以取值为true,false 和null值。 变量声明：使用%type属性例： declare v_empno number(4); v_empno2 emp.empno%type; -将v_empno2 的类型设置为 表emp字段empno的类型 v_empno3 v_empno2%type; -将v_empno3 的类型设置为 变量v_empno2的类型；begin dbms_output.put_line(test);end; %type 可以将变量声明的类型设置为另一个变量的类型22.PL/sql (在PL/SQL里注释一行用多行用/* */) 数组类型的声明： -先声明数组类型 ，然后才声明一个数组变量。 declare type type_table_emp_empno is table of emp.empno%type index by binary_integer ; -定义一个类型 名为type_table_emp_empno 是数组 由emp.empno的类型的数据构成 由binary_integer类型来索引； 简化为： declare type 类型名 is table of 数据类型 index by binary_integer; 定义数组变量： v_empnos type_table_emp_empno ;声明一个变量，其类型为type_table_emp_empno ； 赋值： v_empnos(0) := 1234; v_empnos(-1) :=2345; v_empnos(1) :=9887; 打印输出;dbms_output.put_line(v_empnos(-1); Record 变量类型： declare type type_record_dept is record -声明一种record类型 ，此类型中存放多个数据( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type);v_dept type_record_dept; -声明一个type_record_dept类型的变量叫做：v_dept; beginv_dept.deptno := 50;v_dept.dname := sale;v_dept.loc :=nanchang;dbms_output.put_line(v_dept.deptno| |v_dept.dname| |v_dept.loc); end; 还可以使用%rowtype 声明record变量； declare v_temp v_dept%rowtype; - v_dept 为一个record类型的变量；23. pl/sql 中使用调用sql语句。 使用查询语句的时候返回且只返回一个值。 select 语句中如果不使用游标则必须有into 例 declare v_ename emp.ename%type; v_sal emp.sal%type; begin select ename,sal into v_ename,v_sal from emp where empno = 7369; dbms_output.put_line(v_ename | | v_sal); end; declare v_deptno emp2.deptno%type :=10; v_count number; begin update emp2 set sal=sal/2 where deptno = v_deptno; -在plsql中调用update语句 dbms_output.put_line(sql%rowcount |条记录被影响。); sql%rowcount 记录最近一次sql语句改变的记录条数。(若是select 语句，有多少个值就有多少记录被影响) commit; -执行完update。等语句后要提交数据。* end;24.plsql 中调用ddl语句（create table）: begin execute immediate create table T (nnn varchar2(20) default aa); -执行 立即 创建表t 注意单引号里面的单引号要改为两个单引号 end; 例： declare v_sal emp.sal%type; begin select sal into v_sal from emp where empno=7839; if(v_sal2500) then update emp set sal=sal/2 where empno=7839; else dbms_output.put_line(v_sal); end if; commit; end;25.plsql中的循环 ： 一定是从loop开始 end loop 结束；loop 和 end loop 相当于java中的 例； declare i binary_integer :=1; begin loop dbms_output.put_line(i); i:=i+1; exit when(i=11); end loop; end;-while循环 declare i binary_integer :=1; begin while i begin for k in 1.10 loop dbms_output.put_line(k); end loop; for k in reverse 1.10 loop 倒序 dbms_output.put_line(k); end loop; end;26.plsql处理异常 例 declare v_temp number(4); begin select empno into v_temp from emp where deptno = 10; exception when too_many_rows then too_mang_rows 系统已定义的异常 dbms_output.put_line(太多记录了); when others then dbms_output.put_line(error!); end; 其它异常： no_date_found 没有找到数据；27.将Exception保存到errorlog表中* 首先创建一个表 create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date );*创建一个序列： create sequence seq_errorlog_id start with 1 increment by 1;declarev_temp number(4);-v_deptno emp.deptno%type := 20;v_errcode number;v_errmsg varchar2(1024);beginselect empno into v_temp from emp where deptno=10;-delete from emp where deptno = v_deptno;-commit;exceptionwhen others thenrollback;-回滚 v_errcode := SQLCODE;-错误的代码（关键字）v_errmsg:=SQLERRM;-错误的原因insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);commit;end;28.plsql 游标 对表进行遍历； 例： declare cursor c is select * from emp;/声明游标； v_emp c%rowtype; begin open c; loop fetch c into v_emp; exit when(c%notfound); dbms_output.put_line(v_emp.ename); 此句和上一句不能交换，交换了结果就错了，最后一条的结果会输出两次。 end loop; close c; end; 游标有四个属性 isopen-是否打开 ，notfound-没有fetch到值 ，found-fetch到值 ，fetch-得到的结果数。 游标用while循环： 游标遍历中最实用的循环是for 循环 无须声明v_emp for循环开始的时候自动声明，并且打开游标c， 而且不容易出错。 declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop; 带参数的游标 declare cursor c (v_deptno emp.deptno%type ,v_job emp.job%type) is select ename ,sal from emp where deptno = v_deptno and job = v_job; begin for v_temp in c(30,CLERK) LOOP DBMS_OUTPUT.PUT_LINE(V_TEMP.ENAME); END LOOP; END;/游标C是储存了ename,sal的记录集，只是它带了两个参数（v_deptno,v_job,用于传递到where句后的参数）相当于函数。游标就是用来读的。29.存储过程： 带有名字的plsql的程序块-直接写名字就能执行了 创建一个过程 例： create or replace procedure p is (程序的过程,pl/sql 语句块) / 执行完后此时只是创建了存储过程，期间没有执行内部的程序代码 执行存储过程p： exec p; 或 begin p; end; / 创建完存储过程后，无须再次键入代码了就，只需执行这个存储过程就行了。存储过程有错oracle也会创建的，但是会报错，得改。（oracle 报错不会指出具体哪一行错，用show error 命令） 创建带有参数的过程：例： *创建过程： create or replace procedure p (v_a in number ,v_b number ,v_ret out number, v_temp in out number)in,out 代表参数是传入还是传出的。默认是in is begin if(v_av_b) then v_ret := v_a; else v_ret := v_b; end if; v_temp := v_temp +1;* end; *调用过程： declare v_b number :=3; v_a number :=4; v_ret number; v_temp number :=5; begin p(v_a,v_b,v_ret,v_temp); dbms_output.put_line(v_ret); dbms_output.put_line(v_temp);* end;删除存储过程：drop procedure 过程名；30.创建函数-不是很重要 create or replace function sal_tax (v_sal number) return number is begin if(v_sal2000) then return 0.1; elsif(v_sal create table emp2_log ( uname varchar2(20),谁 action varchar2(10),做什么操作 atime date什么时间 ); 触发器可用于数据的一致性。一个表中的字段被另一个表参考，当修改被参考表的主键时，参考表同时改变如：执行这句话：update dept set deptno = 99 where deptno = 10;(deptno有被其他表参考，改了其他表此字段就没意义了，也得跟着改，用触发器)如下： create trigger trig;after update on dept for each row beginupdate emp set deptno = :NEW.deptno where deptno = :OLD.deptno; end; 这时就可以执行上句话了。 创建完触发器后，执行数据库操作后会被记录在emp2_log里。32.综合运用 树状形式展现帖子； 递归存储过程； create or replace procedure p(v_id article.pid%type,v_level binary_integer) iscursor c is select * from article where pid = v_id;v_prestr varchar2(100) := ; beginfor i in 1.v_level loop v_prestr := v_prestr | *;-是连接上的意思。end loop;for v_article in c loop dbms_output.put_line(v_prestr | v_article.cont); if(v_article.isleaf = 0) thenp(v_article.id,v_level+1); end if;end loop; end;Oracle查询例子笔记：-求部门平均工资的等级 select deptno ,avg_sal ,grade from (select deptno ,avg(sal) avg_sal from emp group by deptno) emp2 left join salgrade on(emp2.avg_sal between salgrade.losal and salgrade.hisal)-求部门的平均的工资等级 select deptno ,avg(grade) from (select deptno , ename,grade from emp join salgrade on( emp.sal between salgrade.losal and salgrade.hisal) group by deptno-求出哪些人是领导者select ename from emp where empno in (select distinct mgr from emp )-不用组函数，求薪水的最高值select sal from empwhere sal not in(select distinct e1.sal from emp e1 join emp e2 on (e1.sal( select max(sal) from emp where empno not in (select mgr from emp where mgr is not null)/-求薪水最高的前5名雇员 select ename,sal from emp where rownum =5 order by sal desc;(错的，因为这是先选出前5个记录后再排序，而题目是要求所有的选出最高前5名) select ename , sal from (select ename,sal from emp order by sal desc) where rownum =6 and r=10; 若select ename,sal,rownum r form (select ename,sal from emp order by sal desc) 换成 select ename,sal,rownum r form emp order by sal desc；就错了，因为这是先生成 含有ename sal rownum字段的表再根据sal排序的，而题目是先生成已经排序出的含有ename,sal 字段的表（该表默认有rownum1，2，3）再显示出rownum的值。没有rownum字段的表都默认含有 ，有此字段的就会显示出来。-面试题：比较效率select * from emp where deptno =10 and ename like %A%;select * from emp where ename like %A% and deptno = 10;-面试题：有三个表s，c，sc学生s(sno,sname) 课程表c(cno,cname,cteacher)sc(sno,cno,scgrade)1.找出没有选过“黎明”老师的所有学生姓名；select sname from s join sc on (s.sno=sc.sno) join c on(o=o) where cteacher 黎明2.列出2门以上（含2门）不及格学生姓名及平均成绩： * 先选出两门不及格的学生的序号：select sno from sc where scgrade=2; * 再求出两门或以上不及格学生姓名:select sname from s where sno in(select sno from sc where scgrace=2);3.即学过1号课程又学过2号课程所有学生的姓名： select sname from s join sc on (s.sno = sc.sno) where cno=1 and s.sno in (select distinct sno from sc where cno=2);笔记：-求薪水最高的前5名雇员 select ename,sal from emp where rownum =5 order by sal desc;(错的，因为这是先选出前5个记录后再排序，而题目是要求所有的选出最高前5名) select ename , sal from (select ename,sal from emp order by sal desc) where rownum =6 and r=10; 若select ename,sal,rownum r form (select ename,sal from emp order by sal desc) 换成 select ename,sal,rownum r form emp order by sal desc；就错了，因为这是先生成 含有ename sal rownum字段的表再根据sal排序的，而题目是先生成已经排序出的含有ename,sal 字段的表（该表默认有rownum1，2，3）再显示出rownum的值。没有rownum字段的表都默认含有 ，有此字段的就会显示出来。oracle 查出的表前面默认有一列数字1，2，3只是没有显示出来。所以要求前5名时可以用rownum=5就可以求出前5条记录,放在where条件语句里。（rownum只跟或时，可这样：先select rownum r,列名，列名 from -.然后用select 列名，列名 from (select rownum r,列名，列名 from -)where rownun 5;mysql求从第几行到第几行很简单，用limit命令就可以，而oracle就不一样了：多个DML语句oracle会当作一个事务来执行，就像hibernate里一样。碰到DDL，DCL，commit,rollback时会自动提交。查询当前用户下面有多少表：select table_name from user_tables;查询当前用户下面有多少视图：select view_name from user_views;查询当前用户下面有多少约束名：select constraint_name from user_constraints;像user_tables;user_views;user_constraints叫做数据词典表；数据词典表的表叫做dictionaries表；用于存储数据词曲表；在mysql里自动递增用autoincrese 来标识某个属性，而oracle是采用序列。如：create sequence seq;（一般一个序列对应一个字段） create table article(id int,title varchar(20),cont varchar(100); insert into article values(seq.nextval,kfaf,lfjallkjda); insert into article values(seq.nextval,dfds,fdssa);这样就会自动递增了。 序列删除用DROP；优化查询表时要想到索引，面试时。-PL/QL-set serveroutput on;-开启输出方法begindbms_output.put_line(HelloWorld!);end;/ -声明赋值declarev