oracle数据库学习笔记

查看表结构 ： desc emp;

虚表 dual 如 select sysdate from dual;

关于给字段取别名：

select 字段 别名 from 表;

select 字段 as 别名 from 表;

如果别名需要用到空格：select 字段 "含空格别名" from 表;

含有空值的数学表达式结果都是空值。

字符串连接符： ||  如果连接的字符串中含有单引号，使用 2个单引号即可，如   select ename||'abc''def' from emp;

distinct

消除重复的值 select distinc 字段 from 表;

where

筛选: 

基本筛选：> < = <>is (not) nullin(...)/not in(...)...and/or...日期按照格式...where hiredate>'03-12月-81'红色部分必须格式一致

模糊查询：like

...like 'ALL';

...like '%ALL';取出所有以 ALL 结尾的。

...like 'ALL%';取出所有以 ALL 开头的。

...like '%ALL%';取出所有含有ALL的。

...like '_A%';取出所有第二个是字符A的，一个_代表一个字符。

如果检索包含 % 的字符串，使用转义符 \ ：...like '%\%%';转义符可以指定：...like '%$%%' escape '$';

order by

排序

select 字段 from 表 order by 字段 desc/asc; desc：降序 asc：升序，不写表示升序。where 在order by 前面：select 字段 from 表 where ... order by ...

可以按照多个字段排序：...order by 字段1 desc/asc,字段2 desc/asc...;表示的意思是，当字段1相同时，数据按照字段2指定顺序排列

函数：可以用在检索的字段，可以用在where条件中

如：

转换成小写 lower()：select lower(ename) from emp;select ename from emp where lower(ename) like...

字符串截取substr(字段名,初始位置,截取个数):select substr(ename,2,1) from emp;其中初始位置从1开始，不是从0开始

字符chr(数字) ：select chr(65) from dual;结果为 A；相反，ascii('A'),结果为65

四舍五入round():round(23.653)结果为24，round(23.653,2)结果为23.65，round(23.653,1)结果为23.7，round(23.653,-1)结果为20

函数有很多，可以不记得具体使用，但是需要知道有哪些函数

其中，需要牢记的函数：

to_char()

to_date()

to_number()

nvl()

空值处理nvl(字段,0):如果字段为空，用0代替

max() 

min()

avg()

组合函数：

sum()

count()可以和distinct一起用：count(distinct 字段)

group by

当需要用到组合函数時，同时又需要分类计算，需要用到group by：例如班级平均成绩，进行科目分类。

having

对分组进行限制，在goup by之后

总结一下sql语句的顺序：

select * from 表 

where ...

group by...

having...

order by...

子查询：将一个查询结果用括号括起来当作一个表来使用。

连接查询：

92年标准： select ... from 表1,表2...where ...

存在一个不易读的问题，where后面连接的条件和逻辑过滤条件混合在一起。

99年建立新的标准：使用 join

各种复杂的查询，可以通过一步一步从内而外来拆解成简单的查询

create - insert - update - delete

创建用户：

exp 导出数据

drop user hcz;

create user hcz identified by hcz default tablespace user quota 10m on users;

grant create session,create table,create view to hcz;

imp 导入数据

关于rownum：select * from emp where  rownum </<= 5;只能跟<   or  <=

解决办法：用子查询 select * from (select rownum r,e.* from emp e) where r<10

insert

insert into 表 values (...);

insert into 表(....) values (...);

insert into 表 select ....; 检索的整个结果插入

update

update 表 set 字段1 = ...,字段2 = ... where ...

delete

delete from 表 where... 不加where条件删除整个表的数据，删除表结构：drop table 表

事物transaction：

起始于一个DML语句，结束于commit、rollback、DDL/DCL语句自动commit、正常断开自动commit、异常断开自动rollback

建表

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

create table stu
(
       id number(6),
       name varchar2(20) constraint stu_name_nn not null, --非空约束
       sex number(1),
       age number(3),
       sdate date,
       grade number(2) default 1,
       class number(4),
       email varchar2(50),
       constraint stu_class_fk foreign key (class) references class(id), --外键
       constraint stu_id_pk primary key (id),-- 主键
       constraint stu_name_email_uni unique(email,name), -- 复合唯一约束
）

其中5中约束：not null、unique、primary key、foreign key、check
修改表结构：

alter table stu add(addr varchar2(100));


alter table stu modify(addr varchar2(150));

alter table stu drop constraint stu_class_fk;

alter table stu add constraint stu_class_fk foreign key (class) references class(id)

create sequence seq;
select seq.nextval from dual;--1
select seq.nextval from dual;--2
select seq.nextval from dual;--3

create sequence emp_sequence


INCREMENT BY 1 -- 每次加几个


START WITH 1 -- 从1开始计数


NOMAXVALUE -- 不设置最大值


NOCYCLE -- 一直累加，不循环


CACHE 10;

set serveroutput on;
begin
  dbms_output.put_line('hello world'); --输出hello world
end;
declare
  v_name varchar2(20);
begin
  v_name := 'myname';
  dbms_output.put_line(v_name); -- 输出 myname
end;

declare
  v_num number := 0;
begin
  v_num := 2/v_num;
  dbms_output.put_line(v_num);
exception
  when others then -- 没有写原来的情况，那么其他情况（others）就包括所有情况
    dbms_output.put_line('error');
end;

declare
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
--type表示定义一种类型，名为type_table_emp_empno,类型为emp.empno，下标为binary_integer类型
    v_empnos type_table_emp_empno;-- 定义一个变量v_emonos，类型为type_table_emp_empno
begin
  v_empnos(0) := 7369;
  v_empnos(2) := 7839;
  v_empnos(-1) := 9999; --oracle中下标可以是负数
  dbms_output.put_line(v_empnos(-1));
end;

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 := 'aaa';
    v_temp.loc := 'bj';
    dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname); --取值
