第三章 : 事务、约束、主键、视图、序列、索引



- -dml语句
- - 3、 delete：删除表中满足 where条件的已有的行

- -删除 300号部门
delete from departments
   where department_id = 300;

- -不带 where条件的 delete会删除表中所有的行。
- -这叫做清空一张表
delete from copy_emp;

rollback;
- - delete语句中也可以带子查询

- -删除公关部的所有雇员
delete from copy_emp
   where department_id =( select department_id
                          from departments
                          where department_name like '%Public%');


- -清空一个表更有效的方法是使用 TRUNCATE语句。
- - TRUNCATE语句比 DELETE语句快，原因是：
- - TRUNCATE语句是DDL语句，不生成回滚信息。
- -截断一个表不会触发表的删除触发器

- -注意：截断表操作不能回滚

- -如果一个表是一个引用完整性约束的父表，就不能截断该表。

- - table COPY_EMP已截断。
truncate table copy_emp;

- -数据库事务（ transaction）
- -数据库事务有一条或者多条dml语句构成，它们形成一个逻辑的
- -工作单元，这些语句要么全部执行成功，要么全部执行失败。

- -数据库事务被用来保证数据的完整性和一致性。

- -在Oracle中，除了dml事务，还有ddl事务和dcl事务。
- -后面两种事务都由一条语句构成，并且是自动提交的，用户
- -不能控制。dml事务不是自动提交的，需要用户发出 commit
- -或者 rollback语句来结束。

- -Oracle中的事务是自动开始的：当第一条dml语句执行时，
- -事务自动开始。没有专门开始事务的语句。

- -Oracle中控制事务的语句有 4条：
- - commit：成功提交并结束事务
- - rollback：回滚并结束事务，撤销事务所做的所有工作
- - savepoint 保存点：在事务中定义一个保存点（实际上就是打一个标签），
- -以供在后面使用 rollback to命令来撤销事务所做的部分工作
- - rollback to 保存点：撤销事务所做的部分工作，但是并不结束事务

- -创建示例表
create table transaction_demo(
  id number,
   name varchar2( 10)
);

- -提交现在活动的事务
commit;

- -以下操作自动在一个新的事务中运行
insert into transaction_demo(id, name)
   values( 10, '张三');

insert into transaction_demo(id, name)
   values( 20, '李四');

- -定义保存点a
savepoint a;

- -将李四改为王五
update transaction_demo
   set name = '王五'
   where id = 20;

- -定义保存点b
savepoint b;  

delete from transaction_demo
   where id = 10;

- -以下决定如何结束前面的事务
- -撤销事务做的删除操作
rollback to b;

- -撤销事务做的 update操作
rollback to a ;

- -撤销事务做的所有操作
rollback;


- -在一个事务中所做的工作，都是在内存中完成的。只有当
- -事务提交之后，所有修改才写到磁盘文件中。

- - 1）在一个事务提交之前，其它用户（事务）不能查看到当前事务
- -对表所做的修改。
insert into transaction_demo(id, name)
   values( 10, '张三');

insert into transaction_demo(id, name)
   values( 20, '李四');

- -现在转到sqlplus中开一个新的会话，执行查询：
- -看不到任何行
select * from transaction_demo

- -提交事务
commit;

- - - -现在再转到sqlplus中执行查询：
- -可以看到 2行
select * from transaction_demo


- - 2）当前事务对表做了修改操作，如果其它事务也试图来修改同样的
- -数据，它会被阻塞。 
update transaction_demo
   set name = '王五'
   where id = 20;

- - - - - -现在转到sqlplus中执行同样的查询，会被阻塞： 
update transaction_demo
   set name = '王五'
   where id = 20;

- -提交后，sqlplus会话退出阻塞状态 
commit;

- -在 select语句中，可以使用 for update子句对
- -表中的行手工的加锁，以阻止其他用户修改这些行。

- -将所有的销售人员加锁
select last_name,salary
   from employees
   where job_id = 'SA_REP'
   for update;

- -此时，在sqlplus中执行上面同样的语句，会被阻塞。
- -直到当前事务提交或者回滚
commit;  

