數(shù)據(jù)庫(kù)SQL ServeSQLite教程 課件 第5章 關(guān)系數(shù)據(jù)庫(kù)（下）

數(shù)據(jù)庫(kù)SQLServer/SQlite教程授課教師：第5章關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL(下)表格是數(shù)據(jù)庫(kù)里最重要的對(duì)象，包括結(jié)構(gòu)和數(shù)據(jù)。創(chuàng)建表只是創(chuàng)建表的結(jié)構(gòu)，其中并不包含數(shù)據(jù)，可以通過(guò)Insert語(yǔ)句向表中插入數(shù)據(jù)，使用Update語(yǔ)句更新數(shù)據(jù)，還可以用Delete語(yǔ)句對(duì)不需要的數(shù)據(jù)記錄進(jìn)行刪除，甚至清空整個(gè)表。第5章

關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL(下)5.1數(shù)據(jù)操作語(yǔ)言(DML)目錄5.2數(shù)據(jù)查詢語(yǔ)言(DQL)5.3數(shù)據(jù)控制語(yǔ)言(DCL)5.1數(shù)據(jù)操作語(yǔ)言(DML)SQL中數(shù)據(jù)操作語(yǔ)句包括insert、update和delete，分別實(shí)現(xiàn)數(shù)據(jù)的插入、更新和刪除功能。與后面討論的select語(yǔ)句相比，數(shù)據(jù)操作語(yǔ)句相對(duì)簡(jiǎn)單，本節(jié)主要通過(guò)例子來(lái)介紹這幾個(gè)語(yǔ)句的用法。5.1

數(shù)據(jù)操作語(yǔ)言(DML)SQL使用insert語(yǔ)句為數(shù)據(jù)表添加記錄。insert語(yǔ)句通常有兩種形式：一種是一次插入一條記錄，另一種是一次插入多條記錄，即使用子查詢批量插入。insert的基本語(yǔ)法格式如下：insert[into]tablename[(column{,column})]values(columnvalue[{,columnvalue}])使用查詢結(jié)果插入記錄的格式如下：insert[into]tablename...values(select...from...)此方法在select部分再單獨(dú)介紹。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)參數(shù)說(shuō)明：·insertinto是插入語(yǔ)句的命令關(guān)鍵詞，其中into可以省略。tablename指定要向其中插入數(shù)據(jù)的表的名稱。columnlist是列列表，用來(lái)指定要向其中插入數(shù)據(jù)的列，列和列之間用逗號(hào)分開(kāi)?！alues用于引出要插入的數(shù)據(jù)，columnvalue是數(shù)據(jù)表達(dá)式列表，數(shù)據(jù)項(xiàng)之間需要用逗號(hào)分開(kāi)。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)參數(shù)說(shuō)明：·insertinto是插入語(yǔ)句的命令關(guān)鍵詞，其中into可以省略。tablename指定要向其中插入數(shù)據(jù)的表的名稱。columnlist是列列表，用來(lái)指定要向其中插入數(shù)據(jù)的列，列和列之間用逗號(hào)分開(kāi)?！alues用于引出要插入的數(shù)據(jù)，columnvalue是數(shù)據(jù)表達(dá)式列表，數(shù)據(jù)項(xiàng)之間需要用逗號(hào)分開(kāi)。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)向表中插入數(shù)據(jù)應(yīng)注意以下幾點(diǎn)：(1)數(shù)據(jù)表達(dá)式列表columnvalue中的數(shù)據(jù)值應(yīng)該與列列表columnlist中的列一一對(duì)應(yīng)，數(shù)據(jù)類型也應(yīng)該兼容。(2)必須為表中所有定義notnull的列提供值，對(duì)于定義為null的列既可以提供值也可以不提供值。(3)如果表中存在標(biāo)識(shí)列，則不能向標(biāo)識(shí)列中插入數(shù)據(jù)。如果表中有計(jì)算列，則不能向計(jì)算列中插入值。(4)因?yàn)橹麈I所在列不允許有空值也不允許有重復(fù)值，所以插入數(shù)據(jù)時(shí)必須保證主鍵所在列中有值而且不能與該列中已經(jīng)存在的值重復(fù)。(5)如果表中存在外鍵約束，則向表中插入數(shù)據(jù)時(shí)要注意避免違反參照完整性約束。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)在接下來(lái)的例子中將向表books中插入數(shù)據(jù)。先分析一下books表的特點(diǎn)，表中包括6個(gè)列，即bookid、title、isbn、author、unitprice和categorycode。其中：bookid是主鍵、int類型；title、isbn和author被定義成notnull、字符型；categorycode是一個(gè)外鍵，父表是categories，存放圖書的類別，categories表中已經(jīng)存在數(shù)據(jù)，如圖5-1所示。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)圖5-1categories表例5-1

向表books中插入一條圖書記錄。代碼如下：insertintobooks(bookid,title,isbn,author,unitprice,categorycode)values(2,'HTML5+CSS3網(wǎng)頁(yè)設(shè)計(jì)','978-7-5635-5232-1','周濤',45.00,'003')說(shuō)明：例5-1中向所有列中都插入了數(shù)據(jù)，而且數(shù)據(jù)項(xiàng)的順序與表中各個(gè)列的順序一致，這種情況下可以省略列列表。如果字段是字符型、日期型，則插入的值需要加單引號(hào)作為定界符，數(shù)值型數(shù)據(jù)不加單引號(hào)。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)例5-2

向表books中插入一條在表categories中沒(méi)有對(duì)應(yīng)類別號(hào)的圖書記錄。代碼如下：insertbooksvalues(14,'SQLServer項(xiàng)目教程','978-7-1254-2487-1','王英',35.10,'007')執(zhí)行上述語(yǔ)句時(shí)系統(tǒng)報(bào)錯(cuò)，從消息窗口中可以看到提示信息為insert語(yǔ)句與foreignkey約束“FK_books_categories”沖突。解決辦法是先在表categories中添加“007”類別。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)例5-3

向orderitems表中插入2條記錄，分別是1號(hào)訂單中的5號(hào)和6號(hào)圖書。該表包括orderid、bookid、quantity、price、total列，其中主鍵是(orderid，bookid)；total是計(jì)算列(等于quantity乘以price)。代碼如下：insertintoorderitems(orderid,bookid,quantity,price)values(1,5,15,38.5),(1,6,10,26.1)說(shuō)明：計(jì)算列不需要輸入，自動(dòng)計(jì)算。5.1.1

數(shù)據(jù)插入1.命令方式插入數(shù)據(jù)在SSMS中，除了用insert語(yǔ)句插入記錄，還可以使用菜單方式插入記錄。例5-4

向student表中插入記錄。其中有一個(gè)主鍵約束PK_s#、一個(gè)檢查約束CK_student_email(默認(rèn)格式：emaillike'%_@%_._%')。操作步驟如下：(1)在“對(duì)象資源管理器”中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)和“表”節(jié)點(diǎn)，用鼠標(biāo)右鍵單擊student表，在彈出的快捷菜單中選擇“編輯前200行”，進(jìn)入編輯界面，如圖5-2所示。(2)依次輸入各行字段的值，單擊“保存”按鈕。如果需要?jiǎng)h除某行，單擊行前的按鈕選中整條記錄，或按住“Ctrl”鍵選中多條記錄，或拖動(dòng)鼠標(biāo)左鍵選定多條記錄，并在選中記錄上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令，則刪除相應(yīng)的數(shù)據(jù)記錄。5.1.1

數(shù)據(jù)插入2.菜單方式插入數(shù)據(jù)5.1.1

