oracle---DML语句

DML： Data Manipulation Language

      数据操纵语言，SQL的分类之一，命令使用户能够查询数据库以及操作已有数据库中的数据的计算机语言。此外还有DDL(Data Definition Language)数据定义语言和DCL(Data Control Language)数据控制语言。

• 主要内容:

        insert
        update
        delete
        ------------事务
        commit
        savepoint
        rollback

• sql语句的分类:

        select查询语句
        DML
        DDL
        DCL
        事务控制语句 TCL

    DML: insert  update  delete

• 测试使用的表: 没有主外键关联

create table t_user(
        id number,
        name varchar2(50) constraint user_name_nn not null,
        email varchar2(50),
        gender char(1),
        age number,
        birthday date,
        constraint user_id_pk primary key(id),
        constraint user_email_un unique(email),
        constraint user_gender_ck check(gender in('f','m'))
    );

//drop table t_user;      可删除表

• insert语句:

向表中插入数据:
        //默认是向表中的每一个列中【依次】插入数据
        insert into t_user values(1,'tom','abc','f',20,'11-8月-98');
        注:违反任意一种约束那么就插入数据失败

 //也可以指明向表中的哪些列插入数据
   //注意:可以任意交换下面列名的位置,只有values语句中的值也对应交换即可
        insert into t_user(id,name,email,gender,age,birthday) values(2,'tom','abc1','f',20,'11-8月-98');

   //列的值可以是null的话,那么也在插入的时候不指定这个列
   //注意:unique约束和check约束的值,都可以为null
    //注意:主键约束和非空约束的值,都不可以为null
        insert into t_user(id,name,email,gender) values(3,'tom','abc3','f');
        insert into t_user(id,name,email) values(4,'tom','abc4');
        insert into t_user(id,name) values(5,'tom');

//使用【运行时参数】设置需要输入表中的值
        insert into t_user(id,name) values(&id,'&name');
                       若没有单引号 则输入时手动添加单引号

 //把查询的结果 插入到表中
        //前提是查询的列的顺序和要插入表中列的顺序是一致的,这个一致指的的是【数据类型是一种的】
        insert into t_user(id,name,birthday) 
        select id,last_name,start_date 
        from s_emp
        where id>6;

• update语句

      //修改表中所有数据的age值为20岁
        update t_user set age=20;
        
        //修改表中所有数据的age和gender的值
        update t_user set age=25,gender='m';
        
        //修改表中id小于10数据的age和gender的值为null
        update t_user 
        set 
        age=null,gender=null 
        where id<10;
        
        //修改id为18的用户的名字为zhangsan
        update t_user set name='zhangsan' where id=18;
 

• delete语句

    delete from 表名 
    delete from 表名 where 条件
    
        //删除表中id大于20的用户信息
        delete from t_user where id>20;
        
        //删除名字为张三的用户信息
        delete from t_user where name='zhangsan';
        
        //删除表中所有的数据
        delete from t_user;
--------------------------------------------------------------------------

• 测试使用的表: 主外键关联

    create table t_customer(
            id number,
            name varchar2(20) constraint customer_name_nn not null,
            constraint customer_id_pk primary key(id)
    );

    create table t_order(
        id number,
        price number,
        customer_id number,
        constraint order_id_pk primary key(id),
        constraint order_cid_fk foreign key(customer_id) references t_customer(id)
    );

• insert语句:

//t_customer表中插入数据
        insert into t_customer(id,name) values(1,'tom1');
        insert into t_customer(id,name) values(2,'tom2');
        insert into t_customer(id,name) values(3,'tom3');

 //t_order表中插入数据
      //customer_id外键列的值必须是t_customer表中出现过的
        insert into t_order(id,price,customer_id) values(1,1000,1);
        insert into t_order(id,price,customer_id) values(2,2000,2);

        //插入出错,因为6这个值并没有在t_customer表中出现过的
        insert into t_order(id,price,customer_id) values(3,3000,6);

 //t_order表中插入数据
        //默认情况下,外键列上的值是可以为空的
        insert into t_order(id,price,customer_id) values(3,3000,null);
        insert into t_order(id,price) values(4,4000);
        注意:如果在外键列上加一个非空约束,那么这个外键列的值就不能为null了(可以给一个列上添加多种约束)
       

  //t_order表中插入数据
        //默认情况下,外键列上的值是可以重复的
        insert into t_order(id,price,customer_id) values(5,5000,1);
        insert into t_order(id,price,customer_id) values(6,6000,1);
        注意:如果在外键列上加一个唯一约束,那么这个外键列的值就不能重复了(可以给一个列上添加多种约束)

