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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步