SQL語(yǔ)言處理
本章墨叛，我們將會(huì)重點(diǎn)探討SQL語(yǔ)言基礎(chǔ)，學(xué)習(xí)用SQL進(jìn)行數(shù)據(jù)庫(kù)的數(shù)據(jù)增加模蜡、刪除和修改等操作漠趁。另外請(qǐng)注意本章的SQL語(yǔ)法基于Oracle數(shù)據(jù)庫(kù)的PL/SQL語(yǔ)法。
表的數(shù)據(jù)操作
DML 可以在下列條件下執(zhí)行:
- 向表中插入數(shù)據(jù)
- 修改現(xiàn)存數(shù)據(jù)
- 刪除現(xiàn)存數(shù)據(jù)
新增
使用 INSERT 語(yǔ)句向表中插入數(shù)據(jù)哩牍。
處理語(yǔ)法
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
--使用這種語(yǔ)法一次只能向表中插入一條數(shù)據(jù)棚潦。
- 為每一列添加一個(gè)新值令漂。
- 按列的默認(rèn)順序列出各個(gè)列的值膝昆。
- 在 INSERT 子句中隨意列出列名和他們的值丸边。
- 字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中。
插入空值
隱式方式: 在列名表中省略該列的值荚孵。
INSERT INTO HR.DEPARTMENTS
(DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES
(330, 'Purchasing');
--1 row created.
顯示方式: 在VALUES 子句中指定空值妹窖。
INSERT INTO HR.DEPARTMENTS VALUES (400, 'Finance', NULL, NULL);
--1 row created.
插入系統(tǒng)時(shí)間
SYSDATE 記錄當(dāng)前系統(tǒng)的日期和時(shí)間。
INSERT INTO HR.EMPLOYEES
(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID)
VALUES
(113,
'Louis',
'Popp',
'LPOPP',
'515.124.4567',
SYSDATE,
'AC_ACCOUNT',
6900,
NULL,
205,
100);
--1 row created.
INSERT INTO HR.EMPLOYEES
VALUES
(114,
'Den',
'Raphealy',
'DRAPHEAL',
'515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT',
11000,
NULL,
100,
30);
--1 row created.
插入其他表的數(shù)據(jù)
INSERT INTO SALES_REPS
(ID, NAME, SALARY, COMMISSION_PCT)
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, COMMISSION_PCT
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%';
--4 rows created.
更新
使用 UPDATE 語(yǔ)句更新數(shù)據(jù)收叶。
語(yǔ)法示例
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
--可以一次更新多條數(shù)據(jù)
使用 WHERE 子句指定需要更新的數(shù)據(jù)骄呼。
UPDATE HR.EMPLOYEES
SET DEPARTMENT_ID = 70
WHERE EMPLOYEE_ID = 113;
--1 row updated.
--如果省略WHERE子句，則表中的所有數(shù)據(jù)都將被更新判没。
在UPDATE語(yǔ)句中使用子查詢
UPDATE HR.EMPLOYEES
SET JOB_ID =
(SELECT JOB_ID FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 205),
SALARY =
(SELECT SALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 205)
WHERE EMPLOYEE_ID = 114;
--1 row updated.
--更新 114號(hào)員工的工作和工資使其與 205號(hào)員工相同
UPDATE COPY_EMP
SET DEPARTMENT_ID =
(SELECT DEPARTMENT_ID FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 100)
WHERE JOB_ID = (SELECT JOB_ID FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 200);
--1 row updated.
--在 UPDATE 中使用子查詢蜓萄，使更新基于另一個(gè)表中的數(shù)據(jù)。
刪除
使用 DELETE 語(yǔ)句從表中刪除數(shù)據(jù)澄峰。
語(yǔ)法示例
DELETE [FROM] table
[WHERE condition];
使用WHERE 子句指定刪除的記錄嫉沽。
DELETE FROM HR.departments
WHERE department_name = 'Finance';
--1 row deleted.
--如果省略WHERE子句，則表中的全部數(shù)據(jù)將被刪除俏竞。
在 DELETE 中使用子查詢
DELETE FROM HR.employees
WHERE department_id =
(SELECT department_id
FROM HR.departments
WHERE department_name LIKE '%Public%');
--1 row deleted.
--在 DELETE 中使用子查詢绸硕，使刪除基于另一個(gè)表中的數(shù)據(jù)。
高級(jí)函數(shù)處理
NVL & NVL2
NVL
將空值轉(zhuǎn)換成一個(gè)已知的值：可以使用的數(shù)據(jù)類型有日期魂毁、字符玻佩、數(shù)字。
函數(shù)的一般形式:
NVL(commission_pct, 0)NVL(hire_date, SYSDATE)NVL(job_id, 'No Job Yet')
使用示例
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM hr.employees;
--NVL(commission_pct, 0):若commission_pct是NULL席楚，那么取0作為它的值
NVL2
SELECT last_name, salary, commission_pct,
NVL2(commission_pct, 'SAL+COMM', 'SAL') income
FROM hr.employees WHERE department_id IN (50, 80);
--NVL2(commission_pct, 'SAL+COMM', 'SAL'):若commission_pct不是NULL咬崔，就取SAL+COMM，否則取SAL
COALESCE
COALESCE 與 NVL 相比的優(yōu)點(diǎn)在于 COALESCE 可以同時(shí)處理交替的多個(gè)值烦秩。
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;
-- COALESCE(commission_pct, salary, 10)是返回第一個(gè)非空的值刁赦，若commission_pct為NULL就返回salary，若salary也為NULL闻镶，就返回10
在 SQL 語(yǔ)句中使用IF-THEN-ELSE 邏輯甚脉。
使用兩種方法:CASE 表達(dá)式DECODE 函數(shù)
DECODE
使用語(yǔ)法
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
使用示例
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
CASE表達(dá)式
使用語(yǔ)法
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
使用示例
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM hr.employees;
--根據(jù)JOB_ID的值，對(duì)工資做相應(yīng)的處理
事務(wù)
- 以第一個(gè) DML 語(yǔ)句的執(zhí)行作為開始
- 以下面的其中之一作為結(jié)束:
- COMMIT 或 ROLLBACK 語(yǔ)句
- DDL 或 DCL 語(yǔ)句（自動(dòng)提交）
- 用戶會(huì)話正常結(jié)束系統(tǒng)異常終了
使用COMMIT 和 ROLLBACK語(yǔ)句,我們可以:
- 確保數(shù)據(jù)完整性铆农。
- 數(shù)據(jù)改變被提交之前預(yù)覽牺氨。
- 將邏輯上相關(guān)的操作分組。
視圖
- 控制數(shù)據(jù)訪問
- 簡(jiǎn)化查詢
- 數(shù)據(jù)獨(dú)立性
- 避免重復(fù)訪問相同的數(shù)據(jù)
視圖創(chuàng)建
語(yǔ)法示例
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
創(chuàng)建視圖
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM hr.employees
WHERE department_id = 80;
--View created.
創(chuàng)建視圖時(shí)在子查詢中給列定義別名
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM hr.employees
WHERE department_id = 50;
--View created.
--在選擇視圖中的列時(shí)應(yīng)使用別名
視圖修改
使用CREATE OR REPLACE VIEW 子句修改視圖
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM hr.employees
WHERE department_id = 80;
--View created.
--CREATE VIEW 子句中各列的別名應(yīng)和子查詢中各列相對(duì)應(yīng)
刪除視圖
刪除視圖只是刪除視圖的定義墩剖，并不會(huì)刪除基表的數(shù)據(jù)
刪除語(yǔ)法
DROP VIEW view;
--刪除視圖view
存儲(chǔ)過程
存儲(chǔ)過程猴凹，Procedure，是一組為了完成特定功能的SQL語(yǔ)句集合岭皂，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中郊霎，用戶通過指定存儲(chǔ)過程的名稱并給出參數(shù)來(lái)執(zhí)行。
存儲(chǔ)過程中可以包含邏輯控制語(yǔ)句和數(shù)據(jù)操縱語(yǔ)句爷绘，它可以接受參數(shù)书劝、輸出參數(shù)进倍、返回單個(gè)或多個(gè)結(jié)果集以及返回值。
由于存儲(chǔ)過程在創(chuàng)建時(shí)即在數(shù)據(jù)庫(kù)服務(wù)器上進(jìn)行了編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中购对，所以存儲(chǔ)過程運(yùn)行要比單個(gè)的SQL語(yǔ)句塊要快猾昆。同時(shí)由于在調(diào)用時(shí)只需用提供存儲(chǔ)過程名和必要的參數(shù)信息，所以在一定程度上也可以減少網(wǎng)絡(luò)流量骡苞、簡(jiǎn)單網(wǎng)絡(luò)負(fù)擔(dān)垂蜗。
基本語(yǔ)法
CREATE OR REPLACE PROCEDURE 存儲(chǔ)過程名字
(
參數(shù)1 IN NUMBER,
參數(shù)2 IN NUMBER
) IS
變量1 INTEGER :=0;
變量2 DATE;
BEGIN
[執(zhí)行語(yǔ)句]
END
使用示例
創(chuàng)建一個(gè)表Student，并插入一百萬(wàn)條記錄解幽，在奇數(shù)的時(shí)候插入Tom記錄贴见，偶數(shù)的時(shí)候插入Lucy記錄。使用存儲(chǔ)過程來(lái)實(shí)現(xiàn)躲株。
Create Table student (
s_id Number(8),
s_name Varchar2(20),
s_sex Char(2)
);
--創(chuàng)建表student
Create Or Replace Procedure p_insert_data
AS
Begin
For i In 1..1000000 Loop
If i Mod 2 = 1 Then
Insert into student Values(i, 'Tom', '男');
Else
Insert into student Values(i, 'Lucy', '女');
End If;
End Loop;
Commit;
dbms_output.put_line('插入數(shù)據(jù)完成!');
End;
--創(chuàng)建存儲(chǔ)過程
調(diào)用存儲(chǔ)過程
CALL p_insert_data();
