【Oracle】day05_视图_序列_索引_约束

【Oracle】day05_视图_序列_索引_约束

1.视图

也称为虚表(虚拟的表),视图在SQL语句中体现的地方和表一致,但是其不是数据库中真正存在的表,而是一个SELECT语句的查询结果集,所以视图中有哪些字段,对应的都有哪些记录完全取决于SELECT查询的结果.

eg:

CREATE VIEW v_emp_10
AS
SELECT ename,empno,sal,deptno
FROM emp
WHERE deptno=10;
DESC v_emp_10;

1)当我们通过SELECT语句查询视图时,数据库会先使用该视图对应的SELECT语句将数据查询出来,然后将这个结果集当作一张表,再进行我们的SELECT语句,将要查询的数据查询出来.所以,视图就是在FROM中使用子查询.
eg:SELECT * FROM v_emp_10;

2)视图中对应的子查询中,查询的字段若含有表达式函数,那么必须要添加别名.单纯的字段也可以添加别名.这样创建出来的视图对应的字段名就是该别名.

( OR REPLACE 的作用是如果视图不存在,则创建,存在则替换)

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT ename name,empno id,sal
FROM emp
WHERE deptno=20;
DESC v_emp_10;

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT deptno no,COUNT(*) num
FROM emp
GROUP BY deptno;
DESC v_emp_10;

3)视图的作用：简化复杂查询,限制数据访问.

4)对视图进行DML操作只能对简单视图进行.

对视图进行DML操作就是对视图数据来源的基表进行的.

--INSERT

向视图插入数据,数据实际上是插入视图来源的基表中,而且,新增的记录只有视图看得到的字段会有值,其他字段插入默认值.

视图有可能插入一条视图自己看不见的数据,这样会对基表产生数据污染.

INSERT INTO v_emp_10
VALUES('QQQ',1001,6000);
SELECT * FROM emp;

--UPDATE

更新视图数据,就是更新基表的数据

与INSERT一样,更新视图数据有可能导致视图无法再次查询到该数据！

--DELETE

DELETE视图中的数据就是删除表中对应数据,但是删除只能删除视图看得到的数据.

--WITH CHECK OPTION 检查选项

当视图添加该选项后,
对视图进行INSERT：必须插入的数据视图可见

对视图进行UPDATE：必须更新后视图对其可见

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal,deptno
FROM emp
WHERE deptno=10
WITH CHECK OPTION;
SELECT * FROM v_emp_10;
INSERT INTO v_emp_10_qxl
VALUES(1001,'AAAA',2000,20);--错误
UPDATE v_emp_10
SET deptno=20;

--WITH READ ONLY 选项

视图就不允许进行DML操作了.(和WITH CHECK OPTION 只能写其一)

CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id,ename name,sal,deptno
FROM emp
WHERE deptno=10
WITH READ ONLY;
SELECT * FROM v_emp_10_qxl;
INSERT INTO v_emp_10_qxl
VALUES(1001,'AAAA',2000,10);--错误 此处不允许虚拟列
UPDATE v_emp_10_qxl
SET deptno=20;

5)数据字典

查看数据字典,得知用户创建过的所有数据库对象

SELECT object_name,object_type
FROM user_objects;

只看视图,可以查看视图的数据字典

SELECT text,view_name
FROM user_views;

只看表

SELECT *
FROM user_tables;

6)定义复杂视图

--定义一个可以查看各部门薪资情况的视图

CREATE VIEW v_emp_salary_qxl
AS
SELECT d.dname,AVG(e.sal) avg_sal,SUM(e.sal) sum_sal,
       MAX(e.sal) max_sal,MIN(e.sal) min_sal
FROM emp e JOIN dept d
ON e.deptno=d.deptno
GROUP BY d.dname;
SELECT * FROM v_emp_salary;

复杂视图不允许DML操作

7）删除视图

DROP VIEW view_name;
DROP VIEW v_emp_10_qxl;
SELECT * FROM emp;

2.序列

作用：根据设定的方式,生成一组数字.
通常序列被用来为表中的主键字段提供值.
语法：
CREATE SEQUENCE [schema.]sequence_name
    [ START WITH i ] [ INCREMENT BY j ]
    [ MAXVALUE m | NOMAXVALUE ]
    [ MINVALUE n | NOMINVALUE ]
    [ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]
eg:
CREATE SEQUENCE seq_emp_id_qxl
START WITH 100 INCREMENT BY 10;

