实验1：关系数据库的创建、查询及其相关基本操作

利用PostgreSQL数据库实现对关系数据库的相关操作，包括数据库创建、关系表创建和查询等基本操作，为后续空间数据库实习打下坚实基础。

实验1.1 数据库定义

（1）实验目的

理解和掌握数据库DDL语言，能够熟练地使用SQL
DDL语句创建、修改和删除数据库、模式、索引和基本表。

（2）实验内容和要求

理解和掌握SQL
DDL语句的语法，特别是各种参数的具体含义和使用方法；使用SQL语句创建、修改和删除数据库、模式、索引和基本表。掌握SQL语句常见语法错误的调试方法。

（3）实验重点和难点

实验重点：创建数据库、基本表。

实验难点：创建基本表时，为不同的列选择合适的数据类型，正确创建表级和列级完整性约束，如列值是否允许为空、主码和外码等。注意：数据完整性约束，可以在创建基本表时定义，也可以先创建表然后定义完整性约束；由于完整性约束的限制，被引用的表要先创建。

（4）实验任务

现有“学生课程”数据库，包括三个表：

Student 表，由学号Sno,姓名 Sname（不为空，唯一）, 性别Ssex（默认男）, 年龄Sage,
所在系Sdept等属性组成；其中Sno为主码；

Course表，由课程号Cno，课程名Cname（不为空），先修课号Cpno，学分Ccredit等属性组成；其中Cno为主码；

SG表，由学号Sno，课程号Cno，成绩Grade组成；其中（Sno，Cno）为候选码；

假如该数据库属于用户HBSF；

基于上述信息，采用SQL语句实现以下要求，并通过SQL server
2008软件检查语句执行结果：

1. 创建学生课程数据库，数据库名称为“S-T”；

3、为“S-T”数据库创建Student、Course以及SG表，需满足上述相关表描述条件；

4、向学生表Student添加“入学日期Scome”属性，类型为DATE；

5、删除Student表Scome列；

6、将课程表Course的学分属性类型改为INT型；

7、在Student表的学号属性上建立降序索引，索引名为IndexSno；在Course上建立课程号升序唯一索引，索引名自定义；

8、删除7中建立的索引；

实验1.2 数据基本查询

（1）实验目的

掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。

（2）实验内容和要求

针对S-T数据库设计各种单表查询SQL语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。

说明：简单地说，SQL程序设计规范包含SQL关键字大写、表名、属性名、存储过程名等标示符大小写混合、SQL程序书写缩进排列等编程规范。

（3）实验重点和难点

实验重点：分组统计查询、单表自身连接查询、多表连接查询。

实验难点：区分元组过滤条件和分组过滤条件；确定连接属性，正确设计连接条件。

（4）实验任务

1、查询全体学生的学号和姓名；查询全体学生所有信息；管理系全体学生信息；

2、查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名；

3、给上例的结果集指定列名（查询结果显示列名称依次为：“姓名”、“出生年份”、“所属系”），提升查询结果的辨识度；

4、查询选修了3号课程的学生学号及成绩，查询结果按分数降序排列；

5、查询学习1号课程的学生最高分、平均分；

6、查询选修了课程的学生的学号（查询结果不重复显示学号）；

7、查询与“李洋”在同一个系学习的学生姓名（用链接查询）；

8、查询年龄在20岁以下的学生的姓名及其年龄；查询考试成绩有不及格的学生的学号；

9、查询所有不姓刘的学生姓名；查询DB_Design课程的课程号和学分；

10、查询年龄在20-30岁之间（包括20岁和30岁）的学生的姓名、所在系；

11、查询 IS,CS,MA系的所有学生的姓名和性别；

13、查询没有先行课的课程的课程号和课程名；

14、查询全体学生的情况,查询结果按所在系号升序排列,
同一系中的学生按年龄降序排列；

15、列出所有学生的基本情况和选课情况, 若没有选课,则只列出基本情况信息；

16、查询学生总人数；

17、查询所有课程的总学分数和平均学分数,以及最高学分和最低学分；

18、查询信息系“IS”学生“数据结构”课程的平均成绩；

19、查询每个学生的课程成绩最高的成绩信息；

20、查询各系的学生的人数并按人数从多到少排序；

*21、查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前；

22、查询选修了3门课程以上的学生的学号；

23、查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数；

24、查询至少选修了2门课程的学生的平均成绩；

25、查询平均分超过80分的学生的学号和平均分；

实验1.3 数据高级查询

（1）实验目的

