mysql数据库实验答案.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 Student default character set utf8 default COLLATE utf8_bin;Use Student;Create Table Student(SNo char(20) primary key,SName char(20) ,SSex char(4) default 男,SAge int) ENGINE=InnoDB;Create Table Course(CNochar(20) primary key,CName char(20) NOT NULL,CRedit float) E

3、NGINE=InnoDB;Create Table SC(SNo char(20) NOT NULL,CNochar(20) NOT NULL,Grade float,Primary Key(SNo, CNo),Foreign Key(SNo) References Student(SNo) On Delete Cascade,Foreign Key(CNo) References Course(CNo) ENGINE=InnoDB;Create Table T(TNo char(20) Primary Key,TName char(20) NOT NULL,TSex char(4) defa

4、ult 男,2birthday DateTime,dept char(20),title char(20),address char(20) ENGINE=InnoDB;Create Table Salary(TNo char(20) NOT NULL,jbgz float,zwgz float,hj float,Foreign Key(TNo) References T(TNo) On Delete Cascade) ENGINE=InnoDB;2、用 alter 修改基本表（1）在已存在的学生表 student 中增加一个 sdept（系）的新的属性列；alter table Studen

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

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

7、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 Constraint:SAge check(SAge = 18 And SAge 2;2、多表查询，用 select 检索6（1

8、）查询教师的收入情况，包括教师号、姓名及月总收入。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 课程号,CB.PreCourse AS 间接先修课号from course CA,course CBwhere C

9、A.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 and SC.grade90;（6）查询与王五在同一个系学习的学生。select *from studentwh

10、ere sdept=(select sdeptfrom student where sname=王五);实验五 视图1、建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。create view boystudent_view as select student.sno,sname,cno,gradefrom student,SCwhere student.ssex=男 and student.sno=SC. sno;2、在男学生视图中查询平均成绩大于 80 分的学生学号与姓名。select sno,snamefrom boystudent_view7group by sno,snameh

11、aving 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 student.sno,sname,sage,ssexfrom student,SCwhere student.sde

12、pt=信息 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 view year_viewas select student.sno,sname,2007-sage as birthd

13、ayfrom student7、建立一个计算机系学生的视图，并要求在进行修改、插入操作时，仍然要确保视图只有计算机系的学生。create view jisuanji2_viewas select student.sno,sname,sage,ssexfrom studentwhere sdept=计算机with check option8、向学生视图中插入一条新记录（951101，钱进，男，20）create view student_view1as select sno,sname,ssex,sagefrom student;insert into student_view1 values(

14、951101,钱进,男,20)9、将学生视图中学号为 991101 的学生姓名改为“刘平平” 。8update student_view1 set sname=刘平平where sno=99110110、删除计算机系视图中学号为 991201 的记录。deletefrom jisuanji2_view where sno=99120111、删除学生出生年份的视图。drop view year_view;实验六 集合函数的应用1、使用 select 语句查询（1）9911 班学生的人数和平均成绩（规定学生学号的前四位是班级号）SELECT COUNT(sno) as 总人数， AVG(sc.gr

15、ade)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)90（4）月工资大于平均工资的教师号SELECT TNo FROM Salary WHERE hj=( SELECT AVG(hj) from Salary ) 2、实验完成后，撤消建立的基本表和视图。 实验

16、七 创建、执行、修改和删除存储过程1、建立查询计算机系女学生的存储过程。Use student;CREATE PROCEDURE SELECT_WOMAN()9BEGIN SELECT * FROM student where ssex=女;end;call select_wanman();2、定义一个存储过程不带参数，作用是查询选修了课程学生的学号，并执行这个存储过程。use student;Create procedure numofsc()BEGIN select sno from SC group by snohaving count(*)0;End;call numofsc();3、

17、定义一个带输出参数的存储过程，作用是查询 Student 表中的学生个数，并赋值给输出参数,执行该存储过程，同时通过输出参数来输出学生个数。 CREATE PROCEDURE proc1(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM student; END; 执行 CALL proc1 ( p0 ); SELECT p0 AS 学生人数; 4、定义一个带输出参数和输出参数的存储过程，执行该存储过程，通过输入参数学号,查到到姓名,同时输入姓名,。use student;Create procedure getnamebysno(in xh char

18、(10),out name char(20)BeginSelect sname into name from student where sno=xh;End;执行set name=null;call getnamebysno(000105,name);10select name;5、修改刚建立的存储过程。ALTER PROCEDURE SELECT_ 6、删除刚建立的存储过程。DROP PROCEDURE SELECT_WOMAN实验八 触发器的插入、删除、更新和创建1、 对 student 表创建 delete 触发器，当删除某个学生记录时，同时在选课表 SC 中删除该学生的选课记录。my

19、sql 通过use student;Insert Into Student Values(000101, 刘八, 女, 22, 信息科学系);Insert Into SC Values(000101, 2, 91);select * from student ; select * from sc ; create trigger t1 AFTER delete on student FOR EACH ROW BEGINDelete from SC WHERE sno = old.sno;END;2、 在 student 表上定义了一个 update 触发程序，用于检查更新 sage 时将使用的新值，小于16 时取得 16,大于 40 时取 40，并更改值。use student;create trigger upd_check before update on student FOR EACH ROW beginif new.sage60 thenset new.sage=40;end if;end;