oracle--day5（DML（insert update delete 级联删除)，事务，隔离，修改表和约束(alter语句)，Sequence 序列，视图view（虚表），索引）

本文链接：https://blog.csdn.net/qq_41533897/article/details/100748639

第九章：DML语句主要内容: insert update delete ------------ 事务 commit savepoint rollback sql语句的分类: select查询语句 DML DDL ...

摘要由CSDN通过智能技术生成

第九章：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');
       如果这里的'&name'没加'',那么在输入tom时需要加'':'tom'

       //把查询的结果插入到表中
       //前提是查询的列的顺序和要插入表中列的顺序是一致的,这个一致指的的是数据类型是一种的
       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_customer表的数据产生任何影响
       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//如果被其他表引用，就把其他表中的外键的那一列的值都设置为空，然后把该表删除

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

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;

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

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

第十章: 修改表和约束(alter语句)

   测试的表:
   create table t_user(
           id number constraint user_id_pk primary key,
           name varchar2(100),
           salary number
   );
   drop table t_user;

   //在表中添加一个新的列
   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);

   //测试刚添加的唯一约束是否生效
   insert into t_user(id,name) values(1,'zs');
   insert into t_user(id,name) values(2,'zs');

   //删除表中的约束
   alter table t_user
   drop constraint user_name_un;

   //修改表的名字:
   rename t_user to mytest;
   rename mytest to t_user;

   //修改表中某列的类型
   alter table t_user
   modify (name varchar2(500));

   //让约束失效:必须知道约束的名字
   alter table t_user
   disable constraint user_id_pk cascade;

   //测试是否设置成功
   insert into t_user(id,name) values(1,'zs1');
   insert into t_user(id,name) values(1,'zs2');

   //让失效的约束再次生效
   alter table t_user
   enable constraint user_id_pk;

   //截断表中的数据(删除),不需要提交,默认已经提交,并且不能回滚
   truncate table t_user;
   相当于:
   delete from t_user;
   commit;

   //给表添加注释
   comment on table t_user is '很好';
   //给列添加注释
   comment on column t_user.name is 'good';
   //查看表中注释
   select * from user_tab_comments where table_name=upper('t_user');//因为数据库中的所有命令表名默认是大写，可以用小写输入，数据库会默认转换为大写
   //查看列中的注释
   select * from user_col_comments
   where
   comments is not null
   and
   table_name=upper('t_user');

第十一章: 序列
Sequence 序列
作用:帮我们生成主键列的值(特点:非空唯一)

   创建序列:
   一般不需要设置sequence的属性,使用默认的方式去创建就可以了.
   create sequence 序列名;

   如果需要设置属性,那么就加上下面的语句.
   [INCREMENT BY n] 每次拿出值加多少
   [START WITH n]    初始值从几开始
   [{MAXVALUE n | NOMAXVALUE}] 最大值
   [{MINVALUE n | NOMINVALUE}] 最小值
   [{CYCLE | NOCYCLE}] 到了最大值后是否循环(如果循环会从1开始)
   [{CACHE n | NOCACHE}] 每次在缓存里面放多少个值.

   例如:创建序列并设置属性
   create sequence seq_test
   increment by 2
   start with 45
   maxvalue 60
   cycle
   nocache;

drop sequence seq_test;

例如:创建序列使用默认属性
create sequence seq_test;

   对应序列,我们只有俩种操作:
       1.获得序列中的下一个值
           //这个值对于当前这个序列来的其他值来说,肯定是非空唯一
           select seq_test.nextval
           from dual;

       2.查询序列中当前的值是多少
           select seq_test.currval
           from dual;//得先有值才能看当前值，也就是现有.nextval

   向t_user表插入数据,其中id值可以需要生成
       create table t_user(
           id number constraint user_id_pk primary key,
           name varchar2(100),
           salary number
       );
       drop table t_user;

   //创建序列
   drop sequence t_user_seq;
   create sequence t_user_seq;

   //插入数据使用序列产生id值
   insert into t_user(id,name,salary) values(t_user_seq.nextval,'tom',2000);

   通过数据字典查询当前用户的序列
       select sequence_name
       from user_sequences;

select table_name
from user_tables;

第十二章: 视图view（虚表）
视图就是提取一张或者多张表的数据生成一个映射，操作视图可以达到操作原表的效果，方便数据的管理以及安全操作。

   视图的作用:
       1.隐藏表中的重要数据
       2.代替一些比较长的sql语句

   视图分为俩类: 视图的本质就是一条sql语句
       简单视图:
           视图所代表的sql中如果没有group by语句,没有组函数,查询的只有一张表,那么这样的视图就是简单视图.
       复杂视图：
           视图所代表的sql中如果有group by语句,或者有组函数,或者查询的是多张表,那么这样的视图就是复杂视图.

