SQL Server數(shù)據(jù)庫基礎(chǔ)課件第四單元 管理數(shù)據(jù)庫表中數(shù)據(jù)

第四單元管理數(shù)據(jù)庫表中數(shù)據(jù)任務(wù)4.1存儲數(shù)據(jù)任務(wù)4.2更新數(shù)據(jù)任務(wù)4.3刪除數(shù)據(jù)4.1.1情景描述4.1.2問題分析4.1.3解決方案4.1.4知識總結(jié)4.1.5應(yīng)用實踐任務(wù)4.1存儲數(shù)據(jù)學生管理數(shù)據(jù)庫用來存放具體數(shù)據(jù)的表創(chuàng)建好后，就可以在表中儲存數(shù)據(jù)了。如果一張表建立好后沒有任何數(shù)據(jù)，那它只是一個空的表結(jié)構(gòu)，不能起到任何實際作用。學生管理系統(tǒng)數(shù)據(jù)庫的開發(fā)人員，需要把課程有關(guān)信息添加到“課程”表中，添加了信息后，才可以根據(jù)應(yīng)用系統(tǒng)的需要對課程信息進行操作。4.1.1情景描述為了完成任務(wù)，開發(fā)人員需要完成以下任務(wù)：1．根據(jù)“課程”表的結(jié)構(gòu)，設(shè)計表的有關(guān)數(shù)據(jù)，如表4-1所示；2．使用插入數(shù)據(jù)的命令來完成記錄的存儲，并在SQLServer2008上執(zhí)行。4.1.2問題分析課程編號課程名稱課程性質(zhì)學分開課學期課程分類1數(shù)據(jù)結(jié)構(gòu)專業(yè)必修課4第三學期專業(yè)拓展2C語言程序設(shè)計專業(yè)必修課5第一學期專業(yè)拓展3公共英語公共必修課4第一學期素質(zhì)拓展4軟件工程專業(yè)選修課3第二學期專業(yè)拓展5經(jīng)濟學公共選修課2第四學期技能拓展6網(wǎng)頁設(shè)計專業(yè)必修課3第五學期專業(yè)拓展1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數(shù)據(jù)庫”文件夾下的數(shù)據(jù)庫“學生管理”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：4.1.3解決方案INSERTINTO課程

VALUES(1,'數(shù)據(jù)結(jié)構(gòu)','專業(yè)必修課',4,'第三學期','專業(yè)拓展')

INSERTINTO課程VALUES(2,'C語言程序設(shè)計','專業(yè)必修課',5,'第一學期','專業(yè)拓展')

INSERTINTO課程VALUES(3,'公共英語','公共必修課',4,'第一學期','素質(zhì)拓展')

INSERTINTO課程VALUES(4,'軟件工程','專業(yè)選修課',3,'第二學期','專業(yè)拓展')

INSERTINTO課程(課程編號,課程名稱,課程性質(zhì),課程分類,學分,開課學期)VALUES(5,'經(jīng)濟學','公共選修課','技能拓展',2,'第四學期')

