SQL復(fù)雜查詢語(yǔ)句總結(jié)

1、第第4章章 查詢與視圖查詢與視圖 4.1 SQLSQL語(yǔ)言簡(jiǎn)介語(yǔ)言簡(jiǎn)介 4.2 SELECT數(shù)據(jù)訪問(wèn)基本方法數(shù)據(jù)訪問(wèn)基本方法 4.3 條件檢索的條件檢索的SELECT語(yǔ)句語(yǔ)句4.4 從多張表檢索的從多張表檢索的SELECT語(yǔ)句語(yǔ)句4.5 UnionUnion操作和子查詢語(yǔ)句操作和子查詢語(yǔ)句 4.6 SQLSQL常用函數(shù)及其使用方法常用函數(shù)及其使用方法4.1 SQLSQL語(yǔ)言簡(jiǎn)介語(yǔ)言簡(jiǎn)介 1.SQL (Structured Query Language):結(jié)構(gòu)化查詢語(yǔ)言，結(jié)構(gòu)化查詢語(yǔ)言，是一種介于關(guān)系代數(shù)與關(guān)系運(yùn)算之間的語(yǔ)言，主要功能包括是一種介于關(guān)系代數(shù)與關(guān)系運(yùn)算之間的語(yǔ)言，主要功能包括查詢、

2、操縱、定義和控制等方面，是一個(gè)通用的、功能極強(qiáng)查詢、操縱、定義和控制等方面，是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言。的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言。2.Transact-SQL的組成的組成3.1）數(shù)據(jù)定義語(yǔ)言）數(shù)據(jù)定義語(yǔ)言(DDL Data Definition Language)：用來(lái)建立數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)對(duì)象。如用來(lái)建立數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)對(duì)象。如Create table、view等。等。4.2）數(shù)據(jù)操縱語(yǔ)言）數(shù)據(jù)操縱語(yǔ)言(DML Data Manipulation Language):用來(lái)操縱數(shù)據(jù)庫(kù)中的數(shù)據(jù)的命令。如用來(lái)操縱數(shù)據(jù)庫(kù)中的數(shù)據(jù)的命令。如select、insert、update、delete等。等。5

3、.3）數(shù)據(jù)控制語(yǔ)言）數(shù)據(jù)控制語(yǔ)言(DCL Data Control Language):用來(lái)用來(lái)控制數(shù)據(jù)庫(kù)組建的存取權(quán)限等。如控制數(shù)據(jù)庫(kù)組建的存取權(quán)限等。如Grant、Revoke等。等。6.4）流程控制語(yǔ)言）流程控制語(yǔ)言(FCL Flow Control Language):用來(lái)設(shè)用來(lái)設(shè)計(jì)應(yīng)用程序的語(yǔ)句。如計(jì)應(yīng)用程序的語(yǔ)句。如if、while、case等。等。7.5）其他語(yǔ)言要素）其他語(yǔ)言要素(ALE Additional language Element):包括變量、運(yùn)算符、函數(shù)和注解等。包括變量、運(yùn)算符、函數(shù)和注解等。4.2 SELECT數(shù)據(jù)查詢數(shù)據(jù)查詢 SELECT語(yǔ)句的基本格式如下：

4、語(yǔ)句的基本格式如下：SELECT select_listFROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC INTO new_table SELECT 子句子句SELECT ALL | DISTINCT TOP n PERCENT := * | table_name | view_name | table_alias .* | column_name | expression | AS

5、column_alias | column_alias = expression ,.n 4.2.1 表中列的使用方法表中列的使用方法 1. 選擇所有字段選擇所有字段SELECT * FROM 表名表名 如：如：use pubsselect * from authors(顯示顯示authors中的所有信息，全表查詢）中的所有信息，全表查詢） 2. 選擇部分字段選擇部分字段SELECT 列名列名1,列名列名2,列名列名n FROM 表名表名 如：如：use studentselect 學(xué)號(hào)學(xué)號(hào),姓名姓名,性別性別 from 學(xué)生基本情況學(xué)生基本情況(顯示學(xué)生基本情況中學(xué)號(hào)顯示學(xué)生基本情況中學(xué)號(hào),

6、姓名姓名,性別字段的信息）性別字段的信息）SELECT語(yǔ)句的使用方式語(yǔ)句的使用方式3. 為字段設(shè)置別名為字段設(shè)置別名SELECT 列名列名1 as 新名新名1,列名列名2 as 新名新名2,列名列名n as 新名新名n FROM 表名表名 (將選擇字段的標(biāo)題按新的名稱顯示）將選擇字段的標(biāo)題按新的名稱顯示）注意：新標(biāo)題的名稱可以有下列方式：注意：新標(biāo)題的名稱可以有下列方式：1)在列表達(dá)式后面給出列名在列表達(dá)式后面給出列名 select xh 學(xué)號(hào)學(xué)號(hào)2)用用“=”來(lái)連接列表達(dá)式來(lái)連接列表達(dá)式 select 學(xué)號(hào)學(xué)號(hào) = xh3)新標(biāo)題的名稱用單引號(hào)、雙引號(hào)括起來(lái)；新標(biāo)題的名稱用單引號(hào)、雙引號(hào)括起

7、來(lái)；4)用用AS關(guān)鍵字來(lái)連接列表達(dá)式和指定的列名關(guān)鍵字來(lái)連接列表達(dá)式和指定的列名 SELECT語(yǔ)句語(yǔ)句例如：查詢例如：查詢authors中編號(hào)、姓名、電話、地址的信息，可中編號(hào)、姓名、電話、地址的信息，可以采用以下方式：以采用以下方式：1.select au_id 編號(hào)編號(hào),au_lname 姓姓,au_fname 名名,phone 電電話話,address 地址地址 from authors2.select 編號(hào)編號(hào)=au_id ,姓姓=au_lname ,名名=au_fname ,電話電話=phone ,地址地址=address from authors3.select au_id 編號(hào)編

