oracle

一、访问方式：
      (1)命令行
      (2)sqlplus客户端
      (3)浏览器(http://127.0.0.1:5560/isqlplus/，如果不能访问，则isqlplusctl start)
二、用户解锁：
      登录：sqlplus sys/密码 as sysdba;
      解锁：alert user scott account unlock;

三.用户操作

    1.备份  exp

   2.创建用户 create user 用户名  identified by 密码  default  tablespace users quota 10M  on users

                     grant create session , create table , create view;

   3. 导进数据库  imp
四.语句(字母加单引号   中文加双引号)

1. desc  emp;

2.select * from  emp;

3.select distinct sal from emp;

4.select * from emp where deptno = 10  order by  sal  desc/asc;

5.select * from emp where sal between 400 and 1500;

6.select * from   emp  where  sal  in (122,44,45555);

7. select  lower/upper(ename) from emp;

8.select substr (ename ,3,3)  from emp;

9.select chr(65) from dual;

10. select ascii('A') from dual;

11. select round (233.44454, 3)  from dual;

12.select to_char (sal , '$1111.3333') from dual;

14 select to_char (sysdate, 'YYYY-MM-DD  HH(24):MI:SS') from dual;

15. select to_number(123,'') from dual;

16.select ename , sal*12+nvl(comm, 0) from emp;

17. slect sum/max/min/avg (sal) from emp  group by deptno,job;

18.selct count (*) from emp ; 记录多少行

19.slect sum/max/min/avg (sal) from emp  group by deptno  having avg(sal)<2000;

20.select * from emp

     where sal >1000

    group by deptno

    having  avg(sal) <3333

    order by job desc;

21.select ename, dname from emp (cross) join dept on(连接条件） where （排除条件）；

 

22.select ename , dname , grade from emp e join dept d on(e.deptno =d.deptno) 

        join salgrade s on(e.sal between s.losal and s.hisal)  where ename  not like '_A%';

23 外连接   left /right /full   join

24rownum 只能用<=或者 < 不能用 >;

 

ddm

 

25. insert  into dept (deptno, dname)  values(50, 'game);

26.rollback;

27. insert into dept 2 select * from dept;

28.update emp set   sal = sal*2;

29.delete from emp where deptno <24;

 

 

数据定义语言

30.create table t (a  var char2(10));

31.drop table t;

33. commit;

34 ,非空  not null;

35，唯一  unique;

36，主键  primary key;

37，外键   必须是主键

38.check（）

改变表结构（应该把建表的语句保留下来）

39.alter table stu add (adr varchar2(100));

40.alter table stu drop  (addr);

41alter table stu modify (addr varchar2(334));

41.alter table stu  drop constraint stu _class_ fk;

42. select table_name from user_table;

43.select constraint_name from user_constraint;

44.desc user_tables;

45.desc dictionary;

46.create index index_stu _emai on stu (email, class);

47. drop index index_stu_email;

48.select index_name from user _index;

50.create view v$_dept_avg_sal  as子查询；

51.create sequence seq;

52. select seq.nextval from dual;

 

 

范式

 

第一范式  1.主键 2.列不可分

第二范式 1.不存在非主键依赖部分主键 ——把一张表拆成多张表

第三范式  不存在间接依赖 （班级信息——班级学号——学生学号（主键））

 

 

 

 

其他的 内容

求部门的平均薪水 的等级：
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
/
求部门中那些人薪水最高 ：
select ename , sal from emp
join (select max(sal) max_sal , deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno)

-- 求部门平均 的薪水等级
select deptno, avg(grade) from (select deptno, ename, sal ,grade from emp e join salgrade s
on ( e.sal between s.losal and s.hisal)) t group by deptno

——雇员中有那些人是经理人
 select ename from emp where empno in (select distinct mgr from emp)

——不准用组函数，求薪水最高值
1.select sal from emp where sal not in (select sal from(select e.sal from emp e
join ( select e1.sal from emp e1) t
on ( e.sal < t.sal)) )
2.select distinct sal from emp where sal not in
( select distinct e.sal from emp e join emp em on(e.sal < em.sal))
——求平均薪水最高的部门的部门编号
1.select deptno ,avg_sal from
(select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal ,deptno from emp group by deptno)
)

2.select deptno ,avg_sal from
(select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno)

——求平均薪水最高的部门的部门名称
 select dname from dept where deptno =
(select deptno  from
(select avg(sal) avg_sal , deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal ,deptno from emp group by deptno)
)
)
——求平均薪水的等级最低的部门的部门名称
1.select dname ,t1.deptno, grade, avg_sal from
 (select deptno, avg_sal, grade from 
   ( select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
) t1
 join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
 select min(grade) from
 (
    select deptno, avg_sal, grade from 
  ( select deptno, avg(sal) avg_sal from emp group by deptno)t
 join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
)

2.

SQL> conn sys/tubiaosong as sysdba;
已连接。
SQL> grant create table, create view to scott;

授权成功。

SQL> conn scott/tubiaosong

create view  v$_dept_avg_sal_info as
select deptno, grade, avg_sal from
(select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

select dname ,t1.deptno, grade, avg_sal from
 (select deptno, avg_sal, grade from 
   ( select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
) t1
 join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
 select min(grade) from
 t1 )

——求部门经理人中平均薪水最低的

 

—— 求部门经理中 比 普通员工 最高工资还高的名字
select ename from emp 
where empno in ( select distinct mgr from emp where mgr is not null) and
  sal >
 (select max(sal) from emp where empno not in ( select distinct mgr from emp where mgr is not null))

——求薪水最高的前五名
select ename , sal from ( select ename, sal from emp order by sal desc)
where rownum <= 5
——求薪水 在第六名到第十名 的名字
elect ename, sal, r from (select ename, sal, rownum r from ( select ename, sal from emp order by sal
desc)) where r >=6 and r <= 10

 

 

——创建表

create table student
(
id number(6) constraint student_id primary key,
name varchar2(20) constraint student_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2),
class number(4) references class(id),
email varchar2 (50),
costraint stu_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key(id,name),
constraint student_name_email_uni unique(email, name)
)
/

create table class
(
 id number(4) primary key,
name varchaer2(20) not null
)

pl 语句
1.变量声明 第一字符必须是字母；
2.每一行只能声明一个变量；

常用变量类型

1.   binary_interger: 整数
2.   number: 数字类型
3.   char: 定长字符串
4.   varchar2: 变长字符串
5.   date: 日期
6.   long: 长字符串  2GB
7.   boolean: 布尔类型 可以去null

使用 %type属性
declare
 v_empno emp.empno%type;  随表的属性改变

 

table类型

 type(表示定义了一种信的类型） type_table_emp_empno  is table(类似于数组类型） of emp.empno%type （表示数组装的类型） index by binary_integer（下标的类型 一般这样写就可以了）;
 begin
      v-empnos(0) := 123;

 

record 变量类型

   type type_record_dept is record(类似于 java的类）
   （
           deptno dept.deptno%type,
    dname dept.dname%type
);
    v_temp type_record_dept;
begin
    v_tem.deptno :=50;
      
使用%rowtype声明record变量
declare
     v_temp dept%rowtype;
begin
   v_temp.deptno:=40;

不能打印布尔类型

declare
 v_ id number(2，2);  注意
 v_sname varchar2(20) not null := 'Myname  注意
 v_name varchar2(20) := 'myname';
 v_num number(4) := 0;

begin
  v_id := 2.22;
   dbms_output.put_line(v_name|| v_id);   连接字符
   v_num := 4/v_num;

Exception
  when others then
    dbms_output.put_line('erro');

end;
/

 

 

 

 

 

 

select update insert 语句

select 必须返回一条唯一 语句；

select ename ,sal into v_ename , v_sal from emp where empno = 5333; 必须保证能返回一条数据  没有和多了都不行

其他的语句 都一样 但 要执行 commit
sql  代表刚刚执行的语句
rowcount  代表sql 影响了多少条记录

begin
   update emp2 set sal = sal/2 where daptno = v_deptno;
   dbms_output.put_line(sql%rowcount ||'条记录被影响');
   commit;

 

 

 

 

语句定义 ddl

begin
   execute immdiate 'create table T (nnn varchar2(20)default ''aaa'')';
end;

\

drop table t;

 

if语句

declare
 V_sal em.sal%type;
begin
 select sal into v_sal from emp
  where empno = 7369;
 if(v_sal <1200) then
  dbms_output.put_line ('low');
 elsif(v_sal <2000) then
  dbms_output.put_line('middle');
 else
  dbms_output.put_line('high');
 end if;
end;

 

循环语句

1.begin
      loop
            dbms_output.put_line(i);
     i := i +1;
     exit when (i>=11);
     end loop;

2.begin
 while j<11 loop
 dbms_output.put_line(j);
  j:= j+1;
 end loop;
3.begin
 for k in 1..10 loop
      end loop;
      for k in reverse 1..10 loop   逆序
        dbms_output.put_line(k);
     end loop;

 

错误处理

too_many_rows  太多值
no_data_found  没有数据

 

记录错误的做法

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 ;    注意

declare
 v_deptno dept.deptno%type :=10;
 v_errmsg varchar2(1024);
 v_errcode number;
begin
 delete from dept where deptno = v_deptno;
 commit;
exceptin
 when others then
  rollback;
  v_errcode :=SQLCODe;
  v_errmsg := SQLERRM;
     insert into errorlog valuse (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
  commit;
end;

 

游标

1.declare
 cursor c is
  select * from emp;
 v_emp c%rowtype;
begin
 open c;
 fetch c into v_emp;
 dbms_output.put_line(v_em.ename);
 close c;
end;

2.declare
    cursor c is
 select * from emp;
 v_em 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;

3.declare
    cursor c is
 select * from emp;
 v_em c%rowtype;
begin
 open c;
        fetch c into v_emp;
        while (c%notfound) loop
      dbms_output.put_line (v_emp.ename);
             fetch c into v_emp;
        end  loop;
 close c;
end;

4.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  em.deptno%type, v_job emp.jov%type)
    is(
        select ename, sal from emp where deptno = v_deptno and job = v_job;
 --v_temp c%rowtype; 注释 声明

begin

 for v_temp in c(30, 'CLERK') loop
            dbms_output.put_line (v_emp.ename);
     end loop;
 end;

可以更新 的游标

declare
    cursor c is
 select * from emp for update ;

begin
        for v_emp 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 fi;
       end looop;
    commit;
end

 

 

创建 存储过程
 

create or repleace procedure p
 is
  cursor c is
 select * from emp for update ;

begin
        for v_emp 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 fi;
       end looop;
    commit;
end

   只是创建  并没有执行

执行  exec p；

或者  begin
     p；
  end；

带参数的存储过程

create or replace procedure p
  (v_a in( 调用环境 给 存入传入参数 ) number, v_b（默认是in） number, v_ret out（与in 相反） number, v_temp in out number)
 is

begin
 if (v_a > v_b) then
 v_ret :=v_a;
 else
 v_ret :=v_b;
   end if;
   v_temp := v_temp +1;
end;

 

declare
   v_a number := 3;
   v_b 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;

存储过程不会告诉你哪里错误
用

  show error

查找错误

 

函数

创建函数

create or replace function  sla_tax
    (v_sal number)
    return number
is
begin
   if(v_sal<2000) then
       return 0.10;
   elsif(v_sal <2750) then
 return 0.15;
   else
 return 0.2o;
   end if;
   end;

调用函数

  select lower(ename), sal_tax(sal) from emp;

 

触发器

  当你做一件事，产生另外一个事件；

  用来记录；必须依附一张表

触发器创建
create or replace trigger trig
    after/before insert or delete or update on emp2 （for each row）

begin
    if inserting then
    elsif updating then
    elsif deleting then
    end if;
end;

所以表 的 值同步的变  (最好不要用）

create or repleace trigger trig
  after update on dept
   for each row
begin
  update emp set deptno = :NEW.deptno where  deptno = : OLD.deptno;
end;

 

update dept set deptno = 99 where deptno = 10;

 

 

 

 

 

——树状结构的储存和展示

create table article
(
id number primary key,
cont varchar2(400),
pid number, 回复的父节点 id
isleaf number(1), 0--代表非叶子节点 ，1 代表叶子节点
alevel  number(2)
);

insert into article  values (1,'蚂蚁大战大象',0,0,0);
insert into article  values (2,'蚂蚁被打趴下了'，1,0,1);
insert into article  values (3,'蚂蚁不好过',2,1,2);
insert into article  values (4,'瞎说',2,0,2);
insert into article  values (5,'没有瞎说',4,1,3);
insert into article  values (6,'怎么可能',1,0,1);
insert into article  values (7,'怎么么有可能',6,1,2);
insert into article  values (8,'可能性很大',6,1,2);
insert into article  values (9,'大象进医院',2,10,2);
insert into article  values (10,'护士是蚂蚁',9,1,3);
蚂蚁大战大象
    蚂蚁被打趴下了
       蚂蚁不好过
       瞎说
           没有瞎说
       大象进医院
           护士是蚂蚁
    怎么可能
          怎么没有可能
          可能性很大

定义递归

create or replace procedure p (v_pid article.pid%type,v_lever binary_integer ) is
  cursor c is select * from arcticle where pid = v_pid;
  v_preStr varchar2(1024) :='';

begin
   for i in 1..v_lever loop
     v_preStr := v_preStr ||'********';
   end loop;
  for v_article in c loop
   dbms_output.put_line (v_article.cont);
   if(v_article.isleaf = 0) then
     p(v_article.id, v_lever+1);
  end if;
 end loop;

 

exec p(0,0);
 

set serveroutput on;