數(shù)據(jù)插入2.菜單方式插入數(shù)據(jù)圖5-2“編輯前200行”工作界面SQL使用update語(yǔ)句更新或修改滿足規(guī)定條件的記錄。update語(yǔ)句的格式如下：updatetablenamesetcolumn=newvalue[,nextcolumn=newvalue2...][wherecolumnnameoperatorvalue[and|orcolumnnameoperatorvalue]]說(shuō)明：將符合where條件的記錄的一個(gè)或多個(gè)列修改為新值。若省略where，則全表更新。operator即運(yùn)算符。5.1.2

數(shù)據(jù)更新例5-5在數(shù)據(jù)庫(kù)studscore_ds1中，將表student中s#是2018010103的學(xué)生的age加1。代碼如下：updatestudentsetage=age+1wheres#='2018010103'說(shuō)明：如果省略where條件，則所有記錄加1歲。例5-6將學(xué)生John的性別改為“男”，年齡改為“23”。代碼如下：updatestudentsetsex='男',age=23wheresname='John'5.1.2

數(shù)據(jù)更新SQL使用delete語(yǔ)句刪除數(shù)據(jù)庫(kù)表格中的行或記錄。12delete語(yǔ)句truncatetable命令5.1.3

數(shù)據(jù)刪除語(yǔ)句格式如下：deletefrom<表名>where<條件>說(shuō)明：將符合<條件>的記錄從表中刪除。例5-7：在數(shù)據(jù)庫(kù)studscore_ds1中，將表student中學(xué)號(hào)s#為2018010101的學(xué)生刪除。代碼如下：deletefromstudentwheres#='2018010101'說(shuō)明：如果省略where條件，則刪除表中所有的記錄。1.delete語(yǔ)句5.1.3

數(shù)據(jù)刪除如果要?jiǎng)h除表中的所有數(shù)據(jù)記錄，則使用truncatetable命令比用delete命令快得多，這是因?yàn)閐elete命令除了刪除數(shù)據(jù)外，還會(huì)對(duì)刪除數(shù)據(jù)在事務(wù)處理日志中作出記錄，以便刪除失敗時(shí)可以使用事務(wù)處理日志來(lái)恢復(fù)數(shù)據(jù)。而truncatetable命令的功能相當(dāng)于使用不帶where子句的delete命令。語(yǔ)句格式如下：truncatetabletable_name2.truncatetable命令5.1.3

數(shù)據(jù)刪除例5-8刪除學(xué)生成績(jī)表studscoreinfo中的所有記錄。代碼如下：truncatetablestudscoreinfo需要指出的是，truncatetable命令不能用于有依賴關(guān)系的表，也不能激發(fā)觸發(fā)器。2.truncatetable命令5.1.3

數(shù)據(jù)刪除merge關(guān)鍵字是在SQLServer2008引入的DML關(guān)鍵字，它能將insert、update、delete簡(jiǎn)單地并為一句。MSDN對(duì)merge的解釋是：根據(jù)與源表連接的結(jié)果，對(duì)目標(biāo)表執(zhí)行插入、更新或刪除操作。例如，根據(jù)在另一個(gè)表中找到的差異在一個(gè)表中插入、更新或刪除行，可以對(duì)兩個(gè)表進(jìn)行同步。通過(guò)這個(gè)描述可以看出，merge是對(duì)兩個(gè)表之間的數(shù)據(jù)進(jìn)行操作的。1.功能5.1.4

merge語(yǔ)句merge的功能是：檢查原數(shù)據(jù)表記錄和目標(biāo)表記錄，如果記錄在原數(shù)據(jù)表和目標(biāo)表中均存在，則目標(biāo)表中的記錄將被原數(shù)據(jù)表中的記錄更新(執(zhí)行update操作)；如果目標(biāo)表中不存在的某些記錄在原數(shù)據(jù)表中存在，則原數(shù)據(jù)表的這些記錄將被插入到目標(biāo)表中(執(zhí)行insert操作)。1.功能5.1.4

merge語(yǔ)句merge的語(yǔ)法格式如下：mergeintotable_name[table_alias]using{table|view|sub_query}[table_alias]on(joincondition)whenmatched[and<clause_search_condition>]thenupdatesetcol1=coll_val1,col2=col2_val2whennotmatched[and<clause_search_condition>]theninsert(column_list)values(column_values)2.語(yǔ)法格式及示例5.1.4

merge語(yǔ)句參數(shù)說(shuō)明：第一行merge子句：命名目標(biāo)表并給出別名。第二行using子句：提供merge操作的數(shù)據(jù)源，并給出別名。第三行on子句：指定合并的條件。第四行whenmatchedthen子句：判斷條件符合則對(duì)目標(biāo)表更新或刪除。第八行whennotmatchedthen子句：判斷條件不符合則執(zhí)行插入的操作。2.語(yǔ)法格式及示例5.1.4

merge語(yǔ)句例5-9

將表student中年齡為21和22歲的學(xué)生信息添加到表studentinfo中。首先，復(fù)制student表的結(jié)構(gòu)到studentinfo，代碼如下：selecttop0s#,sname,age,sex,classidintostudentinfofromstudent其次，添加記錄，代碼如下：insertintostudentinfoselect*fromstudentwhereage=21orage=222.語(yǔ)法格式及示例5.1.4

merge語(yǔ)句例5-10

使用merge關(guān)鍵字合并學(xué)生信息表studentinfo。代碼如下：mergestudentinfoiusingstudentdoni.s#=d.s#whenmatchedthenupdateseti.sname=d.sname,i.age=d.age+1whennotmatchedtheninsertvalues(d.s#,d.sname,d.age,d.sex,d.classid);說(shuō)明：merge語(yǔ)句必須以分號(hào)(;)結(jié)尾。2.語(yǔ)法格式及示例5.1.4

merge語(yǔ)句5.2數(shù)據(jù)查詢語(yǔ)言(DQL)在數(shù)據(jù)庫(kù)操作中，數(shù)據(jù)查詢是最主要的操作，SQL中的數(shù)據(jù)查詢功能非常全面。SQL使用select語(yǔ)句來(lái)實(shí)現(xiàn)數(shù)據(jù)的查詢，并按用戶要求檢索數(shù)據(jù)，將查詢結(jié)果以表格的形式返回。5.2

