美文网首页Oracle数据库管理之道@IT·互联网码农的世界
oracle数据库——知识点总结(加示例)

oracle数据库——知识点总结(加示例)

作者: 雾里看花最是迷人 | 来源:发表于2017-05-02 18:59 被阅读355次

    新入oracle数据库,把目前学到的知识点记录下来,可能都比较基础,但还是比较全的,里面的示例都是自己在PL/SQL中跑过的,如果有错误,还望各位大侠指出哈。

    创建用户

    1.创建用户(使用管理员身份创建,即用system进行登录)

    create user rick identified by ric account unlock;
    --创建一个用户名是rick的用户,密码是ric,用户状态未锁定
    

    2.上诉创建后,如果直接用设置的用户名和密码进行登录,则会提示下面的错误


    没有权限.png

    3.可以理解为用户没有会话权限,无法进行数据库登录。则,接下来给予用户rick基本的connect权限

    grant connect to rick; --将connect权限给予rick
    

    4.这次rick可以作为normal用户登录(如果选择dba身份,则需要更高的权限)
    5.重新使用system登录数据库,更改rick的信息

    alter user rick account lock; --将rick的状态设置为锁定
    

    6.再次以rick来登录,则会提示下面错误(一开始在创建用户时如果不设置状态为unlock,则系统会默认为lock状态)


    locked.png

    创建表及约束

    create table infos
    (
      stuid varchar2(7) not null,
      age number(2) not null
    )
    alter table infos add constraint infos_check --给infos表加一个约束,限制age在20到60之间
    check(age>20 and age<60)
    

    根据结果集创建表

    create table infos2 as select * from infos;  --会把数据一起复制到新表中
    
    create table infos3 as select * from infos where 1=2;
     --后面加了一个where条件,这个条件不成立,则只会复制表结构,不包括数据
    

    插入数据时,如果表中字段是date类型,需要先使用to_date('2012-12-29','YYYY-MM-DD')将数据转换成date类型

    当两个表的结构全部相同时,可以直接插入结果集

    insert into infos3 select * from infos;
    

    清空表中的数据(不可恢复)

    turncate table infos;
    

    字符串拼接

    select (ename||' is a '||job) as "employee detail" from emp; 
    --括号内的内容会在一列中显示出来
    

    消除重复项

    select distinct deptno from emp;
    

    in操作查询指定列表的值

    select ename,job from emp where job in ('salesman','president','analyst');
    --只要job是salesman、president、analyst中任意一个即满足条件
    

    between……and操作查询范围内的数据

    select ename,job from emp where sal between 1000 and 2000; --包括边界
    

    like模糊查询

    • % 表示零个或多个任意字符
    • _ 表示一个任意字符
    • 如果查询条件中有特殊字符,则使用[ ]括起来

    集合运算

    • intersect (交集) 返回两个查询共有的记录
    • union all(并集) 返回各个查询的所有记录,包括重复记录
    • union(并集) 返回各个查询的所有记录,不包括重复记录
    • minus(补集) 返回第一个查询的记录减去第二个查询的记录后剩余的记录

    使用union实现多条数据插入

    insert into dept
    select 50,'公关部','武汉' from dual
    union
    select 60,'研发部','重庆' from dual
    union
    select 70,'培训部','四川' from dual
    

    其中dual是一个oracle自带的特殊的表,使用只是为了符合select的查询结构,它只会返回一行记录

    连接查询

    • 内连接:join……on 两个表中只有满足条件的才会被查询出来
    • 左外连接:left join ……on 左边的表不受限制,右边的表需要满足条件
    • 右外连接:right join……on 右边的表不受限制,左边的表需要满足条件
    • 全外连接:full join……on 左右两张表都不受限制

    ANY子查询(只要满足ANY中的一个即可)

    select ename,job from emp where sal<any(select sal from emp where job='salesman'); 
    --查询出比任意一个销售的工资低的员工姓名和工作
    

    ALL子查询(需要满足all中的所有)

    select ename,job from emp where sal>all(select sal from emp where job='salesman');
     --查询出比所有销售工资都高的员工的姓名和工作
    

    伪列

    • ROWID:表中每一行在数据文件中都有一个物理地址,ROWID就是返回这个物理地址
    • ROWNUM:表中每行数据前面的序号
    select t.* from (select ename,job sal from emp order by sal desc) t where rownum<5;
    --查出工资排行前四的员工信息
    
    select t.* from (select ename,job,rownum r from emp where rownum<10) t where r>5; 
    --查询第6到第9条数据
    
    数学函数.png

    日期函数

    • ADD_MONTHS(d,n) 在日期d的基础上加上n个月,返回新日期
    • LAST_DAY(d) 返回指定日期当月的最后一天
    • ROUND(d,fmt) 返回一个以fmt为格式的四舍五入的日期值
    • EXTRACT(fmt FROM d) 从日期d中提取fmt部分(YEAR、MONTH……)
    • TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') 按照右边的格式转换日期成字符串,HH24表示是采用24小时制
    • TO_DATE(x, 'YYYY-MM-DD') 按照右边的格式把字符串x转换成对应的date类型

    其他

    • NVL(x,value) 如果x为空,则返回value,否则正常返回x
    • NVL2(x,value1,value2) 如果x非空,返回value1,否则返回value2
    • SUM()统计某一列中的数据的和,COUNT()统计有多少条数据(即行)

    同义词

    create synonym myemp for scott.emp; --给scott用户的emp表创建别名myemp
    

    oracle可以为表、视图、序列、过程、函数、程序包等指定别名,比如上面的,当当前用户拥有权限时,访问emp表,可以直接访问myemp。而不需要使用scott.emp

    创建视图

    create view view_name as select ……;  --将查询到的结果保存成视图
    
    create view empdetail
    as
    select empno,ename,emp.deptnp,dname
    from emp join dept on emp.deptno=dept.deptno;
    

    上图是将emp表与dept表联合查询后的结果保存成视图,视图相当于一张新的表(虚表),可以直接来用

    创建索引

    create index ename_index on emp(ename);  --为emp表的ename创建索引
    

    创建表空间 (目前不是很懂表空间)

    create tablespace myspace
    datafile 'd:/a.ora' size 10M;
             'd:/b.ora' size 5M;
    extent management local  --指出表空间类型是:本地管理表空间
    uniform size 1M;  --指定每个分区的统一大小
    

    扩充表空间

    alter tablespace myspace
    add datafile 'd:/c.ora' size 10M;
    

    为表和索引指定表空间,只需要在其创建语句后面加上tablespace space_name即可,这种情况的表空间是不能修改的

    PL/SQL块

    declare
      --声明部分,如常量和变量
    begin
      --执行部分,这部分不能少
    exception
      --异常处理部分
    end;
    

    如下例子

    declare
       sname varchar2(20) default 'jerry'; --声明变量sname并设置初始值
    begin
       select ename into sname from emp where empno=9527; --将查询到的值赋给sname变量
       dbms_output.put_line(sname);  --输出sname
    end;
    

    还可以声明宿主变量,该变量属于全局变量,类似于web中的session,只要用户没退出,这个变量都可以使用

    var emp_name varchar(30);
    begin
    select ename into :emp_name from emp where empno=9527;
    end;
    

    !! 注意在使用这种宿主变量时,需要在变量名前加:

    属性数据类型

    • %ROWTYPE 引用表中的一行作为数据类型
    declare
       myemp emp%rowtype; --创建一个以emp表中一行作为数据类型的变量
    begin
       select * into myemp from emp where empno=9527; --赋值
       dbms_output.put_line(myemp.ename); --ename相当于一个对象,可以使用.来调用它里面的属性值
    end;
    
    • %TYPE 引用表中的某列的属性作为数据类型
    declare
       cursal emp.sal%TYPE; --引用emp表中的sal列的类型作为cursal的数据类型
       mysal number(4):=3000; --声明一个变量
       totalsal mysal%TYPE;  --引用变量mysal的数据类型作为当前变量的数据类型
    begin
       select sal into cursal from emp where empno=9527;
       total:=cursal+mysal;
       dbms_output.put_line(totalsal);
    end;
    

    IF ELSE语句

    declare
       newsal emp.sal%TYPE; --
    begin
       select sal into newsal from emp where ename='JAMES';
       if newsal>2000 then
          update emp set comm=800 where ename='JAMES';
       else
          update emp set comm=500 where ename='JAMES';
       end if;
    end;
    

    IF THEN ……ELSIF THEN

    declare
       newsal emp.sal%TYPE;
    begin
       select sal into new sal from emp where ename='JAMES';
       if newsal>1500 then
          update emp set comm=1000 where ename='JAMES';
       elsif newsal>1000 then
          update emp set comm=800 where ename='JAMES';
       else
          update emp set comm=500 where ename='JAMES';
       end if;
    end;
    

    !!! 注意是elsif , 不是elseif

    CASE语句

    declare
      v_grade char(1):=upper('&p_grade'); --&表示弹出输入框,接收用户的输入值
    begin
      case v_grade
         when 'A' then
            dbms_out.put_line('Excellent');
         when 'B' then
            dbms_out.put_line('Very Good');
         when 'C' then
            dbms_out.put_line('Good');
         else
            dbms_out.put_line('no such grade');
         end case;
    end;
    

    还可以把case的值赋给变量,如下

    declare
       v_grade char(1):=upper('&grade');
       p_grade varchar(20);
    begin
       p_grade:=
       case v_grade
           when 'A' then
               'Excellent'
           when 'B' then
               'Very Good'
           when 'C' then
               'Good'
           else 
               'no such grade'
       end case;
       dbms_output.put_line(v_grade);
    end;
    

    LOOP循环

    declare
       counter number(3):=0;
       sumResult number:=0;
    begin
       loop
         counter:=counter+1;
         sumResult:=sumResult+counter;
         if counter>=100 then
            exit;  --退出loop循环
         end if;
       end loop;
    end;
    

    WHILE循环

    declare
       counter number(3):=0;
       sunResult number:=0;
    begin
       while counter<100 loop
          counter:=counter+1;
          sunResult:=sumResult+counter;
       end loop;
    end;
    

    FOR循环

    declare 
       counter number(3):=0;
       sumResult number:=0;
    begin
       for counter in 1..100 loop  -- 1..100表示1到100这个范围
           sumResult:=sumResult+counter;
       end loop;
    end;
    

    GOTO语句

    declare
       sumsal emp.sal%TYPE;
    begin
       select sum(sal) into sumsal from emp;
       if sumsal>20000 then
          goto first_label;  --前往第一个标签处
       else
          goto second_label;  --前往第二个标签处
       end if;
       <<first_label>> --<<>>是goto可识别的标志
       dbms_output.put_line(sumsal);
       <<second_label>>
       null;  --无操作,语句接着往下执行
    end;
    

    动态SQL

    declare
       sql_stmt varchar2(200); --存放SQL语句
       emp_id number(4):=7566; --实参
       dept_id number(2):=90;  --实参
       dept_name varchar2(14):='PERSOLLNNEL'; --实参
       location varchar2(13):='DALLAS'; --实参
       emp_rec emp%ROWTYPE; --一行数据
    begin
       sql_stmt:='insert into dept values(:1,:2,:3)'; --里面的:1,:2,:3相当于形参
       execute immediate sql_stmt using dept_id,dept_name,location; --using后面的相当于实参
       sql_stmt:='select * from emp where empno=:id'; --:id相当于形参
       execute immediate sql_stmt into emp_rec using emp_id; --把执行sql_stmt的结果赋给emp_rec,using后面的相当于实参
    end;
    

    异常处理

    declare
       newsal emp.sal%TYPE;
    begin
       select sal into newsal from emp;
    exception
       when TOO_MANY_ROWS then  --TOO_MANY_ROWS是常用异常名
           dbms_output.put_line('返回的记录太多');
       when OTHERS then
           dbms_output.put_line('未知异常');
    end;
    

    自定义异常

    declare
       cursal emp.sal%TYPE;
       myexp exception; --定义一个名为myexp的异常
    begin
       select sal into cursal from emp where ename='JAMES';
       if cursal<5000 then
         raise myexp;  --raise用于引发myexp这个异常
       end if;
    exception
       when NO_DATA_FOUND then
         dbms_output.put_line('no resource found');
       when MYEXP then
         dbms_output.put_line('sal is less 5000');
    end; 
    

    创建存储过程

    create procedure my_procedure(eno in number,salary out number)
     --in表示是传入的参数,out表示输出的参数,这里声明的参数不写范围
    as
    begin
      select sal into salary from emp where empno=eno;
    end;
    

    执行上述存储过程

    declare
      my_salary number; --声明一个变量用于接收输出参数
      my_eno number:=7369; --作为传入的参数
    begin 
      my_procedure(7369,my_salary); --执行存储过程
      dbms_output.put_line(my_salary);
    end;
    

    创建触发器

    create trigger my_trigger 
    after insert or update on dept for each row 
    --after表示在操作之后触发,on dept表示这个触发器是绑定在dept表上的
    declare  --不需要变量的话,可以不要declare
    begin
      if inserting then  --如果执行的是插入操作,则执行下面语句
         dbms_output.put_line('已经插入');
      elsif updating then  --如果执行的是更新操作,则执行下面语句
         dbms_output.put_line('已经更新');
      end if;
    end;
    

    创建游标(游标可以对结果集进行一行行处理)

    • 隐式游标(不用创建,由系统自动控制开启和关闭)
    begin
       update emp set sal=sal+100 where empno=7876; --增删改查时系统会自动打开隐式游标
       if sql%found then  --如果有游标存在,就说明之前的操作成功
          dbms_output.put_line('修改成功');
       else 
          dbms_output.put_line('修改失败');
       end if;
    end;
    
    • 显示游标
    declare
       cursor emp_cursor is select * from emp; 
    --声明一个游标,这个游标时指向emp表的返回集(多行),可以理解为这个地方的游标是一个游标工厂
       my_cursor emp_cursor%rowtype;  --声明一个自己的游标,它指向的是一行数据
    begin
       for my_cursor in emp_cursor loop --循环,只要在游标工厂中还有自己的位置就继续
          dbms_output.put_line('job is '||my_cursor.job||' and salary is '||salary);
       end loop;
    ennd;
    

    相关文章

      网友评论

        本文标题:oracle数据库——知识点总结(加示例)

        本文链接:https://www.haomeiwen.com/subject/zkchtxtx.html