Oracle基础之二->oracle视图、用户管理、序列、DML、事务、索引、数据库设计三范式

一、视图

1、视图(view),也称虚拟表，不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候，只是重新执行SQL。
2、视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表，一个视图也可以从另一个视图中产生
3、视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中
4、视图看上去非常像数据库的物理表，对他的操作同任何其他的表一样，当通过视图修改数据时，实际上是在改变基表中的数据，相反地，基表数据的改变也会自动反映在由基表产生的视图中，由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）

1、创建视图，使用视图

--创建视图:如果普通用户第一次创建视图，提示没有权限，要使用管理员去修改权限
--grant create view to scott;
--revoke create from scott;回收权限
create view v_emp as select * from emp where deptno = 30;
--视图的使用
select * from v_emp;
--向视图中添加数据
insert into v_emp(empno,ename) values(1111,'zhangsan');
select * from emp;
--如果定义的视图是非只读视图的话，可以通过视图向表中插入数据，如果是只读视图，则不可以插入数据
create view v_emp2 as select * from emp with read only;
select * from v_emp2;
--只读视图只提供查询的需求，无法进行增删改操作
insert into v_emp2(empno,ename) values(1234,'lisi');
--删除视图
drop view v_emp2;
--当删除视图中的数据的时候，如果数据来源于多个基表,
--则此时不能全部进行删除，只能删除一个表中的数据

--求平均薪水的等级最低的部门，他的部门名称是什么，我们完全使用子查询
--(1)求平均薪水
select avg(sal) from emp group by deptno;
--（2）求平均薪水的等级
select t.deptno,sg.grade gd from salgrade sg join (select deptno,avg(sal) vsal from emp group by deptno)t
on t.vsal between sg.losal and sg.hisal
--（3）求平均薪水的等级最低的部门
select min(t1.gd) from (select t.deptno,sg.grade gd from salgrade sg join (select deptno,avg(sal) vsal from emp group by deptno)t
on t.vsal between sg.losal and sg.hisal)t1
--(4)求平均薪水的等级最低的部门名称
select d.dname, d.deptno
  from dept d
  join (select t.deptno, sg.grade gd
          from salgrade sg
          join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t
            on t.vsal between sg.losal and sg.hisal) t
    on t.deptno = d.deptno
 where t.gd =
       (select min(t1.gd)
          from (select t.deptno, sg.grade gd
                  from salgrade sg
                  join (select deptno, avg(sal) vsal from emp group by deptno) t
                    on t.vsal between sg.losal and sg.hisal) t1)

--查看SQL语句能发现，sql中有很多的重复的sql子查询，可以通过视图将重复的语句给抽象出来
create view v_deptno_grade as select t.deptno,sg.grade gd from salgrade sg join (select deptno,avg(sal) vsal from emp group by deptno)t
on t.vsal between sg.losal and sg.hisal;
--使用视图替换
select d.dname, d.deptno
  from dept d
  join v_deptno_grade t
    on t.deptno = d.deptno
 where t.gd =
       (select min(t1.gd)
          from v_deptno_grade t1)

二、用户管理

三、序列

--在Oracle中如果要完成一个列的自增操作，必须要使用序列
/*
create sequence seq_name
increment by n  每次增长几
start with n  从哪个值开始增长
maxvalue n|no maxvalue  10^27 or -1 最大值
minvalue n|no minvalue  最小值
cycle|nocycle  是否循环
cache n|nocache   是否有缓存
*/

create sequence my_sequence
increment by 2
start with 1

--使用
--注意，如果创建好序列只会，没有经过任何的使用，那么不能获取当前的值，
--必须要先执行nextval之后才能获取当前值

--查看当前序列的值
select my_sequence.currval from dual;
--获取序列的下一个值
select my_sequence.nextval from dual;

insert into emp(empno,ename) values(my_sequence.nextval,'hehe');
select * from emp;

四、Insert Delete Update

1、数据更新

--DML:数据库操作语言
--增
--删
--改
--在实际项目中，使用最多的是读取操作，但是插入数据和删除数据同等重要，而修改操作相对较少
/*插入：
  元组值的插入：
  查询结果的插入
*/
--最基本的插入方式
--insert into tablename values(val1,val2......)如果表名之后没有列名，那么只能将所有列都插入
--insert into tablename(col1,col2......) values(val1,val2......)可以指定向哪些列中插入数据
insert into emp values(2222,'haha','clerk',7902,to_date('2019-11-2','YYYY-MM-dd'),1000,500,10);
select * from emp;
insert into emp(empno,ename) values(3333,'wangwu');
--创建表的其他方式

