笔记：ORACLE数据库基础学习 第五天

--视图VIEW
--视图是数据库对象之一，在SQL语句中体现的角色与表一致
--但是视图并非一张真实存在的表，它只是一个查询语句对应
--的结果集。
CREATE VIEW v_emp_10_gl
AS
SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno=10

DESC v_emp_10_gl   --查看视图结构

SELECT *
FROM v_emp_10_gl

--视图对应的子查询中的字段可以指定别名，
--这样该试图对应的字段名就是这个别名，
--当一个字段是函数或者表达式，那么该字
--段必须指定别名
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10

--视图根据对应的子查询不同，分为简单视图和复杂视图
--简单视图：对应的子查询不含有函数，表达式，分组，去重，关联查询
--除了简单视图就是复杂视图

--简单视图可以进行DML操作，对该视图的操作就是对该试图
--数据来源的基础表进行的操作，复杂视图不允许进行DML操作
--对简单视图进行DML操作也不能违反基础表的约束条件。
--读视图进行DML操作，视图对基础表操作时，只能读视图可见
--的字段进行。
INSERT INTO v_emp_10_gl
(id,name,salary,deptno)
VALUES
(1001,'JACK',3000,10)

SELECT * FROM v_emp_10_gl
SELECT * FROM emp_gl

UPDATE v_emp_10_gl
SET salary=4000
WHERE id=1001

DELETE FROM v_emp_10_gl WHERE name='JACK'

--对视图的不当DML操作会污染基础表数据
--即：对试图进行DML操作后，试图对基础表对应数据
--进行该DML操作，但是操作后视图却对该记录不可见
INSERT INTO v_emp_10_gl
(id,name,salary,deptno)
VALUES
(1001,'JACK',3000,20)

SELECT * FROM emp_gl
SELECT * FROM v_emp_10_gl

UPDATE v_emp_10_gl
SET deptno =20

--DELETE 不会产生污染现象
DELETE FROM v_emp_10_gl
WHERE deptno=20

--为视图添加检查选项，可以避免对试图操作而导致
--的对基表的数据污染。
--WITH CHECK OPTION
--该选项要求对视图进行DML操作后，该记录必须对试图可见。
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10
WITH CHECK OPTION

--只读选项
--WITH READ ONLY
--只读选项要求对试图仅能进行查询操作
--不能进行任何DML操作
CREATE OR REPLACE VIEW v_emp_10_gl
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_gl
WHERE deptno=10
WITH READ ONLY 

--常用的数据库的数据字典
--USER_OBJECTS:记录用户创建过的所有对象
SELECT object_name,object_type FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%GL'

SELECT * FROM user_objects
WHERE object_name LIKE '%GL'

--USER_VIEWS:专门记录曾经创建过的试图信息
SELECT view_name,text
FROM user_views
WHERE view_name LIKE '%GL'

--USER_TABLES:专门记录曾经创建过的表的信息
SELECT table_name
FROM user_tables
WHERE table_name LIKE '%GL'

--删除视图
DROP VIEW v_emp_10_gl

--创建复杂视图

--创建一张试图，包含员工工资及相关部门信息
--包含：每个部门的平均工资，最大，最小，工资总和
--以及对应的部门名称，部门编号。
CREATE VIEW v_emp_salinfo_gl
AS
SELECT ROUND(AVG(e.sal),2) avg_sal,MAX(e.sal) max_sal,
       MIN(e.sal) min_sal,SUM(e.sal) total,
       e.deptno deptno,d.dname name,d.loc loc
FROM emp_gl e  JOIN dept_gl d
ON e.deptno=d.deptno
GROUP BY e.deptno,d.dname,d.loc

--查看哪些员工的工资高于其所在部门平均工资？
SELECT e.ename,e.sal,e.deptno
FROM emp_gl e,v_emp_salinfo_gl v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal

--序列SEQUENCE
--序列是数据库对象之一，作用是根据指定的规则生成
--一系列数字。通常使用序列生成的数字是为表中的
--主键字段提供值使用。
CREATE SEQUENCE seq_emp_id_gl
START WITH 1
INCREMENT BY 1

--序列支持两个伪列：
--NEXTVAL：获取序列的下一个数字，如果是新创建的序列
--那么会从START WITH开始返回。之后则是用上次生成的
--数字加上步长来得到本次生成的数字返回。
--需要注意：序列是不能后退的。并且不受事务控制。
SELECT seq_emp_id_gl.NEXTVAL
FROM dual

--CURRVAL：获取序列最后生成的数字，新创建的序列至少
--调用NEXTVAL生成一个数字后才可以使用。CURRVAL不会
--导致序列步进。
SELECT seq_emp_id_gl.CURRVAL
FROM dual

--使用序列为EMP表主键字段提供值：
INSERT INTO emp_gl
(empno,ename,job,sal,deptno)
VALUES 
(seq_emp_id_gl.NEXTVAL,'JACK','CLERK',3000,10)

SELECT * FROM emp_gl

--删除序列
DROP SEQUENCE seq_emp_id_gl

