7-29 DQL，多表设计

1、DQL是什么？

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

查询关键字: SELECT

2、查询语句的完整格式是怎样的？

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

3、条件查询的关键字是什么，常见的运算符有哪些？

查询关键字: where ，语法(select 字段列表 from 表名 where 条件列表 ;)

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN ... AND ...	在某个范围之内(含最小、最大值)
IN(...)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且 (多个条件同时成立)
OR 或 ||	或者 (多个条件任意一个成立)
NOT 或 !	非 , 不是

 

4、如何实现模糊查询？

关键字: like

（select * from 字段列表 where 表名 like '__' );

5、怎么实现排序查询？

关键字: order by

语法（select 字段列表 from 表名 order by 字段1 排序方式1 , 字段2 排序方式2 ;）

• ASC : 升序(默认值)

• DESC: 降序

如果是升序, 可以不指定排序方式ASC ;

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

6、怎么实现分页查询？

关键字: limit

语法：select 字段列表 from 表名 limit 起始索引, 查询记录数 ;

7、分页查询中怎么计算某一页开始索引？

（页码-1）*查询记录数；

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

8、怎么实现分组查询，分组查询的目的是什么？

语法：select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];

目的：对数据库中的数据进行聚合、分类、筛选和统计，以满足各种数据分析和报表生成的需求。（方便操作数据）

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

• 判断条件不同：where不能对聚合函数进行判断，而having可以。

  分组查询注意点：**

  分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

  • 执行顺序: where > 聚合函数 > having 。

  • 支持多字段分组, 具体语法为 : group by columnA, columnB

9、聚合函数有哪些？

聚合函数：将一列数据作为一个整体，进行纵向计算 。

语法：select 聚合函数(字段列表) from 表名 ;

常见的聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

10、怎么实现给查询的值设定新的数据：比例 1变成男，2变成女

写法一 ：if（gender='1' , '男'，'女'）

写法二 ：case gender

when 1 then '男'

when 2 then '女'

end

11、表和表之间有哪些关系？

三种关系：一对一 ，一对多，多对多；

12、不同关系怎么设计外键

外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。 对应的关键字：foreign key

一对多：在多的一方添加外键，关联另外一方的主键。

一对一：任意一方，添加外键，关联另外一方的主键。

多对多：通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键。

13、什么是物理外键，什么是逻辑外键

1). 物理外键

介绍：使用 foreign key 定义外键关联另外一张表

缺点：

A. 影响增、删、改的效率（需要检查外键关系）。

            B. 仅用于单节点数据库，不适用与分布式、集群场景。
​
            C. 容易引发数据库的死锁问题，消耗性能。

2). 逻辑外键

介绍：在业务层逻辑中，解决外键关联。

    特点：通过逻辑外键，就可以很方便的解决上述问题。

14.多表设计的思路和流程

多表设计是在数据库中创建多个表，并使用适当的关系来建立它们之间的连接。下面是多表设计的一般思路和流程：

1. 分析需求：首先，你需要仔细分析你的系统或应用程序的需求，明确需要存储和管理的数据以及它们之间的关系。这将有助于确定需要创建的表以及它们之间的联系。

2. 标识实体和关系：在分析需求的基础上，识别系统中的实体（例如"用户"、"订单"、"产品"等）以及它们之间的关系（例如"一对多"、"多对多"等）。这将帮助你确定需要创建的表以及表之间的关系。

3. 设计表结构：根据实体和关系，设计每个表的结构。确定每个表的名称、字段和数据类型。确保每个字段的含义清晰，并遵循数据库设计的最佳实践，例如避免冗余数据和保持数据的一致性。

4. 确定主键和外键：在设计表结构时，确定每个表的主键，用于唯一标识每个记录。如果有需要，确定外键，用于建立表之间的关联。外键表示另一个表中的主键，用于创建表之间的关系。

5. 设计索引：根据查询需求和性能优化的考虑，设计适当的索引。索引可以加快查询速度，并提高查询性能。确定需要在哪些列上创建索引，并选择适当的索引类型（例如唯一索引、聚簇索引等）。

6. 建立表之间的关系：根据实体和关系，使用主键和外键建立表之间的关系。这可以通过在相关列上定义外键约束来实现。确保定义适当的级联操作，确保数据的完整性和一致性。

7. 规范化和性能优化：在设计过程中，要注意规范化数据库结构，以消除冗余数据并提高数据存储效率。考虑性能优化，例如使用合适的数据类型、适当的索引和编写高效的查询语句。

8. 创建表并填充数据：基于设计好的表结构，使用数据库管理工具（例如MySQL、Oracle等）创建表，并根据需求插入适当的数据。

9. 测试和维护：在创建表和填充数据后，进行测试以确保表和关系的正确性。进行必要的调整和更改，并确保及时更新数据。定期进行数据库维护，例如备份和优化。

总的来说，多表设计的思路和流程包括需求分析、实体和关系的识别、表结构的设计、主键和外键的确定、索引设计、建立表之间的关系、规范化和性能优化、表的创建和数据填充、测试和维护。这些步骤将帮助你创建一个有效、可扩展和易于维护的数据库结构。

15.物理外键为什么容易引发数据库的死锁问题

物理外键（Physical Foreign Key）是指在数据库中使用物理级别的外键约束来确保表之间的引用完整性。在物理外键的实现中，数据库会对外键关系进行加锁操作，从而保证数据的一致性。然而，物理外键也可能引发数据库的死锁问题，原因如下：

1. 外键检查顺序：当涉及到多个表之间的外键引用时，数据库系统需要按照约束的顺序检查和验证外键的完整性。如果两个或多个事务同时操作多个表，并且这些表之间存在复杂的外键关系，可能会导致事务之间形成死锁。

2. 锁竞争：在并发环境中，多个事务可能同时访问和修改涉及外键的表。当多个事务同时请求获取对涉及的表或行的锁时，可能会发生锁竞争。如果多个事务之间的操作顺序不当，并且涉及到物理外键的锁冲突，可能导致死锁的发生。

3. 事务操作顺序：在数据库系统中，事务按照一定的顺序执行操作。如果多个事务在相同的操作序列中以不同的顺序执行，并且涉及到外键的更新操作，可能会导致死锁的产生。

为了避免物理外键引发死锁问题，可以考虑以下几点：

1. 优化事务操作顺序：合理设计事务的操作顺序，避免同时操作多个表，并尽量按照约束的顺序进行操作，以减少死锁的发生机会。

2. 合理配置锁级别：根据实际情况选择合适的锁级别。例如，使用行级锁而不是表级锁，可以减少锁冲突的可能性。

3. 限制事务的范围：尽量减小事务的范围，确保事务尽早执行并提交，从而减少锁定资源的时间。

4. 定期监测和优化：定期监测数据库性能并进行优化，包括检查死锁情况、优化查询和索引等。

总的来说，物理外键在数据库中确保数据的完整性，但也可能引发死锁问题。通过优化事务操作顺序、配置合适的锁级别、限制事务范围以及定期监测和优化，可以减少死锁发生的可能性。