Oracle基础（十六）：序列、索引、约束
一、序列
序列也是数据库对象之一，作用是根据指定的规则生成一系列数字,一般用于为表的每一条记录的主键字段提供值。
其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。
创建序列语法：
CREATE SEQUENCE 序列名
[START WITH n] 序列的初始值 。(不指定时默认值是1)
[INCREMENT BY n] 序列变化的步进，正数表示递增，负数表示递减。(不指定时默认值是1)
[MAXVALUE m| NOMAXVALUE] 序列可生成的最大值。(默认不限制最大值，NOMAXVALUE)
[MINVALUE n| NOMINVALUE] 序列可生成的最小值。(默认不限制最小值，NOMINVALUE)
[CYCLE|NOCYCLE] 用于定义当序列产生的值达到限制值后是否循环(NOCYCLE:不循环，CYCLE:循环)。
[CACHE n|NOCACHE]; 表示缓存序列的个数，以提高序列值的生成效率，数据库异常终止可能会导致序列中断不连续的情况，默认值为20，如果不使用缓存可设置NOCACHE
--NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。
--CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效。（1）创建序列
create sequence seq_emp_id
start with 1
increment by 1;（2）序列的伪列
序列支持两个伪列：
①NEXTVAL：获取序列下一个值，若是创建新的序列，那么第一次调用返回的是START WITH 指定的值，
以后每次调用都会的得到当前序列值加上步长后的数字，序列会根据序列最后生成的数字加上步进来得到。
NEXTVAL会导致序列发生步进，序列是不能回退的。
②CURRVAL：获取序列当前值，即：最后一次调用NEXTVAL后生成的数字。
CURRVAL不会导致步进。但是新创建的序列至少调用一次NEXTVAL生成一个值后才可以使用CURRVAL。
使用序列:序列名.NEXTVAL、序列名.CURRVAL
select seq_emp_id.nextval from dual;--返回一个值
select seq_emp_id.currval from dual;--获取当前值
--使用序列为emp表中新插入的数据提供主键字段的值
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(seq_emp_id.NEXTVAL,'JACK',3000,10);--序列的值是不能回退的。
INSERT INTO emp(empno,ename,sal,deptno)
VALUES(seq_emp_id.NEXTVAL,'ROSE',2300,20);
select * from emp;
desc user_sequences;
select SEQUENCE_NAME from user_sequences;--查看数字字典
select * from user_sequences;（3）删除序列
语法：DROP SEQUENCE sequenceName;
drop sequence seq_emp_id;二、索引
索引是数据库对象之一。通俗的来讲，索引在表中的作用，相当于书的目录对书的作用。
索引是加快查询效率的机制，快速定位我们需要查找的表的内容（物理位置），提高sql语句的执行性能。
当对一个建立索引的表的数据进行增删改的操作时，oracle会自动维护索引，使得其仍然能够更好的工作。
索引的建立以及应用是数据库自行完成的。
(注意：是对在表的某个字段加索引，LIKE模糊查询是不会应用到索引的)
语法：
CREATE [UNIQUE] INDEX index_name
ON table_name(column[,column...])
--UNIQUE表示唯一索引（1）单一索引和添加索引的原则
若将某个字段作为排序根据、去重或过滤条件，应当给这个字段添加索引以提高SQL的执行效率。
--对emp表ename字段添加索引（单一索引）
create index idx_emp_ename on emp(ename);（2）复合索引
复合索引，也叫多列索引，是基于多个列的索引。
如果经常在WHERE或GROUP BY 子句中使用多个字段作为过滤、分组和排序依据，
可以给这几个字段建立复合索引(建议在使用时多个字段的顺序与建立的索引一致)
create index idx_emp_job_sal on emp(job,sal);
--在查询时自动应用到索引 idx_hw_job_sal
select empno,ename,job,sal from emp order by job,sal;（3）函数索引
基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或表达式计算索引列的值。
--创建基于函数的索引
--如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引
create index idx_emp_ename_upper on emp(upper(ename));
--在做下面的查询时，会自动应用刚刚建立的索引
SELECT * FROM emp WHERE UPPER(ename)='KING';
--SELECT * FROM emp WHERE UPPER(ename) LIKE '%n%';模糊查询LIKE不会使用索引（4）修改索引和删除索引
如果经常在索引列上执行DML操作，导致索引指向不够准确，需要定期重建索引，提高索引的空间利用率
语法：ALTER INDEX index_name REBUILD;
alter index idx_emp_ename rebuild;
当表上有不合理的索引，会导致操作性能下降
删除索引的语法：DROP INDEX index_name;
drop index idx_emp_ename;
（5）使用索引的原则
合理使用索引来提升查询效率，索引的使用原则：
1.为经常出现在WHERE子句中创建索引。
2.为经常出现在ORDER BY,DISTINCT后面得字段建立索引，如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致。
3.为经常作为表的连接条件的列上添加索引。
4.不要在经常做DML操作的表上建立索引，会频繁的REBUILD。
5.不要在小表上（即数据量小的表）建立索引。
6.限制表上的索引数目，索引并不是越多越好。
7.删除很少被使用的、不合理的索引。
三、约束(CONSTRAINT)
全称是约束条件，也称完整性约束条件。
约束是数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行。
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。
约束的类型
约束条件包括：
1.非空约束： NOT NULL 简称NN
2.唯一性约束： UNIQUE 简称UN
3.主键约束： PRIMARY KEY 简称PK
4.外键约束： FOREIGN KEY 简称FK
5.检查约束： CHECK 简称CK
（1）非空约束：NOT NULL
非空约束用于确保字段值不为空。默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能取空值。
给某个字段添加非空约束，任何情况下不允许为空，即：
-当执行INSERT操作时，必须提供这个列的数据
-当执行UPDATE操作时，不能给这个列的值设为NULL
建表时给字段添加非空约束，语法：
CREATE TABLE table_name(
字段名 数据类型 NOT NULL,
字段名 数据类型 CONSTRAINT 约束名 NOT NULL (标准写法，约束名命名规则：tableName_colum_nn)
)
--建表时给字段添加非空约束
desc employees;
create table employees(
eid number(6),
name varchar(30) not null,
salary number(7,2),
hiredate date constraint employess_hiredate_nn not null
);
--可以在建表后，通过DDL修改表的定义，给字段添加非空约束
ALTER TABLE employees MODIFY(eid NUMBER(6) NOT NULL);
--或
alter table employees modify(eid number(6) constraint employees_eid_nn not null);
--取消表中字段非空约束,(在oracle的某些版本中，在取消字段的非空约束时可以不用指定NULL关键字，
--若指定了NULL都支持取消非空约束)
alter table employees modify(eid number(6) null);
desc employees;
--给employees表插入数据
INSERT INTO employees
(eid,name,salary,hiredate)
VALUES(1001,'张三',3000,SYSDATE);
SELECT * FROM employees;
--报错，给name字段添加非空约束后，不允许插入NULL值，即使在插入时未指定出该字段
INSERT INTO employees
(eid,salary,hiredate)
VALUES(1001,3000,SYSDATE);（2）唯一性约束：UNIQUE
唯一性约束要求该字段或者字段的组合不出现重复值。
给表的某个字段定义了唯一性约束，该字段的值不允许重复，NULL 除外。
唯一性约束可在建表时同时给字段定义，也可以在建表后建立。
建表时给字段添加唯一性约束，语法：
CREATE TABLE table_name(
字段名 数据类型 UNIQUE,
字段名xxx 数据类型,
CONSTRAINT 约束名 UNIQUE(字段名xxx) (标准写法，约束名命名规则：tableName_colum_uk)
)
*/
--建表时给字段添加唯一性约束
create table employees1(
eid number(6) unique,
name varchar2(30),
email varchar2(50),
salary number(7,2),
hiredate date,
constraint employees1_email_uq unique(email)
);
desc employees1;
INSERT INTO employees1 (eid,name,email) VALUES (1,'JACK','JACK@QQ.COM');
INSERT INTO employees1 (eid,name,email) VALUES (1,'JACK','JACK@QQ.COM');--报错，违反唯一约束条件
INSERT INTO employees1 (eid,name,email) VALUES (2,'JACK','JACK2@QQ.COM');
INSERT INTO employees1 (eid,name,email) VALUES (NULL,'JACK',NULL);--唯一性约束允许插入空值
select * from employees1;
DELETE FROM employees1 WHERE name='JACK';
--在建表后给字段添加唯一性约束(给字段添加唯一性约束时表的那个字段不能有重复的记录!!否则会报错)
--语法：ALTER TABLE table_name ADD CONSTRAINT 约束名 UNIQUE(字段名);
alter table employees1 add constraint employees1_name_uk unique(name);
INSERT INTO employees1 (eid,name,email) VALUES (1,'JACK','JACK@QQ.COM');
INSERT INTO employees1 (eid,name,email) VALUES (1,'JACK','JACK@QQ.COM');--报错，违反唯一约束条件 （3）主键约束：PRIMARY KEY
主键约束条件从功能上看相当于非空(NOT NULL)且唯一(UNIQUE)的结合。
主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
主键可以用来在表中唯一的确定一行数据
注意：一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制
--主键约束要求该字段的值非空且唯一
--主键约束只能在一张表的一个字段上建立。
--主键:使用该字段的值可以唯一定位表中的一条记录
--主键选取原则：
-- 1.应是对系统无意义的数据
-- 2.永远也不要更新主键，让主键除了唯一标识一行之外，再无其他用途。
-- 3.不应包含动态变化的数据，如时间戳
-- 4.主键应自动生成，不要认为干预，以免使它带有除了唯一标识一行以外的意义
-- 5.主键尽量建立在单列上。
--建表时给字段添加主键约束
CREATE TABLE employees2(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
DESC employees2;
INSERT INTO employees2 (eid,name) VALUES (1001,'JACK');
INSERT INTO employees2 (eid,name) VALUES (1001,'JACK');--报错，违反唯一约束条件
INSERT INTO employees2 (eid,name) VALUES (null,'JACK');--报错，无法将 NULL 插入 ("MEOW"."EMPLOYEES2"."EID")
SELECT * FROM employees2;
--在建表后给字段添加主键约束(给字段添加唯一主键束时表的那个字段的值不能有重复或为NULL的记录!!否则会报错)
--语法：ALTER TABLE table_name ADD CONSTRAINT 约束名 PRIMARY KEY(字段名);
--例如：ALTER TABLE employees2 ADD CONSTRAINT employees2_eid_pk PRIMARY KEY(eid);（4）外键约束：FOREIGN KEY
外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证
两个字段的关系。
例如：
dept表：主表或父表
emp表：从表或子表
若dept表中的deptno字段(PK主键约束)与emp表中的deptno字段(外键约束FK)，
emp表的deptno字段对应着dept表的deptno主键字段，则称emp的deptno字段为dept表的deptno主键字段的外键。
若添加外键约束，则emp表的外键字段的值必须对应deptno表的主键字段的值或者NULL值
表里的外键字段对应另一张表的主键字段
注意：若主表的主键字段有外键参照时，则无法删除记录
外键约束会降低性能（少用），即使是关联也不一定需要外键约束。
--语法：
--1.创建表时并创建外键约束
CREATE TABLE 从表(
字段名 数据类型 primary key,
CONSTRAINT 约束名 FOREIGN KEY(从表中作外键的字段名) REFERENCES 主表名称(主表中主键的字段名)
);
--2.表创建成功后再添加外键约束
ALTER TABLE 从表表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 主表名称(主键列名)
update emp set deptno=50 where ename='SCOTT';--违反完整约束条件（5）检查约束：CHECK
检查约束条件用来强制在字段上的每个值都要满足CHECK中定义的条件。
当定义了CHECK约束的列新增或修改数据时，数据必须符合CHECK约束中定义的条件。
--建表时给字段添加检查约束，语法：
CREATE TABLE table_name(
字段名 数据类型 CHECK(表达式),
salary NUMER(7,2) CHECK(salary>=0)
);
--建表时给字段添加检查约束
CREATE TABLE employees3(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2) CHECK (salary>=0),
hiredate DATE
);
INSERT INTO employees3 (eid,name,salary) VALUES (1001,'JACK',1000);
INSERT INTO employees3 (eid,name,salary) VALUES (1002,'ROSE',0);
--试图给salary插入一个小于0的数会报错，违反检查约束条件
INSERT INTO employees3 (eid,name,salary) VALUES (1002,'ROSE',-1);
--在建表后给字段添加检查约束
--ALTER TABLE table_name ADD CONSTRAINT 约束名 CHECK (表达式);
--如：ALTER TABLE employee3 ADD CONSTRAINT employee3_salary_ck CHECK(salary BETWEEN 2000 AND 4000);
SELECT * FROM employees3;
