数据库的复习资料大全

数据库的复习资料大全

1.数据库的概念和常用操作：如创建、删除、备份等

（1）数据库的创建

这将创建一个名为 database_name 的空数据库。如果需要指定其他选项（例如默认字符集或排序规则），则可以在语句中添加其他参数

（2）数据库的删除

请注意，此操作将永久删除该数据库及其所有内容，包括表、数据等。

（3）数据库的备份

2.数据表的概念和常用操作：如创建、修改、查询、删除数据表等

（1）数据表的创建

这将创建一个名为 table_name 的数据表，并为其指定列和约束。在列定义中，需要指定每列的数据类型和可选的约束条件，例如 NOT NULL 或 PRIMARY KEY。

（4）数据表的修改

要修改数据表，可以使用 ALTER TABLE 语句。例如，可以添加新列，更改列名或更改列属性等。

 （5）数据表的查询

查询数据表是从其中检索数据的主要方式。可以使用 SELECT 语句来查询数据表中的数据。例如，可以使用以下语法来选择所有列和行：

（6）数据表的删除

请注意，此操作将永久删除该数据表及其所有内容，包括表、数据等。 

3.SQL 语句：包括 SELECT、INSERT、UPDATE、DELETE 等语句的使用方法和语法规则

1. SELECT 语句：

SELECT 语句用于从数据库中检索数据。一般的查询语法如下：

其中，column1, column2 等表示要检索的列名称；table_name 是要检索的表名称；condition 表示筛选的条件，例如 WHERE column1 = 'value'。

此外，还可以使用 ORDER BY 子句对结果进行排序，使用 LIMIT 子句限制检索结果的数量等。

1. INSERT 语句：

INSERT 语句用于将新行添加到数据库表中。一般的语法如下：

其中，table_name 是要插入数据的表名称，column1、column2 等是列名称，values1、values2 等是对应列的值。

1. UPDATE 语句：

UPDATE 语句用于更新数据库表中的现有行。一般的语法如下：

其中，table_name 是要更新的表名称，column1、column2 等是要更新的列名称，values1、values2 等是对应列的新值，condition 表示更新的条件。

1. DELETE 语句：

DELETE 语句用于从数据库表中删除行。一般的语法如下：

 其中，table_name 是要删除数据的表名称，condition 表示删除的条件。

4.数据类型：MySQL 支持的数据类型以及如何选择合适的数据类型

1.数字类型。

• INT：用于存储整数，常用的整数类型有 INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT 等；
• FLOAT、DOUBLE：用于存储浮点数；
• DECIMAL：用于存储精确的十进制数字。
• 2.字符串类型：
• CHAR、VARCHAR：用于存储字符，CHAR 固定长度，VARCHAR 可变长度；
• TEXT：用于存储较长的字符串。
• 3.日期/时间类型：
• DATE：用于存储日期；
• TIME：用于存储时间；
• DATETIME：用于存储日期和时间；
• TIMESTAMP：也用于存储日期和时间，但它记录的是自1970年1月1日以来的秒数。

5.索引的概念和使用：如何创建和使用索引，以及索引对查询性能的影响

1. 索引的概念

索引是为了提高数据库查询效率，而创建的一种数据结构。它类似于图书馆中的书目索引，可以帮助我们更快地找到需要的数据，从而提高查询效率。

      2.如何创建和使用索引

创建索引需要使用 CREATE INDEX 语句，并指定要创建索引的表名、列名和索引类型等信息。例如：

 在此示例中，index_name 是索引的名称，table_name 是要创建索引。

6.视图的概念和使用：如何创建和使用视图进行数据查询和管理

     1.视图的概念

视图是一个虚拟的表，它基于 SQL 查询语句的结果集而创建。视图并不实际存储数据，而是从一个或多个表中获取数据，并按照查询语句的要求进行组合和排序。视图的作用在于简化数据访问，同时保护数据安全性。

     2.创建视图

其中，view_name 是视图的名称；column1、column2 等是要返回的列名称；table_name 是要检索的表名称；condition 表示筛选的条件。

创建视图后，可以像使用常规表一样对其进行查询。

     3.使用视图

