北郵大三下第5次數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告mysql

1、catherine2015/6/22實(shí)驗(yàn)五 數(shù)據(jù)庫完整性與安全性實(shí)驗(yàn)一、 實(shí)驗(yàn)內(nèi)容1.1完整性實(shí)驗(yàn)：（1） 分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束；（2） 分別向?qū)W生表、課程表插入具有相同學(xué)號(hào)和相同課程編號(hào)的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗(yàn)證其實(shí)體完整性約束；（3） 向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒有的，驗(yàn)證參照完整性約束；（4） 刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束；（5） 定義存儲(chǔ)過程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行。（6） 定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試。（7） 用sql完成以上操作。1

2、.2安全性實(shí)驗(yàn)內(nèi)容（1） 定義一新的登陸帳號(hào)、數(shù)據(jù)庫用戶，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限；（2） 分別用sa用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改；（3） 再次用此用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改。（4） 用sql語句分別完成以上內(nèi)容。二、實(shí)驗(yàn)要求(1) 用sql語句完成以上操作(2) 要求學(xué)生獨(dú)立完成以上內(nèi)容。(3) 實(shí)驗(yàn)完成后完成要求的實(shí)驗(yàn)報(bào)告內(nèi)容。三、實(shí)驗(yàn)環(huán)境系統(tǒng)：windows 7軟件：mysql5.6四、實(shí)驗(yàn)步驟及結(jié)果分析4.1完整性實(shí)驗(yàn)：1.1.1 分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束；主鍵約束（建立表格

3、時(shí)已經(jīng)完成）：實(shí)現(xiàn)實(shí)體完整性約束。create table course ( cno varchar(3) not null, cname varchar(12) default null, lhour int default null, credit int default null, semester varchar(2) default null, primary key (cno);create table sc ( sno varchar(6) not null, cno varchar(3) not null, grade int default null, primary key

4、 (sno,cno);create table student ( sno varchar(6) not null, sname varchar(6) default null, sex varchar(2) default null, bdate datetime default null, dept varchar(8) default null, classno varchar(4) default null, primary key (sno);外鍵：實(shí)現(xiàn)參照完整性約束；alter table sc add constraint snoforeign key (sno)referenc

5、es student(sno);alter table sc add constraint cnoforeign key (cno)references course(cno);查看插入結(jié)果：1.1.2分別向?qū)W生表、課程表插入具有相同學(xué)號(hào)和相同課程編號(hào)的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗(yàn)證其實(shí)體完整性約束；由下圖知，插入失敗，失敗原因：【17:36:25insert into student values('31401','郭子敬','男','1980-1-2 00:00','計(jì)算機(jī)','3146')error

6、 code: 1062. duplicate entry '31401' for key 'primary'0.015 sec】，說明主鍵能夠標(biāo)識(shí)表中每條信息的唯一性，不可以重復(fù)。1.1.3 向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒有的，驗(yàn)證參照完整性約束；刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束；插入數(shù)據(jù)失敗，失敗原因【17:43:04insert into sc values('31401','d03', null)error code: 1452. cannot add or update a child row:

7、 a foreign key constraint fails (student.sc, constraint cno foreign key (cno) references course (cno)0.109 sec】，因?yàn)檎n程號(hào)“d04”不存在于課程表中。1.1.4 刪除學(xué)生表中的所有數(shù)據(jù)：刪除失敗，受參照完整性約束的影響，失敗原因【17:47:25delete from studenterror code: 1175. you are using safe update mode and you tried to update a table without a where that

8、uses a key column to disable safe mode, toggle the option in preferences -> sql queries and reconnect.0.015 sec】1.1.5 定義存儲(chǔ)過程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行。這里需要注意的是delimiter 和delimiter ;兩句，delimiter是分割符的意思，因?yàn)閙ysql默認(rèn)以""為分隔符，如果我們沒有聲明分割符，那么編譯器會(huì)把存儲(chǔ)過程當(dāng)成sql語句進(jìn)行處理，則存儲(chǔ)過程的編譯過程會(huì)報(bào)錯(cuò)，所以要事先用delimiter關(guān)鍵字申明當(dāng)前段分隔符，這

9、樣mysql才會(huì)將""當(dāng)做存儲(chǔ)過程中的代碼，不會(huì)執(zhí)行這些代碼，用完了之后要把分隔符還原。delimiter $create procedure test() beginselect student.sno,student.sname,ofrom student ,sc where student.sno=sc.sno and student.sno='31401'end $建立存儲(chǔ)過程：運(yùn)行存儲(chǔ)過程：查看存儲(chǔ)過程：1.1.6 定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試。 需要建立一個(gè)新的table，然后通過對(duì)舊table

10、的操作來觸發(fā)對(duì)新table的操作，而不能通過一個(gè)表的insert觸發(fā)對(duì)同一個(gè)表的操作。創(chuàng)建觸發(fā)器：create trigger a after insert on student for each rowupdate stuset bdate=date_add(bdate,interval 1 day);注：date_add(date,interval expr type) 函數(shù)向日期添加指定的時(shí)間間隔。事件沒有觸發(fā)前表的狀態(tài)：測(cè)試：insert into student values('31455','馮宇寧','男','1980-1-

11、2 00:00','計(jì)算機(jī)','3146');4.2安全性實(shí)驗(yàn)內(nèi)容(1)定義一新的登陸帳號(hào)、數(shù)據(jù)庫用戶“zyr”，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限；授權(quán)用戶zyr訪問學(xué)生數(shù)據(jù)庫的讀權(quán)利 。查看： (2)分別用超級(jí)用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改；root用戶訪問student數(shù)據(jù)庫:root用戶修改：登陸新建數(shù)據(jù)庫：新建用戶zyr訪問數(shù)據(jù)庫：新建用戶zyr修改數(shù)據(jù)庫：因?yàn)橛脩魖yr只有查詢權(quán)限，沒有修改權(quán)限，所以修改失敗。五、實(shí)驗(yàn)總結(jié)（1）設(shè)置觸發(fā)器后，測(cè)試時(shí)，插入失敗，失敗原因：【10:12:11insert in

12、to student values('31430','張明','女','1980-1-2 00:00','計(jì)算機(jī)','3146')error code: 1175. you are using safe update mode and you tried to update a table without a where that uses a key column to disable safe mode, toggle the option in preferences -> sql queries and reconnect.0.078 sec】解決方法: 進(jìn)過一番搜索之后發(fā)現(xiàn)原來是mysql workbench的安全設(shè)置。當(dāng)要執(zhí)行的sql語句是進(jìn)行批量更新或者刪除的時(shí)候就會(huì)提示這個(gè)錯(cuò)誤。· 打開workbench的菜單edit->preferences.· 切換到sql editor頁面· 把forbid update and delete statements without a where clause (safe updates)之前的對(duì)勾去掉· 點(diǎn)擊ok按鈕· 最后一步記得要重啟一下work