第3章SQL語言基礎（3）3.1SQL簡介3.2SQL語言的基本元素3.3SQL語言中的運算符3.4常用的SQL*Plus命令3.5SQL語言中的函數(shù)3.6關系表的DDL語句3.7關系表的DML語句3.8SQL的數(shù)據(jù)查詢3.9視圖3.8SQL的數(shù)據(jù)查詢SELECT語句是數(shù)據(jù)庫應用中最常用的語句，也是SQL標準中最靈活和應用最廣泛的語句之一。SELECT語句是一個查詢表達式，它以關鍵字SELECT開頭，并且包括大量構(gòu)成該表達式的元素。SELECT語句可以從數(shù)據(jù)庫中按用戶的要求查詢行，而且允許從一個表或多個表中選擇滿足給定條件的一個或多個行或列，并將數(shù)據(jù)以用戶規(guī)定的格式進行整理后返回給客戶端。SELECT語句的完整格式比較復雜，其主要子句格式如下： SELECT[ALL|DISTINCT]{*|目標列表達式列表} FROM<表名或視圖名>[,<表名或視圖名>…] [WHERE<條件表達式>] [GROUPBY<列名>] [HAVING<條件表達式>] [ORDERBY<列名>[ASC|DESC]]SELECT子句：用來指定由查詢返回的列。FROM子句：用來指定從中查詢行的源表。WHERE子句：用來指定限定返回的行的搜索條件。GROUP子句：用來指定查詢結(jié)果的分組條件。HAVING子句：用來指定組成聚合的搜索條件。ORDER子句：用來指定結(jié)果集的排序方式。在上述SELECT語句中，F(xiàn)ROM、WHERE、GROUPBY和HAVING子句被稱為表表達式。當處理SELECT語句時，這一部分總是首先被評估。表表達式中的每個子句按照次序被依次評估，評估的結(jié)果是一個虛擬表，用于以后的評估。也就是說，前一個子句的評估結(jié)果將用于下一個子句，直到表表達式的每一個子句都被評估完為止。例如，在SELECT語句中首先要評估的子句是FROM子句，如果指定了WHERE子句，那么FROM子句的評估結(jié)果將用于WHERE子句。各子句的評估次序如下：①FROM②WHERE（可選）③GROUPBY（可選）④HAVING（可選）⑤SELECT⑥ORDERBY（可選）當創(chuàng)建比較復雜的SELECT語句時，尤其是在處理連接和子查詢時，對評估的次序有一個基本的了解顯得尤為重要。3.8.1基本查詢最基本的查詢就是從指定的表中找出符合條件的記錄。

SELECT* FROMstudent WHEREsex='男'ANDdno='d01';3.8.2SELECT子句在SELECT子句中，主要是通過指定列名確定表的哪些列值出現(xiàn)在結(jié)果集中，這相當于關系代數(shù)的投影操作。 ①SELECT子句后是以逗號分隔的各查詢輸出項。

SELECTsno,sname,dno FROMstudent WHEREsex='男';②用戶可以另外指定列標題（別名）。

SELECT輸出項1[AS]列標題,… SELECTsno學號,snameAS"name" FROMstudent WHEREsex='男';當別名沒有被雙引號括起來時，別名的顯示結(jié)果為大寫。如果別名中包含了特殊字符，或想讓別名以原樣顯示，就要使用雙引號把別名括起來。③SELECT的輸出項為表達式，可以是列名、常量、函數(shù)、運算符等。

SELECTename||'年薪為：'||sal*12員工的年薪,'元'單位

FROMemp;3.8.3WHERE子句WHERE后的條件表達式中參與運算的值可以是常量、函數(shù)及FROM子句所聲明的表中的列名。字符串及日期用單引號括起來，字符串是區(qū)分大小寫的。⑴日期型數(shù)據(jù)的比較，越早的日期越小。查1980年以前出生的學生。

SELECT* FROMstudent WHEREbirtday<'1-1月-1980';⑵集合運算符 SELECTdname,tel FROMdep WHEREdnameIN('計算機系','數(shù)學系');

或

WHEREdname=ANY('計算機系','數(shù)學系');⑶空值的處理 SELECTcno FROMsc WHEREsno=9801ANDgradeISNULL;(34)查詢有選課記錄,但沒有考試成績的學生的學號和課程號,正確的SQL語句是：

