Oracle數(shù)據(jù)庫體系結(jié)構(gòu)簡介

Oracle實例：是存在于物理內(nèi)存上的一種數(shù)據(jù)結(jié)構(gòu)，用來管理和訪問Oracle數(shù)據(jù)庫，由系統(tǒng)提供的多個后臺進程和一個共享內(nèi)存池組成，共享的內(nèi)存池可以被所有進程訪問

實際上oracle實例也就是平常所說的數(shù)據(jù)庫服務(wù)

區(qū)別：實例可以操作任何數(shù)據(jù)庫，任何時刻一個實例只能關(guān)聯(lián)一個數(shù)據(jù)庫；但是一個數(shù)據(jù)庫可以被多個實例同時操作【RAC】

在雙十一：同款商品可以被多個用戶訪問；單個用戶只能操作一件商品

-- 插入 sql 文件
@d:/del_data.sql;
@d:/hr_cre.sql;
@d:/hr_popul.sql;

SQL語句主要分為三大類

種類	含義
DML（Data Manipulation Language）	數(shù)據(jù)庫操縱語言：修改和查詢數(shù)據(jù)記錄INSERT，DELETE，UPDATE，SELECT
DDL（Data Manipulation Language）	數(shù)據(jù)庫定義語言：定義數(shù)據(jù)庫的結(jié)構(gòu)，創(chuàng)建，修改和刪除CREATE TABLE，CREATE INDEX，DROP TABLE，DROP INDEX，ALTER TABLE（修改表結(jié)構(gòu)，添加、刪除和修改列長度）
DCL（Data Control Language）	數(shù)據(jù)庫控制語言：用來控制數(shù)據(jù)庫的訪問，GRANT授權(quán)，REVOKE撤銷授權(quán)，COMMIT提交事務(wù)，ROLLBACK事務(wù)回退，SAVEPOINT設(shè)置保存點，LOCK鎖定特定部分數(shù)據(jù)庫

補充SQL初步

--查詢表結(jié)構(gòu)
desc employees;

算術(shù)運算符作用于時間（時間只能加減不能乘除）和數(shù)字

--查詢前天，今天和明天日期
select sysdate-1, sysdate, sysdate+1
from dual;

NULL值：無效，為指定的值（并不是空格或者0）。凡是NULL值參與了運算，結(jié)果都是NULL

導入sql文件

@d:/xxx.sql

別名

--別名使用雙引號，字符串是單引號
SELECT sysdate-1 AS "昨天", sysdate AS "今天", sysdate+1 AS "明天"
FROM dual

需要注意的是oracle中字符串是單引號，別名用的是雙引號

連接符

和MySQL的 CONCAT(str1, str2)不同的是把列于列，列與字符連接在一起用 || 表示合成列

--oracle中拼接字符串時 "||"
SELECT EMPLOYEE_NAME || '`s ID is:' || EMPLOYEE_ID
FROM employee

distinct去重的坑

--查詢出的字段需要數(shù)量上相對
SELECT last_name, distinct(department_id)
FROM employee

報錯原因是 last_name 需要和 去重后的部門ID 數(shù)量相對應(yīng)，否則就是表達式缺失

過濾和排序數(shù)據(jù)

WHERE 子句緊跟隨在 FROM 后邊

日期格式

• 字符和日期要包含在單引號中
• 字符大小寫敏感，日期格式敏感
• 默認的日期格式是 DD-MON月-RR

--查詢的日期格式
SELECT last_name, hire_date FROM employees WHERE hire_date = '7-6-1994'
--查詢出的hire_date字段數(shù)據(jù)格式：1994/6/7 星期
SELECT last_name, hire_date FROM employees WHERE to_char(hire_date, 'yyyy-mm-dd') = '1994-06-07'

比較運算

操作符	含義
<	小于
<=	小于等于
=	等于
>=	大于等于
>	大于
!=	不等于，也可以是<>

其它比較運算符

操作符	含義
BETWEEN…AND…	閉區(qū)間內(nèi)的值
INT(SET)	等于值列表中的一個
LIKE	模糊查詢
IS NULL	空值
IS NOT NULL	非空值