end;

declare
    v_temp dept%rowtype;
begin
    v_temp.deptno := 50;
    v_temp.dname := 'bbb';
    v_temp.loc := 'bj';
    dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;

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;--不用游标的时候，select语句里面必须有into，select需有且仅有一条数据
  dbms_output.put_line(v_ename || ' ' || v_sal);
end;

declare
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 7369;
  dbms_output.put_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 dept values(v_deptno,v_dname,v_loc);
  commit;
end;
update
declare
  v_deptno emp.deptno%type := 50;
  v_count number;
begin
  update emp set sal = sal/2 where deptno = v_deptno;
  dbms_output.put_line(sql%rowcount || '条数据被影响');--sql表示刚刚执行的sql语句，rowcount表示被影响的语句条数
  commit;
end;

create
begin
       execute immediate 'create table T(name varchar2(20) default ''abc'')';
end;

--if 取出7369的薪水，如果小于1200，则输出low，如果小于2000则输出middle，否则high
declare
     v_sal emp.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 -- 注意：elsif 写法，不是 else if
          dbms_output.put_line('middle');
     else				-- else 后面没有then
          dbms_output.put_line('high');
      end if;		-- 有分号 注意
end;

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

declare
      j binary_integer := 1;
begin
      while j<=11 loop
            dbms_output.put_line(j);
            j := j+1;
       end loop;
end;

begin
       for k in 1..10 loop     -- 1-10循环输出
           dbms_output.put_line(k);
       end loop;
       
       for k in reverse 1..10 loop   -- 10-1循环输出
           dbms_output.put_line(k);
       end loop;
end;

declare
       cursor c is
              select * from emp;
       v_emp c%rowtype;
begin
       open c;
       fetch c into v_emp; --fetch 取得 的意思
       dbms_output.put_line(v_emp.ename);
       close c;
end;

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;

declare
       cursor c is 
              select * from emp;
       v_emp emp%Rowtype;
begin
       open c;
       fetch c into v_emp; --先fetch，否则c%found一直是false
       while(c%found) loop
         dbms_output.put_line(c%rowcount || v_emp.ename); --输出结果为 1XXX 2XXX 3XXX 4XXX...14XXX
         fetch c into v_emp; 
       end loop;
       close c;
end; 

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 emp for update; -- for update
begin
       for v_temp in c loop
           if(v_temp.sal<2000) then
             update emp set sal = sal*2 where current of c; --where条件注意 当前的
           elsif(v_temp.sal=5000) then	-- =用于判断，既不是‘：=’，也不是‘==’
             delete from emp where current of c;
           end if;
       end loop;
       commit; -- 注意提交
end;

create or replace procedure p is
       cursor c is
              select * from emp for update;
begin
       for v_emp in c loop
           if(v_emp.deptno = 10) then
              update emp set sal = sal+10 where current of c;
            elsif(v_emp.deptno = 20) then
              update emp set sal = sal+20 where current of c;
            else
              update emp set sal = sal+30 where current of c;
            end if;
        end loop;
        commit;
end;

exec p;

begin
     p;
end;

create or replace procedure
       p(v_a in number,v_b number,v_ret out number,v_temp in out number)  -- in输入参数，out输出参数，不写默认输入
   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;

drop procedure p;

function
create or replace function sal_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.20;
       end if;
end;


select ename,sal_tax(sal) from emp;

create or replace trigger trig
       after insert or delete or update on emp for each row  --after表示操作之后，before表示之前；for each row表示每一行，可以不写就只会出发一次
begin
       if inserting then
          insert into emp_log values(USER,'insert',sysdate);
       elsif updating then
           insert into emp_log values(USER,'update',sysdate);
       elsif deleting then
           insert into emp_log values(USER,'delete',sysdate);
       end if;
end;

update dept set deptno=99 where dept=10;--这个操作是不能执行的，但是可以写一个触发器，这条语句就可以用了：
create or replace trigger trig after update on dept for each row
begin
       update emp set deptno = :NEW.deptno where deptno = :OLD.deptno; -- :NEW 新状态 :OLD 旧状态	
end;
update dept set deptno = 99 where deptno = 10;--可以执行

create table article
(
       id number primary key, --主键
       content varchar2(4000), -- 内容
       pid number, --父节点主键
       isleaf number(1), --冗余字段 便于开发 ：是否是叶子节点 0非叶子节点 1叶子节点
       alevel number(2) --冗余字段 便于开发  ：节点等级 ，根节点0，根节点的子节点1...以此类推
);