OCP課程3：SQL之使用SELECT語句檢索資料

select語句有三個功能：投影、選擇和多表聯合，投影指選擇表的某些列，選擇指選擇表的某些行，多表聯合指從多張表獲取資料。這一章主要講投影的功能。

 

 

1、基本SELECT語句語法

包含select子句和from子句：

• select子句列出要選擇的列，其中*表示選擇表中的所有列，distinct關鍵字表示去掉重複的列，多列之間使用逗號隔開，也可以使用表示式，可以為列或者表示式指定一個別名
• from子句指明這些列來自於哪張表

 

例子：使用*選擇所有表中列

SQL> select * from departments;

 

例子：使用列名字選擇指定的列

SQL> select department_id,location_id from departments;

 

 

2、SQL語句的一些書寫規範

• SQL語句不區分大小寫
• SQL語句可以寫在一行，也可以跨行
• 關鍵字不能縮寫及跨行
• 子句一般單獨一行
• 可以使用縮排提高可讀性
• 在SQL*PLUS中使用分號作為語句的結束

3、列的預設顯示

在SQL Developer中：

• 列的對齊方式：居中
• 列頭顯示方式：大寫

在SQL*PLUS和PL/SQL Developer中：

• 字元和日期列左對齊
• 數字列右對齊
• 列頭顯示方式：大寫

 

 

4、算術表示式

使用算術運算子建立表示式。

 

例子：使用+

SQL> SELECT last_name,salary,salary+300 from employees;

 

例子：使用*和+

SQL> SELECT last_name,salary,12*salary+100 from employees;

 

例子：使用括號改變運算優先順序

SQL> SELECT last_name,salary,12*(salary+100) from employees;

 

 

5、null

null表示一個未知的值，既不是零也不是空格。

 

例子：檢視人員表中的提成，其中有的人的提成為null

SQL> SELECT last_name,job_id,salary,commission_pct from employees;

 

null具有傳染性，也就是說null和任何值進行運算都為null。

 

例子：使用包含null的提成欄位進行算術運算，結果也是null

SQL> select last_name,12*salary*commission_pct from employees;

 

 

6、定義列別名

前面的SQL語句一個列的名字為12*SALARY*COMMISSION_PCT，很長，我們可以使用一個有意義的簡短別名來替代他，更易讀。

可以直接在列名字後面加上別名，也可以使用as關鍵字。

 

例子：使用as關鍵字加列別名以及直接加上列別名

SQL> select last_name as name,commission_pct comm from employees;

NAME                            COMM

------------------------- ----------

OConnell

 

別名和列名一樣，預設都是以大寫顯示，如果別名包含空格，特殊字元或者不想使用大寫顯示，就需要加上雙引號。

 

例子：加上雙引號的別名

SQL> select last_name "Name",salary*12 "Annual Salary" from employees;

Name                      Annual Salary

------------------------- -------------

OConnell                          31200

 

 

7、連線運算子||

Oracle中的連線運算子使用兩個豎槓表示，可以將列或者字串與其他列連線起來，可以把多個欄位連線成一個欄位來顯示。

 

例子：2個欄位連線成1個欄位顯示

SQL> select last_name||job_id as "Employees" from employees;

Employees

-----------------------------------

AbelSA_REP

AndeSA_REP

AtkinsonST_CLERK

 

 

8、字變數

• 字變數是select語句中個一個字元，一個數字或者一個日期
• 日期和字元字變數的值必須使用單引號括起來
• 輸出的每一行都會顯示一次字變數字元

 

例子：使用單引號將字元字變數括起來，每一行都會顯示一次這個字變數

SQL> select last_name||' is a '||job_id as "Employee Details" from employees;

Employee Details

-----------------------------------------

Abel is a SA_REP

Ande is a SA_REP

Atkinson is a ST_CLERK

 

例子：把人員表某些資料轉換成insert語句，複製到其他資料庫去執行，滿足臨時少量資料遷移的需求

SQL> select 'insert into employee(employee_id,last_name) values(' || employee_id || ',''' || last_name || ''');' as ttt from employees;

TTT

--------------------------------------------------------------------------------

insert into employee(employee_id,last_name) values(174,'Abel');

insert into employee(employee_id,last_name) values(166,'Ande');

 

這裡如果要顯示字變數裡面的單引號，那就使用兩個單引號，也可以使用q運算子，同時還需要加上分割符號，可以是方框，問號，小括號，但是必須要配對。

 

例子：使用q運算子顯示字變數裡面的單引號

SQL> select department_name||q'[,it's assigned Manager Id: ]'||manager_id AS "Department and Manager" from departments;

Department and Manager

--------------------------------------------------------------------------------

Administration,it's assigned Manager Id: 200

Marketing,it's assigned Manager Id: 201

這種方式平常用得少一些

 

 

9、使用distinct去掉重複的結果

使用select語句查詢的結果預設顯示所有的行，包括重複的行，可以使用distinct關鍵字去重。

 

例子：對比不使用與使用distinct的結果

SQL> select department_id from employees;

DEPARTMENT_ID

-------------

           50

不使用distinct，結果有107行

SQL> select distinct department_id from employees;

DEPARTMENT_ID

-------------

          100

使用distinct，結果只有12行

 

例子：distinct後面跟多個欄位，表示多個欄位聯合起來唯一

SQL> select distinct department_id,manager_id from employees;

DEPARTMENT_ID MANAGER_ID

------------- ----------

           40        101

 

 

10、SQL開發環境

我們平常用得更多的SQL開發環境是PL/SQL Developer，這裡介紹了Oracle自己的SQL Developer，我們簡單看一下。

首先啟動SQL Developer。

[ ~]# su - oracle
[ ~]$ export DISPLAY=192.168.230.1:0.0

[ ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/sqldeveloper/
[ sqldeveloper]$ ./sqldeveloper.sh 

新建連線。

輸入連線名稱、使用者名稱、密碼、伺服器地址、埠及SID，點選“Test”，測試成功，點選“Connect”，進行連線。

就可以看到相關物件了。

這個SQL Developer功能和PL/SQL Developer差不多，平時用得也比較少，就不多講了。

 

 

11、相關習題

（1）Evaluate the following SQL statement: SELECT product_name ||'it's not available for order' FROM product_information WHERE product_status = 'obsolete'；You received the following error while executing the above query: ERROR: ORA-01756: quoted string not properly terminated；What would you do to execute the query successfully？
A.Enclose the character literal string in the SELECT clause within the double quotation marks.
B.Do not enclose the character literal string in the SELECT clause within the single quotation marks.
C.Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
D.Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

答案：C