数据库基础

关系型数据库完整性规则有哪些？

数据库完整性（Database Integrity）是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。数据库完整性由各种各样的完整性约束来保证，关系型数据库提供了三类完整性规则，分别是：

• 实体完整性规则：主码的值不能为空或部分为空。
• 参照完整性规则：如果关系R1的外键和关系R2的主键相符，那么外键的每个值必须在关系R2的主键的值中可以找到或者是空值。
• 用户自定义完整性规则：用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如，属性值根据实际需要，要具备一些约束条件，如规定选课关系中成绩属性的取值范围在0和100之间。

实体完整性规则和参照完整性规则是关系模型必须满足的完整性的约束条件，称为关系完整性规则，它们适用于任何关系型数据库系统。

数据库约束有哪几种？

在数据库表的开发中，使用约束可以更好地保证数据库中数据的完整性。可以在定义表时定义完整性约束，也可以通过索引或触发器等方式定义完整性约束。约束分为两类：列级约束和表级约束，二者处理机制是一样的。行级约束放在列后，表级约束放在表后，多个列共用的约束放在表后。完整性约束是一种规则，不占用任何数据库空间。它存在数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束是启用还是禁用，当约束启用时，它增强了数据的完整性，否则，约束始终存在于数据字典中。

• 主键约束（Primary Key）主键是一个唯一的标识，本身不能为空，即主键必须非空且唯一。例如：身份证编号是唯一的，不可重复，不可为空。
• 唯一约束（UNIQUE）在一个表中，只允许建立一个主键约束，而对于其他列，如果不希望出现重复值的话，那么可以使用唯一约束。
• 检查约束（CHECK）检查一列的内容是否合法。例如性别，只能是男或女，年龄大小不能小于等于 0。
• 非空约束（NOT NULL）用于控制字段的内容一定不能为空（NULL）。
• 外键约束（Foreign Key）在两张表中进行约束操作。主键是一个非空且唯一的约束，外键是在两张表中进行约束，外键的取值必须是参照的主键值或空值。

数据库有哪些范式是什么？

数据库的范式（Normal Forms）是关系数据库理论中的基础概念，用于指导数据库表的设计，以减少数据冗余和提高数据完整性。目前关系数据库有六种范式：

1. 第一范式（1NF）

   • 定义：要求数据库表的每一列都是不可分割的原子数据项，即每一列都是单一的数据类型，而不是集合、数组、记录等非原子数据项。

   • 作用：确保表中数据的原子性，避免数据的重复存储。

2. 第二范式（2NF）

   • 定义：在满足第一范式的基础上，第二范式要求数据库表中的每个非主属性都完全依赖于主键，而不是依赖于主键的一部分。

   • 作用：消除部分函数依赖，减少数据冗余。

3. 第三范式（3NF）

   • 定义：在满足第二范式的基础上，第三范式要求一个关系中不包含已在其他关系已包含的非主键信息，即消除非主属性对主键的传递函数依赖。

   • 作用：进一步减少数据冗余，提高数据独立性。

4. 巴斯-科德范式（BCNF）

   • 定义：巴斯-科德范式是对第三范式的进一步扩展，它要求所有非主属性对每一个候选键都是完全函数依赖，并且没有任何属性完全函数依赖于非候选键的任何一组属性。

   • 作用：进一步减少数据冗余，提高数据的一致性。

5. 第四范式（4NF）

   • 定义：第四范式主要处理多值依赖问题，它要求关系中的非平凡多值依赖必须是不可约的，即不能分解为更低级的多值依赖。

   • 作用：减少数据冗余，提高数据处理的效率。

6. 第五范式（5NF）

   • 定义：第五范式也称为完美范式或投影-连接范式，它要求关系必须满足连接依赖的保持性和投影依赖的保持性。

   • 作用：进一步减少数据冗余，提高数据的一致性和可维护性。

数据库的范式从第一范式到第五范式，是一个逐步减少数据冗余、提高数据一致性和独立性的过程。一般来说，数据库设计只需满足第三范式即可满足大部分需求。然而，在特定情况下，为了进一步提高数据处理的效率和减少数据冗余，可以考虑采用更高级别的范式。

事务有什么特性？

事务是一种机制，是一段程序，是一系列的数据库操作构成的集合，在逻辑上是一个不可分割的工作单元。事务具有四个核心特性，这些特性通常被简称为ACID。

1. 原子性（Atomicity）

   • 定义：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。

   • 解释：如果事务在执行过程中遇到错误或者因为某些原因需要回滚，那么已经执行的所有操作都必须被撤销，使数据库回到事务开始前的状态。

