Oracle

group by语句： select deptno,round(avg(sal),2) from scott.emp group by deptno;

 select job,deptno,avg(sal) from scott.emp group by job,deptno;


select ename,max(sal) from scott.emp

*

第 1 行出现错误:

ORA-00937: 不是单组分组函数

因为一个max（sal） 可能对应多个ename，所以必须出现group by语句

select ename,max(sal) from scott.emp group by ename;

单组函数必须对应的值是唯一的。


where语句是对单条语句进行过滤


having语句是对分组进行过滤。

select deptno,avg(sal) from scott.emp group by deptno having avg(sal)>2000；


select语句顺序


1 select*from scott.emp

2 where sal>1000

3 group by deptno

4 having

5 order by



子查询：查一个人赚的最多

SQL> select ename,sal from scott.emp

2 where sal=(select max(sal) from scott.emp);



自连接

SQL> select e1.ename,e2.ename from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;


SQL> select ename,dname,grade from scott.emp e,scott.dept d,scott.salgrade s

2 where e.deptno=d.deptno and e.sal between s.losal and s.hisal;


两张表的自连接

SQL> select ename,dname from scott.emp join scott.dept on(emp.deptno=dept.deptno

);


求薪水等级

SQL> select ename,grade from scott.emp e join scott.salgrade s on(e.sal between

s.losal and s.hisal);


综合

SQL> select ename,dname,grade

2 from scott.emp e join scott.dept d on(e.deptno=d.deptno)

3 join scott.salgrade s on(e.sal between s.losal and s.hisal)

4 where ename not like '_A%';


左（右）连接

SQL> select e1.ename,e2.ename from scott.emp e1 left（right） join scott.emp e2 on(e1.mgr

=e2.empno);


求部门中哪些人薪水最高

SQL> select ename,sal from scott.emp

2 join(select max(sal) Max_sal,deptno from scott.emp group by deptno) t

3 on(emp.sal=t.Max_sal and emp.deptno=t.deptno);


求部门中平均薪水的等级

select deptno,grade,Avg_sal from

(select deptno,avg(sal) Avg_sal from scott.emp group by deptno) t

join scott.salgrade s on(t.Avg_sal between s.losal and s.hisal );


部门中那些人是经理人

SQL> select ename from scott.emp where empno in(select mgr from scott.emp);


不用组函数求薪水的最高值

SQL> select distinct sal from scott.emp where sal not in(select distinct e1.sal

2 from scott.emp e1 join scott.emp e2 on(e1.sal<e2.sal));


平均薪水最高的部门的部门编号

1. select deptno,Avg_sal from

(select avg(sal) Avg_sal,deptno from scott.emp group by deptno)

//把那整条语句当成平均工资最高的值来对待。

where Avg_sal=

(select max(Avg_sal) from

(select avg(sal) Avg_sal,deptno from scott.emp group by deptno));


2. 组函数的嵌套：select deptno,avg_sal from

(select avg(sal) avg_sal,deptno from scott.emp group by deptno)

where avg_sal=

(select max(avg(sal)) from scott.emp group by deptno)

平均薪水最高的部门的部门名称

select dname from scott.dept where deptno=