8、號(hào),au_lname 姓姓,au_fname 名名,phone 電話電話,address 地址地址 from authors4.select au_id 編號(hào)編號(hào),au_lname 姓姓,au_fname 名名,phone 電話電話,address 地址地址 from authors5.select au_id as 編號(hào)編號(hào),au_lname as 姓姓,au_fname as 名名,phone as 電話電話,address as 地址地址 from authorsSELECT語(yǔ)句語(yǔ)句4. 在選擇列表中使用表達(dá)式在選擇列表中使用表達(dá)式在查詢數(shù)據(jù)時(shí)，可以通過(guò)運(yùn)算操作來(lái)控制從在查詢數(shù)據(jù)時(shí)，可以

9、通過(guò)運(yùn)算操作來(lái)控制從一個(gè)表中的返回值。一個(gè)表中的返回值。例如：查詢每個(gè)學(xué)生的總成績(jī)、平均成績(jī)。例如：查詢每個(gè)學(xué)生的總成績(jī)、平均成績(jī)。select 學(xué)號(hào)學(xué)號(hào),姓名姓名,數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī)+語(yǔ)文成績(jī)語(yǔ)文成績(jī)+英語(yǔ)英語(yǔ)成績(jī)成績(jī) as 總成績(jī)總成績(jī),(數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī)+語(yǔ)文成績(jī)語(yǔ)文成績(jī)+英語(yǔ)成英語(yǔ)成績(jī)績(jī))/3 as 平均成績(jī)平均成績(jī) from 學(xué)生基本情況學(xué)生基本情況SELECT語(yǔ)句語(yǔ)句4. 消除字段數(shù)據(jù)的重復(fù)值消除字段數(shù)據(jù)的重復(fù)值在查詢數(shù)據(jù)時(shí)，可能會(huì)有許多重復(fù)的數(shù)據(jù)。在查詢數(shù)據(jù)時(shí)，可能會(huì)有許多重復(fù)的數(shù)據(jù)。SQL 提供的提供的Distinct 關(guān)鍵字，可以從關(guān)鍵字，可以從select 語(yǔ)句的結(jié)果集中消除

10、重復(fù)的數(shù)據(jù)。語(yǔ)句的結(jié)果集中消除重復(fù)的數(shù)據(jù)。例如：例如：1)查詢學(xué)生來(lái)至哪些院系的信息。查詢學(xué)生來(lái)至哪些院系的信息。select distinct 院系名稱院系名稱 from 學(xué)生基本情學(xué)生基本情況況2)查詢有哪些專業(yè)的學(xué)生。查詢有哪些專業(yè)的學(xué)生。select distinct 所學(xué)專業(yè)所學(xué)專業(yè) from 學(xué)生基本情學(xué)生基本情況況SELECT語(yǔ)句語(yǔ)句6. 限制記錄的行數(shù)限制記錄的行數(shù)在限制查詢記錄的行數(shù)時(shí)，可以使用下列方式：在限制查詢記錄的行數(shù)時(shí)，可以使用下列方式：1)使用使用top n : 返回前返回前n條記錄；條記錄；2)使用使用top n percent :返回前返回前n%條記錄條記錄;3

11、)使用使用set rowcount n:返回前返回前n條記錄。條記錄。n=0關(guān)閉關(guān)閉例如：例如：1）顯示前）顯示前5條記錄條記錄select top 5 * from 學(xué)生基本情況學(xué)生基本情況2)顯示顯示20%學(xué)生的信息學(xué)生的信息select top 20 percent * from 學(xué)生基本情況學(xué)生基本情況3）對(duì)所有）對(duì)所有select 語(yǔ)句，均顯示語(yǔ)句，均顯示5條記錄。條記錄。set rowcount 5select * from 學(xué)生基本情況學(xué)生基本情況4.2 條件子句的使用方法條件子句的使用方法1.條件子句條件子句最常用的條件子句是最常用的條件子句是where 和和having，用它

12、們來(lái)，用它們來(lái)指定一系列條件，執(zhí)行操作時(shí)只返回滿足條件的記錄。指定一系列條件，執(zhí)行操作時(shí)只返回滿足條件的記錄。Having 通常與通常與Group by一起使用，用來(lái)說(shuō)明返回一起使用，用來(lái)說(shuō)明返回分組的條件。分組的條件。例如：例如：1）顯示男生的相關(guān)信息）顯示男生的相關(guān)信息Select * from 學(xué)生基本情況學(xué)生基本情況 where 性別性別=男男2）顯示男生人數(shù)超過(guò)）顯示男生人數(shù)超過(guò)10人的院系信息人的院系信息select 院系名稱院系名稱,count(*) as 男生人數(shù)男生人數(shù) from 學(xué)學(xué)生基本情況生基本情況 where 性別性別=男男 group by 院系名稱院系名稱 hav

13、ing count(*)104.2 SELECT語(yǔ)句的查詢條件語(yǔ)句的查詢條件2算術(shù)表達(dá)式、比較運(yùn)算符算術(shù)表達(dá)式、比較運(yùn)算符算術(shù)運(yùn)算符有：算術(shù)運(yùn)算符有：+ +、- -、* *、/ /、% %使用算術(shù)表達(dá)式的一般形式為：使用算術(shù)表達(dá)式的一般形式為：expression operator expression 比較運(yùn)算符：比較運(yùn)算符：是最為常見(jiàn)的一種條件限制方是最為常見(jiàn)的一種條件限制方式，用于測(cè)試兩個(gè)表達(dá)式是否相同，返回值為式，用于測(cè)試兩個(gè)表達(dá)式是否相同，返回值為True 或或False。WHERE子句中允許出現(xiàn)的比較運(yùn)算符有：子句中允許出現(xiàn)的比較運(yùn)算符有：=（等于）、（等于）、（大于）、（大于）、

14、=（對(duì)于等于）、（對(duì)于等于）、（小于）、（小于）、=（小于等于）、（小于等于）、（不等于）（不等于） 、!=（不等于）（不等于） 、!（不大于）、（不大于）、!= 80 and 數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī)=904.2 SELECT語(yǔ)句的查詢條件語(yǔ)句的查詢條件4.IN 列表搜索條件列表搜索條件IN 列表搜索條件用于返回與給定的列表中任意一列表搜索條件用于返回與給定的列表中任意一個(gè)值相匹配的記錄。格式為：個(gè)值相匹配的記錄。格式為：條件字段條件字段 NOT IN (列表選項(xiàng)列表選項(xiàng)) 例如：查詢數(shù)學(xué)成績(jī)?yōu)槔纾翰樵償?shù)學(xué)成績(jī)?yōu)?0、80、90的信息。的信息。select * from 學(xué)生基本情況學(xué)生基本情況

