end数据库实验答案.doc

1、1实验一 创建、修改数据库和表结构1、用 create 建立教学数据库的五个基本表：（1）学生表（学号，姓名，性别，年龄） ，student(Sno， sname， ssex，sage) ；（2）课程表（课程号，课程名，学分） ，Course (Cno, Cname, credit) ；（3）选课表（学号，课程号，成绩） ，SC (Sno, Cno, grade ) ；(4) 教师表（教师号，姓名，性别，出生年月，系部，职称，地址） ，T(Tno， Tname，ssex，birthday，dept，title，address) ；(5) 工资表（教师号，基本工资，职务工资，合计） ，Salar

2、y(Tno，jbgz，zwgz，hj)；Create Database StudentUse StudentCreate Table Student(SNo char(20) primary key,SName char(20) ,SSex char(4) default 男,SAge int) ;在 MySQL 中，每个表指明数据库引擎，字符集比较好，用下列语句！Create Table Student(SNo char(20) primary key,SName char(20) ,SSex char(4) default 男,SAge int) ENGINE=MyISAM DEFAULT

3、 CHARSET=utf8 COLLATE=utf8_bin;Create Table Course(CNochar(20) primary key,CName char(20) NOT NULL,CRedit float,);Create Table SC(SNo char(20) NOT NULL,CNochar(20) NOT NULL,Grade float,2Primary Key(SNo, CNo),Foreign Key(SNo) References Student(SNo) On Delete Cascade,Foreign Key(CNo) References Cours

4、e(CNo);Create Table T(TNo char(20) Primary Key,TName char(20) NOT NULL,TSex char(4) default 男,birthday DateTime,dept char(20),title char(20),address char(20);Create Table Salary(TNo char(20) NOT NULL,jbgz float,zwgz float,hj float,Foreign Key(TNo) References T(TNo) On Delete Cascade);2、用 alter 修改基本表

5、（1）在已存在的学生表 student 中增加一个 sdept（系）的新的属性列；alter table Student add Dept char(20);（2）将学生表 student 中 sname 属性列的数据类型修改为变长字符串 varchar(10)。alter able Student alter colum sname varchar(10)3、建立一个临时表，然后将其删除Create Table temp (ANochar(20) NOT NULL,B float, C char(10) )Drop tabte temp实验二 建立与删除索引1、用 create index

6、在学生表 student 的学号 sno 上建立聚簇索引。Create Clustered Index SNo_Index On Student(SNo);2、在学生表 student 中，为姓名 sname 建立非聚簇索引。3Create Index SName_Index On Student(SName);3、在课程表的课程号 Cno 上建立唯一索引。Create Unique Index CNo_Index On Course(CNo);4、在选课表的学号 sno、成绩 Grade 上建立复合索引，要求学号为升序，学号相同时成绩为降序。Create Index SCNo_Index O

7、n SC(SNo ASC, Grade DESC);5、用 drop 删除学生表 student 的索引。Drop Index Student.SNo_Index;6、增加学生表 student 中姓名唯一约束。Alter Table Student Add Unique(SName);7、增加学生表 student 中性别男 、 女唯一约束。Alter Table Student Add Constraint:SSex check(SSex = 男 or SSex = 女);8、增加学生表 student 中年龄 1825 岁约束。Alter Table Student Add Constr

8、aint:SAge check(SAge = 18 And SAge 2;2、多表查询，用 select 检索（1）查询教师的收入情况，包括教师号、姓名及月总收入。select T.Tno,Tname, hj /不能写成 select Tno,因为 Tno 不明确from T,Salarywhere T.Tno=Salary.Tno;（2）查询每个学生的学号、姓名、选修课程及成绩。select student.sno,sname,cno,gradefrom student,sc where student.sno=sc.sno;（3）查询每一门课的间接先修课。select CA.cno AS

9、课程号,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.Tname（5）查询选修 2 号课程且成绩在 90 分以上的所有学生。select * from student,SCwhere student.sno=SC.sno and SC.cno=2

10、 and SC.grade90;（6）查询与王五在同一个系学习的学生。select *from studentwhere sdept=(select sdeptfrom student where sname=王五);7实验五 视图1、建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。create view boystudent_view as select student.sno,sname,cno,gradefrom student,SCwhere student.ssex=男 and student.sno=SC. Sno and o=o;2、在男学生视图中查询平均成绩大于 80

11、分的学生学号与姓名。select sno,snamefrom boystudent_viewgroup by snohaving AVG(grade)803、建立信息系选修了 1 号课程的学生的视图。create view xinxi_view1as select student.sno,sname,ssex,sagefrom student,SCwhere student.sdept=信息 and student.sno=SC.sno and SC.cno=14、建立信息系选修了 1 号课程且成绩在 90 分以上的学生的视图。create view xinxi_view2as select

12、student.sno,sname,sage,ssexfrom student,SCwhere student.sdept=信息 and student.sno=SC.sno and SC.cno=1 and SC.grade905、建立计算机系选修了 2 号课程的学生的视图。create view jisuanji_viewas select student.sno,sname,sage,ssexfrom student,SCwhere student.sdept=计算机 and student.sno=SC.sno and SC.cno=26、建立一个学生出生年份的视图。create vi

13、ew year_viewas select sno,sname,2016-sage as birthdayfrom student7、建立一个计算机系学生的视图，并要求在进行修改、插入操作时，仍然要确保视图只有计算机系的学生。create view jisuanji2_view8as select student.sno,sname,sage,ssexfrom studentwhere sdept=计算机with check option8、向学生视图中插入一条新记录（951101，钱进，男，20）create view student_view1as select sno,sname,sse

14、x,sagefrom student;insert into student_view1 values(951101,钱进,男,20)9、将学生视图中学号为 991101 的学生姓名改为“刘平平” 。update student_view1 set sname=刘平平where sno=99110110、删除计算机系视图中学号为 991201 的记录。deletefrom jisuanji2_view where sno=99120111、删除学生出生年份的视图。drop view year_view;实验六 集合函数和授权语句应用1、使用 select 语句查询（1）9911 班学生的人数和

15、平均成绩（规定学生学号的前四位是班级号）SELECT COUNT(sno) as 总人数， AVG(sc.grade)AS 平均分 FROM scWHERE sno like 9912%（2）每个学生的学号和相应的平均成绩SELECT sno, AVG(grade) FROM,scGROUP BY student.sno（3）成绩大于 90 分的学生学号SELECT sno,MAX(sc.grade) as maxgrade FROM scGROUP BY snoHAVING MAX(grade)909（4）月工资大于平均工资的教师号SELECT TNo FROM Salary WHERE h

16、j=( SELECT AVG(hj) from Salary ) 2、使用 GRANT 语句，把对基本表 STUNT、SC、COURSE 的使用权限授给其它用户。3、使用 GRANT 语句，让用户 LI 有权在当前库中创建视图。4、使用 REVOKE 语句，不允许 ZHAO 这个用户在当前库中创建视图。5、实验完成后，撤消建立的基本表和视图。 实验七 创建、执行、修改和删除存储过程1、建立查询计算机系女学生的存储过程。CREATE PROCEDURE SELECT_WOMANAS SELECT * FROM student WHERE SSEX=女2、执行刚建立存储过程。exec select

17、_woman3、修改刚建立的存储过程。ALTER PROCEDURE SELECT_WOMANAS SELECT * FROM studentWHERE SSEX=男4、建立一个按性别查询学生信息的带参数的存储过程。CREATE PROCEDURE SELECT_SEX(vssex char(4) )ASSELECT * FROM student WHERE student.ssex=vssex*/exec SELECT_SEX vssex=男;5、删除刚建立的存储过程。DROP PROCEDURE SELECT_WOMAN实验八 触发器的插入、删除、更新和创建1、对 student 表创建

18、delete 触发器，当删除某个学生记录时，同时在选课表 SC 中删除该学生的选课记录。create trigger t110on student for delete asdelete from sc where sno in (select sno from deleted)/*当表 student 和 SC 都有 sno 为000101delete from student where sno=000101select * from student 看结果2、对 student 表创建 update 触发器，当对 student 表进行修改时，显示被修改的记录。2 create trig

19、ger t2on student for update asselect * from deletedInsert Into Student Values(00001, 王五, 男, 23, 数理系);Update student Set sage = sage + 1 where sno=00001 看结果3、对 SC 表创建 insert 触发器，当对 SC 表插入一条记录时，检查 SC 表中的学号在student 表中是否存在，如果不存在，则不允许插入新记录。create trigger t3on sc for insert asif(select count(*) from student where sno in(select sno from inserted)rollback transactioncreate trigger t3on sc for insert asbeginif (select count(*) from student where sno in (select sno from inserted)=0begin print 无效的记录rollback transactionendend4、用 alter trigger 修改以上编写的触发器。5、用 drop trigger 删除以上编写的触发器。create trigger t4