使用视图的主要优点在于它可以简化复杂的查询，并提供更安全的数据访问。对于经常需要访问同一组数据的用户，创建视图是非常有用的。

此外，视图还可以用于隐藏敏感数据。例如，可以使用视图来限制对含有敏感数据的列的访问权限，而不是直接访问底层表。

     4.修改视图

在某些情况下，可能需要修改现有视图。可以使用 ALTER VIEW 语句修改视图的定义。例如，可以添加或删除列，更改筛选条件等等。

    5.删除视图

要删除视图，可以使用 DROP VIEW 语句。例如：

需要注意的是，删除视图并不会删除底层表中的数据，只是删除了对这些数据的访问。 

7.存储过程和触发器的概念和使用：如何创建和使用存储过程和触发器来实现业务逻辑

       1.存储过程的概念和使用

存储过程是一组预编译的 SQL 语句，它们被组合在一起以执行特定的任务。存储过程可以在数据库中创建、修改和删除数据，以及返回结果集。存储过程有助于简化复杂的业务逻辑，并提高应用程序的性能。

要创建存储过程，可以使用 CREATE PROCEDURE 语句。例如：

 其中，procedure_name 是存储过程的名称，SQL 语句应该写在 BEGIN 和 END 之间。创建存储过程后，可以使用 EXECUTE 语句来调用它。例如：

     2.触发器的概念和使用

触发器是一种在数据库操作发生前或发生后自动执行的存储过程。当一个指定的事件（如插入、更新或删除行）发生时，触发器可以执行一个或多个 SQL 语句。触发器通常用于强制执行数据完整性约束，或在某些情况下自动更新相关数据。

要创建触发器，可以使用 CREATE TRIGGER 语句。例如：

其中，trigger_name 是触发器的名称，table_name 是触发器所涉及的表的名称，FOR INSERT, UPDATE, DELETE 表示触发器是在数据插入、更新或删除操作之前或之后执行的。

     3如何使用存储过程和触发器实现业务逻辑

存储过程和触发器是管理数据库中数据的强大工具。它们可以帮助您处理复杂的业务逻辑，并确保数据的完整性和一致性。例如：

• 使用存储过程将多个 SQL 语句组合在一起来执行某些特定的任务；
• 使用触发器仅在满足某些条件时才更新其他表中的数据；
• 使用存储过程自动执行复杂的算法和运算；
• 使用触发器强制执行数据完整性约束等。

 需要注意的是，在使用存储过程和触发器时，应该谨慎考虑每个存储过程和触发器的设计，以确保它们不会对性能产生不利影响，同时还要正确地处理错误和异常情况。

8.外键的概念和使用：如何创建和使用外键，以及外键对数据完整性的保护

     1.外键的概念

外键是一种约束，用于确保表之间的关系的完整性。外键可以强制执行一个表中的值与另一个表中的值相匹配，或者确保在另一个表中的值被删除或更新时，引用该值的表不会损坏。

     2.如何创建外键

要创建外键，需要在包含外键的表中定义一个列，并将该列定义为对应表的主键或唯一约束的参照列。创建外键的语法如下：

 其中，table_name 是包含外键的表名称；constraint_name 是约束的名称；foreign_key_column 是包含外键的列名称；referenced_table 是参照列所属的表名称，referenced_column 是参照列的名称。

例如，要在 Orders 表中创建一个外键，以确保 CustomerID 列的值与 Customers 表中的 CustomerID 列相匹配，可以运行以下 SQL 语句：

     3.如何使用外键

一旦外键被创建，就会强制实施表之间的关系。当尝试插入或更新表中的数据时，如果违反外键约束，则会出现错误。这可以确保了数据的一致性和完整性，防止不一致的数据出现。

     4.外键对数据完整性的保护

外键被用来保护数据完整性，因为它们防止了对表中关键列进行不正确或无效的插入、更新或删除操作。外键也可用于确保相关表之间的引用保持一致，并帮助避免数据丢失或损坏。

9.,主键的创建与主键的限制：主键必须是唯一的，不允许为空，且一个表只能有一个主键。通常情况下，主键选用自增长的整数类型，例如使用 "INT AUTO_INCREMENT"。

     1.主键的概念

