(end)數(shù)據(jù)庫實(shí)驗(yàn)答案

1、實(shí)驗(yàn)一 創(chuàng)建、修改數(shù)據(jù)庫和表結(jié)構(gòu)1、用create建立教學(xué)數(shù)據(jù)庫的五個(gè)基本表：（1） 學(xué)生表（學(xué)號，姓名，性別，年齡），student(Sno， sname， ssex，sage) ；（2）課程表（課程號，課程名，學(xué)分），Course (Cno, Cname, credit) ；（3）選課表（學(xué)號，課程號，成績），SC (Sno, Cno, grade ) ；(4) 教師表（教師號，姓名，性別，出生年月，系部，職稱，地址），T(Tno，Tname，ssex，birthday，dept，title，address) ；(5) 工資表（教師號，基本工資，職務(wù)工資，合計(jì)），Salary(Tno，jb

2、gz，zwgz，hj)；Create Database StudentUse StudentCreate Table Student(SNochar(20) primary key,SNamechar(20) ,SSexchar(4) default 男,SAgeint) ;在MySQL 中，每個(gè)表指明數(shù)據(jù)庫引擎，字符集比較好，用下列語句！Create Table Student(SNochar(20) primary key,SNamechar(20) ,SSexchar(4) default 男,SAgeint) ENGINE=MyISAM DEFAULT CHARSET=utf8 COL

3、LATE=utf8_bin;Create Table Course(CNochar(20) primary key,CNamechar(20) NOT NULL,CReditfloat,);Create Table SC(SNochar(20) NOT NULL,CNochar(20) NOT NULL,Gradefloat,Primary Key(SNo, CNo),Foreign Key(SNo) References Student(SNo) On Delete Cascade,Foreign Key(CNo) References Course(CNo);Create Table T(

4、TNochar(20) Primary Key,TNamechar(20) NOT NULL,TSexchar(4) default 男,birthday DateTime,deptchar(20),titlechar(20),address char(20);Create Table Salary(TNochar(20) NOT NULL,jbgzfloat,zwgzfloat,hjfloat,Foreign Key(TNo) References T(TNo) On Delete Cascade);2、用alter修改基本表（1）在已存在的學(xué)生表student中增加一個(gè)sdept（系）的新

5、的屬性列； alter table Student add Dept char(20);（2）將學(xué)生表student中sname屬性列的數(shù)據(jù)類型修改為變長字符串varchar(10)。alter able Student alter colum sname varchar(10)3、建立一個(gè)臨時(shí)表，然后將其刪除 Create Table temp (ANochar(20) NOT NULL,Bfloat, C char(10) ) Drop tabte temp實(shí)驗(yàn)二 建立與刪除索引1、用create index在學(xué)生表student的學(xué)號sno上建立聚簇索引。Create Clustered

6、 Index SNo_Index On Student(SNo);2、在學(xué)生表student中，為姓名sname建立非聚簇索引。 Create Index SName_Index On Student(SName);3、在課程表的課程號Cno上建立唯一索引。 Create Unique Index CNo_Index On Course(CNo);4、在選課表的學(xué)號sno、成績Grade上建立復(fù)合索引，要求學(xué)號為升序，學(xué)號相同時(shí)成績?yōu)榻敌?。Create Index SCNo_Index On SC(SNo ASC, Grade DESC);5、用drop刪除學(xué)生表student的索引。 Dro

7、p Index Student.SNo_Index;6、增加學(xué)生表student中姓名唯一約束。 Alter Table Student Add Unique(SName);7、增加學(xué)生表student中性別男、女唯一約束。 Alter Table Student Add Constraint:SSex check(SSex = 男 or SSex = 女);8、增加學(xué)生表student中年齡1825歲約束。Alter Table Student Add Constraint:SAge check(SAge = 18 And SAge = 25);9、增加選課表SC中學(xué)號sno的外碼約束。Al

8、ter Table SC Add Foreign Key(SNo) references Student(SNo);-實(shí)驗(yàn)三 數(shù)據(jù)的插入、更新及刪除操作1、用insert輸入數(shù)據(jù)。學(xué)生表student的數(shù)據(jù)991201 張三22男計(jì)算機(jī)系991202 李四 21男信息系991101王五23男數(shù)學(xué)系991102陳六19男計(jì)算機(jī)系991103吳七24女?dāng)?shù)學(xué)系000101劉八22女信息系Insert Into Student Values(991201, 張三, 男, 22, 計(jì)算機(jī)科學(xué)與技術(shù)系);Insert Into Student Values(991202, 李四, 男, 21, 信息科學(xué)系

9、);Insert Into Student Values(991101, 王五, 男, 23, 數(shù)理系);Insert Into Student Values(991102, 陳六, 男, 19, 計(jì)算機(jī)科學(xué)與技術(shù)系);Insert Into Student Values(991103, 吳七, 女, 24, 數(shù)理系);Insert Into Student Values(000101, 劉八, 女, 22, 信息科學(xué)系);課程表course的數(shù)據(jù)1 數(shù)學(xué)52 數(shù)據(jù)結(jié)構(gòu)43 程序設(shè)計(jì)24 數(shù)據(jù)庫原理35 操作系統(tǒng)3Insert Into Course Values(1, 數(shù)學(xué), 5);Inse

