Oracle-SQL

数据完整性约束
分类：1、域完整性约束（非空not null，检查check）
2、实体完整性约束（唯一unique，主键primary key）
3、参照完整性约束（外键foreign key）

约束类型：
主键约束（Primary Key）
唯一约束（Unique）
检查约束（Check）
非空约束（not null）
外键约束（Foreign Key）

三种完整性约束的区别：
域完整性约束：字段约束
实体完整性约束：行和行之间的约束
引用完整性约束：表和表之间的约束

约束从作用上分类，可以分成两大类：
表级约束：可以约束表中的任意一列或多列。可以定义除了Not Null以外的任何约束。
列级约束：只能约束其所在的某一列。可以定义任何约束。

命名规则推荐采用：约束类型_约束字段
非空约束 ———NN_表名_列名
唯一约束———-UK_表名_列名
主键约束———-PK_表名
外键约束———-FK_表名_列名
检查约束———-CK_表名_列名

主键约束是数据库中最重要的约束，一个表中只允许一个主键
非空约束只能在字段级定义
唯一性约束允许出现空值，且可以多个空值
检查约束可插入或修改记录
外键约束其值必须在另一表的主键或者唯一键中列出，作为主键的表称为主表，作为外键的关系为依赖表

添加约束：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明
删除约束：ALTER TABLE 表名 DROP CONSTRAINT 约束名
可增加或删除约束，但不能直接修改

序列是oracle专有的对象，它用来产生一个自动递增的数列
例：create sequence seq_empcopy_id start with 1 increment by 1;

使用序列

    select seq_empcopy_id.nextval from dual
    insert into empcopy  (empno,ename)
    values (seq_empcopy_id.nextval, ‘TEST’);

查看序列状态：select seq_empcopy_id.currval from dual
删除序列 ：drop sequence seq_empcopy_id;
索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
创建索引的两种情况：自动和手动
自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.
手动: 用户可以创建索引以加速查询
在一列或者多列上创建索引：CREATE INDEX 索引名称 ON table (column[, column]...);
通过DROP INDEX 命令删掉一个索引：DROP INDEX index;
删掉 UPPER_LAST_NAME_IDX 索引：DROP INDEX upper_last_name_idx;

视图是从若干基本表和其他视图构造出来的表
在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据
在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”

视图的作用：
1、可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
2、可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。
3、提供了对相同数据的不同显示

创建视图

CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];

查询视图：select * from viewname;不需要写完整的select查询语句
删除视图：Drop view viewname;删除视图不会导致数据丢失，因为视图是基于数据库表的一个查询

事务（Transaction）是一个操作序列
事务是为了保证数据库的完整性
在oracle中，没有事务开始的语句。一个Transaction起始于一条DM L (Insert、Update和Delete )语句，结束于以下的几种情况：
1、用户显式执行Commit语句提交操作或Rollback语句回退。
2、当执行DDL(Create、Alter、Drop)语句事务自动提交。
3、用户正常断开连接时，Transaction自动提交。
4、系统崩溃或断电时事务自动回退

Commit表示事务成功地结束
Rollback表示事务不成功的结束
一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销。
执行一个 DDL ，DCL语句或从 SQL*Plus正常退出，都会自动执行commit命令。
提交或回滚前数据的状态
1、以前的数据可恢复
2、当前的用户可以看到DML操作的结果
3、其他用户不能看到DML操作的结果
4、被操作的数据被锁住,其他用户不能修改这些数据
提交后数据的状态
1、数据的修改被永久写在数据库中.
2、数据以前的状态永久性丢失.
3、所有的用户都能看到操作后的结果.
4、记录锁被释放,其他用户可操作这些记录.
回滚后数据的状态
1、语句将放弃所有的数据修改
2、修改的数据被回退.
3、恢复数据以前的状态.
4、行级锁被释放.

rowid在记录创建时生成，而且是不变，直接指向硬件上的存储位置，用rowid直接访问是最快的只要记录没被搬动过，rowid是不变的

rownum是个伪列，查询的时候除非特别指定，否则不会显示，其主要的用处是控制查询返回的行数，只能使用：<,<=，当rownum和order by一起使用时，会首先选出符合rownum条件的记录，然后再进行排序，这会给我们的查询带来难度。
例：

    Select rowid,rownum,e.* from emp
    Select rowid,rownum,e.* from emp order by sal
    Select rowid,rownum,e.* from emp where rownum<=5