--模糊查詢過程中轉(zhuǎn)義字符問題：查詢名字中含有"_"的員工
SELECT last_name, department_id, salary
FROM employees
WHERE last_name LIKE '%\_%a' EXCAPE ('\')

邏輯運算

操作符	含義
AND	并
OR	或
NOT	否

優(yōu)先級

優(yōu)先級
1	算術(shù)運算符
2	連接運算符
3	比較運算符
4	IS [NOT] NULL，IN，LIKE
5	[NOT] BETWEEN…AND…
6	NOT
7	AND
8	OR

排序

• 默認生升序asc，降序desc
• 一級排序相同則會自動跳到二級排序

單行函數(shù)

SQL中不同類型的函數(shù)

單行函數(shù)

字符

大小寫控制函數(shù)

--小寫和大寫
SELECT LOWER('hand-china.COM') AS "LOWER小寫", UPPER('hand-china.COM') AS "UPPER大寫", INITCAP('hand-china.COM') AS "INITCAP大駝峰"
FROM dual

字符控制函數(shù)

函數(shù)	結(jié)果
CONCAT(‘Hello’, ‘World’)	HelloWorld
SUBSTR(‘HelloWorld’, 2, 4)	ello
LENGTH(‘HelloWorld’)	10
INSTR(‘HelloWorld’, ‘W’)	6
LPAD(salary, 10, ‘*’)	******7500
RPAD(salary, 10, ‘*’)	7500******
TRIM(‘H’ FROM ‘HelloHWorldH’)	elloHWOrld
REPLACE(‘a(chǎn)bcdab’, ‘b’, ‘m’)	amcdam

數(shù)值

--日期或者數(shù)字進行截斷
SELECT TRUNC(435.45, 1), TRUNC(435.45), TRUNC(435.45, -1)
435.4	435	   430

日期

--兩個日期之間相差的天數(shù)
SELECT "title", "level", SYSDATE-"create_time" AS "WorkedDays" FROM cxf_44569;
--兩個日期之間相差的月數(shù)
SELECT "title", "level", （SYSDATE-"create_time")/30 AS "WorkedDays1", MONTHS_BETWEEN(SYSDATE, "create_time")  AS "WOrkedDays2" FROM cxf_44569;
--指定月數(shù)加上月數(shù)，指定日期下一個星期對應(yīng)的日期
SELECT ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -3), NEXT_DAY(SYSDATE, '星期六') FROM dual;
--每個月的倒數(shù)第二天創(chuàng)建的題目信息
SELECT * FROM cxf_44569 WHERE "create_time" = LAST_DAY("create_time")-1;
--日期的四舍五入和截斷
SELECT SYSDATE, ROUND(SYSDATE, 'MONTH'), ROUND(SYSDATE, 'MM'), TRUNC(SYSDATE, 'HH') FROM dual;

轉(zhuǎn)換

TO_CHAR 和 TO_DATE

--日期轉(zhuǎn)字符串
SELECT "title", TO_CHAR("create_time", 'yyyy"年"mm"月"dd"日"')
FROM cxf_44569
WHERE '2023年08月30日' != TO_CHAR("create_time", 'yyyy"年"mm"月"dd"日"')
--字符串轉(zhuǎn)日期
SELECT "title", TO_CHAR("create_time", 'yyyy"年"mm"月"dd"日"')
FROM cxf_44569
WHERE "create_time" != TO_DATE('2023年08月31日', 'yyyy"年"mm"月"dd"日"')

TO_CHAR 和 TO_NUMBER

--按照數(shù)據(jù)本地貨幣格式查詢
SELECT TO_NUMBER('￥001,234,567.89', 'L000,000,999.99')
FROM dual

貨幣類型符號需要相對應(yīng)

通用

適用于任何數(shù)據(jù)類型，同時也適用于空值

• NVL(expr1, expr2)：expr1為NULL，則返回expr2
• NVL2(expr1, expr2, expr3)：expr1不為NULL則返回expr2；否則返回expr3
• NULLIF(expr1, expr2)：如果expr1和expr2相等，則返回NULL；否則返回expr1
• COALESCE(expr1, expr2, …exprN)：如果第一個表達式為NULL則返回第二個；如果第二個還為NULL，則返回第三個

--NUL查詢獎金提成后的工資
SELECT employee_id, last_name, salary*12(1+NVL(commission_pct, 0)) FROM employees

使用條件表達式

