数据库PPT课后习题 - 第三章

最新推荐文章于 2024-08-14 23:11:00 发布

Catalany

最新推荐文章于 2024-08-14 23:11:00 发布

阅读量945

点赞数 12

分类专栏：数据库复习文章标签：数据库笔记

本文链接：https://blog.csdn.net/Catalany/article/details/139029762

版权

数据库复习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

PPT 课后习题

3.1.1 SQL是一种什么类型的语言？它与 Java 有什么区别？

SQL（Structured Query Language，结构化查询语言）是一种对关系数据库进行访问的数据操作语言。
SQL 是一种专门用于数据库操作的语言，而 Java 是一种通用的编程语言，用途更加广泛。在实际开发中，通常会将两者结合使用，使用 Java 来开发应用程序，并使用 SQL 来管理和操作数据库中的数据。

SQL语言的特点：

数据的定义、操纵、控制一体化
既可以命令行联机操作、也可以嵌入编程操作
非过程化，数据的存取路径与具体操作处理由 DBMS 系统自动完成
语言简洁、易学易用

3.1.2 SQL 语言中的 DDL 语句与 DML 语句有何区别？

DDL（Data Defination Language），数据定义语言。用于创建、修改、删除数据库对象的语句。

CREATE

DROP

ALTER

DML（Data Manipulation Language），数据操纵语言。

INSERT

UPDATE

DELETE

此外，还有 DQL（Query）、DCL（Control）、 TCL（Transaction Control）、 CCL（Cursor Control）

3.1.3 PostgreSQL数据库除了支持标准 SQL 语言外，还支持哪些语言操作？

（感觉不会考）

除了标准 SQL 语言外，PostgreSQL 还支持以下语言操作：

PL/pgSQL：过程化语言，类似于 Oracle 的 PL/SQL 和 Microsoft SQL Server 的 T-SQL。它提供了过程、函数、条件、循环等编程结构，用于编写存储过程和触发器等数据库对象。
PL/Python：允许在数据库中嵌入 Python 代码。
PL/Perl：允许在数据库中嵌入 Perl 代码。
PL/Tcl：允许在数据库中嵌入 Tcl 代码。

通过支持这些语言，PostgreSQL 提供了灵活的开发和扩展能力。

3.1.4 PostgreSQL数据库除了支持SQL固有数据类型外，还支持哪些数据类型？

除了 SQL 标准中的数据类型外，PostgreSQL 还支持数组、JSON 和 JSONB、范围、几何类型、网络地址类型、UUID、XML、枚举等扩展数据类型。

3.2 图书借阅管理系统数据库表设计如下：

READER(PerID,Name,Age,TelPhone)
BOOK(ISBN,Title,Author,Publisher)
LOAN(PerID,ISBN,Date,Type,Note)

针对LOAN表如何定义代理键？如何定义参照完整性约束？
给出创建LOAN表的SQL语句。
如何在这些表中创建索引？创建更多索引可以进一步提高访问性能吗？
在使用索引后，它对数据库中查询、修改、删除数据分别有什么影响？

CREATE TABLE LOAN(
    LOANID serial  NOT NULL,
    PerID char(13) NOT NULL,
    ISBN  char(13) NOT NULL,
    Date  DATE	   NOT NULL,
    Type  varchar(10) NOT NULL,
    Note  TEXT	   NOT NULL,
    CONSTRAINT LOANID_PK PRIMARY KEY(LOANID),
    CONSTRAINT PerID_FK FOREIGN KEY(PerID)
    	REFERENCES READER(PerID)
    	ON DELETE CASCADE,
    CONSTRAINT ISBN_FK FOREIGN KEY(ISBN)
    	REFERENCES BOOK(ISBN)
    	ON DELETE CASCADE
);

如上

CREATE INDEX idx_loan_date ON LOAN(Date);
CREATE INDEX idx_loan_type ON LOAN(Type);
-- 由于 PerID 和 ISBN 列都是外键，通常会自动创建索引以提高连接性能