數(shù)據(jù)查詢語(yǔ)言(DQL)select查詢的基本語(yǔ)法格式如下：selectselect_list[intonew_table_name]fromtable_list[wheresearch_conditions][groupbygroup_by_list][havingsearch_conditios][orderbyorder_list[asc|desc]]5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)1)查詢的語(yǔ)法格式5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)1)查詢的語(yǔ)法格式上述語(yǔ)法中共有7個(gè)子句，其中select和from子句是必不可少的。各子句的功能如下：(1)select_list子句用于指定希望查看的列，中間用逗號(hào)分隔。(2)intonew_table_name子句用于將檢索出來(lái)的結(jié)果集創(chuàng)建一個(gè)新的數(shù)據(jù)表。(3)fromtable_list子句用于指定檢索數(shù)據(jù)的數(shù)據(jù)表的列表。(4)where<條件>子句用于對(duì)數(shù)據(jù)行進(jìn)行篩選，指定查詢的條件，是一個(gè)條件表達(dá)式，只有滿足條件的數(shù)據(jù)行才作為查詢的對(duì)象。5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)1)查詢的語(yǔ)法格式(4)where<條件>子句用于對(duì)數(shù)據(jù)行進(jìn)行篩選，指定查詢的條件，是一個(gè)條件表達(dá)式，只有滿足條件的數(shù)據(jù)行才作為查詢的對(duì)象。(5)groupby<分組列名表>子句用于指定要分組的列。(6)having<條件>子句用于指定分組的條件。從結(jié)果集對(duì)記錄進(jìn)行篩選，只有滿足條件表達(dá)式的組才作為查詢的對(duì)象。(7)orderby<排序列名表>子句用于對(duì)查詢的結(jié)果排序。asc表示升序排序，desc表示降序排序。asc是默認(rèn)選項(xiàng)。5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)2)select查詢的執(zhí)行過(guò)程雖然select查詢的各個(gè)子句書寫的順序是select→from→where→groupby→having→orderby，但是在計(jì)算機(jī)中各個(gè)子句實(shí)際的執(zhí)行順序是from→where→groupby→having→select→orderby。也就是說(shuō)首先確定從哪個(gè)或哪些表(或視圖)中查詢數(shù)據(jù)，如有必要就篩選，如有必要就分組，還有必要再對(duì)分組進(jìn)行篩選。接下來(lái)確定查詢結(jié)果，如有排序要求就對(duì)查詢結(jié)果進(jìn)行排序。5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)2)select查詢的執(zhí)行過(guò)程其過(guò)程如下：(1)讀取from子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。例如，從兩張表中取數(shù)，對(duì)比記錄數(shù)、兩張表記錄數(shù)的乘積數(shù)，理解笛卡爾積。(2)選取滿足where子句中給出的條件表達(dá)式的元組。(3)按照groupby子句中指定列的值進(jìn)行分組，同時(shí)提取滿足having子句中組條件表達(dá)式的那些組。(4)按照select子句中給出的列名或列表達(dá)式求值輸出。(5)orderby子句對(duì)輸出的目標(biāo)表進(jìn)行排序，按asc(升序)排列，或按desc(降序)排列。5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)3)使用select查詢應(yīng)注意的問(wèn)題(1)在數(shù)據(jù)庫(kù)系統(tǒng)中，可能存在對(duì)象名稱重復(fù)的現(xiàn)象。例如，兩個(gè)用戶同時(shí)定義了studinfo的表，在引用用戶ID為stud的用戶定義的studinfo表時(shí)，需要使用用戶ID限定數(shù)據(jù)表的名稱。語(yǔ)法代碼如下：select*fromstud.studinfo(2)在使用select語(yǔ)句進(jìn)行查詢時(shí)，需要引用的對(duì)象所在的數(shù)據(jù)庫(kù)不一定總是當(dāng)前的數(shù)據(jù)庫(kù)，在引用數(shù)據(jù)表時(shí)需要使用數(shù)據(jù)庫(kù)來(lái)限定數(shù)據(jù)表的名稱。語(yǔ)法代碼如下：select*fromstudscore_ds1.dbo.studinfoselect*fromstudscore_ds1..studinfo5.2.1

SQL簡(jiǎn)單查詢1.查詢結(jié)構(gòu)3)使用select查詢應(yīng)注意的問(wèn)題(3)在from子句中指定的數(shù)據(jù)表和視圖可能包含有相同的字段名稱，外鍵字段名稱很可能與相應(yīng)的主鍵字段名稱相同。因此，為避免字段引用時(shí)的歧義，必須使用數(shù)據(jù)表或視圖名稱來(lái)限定字段名稱。語(yǔ)法代碼如下：selectstudinfo.studno,studname,classinfo.classid,classnamefromstudinfo,classinfowherestudinfo.classid=classinfo.classid5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句select子句指定需要通過(guò)查詢返回的表的列。語(yǔ)法格式如下：select[all|distinct][topn[percent][withties]]<select_list>其中：<select_list>={*|{table_name|view_name|table_alias}.*|{column_name|expression|identitycol|rowguidcol}[[as]column_alias]|column_alias=expression}[,...n]5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句參數(shù)說(shuō)明：(1)all：指明查詢結(jié)果中可以顯示值相同的列，all是系統(tǒng)默認(rèn)的選項(xiàng)。(2)select_list：指所要查詢的表的列的集合，多個(gè)列之間用逗號(hào)分開(kāi)。(3)*：通配符，返回所有對(duì)象的所有列。(4)table_name|view_name|table_alias.*：限制通配符*的作用范圍，凡是帶*的項(xiàng)均返回其中所有的列。(5)column_name：指定返回的列名。(6)expression：表達(dá)式可能為列名常量、函數(shù)或它們的組合。此時(shí)應(yīng)給表達(dá)式指定一個(gè)別名，通常有3種方式：原列名as別名、原列名別名、別名=原列名。在一個(gè)查詢語(yǔ)句中，也可以混合使用以上3種方式來(lái)定義別名。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句(7)

identitycol：返回identity列。如果from子句中有多個(gè)表含有identity列，則在identitycol選項(xiàng)前必須加上表名，如table.identitycol。(8)

rowguidcol：返回表的rowguidcol列，同identitycol選項(xiàng)。當(dāng)要指定多個(gè)rowguidcol列時(shí)，選項(xiàng)前要加上表名。(9)

column_alias：在返回的查詢結(jié)果中用此別名替代列的原名。column_alias可用于orderby子句，但不能用于where、groupby、having子句。下面是兩個(gè)*(通配符)的使用實(shí)例。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句例5-11

查詢學(xué)生信息表student的所有記錄。代碼如下：select*fromstudent等同于以下代碼：selects#,sname,sex,age,classidfromstudent5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句例5-12

查詢學(xué)生信息表studscoreinfo，并使用計(jì)算列。代碼如下：selectstudno,courseid,studscore*0.8fromstudscoreinfo注意：查看查詢結(jié)果，此處studscore*0.8無(wú)別名，沒(méi)有字段名。select語(yǔ)句中使用all/distinct選項(xiàng)來(lái)顯示表中符合條件的所有行或刪除其中重復(fù)的數(shù)據(jù)行。all是默認(rèn)選項(xiàng)，可以省略，因此不需要特別指定。distinct關(guān)鍵字用于去除重復(fù)的記錄，如果distinct后面是多個(gè)字段名，則是多個(gè)字段的組合不重復(fù)的記錄。null值被認(rèn)為是相同的值。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句例5-13

查詢學(xué)生信息表studinfo中不重復(fù)的性別記錄。查詢結(jié)果如圖5-3所示。代碼如下：selectdistinctstudsexfromstudinfo圖5-3distinct關(guān)鍵字5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句在數(shù)據(jù)查詢時(shí)，經(jīng)常需要查詢最好的、最差的、最前的、最后的幾條記錄，這時(shí)需要用到top關(guān)鍵字。topn[percent]用于指定返回查詢結(jié)果的前n行數(shù)據(jù)，如果有percent關(guān)鍵字指定，則返回查詢結(jié)果的前百分之n行的數(shù)據(jù)。withties選項(xiàng)只能在使用了orderby子句后才能使用。當(dāng)指定此項(xiàng)時(shí)除了返回由topn[percent]指定的數(shù)據(jù)行外，還要返回與topn[percent]返回的最后一行記錄中由orderby子句指定的列的列值相同的數(shù)據(jù)行。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句例5-14

查詢學(xué)生信息表studinfo中前10條記錄。代碼如下：selecttop10*fromstudinfo例5-15

查詢學(xué)生成績(jī)信息表studscoreinfo中courseid為a0101，成績(jī)前20%的記錄。代碼如下：selecttop20percent*fromstudscoreinfowherecourseid='a0101'例5-16

查詢學(xué)生成績(jī)信息表studscoreinfo中courseid為a0101，成績(jī)排在前3名的記錄。代碼如下：selecttop3*fromstudscoreinfowherecourseid='a0101'orderbystudscoredesc5.2.1

SQL簡(jiǎn)單查詢2.查詢操作1)select子句例5-17

