学习笔记day65-----oralce-事物控制语句、约束、其他数据库对象、分页

1、事物控制语句(TCL语句)
    1.1、事物控制语句的含义
        commit;     提交当前会话中所有未提交的操作。
        rollback;   回滚，撤销当前会话中所有未提交的操作。
        savepoint;  保存点;创建一个保存点
            rollback to 保存点;撤销保存点之后的未提交的操作
    1.2、各类sql语句和事务的关系
        select:     和事务无关;
        del、dcl;   隐式提交，这类语句中结尾带有一个隐式的commit;
        dml;        显示提交;
    1.3、事务的四大特性(ACID)
        1)事务的原子性(Atomicity)
            事务中的多条语句是一个不可分割的整体
            转帐:
                --  创建一个测试表  账户表
                    create table account(id number primary key,balance number(11,2) check(balance>0));
                --  插入两条测试数据
                    insert into account values(1,8500);
                    insert into account values(2,5000);
                --  转帐 1-->2 转帐 10000;
                    update account set balance=balance-10000 where id=1;
                    update account set balance=balance+10000 where id=2;
            在一个事务的所有语句要么一起成功，要么一起撤销;
        2)一致性(Consistency)
            事务执行的结果必须是数据库从一种一致性状态变成另一种一致性状态。
        3)隔离性(Isolation)
            一个事务中的语句对数据库中数据所作的修改，在提交之前对于其他的事务是不可见的。
        4)持久性(Durability)
            指的是一个事务一旦提交，对数据库中的数据的改变就是永久的。
    1.4、部分提交 部分撤销
        insert into account values(3,1000);
        savepoint a;
        insert into account values(4,2000);
        savepoint b;
        insert into account values(5,3000);
        select * from account;
        rollback b;
        commit;
        select*from account;
2、约束 constraint
    2.1、概念
        对数据库表中的字段添加的限制
            目的:确保数据的完整性
                实体完整性:表中不能有重复的行
                域完整性:表中的字段应该符合的格式、范围等
                引用完整性:表之间的关系
                自定义完整性:根据业务要求定义
    2.2、约束的类型
        1)  主键约束 primary key
            唯一、非空
            一个表中只能有一个主键
        2)  唯一约束    unique
            唯一、允许为NULL
            一个表中可以有多个字段都唯一
        3)  非空约束    not null
            不允许为null
        4)  检查约束 check(表达式)
            要求字段的值必须符合表达式的要求
        5)  外键约束    foreign key 、references
    2.3、约束的实现方式
        1)列级实现
            定义完一个字段后，直接在后面添加的约束
        2)表级实现
            在定义完表中的所有字段后，逗号隔开，添加约束
    2.4、主键约束
        系统自动对约束命名
        2.4.1、列级
            --  创建测试表
                create table testcons_lx_42(id number(7) primary key,name varchar2(20));
            --  插入测试数据
                insert into testcons_lx_42 values(1,'test1');
                //错误，主键字段的值不能重复
                insert into testcons_lx_42 values(1,'test2');
                //错误，主键字段的值不能为null
                insert into testcons_lx_42 values(null,'test3');
            --  用户为约束命名
                语法:   
                    constraint 约束名 约束规则   constraint testcons_lx_42_id_pk primary key;
                    约束名:表名_字段名_约束类型
                create table testcons_lx_42(id number(7) constraint testcons_lx_42_id_pk primary key,name varchar2(20));
        2.4.2、表级
            目的:为了应对一个约束中设计多个字段的情况，称为组合键(符合键)。
            create table testcons_lx_42(id number(7),userid number(7),name varchar2(20),constraint testcons_lx_42_id_pk primary key(id,userid));
            --注意:实际开发中不建议使用组合主键
        2.5、唯一、非空、检查约束
           2.5.1、列级实现
            create table testcons_lx_42(
                    id number(7) primary key,
                    userid varchar2(18) unique,
                    name varchar2(20) not null,
                    salary number(11,2) check(salary>7000)
                    );
            1)  唯一
                --不允许重复
                    insert into testcons_lx_42 values(1,'1','test1',8000);
                    //错误，重复
                    insert into testcons_lx_42 values(2,'1','test2',8000);
                --允许为null
                    insert into testcons_lx_42 values(3,null,'test3',8000);
                --null值重复(并不是sql标准，是oralce里的)
                    insert into testcons_lx_42 values(4,null,'test3',8000);
            2)  非空
                --不允许为NULL
            3)  检查约束
                --字段值必须是表达式为true
                    //错误，检查约束不满足
                    insert into testcons_lx_42 values(5,null,'test3',6000);
            2.5.2、表级实现
                drop table testcons_lx_42;
                /*一个表中，可以有多个唯一约束*/
                cleate table testcons_lx_42(
                            id number(7),
                            userid number(7),
                            name varchar2(20) not null,
                            salary number(11,2)，
                            primary key(id),
                            unique(userid),
                            check(salary>7000)
                        );
            2.5.3、在业务层面上，没有多个字段联合非空的需求，所以oracle没有提供非空约束的表级实现。
        2.6、外键约束
            2.6.1、概念
                一个表中，某个字段的值，受限于另一个表中某个字段的值。
                主表(父表):字段值被引用的表
                从表(子表):引用主表中字段的值的表，定义外键约束的表，外键字段的值必须来自于主表中的唯一性字段，或者为null
            2.6.2、创建表、
                一般先创建主表，再创建从表，除非建表时不添加外键约束。
                注意:外键引用的主表中的字段必须是主键或唯一
                create table parent_lx_42(
                        id number(7) primary key,
                        name varchar2(20)
                        );
                create table child_lx_42(
                        c_id number(7),
                        c_name varchar2(20),
                        p_id number(7) references parent_lx_42(id)
                        );
            2.6.3、dml操作
                1)insert
                    insert into parent_lx_42 values(1,'Admin');
                    insert into parent_lx_42 values(2,'HR');
                    commit;
                    insert into child_lx_42 values(1,'test1',1);
                    insert into child_lx_42 values(2,'test2',2);
                    insert into child_lx_42 values(3,'test3',null);
                    commit;
                    //错误，子表引用的主键的值不存在
                    insert into child_lx_42 values(4,'test4',3);
                    integrity constraint (CSD1703.SYS_C0042374) violated - parent key not found
                2)update
                    //错误,改数据被子表引用
                    update parent_lx_42 set id=3 where id=1;
                    integrity constraint (CSD1703.SYS_C0042374) violated - child record found
                    //错误，引用的数据在主表中不存在
                    update child_lx_42 set p_id=3 where p_id is null;
                3)delete
                    delete from child_lx_42 wherer id=1;
            2.6.4、删除表
                先删除从表，再删除主表
                drop table 表名 cascade constraints; 先删外键约束，再删表
                drop table child_lx_42;
                drop table parent_lx_42;


                user_constraints 数据库维护了一张记录了每张表的约束的表
            2.6.5、级联删除和级联置空
                on delete cascade   --级联删除
                on delete set null  --级联置空