A）SELECT學號,課程號FROMscWHERE成績="" B）SELECT學號,課程號FROMscWHERE成績=NULL C）SELECT學號,課程號FROMscWHERE成績ISNULL D）SELECT學號,課程號FROMscWHERE成績例emp表 SELECTname姓名,s1+NVL(s2,0)收入

FROMemp;namesexs1s2a男500b女60015c女40015d男700⑷匹配符匹配符有兩個：

"-"代表一個且只能是一個字符。

"%"代表零個或多個字符。①當字符串比較中使用匹配符時，必須使用LIKE運算符。 SELECT* FROMstudent WHEREsnameLIKE'王%';②在進行字符串比較時，要注意字符串格式的影響。 aCHAR(10) bVARCHAR2(10) a=b='sunday'

SELECT* FROMtemp WHEREaLIKE'%day';× WHEREbLIKE'%day';√ WHEREaLIKE'%day%';√③轉(zhuǎn)義符 SELECT* FROM課程表

WHEREcnameLIKE'a\_%'escape'\';課程表cnocname1a_C++2b_C++3a_數(shù)據(jù)庫4b_數(shù)據(jù)庫5access3.8.4ORDERBY子句ORDERBY子句實現(xiàn)查詢結(jié)果的排序輸出。例：查詢d01部門的職工姓名及年齡，并按年齡由小到大排序輸出。

SELECTename姓名,age年齡

FROMemp WHEREdeptno='d01' ORDERBYage;排序項可以是出現(xiàn)在查詢列表中的列名、表達式、列標題，也可以指定輸出項的序號，或用別名，還可以指定排序是升序（ASC）還是降序（DESC）。在排序子句中可以指定多個排序項。

SELECTdeptno,ename,age,sal FROMemp ORDERBYdeptno,ageDESC,sal;注意：①排序子句必須作為最后一個子句出現(xiàn)。②不使用ORDERBY子句，查詢結(jié)果的次序是不確定的。③排序子句不能出現(xiàn)在子查詢中。④排序的列名可以不出現(xiàn)在輸出項中，但是當查詢子句包含有DISTINCT選項時，就不能指定沒有出現(xiàn)在查詢列表中的列作為排序列。3.8.5多表查詢與連接操作為了從多個表中得到所需的數(shù)據(jù)，Oracle提供了連接來完成多表查詢。Oracle提供了4種類型的連接：相等連接、自連接、不等連接和外連接。⑴相等連接SELECTsno,sname,sexFROMstudent,depWHEREdname='計算機系'ANDstudent.dno=dep.dno"連接"實際上將不同的表中的相關記錄連在一起，形成一個跨表的大記錄。相等連接也叫簡單連接或內(nèi)連接，這種連接查詢通常涉及到主碼和外碼。連接指南：①要連接的表都要放在FROM子句中，表名用逗號分開。②連接的條件放在WHERE子句中。③要進行n個表的連接需要至少n-1個連接條件。④如果多個表中有相同的列名時，在這些列的前面要冠以表名來區(qū)別它們，表名和列名之間用圓點隔開。⑤在列前冠以表名可以改善系統(tǒng)的效率，因為表名告訴Oracle服務器到哪一個表中找哪些列。⑥如果表名很長，可以使用表的別名。SELECTs.sno,s.sname,s.sexFROMstudents,depdWHEREd.dname='計算機系'ANDs.dno=d.dno;在以下條件下，得到的是笛卡爾積：①查詢語句中漏掉了連接條件。②查詢語句中兩個表中的所有行都滿足連接條件。③查詢語句中的連接條件無效。⑵自連接職員表

emp(empno,ename,job,mgr)CREATETABLEemp( empnoCHAR(3)PRIMARYKEY, enameVARCHAR2(10), jobVARCHAR2(10), mgrCHAR(3)REFERENCESemp(empno));職員的主管查分析員的經(jīng)理SELECT

w.empno,w.ename,w.job,w.mgr,FROM

empw,empmWHERE

w.mgr=m.empnoANDw.job='analyst';⑶不等連接相等連接中連接運算符為等號，我們也可以使用其它運算符，其它運算符產(chǎn)生的連接叫不等連接。選課表

sc(sno,cno,grade)

成績等級劃分表ggradeLOHI1044245593607047185586100查成績?yōu)椋常导壷g的學生的成績及等級。SELECTsc.sno,o,sc.grade,g.gradeFROMsc,gWHERE

