1.
2.
4.
7.
9.
10.
第二篇：《数据库系统概论》实验报告
《数据库系统概论》
实验报告
专业：软件工程（2）班
姓名：鄂小妹
学号：070107021106
题目：实验二（1）交互式SQL 语言
实验内容及完成情况：（写出每一种类型的SQL测试用例）
（一）数据定义
一、基本表操作
1.建立基本表
1）创建学生表Student，由以下属性组成： 学号SNO（INT 型，主码），姓名SNAME（CHAR 型，长度为8，非空唯一），性别SEX（CHAR型，长度为2），所在系DEPTNO （INT型）。 CREATE TABLE Student(
SNO INT PRIMARY KEY,
SNAME CHAR(8) NOT NULL UNIQUE,
SEX CHAR(2),
DEPTNO INT);
2）创建课程表Course，由以下属性组成：课程号CNO（INT型），课程名CNAME（CHAR 型，长度为20，非空），授课教师编号TNO（INT型），学分CREDIT（INT型）。其中(CNO， TNO)为主码。
CREATE TABLE Course(
CNO INT,
CNAME CHAR(20) NOT NULL,
TNO INT,
CREDIT INT,
PRIMARY KEY (CNO,TNO));
3）创建学生选课表SC，由以下属性组成：学号SNO，课程CNO，成绩GRADE。所有属性
均为INT型，其中(SNO，CNO)为主码。
CREATE TABLE SC(
SNO INT,
CNO INT,
GRADE INT,
PRIMARY KEY(SNO,CNO));
4）创建教师表Teacher，由以下属性组成：教师编号TNO（INT型，主码），教师姓名 TNAME（CHAR型，长度为8，非空），所在系DEPTNO（INT型）。
CREATE TABLE Teacher(
TNO INT PRIMARY KEY,
TNAME CHAR(8) NOT NULL,
DEPTNO INT);
5）创建系表Dept，由以下属性：系号DEPTNO（INT型，主码），系名DNAME（CHAR型，
长度为20，非空）。
CREATE TABLE Dept(
DEPTNO INT PRIMARY KEY,
DNAME CHAR(20) NOT NULL);
2.修改基本表
1）在Student表中加入属性AGE(INT型)。
ALTER TABLE Student ADD AGE INT;
2）将Student表中的属性SAGE类型改为SMALLINT型。
ALTER TABLE Student ALTER COLUMN SAGE SMALLINT;
3.删除基本表
1）在所有操作结束后删除STUDENT表。
DROP TABLE Student；
2）在所有操作结束后删除COURSE表。
DROP TABLE Course；
3）在所有操作结束后删除SC表。
DROP TABLE SC；
4）在所有操作结束后删除TEACHER表。
DROP TABLE Teacher；
5）在所有操作结束后删除DEPT表。
DROP TABLE Dept；
二、索引操作
1.建立索引
1）在Student表上建立关于SNO的唯一索引。
CREATE UNIQUE INDEX stusno ON Student(SNO);
2)在Course表上建立关于CNO升序的唯一索引。
CREATE UNIQUE INDEX coucno ON Course(CNO);
2.删除索引
1）删除Student表上的索引stusno。
DROP INDEX stusno；
2）删除Course表上的索引coucno。
DROP INDEX coucno；
三、视图操作
1.建立视图
在插入数据的Student基本表上为计算机科学与技术系的学生记录建立一个视图 CS_STUDENT。
CREATE VIEW CS_STUDENT AS
SELECT *
FROM Student
WHERE DEPTNO=
(SELECT DEPTNO
FROM Dept
WHERE DNAME='计算机科学与技术')
WITH CHECK OPTION;
2.删除视图
在操作结束后删除视图CS_STUDENT。
DROP VIEW CS_STUDENT;
（二）数据操作
一、更新操作
1.插入数据
1）向STUDENT表插入下列数据：
1001,张天,男,10,20
1002,李兰,女,10,21
1003,陈铭,男,10,21
1004,刘茜,女,20,21
1005,马朝阳,男,20,22
INSERT INTO Student VALUES(1001,'张天','男',10,20); INSERT INTO Student VALUES(1002,'李兰','女',10,21); INSERT INTO Student VALUES(1003,'陈铭','男',10,21); INSERT INTO Student VALUES(1004,'刘茜','女',20,21); INSERT INTO Student VALUES(1005,'马朝阳','男',20,22);
2）向COURSE表插入下列数据：
1,数据结构,101,4
2,数据库,102,4
3,离散数学,103,4
4,C语言程序设计,101,2
INSERT INTO Course VALUES(1,'数据结构',101,4); INSERT INTO Course VALUES(2,'数据库',102,4); INSERT INTO Course VALUES(3,'离散数学',103,4);
INSERT INTO Course VALUES(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
INSERT INTO SC VALUES(1001,1,80);
INSERT INTO SC VALUES(1001,2,85);
INSERT INTO SC VALUES(1001,3,78);
INSERT INTO SC VALUES(1002,1,78);
INSERT INTO SC VALUES(1002,2,82);
INSERT INTO SC VALUES(1002,3,86);
INSERT INTO SC VALUES(1003,1,92);
INSERT INTO SC VALUES(1003,3,90);
INSERT INTO SC VALUES(1004,1,87);
INSERT INTO SC VALUES(1004,4,90);
INSERT INTO SC VALUES(1005,1,85);
INSERT INTO SC VALUES(1005,4,92);
4）向TEACHER表插入下列数据：
101,张星,10
102,李珊,10
103,赵天应,10
104,刘田, 20
INSERT INTO Teacher VALUES(101,'张星',10); INSERT INTO Teacher VALUES(102,'李珊',10); INSERT INTO Teacher VALUES(103,'赵天应',10); INSERT INTO Teacher VALUES(104,'刘田', 20);
5）向DEPT表插入下列数据：
10,计算机科学与技术
20,信息
INSERT INTO Dept VALUES(10,'计算机科学与技术'); INSERT INTO Dept VALUES(20,'信息');
2.修改数据
将张星老师数据结构课的学生成绩全部加2分 UPDATE SC
SET GRADE = GRADE + 2
WHERE CNO IN
(SELECT CNO
FROM Course,Teacher
WHERE Course.TNO = Teacher.TNO AND Teacher.TNAME='张星');
3.删除数据
删除马朝阳同学的所有选课记录
DELETE FROM SC
WHERE SNO IN
(SELECT SNO
FROM Student
WHERE SNAME = '马朝阳');
二、查询操作
1.单表查询
1）查询所有学生的信息。
SELECT *
FROM Student；
结果：1001 张天 男 10 20
1002 李兰 女 10 21
1003 陈铭 男 10 21
1004 刘茜 女 20 21
1005 马朝阳 男 20 22
2）查询所有女生的姓名。
SELECT SNAME
FROM Student
WHERE SEX = '女';
结果：李兰
刘茜
3）查询成绩在80到89之间的所有学生选课记录，查询结果按成绩的降序排列。 SELECT *
FROM SC
WHERE GRADE >= 80
AND GRADE <= 89
ORDER BY GRADE DESC;
结果：1004 1 87
1002 3 86
1001 2 85
1005 1 85
1002 2 82
1001 1 80
4）查询各个系的学生人数。
SELECT DEPTNO,count(SNO)
FROM Student
GROUP BY DEPTNO;
结果：10 3
20 2
2.连接查询
查询信息系年龄在21岁以下（含21岁）的女生姓名及其年龄。
SELECT SNAME,AGE
FROM Student,Dept
WHERE Student.DEPTNO = Dept.DEPTNO
AND Dept.DNAME = '信息'
AND AGE <= 21
AND SEX = '女';
结果：刘茜 21
3.嵌套查询
1）查询修课总学分在10学分以下的学生姓名。 SELECT SNAME
FROM Student
WHERE SNO IN
(SELECT SNO
FROM SC,Course
WHERE SC.CNO = Course.CNO
GROUP BY SNO
HAVING SUM(CREDIT)<10);
结果：陈铭
刘茜
马朝阳
2）查询各门课程取得最高成绩的学生姓名及其成绩。 SELECT CNO,SNAME,GRADE
FROM Student,SC SCX
WHERE Student.SNO = SCX.SNO AND SCX.GRADE IN (SELECT MAX(GRADE)
FROM SC SCY
WHERE SCX.CNO = SCY.CNO
GROUP BY CNO);
结果：2 张天 85
3 陈铭 92
1 陈铭 90
4 马朝阳 92
3）查询选修了1001学生选修的全部课程的学生学号。 SELECT SNO
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC SCX
WHERE SCX.SNO = 1001 AND NOT EXISTS (SELECT *
FROM SC SCY
WHERE SCY.SNO = Student.SNO AND SCY.cno = SCX.cno));
结果：1001
1002
4）查询选修了张星老师开设的全部课程的学生姓名。
SELECT SNAME
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM Course
WHERE TNO IN
(SELECT TNO
FROM Teacher
WHERE TNAME = '张星') AND NOT EXISTS
(SELECT *
FROM SC
WHERE SC.SNO = Student.SNO AND SC.CNO = Course.CNO));
结果：刘茜
马朝阳
出现的问题及解决方案：
1.在创建基本表时是否可以缺省主码？
可以。在定义基本表时可以定义主码也可以先不定义主码。
2.对基本表进行修改，执行ALTER TABLE Student MODIFY Sage SMALLINT显示执行 失败。
当基本表中没有数据时，KingbaseES允许对基本表的属性类型进行修改，如上述修改
可以写成ALTER TABLE Student ALTER COLUMN SAGE SMALLINT，执行成功。但如果基本表
中已经存有数据时，系统则会给出数据将丢失的警告，不允许进行属性类型的修改。
3.在SQL Server中没有提供删除属性列的语句，KingbaseES则支持删除属性列。
如果要在基本表Student上删除属性列SNAME，可以执行ALTER TABLE Student DROP SNAME。
题目：实验四 数据控制（完整性部分）
实验内容及完成情况：
实验环境介绍：
硬件：CPU：P3 800
硬盘：30G
内存：512M
系统：Windows 2000 Advanced Server
在本实验中，我们将对完整性进行讨论，包括3类完整性、CHECK短语、CONSTRAIN 子句、触发器。因为完整性约束绝大部分是在定义表结构时进行的，因此可能需要多次定义
表，如果表名发生重复的话，可以先将旧表删除后再建立。
1. 实体完整性
【例4】定义表的主码。
关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。定义主码的方法 分为定义为列级约束条件和定义为表级约束条件两种。
[例4-1]定义表Student，并将其中的Sno属性定义为主码。
CREATE TABLE STUDENT(
SNO CHAR(7) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20));
或者：
CREATE TABLE STUDENT(
SNO CHAR(7),
SNAME CHAR(8),
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20),
PRIMARY KEY (SNO));
[例4-2]定义表SC,将其中的属性Sno,Cno定义为主码。
对于多个属性构成的码，只能够将其定义为表级约束条件，而无法用列级约束条件来实
现。
CREATE TABLE SC(
SNO CHAR(7) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY (SNO,CNO));
2. 参照完整性。
【例5】定义表的外码。
关系模型的参照完整性是在CREATE TABLE中用FOREIGN KEY语句来定义的，并用 REFERENCES来指明外码参照的是哪些表的主码。
定义表SC，其中Sno参照表Student的主码Sno，Cno参照表Course的主码Cno。 CREATE TABLE SC(
SNO CHAR(7) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),
FOREIGN KEY (CNO) REFERENCES COURSE(CNO));
3. 用户定义完整性。
【例6】用户定义的属性上的约束条件。
[例6-1]列值非空。在定义SC表时，Sno、Cno和Grade属性都不允许取空值。 在不特别声明的情况下，非码属性的值是允许取空值的。
CREATE TABLE SC(
SNO CHAR(7) NOT NULL, /*列值非空的约束NOT NULL*/
CNO CHAR(4) NOT NULL,
GRADE SMALLINT NOT NULL);
[例6-2]列值唯一。建立部门表DEPT，要求部门名称Dname取值唯一，部门编号Deptno属
性为主码。
CREATE TABLE DEPT(
DEPTNO NUMERIC(7) PRIMARY KEY,
DNAME VARCHAR(9) UNIQUE, /*UNIQUE约束要求Dname取值唯一*/ LOCATION VARCHAR(10));
[例6-3]CHECK短语指定列值应该满足的条件。定义表Student，属性Ssex的值只允许取“男”
或“女”；定义表SC，属性Grade的值定义在0－100之间。
CREATE TABLE STUDENT(
SNO CHAR(7) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2) CHECK(SSEX IN(‘男’,’女’)), /*CHECK语句约束条件*/ SAGE SMALLINT,
SDEPT CHAR(20));
CREATE TABLE SC(
SNO CHAR(7) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT CHECK (GRADE>0 AND GRADE<100), /*CHECK语句约束 条件*/
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES STUDENT(SNO),
FOREIGN KEY (CNO) REFERENCES COURSE(CNO));
【例7】用户定义的元组上的约束条件。
CHECK短语不光能够定义属性列上的约束条件，还允许用户定义元组级的约束条件。 定义表Student，要求当学生性别为男时，其名字不能以Ms.打头。
CREATE TABLE STUDENT(
SNO CHAR(7) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20),
CHECK (SSEX = ‘女’ OR SNAME NOT LIKE ‘Ms.%’));
/*定义了Sname和Ssex之间的约束条件*/
4. CONSTRAINT完整性约束命名子句。
【例8】在定义表时利用约束命名子句对完整性约束条件命名，能够灵活地增加或删除一个
完整性约束条件。
[例8-1]定义表Student，要求学号在90000－99999之间，姓名不能取空值，年龄小于30，
性别只能是“男”或“女”。要求全部用约束命名子句实现。
CREATE TABLE STUDENT(
SNO NUMERIC(5)
CONSTRAINT C1 CHECK (SNO BETWEEN 90000 AND 99999),
SNAME VARCHAR(20)
CONSTRAINT C2 NOT NULL,
SAGE NUMERIC(3)
CONSTRAINT C3 CHECK (SAGE < 30),
SSEX VARCHAR(2)
CONSTRAINT C4 CHECK (SSEX IN (‘男’,’女’)),
CONSTRAINT StudentKey PRIMARY KEY(SNO));
在表Student上共定义了5个约束条件，包括主码约束以及C1、C2、C3、C4四个列级 约束。
[例8-2]修改表Student中的完整性限制，去掉对性别的限制，并将年龄的限制由小于30改
为小于40。
ALTER TABLE STUDENT /*去掉对性别的限制条件C4*/
DROP CONSTRAINT C4;
ALTER TABLE STUDENT /*先删掉原来的约束条件再增加一个新的约束条件*/ DROP CONSTRAINT C3;
ALTER TABLE STUDENT
ADD CONSTRAINT C3 CHECK(SAGE <40);
5. 触发器。
触发器可以看成是一类特殊的存储过程，在满足某个特定条件时自动触发执行，是提
高
数据库服务器性能的有力工具。
触发器分为三类，更新触发器、插入触发器和删除触发器。
能够定义触发器的用户有：
1） 表的所有者；
2） 系统管理员；
3） 拥有创建触发器的权限，且拥有对操作对象的相应的操作权限的用户。
【例9】定义表TAB,并在其上定义触发器TRI，在对TAB的插入和更新前检查，如果插入
或更新的值在100－1000之间的话，将值置为50；如果值大于1000的话，则给出新值不允
许大于1000的提示。
CREATE TABLE tab (col int); INSERT INTO tab VALUES(10); INSERT INTO tab VALUES(20); CREATE TRIGGER tri BEFORE UPDATE OR INSERT ON tab FOR EACH ROW AS BEGIN IF new.col > 100 AND new.col < 1000 THEN new.col := 50; END IF; IF new.col > 1000 THEN RAISE EXCEPTION ‘New values can not more than 1000’; END IF; END;
检测：1）向表TAB插入数据（150）。
执行：INSERT INTO TAB VALUES(150);
结果：插入的数据为150。值在100－1000之间，触发器TRI自动执行，将插入 的值置为50。
2）对表中的数据进行更新，将20更新为1500。
执行：UPDATE TAB SET COL = 1500 WHERE COL = 20;
结果：系统报出‘New values can not more than 1000’的错误，
对表进行查询后发现，表中的数据并没有发生改变。说明在更新数据时，值大于1000 则触发触发器TRI自动执行，系统报错，更新无效。
【例10】删除触发器TRI。
DROP TRIGGER TRI ON TAB;
删除触发器TRI之后，在对TAB进行插入和更新就不再有以上限制了。