2. 一致性（Consistency）

   • 定义：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

   • 解释：事务执行前后，数据库中的所有数据都必须满足所有的完整性约束，包括实体完整性、参照完整性和用户定义的完整性等。

3. 隔离性（Isolation）

   • 定义：一个事务的执行不能被其他事务所干扰，即一个事务内部的操作及使用的数据对其他并发事务是隔离的。

   • 解释：隔离性要求事务在并发执行时，各个事务之间是相互隔离的，一个事务的执行不会影响到其他事务的执行结果。事务的隔离级别决定了事务之间的可见性和干扰程度。

4. 持久性（Durability）

   • 定义：一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。

   • 解释：持久性要求事务一旦提交，即使系统发生故障也不会丢失。这通常是通过将事务的修改记录到持久化存储设备上来实现的，如硬盘。

总的来说，事务的ACID特性是数据库管理系统（DBMS）提供的一种机制，用于确保数据库操作的正确性和可靠性。这些特性共同作用于事务的执行过程中，保证了数据的一致性和完整性。

事务的常见分类有哪些？

一、按启动与执行方式分类

1. 显示事务（用户定义或指定的事务）：

   • 这种事务可以由用户显式地定义启动和结束。例如，在存储过程代码中写BEGIN TRANSACTION，或者在C#、Java等编程语言中调用API来开启一个事务。在事务无误的情况下执行COMMIT提交，有错误时执行ROLLBACK回滚。

2. 自动提交事务：

   • 这是数据库引擎的默认事务管理模式。在这种模式下，每一个数据库操作（如一条SQL语句）都被视为一个独立的事务。如果操作成功完成，则自动提交；如果遇到错误，则自动回滚。

   • 特点：简单、方便，但缺乏灵活性。

3. 隐性事务：

   • 当连接以隐性事务模式进行操作时，SQL将在提交或回滚当前事务后自动启动新事务。这种模式下，用户无需显式地描述事务的开始，只需在每个事务结束时提交或回滚。

   • 设置方式：使用Transact-SQL的SET IMPLICIT_TRANSACTIONS ON命令或相应的API设置。

   • 特点：生成连续的事务链，但需要用户手动控制事务的提交或回滚。

二、按事务结构分类

可以把事务分为以下几种类型：扁平事务（Flat Transactions）,带有保存点的扁平事务（Flat Transactions with Save points）, 链事务（Chained Transactions）,嵌套事务（Nested Transactions）, 分布式事务（Distributed Transactions）。

• 扁平事务

  • 事务类型中最简单的一种，但是在实际生产环境中，这可能是使用最频繁的事务，在扁平事务中，所有操作都处于同一层次，其由BEGIN开始，由COMMIT或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚，因此，扁平事务是应用程序成为原子操作的基本组成模块。因为其简单，使用频繁，故每个数据库系统都实现了对扁平事务的支持。扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。 扁平事务一般有三种不同的结果：
    1. 事务成功完成；
    2. 应用程序要求停止事务。比如应用程序在捕获到异常时会回滚事务；
    3. 外界因素强制终止事务。如连接超时或连接断开。

• 带有保存点的扁平事务

  • 除了支持扁平事务支持的操作外，还允许在事务执行过程中回滚到同一事务中较早的一个状态（保存点）。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销太大。带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。

• 链事务

  • 是指一个事务由多个子事务链式组成，它可以被视为保存点模式的一个变种。链事务允许一个事务的提交触发另一个事务的开始。这种类型的事务通常用于需要按顺序执行的一系列操作，其中每个操作都依赖于前一个操作的结果。链事务与带有保存点的扁平事务的不同之处体现在：
    1. 带有保存点的扁平事务能回滚到任意正确的保存点，而链事务中的回滚仅限于当前事务，即只能恢复到最近的一个保存点。
    2. 对于锁的处理，两者也不相同，链事务在执行COMMIT后即释放了当前所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。

• 嵌套事务

  • 嵌套事务是指一个事务可以包含其他事务，形成嵌套的层次结构，嵌套事务的层次结构可以看成是一棵树。。内部事务可以独立于外部事务提交或回滚，但它们的提交或回滚可能会受到外部事务的影响。

  • 特点：支持复杂的事务处理逻辑，可以实现事务间的并行执行和独立控制。

• 分布式事务

  • 分布式事务涉及多个独立的数据库或系统之间的事务处理。这种事务类型需要确保事务的原子性、一致性、隔离性和持久性在不同系统之间得到保证。

  • 特点：处理复杂，需要特殊的机制（如两阶段提交协议或三阶段提交协议）来确保事务的一致性和原子性。

