Oracle基础 三（约束、序列、视图 、索引）

约束

约束就是指对插入数据的各种限制，例如：人员的姓名不能为空，人的年龄只能在 0~150 岁之间。约束可以对数据库中的数据进行保护。
约束可以在建表的时候直接声明，也可以为已建好的表添加约束。

约束分类

1：主键约束 primary key (mysql 通过Primary key实现主键约束)，但是Oracle是通过序列实现主键约束！
2：外键约束 constraint … foreign key(…) peferences…
3：非空约束 not null
4：唯一约束,值不能重复（空值除外）unique
5：条件约束，插入的数据必须满足某些条件 check

例如：

  CREATE TABLE person_a(
	Pid      VARCHAR(18)  NOT NULL ,  -- 非空约束，非空指的就是必须给字段赋值！ 
	Name    VARCHAR(200)  UNIQUE  NOT NULL,   -- 唯一约束  unique 
	Age      NUMBER(3),
	Birthday  DATE,-- 建表的时候利用data   ---赋值利用to_date
	SEX     VARCHAR(2)   DEFAULT  ‘男’,  -- 检查约束  check('男')
	CONSTRAINT   person_pid_pk  PRIMARY KEY(pid)   --主键约束
);

• 主键的特性是什么 ？
  （1）主键具有唯一性 （主键 不可以重复）
  （2）非空性

修改约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型（约束字段）;
关于约束类型的命名一定要统一:
1)PRIMARY KEY:主键字段Pid_PK 主键简写为 ：pk
2)UNIQUE：字段sex_UK unique简写：uk
3)CHECK：字段_CK check --ck
4)FOREIGN KEY：父字段_子字段_FK --外键 FK

ALTER  TABLE  person  ADD  CONSTRAINT  person_pid_PK   PRIMARY KEY(pid);
ALTER  TABLE  person  ADD  CONSTRAINT  person_name_UK  UNIQUE(pid);
ALTER  TABLE  person  ADD  CONSTRAINT  person_age_CK  CHECK(age BETWEEN 0 AND  150);
ALTER  TABLE  person  ADD  CONSTRAINT  person_sex_CK  CHECK(sex IN (‘男’,’女’,’中’));

序列

在很多数据库中都存在一个自动增长的列,如果现在要想在oracle中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

序列的创建

create sequence 序列名称
start with n  --- 表示的是序列的开始值
increment by n ---每次序列增长幅度
maxvalue  n/ minvalue n     --- 表示的是序列的最大/最小内存空间 
cycle /nocycle    --- 表示的是 序列是否发生缓存空间 
cache  n; -- 表示的是序列的缓存区域的大小

• 建立序列

 CREATE SEQUENCE toys_seq    
  START WITH 10
  INCREMENT BY 10
  MAXVALUE 2000
  MINVALUE 10
  NOCYCLE
  CACHE 10;

序列操作

定义序列的目的就是读取数据

• nextval 表示的是将序列一行一行的读取
• currval 表示的是返回当前序列的值 ------等价于 java的 .next（）方法
• 序列读取数据的 语法结构如下 ：
  select 序列名称.nextval/currval from 表名称;

select TOYS_SEQ.NEXTVAL from emp;  -- 增量
select TOYS_SEQ.CURRVAL from dual;

– Oracle 实现主键递增

create table toys(t_id number primary key,t_name varchar2(12) not null);

-- 给主键添加序列 
语法结构
insert into toys(t_id,t_name) values(序列名称.nextval,'王老师');
insert into toys(t_id,t_name) values(TOYS_SEQ.nextval,'王老师'); -- id 是没有的 、默认值就是序列的stratwith =1

select * from toys

insert into toys(t_id,t_name) values(TOYS_SEQ.nextval,'王老师'); --居多使用

insert into toys(t_id,t_name) values(TOYS_SEQ.currval,'王老师'); 

• 一旦利用nextval ，不能再使用currval

删除序列

drop sequence 序列名称;

面试：序列的作用是什么？为什么定义序列 、序列的核心是什么？
(1):作用就是帮助Oracle的primay key实现递增！
(2):序列可以更加快速、高效的读取 “海量数据”-----利用大数据读取数据使用居多！
(3):序列的核心就是 ：nextval currval

ROWNUM

伪列:所有的记录中都存在此列，用以表示行的编号，在实际中可以通过此列并结合子查询完成表中的部分数据显示。

视图

视图是一个封装了各种复杂查询的语句

视图的优点