查詢學(xué)生成績(jī)信息表studscoreinfo中courseid為a0101，成績(jī)排在前3名的記錄，包括并列成績(jī)。代碼如下：selecttop3withties*fromstudscoreinfowherecourseid='a0101'orderbystudscoredescSQL使用as關(guān)鍵字進(jìn)行別名運(yùn)算(as可以省略，但空格不能省略)，可靈活指定查詢結(jié)果各字段顯示的名稱。此外，如果別名中間包括空格，則必須加雙引號(hào)。例5-18

查詢表student的s#、sname、classid等信息，且字段名以中文名字顯示。代碼如下：selects#as學(xué)號(hào),sname姓名,班級(jí)編號(hào)=classidfromstudentintonew_table_name子句用于將查詢的結(jié)果集創(chuàng)建一個(gè)新的表。新表的列由select子句中指定的列構(gòu)成，且查詢結(jié)果各列必須具有唯一的名稱。新表中的數(shù)據(jù)是由where子句指定的，但如果select子句中指定了計(jì)算列，在新表中對(duì)應(yīng)的列則不是計(jì)算列，而是一個(gè)實(shí)際存儲(chǔ)在新表中的列，其中的數(shù)據(jù)由執(zhí)行select…into語(yǔ)句時(shí)計(jì)算得出。例5-19

查詢表studscoreinfo中courseid為a0101的記錄，并插入到新表stscore_1。代碼如下：select*intostscore_1fromstudscoreinfowherecourseid='a0101'5.2.1

SQL簡(jiǎn)單查詢2.查詢操作2)into子句from子句主要用來(lái)指定檢索數(shù)據(jù)的來(lái)源，指定數(shù)據(jù)來(lái)源的數(shù)據(jù)表和視圖的列表，該列表中的表名和視圖名之間用逗號(hào)分開(kāi)。from子句不可省略。語(yǔ)法格式如下：from{<table_sourse>}[,...n]例5-20

使用表別名查詢表studinfo中的記錄。代碼如下：selects.studno學(xué)號(hào),s.studname姓名fromstudinfos5.2.1

SQL簡(jiǎn)單查詢2.查詢操作3)from子句where子句用于對(duì)表中的數(shù)據(jù)記錄進(jìn)行篩選，其中構(gòu)造篩選的條件表達(dá)式是重點(diǎn)。需要強(qiáng)調(diào)的是，在where子句中不能使用聚合函數(shù)及別名。語(yǔ)法格式如下：where<search_condition>功能：限制結(jié)果集內(nèi)返回的行。查詢的限制條件可以是比較運(yùn)算符(=、<>、<、>、>=等)、范圍說(shuō)明(betweenand和notbetweenand)、可選值列表(in、notin)、模式匹配(like、notlike)、是否為空值(isnull和isnotnull)、上述條件的邏輯組合(and、or、not)。分別介紹如下：5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(1)使用比較查詢條件。比較查詢條件由表達(dá)式的雙方和比較運(yùn)算符組成，系統(tǒng)根據(jù)查詢條件的真假來(lái)決定某一條記錄是否滿足查詢條件。只有滿足查詢條件的記錄才會(huì)出現(xiàn)在最終結(jié)果集中。例5-21

查詢成績(jī)大于70的學(xué)生成績(jī)信息。代碼如下：select*fromstudscoreinfowherestudscore>70例5-22

查詢1981年1月1日及以后出生的學(xué)生信息。代碼如下：select*fromstudscoreinfowherestudbirthday>='1981/01/01'5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(2)使用邏輯運(yùn)算符。and連接兩個(gè)布爾表達(dá)式并當(dāng)兩個(gè)表達(dá)式都為true時(shí)返回“true”。or將兩個(gè)條件結(jié)合起來(lái)。not用于反轉(zhuǎn)查詢條件的結(jié)果。其優(yōu)先級(jí)順序是：括號(hào)最優(yōu)先，其次not＞and＞or。例5-23

查詢學(xué)生成績(jī)?cè)?0到70之間的所有記錄。代碼如下：select*fromstudscoreinfowherestudscore＞=60andstudscore＜=70例5-24

查詢學(xué)生成績(jī)小于等于70或者大于等于90的所有記錄。代碼如下：select*fromstudscoreinfowherestudscore＜=70orstudscore＞=905.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(3)使用范圍查詢條件。內(nèi)含范圍條件(between…and)要求返回記錄某個(gè)字段的值在兩個(gè)指定值范圍內(nèi)，同時(shí)包括這兩個(gè)指定的值。排除范圍條件(notbetween…and)則相反。例5-25

查詢學(xué)生成績(jī)?cè)?0到80之間的記錄。代碼如下：select*fromstudscoreinfowherestudscorebetween70and805.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(4)使用列表查詢條件。in關(guān)鍵字的格式為：in(列表值1，列表值2，…)。其功能是將返回所有與列表中的任意一個(gè)值匹配的記錄。例5-26

查詢課程代碼courseid為a0101、a0102的學(xué)生成績(jī)信息。代碼如下：select*fromstudscoreinfowherecourseidin('a0101','a0102')5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(5)使用模式查詢條件(like或notlike)。模式查詢條件常用來(lái)返回符合某種格式的所有記錄。模式匹配通配符是like，另外還需要使用模式通配符，如表5-1所示。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句通配符描述%包含0個(gè)或任意多個(gè)的任意字符串_代表任何單個(gè)字符[]指定范圍([a-f])或集合([abcdef])中的任何一個(gè)字符[^]不在指定范圍([a-f])或集合([abcdef])的任何一個(gè)字符表5-1like通配符一覽表例5-27

查詢姓名以“李”字開(kāi)頭的學(xué)生信息。代碼如下：select*fromstudinfowherestudnamelike'李%'例5-28

查詢姓名第二個(gè)字為“瓊”的學(xué)生信息。代碼如下：select*fromstudinfowherestudnamelike'_瓊%'5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句(6)使用空值判斷查詢。空值查詢常用于查詢某一字段為空值的記錄，可以使用“isnull”(是空值)或“isnotnull”(不是空值)關(guān)鍵字來(lái)指定查詢條件。在表的某些列可能存在空值“null”?！皀ull”不是一種值，表示一種未知或不確定的狀態(tài)，它并不表示零、零長(zhǎng)度的字符串或空白(字符值)。例5-29

在班級(jí)信息表classinfo中，查詢班級(jí)描述為空的班級(jí)情況。代碼如下：select*fromclassinfowhereclassdescisnull5.2.1

SQL簡(jiǎn)單查詢2.查詢操作4)where子句有時(shí)需要對(duì)表中的數(shù)據(jù)進(jìn)行分組，然后對(duì)每個(gè)組單獨(dú)進(jìn)行統(tǒng)計(jì)計(jì)算，此時(shí)需要使用groupby子句。在按照指定的條件進(jìn)行分類計(jì)算時(shí)，可以使用聚合函數(shù)計(jì)算各組的數(shù)據(jù)。語(yǔ)法格式如下：groupby[all]group_by_expression[,...n]其中：group_by_expression是對(duì)表執(zhí)行分組的表達(dá)式，也稱分組列。注意，在使用groupby子句時(shí)，只有聚合函數(shù)和groupby分組的列才能出現(xiàn)在select子句中。如果需要在select子句出現(xiàn)的列，就要寫在分組字段的后面。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作5)groupby子句聚合函數(shù)在查詢結(jié)果集中生成匯總值。聚合函數(shù)(除count(*)以外)處理單個(gè)列中全部所選的值以生成一個(gè)結(jié)果值。聚合函數(shù)可以應(yīng)用于表中的所有行、where子句指定的表的子集或表中一組或多組行。常用的聚合函數(shù)及其含義如表5-2所示。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作5)groupby子句聚合函數(shù)含義sum(expression)數(shù)字表達(dá)式中所有值的和avg(expression)數(shù)字表達(dá)式中所有值的平均值count(expression)表達(dá)式中值的個(gè)數(shù)count(*)選定的行數(shù)max(expression)表達(dá)式中的最高值min(expression)表達(dá)式中的最低值表5-2聚合函數(shù)例5-30