15、where 數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī) in (70,80,90) In列表?xiàng)l件是列表?xiàng)l件是OR的簡(jiǎn)化形式，上面語(yǔ)句等價(jià)于：的簡(jiǎn)化形式，上面語(yǔ)句等價(jià)于：select * from 學(xué)生基本情況學(xué)生基本情況 where 數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī) = 70 OR數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī) = 80 OR 數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī) = 904.2 SELECT語(yǔ)句的查詢條件語(yǔ)句的查詢條件6LIKE匹配模式匹配模式LIKE匹配模式是確定條件字符串是否與指定的模式匹匹配模式是確定條件字符串是否與指定的模式匹配。使用格式：配。使用格式：條件字段條件字段 NOT LIKE 匹配模式匹配模式SQL中的有效模式包括：中的有效模式包括：%：可匹配任

16、意類型和長(zhǎng)度的字符串：可匹配任意類型和長(zhǎng)度的字符串。Like Like 李李%_（下劃線）：可匹配任何單個(gè)字符（下劃線）：可匹配任何單個(gè)字符。Like 71005_Like 71005_ ：指定范圍或集合中的任何單個(gè)字符。：指定范圍或集合中的任何單個(gè)字符。Like a-dLike a-d：不屬于指定范圍或集合的任何單個(gè)字符：不屬于指定范圍或集合的任何單個(gè)字符例如：例如：1）查詢所有姓）查詢所有姓“張張”的學(xué)生信息的學(xué)生信息select * from 學(xué)生基本情況學(xué)生基本情況 where 姓名姓名 like 張張%4.2 SELECT語(yǔ)句的查詢條件語(yǔ)句的查詢條件6NULL搜索條件搜索條件空值比較

17、的關(guān)鍵字是空值比較的關(guān)鍵字是 IS NULL或或 IS NOT NULL。其中。其中NULL表示字段的數(shù)據(jù)未知或不確表示字段的數(shù)據(jù)未知或不確定。格式為：定。格式為：表達(dá)式表達(dá)式 IS NULL 或或 表達(dá)式表達(dá)式IS NOT NULL例如：查詢?nèi)鄙贁?shù)學(xué)成績(jī)的學(xué)生信息。例如：查詢?nèi)鄙贁?shù)學(xué)成績(jī)的學(xué)生信息。select * from 學(xué)生基本情況學(xué)生基本情況 where 數(shù)學(xué)成數(shù)學(xué)成績(jī)績(jī) is null查詢示例表查詢示例表練習(xí)討論練習(xí)討論1.按按10%的比例顯示本專業(yè)的男生信息；的比例顯示本專業(yè)的男生信息；2.查詢查詢“管理學(xué)院管理學(xué)院”專業(yè)分布情況；專業(yè)分布情況；3.查詢本專業(yè)學(xué)生來(lái)自哪些省份；查

18、詢本專業(yè)學(xué)生來(lái)自哪些省份；4.查詢英語(yǔ)不及格學(xué)生的信息，顯示學(xué)號(hào)、姓名、專業(yè)、英語(yǔ)查詢英語(yǔ)不及格學(xué)生的信息，顯示學(xué)號(hào)、姓名、專業(yè)、英語(yǔ)原成績(jī)、英語(yǔ)原成績(jī)、英語(yǔ)+10、英語(yǔ)、英語(yǔ)*1.1等字段信息；等字段信息；5.顯示管理學(xué)院學(xué)生中，不姓顯示管理學(xué)院學(xué)生中，不姓“劉劉”和和“張張”的學(xué)生信息；的學(xué)生信息；6.查詢查詢“數(shù)學(xué)數(shù)學(xué)”在在65-75之間，而之間，而“英語(yǔ)英語(yǔ)”不在不在70-90之間的之間的學(xué)生信息；學(xué)生信息；7.查詢查詢“管理學(xué)院、能源學(xué)院、機(jī)械學(xué)院、人文學(xué)院管理學(xué)院、能源學(xué)院、機(jī)械學(xué)院、人文學(xué)院”學(xué)號(hào)在學(xué)號(hào)在01-07之間的學(xué)生信息。之間的學(xué)生信息。8.將學(xué)生的將學(xué)生的“學(xué)號(hào)、姓名、

19、性別、身份證號(hào)學(xué)號(hào)、姓名、性別、身份證號(hào)” 作為一列、作為一列、“院系名稱、專業(yè)院系名稱、專業(yè)”作為一列，各數(shù)據(jù)之間用作為一列，各數(shù)據(jù)之間用“，”分隔；列分隔；列名分別為名分別為“基本信息基本信息”、“隸屬關(guān)系隸屬關(guān)系”進(jìn)行顯示；進(jìn)行顯示；9.查詢總成績(jī)查詢總成績(jī)、=、(select avg(數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī)) FROM 學(xué)生基本情況學(xué)生基本情況 )思考：思考：1）如何查詢本專業(yè)大學(xué)英語(yǔ)成績(jī)）如何查詢本專業(yè)大學(xué)英語(yǔ)成績(jī)=60且低且低于平均分的學(xué)生信息？于平均分的學(xué)生信息？2)統(tǒng)計(jì)管理學(xué)院各專業(yè)高等數(shù)學(xué)低于平均分的人數(shù)。統(tǒng)計(jì)管理學(xué)院各專業(yè)高等數(shù)學(xué)低于平均分的人數(shù)。3）查詢大學(xué)英語(yǔ)成績(jī)最高分的學(xué)生