并发事务带来哪些问题?

并发事务是指同时运行的多个事务，在数据库系统中，这种并发性虽然提高了系统资源的利用率和效率，但也带来了一系列问题。

1. 脏读（Dirty Read）

   • 定义：当一个事务读取到了另一个事务尚未提交的数据，而这些数据在读取后被第二个事务修改（回滚或提交），那么第一个事务看到的就是不一致的数据，这种情况被称为脏读。

   • 影响：脏读会导致事务读取到不正确的数据，影响数据的准确性和一致性。

2. 不可重复读（Nonrepeatable Read）

   • 定义：如果一个事务在读取某数据后，其他事务对该数据进行了修改（并提交了修改），导致第一个事务无法重复读取到之前的版本，这被称为不可重复读。

   • 影响：不可重复读破坏了事务的隔离性，使得在同一个事务内多次读取同一数据的结果不一致。

3. 幻读（Phantom Read）

   • 定义：当一个事务对多个表进行操作时，如果其他事务插入了新的数据，导致第二次查询时需要读取的数据页已经被其他事务更新，需要重新从磁盘读取，或者查询结果集中出现了新的记录，这就是幻读。

   • 影响：幻读同样破坏了事务的隔离性，使得在同一个事务内执行两次相同的查询语句得到的结果集不同。

4. 丢失修改（Lost Update）

   • 定义：当两个或多个事务同时修改同一行数据时，最后提交的事务会覆盖之前提交的事务所做的修改，从而导致其中一个事务的修改丢失。

   • 影响：丢失修改会导致数据更新不准确，影响数据的完整性和一致性。

5. 锁竞争（Lock Contention）

   • 定义：当多个并发事务同时对数据库进行操作时，需要占用一定的锁资源，这可能导致锁竞争激烈，影响数据库的性能。

   • 影响：锁竞争会增加事务的等待时间，降低数据库的并发处理能力。

6. 死锁（Deadlock）

   • 定义：当两个或多个事务相互等待对方释放资源时，可能导致死锁的发生，使得事务无法继续执行。

   • 影响：死锁会严重影响数据库的性能和可用性，需要数据库管理系统进行死锁检测和解除。

解决方案

• 使用锁机制：包括悲观锁和乐观锁。悲观锁在读取数据之前先加锁，确保数据在事务期间不会被其他事务修改；乐观锁则在数据更新时检查是否与其他事务发生冲突。
• 设置事务隔离级别：通过调整事务的隔离级别来减少并发事务之间的干扰。常见的隔离级别包括读未提交、读已提交、可重复读和串行化。
• 使用数据库的一致性视图：确保每个事务看到的数据都是一致的，未提交的数据对其他事务不可见。
• 分布式锁：在分布式系统中，使用分布式锁来协调多个节点上的事务操作。

并发事务虽然提高了系统资源的利用率和效率，但也带来了脏读、不可重复读、幻读、丢失修改、锁竞争和死锁等问题。为了解决这些问题，需要采用合适的并发控制策略和技术手段。

事务的隔离级别有哪些？

事务的隔离级别是数据库管理系统（DBMS）提供的一种机制，用于定义事务之间的可见性和相互影响程度。它们用于确保事务的原子性、一致性、隔离性和持久性（ACID属性中的I）。不同的隔离级别提供了不同程度的数据一致性和保护，但也可能影响到系统的并发性能和事务的吞吐量。SQL标准定义了四个隔离级别，从低到高依次是：

1. READ UNCOMMITTED（读未提交）：

   • 这是最低的隔离级别。

   • 一个事务可以读取到另一个事务未提交的数据（脏读）。

   • 这可能导致不可重复读和幻读问题。

   • 在这个级别下，事务之间几乎没有隔离。

2. READ COMMITTED（读已提交）：

   • 一个事务只能读取到另一个事务已经提交的数据。

   • 这解决了脏读的问题，但是仍然可能遇到不可重复读和幻读。

   • 大多数数据库系统（如Oracle, SQL Server）的默认隔离级别是READ COMMITTED。

3. REPEATABLE READ（可重复读）：

   • 确保在同一个事务内多次读取同一数据的结果是一致的。

   • 这解决了脏读和不可重复读的问题，但是仍然可能遇到幻读（即在同一事务中，两次相同的查询可能会返回不同数量的行）。

   • MySQL的默认隔离级别就是REPEATABLE READ（但在某些存储引擎如InnoDB中，通过多版本并发控制MVCC和间隙锁，实际上已经解决了幻读问题）。

4. SERIALIZABLE（可串行化）：

   • 这是最高的隔离级别。

   • 它通过强制事务串行执行，避免了脏读、不可重复读和幻读。

   • 在这种级别下，事务之间完全隔离，但是会严重影响系统的并发性能。

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定。

• Oracle数据库支持Read Committed（提交读）和Serializable（可串行化）这两种事务隔离级别，提交读是Oracle数据库默认的事务隔离级别，Oracle不支持脏读，在READ COMMITTED级别就已经解决了幻读问题。SYS用户不支持Serializable（可串行化）隔离级别。
• MySQL数据库支持全部4种事务隔离级别，Repeatable Read（可重复读）是MySQL数据库的默认隔离级别。MySQL 的 InnoDB 存储引擎通过多版本并发控制MVCC 解决不可重复读的问题。而加上间隙锁解决了幻读问题。

存储过程有什么优缺点？

优点

1. 提高性能：

   • 减少网络流量：由于存储过程在数据库服务器上执行，因此它们可以直接访问数据库中的数据，而不需要将大量数据发送到客户端应用程序。这减少了在网络上传输的数据量，从而提高了性能。

   • 减少编译次数：存储过程在数据库服务器上被编译并存储为可执行代码，因此当它们被多次调用时，不需要每次都重新编译，这进一步提高了执行速度。

   • 优化执行计划：数据库管理系统（DBMS）可以为存储过程生成优化的执行计划，并在存储过程被调用时重复使用这些计划，这有助于加快查询处理速度。

2. 增强安全性：

   • 权限控制：通过授予用户执行存储过程的权限，而不是直接访问表或视图，数据库管理员可以更精细地控制用户对数据的访问。

   • 减少SQL注入风险：存储过程使用参数化查询，这有助于防止SQL注入攻击，因为攻击者无法直接修改存储过程内部的SQL语句。

3. 提高可维护性：

   • 集中管理：存储过程将业务逻辑封装在数据库中，使得代码更加集中和易于管理。当业务逻辑发生变化时，只需修改存储过程即可，而无需更改多个应用程序中的代码。

   • 文档化：存储过程可以被文档化，这使得其他开发人员或数据库管理员更容易理解数据库的架构和业务逻辑。

4. 促进复用性：

   • 模块化：存储过程可以被视为数据库中的模块，它们可以被多个应用程序或用户重复使用。这有助于减少代码冗余，并促进代码的重用。

   • 跨平台兼容性：存储过程是与数据库系统紧密相关的，因此它们可以在不同的客户端应用程序之间共享，而无需担心跨平台兼容性问题。

5. 支持复杂操作：

   • 存储过程支持复杂的业务逻辑和事务处理，可以在单个过程中执行多个SQL语句，并维护这些语句之间的事务一致性。

6. 自动优化：

   • 某些数据库系统能够自动优化存储过程的执行计划，以适应数据分布和查询模式的变化。这有助于确保存储过程始终能够高效地执行。

缺点 ：

1. 调试困难：

   • 存储过程的调试相对于应用程序代码来说可能更加困难。因为存储过程是在数据库服务器上执行的，所以开发者可能需要使用特定的数据库调试工具，而这些工具可能不如应用程序开发环境中的调试工具那样直观和强大。

   • 调试问题可能还涉及数据库内部的复杂性和性能优化问题，这进一步增加了调试的难度。

2. 移植性问题：

   • 存储过程是与特定数据库系统紧密相关的，因此它们可能不具有跨数据库系统的可移植性。如果需要将数据库迁移到另一种类型的数据库系统，那么可能需要重写存储过程，以适应新的数据库环境。

   • 这不仅增加了迁移工作的复杂性，还可能引入新的错误和性能问题。

3. 重新编译问题：

   • 虽然存储过程在首次编译后可以重用执行计划，但如果数据库结构发生变化（例如，表结构变更、索引重建等），那么可能需要重新编译存储过程以生成新的执行计划。

   • 在某些情况下，如果存储过程引用了已经发生变化的数据库对象，那么可能需要手动重新编译存储过程，以确保其能够正确执行。

4. 增加系统复杂性：

   • 过度使用存储过程可能会增加数据库系统的复杂性。因为存储过程包含了业务逻辑和数据处理逻辑，所以它们可能变得非常庞大和复杂，难以理解和维护。

   • 这可能导致数据库系统的性能下降，因为复杂的存储过程可能需要更多的内存和CPU资源来执行。

5. 维护难度：

   • 由于存储过程通常被封装在数据库中，因此它们可能难以被非数据库专业人员理解和维护。这增加了系统维护的难度和成本。

   • 此外，如果存储过程中存在错误或性能问题，那么可能需要专业的数据库管理员或开发人员来进行修复和优化。

