DML数据操作语句

DML语句

    主要内容:

        insert

        update

        delete

    sql语句的分类:

        数据查询语句（DQL）：SELECT

        数据操作语句（DML）： INSERT, UPDATE, DELETE (增删改) 需要commit

        数据定义语句（DDL）： CREATE, ALTER, DROP, RENAME, TRUNCATE

        数据控制语句（DCL）： GRANT, REVOKE

        事务控制语句（DTL）： COMMIT（事务提交）, ROLLBACK（事务回滚）, SAVEPOINT（设置回滚点）

    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;

        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','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;

        

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

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

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

            注意:如果在外键列上加一个唯一约束,那么这个外键列的值就不能重复了(可以给一个列上添加多种约束)

        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.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 语句就可以起作用了】

            drop table t_customer cascade constraints;

                以上的drop语句是直接将t_customer表删除，同时级联删除了引用了t_customer中列的外键，但是如果在删除数据时

            遇到由于主外键联系而无法删除的情况时：on delete 就应运而生了。但是注意：使用 on delete 不仅会删除被引用的数

            据，同时也会删除引用的数据。

        4.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;

        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;