OCP課程4：SQL之限制和排序資料
這章主要講了3個功能：
- 使用where語句限制資料
- 使用order by語句排序資料
- 使用替換變數
1、使用where子句限制行
語法：
where子句必須在from子句之後。
例子：只查詢部門編號為90的人員資訊
SQL> select employee_id,last_name,job_id,department_id from employees where department_id=90;
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King AD_PRES 90
101 Kochhar AD_VP 90
102 De Haan AD_VP 90
如果要限制字元型別，日期型別，必須要用單引號引起來，而且單引號裡面的字元區分大小寫，日期要按照指定的格式書寫，預設的的格式是DD-MON-RR格式。
例子：查詢名字為Whalen的人員資訊
SQL> select last_name,job_id,department_id from employees where last_name='Whalen';
LAST_NAME JOB_ID DEPARTMENT_ID
------------------------- ---------- -------------
Whalen AD_ASST 10
如果'Whalen'不加單引號，則會報錯
SQL> select last_name,job_id,department_id from employees where last_name=Whalen;
select last_name,job_id,department_id from employees where last_name=Whalen
*
ERROR at line 1:
ORA-00904: "WHALEN": invalid identifier
如果'Whalen'全部小寫，則會沒有結果
SQL> select last_name,job_id,department_id from employees where last_name='whalen';
no rows selected
例子：查詢入職日期為23-MAY-06的人員資訊
SQL> select last_name,hire_date from employees where hire_date='23-MAY-06';
LAST_NAME HIRE_DATE
------------------------- ------------
Feeney 23-MAY-06
如果格式修改為中國習慣的YYYY-MM-DD，則會報錯
SQL> select last_name,hire_date from employees where hire_date='2006-05-23';
select last_name,hire_date from employees where hire_date='2006-05-23'
*
ERROR at line 1:
ORA-01861: literal does not match format string
這裡實際上做了一個隱式轉換，將字串轉換成日期型別，格式不一致的話，就報錯了，當然也可以用轉換函式，我們下一章會講。
我們剛才看到限制條件的時候，字元型別和日期型別要使用單引號引起來，字元型別要區分大小寫，日期型別有嚴格的格式匹配的。
2、比較條件
在where子句中常用的比較運算子如下表：
![clipboard[1]](http://img.blog.itpub.net/blog/attachment/201511/13/28536251_14473774901pmR.png "clipboard[1]")
例子：查詢薪水小於等於3000的人員
SQL> select last_name,salary from employees where salary<=3000;
LAST_NAME SALARY
------------------------- ----------
OConnell 2600
例子：查詢薪水在2500到3000之間，包含2500和 3000的人員
SQL> select last_name,salary from employees where salary between 2500 and 3500;
LAST_NAME SALARY
------------------------- ----------
OConnell 2600
例子：查詢被管理ID為100、101及102管理者管理的人員資訊
SQL> select employee_id,last_name,salary,manager_id from employees where manager_id in (100,101,201);
EMPLOYEE_ID LAST_NAME SALARY MANAGER_ID
----------- ------------------------- ---------- ----------
201 Hartstein 13000 100
Oracle中可以使用like條件進行萬用字元搜尋，Oracle中的萬用字元有2種：
- %：表示0或者多個字元
- _：表示一個字元
例子：查詢員工的姓中第一個字母為S的員工資訊
SQL> select first_name from employees where first_name like 'S%';
FIRST_NAME
--------------------
Sundar
例子：查詢員工的名字中第二個字母為o的員工資訊
SQL> select last_name from employees where last_name like '_o%';
LAST_NAME
-------------------------
Colmenares
如果要搜尋包含%或者_的欄位值，則需要使用escape關鍵字進行轉義
例子：查詢工作ID中包含_的人員資訊
SQL> select employee_id,job_id from employees where job_id like 'SH\_%' escape '\';
EMPLOYEE_ID JOB_ID
----------- ----------
180 SH_CLERK
測試是否為null必須使用is null或者is not null，不能使用等號
例子：查詢管理ID為空的人員資訊，也就是查詢沒有人管的人員資訊（應該只有國王沒有上級吧）
SQL> select last_name,manager_id from employees where manager_id is null;
LAST_NAME MANAGER_ID
------------------------- ----------
King
3、邏輯條件
在where子句中的邏輯運算子如下表：
![clipboard[2]](http://img.blog.itpub.net/blog/attachment/201511/13/28536251_1447377492PPzy.png "clipboard[2]")
例子：查詢薪水大於等於10000而且工作編號包含MAN的人員資訊
SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 and job_id like '%MAN%';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
201 Hartstein MK_MAN 13000
例子：查詢薪水大於等於10000或者工作編號包含MAN的人員資訊
SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 or job_id like '%MAN%';
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
201 Hartstein MK_MAN 13000
例子：查詢工作編號不是'IT_PROG', 'ST_CLERK', 'SA_REP'這三個的人員資訊
SQL> select last_name,job_id from employees where job_id not in('IT_PROG','ST_CLERK','SA_REP');
LAST_NAME JOB_ID
------------------------- ----------
Baer PR_REP
4、運算優先順序
Oracle中的運算優先順序如下表：
![clipboard[3]](http://img.blog.itpub.net/blog/attachment/201511/13/28536251_1447377495kZ7j.png "clipboard[3]")
可以使用括號來改變優先順序。
例子：查詢薪水大於15000而且工作編號為'AD_PRES'的人員資訊以及工作編號為'SA_REP'的人員資訊
SQL> select last_name,job_id,salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
King AD_PRES 24000
Tucker SA_REP 10000
例子：查詢薪水大於15000而且工作編號為'SA_REP'或'AD_PRES'的人員資訊
SQL> select last_name,job_id,salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000;
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
King AD_PRES 24000
5、使用order by子句進行排序
select語句的結果預設是沒有排序的，我們可以使用order by子句對指定欄位進行排序，order by子句位於select語句的最後，使用如下2個關鍵字進行排序
- ASC：升序，不使用關鍵字的話預設為升序
- DESC：降序
例子：查詢人員資訊，按照入職時間升序排序
SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date;
LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE
------------------------- ---------- ------------- ------------
De Haan AD_VP 90 13-JAN-01
Mavris HR_REP 40 07-JUN-02
例子：查詢人員資訊，按照入職時間降序排序
SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date desc;
LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE
------------------------- ---------- ------------- ------------
Banda SA_REP 80 21-APR-08
order by後面除了可以指定欄位進行排序，還可以使用欄位的別名以及欄位的位置。
例子：使用欄位的別名對人員年薪進行排序
SQL> select employee_id,last_name,salary*12 annsal from employees order by annsal;
EMPLOYEE_ID LAST_NAME ANNSAL
----------- ------------------------- ----------
132 Olson 25200
例子：使用欄位的位置對人員年薪進行排序
SQL> select employee_id,last_name,salary*12 annsal from employees order by 3;
EMPLOYEE_ID LAST_NAME ANNSAL
----------- ------------------------- ----------
132 Olson 25200
order by子句後面可以使用多個欄位進行排序，但是ASC，DESC關鍵字只對其前面的一個欄位有效，如果欄位後面沒有加上關鍵字，預設是ASC升序。
例子：使用部門編號進行升序排列，部門編號相同的再使用薪水進行降序排列來顯示人員資訊
SQL> select last_name,department_id,salary from employees order by department_id,salary desc;
LAST_NAME DEPARTMENT_ID SALARY
------------------------- ------------- ----------
Whalen 10 4400
6、使用替換變數
透過使用替換變數，可以用1條SQL語句執行不同的查詢，比如我剛開始需要查詢人員編號為100的資訊，後來又需要查詢人員編號為200的資訊，如果使用替換變數，就可以沿用前面的的語句。替換變數有2個符號，一個是單&符號，一個是雙&&符號，雙&&符號用在語句當中這個變數出現多次的情況。
例子：透過提示輸入人員編號查詢不同人員的資訊
SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;
Enter value for employee_num: 100
old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num
new 1: select employee_id,last_name,salary,department_id from employees where employee_id=100
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
100 King 24000 90
如果變數的值是字元或者日期，則最好在語句中使用單引號講變數引起來，就不需要在輸入變數的時候輸入單引號了。
例子：透過提示輸入工作編號查詢不同人員的資訊
SQL> select last_name,department_id,salary*12 from employees where job_id='&job_title';
Enter value for job_title: IT_PROG
old 1: select last_name,department_id,salary*12 from employees where job_id='&job_title'
new 1: select last_name,department_id,salary*12 from employees where job_id='IT_PROG'
LAST_NAME DEPARTMENT_ID SALARY*12
------------------------- ------------- ----------
Hunold 60 108000
替換變數除了用在條件比較，還可以用於select子句中的欄位，where子句中的整個條件，order by子句的排序欄位，甚至select關鍵字後面的所有內容。
例子：透過提示輸入需要的欄位資訊來進行查詢、限制和排序
SQL> select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column;
Enter value for column_name: salary
Enter value for condition: salary>15000
Enter value for order_column: last_name
old 1: select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column
new 1: select employee_id,last_name,job_id,salary from employees where salary>15000 order by last_name
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
102 De Haan AD_VP 17000
如果語句裡面的變數需要重複使用，可以使用&&符號。
例子：透過提示輸入需要的欄位進行選擇和排序
SQL> select employee_id,last_name,job_id,&&column_name from employees order by &column_name;
Enter value for column_name: department_id
old 1: select employee_id,last_name,job_id,&&column_name from employees order by &column_name
new 1: select employee_id,last_name,job_id,department_id from employees order by department_id
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen AD_ASST 10
如果將上面的語句再次執行，會發現不需要輸入變數的值了，這是由於剛才輸入變數的值時就已經在系統中定義了該變數的值為department_id，如果要更換為其他的值，就需要使用undefine關鍵字刪除該變數，當然也可以使用define預先定義變數的值。
例子：先定義一個變數並賦值，再在select語句中使用，最後刪除變數
SQL> define employee_num=200
SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;
old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num
new 1: select employee_id,last_name,salary,department_id from employees where employee_id=200
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
200 Whalen 4400 10
SQL> undefine employee_num;
前面進行變數替換的時候，會顯示替換前和替換後的語句，可以設定sqlplus的verify環境變數進行設定是否顯示。
例子：設定進行變數替換的時候不顯示替換前和替換後的語句
SQL> set verify off
SQL> select employee_id,last_name,salary from employees where employee_id=&employee_num;
Enter value for employee_num: 200
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
200 Whalen 4400
SQL> show verify
verify OFF
sqlplus裡面環境變數還有很多，可以所有show all進行顯示，如果要設定，就使用set。
7、相關習題
（1）View the Exhibit and examine the description of the EMPLOYEES table. Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME, DEPARTMENT_ID, and the total annual salary: SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWE(SYSDATE, hire_date)/12 >= 5 When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly ?
A.Change the SELECT clause to SELECT last_name, department_id, salary*12 +50 "Annual Compensation".
B.Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
C.Change the SELECT clause to SELECT last_name, department_id, (salary+50)*12 "Annual Compensation".
D.Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".
答案：C
（2）View the Exhibit and examine the description of the EMPLOYEES table. Your company wants to give 5% bonus to all the employees on their annual salary. The SALARY column stores the monthly salary for an employee. To check the total for annual salary and bonus amount for each employee, you issued the following SQL statement: SELECT first_name, salary, salary*12+salary*12*.05 "ANNUAL SALARY + BONUS" FROM employees? Which statement is true regarding the above query?
A.It would execute and give you the desired output.
B.It would not execute because the AS keyword is missing between the column name and the alias.
C.It would not execute because double quotation marks are used instead of single quotation marks for assigning alias for the third column.
D.It would execute but the result for the third column would be inaccurate because the parentheses for overriding the precedence of the operator are missing.
答案：A
（3）View the Exhibit and examine the description of the EMPLOYEES table. You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table where in the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000. Which SQL statement would get you the desired result?
A.SELECT employee_id, first_name FROM employees WHERE job_id like 'MAN%' OR job_id like 'CLERK%' AND department_id = 30 AND salary > 3000;
B.SELECT employee_id, first_name FROM employees WHERE job_id like '%MAN' OR job_id like '%CLERK' AND (department_id = 30 OR salary > 3000);
C.SELECT employee_id, first_name FROM employees WHERE (job_id like '%MAN' AND job_id like '%CLERK') AND department_id = 30 OR salary > 3000;
D.SELECT employee_id, first_name FROM employees WHERE (job_id like '%MAN' OR job_id like '%CLERK' ) AND department_id = 30 AND salary > 3000;
答案：D
（4）View the Exhibit and examine the details of the PRODUCT_INFORMATION table. You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: SELECT product_name, list_price FROM product_information WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088 ； Which statement is true regarding the execution of the query？
![clipboard[4]](http://img.blog.itpub.net/blog/attachment/201511/13/28536251_1447377502nmuR.png "clipboard[4]")
A.It would execute but the output would return no rows.
B.It would execute and the output would display the desired result.
C.It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
D.It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
答案：A
（5）View the Exhibit and examine the description of the PRODUCT_INFORMATION table. SELECT product_name, list_price, min_price, list_price-min_price Difference FROM product_information； Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE (Choose all that apply.)
A.ORDER BY 4
B.ORDER BY MIN_PRICE
C.ORDER BY DIFFERENCE
D.ORDER BY LIST_PRICE
E.ORDER BY LIST_PRICE - MIN_PRICE
答案：ACE
（6）View the Exhibit and examine the description of the EMPLOYEES table. You executed the following SQL statement: SELECT first_name, department_id, salary FROM employees ORDER BY department_id, first_name, salary desc；Which two statements are true regarding the output of the above query？(Choose two.)
A.The values in all the columns would be sorted in the descending order.
B.The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID column.
C.The values in the FIRST_NAME column would be sorted in ascending order for all the employees having the same value in the DEPARTMENT_ID column.
D.The values in the FIRST_NAME column would be sorted in the descending order for all the employees having the same value in the DEPARTMENT_ID column.
E.The values in the SALARY column would be sorted in descending order for all the employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
答案：CE
（7）Evaluate the following SQL statement: SELECT 2 col1,'y' col2 FROM dual UNION SELECT 1,'x' FROM dual UNION SELECT 3,NULL FROM dual ORDER BY 2; Which statement is true regarding the output of the SQL statement？
A.It would execute and the order of the values in the first column would be 3, 2, 1.
B.It would execute and the order of the values in the first column would be 1, 2, 3.
C.It would not execute because the column alias name has not been used in the ORDER BY clause.
D.It would not execute because the number 2 in the ORDER BY clause would conflict with the value 2 in the first SELECT statement
答案：B
備註：在對含有空值的列進行排序時，空值可以當作最大值
（8）View the Exhibit and examine the data in the PRODUCT_INFORMATION table. There are some products listed in the PRODUCT_INFORMATION table that have no value in the LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME for these products: SELECT product_name, list_price FROM product_information WHERE list_price = NULL；The query returns no rows. What changes would you make in the statement to get the desired result ？
A.Change the WHERE clause to WHERE list_price = 0
B.Change the WHERE clause to WHERE list_price = ' '.
C.Change the WHERE clause to WHERE list_price IS NULL.
D.In the WHERE clause, enclose NULL within single quotation marks.
E.In the WHERE clause, enclose NULL within double quotation marks.
答案：C
（9）View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
You want to see the product names and the date of expiration of warranty for all the products,
if the product is purchased today. The products that have no warranty should be displayed at
the top and the products with maximum warranty period should be displayed at the bottom.
Which SQL statement would you execute to fulfill this requirement?
A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM
product_information ORDER BY SYSDATE-warranty_period;
B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM
product_information ORDER BY SYSDATE+warranty_period;
C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM
product_information ORDER BY SYSDATE;
D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM
product_information WHERE warranty_period >SYSDATE;
答案：B
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-1836079/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OCP課程16：SQL之處理大資料SQL大資料
- OCP課程13：SQL之使用資料字典檢視管理物件SQL物件
- OCP課程18：SQL之管理不同時區下的資料SQL
- OCP課程39：管理Ⅰ之移動資料
- OCP課程19：SQL之使用子查詢檢索資料SQL
- OCP課程24：管理Ⅰ之資料庫安裝資料庫
- OCP課程56：管理II之SQL調優SQL
- OCP課程15：SQL之管理模式物件SQL模式物件
- OCP課程21：SQL之正規表示式SQL
- OCP課程12：SQL之建立其他模式物件SQL模式物件
- OCP課程9：SQL之使用集合運算子SQL
- OCP課程8：SQL之使用子查詢SQL
- OCP課程6：SQL之使用組函式SQL函式
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- OCP課程3：SQL之使用SELECT語句檢索資料SQL
- OCP課程60：管理Ⅰ之管理資料庫空間資料庫
- OCP課程61：管理II之複製資料庫資料庫
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- OCP課程26：管理Ⅰ之管理資料庫例項資料庫
- OCP課程50：管理II之診斷資料庫資料庫
- OCP課程5：SQL之使用單行函式SQL函式
- OCP課程17：SQL之透過分組相關資料產生報告SQL
- OCP課程23：管理Ⅰ之資料庫體系結構資料庫
- OCP課程53：管理II之使用閃回資料庫資料庫
- SQL菜鳥筆記之第六篇 資料的限制和排序 (下)SQL筆記排序
- SQL菜鳥筆記之第五篇 資料的限制和排序 (中)SQL筆記排序
- SQL菜鳥筆記之第四篇 資料的限制和排序 (上)SQL筆記排序
- OCP課程14：SQL之控制使用者訪問SQL
- OCP課程42：管理II之核心概念和工具
- OCP課程20：SQL之分層查詢SQL
- OCP課程7：SQL之多表查詢SQL
- OCP課程47：管理II之還原和恢復任務
- OCP課程27：管理Ⅰ之管理ASM例項ASM
- 陳安之課程資料下載
- OCP課程58：管理II之自動任務
- OCP課程54：管理II之管理記憶體記憶體
- OCP課程45：管理II之備份設定
- OCP 複習筆記之PL/SQL (4)筆記SQL