汽車(chē)服務(wù)企業(yè)信息化管理基礎(chǔ)關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL.ppt

2020 3 4 1 四關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL 4 1查詢(xún)語(yǔ)句4 2數(shù)據(jù)更新4 3數(shù)據(jù)定義 學(xué)習(xí)目標(biāo) 能夠熟練掌握SQL語(yǔ)言并能夠在實(shí)際工作中進(jìn)行靈活的應(yīng)用 2020 3 4 2 SQL是結(jié)構(gòu)化查詢(xún)語(yǔ)言 StructuredQueryLanguage 的縮寫(xiě) 美國(guó)國(guó)家標(biāo)準(zhǔn)局ANSI在1986年將SQL作為關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)的標(biāo)準(zhǔn)語(yǔ)言 后被國(guó)際標(biāo)準(zhǔn)化組織 ISO 采納為國(guó)際標(biāo)準(zhǔn) 現(xiàn)在很多大型數(shù)據(jù)庫(kù)都實(shí)現(xiàn)了SQL語(yǔ)言 SQL概述 2020 3 4 3 SQL和SQLSERVER的區(qū)別 SQL structuredquerylanguage 結(jié)構(gòu)化查詢(xún)語(yǔ)言 它是一種標(biāo)準(zhǔn) 不是一種軟件SQLSERVER是數(shù)據(jù)庫(kù)管理系統(tǒng)的一種它是一種軟件 這種軟件在遵循SQL這種標(biāo)準(zhǔn) 很多數(shù)據(jù)庫(kù)管理軟件及開(kāi)發(fā)工具都支持SQL這種標(biāo)準(zhǔn) 2020 3 4 4 SQL語(yǔ)言的特點(diǎn) SQL語(yǔ)言的特點(diǎn) 1 綜合統(tǒng)一2 高度非過(guò)程化3 面向集合的操作方式4 以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方法5 語(yǔ)言簡(jiǎn)潔 易學(xué)易用 2020 3 4 5 2020 3 4 6 SQL語(yǔ)句的書(shū)寫(xiě)準(zhǔn)則 對(duì)大小寫(xiě)不敏感一條語(yǔ)句可以寫(xiě)成一行或多行關(guān)鍵字不能在行與行之間分開(kāi)數(shù)據(jù)項(xiàng)例如屬性項(xiàng) 表 視圖項(xiàng)等同時(shí)列出時(shí) 分隔符用逗號(hào) 字符或字符串常量要用單引號(hào)定界 2020 3 4 7 基本操作對(duì)象 基本表 本身獨(dú)立存在的表 SQL中一個(gè)關(guān)系就對(duì)應(yīng)一個(gè)表 一個(gè)表對(duì)應(yīng)一個(gè)存儲(chǔ)文件 一個(gè)表可以帶若干索引 視圖 從一個(gè)或幾個(gè)基本表中導(dǎo)出的表 本身不獨(dú)立存放在數(shù)據(jù)庫(kù)中 數(shù)據(jù)庫(kù)只存儲(chǔ)視圖的定義 視圖相當(dāng)于一個(gè)虛表 用戶(hù)可以在視圖上在定義視圖 索引 為快速訪(fǎng)問(wèn)數(shù)據(jù) 而在包含數(shù)據(jù)的表中增加的一種組織 分為聚簇索引和非聚簇索引 指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織 例如 人事檔案 員工姓名 地址 電話(huà)號(hào)碼 社會(huì)安全號(hào) 工資 其中只有姓名 地址 電話(huà)號(hào)碼是公開(kāi)信息 可以生成不包含社會(huì)安全號(hào)和工資的視圖 對(duì)表和視圖設(shè)置不同的權(quán)限 2020 3 4 8 4 1查詢(xún)語(yǔ)句 4 1 1基本查詢(xún)4 1 2使用列表達(dá)式4 1 3WHERE從句的進(jìn)一步使用4 1 4數(shù)據(jù)匯總4 1 5連接查詢(xún)4 1 6嵌套查詢(xún)4 1 7聯(lián)合查詢(xún)4 1 8使用系統(tǒng)內(nèi)置函數(shù)的查詢(xún) 2020 3 4 9 SQL的核心是數(shù)據(jù)查詢(xún) 對(duì)于數(shù)據(jù)庫(kù)的查詢(xún)操作是通過(guò)SELECT查詢(xún)命令實(shí)現(xiàn)的 它的基本形式由SELECT FROM WHERE查詢(xún)塊組成 多個(gè)查詢(xún)塊可以嵌套執(zhí)行 2020 3 4 10 語(yǔ)句格式SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 指定要顯示的屬性列 指定查詢(xún)對(duì)象 指定查詢(xún)條件 查詢(xún)結(jié)果按指定列的值分組 篩選出只有滿(mǎn)足指定條件的組 對(duì)查詢(xún)結(jié)果表按指列值的升序或降序排序 2020 3 4 11 4 1 1基本查詢(xún) 1 簡(jiǎn)單的無(wú)條件查詢(xún) 例 Select fromstu info 把stu info表中的所有記錄的所有屬性查詢(xún)出來(lái) 2020 3 4 12 SELECT FROMSTU INFOWHEREXSH 06 4 1 1基本查詢(xún) 2 簡(jiǎn)單的條件查詢(xún)例4 2 從edu d的數(shù)據(jù)庫(kù)表stu info中查找包含所有列的土建學(xué)院 學(xué)院代號(hào)xsh為06 的學(xué)生名單 字符或字符串用單引號(hào)引起來(lái) 相當(dāng)于選擇運(yùn)算 2020 3 4 13 例4 3 從數(shù)據(jù)庫(kù)EDU INFO中查詢(xún)土建學(xué)院 XSH 06 的學(xué)生的學(xué)號(hào) 姓名 性別 學(xué)院代號(hào)信息 SELECTXH XM XBM XSHFROMSTU INFOWHEREXSH 06 3 查詢(xún)語(yǔ)句中投影運(yùn)算的實(shí)現(xiàn) 2020 3 4 14 4 1 2使用列表達(dá)式 查詢(xún)指定的列 除了可以使用 或者屬性列表以外 還可以使用列表達(dá)式 SELECTXH KCH JSH 150成績(jī) KSCJ 1 50FROMXK 利用列表達(dá)式實(shí)現(xiàn)不同列的連接 例4 5SELECT 學(xué)號(hào) XH XM XSH BHFROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 在SQLServer2000系統(tǒng)的企業(yè)管理器中運(yùn)行后系統(tǒng)自動(dòng)將語(yǔ)句的寫(xiě)法改成了 SELECT 學(xué)號(hào) ASEXPR1 XH XM XSHASEXPR2 BHFROMSTU INFOWHERE XBM 男 ORDERBYXHDESC 2020 3 4 16 注意 更改的是查詢(xún)結(jié)果顯示的列標(biāo)題 這是列的別名 而不是更改了數(shù)據(jù)庫(kù)表或視圖的列標(biāo)題 2 修改查詢(xún)結(jié)果的列標(biāo)題 例如 改寫(xiě)例4 5SELECTXHAS學(xué)號(hào) XM XSHAS姓名和學(xué)院代碼 BHAS班級(jí)FROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 2020 3 4 17 另一種形式 SELECT學(xué)號(hào) XH 姓名和學(xué)院代碼 XM XSH 班級(jí) BHFROMSTU INFOWHEREXBM 男 ORDERBYXHDESC 注意 當(dāng)自定義的列標(biāo)題中含有空格時(shí) 必須用引號(hào)將標(biāo)題括起來(lái) 2020 3 4 18 采用別名的目的 數(shù)據(jù)庫(kù)表中的列名用英文字母表示更方便 但是為了在查詢(xún)時(shí) 讓用戶(hù)看到的結(jié)果更直觀 所以可以采用中文別名 2020 3 4 19 查詢(xún)工作的關(guān)鍵就在通過(guò)分析怎樣把實(shí)際問(wèn)題中的自然語(yǔ)言描述轉(zhuǎn)化為1 從哪個(gè)表中查詢(xún)即 from后的一項(xiàng)2 要查詢(xún)哪些列即 select后的一項(xiàng)3 要查詢(xún)的條件即 where后的一項(xiàng) 完成查詢(xún)工作的三個(gè)關(guān)鍵步驟 第3步最復(fù)雜 4 1 3WHERE從句的進(jìn)一步使用 WHERE從句中 可以使用一個(gè)或者多個(gè)邏輯表達(dá)式限制查詢(xún)數(shù)據(jù)的范圍 表4 1常用查詢(xún)條件一覽表 2020 3 4 21 1 表達(dá)式比較 例如 SELECT FROMXKWHEREKSCJ 60 SELECTXH學(xué)號(hào) KCH課程號(hào) KSCJ成績(jī)FROMXKWHEREKSCJBETWEEN60AND70 2020 3 4 22 3 確定集合 例4 7 查詢(xún)應(yīng)電0601 機(jī)專(zhuān)0601班的學(xué)生的信息 SELECT學(xué)號(hào) 姓名 班級(jí)FROMmingdanWHERE班級(jí)IN 應(yīng)電0601 機(jī)專(zhuān)0601 當(dāng)與指定的值表中的任何一個(gè)匹配時(shí) 返回TRUE 否則返回FALSE 2020 3 4 23 4 字符匹配 用NOTLIKE和LIKE與通配符 和 搭配 其運(yùn)算對(duì)象可以是CHAR VARCHAR TEXT NTEXT DATETIME和SMALLDATETIME類(lèi)型的數(shù)據(jù) 表示任意長(zhǎng)度的字符串 表示人一單個(gè)字符 例如 1 SELECT FROMmingdanWHERE班級(jí)LIKE 應(yīng) 2 SELECT FROMmingdanWHERE姓名LIKE 紅 2020 3 4 24 5 涉及空值的查詢(xún) 例如 SELECT FROMmingdanWHERE班級(jí)ISNULL 6 多重條件查詢(xún) 例如 SELECT FROMmingdanWHERE班級(jí)LIKE 應(yīng) AND姓名LIKE 紅 2020 3 4 25 一般格式 SELECT列名1 列名2 FROM表名WHERE條件表達(dá)式ORDERBY列名1 ASC DESC 列名2 ASC DESC 升序 默認(rèn) 降序 4 1 4 設(shè)定排序條件 2020 3 4 26 例4 4 從數(shù)據(jù)庫(kù)EDU D的表STU INFO中查詢(xún)建9809班學(xué)生情況并按學(xué)號(hào)排序 SELECT FROMSTU INFOWHEREBH 建9809 ORDERBYXH SELECT FROMSTU INFOWHEREBH 建9809 ORDERBYZYH XHDESC 按多列排序 先按專(zhuān)業(yè)升序排列 然后同一專(zhuān)業(yè)的記錄按學(xué)號(hào)進(jìn)行降序排列 注意 1 ORDERBY子句不改變基本表中行或者列的順序 只改變查詢(xún)結(jié)果的排列順序 2 ORDERBY子句制定排序的列必須出現(xiàn)在SELECT子句的列表達(dá)式中 3 排序是查詢(xún)語(yǔ)句的最后一步工作 所以要把ORDERBY子句一般放在查詢(xún)語(yǔ)句的最后 2020 3 4 28 在要查詢(xún)的屬性名前加DISTINCT 以去掉結(jié)果中重復(fù)的值 例 SELECTDISTINCT班級(jí)FROMmingdan 注意 在一個(gè)SELECT語(yǔ)句中 DISTINCT只能出現(xiàn)一次 并且DISTINCT必須寫(xiě)在所有列名之前 5 限定重復(fù)記錄 2020 3 4 29 4 1 4數(shù)據(jù)匯總 1 聚合函數(shù) 表4 2聚合函數(shù)一覽表 表示去掉結(jié)果中的重復(fù)行 指定ALL選項(xiàng)或不指定則表示保留重復(fù)行 2020 3 4 30 例4 8 查詢(xún)學(xué)生總數(shù)SELECTCOUNT AS學(xué)生數(shù)FROMmingdan或者SELECTCOUNT XH FROMmingdan 例4 9 已知XK中高等數(shù)學(xué)的代號(hào)是090101 查詢(xún)學(xué)生選修高等數(shù)學(xué)的平均成績(jī)SELECTAVG KSCJ AS 高等數(shù)學(xué)平均成績(jī) FROMXKWHEREKCH 090101 因?yàn)閄H是該表的主鍵 不允許有空值 查詢(xún)每個(gè)班的學(xué)生人數(shù) 2020 3 4 31 例4 10 查詢(xún)學(xué)生選修高等數(shù)學(xué)的最高分和最低分SELECTMAX KSCJ AS 高等數(shù)學(xué)最高分 MIN KSCJ AS 高等數(shù)學(xué)最低分 FROMXKWHEREKCH 090101 注意 在查詢(xún)中 除COUNT 外 所有的聚合函數(shù)都不包括取值為空的行 2020 3 4 32 2 GROUPBY子句 GROUPBY子句用于對(duì)表或視圖中數(shù)據(jù)的查詢(xún)結(jié)果按某一列或多列值分組 值相等的分為一組 2 GROUPBY子句 例4 11 在表mingdan中查詢(xún)各班的學(xué)生人數(shù) SELECT班級(jí) COUNT XH AS人數(shù)FROMmingdanGROUPBY班級(jí) 注意 SELECT子句的列表中只能包含在GROUPBY中指出的列或在聚合函數(shù)中指定的列 GROUPBY子句用于對(duì)表或視圖中數(shù)據(jù)的查詢(xún)結(jié)果按某一列或多列值分組 值相等的分為一組 2020 3 4 33 2 GROUPBY子句 舉例 從表stu中統(tǒng)計(jì)xsh 0015 中男生和女生的人數(shù) Selectcount fromstuwherexsh 0015 groupbyxb 還可以按多個(gè)字段分組 思考 統(tǒng)計(jì)每個(gè)學(xué)院的男生和女生的人數(shù) 2020 3 4 34 例4 12 在表STU INFO上產(chǎn)生一個(gè)結(jié)果集 包括每個(gè)專(zhuān)業(yè)的男生 女生人數(shù) SELECTZYH XBM COUNT AS人數(shù)FROMSTU INFOGROUPBYZYH XBM 2020 3 4 35 3 HAVING短語(yǔ) HAVING短語(yǔ)指定組或聚合的條件 只有滿(mǎn)足條件的組才被選出來(lái) HAVING通常與GROUPBY子句一起使用 注意與WHERE子句區(qū)分 SELECTXH COUNT FROMXK GROUPBYXH HAVINGCOUNT 3 例4 15查詢(xún)選修了3門(mén)以上課程的學(xué)生及選課數(shù) 2020 3 4 36 例4 16 只查詢(xún)選修了3門(mén)以上課程的學(xué)號(hào)前四位是20011的學(xué)生學(xué)號(hào)及選課數(shù) SELECTXH COUNT FROMXKWHEREXHLIKE 2001 GROUPBYXHHAVINGCOUNT 3 2020 3 4 37 Having短語(yǔ)指定選擇組的條件 只有滿(mǎn)足條件的組才被選出來(lái) Where子句從基本表中選擇滿(mǎn)足條件的元組 having選擇滿(mǎn)足條件的組 2020 3 4 38 若一個(gè)查詢(xún)同時(shí)涉及兩個(gè)或兩個(gè)以上的表 則稱(chēng)為連接查詢(xún) 連接查詢(xún)中用來(lái)連接兩個(gè)關(guān)系的條件稱(chēng)為連接條件或連接謂詞 一般格式為 連接謂詞中的列名稱(chēng)為連接字段 4 2 5連接查詢(xún) 例如 xk xh stu info xh 連接查詢(xún)的from后面跟多個(gè)表 2020 3 4 39 連接查詢(xún)中用來(lái)連接兩個(gè)關(guān)系的條件稱(chēng)為連接條件或連接謂詞 格式 其中比較運(yùn)算符主要有 當(dāng)連接運(yùn)算符為 時(shí) 叫等值連接 使用其它運(yùn)算符稱(chēng)為非等值連接 連接謂詞中的列名稱(chēng)為連接字段 在SELECT中連接條件放在where后面 2020 3 4 40 例 查詢(xún)每個(gè)學(xué)生及其選課情況 Selectstu info xk Fromstu info xkWherestu info xh xk xh 2020 3 4 41 例 查詢(xún)物理012班每個(gè)學(xué)生及其選修課程的情況 SELECTstu info xk FROMstu info xkWHEREstu info xh xk xhandbh 物理012 復(fù)合條件連接 2020 3 4 42 例4 18 查詢(xún)選修了有機(jī)化學(xué)這門(mén)課程的學(xué)生的姓名 SELECTXMFROMSTU INFO XK GCOURSEWHEREGCOURSE KM 有機(jī)化學(xué) ANDGCOURSE KCH XK KCHANDXK XH STU INFO XH 說(shuō)明 1 連接查詢(xún)涉及的所有表名都放在FROM子句后面 2 連接條件放在WHERE子句中 3 如果屬性列名在參加連接的各表中是唯一的 可以省略表名前綴 如果屬性列名是兩個(gè)表共同的屬性 則一定要加表名前綴 2020 3 4 43 4 在書(shū)寫(xiě)連接查詢(xún)時(shí) 為了簡(jiǎn)化 可以為表名取別名 別名應(yīng)該簡(jiǎn)單 別名只在本次查詢(xún)有效 例 4 18 SELECTXMFROMSTU INFOS XKX GCOURSECWHEREC KM 有機(jī)化學(xué) ANDC KCH X KCHANDX XH S XH 2020 3 4 44 4 1 6嵌套查詢(xún) 例4 19 在STU info表中查詢(xún)選修了課程編號(hào)為090201的學(xué)生姓名 分析 查詢(xún)所要求的結(jié)果出自一個(gè)關(guān)系 STU info 但相關(guān)的條件卻涉及多個(gè)關(guān)系 STU info和XK SELECTXMFROMSTU INFOWHERE XHIN SELECTXHFROMXKWHEREKCH 090201 一個(gè)SELECT FROM WHERE語(yǔ)句稱(chēng)為一個(gè)查詢(xún)塊 將一個(gè)查詢(xún)塊嵌套在另一個(gè)查詢(xún)塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢(xún)稱(chēng)為嵌套查詢(xún) 2020 3 4 45 說(shuō)明 1 這個(gè)查詢(xún)首先執(zhí)行小括號(hào)里的內(nèi)部查詢(xún) 得到一個(gè)結(jié)果集 再判斷外部查詢(xún)的列是否屬于這個(gè)集合 2 關(guān)鍵字IN的前面只能有一個(gè)列 這個(gè)列必須與內(nèi)部查詢(xún)結(jié)果集里的各個(gè)值含義相同 數(shù)據(jù)類(lèi)型兼容 SELECTXMFROMSTU INFOWHERE XHIN SELECTXHFROMXKWHEREKCH 090201 2020 3 4 46 例4 20查詢(xún)與劉玉濤在同一個(gè)班學(xué)習(xí)的學(xué)生 SELECTXH XM BHFROMSTU INFOWHERE BHIN 1 帶有in謂詞的子查詢(xún) 同一個(gè)學(xué)校的劉玉濤可能重名 而且可能不同班 SELECTBHFROMSTU INFOWHEREXM 劉玉濤 2020 3 4 47 2 帶有比較運(yùn)算符的子查詢(xún) 例4 21查詢(xún)與學(xué)號(hào)2000029001的學(xué)生在同一個(gè)班學(xué)習(xí)的學(xué)生 按學(xué)號(hào)排序 SELECTXH XM BHFROMSTU INFOWHERE BH ORDERBYXH 明確內(nèi)查詢(xún)結(jié)果是唯一值時(shí)可以使用 SELECTBHFROMSTU INFOWHEREXH 2000029001 2020 3 4 48 帶有比較運(yùn)算符的子查詢(xún) 舉例 從mingdan表中查詢(xún)成績(jī)最高的同學(xué)的學(xué)號(hào) 姓名和成績(jī) Select學(xué)號(hào) 姓名 成績(jī)frommingdanWhere成績(jī) selectmax 成績(jī) frommingdan Select學(xué)號(hào) max 成績(jī) frommingdan Select學(xué)號(hào) max 成績(jī) frommingdangroupby學(xué)號(hào) 2020 3 4 49 3 帶有ANY SOME ALL的比較子查詢(xún) 例4 22查詢(xún)其他班級(jí)中比材物2k01班某個(gè)學(xué)生年齡小的學(xué)生的姓名和年齡 SELECTXM NLFROMSTU INFOWHERENL 材物2k01 同義字 SELECTNLFROMSTU INFOWHEREBH 材物2k01 2020 3 4 50 4 帶有EXISTS謂詞的子查詢(xún) EXISTS是測(cè)試子查詢(xún)是否有數(shù)據(jù)行返回 如果有則返回TRUE 否則返回FALSE NOTEXISTS則相反 當(dāng)結(jié)果表為空時(shí) 才返回TRUE 例4 23查詢(xún)選修了高等數(shù)學(xué) KCH 090101 的學(xué)生的姓名等信息 SELECTXM XBM BHFROMSTU INFOWHEREEXISTS SELECT FROMXKWHEREXH STU INFO XHANDKCH 090101 不需要明確指定列名 這個(gè)子查詢(xún)不一定會(huì)執(zhí)行完 只要發(fā)現(xiàn)匹配條件成立 就退出子查詢(xún) 2020 3 4 51 例4 24查詢(xún)沒(méi)有選修高等數(shù)學(xué)的學(xué)生的姓名等信息 SELECTXM XBM BHFROMSTU INFOWHERENOTEXISTS SELECT FROMK2001WHEREXH STU INFO XHANDKM 高等數(shù)學(xué) 2020 3 4 52 例 在stu info表中查詢(xún)選修了090101號(hào)課程的學(xué)生姓名SELECTxmFROMstu infoWHERE XHIN SELECTXHFROMxkWHERE kch 090101 當(dāng)查詢(xún)的信息涉及一個(gè)關(guān)系時(shí) 嵌套查詢(xún)和連接查詢(xún)可互換 Selectxmfromstu info xkWherestu info xh xk xhandkch 090101 2020 3 4 53 4 1 7聯(lián)合查詢(xún) 多個(gè)SELECT語(yǔ)句的結(jié)果可進(jìn)行集合操作 SQLServer的Transact SQL語(yǔ)言只提供UNION 并 運(yùn)算符實(shí)現(xiàn)并操作 語(yǔ)法格式 SELECT 1UNION ALL SELECT 2 UNION ALL SELECT 3 進(jìn)行并運(yùn)算的子查詢(xún) 2020 3 4 54 例4 25 有兩個(gè)數(shù)據(jù)庫(kù)表STUFR和STUIS 其結(jié)構(gòu)完全一樣 分別存放外語(yǔ)學(xué)院和信息學(xué)院的學(xué)生基本信息 現(xiàn)在要查詢(xún)兩個(gè)學(xué)院女生的學(xué)號(hào) 姓名 SELECTXH XMFROMSTUFRWHEREXB 女 UNIONSELECTXH XMFROMSTUISWHEREXB 女 2020 3 4 55 說(shuō)明 1 使用UNION運(yùn)算符進(jìn)行聯(lián)合查詢(xún)時(shí) 要保證各個(gè)SELECT語(yǔ)句的目標(biāo)列表達(dá)式數(shù)量相等 排列順序相互一一對(duì)應(yīng) 數(shù)據(jù)類(lèi)型必須兼容 2 UNION之后如果使用了ALL選項(xiàng) 則顯示所有的包括重復(fù)的行 如果沒(méi)有使用ALL選項(xiàng) 則重復(fù)行只顯示一行 3 UNION操作常用于歸檔數(shù)據(jù) 例如歸檔各個(gè)基層部門(mén)的數(shù)據(jù)等 運(yùn)行時(shí)將查詢(xún)的數(shù)據(jù)合并到第一個(gè)表中 4 也可以對(duì)結(jié)果進(jìn)行排序或者分組匯總 這是必須把ORDERBY子句或GROUPBY子句放在最后一個(gè)SELECT語(yǔ)句的后面 并且必須是針對(duì)第一個(gè)SELECT語(yǔ)句的列進(jìn)行的排序或分組 5 使用UNION連接的所有SELECT語(yǔ)句也可以使用同一張表 此時(shí)UNION運(yùn)算符可以用OR運(yùn)算符來(lái)代替 2020 3 4 56 例4 26 將上個(gè)例子中的兩個(gè)數(shù)據(jù)庫(kù)表STUFR和STUIS的數(shù)據(jù)合并到結(jié)構(gòu)相同的全校的學(xué)生數(shù)據(jù)表STU中去 SELECT FROMSTUUNIONALLSELECT FROMSTUFRUNIONALLSELECT FROMSTUIS 如何將查詢(xún)的結(jié)果保存在當(dāng)前數(shù)據(jù)庫(kù)新建的表NEWSTU中 SELECT INTONEWSTUFROMSTUFRUNIONALLSELECT FROMSTUIS 2020 3 4 57 例4 27 查詢(xún)01學(xué)院的學(xué)生及所有的本科生 SELECT FROMSTU INFOWHEREXSH 01 UNIONSELECT FROMSTU INFOWHEREPYCCM 本科 改寫(xiě)SELECT FROMSTU INFOWHEREXSH 01 ORPYCCM 本科 2020 3 4 58 4 1 8使用系統(tǒng)內(nèi)置函數(shù)的查詢(xún) T SQL提供了三種系統(tǒng)內(nèi)置函數(shù) 行集函數(shù) 聚合函數(shù)和標(biāo)量函數(shù) 其中聚合函數(shù)在4 1 4節(jié)進(jìn)行了介紹 1 數(shù)學(xué)函數(shù) 對(duì)SQLServer提供的數(shù)字?jǐn)?shù)據(jù) decimal integer float real money smallmoney smallint和tinyint 進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果 2020 3 4 59 2 RAND函數(shù)語(yǔ)法格式 RAND seed 功能 返回0 1之間的一個(gè)隨機(jī)值 1 ABS函數(shù)語(yǔ)法格式 ABS 數(shù)值型表達(dá)式 功能 返回給定數(shù)值表達(dá)式的絕對(duì)值 參數(shù)seed 種子 為整型表達(dá)式 2020 3 4 60 1 ASC 函數(shù)語(yǔ)法格式 ASC 字符型表達(dá)式 功能 返回字符表達(dá)式最左端字符的ASC 值 2 CHAR函數(shù)語(yǔ)法格式 CHAR 0 255之間的整數(shù) 功能 將ASC 轉(zhuǎn)換為字符 2020 3 4 61 3 LEFT函數(shù)語(yǔ)法格式 LEFT 字符型表達(dá)式 整型表達(dá)式 功能 返回從字符串左邊開(kāi)始指定個(gè)數(shù)的字符 例4 28 查詢(xún)學(xué)號(hào)最左邊的4個(gè)字符 SELECTLEFT XH 4 FROMSTU INFO 2 字符串處理函數(shù) 2020 3 4 62 4 LTRIM函數(shù)語(yǔ)法格式 LTRIM 字符型表達(dá)式 功能 刪除字符型表達(dá)式字符串最左邊的空格 并返回字符串 5 REPLACE函數(shù)語(yǔ)法格式 REPLACE 字符表達(dá)式1 字符表達(dá)式2 字符表達(dá)式3 功能 用字符表達(dá)式3替換字符表達(dá)式1中包含的字符表達(dá)式2 并返回替換后的表達(dá)式 2020 3 4 63 例4 29已知某學(xué)校學(xué)號(hào) XH char 的前4位是入學(xué)年 第5位是性別代碼 后5位是流水號(hào) 如某女生的學(xué)號(hào) 2002030101 在學(xué)生基本情況數(shù)據(jù)庫(kù)表STU INFO中查詢(xún)所有學(xué)生的學(xué)號(hào) 姓名 入學(xué)年 性別信息 6 SUBSTRING函數(shù)語(yǔ)法格式 SUBSTRING expression 整型 整型 功能 返回expression中指定的部分?jǐn)?shù)據(jù) 可以是字符串 二進(jìn)制串 text image字段或表達(dá)式 指定從expression的第幾個(gè)字節(jié)開(kāi)始 length指定要返回的字節(jié)數(shù) SELECTXH XM LEFT XH 4 AS入學(xué)年 SUBSTRING XH 5 1 AS性別碼FROMSTU INFOORDERBYXH 2020 3 4 64 7 STR函數(shù)語(yǔ)法格式 STR FLOATL類(lèi)型的表達(dá)式 length decimal 功能 將數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù) 返回值的總長(zhǎng)度 包括小數(shù)點(diǎn) 制定小數(shù)點(diǎn)右邊的位數(shù) 2020 3 4 65 3 系統(tǒng)函數(shù) 系統(tǒng)函數(shù)用于對(duì)SQLServer中的值 對(duì)象和設(shè)置進(jìn)行操作并返回有關(guān)信息 1 CASE函數(shù) 簡(jiǎn)單的CASE函數(shù)語(yǔ)法格式 CASE輸入表達(dá)式WHEN比較表達(dá)式THEN結(jié)果表達(dá)式 ELSE表達(dá)式END功能 計(jì)算輸入表達(dá)式的值 與每一個(gè)WHEN的比較表達(dá)式的值比較 如果相等 則返回對(duì)應(yīng)的結(jié)果表達(dá)式的值 否則 返回ELSE之后的表達(dá)式的值 如果省略了ELSE 則返回NULL值 2020 3 4 66 例4 30 在STU INFO中查詢(xún)03學(xué)院學(xué)生的學(xué)號(hào) 姓名 性別 并將性別分別轉(zhuǎn)換成 男生 女生 SELECTXH XM XBM XBM CASE 男 WHENXBMTHEN 男生 ELSE 女生 ENDFROMSTU INFOWHEREXSH 03 2020 3 4 67 CASE搜索函數(shù)語(yǔ)法格式 CASEWHEN條件1THEN表達(dá)式1WHEN條件2THEN表達(dá)式2 ELSE表達(dá)式END功能 系統(tǒng)將查詢(xún)出的結(jié)果進(jìn)行判斷 當(dāng)滿(mǎn)足WHEN的某個(gè)條件時(shí) 則將該結(jié)果顯示為T(mén)HEN之后的表達(dá)式的值 如果沒(méi)有滿(mǎn)足的條件 則顯示ELSE之后的表達(dá)式的值 如果沒(méi)有指定ELSE子句時(shí) 返回NULL值 2020 3 4 68 例4 31 查詢(xún)成績(jī)表XK中選修了 090101 課程的學(xué)生的學(xué)號(hào) 5分制成績(jī) SELECTXH 5分制成績(jī) CASEWHENKSCJ 60ANDKSCJ 70ANDKSCJ 80ANDKSCJ 90THEN 優(yōu)秀 ENDFROMXKWHEREKCH 090101 注意 這個(gè)查詢(xún)改變的僅僅是查詢(xún)出的結(jié)果 而基礎(chǔ)表中的數(shù)據(jù)并沒(méi)有發(fā)生轉(zhuǎn)換 2020 3 4 69 2 CAST函數(shù)語(yǔ)法格式 CAST 表達(dá)式AS數(shù)據(jù)類(lèi)型 功能 將表達(dá)式的值轉(zhuǎn)換為數(shù)據(jù)類(lèi)型參數(shù)所指定的類(lèi)型 例4 32 查詢(xún)成績(jī)表K2004 其中考試成績(jī) KSCJ XH XM等數(shù)據(jù)類(lèi)型都是CHAR 現(xiàn)要查詢(xún)考試成績(jī)?cè)?0分到60之間的學(xué)生及成績(jī) 并將成績(jī)加10分顯示 SELECTXH XM KSCJ CAST KSCJASFLOAT 10AS加分后成績(jī)FROMK2004WHEREKSCJLIKE 5 2020 3 4 70 3 日期時(shí)間函數(shù)GETDATE語(yǔ)法格式 GETDATE 功能 按照SQLServer標(biāo)準(zhǔn)內(nèi)部格式返回當(dāng)前的系統(tǒng)日期和時(shí)間 返回值數(shù)據(jù)類(lèi)型為datetime型 4 年 月 日函數(shù)語(yǔ)法格式 YEAR date MONTH date DAY date 其中參數(shù)date是數(shù)據(jù)類(lèi)型為datetime或smalldatetime的表達(dá)式 返回值的數(shù)據(jù)類(lèi)型為int型 2020 3 4 71 4 2數(shù)據(jù)更新 主要包括對(duì)數(shù)據(jù)庫(kù)表的數(shù)據(jù)進(jìn)行插入 修改 刪除操作 INSERTUPDATEDELETE 2020 3 4 72 4 2 1插入數(shù)據(jù) 插入數(shù)據(jù)的操作有兩種形式 1 使用VALUES子句向數(shù)據(jù)庫(kù)的基本表表一次插入一個(gè)元組 2 插入SELECT子查詢(xún)的結(jié)果 一次插入一批數(shù)據(jù)記錄 2020 3 4 73 1 插入單個(gè)元組 語(yǔ)法格式 INSERTINTO VALUES 說(shuō)明 1 屬性列與常量必須一一對(duì)應(yīng) 數(shù)據(jù)類(lèi)型要一致 2 在基本表結(jié)構(gòu)定義中未說(shuō)明為NOTNULL的屬性列 如果沒(méi)有出現(xiàn)在INTO子句后 這些列將取空值 已經(jīng)說(shuō)明為NOTNULL的屬性列 則必須出現(xiàn)在INTO子句后面 3 如果INTO子句后面沒(méi)有指定任何列 則VALUES子句后面的常量個(gè)數(shù)必須與基本表中列的個(gè)數(shù)相等 且類(lèi)型 順序一致 2020 3 4 74 例 在STU COU數(shù)據(jù)庫(kù)中 INSERTINTOSTU XH XM XB XSH NL VALUES 20050902090 王東方 男 0019 22 等價(jià)于 INSERTINTOSTUVALUES 20050902090 王東方 男 0019 22 XH XM XB XSH NL 表中所有的列 2020 3 4 75 2 插入子查詢(xún)結(jié)果 語(yǔ)法格式 INSERT INTO 表名 列名1 列名N SELECT兼容列名1 兼容列名NFROM兼容表名WHERE邏輯表達(dá)式 2020 3 4 76 例 4 33在STU表中查詢(xún)出女生的學(xué)生信息存放在STUIS表中 INSERTINTOSTUISSELECTXH XM XB XSH NLFROMSTUWHEREXB 女 INSERTintoSTUISSELECT FROMSTUWHEREXB 女 或者 2020 3 4 77 4 2 2修改數(shù)據(jù) 修改數(shù)據(jù)主要是對(duì)數(shù)據(jù)庫(kù)表中一個(gè)或多個(gè)元組某個(gè)或某些屬性的值進(jìn)行更改 語(yǔ)法格式 UPDATESET WHERE 2020 3 4 78 1 修改一個(gè)元組的值 例如 UPDATESTUSETXSH 機(jī)械 NL NL 1WHEREXH 20000501022 2 修改多個(gè)元組的值 例 將所有課程的學(xué)分改為2分 UPDATECOURSESETCCREDIT 2 例 將女生年齡增加1歲 UPDATESTUSETNL NL 1WHEREXB 女 2020 3 4 79 3 帶子查詢(xún)的修改語(yǔ)句 4 34 將選修了高等數(shù)學(xué)的90分以下的學(xué)生的成績(jī)加10分 從edu d庫(kù)中的xk數(shù)據(jù)表中修改 UPDATEXKSETKSCJ KSCJ 10WHEREKCH SELECTKCHFROMGCOURSEWHEREKM 高等數(shù)學(xué) ANDKSCJ 90 建議 使用UPDATE對(duì)批量數(shù)據(jù)進(jìn)行修改前 先用SELECT語(yǔ)句將要修改的記錄查詢(xún)出來(lái) 仔細(xì)檢查無(wú)誤后 再進(jìn)行修改 2020 3 4 80 4 2 3刪除數(shù)據(jù) 語(yǔ)法格式 DELETEFROM WHERE 注意 DELETE命令刪除的是一個(gè)或多個(gè)元組 記錄 行 而不是某個(gè)行中個(gè)別列的值 不需要的某個(gè)行的個(gè)別列的值 只能用UPDATE命令修改成NULL值或空格符 不能被刪除 2020 3 4 81 1 刪除一個(gè)元組 例如 DELETEFROMSTU INFOWHEREXH 2003050601 2 刪除多個(gè)元組 例如 DELETEFROMSTU INFOWHEREBH 應(yīng)0203 2020 3 4 82 3 帶子查詢(xún)的刪除語(yǔ)句 例4 35 將信息學(xué)院學(xué)生的選課信息全部刪除 注意 為避免誤操作 在日常工作中 通常操作刪除語(yǔ)句時(shí) 要先查詢(xún)要?jiǎng)h除的數(shù)據(jù) 確認(rèn)無(wú)誤后 再將SELECT 部分改成DELETE進(jìn)行刪除操作 DELETEFROMXK STU INFOWHERESTU INFO XSH 12 ANDSTU INFO XH XK XH SELECTXK FROMXK STU INFOWHERESTU INFO XSH 12 ANDSTU INFO XH XK XH SELECT FROMXKWHERE 12 SELECTXSHFROMSTU INFOWHERESTU INFO XH XK XH DELETEFROMXKWHERE 12 SELECTXSHFROMSTU INFOWHERESTU INFO XH XK XH 2020 3 4 83 4 3數(shù)據(jù)定義 SQL的數(shù)據(jù)定義語(yǔ)句是對(duì)數(shù)據(jù)庫(kù)表 視圖 索引等的結(jié)構(gòu)和屬性進(jìn)行定義 常見(jiàn)的操作方式如下表 2020 3 4 84 4 3 1定義基本表 一 語(yǔ)法格式 CREATETABLE 數(shù)據(jù)庫(kù)名 默認(rèn)為當(dāng)前數(shù)據(jù)庫(kù) 要定義的基本表的名字 組成該表的各個(gè)屬性 列 涉及相應(yīng)屬性列的完整性約束條件 涉及一個(gè)或多個(gè)屬性列的完整性約束條件 2020 3 4 85 常用完整性約束主碼約束 PRIMARYKEY唯一性約束 UNIQUE非空值約束 NOTNULL參照完整性約束FOREIGNKEY REFERENCESCHECK約束CHECK默認(rèn)值DEFAULT標(biāo)識(shí)列IDENTITY seed inctement 公式AS 2020 3 4 86 二 數(shù)據(jù)類(lèi)型 2020 3 4 87 浮點(diǎn)類(lèi)型 2020 3 4 88 字符類(lèi)型 2020 3 4 89 二進(jìn)制數(shù)據(jù)類(lèi)型 2020 3 4 90 時(shí)間類(lèi)型 2020 3 4 91 貨幣類(lèi)型 2020 3 4 92 例1 建立一個(gè) 學(xué)生 表Student 它由學(xué)號(hào)Sno 姓名Sname 性別Ssex 年齡Sage 所在系Sdept五個(gè)屬性組成 其中學(xué)號(hào)不能為空 值是唯一的 并且姓名取值也唯一 CREATETABLEStudent SnoCHAR 5 NOTNULLUNIQUE SnameCHAR 20 UNIQUE SsexCHAR 1 SageINT SdeptCHAR 15 2020 3 4 93 例2 建立一個(gè) 學(xué)生選課 表SC 它由學(xué)號(hào)Sno 課程號(hào)Cno 修課成績(jī)Grade組成 其中 Sno Cno 為主碼 CREATETABLESC SnoCHAR 5 CnoCHAR 3 Gradeint Primarykey Sno Cno 2020 3 4 94 例4 36 定義一個(gè)學(xué)生的成績(jī)表k2001 CREATETABLEk2001 表名為K2001 XHvarchar 12 NOTNULL XH 學(xué)號(hào) 列 不為空 XMvarchar 8 NULL 姓名 KCHvarchar 8 NOTNULL 課程代碼 KSCJvarchar 5 NULL 考試成績(jī)8 KKNYvarchar 5 NULL 開(kāi)課時(shí)間 KCXFvarchar 5 NULL 課程學(xué)分 KMvarchar 30 NULL 課程名稱(chēng) KCFZvarchar 1 NULL 課程分組 JSMvarchar 8 NULL 任課教師 BZvarchar 18 NULL 備注 2020 3 4 95 例4 37 創(chuàng)建表n jobs CREATETABLEn jobs job idsmallintIDENTITY 1 1 PRIMARYKEYjob descvarchar 50 NOTNULLDEFAULT 新部門(mén) 暫無(wú)職位 min lvltinyintNOTNULLCHECK min lvl 12 max lvltinyintNOTNULLCHECK max lvl 250 是表示為PRIMARYKEY約束創(chuàng)建聚集關(guān)鍵字 表示新列是標(biāo)識(shí)列 必須同時(shí)指定種子seed和增量increment 或者二者都不指定 如果二者都未指定 則取默認(rèn)值 1 1 設(shè)置默認(rèn)值 默認(rèn)值可以是常量 NULL或系統(tǒng)函數(shù) 例4 38 創(chuàng)建表new employees CREATETABLEnew employee emp idchar 9 CONSTRAINTPK emp idPRIMARYKEYNONCLUSTEREDCONSTRAINTCK emp idCHECK emp idLIKE A Z A Z A Z 1 9 0 9 0 9 0 9 0 9 FM oremp idLIKE A Z A Z 1 9 0 9 0 9 0 9 0 9 FM 每個(gè)員工編號(hào)由三個(gè)字符打頭 然后是10000 99999的某個(gè)數(shù)字和代表性別的字母F或M fnamevarchar 20 NOTNULL minitchar 1 NULL lnamevarchar 30 NOTNULL job idsmallintNOTNULLDEFAULT1REFERENCESjobs job id job lvltinyintDEFAULT10 pub idchar 4 NOTNULLDEFAULT 9952 REFERENCESpublishers pub id hire datedatetimeNOTNULLDEFAULT getdate 使用了獲取當(dāng)前日期的函數(shù)作為默認(rèn)值 表示PRIMARYKEY NOTNULL UNIQUE FOREIGNKEY或CHECK約束定義的開(kāi)始 約束的名稱(chēng) 外鍵約束 2020 3 4 97 4 3 2修改基本表 語(yǔ)法結(jié)構(gòu) ALTERTABLE ADD 完整性約束 DROP COLUMN ALTERCOLUMN 增加新列和新的完整性約束條件 刪除指定的完整性約束條件 用于修改列名和數(shù)據(jù)類(lèi)型 2020 3 4 98 例4 43向Stu info表增加 入學(xué)時(shí)間 列 其數(shù)據(jù)類(lèi)型為日期型 ALTERTABLESTU INFOADDRXSJDATETIME 不論基本表中原來(lái)是否已有數(shù)據(jù) 新增加的列一律為空值 1 添加列 2020 3 4 99 例4 44 將教師表的XB列改為SMALLINT數(shù)據(jù)類(lèi)型 注意 修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù) 2 修改列 ALTERTABLEGTECHALTERCOLUMNXBSMALLINT 2020 3 4 100 3 刪除列 例4 45 刪除教師表中的性別一列 ALTERTABLEGTECHDROPCOLUMNXB 下面的列不能除去 被復(fù)制列 用在索引中的列 用在CHECK FOREIGNKEY UNIQUE或PRIMARYKEY約束中的列 有相關(guān)聯(lián)的默認(rèn)值 由DEFAULT關(guān)鍵字定義 的列或綁定到默認(rèn)對(duì)象的列 綁定到規(guī)則的列 2020 3 4 101 例 刪除數(shù)據(jù)庫(kù)表new employee中的emp id的檢查約束 ALTERTABLEnew employeeDROPCK emp id 4 刪除約束 2020 3 4 102 4 3 3刪除基本表 語(yǔ)法格式 DROPTABLE 2020 3 4 103 4 3 4視圖 視圖是關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)提供給用戶(hù)以多種角度觀察數(shù)據(jù)庫(kù)中數(shù)據(jù)的重要機(jī)制 視圖是從一個(gè)或多個(gè)基本表 或視圖 導(dǎo)出的表 是個(gè)虛表 數(shù)據(jù)庫(kù)只存放視圖的定義 不存放視圖對(duì)應(yīng)的數(shù)據(jù) 這些數(shù)據(jù)仍存放在原來(lái)的基本表中 所以 視圖的數(shù)據(jù)跟隨基本表的數(shù)據(jù)而變化 視圖一旦被定義 就可以被查詢(xún) 刪除 修改 再定義一個(gè)新的視圖 2020 3 4 104 補(bǔ)充說(shuō)明 視圖是一個(gè)虛擬表 其內(nèi)容由查詢(xún)定義 同真實(shí)的表一樣 視圖包含一系列帶有名稱(chēng)的列和行數(shù)據(jù) 但是 視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在 行和列數(shù)據(jù)來(lái)自由定義視圖的查詢(xún)所引用的表 并且在引用視圖時(shí)動(dòng)態(tài)生成 視圖允許我們把表結(jié)構(gòu)的細(xì)節(jié)封裝起來(lái) 這些表可能因你的應(yīng)用的進(jìn)化而變化 而我們卻可以通過(guò)視圖給用戶(hù)一個(gè)一致的接口 視圖幾乎可以在一個(gè)真正的表可以使用的任何地方使用 在其它視圖上面再建造視圖也是很常見(jiàn)的 2020 3 4 105 1 定義視圖 1 用企業(yè)管理器建立視圖 自學(xué) 2 用SQL數(shù)據(jù)定義語(yǔ)句createview命令建立視圖 語(yǔ)法格式 CREATEVIEW視圖名AS WITHCHECKOPTION 對(duì)視圖進(jìn)行UPDATE INSERT和DELETE操作時(shí)要保證更新 插入或刪除的行滿(mǎn)足視圖定義中的條件表達(dá)式 2020 3 4 106 注意問(wèn)題 1 只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建視圖 2 一個(gè)視圖最多可以引用1024個(gè)列 3 視圖的命名必須符合SQLServer中的標(biāo)識(shí)符的定義規(guī)則 4 可以將視圖建立在其他視圖或者引用視圖的過(guò)程之上 SQLServer2000中允許最多32層的視圖嵌套 5 不能將規(guī)則 默認(rèn)值定義綁定在視圖上 6 定義視圖的查詢(xún)語(yǔ)句中不能包括COMPUTE COMPUTEBY ORDERBY子句或是INTO等關(guān)鍵詞 7 在視圖中不能定義全文索引 但可以定義索引 8 不能創(chuàng)建臨時(shí)視圖 而且也不能在臨時(shí)表上創(chuàng)建視圖 2020 3 4 107 9 默認(rèn)狀態(tài)下 視圖中的列繼承他們?cè)诨碇械拿Q(chēng) 對(duì)于以下情況 在創(chuàng)建視圖時(shí)需要明確給出每一列的名稱(chēng) 視圖中的某些列來(lái)自表達(dá)式 函數(shù)或常量 視圖中兩個(gè)或多個(gè)列在不同表中具有相同的名稱(chēng) 希望在視圖中的列使用不同于基表中的列名時(shí) 2020 3 4 108 A 選擇列定義視圖 例 CREATEVIEWMYVIEW1ASSELECTEMP ID LNAME FNAMEFROMEMPLOYEE 定義了視圖后 可以和表一樣 使用SELECT語(yǔ)句訪(fǎng)問(wèn)它 2020 3 4 109 B 基于列的表達(dá)式定義視圖 例 CREATEVIEWMYVIEW2ASSELECTTITLE ADVANCE PRICE ROYALTY YTD SALESASNEWPRICEFROMTITLESWHEREPRICE 5 2020 3 4 110 C 選擇行定義視圖 例 CREATEVIEWMYVIEW3ASSELECT FROMTITLESWHERETITLELIKE SALES 2020 3 4 111 D 選擇行和列 例 CREATEVIEWMYVIEW4ASSELECTTITLE ID PUB ID TITLEFROMTITLESWHERETITLELIKE SALES 2020 3 4 112 E 基于多個(gè)表定義視圖 例 CREATEVIEW查詢(xún)化學(xué)院學(xué)生ASSELECTSTU INFO XH STU INFO XM STU INFO BH GDEPT XSM GFIED ZYMFROMSTU INFO GDEPT GFIEDW