使用PL/SQL Developer导入/出数据
一、Oracle Export/ Oracle Import
1、扩展名dmp
2、使用的就是exp/imp命令
3、二进制文件，无法查看
4、可以跨平台，效率高、使用最广
二、SQL Inserts
1、扩展名sql
2、可使用记事本等查看，效率不如第一种
3、适合小数据量导入导出
4、不能导出blob、clob等字段
三、PL/SQL Developer
1、扩展名pde
2、PL/SQL Developer的自有文件格式，只能使用该软件来导入导出
3、很少使

三大范式：
第一范式的合理遵循需要根据系统的实际需求来定（独立最小个体）
eg：姓名和性别单独是两列，不能出现姓名和性别放在一列中的情况

第二范式在第一范式的基础上需要确保数据库表中的每一列都和主键相关，（行数据的合法性）。

第三范式在第二范式的基础上，确保数据库中的每一列数据都和主键直接相关，而不能间接相关，属性不依赖于其他非主属性（表与表之间的关系建立）

第四范式·先知是大规模分布式机器学习的全流程平台。平台面向需要人工智能能力的企业，集模型调研、模型预估及模型自学习服务于一身，覆盖人工智能业务提升闭环；从而帮助企业解决业务增长难题，打造以人工智能为核心驱动的新增长点。

范式的优点：结构合理，冗余较小，尽量避免插入删除修改异常
范式的缺点：性能降低，多表查询比单表查询速度慢

    Declare
        …declarations
    Begin
        …executeable code
    Exception
        …exception handler
    End;

Declare: 声明部分，可选，用来定义
使用的变量、常量、游标等

Begin: 是核心部分，执行各种对数据库
操作的语句

Exception:异常处理部分

输出：dbms_output.put_line();

行注释：–
块注释：/注释多行/

例：

declare
            score number :=95;
        begin
            if score<60 then
                dbms_output.put_line("不及格");
            elsif score<=70 then
                dbms_output.put_line("合格");
            elsif score<=80 then
                dbms_output.put_line("良好");
            elsif score<=90 then
                dbms_output.put_line("在努力一把");
            elsif score<=100 then
                dbms_output.put_line("太棒了");
            else
                dbms_output.put_line("你是分数是错误的");
                end if;
        end;

例：

declare
            a int :=10;
            b int :=1;
            j int;
    begin
            for j in 1..a loop
                b :=b*j;
            end loop;
            dbms_output.put_line(a||"的集成是"||b);
        end;

例：

declare
  a int :=10;
  b int :=1;
  begin
   loop
      b:=b*a;
      a:=a-1;
      exit when a=1;
      end loop;
      a:=10;
      dbms_output.put_line(a ||'的阶乘是'|| b);
end;

例：

    declare
        a int :=10;
        b int :=1;
        j int;
    begin
        while a>=1 loop
            b:=b*a;
            a:=a-1;
        end loop;
        a:=10;
        dbms_output.put_line(a ||'的阶乘是'|| b);
    end;

例：

    declare
        a varchar2(10) := 'A';
        b varchar2(20) := 'B';
    begin
        b := case a
            when 'A' then
                '优秀'
            when 'B' then
                '良好'
            else
                '找不到'
            end;
            dbms_output.put_line(b);
    end;

例：

    declare
        a int := 10;
        b int := 1;
    begin
        <<abc>>
        b := b * a;
        a := a - 1;
        if a >= 1 then
            goto abc;
        end if;
        a:=10;
        dbms_output.put_line(a || '的阶乘是' || b);
    end;

例：–九九乘法表

    declare
        a int := 10;
        b int := 1;
        i int;
        j int;
    begin
        for i in 1 .. a loop
            for j in 1 .. i loop
                b := i * j;
                dbms_output.put(j || '*' || i || '=' || b ||'     ');
            end loop;
        dbms_output.put_line('');
        end loop;
    end;

例：–三角形

    declare
      i int := 5;
      a int;
      b int;
      c int;
    begin
        for c in 1 .. i loop
            for a in 1 .. (i - c) loop
                dbms_output.put(' ');
            end loop;
            for b in 1 .. (2 * c - 1) loop
                dbms_output.put('*');
            end loop;
            dbms_output.put_line('');
        end loop;
    end;