6. 性能瓶颈：

   • 在某些情况下，存储过程可能会成为性能瓶颈。例如，如果存储过程中包含了大量的复杂逻辑和数据处理操作，那么它们可能会消耗大量的CPU和内存资源，从而影响数据库的整体性能。

存储过程与函数有什么区别与联系?

存储过程和函数在数据库系统中都是用于封装和重用SQL代码的对象，但它们之间存在一些关键的区别。

1. 返回值的数量和类型

   • 存储过程：可以返回多个值，包括输出参数、状态码、消息等。它还可以返回记录集，这对于从数据库查询并返回多行数据非常有用。存储过程的参数类型包括IN（输入）、OUT（输出）和INOUT（输入输出）。

   • 函数：通常只能返回一个值，这个值可以是标量值（如整数、浮点数、字符串等）或表对象（在某些数据库系统中支持）。函数的参数只有IN类型，用于输入数据到函数中。函数需要明确指定返回类型，并且在函数体中必须包含一个有效的RETURN语句来返回值。

2. 使用和调用方式

   • 存储过程：通常作为独立的部分来执行，通过调用其名称并传递必要的参数来执行。存储过程可以执行复杂的业务逻辑，包括数据查询、数据修改（如INSERT、UPDATE、DELETE操作）以及控制流语句（如IF...ELSE、WHILE循环等）。

   • 函数：可以作为查询语句的一部分来调用，特别是在SELECT语句中。函数可以用于计算字段值、转换数据类型或执行其他数据处理任务。由于函数可以返回一个表对象，因此它可以在FROM子句中被用作表来查询。

3. 功能和复杂性

   • 存储过程：处理的功能相对复杂，可以执行一系列SQL语句和控制流语句。它们通常用于封装复杂的业务逻辑，以提高代码的重用性和可维护性。

   • 函数：实现的功能相对针对性强，主要用于执行特定的数据处理任务。函数的限制较多，例如不能使用临时表（在某些数据库系统中可能可以使用表变量），并且不能执行一组修改全局数据库状态的操作（如数据修改语句）。

4. 性能和优化

   • 存储过程和函数都可以被数据库系统编译和存储为可执行代码，从而加快执行速度。然而，由于存储过程可以执行更复杂的逻辑和更多的SQL语句，因此在处理大量数据和复杂业务逻辑时，存储过程可能具有更好的性能。

触发器的作用、优缺点有哪些？

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

  DELIMITER $
	CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END $
	DELIMITER ;

作用

1. 数据完整性控制：触发器可以确保数据的完整性和一致性。通过在触发器中编写复杂的条件和动作，可以对数据进行有效的监控和约束。例如，触发器可以检查插入的数据是否满足特定的约束条件，如主键、外键、唯一性等，如果不满足条件，触发器可以阻止不合法的操作或进行相应的修复。
2. 数据冗余控制：触发器可以避免数据的冗余和不一致。当某个表中的数据发生变化时，触发器可以自动更新相关的冗余数据，确保数据的一致性。例如，在订单表中，当有新订单插入时，触发器可以自动更新产品表中的库存数量。
3. 业务逻辑处理：触发器可以处理复杂的业务逻辑。通过在触发器中编写复杂的逻辑判断和操作，可以实现灵活的商业规则和数据流程。例如，在银行系统中，可以使用触发器实现自动化的转账和结算过程。
4. 安全控制：触发器可以加强数据的安全性和保护机制。通过在触发器中编写安全检查和权限控制，可以限制用户对数据库的访问和操作，防止非法的数据篡改和滥用。例如，触发器可以在敏感表的操作中进行审计和日志记录，以便追踪和监控用户的行为。
5. 性能优化：触发器可以提高数据库的性能和效率。通过在触发器中优化查询和操作的逻辑，可以减少对数据库的频繁访问和重复计算，从而实现更快的数据处理速度和更高的系统效率。例如，触发器可以在数据更新时自动更新相关的缓存，减少重复查询的次数。

触发器的触发方式主要分为三种：

• 前置触发器（Before Trigger）：在触发器监控的数据库操作之前触发。例如，当插入一条新记录之前，可以通过前置触发器对要插入的数据进行校验和修复。
• 后置触发器（After Trigger）：在触发器监控的数据库操作之后触发。例如，当更新一条记录之后，可以通过后置触发器对相关的数据进行更新和同步。
• 替代触发器（InsteadOf Trigger）：在触发器监控的数据库操作之前，替代原始操作并执行替代操作。例如，可以使用替代触发器实现视图的更新操作，从而简化复杂的数据处理流程。