(sc.gradeBETWEENg.LOANDg.HI)ANDg.grade>2;⑷外連接查詢女生所在系的信息，同時也返回那些沒有女生的系的信息。

SELECTsname女生,sex性別,dname系名

FROMstudents,depd WHEREs.sex='女'ANDs.dno(+)=d.dno;對于一般連接，那些沒有女生的系不會輸出。外連接返回兩種記錄： ①滿足連接條件的那些記錄。 ②來自其中一個表的記錄，這些記錄不滿足連接條件而不能與另一個表的記錄連接。外連接的連接運算符為(+)。該連接運算符既可以放在等號的左邊也可以放在等號的右邊，但一定要放在缺少相應信息的那一邊。⑸SQL：1999語法的連接Oracle9i對多表連接的語法進行了擴充，它開始支持美國國家標準化組織（ANSI）的SQL標準"SQL：1999"的語法。

SELECTsno,sname,sex FROMstudents,depd WHEREd.dname='計算機系'ANDs.dno=d.dno; SELECTsno,sname,sex FROMstudents JOINdepd USING(dno)WHEREd.dname='計算機系';

SELECTsno,sname,sex FROM

students JOINdepd ONs.dno=d.dnoWHEREd.dname='計算機系';此處不能寫成d.dno3.8.6查詢語句中的集合操作

⑴UNION（并運算）將屬于一個查詢結(jié)果的行和屬于另一個查詢結(jié)果的行結(jié)合起來構(gòu)成并運算的結(jié)果。查詢選修了c01或c02課程的學生。

(SELECTsnoFROMscWHEREcno='c01') UNION (SELECTsnoFROMscWHEREcno='c02');⑵INTERSECT（交運算）將既屬于一個查詢結(jié)果又屬于另一個查詢結(jié)果的行組合起來構(gòu)成交運算的結(jié)果。查詢同時選修了c01和c02兩門課程的學生。

(SELECTsnoFROMscWHEREcno='c01') INTERSECT (SELECTsnoFROMscWHEREcno='c02');⑶MINUS（差運算）查詢選修了c01，但沒有選修c02的學生。

(SELECTsnoFROMscWHEREcno='c01') MINUS (SELECTsnoFROMscWHEREcno='c02');3.8.7查詢中的重復值處理①DISTINCT去掉重復值。②ALL包括重復值（默認選項）查詢開設的課程。

SELECTDISTINCTcname FROMcourse;（主碼是cno）(31)查詢學生選修課程小于60分的學號，正確的SQL語句是：A）SELECTDISTINCT學號FROMSCWHERE"成績"<60B）SELECTDISTINCT學號FROMSCWHERE成績<"60"C）SELECTDISTINCT學號FROMSCWHERE成績<60D）SELECTDISTINCT"學號"FROMSCWHERE"成績"<603.8.8組函數(shù)（統(tǒng)計函數(shù)）與聚合操作常用的組函數(shù)有5個：⑴一般統(tǒng)計①查d01部門職工的工資總額、最高工資、最低工資和平均工資。

SELECTSUM(sal),MAX(sal),MIN(sal),AVG(sal) FROMemp WHEREdeptno='d01';②統(tǒng)計職工總數(shù)。

SELECTCOUNT(*) FROMemp;如統(tǒng)計職工總數(shù)，不含沒有工作部門的職工。

SELECTCOUNT(deptno) FROMemp;③查詢本學期被選修的課程數(shù)。（選課表中，成績?yōu)榭毡硎緸楸緦W期選的課）

SELECTCOUNT(DISTINCTcno) FROMsc WHEREgradeISNULL;(34)查詢有選課記錄,但沒有考試成績的學生的學號和課程號,正確的SQL語句是：A）SELECT學號,課程號FROMscWHERE成績=""B）SELECT學號,課程號FROMscWHERE成績=NULLC）SELECT學號,課程號FROMscWHERE成績ISNULLD）SELECT學號,課程號FROMscWHERE成績(20)以下關于空值(NULL值)敘述正確的是A）空值等于空字符串B）空值等同于數(shù)值0C）空值表示字段或變量還沒有確定的值D）VisualFoxPro不支持空值(15)設有SC(學號，課程號，成績)表,下面SQL的SELECT語句檢索成績高于或等于平均成績的學生的學號。SELECT學號FROMsc WHERE成績>=(SELECT____FROMsc)⑵分組統(tǒng)計職員表