20、信息。）查詢大學(xué)英語(yǔ)成績(jī)最高分的學(xué)生信息。Exists子查詢子查詢3）使用）使用Exists的子查詢的子查詢使用使用Exists（或（或Not Exists）引入子查詢時(shí)，就相）引入子查詢時(shí)，就相當(dāng)于進(jìn)行一次存在測(cè)試。當(dāng)于進(jìn)行一次存在測(cè)試。外部查詢的外部查詢的Where子句測(cè)試子句測(cè)試子查詢返回的行是否存在。子查詢返回的行是否存在。子查詢實(shí)際上不產(chǎn)生任何子查詢實(shí)際上不產(chǎn)生任何數(shù)據(jù)，它只返回?cái)?shù)據(jù)，它只返回True或或False。例如：查詢成績(jī)表中女生的學(xué)習(xí)成績(jī)。例如：查詢成績(jī)表中女生的學(xué)習(xí)成績(jī)。SELECT * FROM 成績(jī)表成績(jī)表 Where exists(select * FROM 學(xué)生基

21、本情況學(xué)生基本情況 where 學(xué)號(hào)學(xué)號(hào)=成績(jī)表成績(jī)表.學(xué)號(hào)學(xué)號(hào) and 性別性別=女女)思考：思考：1）如何查詢管理學(xué)院的學(xué)生成績(jī)？）如何查詢管理學(xué)院的學(xué)生成績(jī)？2）查詢本專業(yè)高等數(shù)學(xué)不及格的學(xué)生信息？）查詢本專業(yè)高等數(shù)學(xué)不及格的學(xué)生信息？練習(xí)討論練習(xí)討論數(shù)據(jù)表名：數(shù)據(jù)表名：JBQK數(shù)據(jù)表名：數(shù)據(jù)表名：CJB練習(xí)討論練習(xí)討論n根據(jù)根據(jù)JBQK表、表、CJB表完成下列查詢：表完成下列查詢：n1.查詢高等數(shù)學(xué)成績(jī)高于平均成績(jī)的學(xué)生信查詢高等數(shù)學(xué)成績(jī)高于平均成績(jī)的學(xué)生信息，顯示學(xué)號(hào)、姓名、專業(yè)、高等數(shù)學(xué)等；息，顯示學(xué)號(hào)、姓名、專業(yè)、高等數(shù)學(xué)等；n2.查詢信息管理專業(yè)男生的高等數(shù)學(xué)成績(jī)、查詢信息管理

22、專業(yè)男生的高等數(shù)學(xué)成績(jī)、電子商務(wù)專業(yè)女生的高等數(shù)學(xué)成績(jī)、能源學(xué)院電子商務(wù)專業(yè)女生的高等數(shù)學(xué)成績(jī)、能源學(xué)院所有學(xué)生的數(shù)學(xué)成績(jī)，顯示顯示學(xué)號(hào)、姓名、所有學(xué)生的數(shù)學(xué)成績(jī)，顯示顯示學(xué)號(hào)、姓名、性別、院系、專業(yè)、高等數(shù)學(xué)等，按院系、專性別、院系、專業(yè)、高等數(shù)學(xué)等，按院系、專業(yè)排序；業(yè)排序；n3.查詢與查詢與“劉文東劉文東”同專業(yè)的學(xué)生信息，顯同專業(yè)的學(xué)生信息，顯示顯示學(xué)號(hào)、姓名、專業(yè)、各門課程成績(jī)。示顯示學(xué)號(hào)、姓名、專業(yè)、各門課程成績(jī)。基于查詢的數(shù)據(jù)表基于查詢的數(shù)據(jù)表1 基于查詢生成新的數(shù)據(jù)表基于查詢生成新的數(shù)據(jù)表如果需要將查詢結(jié)果保存下來(lái)，使用如果需要將查詢結(jié)果保存下來(lái)，使用INTO子句可以生成一個(gè)新

23、表并將結(jié)果保存在這個(gè)新子句可以生成一個(gè)新表并將結(jié)果保存在這個(gè)新的數(shù)據(jù)表中。的數(shù)據(jù)表中。命令基本格式：命令基本格式：Select 選擇字段表選擇字段表 Into 新的表名新的表名 FROM 已有的表已有的表 where 條件條件例如：例如：1）將）將“學(xué)生基本情況學(xué)生基本情況”表復(fù)制成表復(fù)制成jbqkSelect * Into jbqk from 學(xué)生基本情況學(xué)生基本情況2)統(tǒng)計(jì)各個(gè)院系英語(yǔ)成績(jī)的平均分、最高分、最低分、考試統(tǒng)計(jì)各個(gè)院系英語(yǔ)成績(jī)的平均分、最高分、最低分、考試人數(shù)，結(jié)果放在人數(shù)，結(jié)果放在Eng_tj表。表。SELECT 學(xué)生基本情況學(xué)生基本情況.院系名稱院系名稱, AVG(成績(jī)表成

24、績(jī)表.大學(xué)英語(yǔ)大學(xué)英語(yǔ)) AS 英語(yǔ)平均英語(yǔ)平均, MAX(成績(jī)表成績(jī)表.大學(xué)英語(yǔ)大學(xué)英語(yǔ)) AS 英語(yǔ)最高英語(yǔ)最高, MIN(成成績(jī)表績(jī)表.大學(xué)英語(yǔ)大學(xué)英語(yǔ)) AS 英語(yǔ)最低英語(yǔ)最低, COUNT(*) AS 考試人數(shù)考試人數(shù)INTO Eng_tjFROM 學(xué)生基本情況學(xué)生基本情況 INNER JOIN 成績(jī)表成績(jī)表 ON 學(xué)生基本情況學(xué)生基本情況.學(xué)號(hào)學(xué)號(hào) = 成績(jī)表成績(jī)表.學(xué)號(hào)學(xué)號(hào)GROUP BY 學(xué)生基本情況學(xué)生基本情況.院系名稱院系名稱思考：思考：1）將管理學(xué)院學(xué)習(xí)成績(jī)前）將管理學(xué)院學(xué)習(xí)成績(jī)前10名學(xué)生的信息保存到數(shù)名學(xué)生的信息保存到數(shù)據(jù)表?yè)?jù)表gl_10中，只保存學(xué)號(hào)、姓名、專業(yè)、各