(

select deptno from

(select avg(sal) Avg_sal,deptno from scott.emp group by deptno)

where Avg_sal=

(select max(Avg_sal) from

(select avg(sal) Avg_sal,deptno from scott.emp group by deptno)

)


求平均薪水的等级最低的部门的部门名称

1 select t1.deptno,avg_sal,dname from

2 (select deptno,grade,avg_sal from

3 (select deptno,avg(sal) avg_sal from scott.emp group by deptno) t

4 join scott.salgrade s on(t.avg_sal between s.losal and s.hisal)

5 ) t1

6 join scott.dept on(t1.deptno=dept.deptno)

7 where grade=

8 (

9 select min(grade) from

10 (select grade from

11 (select avg(sal) avg_sal from scott.emp group by deptno) t

12 join scott.salgrade s on(t.avg_sal between s.losal and s.hisal)

13 )

14* )



授权键表格和视图

conn sys/bjsxt as sysdba;

grant create table,create view to scott;

commit 提交完成将不能rollback回退了。

dml 语句 select delete insert  update 

ddl语句 creat table

dcl语句 grant

当用户正常断开连接 cosection 会自动提交

数据库产生对象

怎么创建表

创建一个学生表

create table stu(

id number(6),

name varchar2(20),

sex number(1),

age number(3),

sdata data,

grade number(2) default 1,

class number(2),

email varchar2(50)

);

非空 constraint

create table stu(

id number(6),

name varchar2(20) constraint stu_name_nn not null,

sex number (1),

age number(3),

sdata data,

grade number(2) default 1,

class number(2),

email varchar2(50)

);

唯一约束 unique

create table stu(

id number(6),

name varchar2(20),

sex number(1),

age number(3),

sdata data,

grade number(2) default 1,

class number(2),

email varchar2(50) unique

);

组合约束

create table stu(

id number(6),

name varchar2(20) contraint stu_name_nn not null,

sex number(1),

age number(3),

sdata data,

grade number(2) default 1,

class number(2),

email varchar2(50),

constraint stu_name_email_uni unique(email,name)

)

加字段上，叫字段约束，加表上叫表级约束。

primary key 主键约束。既不能为空又不能重复

 create table stu

 (

 id number(6) primary key,

 name varchar2(20),

 class number(4),

 constriant stu_class_fk foreign key(class) references class(id),

 constraint stu_id_pk primary key(id)

 )

外键约束 foreign key

1  create table stu(

2  id number(6),

3  name varchar2(20),

4  class number(4),

5  constraint stu_class_fk foreign key(class) references class(id),

6  constraint stu_id_pk primary key(id)

7* )

修改表结构 alter

增加数据  alter table stu add(addr varchar2(50));

删除表数据  alter table stu drop (addr);

修改表数据 alter table stu modify(addr varchar(50));

取消约束条件 alter  table stu drop constraint stu_class_fk;

添加约束条件 alter table stu add constraint stu_class_fk foreign key(class) references class(id)

数据字典表

user_tables

user_constraints

创建索引(不能轻易创建，有需要在创建)

create index idx_stu_email on stu (email);

创建视图

功能：

就是一个子查询，简化查询、给出部分信息保护数据，坏处：维护数据。

创建序列 create sequence sqe;

序列下一个数 select sqe.nextval from dual;

删除 drop sequence sqe；

数据库设计三范式：

第一范式：要有主键，列不可分

第二范式：当一张表有多个字段作为主键的时候，非主键字段不能依赖部分主键。不能存在部分依赖。

35_三范式.avi

 数据库设计的三范式：
 范式：数据库设计时的一些规则。而这些规则是由一个姓范的人规定的，所以叫范式
 三范式设计的规则：
 三范式所追寻的原则是：不存在冗余数据(同样的数据我不存第二遍)
 
 第一范式的要求：1.要有主键(设计任何表都要有主键)
   2.列不可分

 第二范式的要求：当一张表里面有多个字段作为主键的时候，非主键的这些字段，不能依赖于部分主键
   (只能依赖整个组合的主键，不能依赖部分)
   叫做：不能存在部分依赖

 第三范式的要求：不能存在传递依赖(除了主键之外的任何其他字段必须直接依赖于主键)

36_BBS_1.avi
 
 1.论坛是要分板块的，板块里面有不同的帖子
 2.帖子是有回复的，第2个人回复第1个人，第3个人回复第2个人，第4个人回复第3个人，第5个人回复第2个人，这是一种树状结构
 3.只有注册的用户才能发表帖子
 4.每一个板块有自己的版主，版主可以删帖子
 按照上面需求，设计表，来支撑我们整个BBS项目

37_BBS_2.avi

 老师与学生探讨表的设计
 user(id,name,password)
 board(id,name,user_id)//版块(版块号，名字，版主)

 message(id,author_id,board_id,content)

PL/SQL语言

SQL> set serveroutput on;

// 默认是 off ，设成 on 是让 Oracle 可以在客户端输出数据

SQL> begin

  2  dbms_output.put_line('hello,world!');

  3  end;

  4  /

hello,world!

PL/SQL 过程已成功完成。

----------------pl/sql变量的赋值与输出----

       declare

              v_name varchar2(20);//声明变量v_name变量的声明以v_开头

       begin

              v_name := 'myname';

              dbms_output.put_line(v_name);

       end;

-----------pl/sql对于异常的处理(除数为0)-------------

       declare

              v_num number := 0;

       begin

              v_num := 2/v_num;

              dbms_output.put_line(v_num);

       exception

              when others then

              dbms_output.put_line('error');

       end;

----------变量的声明----------

       binary_integer:整数，主要用来计数而不是用来表示字段类型   比number效率高

       number:数字类型

       char:定长字符串

       varchar2：变长字符串

       date：日期

       long：字符串，最长2GB

       boolean：布尔类型，可以取值true，false，null//最好给一初值

----------变量的声明，使用 '%type'属性---------

       declare

              v_empno number(4);

              v_empno2 emp.empno%type;

              v_empno3 v_empno2%type;

       begin

              dbms_output.put_line('Test');

       end;

       /

//使用%type属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且%type属性，可以用于变量身上

constant 相当于java中final

42、-----------------Record变量类型（相当于Java里面的类）--------------------------------------------

       declare

              type type_record_dept is record

              (

                     deptno dept.deptno%type,

                     dname dept.dname%type,

                     loc dept.loc%type

              );

              v_temp type_record_dept;

       begin

              v_temp.deptno:=50;

              v_temp.dname:='aaaa';

              v_temp.loc:='bj';

              dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);

       end;

