oracle SQL語句練習MERGE、模糊查詢、排序、

標籤：des   io   使用   ar   strong   for   資料   art   cti   

Oracle支援的SQL指令可分為資料操作語言語句、資料定義語言 (Data Definition Language)語句、事務控制語句、會話控制語句等幾種類型：
1、資料操作語言語句
資料操作語言語句（Data manipulation language，DML）用於進行資料的檢索和更新操作。資料檢索是資料庫應用中使用頻率最高的操作類型，因此資料檢索的效率對資料庫的整體效能影響顯著。資料更新包括資料的插入、修改和刪除等操作，資料更新操作具有一定的風險性，在其執行過程中DBMS必須保證資料的一致性，以確保資料有效。
SELECT、INSERT、DELETE、UPDATE、MERGE
2、資料定義語言 (Data Definition Language)語句
資料定義語言 (Data Definition Language)語句（Data definition language，DDL）用於定義資料的格式和形態，比如定義資料表、視圖和索引等資料庫物件。在建立資料庫時使用者首先要使用的就是DDL語句。
CREATE、ALTER、DROP、RENAME、TRUNCATE
3、資料控制語言語句
資料控制語言（Data Control Language，DCL）用於對使用者授權或撤銷其許可權，也可使用角色實現對使用者的大量授權或撤銷許可權，在應用開發層面較少用到，
GRANT、REVOKE
4、事務控制語句
事務控制語句（Transaction Control Statement）用於實現資料庫交易管理
COMMIT、ROLLBACK、SAVEPOINT
5、會話控制語句
會話控制語句（Session Control Statement）用於動態修改目前使用者會話的屬性，在應用開發層面極少用到。
ALTER SESSION、SET ROLE
二、SQL基礎
1、刪除表中資料
使用DELET指令，可刪除資料表中已有的資料，例如：
DELETE student; 或：
DELETE FROM student;
這是"DELETE"指令的最基本形式，此操作會刪除student表中的所有資料，但表的結構仍存在，即此後還可以對該表進行插入資料操作。
2、查看錶結構
使用"DESC"或" DESCRIBE "指令可以查看指定表的結構，包括表中各欄位的名稱、資料類型、及是否允許為空白值等。例如：
DESC student;
DESCRIBE student;
3、刪除表
使用"DROP"指令刪除已有的資料表，包括表中資料和表的結構，例如：
DROP TABLE student;
刪除後student表不再可用。

INSERT INTO myemp (SELECT ename, sal, hiredate FROM emp);
INSERT INTO myemp(name, salary) (SELECT ename, sal FROM emp);

--建立樣本表
CREATE TABLE myemp1(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));
CREATE TABLE myemp2(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));
CREATE TABLE myemp3(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));
CREATE TABLE myempOther(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), salary NUMBER(7,2));
INSERT ALL WHEN job=‘MANAGER‘ THEN INTO myemp1
WHEN job=‘CLERK‘ THEN INTO myemp2
WHEN job=‘ANALYST‘ THEN INTO myemp3
ELSE INTO myempOther
SELECT empno, ename, job, sal FROM emp;

修改某一個欄位：
ALTER TABLE EMP2 RENAME COLUNM SALARY TO SAL;
========================
MERGE操作
MERGE操作用於實現資料合併——根據條件在表中執行資料的修改或插入操作，如果要插入的記錄在目標表中已經存在，則執行更新操作、否則執行插入操作。其文法格式如下：
MERGE INTO dest_table [alias]
USING (source_table | view | sub_query) [alias]
ON (join_condition)
WHEN MATCHED THEN
UPDATE SET column1 = column1_value, column2 = column2_value
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
其中，dest_table用於指定要將資料合併到的目標表，source_table（view，sub_query）為提供資料的源表（視圖或子查詢），ON子句用於指定合併作業中資料的串連條件。針對源表中的每一條記錄，如果在目標表中找到與其相應的記錄（符合串連條件的記錄），則執行WHEN MATCHED THEN分支的UPDATE語句，修改目標表中相應記錄；否則執行WHEN NOT MATCHED THEN分支的INSERT語句，將源表的目前記錄插入到目標表中。
下面給出一個直觀的例子：
DROP TABLE test1;
DROP TABLE test2;
--建立來源資料表test1，插入兩條樣本資料
CREATE TABLE test1(eid NUMBER(10), name VARCHAR2(20),birth DATE,salary NUMBER(8,2));
INSERT INTO test1 VALUES (1001, ‘張三‘, ‘20-5月-1970‘, 2300);
INSERT INTO test1 VALUES (1002, ‘李四‘, ‘16-4月-1973‘, 6600);
--SELECT * FROM test1;
--建立目標資料表test2，插入一條樣板資料
CREATE TABLE test2(eid NUMBER(10), name VARCHAR2(20),birth DATE,salary NUMBER(8,2));
INSERT INTO test2 VALUES (1001, ‘王五‘, ‘29-11月-2008‘, 2300);
--SELECT * FROM test2;
--將源表test1中資料合併到目標表test2
MERGE INTO test2 USING test1 ON(test1.eid = test2.eid ) WHEN matched THEN UPDATE SET name = test1.name, birth = test1.birth, salary = test1.salary WHEN NOT matched THEN INSERT (eid, name, birth, salary) VALUES(test1.eid, test1.name, test1.birth, test1.salary);
--查詢合并後目標表test2中資料內容
SELECT * FROM test2;
SQL> SELECT * FROM test2;

