广工数据库实验报告.docx
《广工数据库实验报告.docx》由会员分享，可在线阅读，更多相关《广工数据库实验报告.docx（66页珍藏版）》请在冰豆网上搜索。
广工数据库实验报告
实验报告
课程名称___数据库系统实验报告_
题目名称_______实验报告_______
专业班级______13级计科九班___
学号_______
学生姓名________________
指导教师何晓桃
2015年11月15日
实验二
一、实验目的
通过对Mysql语句的操作，加深对Mysql的使用，以及熟悉对Mysql语句的运用，提高运用数据库的能力。
二、实验平台
1、数据库管理系统：
MySql5.6.24
2、可视化管理工具：
NavicatforMysql
三、实验准备
对数据库有一定的了解，以及对Mysql语句有了一定的熟悉。
四、实验内容
建数据库student------测试用
先创建一个学生数据库：
数据库名称为：
student
测试所要用到的表：
学生表：
Student（Sno,Sname,Ssex,Sage,Sdept）
课程表：
Course（Cno,Cname,Cpno,Ccredit）
学生选课表：
SC（Sno,Cno,Grade）
/*注释:
黑色加粗是所在表的主键，红色倾斜是所在表的外键*/
插入数据后得到：
Student表
Course表
Sc表
1、数据定义测试（CREATE、DROP）
（1）、模式的定义与删除
【例1】
定义一个学生－课程模式S-T
Createschema”S-T”authorizationroot;
操作失败！
原因：
MySql不支持创建模式
【例2】
Createschemaauthorizationroot;
操作失败！
原因：
MySql不支持创建模式
【例3】
createschemaTESTauthorizationroot
createtableTAB1（COL1smallint,
COL2int,
COL3char（20）,
COL4numeric（10,3）,
COL5decimal（5,2）
）;
操作失败！
原因：
MySql不支持创建模式
【例4】
DropschemaTESTcascade;
操作失败！
原因：
MySql不支持创建模式，无法创建模式，故无法删除模式
（2）、基本表的定义、删除与修改
【例5】
*建立学生表：
createtableStudent
（Snochar（9）PRIMARYKEY,
Snamechar（20）UNIQUE,
Ssexchar
（2）,
Sagesmallint,
Sdeptchar（20）
）;
结果截图如下，同时可以通过descStudent查询表的结构：
【例6】
*建立一个课程表Course:
createtableCourse
（Cnochar（4）primarykey,
Cnamechar（40）,
Cpnochar（4）,
Ccreditsmallint,
foreignkeyCpnoreferencesCourse（Cno）
）;
操作失败!
解决方法：
重写定义外键的sql语句，
改为constraintc_keyforeignkey（Cpno）referencesCourse（Cno）
结果如下：
为了方便后面测试的需要，把加在Course中的外键约束去掉：
altertableCoursedropforeignkeyc_key;
【例7】
*建立学生选修课本表SC
CREATETABLESC
（
SnoChar（9）,
CnoCHAR（4）,
GradeSMALLINT,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
FOREIGNKEY（Cno）REFERENCESCourse（Cno）
）;
*修改基本表
【例8】
向Student表中增加“入学时间列”，数据类型为日期型
语句：
altertableStudentaddS_entranceDate
注：
虽然在dos界面上新增一列列名是Extra，但是在可视化界面上是显示列名是S_entrance
【例9】
将年龄的数据类型由字符型改为整数
语句：
altertableStudentaltercolumnSageint
操作失败！
解决方法：
把语句改为altertableStudentmodifySageInt;
【例10】
增加课程名称必须取唯一值的约束条件
语句：
altertableCourseaddunique（Cname）;
【例11】
删除Student表
DroptableStudentCASCADE;
操作失败！
原因：
MySql不支持CASCADE
解决方法：
删除Student表之前，先去除Student表的约束。
【例12】
createviewIS_Student
as
selectSno,Sname,Sage
fromStudent
whereSdept=‘IS’;
（3）、索引的建立与删除
【例13】
createclusterindexStusnameonStudent（Sname）;
创建失败！
在此先建立一个普通索引，供后面测试用：
createindexIndexnameonStudent（Sname）;
【例14】
createuniqueindexStusnoonStudent（Sno）;
createuniqueindexCoucnoonCourse（Cno）;
createuniqueindexSCnoonSC（SnoASC,CnoDESC）;
【例15】
删除Student表的Indexname索引
dropindexIndexname;
操作失败！
原因：
语法格式不正确
解决方法：
把语句修改为dropindexIndexnameonStudent;
2、数据查询测试（SELECT）
（1）、单表查询
【例1】
查询全体学生的学号和姓名
selectSno,SnamefromStudent;
【例2】
查询全体学生的姓名、学号、所在系
selectSname,Sno,SdeptfromStudent;
【例3】
查询全体学生的详细记录
Select*fromStudent;
【例4】
查询全体学生的姓名及其出生年份
selectSname,2004-SagefromStudent;
【例5】
查询全体学生的姓名、出生年份和所在的院系，用小写字母表示院系
selectSname,’YearofBirth:
’,2004-Sage,lower（Sdept）fromStudent;
【例6】
查询选修了课程的学生学号
selectSnofromSC;
【例7】
查询计算机系全体学生的名单
SelectSnamefromStudentwhereSdept=’CS’;
【例8】
查询年龄在20岁以下的学生姓名及其年龄
SelectSname,SagefromStudentwhereSage<20;
【例9】
查询考试成绩有不及格的学生的学号
SelectdistinctSnofromSCwhereGrade<60;
【例10】
查询年龄在20~30岁之间的学生的姓名系别和年龄
selectSname,Sdept,SagefromStudentwhereSagebetween20and23;
【例11】
查询年龄不在20~30岁之间的学生的姓名系别和年龄
selectSname,Sdept,SagefromStudentwhereSagenotbetween20and23;
【例12】
查询计算机科学系、数学系和信息系学生的姓名和性别
selectSname,SsexfromStudentwhereSdeptin（‘CS’,’MA’,’IS’）;
【例13】
查询既不是计算机科学系、数学系，也不是信息系的学生的习姓名和性别
SelectSname,SsexfromStudentwhereSdeptnotin（'CS','MA','IS'）;
【例14】
查询学好为200215121的学生的详细情况
Select*fromStudentwhereSnolike'200215121';
等价于select*fromStudentwhereSno=’200215121’;
【例15】
查询所有姓刘的学生、学号和性别
SelectSname,Sno,SsexfromStudentwhereSnamelike'刘%';
【例16】
查询姓‘欧阳且全名为3个汉字的学生的姓名
SelectSnamefromstudentwhereSnamelike‘欧阳_’;
【例17】
查询名字中第二字为“阳”子的学生的姓名和学号
SelectSname,SnofromStudentwhereSnamelike‘_阳%’;
【例18】
查询所有不姓刘的学生的姓名
SelectSname,Sno,SsexfromStudentwhereSnamenotlike‘刘%’;
【例19】
由于CS中无DB_Design课程，现在添加上去
查询DB——Design课程的课程号和学分。
SelectCno,CcreditfromCoursewhereCnamelike‘DB\_Design’escape’\’;
操作失败！
解决方法：
直接SelectCno,CcreditfromCoursewhereCnamelike'DB_Design';
原因是MySql能够识别转义字符
【例20】
查询以DB开头，且倒数第三个字符为i的课程的详细情况
SelectCno,CcreditfromCoursewhereCnamelike‘DB\_%i__‘escape‘\’;
操作失败！
解决方法：
直接改为SelectCno,CcreditfromCoursewhereCnamelike‘DB%i__’;
原因是MySql能够识别转义字符
【例21】
查询缺少成绩的学生的学号和相应的课程号
SelectSno,CnofromSCwhereGradeisnull;
【例22】
查询有成绩的学生的学号和相应的课程号
SelectSno,CnofromSCwhereGradeisnotnull;
【例23】
查询计系年龄在20岁一下的学生姓名
SelectSnamefromStudentwhereSdept=’CS’andSage<20;
【例24】
查询选修了3号课程的学生的学好及其成绩，查询结果按分数的降序排列
SelectSno,GradefromSCwhereCno=’3’orderbyGradedesc;
【例25】
查询全体学生情况，结果按所在系的序号排列，同一系中的学生按年龄降序排列
Select*fromStudentorderbySdept,Sagedesc;
【例26】
查询学生总人数
Selectcount（*）fromStudent;
【例27】
查询了选修了课程的学生人数
Selectcount（DistinctSno）fromSC;
【例28】
计算1号课程的学生平均成绩
Selectavg（Grade）fromSCwhereCno=’1’;
【例29】
查询选修1号课程的学生最高分数
SelectMax（Grade）fromSCwhereCno=’1’;
【例30】
查询学生200215012选修课程的总分数
Selectsum（Grade）fromSC,CoursewhereSno=’200215012’andSC.Cno=Course.Cno;
【例31】
求各个课程号及相应的选课人数。
SelectCno,count（Sno）fromSCgroupbyCno;
【例32】
查询选修了三门以上课程的学生学号
SelectSnofromSCgroupbySnohavingcount（*）>3;
（2）、连接的查询
【例33】
查询每个学生极其选修课的情况
SelectStudent.*,Sc.*fromStudent,SCwhereStudent.Sno=SC.sno;
【例34】
对例33用自然连接完成
SelectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradefromStudent,ScwhereStudent.Sno=Sc.sno;
【例35】
查询每一门课的间接先修课
SelectFIRST.Cno,SECOND.Cpno
FromCourseFIRST,CourseSECOND
WhereFIRST.Cpno=SECOND.Cno;
【例36】
改写例33，使用外连接
SelectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
fromStudentLEFTJOINSC
on（Student.Sno=SC.Sno）;
【例37】
查询选修2号课程且成绩在90分以上的所有学生
SelectStudent.Sno,SnamefromStudent,SCwhereStudent.Sno=SC.Snoand
SC.Cno=’2’andSC.Grade>90;
【例38】
查询每个学生的学号，姓名。
选修的课程名及成绩
SelectStudent.Sno,Sname,Cname,Grade
FromStudent,SC,Course
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cno;
（3）、嵌套查询
【例39】
查询与刘晨在同一个系的学生
SelectSno,Sname,SdeptfromStudentwhereSdeptin
（selectSdeptfromStudentwhereSname=’刘晨’）;
【例40】
查询选修了课程名为信息系统的学生学号和姓名
SelectSno,SnamefromStudentwhereSnoin（SelectSnofromSCwhereCnoin（selectCnofromCoursewhereCname=’信息系统’））;
【例41】
找出每个学生超过他选修课程平均分的课程号
SelectSno,Cno
fromSCx
whereGrade>=（selectAVG（Grade）
fromSCy
wherey.Sno=x.Sno）;
【例42】
查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
SelectSname,SagefromStudentwhereSage’CS’;
【例43】
查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄
SelectSname,SagefromStudentwhereSage’CS’;
【例44】
查询选修了1号课的学生姓名
SelectSnamefromStudentwhereexists
（select*fromSCwhereSno=Student.SnoandCno=’1’）;
【例45】
查询没有选修了1号课的学生姓名
SelectSnamefromStudentwherenotexists
（select*fromSCwhereSno=Student.SnoandCno=’1’）;
【例46】
查询选修了全部课程的学生姓名
SelectSname
FromStudent
Wherenotexists
（select*fromCoursewherenotexists
（select*fromSCwhereSno=Student.SnoandCno=Course.Cno））;
【例47】
查询至少选修了学生200215122选修的全部课程的学生号码
SelectdistinctSno
fromSCSCX
wherenotexists（select*fromSCSCYwhereSCY.Sno=’200215122’and
notexists
（select*fromSCSCZ
whereSCZ.Sno=SCX.Snoand
SCZ.Cno=SCY.Cno））;
（4）、集合查询
【例48】
Select*fromStudentwhereSdept=’CS’
Union
Select*fromStudentwhereSage<=19;
【例49】
查询选修了课程1或者课程2的学生
SelectSnofromSCwhereCno=’1’
Union
SelectSnofromSCwhereCno=’2’;
【例50】
查询计系的学生与年龄不大于19岁的学生的交集
Select*fromStudentwhereSdept=’CS’
Intersect
Select*fromStudentwhereSage<=19;
操作失败！
原因：
MySql不支持INTERSECT
改为：
SELECT*FROMstudentWHERESdeptIN
（SELECTSdeptFROMstudentWHERESage<20
ANDSdept='CS'）;
【例51】
查询选修了课程1又选修了课程2的学生
SelectSnofromSCwhereCno=’1’
Intersect
SelectSnofromSCwhereCno=’2’;
操作失败！
原因：
MySql不支持INTERSECT
改为：
SelectSnofromSCwhereCno='1'ANDSnoIN
（SELECTSnoFROMSCWHERECno='2'）；
【例52】
查询计系的学生与年龄不大于19岁学生的差集
Select*fromStudentwhereSdept=’CS’
Except
Select*fromStudentwhereSage<=19;
操作失败！
MySql不支持EXCEPT。
解决：
改为Select*fromStudentwhereSdept='CS'ANDSage>19;
3、数据更新测试（UPDATE）
（1）、插入数据
【例1】
将一个新生元组插入Student表中
Insert
intoStudent（Sno,Sname,Ssex,Sdept,Sage）
values（‘200215128’,’陈东’,’男’,’IS’,’18’）;
【例2】
将学生张成民的信息插入Student表中
insertintoStudentvalues（'200215126','张成民','男',18,'CS',null）;
【例3】
插入一条选课记录
【例4】
对每个系求学生的平均年龄，并把结果存入数据库
先创建一个表createtableDept_age（Sdeptchar（15）,Avg_agesmallint）;
对Student按系分组求平均年龄，再把系名和平均年龄存入新表中
insertintoDept_age（Sdept,Avg_age）selectSdept,AVG（Sage）
fromStudent
groupbySdept;
（2）、修改数据
【例5】
将学生200215121的年龄改为22岁
UpdateStudentsetSage=22whereSno=’200215121’;
【例6】
将所有学生的年龄加1岁
UpdateStudentsetSage=Sage+1;
【例7】
将计算机科学系全体学生的成绩置零
UpdateSCsetGrade=0
Where‘CS’=selectSdept
fromStudent
whereStudent.Sno=SC.Sno;
（3）、删除数据
【例8】
删除学号为200215128的学生记录
DeletefromStudentwhereSno=’200215128’;
操作失败！
原因：
有外键约束，要删除该数据需先解除外键约束。
【例9】
删除所有的学生选课记录
DeletefromSC;
【例10】
删除计系所有学生的选课记录
DeletefromSC
where‘CS’=（selectSdeptfromStudentfromStudentwhereStudent.Sno=SC.Sno）;
4、视图
（1）、定义视图
【例1】
建立信息系学生的视图
createviewIS_Student
as
selectSno,Sname,Sage
fromStudent
whereSdept='IS';
【例2】
在例1的要求上再加一个要求：
要求更改数据后仍保持该视图的作用
createviewIS_Student
as
selectSno,Sname,Sage
fromStu
升级会员 