oracle数据库学习笔记

刺骨的言语ヽ痛彻心扉 2022-07-10 04:08 262阅读 0赞

查看表结构 ： 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()

![Image 1][]

to\_date()

![Image 1][]

to\_number()

![Image 1][]

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)

数据字典表：查看当前用户下有哪些表，都有什么视图等等之类的信息。  
user\_tables  
user\_views  
user\_constraints  
.....  
这些表的名字存在于 dictionary 表中。dictionary 中有table\_name 和 comments 2个字段。  
  
  
索引 index  
只是为了访问字段更加快捷，只是访问快。  
create index 索引名 on 表（字段）  
drop index 索引名  
查找索引:  
select \* from user\_indexes  
什么时候建立索引？  
不要轻易建立索引：影响其他操作的效率，并且会占用空间。  
  
  
视图view  
视图就是一个子查询。  
create view 视图名 as select .....  
查找视图  
select \* from user\_views  
视图可以简化查询，但是不便于维护。如果表结构发生变化，视图也需要修改。  
视图里面的数据是可以修改的，但是不建议在视图里面修改数据。  
  
  
序列sequence  
oracle 特有

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;

一旦定义了emp\_sequence，你就可以用CURRVAL，NEXTVAL  
CURRVAL=返回sequence的当前值  
NEXTVAL=增加sequence的值，然后返回sequence值  
  
  
2道经典面试题:  
1.有三个表s，c，sc  
S（sno，sname）代表（学号，姓名）  
C（cno，cname，cteacher）代表（课号，课名，教师）  
SC（sno，cno，scgrade）代表（学号，课号，成绩）  
问题：  
1.找出没有选过“黎明”老师的所有学生姓名（简单）  
2.列出2门以上（含2门）不及格学生姓名及平均成绩（group by...having..）  
3.即学过1号课程，也学过2号课程的所有学生姓名(学过1的学生 in （学过2的学生）)  
  
  
2.一个简单的表table ，包含100条以上的信息，其中包括：  
产品 颜色 数量  
产品1 红色  123  
产品1 蓝色  126  
产品2 蓝色  103  
产品2 红色  null  
产品2 红色  89  
产品1 红色  203  
..........  
请用sql完成以下问题：  
1.按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量  
2.按产品分类，将数据按照下列方式进行统计显示  
产品 红色蓝色  
select 产品,  
sum(case when 颜色 = '红色' then 数量 end) as 红色,  
sum(case when 颜色 = '蓝色' then 数量 end) as蓝色  
from table group by 产品  
  
  
数据库设计三范式：  
三范式虽然很有用，但是还是具体问题具体对待。  
1. 要有主键，列不可分  
2.当一个表含有复合主键時，非主键的数据不能依赖部分主键（必须全部依赖），即不能存在部分依赖  
3.属性不依赖于其他非主属性  
  
  
PL/SQL  oracle特有  
也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）  
PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束

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;

\--table变量类型 table表示的是一个数组，指定数组的类型必须重新定义一种数组类型

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;

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

使用%rowtype声明record变量

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;

SQL语句的运用

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;

在输出中文的时候，可能会出现乱码，这是工具的原因，在命令行中则不会出现这个问题，解决的办法：  
1.查看注册表：开始-运行-输入regedit-回车进入注册表，依次单击HKEY\_LOCAL\_MACHINE--->SOFTWARE ---> ORACLE--->KEY\_OraDb11g\_home1（不同版本的Oracle显示的都不太一样，但都会包含home这个单词），找到“NLS\_LANG”，查看数值数据是否为：“SIMPLIFIED CHINESE\_CHINA.ZHS16GBK”，如果不是就将它设置为“SIMPLIFIED CHINESE\_CHINA.ZHS16GBK。”我的就是这个。没有修改  
2.设置完注册表后，接下来设置我们的环境变量，计算机（右键） --->属性--->高级系统设置--->高级--->环境变量--->新建，个人建议新建用户变量，变量名输入：“NLS\_LANG”，变量值输入：“SIMPLIFIEDCHINESE\_CHINA.ZHS16GBK”。重启pl/sql developer，登录時可能会报错：ora-12705:cannot access NLS data ...什么的，需要把之前的乱码那条数据删掉就好了。

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

分支循环语句  
if

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

相当于java中的do-while循环，结果是 1 2 3 4...10

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

相当于java中的while循环，结果是1 2 3 4...11  
for循环

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;

游标（重点）  
oracle里面遍历数据，类似于java中的迭代器Iterator

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;

需要说明一下，游标属性：ISOPEN  FOUND/NOTFOUND ROWCOUNT

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;

最方便的遍历方法for循环:不用定义变量了，不用手动close了。这里的for循环类似于java的foreach

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;

存储过程procedure  
由于存在需要直接调用某个程序的需求，所以产生了存储过程。

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;

调用procedure的方式

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;

往往在创建存储过程之中，代码会有错误，但是oracle只是提示：警告：创建的过程带有编译错误，此时可以查看错误：

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...以此类推
    );

字段的说明只是个例子

[Image 1]: