《数据库系统概论》实验报告

《数据库系统概论》

实验报告

专业：软件工程（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),

本文来源：https://www.bwwdw.com/article/vi5g.html