BUAA-DataBase-Homework3

数据库第三章作业

实验环境

• Windows 10 21H2
• SQL Server 19
• Microsoft SQL Server Management Studio

实验内容

创建数据库

1. 创建一个 school 数据库，为数据库文件和日志文件命名，并将它们存储到不同目录下。将数据库文件的初始大小设置为 10M，最大大小设置为 20M，每次增长 1M。
2. 为 school 数据库再增加一个数据库文件。

新建数据库图示如下，过程中要注意选取自动增长和最大大小。数据库文件第一行为普通数据，第二行为数据库日志文件。

右键数据库名并查看数据库属性，可以添加新的数据库文件

完成练习

数据表规格如下：

• 学生表（学号，姓名，性别，年龄，入学年份，籍贯，系号，班长学号），学号是主码，系号和班长学号是外部码，手机号码必须唯一，学生的年龄不得小于10岁和大于50岁，性别必须是’男’或者’女’。
• 系表（系号，系名，系主任），其中系号是主码，系名不能有重复的。
• 选课表（学号，课程号，成绩），（学号，课程号）是主码，学号和课程号是外部码，成绩不能小于0分和大于100分
• 课程表（课程号，课程名，先修课，学分），课程号是主码，课程名必须唯一，学分必须大于0小于5。

• 创建上述表，并定义相应的完整性约束。

实现数据表创建的代码为 Create.sql 文件，于 SSMS 中运行可以直接获得正确的数据表结构。

/* 建立数据表和相关约束 */

CREATE TABLE Department(
	Dnumber INT PRIMARY KEY,
	Dname VarChar(40) UNIQUE,
	Director VarChar(10)
);

CREATE TABLE Class(
	Cnumber VarChar(10) PRIMARY KEY,
	Cname VarChar(40) UNIQUE,
	Cprev VarChar(10),
	Ccredit FLOAT CONSTRAINT Ccredit_1 CHECK (Ccredit > 0 AND Ccredit < 5),
);

CREATE TABLE Student(
	Sno VarChar(10) PRIMARY KEY,
	Sname VarChar(40),
	Ssex VarChar(10) CONSTRAINT Ccredit_0 CHECK (Ssex='男' OR Ssex='女'),
	Sage FLOAT CHECK (Sage >= 10 AND Sage <= 50),
	Syear FLOAT,
	Shometown VarChar(40),
	SDnumber INT,
	Smonitor VarChar(10),
	FOREIGN KEY (SDnumber) REFERENCES Department(Dnumber),
	FOREIGN KEY (Smonitor) REFERENCES Student(Sno)
);

CREATE TABLE Record(
	Sno VarChar(10),
	Cnumber VarChar(10),
	Score FLOAT CHECK (Score >= 0 AND Score <= 100),
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cnumber) REFERENCES Class(Cnumber),
    PRIMARY KEY (Sno, Cnumber)
);

• 将提供的上述各表的数据导入 SQL Server。如果导入过程存在错误，请说明可能是什么原因。

通过数据库菜单下的 任务 - 导入数据 操作将 Excel 文件导入数据库中（Excel → SQL Server Native Client 11.0）。导入过程中发生了错误，经检查发现是因为约束条件出错：不能将选课表中不存在的学号（外码） 0320、0321 加入数据表中；同时还存在同一学生有多个同一课程的成绩的现象（主码重复），修改方式是删去不合法记录。

• 在学生表中插入学号为26，姓名为’李四’，性别为’女’，年龄为20，入学年份为2008，籍贯为’广东’，手机号码为10010001000，班长学号为10的一条记录。

首先，原文件中学生表不存在手机号码字段，故予以忽略；其次，不存在学号为 10 的记录，在这里忽视班长学号的信息。

INSERT
INTO Student
Values ('26', '李四', '女', 20, 8, '广东', null, '0105');

• 删除上述记录。

由于 Student 主码为学号（Sno），故使用 Sno 作为筛选条件删除学号为 26 的记录即可。

DELETE
FROM Student
WHERE Sno=26;

• 将学生表中的姓名字段的长度改为6个汉字。

由于姓名字段原本使用了 VarChar(10) 作为字段长度限制，且一个汉字通常使用两个VarChar，故将长度修改为 VarChar(12) 即可。

/* ALTER COLUMN */

ALTER TABLE Student 
ALTER COLUMN Sname VarChar(12);

操作后可从可视化界面中观察结果：

• 为学生表增加一个字段电子邮件，20个字符。

对一般英文字符，使用 Char 作为变量类型即可。

/* ALTER COLUMN */