INSERTINTO課程(課程編號,課程分類,課程名稱,學分,開課學期,課程性質(zhì))VALUES(6,'專業(yè)拓展','網(wǎng)頁設(shè)計',4,'第五學期','專業(yè)必修課')4．單擊工具欄上的【執(zhí)行】按鈕。使用INSERTINTO…VALUES命令存儲數(shù)據(jù)使用INSERTINTO…SELECT命令把已經(jīng)存在的表中的數(shù)據(jù)，存儲到另外一個表中使用SELECT…INTO…命令存儲數(shù)據(jù)4.1.4知識總結(jié)INSERTINTO表名(列名的列表)VALUES(表達式的列表)使用INSERTINTO…VALUES命令存儲數(shù)據(jù)參數(shù)說明如下：表名：用于指定用來存儲數(shù)據(jù)的表的名稱。列名的列表：要在其中插入數(shù)據(jù)的表中的一列或多列的列表，多列之間用逗號分隔。參數(shù)如果省略，則表示表中的所有列都要插入數(shù)據(jù)。表達式的列表：要插入的數(shù)據(jù)值的列表。注意：（1）對于指定了參數(shù)“列名的列表”中的每個列，都必須有一個數(shù)據(jù)值與之相對應(yīng)，表達式的順序和指定的列名的順序要一致；如果沒有指定參數(shù)“列名的列表”，表達式的順序與表中列的順序一致，必須包含表中每列的值。即表達式列表的數(shù)量和表達式值的數(shù)據(jù)類型以及順序必須與表中或列名的列表中的一致。（2）表中可以為空的字段在“列名的列表”參數(shù)中不必指出，那么在表達式中也可以不用輸入對應(yīng)的值。（3）關(guān)鍵字INTO可以省略。（4）存儲的數(shù)據(jù)不能違反表中已經(jīng)存在的約束?！纠?-1】在“專業(yè)”表中添加記錄，具體數(shù)據(jù)如表所示。專業(yè)代碼專業(yè)名稱描述狀態(tài)1軟件技術(shù)軟件開發(fā)相關(guān)12網(wǎng)絡(luò)技術(shù)通信、網(wǎng)絡(luò)安全相關(guān)13硬件技術(shù)單片機、嵌入式相關(guān)14信息管理技術(shù)

1INSERTINTO專業(yè)VALUES(1,'軟件技術(shù)','軟件開發(fā)相關(guān)','1')

INSERTINTO專業(yè)(專業(yè)代碼,專業(yè)名稱,描述,狀態(tài))VALUES(2,'網(wǎng)絡(luò)技術(shù)','通信、網(wǎng)絡(luò)安全相關(guān)','1')

INSERTINTO專業(yè)(專業(yè)名稱,描述,專業(yè)代碼,狀態(tài))VALUES('硬件技術(shù)','單片機、嵌入式相關(guān)',3,'1')

INSERTINTO專業(yè)(專業(yè)名稱,專業(yè)代碼,狀態(tài))VALUES('信息管理技術(shù)',4,'1')說明：專業(yè)代碼為“1”的記錄的添加省略了“列名的列表”參數(shù)，VALUES值的順序和表中字段的順序一致；專業(yè)代碼為“2”和“3”的記錄的添加指定了“列名的列表”參數(shù)，VALUES值的順序和指定的“列名的列表”參數(shù)的順序一致；專業(yè)代碼為“4”的記錄的添加，“描述”字段為空，在“列名的列表”參數(shù)中不指定“描述”字段，VALUES值中也沒有此字段對應(yīng)的值，VALUES值中的個數(shù)、順序和數(shù)據(jù)類型與“列名的列表”參數(shù)一致。【例4-2】在“班級”表中添加記錄，具體數(shù)據(jù)如表所示。班級代碼班級名稱專業(yè)代碼所屬年級班主任描述1計算機120112012級李秋軟件開發(fā)2計算機120222012級羅莉網(wǎng)絡(luò)安全3計算機130112013級劉亞軟件開發(fā)4計算機130222013級羅莉網(wǎng)絡(luò)安全5計算機130332013級劉偉嵌入式6計算機140132014級劉偉嵌入式7計算機140242014級羅莉信息管理INSERTINTO班級VALUES(1,'計算機',1,'2012級','李秋','軟件開發(fā)')INSERTINTO班級VALUES(2,'計算機',2,'2012級','羅莉','網(wǎng)絡(luò)安全')

INSERTINTO班級VALUES(3,'計算機',1,'2013級','劉亞','軟件開發(fā)')

INSERTINTO班級VALUES(3,'計算機',2,'2013級','羅莉','網(wǎng)絡(luò)安全')

INSERTINTO班級VALUES(5,'計算機',3,'2013級','劉偉','嵌入式')