--复制表结构同时复制表数据 不会复制约束
create table emp2 as select * from emp;
--复制表结构不复制表数据，因为条件不满足 不会复制约束
create table emp3 as select * from emp where 1=2;
--如果有一个集合的数据，把集合中的所有数据都挨条插入的话，效率如何？一般在实际操作中很少一条条插入，更多的是批量插入

/*
删除操作
delete from tablename where condition
*/
select * from emp2;
--删除满足条件的数据
delete from emp2 where deptno = 10;
--删除所有数据
delete from emp2;
--truncate,跟delete有所不同，delete在进行删除的时候经过事务
--而truncate不经过事务，一旦删除就是永久删除，不具备回滚的操作
--不建议使用，如果你手抖了呢
truncate table emp2;

/*
修改操作
update tablename set col1=val1,col2=val2 where condition
可以跟新或者修改满足条件的一个列或者多个列
*/

update emp set ename='heihei' where ename = 'hehe'
select * from emp;

五、事务

1、事务（Transaction）是一个操作序列，这些操作要么都做，要么都不做，是一个不可分割的单位，是数据库环境中的逻辑工作单位。
2、事务是为了保证数据库的完整性
3、事务不能嵌套
4、在oracle中，没有事务开始的语句。一个Transaction起始于一条DML（Insert、Update和Delete）语句，结束于以下几种情况：

(1)用户显式执行Commit语句提交操作或Rollback语句回退
(2)当执行DDL(Create、Alter、Drop)语句事务自动提交
(3)用户正常断开连接时，Transaction自动提交
(4)系统崩溃或断电时事务自动回退

1、commit & Rollback

1、Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。每个Commit语句都可以看成是一个事务成功的结束，同时也是另一个事务的开始。
2、Rollback表示事务不成功的结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的更新必须被撤销，数据库应恢复该事务到初始状态。每个Rollback语句同时也是另一个事务的开始。
3、一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销。
4、执行DDL,DCL语句或从SQL*PLUS正常退出，都会自动执行commit命令
5、savepoint test01 保存点，当一个操作集合中包含多条sql语句，但是只想让其中某部分成功，某部分失败，就可以使用savepoint
6、rollback to test01

2、acid

1、原子性（Atomicity）

一个原子事务要么完整执行，要么干脆不执行。这意味着，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个工作单元或事务就会被终止，即此前对数据所作的任何修改都将被撤销，如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的
2、一致性（Consistency）
一直性代表了底层数据存储的完整性，他必须由事务系统和应用开发人员共同来保证，事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求，应用开发人员则需要保证数据库有适当的约束（主键，引用完整性等），并且工作单元中所实现的业务逻辑不会导致数据的不一致（即，数据预期所表达的现实业务情况不相一致）。例如：在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。支付宝账号100，你读到余额要取，有人向你转100，但是事务没提交，（这时候你读到的余额应该是100，而不是200），这种就是一致性

3、隔离性（Isolation）

隔离性意味着事务必须在不干扰其他进程或事务的前提下独立执行。换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响

4、持久性（Durability）

持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备，这样可以保证，所作的修改在任何系统瘫痪时不至于丢失

3、隔离级别

1、read uncommited

脏读问题：读到别人未提交的数据
不可重复读
幻读

2、read commited

解决了脏读问题，但是存在不可重复读和幻读问题：在同一个事务中执行两次查询，得到的结果不一样，第一次查询的时候，别人还没提交，读到的是别人提交之前的数据，第二次查询的时候读到别人提交后的数据，两次结果不一致，叫不可重复读

3、repeatable read

解决了脏读和不可重复读，但是存在幻读问题：在同一个事务中查询n次，结果也是一致的，这个时候即使其他的事务修改并提交了数据，该事务也查询不到，只有将该事务提交之后，重新查询，才能查到别人修改的数据

幻读：在此隔离级别下，在同一个事务的执行过程中，如果其他的事务提交了数据，该事务也读取不到，那么如果别人插入了一条数据，该事务中是不知道的，如果该事务也插入一条相同的数据，会出现错误（主键重复），但在该事务中却不知道这条数据已经存在（难道是我产生幻觉了吗），这叫幻读

4、序列化

解决了所有问题，但是效率极低

5、总结
现在学习的是数据库级别的事务，需要掌握的就是事务的隔离级别和产生的数据不一致的情况，后续会学习声明式事务及事务的传播特性以及分布式事务，后面学到了再来补充

五、建表

1、常用数据类型

2、表的创建

3、约束

创建表的时候可以给表中的数据添加数据校验规则，这些规则称之为约束
约束分为5大类:

1、not null:非空约束，插入数据的时候某些列不允许为空
2、unique key:唯一键约束，可以限定某一个列的值是唯一的，唯一键的列一般被用作索引列
3、primary key:主键：非空且唯一，任何一张表一般情况下最好有主键，用来唯一的标识一行记录
4、foreign key：外键，当多个表直接有关联关系（一个表的某个列的值依赖于另一个表的某个值）的时候，需要使用外键
5、check约束:可以根据用户自己的需求去限定某些列的值

示例

--DDL
--创建表
create table student(
stu_id number(10),
name varchar2(20),
age number(3),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);
insert into student values(20191109,'zhangsan',22,to_date('2019-11-09','YYYY-MM-DD'),'2','1','[e-mail]');
insert into student (stu_id,name,age,hiredate,classes,email)
values(20191109,'zhangsan',22,to_date('2019-11-09','YYYY-MM-DD'),'2','[e-mail]');

select * from student; 
--正规的表结构涉及需要使用第三方工具Powerdesigner
--再添加表的列的时候，不能允许设置成not null
alter table student add address varchar2(100);
alter table student drop column address;
alter table student modify(email varchar2(100));
--表重命名
rename student to stu;
--删除表
--在删除表的时候，经常回遇到多个表关联的情况，多个表关联的时候不能随意删除，需要使用级联删除
--cascade:如果A，B，A中的某一个字段跟B表中的某一个字段做关联，那么在删除表A的时候，需要先将表B删除
--set null:在删除的时候，把表的关联字段设置成null
drop table stu ;

--约束
/*
创建表的时候可以给表中的数据添加数据校验规则，这些规则称之为约束
约束分为5大类:
not null:非空约束，插入数据的时候某些列不允许为空
unique key:唯一键约束，可以限定某一个列的值是唯一的，唯一键的列一般被用作索引列
primary key:主键：非空且唯一，任何一张表一般情况下最好有主键，用来唯一的标识一行记录
foreign key：外键，当多个表直接有关联关系（一个表的某个列的值依赖于另一个表的某个值）的时候，需要使用外键
check约束:可以根据用户自己的需求去限定某些列的值
*/
create table stu(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age >0 and age<126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

insert into stu 
values(20191109,'zhangsan',22,to_date('2019-11-09','YYYY-MM-DD'),'2','1','[e-mail]',10);

--古创建表的时候没有添加外键约束，可以通过修改表添加
alter table stu add constraint fk_0001 foreign key(deptno) references dept(deptno)

六、索引

1、介绍

1、索引是为了加快对数据的搜索速度而设立的。索引是方案（schema）中的一
个数据库对象,与表独立存放.
2、 索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法
快速定位数据,减少了磁盘的I/O
3、 Sql中的索引是非显示索引，也就是在索引创建以后，在用户撤销它之前不
会在用到该索引的名字，但是索引在用户查询时会自动起作用。
4、 索引的创建有两种情况

(1). 自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动
创建一个对应的唯一索引.
(2). 手动: 用户可以创建索引以加速查询

2、开发中索引的要点

1. 索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
2. 索引数据可能要占用大量的存储空间。
3. 并非所有的数据都适合于索引。唯一性不好的数据（如省）从索引的到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多。
4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选。
5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

3、操作

1、在一列或者多列上创建索引.

CREATE INDEX index ON table (column[, column]...);

2、下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.

CREATE INDEX emp_last_name_idx
ON emp (ename)

3、通过DROP INDEX 命令删掉一个索引.

DROP INDEX index;

4、删掉 UPPER_LAST_NAME_IDX 索引.

DROP INDEX upper_last_name_idx;

4、示例

--索引：加快数据的检索
--创建索引
create index i_ename on emp(ename);
--删除索引
drop index i_ename;

七、三范式

在设计和数据库有关的系统时，数据库表的设计至关重要，这些设计关系整个系统的架构，需要精心的仔细考虑。
数据库的设计主要包含了设计表结构和表之间的联系，在设计的过程中，有一些规则应该遵守
总的来说，三范式产生是为了解决数据冗余问题

1、第一范式

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。例如，对于员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，
不满足第一范式（1NF）的数据库就不是关系数据库。

列不可分。

2、第二范式

第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。要求实体的属性完全依赖于主关键字。

不能部分依赖。即：一张表存在组合主键时，其他非主键字段不能部分依赖

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字

3、第三范式

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。

不能存在传递依赖。即：除主键外，其他字段必须依赖主键

4、总结

▪ 第一范式
– 列不可分
▪ 第二范式
– 列必须直接依赖主键
▪ 第三范式
– 传递依赖
▪ 表里面的列不能出现其它表的非主键字段