序列有两个伪列：

-NEXTVAL：向序列要下一个数字

若序列还有没生成过数字,则第一次获取的数字为START WITH指定的数字,否则是用之前生成过的数字加上步长得到的.一旦获取了下一个数字,就无法再通过序列获取之前生成的数字了！

-CURRVAL：向序列要最后一次生成的数字

该伪列可以调用若干次,每次返回的数字一样,并不会导致序列获取下一个值,除非使用NEXTVAL.

需要注意,序列刚创建完毕后,至少要调用NEXTVAL一次生成数字后才可以使用该伪列.

SELECT seq_emp_id_qxl.NEXTVAL FROM dual;
SELECT seq_emp_id_qxl.CURRVAL FROM dual;

INSERT INTO emp_qxl
(empno,ename,sal)
VALUES(seq_emp_id_qxl.NEXTVAL,'JACK',5000);

SELECT * FROM emp_qxl;

删除序列
语法：
DROP SEQUENCE seq_name;
eg:
DROP SEQUENCE seq_emp_id_qxl;

3.索引 --提高表的查询效率

CREATE INDEX idx_emp_qxl_ename_qxl ON emp(ename);

索引的算法与应用完全是数据库自行决定.

--复合索引
CREATE INDEX idx_emp_qxl_job_sal ON emp_qxl(job,sal);

--基于函数的索引
CREATE INDEX idx_emp_qxl_upperename_qxl ON emp_qxl(UPPER(ename));

--重建索引 (如果经常在索引列上执行DML操作,需要定期重构索引)
ALTER INDEX idx_emp_qxl_ename_qxl REBUILD;

--删除索引
DROP INDEX idx_emp_qxl_ename_qxl;

原则：
1)不要在小表(数据量比较小)上建立索引
2)为经常出现在WHERE子句中的列创建索引
3)为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引
  的字段顺序要和这些关键字后面的字段顺序一致
4)为经常作为表的连接条件的列上创建索引
5)不要在经常做DML操作的表上建立索引
6)限制表上的索引数目，索引并不是越多越好
7)删除很少被使用的、不合理的索引

4.约束

1)NOT NULL(NN) 非空约束 列级约束

eg:
CREATE TABLE employee_qxl(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE CONSTRAINT employee_qxl_hiredate_nn NOT NULL
);
DESC employee_qxl;

--在建表之后修改表的定义添加非空约束
ALTER TABLE employee_qxl
MODIFY(eid NUMBER(6) NOT NULL);
DESC employee_qxl;

--取消非空约束
ALTER TABLE employee_qxl
MODIFY(eid NUMBER(6) NULL);
DESC employee_qxl;

2)UNIQUE (UK) 唯一约束(除NULL之外不能有重复的)
eg:
CREATE TABLE employee1_qxl(
eid NUMBER(6) UNIQUE,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employee1_qxl_email_uk UNIQUE(email)
);

INSERT INTO employee1_qxl
(eid,name) VALUES(NULL,'rose');
SELECT * FROM employee1_qxl;

--在建表之后增加唯一性约束条件
ALTER TABLE employee1_qxl
ADD CONSTRAINT employee1_qxl_name_uk UNIQUE(hiredate);

3)PRIMARY KEY (PK) 主键约束 (一张表只能有一个)
主键选取的原则:
-主键应是对系统无意义的数据
-永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
-主键不应包含动态变化的数据，如时间戳
-主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
-主键尽量建立在单列上

--添加主键约束：
CREATE TABLE employee2_qxl(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
DESC employee2_qxl;

--建表后添加主键约束
CREATE TABLE employee3_qxl(
eid NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);
DESC employee3_qxl;
ALTER TABLE employee3_qxl
ADD CONSTRAINT employee3_qxl_eid_pk PRIMARY KEY(eid);

4)外键约束
-外键约束条件包括两个方面的数据约束:
 从表上定义的外键的列值,必须从主表被参照的列值中选取,或者为NULL;
 当主表参照列的值被从表参照时,主表的该行记录不允许被删除.

5)检查约束 CHECK
--添加检查约束
ALTER TABLE employee3_qxl
ADD CONSTRAINT employee3_salary_check CHECK(salary BETWEEN 1000 AND 2500);
INSERT INTO employee3_qxl
(eid,salary) VALUES(2,1000);--错误 违反检查约束条件
SELECT * FROM employee3_qxl;