mysql数据库实验答案.docx

1、mysql数据库实验答案实验一 创立、修改数据库和表构造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) 工资表教师号，根本工资，职务工资，合计，Salary(Tno，jbgz，zw

2、gz，hj)；Create Database Studentdefault 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( CNo char(20) primary key, CName char(20) NOT NULL, CRedit float) ENGINE=I

3、nnoDB;Create Table SC( SNo char(20) NOT NULL, CNo char(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) de

4、fault 男, birthday 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 Student

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

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

7、学生表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 = 25);9、增加选课表SC中学号sno的外码约束。Alter Table SC Add Foreign Key(SN

8、o) references Student(SNo);-实验三 数据的插入、更新及删除操作1、用insert输入数据。学生表student的数据991201 张三22男计算机系991202 李四 21男信息系991101王五23男数学系991102陈六19男计算机系991103吴七24女数学系000101刘八22女信息系Insert Into Student Values(991201, 张三, 男, 22, 计算机科学与技术系);Insert Into Student Values(991202, 李四, 男, 21, 信息科学系);Insert Into Student Values(99

9、1101, 王五, 男, 23, 数理系);Insert Into Student Values(991102, 陈六, 男, 19, 计算机科学与技术系);Insert Into Student Values(991103, 吴七, 女, 24, 数理系);Insert Into Student Values(000101, 刘八, 女, 22, 信息科学系);课程表course的数据1 数学52 数据构造43 程序设计24 数据库原理35 操作系统3Insert Into Course Values(1, 数学, 5);Insert Into Course Values(2, 数据构造,

10、4);Insert Into Course Values(3, 程序设计, 2);Insert Into Course Values(4, 数据库原理, 3);Insert Into Course Values(5, 操作系统, 3);选课表SC的数据991201190991201580991201385991201490991102185991102298000101291Insert Into SC Values(991201, 1, 90);Insert Into SC Values(991201, 5, 80);Insert Into SC Values(991201, 3, 85);

11、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的数据0001张三男1968-10信息副教授湘潭0002李四 女1956-11信息教授长沙1001王五男1973-07计算机讲师湘潭1008陈六男1970-08计算机副教授北京Insert Into T Values(0001, 张三, 男, 1968-10-10, 信息科学系, 副教授, 湘潭

12、);Insert Into T Values(0002, 李四, 女, 1956-11-10, 信息科学系, 教授, 长沙);Insert Into T Values(1001, 王五, 男, 1973-07-20, 计算机科学与技术系, 讲师, 湘潭);Insert Into T Values(1008, 陈六, 男, 1970-08-20, 计算机科学与技术系, 副教授, 北京);根本表Salary的数据00011000300130000021500500200010018002001000Insert Into Salary Values(0001, 1000, 300, 1300);I

13、nsert Into Salary Values(0002, 1500, 500, 2000);Insert Into Salary Values(1001, 800, 200, 1000);*/2、用delete删除数据记录1删除教师表T中教师号为0001的元组。2删除教师表T中的全部数据。update t set birthday=1961-10-04 where Tno =0001Delete From T;3、用 update更新数据记录（1）把0001号教师的根本工资加100。（2）把所有教师的根本工资都加100。Update Salary Set jbgz = jbgz + 100

14、 Where TNo = 0001Update Salary Set jbgz = jbgz + 100实验四 数据的查询1、简单查询，用select检索1查询所有学生的根本情况。select * from student;2查询教师每月应交纳的个人所得税。 select hj*0.005 as monthshui from Salary;3查询张三与李四两位同学的根本情况。select *from student where sname=张三 or sname=李四;4查询9911班学生的根本信息规定学生学号的前四位是班级号。select *from studentwhere sno lik

15、e 9911%;5查询所有年龄在20岁以下的学生姓名及其年龄。 select sname,sage from student where sage2;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,scwhere student.sno=sc.sno;3查询每一

16、门课的间接先修课。 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 addressfrom T Tywhere Tx.TnameTy.Tname);select *from T Tx, T Ty where Tx. address=Ty. Address and Tx.Tname

17、Ty.Tname5查询选修2号课程且成绩在90分以上的所有学生。select *from student,SCwhere student.sno=SC.sno and SC.cno=2 and SC.grade90;6查询与王五在同一个系学习的学生。select *from studentwhere sdept=(select sdeptfrom studentwhere sname=王五);实验五 视图1、建立男学生的视图，属性包括学号、姓名、选修课程名和成绩。 create view boystudent_view as select student.sno,sname,cno,grade

