sql實(shí)驗(yàn)講義-數(shù)據(jù)完整性試驗(yàn)、索引

1、SQL-Server實(shí)驗(yàn)講義 PAGE 16實(shí)驗(yàn)四 數(shù)據(jù)完整性試驗(yàn)一 實(shí)驗(yàn)?zāi)康?理解實(shí)體完整性、參照完整性、用戶自定義完整性的作用2 特別掌握外碼的作用。二 實(shí)驗(yàn)要求記錄試驗(yàn)中遇到的問(wèn)題，并寫(xiě)出原因。三 實(shí)驗(yàn)內(nèi)容1 實(shí)體完整性1 student 表數(shù)據(jù)輸入 學(xué)號(hào)姓名性別年齡系科3001趙達(dá)男20SX3002楊麗女21JSJ3001李寅女21SX輸入上述數(shù)據(jù)，記錄出現(xiàn)的問(wèn)題，說(shuō)明原因。在創(chuàng)建表的時(shí)候出現(xiàn)錯(cuò)誤信息”將截?cái)嘧址投M(jìn)制代碼”,因?yàn)橐粋€(gè)漢字在UTF-8編碼下占用字節(jié)是3 所以應(yīng)該將屬性sex改成char(3)select * from student 查看你輸入了幾行數(shù)據(jù)。2 cou

2、rse 表數(shù)據(jù)的輸入CnoCnameCpnoCcredit1081電子商務(wù)43 SC 表數(shù)據(jù)的輸入SnoCnoGrade30011081903001108179輸入上述數(shù)據(jù)，記錄出現(xiàn)的問(wèn)題，說(shuō)明原因。錯(cuò)誤信息” 違反了 PRIMARY KEY 約束“PK_sc_905C05335F5775AE”。不能在對(duì)象“dbo.sc”中插入重復(fù)鍵。重復(fù)鍵值為 (3001, 1801)”因?yàn)橹麈I是sno與cno的聯(lián)合,而插入的數(shù)據(jù)重復(fù),所以破壞了數(shù)據(jù)庫(kù)實(shí)體完整性性的規(guī)則!2 用戶自定義完整性約束表student 有用戶自定義約束：性別 不能為空且取值范圍為男，女年齡大于16歲表course 的自定義約束：C

3、credit 取值范圍 0 ,1,2,3,4,5 課程表的每一行的 Cno 與 cpno 不可相同1 student 表數(shù)據(jù)輸入 學(xué)號(hào)姓名性別年齡系科3005趙達(dá)男14SX3006楊麗南21JSJ輸入上述數(shù)據(jù)，記錄出現(xiàn)的問(wèn)題，說(shuō)明原因。錯(cuò)誤信息” INSERT 語(yǔ)句與 CHECK 約束CK_student_age_5165187F沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)school，表dbo.student, column age?！币?yàn)橛脩魧傩詀ge約束條件位大于16歲,二插入數(shù)據(jù)位14 違反了用戶自定義完整性規(guī)則,所以報(bào)錯(cuò)select * from student 查看你輸入了那些數(shù)據(jù)。2 course

4、表數(shù)據(jù)的輸入CnoCnameCpnoCcredit1085C+91086語(yǔ)文10863輸入上述數(shù)據(jù)，記錄出現(xiàn)的問(wèn)題，說(shuō)明原因。沒(méi)有錯(cuò)誤3 SC 表數(shù)據(jù)的輸入SnoCnoGrade30021081128輸入上述數(shù)據(jù)，記錄出現(xiàn)的問(wèn)題，說(shuō)明原因。出現(xiàn)錯(cuò)誤信息” INSERT 語(yǔ)句與 CHECK 約束CK_sc_grade_5812160E沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù)school，表dbo.sc, column grade?！背煽?jī)約束為0-100之間 128破壞了用戶自義完整性規(guī)則3 參照完整性約束掌握表之間建立外碼后，對(duì)被參照表的如下操作會(huì)有何影響：修改主碼、插入新行、刪除新行？對(duì)參照表添加新行、刪除行

