PLSQL基本查询与排序.docx

资源描述

PLSQL基本查询与排序.docx

《PLSQL基本查询与排序.docx》由会员分享，可在线阅读，更多相关《PLSQL基本查询与排序.docx（36页珍藏版）》请在冰豆网上搜索。

PLSQL基本查询与排序.docx

PLSQL基本查询与排序

課程一PL/SQL基本查詢與排序

本課重點：

1、寫SELECT語句進行資料庫查詢

2、進行數學運算

3、處理空值

4、使用別名ALIASES

5、連接列

6、在SQLPLUS中編輯緩衝，修改SQLSCRIPTS

7、ORDERBY進行排序輸出。

8、使用WHERE欄位。

一、寫SQL命令：

不區分大小寫。

SQL語句用數字分行，在SQLPLUS中被稱為緩衝區。

最後以；或/結束語句。

也可以用RUN來執行語句

二、例1：

SQL>SELECTdept_id,last_name,manager_id

2FROMs_emp;

2：

SQL>SELECTlast_name,salary*12,commission_pct

2FROMs_emp;

對於數值或日期型的欄位，可以進行相應的四則運算，優先順序與標準的高階語言相同。

SQL>SELECTlast_name,salary,12*（salary+100）

2FROMs_emp;

三、列的別名ALIASES：

計算的時候特別有用；

緊跟著列名，或在列名與別名之間加“AS”；

如果別名中含有SPACE，特殊字元，或大小寫，要用雙引號引起。

例（因字體原因，讀者請記住：

引號為英文雙引號DoubleQuotation）：

SQL>SELECTlast_name,salary,

212*（salary+100）”AnnualSalary”

3FROMs_emp;

四、連接符號：

連接不同的列或連接字串

使結果成為一個有意義的短語：

SQL>SELECTfirst_name||’’||last_name

2||’,’||title”Employees”

3FROMs_emp;

五、管理NULL值：

SQL>SELECTlast_name,title,

2salary*NVL（commission_pct,0）/100COMM

3FROMs_emp;

此函數使NULL轉化為有意義的一個值，相當於替換NULL。

六、SQLPLUS的基本內容，請參考

七、ORDERBY操作：

與其他SQL92標準資料庫相似，排序如：

SELECTexpr

FROMtable

[ORDERBY{column,expr}[ASC|DESC]];

從Oracle7release7.0.16開始，ORDERBY可以用別名。

另：

通過位置判斷排序：

SQL>SELECTlast_name,salary*12

2FROMs_emp

3ORDERBY2;

這樣就避免了再寫一次很長的運算式。

另：

多列排序：

SQL>SELECTlastname,dept_id,salary

2FROMs_emp

3ORDERBYdept_id,salaryDESC;

八、限制選取行：

SELECTexpr

FROMtable

[WHEREcondition（s）]

[ORDERBYexpr];

例1：

SQL>SELECTfirst_name,last_name,start_date

2FROMs_emp

3WHEREstart_dateBETWEEN’09-may-91’

4AND’17-jun-91’;

例2：

SQL>SELECTlast_name

2FROMs_emp

3WHERElast_nameLIKE’_a%’; //顯示所有第二個字母為a的last_name

例3：

如果有列為NULL

SQL>SELECTid,name,credit_rating

2FROMs_customer

3WHEREsales_rep_idISNULL;

優先順序：

Order EvaluatedOperator

1 Allcomparisonoperators （=,<>,>,>=,<,<=,IN,LIKE,ISNULL,BETWEEN）

2 AND

3 OR

總結：

我們今天主要學習了如何進行查詢SELECT操作，具體的組合查詢與子查詢將在以後的課堂中

學習，同時希望大家可以工作、學習中多多摸索，實踐!

課程二PL/SQLPL/SQL查詢行函數

本課重點：

1、掌握各種在PL/SQL中可用的ROW函數

2、使用這些函數的基本概念

3、SELECT語句中使用函數

4、使用轉換函數

注意：

以下實例中標點均為英文半形

一、FUNCTION的作用：

進行資料計算，修改獨立的資料，處理一組記錄的輸出，不同日期顯示格式，進行資料類型轉換

函數分為：

單獨函數（ROW）和分組函數

注意：

可以嵌套、可以在SELECT,WHERE,和ORDERBY中出現。

語法：

function_name（column|expression,[arg1,arg2,...]）

二、字元型函數

1、LOWER轉小寫

2、UPPER

3、INITCAP 首字母大寫

4、CONCAT 連接字元，相當於||

5、SUBSTR SUBSTR（column|expression,m[,n]）

6、LENGTH 返回字串的長度

7、NVL 轉換空值

其中，1、2經常用來排雜，也就是排除插入值的大小寫混用的幹擾，如：

SQL>SELECTfirst_name,last_name

2FROMs_emp

3WHEREUPPER（last_name）=’PATEL’;

