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