广工数据库实验报告Word文件下载.docx
《广工数据库实验报告Word文件下载.docx》由会员分享,可在线阅读,更多相关《广工数据库实验报告Word文件下载.docx(66页珍藏版)》请在冰豆网上搜索。
1、数据定义测试(CREATE、DROP)
(1)、模式的定义与删除
【例1】
定义一个学生-课程模式S-T
Createschema”S-T”authorizationroot;
操作失败!
原因:
MySql不支持创建模式
【例2】
Createschemaauthorizationroot;
【例3】
createschemaTESTauthorizationroot
createtableTAB1(COL1smallint,
COL2int,
COL3char(20),
COL4numeric(10,3),
COL5decimal(5,2)
);
【例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)、单表查询
查询全体学生的学号和姓名
selectSno,SnamefromStudent;
查询全体学生的姓名、学号、所在系
selectSname,Sno,SdeptfromStudent;
查询全体学生的详细记录
Select*fromStudent;
查询全体学生的姓名及其出生年份
selectSname,2004-SagefromStudent;
查询全体学生的姓名、出生年份和所在的院系,用小写字母表示院系
selectSname,’YearofBirth:
’,2004-Sage,lower(Sdept)fromStudent;
查询选修了课程的学生学号
selectSnofromSC;
查询计算机系全体学生的名单
SelectSnamefromStudentwhereSdept=’CS’;
查询年龄在20岁以下的学生姓名及其年龄
SelectSname,SagefromStudentwhereSage<
20;
查询考试成绩有不及格的学生的学号
SelectdistinctSnofromSCwhereGrade<
60;
查询年龄在20~30岁之间的学生的姓名系别和年龄
selectSname,Sdept,SagefromStudentwhereSagebetween20and23;
查询年龄不在20~30岁之间的学生的姓名系别和年龄
selectSname,Sdept,SagefromStudentwhereSagenotbetween20and23;
查询计算机科学系、数学系和信息系学生的姓名和性别
selectSname,SsexfromStudentwhereSdeptin(‘CS’,’MA’,’IS’);
查询既不是计算机科学系、数学系,也不是信息系的学生的习姓名和性别
SelectSname,SsexfromStudentwhereSdeptnotin('
CS'
'
MA'
IS'
查询学好为200215121的学生的详细情况
Select*fromStudentwhereSnolike'
200215121'
;
等价于select*fromStudentwhereSno=’200215121’;
查询所有姓刘的学生、学号和性别
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__’;
【例21】
查询缺少成绩的学生的学号和相应的课程号
SelectSno,CnofromSCwhereGradeisnull;
【例22】
查询有成绩的学生的学号和相应的课程号
SelectSno,CnofromSCwhereGradeisnotnull;
【例23】
查询计系年龄在20岁一下的学生姓名
SelectSnamefromStudentwhereSdept=’CS’andSage<
【例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】
查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
any(selectSagefromStudentwhereSdept=’CS’)andSdept<
>
’CS’;
【例43】
查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄
all(selectSagefromStudentwhereSdept=’CS’)andSdept<
【例44】
查询选修了1号课的学生姓名
SelectSnamefromStudentwhereexists
(select*fromSCwhereSno=Student.SnoandCno=’1’);
【例45】
查询没有选修了1号课的学生姓名
SelectSnamefromStudentwherenotexists
【例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’
SelectSnofromSCwhereCno=’2’;
【例50】
查询计系的学生与年龄不大于19岁的学生的交集
Select*fromStudentwhereSdept=’CS’
Intersect
Select*fromStudentwhereSage<
MySql不支持INTERSECT
改为:
SELECT*FROMstudentWHERESdeptIN
(SELECTSdeptFROMstudentWHERESage<
20
ANDSdept='
【例51】
查询选修了课程1又选修了课程2的学生
MySql不支持INTERSECT
SelectSnofromSCwhereCno='
1'
ANDSnoIN
(SELECTSnoFROMSCWHERECno='
2'
);
【例52】
查询计系的学生与年龄不大于19岁学生的差集
Except
MySql不支持EXCEPT。
解决:
改为Select*fromStudentwhereSdept='
ANDSage>
19;
3、数据更新测试(UPDATE)
(1)、插入数据
将一个新生元组插入Student表中
Insert
intoStudent(Sno,Sname,Ssex,Sdept,Sage)
values(‘200215128’,’陈东’,’男’,’IS’,’18’);
将学生张成民的信息插入Student表中
insertintoStudentvalues('
200215126'
张成民'
男'
18,'
null);
插入一条选课记录
对每个系求学生的平均年龄,并把结果存入数据库
先创建一个表createtableDept_age(Sdeptchar(15),Avg_agesmallint);
对Student按系分组求平均年龄,再把系名和平均年龄存入新表中
insertintoDept_age(Sdept,Avg_age)selectSdept,AVG(Sage)
fromStudent
groupbySdept;
(2)、修改数据
将学生200215121的年龄改为22岁
UpdateStudentsetSage=22whereSno=’200215121’;
将所有学生的年龄加1岁
UpdateStudentsetSage=Sage+1;
将计算机科学系全体学生的成绩置零
UpdateSCsetGrade=0
Where‘CS’=selectSdept
fromStudent
whereStudent.Sno=SC.Sno;
(3)、删除数据
删除学号为200215128的学生记录
DeletefromStudentwhereSno=’200215128’;
有外键约束,要删除该数据需先解除外键约束。
删除所有的学生选课记录
DeletefromSC;
删除计系所有学生的选课记录
DeletefromSC
where‘CS’=(selectSdeptfromStudentfromStudentwhereStudent.Sno=SC.Sno);
4、视图
(1)、定义视图
建立信息系学生的视图
createviewIS_Student
as
selectSno,Sname,Sage
whereSdept='
在例1的要求上再加一个要求:
要求更改数据后仍保持该视图的作用
fromStu