-----------使用 %rowtype声明record变量，直接参照表来声明record-------------------

       declare

              v_temp dept%rowtype;

       begin

              v_temp.deptno:=50;

              v_temp.dname:='aaaa';

              v_temp.loc:='bj';

       dbms_output.put_line(v temp.deptno || '' || v temp.dname)                

       end;

       

43 、 --------------select 语句的运用 ( 必须保证 select 语句有相应的返回记录 , 并且只能返回一个值 )-------------------

       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;

---------------------------select 语句的应用（ record ） ----------------------------------------

       declare

              v_emp emp%rowtype;

       begin

              select * into v_emp from emp where empno=7369;

              dbms_output_line(v_emp.ename);

       end;

------------- insert 语句的应用 -----------------------------

       declare

              v_deptno dept.deptno%type := 50;

              v_dname dept.dname%type :='aaa';

              v_loc dept.loc%type := 'bj';

       begin

              insert into dept2 values(v_deptno,v_dname,v_loc);

               commit;

       end;

       ------------- update 语句的应用 ------------------------------

       declare

              v_deptno emp2.deptno%type := 50;

              v_count  number;

       begin

              update emp2 set sal = sal/2 where deptno = v_deptno;

              dbms_output.put_line(sql%rowcount || ‘ 条记录被影响 ’);

               commit;

       end;

注： sql%rowcount 统计上一条 sql 语句更新的记录条数

ddl是create语句

dcl 是 grant语句

if语句

  1  declare

  2  v_sal emp2.sal%type;

  3  begin

  4  select sal into v_sal from emp2 where empno=7839;

  5  if(v_sal>2500)then

  6     dbms_output.put_line(v_sal/2);

  7  elsif(v_sal=2500)then

  8  dbms_output.put_line(v_sal);

  9  else

 10  dbms_output.put_line(v_sal*2);

 11  end if;

 12* end;

loop语句

 1  declare

 2  i binary_integer:=1;

 3  begin

 4  loop

 5  dbms_output.put_line(i);

 6  i:=i+1;

 7  exit when(i>=11);

 8  end loop;

 9* end;

while loop语句

SQL>  declare

  2  j binary_integer:=1;

  3  begin

  4  while j<11 loop

  5  dbms_output.put_line(j);

  6  j:=j+1;

  7  end loop;

  8  end;

  9  /

for循环

  1  begin

  2  for k in 1..10 loop

  3  dbms_output.put_line(k);

  4  end loop;

  5  for k in reverse 1..10 loop

  6  dbms_output.put_line(k);

  7  end loop;

  8* end;

46、-----------------------异常(1) ---------------------------

  declare

  v_temp number(4);

  begin

  select empno into v_temp from emp where deptno = 10;

  exception

  when too_many_rows then

  dbms_output.put_line('太多记录了');

  when others then

  dbms_output.put_line('error');

  end;

-----------------------异常(2) ---------------------------

  declare

  v_temp number(4);

  begin

  select empno into v_temp from emp where empno = 2222;

  exception

  when no_data_found then

  dbms_output.put_line('没有该项数据');

  end;

----------------错误记录日志(用表记录：将系统日志存到数据库便于以后查看) -----------