統(tǒng)計(jì)所有成績(jī)的平均分。代碼如下：selectavg(studscore)fromstudscoreinfo例5-31

統(tǒng)計(jì)課程代碼A0101的所有成績(jī)的平均分。代碼如下：selectavg(studscore)fromstudscoreinfowherecourseid='A0101'例5-32

統(tǒng)計(jì)課程代碼A0101的成績(jī)平均分、最高分、最低分，并指定別名。代碼如下：selectavg(studscore)avgscore,max(studscore)maxscore,min(studscore)minscorefromstudscoreinfowherecourseid='A0101'聚合函數(shù)通常與groupby一起使用，對(duì)給定字段分組之后的結(jié)果進(jìn)行分類計(jì)算。顯示結(jié)果時(shí)，可以對(duì)聚合函數(shù)使用別名。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作5)groupby子句例5-33

在表studinfo中，統(tǒng)計(jì)男生和女生的人數(shù)。代碼如下：selectstudsex,count(studsex)as人數(shù)fromstudinfogroupbystudsex注意：selectclassid,studsex,count(studsex)as人數(shù)fromstudinfogroupbystudsex直接在select子句中加classid是不對(duì)的。正確的寫法如下：selectclassid,studsex,count(studsex)as人數(shù)fromstudinfogroupbystudsex,classid5.2.1

SQL簡(jiǎn)單查詢2.查詢操作5)groupby子句例5-34

在studscoreinfo表中，統(tǒng)計(jì)各個(gè)學(xué)生的總分、課程門數(shù)、平均分及總平均分。并用cast函數(shù)保留2位小數(shù)。代碼如下：selectstudno,sum(studscore)as總分,count(*)課程門數(shù),cast(avg(studscore)asnumeric(6,2))平均分,cast(sum(studscore)/count(*)asnumeric(6,2))as總平均分fromstudscoreinfogroupbystudno5.2.1

SQL簡(jiǎn)單查詢2.查詢操作5)groupby子句having子句用于指定分組搜索條件，是對(duì)分組之后的結(jié)果再次篩選。having子句必須和groupby子句一起使用，有having子句就必須有g(shù)roupby子句，但有g(shù)roupby子句可以沒(méi)有having子句。having和where類似，其區(qū)別在于where子句在進(jìn)行分組操作之前對(duì)查詢結(jié)果進(jìn)行篩選，而having子句是對(duì)分組操作之后的結(jié)果再次篩選。作用的對(duì)象也不同，where子句作用于表和視圖，having子句作用于組。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作6)having子句例5-35

查詢平均分在75及以上的學(xué)生記錄。代碼如下：selectstudno,cast(avg(studscore)asnumeric(6,1))avgscorefromstudscoreinfogroupbystudnohavingavg(studscore)>=75注意：聚合函數(shù)只能應(yīng)用于select子句和having子句。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作6)having子句orderby子句指定查詢結(jié)果的排序方式。其語(yǔ)法格式如下：orderby{order_by_expression[asc|desc]}[,...n]order_by_expression可以是表或視圖的列的名稱或別名。asc表示升序(默認(rèn))；desc表示降序。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作7)orderby子句例5-35例5-36

統(tǒng)計(jì)各個(gè)學(xué)生的平均分，并按平均分由高到低進(jìn)行排序。代碼如下：selectstudno,avg(studscore)as平均分fromstudscoreinfogroupbystudnoorderby平均分desc例5-37

查詢所有圖書的編號(hào)、書名、單價(jià)和類別代號(hào)，先按類別代號(hào)升序排列，對(duì)于類別代號(hào)相同的圖書再按單價(jià)降序排列。代碼如下：selectbookid,title,unitprice,categorycodefrombooksorderbycategorycode,unitpricedesc注意：升序asc可以省略，但降序desc不可省略。5.2.1

SQL簡(jiǎn)單查詢2.查詢操作7)orderby子句前一節(jié)介紹了SQL查詢的基本功能和方法，本節(jié)通過(guò)介紹SQL高級(jí)查詢技術(shù)，包括多表關(guān)聯(lián)查詢，union子句的使用，子查詢(嵌套查詢)的使用，左連接、右連接、全連接查詢，實(shí)用SQL語(yǔ)句的使用等內(nèi)容和方法，完成一些重要而復(fù)雜的操作。5.2.2

SQL高級(jí)查詢SQL簡(jiǎn)單查詢是基于單個(gè)數(shù)據(jù)表來(lái)實(shí)現(xiàn)的。在數(shù)據(jù)庫(kù)中，各個(gè)表存放著不同的數(shù)據(jù)，表和表之間存在著各種聯(lián)系，往往需要用多個(gè)表中的數(shù)據(jù)來(lái)組合查詢，補(bǔ)充所需要的信息。所謂多表查詢是相對(duì)于單表查詢而言的，是指從多個(gè)關(guān)聯(lián)表中查詢數(shù)據(jù)，通常采用等值多表查詢的方式，即在where子句中設(shè)置等值的條件來(lái)查詢多個(gè)數(shù)據(jù)表中關(guān)聯(lián)的數(shù)據(jù)。這種查詢要求關(guān)聯(lián)的多個(gè)數(shù)據(jù)表的某些字段具有相同的屬性，即具有相同的數(shù)據(jù)類型和寬度。5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢?cè)趙here子句中，可以將具有相等的字段值的兩張表連接起來(lái)，數(shù)據(jù)來(lái)源于兩張表。例5-38

查詢某班級(jí)學(xué)生的基本信息和成績(jī)信息，數(shù)據(jù)來(lái)源于表student和表sc，代碼如下：select*fromstudent,scwherestudent.s#=sc.s#查詢結(jié)果如圖5-4所示。5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢1)雙表關(guān)聯(lián)查詢圖5-4

等值查詢從查詢結(jié)果中可以看到，有5個(gè)學(xué)生有成績(jī)。但同時(shí)，也可以發(fā)現(xiàn)s#出現(xiàn)重復(fù)，也不知道c#代表什么課程。例5-39

查詢某班級(jí)學(xué)生的基本信息和成績(jī)信息，包括s#、sname、age、c#、score等字段，數(shù)據(jù)來(lái)源于student表和sc表。代碼如下：selectstudent.s#,sname,age,c#,scorefromstudent,scwherestudent.s#=sc.s#5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢1)雙表關(guān)聯(lián)查詢例5-40

使用別名、邏輯運(yùn)算符查詢滿足復(fù)雜條件的記錄，結(jié)果如圖5-5所示。代碼如下：selects.s#學(xué)號(hào),sname姓名,c#課程代碼,score成績(jī)fromstudents,scwheres.s#=sc.s#andc#='001'5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢1)雙表關(guān)聯(lián)查詢圖5-5

雙表別名查詢有時(shí)需要將多個(gè)表進(jìn)行關(guān)聯(lián)查詢，才能比較完整地反映有關(guān)信息。超過(guò)兩個(gè)表的關(guān)聯(lián)查詢稱為多表查詢，返回多個(gè)表中與連接條件相互匹配的記錄，不返回不相匹配的記錄。5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢2)多表關(guān)聯(lián)查詢例5-41

