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 列都是外键,通常会自动创建索引以提高连接性能
创建更多索引可以加快相关查询的速度,但也会增加数据库的维护成本和存储开销。
-
索引对数据库操作的影响
- 对查询的影响: 正面影响:索引可以显著提高查询性能,特别是在搜索、过滤和排序操作中。使用索引可以快速定位数据,而不是进行全表扫描。
- 对插入的影响: 负面影响:插入操作需要同时更新相关索引,因此会增加一些开销。插入新的行时,必须将新数据插入到表中并更新所有相关索引。
- 对更新的影响: 负面影响:更新涉及到索引列时,数据库需要更新相关索引,这会增加更新操作的开销。更新非索引列的操作影响较小。
- 对删除的影响: 负面影响:删除操作也需要更新索引。因此,删除操作会因为索引的维护而稍微变慢,尤其是当删除的行影响多个索引时。
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嵌套子查询:
-
需要按步骤处理数据:如在一个查询中先过滤数据,再在外层查询中进行进一步处理。
-
需要从子查询中派生出中间结果:如在外层查询中使用子查询的结果进行计算。
-
当外层查询依赖子查询结果:如使用子查询的结果作为过滤条件。
-
-- 示例 SELECT employee_id, employee_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location = 'New York' );
-
-
SQL多表关联查询:
-
需要从多个表中检索相关数据:如需要 同时 从两个或多个表中获取数据。
-
需要将不同表的相关数据结合起来:如连接多个表以生成完整的数据视图。
-
当涉及到表间关系时:如主外键关系、需要跨表聚合等。
-
-- 示例 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 |
影响范围 | 影响数据库结构,可能导致重建和数据迁移 | 仅影响查询结果,不改变数据库结构 |
- 类型
- 操作互斥与否
- 回滚
- 影响范围