INSERTINTO班級(班主任,班級代碼,班級名稱,描述,專業(yè)代碼,所屬年級)VALUES('劉真',6,'計算機','嵌入式',3,'2014級')

INSERTINTO班級(班級代碼,班級名稱,專業(yè)代碼,所屬年級)VALUES(7,'計算機',4,'2014級')說明：前3條記錄和第5條記錄省略了表中的字段列表，VALUES值的順序和表中的順序、個數(shù)、數(shù)據(jù)類型都一致；第4條記錄的“班級代碼”字段和第3條記錄的重復了，違反了主鍵約束，所以不能正確存儲數(shù)據(jù)；第6條和第7條記錄指明了表中的字段列表，VALUES值的順序和指定的順序、個數(shù)、數(shù)據(jù)類型一致。在第7條記錄中，表中字段列表中省略了“描述”字段，“班主任”字段，存儲的數(shù)據(jù)這兩個字段的值也為空，可以通過更新記錄的方式增加這兩個字段的值，在后面介紹。【例4-3】在“學生”表中添加記錄，具體數(shù)據(jù)如表所示。學號姓名性別出生日期個人聯(lián)系電話1王思旭男1993-03-15131023156102陳芳女1994-02-26131023156103趙建男1992-12-0113202315612……………………表格不完整第2條記錄和第1條記錄的“個人聯(lián)系電話”字段重復，違反了表中定義的唯一約束，不能正確保存第2條數(shù)據(jù)；第3條記錄的最后一個字段“班級編號”為外鍵，取值為8，參考引用“班級”表的“班級代碼”字段，而“班級”表中沒有班級代碼為8的記錄，所以違反了外鍵約束，記錄也不能正確保存?！纠?-4】在“選課”表中添加記錄，具體數(shù)據(jù)如表所示。學號課程編號成績11901208108395848891459582456544495173625163867270INSERTINTO選課VALUES(1,1,90)INSERTINTO選課VALUES(1,2,DEFAULT)INSERTINTO選課VALUES(8,1,DEFAULT)INSERTINTO選課VALUES(8,3,95)INSERTINTO選課VALUES(8,4,88)INSERTINTO選課VALUES(9,1,45)INSERTINTO選課VALUES(9,5,82)INSERTINTO選課VALUES(4,5,65)INSERTINTO選課VALUES(4,4,49)INSERTINTO選課VALUES(5,1,73)INSERTINTO選課VALUES(6,2,51)INSERTINTO選課VALUES(6,3,86)INSERTINTO選課VALUES(7,2,70)第2條和第3條記錄，“成績”字段取值為“DEFAULT”，存儲的是在“選課”表中為“成績”字段設(shè)置的默認值。INSERT表名1SELECT字段列表FROM表名2WHERE條件表達式使用INSERTINTO…SELECT命令把已經(jīng)存在表中數(shù)據(jù)存儲到另外一個表參數(shù)說明如下：表名1：用于指定用來存儲數(shù)據(jù)的表的名稱。字段列表：查詢結(jié)果的字段的列表。表名2：查詢數(shù)據(jù)所在的源表。條件表達式：查詢結(jié)果限定行的條件。注意：參數(shù)“字段列表”的數(shù)據(jù)類型、順序、個數(shù)要和表名1中的字段的一致?！纠?-5】在“學生管理”數(shù)據(jù)庫下創(chuàng)建表“學生備份”，表結(jié)構(gòu)如表所示，把“學生”表中女生的相關(guān)信息儲存在“學生備份”表中。字段名稱數(shù)據(jù)類型是否允許NULL值約束學號int否主鍵姓名varchar(50)否

性別char(2)否

出生日期datetime是