--序列的数据字典
SELECT * FROM user_sequences

--INDEX 索引
--索引是数据库对象之一，作用是提高查询效率
--索引的创建时是数据库自行完成的，并且数据库
--会在适当的时候自动使用索引。
CREATE  INDEX idx_emp_ename_gl
ON emp_gl(ename)

--经常出现在WHERE 中和ORDER BY中的字段要添加索引
--经常出现在DISTINGCT后面的字段也可以添加索引
--需要注意：对于字符串类型字段，若在WHERE中使用LIKE
--进行过滤时，是不会用到索引的。

CREATE INDEX idx_emp_job_sal_gl
ON emp_gl(job,sal)

CREATE INDEX idx_ename_upper_gl
ON emp_gl(UPPER(ename))

--重建索引
ALTER INDEX idx_emp_ename_gl REBUILD

--删除索引
DROP INDEX idx_emp_ename_gl

--建立非空约束
CREATE TABLE employees_gl(
  eid NUMBER(6),
  name VARCHAR2(30) NOT NULL,
  salary NUMBER(7,2),
  hiredate DATE 
    CONSTRAINT employees_gl_hiredate_nn NOT NULL
)

SELECT * FROM user_constraints
WHERE constraint_name='EMPLOYEES_GL_HIREDATE_NN'

DESC employees_gl

--修改时添加非空约束
ALTER TABLE employees_gl
MODIFY (eid NUMBER(6) NOT NULL)

--删除非空约束
ALTER TABLE employees_gl
MODIFY (eid NUMBER(6)NULL)

--添加唯一性约束
CREATE TABLE employees2_gl(
  eid NUMBER(6)UNIQUE,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE,
  CONSTRAINT employess2_gl_email_uk UNIQUE(email)
)
DESC employees2_gl

SELECT * FROM user_constraints
WHERE table_name ='EMPLOYEES2_GL'

INSERT INTO employees2_gl
(eid,name,email)
VALUES
(NULL,'JACK',NULL)

SELECT * FROM employees2_gl

DELETE FROM employees2_gl

--向已建表中添加唯一性约束
ALTER TABLE employees2_gl
ADD
CONSTRAINT employees2_gl_name_uk UNIQUE(name)


--建立主键约束
--一张表只能在一个字段定义主键约束，主键约束要求该字段非空且唯一
CREATE TABLE employees3_gl(
  eid NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30),
  email VARCHAR2(50),
  salary NUMBER(7,2),
  hiredate DATE
)

INSERT INTO employees3_gl
(eid,name,email)
VALUES
(1,'JACK','[email protected]')

--添加检查约束
ALTER TABLE employees3_gl
ADD
CONSTRAINT employees3_gl_salary_check CHECK(salary>2000)

INSERT INTO employees3_gl
(eid,name,salary)
VALUES
(2,'NOAH',2500)

SELECT * FROM employees3_gl--1:创建一个视图，包含20号部门的员工信息，
--  字段:empno,ename,sal,job,deptno
CREATE OR REPLACE VIEW v_emp_gl_20
AS
SELECT  empno,ename,sal,job,deptno
FROM emp_gl
WHERE deptno=20;

SELECT * FROM v_emp_gl_20;
--2:创建一个序列seq_emp_no,从10开始，步进为10
CREATE SEQUENCE seq_emp_gl_no
START WITH 10
INCREMENT BY 10;
--3:编写SQL语句查看seq_emp_no序列的下一个数字
SELECT seq_emp_gl_no.NEXTVAL
FROM dual;
--4:编写SQL语句查看seq_emp_no序列的当前数字
SELECT seq_emp_gl_no.CURRVAL
FROM dual;
--5:为emp表的ename字段添加索引:idx_emp_ename 
CREATE INDEX idx_emp_gl_ename
ON emp_gl(ename);
--6:为emp表的LOWER(ename)字段添加索引:idx_emp_lower_ename
CREATE INDEX idx_emp_gl_lower_ename
ON emp_gl(LOWER(ename));
--7:为emp表的sal,comm添加多列索引
CREATE INDEX idx_emp_gl_sal_comm
ON emp_gl(sal,comm);
--8:创建myemployee表，字段:
--  id NUMBER(4) ,
--  nameVARCHAR2(20),
--  birthday DATE,
--  telephone VARCHAR2(11)
--  scoreNUMBER(9,2)
--  其中id作为主键，name要求不能为空,telephone需要唯一,score值必须>=0
CREATE TABLE myemployee(
  id NUMBER(4),
  name VARCHAR2(20) CONSTRAINT myemployee_name_NN NOT NULL,
  birthday DATE,
  telephone VARCHAR2(11),
  score NUMBER(9,2),
  CONSTRAINT myemployee_id_pk PRIMARY KEY(id),
  CONSTRAINT myemployee_telephone_uk UNIQUE(telephone),
  CONSTRAINT myemployee_score_check CHECK(score>=0)  
)

SELECT * FROM user_constraints
WHERE table_name='MYEMPLOYEE';