25、門課程成績(jī)。中，只保存學(xué)號(hào)、姓名、專業(yè)、各門課程成績(jī)。2）如何將本專業(yè)不及格學(xué)生的信息保存在數(shù)據(jù)表）如何將本專業(yè)不及格學(xué)生的信息保存在數(shù)據(jù)表bjg_xs? ，保存學(xué)號(hào)、姓名、專業(yè)、各門課程成績(jī)。保存學(xué)號(hào)、姓名、專業(yè)、各門課程成績(jī)。3）將）將jbqk表的結(jié)構(gòu)復(fù)制到表的結(jié)構(gòu)復(fù)制到j(luò)b_jg中。中。將查詢結(jié)果插入數(shù)據(jù)表將查詢結(jié)果插入數(shù)據(jù)表2 向已有表插入數(shù)據(jù)向已有表插入數(shù)據(jù)用用Insert IntoSelect語(yǔ)句，可以將一個(gè)子語(yǔ)句，可以將一個(gè)子查詢的結(jié)果添加到數(shù)據(jù)表中。查詢的結(jié)果添加到數(shù)據(jù)表中。命令格式：命令格式：Insert Into 數(shù)據(jù)表數(shù)據(jù)表 Select 查詢查詢例如：創(chuàng)建臨時(shí)數(shù)據(jù)表例如

26、：創(chuàng)建臨時(shí)數(shù)據(jù)表stu_1，存放從基本，存放從基本情況表、成績(jī)表中查詢出來(lái)的學(xué)號(hào)、姓名、性情況表、成績(jī)表中查詢出來(lái)的學(xué)號(hào)、姓名、性別、年齡、總成績(jī)、平均成績(jī)等信息。別、年齡、總成績(jī)、平均成績(jī)等信息。create table #stu_1(學(xué)號(hào)學(xué)號(hào) char(8),姓名姓名 nchar(4),性別性別 nchar(1),年齡年齡 tinyint,總成績(jī)總成績(jī) int,平均成績(jī)平均成績(jī) decimal(5,2)goinsert into #stu_1 select jbqk.學(xué)號(hào)學(xué)號(hào),jbqk.姓名姓名,jbqk.性別性別,year(getdate()-year(出生日期出生日期),cjb.高等數(shù)

27、學(xué)高等數(shù)學(xué)+cjb.大學(xué)英語(yǔ)大學(xué)英語(yǔ)+cjb.計(jì)算機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)+cjb.管理學(xué)管理學(xué),(cjb.高等數(shù)學(xué)高等數(shù)學(xué)+cjb.大學(xué)英語(yǔ)大學(xué)英語(yǔ)+cjb.計(jì)算機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)+cjb.管理學(xué)管理學(xué))/4from 學(xué)生基本情況學(xué)生基本情況 jbqk inner join 成績(jī)表成績(jī)表 cjb on jbqk.學(xué)號(hào)學(xué)號(hào)=cjb.學(xué)號(hào)學(xué)號(hào)goselect * from #stu_1思考：如何將信息管理、電子商務(wù)專業(yè)學(xué)習(xí)成績(jī)前思考：如何將信息管理、電子商務(wù)專業(yè)學(xué)習(xí)成績(jī)前5名的學(xué)名的學(xué)生信息放到與基本情況表結(jié)構(gòu)類似的數(shù)據(jù)表生信息放到與基本情況表結(jié)構(gòu)類似的數(shù)據(jù)表inf_ec中？中？子查詢修改記錄子查詢修改記

28、錄3 用子查詢修改記錄用子查詢修改記錄Update 語(yǔ)句中，語(yǔ)句中，Set子句、子句、Where 子句均可以使用子查詢。子句均可以使用子查詢。例如：在基本情況表中添加字段例如：在基本情況表中添加字段“第第1學(xué)期總分學(xué)期總分”、“第第1學(xué)期平均學(xué)期平均”，并修改，并修改這些字段的值。這些字段的值。alter table 學(xué)生基本情況學(xué)生基本情況 add 第第1學(xué)期總分學(xué)期總分 smallint,第第1學(xué)期平均學(xué)期平均 decimal(5,2)goupdate 學(xué)生基本情況學(xué)生基本情況 set 第第1學(xué)期總分學(xué)期總分=(select cjb.高等數(shù)學(xué)高等數(shù)學(xué)+cjb.大學(xué)英語(yǔ)大學(xué)英語(yǔ)+cjb.計(jì)算

29、機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)+cjb.管理學(xué)管理學(xué) from 成績(jī)表成績(jī)表 cjb where 學(xué)生基本情況學(xué)生基本情況.學(xué)號(hào)學(xué)號(hào)=cjb.學(xué)號(hào)學(xué)號(hào)), 第第1學(xué)期平均學(xué)期平均=(select (cjb.高等數(shù)學(xué)高等數(shù)學(xué)+cjb.大學(xué)英語(yǔ)大學(xué)英語(yǔ)+cjb.計(jì)算機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)+cjb.管理學(xué)管理學(xué))/4 from 成績(jī)表成績(jī)表 cjb where 學(xué)生基本情況學(xué)生基本情況.學(xué)號(hào)學(xué)號(hào)=cjb.學(xué)號(hào)學(xué)號(hào))goselect * from 學(xué)生基本情況學(xué)生基本情況思考：思考：1）對(duì)本專業(yè)高等數(shù)學(xué)不及格的學(xué)生，將數(shù)學(xué)成績(jī)提高）對(duì)本專業(yè)高等數(shù)學(xué)不及格的學(xué)生，將數(shù)學(xué)成績(jī)提高15%；2）將不是本專業(yè)學(xué)生的各門課程成績(jī)清空

30、；）將不是本專業(yè)學(xué)生的各門課程成績(jī)清空；子查詢刪除記錄子查詢刪除記錄4 用子查詢刪除記錄用子查詢刪除記錄Delete 命令的命令的where 子句可以使用子查詢來(lái)從數(shù)據(jù)表中刪子句可以使用子查詢來(lái)從數(shù)據(jù)表中刪除相關(guān)記錄。除相關(guān)記錄。例如：從基本情況表、成績(jī)表中刪除例如：從基本情況表、成績(jī)表中刪除“男生男生”的相關(guān)記錄。的相關(guān)記錄。delete from 成績(jī)表成績(jī)表 where exists(select * from 學(xué)生基學(xué)生基本情況本情況 jbqk where 成績(jī)表成績(jī)表.學(xué)號(hào)學(xué)號(hào)=jbqk.學(xué)號(hào)學(xué)號(hào) and jbqk.性別性別=男男)godelete from 學(xué)生基本情況學(xué)生基本情況