10、rt Into Course Values(2, 數(shù)據(jù)結(jié)構(gòu), 4);Insert Into Course Values(3, 程序設(shè)計(jì), 2);Insert Into Course Values(4, 數(shù)據(jù)庫原理, 3);Insert Into Course Values(5, 操作系統(tǒng), 3);選課表SC的數(shù)據(jù)991201190991201580991201385991201490991102185991102298000101291Insert Into SC Values(991201, 1, 90);Insert Into SC Values(991201, 5, 80);Insert

11、 Into SC Values(991201, 3, 85);Insert Into SC Values(991201, 4, 90);Insert Into SC Values(991102, 1, 85);Insert Into SC Values(991102, 2, 98);Insert Into SC Values(000101, 2, 91);基本表T的數(shù)據(jù)0001張三男1968-10信息副教授湘潭0002李四 女1956-11信息教授長沙1001王五男1973-07計(jì)算機(jī)講師湘潭1008陳六男1970-08計(jì)算機(jī)副教授北京Insert Into T Values(0001, 張三

12、, 男, 1968-10-10, 信息科學(xué)系, 副教授, 湘潭);Insert Into T Values(0002, 李四, 女, 1956-11-10, 信息科學(xué)系, 教授, 長沙);Insert Into T Values(1001, 王五, 男, 1973-07-20, 計(jì)算機(jī)科學(xué)與技術(shù)系, 講師, 湘潭);Insert Into T Values(1008, 陳六, 男, 1970-08-20, 計(jì)算機(jī)科學(xué)與技術(shù)系, 副教授, 北京);基本表Salary的數(shù)據(jù)00011000300130000021500500200010018002001000Insert Into Salary

13、Values(0001, 1000, 300, 1300);Insert Into Salary Values(0002, 1500, 500, 2000);Insert Into Salary Values(1001, 800, 200, 1000);*/2、用delete刪除數(shù)據(jù)記錄（1）刪除教師表T中教師號為0001的元組。（2）刪除教師表T中的全部數(shù)據(jù)。update t set birthday=1961-10-04 where Tno =0001 Delete From T;3、用 update更新數(shù)據(jù)記錄（1） 把0001號教師的基本工資加100。（2） 把所有教師的基本工資都加1

14、00。 Update Salary Set jbgz = jbgz + 100 Where TNo = 0001Update Salary Set jbgz = jbgz + 100實(shí)驗(yàn)四 數(shù)據(jù)的查詢1、簡單查詢，用select檢索（1）查詢所有學(xué)生的基本情況。select * from student;（2）查詢教師每月應(yīng)交納的個(gè)人所得稅。 select hj*0.005 as monthshui from Salary;（3）查詢張三與李四兩位同學(xué)的基本情況。 select * from student where sname=張三 or sname=李四;（4）查詢9911班學(xué)生的基本信

15、息（規(guī)定學(xué)生學(xué)號的前四位是班級號）。 select * from student where sno like 9911%;（5）查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 select sname,sage from student where sage2;2、多表查詢，用select檢索（1）查詢教師的收入情況，包括教師號、姓名及月總收入。select T.Tno,Tname, hj /不能寫成select Tno,因?yàn)門no不明確from T,Salarywhere T.Tno=Salary.Tno;（2）查詢每個(gè)學(xué)生的學(xué)號、姓名、選修課程及成績。 select student.sno

16、,sname,cno,gradefrom student,sc where student.sno=sc.sno;（3）查詢每一門課的間接先修課。 select CA.cno AS 課程號,CB.PreCourse AS 間接先修課號from course CA,course CBwhere CA.PreCourse=CB.cno and CB.PreCourse is not null;（4）查詢有相同地址的兩位教師的信息。 select *from T Txwhere Tx.address in (select address from T Ty where Tx.TnameTy.Tnam

17、e); select * from T Tx, T Ty where Tx. address=Ty. Address and Tx.TnameTy.Tname（5）查詢選修2號課程且成績在90分以上的所有學(xué)生。 select * from student,SCwhere student.sno=SC.sno and SC.cno=2 and SC.grade90;（6）查詢與王五在同一個(gè)系學(xué)習(xí)的學(xué)生。select *from studentwhere sdept=(select sdept from student where sname=王五);實(shí)驗(yàn)五 視圖1、建立男學(xué)生的視圖，屬性包括學(xué)號

