1、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 vie
2、w 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 ,crea
3、te 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
4、的所有行。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 表(字段)可以为约束条件取名字 如 constr
5、aint 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
6、（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(addres
7、s 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_
8、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_s
9、tu_email ;删除索引当此字段数据量大，访问量大则建议索引，但建立索引时要谨慎，它不仅占用很多空间，而且还降低插入数据时的效率。17.视图就是一个子查询，视图会增加维护的开销。视图可以更新数据，但不常这么做。18.序列：oracle 中使用 sequence。序列：产生一个唯一的不间断序列 如 1，2，3，4，5，6create sequence seq ;创佳一个序列。应用： insert into student (sno,sname) values(seq.nextval,jay); 为student表插入一行数据，其中 sno由序列产生。删除序列：drop sequence se
10、q；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_
11、name :=guanghua; dbms_output.put_line(v_name); end;3*declarenum number :=0;beginnum := 2/num;dbms_output.put_line(num);exceptionwhen others thendbms_output.put_line(error:被 0除);end;变量声明的规则1. 变量名不能够使用保留字，如 from，select 等2. 第一个字符必须是字母3. 变量名最多包含 30个字符4. 不要与数据库的表或者列同名5. 每一行只能声明一个变量变量类型： * binary_integer:
12、 整数，用来记数而不是用来表示字段类型。* number:数字类型(包含整数和小数)* char:定长字符串* varchar2:变长字符串* date:日期* long:长字符串，最长 2GB* boolean:布尔类型，可以取值为 true,false 和 null值。变量声明：使用%type 属性例： declarev_empno number(4);v_empno2 emp.empno%type; -将 v_empno2 的类型设置为 表 emp字段 empno的类型v_empno3 v_empno2%type; -将 v_empno3 的类型设置为 变量 v_empno2的类型；be
13、gindbms_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类型来索引；简化为： decla
14、re 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类型 ，此
15、类型中存放多个数据(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变量；d
16、eclare v_temp v_dept%rowtype; - v_dept 为一个 record类型的变量；23. pl/sql 中使用调用 sql语句。使用查询语句的时候返回且只返回一个值。select 语句中如果不使用游标则必须有 into例declarev_ename emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_ename,v_sal from emp where empno = 7369;dbms_output.put_line(v_ename | | v_sal);end;declarev_deptn
17、o emp2.deptno%type :=10;v_count number;beginupdate 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）:beginexe
18、cute immediate create table T (nnn varchar2(20) default aa);-执行 立即 创建表 t 注意单引号里面的单引号要改为两个单引号end;例：declarev_sal emp.sal%type;beginselect sal into v_sal from emp where empno=7839;if(v_sal2500) thenupdate emp set sal=sal/2 where empno=7839;elsedbms_output.put_line(v_sal);end if;commit;end;25.plsql中的循环
19、： 一定是从 loop开始 end loop 结束；loop 和 end loop 相当于 java中的 例； declarei binary_integer :=1;beginloopdbms_output.put_line(i);i:=i+1;exit when(i=11);end loop;end;-while循环 declarei binary_integer :=1;beginwhile i beginfor k in 110 loopdbms_output.put_line(k);end loop;for k in reverse 110 loop 倒序dbms_output.pu
20、t_line(k);end loop;end;26.plsql处理异常例declarev_temp number(4);beginselect empno into v_temp from emp where deptno = 10;exceptionwhen too_many_rows then too_mang_rows 系统已定义的异常dbms_output.put_line(太多记录了);when others thendbms_output.put_line(error!);end;其它异常： no_date_found 没有找到数据；27.将 Exception保存到 errorl
21、og表中* 首先创建一个表 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_
22、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 游标 对表进行遍历；例：declarecursor c
23、 isselect * from emp;/声明游标；v_emp c%rowtype;beginopen c;loopfetch 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_em
24、p for循环开始的时候自动声明，并且打开游标 c， 而且不容易出错。declarecursor c is select * from emp;beginfor v_emp in c loopdbms_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;beginfor v_temp in c(
25、30,CLERK) LOOPDBMS_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;
26、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 isbeginif(v_av_b) thenv_ret := v_a;elsev_ret := v_b;e
27、nd if;v_temp := v_temp +1;* end;*调用过程：declarev_b number :=3;v_a number :=4;v_ret number;v_temp number :=5;beginp(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
28、isbeginif(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 rowbegi
29、nupdate 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) := ; begin
30、for i in 1v_level loopv_prestr := v_prestr | *;-是连接上的意思。end loop;for v_article in c loopdbms_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 ,gradefrom (select deptno ,avg(sal) avg_s
31、al from emp group by deptno) emp2 left join salgradeon(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
32、 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 empwhere empno not in(select mgr from emp where mgr is not null)/-求薪水最高的前 5名雇员select ename,sal from emp where rownum =6
33、 and r 黎明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
34、 sc where cno=2);笔记：-求薪水最高的前 5名雇员select ename,sal from emp where rownum =6 and r时，可这样：先 select rownum r,列名，列名 from -.然后用 select 列名，列名 from (select rownum r,列名，列名 from -)where rownun 5;mysql求从第几行到第几行很简单，用 limit命令就可以，而 oracle就不一样了：多个 DML语句 oracle会当作一个事务来执行，就像 hibernate里一样。碰到DDL，DCL，commit,rollback 时会自
35、动提交。查询当前用户下面有多少表：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 s
36、equence 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(HelloW
37、orld!);end;/ -声明赋值declarev_name varchar2(20);beginv_name := myname;dbms_output.put_line(v_name);end;/ -展示错误declarev_num number := 0;beginv_num := 2/v_num;dbms_output.put_line(v_num);end;/ -捕捉错误declarev_num number := 0;beginv_num := 2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_ou
38、tput.put_line(error);end;/ -变量声明的规则1. 变量名不能够使用保留字，如 from，select 等2. 第一个字符必须是字母3. 变量名最多包含 30个字符4. 不要与数据库的表或者列同名5. 每一行只能声明一个变量-常用变量类型1. binary_integer:整数，主要用来计数而不是用来表示字段类型2. number:数字类型3. char:定长字符串4. varchar2:变长字符串5. date:日期6. long:长字符串，最长 2GB6. boolean:布尔类型，可以取值为 true，false 和 null值-declare变量的声明decla
39、rev_temp number(1);v_count binary_integer :=0;v_sal number(7,2) := 4000.00;v_date date := sysdate;v_pi constant number(3,2) := 3.14;-constant表示常量v_valid boolean := false;-不能打印 Boolean类型变量的值v_name varchar2(20) not null := MyName;begindbms_output.put_line(v_temp value: | v_count);end;/ -创建 emp表create
40、table emp(empno number(4) not null ,ename varchar(10),job varchar(9),hiredate date,sal number(7,2),deptno number(4),primary key(empno);commit;insert into emp values(7369,smith,computer,1300,10);insert into emp values(110,jerry,computer,1300,10);insert into emp values(119,lucy,computer,1300,50);-变量声明
41、，使用%type 属性declarev_empno number(4);v_empno2 emp.empno%type;-声明变量 v_empno2为 emp表中 empno字段的类型v_empno3 v_empno2%type;-声明变量 v_empno3为变量 v_empno2的类型begindbms_output.put_line(Test);end;/ -复合类型变量（table，Record）-table变量类型(相当于数组)declare-自定义类型 type_table_emp_empno为 emp表 empno字段类型,下标用整数表示type type_table_emp_em
42、pno is table of emp.empno%type index by binary_integer;v_empnos type_table_emp_empno;-用自定的类型声明变量beginv_empnos(0) := 7369;v_empnos(2) := 7839;v_empnos(-1) := 9999;dbms_output.put_line(v_empnos(-1);end;/ -创建 dept表create table dept(deptno number(4) not null ,dname varchar(10),loc varchar(100),primary k
43、ey(deptno);-Record变量类型（相当 java中的类）declaretype type_record_dept is record-自定义类型 type_record为 record类型(deptno dept.deptno%type,-deptno为 dept表 depno字段类型dname dept.dname%type,-dname为 dept表 dname字段类型loc dept.loc%type-loc为 dept表 loc字段的类型);v_temp type_record_dept;-用自定义变量beginv_temp.deptno := 50;v_temp.dnam
44、e := aaaa;v_temp.loc := bj;dbms_output.put_line(v_temp.deptno | | v_temp.dname);end;/ -使用%rowtype 声明 record变量declarev_temp dept%rowtype;beginv_temp.deptno := 50;v_temp.dname := aaaa;v_temp.loc := bj;dbms_output.put_line(v_temp.deptno | | v_temp.dname);end;/ -SQL语句的运用declarev_ename emp.ename%type;v_s
45、al emp.sal%type;beginselect ename,sal into v_ename,v_sal from emp where empno = 7369;dbms_output.put_line(v_ename | | v_sal);end;/ -未找到记录错误(查询的不存在，会报错误)declarev_ename emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_ename,v_sal from emp where empno = 111;dbms_output.put_line(v_ename | | v_sal);end;/ -返回多条记录错误（dept = 10 有多条记录）declarev_ename emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_ename,v_sal from em