18、 from student,SC where student.ssex=男 and student.sno=SC. sno;2、在男学生视图中查询平均成绩大于80分的学生学号与姓名。select sno,snamefrom boystudent_viewgroup by sno,snamehaving AVG(grade)803、建立信息系选修了1号课程的学生的视图。create view xinxi_view1 as select student.sno,sname,ssex,sagefrom student,SC where student.sdept=信息 and student.sno

19、=SC.sno and SC.cno=14、建立信息系选修了1号课程且成绩在90分以上的学生的视图。create view xinxi_view2 as select student.sno,sname,sage,ssexfrom student,SC where student.sdept=信息 and student.sno=SC.sno and SC.cno=1 and SC.grade905、建立计算机系选修了2号课程的学生的视图。create view jisuanji_viewas select student.sno,sname,sage,ssex from student,SC

20、 where student.sdept=计算机 and student.sno=SC.sno and SC.cno=26、建立一个学生出生年份的视图。create view year_viewas select student.sno,sname,2007-sage as birthdayfrom student7、建立一个计算机系学生的视图，并要求在进展修改、插入操作时，仍然要确保视图只有计算机系的学生。create view jisuanji2_viewas select student.sno,sname,sage,ssex from student where sdept=计算机wi

21、th check option8、向学生视图中插入一条新记录951101，钱进，男，20 create view student_view1 as select sno,sname,ssex,sage from student; insert into student_view1 values(951101,钱进,男,20)9、将学生视图中学号为991101的学生姓名改为“刘平平。update student_view1 set sname=刘平平 where sno=99110110、删除计算机系视图中学号为991201的记录。deletefrom jisuanji2_view where

22、sno=99120111、删除学生出生年份的视图。drop view year_view;实验六 集合函数的应用1、使用select语句查询19911班学生的人数和平均成绩规定学生学号的前四位是班级号SELECT COUNT(sno) as 总人数，AVG(sc.grade)AS 平均分 FROM scWHERE sno like 9912%2每个学生的学号和相应的平均成绩SELECT sno,AVG(grade) FROM,scGROUP BY student.sno3成绩大于90分的学生学号SELECT sno,MAX(sc.grade) as maxgrade FROM scGROUP

23、BY snoHAVING MAX(grade)904月工资大于平均工资的教师号SELECT TNo FROM Salary WHERE hj=( SELECT AVG(hj) from Salary ) 2、实验完成后，撤消建立的根本表和视图。实验七 创立、执行、修改和删除存储过程1、建立查询计算机系女学生的存储过程。Use student;CREATE PROCEDURE SELECT_WOMAN()BEGIN SELECT * FROM student where ssex=女;end;call select_wanman();2、定义一个存储过程不带参数，作用是查询选修了课程学生的学号，

24、并执行这个存储过程。use student;Create procedure numofsc() BEGIN select sno from SC group by sno having count(*)0;End;call numofsc();3、定义一个带输出参数的存储过程，作用是查询Student表中的学生个数，并赋值给输出参数,执行该存储过程，同时通过输出参数来输出学生个数。CREATE PROCEDURE proc1(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM student; END; 执行 CALL proc1 ( p0 );SELE

25、CT p0 AS 学生人数; 4、定义一个带输出参数和输出参数的存储过程，执行该存储过程，通过输入参数学号,查到到姓名,同时输入姓名,。use student;Create procedure getnamebysno(in xh char (10),out name char(20)BeginSelect sname into name from student where sno=xh;End;执行set name=null;call getnamebysno(000105,name);select name;5、修改刚建立的存储过程。ALTER PROCEDURE SELECT_6、删除

26、刚建立的存储过程。DROP PROCEDURE SELECT_WOMAN实验八 触发器的插入、删除、更新和创立1、对student表创立delete触发器，当删除某个学生记录时，同时在选课表SC中删除该学生的选课记录。mysql 通过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 stud

27、ent 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 begin if new.sage60 then set new.sage=40; end if;end;select * from student; update student se

28、t sage=2 where sno=000105; select * from student ;3、在student表上定义了一个insert触发程序，用于检查插入ssex时只能取男和女，输入其它时取NULL。use student;create trigger ins_check before insert on student FOR EACH ROW begin if new.ssex男 and new.ssex女 then set new.ssex=null; end if;end; select * from student;Insert Into Student Values(000106, 陈规则, 五, 22, 信息科学系);select * from student;4、alter trigger 5、drop trigger on 表名;6、create database trigg;CREATE TABLE t_a ( id smallint(1) unsigned NOT NULL AUTO_INCREMENT, username varchar(20) DEFAULT NULL, groupid