①提供了另外一种级别的表安全性 ！–数据的安全性
②隐藏的数据的复杂性—避免了很多 sql的难易程度！例如：子查询 、关键字等复杂的sql
③简化的用户的SQL命令
④隔离基表结构的改变 —基表就是主表 -----主库（主数据） —从库
⑤通过重命名列，从另一个角度提供数据

创建视图

• 基本语法

CREATE VIEW 视图名字(字段) AS 子查询
[WITH CHECK OPTION] 
[WITH READ ONLY]

• 参数说明

CREATE：用于创建和修改视图
WITH CHECK OPTION ：用于创建限制数据访问的视图
WITH READ ONLY ：用于创建只读视图

建立一个只包含 20 部门雇员信息的视图（雇员的编号、姓名、工资）

CREATE VIEW empv20 (empno,ename,sal) AS SELECT empno,ename,sal FROM emp 
WHERE deptno=20 ;

同名视图

出现同名视图，查询出错。
解决方法：
①先将之前的视图删除掉，再进行创建：
DROP VIEW empv20 ;
②先自动删除，之后自动创建。
CREATE OR REPLACE VIEW empv20
举例：

CREATE OR REPLACE VIEW empv20 (deptno,msal) AS (SELECT deptno,MIN(sal) FROM 
emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE 
deptno=20)) ;

视图操作

• 查询操作 select

select * from empv20;

• 修改操作 update

update empv20 set empv20.ENAME='andy' where empv20.JOB='CLERK';

• 添加操作 insert

insert into empv20 values (7777, 'chy', 'MANAGER', 8888, sysdate, 10000,1111.11,20);

• 删除操作 delete

delete from empv20 where empv20.EMPNO=7777;

• 删除视图 drop

drop view empv20;

为什么要使用视图？
（1）：保护原始数据的完整性!
（2）：提高sql的查询速度！
（3）：试图可以虚拟化数据 —这个功能对于数据库的数据的备份与还原有很大的作用！

索引

索引是一种用于提升查询效率的数据库对象，通过快速定位数据的方法减少磁盘的I/O操作，索引信息与表独立存放，Oracle数据库自动使用和维护索引。

索引优缺点

• 建立索引的优点
  1.大大加快数据的检索速度;
  2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
  3.加速表和表之间的连接;
  4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
• 索引的缺点
  1.索引需要占物理空间。
  2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的
  维护速度。

创建索引

（1）手动创建
用户可以在其它列上创建非唯一的索引，以加快sql查询速度

• 语法结构
  create index 索引名称 on (表名称).,.....

create index abc on student(sid,sname);
create index abc1 on student(sname,sid);

这两种索引方式是不一样的

• 索引 abc 对 Select * from student where sid=1; 这样的查询语句更有效
• 索引 abc1 对 Select * from student where sname=‟louis‟; 这样的查询语句更有效
• 因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段放在组合字段的前面

（2） 自动创建
在定义主键或唯一键约束时，系统会自动创建相应字段的唯一性索引。

删除索引

• 语法结构
  drop index 索引名称;

备注：索引建立成功只能，尽量不要删除。因为index可以直接和数据有关系，会破坏数据的内存空间。index和数据完整性直接由算法实现。

index底层其实是一种快速排序发算法！

索引的分类

（1）非唯一性索引 --------关键字 REBUILD;

• 语法结构 ：

alter index 索引名称 rebuild;
– 数据库里面id索引的值可以重复 ，体现了一个关系 ：key —value的关系

例如：java的Map集合 -----面试肯定问题！
(1):Map集合存放的是key --value Map<key,value>
(2):key是不可以重复的，value是可以重复的！
(3):一个键可以对应多个值-----体现了Oracle的 重复索引的思想 。
(4):不管是Python 、java Mysql Oracle 、 key 键一般都是Integer类型

（2） 唯一性索引

唯一索引确保在定义索引的列中没有重复值 。Oracle 自动在表的主键列上创建唯一索引 。使用CREATE UNIQUE INDEX语句创建唯一索引()

• 语法结构
  create unique index index名称 on 表名称(任意字段)

ceate unique index student5_id on student5(stu_id);   

什么时候创建索引

1：列中数值发布比较广泛。
2：列经常在where子句或者连接条件出现
3：表经常访问或者数据量较大的时候，访问的数据量大概占总量的2%—4%

什么时候不创建索引

1：数据库表很小。
2：列经常不在where子句或者连接条件出现
3：表经常更新