emp(eno,ename,job,sal,deptno)統(tǒng)計各工種的平均工資。

SELECTjob,AVG(sal) FROMemp GROUPBYjob;注意：如果在一個查詢中使用了分組函數(shù)，任何不在分組函數(shù)中的列或表達式必須出現(xiàn)在GROUPBY子句中。

SELECTjob,AVG(sal) FROMemp GROUPBYjob;

GROUPBYdeptno;×

⑶HAVING子句的使用HAVING子句用于篩選滿足條件的組。查詢平均工資高于1500的工種。

SELECTjob,AVG(sal) FROMemp WHEREAVG(sal)>1500 GROUPBYjob;×ORA-00934：此處不允許使用分組函數(shù) SELECTjob,AVG(sal) FROMemp GROUPBYjob; HAVINGAVG(sal)>1500 ORDERBYAVG(sal);(24)SQL的SELECT語句中,"HAVING<條件表達式>"用來篩選滿足條件的

A）列B）行C）關系D）分組(14)使用SQL語言的SELECT語句進行分組查詢時,如果希望去掉不滿足條件的分組,應當在GROUPBY中使用_______子句。3.8.9子查詢在SQL中，一個SELECT－FROM－WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE或HAVING子句的條件中的查詢稱為嵌套查詢。

SELECT* FROMstudent WHEREsex= (SELECTsex FROMstudent WHEREsname='劉勇');上層查詢塊稱為外層查詢或父查詢，下層查詢稱為內(nèi)層查詢或子查詢。系統(tǒng)從內(nèi)層子查詢做起。⑴單值子查詢（返回單一的值）單值子查詢的比較關系符包括：

>、>=、<、<=、=、!=查工資在平均線以下的員工。

SELECTempno,ename,sal FROMemp WHEREsal<(SELECTAVG(sal)FROMemp);

WHEREsal<AVG(sal);× ORA-00934:此處不允許使用分組函數(shù) 注：where條件中不能包含分組函數(shù)。 ①主查詢和子查詢的數(shù)據(jù)可以來自不同的表。 ②單行子查詢除了可以放在WHERE子句中，還可以放在HAVING子句中。例：查平均工資高于最低平均工資（按工種分類）的所有工種。（即平均工資不是最低的工種）SELECTjobFROMempGROUPBYjobHAVINGAVG(sal)>(SELECTMIN(AVG(sal))FROMempGROUPBYjob);？SELECT*FROMstudentWHEREsex= (SELECTsex FROMstudent WHEREdno='d01');⑵多行子查詢子查詢返回多行。比較使用集合運算符。例：查工資比所有工種平均工資都低的職工。

SELECTempno,ename,sal,job FROMemp WHEREsal<ALL (SELECTAVG(sal) FROMemp GROUPBYjob);例：多列子查詢。查工資為所任工種最高的職工。

SELECTempno,ename,sal,job FROMemp WHERE(sal,job)IN (SELECTMAX(sal),job FROMemp GROUPBYjob);⑶相關子查詢SQL還提供了一個EXISTS運算符，EXISTS代表存在量詞。帶EXISTS的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值"true"或邏輯假值"false"，不空為真，空為假。例：查所有選修了01號課程的學生的姓名。 SELECTsname FROMstudent WHEREEXISTS (SELECT* FROMsc WHEREsno=student.snoANDcno='01');由EXISTS引出的子查詢，其目標列表達式通常都用*。此類查詢與前面講的子查詢有一個明顯的區(qū)別，即子查詢的查詢條件依賴于外層父查詢的某個屬性值，這類查詢稱為相關子查詢。從概念上講，相關子查詢的一般處理過程是：首先取外層查詢中（student）表的第1個元組，根據(jù)它與內(nèi)層查詢相關的屬性值（sno）處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再?。╯tudent）表的下一個元組；重復這一過程，直到外層（student）表全部檢查完為止。(35)查詢選修C2課程號的學生姓名,下列SQL語句中錯誤的是A）SELECT姓名FROMSWHEREEXISTS (SELECT*FROMSCWHERE學號=S.學號AND課程號='C2')B）SELECT姓名FROMSWHERE學號IN (SELECT學號FROMSJOINSCONS.學號=SC.學號WHERE課程號='C2')C）SELECT姓名FROMSJOINSCONS.學號=SC.學號WHERE課程號='C2'D）SELECT姓名