《SQL語言基礎》PPT課件.ppt

1、第6章 SQL語言基礎,第6章 SQL語言基礎,目前，無論是像Oracle、SQL Server、DB2、Sybase等這些大型的數(shù)據(jù)庫管理系統(tǒng)，還是像Visual FoxPro、MySQL、Access等中小型的數(shù)據(jù)庫管理系統(tǒng)，都支持SQL作為數(shù)據(jù)查詢語言。,6.1 SQL語言概述,6.1.1 SQL語言的功能 SQL語言主要有數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制等功能。 1.數(shù)據(jù)定義 2.數(shù)據(jù)操縱 3.數(shù)據(jù)控制 4.嵌入功能,6.1 SQL語言概述,6.1.2 SQL語言的特點 1.綜合統(tǒng)一 2.高度非過程化 3.統(tǒng)一的語法結構 4.面向集合的操作方式 5.語言簡潔，易學易用,6.1 SQL語言概

2、述,6.1.3 PL/SQL與SQL的關系 PL/SQL（Procedural Language/SQL，過程化SQL）也是一種程序設計語言，是Oracle公司對標準SQL語言的過程化擴展，PL/SQL在普通SQL語句的使用上增加了編程語言的特點，所以PL/SQL就可以把數(shù)據(jù)操作和查詢語句組織在PL/SQL代碼的過程性單元中，通過邏輯判斷、循環(huán)等操作實現(xiàn)復雜的功能或者計算。PL/SQL主要用于在Oracle數(shù)據(jù)庫系統(tǒng)上進行數(shù)據(jù)操作和開發(fā)應用。,6.2 數(shù)據(jù)定義,數(shù)據(jù)定義功能是針對數(shù)據(jù)對象進行定義，主要包括表、視圖和索引、觸發(fā)器、存儲過程和程序包等。,6.3 數(shù)據(jù)操縱,數(shù)據(jù)操縱包括數(shù)據(jù)查詢和數(shù)據(jù)

3、更新兩大類操作，是通過數(shù)據(jù)操縱語言DML來實現(xiàn)。數(shù)據(jù)查詢操作是通過SQL的SELECT語句來完成。數(shù)據(jù)更新包括數(shù)據(jù)插入、刪除和修改操作，對應SQL的INSERT、DELETE、UPDATE語句。在Oracle 11g中，數(shù)據(jù)操縱語句除了上述語句外，還包括TRUNCATE、CALL、EXPLAIN PLAN、LOCK TABLE語句等。,6.4 數(shù)據(jù)控制,數(shù)據(jù)控制功能主要包括數(shù)據(jù)庫的事務管理功能和數(shù)據(jù)保護功能等，即對數(shù)據(jù)庫的恢復、并發(fā)控制、安全性和完整性控制等，而這些功能都是通過對各種數(shù)據(jù)庫對象的各種操作權限進行管理而實現(xiàn)的。,6.5 基本數(shù)據(jù)查詢,6.5.1 數(shù)據(jù)查詢基本結構 SELECT語句

4、的基本格式為： SELECT ALL | DISTINCT TOP n PENCERT , INTO FROM ,數(shù)據(jù)源表名或視圖名 WHERE GROUP BY HAVING ORDER BY ASC | DESC ,6.5 基本數(shù)據(jù)查詢,6.5.2 簡單查詢 1.查詢所有列 【例6.1】查詢表scott.emp表中所有列，可以使用如下語句： SELECT * FROM scott.emp; 2.查詢指定列 【例6.2】查詢表scott.emp表中的ename列，語句如下：說 明 SELECT ename FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.2 簡單查詢 3.改變列

5、標題 其語法格式如下： SELECT AS ， AS ， FROM 【例6.3】查詢表scott.emp表中的ename列將其列名改為雇員姓名，語句如下： SELECT ename AS 雇員姓名 FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.2 簡單查詢 4.查詢經(jīng)過計算的值 【例6.4】下列查詢語句就是進行運算的結果，查詢emp表中的兩項工資的總和，執(zhí)行結果如圖6-2所示。 SELECT sal+comm FROM scott.emp; 5.利用DISTINCT關鍵字消除取值重復的行 【例6.5】消除emp表中job列的重復值的語句如下，執(zhí)行結果如下圖。 SELECT DI