掌握SQL嵌套查询和集合查询等各种高级查询的设计方法等。

（2）实验内容和要求

针对S-T数据库，正确分析用户查询要求，设计各种嵌套查询和集合查询。

（3）实验重点和难点

实验重点：嵌套查询。

实验难点：相关子查询、多层EXIST嵌套查询。

（4）实验任务（用嵌套查询实现）

1、查询所有选修了1号课程的学生姓名；

2、求成绩低于该门课程平均成绩的学生的成绩信息；

3、查询没有选修了1号课程的学生姓名；

4、查询与其他所有学生年龄均不同的学生学号、姓名和年龄；

5、求没有人选修的课程号和课程名；

6、查询学习1号课程的学生最高分、平均分（用EXISTS表示）；

7、查询没有选修1号课程的学生姓名（用EXISTS表示）。

8、查询选修了全部课程的学生姓名；

9、查询至少选修了学生95002选修的全部课程的学生的学号；

实验1.4 数据更新

（1）实验目的

熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。

（2）实验内容和要求

针对TPC-H数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句，并调试成功。

（3）实验重点和难点

实验重点：插入、修改和删除数据的SQL。

实验难点：与嵌套SQL子查询相结合的插入、修改和删除数据的SQL语句；利用一个表的数据来插入、修改和删除另外一个表的数据。

（4）实验任务

1、向Student表中插入一条记录（‘200501’，‘李勇’）;

2、向Student表中插入记录（‘200502’，‘男’）和（‘200503’，‘女’）;

3、将数据表student中学号为200215121的记录的性别及所在系的字段值改为（女，IS）；

4、将数据表SC中所有记录的成绩的字段值增加10％；

5、将数据表Student中姓名中姓“王”的记录年龄增加1；

6、将选修课程号为1的记录的成绩字段值增加10 ，其他记录的成绩字段值增加5；

实验2：安全性语言

安全性实验包含两个实验项目，其中1个为必修，1个为选修。自主存取控制实验为设计型实验项目，审计实验为验证型实验项目。

实验2.1 自主存取控制实验

（1）实验目的

掌握自主存取控制权限的定义和维护方法。

（2）实验内容和要求

定义用户、角色，分配权限给用户、角色，回收权限，以相应的用户名登录数据库验证权限分配是否正确。

（3）实验重点和难点

实验重点：定义角色，分配权限和回收权限。

实验难点：实现权限的再分配和回收。

（4）实验任务

1、理解SQL server 2008
服务器登录名、角色、数据库用户、角色、模式（也称架构）的关系；

2、为S-T数据库新建学生用户“StudentUser”；

Step 1: 超级管理员登陆服务器；

Step 2: 资源管理器中找到“安全性 -> 登录名”，右键“登录名”选择“新建登录名”；

Step 3: 设置登录名为“StudentUser”，验证方式选择“SQL Server
身份验证”，密码初始：123456，将默认强制密码过期去除；

Step 4:
设置该登录名的归属“服务器角色”、映射S-T数据库、SQL登陆选项，分别如下图：

3、思考登录名StudentUser对S-T数据库有何操作权限？用户StudentUser对S-T数据库有何操作权限？用StudentUser登陆并验证。

Step 1: 右键已登陆服务器名称，选择“断开连接”；

Step 2：连接服务器，选择“SQL
Server身份验证”，登陆名为经上述步骤新建的StudentUser登录名，密码为：123456；

Step 3: 新建查询语句，并输入 use
[S-T]，通过SQL选择当前用户要操作的数据库，并点击执行；

Step
4：验证当前登陆用户，对S-T数据的操作权限，如下图为验证在Student表上的查询权限，执行后，数据库直接拒绝该查询语句；原因：该用户对S-T数据库无查询权限；

请同学们验证该用户在S-T数据库的Student、Course、SC表的查询、更新、删除、创建权限；【要求：将相关步骤和结果截图】

4、定义权限：给StudentUser用户授予S-T数据库相关操作权限；如授予StudentUser用户对Student表的查询权限；

【方法一：SQL Server 对话框方式】

Step 1: 退出StudentUser用户登录，采用管理员身份登录；

Step 2: 找到S-T数据库StudentUser，右键选择“属性”；

Step 3: 按如下1-8顺序操作，实现对Student表的查询授权；

6、用SQL语句建立计算机系学生的视图CS_Student，并给该视图的查询、更新权限授权给StudentUser用户；

（2）查询授权

实验3：完整性语言

完整性语言实验包含1个验证型实验项目。

（1）实验目的

掌握实体完整性、参照完整性以及自定义完整性的定义和维护方法。

（2）实验内容和要求

定义实体完整性、参照完整性以及具体应用语义，如NULL/NOT
NULL、DEFAULT，UNIQUE、CHECK等，删除实体完整性、参照完整性以及具体应用语义。能够写出两种方式定义完整性的SQL语句：创建表时定义完整性、创建表后定义完整性。设计SQL语句验证完整性约束是否起作用。

（3）实验重点和难点

实验重点：创建表时定义实体、参照、自定义完整性。

实验难点：有多个候选码时实体完整性的定义、参照完整性的违约处理定义。

（4）实验准备

请执行以下SQL语句。

create database "S-T";

（5）实验任务

一、创建表时定义实体、参照完整性以及自定义完整性（可用CONSTRAINT语句）。

（1）为数据库“S-T”创建关系Student(Sno,Sname,Ssex,Sage,Sdept),其中Sno为主键，要求Sno取1000~5000之间值，Sname非空，Sage小于30，Ssex只能是‘男’或‘女’。

（2）为数据库“S-T”创建关系Course(Cno,Cname,Cpno,Ccredit),其中Cno为主键，Cname唯一。

(3）为数据库“S-T”创建关系SC(Sno,Cno,Grade),其中(Sno,Cno)为外码，参照Student和Course表。

第一个实验已完成以上操作。

二、删除实体、参照完整性以及自定义完整性；

利用SQL语句删除任务1创建的Student表、Course表以及SC表上的各列属性上约束条件；

（1）删除关系Student(Sno,Sname,Ssex,Sage,Sdept)中的约束；

（2）删除关系Course(Cno,Cname,Cpno,Ccredit)中的约束；

（3）删除关系SC(Sno,Cno,Grade)中的约束；

三、创建表后定义实体、参照完整性以及自定义完整性（可用CONSTRAINT语句）；

（1）增加Student表Sno主键约束，约束名P1；

（2）增加Student表Sname唯一性约束，约束名C2；

（3）修改表Student中的C1约束，要求学号改为在10000~999999之间；修改Student表中C4约束，要求年龄由小于30改为小于40。

（4）增加Course表Cno主键约束，约束名P2；

（5）增加Course表Cname唯一性约束，约束名C5；

（6）增加SC表（Sno，Cno）外码约束P3、P4，参照Student和Course表;

实验5 数据库设计

Step5：数据实施

1、通过SQL server设计工具实现数据库结构的设计。

步骤1：选择“数据库”，右键“新建数据库”；

步骤2：在新建数据库页面，设置数据库相关物理参数，比如数据库大小、增长方式、存储路径等；

步骤3：新建表，根据Step3设计的数据库关系表，完成数据库关系表的创建。表创建过程如下：

SQL代码

CREATE DATABASE [S-T];

CREATE TABLE Student
	(Sno CHAR(9) PRIMARY KEY,
	Sname CHAR(20) UNIQUE,
	Ssex CHAR(2)default '男',
	Sage SMALLINT,
	Sdept CHAR(20)
	)
CREATE TABLE Course
	(Cno CHAR(4) PRIMARY KEY,
	Cname CHAR(40)NOT NULL,
	Cpno CHAR(4),
	Ccredit SMALLINT,
	FOREIGN KEY(Cpno) REFERENCES Course(Cno)
	)
CREATE TABLE SC
	(Sno CHAR(9),
	Cno CHAR(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
	) 
ALTER TABLE Student 
ADD S_entrance DATE;
ALTER TABLE Student 
DROP COLUMN Scome;
ALTER TABLE Course
ALTER COLUMN Ccredit INT;
CREATE UNIQUE INDEX IndexSno ON Student(Sno DESC);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
DROP INDEX IndexSno ON Student;
DROP INDEX Coucno ON Course;

SELECT Sno,Sname
FROM Student;
SELECT *
FROM Student;
SELECT *
FROM Student
WHERE Sdept IN('MA');
SELECT Sname,2019-Sage,Sdept,LOWER(Sdept)
FROM Student;
SELECT Sname 姓名,2019-Sage 出生年份,LOWER(Sdept) 所属系
FROM Student;
SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
SELECT MAX(Grade),AVG(Grade)
FROM SC
WHERE Cno='1';
SELECT Sno
FROM SC;
SELECT S1.Sno
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='李洋'
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
SELECT Sname
FROM Student
WHERE Sname NOT LIKE '刘%'
SELECT Cno,Ccredit
FROM Course
WHERE Cname='DB_Design'
SELECT Sname,Ssex,Sdept
FROM Student
WHERE Sage BETWEEN 20 AND 30;
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('IS','CS','MA');
SELECT Cno,Cname
FROM Course
WHERE Cpno=''
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);
SELECT COUNT(*)
FROM Student
SELECT SUM(Ccredit),AVG(Ccredit),MAX(Ccredit),MIN(Ccredit)
FROM Course;
SELECT AVG(Grade)
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sdept='IS' AND Cname='数据结构';
SELECT *
FROM SC A 
WHERE Grade=
	 (SELECT MAX(Grade)
	  FROM SC
	  WHERE Sno=A.Sno
	  );
SELECT Sdept,COUNT(*)
FROM Student
GROUP BY Sdept
ORDER BY COUNT(*) DESC;
SELECT Sdept,Ssex,COUNT(*)
FROM Student
GROUP BY Sdept,Ssex
ORDER BY Sdept,Ssex DESC;
SELECT Sno,Sname
FROM Student
WHERE Sno IN
			(SELECT SC.Sno
			 FROM SC
			 GROUP BY SC.Sno
			 HAVING COUNT(*)>3);
SELECT Sno,AVG(Grade),MAX(Grade),MIN(Grade),COUNT(*)
FROM SC
GROUP BY Sno
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING COUNT(*)>=2
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=80
SELECT Sname FROM Student
WHERE Sno IN
			(SELECT Sno
			 FROM SC
			 WHERE Cno='1')
SELECT *
FROM Grade A
WHERE Grade<
			(SELECT AVG(Grade)
			 FROM SC
			 WHERE Cno=A.Cno)
SELECT Sname FROM Student
WHERE Sno NOT IN
			(SELECT Sno
			 FROM SC
			 WHERE Cno='1')
SELECT Sno,Sname,Sage
FROM Student A
WHERE NOT EXISTS
				(SELECT *
				 FROM 
				 STUDENT B
				 WHERE A.Sage=B.Sage AND A.sno<>B.sno)
SELECT Cno,Cname
FROM Course C
WHERE NOT EXISTS
				(SELECT *
				 FROM SC
				 WHERE SC.Cno=C.Cno)


SELECT MAX(Grade),AVG(Grade)
From SC
Where EXISTS
       (SELECT*
        From SC
        Where Cno='1')


SELECT Sname
FROM Student
WHERE NOT EXISTS
				(SELECT *
				 FROM SC
				 WHERE Sno=Student.Sno AND Cno='1');
SELECT Sname
FROM Student
WHERE NOT EXISTS
				(SELECT *
				 FROM Course
				 WHERE NOT EXISTS
								 (SELECT *
								  FROM SC
								  WHERE Sno=Student.Sno
									AND Cno=Course.Cno));
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
				(SELECT *
				 FROM SC SCY
				 WHERE SCY.Sno='95002'and
						NOT EXISTS
						(SELECT *
						 FROM SC SCZ
						 WHERE SCZ.Sno=SCX.Sno AND
							   SCZ.Cno=SCY.Cno));
			 

ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
DROP CONSTRAINT C4;
ALTER TABLE Student
DROP CONSTRAINT StudentKey;
ALTER TABLE Course
DROP CONSTRAINT CourseKey;
ALTER TABLE SC
DROP CONSTRAINT PK__SC__E6000253412EB0B6;
ALTER TABLE SC
DROP CONSTRAINT FK__SC__Cno__440B1D61;
ALTER TABLE SC
DROP CONSTRAINT FK__SC__Sno__4316F928;
ALTER TABLE Student
ADD CONSTRAINT P1 PRIMARY KEY(Sno);
ALTER TABLE Student
ADD CONSTRAINT C2 UNIQUE(Sno);
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK(Sno BETWEEN 10000 AND 999999);
ALTER TABLE Student
DROP CONSTRAINT C4;
ALTER TABLE Student
ADD CONSTRAINT C4 CHECK(Sage<40);
ALTER TABLE Course
ADD CONSTRAINT P2 PRIMARY KEY(Cno);
ALTER TABLE Course
ADD CONSTRAINT C5 UNIQUE(Cname);
ALTER TABLE SC 
Add CONSTRAINT P3 FOREIGN KEY(Sno)REFERENCES STUDENT(SNO); 
ALTER TABLE SC
Add CONSTRAINT P4 FOREIGN KEY(Cno)REFERENCES COURSE(CNO);