18、、姓名、選修課程名和成績。 create view boystudent_view as select student.sno,sname,cno,gradefrom student,SCwhere student.ssex=男 and student.sno=SC. Sno and o=o;2、在男學(xué)生視圖中查詢平均成績大于80分的學(xué)生學(xué)號與姓名。 select sno,snamefrom boystudent_viewgroup by snohaving AVG(grade)803、建立信息系選修了1號課程的學(xué)生的視圖。 create view xinxi_view1 as select

19、student.sno,sname,ssex,sage from student,SC where student.sdept=信息 and student.sno=SC.sno and SC.cno=14、建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。 create view xinxi_view2 as select student.sno,sname,sage,ssex from student,SC where student.sdept=信息 and student.sno=SC.sno and SC.cno=1 and SC.grade905、建立計(jì)算機(jī)系選修了2號課程的

20、學(xué)生的視圖。create view jisuanji_viewas select student.sno,sname,sage,ssexfrom student,SCwhere student.sdept=計(jì)算機(jī) and student.sno=SC.sno and SC.cno=26、建立一個(gè)學(xué)生出生年份的視圖。create view year_viewas select sno,sname,2016-sage as birthdayfrom student7、建立一個(gè)計(jì)算機(jī)系學(xué)生的視圖，并要求在進(jìn)行修改、插入操作時(shí)，仍然要確保視圖只有計(jì)算機(jī)系的學(xué)生。create view jisuanji

21、2_viewas select student.sno,sname,sage,ssexfrom studentwhere sdept=計(jì)算機(jī)with check option8、向?qū)W生視圖中插入一條新記錄（951101，錢進(jìn)，男，20） create view student_view1 as select sno,sname,ssex,sage from student;insert into student_view1 values(951101,錢進(jìn),男,20)9、將學(xué)生視圖中學(xué)號為991101的學(xué)生姓名改為“劉平平”。 update student_view1 set sname=劉平

22、平 where sno=99110110、刪除計(jì)算機(jī)系視圖中學(xué)號為991201的記錄。 deletefrom jisuanji2_view where sno=99120111、刪除學(xué)生出生年份的視圖。drop view year_view;實(shí)驗(yàn)六 集合函數(shù)和授權(quán)語句應(yīng)用1、使用select語句查詢（1）9911班學(xué)生的人數(shù)和平均成績（規(guī)定學(xué)生學(xué)號的前四位是班級號）SELECT COUNT(sno) as 總?cè)藬?shù)， AVG(sc.grade)AS 平均分 FROM scWHERE sno like 9912%（2）每個(gè)學(xué)生的學(xué)號和相應(yīng)的平均成績SELECT sno, AVG(grade) FR

23、OM,scGROUP BY student.sno（3）成績大于90分的學(xué)生學(xué)號SELECT sno,MAX(sc.grade) as maxgrade FROM scGROUP BY snoHAVING MAX(grade)90（4）月工資大于平均工資的教師號SELECT TNo FROM Salary WHERE hj=( SELECT AVG(hj) from Salary ) 2、使用GRANT語句，把對基本表STUNT、SC、COURSE的使用權(quán)限授給其它用戶。3、使用GRANT語句，讓用戶LI有權(quán)在當(dāng)前庫中創(chuàng)建視圖。4、使用REVOKE語句，不允許ZHAO這個(gè)用戶在當(dāng)前庫中創(chuàng)建視圖

24、。5、實(shí)驗(yàn)完成后，撤消建立的基本表和視圖。 實(shí)驗(yàn)七 創(chuàng)建、執(zhí)行、修改和刪除存儲過程1、建立查詢計(jì)算機(jī)系女學(xué)生的存儲過程。CREATE PROCEDURE SELECT_WOMANAS SELECT * FROM student WHERE SSEX=女2、執(zhí)行剛建立存儲過程。 exec select_woman3、修改剛建立的存儲過程。 ALTER PROCEDURE SELECT_WOMANAS SELECT * FROM studentWHERE SSEX=男4、建立一個(gè)按性別查詢學(xué)生信息的帶參數(shù)的存儲過程。CREATE PROCEDURE SELECT_SEX(vssex char(4)

25、 )ASSELECT * FROM student WHERE student.ssex=vssex*/exec SELECT_SEX vssex=男;5、刪除剛建立的存儲過程。DROP PROCEDURE SELECT_WOMAN 實(shí)驗(yàn)八 觸發(fā)器的插入、刪除、更新和創(chuàng)建1、對student表創(chuàng)建delete觸發(fā)器，當(dāng)刪除某個(gè)學(xué)生記錄時(shí)，同時(shí)在選課表SC中刪除該學(xué)生的選課記錄。 create trigger t1 on student for delete as delete from sc where sno in (select sno from deleted) /*當(dāng)表student 和SC 都有 sno為000101 delete from student where sno=000101 select * from student 看結(jié)果2、對student表創(chuàng)建update 觸發(fā)器，當(dāng)對student表進(jìn)行修改時(shí)，顯示被修改的記錄。2 create trigger t2 on student for update as select * from deletedInsert Into Student Values(00