31、 where 性別性別=男男g(shù)oselect * from 學(xué)生基本情況學(xué)生基本情況select * from 成績(jī)表成績(jī)表思考：將本專業(yè)學(xué)生的成績(jī)備份到思考：將本專業(yè)學(xué)生的成績(jī)備份到dele_bak，并從，并從cjb中刪中刪除；同時(shí)在除；同時(shí)在jbqk表中添加字段表中添加字段Dele_flag 、dele_table ，并，并給給dele_flag賦值賦值1、給、給dele_table賦值備份表的名稱。賦值備份表的名稱。SQL查詢的增強(qiáng)功能查詢的增強(qiáng)功能1.COMPUTE子句子句2.使用使用COMPUTE和和COMPUTE BY子句既能瀏覽詳子句既能瀏覽詳細(xì)數(shù)據(jù)又可看到統(tǒng)計(jì)的結(jié)果。細(xì)數(shù)據(jù)又可

32、看到統(tǒng)計(jì)的結(jié)果。3.主要功能：主要功能：4.1）生成合計(jì)作為附加的匯總放在結(jié)果集的最后。）生成合計(jì)作為附加的匯總放在結(jié)果集的最后。5.2）當(dāng)與）當(dāng)與BY一起使用時(shí)，則在結(jié)果集生成控制中斷一起使用時(shí)，則在結(jié)果集生成控制中斷與小計(jì)。與小計(jì)。6.3）同一查詢內(nèi)可同時(shí)用）同一查詢內(nèi)可同時(shí)用COMPUTE和和COMPUTE BY子句。子句。7.4）如果使用）如果使用COMPUTE BY，則必須使用，則必須使用ORDER BY。COMPUTE子句子句其語(yǔ)法形式為：其語(yǔ)法形式為：COMPUTE 聚合函數(shù)聚合函數(shù)(column_name),n BY column_name ,n 注意：注意：nCOMPUTE

33、BY 子句不能與子句不能與SELECT INTO子句一起使用。子句一起使用。nCOMPUTE子句中的列必須出現(xiàn)在子句中的列必須出現(xiàn)在SELECT子句的列表中。子句的列表中。nCOMPUTE BY表示按指定的列進(jìn)行明細(xì)匯總，使用表示按指定的列進(jìn)行明細(xì)匯總，使用BY關(guān)鍵字時(shí)必須同時(shí)使用關(guān)鍵字時(shí)必須同時(shí)使用ORDER BY子句，并且子句，并且COMPUTE BY后出現(xiàn)的列必須具有與后出現(xiàn)的列必須具有與ORDER BY后出現(xiàn)的后出現(xiàn)的列相同的順序，且不能跳過(guò)其中的列。列相同的順序，且不能跳過(guò)其中的列。例如：如果例如：如果ORDER BY子句按照如下順序指定排序列：子句按照如下順序指定排序列：ORDER

34、 BY a,b,c則則COMPUTE BY后的列表只能是下面任一種形式：后的列表只能是下面任一種形式：BY a,b,cBY a,bBY aCOMPUTE子句子句應(yīng)用舉例：應(yīng)用舉例：1）對(duì)管理學(xué)院學(xué)生的學(xué)號(hào)、姓名、高等數(shù)學(xué)、）對(duì)管理學(xué)院學(xué)生的學(xué)號(hào)、姓名、高等數(shù)學(xué)、大學(xué)英語(yǔ)、數(shù)據(jù)庫(kù)列出明細(xì)，并統(tǒng)計(jì)高等數(shù)學(xué)大學(xué)英語(yǔ)、數(shù)據(jù)庫(kù)列出明細(xì)，并統(tǒng)計(jì)高等數(shù)學(xué)平均分、最高分及人數(shù)；平均分、最高分及人數(shù)；2）對(duì)管理學(xué)院學(xué)生按專業(yè)列出學(xué)號(hào)、姓名、）對(duì)管理學(xué)院學(xué)生按專業(yè)列出學(xué)號(hào)、姓名、專業(yè)、高等數(shù)學(xué)的明細(xì)，并統(tǒng)計(jì)高等數(shù)學(xué)的平專業(yè)、高等數(shù)學(xué)的明細(xì)，并統(tǒng)計(jì)高等數(shù)學(xué)的平均、最高、最低成績(jī)；均、最高、最低成績(jī)；3）按專業(yè)列出高

35、等數(shù)學(xué)的明細(xì)，并統(tǒng)計(jì)各專）按專業(yè)列出高等數(shù)學(xué)的明細(xì)，并統(tǒng)計(jì)各專業(yè)的平均、最高、最低成績(jī)；然后計(jì)算全校的業(yè)的平均、最高、最低成績(jī)；然后計(jì)算全校的平均、最高、最低成績(jī)。平均、最高、最低成績(jī)。WITH ROLLUP在在Group By子句后使用子句后使用with Rollup，將對(duì)，將對(duì)Group By指定的各列產(chǎn)生匯總行。指定的各列產(chǎn)生匯總行。例如：例如：1）統(tǒng)計(jì)各專業(yè)人數(shù)及總?cè)藬?shù)。）統(tǒng)計(jì)各專業(yè)人數(shù)及總?cè)藬?shù)。2）按專業(yè)統(tǒng)計(jì)男、女生人數(shù)，并統(tǒng)計(jì)專業(yè)）按專業(yè)統(tǒng)計(jì)男、女生人數(shù)，并統(tǒng)計(jì)專業(yè)總?cè)藬?shù)及學(xué)生的總?cè)藬?shù)。總?cè)藬?shù)及學(xué)生的總?cè)藬?shù)。3）按學(xué)院、專業(yè)統(tǒng)計(jì)數(shù)據(jù)庫(kù)的平均、最高）按學(xué)院、專業(yè)統(tǒng)計(jì)數(shù)據(jù)庫(kù)的平均、最高

