《SQL語(yǔ)言基礎(chǔ)》PPT課件.ppt

第6章 SQL語(yǔ)言基礎(chǔ),第6章 SQL語(yǔ)言基礎(chǔ),目前，無(wú)論是像Oracle、SQL Server、DB2、Sybase等這些大型的數(shù)據(jù)庫(kù)管理系統(tǒng)，還是像Visual FoxPro、MySQL、Access等中小型的數(shù)據(jù)庫(kù)管理系統(tǒng)，都支持SQL作為數(shù)據(jù)查詢語(yǔ)言。,6.1 SQL語(yǔ)言概述,6.1.1 SQL語(yǔ)言的功能 SQL語(yǔ)言主要有數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制等功能。 1.數(shù)據(jù)定義 2.數(shù)據(jù)操縱 3.數(shù)據(jù)控制 4.嵌入功能,6.1 SQL語(yǔ)言概述,6.1.2 SQL語(yǔ)言的特點(diǎn) 1.綜合統(tǒng)一 2.高度非過(guò)程化 3.統(tǒng)一的語(yǔ)法結(jié)構(gòu) 4.面向集合的操作方式 5.語(yǔ)言簡(jiǎn)潔，易學(xué)易用,6.1 SQL語(yǔ)言概述,6.1.3 PL/SQL與SQL的關(guān)系 PL/SQL（Procedural Language/SQL，過(guò)程化SQL）也是一種程序設(shè)計(jì)語(yǔ)言，是Oracle公司對(duì)標(biāo)準(zhǔn)SQL語(yǔ)言的過(guò)程化擴(kuò)展，PL/SQL在普通SQL語(yǔ)句的使用上增加了編程語(yǔ)言的特點(diǎn)，所以PL/SQL就可以把數(shù)據(jù)操作和查詢語(yǔ)句組織在PL/SQL代碼的過(guò)程性單元中，通過(guò)邏輯判斷、循環(huán)等操作實(shí)現(xiàn)復(fù)雜的功能或者計(jì)算。PL/SQL主要用于在Oracle數(shù)據(jù)庫(kù)系統(tǒng)上進(jìn)行數(shù)據(jù)操作和開發(fā)應(yīng)用。,6.2 數(shù)據(jù)定義,數(shù)據(jù)定義功能是針對(duì)數(shù)據(jù)對(duì)象進(jìn)行定義，主要包括表、視圖和索引、觸發(fā)器、存儲(chǔ)過(guò)程和程序包等。,6.3 數(shù)據(jù)操縱,數(shù)據(jù)操縱包括數(shù)據(jù)查詢和數(shù)據(jù)更新兩大類操作，是通過(guò)數(shù)據(jù)操縱語(yǔ)言DML來(lái)實(shí)現(xiàn)。數(shù)據(jù)查詢操作是通過(guò)SQL的SELECT語(yǔ)句來(lái)完成。數(shù)據(jù)更新包括數(shù)據(jù)插入、刪除和修改操作，對(duì)應(yīng)SQL的INSERT、DELETE、UPDATE語(yǔ)句。在Oracle 11g中，數(shù)據(jù)操縱語(yǔ)句除了上述語(yǔ)句外，還包括TRUNCATE、CALL、EXPLAIN PLAN、LOCK TABLE語(yǔ)句等。,6.4 數(shù)據(jù)控制,數(shù)據(jù)控制功能主要包括數(shù)據(jù)庫(kù)的事務(wù)管理功能和數(shù)據(jù)保護(hù)功能等，即對(duì)數(shù)據(jù)庫(kù)的恢復(fù)、并發(fā)控制、安全性和完整性控制等，而這些功能都是通過(guò)對(duì)各種數(shù)據(jù)庫(kù)對(duì)象的各種操作權(quán)限進(jìn)行管理而實(shí)現(xiàn)的。,6.5 基本數(shù)據(jù)查詢,6.5.1 數(shù)據(jù)查詢基本結(jié)構(gòu) SELECT語(yǔ)句的基本格式為： 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 簡(jiǎn)單查詢 1.查詢所有列 【例6.1】查詢表scott.emp表中所有列，可以使用如下語(yǔ)句： SELECT * FROM scott.emp; 2.查詢指定列 【例6.2】查詢表scott.emp表中的ename列，語(yǔ)句如下：說(shuō) 明 SELECT ename FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.2 簡(jiǎn)單查詢 3.改變列標(biāo)題 其語(yǔ)法格式如下： SELECT AS ， AS ， FROM 【例6.3】查詢表scott.emp表中的ename列將其列名改為雇員姓名，語(yǔ)句如下： SELECT ename AS 雇員姓名 FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.2 簡(jiǎn)單查詢 4.查詢經(jīng)過(guò)計(jì)算的值 【例6.4】下列查詢語(yǔ)句就是進(jìn)行運(yùn)算的結(jié)果，查詢emp表中的兩項(xiàng)工資的總和，執(zhí)行結(jié)果如圖6-2所示。 SELECT sal+comm FROM scott.emp; 5.利用DISTINCT關(guān)鍵字消除取值重復(fù)的行 【例6.5】消除emp表中job列的重復(fù)值的語(yǔ)句如下，執(zhí)行結(jié)果如下圖。 SELECT DISTINCT job FROM scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.3 使用WHERE子句進(jìn)行篩選 1.比較運(yùn)算符 【例6.6】查看scott.emp表中在10號(hào)部門的所有員工具體情況。 SELECT * FROM scott.emp WHERE deptno=10; 2.確定范圍 【例6.7】查詢scott.emp表中工資在1500和3000之間的雇員編號(hào)。 SELECT empno FROM scott.emp WHERE sal BETWEEN 1500 and 3000;,6.5 基本數(shù)據(jù)查詢,3.確定集合 【例6.8】查詢scott.emp表中在10，20或30號(hào)部門工作的雇員編號(hào)。 SELECT * FROM scott.emp WHERE deptno IN(10, 20, 30); 4.字符匹配 謂詞LIKE可以用來(lái)進(jìn)行字符串的匹配。其一般語(yǔ)法格式如下： 列表 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.多重條件查詢 多重條件查詢也稱為復(fù)合條件查詢，是指WHERE子句后有多個(gè)查詢條件，使用邏輯運(yùn)算符（AND、OR、NOT）將多個(gè)條件聯(lián)接起來(lái)，組成復(fù)合查詢條件。 【例6.11】查詢10號(hào)部門工資在2000元以上的雇員信息。 SELECT * FROM scott.emp WHERE deptno=10 and sal2000;,6.5 基本數(shù)據(jù)查詢,6.5.4 使用ORDER BY子句進(jìn)行查詢的排序 使用ORDER BY子句的語(yǔ)法格式如下： SELECT ALL | DISTINCT TOP n PENCERT , FROM ORDER BY ASC | DESC 【例6.12】請(qǐng)將emp表中元組按照部門號(hào)升序、員工編號(hào)降序排列。 SELECT * FROM scott.emp ORDER BY deptno ASC,empno DESC;,6.5 基本數(shù)據(jù)查詢,6.5.5 使用GROUP BY子句進(jìn)行分組查詢 GROUP BY子句可以將查詢結(jié)果集的各行按一列或多列取值相等的原則進(jìn)行分組。分組的目的是為了能為每個(gè)分組生成其匯總信息，即細(xì)化聚合函數(shù)的作用對(duì)象為每一組。 例6.13統(tǒng)計(jì)emp表中各個(gè)部門的人數(shù)。 select deptno ,count(*) from scott.emp Group by deptno;,6.5 基本數(shù)據(jù)查詢,6.5.6 使用HAVING子句對(duì)分組進(jìn)行篩選 HAVING子句通常與GROUP BY子句一起使用，其作用是在在完成對(duì)分組結(jié)果統(tǒng)計(jì)后，再使用HAVING子句對(duì)分組結(jié)果進(jìn)行進(jìn)一步的篩選。 【6.14】統(tǒng)計(jì)各個(gè)部門人數(shù)，將多于5人的部門編號(hào)輸出。 Select deptno from scott.emp group by deptno Having count(*)5;,6.5 基本數(shù)據(jù)查詢,6.5.7 使用INTO子句將查詢結(jié)果存儲(chǔ)到表中 在SELECT語(yǔ)句中使用INTO子句可以將查詢的結(jié)果存儲(chǔ)到一個(gè)新建的數(shù)據(jù)表或臨時(shí)表中。 例6.15 將emp表中的內(nèi)容復(fù)制到newemp中。 Create table newemp(neweno number(4); Insert into newemp1 select empno from scott.emp;,6.5 基本數(shù)據(jù)查詢,6.5.8 常用函數(shù) 在數(shù)據(jù)庫(kù)查詢中，經(jīng)常要對(duì)查詢的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)計(jì)算，如求平均值、總和、平方差等，本節(jié)介紹Oracle數(shù)據(jù)庫(kù)系統(tǒng)提供的主要函數(shù)。 1字符函數(shù) 下面介紹常用的幾個(gè)字符函數(shù)的用法。 （1）ASCII()和CHR() ASCII()和CHR()是兩個(gè)基于ASCII的函數(shù)，ASCII(x)函數(shù)用于獲得字符串x最左邊字符的ASCII碼，CHR(x)函數(shù)用于獲得ASCII碼值為x的對(duì)應(yīng)的字符。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 【例6.13】查看A的ASCII碼。 SELECT ASCII(A) FROM dual; 結(jié)果是65，即函數(shù)返回字符“A”的ASCII碼值。 【例6.14】查看ASCII碼為65的字符。 SELECT CHR(65) FROM dual; 結(jié)果是字母A。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （2）LENGTH() LENGTH(x)函數(shù)用于獲得x字符串中字符的個(gè)數(shù)，即返回字符串的長(zhǎng)度。 【例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(x, y)函數(shù)用于將字符串y添加在字符串x之后，即將兩個(gè)字符串連接起來(lái)形成一個(gè)字符串。 【例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()將字符串全部轉(zhuǎn)為小寫，而UPPER()將字符串全部轉(zhuǎn)為大寫。 （5）LTRIM()、RTRIM()和TRIM() LTRIM(x , trim_string)函數(shù)用于從x的左邊截去一些字符，該函數(shù)還可以使用可選的參數(shù)trim_string來(lái)指定要截去的字符；如果沒(méi)有指定trim_string參數(shù)，默認(rèn)情況下會(huì)截去空格。同理，RTRIM函數(shù)用于從x的右邊截去一些字符，TRIM函數(shù)用于從x的左邊和右邊各截去一些字符。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （ 6 ）SUBSTR () SUBSTRING (， ， length) 返回從字符串expression左邊第starting_ position 個(gè)字符起length個(gè)字符的部分。 Select substr(xiao ming is a good man,16,8) From dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 2數(shù)字函數(shù) 下面介紹幾個(gè)常用的數(shù)字函數(shù)的具體用法。 （1）ABS() ABS(x)用于得到x的絕對(duì)值。 （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)用于計(jì)算x的y次冪。 （6）ROUND() ROUND(x, y)用于計(jì)算對(duì)x取整的結(jié)果，即對(duì)x進(jìn)行四舍五入計(jì)算，并返回；其中y為可選參數(shù)，說(shuō)明對(duì)第幾位小數(shù)取整。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 2數(shù)字函數(shù) （7）SQRT() SQRT(x)函數(shù)用于計(jì)算x的平方根，負(fù)數(shù)無(wú)意義。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 3聚合函數(shù) 在實(shí)際應(yīng)用中，常常需要對(duì)表中的數(shù)據(jù)進(jìn)行分類、統(tǒng)計(jì)、匯總等操作，如統(tǒng)計(jì)公司的人數(shù)、平均工資、最高工資等，這些都需要聚合函數(shù)來(lái)實(shí)現(xiàn)。聚合函數(shù)也被稱為聚集函數(shù)、分組函數(shù)、統(tǒng)計(jì)函數(shù)。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （1）AVG()函數(shù) AVG()函數(shù)用于返回一組數(shù)值中所有非空數(shù)值的平均值。 （2）MAX()和MIN()函數(shù) MIN()函數(shù)用于返回一個(gè)列范圍內(nèi)的最小非空值；MAX()函數(shù)用于返回最大值。 （3）COUNT()函數(shù) COUNT()函數(shù)用于返回一個(gè)列內(nèi)所有非空值的個(gè)數(shù)，這是一個(gè)整型值。,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （4）SUM()函數(shù) SUM()函數(shù)是最常用的聚合函數(shù)之一，其功能是返回一個(gè)列范圍內(nèi)所有非空值的總和。和AVG()函數(shù)一樣，它用于數(shù)值數(shù)據(jù)類型。 4日期時(shí)間函數(shù) Oracle數(shù)據(jù)庫(kù)系統(tǒng)提供了豐富的日期時(shí)間函數(shù)來(lái)處理日期類型數(shù)據(jù)，有些函數(shù)可用于解析日期值的日期與時(shí)間部分，有些函數(shù)可用于比較、操縱日期/時(shí)間值。,6.5 基本數(shù)據(jù)查詢,4日期時(shí)間函數(shù) （1）LAST_DAY() LAST_DAY()返回指定日期所在月最后一天的日期，通常被用來(lái)確定當(dāng)前月中還剩下多少天。 【例6.21】執(zhí)行下列語(yǔ)句求當(dāng)前月份的最后一天。 SELECT LAST_DAY(SYSDATE) FROM DUAL; （2）MONTHS_BETWEEN() 【例6.22】下列例子求當(dāng)前日期與2012年12月20日之間相隔的月數(shù)。 SELECT MONTHS_BETWEEN(SYSDATE,20-12月-2012) FROM DUAL;,6.5 基本數(shù)據(jù)查詢,4日期時(shí)間函數(shù) （3）NEXT_DAY() 【例6.23】下列例子求下一個(gè)星期一的日期。 SELECT NEXT_DAY(SYSDATE,星期一) FROM DUAL; (4)獲取系統(tǒng)時(shí)間 Select sysdate from dual; Select current_date from dual; (5)設(shè)置日期格式 Alter session set nls_date_format=yyyy-mm-dd hh:mi:ss;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 5轉(zhuǎn)換函數(shù) （1）CAST() CAST(x AS type)用來(lái)將x轉(zhuǎn)換為由type指定的兼容數(shù)據(jù)庫(kù)類型。 【例6.27】下面查詢展示了使用CAST()將字面值轉(zhuǎn)換為指定的類型： SELECT CAST(12345.67 AS VARCHAR2(10), CAST(9A4F AS RAW(2), CAST(12345.678 AS NUMBER(10,2) FROM dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) （2）CONVERT() 對(duì)于簡(jiǎn)單類型轉(zhuǎn)換，CONVERT()函數(shù)和CAST()函數(shù)的功能相同，只是語(yǔ)法不同。 【例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轉(zhuǎn)換為一個(gè)字符串，可選參數(shù)format是格式化控制符，用來(lái)指定轉(zhuǎn)換后x的格式。結(jié)構(gòu)format取決于x是數(shù)字還是日期。 Select to_char(12345.66) 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轉(zhuǎn)換為一個(gè)數(shù)字，可選參數(shù)format字符串用來(lái)指定轉(zhuǎn)換后的格式。 【例6.27】下面這個(gè)查詢使用TO_NUMBER函數(shù)將字符串970.13轉(zhuǎn)換為一個(gè)數(shù)字，然后再在其基礎(chǔ)上加上25.5： SELECT TO_NUMBER(970.13) + 25.5 FROM dual;,6.5 基本數(shù)據(jù)查詢,6.5.5 常用函數(shù) 6正則表達(dá)式函數(shù) 使用正則表達(dá)式函數(shù)可以在字符串中搜索字符模式。 下面介紹幾個(gè)常用正則表達(dá)式函數(shù)的具體用法。 （1）REGEXP_LIKE() REGEXP_LIKE(x, pattern , match_option)函數(shù)用于在x中查找pattern參數(shù)中定義的正則表達(dá)式。,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() REGEXP_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 高級(jí)數(shù)據(jù)查詢,6.6.1 多表連接查詢 1定義連接的兩種形式 定義連接有兩種形式，一種是在WHERE子句中定義，另一種是在FROM子句中定義。 （1）在WHERE子句中定義連接 在WHERE子句中定義連接的查詢語(yǔ)句基本格式為： SELECT 表名.列名1, 表名.列名2, FROM 表名1, 表名2 WHERE 表名1.列名表名1.列名;,6.6 高級(jí)數(shù)據(jù)查詢,6.6.1 多表連接查詢 （2）在FROM子句中定義連接 在FROM子句中定義連接的查詢語(yǔ)句基本格式為： SELECT 表名.列名1, 表名.列名2, FROM 表名1 表名2 ON （連接條件） WHERE 其中：連接類型可以是：INNER JOIN（內(nèi)連接）、OUTER JOIN（外連接）、CROSS JOIN（交叉連接）。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.1 多表連接查詢 2內(nèi)連接 內(nèi)連接使用INNER JOIN連接關(guān)鍵字，其連接格式為： FROM 表名1 INNER JOIN 表名2 ON （連接條件） （1）等值連接 在連接條件中使用“=”連接運(yùn)算符時(shí)，稱為等值連接。 （2）非等值連接 在連接條件中使用除“=”以外的其它運(yùn)算符（、=、）來(lái)比較被連接列的列值時(shí)，稱為非等值連接。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.1 多表連接查詢 3外連接 如果查詢結(jié)果集包含來(lái)自一個(gè)表的所有行和另一個(gè)表中的匹配行，那么這種連接稱為外連接。 外連接又分為以下三類： 1）左外連接（LEFT OUTER JOIN）：結(jié)果集包含左表中所有行和右表中匹配行。 2）右外連接（RIGHT OUTER JOIN）：結(jié)果集包含右表中所有行和左表中匹配行。 3）全外連接（FULL OUTER JOIN）：結(jié)果集包含左、右兩個(gè)表中的所有行。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.1 多表連接查詢 4交叉連接 交叉連接使用CROSS JOIN關(guān)鍵字來(lái)連接多個(gè)表，不能使用WHERE子句，返回的結(jié)果集包含所連接的表中所有行的全部組合，即結(jié)果集是所連接的各表數(shù)據(jù)行的笛卡爾積。 5自身連接 連接操作不僅可以在兩個(gè)不同的表之間進(jìn)行，也可以是一個(gè)表與其自己進(jìn)行連接，這種連接稱為自身連接。此時(shí)相同表要取不同的別名。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.2 嵌套查詢 在SQL語(yǔ)句中，一個(gè)SQL-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊。有時(shí)一個(gè)查詢塊無(wú)法完成查詢?nèi)蝿?wù)，需要將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING子句的條件中，這種將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的條件子句中的查詢稱為嵌套查詢。 1使用IN謂詞的子查詢 使用IN謂詞的子查詢是指父查詢和子查詢之間用IN關(guān)鍵字進(jìn)行連接，判斷原表中某個(gè)列值是否在子查詢的結(jié)果中。 2使用比較運(yùn)算符的子查詢 使用比較運(yùn)算符的子查詢是指父查詢和子查詢之間用比較運(yùn)算符進(jìn)行連接。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.2 嵌套查詢 3使用ANY或ALL謂詞的子查詢 當(dāng)子查詢返回結(jié)果為多個(gè)值時(shí)，父查詢還可以通過(guò)將比較運(yùn)算符與ANY或ALL結(jié)合來(lái)和子查詢建立連接。 4使用EXISTS謂詞的子查詢 EXISTS代表存在量詞彐。有時(shí)侯只需要考慮子查詢是否有返回結(jié)果，而并不考慮結(jié)果的具體數(shù)據(jù)，此時(shí)可以使用EXISTS謂詞來(lái)定義子查詢。,6.6 高級(jí)數(shù)據(jù)查詢,6.6.3 集合操作 集合操作就是將兩個(gè)或多個(gè)SQL查詢結(jié)果集合并，形成復(fù)合查詢，所以集合操作的查詢也稱為聯(lián)合查詢。,