• update语句:

把两个测试表删除了重新创建,然后向表中插入一些数据
        //t_customer表中插入数据
        insert into t_customer(id,name) values(1,'tom1');
        insert into t_customer(id,name) values(2,'tom2');
        insert into t_customer(id,name) values(3,'tom3');
        //t_order表中插入数据
        insert into t_order(id,price,customer_id) values(1,1000,1);
        insert into t_order(id,price,customer_id) values(2,2000,2);
        
        //把t_order表中id=1的数据的customer_id列修改为3
        update t_order set customer_id = 3 where id = 1;

        //把t_order表中id=1的数据的customer_id列修改为null
        update t_order set customer_id = null where id = 1;
    
        //把t_order表中id=1的数据的customer_id列修改为20
        //sql执行出错,因为就没id=20的顾客
        update t_order set customer_id = 20 where id = 1;

• delete语句:

//删除t_order表中的的所有数据
        //可以成功删除,没有问题,因为删除t_order不会对t_costomer表的数据产生任何影响
        delete from t_order;
        
        //t_order表中插入数据
        insert into t_order(id,price,customer_id) values(1,1000,1);
        insert into t_order(id,price,customer_id) values(2,2000,2);

        
        //删除t_customer表中id=3的数据
        //删除成功,因为t_order表中外键列中没有引用过这个值
        delete from t_customer where id = 3;

        //删除t_customer表中id=1的数据
        //删除失败,因为t_order表中外键列中已经引用了这个值
        delete from t_customer where id = 1;

       【在这种情况下,on delete 语句就可以起作用了】

• 【on delete语句】

       on delete no action(默认情况:什么不都写)
        on delete cascade
        on delete set null    //引用的外键设为null

      on delete语句是在声明外键约束的时候使用的。用户在删除A表中的一条数据,而这条数据被B表中的外键列所引用了,这个时候on delete语句的设置可以告诉oracle这个时候该如何处理：      
        如果在建外键的时候,不加on delete语句,就是on delete no action.

例如1: on delete no action

        create table t_customer(
            id number,
            name varchar2(20) constraint customer_name_nn not null,
            constraint customer_id_pk primary key(id)
        );

    create table t_order(
            id number,
            price number,
            customer_id number,
            constraint order_id_pk primary key(id),
            constraint order_cid_fk foreign key(customer_id) references t_customer(id)
        );

    插入测试数据:
        //t_customer表中插入数据
        insert into t_customer(id,name) values(1,'tom1');
        insert into t_customer(id,name) values(2,'tom2');
        insert into t_customer(id,name) values(3,'tom3');
        //t_order表中插入数据
        insert into t_order(id,price,customer_id) values(1,1000,1);

//删除失败
        //ORA-02292: 违反完整约束条件 - 已找到子记录
        delete from t_customer where id = 1;

例如2: on delete cascade  级联删除

        建表语句和测试数据上例1相同,只是在【声明外键列】的时候加入on delete cascade语句
        create table t_order(
            id number,
            price number,
            customer_id number,
            constraint order_id_pk primary key(id),
            constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete cascade
        );

            
        //同样做删除测试
        //删除成功,同时级联(cascade)删除了t_order表中所关联的那条数据
        delete from t_customer where id = 1;

例如3: on delete set null
        

建表语句和测试数据上例1相同,只是在声明外键列的时候加入on delete set null语句
        create table t_order(
            id number,
            price number,
            customer_id number,
            constraint order_id_pk primary key(id),
            constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete set null
        );

            
        //同样做删除测试
        //删除成功,同时把t_order表中所关联的那条数据的外键设置为了null
        delete from t_customer where id = 1;

• 数据库事务