主键是一种唯一标识表中每行数据的列或一组列。主键列包含一个唯一值，用于唯一地标识该行，并且主键列中不允许出现重复的值。主键还需要满足其他限制，例如不允许为空。

     2.如何创建主键

要在表中创建主键，可以使用 CREATE TABLE 或 ALTER TABLE 语句。例如，使用 CREATE TABLE 语句创建包含主键的表：

其中，column1 是主键列的名称，datatype 是主键列的数据类型。例如，要在 Customers 表中创建名为 CustomerID 的主键列，可以使用如下 SQL 语句：

 另外，使用 ALTER TABLE 语句添加主键也是可行的。例如，在 Customers 表中添加主键的SQL语句如下：

 

     2.主键的限制

主键必须是唯一的、非空的，并且在表中只能有一个。这意味着主键列的值必须是唯一的并且不为空。如果插入记录时，主键列的值为空或与表中其他记录的主键列的值相同，则会出现错误。

通常情况下，主键选用自增长的整数类型（如 "INT AUTO_INCREMENT"），因为它们可以确保唯一性，并且不需要手动输入值，使添加新行更加方便。但是，在某些情况下，您可能需要使用其他数据类型作为主键，例如字符串或日期。

10.

数据操作函数：如 MAX、MIN、AVG、SUM 等函数，用于对数据进行计算和聚合操作。

下面是常见的数据操作函数及其作用：

• MAX(column)：返回指定列中的最大值。
• MIN(column)：返回指定列中的最小值。
• AVG(column)：返回指定列中的平均值。
• SUM(column)：返回指定列的总和。

例如，要获取 Orders 表中 OrderAmount 列的最大值，可以使用以下 SQL 语句：

同样地，要获取 Customers 表中所有 Age 列的平均值，可以使用以下 SQL 语句：

 

通配符

在字符串函数中，还经常使用通配符来进行模糊匹配。常用的通配符是 %（表示任意数量的字符）和 _（表示一个单一字符）。例如，要查找 Customers 表中 LastName 中以 "A" 开头的所有记录，可以使用以下 SQL 语句：

这条语句将返回所有 LastName 列以 "A" 开头的记录。 

日期和时间函数：如 DATE、TIME、MONTH、YEAR 等函数，用于对日期和时间类型的数据进行操作。

     1.日期和时间函数

日期和时间函数是一组用于对日期和时间类型的数据进行操作的函数。它们可用于提取特定部分的日期和时间、获取当前日期和时间、计算日期差值等。下面是常见的日期和时间函数及其作用：

• DATE(date)：提取日期部分。
• TIME(date)：提取时间部分。
• MONTH(date)：返回日期中的月份。
• YEAR(date)：返回日期中的年份。
• NOW()：返回当前日期和时间。
• DATEDIFF(date1, date2)：计算两个日期之间的天数差。

例如，要获取 Customers 表中 BirthDate 列的年份，可以使用以下 SQL 语句

同样地，要获取 Orders 表中 OrderDate 列的月份，可以使用以下 SQL 语句：

 要获取当前日期和时间，可以使用以下 SQL 语句：

 要计算 OrderDate 列中最近两次订单的天数差值，可以使用以下 SQL 语句：

 

     2.日期格式化

在显示日期时，通常需要格式化为特定的字符串格式。MySQL 支持多种日期格式，可以使用 DATE_FORMAT 函数将日期转换为指定格式的字符串。例如，要将 BirthDate 列中的日期格式化为 YYYY/MM/DD 格式，可以使用以下 SQL 语句：

 

同样地，要将 OrderDate 列中的日期格式化为 MM/DD/YYYY HH:MM:SS 格式，可以使用以下 SQL 语句：

联结函数：如 GROUP_CONCAT、JOIN 等函数，用于联结查询结果和数据操作。

     1.联结函数

联结函数是一组用于联结查询结果和进行数据操作的函数。它们可用于将多个行或表的数据联结起来、进行聚合操作等。下面是常见的联结函数及其作用：

• GROUP_CONCAT(expr)：将查询结果中的多个值（expr）联结为一个字符串。
• JOIN：将两个或多个表联结起来。