5、、修改外碼值有何影響？掌握級(jí)聯(lián)修改、級(jí)聯(lián)刪除的概念。注意：表SC的 Sno是外碼，參照student的sno。表SC的 Cno是外碼，參照course的cno。輸入實(shí)驗(yàn)前的數(shù)據(jù)2學(xué)生表 StudentSnoSnameSsexSageSdept4001趙尹男20SX4002楊開(kāi)女20JSJ課程表 course CnoCnameCpnoCcredit1088Java51089數(shù)學(xué)3學(xué)生選課 SC SnoCnograde400110889040021088862 試驗(yàn)過(guò)程 1) 在SC表中添加新行：SnoCnoGrade4001106676 記錄試驗(yàn)結(jié)果.,寫(xiě)出出現(xiàn)此結(jié)果的原因.實(shí)驗(yàn)結(jié)果如下,因?yàn)閟

6、c表中的cno數(shù)據(jù)參照了course表中cno,當(dāng)cno中不存在該字段的時(shí)候,是不迅允許插入的2) 在student表中添加新行 SnoSnameSsexSageSdept4003趙輝男21SX記錄試驗(yàn)結(jié)果.,寫(xiě)出出現(xiàn)此結(jié)果的原因.實(shí)驗(yàn)結(jié)果如下,因?yàn)閟c表中的sno數(shù)據(jù)參照了student表中sno,當(dāng)sno不存在該字段的時(shí)候,是不迅允許插入的3) 刪除student 表的 4001 ，4002學(xué)生 記錄試驗(yàn)結(jié)果.,寫(xiě)出出現(xiàn)此結(jié)果的原因.實(shí)驗(yàn)結(jié)果如下,當(dāng)外表刪除信息的時(shí)候,默認(rèn)時(shí)候不允許刪除的,因?yàn)閰⒄樟送獗?只有當(dāng)外表將參照屬性刪除時(shí)候,才能刪除,或者直接連外表的屬性一起刪除, 四 思考刪除

7、SC表的記錄有限制嗎？ 有限制,受限于參照外鍵屬性采取什么技術(shù)能使不能成功執(zhí)行的命令變得可以執(zhí)行，且使數(shù)據(jù)庫(kù)保持?jǐn)?shù)據(jù)完整性。 sqlserver有,可以使用級(jí)聯(lián)刪除的方式達(dá)到刪除的效果,但是級(jí)聯(lián)刪除也會(huì)連同參照外表的屬性一同刪除 具體做法是在外鍵參照約束后面加上check on delete cascade;4) 把 student 表的學(xué)號(hào) 4003 改為 4018 , 4001改為4021。 記錄試驗(yàn)結(jié)果.,寫(xiě)出出現(xiàn)此結(jié)果的原因?qū)嶒?yàn)結(jié)果如下圖,因?yàn)橥獗韘c中sno屬性參照了student中sno屬性,所以不能刪除思考：采取什么技術(shù)能使本題不能執(zhí)行的命令可以執(zhí)行，且使數(shù)據(jù)庫(kù)保持?jǐn)?shù)據(jù)完整性。s

8、qlserver有,可以使用級(jí)聯(lián)更新的方式達(dá)到刪除的效果,但是級(jí)聯(lián)更新也會(huì)連同參照外表的屬性一同更新具體做法是在外鍵參照約束后面加上check on update cascade;5) 把sc表中的如下記錄的學(xué)號(hào)從4001改為4011。SnoCnoGrade4001108890 記錄試驗(yàn)結(jié)果.,寫(xiě)出出現(xiàn)此結(jié)果的原因.實(shí)驗(yàn)信息如下 因?yàn)閟c表sno參照的是student表,而在student表中沒(méi)有4011這個(gè)學(xué)生,所以不能更新如不成功，則可以采取什么方法來(lái)實(shí)現(xiàn)此要求。級(jí)聯(lián)更新, 可以使用級(jí)聯(lián)更新的方式達(dá)到刪除的效果,但是級(jí)聯(lián)更新也會(huì)連同參照外表的屬性一同更新具體做法是在外鍵參照約束后面加上ch

