MySQL數(shù)據(jù)庫 2.2 數(shù)據(jù)的操作教案

教案首頁課程名稱MySQL數(shù)據(jù)庫開發(fā)本節(jié)課題任務(wù)2.2數(shù)據(jù)的操作授課方式理實(shí)一體化教學(xué)參考及教具姜云橋主編.MySQL數(shù)據(jù)庫開發(fā).臨科院：教務(wù)處,2023計(jì)算機(jī)MySQL數(shù)據(jù)庫軟件多媒體教學(xué)系統(tǒng)資料包教學(xué)目標(biāo)及基本要求掌握插入INSERTINTO的語法了解REPLACEINTO、LOADDATAINFILE的語法掌握UPDATE...SET...命令的語法掌握DELETE的語法能管理數(shù)據(jù)表數(shù)據(jù)能插入數(shù)據(jù)能修改數(shù)據(jù)能刪除數(shù)據(jù)培養(yǎng)溝通能力教學(xué)重點(diǎn)教學(xué)難點(diǎn)插入數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)插入數(shù)據(jù)教學(xué)小結(jié)作業(yè)及要求按照任務(wù)書做好預(yù)習(xí)任務(wù)管理數(shù)據(jù)教后反思

教案用紙教學(xué)內(nèi)容、方法和過程附記任務(wù)2.2數(shù)據(jù)表的操作導(dǎo)入：學(xué)院的教學(xué)系統(tǒng)，可以查到那些數(shù)據(jù)？我們根據(jù)需求分析，要求創(chuàng)建學(xué)生、課程、教師和系部等數(shù)據(jù)表來存儲數(shù)據(jù)?！救蝿?wù)背景】 學(xué)校要建立一個教學(xué)管理系統(tǒng)。根據(jù)需求分析，要求創(chuàng)建學(xué)生、課程、教師和系部等數(shù)據(jù)表來存儲數(shù)據(jù)。接下來，要建立數(shù)據(jù)庫，設(shè)計(jì)數(shù)據(jù)表的結(jié)構(gòu)，并初始化相關(guān)表數(shù)據(jù)?！救蝿?wù)要求】本任務(wù)將學(xué)習(xí)創(chuàng)建和管理數(shù)據(jù)庫、創(chuàng)建和管理表。在任務(wù)實(shí)施過程中，要特別注意表的規(guī)范化，要注意數(shù)據(jù)類型的正確選擇，還要注意數(shù)據(jù)庫和數(shù)據(jù)表字符集的統(tǒng)一問題?！救蝿?wù)必備知識】2.2.1插入數(shù)據(jù) 插入數(shù)據(jù)的方法很多，可以通過INSERTINTO、REPLACEINTO語句插入，也可以使用LOADDATAINFILE方式將保存在文本文件中的數(shù)據(jù)插入到指定的表。一次可以插入一行或插入多行數(shù)據(jù)。INSERTINTO的使用插入數(shù)據(jù)1、為表的所有字段插入數(shù)據(jù)2、為表的指定字段插圖數(shù)據(jù)3、將查詢結(jié)果插入表中一、插入單行數(shù)據(jù)【任務(wù)2.2.1】向student表中插入如下數(shù)據(jù)mysql>insertintostudent(s_no,s_name,s_sex,s_birth,s_address,s_phone,s_photo,d_no)->values('132001','李平','男','2001-02-01','南京路1號',null,null,'D001');如果values值列表與表中的字段順序完全一致，可以省略字段列表，因此上面的語句也可以簡寫成：mysql>insertintostudent->values('132001','李平','男','2001-02-01','南京路1號',null,null,'D001');如果允許某個字段為空或者定義了默認(rèn)值，在插入數(shù)據(jù)時可以不指定該字段的值，此時會插入null或者默認(rèn)值：mysql>insertintostudent(s_no,s_name,s_birth,s_address,d_no)->values('132001','李平','2001-02-01','南京路1號','D001');s_sex列此時插入默認(rèn)值“女”,s_photo列和s_phone列插入null?！救蝿?wù)2.2.2】再次用INSERTINTO語句向STUDENTS表中插入數(shù)據(jù)。mysql>INSERTINTOstudents(s_no,s_name,sex)VALUES('132001','李小平','男');由于STUDENTS表中已經(jīng)有132001學(xué)生的記錄，因此將出現(xiàn)主鍵沖突錯誤，如圖2.4所示。圖2.4運(yùn)行結(jié)果用REPLACEINTO語句則可以直接插入新數(shù)據(jù)，而不會出現(xiàn)錯誤信息： mysql>REPLACEINTOSTUDENTS(s_no,s_name,sex)VALUES('132001','李小平','男');二、插入多行數(shù)據(jù)【任務(wù)2.2.3】一次性向student表中插入下列2行數(shù)據(jù)。mysql>insertintostudent(s_no,s_name,s_sex,s_birth,s_address,s_phone,s_photo,d_no)->values('132002','葉明','女','2001-05-02','學(xué)院路1號',null,null,'D003'),->('132003','張?zhí)?,'男','2000-02-019','文明路6號',null,null,'D001');三、插入查詢數(shù)據(jù)【任務(wù)2.2.4】現(xiàn)有表student_new結(jié)構(gòu)如下所示，請將student表中對應(yīng)數(shù)據(jù)插入student_new表中。首先創(chuàng)建student_new表:mysql>createtablestudent_new->(->s_nochar(8)primarykey,->s_namevarchar(20),->s_sexenum('男','女')default'女',->s_birthdatenotnull,->d_nochar(4)->);將student表中對應(yīng)字段的數(shù)據(jù)插入student_new表:mysql>insertintostudent_new(s_no,s_name,s_sex,s_birth,d_no)->selects_no,s_name,s_sex,s_birth,d_nofromstudent;注意，并不需要studen_new的字段名和select查詢中的字段名相同，但需要個數(shù)一樣，且數(shù)據(jù)類型相兼容。四、插入重復(fù)數(shù)據(jù)當(dāng)插入主鍵相同的記錄時默認(rèn)會報(bào)錯，插入失敗，而有時我們希望當(dāng)插入的數(shù)據(jù)違反主鍵約束時，執(zhí)行更新操作替換原表中的數(shù)據(jù)，此時可通過ONDUPLICATEKEYupdate或REPLACE語句實(shí)現(xiàn)。1.insert…onduplicatekeyupdateinsertintotable_name(col1,col2,...)values(val1,val2,...)onduplicatekeyupdateassignment_list;說明：onduplicatekeyupdate可用于同時實(shí)現(xiàn)插入和更新操作。當(dāng)插入的數(shù)據(jù)違反主鍵約束時，將會執(zhí)行更新操作（assignment_list），替換原表中的數(shù)據(jù)?！救蝿?wù)2.2.5】向student表中插入如下數(shù)據(jù),若數(shù)據(jù)存在，更新s_birth為’2023-03-02’mysql>insertintostudent(s_no,s_name,s_sex,s_birth,s_address,s_phone,s_photo,d_no)->values('122001','張群','男','2000-02-01','文明路8號',null,null,'D001')->onduplicatekeyupdate->s_birth='2023-03-02';2.2.2修改數(shù)據(jù) 用update...set...命令對表中的數(shù)據(jù)進(jìn)行修改?？梢孕薷囊粋€表的數(shù)據(jù)，也可以修改多個表的數(shù)據(jù)。一、單表更新updatetbl_namesetcol1=val1,col2=val2,...[wherewhere_condition][orderby...][limitrow_count]說明：1)只更新一個表中的數(shù)據(jù)，也就意味著SET涉及的字段同屬于一個表。2)對于多個字段的更新，按照從左到右的順序執(zhí)行，col1執(zhí)行完執(zhí)行col2,依次類推。二、多表更新（選）updatetable_referencessetcol1=val1,col2=val2,...[wherewhere_condition]說明：1)更新多個表中的數(shù)據(jù)，意味著SET涉及的字段分屬不同的表。2)多表更新不支持orderby和limit子句。3)多表更新中多個字段的賦值執(zhí)行順序是不確定的?！救蝿?wù)2.2.6】假設(shè)有一課程表與COURSE的結(jié)構(gòu)相同，現(xiàn)將COURSE的數(shù)據(jù)插入到課程表中。mysql>INSERTINTO課程表SELECT*FROMCOURSE;查看課程表的數(shù)據(jù)。 mysql>SELECT*FROM`課程表`;CREATETABLE`score`(`s_no`char(8)NOTNULL,`c_no`char(4)NOTNULL,`report`float(5,1)DEFAULTNULL,PRIMARYKEY(`s_no`,`c_no`),KEY`c_no`(`c_no`),KEY`score_index`(`s_no`,`c_no`))ENGINE=InnoDBDEFAULTCHARSET=utf8;分析與討論（1）VALUES子句：包含各列需要插入的數(shù)據(jù)清單，數(shù)據(jù)的順序要與列的順序相對應(yīng)。若表名后不給出列名，則在VALUES子句中要給出每一列（除IDENTITY和timestamp類型的列）的值，如果列值為空，則值必須置為NULL，否則會出錯。（2）使用INSERT語句可以向表中插入一行數(shù)據(jù)，也可以插入多行數(shù)據(jù)，最好一次插入多行數(shù)據(jù)，各行數(shù)據(jù)之間用“，”分隔。（3）可使用SET子句插入數(shù)據(jù)，用SET子句直接賦值時可以不按列順序插入數(shù)據(jù)，對允許空值的列可以不插入。mysql>INSERTINTOCOURSESETC_NO='B003',C_NAME='應(yīng)用文寫作',TEACHER='馬衛(wèi)平',TYPE='選修',HOURS=60;（4）REPLACEINTO向表中插入數(shù)據(jù)時，首先嘗試插入數(shù)據(jù)到表中，如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷），則先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)，否則，直接插入新數(shù)據(jù)。（5）還可以向表中插入其他表的數(shù)據(jù)，但要求兩個表要有相同的結(jié)構(gòu)。語法格式： INSERTINTOTABLENAME1SELECT*FROMTABLENAME2;2．用LOADDATA語句將數(shù)據(jù)裝入數(shù)據(jù)庫表中【任務(wù)2.2.7】 創(chuàng)建一個名為“課程表”的表，假設(shè)課程表的數(shù)據(jù)已放在“D:\course.txt”中，現(xiàn)將course.txt的數(shù)據(jù)插入到課程表中。mysql>LOADDATALOCALINFILE“D:\course.txt”INTOTABLE課程表charactersetgb312;分析與討論（1）MySQLServer默認(rèn)的字符集是UTF8，在插入數(shù)據(jù)時，為避免中文字符亂碼，要加上charactersetgb2312。 （2）course.txt各行文本之間要用制表符<Tab>分隔。3．圖片數(shù)據(jù)的插入MySQL還支持圖片的存儲，圖片一般可以以路徑的形式來存儲，即插入圖片采用直接插入圖片的存儲路徑。當(dāng)然，也可以直接插入圖片本身，只要用LOAD_FILE()函數(shù)即可。【任務(wù)2.2.8】向STUDENTS表中插入一行數(shù)據(jù)：122110，程明，男，1991-02-01，D001，北京路123號，02066635425，picture.jpg 其中，照片路徑為“D:\IMAGE\picture.jpg”。使用如下語句。mysql>INSERTINTOSTUDENTSVALUES('122110','程明','男','1991-02-01','D001','北京路123號','02066635425','D:\IMAGE\picture.jpg');下面語句是直接存儲圖片本身。 mysql>INSERTINTOXS VALUES('122110','程明','男','1991-02-01','D001','北京路123', '02066635425',LOAD_FILE('D:\IMAGE\picture.jpg'));分析與討論（1）存放圖片的字段要使用BLOB類型。BLOB是專門存儲二進(jìn)制文件的類型，有大小之分，例如mediumblob、longblob等，以存儲大小不同的二進(jìn)制文件，一般的圖形文件使用mediumblob就足夠了。（2）插入圖片文件路徑的辦法要比插入圖片本身好。圖片如果很小的話，可以存入數(shù)據(jù)庫，但是如果圖片大的話，保存或讀取操作會很慢，倒不如將圖片存入指定的文件夾，然后把文件路徑和文件名存入數(shù)據(jù)庫?！救蝿?wù)2.2.9】將A001課程成績*1.2轉(zhuǎn)為120分制計(jì)。 mysql>UPDATESCORESETREPORT=REPORT*1.2WHEREC_NO='A001';修改單個表，語法格式為： UPDATEtbl_name SETcol_name1=[,col_name2=expr2...] [WHERE子句] [ORDERBY子句] [LIMIT子句]總結(jié)分析與討論SET子句：根據(jù)WHERE子句中指定的條件，對符合條件的數(shù)據(jù)行進(jìn)行修改。若語句中不設(shè)定WHERE子句，則更新所有行。2.2.3刪除數(shù)據(jù)一、單表刪除deletefromtbl_name[wherewhere_condition][orderby...][limitrow_count]說明：1)只刪除滿足where_condition條件的數(shù)據(jù)行，沒有指定條件將會刪除表中的全部數(shù)據(jù)。2)如果指定了orderby和limit子句，按照順序刪除row_count行數(shù)據(jù)。3)數(shù)據(jù)刪除后將不能恢復(fù)，因此，在執(zhí)行刪除之前對數(shù)據(jù)做好備份。二、多表刪除deletetbl_name1,tbl_name2,...fromtable_references[wherewhere_condition]或者deletefromtbl_name1,tbl_name2,...usingtable_references[wherewhere_condition]說明：1)第一種語法刪除DELETE后面表中的數(shù)據(jù)，第二種語法刪除from后面表中的數(shù)據(jù)。2)多表刪除也不支持orderby和limit子句?！救蝿?wù)2.2.10】刪除女生記錄【任務(wù)2.2.11】刪除B001課程不及格的成績記錄【任務(wù)2.2.12】刪除SCORE表中分?jǐn)?shù)最低的3行記錄【任務(wù)2.2.13】清空student_new表中的數(shù)據(jù)【任務(wù)2.2.14】同時刪除“聶鳳卿”的信息，以及相應(yīng)的課程成績。注意：刪前看下表中數(shù)據(jù)分析與討論（1）QUICK修飾符：可以加快部分種類的刪除操作的速度。（2）FROM子句：用于指定從何處刪除數(shù)據(jù)。（3）WHERE子句指定的刪除條件。如果省略WHERE子句則刪除該表的所有行。（4）ORDERBY子句：各行按照子句中指定的順序進(jìn)行刪除，此子句只在與LIMIT聯(lián)用時才起作用。ORDERBY子句和LIMIT子句的具體定義將在任務(wù)10中介紹。（5）LIMIT子句：用于告知服務(wù)器在控制命令被返回到客戶端前被刪除的行的最大值。（6）數(shù)據(jù)刪除后將不能恢復(fù)，因此，在執(zhí)行刪除之前一定要對數(shù)據(jù)做好備份?！卷?xiàng)目實(shí)踐】一、在人事管理數(shù)據(jù)庫rsgl中進(jìn)行如下操作1.用insertinto語句向department表插入所有數(shù)據(jù)。數(shù)據(jù)如下：('A001','辦公室'),('A002','人事處'),('A003','宣傳部'),('A004','教務(wù)處'),('A005','科技處'),('A006','后勤處'),('B001','信息學(xué)院'),('B002','藝術(shù)學(xué)院'),('B003','外語學(xué)院'),('B004','金融學(xué)院'),('B005','建筑學(xué)院');2.以文本文件的方式將數(shù)據(jù)裝入到數(shù)據(jù)表employee，文件放在“D:\MYSQL\employee.txt”內(nèi)。(可選命令可參考數(shù)據(jù)恢復(fù))3.用insertinto語句一次性向salary表插入所有數(shù)據(jù)。數(shù)據(jù)如下：('100100',2000.00,4000.00,2266.00,1320.00,300.00,100.00),('100101',3000.00,5000.00,2278.00,1460.00,450.00,30.00),('100102',2500.00,450