- -使用nowait选项，可以在得不到锁时立即退出，不被阻塞
select last_name,salary
   from employees
   where job_id = 'SA_REP'
   for update nowait;

- - - -此时，在sqlplus中执行上面同样的语句，不会被阻塞。


- -使用wait n选项，可以在得不到锁时等待指定的时间。超时后立即退出，不被阻塞
- -ORA - 30006: 资源已被占用; 执行操作时出现 WAIT 超时
select last_name,salary
   from employees
   where job_id = 'SA_REP'
   for update wait 5;

- - FOR UPDATE OF column_name ：
- -这种语法专门针对连接查询的情况，用来指定只对某一张表的行
- -加锁，而不是把多个表里的行都加锁。
- - column_name就是想要加锁的表的列名。可以任选一个。

- -使用ddl语句创建和管理表

- -伪列：使用 desc命令查看表结构时看不见的列，但是
- -在 sql语句中又可以直接使用的列。他是oracle自动提供的

- -最常用的伪列：
- -rownum ：他是给查询结果集中的行一个唯一的编号。总是从 1
- -开始编号

- -rowid：它代表表中任意行的物理存储地址。通过rowid来检索行
- -是最快捷的方式

- -rownum用在 where条件中做比较时，只能使用 <、 < =、 =操作符，
- -不能使用 >操作符。

select rownum,department_name,manager_id
   from departments;

- -查询部门表的前 5条记录
select department_name,manager_id
   from departments
   where rownum < = 5;

- -使用等于比较只能写 = 1
select department_name,manager_id
   from departments
   where rownum = 1;

- -空结果集
select department_name,manager_id
   from departments
   where rownum > 10;

- -一个rowid值包含 4个部分：数据文件编号，数据块编号，
- -行在数据块中的偏移量、数据对象编号
select rowid,department_name,manager_id
   from departments  ;

- -使用dbms_rowid包中的函数可以从一个rowid值中得到
- -各个组成部分的值
select dbms_rowid.rowid_relative_fno(rowid),
       dbms_rowid.rowid_block_number(rowid)
   from departments
   where rownum = 1;

- -创建一张表，就是指定构成该表的各个列的信息。
- -每个列至少要指定列名、数据类型以及宽度。可选的可以指定
- -默认值、约束等等。

- -创建一个dept表
create table dept(
  deptno number( 2),
  dname varchar2( 14),
  loc varchar2( 13),
  created_date date default sysdate
);

- -确认表创建成功
desc dept;

- -给列定义的默认值，在执行 insert操作时可以用到：
- -当不给某个列明确赋值时，他将自动得到默认值或者 null值。
insert into dept(deptno,dname)
   values( 10, 'bgs');

- -定义列的约束
- -约束的主要作用是为了防止非法数据进入到表中。
- -在表这个级别强制实现一些业务规则。

- -约束是对象，他有名字。如果不给约束明确地命名，那么
- -Oracle服务器就使用格式SYS_Cn生成一个名字，
- -这里n是一个整数，因此约束名是唯一的。

- -所有的约束存储在数据字典中

- -可以在列或者表级别定义约束。

- - 5大约束：
- - 1、 not null：非空约束
- - 2、 unique：唯一性约束
- - 3、 primary key：主键约束
- - 4、 foreign key：外键约束
- - 5、 check：检查约束

- -记住：约束是对dml操作进行约束的。

- - 1、 not null：非空约束
- -具有非空约束的列，不能存放 null值
create table test_nn(
  id number not null,
   name varchar2( 10)
);

- -对dml操作的影响
insert into test_nn(id, name)
   values( 10, 'zs');

- - SQL 错误: ORA - 01400: 无法将 NULL 插入 ( "HR". "TEST_NN". "ID")
insert into test_nn(id, name)
   values( null, 'ls');

- - - - SQL 错误: ORA - 01400: 无法将 NULL 插入 ( "HR". "TEST_NN". "ID")
insert into test_nn( name)
   values( 'ls');

