视图(view)
作用
- 控制数据访问
- 简化查询
- 数据独立性
- 避免重复访问相同的数据
视图的创建
注意
在运维数据库，对一些数据进行操作时，最好先将要操作的数据备份，然后再操作数据或者直接操作备份后的数据。
1
2
3
4
-- 快速复制表结构，如果复制的表有数据，则顺势复制表中的所有数据。
create table emp_bank
as
select * from emp
创建方法
1
2
3
4
-- `create or replace` 表示如果创建的视图已存在则替换
create or replace view v_emp_bank
as select ename, sal
from emp_bank
创建之后可以直接通过 select 查询该视图的结果
该结果与 as 后的 select 结果相同。
view 的好处
例1
查询部门号为30的部门总薪水值。
1
2
3
4
5
6
7
8
-- 没有视图
select * from
(
select deptno, sum(sal) sum_sal
from emp_bank
group by deptno
)
where deptno = 30
1
2
3
4
5
6
7
create or replace view v_emp_sum
as
select deptno, sum(sal) sum_sal
from emp_bank
group by deptno;
-- 有视图
select * from v_emp_sum where deptno = 30
例2
查 “SMITH” 的编号、姓名、部门名称、工资范围
1
2
3
4
5
6
7
8
-- 无视图
select emp.empno, emp.ename, dept.dname, salgrade.grade from emp
join dept
on dept.deptno = emp.deptno
and emp.ename = 'SMITH'
join salgrade
on emp.sal
between salgrade.losal and salgrade.hisal
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建视图
create or replace view v_emp_dept_grade
as
select emp.empno, emp.ename, dept.dname, salgrade.grade
from emp
join dept
on dept.deptno = emp.deptno
join salgrade
on emp.sal
between salgrade.losal and salgrade.hisal
-- 有视图下的查询
select * from v_emp_dept_grade where ename = 'SMITH'
建议
因为视图与具体的数据库产品强相关，所以：
- 如果项目的数据库是不变的，可以选择 view 简单一些复杂的多表查
- 如果项目要支持多个数据库铲平，考虑移植性时，尽量不采用。
- 视图过多，也会造成管理上一定的复杂性。（表结构变化时也要同步的更改视图。）
尽量 只对视图进行查询操作 ，不要执行DML操作
索引(index)
作用
- 主要用于提高数据库查询的性能
- 让数据库中的某些字段有序
索引的创建
自动创建
当创建 UNIQUE 、PK 等约束条件时，索引会自动创建。
人为创建
1
create index emp_ename_idx on emp(ename)
索引的建立会让 sql 查询更加快速。
特殊情况
当迫不得已，必须使用以下查询：
1
select * from emp where ename = UPPER('SMITH')
针对该查询，一般会单独再创建一个索引
创建基于函数的索引
1
2
create index emp_ename_upper_idx
on emp(upper(ename));
提醒
合理使用索引提升查询效率，为了提高查询效率，创建和使用索引的原则：
- 为经常出现在where子句中的列创建索引。
- 为经常order by 、 DISTINCT 后面的字段建立索引。如果建立的时复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致。
- 为经常作为表的连接条件的列上创建索引。
- 不要在经常做 DML 操作的表上建立索引。
- 不要在小表上建立索引，在大表上建立索引。
- 限制表上的索引数目，索引并不是越多越好。
- 删除很少被使用的、不合理的索引。
事务(Transaction)
事务 —— transaction 事务、交易
事务的特性：ACID
A：原子性(atomic)
概念：一个事务要么完全发生，要么完全不发生
同一个数据库，当有一个客户端正在执行但未通过 rollbackcommitsavepoint 结束事务时。
其他客户端或是窗口无法对该数据库操作。
B：一致性(consistent)
概念：事务把数据库从一个一致的状态变为另一个状态。
当事务结束之后，无论是通过其他客户端还是其他窗口对数据库进行查询，结果都是相同的。
C：隔离性(isolated)
概念：在事务提交之前，其他事务察觉不到事务的影响。
在当前窗口中事务如果没结束，从其他窗口无法查询到当前窗口修改后的数据。
D：持久性(durable)
概念：一旦事务提交，就是永久的。
事务实例
对事务操作的语句：
- commit 提交
- rollback 回滚
- savepoint 保存点
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table temp1(
id number primary key
);
insert into temp1 values(1);
savepoint A;
insert into temp1 values(3);
savepoint B;
insert into temp1 values(4);
savepoint C;
insert into temp1 values(5);
select * from temp1
rollback to B;
rollback to C;
rollback to A;
commit;
事务的回滚和提交：
- sql 的事务提交之前只有当前窗口可以看到。
- 此时其他窗口执行事务时会处于等待状态。
- 且只有当这个事务提交或者回滚之后，其他窗口或客户端才可以执行事务。
事务的开始：Oracle 中由dml 开始的一个事务
上一个事务的结束就是下一个事务的开始