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

第4章查詢與視圖 4 1SQL語(yǔ)言簡(jiǎn)介4 2SELECT數(shù)據(jù)訪問(wèn)基本方法4 3條件檢索的SELECT語(yǔ)句4 4從多張表檢索的SELECT語(yǔ)句4 5Union操作和子查詢語(yǔ)句4 6SQL常用函數(shù)及其使用方法 4 1SQL語(yǔ)言簡(jiǎn)介 SQL StructuredQueryLanguage 結(jié)構(gòu)化查詢語(yǔ)言 是一種介于關(guān)系代數(shù)與關(guān)系運(yùn)算之間的語(yǔ)言 主要功能包括查詢 操縱 定義和控制等方面 是一個(gè)通用的 功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言 Transact SQL的組成1 數(shù)據(jù)定義語(yǔ)言 DDLDataDefinitionLanguage 用來(lái)建立數(shù)據(jù)庫(kù) 數(shù)據(jù)庫(kù)對(duì)象 如Createtable view等 2 數(shù)據(jù)操縱語(yǔ)言 DMLDataManipulationLanguage 用來(lái)操縱數(shù)據(jù)庫(kù)中的數(shù)據(jù)的命令 如select insert update delete等 3 數(shù)據(jù)控制語(yǔ)言 DCLDataControlLanguage 用來(lái)控制數(shù)據(jù)庫(kù)組建的存取權(quán)限等 如Grant Revoke等 4 流程控制語(yǔ)言 FCLFlowControlLanguage 用來(lái)設(shè)計(jì)應(yīng)用程序的語(yǔ)句 如if while case等 5 其他語(yǔ)言要素 ALEAdditionallanguageElement 包括變量 運(yùn)算符 函數(shù)和注解等 4 2SELECT數(shù)據(jù)查詢 SELECT語(yǔ)句的基本格式如下 SELECTselect listFROMtable source WHEREsearch condition GROUPBYgroup by expression HAVINGsearch condition ORDERBYorder expression ASC DESC INTOnew table SELECT子句 SELECT ALL DISTINCT TOPn PERCENT table name view name table alias column name expression AS column alias column alias expression n 4 2 1表中列的使用方法 1 選擇所有字段SELECT FROM表名如 usepubsselect fromauthors 顯示authors中的所有信息 全表查詢 2 選擇部分字段SELECT列名1 列名2 列名n FROM表名如 usestudentselect學(xué)號(hào) 姓名 性別from學(xué)生基本情況 顯示學(xué)生基本情況中學(xué)號(hào) 姓名 性別字段的信息 SELECT語(yǔ)句的使用方式 3 為字段設(shè)置別名SELECT列名1as新名1 列名2as新名2 列名nas新名n FROM表名 將選擇字段的標(biāo)題按新的名稱顯示 注意 新標(biāo)題的名稱可以有下列方式 1 在列表達(dá)式后面給出列名selectxh學(xué)號(hào)2 用 來(lái)連接列表達(dá)式select學(xué)號(hào) xh3 新標(biāo)題的名稱用單引號(hào) 雙引號(hào)括起來(lái) 4 用AS關(guān)鍵字來(lái)連接列表達(dá)式和指定的列名 SELECT語(yǔ)句 例如 查詢authors中編號(hào) 姓名 電話 地址的信息 可以采用以下方式 selectau id編號(hào) au lname姓 au fname名 phone電話 address地址fromauthorsselect編號(hào) au id 姓 au lname 名 au fname 電話 phone 地址 addressfromauthorsselectau id 編號(hào) au lname 姓 au fname 名 phone 電話 address 地址 fromauthorsselectau id 編號(hào) au lname 姓 au fname 名 phone 電話 address 地址 fromauthorsselectau idas編號(hào) au lnameas姓 au fnameas名 phoneas電話 addressas地址fromauthors SELECT語(yǔ)句 4 在選擇列表中使用表達(dá)式在查詢數(shù)據(jù)時(shí) 可以通過(guò)運(yùn)算操作來(lái)控制從一個(gè)表中的返回值 例如 查詢每個(gè)學(xué)生的總成績(jī) 平均成績(jī) select學(xué)號(hào) 姓名 數(shù)學(xué)成績(jī) 語(yǔ)文成績(jī) 英語(yǔ)成績(jī)as總成績(jī) 數(shù)學(xué)成績(jī) 語(yǔ)文成績(jī) 英語(yǔ)成績(jī) 3as平均成績(jī)from學(xué)生基本情況 SELECT語(yǔ)句 4 消除字段數(shù)據(jù)的重復(fù)值在查詢數(shù)據(jù)時(shí) 可能會(huì)有許多重復(fù)的數(shù)據(jù) SQL提供的Distinct關(guān)鍵字 可以從select語(yǔ)句的結(jié)果集中消除重復(fù)的數(shù)據(jù) 例如 1 查詢學(xué)生來(lái)至哪些院系的信息 selectdistinct院系名稱from學(xué)生基本情況2 查詢有哪些專業(yè)的學(xué)生 selectdistinct所學(xué)專業(yè)from學(xué)生基本情況 SELECT語(yǔ)句 6 限制記錄的行數(shù)在限制查詢記錄的行數(shù)時(shí) 可以使用下列方式 1 使用topn 返回前n條記錄 2 使用topnpercent 返回前n 條記錄 3 使用setrowcountn 返回前n條記錄 n 0關(guān)閉例如 1 顯示前5條記錄selecttop5 from學(xué)生基本情況2 顯示20 學(xué)生的信息selecttop20percent from學(xué)生基本情況3 對(duì)所有select語(yǔ)句 均顯示5條記錄 setrowcount5select from學(xué)生基本情況 4 2條件子句的使用方法 1 條件子句最常用的條件子句是where和having 用它們來(lái)指定一系列條件 執(zhí)行操作時(shí)只返回滿足條件的記錄 Having通常與Groupby一起使用 用來(lái)說(shuō)明返回分組的條件 例如 1 顯示男生的相關(guān)信息Select from學(xué)生基本情況where性別 男 2 顯示男生人數(shù)超過(guò)10人的院系信息select院系名稱 count as男生人數(shù)from學(xué)生基本情況where性別 男 groupby院系名稱havingcount 10 4 2SELECT語(yǔ)句的查詢條件 2 算術(shù)表達(dá)式 比較運(yùn)算符算術(shù)運(yùn)算符有 使用算術(shù)表達(dá)式的一般形式為 expressionoperatorexpression比較運(yùn)算符 是最為常見(jiàn)的一種條件限制方式 用于測(cè)試兩個(gè)表達(dá)式是否相同 返回值為T(mén)rue或False WHERE子句中允許出現(xiàn)的比較運(yùn)算符有 等于 大于 對(duì)于等于 不等于 不等于 不大于 不小于 4 2SELECT語(yǔ)句的查詢條件 3 邏輯表達(dá)式在Transact SQL中可以使用的邏輯運(yùn)算符有三個(gè) NOT 邏輯反 對(duì)指定的布爾表達(dá)式求反 AND 邏輯與 只有當(dāng)兩個(gè)條件都是TRUE時(shí)取值為T(mén)RUE OR 邏輯或 當(dāng)兩個(gè)條件中任何一個(gè)條件是TRUE時(shí) 取值為T(mén)RUE 優(yōu)先順序 先Not 再And 后Or 4 2SELECT語(yǔ)句的查詢條件 4 BETWEEN條件BETWEEN用于搜索介于兩個(gè)指定值之間的所有信息 且包括兩個(gè)指定的值 格式為 條件字段 NOT BETWEENbegin expressionANDend expression例如 查詢數(shù)學(xué)成績(jī)?cè)?0到90之間的信息 Select from學(xué)生基本情況where數(shù)學(xué)成績(jī)between80and90Between是AND的簡(jiǎn)化用法 上面的語(yǔ)句等價(jià)于 Select from學(xué)生基本情況where數(shù)學(xué)成績(jī) 80and數(shù)學(xué)成績(jī) 90 4 2SELECT語(yǔ)句的查詢條件 4 IN列表搜索條件IN列表搜索條件用于返回與給定的列表中任意一個(gè)值相匹配的記錄 格式為 條件字段 NOT IN 列表選項(xiàng) 例如 查詢數(shù)學(xué)成績(jī)?yōu)?0 80 90的信息 select from學(xué)生基本情況where數(shù)學(xué)成績(jī)in 70 80 90 In列表?xiàng)l件是OR的簡(jiǎn)化形式 上面語(yǔ)句等價(jià)于 select from學(xué)生基本情況where數(shù)學(xué)成績(jī) 70OR數(shù)學(xué)成績(jī) 80OR數(shù)學(xué)成績(jī) 90 4 2SELECT語(yǔ)句的查詢條件 6 LIKE匹配模式LIKE匹配模式是確定條件字符串是否與指定的模式匹配 使用格式 條件字段 NOT LIKE匹配模式SQL中的有效模式包括 可匹配任意類型和長(zhǎng)度的字符串 Like 李 下劃線 可匹配任何單個(gè)字符 Like 71005 指定范圍或集合中的任何單個(gè)字符 Like a d 不屬于指定范圍或集合的任何單個(gè)字符例如 1 查詢所有姓 張 的學(xué)生信息select from學(xué)生基本情況where姓名like 張 4 2SELECT語(yǔ)句的查詢條件 6 NULL搜索條件空值比較的關(guān)鍵字是ISNULL或ISNOTNULL 其中NULL表示字段的數(shù)據(jù)未知或不確定 格式為 表達(dá)式ISNULL或表達(dá)式ISNOTNULL例如 查詢?nèi)鄙贁?shù)學(xué)成績(jī)的學(xué)生信息 select from學(xué)生基本情況where數(shù)學(xué)成績(jī)isnull 查詢示例表 練習(xí)討論 按10 的比例顯示本專業(yè)的男生信息 查詢 管理學(xué)院 專業(yè)分布情況 查詢本專業(yè)學(xué)生來(lái)自哪些省份 查詢英語(yǔ)不及格學(xué)生的信息 顯示學(xué)號(hào) 姓名 專業(yè) 英語(yǔ)原成績(jī) 英語(yǔ) 10 英語(yǔ) 1 1等字段信息 顯示管理學(xué)院學(xué)生中 不姓 劉 和 張 的學(xué)生信息 查詢 數(shù)學(xué) 在65 75之間 而 英語(yǔ) 不在70 90之間的學(xué)生信息 查詢 管理學(xué)院 能源學(xué)院 機(jī)械學(xué)院 人文學(xué)院 學(xué)號(hào)在01 07之間的學(xué)生信息 將學(xué)生的 學(xué)號(hào) 姓名 性別 身份證號(hào) 作為一列 院系名稱 專業(yè) 作為一列 各數(shù)據(jù)之間用 分隔 列名分別為 基本信息 隸屬關(guān)系 進(jìn)行顯示 查詢總成績(jī) 210 語(yǔ)文成績(jī)?cè)?5 90間 且不屬于 人文學(xué)院 的學(xué)生信息 排序和分組的使用 排序使用Orderby對(duì)查詢結(jié)果中的一個(gè)或多個(gè)字段進(jìn)行排序 排序可以升序 ASC 也可以是降序 DESC 若未指定排序方式 默認(rèn)升序 例如 1 按姓名升序查詢學(xué)生信息 select from學(xué)生基本情況orderby姓名2 查詢數(shù)學(xué)成績(jī)前5名的學(xué)生信息selecttop5 from學(xué)生基本情況orderby數(shù)學(xué)成績(jī)desc思考 1 如何按院系升序 姓名降序查詢 2 如何查詢總成績(jī)前10名的學(xué)生信息 3 如何按性別降序 學(xué)號(hào)升序 平均成績(jī)升序查詢 4 查詢本專業(yè)女生中年齡最小的10個(gè)學(xué)生信息 數(shù)據(jù)記錄的分組 使用GroupBy進(jìn)行分組 返回的結(jié)果中 每一行都產(chǎn)生聚合值 常用的聚合函數(shù)包括 Sum 返回一個(gè)數(shù)字列或計(jì)算列的總和Avg 返回一個(gè)數(shù)字列或計(jì)算列的平均值Min 返回一個(gè)數(shù)字列或計(jì)算列的最小值Max 返回一個(gè)數(shù)字列或計(jì)算列的最大值Count 返回非NULL值的記錄個(gè)數(shù)count 返回符合條件的記錄個(gè)數(shù) 排序與分組 例如 1 統(tǒng)計(jì)各個(gè)院系的學(xué)生人數(shù) select院系名稱 count as人數(shù)from學(xué)生基本情況groupby院系名稱2 統(tǒng)計(jì)各個(gè)院系的男生 女生的人數(shù) select院系名稱 性別 count as人數(shù)from學(xué)生基本情況groupby院系名稱 性別思考 1 查詢各個(gè)專業(yè) 不同省份的學(xué)生人數(shù) 2 查詢數(shù)據(jù)表中所有男生 女生的人數(shù) 3 查詢本專業(yè)數(shù)學(xué)成績(jī)的總分 平均分 最高分 最低分 4 按性別查詢本專業(yè)英語(yǔ)的平均分 最高分 最低分和人數(shù) 4 3數(shù)據(jù)連接與子查詢 數(shù)據(jù)連接查詢根據(jù)各個(gè)表之間的邏輯關(guān)系 從兩個(gè)或多個(gè)表中檢索數(shù)據(jù) 并把所有檢索到的數(shù)據(jù)通過(guò)一個(gè)數(shù)據(jù)集返回 1 用WHERE進(jìn)行數(shù)據(jù)連接使用WHERE子句可以實(shí)現(xiàn)多個(gè)表間的數(shù)據(jù)連接 例如 從學(xué)生基本情況表與成績(jī)表中查詢學(xué)生的學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī) SELECT學(xué)生基本情況 學(xué)號(hào) 姓名 所學(xué)專業(yè) 成績(jī)表 高等數(shù)學(xué) 成績(jī)表 大學(xué)英語(yǔ) 計(jì)算機(jī)基礎(chǔ) 管理學(xué)FROM學(xué)生基本情況 成績(jī)表where學(xué)生基本情況 學(xué)號(hào) 成績(jī)表 學(xué)號(hào) WHERE數(shù)據(jù)連接 如果數(shù)據(jù)表名稱太長(zhǎng) 造成書(shū)寫(xiě)麻煩 可以給數(shù)據(jù)表另外取一個(gè)簡(jiǎn)潔的名稱 即別名 例如 將上面語(yǔ)句中的基本情況表改名為jbqk 將成績(jī)表改名為cjb 則語(yǔ)句變成 SELECTjbqk 學(xué)號(hào) 姓名 所學(xué)專業(yè) 高等數(shù)學(xué) 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) 管理學(xué)FROM學(xué)生基本情況asjbqk 成績(jī)表ascjbwherejbqk 學(xué)號(hào) cjb 學(xué)號(hào)或者改成 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqk 成績(jī)表cjbwherejbqk 學(xué)號(hào) cjb 學(xué)號(hào) JoinOn連接 2用JOINON連接在Select語(yǔ)句中 通常使用Join On連接詞來(lái)實(shí)現(xiàn)表間的連接 例如 上面的Where連接可以改成 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkjoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào) 連接查詢 3用連接關(guān)鍵字進(jìn)行連接從多個(gè)表中查詢相關(guān)數(shù)據(jù) 最好在FROM子句中指定連接條件 這樣可以將指定的連接條件與WHERE子句的搜索條件分開(kāi) 連接查詢的主要用法為 Select選擇字段列表From數(shù)據(jù)表連接的類型其它表 on連接條件 Where搜索條件 OrderBy排序字段 InnerJoin 1 內(nèi)連接 InnerJoin 內(nèi)連接是最常用的連接操作 連接的結(jié)果是從兩個(gè)表中挑選出符合連接條件的數(shù)據(jù) 如果數(shù)據(jù)無(wú)法滿足連接條件則將其丟棄 例如 使用內(nèi)連接 從學(xué)生基本情況表與成績(jī)表中查詢學(xué)生的學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī) SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkinnerjoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào) OuterJoin 2 外連接 OuterJoin 外連接中 參與連接的表有主從之分 以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù)行 符合條件的數(shù)據(jù)將直接返回到查詢結(jié)果中 如果主表的行在從表中沒(méi)有相匹配的行 則對(duì)應(yīng)從表的列將填上NULL值 外連接的類型有 左連接 LeftJoin LeftOuterJoin 右連接 RightJoin RightOuterJoin 完全連接 FullJoin FullOuterJoin LeftJoin 1 左連接 LeftJoin 以連接左邊的表為主表 即左邊表的所有行都顯示出來(lái) 若左邊表在從表中沒(méi)有與連接條件相匹配的數(shù)據(jù)行 則從表不匹配的列填上NULL 例如 前面的查詢 使用左連接查詢 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkleftjoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào) RightJoin 2 右連接 RIGHTJoin 以連接右邊的表為主表 即右邊表的所有行都顯示出來(lái) 若右邊表在左表中沒(méi)有與連接條件相匹配的數(shù)據(jù)行 則左表不匹配的列填上NULL 例如 前面的查詢 使用右連接查詢 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkRightjoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào) FullJoin 3 完全連接 FullJoin 不管另一邊的表是否有匹配的行 查詢結(jié)果將顯示兩個(gè)表的所有行 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkfulljoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào) CrossJoin 3 交叉連接 CrossJoin 交叉連接產(chǎn)生的查詢結(jié)果的行數(shù)是第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù) 即迪卡爾積 如第一個(gè)表有15條記錄 第二表有20條記錄 則交叉連接返回的記錄有300條 對(duì)上面的例子 使用交叉連接 SELECTjbqk 學(xué)號(hào) jbqk 姓名 jbqk 所學(xué)專業(yè) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)FROM學(xué)生基本情況jbqkcrossjoin成績(jī)表cjb 練習(xí)討論 數(shù)據(jù)表名 JBQK 數(shù)據(jù)表名 CJB 練習(xí)討論 1 查詢本專業(yè)陜西前5名學(xué)生各門(mén)課程的學(xué)習(xí)成績(jī) 按省份升序 性別降序 姓名升序進(jìn)行排序 顯示學(xué)生的學(xué)號(hào) 姓名 性別 省份及各門(mén)課程成績(jī) 2 統(tǒng)計(jì)管理學(xué)院各專業(yè)男女生高等數(shù)學(xué)的平均分 最高分 最低分及人數(shù) 按專業(yè)降序排列 3 按內(nèi)連接 左連接 右連接 完全連接顯示兩個(gè)表的信息 按專業(yè)排序 4 統(tǒng)計(jì)管理學(xué)院各專業(yè)大學(xué)英語(yǔ)的平均分 學(xué)習(xí)人數(shù) 按專業(yè)降序排列 只顯示學(xué)習(xí)人數(shù)不低于3人的專業(yè) 5 統(tǒng)計(jì)總成績(jī)前10名的學(xué)生 顯示學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī)及總成績(jī) 按總成績(jī) 專業(yè) 學(xué)號(hào)排序 聯(lián)合查詢 4 聯(lián)合查詢 Union 使用Union子句將兩個(gè)或多個(gè)Select語(yǔ)句的結(jié)果組合成一個(gè)結(jié)果集 按照第一個(gè)Select的字段列表進(jìn)行顯示 Union會(huì)自動(dòng)將重復(fù)的數(shù)據(jù)行刪除 但要求每個(gè)Select語(yǔ)句的結(jié)果必須具有相同的結(jié)構(gòu)和列數(shù) 并且每列的數(shù)據(jù)類型也必須兼容 Union查詢的基本格式 Select from Where條件 UnionSelect from Where條件 n OrderBy排序字段 聯(lián)合查詢 例如 將學(xué)生基本情況表中以01開(kāi)頭的學(xué)號(hào) 各門(mén)課程的成績(jī)與成績(jī)表的學(xué)號(hào) 各門(mén)課程成績(jī)聯(lián)合查詢 SELECT學(xué)號(hào) 數(shù)學(xué) 語(yǔ)文 英語(yǔ)FROM學(xué)生基本情況where學(xué)號(hào)like 01 UnionSELECT學(xué)號(hào) 高等數(shù)學(xué) 大學(xué)英語(yǔ) 計(jì)算機(jī)基礎(chǔ)FROM成績(jī)表思考 1 如何查詢男 女生各5名的學(xué)習(xí)成績(jī) 按性別和總成績(jī)排序 2 如何查詢管理學(xué)院 能源學(xué)院 機(jī)械學(xué)院高等數(shù)學(xué)成績(jī)的最高分 最低分 平均分 2子查詢 Subquery 子查詢是一條包含在另外一條Select語(yǔ)句里面的Select語(yǔ)句 外層的Select語(yǔ)句叫外部查詢 內(nèi)層的Select語(yǔ)句叫內(nèi)部查詢 或子查詢 包括子查詢的Select語(yǔ)句主要格式有 1 where字段名 not IN 子查詢 2 WHERE字段名關(guān)系運(yùn)算符 子查詢 3 where not Exists 子查詢 IN子查詢 使用IN的子查詢使用IN 或NOTIN 的子查詢返回的查詢結(jié)果是一列零值或多列值 子查詢返回結(jié)果之后 外部查詢可以使用這些結(jié)果 格式 where字段名IN 子查詢 例如 查詢與 張海燕 同省份的學(xué)生信息 SELECT FROM學(xué)生基本情況Where省份IN select省份FROM學(xué)生基本情況where姓名 張海燕 思考 1 顯示高等數(shù)學(xué)在80 90間的學(xué)生基本信息 顯示學(xué)號(hào) 姓名 專業(yè) 高等數(shù)學(xué)等信息 2 查詢 5個(gè)年齡較小 的所有學(xué)生信息 3 查詢學(xué)生基本信息 并統(tǒng)計(jì)高等數(shù)學(xué)的平均成績(jī) 每人與平均成績(jī)的差值 2 使用比較運(yùn)算符的子查詢子查詢可以由一個(gè)比較運(yùn)算符引入 比較運(yùn)算符可以是 selectavg 數(shù)學(xué)成績(jī) FROM學(xué)生基本情況 思考 1 如何查詢本專業(yè)大學(xué)英語(yǔ)成績(jī) 60且低于平均分的學(xué)生信息 2 統(tǒng)計(jì)管理學(xué)院各專業(yè)高等數(shù)學(xué)低于平均分的人數(shù) 3 查詢大學(xué)英語(yǔ)成績(jī)最高分的學(xué)生信息 Exists子查詢 3 使用Exists的子查詢使用Exists 或NotExists 引入子查詢時(shí) 就相當(dāng)于進(jìn)行一次存在測(cè)試 外部查詢的Where子句測(cè)試子查詢返回的行是否存在 子查詢實(shí)際上不產(chǎn)生任何數(shù)據(jù) 它只返回True或False 例如 查詢成績(jī)表中女生的學(xué)習(xí)成績(jī) SELECT FROM成績(jī)表Whereexists select FROM學(xué)生基本情況where學(xué)號(hào) 成績(jī)表 學(xué)號(hào)and性別 女 思考 1 如何查詢管理學(xué)院的學(xué)生成績(jī) 2 查詢本專業(yè)高等數(shù)學(xué)不及格的學(xué)生信息 練習(xí)討論 數(shù)據(jù)表名 JBQK 數(shù)據(jù)表名 CJB 練習(xí)討論 根據(jù)JBQK表 CJB表完成下列查詢 1 查詢高等數(shù)學(xué)成績(jī)高于平均成績(jī)的學(xué)生信息 顯示學(xué)號(hào) 姓名 專業(yè) 高等數(shù)學(xué)等 2 查詢信息管理專業(yè)男生的高等數(shù)學(xué)成績(jī) 電子商務(wù)專業(yè)女生的高等數(shù)學(xué)成績(jī) 能源學(xué)院所有學(xué)生的數(shù)學(xué)成績(jī) 顯示顯示學(xué)號(hào) 姓名 性別 院系 專業(yè) 高等數(shù)學(xué)等 按院系 專業(yè)排序 3 查詢與 劉文東 同專業(yè)的學(xué)生信息 顯示顯示學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī) 基于查詢的數(shù)據(jù)表 1基于查詢生成新的數(shù)據(jù)表如果需要將查詢結(jié)果保存下來(lái) 使用INTO子句可以生成一個(gè)新表并將結(jié)果保存在這個(gè)新的數(shù)據(jù)表中 命令基本格式 Select選擇字段表Into新的表名FROM已有的表 where條件 例如 1 將 學(xué)生基本情況 表復(fù)制成jbqkSelect Intojbqkfrom學(xué)生基本情況 2 統(tǒng)計(jì)各個(gè)院系英語(yǔ)成績(jī)的平均分 最高分 最低分 考試人數(shù) 結(jié)果放在Eng tj表 SELECT學(xué)生基本情況 院系名稱 AVG 成績(jī)表 大學(xué)英語(yǔ) AS英語(yǔ)平均 MAX 成績(jī)表 大學(xué)英語(yǔ) AS英語(yǔ)最高 MIN 成績(jī)表 大學(xué)英語(yǔ) AS英語(yǔ)最低 COUNT AS考試人數(shù)INTOEng tjFROM學(xué)生基本情況INNERJOIN成績(jī)表ON學(xué)生基本情況 學(xué)號(hào) 成績(jī)表 學(xué)號(hào)GROUPBY學(xué)生基本情況 院系名稱思考 1 將管理學(xué)院學(xué)習(xí)成績(jī)前10名學(xué)生的信息保存到數(shù)據(jù)表gl 10中 只保存學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī) 2 如何將本專業(yè)不及格學(xué)生的信息保存在數(shù)據(jù)表bjg xs 保存學(xué)號(hào) 姓名 專業(yè) 各門(mén)課程成績(jī) 3 將jbqk表的結(jié)構(gòu)復(fù)制到j(luò)b jg中 將查詢結(jié)果插入數(shù)據(jù)表 2向已有表插入數(shù)據(jù)用InsertInto Select語(yǔ)句 可以將一個(gè)子查詢的結(jié)果添加到數(shù)據(jù)表中 命令格式 InsertInto數(shù)據(jù)表Select查詢例如 創(chuàng)建臨時(shí)數(shù)據(jù)表stu 1 存放從基本情況表 成績(jī)表中查詢出來(lái)的學(xué)號(hào) 姓名 性別 年齡 總成績(jī) 平均成績(jī)等信息 createtable stu 1 學(xué)號(hào)char 8 姓名nchar 4 性別nchar 1 年齡tinyint 總成績(jī)int 平均成績(jī)decimal 5 2 goinsertinto stu 1selectjbqk 學(xué)號(hào) jbqk 姓名 jbqk 性別 year getdate year 出生日期 cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué) cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué) 4from學(xué)生基本情況jbqkinnerjoin成績(jī)表cjbonjbqk 學(xué)號(hào) cjb 學(xué)號(hào)goselect from stu 1思考 如何將信息管理 電子商務(wù)專業(yè)學(xué)習(xí)成績(jī)前5名的學(xué)生信息放到與基本情況表結(jié)構(gòu)類似的數(shù)據(jù)表inf ec中 子查詢修改記錄 3用子查詢修改記錄Update語(yǔ)句中 Set子句 Where子句均可以使用子查詢 例如 在基本情況表中添加字段 第1學(xué)期總分 第1學(xué)期平均 并修改這些字段的值 altertable學(xué)生基本情況add第1學(xué)期總分smallint 第1學(xué)期平均decimal 5 2 goupdate學(xué)生基本情況set第1學(xué)期總分 selectcjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué)from成績(jī)表cjbwhere學(xué)生基本情況 學(xué)號(hào) cjb 學(xué)號(hào) 第1學(xué)期平均 select cjb 高等數(shù)學(xué) cjb 大學(xué)英語(yǔ) cjb 計(jì)算機(jī)基礎(chǔ) cjb 管理學(xué) 4from成績(jī)表cjbwhere學(xué)生基本情況 學(xué)號(hào) cjb 學(xué)號(hào) goselect from學(xué)生基本情況思考 1 對(duì)本專業(yè)高等數(shù)學(xué)不及格的學(xué)生 將數(shù)學(xué)成績(jī)提高15 2 將不是本專業(yè)學(xué)生的各門(mén)課程成績(jī)清空 子查詢刪除記錄 4用子查詢刪除記錄Delete命令的where子句可以使用子查詢來(lái)從數(shù)據(jù)表中刪除相關(guān)記錄 例如 從基本情況表 成績(jī)表中刪除 男生 的相關(guān)記錄 deletefrom成績(jī)表whereexists select from學(xué)生基本情況jbqkwhere成績(jī)表 學(xué)號(hào) jbqk 學(xué)號(hào)andjbqk 性別 男 godeletefrom學(xué)生基本情況where性別 男 goselect from學(xué)生基本情況select from成績(jī)表思考 將本專業(yè)學(xué)生的成績(jī)備份到dele bak 并從cjb中刪除 同時(shí)在jbqk表中添加字段Dele flag dele table 并給dele flag賦值1 給dele table賦值備份表的名稱 SQL查詢的增強(qiáng)功能 COMPUTE子句使用COMPUTE和COMPUTEBY子句既能瀏覽詳細(xì)數(shù)據(jù)又可看到統(tǒng)計(jì)的結(jié)果 主要功能 1 生成合計(jì)作為附加的匯總放在結(jié)果集的最后 2 當(dāng)與BY一起使用時(shí) 則在結(jié)果集生成控制中斷與小計(jì) 3 同一查詢內(nèi)可同時(shí)用COMPUTE和COMPUTEBY子句 4 如果使用COMPUTEBY 則必須使用ORDERBY COMPUTE子句 其語(yǔ)法形式為 COMPUTE聚合函數(shù) column name n BYcolumn name n 注意 COMPUTE BY 子句不能與SELECTINTO子句一起使用 COMPUTE子句中的列必須出現(xiàn)在SELECT子句的列表中 COMPUTEBY表示按指定的列進(jìn)行明細(xì)匯總 使用BY關(guān)鍵字時(shí)必須同時(shí)使用ORDERBY子句 并且COMPUTEBY后出現(xiàn)的列必須具有與ORDERBY后出現(xiàn)的列相同的順序 且不能跳過(guò)其中的列 例如 如果ORDERBY子句按照如下順序指定排序列 ORDERBYa b c則COMPUTEBY后的列表只能是下面任一種形式 BYa b cBYa bBYa COMPUTE子句 應(yīng)用舉例 1 對(duì)管理學(xué)院學(xué)生的學(xué)號(hào) 姓名 高等數(shù)學(xué) 大學(xué)英語(yǔ) 數(shù)據(jù)庫(kù)列出明細(xì) 并統(tǒng)計(jì)高等數(shù)學(xué)平均分 最高分及人數(shù) 2 對(duì)管理學(xué)院學(xué)生按專業(yè)列出學(xué)號(hào) 姓名 專業(yè) 高等數(shù)學(xué)的明細(xì) 并統(tǒng)計(jì)高等數(shù)學(xué)的平均 最高 最低成績(jī) 3 按專業(yè)列出高等數(shù)學(xué)的明細(xì) 并統(tǒng)計(jì)各專業(yè)的平均 最高 最低成績(jī) 然后計(jì)算全校的平均 最高 最低成績(jī) WITHROLLUP 在GroupBy子句后使用withRollup 將對(duì)GroupBy指定的各列產(chǎn)生匯總行 例如 1 統(tǒng)計(jì)各專業(yè)人數(shù)及總?cè)藬?shù) 2 按專業(yè)統(tǒng)計(jì)男 女生人數(shù) 并統(tǒng)計(jì)專業(yè)總?cè)藬?shù)及學(xué)生的總?cè)藬?shù) 3 按學(xué)院 專業(yè)統(tǒng)計(jì)數(shù)據(jù)庫(kù)的平均 最高分 同時(shí)統(tǒng)計(jì)各個(gè)學(xué)院以及全校的平均 最高成績(jī) WITHCube 在GroupBy子句后使用withCube 將對(duì)GroupBy指定的各列的所有可能組合均產(chǎn)生匯總行 例如 按專業(yè)統(tǒng)計(jì)男 女生人數(shù) 并統(tǒng)計(jì)專業(yè)總?cè)藬?shù)及男 女生總?cè)藬?shù) 全部學(xué)生的總?cè)藬?shù) Select所學(xué)專業(yè) 性別 count as人數(shù)FromjbqkGroupby所學(xué)專業(yè) 性別withcube 常用字符串函數(shù) 1 ASCII C 返回字符串最左端字符的ASCII值 例如 顯示字符 A a 0 的ASCII值selectascii A asA ascii a asa ascii 0 aszero2 CHAR N 返回n 0 255 對(duì)應(yīng)的字符 例如 顯示十進(jìn)制數(shù)65 97 48 32對(duì)應(yīng)的字符selectchar 65 char 97 char 48 char 32 3 STR FLOAT LEN DECIMAL 將數(shù)轉(zhuǎn)換成字符串 FLOAT 表示要轉(zhuǎn)換的數(shù)值 LEN 轉(zhuǎn)換后的字符串的長(zhǎng)度 DECIMAL 轉(zhuǎn)換后的字符串中包含的小數(shù)位數(shù) 常用字符串函數(shù) 說(shuō)明 1 若沒(méi)有給出長(zhǎng)度 則對(duì)整數(shù)部分按默認(rèn)長(zhǎng)度10位進(jìn)行轉(zhuǎn)換 2 若給出的長(zhǎng)度太短 則先滿足整數(shù)部分 然后是小數(shù)部分 3 若給出的長(zhǎng)度不能滿足整數(shù)部分的長(zhǎng)度 則顯示n個(gè) 例如 selectstr 123 45 str 123 45 6 2 str 123 45 5 2 str 123 45 3 2 str 123 45 2 2 思考 長(zhǎng)度不夠時(shí) 是否進(jìn)行四舍五入 常用字符串函數(shù) 4 SUBSTRING C START LENGTH 截取子串 其中 C 待截取的字符串 START 截取字符的起始位置 LENGTH 截取字符的長(zhǎng)度 例如 SELECTSUBSTRING 12345ABCDEF 1 2 SUBSTRING 12345ABCDEF 5 5 SUBSTRING 12345ABCDEF 6 5 SUBSTRING 12345ABCDEF 10 5 SUBSTRING 12345ABCDEF 12 2 思考 1 位置 長(zhǎng)度參數(shù)是否可以省略 2 顯示基本情況表中 學(xué)號(hào) 01 開(kāi)頭的學(xué)生信息3 根據(jù)身份證號(hào) 顯示1988年出生的學(xué)生信息 常用字符串函數(shù) 4 LEFT C LEN 從左邊開(kāi)始截取給定長(zhǎng)度字符串 6 RIGHT C LEN 從右邊開(kāi)始截取給定長(zhǎng)度字符串 7 LEN C 返回字符串的字符個(gè)數(shù) 不包括尾部空格 8 LTRIM C 刪除字符串左端空格 9 RTRIM C 刪除字符串右端空格 10 CAST 表達(dá)式as類型 將表達(dá)式轉(zhuǎn)換為SQL的某種類型 字符函數(shù)應(yīng)用舉例 1顯示姓名最后一個(gè)字為 強(qiáng) 的學(xué)生 select from學(xué)生基本情況where姓名like 強(qiáng) goselect from學(xué)生基本情況whereright rtrim 姓名 1 強(qiáng) 2顯示身份證號(hào)帶 X 的學(xué)生信息 select from學(xué)生基本情況where身份證號(hào)like X goselect from學(xué)生基本情況whereright rtrim upper 身份證號(hào) 1 X 3顯示姓名為兩個(gè)字的學(xué)生信息 select from學(xué)生基本情況wherelen rtrim 姓名 24將學(xué)號(hào) 姓名 數(shù)學(xué)成績(jī)組成一個(gè)顯示內(nèi)容 select學(xué)號(hào) 姓名 str 數(shù)學(xué)成績(jī) 5 as組合顯示from學(xué)生基本情況思考 1如何查詢姓名僅一個(gè)或兩個(gè)字的學(xué)生信息 2如何將學(xué)號(hào) 姓名 平均成績(jī)進(jìn)行組合顯示 3如何顯示 管院 學(xué)生的信息 字符函數(shù)應(yīng)用舉例 寫(xiě)出下列語(yǔ)句的結(jié)果 1 selectascii 123 ascii char 65 len str 100 23 len ltrim str 12 10 23 456 1000 aslen12 selectascii str 456 ascii ltrim str 456 ascii rtrim str 456 aslen23 selectascii str 456 3 len left 管理 2 len right 學(xué)院 2 len rtrim str 789 123 aslen34 declare s1char 100 s2char 50 set s1 西安科技大學(xué)管理學(xué)院 set s2 信息管理與信息系統(tǒng) selectsubstring s1 1 1 substring s1 3 1 substring s1 5 1 substring s2 1 1 substring s2 3 1 char 48 char 49 go 常用日期時(shí)間函數(shù) Getdate 返回當(dāng)前系統(tǒng)的日期和時(shí)間 Year date 返回指定日期的年份 Month date 返回指定日期的月份 Day date 返回指定日期的某天 Dateadd datepart n date 在指定日期上加上一段時(shí)間 返回新的日期時(shí)間 其中 Datepart yyyy mm dd ww wk hh mi ss Datediff datepart startdate enddate 返回兩個(gè)日期的日期和時(shí)間的差值 Datename datepart date 返回指定日期部分的字符串 日期時(shí)間函數(shù)舉例 1 selectdateadd yyyy 2 getdate 2 select學(xué)號(hào) 姓名 出生日期 datediff yy 出生日期 getdate as年齡from學(xué)生基本情況wheremonth getdate month 出生日期 3 select 2年后的今天是 date