安卓課堂知識

6.1簡單select查詢6.2select語句的統(tǒng)計功能6.3select語句中的多表連接6.4子查詢第6章數(shù)據(jù)查詢2/3/20231教學要求：通過本章學習，讀者應掌握以下內容：利用SELECT語句進行單表查詢的方法利用SELECT語句進行多表連接查詢的方法嵌套查詢的方法2/3/202326．1簡單SELECT語句6．1．1SELECT的語法格式SELECT語句的基本語法格式如下：SELECT<字段列表> [INTO新表名] FROM<表名/視圖名列表> [WHERE條件表達式] [GROUPBY列名列表] [HAVING條件表達式] [ORDERBY列名1[ASC|DESC]，列名2[ASC|DESC]，．．．，列名n[ASC|DESC]][COMPUTE行聚合函數(shù)名（統(tǒng)計表達式）[,…n][BY分類表達式[,…n]]]2/3/20233其中各子名說明如下：（1）字段列表用于指出要查詢的字段，也就是查詢結果中包含的字段的名稱。（2）INTO子句用于創(chuàng)建一個新表，并將查詢結果保存到這個新表中。（3）FROM子句用于指出所要進行查詢的數(shù)據(jù)來源，即來源于哪些表或視圖的名稱。（4）WHERE子句用于指出查詢數(shù)據(jù)時要滿足的檢索條件。（5）GROUPBY子句用于對查詢結果分組。（6）ORDERBY子句用于對查詢結果排序。2/3/20234SELECT語句的功能如下：從FROM列出的數(shù)據(jù)源表中，找出滿足WHERE檢索條件的記錄，按SELECT子句的字段列表輸出查詢結果表，在查詢結果表中可進行分組與排序。說明：在SELECT語句中SELECT子句與FROM子句是不可少的，其余的是可選的。2/3/202356．1．2基本的SELECT語句SELECT語句的基本形式如下： SELECT<字段列表> FROM<表名列表> [WHERE查詢條件]1．選擇表中若干列USExsglGOSELECT學號,姓名FROMxs2/3/202362．查詢表中全部列（*）將表中所有屬性都選出來，可以有兩種方法。一種方法是在SELECT命令后面列出所有列名。如果列的顯示順序與其在基表中的順序相同，也可以簡單地將<字段列表>簡寫為“*”。USExsglGOSELECT*FROMkcGO2/3/202373．設置字段別名T-SQL提供了在SELECT語句中操作別名的方法。用戶可以根據(jù)實際需要對查詢數(shù)據(jù)的列標題進行修改，或者為沒有標題的列加上臨時標題。其語法格式為：列表達式[as]別名或：別名=列表達式【例6.3】查詢xsgl數(shù)據(jù)庫的kc表，列出表中的所有記錄，每個記錄名稱依次為課程編號，課程名稱，課程學分及課程時數(shù)。USExsglGOSELECT課程號AS課程編號,課程名AS課程名稱,學分AS課程學分,學時數(shù)AS課程時數(shù)FROMkc2/3/202384．查詢經過計算的值SELECT子句的<字段列表>不僅可以是表中的屬性列，也可以是表達式，包括字符串常量、函數(shù)等。其語法格式為：計算字段名=表達式【例6.4】查詢所有學生的學號，姓名及年齡。USExsglGOSELECT學號,姓名,年齡=DATEDIFF(YY,出生時間,GETDATE())FROMxsGO2/3/202395．返回全部記錄（ALL）要返回所有記錄可在SELECT后使用ALL，ALL是默認設置，因此也可以省略?！纠?.5】查詢XS表中所有學生專業(yè)。USExsglGOSELECT專業(yè)FROMxsGO2/3/2023106．過濾重復記錄（DISTINCT）在例6.5的執(zhí)行結果集中顯示重復行。如果讓重復行只顯示一次，需在SELECT子句中用DISTINCT指定在結果集中只能顯示唯一行。【例6.6】查詢xs表中的學生所在專業(yè)有哪些。（重復專業(yè)只顯示一次）。USExsglGOSELECTDISTINCT專業(yè)FROMxsGO2/3/2023117．僅返回前面若干條記錄其語法格式如下：SELECT[TOPn︱TOPnPERCENT]列名l[，．．．n]FROM表名其中：TOPn表示返回最前面的n行，n表示返回的行數(shù)。TOPnPERCENT表示返回的最前面的n％行?！纠?.7】查詢xs表中前5條記錄USExsglGOSELECTTOP5*FROMxsGO2/3/202312【例6.8】查詢XS表中前面10％行記錄USExsglGOSELECTTOP10PERCENT*FROMxsGO2/3/2023136．1．3使用INTO子句使用INTO子句允許用戶定義一個新表，并且把SELECT子句的數(shù)據(jù)插入到新表中，其語法格式如下。SELECT<字段列表> INTO新表名 FROM<表名列表> WHERE查詢條件使用INTO子句插入數(shù)據(jù)時，應注意以下幾點：（1）新表不能存在，否則會產生錯誤信息。（2）新表中的列和行是基于查詢結果集的。（3）使用該子句必須在目的數(shù)據(jù)庫中具有CREATETABLE權限。（4）如果新表名稱的開頭為“#”，則生成的是臨時表。注意：使用INTO子句，通過在WHERE子句中FALSE條件，可以創(chuàng)建一個和源表結構相同的空表。2/3/202314【例6.9】創(chuàng)建一個和xs表結構相同的xs_new表。USExsglGOSELECT*INTOxs_newFROMxsWHERE6>8設置“WHERE6>8”這樣一個明顯為邏輯否的條件的目的是為了只保留xs表的結構，而不返回任何記錄。2/3/202315【例6.10】查詢所有女生的信息并將結果保存在名為“女生表”的數(shù)據(jù)表中。USExsglGOSELECT*INTO女生表FROMxsWHERE性別='女'2/3/202316【例6.11】查詢所有男生的信息并將結果存入臨時表中。USExsglSELECT*INTO#TEMPDBFROMxsWHERE性別='男'要查看臨時表的內容可用下面的語句：SELECT*FROM#TEMPDB2/3/2023176．1．4使用WHERE子句其語法格式如下：SELECT列名1[,…列名n]FROM表名WHERE條件表達式使用WHERE子句可以限制查詢的記錄范圍。在使用時，WHERE子句必須緊跟在FROM子句后面。WHERE子句中的條件是—個邏輯表達式，其中可以包含的運算符見表6-1。2/3/2023182/3/2023191．比較表達式作查詢條件比較表達式是邏輯表達式的一種，使用比較表達式作為查詢條件的一般表達形式是：表達式比較運算符表達式其中：表達式為：常量、變量和列表達式的任意有效組合。比較運算符包括：=（等于）、<（小于）、>（大于）、<>（不等于）、!>（不大于）、!<（不小于）、>=（大于等于）、<=（小于等于）、!=（不等于）。2/3/202320【例6.12】查詢年齡在23歲以下的學生。USExsglGOSELECT姓名,性別,年齡=DATEDIFF(YEAR,出生時間,GETDATE())FROMxsWHEREDATEDIFF(YEAR,出生時間,GETDATE())<=23GO2/3/2023212．邏輯表達式作查詢條件使用邏輯表達式作為查詢條件的一般表達形式是：表達式1AND|OR表達式2，或NOT表達式【例6.13】查詢年齡為23歲且性別為“女”的學生。USExsglGOSELECT姓名,性別,年齡=DATEDIFF(YEAR,出生時間,GETDATE())FROMxsWHEREDATEDIFF(YEAR,出生時間,GETDATE())=23AND性別='女'2/3/2023223．使用（NOT）BETWEEN…AND關鍵字其語法格式為：表達式[NOT]BETWEEN表達式lAND表達式2謂詞可以用來查找屬性值在（或不在）指定范圍內的元組，其中BETWEEN后是范圍的下限（即低值），AND后是范圍的上限（即高值）。使用BETWEEN限制查詢數(shù)據(jù)范圍時同時包括了邊界值，而使用NOTBETWEEN進行查詢時沒有包括邊界值?！纠?.14】查詢年齡在20到22之間的女學生的學號，姓名，年齡。USExsglGOSELECT姓名,性別,年齡=DATEDIFF(YEAR,出生時間,GETDATE())FROMxsWHEREDATEDIFF(YEAR,出生時間,GETDATE())BETWEEN20AND22AND性別='女'2/3/2023234．使用IN關鍵字同BETWEEN關鍵字一樣，IN的引入也是為了更方便地限制檢索數(shù)據(jù)的范圍，靈活使用IN關鍵字，可以用簡潔的語句實現(xiàn)結構復雜的查詢。語法格式為：表達式[NOT]IN（表達式1,表達式2[,…表達式n]）如果“表達式”的值是謂詞IN后面括號中列出的表達式1，表達式2,…表達式n的值之一，則條件為真。【例6.15】查詢選修了“a003”和“j003”的學生的學號。USExsglGOSELECTDISTINCT學號FROMcjWHERE課程號IN('a003','j003')GO2/3/2023245．使用LIKE關鍵字語法格式：表達式[NOT]LIKE<匹配串>其含義是查找指定的屬性列值與<匹配串>相匹配的元組。<匹配串>可以是一個完整的字符串，也可以含有通配符。SQLServer提供了以下4種通配符供用戶靈活實現(xiàn)復雜的查詢條件。％（百分號）：表示從0到n個任意字符。_（下劃線）：表示單個的任意字符。[]（封閉方括號）：表示方括號里列出的任意一個字符。[^]：任意一個沒有在方括號里列出的字符。需要注意的是，以上所有通配符都只有在LIKE子句中才有意義，否則通配符會被當作普通字符處理。2/3/202325【例6.16】查詢姓“張”學生的學號及姓名USExsglGOSELECT學號,姓名FROMxsWHERE姓名LIKE'張%'GO注意：通配符和字符串必須括在單引號中。要查找通配符本身時，需將它們用方括號括起來。例如：LIKE'[[]'表示要匹配“[”。2/3/2023266．涉及空值的查詢對于空值（NULL）要用IS進行連接，不能用“=”代替。【例6.17】查詢選修了課程卻沒有成績的學生的學號。USExsGOSELECT學號FROMcjWHERE成績ISNULLGO2/3/2023276．1．5使用ORDERBY子句對查詢的結果進行排序，通過使用ORDERBY子句實現(xiàn)。語法格式如下：ORDERBY表達式1[ASC︱DESC][,…n]]其中，表達式給出排序依據(jù)，即按照表達式的值升序（ASC）或降序（DESC）排列查詢結果。在默認的情況下，ORDERBY按升序進行排列，即默認使用的是ASC關鍵字。不能按ntext、text或image類型的列排序，因此ntext、text或image類型的列不允許出現(xiàn)在ORDERBY子句中。2/3/202328【例6.18】按年齡從小到大的順序顯示女學生的姓名，性別及出生時間。USExsglGOSELECT姓名,性別,出生時間FROMxsWHERE性別='女'ORDERBY出生時間DESCGO2/3/2023296．2SELECT語句的統(tǒng)計功能為了進一步方便用戶，增強檢索功能，SELECT語句中的統(tǒng)計功能可以對查詢結果集進行求和、求平均值、求最大最小值等操作。統(tǒng)計的方法是通過集合函數(shù)和GROUPBY子句、COMPUTE子句進行組合來實現(xiàn)。6．2．1使用集合函數(shù)匯總查詢是把存儲在數(shù)據(jù)庫中的數(shù)據(jù)作為一個整體，對查詢結果得到的數(shù)據(jù)集合進行匯總或求平均值等各種運算。SQLServer提供了一系列統(tǒng)計函數(shù)，用于實現(xiàn)匯總查詢。常用的統(tǒng)計函數(shù)見表6-2。2/3/2023302/3/202331【例6.19】查詢學生總人數(shù)USExsglGOSELECT學生總人數(shù)=COUNT(*)FROMxsGO查詢結果如圖6.17所示2/3/202332如果指定DISTINCT短語，則表示在計算時要取消指定列中的重復值。如果不指定DISTINCT短語或指定ALL短語（ALL為缺省值），則表示不取消重復值。【例6.20】查詢選修了“a003”課程的學生人數(shù)。USExsglGOSELECT選課人數(shù)=COUNT(DISTINCT學號)FROMcjWHERE課程號='a003'GO2/3/202333【例6.21】查詢選修了“a001”號課程的學生最高分數(shù)。USExsglGOSELECTa001課程最高分=MAX(成績)FROMcjWHERE課程號='a001'GO2/3/2023346．2．2使用GROUPBY子句其語法格式如下。GROUPBY列名[HAVING條件表達式]HAVING條件表達式選項是對生成的組進行篩選。2/3/202335【例6.22】在xs表中分專業(yè)統(tǒng)計出男生和女生的平均年齡及人數(shù)，結果按性別排序。USExsglGOSELECT專業(yè),性別,AVG(DATEDIFF(YEAR,出生時間,GETDATE()))AS平均年齡,COUNT(*)AS人數(shù)FROMxsGROUPBY專業(yè),性別ORDERBY性別GO2/3/202336【例6.23】查詢cj表中平均成績大于80分的學生的學號、平均分，并按分數(shù)由高到低排序。USExsglGOSELECT學號,AVG(成績)AS平均成績FROMcjGROUPBY學號HAVINGAVG(成績)>=80ORDERBYAVG(成績)DESCGO2/3/202337注意：WHERE子句是對表中的記錄進行篩選，而HAVING子句是對組內的記錄進行篩選，在HAVING子句中可以使用集合函數(shù)，并且其統(tǒng)計運算的集合是組內的所有列值，而WHERE子句中不能使用集合函數(shù)。2/3/2023386．2．3使用COMPUTE子句使用COMPUTEBY子句，它對BY后面給出的列進行分組顯示，并計算該列的分組小計。其語法格式如下：COMPUTE集合函數(shù)[BY分類表達式]注意：（1）COMPUTE或COMPUTEBY子句中的表達式，必須出現(xiàn)在選擇列表中，并且必須將其指定為與選擇列表中的某個表達式完全一樣，不能使用在選擇列表中指定的列的別名。（2）在COMPUTE或COMPUTEBY子句中，不能指定為ntext、text和image數(shù)據(jù)類型。（3）如果使用COMPUTEBY，則必須也使用ORDERBY子句。表達式必須與在ORDERBY后列出的子句相同或是其子集，并且必須按相同的序列。例如，如果ORDERBY子句是：ORDERBYa,b,c則COMPUTE子句可以是下面的任意一個（或全部），即：COMPUTEBYa,b,cCOMPUTEBYa,bCOMPUTEBYa（4）在SELECTINTO語句中不能使用COMPUTE。因此，任何由COMPUTE生成的計算結果不出現(xiàn)在用SELECTINTO語句創(chuàng)建的新表內。2/3/202339【例6.24】在cj表中求每門課程成績大于90分的人數(shù)，并顯示統(tǒng)計的明細。USExsglGOSELECT*FROMcjWHERE成績>=90ORDERBY課程號COMPUTECOUNT(課程號)BY課程號GO2/3/2023406．3SELECT語句中的多表連接在實際工作中，我們所查詢的內容往往是涉及多張表中的內容。連接查詢的目的是通過加載連接字段條件將多個表連接起來，以便從多個表中檢索用戶所需要的數(shù)據(jù)。在SQLServer中連接查詢類型分為交叉連接、內連接、外連接、自連接。連接查詢就是關系運算的連接運算，它是從多個數(shù)據(jù)源間（FORM）查詢滿足—定條件的記錄。2/3/2023416．3．1交叉連接交叉連接也叫非限制連接，它是將兩個表不加任何約束地組合起來。也就是將第一個表的所有行分別與第二個表的每行形成一條新的記錄，連接后該結果集的行數(shù)等于兩個表的行數(shù)積，列數(shù)等于兩個表的列數(shù)和。在數(shù)學上，就是兩個表的笛卡爾積，在實際應用中一般是沒有意義的，但在數(shù)據(jù)庫的數(shù)學模式上有重要的作用。其語法結構如下：SELECT列名列表FROM表名1CROSSJOIN表名2或者SELECT列名列表FROM表名1,表名22/3/2023426．3．2內連接內連接也叫自然連接，它是組合兩個表的常用方法。內連接就是只包含滿足連接條件的數(shù)據(jù)行，是將交叉連接結果集按照連接條件進行過濾的結果。連接條件通常采用“主鍵=外鍵”的形式。內連接有以下兩種語法格式。其語法結構如下：SELECT列名列表FROM表名1[INNER]JOIN表名2ON表名1.列名=表名2.列名或SELECT列名列表FROM表名1,表名2WHERE表名1.列名=表名2.列名2/3/2023436．3．3外連接在自然連接中，只有在兩個表中匹配的才能在結果集中出現(xiàn)。而在外連接中可以只限制一個表，而對另外一個表不加限制（即另外一個表中的所有行都出現(xiàn)在結果集中）。外連接分為左外連接、右外連接和全外連接。左外連接是對連接條件中左邊的表不加限制；右外連接是對右邊的表不加限制；全外連接對兩個表都不加限制，所有兩個表中的行都會包括在結果集中。2/3/2023441．左外連接SELECT列名列表FROM表名1ASALEFT[OUTER]JOIN表名2ASBONA.列名=B.列名2．右外連接其語法格式如下：SELECT列名列表FROM表名1ASARIGHT[OUTER]JOIN表名2ASBONA.列名=B.列名2/3/2023453．全外連接其語法格式如下：SELECT列名列表FROM表名1ASAFULL[OUTER]JOIN表名2ASBONA.列名=B.列名2/3/2023466．3．4自連接連接操作不僅可以在不同的表上進行，也可以在同一張表內進行自身連接，即將同一個表的不同行連接起來。自連接可以看作一張表的兩個副本之間的連接。表名在FROM子句中出現(xiàn)兩次，必須對表指定不同的別名，在SELECT子句中引用的列名也要使用表的別名進行限定，使之在邏輯上成為兩張表。2/3/2023476．3．5合并結果集合并查詢也稱聯(lián)合查詢是將兩個或兩個以上的查詢結果合并，形成一個具有綜合信息的查詢結果。使用UNION語句可以把兩個或兩個以上的查詢結果集合并為一個結果集。其語法格式如下：查詢語句UNION[ALL]查詢語句注意:（1）聯(lián)合查詢是將兩個表（結果集）順序連接。（2）UNION中的每一個查詢所涉及到的列必須具有相同的列數(shù)、相同位置的列的數(shù)據(jù)類型要相同。若長度不同，以最長的字段作為輸出字段的長度。（3）最后結果集中的列名來自第一個SELECT語句。（4）最后一個SELECT查詢可以帶ORDERBY子句，對整個UNION操作結果集起作用。且只能用第一個SELECT查詢中的字段作排序列。（5）系統(tǒng)自動刪除結果集中重復的記錄，除非使用ALL關鍵字。2/3/2023486．4子查詢在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為子查詢。子查詢總是寫在圓括號中，可以用在使用表達式的任何地方。上層的查詢塊稱為外層查詢或父查詢，下層查詢塊稱為內查詢或子查詢。SQL語言允許多層嵌套查詢。即一個子查詢中還可以嵌套其他子查詢。注意：子查詢的SELECT語句中不能使用ORDERBY子句，ORDERBY子句只能對最終查詢結果排序。2/3/2023496．4．1嵌套子查詢嵌套子查詢的執(zhí)行不依賴于外部嵌套。其一般的求解方法是由里向外處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件。1．比較測試中的子查詢比較測試中的子查詢是指父查詢與子查詢之間用比較運算符進行連接。但是用戶必須確切地知道子查詢返回的是一個單值，否則數(shù)據(jù)庫服務器將報錯。返回的單個值被外部查詢的比較操作（如：=、!=、<、<=、>、>=）使用，該值可以是子查詢中使用集合函數(shù)得到的值。2/3/202350【例6.33】求選修了哲學課程的學生的學號及姓名。USExsglGOSELECTxs.學號,姓名,專業(yè)FROMxs,cjWHERExs.學號=cj.學號ANDcj.課程號=(SELECT課程號FROMkcWHERE課程名='哲學')GO2/3/202351【例6.34】在xs表中查詢和“程明”在同一專業(yè)的所有男同學信息。USExsglGOSELECT*FROMxsWHERE性別='男'AND專業(yè)=(SELECT專業(yè)FROMxsWHERE姓名='程明')AND姓名<>'程明'GO2/3/2023522．集合成員測試中的子查詢集合成員測試中的子查詢是指父查詢與子查詢之間用IN或NOTIN進行連接，判斷某個屬性列值是否在子查詢的結果中，通常子查詢的結果是一個集合。IN表示屬于，即外部查詢中用于判斷的表達式的值與子查詢返回的值列表中的一個值相等；NOTIN表示不屬于?！纠?.35】求選修了學分為5分的課程的學生的學號及姓名。USExsglGOSELECTDISTINCTxs.學號,姓名,專業(yè)FROMxs,cjWHERExs.學號=cj.學號ANDcj.課程號IN(SELECT課程號FROMkcWHERE學分=5)2/3/