创建更多索引可以加快相关查询的速度，但也会增加数据库的维护成本和存储开销。

索引对数据库操作的影响
1. 对查询的影响: 正面影响：索引可以显著提高查询性能，特别是在搜索、过滤和排序操作中。使用索引可以快速定位数据，而不是进行全表扫描。
2. 对插入的影响: 负面影响：插入操作需要同时更新相关索引，因此会增加一些开销。插入新的行时，必须将新数据插入到表中并更新所有相关索引。
3. 对更新的影响: 负面影响：更新涉及到索引列时，数据库需要更新相关索引，这会增加更新操作的开销。更新非索引列的操作影响较小。
4. 对删除的影响: 负面影响：删除操作也需要更新索引。因此，删除操作会因为索引的维护而稍微变慢，尤其是当删除的行影响多个索引时。

3.3.1 在对多个关联表（eg. College、Teacher）进行数据插入时，哪个表须先插入数据？在对Teacher表插入数据时，须遵循什么约束？

在对多个关联表进行数据插入时，必须先插入父表的数据，再插入子表的数据。此处先插入College表，因为Teacher表有外键指向College表。
- 必须确保引用的 College 表的主键已经存在，否则外键约束会导致插入失败。
- 插入的数据必须符合 Teacher 表的所有字段约束（如数据类型、非空约束等）。

3.3.2 批量插入元组数据SQL语句与逐次多条插入数据SQL语句相比，其处理效率为什么更高？

减少网络通信开销：批量插入只需要一次网络通信，而逐条插入需要多次网络通信，这会导致较大的开销。
事务管理开销减少：批量插入通常可以在一个事务中完成，而逐条插入每次可能需要单独管理事务，增加了事务的开销。
优化器效率更高：数据库优化器可以对批量插入进行更好的优化，例如更有效地使用内存和索引。
日志记录开销减少：批量插入可以减少日志记录的次数和大小，提高写入效率。

3.3.3 PUBLISHER(PubID,Name,Addr,TelPhone) BOOK(ISBN,Title,Author,PubID)

在对出版社信息进行数据修改时，如何同步修改关联表数据？
举例说明在删除“xx出版社”信息数据时，如何同步删除关联表数据？

Ans：

-- 法一：同步修改
UPDATE PUBLISHER SET PubID = 2 WHERE PubID = 1;
UPDATE BOOK 	 SET PubID = 2 WHERE PubID = 1;
-- 法二：添加外键约束 (这个答案更靠谱)
ALTER TABLE BOOK
ADD CONSTRAINT fk_pubid
FOREIGN KEY (PubID)
REFERENCES PUBLISHER(PubID)
ON UPDATE CASCADE;

-- 法一同上，同步修改
-- 法二:
ON DELETE CASCADE;

3.4.1 在SQL查询语句中，什么情况下使用内置函数？

数据转换和格式化：如 CAST() 和 CONVERT() 用于数据类型转换， TO_CHAR() 用于日期和数字格式化。
聚合操作：如 SUM(), AVG(), COUNT(), MAX(), MIN() 用于计算聚合值。
字符串操作：如 CONCAT(), SUBSTRING(), UPPER(), LOWER() 用于字符串操作。
日期和时间处理：如 CURRENT_DATE(), DATEADD(), DATEDIFF() 用于处理日期和时间。
数学运算：如 ABS(), ROUND(), CEIL(), FLOOR() 用于数学计算。
条件判断：如 CASE, COALESCE(), NULLIF() 用于条件判断和处理。

对于数据：转换、数学运算
操作：聚合、字符串、时间日期
判断：条件

3.4.2 在SQL查询语句中，如何对查询结果实现分组统计？

使用 GROUP BY 语句

3.4.3 在什么情况下，使用SQL嵌套子查询？什么情况下，使用SQL多表关联查询？