例如，要获取 Products 表中每个 CategoryID 对应的所有 ProductName，可以使用以下 SQL 语句：

以上语句将返回每个 CategoryID 和对应的 ProductName 列表，其中多个 ProductName 由逗号分隔。

同样地，要联结 Customers 表和 Orders 表，并返回每个客户的订单总数和总金额，可以使用以下 SQL 语句：

 

     2.联结类型

在联结函数中，还需要指定联结类型，包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 等。这些类型决定了如何联结两个表以及哪些记录将包含在结果集中。以下是常见的联结类型及其表现：

• INNER JOIN：返回两个表中能够匹配的记录。
• LEFT JOIN：返回左表中的所有记录和右表中能够匹配的记录。
• RIGHT JOIN：返回右表中的所有记录和左表中能够匹配的记录。
• FULL OUTER JOIN：返回左表和右表中的所有记录。

11.自定义函数：在 MySQL 中，用户也可以创建自定义函数，以满足特定的业务需求。自定义函数需要使用 CREATE FUNCTION 语句进行创建，可以使用 SQL 语句和变量来完成函数的定义。存储函数通常可以作为存储过程的子程序被调用，在存储过程中通过 CALL 语句进行调用。

     1.创建自定义函数

要创建自定义函数，需要使用 CREATE FUNCTION 语句。该语句具体格式如下：

其中，function_name 是自定义函数的名称，arguments 是函数的参数，return_type 是函数返回值的类型，function_body 是具体的函数实现代码。

例如，创建一个计算平方的函数 square，可以使用以下 SQL 语句：

 以上语句定义了一个名为 square 的函数，接受一个输入参数 num，返回值类型为 INT，并将输入参数的平方作为返回值。在函数体中，我们使用 DECLARE 声明了一个变量 result，用于保存计算结果，然后使用 SET 计算平方，并使用 RETURN 返回结果。

     2.调用自定义函数

要调用自定义函数，可以使用 SELECT 语句或存储过程中的 CALL 语句。例如，在 Customers 表中计算每个客户的订单总数，并调用 square 函数计算平方和，可以使用以下 SQL 语句：

以上语句将返回每个客户的名称和订单总数的平方和。

如果要在存储过程中调用自定义函数，可以使用以下 SQL 语句：

以上存储过程中调用了 square 函数，并将结果赋值给变量 total，然后输出变量的值。 

12.存储函数与存储过程调用：存储函数通常可以作为存储过程的子程序被调用，在存储过程中通过 CALL 语句进行调用。

     1.存储函数的创建

可以使用 CREATE FUNCTION 语句。该语句具体格式如下： 

其中，function_name 是存储函数的名称，arguments 是函数的参数列表，return_type 是函数返回值的类型。在 BEGIN 和 END 之间，可以编写具体的函数实现代码。例如，以下是一个计算前 N 个自然数和的存储函数：

以上代码定义了一个名为 sum_natural_numbers 的存储函数，接受一个整型参数 n，并返回前 N 个自然数的和。在函数体中，我们使用 DECLARE 声明了两个变量 i 和 result，分别用于计数和保存计算结果。然后，在 WHILE 循环中计算前 N 个自然数的和，并将结果通过 RETURN 语句返回。 

在创建存储函数之后，可以像普通函数一样在 SQL 查询中调用它，例如：

以上查询将返回从 1 到 100 的自然数和。 

     2.存储函数调用

要在 SQL 查询中调用存储函数，可以使用以下语法：

 其中，function_name 是存储函数的名称，arguments 是输入参数。例如，如果有一个名为 my_func 的函数，接受一个整型参数并返回该参数加一的结果，则可以使用以下 SQL 查询调用该函数：

      3.存储过程调用存储函数

要在存储过程中调用存储函数，可以使用以下语法：

其中，function_name 是存储函数的名称，arguments 是输入参数，@var 是用于保存函数返回值的变量。例如，如果有一个名为 my_func 的函数，接受一个整型参数并返回该参数加一的结果，则可以使用以下存储过程调用该函数：

以上存储过程中调用了 my_func 函数，并将结果赋值给变量 result，然后输出变量的值。