db第三章作业

一、在school数据库中完成以下练习

sql语句

-- 1 创建表，并定义相应的完整性约束
CREATE TABLE 系 (
	系号 INT,
	系名 VARCHAR(30) UNIQUE,
	系主任 VARCHAR(15),
	PRIMARY KEY(系号)
);

CREATE TABLE 学生(
	学号 VARCHAR(15),
	姓名 VARCHAR(10),
	性别 CHAR(1) CHECK (性别 IN ('男', '女')),
	年龄 INT CHECK (年龄 BETWEEN 10 AND 50),
	入学年份 VARCHAR(10),
	籍贯 VARCHAR(50),
	系号 INT,
	手机号 VARCHAR(15) UNIQUE,
	班长学号 VARCHAR(10),
	PRIMARY KEY(学号),
	FOREIGN KEY(系号) REFERENCES 系(系号),
	FOREIGN KEY(班长学号) REFERENCES 学生(学号)
);

CREATE TABLE 课程 (
	课程号 VARCHAR(15),
	课程名 VARCHAR(15) UNIQUE,
	先修课 VARCHAR(15),
	学分 DECIMAL(2,1),
	CHECK (学分>0 AND 学分<5),
	PRIMARY KEY(课程号)
);

CREATE TABLE 选课 (
	学号 VARCHAR(15),
	课程号 VARCHAR(15),
	成绩 DECIMAL(5, 2),
	PRIMARY KEY(学号, 课程号),
	FOREIGN KEY(学号) REFERENCES 学生(学号),
	FOREIGN KEY(课程号) REFERENCES 课程(课程号),
	CHECK (成绩 BETWEEN 0 AND 100)
);

-- 2 将提供的上述各表的数据导入SQL Server

-- 3 在学生表中插入学号为26，姓名为'李四'，性别为'女'，年龄为20，入学年份为2008，籍贯为'广东'，手机号码为10010001000，班长学号为10的一条记录
INSERT INTO 学生 VALUES(26, '李四', '女', 20, 2008, '广东', null, 10010001000, 10);

-- 4  删除上述记录
DELETE from 学生 where 学号 = 26;

-- 5 将学生表中的姓名字段的长度改为6个汉字
ALTER TABLE 学生 MODIFY 姓名 VARCHAR(18);

-- 6. 为学生表增加一个字段电子邮件，20个字符。
ALTER TABLE 学生 ADD 电子邮件 VARCHAR(20);

-- 7.对课程表的学分字段上的完整性约束进行修改，使其在0到6之间取值。
ALTER TABLE 课程 MODIFY 学分 DECIMAL(2,1) CHECK (学分>=0 AND 学分<=6);

-- 8. 为学生表在学号列上创建cluster索引。
-- CACHE INDEX idx_学生_学号 ON 学生(学号);

-- 9. 创建一个视图，计算每门课的最高分。
CREATE VIEW 每门课最高分 AS
SELECT 课程号, MAX(成绩) AS 最高分 FROM 选课
GROUP BY 课程号;

-- 10.查找每个学生的学号、姓名、总成绩和平均分。
SELECT 学生.学号, 姓名, SUM(选课.成绩) AS 总成绩, AVG(选课.成绩) AS 平均分 FROM 学生
JOIN 选课 on 学生.学号 = 选课.学号 GROUP BY 学生.学号;

-- 11.将6系所有学生的年龄，改为7系学生的平均年龄。
SET @avg_age = (SELECT AVG(年龄) FROM 学生 WHERE 系号 = 7);
UPDATE 学生 SET 年龄 = @avg_age WHERE 系号 = 6;

-- 12.将’曹洪’同学操作系统课程的成绩改为62分。
UPDATE 选课
SET 成绩 = 62
WHERE 学号 = (SELECT 学号 FROM 学生 WHERE 姓名 = '曹洪')
  AND 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 = '操作系统');

-- 13. 查找所有学生的姓名、入学年份和籍贯。
SELECT 姓名, 入学年份, 籍贯 FROM 学生;

-- 14.列出籍贯为'山东'的同学的所有属性。
SELECT * FROM 学生 WHERE 籍贯 = '山东';

-- 15.查找年龄最小的学生的学号和姓名。
SELECT 学号, 姓名 FROM 学生 WHERE 年龄 = (SELECT MIN(年龄) FROM 学生);

