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

1、数据库系统概论实验报告专业：软件工程（2）班姓名：鄂小妹学号：6题目：实验二（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、); 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

3、, 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 PRIM

4、ARY 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 TABL

5、E 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表上

6、的索引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）向ST

7、UDENT表插入下列数据： 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 VALUE

8、S(1005,马朝阳,男,20,22); 2）向COURSE表插入下列数据：1,数据结构,101,4 2,数据库,102,43,离散数学,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

9、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

10、 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); IN

11、SERT 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 (SELEC

12、T 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 马朝阳

13、 男 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

14、 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 = Co

15、urse.CNO GROUP BY SNO HAVING SUM(CREDIT)0 AND GRADE100), /*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

16、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，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。要求全部用约束命名子

17、句实现。 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个约束条件，包括主码约

18、束以及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 100 AND new.col 1000 THEN RAISE EXCEPTION New values can not more than 1000; END IF; END; 检测：1）向表TAB插入数据（150）。 执行：INSERT INTO TAB VALUES(150); 结果：插入的数据为1