EID NAME BIRTH SALARY
---------- -------------------- -------------- ----------
1001 張三 10-1月 -10 2300
1002 李四 10-1月 -10 6600
可以看出，由於test2表中已存在eid=1001的記錄行，本次合併作業中，只是對其進行了修改操作，同時還其中插入了一條新記錄（eid=1002）。
需要注意的是，在進行MERGE操作時，WHEN matched THEN分支的UPDATE語句中不允許更建立立表間記錄串連關係的參照欄位（如上述的eid欄位），因為這樣會破壞記錄間的對應關係。例如，上述MERGE語句如改為如下形式：
MERGE INTO test2 USING test1 ON(test1.eid = test2.eid ) WHEN matched THEN UPDATE SET eid = test1.eid, name = test1.name, birth = test1.birth, salary = test1.salary WHEN NOT matched THEN INSERT (eid, name, birth, salary) VALUES(test1.eid, test1.name, test1.birth, test1.salary);
第 1 行出現錯誤:
ORA-38104: 無法更新 ON 子句中引用的列: "TEST2"."EID"
===============================================================================

Sql Plus中的自動認可
Sql Plus中執行SQL語句時預設設定為非自動認可，也可以使用如下指令顯式設定是否自動認可：
SET AUTOCOMMIT ON; --設定為自動認可
SET AUTOCOMMIT OFF; --設定為非自動認可
上述設定只在本次資料庫連接會話有效，待重啟Sql Plus後會恢複到其預設設定。也可以執行下述命令查看其當前提交方式：
SHOW AUTOCOMMIT;
如返回結果“autocommit OFF”則為非自動認可，如返回“autocommit IMMEDIATE”則為自動認可。
自動認可模式會影響應用程式的效能和事務邏輯，如果應用程式需要事務的完整性，則不能使用自動認可。
====================================
儲存點
如果需要，還可以在事務中使用儲存點（Savepoint）在當前的事務中的關鍵點處建立標記，將來就可以回退到指定的標記（儲存點）處，以實現事務的部分復原。例如：
INSERT INTO dept VALUES(55,‘Adv‘,‘Beijing‘);
INSERT INTO dept VALUES (56,‘Sec‘,‘Shanghai‘);
SAVEPOINT p1;
INSERT INTO dept VALUES (57,‘Acc‘,‘Tianjin‘);
---
SELECT * FROM dept;
ROLLBACK TO p1;
SELECT * FROM dept;
=================================
使用串連運算式
在Oracle資料庫中，可以使用串連運算子"||"把欄位與文本、或其它運算式串連在一起，得到一個新的字串，實現“合成列”的功能。例如下述語句：
SQL> SELECT ‘編號:‘ || empno , ename || ‘的工資為:‘, sal || ‘元‘ FROM emp;

‘編號:‘||EMPNO ENAME||‘的工資為:‘ SAL||‘元‘
--------------------------------------------- ------------------- ---------
編號:7369 SMITH的工資為: 800元
編號:7499 ALLEN的工資為: 1600元
編號:7521 WARD的工資為: 1250元
編號:7566 JONES的工資為: 2975元
編號:7654 MARTIN的工資為: 1250元
編號:7698 BLAKE的工資為: 2850元
編號:7782 CLARK的工資為: 2450元
編號:7788 SCOTT的工資為: 3000元
編號:7839 KING的工資為: 5000元
編號:7844 TURNER的工資為: 1500元
編號:7876 ADAMS的工資為: 1100元
編號:7900 JAMES的工資為: 950元
編號:7902 FORD的工資為: 3000元
編號:7934 MILLER的工資為: 1300元
SQL> SELECT ‘Dept.‘||deptno || ‘‘s name is ‘|| dname FROM DEPT;
ERROR:
ORA-01756: 引號內的字串沒有正確結束