-- 16.查找选修了'数据库'的学生的学号。
SELECT 学号 FROM 选课 WHERE 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库');

-- 17. 查找选修了'编译技术'的女学生的学号和姓名。
SELECT 学生.学号, 姓名 FROM 学生
    JOIN 选课 ON 学生.学号 = 选课.学号
        WHERE 选课.课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 = '编译技术') AND 性别 = '女';

-- 18. 查找'典韦'同学的班长所选修的课程的课程号。
SELECT 课程号 FROM 选课
WHERE 学号 = (SELECT 班长学号 FROM 学生 WHERE 姓名 = '典韦');

-- 19. 查找名字中倒数第二字为'侯'的学生的学号、姓名和所在系的系名。
SELECT 学号, 姓名, 系.系名 FROM 学生
JOIN 系 ON 学生.系号 = 系.系号
WHERE SUBSTRING(姓名, -2, 1) = '侯';

-- 20. 查找名字以P打头，倒数第三字为L的课程的名字
SELECT 课程名 FROM 课程 WHERE 课程名 LIKE 'P%' AND SUBSTRING(课程名, -3, 1) = 'L';

-- 21. 查找'甘宁'同学所有选修课程的总分。
SELECT SUM(成绩) FROM 选课 WHERE 学号 = (SELECT 学号 FROM 学生 WHERE 姓名 = '甘宁');

-- 22. 查找既选修了'数据库'，也选修了'操作系统'的同学。
SELECT * FROM 学生 WHERE 学号 IN (
	SELECT 学号 FROM 选课 WHERE 课程号 = (
	    SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库'
           )
)
INTERSECT SELECT * FROM 学生 WHERE 学号 IN (
	SELECT 学号 FROM 选课 WHERE 课程号 = (
	    SELECT 课程号 FROM 课程 WHERE 课程名 = '操作系统'
    )
);

-- 23. 查找没有选修'数据库'课程的学生的学号和姓名。
SELECT 学号, 姓名 FROM 学生
WHERE 学号 NOT IN (SELECT 学号
                   FROM 选课
                   WHERE 课程号 = (SELECT 课程号
                                   FROM 课程
                                   WHERE 课程名 = '数据库'));

-- 24. 查找'数据库'课程及格了，但'编译技术'没有及格的学生的学号和姓名。
SELECT 学号, 姓名 FROM 学生 WHERE 学号 IN (
	SELECT 学号 FROM 选课 WHERE 课程号 = (
		SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库'
	) AND 成绩 >= 60
    INTERSECT
    SELECT 学号 FROM 选课 WHERE 课程号 = (
		SELECT 课程号 FROM 课程 WHERE 课程名 = '编译技术'
	) AND 成绩 < 60
);

-- 25. 查找数据库成绩低于数据库课平均成绩的同学的学号和姓名。
SELECT 学号, 姓名 FROM 学生 WHERE 学号 IN (
	SELECT 学号 FROM 选课
	WHERE 课程号 = (
        SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库'
	) AND 成绩 < (
	    SELECT AVG(成绩) FROM 选课 WHERE 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库')
	)
);

-- 26. 查找与'貂蝉'同学选修课程完全相同的学生的学号和姓名（不能多选也不能少选）。
SELECT @diaochan_id := 学号 FROM 学生 WHERE 姓名 = '貂蝉';
SELECT 学生.学号, 姓名 FROM 学生
JOIN 选课 ON 学生.学号 = 选课.学号
WHERE 学生.学号 <> @diaochan_id -- 排除自己
GROUP BY 学生.学号
HAVING COUNT(选课.课程号) = (
    SELECT COUNT(课程号) FROM 选课 WHERE 学号 = @diaochan_id
)
AND NOT EXISTS ( -- 没有选修貂蝉没有选的课程
        SELECT 1
        FROM 选课 AS T1
        WHERE T1.学号 = 学生.学号
          AND T1.课程号 NOT IN (
            SELECT 课程号 FROM 选课 WHERE 学号 = @diaochan_id
        )
)
AND NOT EXISTS ( -- 貂蝉没有选修这些学生没有选的课程
        SELECT 1
        FROM 选课 AS T2
        WHERE T2.学号 = @diaochan_id
          AND T2.课程号 NOT IN (
            SELECT 课程号
            FROM 选课
            WHERE 学号 = 学生.学号
        )
);