创建序列（用来处理递增的ＩＤ）：

  create sequence seq_errorlog_id start with 1 increment by 1;

  创建日志表:

  create table errorlog

  (

  id number primary key,

  errcode number,

  errmsg varchar2(1024),

  errdate date

  );

  示例程序：

  declare

  v_deptno dept.deptno%type := 10;

  v_errcode number;

  v_errmsg varchar2(1024);

  begin

  delete from dept where deptno = v_deptno;

  commit;

  exception

  when others then

  rollback;

  v_errcode := SQLCODE;

  v_errmsg := SQLERRM;

  insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate);

  commit;

  end;

、 ---------------------PL/SQL 中的重点 cursor( 游标 ) 和指针的概念差不多 ----------------------

       declare

              cursor c is

select * from emp; // 此处的语句不会立刻执行，而是当下面的 open c 的时候，才会真正去数据库中取数据

              v_emp c%rowtype;

       begin

              open c;

                     fetch c into v_emp;

dbms_output.put_line(v_emp.ename); // 这样会只输出一条数据 134 将使用循环的方法输出每一条记录

         close c;

       end;

---------------------- 使用 do while  循环遍历游标中的每一个数据 ---------------------

       declare

              cursor c is

                     select * from emp;

              v_emp c%rowtype;

       begin

              open c;    

              loop

                     fetch c into v_emp;

(1)   exit when (c%notfound);  //notfound 是 oracle 中的关键字，作用是判断是否还有下一条数据

(2)   dbms_output.put_line(v_emp.ename);  //(1)(2) 的顺序不能颠倒，否则会把最后一条结果再多打印一次。

          end loop;

          close c;

       end;

------------------------ 使用 while 循环，遍历游标 ---------------------

       declare

              cursor c is

                     select * from emp;

              v_emp emp%rowtype;

       begin

              open c;

              fetch c into v_emp;

              while(c%found) loop

                 dbms_output.put_line(v_emp.ename);

                 fetch c into v_emp;

          end loop;

          close c;

       end;

-------------------------- 使用for 循环，遍历游标（最方便快捷的方法！） ---------------------

       declare

              cursor c is

                 select * from emp;

       begin

              for v_emp in c loop

                     dbms_output.put_line(v_emp.ename);

              end loop;

       end;

--------------------------- 带参数的游标（相当于函数） ---------------------

       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;

------------------------- 可更新的游标 -----------------------------

       declare

              cursor c

              is

                 select * from emp2 for update;

       begin

          for v_temp in c loop

              if(v_temp.sal < 2000) then

                     update emp2 set sal = sal * 2 where current of c;

             elsif (v_temp.sal =5000) then

              delete from emp2 where current of c;

              end if;

            end loop;

            commit;

       end;

-----------------------------创建触发器（trigger） 触发器不能单独的存在，必须依附在某一张表上

写 主语 谓语 宾语 游戏

  创建触发器的依附表：

  create table emp2_log

  (

  ename varchar2(30) ,

  eaction varchar2(20),

  etime date

  );

  create or replace trigger trig

after insert or delete or update on emp2 for each row --加上此句，每更新一行，触发一次，不加入则值触发一次

  begin

  if inserting then

  insert into emp2_log values(USER, 'insert', sysdate);

  elsif updating then

  insert into emp2_log values(USER, 'update', sysdate);

  elsif deleting then

  insert into emp2_log values(USER, 'delete', sysdate);

  end if;

  end;

更新

update emp2 set sal=sal*2 where deptno=30;

查看更新日志。

select*from emp2_log;

-------------------- 触发器用法之一：通过触发器更新约束的相关数据 -------------------

       create or replace trigger trig

              after update on dept

              for each row

       begin

              update emp set deptno =:NEW.deptno where deptno =: OLD.deptno;

       end;

       

       // 只编译不显示的解决办法 set serveroutput on;

52 、 ------------------------------- 通过创建存储过程完成递归

    

 create or replace procedure p(v_pid article.pid%type,v_level binary_integer) is

              cursor c is select * from article where pid = v_pid;

              v_preStr varchar2(1024) := '';

       begin

         for i in 0..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) then

                     p(v_article.id,v_level+1);

              end if;

              end loop;

       end;

通过创建存储过程完成递归(完成输出表)

create or replace procedure p is

cursor c is select*from scott.emp;

c_row c%rowtype;

begin

for c_row in c loop

dbms_output.put_line(c_row.ename||' '||c_row.deptno||' '||c_row.mgr||' '||c_row.job||' '||to_char(c_row.hiredate,'YYYY-MM-DD')||' '||c_row.sal||' '||nvl(c_row.comm,0));

end loop;

end;