根據(jù)圖5-6所示的student、sc和course表，查詢學(xué)生的基本信息，包括個(gè)人基本情況、課程信息和成績(jī)。代碼如下：selects.s#學(xué)號(hào),sname姓名,sc.c#課程代碼,ame課程名稱,c.credit學(xué)分,score成績(jī)fromstudents,coursec,scwheres.s#=sc.s#andsc.c#=c.c#andsc.c#='001'5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢2)多表關(guān)聯(lián)查詢圖5-6sc表(s#和c#是雙屬性主鍵，c#是外鍵)查詢結(jié)果如圖5-7所示。5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢2)多表關(guān)聯(lián)查詢圖5-7多表關(guān)聯(lián)查詢結(jié)果在單表查詢中，可以使用聚合函數(shù)進(jìn)行統(tǒng)計(jì)，但統(tǒng)計(jì)結(jié)果的信息不夠全面，需要使用多表查詢補(bǔ)齊相關(guān)信息。在多表關(guān)聯(lián)查詢中，仍可以使用聚合函數(shù)進(jìn)行統(tǒng)計(jì)。例5-42

在student、sc等表中，查詢學(xué)生的學(xué)號(hào)、姓名、平均分等字段信息。代碼如下：selects.s#,s.sname,avg(score)asavgscorefromstudents,scwheres.s#=sc.s#groupbys.s#,sname例5-42中，兩表通過(guò)學(xué)號(hào)關(guān)聯(lián)，因?yàn)閮杀砭袑W(xué)號(hào)字段，所以為student表指定別名，以別名對(duì)學(xué)號(hào)字段進(jìn)行限制。使用了groupby子句，只有g(shù)roupby后面的字段和聚合函數(shù)才能放在select子句后面，因此，除學(xué)號(hào)之外，姓名字段也必須放在groupby子句后面。5.2.2

SQL高級(jí)查詢1.關(guān)聯(lián)表查詢3)關(guān)聯(lián)表使用聚合函數(shù)使用union運(yùn)算符可以將兩個(gè)或多個(gè)select子句的結(jié)果組合成一個(gè)結(jié)果集。使用union組合的結(jié)果集都必須滿足三個(gè)條件：具有相同的結(jié)構(gòu)，字段數(shù)目相同，結(jié)果集中相應(yīng)字段的數(shù)據(jù)類型必須兼容。同時(shí)還要注意以下幾點(diǎn)：(1)union中每一個(gè)查詢所涉及的列必須具有相同的列數(shù)、相同的數(shù)據(jù)類型，并以相同的順序出現(xiàn)。(2)最后結(jié)果集里的列名來(lái)自第一個(gè)select語(yǔ)句。(3)若union中包含orderby子句，則將對(duì)最后的結(jié)果集排序。(4)在合并結(jié)果集時(shí)，默認(rèn)從最后的結(jié)果集中刪除重復(fù)的行，除非使用all關(guān)鍵字。5.2.2

SQL高級(jí)查詢2.使用union連接union運(yùn)算符的語(yǔ)法格式如下：select子句union[all]select子句例5-43

查詢成績(jī)?cè)?0～70分?jǐn)?shù)段和90及以上區(qū)域的學(xué)生信息。代碼如下：select*fromscwherescore>=60andscore<=70unionallselect*fromscwherescore>=905.2.2

SQL高級(jí)查詢2.使用union連接

例5-44

在sc表中，使用union統(tǒng)計(jì)課程編號(hào)為001的各分?jǐn)?shù)段人數(shù)。代碼如下：select'優(yōu)秀'as等級(jí),'[90,100]'as分?jǐn)?shù)段,count(*)as人數(shù)fromscwherec#='001'andscorebetween90and100unionselect'良好'as等級(jí),'[80,90)'as分?jǐn)?shù)段,count(*)as人數(shù)fromscwherec#='001'andscore＞=80andscore＜905.2.2

SQL高級(jí)查詢2.使用union連接unionselect'中等'as等級(jí),'[70,80)'as分?jǐn)?shù)段,count(*)as人數(shù)fromscwherec#='A0101'andscore＞=70andscore＜80unionselect'及格'as等級(jí),'[60,70)'as分?jǐn)?shù)段,count(*)as人數(shù)fromscwherec#='001'andscore＞=60andscore＜70unionselect'不及格'as等級(jí),'[0,60)'as分?jǐn)?shù)段,count(*)as人數(shù)fromscwherec#='001'andscore＜605.2.2

SQL高級(jí)查詢2.使用union連接在SQL中，當(dāng)一個(gè)查詢語(yǔ)句嵌套在另一個(gè)查詢的查詢條件之中時(shí)，該查詢稱為嵌套查詢，又稱為子查詢。在一個(gè)外層查詢中包含有另一個(gè)內(nèi)層查詢，其中外層查詢稱為主查詢，內(nèi)層查詢稱為子查詢。通常情況下，先使用嵌套查詢中的子查詢挑選出部分?jǐn)?shù)據(jù)，以作為主查詢的數(shù)據(jù)來(lái)源或搜索條件。子查詢總是寫在圓括號(hào)中，任何允許使用表達(dá)式的地方都可以使用子查詢。在Transact-SQL中，包含子查詢的語(yǔ)句和語(yǔ)義上等效的不包含子查詢的語(yǔ)句在性能上通常沒(méi)有差別。但是，在一些必須檢查存在性的情況下，使用連接查詢會(huì)獲得更好的性能。5.2.2

SQL高級(jí)查詢3.子查詢1)子查詢的基本概念使用子查詢時(shí)應(yīng)注意以下幾點(diǎn)：(1)子查詢的基本結(jié)構(gòu)和基本查詢一樣，其中select子句和from子句是必需的，而where子句、groupby子句和having子句是可選的。(2)子查詢的select語(yǔ)句通常使用圓括號(hào)括起來(lái)。(3)子查詢的select語(yǔ)句中通常只有一個(gè)列，不能使用compute子句。(4)除非在子查詢中使用了top選項(xiàng)，否則子查詢中不能使用orderby子句。(5)如果某個(gè)數(shù)據(jù)表只出現(xiàn)在子查詢中，而不出現(xiàn)在主查詢中，那么在數(shù)據(jù)列表中不能包含該數(shù)據(jù)表中的字段。5.2.2

SQL高級(jí)查詢3.子查詢1)子查詢的基本概念(1)使用in關(guān)鍵字。當(dāng)子查詢的結(jié)果不唯一時(shí)，可以在子查詢前使用運(yùn)算符in。in關(guān)鍵字在大多數(shù)情況下應(yīng)用于嵌套查詢中，首先使用select語(yǔ)句選定一個(gè)范圍，然后將選定的范圍作為in關(guān)鍵字的符合條件的列表，從而得到最終的結(jié)果。語(yǔ)法格式如下：test_expression[not]in(subquery|expression[,...n])5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用參數(shù)說(shuō)明：①test_expression是任何有效的SQLServer表達(dá)式。②subquery是包含某列結(jié)果集的子查詢。expression是一個(gè)表達(dá)式列表，用來(lái)測(cè)試是否匹配。例5-45

查詢中考成績(jī)滿分的學(xué)生信息。代碼如下：select*fromstudentwheres#in(selects#fromscwherescore=100)5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-46

在表orderitems、表books中，查詢書名包含字符串ASP的圖書的訂單號(hào)、數(shù)量。代碼如下：selectorderid,quantityfromorderitemswherebookidin(selectbookidfrombookswheretitlelike'%ASP%')(2)使用比較運(yùn)算符的子查詢。使用比較運(yùn)算符的子查詢的結(jié)果必須是單值，即子查詢的結(jié)果為單行單列的值。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-47