-- 27. 查找不仅选修了'貂蝉'同学选修的课程，而且还选修了其他课程的同学。
SELECT 学生.学号, 姓名 FROM 学生
JOIN 选课 ON 学生.学号 = 选课.学号
WHERE 学生.学号 <> @diaochan_id
GROUP BY 学生.学号
HAVING COUNT(选课.课程号) > (
    SELECT COUNT(课程号) FROM 选课 WHERE 学号 = @diaochan_id
)
AND EXISTS ( -- 选修了貂蝉没有选的课程
    SELECT 1
    FROM 选课 AS T1
    WHERE T1.学号 = 学生.学号
      AND T1.课程号 NOT IN (
        SELECT 课程号 FROM 选课 WHERE 学号 = @diaochan_id
    )
)
AND NOT EXISTS( -- 貂蝉没有选修这些学生没有选的课程
    SELECT 1
    FROM 选课 AS T1
    WHERE T1.学号 = @diaochan_id AND T1.课程号 NOT IN (
        SELECT 课程号 FROM 选课 WHERE 学号 = 学生.学号
    )
);

-- 28. 查找'高等数学'平均成绩最高的系的系名。
SELECT 系名 FROM (SELECT 系.系名, AVG(成绩) AS 平均成绩
    FROM 选课
    JOIN 学生 ON 选课.学号 = 学生.学号
    JOIN 课程 ON 选课.课程号 = 课程.课程号
    JOIN 系 ON 学生.系号 = 系.系号
    WHERE 课程名 = '数学'
    GROUP BY 系.系号) as 平均成绩表
    ORDER BY 平均成绩 DESC LIMIT 1;

-- 29. 查找至少有一个籍贯为'四川'同学所选修的课程的课程名。
SELECT 课程名 FROM 课程 WHERE 课程号 IN (
    SELECT 课程号 FROM 选课 WHERE 学号 IN (
        SELECT 学号 FROM 学生 WHERE 籍贯 = '四川'
    )
);

-- 30. 查询选修了'数据库'课程的学生的学号和获得的学分。
SELECT 学号, SUM(课程.学分) AS 学分 FROM 选课
    JOIN 课程 ON 选课.课程号 = 课程.课程号
    WHERE 学号 IN (SELECT 学号 FROM 选课 WHERE 课程号 = (SELECT 课程号 FROM 课程 WHERE 课程名 = '数据库'))
    GROUP BY 学号;

运行结果

由于页面有限，部分查询结果只展示前几条）

2

学生表导入存在问题，因为班长学号不存在，应调整学生顺序，使班长先导入

选课表导入存在问题，具体如下：

0: Duplicate entry '0106-c04' for key '选课.PRIMARY'
0: Duplicate entry '0108-c04' for key '选课.PRIMARY'
0: Duplicate entry '0301-c03' for key '选课.PRIMARY'
0: Duplicate entry '0317-c02' for key '选课.PRIMARY'
0: Cannot add or update a child row: a foreign key constraint fails (`mysql`.`选课`, CONSTRAINT `选课_ibfk_1` FOREIGN KEY (`学号`) REFERENCES `学生` (`学号`))
0: Cannot add or update a child row: a foreign key constraint fails (`mysql`.`选课`, CONSTRAINT `选课_ibfk_1` FOREIGN KEY (`学号`) REFERENCES `学生` (`学号`))

（1）有主码重复的实体，需剔除；（2）表明有学号不在学生表里的

3-7运行结果：

其中3执行失败，因为班长的学号不存在

8

MySQL 中无聚集索引概念，通常会在创建表时定义主键自动创建

9

10

11

学生表中不存在7系的学生，因此执行完后，6系学生的年龄会变为null

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

无

30

二、回答问题：

