筆記：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';