6、STINCT job FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.3 使用WHERE子句進行篩選 1.比較運算符 【例6.6】查看scott.emp表中在10號部門的所有員工具體情況。 SELECT * FROM scott.emp WHERE deptno=10; 2.確定范圍 【例6.7】查詢scott.emp表中工資在1500和3000之間的雇員編號。 SELECT empno FROM scott.emp WHERE sal BETWEEN 1500 and 3000;,6.5 基本數(shù)據(jù)查詢,3.確定集合 【例6.8】查詢scott.emp表中在10，20或30號部門

7、工作的雇員編號。 SELECT * FROM scott.emp WHERE deptno IN(10, 20, 30); 4.字符匹配 謂詞LIKE可以用來進行字符串的匹配。其一般語法格式如下： 列表 NOT LIKE 【例6.9】查詢名字中含有K字母的雇員姓名。 SELECT ename FROM scott.emp WHERE ename LIKE %K%;,6.5 基本數(shù)據(jù)查詢,5.涉及空值NULL的查詢 【例6.10】查詢comm 列為NULL的雇員信息。 SELECT * FROM scott.emp WHERE comm IS NULL; 6.多重條件查詢 多重條件查詢也稱為復合

8、條件查詢，是指WHERE子句后有多個查詢條件，使用邏輯運算符（AND、OR、NOT）將多個條件聯(lián)接起來，組成復合查詢條件。 【例6.11】查詢10號部門工資在2000元以上的雇員信息。 SELECT * FROM scott.emp WHERE deptno=10 and sal2000;,6.5 基本數(shù)據(jù)查詢,6.5.4 使用ORDER BY子句進行查詢的排序 使用ORDER BY子句的語法格式如下： SELECT ALL | DISTINCT TOP n PENCERT , FROM ORDER BY ASC | DESC 【例6.12】請將emp表中元組按照部門號升序、員工編號降序排列。

9、 SELECT * FROM scott.emp ORDER BY deptno ASC,empno DESC;,6.5 基本數(shù)據(jù)查詢,6.5.5 使用GROUP BY子句進行分組查詢 GROUP BY子句可以將查詢結果集的各行按一列或多列取值相等的原則進行分組。分組的目的是為了能為每個分組生成其匯總信息，即細化聚合函數(shù)的作用對象為每一組。 例6.13統(tǒng)計emp表中各個部門的人數(shù)。 select deptno ,count(*) from scott.emp Group by deptno;,6.5 基本數(shù)據(jù)查詢,6.5.6 使用HAVING子句對分組進行篩選 HAVING子句通常與GROUP

