（五）DML语句

学习内容

• 主要内容:
  insert
  update
  delete
  ------------ 事务
  commit
  savepoint
  rollback

• sql语句的分类:
  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;

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;

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语句

 //删除表中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;

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

• 在声明外键约束的时候使用。
  用户在删除A表中的一条数据，而这条数据被B表中的外键列所引用了，这时on delete语句的设置可以告诉oracle这个时候该如何处理

on delete no action

建外键时不加on delete语句，默认情况：什么不都写。

		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);
insert into t_order(id,price,customer_id) values(2,2000,2);

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

on delete cascade

建表语句和测试数据上例相同,只是在声明外键列的时候加入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
		);

insert into t_order(id,price,customer_id) values(1,1000,1);
		insert into t_order(id,price,customer_id) values(2,2000,2);

• 删除

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

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

insert into t_order(id,price,customer_id) values(2,2000,2);

• 删除

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

数据库事务

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操作。(为了保证数据的安全和一致性)
可以使用以下语句：

insert into t_customer(id,name) values(1,'tom1');

commit;

delete from t_customer;

• 语句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语句

事务特征ACID：

• 原子性：Atomicity
  同时成功或者同时失败

• 一致性：Consistency
  事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
  事务操作成功后，数据库所处的状态和他的业务规则是一致的，即数据不会被破坏。如A账户转账100元到B账户，不管操作成功与否，A和B账户的存款总额是不变的。

• 隔离性：Isolation
  事务操作应该相互独立

• 持久性：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
		);

• rollback：

		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;

总结

今天是在菁英班的第九天，照样跟不上老师讲课，上课总是走神，然后敲代码一走神就跟不上，跟不上这节课就凉凉，好想念我的王老师。

---

虽然没什么好写的，但写总结的优良传统不能断。