SQL> SELECT ‘Dept.‘||deptno || ‘‘‘s name is ‘|| dname FROM DEPT;

‘DEPT.‘||DEPTNO||‘‘‘SNAMEIS‘||DNAME
----------------------------------------------------------------------
Dept.10‘s name is ACCOUNTING
Dept.20‘s name is RESEARCH
Dept.30‘s name is SALES
Dept.40‘s name is OPERATIONS
=====================================
使用別名
在SELECT語句中，可以使用別名來重新命名目標表、以及查詢結果中的欄位（或運算式），以增強可讀性。如果別名中使用特殊字元、或者是強制其大小寫敏感，則需使用雙引號將別名括起來，其它情況禁止使用引號，否則出錯；而使用串連運算式時使用的是英文單引號。例如：
SQL> SELECT empno AS 編號, ename 姓名, sal*12 年薪, ename, ename "EnAme",sal*12 "Anual Salary" FROM emp t1;
編號 姓名 年薪 ENAME EnAme Anual Salary
---------- ---------- ---------- ---------- ---------- ------------
7369 SMITH 9600 SMITH SMITH 9600
7499 ALLEN 19200 ALLEN ALLEN 19200
7521 WARD 15000 WARD WARD 15000
7566 JONES 35700 JONES JONES 35700
7654 MARTIN 15000 MARTIN MARTIN 15000
7698 BLAKE 34200 BLAKE BLAKE 34200
7782 CLARK 29400 CLARK CLARK 29400
7788 SCOTT 36000 SCOTT SCOTT 36000
7839 KING 60000 KING KING 60000
7844 TURNER 18000 TURNER TURNER 18000
7876 ADAMS 13200 ADAMS ADAMS 13200
7900 JAMES 11400 JAMES JAMES 11400
7902 FORD 36000 FORD FORD 36000
7934 MILLER 15600 MILLER MILLER 15600
別名"Anual Salary"中包含特殊字元空格，別名"EnAme"則是希望在查詢結果中強制規定其大小寫拼字格式，因此使用雙引號括起來。欄位/運算式與其別名之間的關鍵字"AS"可以省略，表名與其別名之間則不允許使用關鍵字"AS"。

SQL> SELECT ‘sid:‘ ||sid 學號,‘name:‘ ||name 稱謂,age-1 周歲 FROM student;
============================================
運算式中的空值
算術運算式中如果出現空值，則整個運算式結果為空白。這是因為空白值是未知的、不確定的值，不能簡單地當作數值0來處理，因此任何含有空值的算術運算式其運算結果也都是未知的、不確定的。比如，空值乘2的結果仍為空白值。在使用"||"運算子的字串串連運算式中，出現的空值則被當作一個空的（長度為零的）字串來處理。這是因為Oracle目前處理Null 字元串的方法與處理空值的方法相同。
學號 稱謂 周歲
-------------- ------------------------- ----------
sid:101 name:張三 17
sid:102 name:李四
sid:103 name:王五
sid:104 name: 35
===================================================
去除重複行
在預設情況下，查詢結果中包含所有合格記錄行，包括重複行。例如下述語句
SELECT deptno FROM emp;
返回emp表中每一行記錄的deptno欄位值，其中多次出現重複，如果只是想查看一下當前emp表中所出現/涉及到的部門編號，則可在SELECT語句中使用DISTINCT關鍵字，以過濾掉查詢結果中所有的重複行。例如語句：
SQL> SELECT DEPTNO FROM EMP;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
已選擇14行。
SQL> SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
----------
30

20
10
DISTINCT的作用範圍是後面所有欄位的組合，這意味著不會出現重複的欄位組合，但組合中的單個欄位值則可能出現重複，例如下述SQL語句：
SELECT DISTINCT deptno, job FROM emp;
實現了查詢/統計各部門編號及部門內職位設定的功能，
DROP TABLE student;
CREATE TABLE student(
sid VARCHAR2(10),
name VARCHAR2(20),
age NUMBER(3)
);
INSERT INTO student VALUES(101, ‘張三‘, 18);
INSERT INTO student VALUES(102, ‘李四‘, 25);
INSERT INTO student VALUES(103, ‘張三‘, 28);
INSERT INTO student VALUES(103, ‘張三‘, 28);
SELECT * FROM student;
SELECT DISTINCT * FROM student;
============================================
按單欄位排序
SELECT語句查詢結果預設按照表中記錄的物理順序（即記錄的插入順序）進行排列，也可在SELECT語句中使用ORDER BY 子句對查詢結果進行排序，排序方式包括升序（Ascending）和降序（Descending）兩種，分別使用關鍵字ASC和DESC進行標識，預設為升序。
SQL> SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7902 FORD 3000
7788 SCOTT 3000
7839 KING 5000
8888
已選擇15行。