SQL嵌套子查询：
1. 需要按步骤处理数据：如在一个查询中先过滤数据，再在外层查询中进行进一步处理。
2. 需要从子查询中派生出中间结果：如在外层查询中使用子查询的结果进行计算。
3. 当外层查询依赖子查询结果：如使用子查询的结果作为过滤条件。
4. ```
-- 示例
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);
```
SQL多表关联查询：
1. 需要从多个表中检索相关数据：如需要同时从两个或多个表中获取数据。
2. 需要将不同表的相关数据结合起来：如连接多个表以生成完整的数据视图。
3. 当涉及到表间关系时：如主外键关系、需要跨表聚合等。
4. ```
-- 示例
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
```

3.4.4 针对如下SQL查询语句，给出更高效的SQL查询语句：

SELECT customer_id, COUNT(customer_id)
FROM Sales
GROUP BY customer_id
HAVING customer_id !='16' AND customer_id !='2';  -- HAVING 针对目标列, 用于 GROUP BY后

更高效的语句：

SELECT customer_id, COUNT(customer_id)
FROM Sales
WHERE customer_id != '16' AND customer_id != '2'  -- WHERE  针对表, 用于 FROM 后, 且通常情况下WHERE效率更高
GROUP BY customer_id;

优化后将 HAVING 子句替换为 WHERE 子句，减少了分组后过滤的计算量，提高了查询效率。

EX-3.4 在SQL数据访问编程中，有哪些实现高效查询的SQL语句优化方案？

使用适当的索引：为查询频繁使用的列创建索引，如主键、外键和常用的过滤条件列。
**避免在列上使用函数 **：在WHERE子句中对列使用函数会使索引失效，导致全表扫描。（所以最好先预处理）
减少JOIN操作：尽量减少JOIN的数量和复杂度。
避免全表扫描（SELECT *）：通过索引、适当的查询条件和限制返回行数来减少全表扫描。
选择合适的连接类型：根据需求选择最合适的连接类型（如 INNER JOIN, LEFT JOIN），避免不必要的全连接。
使用EXPLAIN分析查询：利用 EXPLAIN 命令分析查询计划，识别和优化性能瓶颈。
避免子查询中的多次扫描：将子查询结果缓存或使用 JOIN 替代多次子查询。
限制结果集：使用 LIMIT 或 TOP 子句来限制返回的行数，避免返回过多无关数据。
使用聚合和分组：合理使用聚合函数和 GROUP BY 子句，减少返回的数据量。
简化查询逻辑：合并相似的查询条件和逻辑，减少不必要的复杂操作。
优化数据类型：选择合适的数据类型，避免使用过大的数据类型（如 TEXT 和 BLOB）。
批量操作：对于大批量数据操作，使用批量插入、更新或删除语句，减少单次操作的开销。

索引
预处理
减少不必要的查询、减少不必要的操作（选择最合适的连接类型）、避免多次子查询、优化数据类型
聚合（SUM、COUNT、MAX等）和分组GROUP BY
批量操作

3.5.1 在什么情况下，可以在视图中插入 or 修改数据？

简单视图：视图基于单个表，不包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION、子查询等复杂操作。
视图中的列：所有的视图列直接映射到基础表中的列，没有计算列或表达式列。
视图有合适的权限：用户必须有对基础表进行插入或更新的权限。
可更新视图：视图必须允许插入或修改操作（某些数据库系统有特定的配置或语法支持视图的可更新性）。
不包含只读视图：视图本身没有被定义为只读

视图不包含聚合、子查询等操作
用户有权限、视图非只读且可更新

3.5.2 如何使用视图对象提高数据访问安全性？

限制列访问：通过视图，只暴露基础表的部分列，隐藏敏感信息。
行级安全：视图可以定义特定的 WHERE 子句，只返回用户有权限访问的行。
简化权限管理：将用户的权限设置在视图上，而不是直接在基础表上，集中控制访问逻辑。
数据抽象：提供一个抽象层，防止用户直接访问和修改基础表，保护数据的完整性。