优点

1. 自动化：触发器可以在特定事件发生时自动执行，无需手动干预，提高了数据处理的效率。

2. 数据一致性：通过触发器自动检查和约束数据，可以确保数据的一致性和完整性。

3. 业务逻辑简化：触发器可以处理复杂的业务逻辑，使得数据处理流程更加简洁和高效。

4. 安全性：触发器可以加强数据的安全性和保护机制，防止非法的数据篡改和滥用。

缺点

1. 可移植性差：不同数据库系统之间的触发器语法和功能可能存在差异，导致触发器在不同数据库系统之间的移植性较差。
2. 占用资源：触发器的执行会占用服务器端的资源，如果触发器过多或过于复杂，可能会影响数据库的性能。
3. 调试困难：由于触发器的执行是在特定事件发生时自动进行的，因此在调试过程中可能会遇到困难。需要借助日志和其他工具来定位问题。
4. 维护成本高：随着业务的发展和变化，可能需要不断地修改和维护触发器，增加了维护成本。

SQL 语句可以分为哪几类？

SQL语句根据其功能主要分为以下四类：

1. 数据查询语言（DQL, Data Query Language）：

   • 主要用于从数据库中的一个或多个表中查询数据。

   • 最常用的DQL语句是SELECT，用于从数据库表中检索数据。

   • DQL语句可以包含各种子句，如WHERE（条件查询）、GROUP BY（分组查询）、HAVING（分组后过滤）、ORDER BY（排序）等，以实现复杂的查询需求。

2. 数据定义语言（DDL, Data Definition Language）：

   • 用于创建、修改和删除数据库内的数据结构，如表、索引、视图等。

   • 主要的DDL语句包括CREATE（创建）、ALTER（修改）、DROP（删除）、RENAME（重命名）和TRUNCATE（截断表）等。

3. 数据操纵语言（DML, Data Manipulation Language）：

   • 用于修改数据库中的数据，包括插入、更新和删除等操作。

   • 主要的DML语句包括INSERT（插入数据）、UPDATE（更新数据）和DELETE（删除数据）。

4. 数据控制语言（DCL, Data Control Language）：

   • 用于对数据库的访问进行控制，包括给用户授予访问权限和取消用户访问权限等操作。

   • 主要的DCL语句包括GRANT（授予权限）和REVOKE（取消权限）。

5. 事务控制语言（Transaction Control Language, TCL）:

   • TCL用于管理数据库的事务，确保数据的一致性和完整性。

   • COMMIT: 提交当前事务，使其更改成为永久性的。ROLLBACK: 回滚事务，撤销在当前事务中所做的所有更改。SAVEPOINT: 创建事务内的保存点，允许在事务中部分回滚。

标准的SQL的解析顺序是怎样的？

标准SQL语句的解析顺序通常遵循一个特定的流程，尽管具体实现可能因不同的数据库管理系统（DBMS）而略有差异，但大体上SQL语句的解析和执行顺序如下：

1. 解析FROM子句:

   • SQL语句首先解析FROM子句，确定要操作的数据源，即涉及哪些表或视图。如果有多个表，还会解析表之间的连接（JOIN）关系。JOIN操作的结果是一个临时的结果集，用于后续的查询处理。

2. 解析ON/WITH/USING子句（如果存在）:

   • 如果FROM子句中包含连接操作，接下来解析连接条件，确定如何将多个表连接在一起。

3. 解析WHERE子句:

   • 接下来解析WHERE子句，这是SQL语句中的过滤条件，用于确定哪些行满足条件，可以参与后续处理。

4. 解析GROUP BY子句（如果存在）:

   • 如果SQL语句中有GROUP BY子句，这时会根据GROUP BY字段对结果集进行分组。

5. 解析聚集函数（如COUNT, SUM, AVG等）:

   • 在GROUP BY之后，如果SQL语句中包含聚集函数，将对每个分组应用相应的聚集函数，计算聚合结果。

6. 解析HAVING子句（如果存在）:

   • HAVING子句用于过滤GROUP BY子句生成的分组，只保留满足条件的分组。

7. 解析SELECT子句:

   • 解析SELECT子句，确定要返回哪些列，以及是否需要进行计算或表达式处理。如果SELECT子句中包含了DISTINCT关键字，则还会对结果集进行去重操作。

8. 解析ORDER BY子句（如果存在）:

   • 最后，如果SQL语句中有ORDER BY子句，将根据指定的列对结果集进行排序。