ALTER TABLE Student 
ADD Email Char(20);

查询整个表的结果如下，发现 Email 列添加成功

• 对课程表的学分字段上的完整性约束进行修改，使其在0到6之间取值。

修改完整性约束应拆解为删除旧约束并添加新约束：

ALTER TABLE Class 
DROP CONSTRAINT Ccredit_1;

ALTER TABLE Class 
ADD CONSTRAINT Ccredit_2 CHECK (Ccredit >= 0 AND Ccredit <= 6);

• 为学生表在学号列上创建cluster索引。

将按学号升序建立唯一聚簇索引。

CREATE UNIQUE INDEX Stusno ON Student(Sno);

• 创建一个视图，计算每门课的最高分。

CREATE VIEW C_G(Cnumber, Gmax)
AS
SELECT Cnumber, MAX(Score)
FROM Record
Group BY Cnumber;

• 查找每个学生的学号、姓名、总成绩和平均分。

SELECT Student.Sno, Sname, SUM(Score), AVG(Score)
FROM Student, Record
WHERE Student.Sno = Record.Sno
Group BY Student.Sno, Sname;

• 将6系所有学生的年龄，改为7系学生的平均年龄。

没有 7 系学生，条件改为 8 系。

UPDATE Student
SET Sage=(
	SELECT AVG(b.Sage) 
	FROM Student b 
	WHERE b.SDnumber = 8
)
WHERE SDnumber = 6;

• 将’曹洪’同学操作系统课程的成绩改为62分。

UPDATE Record
SET Score = 62
WHERE Sno = (
	SELECT Sno
	FROM Student
	WHERE Sname = '曹洪'
) AND Cnumber = (
	SELECT Cnumber
	FROM Class
	WHERE Cname = '操作系统'
);

• 查找所有学生的姓名、入学年份和籍贯。

SELECT Sname, Syear, Shometown FROM Student;

• 列出籍贯为’山东’的同学的所有属性。

SELECT *
FROM Student
WHERE Shometown = '山东';

• 查找年龄最小的学生的学号和姓名。

SELECT Sno, Sname
FROM Student
WHERE Sage = (
	SELECT MIN(Sage)
	FROM Student
);

• 查找选修了’数据库’的学生的学号。

SELECT Sno
FROM Record
WHERE Cnumber = (
	SELECT Cnumber
	FROM Class
	WHERE Cname = '数据库'
);

• 查找选修了’编译技术’的女学生的学号和姓名。

SELECT Record.Sno, Sname
FROM Record, Student
WHERE Record.Sno = Student.Sno AND
	Cnumber = (
		SELECT Cnumber
		FROM Class
		WHERE Cname = '编译技术'
	) AND Ssex = '女';

• 查找’典韦’同学的班长所选修的课程的课程号。

SELECT Cnumber
FROM Record, Student
WHERE Record.Sno = Student.Sno AND
	Record.Sno = (
		SELECT Smonitor
		FROM Student
		WHERE Sname = '典韦'
	);

• 查找名字中倒数第二字为’侯’的学生的学号、姓名和所在系的系名。

SELECT Sno, Sname, SDnumber
FROM Student
WHERE Sname LIKE '%侯_';

• 查找名字以P打头，倒数第三字为L的课程的名字。

SELECT Cname
FROM Class
WHERE Cname LIKE 'P%L__';

• 查找’甘宁’同学所有选修课程的总分。

SELECT SUM(Score) AS TotalScore
FROM Record
WHERE Sno = (
	SELECT Sno
	FROM Student
	WHERE Sname = '甘宁'
);

• 查找既选修了’数据库’，也选修了’操作系统’的同学。

SELECT Sno, Sname
FROM Student s
WHERE EXISTS(
	SELECT *
	FROM Record
	WHERE s.Sno = Sno AND Cnumber = (
		SELECT Cnumber
		FROM Class
		WHERE Cname = '数据库'
	)
) AND EXISTS(
	SELECT *
	FROM Record
	WHERE s.Sno = Sno AND Cnumber = (
		SELECT Cnumber
		FROM Class
		WHERE Cname = '操作系统'
	)
);
/* Another Way to Solve */
SELECT Sno, Sname
FROM Student s
WHERE EXISTS(
	SELECT *
	FROM Record, Class
	WHERE Record.Cnumber = Class.Cnumber AND
		Cname IN ('数据库', '操作系统') AND
		Sno = s.Sno
	Group BY Sno
	HAVING COUNT(*) = 2
);

• 查找没有选修’数据库’课程的学生的学号和姓名。

