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

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