9、eck on update cascade;如不成功，那么把4001修改為4003，能成功嗎？不能成功,因?yàn)橥獗砀虏荒苓B參照表也更新四 思考 參照完整性規(guī)則中,外碼可以為空, 但SC表中的外碼可以為空嗎?為什么?舉一個(gè)外碼可以為空的例子。Sc中外碼不可以為空,因?yàn)閟c中的sc cno是聯(lián)合主鍵如果想取空值 則讓sc中的sno不為主鍵實(shí)驗(yàn)五 索引一實(shí)驗(yàn)?zāi)康恼莆账饕慕?、刪除的方法。二 實(shí)驗(yàn)要求記錄試驗(yàn)中遇到的問(wèn)題，并寫(xiě)出原因。三 實(shí)驗(yàn)內(nèi)容1創(chuàng)建索引建 student 的索引為姓名建立索引，索引名：Ix_student_sname為系科建立索引，索引名：Ix_student_sdeptSC 的

10、索引為課程號(hào)建立索引： ix_sc_cno Course 的索引為課程名建立唯一性索引 ：Ix_course_cname 如何 SP_HELP 查看索引剛才建立的索引？如下圖所示如何在企業(yè)管理器中查看索引？在左側(cè)顯示 二 刪除索引 course 表的索引 IX_course_cname drop index course.Ix_course_cname;三 思考：如何把索引 IX_student_sname 修改為唯一性索引？先刪除索引,在建立唯一索引drop index Ix_student_sname on student;create unique index Ix_student_sn

11、ame on student(sname); 四 實(shí)驗(yàn)思考建立索引的目的 可以大大加快查詢數(shù)據(jù)時(shí)候檢索的速度實(shí)驗(yàn)六 更新數(shù)據(jù)一 實(shí)驗(yàn)?zāi)康恼莆読nsert,update ,delete 語(yǔ)句的使用。二 實(shí)驗(yàn)要求記錄試驗(yàn)中遇到的問(wèn)題，并寫(xiě)出原因。三 實(shí)驗(yàn)內(nèi)容1 insert 1 寫(xiě)出把下述學(xué)生的信息添加到student表中的命令。 學(xué)號(hào)姓名性別年齡系科4001趙茵男20SX4002楊華女212 批量插入數(shù)據(jù)建立一個(gè)新表 sc_name ，有屬性 sno , sname , ssex , cno , grade 。create table sc_name( sno varchar(6) primar

12、y key , sname varchar(10) , ssex varchar(3), cno varchar(6), grade decimal(12,2);把 SX 系學(xué)生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。insert into sc_name(sno, sname, ssex, cno, grade) (select student.sno, sname, sex, cno, grade from student,sc where sdept in (SX) and student.sno = sc.sno);3） 察看 sc_na

13、me 表的數(shù)據(jù)2 Update1 修改 0001 學(xué)生的系科為: JSJupdate student set sdept = JSJ where sno=0001把陳小明的年齡加1歲，性別改為女。update student set age = age+1 ,sex = 女 where sname=陳小明;3修改李文慶的1001課程的成績(jī)?yōu)?93 分update sc set grade = 93 where sno in ( select sno from student where sname=李文慶 );4把“數(shù)據(jù)庫(kù)原理”課的成績(jī)減去1分update sc set grade = gra

14、de-1 where cno in( select cno from course where cname=數(shù)據(jù)庫(kù)原理 );3 Delete1 刪除所有 JSJ 系的男生delete from student where sdept in(JSJ);2 刪除“數(shù)據(jù)庫(kù)原理”的課的選課紀(jì)錄delete from sc where cno in( select cno from course where cname=數(shù)據(jù)庫(kù)原理 );四 思考修改數(shù)據(jù)的命令與修改表結(jié)構(gòu)的命令有何區(qū)別？一個(gè)是ddl對(duì)數(shù)據(jù)進(jìn)行定義 一個(gè)是dml對(duì)數(shù)據(jù)進(jìn)行操縱 實(shí)驗(yàn)七 Sql 查詢語(yǔ)句一 單表1查詢年齡在19至21歲之間的女

15、生的學(xué)號(hào),姓名,年齡,按年齡從大到小排列。select sno, sname, sage from student where ssex=女 and sage between 19 and 21 order by sage desc2查詢姓名中第2個(gè)字為“明”字的學(xué)生學(xué)號(hào)、性別。select sno, ssex from student where sname like _明% 3查詢 1001課程沒(méi)有成績(jī)的學(xué)生學(xué)號(hào)、課程號(hào)select sno, cno from sc where grade is null and cno=10014查詢JSJ 、SX、WL 系的年齡大于25歲的學(xué)生學(xué)號(hào),姓