SELECT DISTINCT Student.Sno, Sname
FROM Student, Record r1, Class
WHERE Student.Sno = r1.Sno AND
	r1.Cnumber = Class.Cnumber AND
	NOT EXISTS(
		SELECT *
		FROM Record r2
		WHERE r1.Sno = r2.Sno AND
			r2.Cnumber = (
				SELECT Cnumber
				FROM Class
				WHERE Cname = '数据库'
			)
);

• 查找’数据库’课程及格了，但’编译技术’没有及格的学生的学号和姓名。

SELECT DISTINCT Student.Sno, Sname
FROM Student, Record
WHERE Student.Sno = Record.Sno AND 
	EXISTS (
		SELECT *
		FROM Record
		WHERE Sno = Student.Sno AND
			Cnumber = (
				SELECT Cnumber
				FROM Class
				WHERE Cname = '数据库') AND
			Score >= 60
	) AND EXISTS(
		SELECT *
		FROM Record
		WHERE Sno = Student.Sno AND
			Cnumber = (
				SELECT Cnumber
				FROM Class
				WHERE Cname = '编译技术') AND
			Score < 60
	);

• 查找数据库成绩低于数据库课平均成绩的同学的学号和姓名。

SELECT DISTINCT Student.Sno, Sname
FROM Student, Record
WHERE Student.Sno = Record.Sno AND
	EXISTS(
		SELECT *
		FROM Record
		WHERE Sno = Student.Sno AND
			Cnumber = (
			SELECT Cnumber
			FROM Class
			WHERE Cname = '数据库') AND
			Score < 
					(SELECT AVG(Score)
					FROM Record
					WHERE Cnumber = (
							SELECT Cnumber
							FROM Class
							WHERE Cname = '编译技术'
						)
					)
	);

• 查找与’貂蝉’同学选修课程完全相同的学生的学号和姓名（不能多选也不能少选）。

SELECT s1.Sno, s1.Sname
FROM Student s1
WHERE NOT EXISTS(
	SELECT *
	FROM Record r1
	WHERE r1.Sno = (SELECT Sno FROM Student WHERE Sname = '貂蝉') AND
		NOT EXISTS(
			SELECT *
			FROM Record r2
			WHERE r2.Sno = s1.Sno AND
				r2.Cnumber = r1.Cnumber
		)
) AND (SELECT COUNT(*) 
		FROM Record 
		WHERE Sno = s1.Sno) = (
			SELECT COUNT(*) 
			FROM Record 
			WHERE Sno = (
				SELECT Sno 
				FROM Student 
				WHERE Sname = '貂蝉'
				)
			);

• 查找不仅选修了’貂蝉’同学选修的课程，而且还选修了其他课程的同学。

SELECT s1.Sno, s1.Sname
FROM Student s1
WHERE NOT EXISTS(
	SELECT *
	FROM Record r1
	WHERE r1.Sno = (SELECT Sno FROM Student WHERE Sname = '貂蝉') AND
		NOT EXISTS(
			SELECT *
			FROM Record r2
			WHERE r2.Sno = s1.Sno AND
				r2.Cnumber = r1.Cnumber
		)
) AND (SELECT COUNT(*) 
		FROM Record 
		WHERE Sno = s1.Sno) > (
			SELECT COUNT(*) 
			FROM Record 
			WHERE Sno = (
				SELECT Sno 
				FROM Student 
				WHERE Sname = '貂蝉'
				)
			);

• 查找’高等数学’平均成绩最高的系的系名。

SELECT TOP 1 Dname
FROM Record, Student, Department
WHERE Cnumber = (SELECT Cnumber FROM Class WHERE Cname = '数学') AND 
	Record.Sno = Student.Sno AND
	Student.SDnumber = Department.Dnumber
GROUP BY SDnumber, Dname
ORDER BY AVG(Score) desc;

• 查找至少有一个籍贯为’四川’同学所选修的课程的课程名。

SELECT Cname
FROM Student, Class, Record
WHERE Student.Sno = Record.Sno AND
	Class.Cnumber = Record.Cnumber AND
	EXISTS(SELECT *
	WHERE Shometown = '四川')
GROUP BY Cname;

鉴定为，根本没有。

• 查询选修了’数据库’课程的学生的学号和获得的学分。

SELECT Student.Sno, Ccredit
FROM Student, Record, Class
WHERE Student.Sno = Record.Sno AND
	Record.Cnumber = Class.Cnumber AND
	Cname = '数据库';

回答问题

• SQL语言的特点。

综合统一、高度非过程化、面向集合的操作方式、以同一种语法结构提供多种使用方式、语言简洁

