SQL復雜查詢語句總結.ppt

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