9. 解析LIMIT子句（如果存在）:

   • 如果有LIMIT子句，最后会根据限制条件返回指定数量的行。

需要注意的是，虽然上述步骤描述了SQL语句的大致解析和执行顺序，但实际上数据库优化器可能会对查询计划进行优化，以提高查询性能。例如，优化器可能会调整连接顺序，或者在必要时提前执行某些子句（如WHERE子句），以减少后续处理的数据量。因此，实际的执行顺序可能与上述顺序有所不同，具体取决于优化器的决策。

子查询中的IN和EXISTS有什么区别？

• IN

  • 使用场景：IN操作符允许你指定一个值列表，子查询返回的结果集需要与这个列表中的值进行匹配。如果子查询返回的结果集中包含某个值，则外层查询会包含对应的行。

  • 性能：当子查询返回的结果集很大时，IN的性能可能会受到影响，因为数据库需要遍历整个列表来检查匹配项。此外，如果子查询返回的结果集被多次使用（例如，在JOIN操作中），它可能会被多次执行，这也会影响性能。

• EXISTS

  • 使用场景：EXISTS操作符用于测试子查询是否返回至少一行数据。如果子查询返回至少一行数据，EXISTS表达式的结果为真（TRUE），并且外层查询会继续处理该行；否则，EXISTS表达式的结果为假（FALSE），外层查询会忽略该行。

  • 性能：EXISTS通常比IN（特别是当子查询返回大量数据时）有更好的性能，因为一旦EXISTS找到第一个匹配项，它就会立即停止处理子查询，并返回TRUE。这意味着EXISTS通常只需要对子查询进行一次或非常有限次的迭代，从而提高了效率。

• 总结

  • 当子查询返回的结果集较小时，IN和EXISTS在性能上的差异可能不明显。

  • 当子查询可能返回大量数据时，EXISTS通常比IN有更好的性能，因为EXISTS在找到第一个匹配项时就会停止执行子查询。

  • 使用EXISTS时，注意确保子查询的条件足够精确，以尽可能减少返回的数据量，从而提高查询效率。

  • 在某些情况下，选择IN还是EXISTS可能取决于具体的数据库管理系统（DBMS）和数据的具体特性。

• NOT EXISTS

  • 使用场景：NOT EXISTS 用于测试一个子查询是否不返回任何行。如果子查询没有返回任何行，则NOT EXISTS表达式的结果为真（TRUE），并且外层查询会包含对应的行。

  • 性能：NOT EXISTS 通常比 NOT IN 有更好的性能，特别是在处理大型数据集时。这是因为 NOT EXISTS 在找到第一个不匹配项时就会停止处理子查询，而 NOT IN 可能需要遍历整个子查询结果集。

• NOT IN

  • 使用场景：NOT IN 用于排除子查询返回的结果集中的所有值。如果外层查询中的某个值不在子查询返回的结果集中，则该行会被外层查询包含。

  • 性能：NOT IN 的性能可能受到子查询返回结果集大小的影响。如果结果集很大，性能可能会下降，因为数据库需要遍历整个列表来检查不匹配项。此外，如果子查询返回的结果集中包含NULL值，则整个NOT IN表达式的结果将是未知的（NULL），这可能会导致查询返回意外的结果。

• 总结

  • 当子查询可能返回大量数据时，或者当性能是一个关键考虑因素时，推荐使用 NOT EXISTS。

  • 如果子查询返回的结果集很小，且不包含NULL值，NOT IN 可能是一个更简单的选择。

  • 需要注意的是，如果子查询可能返回NULL值，NOT IN 可能会导致意外的结果，因为NULL与任何值的比较都会返回NULL，而不是TRUE或FALSE。在这种情况下，使用 NOT EXISTS 或其他逻辑来避免这个问题可能是一个更好的选择。

连接查询有哪几种类型?

连接查询是关系数据库中最主要的查询方式，它允许用户根据多个表之间的内在联系来查询数据。连接查询主要包括以下几种类型：

1. 内连接（INNER JOIN）

   • 定义：内连接返回两个表中符合连接条件的行。如果两个表中的行没有匹配的值，则这些行不会包含在结果集中。

   • 分类：

     • 等值连接：在连接条件中使用等于号（=）运算符比较被连接列的列值。

     • 自然连接：除了等值连接的条件外，还会自动去除结果集中的重复列。它是在两张表中寻找数据类型和列名都相同的字段，然后自动地将它们连接起来，并返回所有符合条件的结果。自然连接以相同列为条件创建等值的列，不推荐使用。

     • 不等连接：使用除等于运算符以外的其他比较运算符（如<、>、<>等）来比较被连接列的列值。

   • 特点：内连接是最常用的连接类型之一，查询结果只包含满足连接条件的行。JOIN默认是INNER JOIN。在需要使用内连接时，可以省略关键字INNER。