個人聯(lián)系電話char(11)是唯一約束CREATETABLE學生備份(學號intprimarykey,姓名varchar(50)notnull,性別char(2)notnull,出生日期datetime,個人聯(lián)系電話char(11)unique)INSERT學生備份SELECT學號,姓名,性別,出生日期,個人聯(lián)系電話FROM學生WHERE性別='女'首先創(chuàng)建學生備份表把女生的相關(guān)信息存儲在學生備份表SELECT字段列表INTO新表名FROM表名WHERE條件表達式使用SELECT…INTO…命令存儲數(shù)據(jù)參數(shù)說明如下：表名：要查詢的數(shù)據(jù)所在的表。字段列表：參數(shù)“表名”包含的字段。新表名：把查詢出來的記錄插入的新表。條件表達式：行的限定條件。注意：SELECT…INTO…語句的功能是從一個表中選擇一些數(shù)據(jù)插入到新表中，這個新表是執(zhí)行查詢語句的時候創(chuàng)建的，查詢語句執(zhí)行之前是不能預先存在的。【例4-6】把“選課”表中成績大于70的記錄放入一個新表“選課NEW”。SELECT學號,課程編號,成績INTO選課NEWFROM選課WHERE成績>70說明：在執(zhí)行命令之前表“選課NEW”在數(shù)據(jù)庫中是不存在的，“選課NEW”表的結(jié)構(gòu)和SELECT后面的字段相同。4.1.5應(yīng)用實踐在銷售數(shù)據(jù)庫中的“賬號”表中存儲數(shù)據(jù)，具體數(shù)據(jù)如表所示。用戶ID密碼用戶名郵件地址郵編電話狀態(tài)1123456lanlanlanlan@163.com沙坪壩40000065231201啟用2123123wanwanwanwan@163.com渝中區(qū)40000063232101啟用3123321chenchenchenchen@163.com北碚區(qū)40070063332325啟用4lanpinglanping@163.com巴南區(qū)40132065896321啟用5manlanmanlan@163.com江北區(qū)40000063654120啟用1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數(shù)據(jù)庫”文件夾下的數(shù)據(jù)庫“銷售”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：INSERTINTO賬號

VALUES(1,'123456','lanlan','lanlan@163.com','沙坪壩','400000','65231201','啟用')INSERTINTO賬號

VALUES(2,'123123','wanwan','wanwan@163.com','渝中區(qū)','400000','63232101','啟用')INSERTINTO賬號

VALUES(3,'123321','chenchen','chenchen@163.com','北碚區(qū)','400700','63332325','啟用')INSERTINTO賬號

VALUES(4,DEFAULT,'lanping','lanping@163.com','巴南區(qū)','401300','65896321','啟用')INSERTINTO賬號

VALUES(5,DEFAULT,'manlan','manlan@163.com','江北區(qū)','400000','63654120','啟用')4．單擊工具欄上的【執(zhí)行】按鈕。4.2.1情景描述4.2.2問題分析4.2.3解決方案4.2.4知識總結(jié)4.2.5應(yīng)用實踐任務(wù)4.2更新數(shù)據(jù)數(shù)據(jù)庫維護人員發(fā)現(xiàn)有的課程信息有誤，課程編號為6的記錄，學分應(yīng)該修改為4，開課學期為第四學期。因此需要對數(shù)據(jù)庫內(nèi)原有的數(shù)據(jù)進行更新來修改錯誤的記錄。4.2.1情景描述為了解決上述問題，需要完成以下任務(wù)：1．寫出更新記錄的命令。2．在SQLServer2008上執(zhí)行命令，驗證更新后的記錄。4.2.2問題分析4.2.3解決方案

