ocp培訓課程：SQL之限制和排序數據 使用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子句中常用的比較操作符如下表：

例子：查找薪水小于等于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子句中的邏輯操作符如下表：

例子：查找薪水大于等于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中的運算優先級如下表：

可以使用括號來改變優先級。

例子：查找薪水大于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。