--CASE xxx WHEN xxx END
SELECt employee_id, last_name, department_id, CASE department_id WHEN 10 THEN salary*1.1,WHEN 20 THEN salary*1.2,ELSE THEN salary*1.3 END AS "New_Salary"
FROM employee
WHERE department_id IN(10, 20, 30)--DECODE()
SELECt employee_id, last_name, department_id, DECODE(department_id, 10, salary*1.1,20, salary*1.2,30, salary*1.3) AS "NewSalary"
FROM employees
WHERE department_id IN(10, 20, 30)

嵌套查詢

• 單行函數(shù)可以嵌套查詢
• 嵌套函數(shù)的執(zhí)行順序是先內(nèi)后外

多表查詢

等值連接

兩個表中的數(shù)據(jù)有有交集

--查詢員工信息，部門信息，地址信息
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id

非等值連接

兩個表中的數(shù)據(jù)互不交集

--查詢運功信息和工作信息【兩個表沒有關(guān)聯(lián)關(guān)系】
SELECT e.employee_id, e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal
AND j.highest_sal

左外連接

--查詢員工信息和部門信息
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
--查詢員工信息和部門信息
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
WHERE e.department_id = d.department_id

滿外連接

-- 有個員工沒有部門ID，但滿外連接也會把該員顯示出來
SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL JOIN departments d
WHERE e.department_id = d.department_id

自連接

--查詢員工及員工老板的信息
SELECT emp.last_name, manaer.last_name, manager.salary, manager.email
FROM employee emp, employee manager
WHERE emp.manager_id = manager.employee_id
AND TO_CHAR(emp.last_name) = 'chen'

聚合分組函數(shù)

SUM，AVG，MIN，MAX在統(tǒng)計字段的時候自動會舍去NULL值

