数据库基础（五）

一、视图

视图也被称为虚表，是一组数据的逻辑表示，视图在SQL语句汇总跳线的样子与表一致，但不是表，只是对应于一条SELECT语句的查询结果集，结果集被赋予一个名字，通常前面加上_v
必须有权限才能创建视图，管理员可以通过

GRANT CREATE VIEW TO user_name

授予用户创建视图的权限

创建视图：

CREATE VIEW v_emp_10ASSELECT empno,name,sal,deptnoFROM empWHERE deptno = 10;
AS
SELECT empno,name,sal,deptno
FROM emp
WHERE deptno = 10;

创建或替换视图：

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,sal salary,deptnoFROM empWHERE deptno = 10;
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno = 10;

查看视图中的数据：

SELECT * FROM v_emp_10

视图中对应的查询语句的字段可以添加别名，这样视图中该字段就是用别名作为字段名,当视图的查询语句的字段含有函数或者表达式时，该字段必须使用别名。

根据视图所对应的子查询分类分为几种类型：

• 简单视图，基于单表建立，且不包含任何函数运算、表达式或分组函数
• 复杂视图，基于单表建立的，但包含函数、表达式等；基于多个表叫做连接视图，属于复杂视图的一种

视图的作用：

• 简化复杂查询
• 限制数据访问

对视图进行DML操作，实际上是对视图数据来源的基表进行操作

• 简单视图能够执行DML操作，但当在基表中定义了非空列，而简单视图对应的SELECT
  语句并没有包含这个非空列，导致这个非空列对视图不可见时，无法对视图执行INSERT操作
• 如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUN伪列，不允许进行DML操作
• DML操作不能违反基表的约束条件，视图看不到的字段全部插入默认值

通过视图插入一条视图看不到的数据，会对基表产生”污染”

INSERT v_emp_10 VALUES(2012,1,23,78.5)

修改也可能对视图产生”污染”

UPDATE v_emp_10 SET year_id=2011 WHERE month_id=11 AND day_id=8

删除只能对视图看得到的数据进行，不会产生”污染”

DELETE FROM v_emp_10 WHERE deptno=20

添加WITH CHECK OPTION后，视图可以对进行DML操作的数据进行检查，确保插入的数据必须视图可见，修改的数据必须保证修改后视图对其仍然可见才可以操作，避免对基表产生”污染”。

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,sal salary,deptnoFROM empWHERE deptno = 10WITH CHECK OPTION;
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno = 10
WITH CHECK OPTION;

添加WITH READ ONLY后，视图不允许DML操作

CREATE OR REPLACE VIEW v_emp_10ASSELECT empno id,ename name,sal salary,deptnoFROM empWHERE deptno = 10WITH READ ONLY;
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp
WHERE deptno = 10
WITH READ ONLY;

和视图相关的数据字典：

• USER_OBJECTS 记录所有创建的数据库对象

  SELECT object_name FROM user_objects WHERE object_type = ‘VIEW'

• USER_VIEWS 记录所有创建过的视图

  SELECT text FROM user_views WHERE view_name = ‘v_emp_10'

• USER_TABLES 记录所有创建过的表

  SELECT table_name FROM user_tables

• USER_UPDATE_COLUMNS 记录所有的列

  SELECT column_name,insertable,updatable,deletable
  FROM user_update_columns
  WHERE table_name = ‘v_emp_10'

删除视图不会导致基表数据的丢失，不会影响基表数据

DROP VIEW view_name

二、序列

序列是一个数据库对象，可以根据指定的步进方式提供一组数字。通常使用序列生成数字的目的是为了数据库某张表的主键提供值使用。通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值

CREATE SEQUENCE seq_emp_empnoSTART WITH 100INCREMENT BY 10;
START WITH 100
INCREMENT BY 10;

序列提供了两个伪列，用于序列获取对应的数字：

• NEXTVAL：获取序列下一个数字
  第一次使用序列时，改伪列获取的是START WITH指定的数字，之后则是用当前数字加上步长得到的。
• CURRVAL：获取序列当前数字
  CURRVAL 必须在序列创建后，至少使用NEXTVAL生成过一个数字后才可以使用

SELECT seq_emp_empno.NEXTVAL FROM dualSELECT seq_emp_empno.CURRVAL FROM dual
SELECT seq_emp_empno.CURRVAL FROM dual

使用序列生成数字作为主键的值

INSERT INTO emp(empno,name,sal,job,deptno)VALUES(seq_emp_empno.NEXTVAL,’JACKSON’,5000,’CLERK’,30)
(empno,name,sal,job,deptno)
VALUES
(seq_emp_empno.NEXTVAL,’JACKSON’,5000,’CLERK’,30)