1. SQL语言的特点。

   • 综合统一

   • 高度非过程化

   • 面向集合的操作方式

   • 以同一种语法结构提供两种使用方式

   • 语言简捷，易学易用

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

   数据库文件类型：

   1. 主数据文件（Primary Data File）：用于存储数据库的所有数据，包括用户数据、系统表、元数据等。每个数据库有且只有一个主数据文件。对应于内模式（物理模式），也就是数据库的物理存储结构。
   2. 辅助数据文件（Secondary Data File）：当主数据文件无法容纳更多数据时，可以创建辅助数据文件来扩展数据库的存储容量。辅助数据文件是可选的，只有在需要扩展时才会创建。同样对应于内模式，用于物理存储扩展。
   3. 日志文件（Transaction Log File）：用于记录数据库的所有事务活动（如插入、更新、删除等），确保在数据库出现故障时能够进行恢复。它主要负责事务管理与故障恢复。日志文件也属于内模式，管理数据库的事务和恢复过程。

   表的存储位置：创建的表及其数据存储在主数据文件和辅助数据文件中（即 .mdf 或 .ibd 文件中）。

   表对应的三级模式：

   • 内模式：表在物理存储上的实现方式，例如文件如何存储、数据如何在硬盘上分布、数据如何通过索引来访问。这是数据库系统内部如何管理存储的具体实现。
   • 概念模式：从用户角度看，表是逻辑层的数据结构（例如表的字段、主键、外键等）。在这个层次上，用户不需要关心数据的实际存储结构，只关心如何访问和操作表。

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

   • 主键约束（PRIMARY KEY）：确保每一行都有唯一标识的字段，主键列的值不能重复，且不能为空。
   • 唯一约束（UNIQUE）：保证列或列的组合在表中具有唯一值，可以为空值，但多个空值允许存在。
   • 外键约束（FOREIGN KEY）：用于维护数据之间的参照完整性，确保一个表中的值必须在另一个表的主键或唯一列中存在。
   • 非空约束（NOT NULL）：防止列中出现空值，确保必须为该列插入有效数据。
   • 检查约束（CHECK）：用于限制列中的数据值范围或条件，确保数据符合指定的条件。

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

   • 自然连接自动基于两个表中相同的属性列进行连接，省去了连接条件
   • 等值连接是基于两个表中某一列（或多列）值相等的连接，通常需要使用ON或WHERE条件来指定连接的条件

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

   子查询按照与外部查询的联系不同，分为普通子查询和相关子查询

   • 普通子查询：与外部查询无关，可单独执行得一组值。

   • 相关子查询：把外查询的列值作为检索条件的条件值

6. 索引有什么作用和缺点？

   作用：

   • 加速查询：通过创建索引可以显著加快数据的查询速度。
   • 提高排序性能：索引有助于快速实现排序操作。
   • 加速表连接：索引有助于在连接表时提高性能。

   缺点：

   • 占用空间：索引会占用额外的存储空间，特别是大数据集上的多索引。
   • 增加修改成本：对带有索引的表进行插入、删除或更新时，索引需要同步更新，增加了写操作的开销。

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

   区别：

   • 基本表：物理存在的表，存储实际数据。
   • 视图：逻辑上的虚拟表，不存储数据，仅是对查询结果的命名。

   视图的优点：

   • 能够简化用户操作
   • 使用户能够以多种角度看待同一数据
   • 提供了一定程度的逻辑独立性
   • 能够对数据提供安全保护

   可更新的视图：通常视图涉及单个表，并且视图中包含了基础表的主键和非计算列，才能进行更新。

8. 请针对第三章SQL语言讲义中的除法例子，给出其他两种除法的实现方法。
   假设需要查询选修了全部课程的学生姓名

- 方法一：使用group by...having
SELECT Sname FROM Student WHERE Sno IN (
    SELECT Sno FROM  SC GROUP BY Sno HAVING COUNT(SC.Cno)=(SELECT COUNT(Cno) FROM Course
);

- 方法二：使用双not exists
Select Sname From Student
    Where Not Exists 
          (Select * from Course
              Where Not Exists
                  (Select * from SC
                     Where Sno=Student.Sno 
                          And Cno=Course.Cno));

- 方法三：参考第二章除运算的定义，将其翻译为sql语句
SELECT Sname FROM Student WHERE Sno NOT IN (
    SELECT Sno FROM (
        - 存在课程没选的学生
        SELECT Sno, Cno FROM Student CROSS JOIN (SELECT Cno FROM Course) AS T1 - 所有学生都选择了所有课程的选课表
        EXCEPT SELECT Sno, Cno FROM SC AS T2 - 做差运算，得出有课没选的情况
	) AS T3
);