--查詢各個部門不同崗位的平均工資
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id
--查詢各部門平均工資大于6k的部門和部門平均工資
SELECT department_id, avg(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000

• 如果要使用 WHERE 的話，需要緊跟 FROM 之后
• 包含在 SELECT 中的一定要出現(xiàn)在 GROUP BY 之后；出現(xiàn)在GROUP BY 之后的不一定需要出現(xiàn)在 SELECT 中
• WHERE 中不能使用聚合函數(shù)；HAVING 中可以使用聚合函數(shù)

子查詢

--查詢job_id與141號員工相同，但salary比143號員工多的員工信息
SELECT last_name, employee_id, salary
FROM employeee
WHERE job_id = (SELECT job_id FROM employee WHERE employee_id = 141) AND salary > (SELECT salary FROM employee FROM employee WHERE employee_id = 143)

--查詢部門中工資比本部門平均工資高的員工號，姓名和工資
SELECT employee_id, last_name, salary
FROM employee e1
WHERE salary > (SELECT AVG(salary)FROM employee e2WHERE e1.employee_id = e2.employee_idGROUP BY department_id)

創(chuàng)建和管理表

--查詢用戶定義的表
SELECT * FROM user_tables
SELECT table_name FROM user_tables
--查詢用戶定義的數(shù)據(jù)對象
SELECT * FROM user_objects
SELECT DISTINCT object_type FROM user_objects
--查詢用戶定義的表，視圖，同義詞和序列
SELECT * FROM user_catalog

創(chuàng)建表

--白手起家
CREATE TABLE emp1 ( id NUMBER ( 10 ), name VARCHAR2 ( 25 ), hire_date TIMESTAMP, salary NUMBER ( 10, 2 ) )
--復制表結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE emp2 AS SELECT
id,
name AS "n a m e",
hire_date,
salary 
FROMemp1;
--復制表結(jié)構(gòu)
CREATE TABLE emp3 AS SELECT
id,
name AS "name",
hire_date,
salary 
FROMemp1 
WHERE1 = 2;

--白手起家
CREATE TABLE emp1 ( id NUMBER ( 10 ), name VARCHAR2 ( 25 ), hire_date TIMESTAMP, salary NUMBER ( 10, 2 ) )
--復制表結(jié)構(gòu)+數(shù)據(jù)
CREATE TABLE emp2 AS SELECT
id,
name AS "n a m e",
hire_date,
salary 
FROMemp1;
--復制表結(jié)構(gòu)
CREATE TABLE emp3 AS SELECT
id,
name AS "name",
hire_date,
salary 
FROMemp1 
WHERE1 = 2;
--新增一字段
ALTER TABLE emp1 ADD ( email VARCHAR ( 20 ) DEFAULT '無' );
--修改一個字段
ALTER TABLE emp1 MODIFY ( id NUMBER ( 15 ) );
--刪除一個列
ALTER TABLE emp1 DROP email;
--重命名一個列
ALTER TABLE emp1 RENAME COLUMN sal TO "s a l";
--刪除一個表
DROP TABLE emp3;
--清空表數(shù)據(jù)，但表結(jié)構(gòu)還在【增刪改可以回滾：DELETE可以回滾】
TRUNCATE TABLE emp2;
DELETE FROM emp2;
--修改表名
RENAME emp2 TO employees2;

數(shù)據(jù)處理

--插入一條數(shù)據(jù)
INSERT INTO emp1 ( id, name, hire_date, sal )
VALUES( 10001, '張三', SYSDATE, 10000 );
--從其它地方拷貝數(shù)據(jù)進行復制【不用 VALUES但需要子查詢中的值列表與 INSERT 子句中的列表對應(yīng)】
INSERT INTO emp1 ( id, name, hire_date, sal ) SELECT
id + 1,
name,
hire_date,
sal 
FROMemp1;
--更改數(shù)據(jù)：修改114號員工的工作和工資和205號員工相等
UPDATE emp1 
SET job_id = ( SELECT job_id FROM emp1 WHERE employee_id = 205 ) 
AND salary = ( SELECT salary FROM emp1 WHERE employee_id = 205 ) 
WHEREemployee_id = 114;
--刪除部門名稱含有Public字符的部門ID
DELETE 
FROMemp1 
WHEREdepartment_id = ( SELECT department_id FROM emp1 WHERE department_name LIKE '%Public%' );
--控制事務(wù)
DELETE FROM emp1 WHERE id = 1;
SAVEPOINT A;
DELETE FROM emp1 WHERE id=2;
SAVEPOINT B;
ROLLBACK TO SAVEPOINT A;
ROLLBACK;

約束

NOT NULL

CREATE TABLE emp
(id NUMBER ( 10 ) CONSTRAINT emp_id_pk PRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp_name_nn NOT NULL,salary NUMBER ( 10, 2 ),email VARCHAR2(20),CONSTRAINT emp_email_uk UNIQUE(email)
);

列約束和表約束

CREATE TABLE emp2
(--列級約束id NUMBER ( 10 ) CONSTRAINT emp2_id_pk FPRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp2_name_nn NOT NULL,salary NUMBER ( 10, 2 ) CONSTRAINT emp2_salary_ CHECK ( salary > 1500 AND salary < 30000 ) NOT NULL,email VARCHAR ( 20 ),deparment_id NUMBER ( 10 )--表級約束CONSTRAINT emp2_email_uk UNIQUE ( email )--唯一約束--主鍵約束-- CONSTRAINT emp2_id_pk FPRIMARY KEY(id),--外鍵約束CONSTRAINT emp2_dept_id_fk FOREIGN KEY ( department_id ) REFERENCES departments ( department_id ) ON DELETE CASCADE --級聯(lián)刪除-- CONSTRAINT emp2_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE SET NULL--級聯(lián)置空
);

• 對于 UNIQUE 的NOT NULL約束，則允許插入的數(shù)據(jù)為NULL不會違反唯一值約束
• 對于 NOT NULL 只能添加在列級上

在創(chuàng)建表級外鍵約束的時候需要添加上 FOREIGN KEY 關(guān)鍵字

--列級約束
department NUMBER(10) CONSTRAINT emp2_dpt_id_fk REFERENCES dpt2(department_id)
--表級約束
CONSTRAINT FOREIGN KEY emp2_dpt_id_fk REFERENCES dpt2(department_id)

添加或刪除約束

--NULL約束只能用MODIFY修改
ALTER TABLE emp2 MODIFY(sal NUMBER(10,2, NOT NULL));
--刪除一個約束
ALTER TABLE emp2 DROP CONSTRAINT emp2_name_uk;
--添加一個約束【添加之前需要把符合當前數(shù)據(jù)】
ALTER TABLE emp2 ADD CONSTRAINT emp2_name_uk UNIQUE(name);
--無效化一個約束
ALTER TABLE emp2 DISABLE CONSTRAINT emp2_nmae_uk;
--有效化一個約束
ALTER TABLE emp2 ENABLE CONSTRAINT emp2_name_uk;
--查詢約束
SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name = 'emp2';
--查詢定義約束的列
SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'emp2';

視圖

視圖基礎(chǔ)

--創(chuàng)建數(shù)據(jù)庫
CREATE TABLE emp
( id NUMBER ( 10 ) CONSTRAINT emp_id_pk PRIMARY KEY,name VARCHAR2 ( 20 ) CONSTRAINT emp_name_nn NOT NULL,salary NUMBER ( 10, 2 ),email VARCHAR2(30),CONSTRAINT emp_email_uk UNIQUE(email)
);
--插入數(shù)據(jù)
INSERT INTO emp ( id, name, salary, email ) VALUES ( 1, '張三', 6000, 'zhangsan@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 2, '李四', 7500, 'lisi@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 3, '王五', 9000, 'wangwu@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 4, '老六', 4500, 'laoliu@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 5, '小七', 5500, 'xiaoqi@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 6, '甲', 3500, 'jia@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 7, '乙', 6500, 'yi@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 8, '丙', 9500, 'bing@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 9, '丁', 3500, 'ding@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 10, '戊', 1500, 'wu@qq.com' );
INSERT INTO emp ( id, name, salary, email ) VALUES ( 11, '戎', 2500, 'rong@qq.com' );
--創(chuàng)建視圖
CREATE VIEW emp_view AS
SELECT id, name, salary, email FROM emp;
--查詢視圖
SELECT * FROM emp_view;
--查詢視圖
SELECT * FROM emp_view;
--查詢表【發(fā)現(xiàn)此時視圖已經(jīng)修改了表數(shù)據(jù)】
SELECT * FROM emp;
--還可以給視圖字段更換別名
CREATE VIEW emp_view1 AS
SELECT id AS "ID", name AS "名稱", salary AS "薪資", email AS "郵箱" FROM emp;
--修改視圖
CREATE OR REPLACE VIEW emp_view1 AS
SELECT id AS "ID", name AS "名稱", salary AS "薪資", email AS "郵箱" FROM emp;
--創(chuàng)建只讀視圖【此時就無法修改視圖】
CREATE OR REPLACE VIEW emp_view1 AS SELECTid AS "ID",name AS "名稱",salary AS "薪資",email AS "郵箱" 
FROMemp WITH READ ONLY;
--刪除視圖
DROP VIEW emp_view1;

• 創(chuàng)建視圖過程中使用了分組函數(shù)就是復雜視圖
• 復雜視圖的 DML 無法進行操作

Top-N分析

SELECT ROWNUM, name, salary FROM
(SELECT name, salary FROM emp ORDER BY salary DESC)
WHERE ROWNUM <= 3;

對 ROWNUM 只能使用 “<” 或 “<=”，而用 “=”，“>”，“>=” 都將不能返回任何數(shù)據(jù)

--查找4~6之間的員工薪資
SELECT ROWNUM, name, salary FROM
(SELECT name, salary FROM emp ORDER BY salary DESC)
WHERE ROWNUM <= 6 AND ROWNUM >=4;

--查找4~6之間的員工薪資
SELECT rn, name, salary 
FROM( SELECT ROWNUM AS rn, name, salaryFROM(SELECT name, salary FROM emp ORDER BY salary DESC)) 
WHERE rn >= 4 AND rn <= 6;

其它數(shù)據(jù)對象

序列

--創(chuàng)建序列
CREATE SEQUENCE empseq
INCREMENT BY 1 --每次增長1
START WITH 1 --從1開始增長
MAXVALUE 100 --提供的最大值，NOMAXVALUE無限制
CYCLE --需要循環(huán)
CACHE 50; --不需要緩存登錄
--使用之前一定要先獲取下一個值【迭代器到11為止】
SELECT empseq.nextval FROM dual;
--獲取當前值
SELECT empseq.currval FROM dual;
--使用序列
INSERT INTO emp(id, name, salary) VALUES(empseq.nextval, '戌', 1500, 'xu@qq.com');
--修改序列的增量，最大值，最小值，循環(huán)選項或是否裝入內(nèi)存
ALTER SEQUENCE empseq
INCREMENT BY 2
MAXVALUE 100
NOCYCLE
NOCACHE
--查看當前有哪些序列
SELECt sequence_name, min_value, max_value, increment_by, last_number FROM user_sequences;
--刪除序列
DROP SEQUENCE empseq;

• 修改序列只有后續(xù)插入的值才會改變
• 改變序列的初始值只能通過刪除序列之后重建序列的方法實現(xiàn)
• 將序列值裝入內(nèi)存可以提高訪問效率
• 序列在下列情況下會出現(xiàn) 裂縫
  • 回滾
  • 系統(tǒng)異常
  • 多個表同時使用同一序列
• 如果不將序列的值裝入內(nèi)存(NOCACHE)，可使用表 USER_SEQUENCES 查看序列當前的有效值

索引

--刪除序列
CREATE INDEX emp_id_index ON emp(id);

什么時候創(chuàng)建索引？

• 列中數(shù)據(jù)值分布范圍很廣
• 列經(jīng)常在 WHERE 子句或連接條件中出現(xiàn)
• 表經(jīng)常被訪問而且數(shù)據(jù)量很大，訪問的數(shù)據(jù)大概占據(jù)數(shù)據(jù)總量的2%~4%

什么時候不要創(chuàng)建索引

• 表很小
• 表經(jīng)常更新
• 查詢的數(shù)據(jù)大于2%~4%
• 列不經(jīng)常作為連接條件或出現(xiàn)在WHERE子句中

索引在查詢的時候速度快了，但是插入速度也就慢了

同義詞

使用同義詞訪問相同的對象

• 方便訪問其它用戶對象
• 縮短對象名字長度

--添加同義詞
--添加同義詞
CREATE SYNONYM e FOR emp;
SELECT * FROM e;
--刪除同義詞
DROP SYNONYM e;

控制用戶權(quán)限及練習

授權(quán)

• 數(shù)據(jù)庫安全性
  • 系統(tǒng)安全性
  • 數(shù)據(jù)安全性
• 系統(tǒng)權(quán)限：對于數(shù)據(jù)庫的權(quán)限
• 對象權(quán)限：操作數(shù)據(jù)庫對象的權(quán)限

以下創(chuàng)建用戶和賦予權(quán)限都是利用 SYSTEM 用戶創(chuàng)建 cvter 用戶

--創(chuàng)建用戶【用戶是cvter并不是C##cvter，因為oracle官網(wǎng)說明了創(chuàng)建用戶之前需要加C##或者c##】
CREATE USER C##cvter IDENTIFIED BY qwe123
--修改密碼
ALTER USER C##cvter IDENTIFIED BY ewq321;

創(chuàng)建用戶之后應(yīng)該賦予權(quán)限

• 創(chuàng)建會話：CREATE SESSION
• 創(chuàng)建表：CREATE TABLE
• 創(chuàng)建視圖：CREATE VIEW
• 創(chuàng)建序列：CREATE SEQUENCE
• 創(chuàng)建過程：CREATE PROCEDURE

--賦予權(quán)限
GRANT CREATE SESSION TO C##cvter;--遠程登陸數(shù)據(jù)庫權(quán)限
GRANT CREATE TABLE TO C##cvter;

創(chuàng)建用戶表空間
用戶擁有 CREATE TABLE 權(quán)限之外還需要發(fā)呢配相應(yīng)的表空間才可以開辟存儲空間用于創(chuàng)建表

--創(chuàng)建用戶表空間
ALTER USER C##cvter QUOTA UNLIMITED ON users;--無限制表空間大小
ALTER USER C##cvter QUOTA 5M ON users;--限制表空間大小為5MB

創(chuàng)建角色并賦予權(quán)限

--創(chuàng)建角色
CREATE ROLE C##my_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO C##my_role;
CREATE USER C##cvter02 IDENTIFIED BY qwe123;
GRANT C##my_role TO C##cvter02;

對象授權(quán)
還需要對新建的用戶開放 system.emp 的 select和update 權(quán)限而其它權(quán)限無法使用

--對象權(quán)限
GRANT select, update ON system.emp TO C##cvter;
--其它權(quán)限無法使用
DROP TABLE system.emp;

WITH GRANT OPTION 和 PUBLIC 關(guān)鍵字

--WITH GRANT OPTION 使用戶同樣具有分配權(quán)限的權(quán)利
GRANT select, update ON system.emp TO C##cvter WITH GRANT OPTION;
--向數(shù)據(jù)庫中所有用戶分配權(quán)限
GRANT select ON system.emp TO PUBLIC;

收回對象權(quán)限

--查詢用戶擁有的權(quán)限
SELECT * FROM user_tab_privs_recd;
--收回cvter的select和update權(quán)限
REVOKE select, update ON system.emp FROM C##cvter;

SET集合運算符

查詢的數(shù)據(jù)列數(shù)和數(shù)據(jù)類型應(yīng)該精準對應(yīng)

UNION ALL 會返回全部的集合。而UNION會返回去重后的集合

高級子查詢

多列子查詢

--查詢與141號或174號員工的manager_id和department_id相同的其他員工的employee_id, manager_id, department_id
SELECTemployee_id,manager_id,department_id 
FROMemployees 
WHERE(manager_id, department_id) IN ( SELECT manager_id, department_id FROM employees WHERE employee_id IN ( 141, 174 ) ) AND employee_id NOT IN ( 141, 174 );

在FROM 子句中使用子查詢

--返回比本部門平均工資高的員工的last_name, department_id, salary及平均工資
SELECT last_name, department_id, salary, (SELECT AVG(salary) FROM employees e3 WHERE e1.department_id = e3.department_id GROUP BY department_id)
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id GROUP BY department_id);SEELCT last_name, e1.department_id, e2.avg_sal ) FROM employees e1, (SELECT department_id, AVG(salary) avg_sal FROM employees GROUP BY department_id) e2 WHERE e1.department_id = e2.department_id;