简单视图和复杂视图的区别:
通过简单视图可以修改原来表中的数据,通过复杂视图是不能修改原来的数据的。

   创建视图：
       create or replace view 视图名字
       as
       sql语句

   删除视图
       drop view 视图名字;

   测试表:
   create table t_user(
       id number constraint user_id_pk primary key,
       name varchar2(100),
       salary number
   );
   drop table t_user;

   插入数据:
   insert into t_user(id,name,salary)
   select id,last_name,salary
   from s_emp;

   //创建视图
   create or replace view v_test
   as
   select *
   from t_user
   where id > 10;

   //查看视图内容
   select *
   from v_test;

//可以通过*简单视图*对原来的表进行数据的删除/更新/插入
delete from v_test where id=16;

update v_test set name = 'zhangsan' where id = 20;

insert into v_test(id,name,salary) values(28,'tom1',3000);

   with read only语句
       特点:只能通过视图进行查询数据,不能修改
       例如:
       create or replace view v_test
       as
       select *
       from t_user
       where id > 10
       with read only;

       这个视图v_test将来只能查询,不能进行修改

       此时再使用上面的DML进行操作数据就会报错。

with check option语句
特点:通过视图进行的修改 那么也必须可以通过这个视图能够显示出来,否则就操作失败

   例如:
       //测试用的表及其数据
       drop table t_user;
       create table t_user(
           id number constraint user_id_pk primary key,
           name varchar2(100),
           salary number
       );

       insert into t_user values(1,'tom1',1000);
       insert into t_user values(2,'tom2',2000);


       创建视图:
       create or replace view v_test
       as
       select id,name,salary
       from t_user
       where id=2
       with check option;

//查询视图中的数据
select * from v_test;

//插入报错因为这个操作通过视图显示不出来
insert into v_test values(3,'tom3',3000);

       //更新失败因为这个操作通过视图显示不出来
       update v_test
       set name='lily'
       where id=1;

       //更新成功因为这个操作通过视图可以显示出来
       update v_test
       set name='lily'
       where id=2;

   复杂视图
       例如:
           create or replace view v_test
           as
           select avg(salary) avgSal
           from t_user;

       复杂视图只能用来查询,不能修改

       select * from v_test;

update v_test
set avgSal=2019;

第十三章: 索引(index)
   索引的概念：
       1. 类似书的目录结构
       2. Oracle 的"索引"是一种对象，是与表关联的可选对象，能提高SQL查询语句的速度
       3. 索引直接指向包含所查询值的行的位置，减少磁盘I/O
       4. 索引和表是相互独立的物理结构
       5. Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

索引的原理：
例如：一个表中有name字段，假设要查找name='tom'的这条数据，可能会有多条

若没有索引，查找这个记录时，需要搜索表中所有的记录，因为不能保证只有一个tom，那么就必须将表中数据全部搜索一遍

若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值在什么位置按照一定的规则进行排列，然后构建索引条目，存储到索引段中，查询name为tom时，可以直接查找该数据所在的对应地方。

创建了索引并不一定就会使用，因为oracle在自动统计表的信息后，会决定是否使用索引，表中数据很少时，使用全表扫描速度已经很快了，那么就没有必要使用索引了。

   索引的创建：
       1.自动创建
           当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
       2.用户创建。
       用户可以创建非唯一值索引以提高在访问数据时的效率。
       语法：
           create index 索引名
           on 表名(列名);
       例如：
           create index emp_index
           on s_emp(last_name);

       删除索引:
           drop index 索引名;
       例如:
           drop index emp_index;

创建成功后可以通过如下语句查看：
select index_name from user_indexes;

   给某列创建索引的原则：
       1.列经常作为where子句的限定条件或者作为连接条件
       2.列包含的数据量很大，并且很多非空的值。
       3.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
       4.列总是作为搜索条件
       5.索引查出的数据量占2%~4%
       6.索引不是越多越好，不是索引越多越能加速查找。
       7.要索引的表不经常进行修改操作

   注意:
       1.在表中的某一个合适的列加入上了索引,那么也只有在数据量很大的时候,才能有所体现出这个查询的效率.
       2.索引一旦建立成功,那么之后这个索引就由系统来管理,我们自己是控制不了的.