10、 BY子句一起使用，其作用是在在完成對分組結果統(tǒng)計后，再使用HAVING子句對分組結果進行進一步的篩選。 【6.14】統(tǒng)計各個部門人數(shù)，將多于5人的部門編號輸出。 Select deptno from scott.emp group by deptno Having count(*)5;,6.5 基本數(shù)據(jù)查詢,6.5.7 使用INTO子句將查詢結果存儲到表中 在SELECT語句中使用INTO子句可以將查詢的結果存儲到一個新建的數(shù)據(jù)表或臨時表中。 例6.15 將emp表中的內(nèi)容復制到newemp中。 Create table newemp(neweno number(4); Insert int

11、o newemp1 select empno from scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.8 常用函數(shù) 在數(shù)據(jù)庫查詢中，經(jīng)常要對查詢的數(shù)據(jù)進行統(tǒng)計計算，如求平均值、總和、平方差等，本節(jié)介紹Oracle數(shù)據(jù)庫系統(tǒng)提供的主要函數(shù)。 1字符函數(shù) 下面介紹常用的幾個字符函數(shù)的用法。 （1）ASCII()和CHR() ASCII()和CHR()是兩個基于ASCII的函數(shù)，ASCII(x)函數(shù)用于獲得字符串x最左邊字符的ASCII碼，CHR(x)函數(shù)用于獲得ASCII碼值為x的對應的字符。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 【例6.13】查看A的ASCII碼。 SELECT A

12、SCII(A) FROM dual; 結果是65，即函數(shù)返回字符“A”的ASCII碼值。 【例6.14】查看ASCII碼為65的字符。 SELECT CHR(65) FROM dual; 結果是字母A。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （2）LENGTH() LENGTH(x)函數(shù)用于獲得x字符串中字符的個數(shù)，即返回字符串的長度。 【例6.15】使用LENGTH函數(shù)求字符串 This is Oracle 11g。 SELECT length(This is Oracle 11g) FROM dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （3）CONCAT() CONCAT

13、(x, y)函數(shù)用于將字符串y添加在字符串x之后，即將兩個字符串連接起來形成一個字符串。 【例6.16】使用CONCAT函數(shù)將EMP表中各元組的ename添加在job之后。 SELECT CONCAT(job,ename) FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （4）LOWER()和UPPER() LOWER()將字符串全部轉為小寫，而UPPER()將字符串全部轉為大寫。 （5）LTRIM()、RTRIM()和TRIM() LTRIM(x , trim_string)函數(shù)用于從x的左邊截去一些字符，該函數(shù)還可以使用可選的參數(shù)trim_string來指定要截

14、去的字符；如果沒有指定trim_string參數(shù)，默認情況下會截去空格。同理，RTRIM函數(shù)用于從x的右邊截去一些字符，TRIM函數(shù)用于從x的左邊和右邊各截去一些字符。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （ 6 ）SUBSTR () SUBSTRING (， ， length) 返回從字符串expression左邊第starting_ position 個字符起length個字符的部分。 Select substr(xiao ming is a good man,16,8) From dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 2數(shù)字函數(shù) 下面介紹幾個常用的數(shù)字函數(shù)的具體

15、用法。 （1）ABS() ABS(x)用于得到x的絕對值。 （2）MOD() MOD(x, y)函數(shù)返回x除以y所得的余數(shù)。 （3）CEIL() CEIL(x)返回大于或等于x的最小整數(shù)，即將參數(shù)x向上取整。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 2數(shù)字函數(shù) （4）FLOOR() FLOOR(x)用于獲得小于或等于x的最大整數(shù)，即將參數(shù)x向下取整。 （5）POWER() POWER(x, y)用于計算x的y次冪。 （6）ROUND() ROUND(x, y)用于計算對x取整的結果，即對x進行四舍五入計算，并返回；其中y為可選參數(shù)，說明對第幾位小數(shù)取整。,6.5 基本數(shù)據(jù)查詢,6.5.5

16、常用函數(shù) 2數(shù)字函數(shù) （7）SQRT() SQRT(x)函數(shù)用于計算x的平方根，負數(shù)無意義。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 3聚合函數(shù) 在實際應用中，常常需要對表中的數(shù)據(jù)進行分類、統(tǒng)計、匯總等操作，如統(tǒng)計公司的人數(shù)、平均工資、最高工資等，這些都需要聚合函數(shù)來實現(xiàn)。聚合函數(shù)也被稱為聚集函數(shù)、分組函數(shù)、統(tǒng)計函數(shù)。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （1）AVG()函數(shù) AVG()函數(shù)用于返回一組數(shù)值中所有非空數(shù)值的平均值。 （2）MAX()和MIN()函數(shù) MIN()函數(shù)用于返回一個列范圍內(nèi)的最小非空值；MAX()函數(shù)用于返回最大值。 （3）COUNT()函數(shù) COUNT(

17、)函數(shù)用于返回一個列內(nèi)所有非空值的個數(shù)，這是一個整型值。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （4）SUM()函數(shù) SUM()函數(shù)是最常用的聚合函數(shù)之一，其功能是返回一個列范圍內(nèi)所有非空值的總和。和AVG()函數(shù)一樣，它用于數(shù)值數(shù)據(jù)類型。 4日期時間函數(shù) Oracle數(shù)據(jù)庫系統(tǒng)提供了豐富的日期時間函數(shù)來處理日期類型數(shù)據(jù)，有些函數(shù)可用于解析日期值的日期與時間部分，有些函數(shù)可用于比較、操縱日期/時間值。,6.5 基本數(shù)據(jù)查詢,4日期時間函數(shù) （1）LAST_DAY() LAST_DAY()返回指定日期所在月最后一天的日期，通常被用來確定當前月中還剩下多少天。 【例6.21】執(zhí)行下列語句求當

18、前月份的最后一天。 SELECT LAST_DAY(SYSDATE) FROM DUAL; （2）MONTHS_BETWEEN() 【例6.22】下列例子求當前日期與2012年12月20日之間相隔的月數(shù)。 SELECT MONTHS_BETWEEN(SYSDATE,20-12月-2012) FROM DUAL;,6.5 基本數(shù)據(jù)查詢,4日期時間函數(shù) （3）NEXT_DAY() 【例6.23】下列例子求下一個星期一的日期。 SELECT NEXT_DAY(SYSDATE,星期一) FROM DUAL; (4)獲取系統(tǒng)時間 Select sysdate from dual; Select curr

19、ent_date from dual; (5)設置日期格式 Alter session set nls_date_format=yyyy-mm-dd hh:mi:ss;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 5轉換函數(shù) （1）CAST() CAST(x AS type)用來將x轉換為由type指定的兼容數(shù)據(jù)庫類型。 【例6.27】下面查詢展示了使用CAST()將字面值轉換為指定的類型： SELECT CAST(12345.67 AS VARCHAR2(10), CAST(9A4F AS RAW(2), CAST(12345.678 AS NUMBER(10,2) FROM dual;,6

20、.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （2）CONVERT() 對于簡單類型轉換，CONVERT()函數(shù)和CAST()函數(shù)的功能相同，只是語法不同。 【例6.28】下面的例子演示CONVERT()函數(shù)的使用： Select convert(搴旇鏄彲浠殑, ZHS16GBK, UTF8) From dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （3）TO_CHAR() TO_CHAR(x , format)函數(shù)用于將x轉換為一個字符串，可選參數(shù)format是格式化控制符，用來指定轉換后x的格式。結構format取決于x是數(shù)字還是日期。 Select to_char(12345.66

21、) from dual; Select to_char(sysdate) from dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （4）TO_NUMBER() TO_NUMBER(x , format)函數(shù)用于將x轉換為一個數(shù)字，可選參數(shù)format字符串用來指定轉換后的格式。 【例6.27】下面這個查詢使用TO_NUMBER函數(shù)將字符串970.13轉換為一個數(shù)字，然后再在其基礎上加上25.5： SELECT TO_NUMBER(970.13) + 25.5 FROM dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 6正則表達式函數(shù) 使用正則表達式函數(shù)可以在字符串中搜索字符模

22、式。 下面介紹幾個常用正則表達式函數(shù)的具體用法。 （1）REGEXP_LIKE() REGEXP_LIKE(x, pattern , match_option)函數(shù)用于在x中查找pattern參數(shù)中定義的正則表達式。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （2）REGEXP_INSTR() REGEXP_INSTR(x, pattern , start , occurrence , return_option , match_option)用于在x中查找pattern；REGEXP_INSTR()返回pattern出現(xiàn)的位置，匹配位置從1開始。 （3）REGEXP_REPLACE() R

23、EGEXP_REPLACE(x, pattern , replace_string , start , occurrence, match_option)用于在x中查找pattern，并將其替換為replace_string。 （4）REGEXP_COUNT() REGEXP_COUNT(x, pattern, start ,match_option)用于在x中查找pattern，并返回pattern在x中出現(xiàn)的次數(shù)。,6.6 高級數(shù)據(jù)查詢,6.6.1 多表連接查詢 1定義連接的兩種形式 定義連接有兩種形式，一種是在WHERE子句中定義，另一種是在FROM子句中定義。 （1）在WHERE子句中

24、定義連接 在WHERE子句中定義連接的查詢語句基本格式為： SELECT 表名.列名1, 表名.列名2, FROM 表名1, 表名2 WHERE 表名1.列名表名1.列名;,6.6 高級數(shù)據(jù)查詢,6.6.1 多表連接查詢 （2）在FROM子句中定義連接 在FROM子句中定義連接的查詢語句基本格式為： SELECT 表名.列名1, 表名.列名2, FROM 表名1 表名2 ON （連接條件） WHERE 其中：連接類型可以是：INNER JOIN（內(nèi)連接）、OUTER JOIN（外連接）、CROSS JOIN（交叉連接）。,6.6 高級數(shù)據(jù)查詢,6.6.1 多表連接查詢 2內(nèi)連接 內(nèi)連接使用INNER JOIN連接關鍵字，其連接格式為： FROM 表名1 INNER JOIN 表名2 ON （連接條件） （1）等值連接 在連接條件中使用“=”連接運算符時，稱為等值連接。 （2）非等值連接 在連接條件中使用除“=”以外的其它運算符（、=、）來比較被連接列的列值時，稱為非等值連接。,6.6 高級數(shù)據(jù)查詢,6.6.1 多表連接查詢 3外連接 如果查詢結果集包含來自一個表的所有行和另一個表中的匹配行，那么這種連接稱為外連接