FIRST_NAMELAST_NAME

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

Vikram Patel

Radha Patel

三、數學運算函數

1、ROUND

四捨五入：

ROUND（45.923，2） =45.92

ROUND（45.923，0） =46

ROUND（45.923，-1） =50

2、TRUNC

截取函數

TRUNC（45.923，2）=45.92

TRUNC（45.923）=45

TRUNC（45.923，-1）=40

3、MOD 餘除

MOD（1600，300）

實例：

SQL>SELECTROUND（45.923,2）,ROUND（45.923,0）,

2ROUND（45.923,-1）

3FROMSYS.DUAL;

四、ORACLE日期格式和日期型函數：

1、默認格式為DD-MON-YY.

2、SYSDATE是一個求系統時間的函數

3、DUAL〔'dju:

el]是一個偽表，有人稱之為空表，但不確切。

SQL>SELECTSYSDATE

2FROMSYS.DUAL;

4、日期中應用的算術運算符

例：

SQL>SELECTlast_name,（SYSDATE-start_date）/7WEEKS

2FROMs_emp

3WHEREdept_id=43;

DATE+NUMBER=DATE

DATE-DATE=NUMBEROFDAYS

DATE+（NUMBER/24）=加1小時

5、函數：

MONTHS_BETWEEN（date1,date2）月份間隔，可正，可負，也可是小數

ADD_MONTHS（date,n）加上N個月，這是一個整數，但可以為負

NEXT_DAY（date,‘char’）如：

NEXT_DAY（restock_date,’FRIDAY’）,從此日起下個週五。

ROUND（date[,‘fmt’]）

TRUNC（date[,‘fmt’]）

解釋下面的例子：

SQL>SELECTid,start_date,

2MONTHS_BETWEEN（SYSDATE,start_date）TENURE,

3ADD_MONTHS（start_date,6）REVIEW

4FROMs_emp

5WHEREMONTHS_BETWEEN（SYSDATE,start_date）<48;

我們看到：

MONTHS_BETWEEN（SYSDATE,start_date）<48，說明至今工作未滿一年的員工。

LAST_DAY（restock_date）返回本月的最後一天

SQL>selectround（sysdate,'MONTH'）fromdual

ROUND（SYSD

----------

01-11月-01

round（sysdate,'YEAR'）=01-1月-02

ROUND之後的值比基值大的最小符合值，大家可以用更改系統時間的方法測試，以15天為分界線，

也是非常形象的四捨五入，而TRUNC恰好相反，是對現有的日期的截取。

五、轉換函數：

1、TO_CHAR

使一個數位或日期轉換為CHAR

2、TO_NUMBER

把字元轉換為NUMBER

3、TO_DATE

字元轉換為日期

這幾個函數較為簡單，但要多多實踐，多看複雜的實例。

SQL>SELECTID,TO_CHAR（date_ordered,’MM/YY’）ORDERED

2FROMs_ord

3WHEREsales_rep_id=11;

轉換時，要注意正確的缺省格式：

SELECTTO_DATE（'03-MAR-92'）CORRECTFROMDUAL；//正確

SELECTTO_DATE（'031092'）CORRECTFROMDUAL；//不正確

SELECTTO_DATE（'031095','MMDDYY'）ERRORRFROMDUAL

輸出3月10日

SELECTTO_DATE（'031095','DDMMYY'）ERRORRFROMDUAL

輸出10月3日

4、實例：

selectto_char（sysdate,'fmDDSPTH"of"MONTHYYYYAM'）TODAYSFROMDUAL;

TODAYS

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

SIXTEENTHof11月2001下午

大小寫沒有什麼影響，引號中間的是不參與運算。

實例：

SELECTROUND（SALARY*1.25）FROMONE_TABLE；

意義：

漲25%工資後，去除小數位。

在現實操作中，很有意義。

5、混合實例：

SQL>SELECTlast_name,TO_CHAR（start_date,

2’fmDD”of”MonthYYYY’）HIREDATE

3FROMs_emp

4WHEREstart_dateLIKE’%91’;

LAST_NAMEHIREDATE

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

Nagayama17ofJune1991

Urguhart18ofJanuary1991

Havel27ofFebruary1991

這裏要注意：

fmDD和fmDDSPTH之間的區別。

SQL>SELECTid,total,date_ordered

2FROMs_ord

3WHEREdate_ordered=

4TO_DATE（’September7,1992’,’Monthdd,YYYY’）;

六、獨立的函數嵌套

SQL>SELECTCONCAT（UPPER（last_name）,

2SUBSTR（title,3））”VicePresidents”

3FROMs_emp

4WHEREtitleLIKE’VP%’;

*嵌套可以進行到任意深度，從內向外計算。

例：

SQL>SELECTTO_CHAR（NEXT_DAY（ADD_MONTHS

2（date_ordered,6）,’FRIDAY’）,

