DML语句(delete,insert,update)

DML语句

一、概述：DML （Data Manipulation Language 数据操作语言）

            insert 将记录插入到数据库 
            update 修改数据库的记录 
            delete 删除数据库的记录
            commit  提交数据
            savepoint  设置回滚点
            rollback   回滚
当执行DML命令如果没有提交，将不会被其他会话看到。除非在DML命令之后执行了DDL命令或DCL命令，或用户退出会话，或终止实例，此时系统会自动发出commit命令，使未提交的DML命令提交。     

二、    sql语句的分类:
        DQL: select查询语句
        DML:数据操作语言
        DDL：数据定义语言
        DCL：事务控制语句
三、对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; 
    3.1.1 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','abc31');
        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;

如上图示从s_emp表中查询出结果，然后插入到t_user表中

注意：在使用时避免主键重复问题         

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

   3.1.3 delete语句
        //删除表中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)
    );     

    drop table t_order;
    drop table t_customer;      
   3.2.1 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表中出现过的id值       

        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表中id列出现过的 

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

        注意:如果在外键列上加一个唯一约束,那么这个外键列的值就不能重复了(可以给一个列上添加多种约束)      
   3.2.2 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; 

   3.2.3 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语句
        on delete no action(默认情况:什么不都写)
        on delete cascade
        on delete set 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)
        );          

        drop table t_order;
        drop table t_customer; 
        插入测试数据:
        //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); 

        //如下面语句进行删除，则显示删除失败
        //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;