• 创建一个数据库，需要创建几个文件，它们分别是做什么用的？它们对应于三级模式中的哪一级？创建的表存储在什么地方？它们对应于三级模式中的哪一级？

创建两个文件。分别是数据库主文件和数据库日志文件。它们对应三级模式中的内模式。创建的表存储在数据库主文件中，对应模式。

• 可以为表定义哪些完整性约束？它们各自的作用是什么？

约束类型	说明
主键约束 （PRIMARY KEY）	确定表中的标识列（主键字段不能为空，必须唯一）标识：确定一个对象的唯一表现
外键约束 （FOREIGN KEY）	确定表与表之间的联系方式，一般情况下通过主表的标识列进行确定主表：给哪张表添加约束哪张表就是主表，辅助表为从表
唯一约束 （UNIQUE）	确定这个字段中的数据必须是唯一存在的
非空约束（NOT NULL）	确定这个字段中的数据必须不能为空
检查约束 （CHECK）	设置这个字段中的数据特性
默认约束 （DEFAULT ）	若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL

• 自然连接和等值连接有什么差别？

等值连接是基于两个表之间的相等条件进行连接，而自然连接是基于两个表之间的相同列名进行连接。等值连接可以连接任意两个表，而自然连接只能连接具有相同列名的两个表。等值连接可以通过指定连接条件来连接两个表，而自然连接只能通过列名相同来连接两个表。

• 子查询分为哪几种？它们之间有什么区别？

子查询主要包括嵌套子查询和连接子查询两种类型。

嵌套子查询是一个查询内部包含另一个子查询的查询。它可以用于在一个完整的查询中引用外部查询中的条件，通常用于在FROM子句中引用外部查询的结果集。嵌套子查询通常涉及多层嵌套，但也可以有单层嵌套。

连接子查询则主要用于在两个或多个表之间建立关系，通过在WHERE子句中使用外部查询的结果作为连接条件。连接子查询可以使用INNER JOIN、LEFT JOIN、RIGHT JOIN等连接类型。

这两种子查询的主要区别在于它们的使用方式和目的不同。嵌套子查询主要关注如何在一个完整的查询中引用外部查询中的条件，而连接子查询则关注如何在多个表之间建立关系。同时，它们的使用场景和限制也不同，需要根据具体的需求和场景选择合适的子查询类型。另外，嵌套子查询和连接子查询在语法上也有所不同，需要根据具体情况进行适当的调整。

• 索引有什么作用和缺点？

1. 提高查询性能：索引可以大大提高查询速度，特别是在处理大量数据时。
2. 减少磁盘I/O操作：索引通过将数据存储在内存中，减少了从磁盘中读取数据的需求，从而减少了I/O操作的数量。
3. 减少重新排序和排序成本：索引通常基于数据表的某些列创建，这样可以减少在进行更新操作时对整个表进行重新排序或排序的成本。

其主要缺点是会增加插入、更新和删除操作的开销，因为每次对这些操作都需要重新构建索引。此外，如果一个表上的索引过多，可能会占用更多的存储空间，并影响数据库的性能。因此，在设计数据库时，需要权衡索引的优点和缺点，并根据实际需求选择合适的索引策略。

• 基本表和视图有什么区别？视图有什么优点？什么样的视图是可以更新的？

基本表是数据库中实际存在的表，具有完整的字段和记录，并且可以与其他表建立关系。基本表提供了数据存储的框架，是数据库中数据存储的基本单位。视图是基本表的虚拟表，它是基于基本表的查询结果而生成的。在实际操作中，视图并不存在于数据库中，它只是一种基于基本表的查询逻辑。

视图优点有：

1. 简化操作：视图可以对基本表进行筛选，只显示符合特定条件的数据，从而简化复杂的查询操作。
2. 提高安全性：视图可以用于保护数据，只有具有适当权限的用户才能查看视图的记录。
3. 提高性能：视图可以减少对基本表的频繁查询操作，从而提高查询性能。

可以更新的视图是指用户可以通过直接修改视图的定义来更新视图所展示的数据。通常情况下，只有受支持的、符合特定条件的视图才可以进行更新操作。更新的操作通常涉及到对基本表的修改，并在更新后重新创建视图。

• 请针对第三章SQL语言讲义中的除法例子，给出其他两种除法的实现方法。

SQL语言中没有全称量词，具体实现时可以把带有全称量词的谓词转换为等价的带有存在量词的谓词。

解决这类的除法问题一般采用双嵌套notexists来实现带全称量词的查询解决所谓forall的问题。