3’fmDay,Monthddth,YYYY’）

4”New6MonthReview”

5FROMs_ord

6ORDERBYdate_ordered;

SQL>SELECTlast_name,

2NVL（TO_CHAR（manager_id）,’NoManager’）

3FROMs_emp

4WHEREmanager_idISNULL;

對於例子，大家重要的理解，並多做測試，並注意英文版和中文版在日期上的區別。

有些教材上的例子，不要盲目的相信其結果，實踐後才有發言權，希望大家能夠在學習的過程中

不要忽略了用，

多想一想為什麼實例要如此設計，在何種情況下應用此實例來解決問題。

這樣，我們才真正掌握了知識。

14:

PL/Sql循序漸進全面學習教程--Oracle

（2）

課程三從多個表中提取資料

本課重點：

1、SELECTFROM多個表，使用等連接或非等連接

2、使用外連接OUTERJOIN

3、使用自連接

注意：

以下實例中標點均為英文半形

一、連接的概念：

是指一個從多個表中的資料進行的查詢。

連接一般使用表的主鍵和外鍵。

連接類型：

等連接、不等連接、外連接、自連接

二、Cartesianproduct：

指的是當JOIN條件被省略或無效時，所有表的行（交叉）都被SELECT出來的現象。

Cartesianproduct可以產生大量的記錄，除非是你有意如此，否則應該加上某種條件限制。

SQL>SELECTname,last_name

2FROMs_dept,s_emp;

300rowsselected.其中一個表12行，一個表25行。

三、簡單連接查詢：

SELECTtable.column,table.column...

FROMtable1,table2

WHEREtable1.column1=table2.column2;

如：

SQL>SELECTs_emp.last_name,s_emp.dept_id,

2s_dept.name

3FROMs_emp,s_dept

4WHEREs_emp.dept_id=s_dept.id;

注意：

表首碼的重要性：

SQL>SELECTs_dept.id”DepartmentID”,

2s_region.id”RegionID”,

3s_region.name”RegionName”

4FROMs_dept,s_region

5WHEREs_dept.region_id=s_region.id;

在WHERE段中，如果沒有首碼，兩個表中都有ID欄位，就顯得的模棱兩可，AMBIGUOUS。

這在實際中應該儘量避免。

WHERE欄位中，還可以有其他的連接條件，如在上例中，加上：

INITCAP（s_dept.last_name）=’Menchu’;

再如：

WHEREs_emp.dept_id=s_dept.idANDs_dept.region_id=s_region.idAND

s_mission_pct>0;

四、表別名ALIAS：

1、使用別名進行多表查詢。

2、僅在這個查詢中生效，一旦用了表別名，就不能再用表的原有的名字進行連接。

實例：

SQL>SELECTc.name”CustomerName”,

2c.region_id”RegionID”,

3r.name”RegionName”

4FROMs_customerc,s_regionr

5WHEREc.region_id=r.id;

別名最多可以30個字元，但當然越少越好。

最好也能容易識別。

五、非等連接

非等連接一般用在沒有明確的等量關係的兩個表；

最簡單的說：

非等連接就是在連接中沒有“=”出現的連接。

SQL>SELECTe.ename,e.job,e.sal,s.grade

2FROMempe,salgrades

3WHEREe.salBETWEENs.losalANDs.hisal;

說明：

Createanon-equijointoevaluateanemployee’ssalarygrade.Thesalary必須在另一個

表中最高和最低之間。

其他操作符<=>=也可以實現，但是BETWEEN是非常簡單實用的。

BETWEEN....AND是指閉區間的，這點要注意，請大家測試。

六、外連接

語法結構：

SELECTtable.column,table.column

FROMtable1,table2

WHEREtable1.column=table2.column（+）;

實例：

SQL>SELECTe.last_name,e.id,c.name

2FROMs_empe,s_customerc

3WHEREe.id（+）=c.sales_rep_id

4ORDERBYe.id;

顯示.....，即使有的客戶沒有銷售代表。

*可以理解為有+號的一邊出現了NULL，也可以做為合法的條件。

外連接的限制：

1、外連接符只能出現在資訊缺少的那邊。

2、在條件中，不能用IN或者OR做連接符。

七、自連接

同一個表中使用連接符進行查詢；

FROM的後面用同一個表的兩個別名。

實例：

SQL>SELECTworker.last_name||’worksfor’||

2manager.last_name

3FROMs_empworker,s_empmanager

4WHEREworker.manager_id=manager.id;

意味著：

一個員工的經理ID匹配了經理的員工號，但這個像繞口令的連接方式並不常用。

以後我們會見到一種子查詢：

selectlast_namefroms_empwheresalary=（selectmax（salary）froms_emp）

也可以看作是一種變向的自連接，但通常我們將其

課程四組函數

本課重點：

1、瞭解可用的組函數

展开阅读全文