UUID也是作为主键生成方式的一种，UUID是一个不重复的32位字符串

SELECT SYS_GUID() FROM dual

三、索引

索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率、排序效率、分组效率而引入，是独立于表的对象，可以存放在与表不同的表空间中

索引会被自动套用，索引也是数据库自行维护的，我们唯一需要关心的就是是否添加索引

CREATE [UNIQUE] INDEX index_name ON table(column[,column...])

• index_name 表示索引名称
• table 表示表名
• column 表示表名，可以建立单列索引或复合索引
• UNIQUE 表示唯一索引

CREATE INDEX idx_emp_ename ON emp(ename);
CREATE INDEX idx_emp_job_sal ON emp(job,sal);
CREATE INDEX idx_emp_ename ON emp(UPPER(name));

如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率

ALTER INDEX idx_emp_ename REBUILD

当表上有一个不合理的索引时，会导致操作性能下降，这时需要删除索引

DROP INDEX idx_emp_ename

合理使用索引提升查询效率

• 为经常出现在WHERE子句中的列创建索引
• 为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
• 为经常作为表的连接条件的列上创建索引
• 不要在经常做DML操作的表上建立索引
• 不要在小表上建立索引
• 限制表上的索引数目，索引并不是越多越好
• 删除很少使用的、不合理的索引

四、约束

约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，否则不能执行

约束条件包括：

• 非空约束（NOT Null）,简称NN
• 唯一性约束（Unique），简称UK
• 主键约束（Primary Key），简称PK
• 外键约束（Foreign Key），简称FK
• 检查约束（Check），简称CK
  
  

(1)非空约束
建表时添加非空约束

CREATE TABLE emp (eid NUMBER(6),name VARCHAR2(30) NOT NULL，email VARCHAR2(50),hiredate DATE CONSTRAINT emp_hiredate_nn NOT NULL)
eid NUMBER(6),
name VARCHAR2(30) NOT NULL，
email VARCHAR2(50),
hiredate DATE CONSTRAINT emp_hiredate_nn NOT NULL
)

建表后添加非空约束

ALTER amp MODIFY (did NUMBER(6) not null);

取消非空约束

ALTER amp MODIFY (did NUMBER(6) null);

(2)唯一性约束
唯一性约束用于保证字段或者字段的组合不出现重复值
当给表的某个值定义了唯一约束条件，该列的值不允许重复，但允许是NULL值

建表时添加唯一性约束

CREATE TABLE emp (
eid NUMBER(6) UNIQUE,
name VARCHAR2(30)，
email VARCHAR2(50),
CONSTRAINT emp_email_uk UNIQUE(email)
)

建表后添加唯一性约束，只有列中没有重复值时才能成功

ALTER TABLE emp ADD CONSTRAINT emp_name_uk UNIQUE(name);

(3)主键约束
主键约束条件从功能上看相当于非空且唯一的组合
一张表只允许简历一个主键，而其它约束则没有明确的个数限制

主键选取的条件：

• 主键应市对系统无意义的数据
• 永远也不要更新主键，让那个之间除了唯一标识一行外，再无其他用途
• 主键不应该包含动态变化的数据，如时间戳
• 主键应自动生成，不要认为干预，以免使它带有除了巍亿标识一行以外的意义
• 主键尽量建立在单列上

建表时添加主键约束

CREATE TABLE emp (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30)，
email VARCHAR2(50)
)

建表后创建主键约束

ALTER TABLE emp ADD CONSTRAINT emp_eid_pk PRIMARY KEY (eid);

(4)外键约束
外键约束定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系
一张表保存的是另一张表主键的值

外键约束包括两个方面的数据约束：

• 从表上的外键的列值，必须从主表被参照的列值中选取，或者为NULL
• 当主表参照列的值被从表参照时，主表的该行记录不允许被删除

外键约束对性能的降低

• 每次DML操作豆浆导致数据库自动对外键所关联的对应表做检查，会产生开销
• 外键确定了主从表的先后生成关系，有时会影响业务逻辑

关联不一定需要外键约束

ALTER TABLE empADD CONSTRAINT emp_deptno_fkFOREIGN KEY(deptno) REFERENCES dept(deptno);
ADD CONSTRAINT emp_deptno_fk
FOREIGN KEY(deptno) REFERENCES dept(deptno);

(5)检查约束
检查约束用来强制要求在字段上的每个值都要满足Check中定义的条件

ALTER TABLE empADD CONSTRAINT amp_salary_checkCHECK(salary > 2000);
ADD CONSTRAINT amp_salary_check
CHECK(salary > 2000);