1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數(shù)據(jù)庫”文件夾下的數(shù)據(jù)庫“學生管理”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：UPDATE課程SET學分=4,開課學期='第四學期'WHERE課程編號=64．單擊工具欄上的【執(zhí)行】按鈕。當學生、課程等表中需要維護的數(shù)據(jù)發(fā)生變化時，需要使用更新數(shù)據(jù)的命令修改表中的數(shù)據(jù)。使用UPDATE命令來更新數(shù)據(jù)的語法規(guī)則為：UPDATE表名SET列名1=數(shù)據(jù)值1,列名2=數(shù)據(jù)值2,列名3=數(shù)據(jù)值3,…WHERE條件表達式參數(shù)說明如下：表名：用于指定需要更新的表的名稱。列名1：要更改數(shù)據(jù)的列的名稱。數(shù)據(jù)值1：列更新后的新值，對列賦新值的表達式之間用逗號分隔。條件表達式：指定更新的記錄的限定條件注意：（1）更新后的數(shù)據(jù)要和字段的數(shù)據(jù)類型保持一致。（2）更新后的數(shù)據(jù)不能違反表中創(chuàng)建的約束條件。（3）WHERE子句指定用于限制修改行的條件，如果省略，則UPDATE語句更新表中所有的行。4.2.4知識總結(jié)UPDATE班級

SET班主任='劉偉'WHERE班級代碼=6【例4-7】“班級”表中，“班級代碼”為6的記錄的班主任字段改為“劉偉”。（1）打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：【例4-8】“課程”表中，“課程編號”為1的記錄的課程性質(zhì)改為“專修”。（1）打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：UPDATE課程

SET課程性質(zhì)='專修'WHERE課程編號=1（2）單擊工具欄上的【執(zhí)行】按鈕，運行結(jié)果如圖4-11，錯誤提示“消息547，級別16，狀態(tài)0，第1行UPDATE語句與CHECK約束"CK__課程__課程性質(zhì)__3D5E1FD2"沖突。該沖突發(fā)生于數(shù)據(jù)庫"學生管理"，表"dbo.課程",column'課程性質(zhì)'語句已終止”。注意：更新后的數(shù)據(jù)不能違反表中的約束。在創(chuàng)建課程表的時候為課程性質(zhì)字段設(shè)置了檢查（check）約束，限定了其取值范圍在銷售數(shù)據(jù)庫中的“賬號”表中存儲數(shù)據(jù)需要更改，用戶ID為5的記錄的狀態(tài)改為“停用”。

1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數(shù)據(jù)庫”文件夾下的數(shù)據(jù)庫“學生管理”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：4.2.5應(yīng)用實踐UPDATE賬號SET狀態(tài)='停用'WHERE用戶ID=54．單擊工具欄上的【執(zhí)行】按鈕。4.3.1情景描述4.3.2問題分析4.3.3解決方案4.3.4知識總結(jié)4.3.5應(yīng)用實踐任務(wù)4.3刪除數(shù)據(jù)在數(shù)據(jù)庫維護過程中，發(fā)現(xiàn)課程表中課程編號為5的課程不需要再開設(shè)了，則需要刪除記錄。4.3.1情景描述為了解決上述問題，需要完成以下任務(wù)：1．根據(jù)刪除記錄的命令，寫出刪除課程編號為5的記錄。2．在SQLServer2008上執(zhí)行，驗證結(jié)果。4.3.2問題分析1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數(shù)據(jù)庫”文件夾下的數(shù)據(jù)庫“學生管理”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：4.3.3解決方案DELETE課程WHERE課程編號=64．單擊工具欄上的【執(zhí)行】按鈕。4.3.4知識總結(jié)當表中的數(shù)據(jù)不再需要的時候，為了節(jié)約存儲空間，需要刪除數(shù)據(jù)，刪除數(shù)據(jù)最小的單位是一行。使用DELETE語句刪除數(shù)據(jù)的語法規(guī)則為：DELETE表名WHERE條件表達式參數(shù)說明如下：表名：用于指定要從其中刪除行的表的名稱。條件表達式：指定刪除記錄的限定條件。注意：刪除記錄要確保沒有被其它表引用。例如刪除一個學生A的記錄，要確保在選課表中沒有此學生A的選課信息；否則，在選課表中還有學生A的選課記錄，但是把學生A的信息從學生表中刪除了，導致選課表中的學生A的選課記錄成為廢數(shù)據(jù)，因為已經(jīng)不存在的學生是不能選課的。刪除的順序應(yīng)該是：先在選課表中刪除學生