例：–procedure

    create or replace procedure p1(a in integer, b in integer, c out integer) as j integer;
    begin
        c := 0; for j in a .. b loop c := c + j;
        end loop;
    end;
    --调用存储过程 
    declare
        c integer;
    begin
        p1(1, 100, c);
        dbms_output.put_line('c:' || c);
    end;

例：–编辑存储过程计算税后工资

    create procedure p2(a in out int) as
    begin
        if a <= 3500 then
        dbms_output.put_line('不用交税');
        elsif a <= 5000 then
            a := a - (a - 3500) * 3 / 100;
        elsif a <= 8000 then
            a := a - (a - 5000) * 10 / 100 - 105;
        elsif a <= 12500 then
            a := a - (a - 8000) * 20 / 100 - 555;
        end if;
    end;
    declare
        a int := 8888;
    begin
        p2(a);
        dbms_output.put_line(a);
    end;

例：

    create or replace function f1(a in emp.empno%type) return emp.ename%type
    as
        rname emp.ename%type;
    begin
        select ename into rname from emp where empno = a;
        return rname;
    end;
    declare
        rn emp.ename%type;
    begin
        rn := f1(7788);
        dbms_output.put_line(rn);
    end;

例：–trigger

    after insert on emp
    declare
        c int;
    begin
        select count(*) into c from emp;
        dbms_output.put_line('当前员工表中有' || c || '条数据');
        end;
    select * from emp;
    insert into emp values(999,'a','b',7902,sysdate,8,null,10);
    create table lz as select * from emp where 1=2;

例：–行级别

    create or replace trigger t2
        after delete on emp
        for each row
    declare
    begin
        insert into lz values(
            :old.empno,
            :old.ename,
            :old.job,
            :old.mgr,
            :old.hiredate,
            :old.sal,
            :old.comm,
            :old.deptno);
    end;

    delete from emp where empno=999;
    select * from lz;

    insert into emp values(999,'a','b',7902,sysdate,8,null,10);

例：–update trigger

    create trigger t3 after update on emp for each row
    declare
    begin
        dbms_output.put_line('更新前：'||:old.ename);
        dbms_output.put_line('更新后：'||:new.ename);
    end;
    update emp set ename='QQ' where ename='a';

存储过程简称过程：

    create [or replace] procedure 过程名
        [(参数1 [in|out|in out] 类型,
        参数2 [in|out|in out] 类型,
        参数n [in|out|in out] 类型
        )]
    is|as
        --过程体局部变量声明
    begin
        --过程体
    end;

创建函数的语法：

    create or replace function 函数名
    [(
        参数1 [in|out|in out] 类型,
        参数2 [in|out|in out] 类型,
        参数n [in|out|in out] 类型
    )]
    return 返回值类型
    is|as
        --函数体局部变量声明
    begin
        --函数体
    end;

过程与函数的区别：
1、过程用来完成一项任务，可以不返回值，也可以返回多个值
2、过程的调用是一条pl/sql语句；
3、函数包含return子句，用来返回一个单独的值
4、函数的调用可以在一个表达式中。

触发器类似过程和函数，在事件发生的时候被自动隐式触发，而且触发器不接受参数。

    create [or replace] trigger [模式.]触发器名
    { before | after | instead of}
    {   DML 触发事件
      | DDL触发事件   [ or DDL触发事件  ]……
      | database 事件 [ or database 事件]……  } 
    on { [模式.]表 | [模式.]视图| database }
    [ for each row [when 触发条件]]
    触发体

例：–打断触发器

    create or replace trigger t4
        after delete on dept
        for each row
    declare
        n int;
    begin
        select count(*) into n from emp where deptno =:old.deptno;
        if n > 0 then
            raise_application_error('-20000', :old.dname || '部门有人不能删除');
        end if;
    end;
    delete dept where deptno = 10;

instead of型触发器又称替代型触发器，只能建立在视图上。用于替换在基于多表的视图上进行的添加、修改或删除操作。
user_triggers：存放当前用户的所有触发器
all_triggers：存放当前用户可以访问的所有触发器
dba_triggers：存放数据库中的所有触发器