36、分，同時(shí)統(tǒng)計(jì)各個(gè)學(xué)院以及全校的平均、最高分，同時(shí)統(tǒng)計(jì)各個(gè)學(xué)院以及全校的平均、最高成績(jī)。成績(jī)。WITH Cube在在Group By子句后使用子句后使用with Cube，將對(duì)，將對(duì)Group By指定的各列的所有可能組合均產(chǎn)生指定的各列的所有可能組合均產(chǎn)生匯總行。匯總行。例如：按專業(yè)統(tǒng)計(jì)男、女生人數(shù)，并統(tǒng)計(jì)專例如：按專業(yè)統(tǒng)計(jì)男、女生人數(shù)，并統(tǒng)計(jì)專業(yè)總?cè)藬?shù)及男、女生總?cè)藬?shù)、全部學(xué)生的總?cè)藰I(yè)總?cè)藬?shù)及男、女生總?cè)藬?shù)、全部學(xué)生的總?cè)藬?shù)。數(shù)。Select 所學(xué)專業(yè)所學(xué)專業(yè),性別性別,count(*) as 人數(shù)人數(shù)From jbqkGroup by 所學(xué)專業(yè)所學(xué)專業(yè),性別性別 with cube常用字符

37、串函數(shù)常用字符串函數(shù)1.ASCII(C):返回字符串最左端字符的返回字符串最左端字符的ASCII值；值；例如：顯示字符例如：顯示字符A、a、0的的ASCII值值select ascii(A) as A,ascii(a) as a,ascii(0) as zero2.CHAR(N):返回返回n(0-255)對(duì)應(yīng)的字符；對(duì)應(yīng)的字符；例如：顯示十進(jìn)制數(shù)例如：顯示十進(jìn)制數(shù)65、97、48、32對(duì)應(yīng)的字符對(duì)應(yīng)的字符select char(65) ,char(97),char(48),char(32)3.STR(FLOAT,LEN,DECIMAL):將數(shù)轉(zhuǎn)換成字將數(shù)轉(zhuǎn)換成字符串；符串；FLOAT：表示要轉(zhuǎn)

38、換的數(shù)值；：表示要轉(zhuǎn)換的數(shù)值；LEN:轉(zhuǎn)換后的字符串的長(zhǎng)度；轉(zhuǎn)換后的字符串的長(zhǎng)度；DECIMAL:轉(zhuǎn)換后的字符串中包含的小數(shù)位數(shù)。轉(zhuǎn)換后的字符串中包含的小數(shù)位數(shù)。常用字符串函數(shù)常用字符串函數(shù)說(shuō)明：說(shuō)明：1）若沒(méi)有給出長(zhǎng)度，則對(duì)整數(shù)部分按默認(rèn)長(zhǎng)度）若沒(méi)有給出長(zhǎng)度，則對(duì)整數(shù)部分按默認(rèn)長(zhǎng)度10位進(jìn)行轉(zhuǎn)換；位進(jìn)行轉(zhuǎn)換；2）若給出的長(zhǎng)度太短，則先滿足整數(shù)部分，然后）若給出的長(zhǎng)度太短，則先滿足整數(shù)部分，然后是小數(shù)部分；是小數(shù)部分；3）若給出的長(zhǎng)度不能滿足整數(shù)部分的長(zhǎng)度，則顯）若給出的長(zhǎng)度不能滿足整數(shù)部分的長(zhǎng)度，則顯示示n個(gè)個(gè)*。例如：例如：select str(123.45), str(123.45,6,

39、2), str(123.45,5,2), str(123.45,3,2), str(123.45,2,2)思考：長(zhǎng)度不夠時(shí)，是否進(jìn)行四舍五入？思考：長(zhǎng)度不夠時(shí)，是否進(jìn)行四舍五入？常用字符串函數(shù)常用字符串函數(shù)4.SUBSTRING(C,START,LENGTH):截取子串；截取子串；其中：其中：C待截取的字符串；待截取的字符串； START截取字符的起始位置；截取字符的起始位置； LENGTH截取字符的長(zhǎng)度。截取字符的長(zhǎng)度。例如：例如：SELECT SUBSTRING(12345ABCDEF,1,2), SUBSTRING(12345ABCDEF,5,5), SUBSTRING(12345ABC

40、DEF,6,5), SUBSTRING(12345ABCDEF,10,5), SUBSTRING(12345ABCDEF,12,2)思考：思考：1）位置、長(zhǎng)度參數(shù)是否可以省略？）位置、長(zhǎng)度參數(shù)是否可以省略？2）顯示基本情況表中，學(xué)號(hào)）顯示基本情況表中，學(xué)號(hào)01開(kāi)頭的學(xué)生信息開(kāi)頭的學(xué)生信息3）根據(jù)身份證號(hào)，顯示）根據(jù)身份證號(hào)，顯示1988年出生的學(xué)生信息年出生的學(xué)生信息常用字符串函數(shù)常用字符串函數(shù)4.LEFT(C,LEN) :從左邊開(kāi)始截取給定長(zhǎng)度從左邊開(kāi)始截取給定長(zhǎng)度字符串；字符串；6.RIGHT(C,LEN) :從右邊開(kāi)始截取給定長(zhǎng)從右邊開(kāi)始截取給定長(zhǎng)度字符串；度字符串；7.LEN(C) :

