关系型数据库面经（数据库系统概论）-CSDN博客

本文链接：https://blog.csdn.net/m0_74229735/article/details/138589536

下面是我在面试中被问到的关系型数据库的问题并且我回答得不是很好的题目，写个博客记录一下。

下面用于实现数据存储安全性的SQL语句是？

实现数据存储安全性通常涉及到权限管理和加密等手段。在SQL中，用于控制数据访问权限的语句主要是GRANT和REVOKE。这些语句可以用来授予或撤销用户对数据库中特定数据的访问权限。

以下是一些常用的与数据安全性相关的SQL语句：

GRANT：用于授予用户或角色特定的权限。

GRANT SELECT, INSERT, UPDATE ON my_table TO user_name;

REVOKE：用于撤销之前授予的权限。

REVOKE INSERT, UPDATE ON my_table FROM user_name;

DENY：在某些数据库系统中（如SQL Server），可以使用DENY语句明确拒绝某个权限，即使该权限已经通过其他方式被授予。
```
DENY DELETE ON my_table TO user_name;
```
CREATE ROLE 和 DROP ROLE：用于创建和删除数据库角色，角色可以被用来组织权限，然后将这些权限授予不同的用户。
```
CREATE ROLE admin_role;
DROP ROLE admin_role;
```

CREATE USER 和 DROP USER：用于创建和删除数据库用户。

CREATE USER user_name IDENTIFIED BY 'password';
DROP USER user_name;

ALTER USER：用于修改数据库用户的信息，如密码。
```
ALTER USER user_name IDENTIFIED BY 'new_password';
```
ALTER TABLE：可以用来添加或修改列的加密属性（如果数据库支持数据列加密）。
```
ALTER TABLE my_table ADD COLUMN encrypted_data VARBINARY;
```
ENCRYPTED 关键字：在某些数据库系统中，可以在创建表时指定列的加密属性。
```
CREATE TABLE my_table (id INT, encrypted_data VARCHAR(255) ENCRYPTED);
```

需要注意的是，上述SQL语句可能因不同的数据库系统（如MySQL, PostgreSQL, SQL Server, Oracle等）而有所不同。此外，实现数据安全性不仅仅是通过SQL语句来控制访问权限，还需要考虑其他因素，如网络安全、数据备份、灾难恢复、加密存储和传输等。

请编写下面的sql语句：学生选课表中有属性学号和所选课程，课程信息表中有属性课程，查询选了全部课程的学生学号(关系代数中的除运算)

要查询选了全部课程的学生学号，可以使用SQL中的子查询和EXISTS逻辑运算符。这里假设学生选课表的名称为StudentCourses，课程信息表的名称为Courses。以下是相应的SQL语句：

SELECT SC.学号
FROM StudentCourses SC
WHERE NOT EXISTS (
    SELECT C.课程
    FROM Courses C
    WHERE NOT EXISTS (
        SELECT 1
        FROM StudentCourses SC2
        WHERE SC2.学号 = SC.学号 AND SC2.所选课程 = C.课程
    )
);

这个查询的逻辑是：对于每个学生，检查是否存在至少一门课程是他没有选择的。如果不存在这样的课程，即所有课程都被该学生选择了，那么这个学生的学号就会被包含在结果集中。

SQL代码分析：

SELECT 1是一个常见的SQL技巧，用于检查子查询是否有返回结果。这里的1是一个任意的常数，它的作用是作为子查询的返回值，以便外层查询可以使用EXISTS或NOT EXISTS来检查子查询是否有返回任何行。

具体的代码解释如下：

SELECT 1
FROM StudentCourses SC2
WHERE SC2.学号 = SC.学号 AND SC2.所选课程 = C.课程

这个子查询的目的是从StudentCourses表中选择出那些学号为SC.学号（即外层查询中当前学生的学号）且所选课程为C.课程（即外层查询中当前考虑的课程）的行。如果这样的行存在，说明该学生在选课表中选择了这门课程。

如果子查询返回了至少一行结果，那么EXISTS子句就会返回TRUE；如果子查询没有返回任何结果，那么EXISTS子句就会返回FALSE。在这个查询中，内层NOT EXISTS的意思是：“对于Courses表中的每一门课程，检查是否存在至少一行在StudentCourses表中的记录，使得该记录的学号与外层查询中的学号相同，并且所选课程与当前课程相同。”

外层的NOT EXISTS则是说：“找出那些在StudentCourses表中的学生，对于Courses表中的每一门课程，都不存在没有选择的情况。” 换句话说，就是找出那些选了所有课程的学生。

请编写下面的sql：学生表里面有属性姓名、成绩，求没有任何一门课程低于80分的学生姓名

SELECT 姓名
FROM Students
EXCEPT
SELECT 姓名
FROM Students
WHERE 成绩 < 80;

这个查询的逻辑是：首先，SELECT 姓名 FROM Students选择了所有学生的姓名。然后，EXCEPT关键字后面跟了一个子查询，这个子查询选择了所有成绩低于80分的学生姓名。最终的结果是第一个查询的结果减去第二个查询的结果，即所有没有成绩低于80分的学生姓名。

数据库系统的三级模式结构

内模式、模式和外模式个数的比例1：1：N

① 模式（Schema）

模式（也称逻辑模式）

数据库中全体数据的逻辑结构和特征的描述
所有用户的公共数据视图，综合了所有用户的需求

一个数据库只有一个模式，可以把模式看成唯一的数据库，实例就是数据库里面的多个表

模式的地位：是数据库系统模式结构的中间层

与数据的物理存储细节和硬件环境无关
与具体的应用程序、开发工具及高级程序设计语言无关

模式的定义:

数据的逻辑结构（数据项的名字、类型、取值范围等）
数据之间的联系
数据有关的安全性、完整性要求

② 外模式（External Schema）
外模式（也称子模式或用户模式）

数据库用户（包括应用程序员和最终用户）使用的局部数据的逻辑结构和特征的描述
数据库用户的数据视图，是与某一应用有关的数据的逻辑表示，可以看成把数据库的部分数据，运用在不同的应用程序上，在UI界面上显示

一个数据库只有一个模式，可以把模式看成唯一的数据库，实例就是数据库里面的多个表
外模式的地位：介于模式与应用之间

模式与外模式的关系：一对多

外模式通常是模式的子集;

一个数据库可以有多个外模式。反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求;

对模式中同一数据，在外模式中的结构、类型、长度、保密级别等都可以不同;

外模式与应用的关系：一对多

同一外模式也可以为某一用户的多个应用系统所使用;

但一个应用程序只能使用一个外模式;可以看成把数据库的部分数据，运用在不同的应用程序上，在UI界面上显示

外模式的用途

保证数据库安全性的一个有力措施；
每个用户只能看见和访问所对应的外模式中的数据；

③ 内模式（Internal Schema）
内模式（也称存储模式）

是数据物理结构和存储方式的描述
是数据在数据库内部的表示方式:

记录的存储方式（顺序存储，按照B树结构存储，
按hash方法存储）
索引的组织方式
数据是否压缩存储
数据是否加密
数据存储记录结构的规定

一个数据库只有一个内模式