查詢ISBN為“978-7-1254-2487-1”的圖書的訂單號(hào)和訂貨數(shù)量。代碼如下：selectorderid,quantityfromorderitemswherebookid=(selectbookidfrombookswhereisbn='978-7-1254-2487-1')(3)使用some/any關(guān)鍵字。some/any關(guān)鍵字完全等價(jià)。通過(guò)比較運(yùn)算符將一個(gè)表達(dá)式的值或列值與子查詢返回的一列值中的每一個(gè)進(jìn)行比較，如果哪行的比較結(jié)果為真，則滿足條件立即返回該行。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用語(yǔ)法格式如下：scalar_expression{=|<>|!=|>|>=|!>|<=|!<}{some|any}(subquery)參數(shù)說(shuō)明：①scalar_expression：任何有效的SQLServer表達(dá)式。②{=|<>|!=|>|>=|!>|<=|!<}：任何有效的比較運(yùn)算符。③{some|any}：指定應(yīng)進(jìn)行比較。當(dāng)子查詢的結(jié)果為多值時(shí)，使用some|any表示匹配子查詢結(jié)果中的任意一個(gè)值即可。④subquery：包含某列結(jié)果集的子查詢。所返回列的數(shù)據(jù)類型必須是與scalar_expression相同的數(shù)據(jù)類型。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-48

查詢表books的圖書單價(jià)高于orderitems表中orderid為1的最低單價(jià)的圖書信息。代碼如下：select*frombookswhereunitprice＞any(selectpricefromorderitemswhereorderid=1)(4)使用all關(guān)鍵字。all的子查詢是把列值與子查詢結(jié)果進(jìn)行比較，但是它要求所有列的查詢結(jié)果都為真，否則不返回行。使用all表示匹配子查詢的所有值才可以。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用語(yǔ)法格式如下：scalar_expression{=|<>|!=|>|>=|!>|<=|!<}all(subquery)其中參數(shù)subquery返回單列結(jié)果集的子查詢，是受限的select子句(不允許使用orderby子句、compute子句和into子句)。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-49

查詢表books的圖書單價(jià)高于或等于表orderitems中orderid為2的最高單價(jià)的圖書信息。代碼如下：select*frombookswhereunitprice＞=all(selectpricefromorderitemswhereorderid=2)或者使用單值比較，執(zhí)行結(jié)果與all一樣。代碼如下：select*frombookswhereunitprice＞=(selectmax(price)fromorderitemswhereorderid=2)5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用(5)使用exists關(guān)鍵字。使用exists關(guān)鍵字指定一個(gè)子查詢，檢測(cè)行的存在。exists搜索條件并不真正地使用子查詢的結(jié)果，它僅僅檢查子查詢是否返回了任何結(jié)果，因此exists子查詢中的select子句可用任意列名或用*號(hào)。關(guān)鍵字exists用來(lái)檢驗(yàn)子查詢的結(jié)果是否為空。在使用exists的子查詢中，外層查詢要依次判斷exists運(yùn)算是否為“true”。如果非空，則exists運(yùn)算返回“true”；如果為空，則exists運(yùn)算返回“false”。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-50

在顧客表customers、訂單表orders中，查詢2009年及以后購(gòu)買過(guò)圖書的顧客的編號(hào)和姓名。代碼如下：selectc.customerid,c.customernamefromcustomerscwhereexists(select*fromordersowhereo.orderdate>='09/01/01'andc.customerid=o.customerid)當(dāng)然，也可以使用notin關(guān)鍵字，查詢結(jié)果一樣。代碼如下：selectcustomerid,customernamefromcustomerswherecustomeridin(selectcustomeridfromorderswhereorderdate>='09/01/01')5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-51

在學(xué)生表student、成績(jī)表sc中，查詢選修了課程的學(xué)生學(xué)號(hào)和姓名。代碼如下：selects#,snamefromstudentwhereexists(select*fromscwheresc.s#=student.s#)執(zhí)行分析：對(duì)于student的每一行，根據(jù)該行的s#去表sc中查找有無(wú)匹配記錄。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用(6)在select子句中使用子查詢。例5-52

在表orderitems和表books中，查詢所有圖書的編號(hào)、書名、單價(jià)及訂單總量。代碼如下：selectbookid,title,unitprice,(selectsum(quantity)fromorderitemsiwherei.bookid=b.bookid)as訂單總量frombooksb5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-52中，不是先執(zhí)行子查詢?nèi)缓笤賵?zhí)行外層查詢。首先執(zhí)行的是外層的from子句，讀取books中的數(shù)據(jù)，同時(shí)定義別名，然后執(zhí)行外層的select子句，顯示第一本書的書號(hào)、書名、單價(jià)，執(zhí)行子查詢，查找第一本書在orderitems中的訂單數(shù)量并匯總統(tǒng)計(jì)，顯示統(tǒng)計(jì)結(jié)果，并定義別名“訂單總量”。最后，依次顯示每一本書的相關(guān)信息。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用(7)在insert語(yǔ)句中使用子查詢。使用insertinto…values語(yǔ)句一次向表中插入的記錄是有限的，可以將values子句替換為一個(gè)select語(yǔ)句，將select語(yǔ)句檢索到的數(shù)據(jù)(可能若干條)插入到指定的表中。5.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用例5-53

在當(dāng)前數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)與成績(jī)表sc結(jié)構(gòu)相同的sc1，將成績(jī)?cè)?5和100之間的記錄復(fù)制到該表中。操作步驟如下：首先，復(fù)制表sc的結(jié)構(gòu)。復(fù)制表的結(jié)構(gòu)時(shí)，使用top關(guān)鍵字，或者使用where條件。selecttop0s#,c#,scoreintosc1fromscselects#,c#,scoreintosc1fromscwhere0<>0其次，將子查詢結(jié)果向表sc1中插入記錄。insertintosc1select*fromscwherescorebetween85and1005.2.2

SQL高級(jí)查詢3.子查詢2)子查詢的使用在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中，表建立時(shí)各數(shù)據(jù)之間的關(guān)系不必確定，常把一個(gè)實(shí)體的所有信息存放在一個(gè)表中。當(dāng)檢索數(shù)據(jù)時(shí)，通過(guò)連接操作查詢出存放在多個(gè)表中的不同實(shí)體的信息。連接操作給用戶帶來(lái)很大的靈活性，可以在任何時(shí)候增加新的數(shù)據(jù)類型，為不同實(shí)體創(chuàng)建新的表，然后通過(guò)連接進(jìn)行查詢。如果一個(gè)查詢需要對(duì)多個(gè)表進(jìn)行操作，就稱為連接查詢。從查詢數(shù)據(jù)的來(lái)源來(lái)實(shí)現(xiàn)表之間的連接，連接時(shí)通過(guò)各個(gè)表之間共同列的關(guān)聯(lián)性來(lái)查詢數(shù)據(jù)。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢。5.2.2

SQL高級(jí)查詢4.連接查詢1)基本概念及分類連接查詢主要包括內(nèi)連接查詢、外連接查詢和交叉連接查詢等。具體如下：(1)內(nèi)連接是SQLServer缺省的連接方式，又分為等值連接、自然連接和不等連接三種。(2)外連接的連接查詢結(jié)果集中既包含那些滿足條件的行，還包含其中某個(gè)表的全部行，有三種形式的外連接：左外連接、右外連接和全外連接。(3)交叉連接即笛卡兒積，是指兩個(gè)關(guān)系中所有元組的所有組合。一般情況下，交叉連接查詢是沒(méi)有實(shí)際意義的。5.2.2