- - SQL 错误: ORA - 01407: 无法更新 ( "HR". "TEST_NN". "ID") 为 NULL  
update test_nn
   set id = null
   where id = 10;

- -对 delete操作没有影响
delete from test_nn;

- - 2、 unique：唯一性约束
- -具有唯一性约束的列不能有重复值。即任意两行在该列上的值都是
- -唯一的
create table test_uk(
  id number,
   name varchar2( 10),
   constraint test_uk_id_uk unique(id)
);

insert into test_uk(id, name)
   values( 10, 'zs');

- - SQL 错误: ORA - 00001: 违反唯一约束条件 (HR.TEST_UK_ID_UK)  
insert into test_uk(id, name)
   values( 10, 'ww');  

insert into test_uk(id, name)
   values( 20, 'ww');  

- - SQL 错误: ORA - 00001: 违反唯一约束条件 (HR.TEST_UK_ID_UK)  
update test_uk
   set id = 10
   where id = 20;


- - 3、 primary key：主键约束
- -主键用来唯一地标识标识表中的每一行。在 where条件中根据主键值来
- -搜索行，可以唯一的定位 1行。

- -具有主键约束的列
- - 1）不能有 null值
- - 2）不能有重复值
- -因此： primary key = not null + unique

- -主键列一般为数字型或者字符串型
create table test_pk(
  id number constraint test_pk_id_pk primary key,
   name varchar2( 20)
);

insert into test_pk(id, name)
   values( 10, 'zs');

- - SQL 错误: ORA - 00001: 违反唯一约束条件 (HR.TEST_PK_ID_PK)  
insert into test_pk(id, name)
   values( 10, 'ww');  

- - SQL 错误: ORA - 01400: 无法将 NULL 插入 ( "HR". "TEST_PK". "ID")
insert into test_pk(id, name)
   values( null, 'ww');

- - SQL 错误: ORA - 01407: 无法更新 ( "HR". "TEST_PK". "ID") 为 NULL
update test_pk
   set id = null
   where id = 10;

- -记住：通常不更新主键值。没有任何意义。  

- - 4、 foreign key：外键约束
- -又叫做参照完整性约束。具有外键约束的列，意味着
- -该列的取值不能随便给，它必须参照它所引用的主键或者唯一键
- -列的取值。

- -按照外键定义，它所引用的列必须是主键或者唯一键。
- -默认情况下，外键列的取值可以为 null

- -外键所引用的主键可以是其它表的，也可以是本表的。
- -如果引用的是本表的主键，这种情况叫做自引用。

- -子表和父表
- -外键所在的表叫做子表，外键引用的表叫父表。
- -子表和父表是相对而言的，一个表可能既是子表又是父表

- -修改dept表，添加主键约束
alter table dept
   add constraint dept_deptno_pk primary key(deptno);

create table test_fk(
  id number,
   name varchar2( 10),
  deptno number,
   constraint test_fk_deptno_fk foreign key(deptno) 
     references dept(deptno)
);

insert into test_fk(id, name,deptno)
   values( 10, 'zs', 10);

- - SQL 错误: ORA - 02291: 违反完整约束条件 (HR.TEST_FK_DEPTNO_FK) - 未找到父项关键字  
insert into test_fk(id, name,deptno)
   values( 20, 'ls', 20);  

- - - - SQL 错误: ORA - 02291: 违反完整约束条件 (HR.TEST_FK_DEPTNO_FK) - 未找到父项关键字  
update test_fk
   set deptno = 20
   where id = 10;

- -删除父表中的行
- - SQL 错误: ORA - 02292: 违反完整约束条件 (HR.TEST_FK_DEPTNO_FK) - 已找到子记录
delete from dept
   where deptno = 10;

insert into dept
   values( 30, '办公室', null, null);

- -可以删除。因为子表中没有依赖于该行的子记录  
delete from dept
   where deptno = 30;  

- -外键列可以为 null值
update test_fk
   set deptno = null
   where id = 10;