SQL> SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;
EMPNO ENAME SAL
---------- ---------- ----------
8888
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800

已選擇15行。
對於數值型欄位的升序排列，是按照欄位數值從小到大的順序排列查詢結果中的每一行記錄。類似地，文本型欄位的升序排列，則是按照常值內容（字串）的英文字典順序進行排列；日期/時間型欄位的升序排列則是按照日期/時間的從“早”到“晚”的順序進行排列。降序排列的情形則相反。
按多欄位組合排序
如要實現按多欄位排序，則可以在在ORDER BY子句中依次給出待排序欄位列表，此時應分別設定每個欄位的排序方式，例如下述語句：
SQL> SELECT deptno, empno, ename, sal FROM emp ORDER BY deptno, sal;

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7934 MILLER 1300
10 7782 CLARK 2450
10 7839 KING 5000
20 7369 SMITH 800
20 7876 ADAMS 1100
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7902 FORD 3000
30 7900 JAMES 950
30 7654 MARTIN 1250
30 7521 WARD 1250
30 7844 TURNER 1500
30 7499 ALLEN 1600
30 7698 BLAKE 2850

已選擇14行。

SQL> SELECT deptno, empno, ename, sal FROM emp ORDER BY SAL,DEPTNO;

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
20 7369 SMITH 800
30 7900 JAMES 950
20 7876 ADAMS 1100
30 7521 WARD 1250
30 7654 MARTIN 1250
10 7934 MILLER 1300
30 7844 TURNER 1500
30 7499 ALLEN 1600
10 7782 CLARK 2450
30 7698 BLAKE 2850
20 7566 JONES 2975
20 7788 SCOTT 3000
20 7902 FORD 3000
10 7839 KING 5000

已選擇14行。在最前
排序中的空值
Oracle資料庫中的查詢結果排序，如果排序欄位出現空值（NULL），預設認為NULL是最大值，所以如果是升序（ASC）排序則空值欄位所屬的記錄行排在最後，降序（DESC）排序則排在最前.
SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm;

EMPNO ENAME COMM
---------- ---------- ----------
7844 TURNER 0
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7369 SMITH
7876 ADAMS
7900 JAMES
7934 MILLER

已選擇8行。

SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm DESC;

EMPNO ENAME COMM
---------- ---------- ----------
7369 SMITH
7900 JAMES
7876 ADAMS
7934 MILLER
7654 MARTIN 1400
7521 WARD 500
7499 ALLEN 300
7844 TURNER 0

已選擇8行。
Oracle資料庫還支援在Order by子句中使用關鍵字NULLS FIRST或NULLS LAST指定將排序欄位為空白值的記錄行排在結果集的最前或最後（不論採用的是升序還是降序排序方式）
SQL> SELECT empno, ename, comm FROM emp WHERE sal<2000 ORDER BY comm DESC NULLS LAST;

EMPNO ENAME COMM
---------- ---------- ----------
7654 MARTIN 1400
7521 WARD 500
7499 ALLEN 300
7844 TURNER 0
7934 MILLER
7876 ADAMS
7900 JAMES
7369 SMITH

已選擇8行。
=======================================
模糊查詢
%（百分比符號）——表示零或多個任一字元的萬用字元。
_（底線）——標識單個字元的萬用字元，可以匹配單個任一字元。
例如，要查詢所有姓名以字母為‘S‘開頭的僱員資訊，可使用SQL語句：
SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘S%‘;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
需要注意的是，模式串中除萬用字元外的字元仍是大小寫敏感的，‘S%‘只能匹配以大寫字母‘S‘開頭的字串，而不能匹配小寫字母‘s‘開頭的字串，如‘smith‘。
SQL> SELECT * FROM EMP WHERE ENAME LIKE ‘_A%‘;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7900 JAMES CLERK 7698 03-12月-81 950 30
對於模糊查詢中的特殊符號可使用ESCAPE標識符來進行轉義尋找，例如要尋找姓名中包含字元‘\‘的僱員的資訊，可以使用如下語句：
SELECT * FROM emp WHERE ename LIKE ‘%\_%‘ ESCAPE ‘\‘;
其中，ESCAPE ‘\‘指定了字元‘\‘為轉義符，模式串‘%\_%‘中的‘\_‘就被轉義為、或者說代表的是字元‘_‘本身，而不再做為萬用字元使用。
SQL> select empno,ename from emp where ename like ‘%\_%‘ ESCAPE ‘\‘;

EMPNO ENAME
---------- ----------
8888 rusky_lu

oracle SQL語句練習MERGE、模糊查詢、排序、