SQL高級(jí)查詢4.連接查詢1)基本概念及分類(1)內(nèi)連接查詢。內(nèi)連接查詢(innerjoin…on…)使用比較運(yùn)算符進(jìn)行表間某(些)列數(shù)據(jù)的比較操作，并列出這些表中與連接條件相匹配的數(shù)據(jù)行。在內(nèi)連接查詢中，只有滿足連接條件的元組才能出現(xiàn)在結(jié)果關(guān)系中。內(nèi)連接的3種連接方式如下：①等值連接。在連接條件中使用等號(hào)(=)運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復(fù)列。②非等值連接。在連接條件中使用除等號(hào)以外的其他比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括>、>=、<=、<、!>、!<和<>。③自然連接。在連接條件中使用等于(=)運(yùn)算符比較被連接列的列值，查詢所涉及的兩個(gè)關(guān)系模式有公共屬性，且公共屬性值相等，相同的公共屬性只在結(jié)果關(guān)系中出現(xiàn)一次。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用內(nèi)連接查詢的語(yǔ)法格式如下：selectselect_listfrom{<table_source><join_type><table_source>[,...n]on<search_condition>}參數(shù)說(shuō)明：·<table_source>：參與連接操作的表名，可以是一張表，也可以是多張表?！?lt;join_type>=inner[outer]join·on<search_condition>：連接操作中的on子句指出連接條件，它由被連接表中的列和比較運(yùn)算符、邏輯運(yùn)算符等構(gòu)成。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用注意：無(wú)論哪種連接，都不能對(duì)text、ntext和image數(shù)據(jù)類型列進(jìn)行直接連接。例5-54

在表student、sc中，查詢學(xué)生的基本信息和成績(jī)信息。代碼如下：select*fromstudentinnerjoinsconstudent.s#=sc.s#5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用例5-55

在表student、sc、class、course中，查詢學(xué)生的基本信息、班級(jí)信息、課程信息和成績(jī)信息。代碼如下：selectstudent.s#,sname,age,sex,student.classid,class.classname,course.c#,ame,course.credit,scorefromclassinnerjoinstudentonclass.classid=student.classidinnerjoinsconstudent.s#=sc.s#innerjoincourseoncourse.c#=sc.c#5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用查詢結(jié)果如圖5-8所示。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用圖5-8內(nèi)連接查詢結(jié)果(2)外連接查詢。外連接分為左連接、右連接和全連接三種。與內(nèi)連接不同的是，外連接不僅列出與連接條件相匹配的行，而還會(huì)列出左表(左外連接時(shí))、右表或兩個(gè)表中所有符合搜索條件的數(shù)據(jù)行。注意，此時(shí)以on給出搜索條件。外連接查詢的語(yǔ)法格式如下：selectselect_listfrom{<table_source><join_type><table_source>[,...n]on<search_condition>}參數(shù)說(shuō)明：<join_type>=left|right|full[outer]join5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用①左外連接。左外連接(leftouterjoin或leftjoin)的結(jié)果集包括leftjoin或leftouterjoin子句中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒(méi)有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用例5-56

在表student、sc中，查詢表student的所有學(xué)生的基本信息和成績(jī)信息。代碼如下：selectstudent.s#,student.sname,student.classid,sc.c#,sc.scorefromstudentleftouterjoinsconstudent.s#=sc.s#查詢結(jié)果如圖5-9所示。從查詢結(jié)果看，該表中2018010106、2018010107、2018010108、2018010109等學(xué)生沒(méi)有選修課程，也就沒(méi)有成績(jī)，顯示為“null”。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用圖5-9左外連接查詢結(jié)果②右外連接。右外連接(rightouterjoin…on…或rightjoin…on…)使用rightjoin或rightouterjoin子句，是左向外連接的反向連接，將返回右表的所有行。如果右表的某行在左表中沒(méi)有匹配行，則將為左表返回空值。例5-57

在表student、sc中，查詢所有學(xué)生的基本信息和成績(jī)信息。代碼如下：selectstudent.s#,student.sname,student.classid,sc.c#,sc.scorefromstudentrightouterjoinsconstudent.s#=sc.s#從查詢結(jié)果看，只包括圖5-9的前12條記錄，也就是有成績(jī)的數(shù)據(jù)行。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用③全連接。全連接(fullouterjoin…on…或fulljoin…on…)使用fulljoin或fullouterjoin子句返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒(méi)有匹配行時(shí)，則另一個(gè)表的選擇列表列包含空值。如果表之間有匹配行，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值。例5-58

在表student、sc中，查詢出現(xiàn)在兩個(gè)表中的所有學(xué)生的基本信息和成績(jī)信息。代碼如下：selectstudent.s#,student.sname,student.classid,sc.c#,sc.scorefromstudentfullouterjoinsconstudent.s#=sc.s#5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用(3)交叉連接查詢。交叉連接(crossjoin)沒(méi)有where子句，它返回連接表中所有數(shù)據(jù)行的笛卡爾積，是指兩個(gè)關(guān)系中所有元組的所有組合，其結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個(gè)表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個(gè)表中符合查詢條件的數(shù)據(jù)行數(shù)。如果兩個(gè)關(guān)系模式中有同名屬性，那么應(yīng)該在執(zhí)行查詢語(yǔ)句之前使用關(guān)系名限定同名的屬性。如果兩個(gè)關(guān)系中的元組個(gè)數(shù)分別是m和n，那么結(jié)果關(guān)系中的元組個(gè)數(shù)是兩個(gè)關(guān)系中的元組個(gè)數(shù)的乘積，即m×n。5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用例5-59

使用交叉連接查詢學(xué)生的基本信息和成績(jī)信息。代碼如下：select*fromstudentcrossjoinsc下面的語(yǔ)句，執(zhí)行結(jié)果同上。select*fromstudent,sc5.2.2

SQL高級(jí)查詢4.連接查詢2)連接查詢的應(yīng)用compute子句是T-SQL中特有的一個(gè)子句，使用compute子句允許用戶同時(shí)觀察查詢所得的各列數(shù)據(jù)的細(xì)節(jié)以及綜合各列數(shù)據(jù)所產(chǎn)生的總和。通過(guò)compute子句既可以計(jì)算數(shù)據(jù)分類后的和，也可以計(jì)算所有數(shù)據(jù)的總和。語(yǔ)法格式如下：compute{{sum|avg|count|max|min}(expression)}[,...n][byexpression[,...n]]5.2.2

SQL高級(jí)查詢5.實(shí)用SQL語(yǔ)句的使用1)使用computeby子句分類統(tǒng)計(jì)例5-60

在表books中，查詢顯示所有圖書的編號(hào)、書名、單價(jià)和類別代碼，最后顯示所有圖書的總價(jià)。代碼如下：selectbookid,title,unitprice,categorycodefrombooksorderbycategorycodecomputesum(unitprice)查詢結(jié)果如圖5-10所示。在結(jié)果的最后添加了一行表示所有圖書單價(jià)之和。5.2.2

SQL高級(jí)查詢5.實(shí)用SQL語(yǔ)句的使用1)使用computeby子句分類統(tǒng)計(jì)圖5-10compute子句查詢結(jié)果例5-61

在表books中，查詢顯示所有圖書的編號(hào)、書名、單價(jià)和類別代碼，并顯示每類圖書的總價(jià)和所有圖書的總價(jià)。代碼如下：selectbookid,title,unitprice,categorycodefrombooksorderbycategorycodecomputesum(unitprice)bycategorycode在compute子句中使用關(guān)鍵字by可以實(shí)現(xiàn)數(shù)據(jù)行分組后再對(duì)每個(gè)組分別進(jìn)行統(tǒng)計(jì)的功能。查詢結(jié)果如圖5-11所示。5.2.2