41、返回字符串的字符個(gè)數(shù)（不包括返回字符串的字符個(gè)數(shù)（不包括尾部空格）尾部空格）8.LTRIM(C) :刪除字符串左端空格；刪除字符串左端空格；9.RTRIM(C):刪除字符串右端空格；刪除字符串右端空格；10. CAST(表達(dá)式表達(dá)式 as 類型類型):將表達(dá)式轉(zhuǎn)換為將表達(dá)式轉(zhuǎn)換為SQL的某種類型。的某種類型。字符函數(shù)應(yīng)用舉例字符函數(shù)應(yīng)用舉例1 顯示姓名最后一個(gè)字為顯示姓名最后一個(gè)字為“強(qiáng)強(qiáng)”的學(xué)生；的學(xué)生；select * from 學(xué)生基本情況學(xué)生基本情況 where 姓名姓名 like %強(qiáng)強(qiáng)%goselect * from 學(xué)生基本情況學(xué)生基本情況 where right(rtrim(姓

42、名姓名),1)=強(qiáng)強(qiáng)2 顯示身份證號(hào)帶顯示身份證號(hào)帶X的學(xué)生信息；的學(xué)生信息；select * from 學(xué)生基本情況學(xué)生基本情況 where 身份證號(hào)身份證號(hào) like %X%goselect * from 學(xué)生基本情況學(xué)生基本情況 where right(rtrim(upper(身份證身份證號(hào)號(hào)),1)=X3 顯示姓名為兩個(gè)字的學(xué)生信息；顯示姓名為兩個(gè)字的學(xué)生信息；select * from 學(xué)生基本情況學(xué)生基本情況 where len(rtrim(姓名姓名)=24 將學(xué)號(hào)、姓名、數(shù)學(xué)成績(jī)組成一個(gè)顯示內(nèi)容；將學(xué)號(hào)、姓名、數(shù)學(xué)成績(jī)組成一個(gè)顯示內(nèi)容；select 學(xué)號(hào)學(xué)號(hào)+ +姓名姓名+str

43、(數(shù)學(xué)成績(jī)數(shù)學(xué)成績(jī),5) as 組合顯示組合顯示 from 學(xué)生基本學(xué)生基本情況情況 思考：思考：1 如何查詢姓名僅一個(gè)或兩個(gè)字的學(xué)生信息？如何查詢姓名僅一個(gè)或兩個(gè)字的學(xué)生信息？ 2 如何將學(xué)號(hào)、姓名、平均成績(jī)進(jìn)行組合顯示？如何將學(xué)號(hào)、姓名、平均成績(jī)進(jìn)行組合顯示？ 3 如何顯示如何顯示“管院管院”學(xué)生的信息？學(xué)生的信息？字符函數(shù)應(yīng)用舉例字符函數(shù)應(yīng)用舉例寫出下列語(yǔ)句的結(jié)果：寫出下列語(yǔ)句的結(jié)果：1. select ascii(123)+ascii(char(65)+ len(str(100+23)+ len(ltrim(str(12*10+23+456/1000) as len12. select

44、 ascii(str(456)+ascii(ltrim(str(456)+ascii(rtrim(str(456) as len23. select ascii(str(456,3)+len(left(管理管理,2)+len(right(學(xué)院學(xué)院,2)+len(rtrim(str(789.123) as len34. declare s1 char(100),s2 char(50)set s1=西安科技大學(xué)管理學(xué)院西安科技大學(xué)管理學(xué)院set s2=信息管理與信息系統(tǒng)信息管理與信息系統(tǒng)select substring(s1,1,1)+substring(s1,3,1)+ substring(s1

45、,5,1)+substring(s2,1,1)+substring(s2,3,1)+char(48)+char(49)go 常用日期時(shí)間函數(shù)常用日期時(shí)間函數(shù)Getdate():返回當(dāng)前系統(tǒng)的日期和時(shí)間；返回當(dāng)前系統(tǒng)的日期和時(shí)間；Year(date):返回指定日期的年份；返回指定日期的年份；Month(date):返回指定日期的月份；返回指定日期的月份；Day(date):返回指定日期的某天；返回指定日期的某天；Dateadd(datepart,n,date):在指定日期上加上一在指定日期上加上一段時(shí)間，返回新的日期時(shí)間；段時(shí)間，返回新的日期時(shí)間；其中：其中：Datepart:yyyy;mm;d

46、d;ww/wk;hh;mi;ss;Datediff(datepart,startdate,enddate):返回兩返回兩個(gè)日期的日期和時(shí)間的差值。個(gè)日期的日期和時(shí)間的差值。Datename(datepart,date):返回指定日期部分的返回指定日期部分的字符串。字符串。日期時(shí)間函數(shù)舉例日期時(shí)間函數(shù)舉例1）select dateadd(yyyy,2,getdate()2）select 學(xué)號(hào)學(xué)號(hào),姓名姓名,出生日期出生日期, datediff(yy,出生日期出生日期,getdate() as 年齡年齡 from 學(xué)生基本情況學(xué)生基本情況where month(getdate()=month(出生

47、出生日期日期)3) select 2年后的今天年后的今天是是+datename(dw,dateadd(yyyy,2,getdate() as 星期星期日期時(shí)間函數(shù)舉例日期時(shí)間函數(shù)舉例declare d datetime -聲明局部變量聲明局部變量set d=getdate() -將系統(tǒng)日期時(shí)間賦給變量將系統(tǒng)日期時(shí)間賦給變量select 今天是今天是+datename(yyyy,d)+年年+ datename(mm,d)+月月+ datename(dd,d)+日日+ datename(hh,d)+時(shí)時(shí)+ datename(mi,d)+分分+ datename(ss,d)+秒秒+ datename

48、(dw,d)日期時(shí)間函數(shù)舉例日期時(shí)間函數(shù)舉例說(shuō)明下列語(yǔ)句的作用：說(shuō)明下列語(yǔ)句的作用：1. select top 5 姓名姓名,datediff(yy,出生日出生日期期,getdate()as 年齡年齡 from 學(xué)生基本情況學(xué)生基本情況 order by 2 desc2. select 學(xué)號(hào)學(xué)號(hào),姓名姓名,出生日期出生日期, year(getdate()-year(出生日期出生日期) as age from 學(xué)生基本情況學(xué)生基本情況 where year(出生日期出生日期) 1982 order by age desc3. select 學(xué)號(hào)學(xué)號(hào),姓名姓名,出生日期出生日期 from 學(xué)生基學(xué)生基本情況本情況 where year(出生日期出生日期) between 1982 and 1985 order by 出生日期出生日期日期時(shí)間函數(shù)舉例日期時(shí)間函數(shù)舉例思考：思考：1 如何顯示下一個(gè)月過(guò)生日的學(xué)生信息？如何顯示下一個(gè)月過(guò)生日的學(xué)生信息？2 如何顯示如何顯示2008年年齡正好年年齡正好25歲的學(xué)生？歲的學(xué)生