限制可访问的行、列，有限权限
防止直接访问和修改基础表 $ \rarr$ 数据抽象

3.5.3 如何提高复杂视图的数据查询访问性能？

索引优化：在基础表上创建适当的索引，支持视图查询的高效执行。
物化视图：对性能要求高的复杂视图，可以使用物化视图，存储预计算的结果，提高查询速度。
查询重写：优化视图定义中的查询，简化复杂的嵌套和联接。
分区表：如果视图涉及大量数据，可以在基础表上使用分区技术，加快查询速度。
数据库缓存：利用数据库缓存机制，减少对视图的重复计算。

优化查询，使用索引、分布技术
预计算、缓存

3.5.4 为什么说数据库DBA遵从职业操守比实施数据库安全措施更重要？

内部威胁防范：DBA拥有对数据库系统的广泛访问权限，若其不遵守职业操守，可能会带来比外部攻击更大的威胁。
数据完整性和保密性：DBA负责管理和维护数据库的安全措施，其职业操守直接影响数据的完整性和保密性。
信任和责任：职业操守保证DBA在管理数据库时能够严格遵循安全和隐私政策，避免滥用权限。
应对紧急情况：在安全措施失效或紧急情况下，DBA的职业操守确保他们采取正确的行动，保护数据安全。

内部安全威胁更大，避免滥用权限

数据完整和保密

紧急情况下应急措施保护数据安全

学堂在线补充

3.2 对比分析PostgreSQL、openGuass如何执行SQL语句?

执行阶段	PostgreSQL	openGauss
解析 (Parsing)	语法树生成、语法检查、词法分析	同
查询重写 (Rewriting)	视图和规则系统重写查询，生成逻辑查询树	同
查询优化 (Planning/Optimization)	成本模型评估执行计划，选择最优计划	扩展优化器，更多高级优化策略和并行查询优化
执行 (Execution)	执行计划操作数据，处理表扫描、索引扫描等	执行计划操作数据，支持更多执行模式和分布式查询
主要区别	成熟优化器，单机性能强，适合中小规模应用	扩展并行处理和分布式能力，更适合大规模企业应用

3.3 truncate语句和delete语句执行数据操作有哪些区别？它们分别适合哪类场景？

区别：

特点	TRUNCATE	DELETE
类型	DDL（数据定义语言）	DML（数据操作语言）
速度	快速，重置表	逐行操作，相对慢
返回值	无返回值	返回删除的行数
触发器	不触发触发器	触发 `DELETE` 触发器
锁机制	表级锁	行级锁
事务回滚	难以回滚	可以回滚
适用场景	快速清空表，无需触发器	条件删除，需要触发器，精确控制删除操作的场景

TRUNCATE：表级、重置表、无返回值、不触发触发器、难以回滚、DDL 快、但是不删除级联！
DELETE：行级、逐行删、返回删除的行数、触发DELETE触发器、可回滚、DML 慢，但是能删除级联！

适用场景：

TRUNCATE：清空大表，重置表用于批量删除且无需触发器的场景，如重置测试环境的数据表。
DELETE：需要条件删除或激活触发器，精确控制删除操作的场景，如删除过期记录。

3.4 分析说明DDL语句和DQL语句在DBMS系统上执行有何不同？

特点	DDL 语句	DQL 语句
类型	数据定义语言（如 `CREATE`, `ALTER`, `DROP`,`TRUNCATE`）	数据查询语言（如 `SELECT`,`DROP`）
锁机制	表级锁或元数据锁（类似互斥锁）	读锁（共享锁），不锁定写操作
事务处理	自动提交，难以回滚	事务控制，支持 `COMMIT` 和 `ROLLBACK`
影响范围	影响数据库结构，可能导致重建和数据迁移	仅影响查询结果，不改变数据库结构