2. 外连接（OUTER JOIN）

   • 定义：外连接用于返回两个表中满足连接条件的行，并且会返回至少一个表中不满足连接条件的行，这些不满足条件的行在另一个表中对应的列将显示为NULL。

   • 分类：

     • 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）：返回左表（LEFT JOIN左边的表）的所有行，以及右表中符合条件的行。如果右表中没有匹配的行，则结果中右表的部分将为NULL。

     • 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：与左外连接相反，返回右表的所有行，以及左表中符合条件的行。

     • 全外连接（FULL JOIN 或 FULL OUTER JOIN）：返回左表和右表中的所有行。如果某行在另一个表中没有匹配，则用NULL值填充。但需要注意的是，MySQL数据库不直接支持全外连接，但可以通过UNION操作结合左外连接和右外连接的结果来模拟全外连接。

3. 交叉连接（CROSS JOIN）

   • 定义：交叉连接返回两个表的笛卡尔积，即两个表中所有可能的行组合。如果第一个表有M行，第二个表有N行，那么结果集将有M*N行。

   • 特点：交叉连接通常用于需要生成大量数据组合的场景，但在实际应用中，由于其返回的数据量可能非常庞大，因此需要谨慎使用。交叉连接不使用WHERE子句，而是在FROM子句中的两个连接表之间使用CROSS JOIN显式标明（显式的交叉连接），也可去掉CROSS JOIN，形成隐式的交叉连接。

4. 自连接（SELF JOIN）

   • 定义：自连接是指对同一个表进行连接操作，通常用于查询表中具有层级或关联关系的数据。

   • 特点：在自连接中，表在FROM子句中多次出现，并且每次出现都需要为其指定一个别名以区分。

什么叫集合查询？

集合查询是指将不同的数据集合按照一定的规则拼接到一起，组成一个新的、临时的数据集合的过程。这些规则通常包括并集、交集、差集等集合操作。

1. 并集（UNION/UNION ALL）：

   • 用于合并两个或多个查询结果集，并去除重复行（UNION）或保留所有行（UNION ALL）。

   • 当执行联合查询时，必须保证它们具有相同个数的结果列且列的数据类型也要相同，否则报错。

2. 交集（INTERSECT）：

   • 定义：返回两个或多个数据集合中共有的元素。

3. 差集（MINUS/EXCEPT）：

   • 定义：返回存在于第一个数据集合中但不在第二个数据集合中的元素。

   • 关键字：在Oracle中使用MINUS，在其他一些数据库（如SQL Server）中可能使用EXCEPT。

注意事项

• 在进行集合查询时，需要确保参与查询的数据集合具有相同的列数和兼容的数据类型。
• UNION和INTERSECT操作默认按照第一列的升序进行排序，如果需要其他排序方式，需要使用ORDER BY子句进行指定。
• MINUS或EXCEPT操作的结果集顺序依赖于查询的顺序，且只返回存在于第一个查询结果集中而不在后续查询结果集中的行。

索引有什么作用？

索引（Index）是数据库管理系统中用于提高数据检索效率的一种数据结构。

索引的主要作用包括：

1. 加快数据检索速度：通过索引，数据库系统可以快速定位到包含所需数据的记录，从而避免了全表扫描，显著提高了查询效率。
2. 支持排序和分组操作：索引可以支持数据库中的排序和分组操作，因为它们可以确保数据按照索引列的顺序存储。
3. 保证数据的唯一性：唯一索引和主键索引可以确保表中数据的唯一性，防止重复数据的插入。
4. 加速表与表之间的连接：在数据库执行多表连接查询时，如果连接字段上有索引，可以加速连接操作。
5. 优化查询计划：数据库优化器可以利用索引来生成更有效的查询计划，以最小的资源消耗获得查询结果。
6. 如在外键列上创建索引可以有效地避免死锁的发生，也可以防止当更新父表主键时，数据库对子表的全表锁定。
7. 索引是减少磁盘I/O的许多有效手段之一，可以将随机IO变成顺序IO。
8. 索引可以帮助服务器避免排序和创建临时表 。

使用索引的缺点：

• 索引必须创建在表上，不能创建在视图上。
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 建立索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间会很大。
• 当对表中的数据进行增加、删除和修改时，系统必须要有额外的时间来同时对索引进行更新维护，以维持数据和索引的一致性，所以索引降低了数据的维护速度。