3、其他的数据库和分页
    3.1、其他的数据库对象
        3.1.1、序列 sequence
            1)作用
                用来产生主键的值
            2)创建序列
                语法：
                    create sequence 序列名;
            3)使用
                序列名.nextval 获取下一个序列值
                序列名.currval 获取当前序列值
                -- 创建表
                    create table student_lx_42(
                                stu_no number(7) primary key,
                                sname varchar2(20)
                            );
                -- 创建序列
                    create sequence student_no_lx_42;
                -- 使用
                    insert into student_lx_42 values(student_no_lx_42.nextval,'test'||student_no_lx_42.currval);
                -- 相关的数字字典
                    user_sequences
                    select 
            4)删除
                drop sequence 序列名; 
        3.1.2、索引 index
            1)作用
                提高查询的效率

                用dml操作的空间和时间 获取查询效率的提升

            2)系统自动在主键字段创建索引

            set timing on --显示语句执行的时间

            s_index表为例:
            --创建表
            create table s_index(
                        id number primary key,
                        name varchar2(20) not null
                    );
            --创建序列
            for i in 1..100000 loop
                insert into s_index values(sindex_id.nextval,'test'||sindex_id.currval);
                commit;
            end loop;

            3)手动添加索引
                语法:
                    create index 索引名 on 表名(字段);
                    create index emp_name_in on emp_lx_42(first_name);
                    相关的数字字典 user_indexes

            4)删除索引
                drop index 索引名;

            3.1.3 视图 view
                1) 概念
                    视图也被成为虚拟表，是一组数据的逻辑表示。
                    视图对应一条select语句，并起了一个名字，即视图名
                    视图本身不包含数据，它只包含映射到源表的一个查询语句，当源表数据变化时，视图数据也随之改变。
                2) 作用
                    简化复杂查询
                    限制数据访问
                3) 创建视图
                    --语法
                        create[or replace] view 视图名[(别名列表)]
                        as
                            select 语句
                            [with read only]
                            [with check option];
                    --普通视图
                        create or replace view vw_emp_lx_42
                        as 
                        select id,last_name name,salary from emp_lx_42
                        where salary<1500;
                    --只读视图
                        create or replace view vw_emp_lx_42
                        as
                        select id,last_name name,salary from emp_lx_42
                        where salary<1500
                        with read only;
                    数字字典:user_views
            3.2 分页
                oracle      rownum(伪列)        
                            为结果集编行号
                mysql       limit
                sql server  top

                为s_emp进行分页，每页显示10行
                1)  显示第一页
                    select rownum,id,first_name,salary from s_emp where rownum<11;
                2)  显示第二页
                    select * from (select rownum rn,id,first_name,salary from s_emp) where rn between 11 and 21;
                3)  先排序再分页
                    select * from 
                    (select rownum rn,id,first_name,salary from (select id,first_name,salary from s_emp order by salary desc)) 
                    where rn between 11 and 21;