事务：  是对数据库中的数据的操作，需要在一个事务中进行，事务可以对DML语句提供保障。

        1.【DML】语句执行的时候,如果当前有事务,那么就使用这个事务,如果当前没有事务,这个执行的DML语句就会产生一个新的事务。
        2.【只有DML语句才会产生事务】,其他语句不会产生事务。
        3.commit/rollback/DDL语句都可以把当前事务给结束掉
        4.commit和DDL语句结束事务的方式是把这个事务给提交了
        5.rollback结束事务的方式是把这个事务给回滚了

注:
            【提交事务】是指让这个事务里面的所有操作都【生效】到数据库中
            【回滚事务】是指让这个事务里面的所有操作都【撤销】

• 测试用的表:

        create table t_customer(
            id number,
            name varchar2(20) constraint customer_name_nn not null,
            constraint customer_id_pk primary key(id)
        );
       // drop table t_customer;    （删除表）

        测试: 使用俩个终端窗口,同一个账号登录到数据库中,观察事务是否提交对用户查看数据的影响
        注:一个用户对A表做了DML操作,但是没有提交事务,这时候别的用户是不能对A表再做其他的DML操作。(为了保证数据的安全和一致性)

  例如1:
        insert ....产生事务A
        update ... 这个操作是事务A中的操作
        insert ..  这个操作是事务A中的操作
        commit;    让事务A里面的三个操作生效、事务A结束
        delete ... 产生新的事务B
        insert ..  这个操作是事务B中的操作
        insert ..  这个操作是事务B中的操作
        insert ..  这个操作是事务B中的操作
        rollback;  让事务B中的四个操作都撤销,事务B结束

例如2:
        insert ....产生事务A
        update ... 这个操作是事务A中的操作
        insert ..  这个操作是事务A中的操作
        DDL语句;   事务A会被提交,事务A结束
        rollback;  这时候回滚已经对事务A不起作用,因为事务A以及被提交了,当前已经没有事务了
        
        【注:create语句 drop语句 alter语句等都属于DDL语句， DDL自动提交事务；】

• 事务特征ACID：

       1、原子性：Atomicity
            同时成功或者同时失败
       2、 一致性：Consistency 
            事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
        3、隔离性：Isolation 
            事务操作应该相互独立
        4、持久性：Durability 
            事务所做的影响 ，在事务结束之后应该能够是持久的。

• isolation  事务隔离级别

事务中产生的问题:

        1.脏读  主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据

        2.不可重复读  主要针对update操作。 一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据两次,但是读到的结果是不同的。

        3.幻读  主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据,事务A第二次用同样的where条件筛选出的却是11条数据,因为事务B在事务A的第一次和第二次查询之间进行了插入操作,并且插入的这个数据满足事务A的where筛选条件.

• 事务隔离级别有:

        read-uncommitted  不提交也能读
        read-committed    提交之后才能读 解决了脏读
        repeatable-read   解决了脏读和不可重复读
        serializable      三个问题都解决了

级别越高解决的问题越多但是效率越低。

注意:并不是所有数据库都支持这四种事务隔离级别,【比如oracle就只支持第二种和第四种这俩种,比如mysql就四种全支持.】

        【oracle里面默认的事务隔离级别是第二种:read-committed】

        oralce里面设置事务隔离级别:
        Set Transaction Isolation Level Read Uncommitted
        Set Transaction Isolation Level Read Committed
        Set Transaction Isolation Level Read Repeatable
        Set Transaction Isolation Level Serializable

• 回滚点/保存点 savepoint

        例如:
        DML语句1
        savepoint A
        DML语句2
        savepoint B
        DML语句3
        rollback to A/B

        这个时候可以通过这个回滚点让事务回滚到指定的位置,如果不指定回滚点而是直接rollback,那么事务会一下子回滚完.

        【特别注意】:rollback到回滚点之后,这个事务并没结束,这个时候还可以接着回滚或者commit提交事务。

create table t_user(
            id number primary key,
            name varchar2(100),
            salary number
        );
   //     drop table t_user;    （删除表操作）

        例如:
        insert into t_user values(1,'tom',1000);
        savepoint A;
        insert into t_user(id,name) values(2,'zs');
        savepoint B;
        delete from t_user;
        rollback to B;

        然后查询看结果：
        select * from t_user;

loading....................