16、名，結(jié)果按系及學(xué)號(hào)排select sno, sname from student where sdept in (JSJ,SX,WL) and sage25order by sdept,sno5按10分制查詢學(xué)生的sno,cno,10分制成績(jī) （1-10分 為1 ，11-20分為2 ，30-39分為3，。90-100為10） select sno,cno,cast(grade/10 as int) from sc;6查詢 student 表中的學(xué)生共分布在那幾個(gè)系中。（distinct）select distinct sdept from student ;7查詢0001號(hào)學(xué)生1001，100

17、2課程的成績(jī)。select grade from sc where sno=0001 and (cno=1001 or cno=1002) ;二 統(tǒng)計(jì)1查詢姓名中有“明”字的學(xué)生人數(shù)。select count(*) from student where sname like %明% 2計(jì)算JSJ系的平均年齡及最大年齡。select avg(sage),max(sage) from student where sdept=JSJ ;3查詢學(xué)生中姓名為張明、趙英的人數(shù)select COUNT(*) from Student where sname in (張英,趙明);4計(jì)算每一門課的總分、平均分

18、，最高分、最低分，按平均分由高到低排列select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cnoorder by avg(grade) desc ;5 計(jì)算 1001,1002 課程的平均分。select avg(grade)as avg from sc where cno in(1001,1002) group by cno6 查詢平均分大于80分的學(xué)生學(xué)號(hào)及平均分 select sc.sno ,avg(grade) from sc group by sc.sno having avg(grade)8

19、0 ;7 統(tǒng)計(jì)選修課程超過(guò) 2 門的學(xué)生學(xué)號(hào)select sno from sc group by sno having count(*)2 ;8 統(tǒng)計(jì)有10位成績(jī)大于85分以上的課程號(hào)。select cno from sc where grade85 group by cno having count(*)=10 ;9 統(tǒng)計(jì)平均分不及格的學(xué)生學(xué)號(hào)select sno from sc group by sno having avg(grade)60 ;10 統(tǒng)計(jì)有大于兩門課不及格的學(xué)生學(xué)號(hào)select sno from sc where grade2 ;三 連接 1查詢 JSJ 系的學(xué)生選修的

20、課程號(hào)select cno from student,sc where student.sno=sc.sno and sdept=JSJ 2查詢選修1002 課程的學(xué)生的學(xué)生姓名 (不用嵌套及嵌套2種方法）select sname from student,sc where student.sno = sc.sno and cno=1002 select sname from student where sno in (select sno from sc where cno=1002)3查詢數(shù)據(jù)庫(kù)原理不及格的學(xué)生學(xué)號(hào)及成績(jī)select sno,grade from sc ,course wh

21、ere o=o and cname=數(shù)據(jù)庫(kù)原理4查詢選修“數(shù)據(jù)庫(kù)原理”課且成績(jī) 80 以上的學(xué)生姓名(不用嵌套及嵌套2種方法）select sname from student , sc , course where student.sno=sc.sno and o = o and grade80 and cname=數(shù)據(jù)庫(kù)原理 select sname from student where sno in ( select sno from sc where grade80 and cno in ( select cno from course where cname=數(shù)據(jù)庫(kù)原理) ) 5查詢

22、平均分不及格的學(xué)生的學(xué)號(hào)，姓名,平均分。select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.sno having avg(grade) 75)Select max(Sname) from sc,student where student.sno=sc.sno and Ssex=女 Group by student.sno having avg(grade)757查詢男學(xué)生學(xué)號(hào)、姓名、課程號(hào)、成績(jī)。(一門課程也沒(méi)有選修的男學(xué)生也要列出

23、，不能遺漏)select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=男四 嵌套、相關(guān)及其他1 查詢平均分不及格的學(xué)生人數(shù)select count(*) from student where sno in( select sno from sc group by sno having avg(grade)=all (select avg(grade) from sc group by sno ) 4 查詢沒(méi)有選修1001，1002課程的學(xué)生姓名。Select sname from student where not exists (Select * from course where cno in (1001,1002) and Not exists ( select * from sc where sno=student.sno and cno=o )5