oracle之回顾二
TCL
1. 事务(Transaction)
事务（Transaction）是一个操作序列。这些操作要么都做, 要么都不做, 是一个不可分割的工作单元, 是数据库环境中的最小工作单元。
1.1 事务的特性(ACID)
1.1.1 Atomicity(原子性)
原子性是指事务包含的所有操作要么全部成功, 要么全部失败回滚, 因此事务的操作如果成功就必须要完全应用到数据库, 如果操作失败则不能对数据库有任何影响.
1.1.2 Consistency(一致性)
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态, 也就是说一个事务执行之前和执行之后都必须处于一致性状态.
1.1.3 Isolation(隔离性)
隔离性是当多个用户并发访问数据库时, 比如操作同一张表时, 数据库为每一个用户开启的事务, 不能被其他事务的操作所干扰, 多个并发事务之间要相互隔离.
1.1.4 Durability(持久性)
持久性是指一个事务一旦被提交了, 那么对数据库中的数据的改变就是永久性的, 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作.
1.2 事务的提交和回滚
a) 提交, 在确保事务执行成功时, 应该将事务进行提交. 提交后, 数据被永久保存, 不能进行回滚.
|
commit; |
b) 回滚, 当事务执行出现故障时, 应该进行事务的回滚操作, 本次事务的所有操作将被还原, 保证数据库的一致性.
|
rollback; |
c)设置回滚点 savepoint
1.3设置回滚点
insert into emp values(2,'dsfs',3000);
savepoint A;
insert into emp values(3,'oldu',4000);
savepoint B;
delete from emp;
rollback to A;
commit;
事务类型
显式事务
需要我们手动的提交或者回滚
DML语言中的所有操作都是显示事务。
隐式事务
数据库自动提交不需要我们做任何处理，同时也不具备回滚性。
DDL、DCL语言都是隐式事务。
DDL
2.创建表
create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(2) );
2.1Oracle数据库中的表
用户表：
由用户创建和维护的表的集合。
包含用户信息。
数据字典：
由oracle服务器创建和维护的表的集合
包含数据库信息
2.1.1常见的数据字典表
查看本用户所拥有的表的名称
select table_name
from user_tables;
查看本用户所拥有的不同的对象类型
select distinct object_type
from user_objects;
查看本用户所拥有的表、视图、同义词、和序列
select * from
from user_catalog;
2.1.2.根据查询创建表
create table dept as select employee_id,last_name,salary,hire_date from employees e where e.department_id=80;
3.alter table 语句
alter table语句可以修改表的信息。
添加一个新列
修改一个已存在的列
删除一个列
3.1添加一个新的列
alter table table
add ( column datatype [default expr][,column datatype]....);
alter table dept add(salary number(8,2))
修改数据数据类型
alter table dept modify(dname varchar2(40));
修改默认值
alter table dept modify(salary number(8,2) default 1000);
alter table dept80 modify location_id not null;
修改列名
alter table emp rename column dname to name;
删除一个列
alter table table
drop colum [ziduan];
4修改名称(Rename)
修改表如下
rename dept80 to dept90;
5.截断表(truncate table)
特点：
1.直接将所有的数据删除，但是表结构是留着的。
2.在截断表时不能指定条件。
3.截断表是隐式事务。
truncate table dept90
6.删除表(drop table)
drop table dept90;
7.数据库中的约束
非空约束(NOT NULL)
唯一性约束(UNIQUE)
主键约束(PRIMARY KEY)
外键约束(FOREIGN KEY): 在列和引用表的一个列之间建立并且强制一个外键关系
用户自定义约束(CHECK)
7.1定义约束原则
创建一个约束：在创建表的同时，或者在创建表之后都可以定义约束。可以给约束起名字，但是约束名不能相同，必须是唯一的。如果没有为约束起名字，oracle服务器会将默认格式SYS_Cn产生一个名字，这里n是一个唯一的整数，所以约束名是唯一的。
8.定义Not NULL约束
8.1实例
create table dept80(
id number,
name varchar2(20) not null,
salary number constraint dept_80_notn not null);
)
9.定义UNIQUE唯一性约束
create table(
employee_id number(6),
last_name varchar2(25) not null,
email varchar2(25),
salary number(8,2),
commission_pct number(2,2)
hire_date date not null,
....
constraint emp_email_uk unique(email);
)
create table dept90(id number constraint dept90_uk unique, name varchar2(20));
修改表定义unique约束
alter table dept90 modify(name unique);
10.定义primary key约束
create table department(
department_id number(4),
department_name varchar2(30),
CONSTRAINT dept_name_nn not null,
manager_id number(6),
location_id number(4),
CONSTRAINT dept_id_pk primary key(department id);
);
create table dept70(id number constraint dept70_pk primary key);
在修改表中定义一个约束
alert table dept60 modify(id constraint dept60_pk primary key);
两个列的主键定义方式
create table dept50(id number,name varchar2(20),constraint dept50_pk primary key(id,name));
11.定义foreign key 约束
create table employee(
employee number(6),
last_name varchar2(25),
email varchar2(25),
salary number(8,2),
......
department_id number(4),
constraint emp_dept_fk foreign key (department_id)
references departments(department_id),
constrant emp_email_uk unique(email);
)
创建表时定义foreignkey
alter table dept50 add(constraint dept50_fk foreign key(d_id)references dept60(id));
12.定义check约束
...salary number(2)
constraint emp_salary_min
check(salary>0)....
12.1创建表时定义check约束
create table dept30 (id number,salary number(8,2) constraint dept30_ck check(salary>1000));
12.2修改表时添加check约束
alter table dept50 add(constraint dept50_ck check(d_id>1000));
13禁用与启用约束
13.1查看约束
select constraint_name ,cnstraint_type,
search_condition
from user_constraints
where table_name='EMPLOYEES';
13.2禁用约束
alter table employee disable constraint emp_emp_id_pk cascade ;
alter table dept40 disable constraint dept40_ck;
级联禁用
alter table dept60 disable constraint dept60_pk cascade;
使用级联操作会使被操参照的外键约束也失效；
13.3启用约束
alter table employees
enable constraint emp_emp_id_pk;
13.3.1示例
alter table dept40 enable constraint dept40_ck;
14.创建数据库其他对象
1.视图
可以通过创建表的视图来表现数据的逻辑子集或者数据的组合。视图是基于表或另一个视图的逻辑表，
一个视图并不包含它自己的数据，它像一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。
1.2视图的优越性
视图限制数据的访问，因为视图能够选择性的显示表中的列。
视图可以用来构成简单的查询以取出复杂查询的结果。例如，视图能用于从多表查询信息，而用户不必知道怎样写连接语句。
视图对特别的用户和应用程序提供数据独立性，一个视图可以从表中取回数据。
1.3视图分类
简单视图和复杂视图
| 特性 | 简单视图 | 复杂视图 |
| 表的数目 | 一个 | 一个或者多个 |
| 包含函数 | 无 | 有 |
| 包含数据分组 | 无 | 有 |
| 通过视图进行DML操作 | 是 | 不允许 |
1.4简单视图VS复杂视图
视图有两种分类：简单和复杂，基本区别涉及DML(insert、update和delete)操作。
简单视图：
数据仅来自一个表
不包括函数或数据分组
能通过视图执行DML操作
复杂视图:
数据来自多个表
包含函数或者数据分组
不允许通过视图进行DML操作
1.5创建简单视图
条件
- 只能对一个表进行查询
- 在查询当中不能包含函数
- 不能包含数据分组
create view empvu80
as select employee_id,last_name,salary
from employees
where department_id=80;
1.5.1示例
创建一个视图，视图中包含部门id为80的员工的id，名字以及薪水。
create view empvu80
as select employee_id,last_name,salary
from employees
where department_id=80;
1.6用子查询中的列别名创建视图
如果在创建视图的查询语句中包含有列别名，那么列别名将作为视图的别名。
实例
创建一个视图，包含部门id为50的员工id使用ID_NVMBER命名该列，包含员工名字使用name命名该列，包含员工的年薪使用ANN_SALARY命名该列。
create view emp50 as select e.employee_id id_number,e.last_name,12*e.salary ann_salary from employee e;
1.7从视图中取回数据
示例一：
查询部门id为80的员工信息，包含他们的id，名字以及薪水。
select * from emp80;
实例二：
查询部门id为50的员工信息，包含他们的id和薪水。
select e.id_number, e.ann_salary from emp50 e;
不能查询视图中未包含的列。
1.8创建复杂视图
特点：
- 当我的视图查询是来自于两张表的
- 在我的语句当中包含了组函数也就是聚合函数
- 当我们的查询当中有分组group by子句出现
create 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, departments d
where e.department_id=d.department_id
group by d.department_name;
1.8.1实例
创建一个视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的平均薪水。
1.9在定义视图时指定列名
select d.department_name,min(e.salary) min,max(e.salary) max,avg(e.salary) avg from employees e ,departments d where e.department_id=d.department_id group by d.department_name;
1.9.1示例
创建一个视图，包含每个部门的部门名称、部门最低薪水、部门最高薪水以及部门的平均薪水。将部门名称为 name 、最低薪水命名为minsal、最高薪水命名为 maxsal 、平均薪水命名为avgsal。
加别名1
create view dept_name as select d.department_name,min(e.salary) min,max(e.salary) max,avg(e.salary) avg
from employees e ,departments d
where e.department_id=d.department_id
group by d.department_name;
加别名2
create view dept_name1 (name,minsal,maxsal,avgsal)as select d.department_name,min(e.salary) ,max(e.salary),avg(e.salary)
from employees e ,departments d
where e.department_id=d.department_id
group by d.department_name;
1.10视图中DML操作的执行规则
如果视图中 包含下面的部分 就不能修改数据：
组函数
groupby 子句
distinct关键字
用表达式定义的列
1.10.1示例
删除emp80视图中雇员ID为100的雇员。
1.11拒绝DML操作
create or replace view empvu10
(employee_number, employee_name,job_title)
as select employee_id, last_name ,job_id
from empoyees
where department_id=10
with read only;
1.12示例
创建一个简单视图、包含employees表中的所有数据，单该视图拒绝DML操作。
create view v_emp as select * from employees with read only;
1.13删除视图
drop view view
删除视图不会丢失数据,因为视图是基于数据库中的基本表的。
示例
删除名称为emp90的视图。
1.14内建视图和子查询
1.14.1什么是内建视图
内建视图是一个带有别名(或者相关名)的可以在sql语句中使用的子查询
一个主查询的在from子句中指定的子查询就是一个内建视图。
内建视图：内建视图由位于from子句中命名了别名的子查询创建。该子查询定义一个可以在主查询中引用数据源。
示例：
显示那些雇员低于他们部门最高薪水的雇员的名字、薪水、部门号和他们部门最高的薪水。
select em.last_name,em.salary,em.department_id ,e.maxsal
from employees em ,(select e. department_id , max(e.salary) maxsal from
employees e group by e.department_id) e
where em.department_id=e.department_id
em.salary<e.maxsal;
1.15Top-N分析
1.15.1什么是“TOP-N”分析
top-n 查询在需要基于一个条件，从表中显示最前面的n条记录或最后的n条记录是有用的。该结果可以用于进一步分析，例如，用top-n分析你可以执行下面的查询类型。
- 在公司中挣钱最多的三个人
- 公司中最新的四个员工
- 销售产品最多的两个销售代表
- 过去六个月中销售最好的3种产品。
1.15.2执行top分析
top-n查询使用一个带有下面描述的元素的一致的嵌套查询结构：
子查询或者内建视图产生数据的排序列表，该子查询或者内建视图包含order by 子句来确保排序以想要的顺序排序。为了取回最大值，需要用desc参数。
在最后的结果集中用外查询限制行数。外查询包括下面的组成部分：
--rownum 伪列，它为从子查询返回的每一行指定一个从1开始连续的值
--一个where子句，它指定被返回的行，外where子句必须用一个<或者《=操作。
工资最高的三个人。
select rownum ,last_name,salary from(select last_name,salary from employees order bysalary desc) where rownum <=3;
1.16oralce的分页查询
1.16.1什么是分页查询
当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如，服务器资源被耗尽，因数据传输量过大而使处理超时，等等。最终都会导致查询无法完成。解决这个问题的一个策略就是分页查询，也就是说不要一次性查询所有的数据，每次查询一部分数据。这样分批地进行处理，可以呈现出很好的用户体验，对服务资源的消耗也不大。
分页查询原则：
在内建视图中通过rownum伪劣值的判断来指定获取数据的数量。
1.16.2示例。
查询雇员表中数据，每次只返回10条数据。
select * from (select rownum rn,e.* from employees e)em where em.rn >=1 and em.rn<=10;
select * from (select rownum rn,e.* from employees e)em where em.rn between 11 and 20;
2.序列
2.1什么是序列
序列是用户创建的数据库对象，序列会产生唯一的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个oracle内部程序产生并增加或者减少。
序列是一个节省时间的对象，因为它可以减少应用程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。
2.2创建序列
2.2.1通过ddl语句创建序列
create sequence sequencce
[increment by n]
[start with n]
[{maxvalue n| nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n|nocache}];
在语法中：
squence是序列发生器的名字
increment by n 指的是序列号之间的间隔，在这儿 n是一个整数(如果该子句被省略,序列量为1)
start with n 指的是要产生的第一个序列数(如果该子句被省略，序列从1开始)
maxvalue n 指定序列能产生的最大值
nomaxvalue 对于升序序列指定10^27为最大值，对于降序序列指定-1为最大值(这里是默认选项)
minvalue n 指定最小序列值
nominvalue 对于升序序列指定1为最小值，对于降序序列指定-(10^27)为最小值
cycle|nocycle指定序列在达到它的最大或者最小值之后，是否继续产生(nocycle是默认选项)
cache n|nocache 指定oracle服务器预先分配多少值，并且在内存中(默认情况下,oracle服务器缓冲20个值)
cteate sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocache nocycle;
2.3操作序列
2.3.1查询序列
select sequence_name min_value,max_value,increment_by,last_number
from user_sequences;
2.3.2使用序列
NEXTVAL和curral伪列
nextval返回下一个可用的序列值，它每次返回一个唯一的被引用值，即使对于不同的用户也是如此。
currval获取当前的序列值
在currval获取一个值以前，nextval对该序列必须发布
select test_seq.currval from dual;
2.3.2.1示例
在location ID2500中插入一个新的部门名称support。
insert into departments(department_id,department_name,location_id)
values(dept_seq.nextval,'Support',2500);
2.4修改序列
2.4.1修改序列
alter sequence dept_deptid_seq
increment by 20
maxvalue 9999999
nocache
nocycle;
2.4.2修改序列的原则
必须是被修改序列的所有者，或者有alter权限。
用alter sequence 语句，只有以后的序列数会受到影响。
用alter sequence语句，startwith选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。
2.4.2.1示例
将dept_seq序列中的增长量修改为20，最大为999999.
alter sequence dept_seq increment by 20 maxvalue 999999 nocache nocycle
2.4.3删除序列
drop sequence dept_deptid_seq;
2.4.3.1示例
删除dept_seq序列。
3.索引
3.1什么是索引
在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或者多列的值进行排序的一种存储结构，它是某个表中一列或者若干列的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需要的内容。
索引提供对表中行的直接和快速访问。它的目的是用已索引的路径快速定位数据以减少磁盘I/O。索引由oracle服务器自动使用和维护，索引逻辑的和物理的独立于他们索引的表，这意味着索引可以在任何时候被创建或者删除，并且不影响基表或其他的索引。
当删除表时，相应的索引也被删除。
3.2索引的类型
唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一建约束时oracle服务器自动创建该索引，索引的名字习惯上是约束的名字。
非唯一性索引：由用户创建，例如，可以创建一个foreign key列索引用于一个查询中的连接来改进数据取回的速度。
3.3创建索引的方式
自动：在一个表的定义中，当定义一个primary key或者unique约束时，一个唯一索引被自动创建
手动：用户能够在列上创建非唯一的索引来加速对行的访问。
3.4使用索引
过多也是坏事。
在表上建立更多的索引并不意味着更快地查询，在带索引地表上被提交地每个dml操作意味着索引必须跟新：与表联系地索引越多，对oracle数据库地影响越大，oracle数据库每次dml操作之后必须跟新所有的索引。
3.4.1什么时候创建索引
- 一个列包含一个大范围的值
- 一个列包含很多的空值
- 一个列或多个列经常同时在一个where 子句中或者一个连接条件中使用
- 表很大，并且经常的查询期望取回少于百分之2到4的行。
3.4.2什么时候不创建索引
表很小
不经常在查询中作为条件被使用的列
大多数查询期望取回多于表中百分之2到4行
表经常被更新
被索引的列作为表达式的一部分被引用
3.5操作索引
3.5.1非唯一性索引的类型
oracle的非唯一性索引：单行索引，复合索引(组合索引)，函数索引
3.5.2创建索引语法
create index index on
table (column[,column]......)
3.5.2.1创建单行索引
3.5.2.1.1示例
为employees表中的last_name创建一个索引并命名为emp_index。
create index emp_index on employees(last_name);
3.5.2.2.2示例
为departments表创建一个包括manager_id与location_id复合索引并命名为dept_man_loc。
create index dept_man_loc on departments(manager_id,location_id);
3.5.2.3创建函数索引
3.5.2.3.1示例
为department表中的department_name创建一个带有大写函数的索引dept_upper2。
create index dept_upper2 on departments(upper(department_name));
3.5.3查询索引
USER_INDEXES数据字典视图包含索引和它唯一的名字。
USER_IND_COLUMNS视图包含索引名、表名和列名。
select ic.index_name ,ic.column_name,
ic.column_position col_pos, ix.uniqueness
from user_indexes ix, user_ind columns ic
where ic.index_name=ix.index_name
and ic.table_name='EMPLOYEES',
select ic.index_name,ic.column_name,ic.column_position,in.uniqueness from user_indexes ix ,user_ind_columns ic where ix.index_name=ic.index_name and ic.table_name=''departments;
3.5.4删除索引
drop index index;
3.5.4.1示例
删除名称为dept_upper的索引。
drop index dept_upper。
4.同义词
4.1什么是同义词
同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和存储过程名或者其他对象名。
该该方法对具有特别长的对象的名字很有用。
4.2创建和删除同义词
4.2.1创建同义词
create sysnonym d_sum
for dept_sum_vu;
4.2.1.1示例
create synonym dep for departments;
4.2.2删除同义词
drop synonym dep;
5.创建用户
5.1什么是oracle用户
oracle用户是来连接数据库和访问数据库对象的。
5.2操作用户
5.2.1创建用户
需要具备创建用户的权限可以使用sys或者system用户来创建新用户。
create user user
indentified by password
5.2.1.1使用默认表空间
5.2.1.1.1示例
创建一个用户名称为u_test,永久表空间使用oracle默认的永久表空间。
create user u_test identified by oracle;
5.2.1.2使用指定表空间
create user u_wq identified by oracle default tablespace wq /(temporary tablespace);
5.2.2.1示例
删除u_test用户
drop user u_test;
删除用户的同时将该用户下的其他对象一并删掉。
drop user u_test cascaed;
十二、数据控制语言(DCL)
grant ... 授予用户权限
revork 撤销用户权限
1.授予系统权限
grant create session,create table,
create sequence,create view
to scott;
1.1授予创建其他对象权限
create session
create table
create sequence
create procedure
unlimited tablespace
为u_wq用户分配创建表、视图、创建序列权限以及使用永久表空间权限。
grant create table,create view ,create sequence to u_wq;
grant unlimited tablespace to u_wq;
2.撤销权限
revoke权限from 用户
2.1示例
revoke create table from u_wq;
撤销u_wq用户创建表的权限
revoke create table from u_wq;
3.oracle中的角色
3.1什么是角色
角色是命名的可以授予用户的相关权限的组，该方法使得授予、撤回和维护权限容易的多。
一个用户可以使用几个角色，并且几个用户也可以被指定相同的角色。
3.2创建角色并授予权限给角色
3.2.1创建角色
create role manager;
3.2.1.1示例
创建一个名称为manager的角色。
3.2.2授予权限给一个角色
grant create table ,create view to manager;
3.2.2.1示例一
向manager角色中添加会话，创建表、视图、创建序列。
grant create session,create table,create view ,create sequence to manager;
3.2.3授予一个角色给用户
grant manager to dehann ，kochhar;
3.2.3.1示例一
创建一个名称为wq用户密码为wq。该用户使用表空间wq。
create user wq identified by wq default tablespace wq;
3.2.3.2示例二
为用户分配可以无限制的使用永久表空间
grant unlimited tablespace to wq;
3.2.3.3示例三
将manager角色分配给wq用户。
grant manager to wq;
3.2.3.4示例四
在wq用户中创建一个测试表，包含一个id列类型为整数类型。
create table test(id number);
3.2.4撤销用户角色
3.2.4.1示例
撤销wq用户的manager角色。
revoke manager from wq;
十三通过PL/SQL Developer查看查询的执行计划
1.什么是执行计划
执行计划是一条查询语句在oracle中执行过程访问路劲的描述。
2.配置执行计划需要显示的项
3.执行计划的常用列字段解释。
基数(Cardinality):oracle估计的当前操作的返回结果集行数
字节(Bytes):执行该步骤后返回的字节数。
耗费(COST)、cpu耗费：oracle估计的该步骤的执行成本，用于说明sql执行的。
time(Time):oracle估计的当前操作所需的时间
4.使用执行计划
通过工具启动执行计划。选中需要查看执行计划的查询语句，在工具栏中选择
Tools---->Explain Plan或者F5
5.查看执行计划
5.1执行顺序
缩进最多的最先执行：(缩进相同时，最上面的最先执行)。
5.2表访问的几种方式
TABLEACCESS FULL(全表扫描)
TABLEACCESS BY INDEX ROWID(通过rowid的表存取)
TABLEACCESS BY INDEX SCAN(索引扫描)
5.2.1Table Acess full(全表扫描)
oracle会读取表中所有的行，并检查每一行是否满足sql语句中的where限制条件
使用建议，数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的5-10%以上。
5.2.2TABLE ACCESS BY INDEX ROWID (通过rowid的表存取)
5.2.2.1什么是ROWID
ROWID是oralce自动加在表中每行最后的一列伪列，就说明表中并不会物理存储ROWID的值。
你可以像使用其他列一样使用它，只是不能对该列的值进行增删改查操作。
一旦一行数据插入后，则其对应的ROWID在该执行声明周期内是唯一的，即使发生行迁移，该行的ROWID值也不变。
5.2.2.2TABLE ACCESS BYINDEX ROWID(索引扫描)
行的rowid指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过rowid可以快速定位到目标数据上，这也是oracle中存取单行数据最块的方法。
5.2.3.1索引扫描其实分为两步
1.扫描索引得到对应的rowid。
2.通过rowid定位到具体的行读取数据。
5.2.3.2五种索引扫描
index unique scan (索引唯一扫描)
index range scan(索引范围扫描)
index full scan(索引全扫描)
index fast full scan(索引快速扫描)
index skip scan(索引跳跃扫描)
5.2.4oracle的优化器
5.2.4.1oracle的优化器种类
RBO(Rule-Based Optimization) 基于规则的优化器
CBO(Cost-Based Optimization)基于代价的优化器