- -默认情况下，删除父表的行时，如果子表中有依赖于该行的
- -子记录存在，那么oracle会抛出异常，不准删除。
- -通过在定义外键约束时使用 on delete cascade（级联删除）选项，
- -在删除父表的行时，会将子表中依赖的子记录同时删除，Oracle
- -不再抛出异常。
drop table test_fk;

create table test_fk(
  id number,
   name varchar2( 10),
  deptno number,
   constraint test_fk_deptno_fk foreign key(deptno) 
     references dept(deptno) on delete cascade
);

insert into test_fk(id, name,deptno)
   values( 10, 'zs', 10);

- -删除父表中的行.此时观察子表，记录被删除
delete from dept
   where deptno = 10;

- -使用 on delete set null
insert into dept(deptno,dname)
   values( 10, '办公室');

drop table test_fk;

create table test_fk(
  id number,
   name varchar2( 10),
  deptno number,
   constraint test_fk_deptno_fk foreign key(deptno) 
     references dept(deptno) on delete set null
);

insert into test_fk(id, name,deptno)
   values( 10, 'zs', 10);

- -删除父表中的行.此时观察子表，子记录未被删除，
- -但是其外键列被设置为 null
delete from dept
   where deptno = 10;

- -带子查询的 create table语句：
- -这种语法就是使用子查询返回的结果集的结构来创建目标表
- -的结构。如果子查询还返回行，那么同时将这些行插入到目标表中

- -目标的列名可以由子查询的 select列表来决定。
- -如果子查询的 select中包含表达式，要给列别名

- -注意，这种语法只能将非空约束带入到目标表中，其它的
- -约束被忽略
create table dept80
   as   select employee_id,last_name,
           salary * 12 annsal,hire_date
         from employees
         where department_id = 80;

- -快速的复制一张表
create table copy_emp
   as select * from employees;

- -快速的创建一个空表（只要表结构不要数据）
create table dept801
   as   select employee_id,last_name,
           salary * 12 annsal,hire_date
         from employees
         where 1 = 2;

- -在 11g中，你可以指定 READ ONLY 将表置于只读模式。
- -可以进行一些维护操作
alter table dept80 read only;

- - SQL 错误: ORA - 12081: 不允许对表 "HR". "DEPT80" 进行更新操作
delete from dept80;

- -将表切换为读写状态（正常模式）
alter table dept80 read write;

- -删除表
drop table dept801;

- -创建使用视图（ view）
- -视图是一个逻辑表，它本身作为一个 select语句保存在数据字典中

- -使用视图，可以把表对客户隐藏起来，使得客户不能直接操作
- -表，从而保护表的数据安全；另外，使用视图可以使客户编写
- -查询大大简化。

- -视图主要用作查询。不是所有的视图都可以做dml操作，
- -有很多限制条件

- -创建视图，保存部门 80的雇员信息
create view empvu80
   as select employee_id,last_name,salary
        from employees
        where department_id = 80;

- -查看视图的结构
desc empvu80;

- -查询视图
select * from empvu80;

- -通过在视图定义的 select语句中使用列别名，就可以控制
- -视图的列名
create view salvu50
   as select employee_id id_number,last_name name,
              12 *salary annsal
        from employees
        where department_id = 50;

- -修改视图：带上 or replace关键字
create or replace view empvu80(
  id_number, name,sal,department_id
) as
   select employee_id ,first_name|| ' '||last_name ,
             salary,department_id
        from employees
        where department_id = 80;

- -创建一个复杂视图：使用了函数、 group by操作
- -创建视图，保存每个部门的名称、最小薪水、最大薪水和平均薪水
create or replace view dept_sum_vu(
   name,minsal,maxsal,avgsal
) as
   select d.department_name, min(e.salary),
          max(e.salary), avg(e.salary)
     from employees e
     join departments d
     on (e.department_id = d.department_id)
     group by d.department_name;

- -定义视图时带 with check option选项
- -：这是一个约束，要求对视图进行的dml操作的结果一定要满足
- -视图的 where条件的限制。如果不满足则抛出异常

create or replace view empvu20
as select *
     from employees
     where department_id = 20
     with check option constraint empvu20_ck;