在 SQL 中使用單列子查詢

--顯式員工的employee_id,last_name和location。其中，若員工department_id與location_id為1800的department_id相同，則location為’Canada’,其余則為’USA’
SELECT employee_id, last_name,( CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id = 1800 ) THEN 'Canada' ELSE 'USA' END ) location 
FROMemployees;--查詢員工的employee_id,last_name,要求按照員工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);

相關(guān)子查詢

--若employees表中employee_id與job_history表中employee_id相同的數(shù)目不小于2，輸出這些相同id的員工的employee_id,last_name和其job_id
SELECT employee_id, last_name, job_id
FROM employees e1
WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e1.employee_id);

使用 EXISTS 和 NOT EXISTS 操作符

EXISTS

--查詢公司管理者的employee_id, last_name, job_id, department_id信息
--方案一
SELECT e1.employee_id, e1.last_name, e1.last_name, e1.job_id, e1.department_id
FROM employees e1
WHERE e1.employee_id IN (SELECT manager_id FROM employees e2 WHERE e1.employee_id = e2.manager_id										  );
--方案二
SELECT DISTINCT e1.employee_id, e1.last_name, e1.last_name, e1.job_id, e1.department_id
FROM employees e1, employees e1
WHERE e1.employee_id = e2.manager_id;
--方案三
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (SELECT 'A' FROM employees e2 WHERE e1.employee_id = e2.manager_id);

NO EXISTS

--查詢departments表中，不存在于employees表中的部門的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE (SELECT 'C'FROM employeesWHERE department_id = d.department_id);--差集
SELECT department_id, department_name
FROM departments d
MINUS
SELECT department_id, department_name
FROM employees

使用子查詢刪除和更新數(shù)據(jù)

--更新
UPDATE emp 
SET department_name = ( SELECT department_name FROM departments WHERE emp.department_id = department_id );
--刪除
DELETE 
FROMemployees e 
WHEREemployee_id IN ( SELECT employee_id FROM emp_history WHERE employee_id = e.employee_id );

使用 WITH 子句

--查詢公司中各部門的總工資大于公司中各部門的平均總工資的部門信息
WITH dept_sumsal AS ( SELECT department_name, SUM( salary ) sum_sal1, sumsal, FROM departments d, employees e WHERE d.department_id = e.department_id GROUP BY department_name ),
dept_avgsal AS (SELECT SUM(sum_sal)/COUNT(*) avg_sum_sal2 FROM dept_sumsal)
SELECT * FROM dept_sumal WHERE sum_sal1 > (SELECT avg_sum_sal2 FROM dept_avgsal) ORDER BY department_name;