《数据库系统概论》实验报告#优选.docx
《《数据库系统概论》实验报告#优选.docx》由会员分享,可在线阅读,更多相关《《数据库系统概论》实验报告#优选.docx(19页珍藏版)》请在冰豆网上搜索。
《数据库系统概论》实验报告#优选
《数据库系统概论》
实验报告
专业:
软件工程
(2)班
姓名:
鄂小妹
学号:
0701********
题目:
实验二
(1)交互式SQL语言
实验内容及完成情况:
(写出每一种类型的SQL测试用例)
(一)数据定义
一、基本表操作
1.建立基本表
1)创建学生表Student,由以下属性组成:
学号SNO(INT型,主码),姓名SNAME(CHAR
型,长度为8,非空唯一),性别SEX(CHAR型,长度为2),所在系DEPTNO(INT型)。
CREATETABLEStudent(
SNOINTPRIMARYKEY,
SNAMECHAR(8)NOTNULLUNIQUE,
SEXCHAR
(2),
DEPTNOINT);
2)创建课程表Course,由以下属性组成:
课程号CNO(INT型),课程名CNAME(CHAR
型,长度为20,非空),授课教师编号TNO(INT型),学分CREDIT(INT型)。
其中(CNO,
TNO)为主码。
CREATETABLECourse(
CNOINT,
CNAMECHAR(20)NOTNULL,
TNOINT,
CREDITINT,
PRIMARYKEY(CNO,TNO));
3)创建学生选课表SC,由以下属性组成:
学号SNO,课程CNO,成绩GRADE。
所有属性
均为INT型,其中(SNO,CNO)为主码。
CREATETABLESC(
SNOINT,
CNOINT,
GRADEINT,
PRIMARYKEY(SNO,CNO));
4)创建教师表Teacher,由以下属性组成:
教师编号TNO(INT型,主码),教师姓名
TNAME(CHAR型,长度为8,非空),所在系DEPTNO(INT型)。
CREATETABLETeacher(
TNOINTPRIMARYKEY,
TNAMECHAR(8)NOTNULL,
DEPTNOINT);
5)创建系表Dept,由以下属性:
系号DEPTNO(INT型,主码),系名DNAME(CHAR型,
长度为20,非空)。
CREATETABLEDept(
DEPTNOINTPRIMARYKEY,
DNAMECHAR(20)NOTNULL);
2.修改基本表
1)在Student表中加入属性AGE(INT型)。
ALTERTABLEStudentADDAGEINT;
2)将Student表中的属性SAGE类型改为SMALLINT型。
ALTERTABLEStudentALTERCOLUMNSAGESMALLINT;
3.删除基本表
1)在所有操作结束后删除STUDENT表。
DROPTABLEStudent;
2)在所有操作结束后删除COURSE表。
DROPTABLECourse;
3)在所有操作结束后删除SC表。
DROPTABLESC;
4)在所有操作结束后删除TEACHER表。
DROPTABLETeacher;
5)在所有操作结束后删除DEPT表。
DROPTABLEDept;
二、索引操作
1.建立索引
1)在Student表上建立关于SNO的唯一索引。
CREATEUNIQUEINDEXstusnoONStudent(SNO);
2)在Course表上建立关于CNO升序的唯一索引。
CREATEUNIQUEINDEXcoucnoONCourse(CNO);
2.删除索引
1)删除Student表上的索引stusno。
DROPINDEXstusno;
2)删除Course表上的索引coucno。
DROPINDEXcoucno;
三、视图操作
1.建立视图
在插入数据的Student基本表上为计算机科学与技术系的学生记录建立一个视图
CS_STUDENT。
CREATEVIEWCS_STUDENTAS
SELECT*
FROMStudent
WHEREDEPTNO=
(SELECTDEPTNO
FROMDept
WHEREDNAME='计算机科学与技术')
WITHCHECKOPTION;
2.删除视图
在操作结束后删除视图CS_STUDENT。
DROPVIEWCS_STUDENT;
(二)数据操作
一、更新操作
1.插入数据
1)向STUDENT表插入下列数据:
1001,张天,男,10,20
1002,李兰,女,10,21
1003,陈铭,男,10,21
1004,刘茜,女,20,21
1005,马朝阳,男,20,22
INSERTINTOStudentVALUES(1001,'张天','男',10,20);
INSERTINTOStudentVALUES(1002,'李兰','女',10,21);
INSERTINTOStudentVALUES(1003,'陈铭','男',10,21);
INSERTINTOStudentVALUES(1004,'刘茜','女',20,21);
INSERTINTOStudentVALUES(1005,'马朝阳','男',20,22);
2)向COURSE表插入下列数据:
1,数据结构,101,4
2,数据库,102,4
3,离散数学,103,4
4,C语言程序设计,101,2
INSERTINTOCourseVALUES(1,'数据结构',101,4);
INSERTINTOCourseVALUES(2,'数据库',102,4);
INSERTINTOCourseVALUES(3,'离散数学',103,4);
INSERTINTOCourseVALUES(4,'C语言程序设计',101,2);
3)向SC表插入下列数据:
1001,1,80
1001,2,85
1001,3,78
1002,1,78
1002,2,82
1002,3,86
1003,1,92
1003,3,90
1004,1,87
1004,4,90
1005,1,85
1005,4,92
INSERTINTOSCVALUES(1001,1,80);
INSERTINTOSCVALUES(1001,2,85);
INSERTINTOSCVALUES(1001,3,78);
INSERTINTOSCVALUES(1002,1,78);
INSERTINTOSCVALUES(1002,2,82);
INSERTINTOSCVALUES(1002,3,86);
INSERTINTOSCVALUES(1003,1,92);
INSERTINTOSCVALUES(1003,3,90);
INSERTINTOSCVALUES(1004,1,87);
INSERTINTOSCVALUES(1004,4,90);
INSERTINTOSCVALUES(1005,1,85);
INSERTINTOSCVALUES(1005,4,92);
4)向TEACHER表插入下列数据:
101,张星,10
102,李珊,10
103,赵天应,10
104,刘田,20
INSERTINTOTeacherVALUES(101,'张星',10);
INSERTINTOTeacherVALUES(102,'李珊',10);
INSERTINTOTeacherVALUES(103,'赵天应',10);
INSERTINTOTeacherVALUES(104,'刘田',20);
5)向DEPT表插入下列数据:
10,计算机科学与技术
20,信息
INSERTINTODeptVALUES(10,'计算机科学与技术');
INSERTINTODeptVALUES(20,'信息');
2.修改数据
将张星老师数据结构课的学生成绩全部加2分
UPDATESC
SETGRADE=GRADE+2
WHERECNOIN
(SELECTCNO
FROMCourse,Teacher
WHERECourse.TNO=Teacher.TNO
ANDTeacher.TNAME='张星');
3.删除数据
删除马朝阳同学的所有选课记录
DELETEFROMSC
WHERESNOIN
(SELECTSNO
FROMStudent
WHERESNAME='马朝阳');
二、查询操作
1.单表查询
1)查询所有学生的信息。
SELECT*
FROMStudent;
结果:
1001张天男1020
1002李兰女1021
1003陈铭男1021
1004刘茜女2021
1005马朝阳男2022
2)查询所有女生的姓名。
SELECTSNAME
FROMStudent
WHERESEX='女';
结果:
李兰
刘茜
3)查询成绩在80到89之间的所有学生选课记录,查询结果按成绩的降序排列。
SELECT*
FROMSC
WHEREGRADE>=80
ANDGRADE<=89
ORDERBYGRADEDESC;
结果:
1004187
1002386
1001285
1005185
1002282
1001180
4)查询各个系的学生人数。
SELECTDEPTNO,count(SNO)
FROMStudent
GROUPBYDEPTNO;
结果:
103
202
2.连接查询
查询信息系年龄在21岁以下(含21岁)的女生姓名及其年龄。
SELECTSNAME,AGE
FROMStudent,Dept
WHEREStudent.DEPTNO=Dept.DEPTNO
ANDDept.DNAME='信息'
ANDAGE<=21
ANDSEX='女';
结果:
刘茜21
3.嵌套查询
1)查询修课总学分在10学分以下的学生姓名。
SELECTSNAME
FROMStudent
WHERESNOIN
(SELECTSNO
FROMSC,Course
WHERESC.CNO=Course.CNO
GROUPBYSNO
HAVINGSUM(CREDIT)<10);
结果:
陈铭
刘茜
马朝阳
2)查询各门课程取得最高成绩的学生姓名及其成绩。
SELECTCNO,SNAME,GRADE
FROMStudent,SCSCX
WHEREStudent.SNO=SCX.SNOANDSCX.GRADEIN
(SELECTMAX(GRADE)
FROMSCSCY
WHERESCX.CNO=SCY.CNO
GROUPBYCNO);
结果:
2张天85
3陈铭92
1陈铭90
4马朝阳92
3)查询选修了1001学生选修的全部课程的学生学号。
SELECTSNO
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMSCSCX
WHERESCX.SNO=1001ANDNOTEXISTS
(SELECT*
FROMSCSCY
WHERESCY.SNO=Student.SNOANDSCY.cno=SCX.cno));
结果:
1001
1002
4)查询选修了张星老师开设的全部课程的学生姓名。
SELECTSNAME
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMCourse
WHERETNOIN
(SELECTTNO
FROMTeacher
WHERETNAME='张星')ANDNOTEXISTS
(SELECT*
FROMSC
WHERESC.SNO=Student.SNOANDSC.CNO=Course.CNO));
结果:
刘茜
马朝阳
出现的问题及解决方案:
1.在创建基本表时是否可以缺省主码?
可以。
在定义基本表时可以定义主码也可以先不定义主码。
2.对基本表进行修改,执行ALTERTABLEStudentMODIFYSageSMALLINT显示执行失败。
当基本表中没有数据时,KingbaseES允许对基本表的属性类型进行修改,如上述修改
可以写成ALTERTABLEStudentALTERCOLUMNSAGESMALLINT,执行成功。
但如果基本表
中已经存有数据时,系统则会给出数据将丢失的警告,不允许进行属性类型的修改。
3.在SQLServer中没有提供删除属性列的语句,KingbaseES则支持删除属性列。
如果要在基本表Student上删除属性列SNAME,可以执行ALTERTABLEStudentDROP
SNAME。
题目:
实验四数据控制(完整性部分)
实验内容及完成情况:
实验环境介绍:
硬件:
CPU:
P3800
硬盘:
30G
内存:
512M
系统:
Windows2000AdvancedServer
在本实验中,我们将对完整性进行讨论,包括3类完整性、CHECK短语、CONSTRAIN
子句、触发器。
因为完整性约束绝大部分是在定义表结构时进行的,因此可能需要多次定义
表,如果表名发生重复的话,可以先将旧表删除后再建立。
1.实体完整性
【例4】定义表的主码。
关系模型的实体完整性在CREATETABLE中用PRIMARYKEY定义。
定义主码的方法
分为定义为列级约束条件和定义为表级约束条件两种。
[例4-1]定义表Student,并将其中的Sno属性定义为主码。
CREATETABLESTUDENT(
SNOCHAR(7)PRIMARYKEY,
SNAMECHAR(8)NOTNULL,
SSEXCHAR
(2),
SAGESMALLINT,
SDEPTCHAR(20));
或者:
CREATETABLESTUDENT(
SNOCHAR(7),
SNAMECHAR(8),
SSEXCHAR
(2),
SAGESMALLINT,
SDEPTCHAR(20),
PRIMARYKEY(SNO));
[例4-2]定义表SC,将其中的属性Sno,Cno定义为主码。
对于多个属性构成的码,只能够将其定义为表级约束条件,而无法用列级约束条件来实
现。
CREATETABLESC(
SNOCHAR(7)NOTNULL,
CNOCHAR(4)NOTNULL,
GRADESMALLINT,
PRIMARYKEY(SNO,CNO));
2.参照完整性。
【例5】定义表的外码。
关系模型的参照完整性是在CREATETABLE中用FOREIGNKEY语句来定义的,并用
REFERENCES来指明外码参照的是哪些表的主码。
定义表SC,其中Sno参照表Student的主码Sno,Cno参照表Course的主码Cno。
CREATETABLESC(
SNOCHAR(7)NOTNULL,
CNOCHAR(4)NOTNULL,
GRADESMALLINT,
PRIMARYKEY(SNO,CNO),
FOREIGNKEY(SNO)REFERENCESSTUDENT(SNO),
FOREIGNKEY(CNO)REFERENCESCOURSE(CNO));
3.用户定义完整性。
【例6】用户定义的属性上的约束条件。
[例6-1]列值非空。
在定义SC表时,Sno、Cno和Grade属性都不允许取空值。
在不特别声明的情况下,非码属性的值是允许取空值的。
CREATETABLESC(
SNOCHAR(7)NOTNULL,/*列值非空的约束NOTNULL*/
CNOCHAR(4)NOTNULL,
GRADESMALLINTNOTNULL);
[例6-2]列值唯一。
建立部门表DEPT,要求部门名称Dname取值唯一,部门编号Deptno属
性为主码。
CREATETABLEDEPT(
DEPTNONUMERIC(7)PRIMARYKEY,
DNAMEVARCHAR(9)UNIQUE,/*UNIQUE约束要求Dname取值唯一*/
LOCATIONVARCHAR(10));
[例6-3]CHECK短语指定列值应该满足的条件。
定义表Student,属性Ssex的值只允许取“男”
或“女”;定义表SC,属性Grade的值定义在0-100之间。
CREATETABLESTUDENT(
SNOCHAR(7)PRIMARYKEY,
SNAMECHAR(8)NOTNULL,
SSEXCHAR
(2)CHECK(SSEXIN(‘男’,’女’)),/*CHECK语句约束条件*/
SAGESMALLINT,
SDEPTCHAR(20));
CREATETABLESC(
SNOCHAR(7)NOTNULL,
CNOCHAR(4)NOTNULL,
GRADESMALLINTCHECK(GRADE>0ANDGRADE<100),/*CHECK语句约束
条件*/
PRIMARYKEY(SNO,CNO),
FOREIGNKEY(SNO)REFERENCESSTUDENT(SNO),
FOREIGNKEY(CNO)REFERENCESCOURSE(CNO));
【例7】用户定义的元组上的约束条件。
CHECK短语不光能够定义属性列上的约束条件,还允许用户定义元组级的约束条件。
定义表Student,要求当学生性别为男时,其名字不能以Ms.打头。
CREATETABLESTUDENT(
SNOCHAR(7)PRIMARYKEY,
SNAMECHAR(8)NOTNULL,
SSEXCHAR
(2),
SAGESMALLINT,
SDEPTCHAR(20),
CHECK(SSEX=‘女’ORSNAMENOTLIKE‘Ms.%’));
/*定义了Sname和Ssex之间的约束条件*/
4.CONSTRAINT完整性约束命名子句。
【例8】在定义表时利用约束命名子句对完整性约束条件命名,能够灵活地增加或删除一个
完整性约束条件。
[例8-1]定义表Student,要求学号在90000-99999之间,姓名不能取空值,年龄小于30,
性别只能是“男”或“女”。
要求全部用约束命名子句实现。
CREATETABLESTUDENT(
SNONUMERIC(5)
CONSTRAINTC1CHECK(SNOBETWEEN90000AND99999),
SNAMEVARCHAR(20)
CONSTRAINTC2NOTNULL,
SAGENUMERIC(3)
CONSTRAINTC3CHECK(SAGE<30),
SSEXVARCHAR
(2)
CONSTRAINTC4CHECK(SSEXIN(‘男’,’女’)),
CONSTRAINTStudentKeyPRIMARYKEY(SNO));
在表Student上共定义了5个约束条件,包括主码约束以及C1、C2、C3、C4四个列级
约束。
[例8-2]修改表Student中的完整性限制,去掉对性别的限制,并将年龄的限制由小于30改
为小于40。
ALTERTABLESTUDENT/*去掉对性别的限制条件C4*/
DROPCONSTRAINTC4;
ALTERTABLESTUDENT/*先删掉原来的约束条件再增加一个新的约束条件*/
DROPCONSTRAINTC3;
ALTERTABLESTUDENT
ADDCONSTRAINTC3CHECK(SAGE<40);
5.触发器。
触发器可以看成是一类特殊的存储过程,在满足某个特定条件时自动触发执行,是提高
数据库服务器性能的有力工具。
触发器分为三类,更新触发器、插入触发器和删除触发器。
能够定义触发器的用户有:
1)表的所有者;
2)系统管理员;
3)拥有创建触发器的权限,且拥有对操作对象的相应的操作权限的用户。
【例9】定义表TAB,并在其上定义触发器TRI,在对TAB的插入和更新前检查,如果插入
或更新的值在100-1000之间的话,将值置为50;如果值大于1000的话,则给出新值不允
许大于1000的提示。
CREATETABLEtab(colint);
INSERTINTOtabVALUES(10);
INSERTINTOtabVALUES(20);
CREATETRIGGERtriBEFOREUPDATEORINSERTONtab
FOREACHROW
AS
BEGIN
IFnew.col>100ANDnew.col<1000THEN
new.col:
=50;
ENDIF;
IFnew.col>1000THEN
RAISEEXCEPTION‘Newvaluescannotmorethan1000’;
ENDIF;
END;
检测:
1)向表TAB插入数据(150)。
执行:
INSERTINTOTABVALUES(150);
结果:
插入的数据为150。
值在100-1000之间,触发器TRI自动执行,将插入
的值置为50。
2)