- -向视图中插入一个部门 20 的雇员
insert into empvu20(employee_id,last_name,
   email,hire_date,job_id,department_id)
    values( 207, '张三', 'abc',sysdate, 'SA_REP', 20);

- -向视图中插入一个部门 50 的雇员
- - SQL 错误: ORA - 01402: 视图 WITH CHECK OPTION where 子句违规
insert into empvu20(employee_id,last_name,
   email,hire_date,job_id,department_id)
    values( 208, '李四', 'abc',sysdate, 'SA_REP', 50);

- -使用 with read only选项定义只读视图
create or replace view empvu10
as select *
     from employees
     where department_id = 10
     with read only ;

- - SQL 错误: ORA - 42399: 无法对只读视图执行 DML 操作
delete from empvu10;

- -删除视图
drop view empvu10;

- -创建使用序列（ sequence）对象
- -序列对象是一个独立的 schema对象，专门用来生成一系列
- -自动增长的整数。主要用来给表的主键列赋值。

- -创建一个序列，用来给部门编号主键字段赋值
create sequence dept_deptid_seq
   increment by 10
   start with 310
   maxvalue 99999
  nocycle
  nocache;

- -要访问序列值，使用序列对象.nextval的语法。
- -序列对象有两个伪列：
- -nextval：返回序列的下一个可用值
- -currval：返回序列的当前值。
- -第一次访问序列，必须使用nextval伪列

insert into departments(department_id,department_name)
   values(dept_deptid_seq.nextval, '公关部');

select dept_deptid_seq.currval
   from dual;

- -修改序列：不能改起始值。
alter sequence dept_deptid_seq
   increment by 10  
   maxvalue 99999
  nocycle
  nocache;

- -删除序列：
drop sequence dept_deptid_seq;


- -索引的创建和使用
- -索引用来改进 select语句的执行效率。
- -索引可以建在一个或者多个列上。建在多个列上的叫做复合索引。
- -建在一个列上的叫做单列索引。

- -索引可以自动或者手工创建：
- -当对列建立了主键或者唯一键约束时，oracle会自动创建唯一索引。
- -该唯一索引的名字和主键约束的名字完全一样。

- -除此之外的其它索引都是手工创建的。
- -索引创建好后，它的使用以及内容的维护都是由oracle自动进行的。

- -最常用的索引就是b树索引。它对小表和大表都合适。

- -现在，copy_emp表上没有任何索引，以下查询就会执行
- -全表扫描。按f10看查询计划
select last_name,salary
   from copy_emp
   where last_name = 'King';

- -在last_name列上建立索引
create index cemp_lname_idx
   on copy_emp(last_name);

- -此次查询走索引  
select last_name,salary
   from copy_emp
   where last_name = 'King';  

- -建立多列索引
- -如果在 where条件中经常以两个或者多个列为条件一起查询，
- -就可以考虑建多列索引

drop index cemp_lname_idx;

- -第一个索引列叫做前导列
create index cemp_name_idx
   on copy_emp(last_name,first_name);

- - where里出现前导列，可以使用复合索引
select last_name,salary
   from copy_emp
   where last_name = 'King'; 

select last_name,salary
   from copy_emp
   where first_name = 'King'; 

select last_name,salary
   from copy_emp
   where last_name = 'King' and first_name = 'King';

- -删除索引：
drop index cemp_name_idx;



- - 5、 check：检查约束
- -该约束定义一个条件（布尔表达式），列值必须满足该条件
create table test_ck(
   name varchar2( 10),
  salary number constraint test_ck_sal_ck check(salary > 0)
);

insert into test_ck( name,salary)
   values( 'zs', 1000);

- - SQL 错误: ORA - 02290: 违反检查约束条件 (HR.TEST_CK_SAL_CK)  
insert into test_ck( name,salary)
   values( 'ls', - 1000);  

- - - - SQL 错误: ORA - 02290: 违反检查约束条件 (HR.TEST_CK_SAL_CK)  
update test_ck
   set salary = - 500
   where name = 'zs';