Oracle数据库整理-(末篇)

六.数据库中DML语句、DDL语句和事务控制

• DML语句，直接操作数据库对象 里面的数据

   insert into
   update
   delete
  

• 事务控制语句

   commit
   savepoint
   rollback
  

insert语句

向表中插入数据:
格式：【insert into 表名[(列名1,列名2,…)] values(对应值1，对应值2,…);】
1).默认是向表中的每一个列中【依次】插入数据

insert into t_user values(1,'tom','abc','f',20,'11-8月-98');

注意:违反任意一种约束那么就插入数据失败
2).也可以指明向表中的哪些列插入数据

insert into t_user(id,name,email,gender,age,birthday)  values(2,'tom','abc1','f',20,'11-8月-98');

注意:可以任意交换下面列名的位置,只要values语句中的值也对应交换即可
3).特殊情况
我们可以把【select语句查询结果】插入到表中！但是的有个【前提，就是查询列】的顺序和要插入表中列的【顺序一致】(数据类型)。另外查询结果值要【满足其他约束】条件。

insert into t_user(id,name,birthday) 
select id,last_name,start_date 
from s_emp;

update语句

格式： update 表名 set 列名=值[,列名=值,…] [where …]
例.修改表中id小于10数据的age和gender的值为null

update t_user set age=null,gender=null where id<10;

delete语句

格式：delete from 表名 [where …];
例:删除名字为张三的用户信息

delete from t_user where name='zhangsan';

例: 删除表中所有的数据

delete from t_user;

on delete语句

以上只是delete语句的普通情况
级联cascade针对表的删除， on delete针对表中列的删除
on delete no action【默认情况:什么不都写】
on delete cascade
on delete set null
在两张表有主外键关系的时候删除一张表中有子记录的数据时,删除不成功;
例:

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表;
像这样:

delete from t_customer where id = 1;

删除失败： ORA-02292: 【违反完整约束条件】 - 已找到子记录

不好的办法就是先把t_order表的中有关系的数据删除,才能删除t_customer表中的数据;
解决办式一: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
		);

删除主表中 id为1的数据(外表中使用了1这个值)
delete from t_customer where id = 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
		);

这样会删除主表数据的时候,同时把外表中的数据置为null

delete from t_customer where id = 1;

删除成功；
同时我们会发现,t_order表中所关联的那条数据的【外键值已经被设置为null】

数据库事务 DML

数据库操作过程中，如果操作有误，可以撤销相应操作(一系列);如果反复确定操作无误，可以使之生效。
commit提交事务;
rollback回滚事务;
savepoint设置回滚点;

事务的产生与结束：

• 【DML语句执行】的时候,如果当前有事务,那么就使用这个事务;如果当前没有事务,这个执行的【DML就会产生一个新的事务】；

   sqlplus test/test
   insert
   update
   delete
   create table ...;
  

• 只有DML语句才会产生事务,【其他语句不会产生事务】；

• commit/rollback/DDL语句都可以把当前事务给结束掉；

• 【commit和DDL语句】结束事务的方式是把这个【事务提交】；

• 【rollback】结束事务的方式是把这个【事务回滚】。

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

事务特征ACID:

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

• 一致性：Consistency 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

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

• 持久性：Durability 事务所做的影响 ，在事务结束之后应该能够是持久的。

isolation事务隔离级别

事务中会产生的问题:

• 脏读
  主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据
• 不可重复读
  主要针对update操作。一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。
• 幻读
  主要针对的是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

数据库DDL语句

create 创建表
drop 删除表
alter 修改表对象结构
truncate 截断
rename 重命名

例: 在表中添加一个新列

alter table t_user  add birthday date;

例: 删除表的某列

alter table t_user drop column birthday;

例: 给表中的列添加约束
这个约束相当于之前的表级约束

alter table t_user
add constraint user_name_un
unique(name);

例:修改表中某列的类型

alter table t_user
modify name varchar2(100) not null;

例: 让约束失效(必须知道约束的名字)

alter table t_user
disable constraint user_id_pk cascade; 

截断表中的数据
截取不需要提交,默认已经提交,并且不能回滚
truncate table t_user;
相当于(将表中数据全部删除，然后提交):
delete from t_user;
commit;
修改表的名字
rename t_user to mytest;
rename mytest to t_user;

七:数据库序列、索引和视图

序列

Sequence 序列
作用:帮用户自动生成主键列的值(非空唯一)
序列名.currval;
序列名.nextval;

创建序列

基本格式:
create sequence 序列名;

应用:

insert into t_user(id,name,salary)
values(seq_test.nextval,concat('tom',seq_test.currval),2000;

视图

视图概念

视图就是【提取一张或者多张表的数据】生成一个映射，操作视图可以达到【操作原表】的效果，【方便数据的管理】以及实现【更安全】操作。

视图的作用:

a.安全性 隐藏表中的重要数据
b.简单性 代替一些比较长的sql语句

视图的分类

简单视图:视图所代表的select语句中基于单个基表，且不包含函数和数据分组操作。
复杂视图: 视图所代表的select中如果【有group by语句】,或者【有组函数】,或者【查询的是多张表】,那么这样的视图就是复杂视图.
注意: 仅仅由多表查询的复杂视图，可以删除数据成功，但是【删除的是外表】 中的数据，主表不会受到影响。

简单视图和复杂视图的区别

通过【简单视图可以修改】原来表中的数据, 这些修改包括插入数据，更新数据和删除数据。
但是对于【复杂视图】来说, 通过视图修改数据必须满足一定的规则。
复杂视图是可以删除数据的，会影响到原表。 可以插入数据，但是有一定限制。

创建视图(【由select语句获得视图】)

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
	AS
	SELECT查询
	[WITH CHECK OPTION]
	[WITH READ ONLY];

• OR REPLACE：如果视图已经存在，则替换旧视图。
• FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使 用，当基表创建成功后，视图才能正常使用。
• NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
• WITH CHECK OPTION： 指出在视图上所进行的修改都要符合select_statement 所指定的限制条件.
• WITH READ ONLY：说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

删除视图

drop view 视图名字;

索引

索引的概念

• 类似书的目录结构

• Oracle 的"索引"是建立在表上的【可选对象】，能提高SQL查询语句的速度

• 索引直接指向包含所查询值的行的位置，减少磁盘I/O

• 【索引和表是相互独立】的物理结构,删除或创建都不影响表本身。

• Oracle 【自动使用并维护索引】，插入、删除、更新表后，自动更新索引

索引的创建

索引的创建分为两种情况，自动创建和用户创建。

• 自动创建
  当在表中指定了primary Key或者unique约束时会【自动创建唯一值索引】。
• 用户创建和删除
  用户可以创建非唯一值索引以【提高在访问数据时的效率】。
  创建索引：
  create index 索引名
  on 表名(列名);
  例：
  create index emp_index
  on s_emp(last_name);
  删除索引:
  drop index 索引名;
  例:
  drop index emp_index;
  创建成功后可以通过【数据字典】查看：
  select index_name from user_indexes;
• 3.给某列创建索引的原则
  索引的最大优势是 提高效率。
  a.列经常作为where子句的限定条件或者作为连接条件
  b.列包含的数据量很大，并且很多非空的值。
  c.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
  d.列总是作为搜索条件
  e.索引查出的数据量占总数据量的2%~4%
  f.索引不是越多越好，不是索引越多越能加速查找。
  g.要建立索引的表不经常进行修改操作
• 4.索引的种类
  Single column 单行索引
  Concatenated 多行索引
  Unique 唯一索引
  NonUnique 非唯一索引