【MySQL】表的增删查改

欢迎来到Cefler的博客😁
🕌博客主页：折纸花满衣
🏠个人专栏：MySQL

前言
本期主要讲解表的增删改查。
CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

👉🏻Create

表的创建的语法就不再阐述，在【MySQL】基本操作中已有介绍，下面主要介绍插入的几种形式

单行数据 + 全列插入和多行数据 + 指定列插入

1. 单行数据插入：

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...);
   

   这个语句将一行数据插入到指定表格中，指定了要插入的列名和相应的值。

2. 全列插入：

   如果你想插入所有列的数据，你可以简化 INSERT INTO 语句，只需指定表名和对应的值，数据库会自动为你填充。

   INSERT INTO table_name
   VALUES (value1, value2, value3, ...);
   

3. 多行数据插入：

   你也可以一次性插入多行数据，只需要在 VALUES 关键字后面列出多组括号包裹的值即可。

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES 
   (value1_1, value1_2, value1_3, ...),
   (value2_1, value2_2, value2_3, ...),
   (value3_1, value3_2, value3_3, ...),
   ...;
   

4. 指定列插入：

   如果你只想为某些列插入数据，可以在 INSERT INTO 语句中指定这些列名。

   INSERT INTO table_name (column1, column2)
   VALUES (value1, value2);
   

   这个语句只会将指定的列填充数据，其他列会使用默认值或 NULL。

你可以根据需要选择适合的插入方式，并确保插入的数据符合表的结构和约束条件。

insert into时遇到主键冲突和唯一键冲突

当使用 INSERT INTO 语句插入数据时，可能会遇到主键冲突和唯一键冲突。这些冲突可能会发生在尝试将数据插入到已存在的记录中，而该记录的主键或唯一键已经存在于表中的情况下。在这种情况下，数据库会报错并拒绝插入重复的键值。解决这些冲突的方法主要有两种：使用 ON DUPLICATE KEY UPDATE 或 ON CONFLICT DO UPDATE。

1. 主键冲突：

   • 当尝试插入的数据中包含与表中已存在记录的主键值相同的情况时，数据库会报告主键冲突，并拒绝插入。
   • 解决主键冲突的方法之一是使用 ON DUPLICATE KEY UPDATE（MySQL）或 ON CONFLICT DO UPDATE（PostgreSQL）语句。这允许在遇到主键冲突时执行更新操作，而不是简单地拒绝插入。

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...)
   ON DUPLICATE KEY UPDATE column1=new_value1, column2=new_value2, ...;
   

2. 唯一键冲突：

   • 如果表中存在唯一键约束，而尝试插入的数据包含与已存在记录的唯一键值相同的情况，则会触发唯一键冲突。
   • 同样，可以使用 ON DUPLICATE KEY UPDATE（MySQL）或 ON CONFLICT DO UPDATE（PostgreSQL）来解决唯一键冲突。这样可以执行更新操作，而不是简单地拒绝插入。

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...)
   ON DUPLICATE KEY UPDATE column1=new_value1, column2=new_value2, ...;
   

使用这些方法可以在插入数据时处理主键冲突或唯一键冲突，确保数据库中的数据一致性。

插入结束后遇到的n rows affected:

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

🍑SELECT ROW_COUNT() 查询受到影响的数据行数
SELECT ROW_COUNT() 是用于获取最近一次执行的 INSERT, UPDATE, DELETE 语句所影响的行数的 MySQL 函数。它返回一个整数，表示受影响的行数。通常在执行数据操作后，可以使用 SELECT ROW_COUNT() 来检查操作的结果，以便在应用程序中进行逻辑处理。

例如，如果你执行了一条 INSERT 语句插入了多行数据，并且想要知道实际插入了多少行，可以在 INSERT 语句后面立即执行 SELECT ROW_COUNT() 来获取插入的行数。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1_1, value1_2, value1_3, ...),
(value2_1, value2_2, value2_3, ...),
(value3_1, value3_2, value3_3, ...);

SELECT ROW_COUNT();

这将返回插入的行数，你可以根据需要对结果进行处理。

insert into查询结果

在MySQL中，你可以使用INSERT INTO ... SELECT语句将一个查询的结果插入到另一个表中。这种技术非常有用，尤其是当你想要从一个表复制数据到另一个表，或者对数据进行某种转换后再插入时。

下面是一个基本的INSERT INTO ... SELECT语句的示例：

INSERT INTO target_table (column1, column2, column3, ...)
SELECT columnA, columnB, columnC, ...
FROM source_table
WHERE some_condition;

在这个示例中：

• target_table 是你想要插入数据的表。
• column1, column2, column3, ... 是target_table中的列名，你希望插入数据到这些列中。
• source_table 是包含你想要复制的数据的表。
• columnA, columnB, columnC, ... 是source_table中的列名，它们包含你想要复制的数据。
• WHERE some_condition 是一个可选的子句，用于指定从source_table中选择哪些行进行复制。

示例

假设我们有两个表：students 和 students_backup。我们想要将students表中所有年龄大于18岁的学生复制到students_backup表中。这两个表的结构是相同的，都包含id, name, 和 age 这三列。

我们可以使用以下SQL语句来实现这个操作：

INSERT INTO students_backup (id, name, age)
SELECT id, name, age
FROM students
WHERE age > 18;

这条语句将从students表中选择所有年龄大于18岁的学生，并将这些数据插入到students_backup表中。

注意事项

• 确保目标表（在这个例子中是students_backup）已经存在，并且具有与你要插入的数据相匹配的列。
• 如果目标表中的某些列有默认值、自增属性或外键约束，你需要考虑这些因素。例如，如果目标表的id列是自增的，你可能不需要在INSERT INTO语句中指定这个列，因为数据库会自动为你生成一个新的ID。
• 如果源表和目标表在某些列上有不同的数据类型或长度限制，你可能需要进行数据转换或截断，以确保数据可以成功插入到目标表中。
• 在执行此类操作之前，最好先备份你的数据，以防万一出现任何意外情况。

👉🏻 Retrieve

语法：

SELECT
	[DISTINCT] {* | {column [, column] ...}
	[FROM table_name]
	[WHERE ...]
	[ORDER BY column [ASC | DESC], ...]
	LIMIT ...

上述语法是select查询语句的大致框架，主要由FROM,WHETE,ORDER BY,LIMIT组成，接下来我会分别介绍这些组成的用法，帮助大家在后续查表时，可以灵活组合运用这些查询子句进行精确查询。

查询字段为表达式

查询字段为表达式的查询可以在 SELECT 语句中使用任何有效的表达式来检索数据，这样你可以在结果中获得经过计算或操作后的新值。以下是一些常见的例子：

1. 计算字段：

   • 你可以在 SELECT 语句中对列进行数学运算或其他计算，以生成新的字段。比如，你可以这样计算两个列的和：

   SELECT column1, column2, column1 + column2 AS sum
   FROM table_name;
   

2. 字符串操作：

   • 你可以对字符串列进行操作，比如连接字符串、截取子串等。比如，你可以这样将两个列的字符串连接起来：

   SELECT column1, column2, CONCAT(column1, ' ', column2) AS concatenated_string
   FROM table_name;
   

3. 日期处理：

   • 如果你的数据库包含日期或时间数据，你可以执行各种日期和时间操作。比如，你可以这样计算两个日期之间的天数差：

   SELECT start_date, end_date, DATEDIFF(end_date, start_date) AS days_difference
   FROM table_name;
   

4. 条件表达式：

   • 你可以在 SELECT 语句中使用条件表达式来根据特定条件返回不同的值。比如，你可以根据条件返回不同的结果：

   SELECT column1, 
          CASE 
              WHEN column2 > 0 THEN 'Positive' 
              WHEN column2 = 0 THEN 'Zero' 
              ELSE 'Negative' 
          END AS value_type
   FROM table_name;
   

通过在 SELECT 语句中使用表达式，你可以根据需要灵活地处理数据，并生成适合你需求的结果。

表达式取别名

在SQL中，你可以使用AS关键字为SELECT语句中的表达式或计算结果取别名。这对于提高查询结果的可读性和可理解性非常有用，尤其是在涉及到计算字段或复杂表达式时。

以下是如何在SELECT语句中为表达式取别名的示例：

SELECT column1 + column2 AS sum_result
FROM table_name;

在这个例子中，column1 + column2是一个计算表达式，它将column1和column2列的值相加。通过使用AS关键字，我们将这个计算结果取名为sum_result，这样在结果集中就会显示sum_result列，而不是column1 + column2。

你还可以使用别名来重命名列名，使它们更易于理解。例如：

SELECT first_name AS given_name, last_name AS family_name
FROM person_table;

在这个例子中，first_name列被重命名为given_name，last_name列被重命名为family_name，这样结果集中的列名更符合直觉，更容易理解。

实际上，在大多数情况下，可以省略AS关键字而直接为SELECT语句中的表达式或计算结果取别名。
然而，在一些数据库系统中，如Oracle数据库，对于某些情况下的别名，省略AS关键字可能会导致语法错误。因此，为了确保查询的可移植性和兼容性，最好使用AS关键字来为表达式取别名。

distinct结果去重

要在SQL查询中使用DISTINCT关键字来去重结果集。这对于在结果集中仅获取唯一值时非常有用。以下是如何在SQL中使用DISTINCT：

SELECT DISTINCT column1, column2, ...
FROM table_name;

这将从指定的表中选择唯一的组合，并将它们作为结果返回。如果你指定多列，系统将检查这些列的组合是否唯一，并返回唯一的组合。

举例来说，如果你有一个表包含名字和姓氏，但是可能有多个人有相同的名字或姓氏，你可以使用DISTINCT来获取所有唯一的名字和姓氏组合。

SELECT DISTINCT first_name, last_name
FROM person_table;

这将返回表中所有不重复的名字和姓氏组合。

where

当你在使用 SELECT 语句时，WHERE 子句是一个非常有用的工具，可以帮助你过滤出符合特定条件的行。以下是一些基本的 WHERE 与 SELECT 配合使用的方法：

1. 基本过滤：

   • 最简单的用法是通过指定列名和条件来筛选数据行。比如，你可以这样查询出表中满足某个条件的行：

   SELECT *
   FROM table_name
   WHERE column1 = value;
   

2. 逻辑操作符：

   • 你可以使用逻辑操作符（AND、OR、NOT）将多个条件组合在一起，以进一步过滤数据。比如，你可以这样查询同时满足两个条件的行：

   SELECT *
   FROM table_name
   WHERE column1 = value1 AND column2 = value2;
   

3. 模糊搜索：

   • 有时候你可能想要匹配模糊的值，而不是精确匹配。在这种情况下，你可以使用 LIKE 操作符结合通配符 % 进行模糊搜索。比如，你可以这样查询以特定字符串开头的行：

   SELECT *
   FROM table_name
   WHERE column1 LIKE 'prefix%';
   

4. 范围查询：

   • 有时你可能需要检索某个范围内的数据，这时你可以使用比较操作符（例如 <, >, <=, >=）结合 BETWEEN 或者 IN 来实现。比如，你可以这样查询某个范围内的行：

   SELECT *
   FROM table_name
   WHERE column1 BETWEEN value1 AND value2;
   

5. 空值检查：

   • 有时你可能需要查找空值或非空值的行，你可以使用 IS NULL 或 IS NOT NULL 来实现。比如，你可以这样查询某列包含空值的行：

   SELECT *
   FROM table_name
   WHERE column1 IS NULL;
   

这些是基本的 WHERE 与 SELECT 配合使用的方法，你可以根据自己的需求灵活运用，以便从数据库中检索出符合条件的数据。

基本比较运算符

order by

ORDER BY 子句是 SQL 中用来对查询结果进行排序的关键字。通过 ORDER BY 子句，你可以按照指定的列对结果集进行升序或降序排序。

以下是 ORDER BY 子句的基本用法：

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• column1, column2, ...：是你希望排序的列名，你可以指定一个或多个列进行排序。
• [ASC|DESC]：是可选的，用于指定排序的顺序，ASC 表示升序（默认），DESC 表示降序。

举例来说，如果你有一个 students 表，其中包含学生的姓名和成绩，你想按照成绩从高到低的顺序来显示学生信息，可以这样写 SQL 查询：

SELECT name, score
FROM students
ORDER BY score DESC;

这将返回一个按照成绩从高到低排序的学生信息列表。

如果你想按照成绩从高到低排序，并且当成绩相同时按照姓名的字母顺序进行排序，可以这样写：

SELECT name, score
FROM students
ORDER BY score DESC, name ASC;

这将返回一个先按成绩降序排列，然后在成绩相同的情况下按姓名升序排列的学生信息列表。

多字段排序，排序优先级随书写顺序

ORDER BY 子句中可以使用列别名

ORDER BY 子句是非常有用的，它可以让你根据不同的需求对查询结果进行灵活的排序，从而更好地展示数据。

limit

LIMIT子句是 SQL 中用来限制查询结果返回的行数的关键字。通过LIMIT子句，你可以指定查询返回的结果集中最多包含多少行数据。

以下是LIMIT子句的基本用法：

SELECT column1, column2, ...
FROM table_name
LIMIT row_count;

• row_count：是你希望返回的行数。它可以是一个数字，表示返回的行数，也可以是两个数字，用逗号分隔，分别表示返回结果集的起始行和行数。例如，LIMIT 10表示返回前10行数据，LIMIT 5, 10表示从第6行开始返回后面的10行数据。

举例来说，如果你有一个 students 表，其中包含学生的姓名和成绩，你想查询出前5名成绩最高的学生，可以这样写 SQL 查询：

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 5;

这将返回一个包含前5名成绩最高学生姓名和成绩的结果集。

如果你想返回倒数几行数据，可以使用负数表示。

👉🏻Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

基本用法在【MySQL】基本操作中已有介绍

👉🏻Delete

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

基本用法在【MySQL】基本操作中已有介绍

truncate截断

在MySQL中，TRUNCATE 是一个用于快速删除表中的所有记录的语句。与 DELETE 语句不同，TRUNCATE 不会记录任何单独的删除操作，因此它通常比 DELETE 更快，并且不会触发任何与表上的记录删除相关的触发器（triggers）。

以下是关于 TRUNCATE 的一些关键点：

1. 快速删除：由于 TRUNCATE 是一种DDL（数据定义语言）操作，而不是DML（数据操作语言）操作，它通常比 DELETE 更快，因为 TRUNCATE 不记录任何单独的行删除操作。
2. 重置AUTO_INCREMENT：如果表有一个AUTO_INCREMENT列，TRUNCATE 会重置该列的计数器到其初始值（通常是1，除非你在创建表时指定了其他值）。
3. 不触发触发器：与 DELETE 不同，TRUNCATE 不会触发任何与删除记录相关的触发器。
4. 锁定表：在执行 TRUNCATE 时，表通常会被锁定，直到操作完成。
5. 无法回滚：由于 TRUNCATE 是一种DDL操作，它通常不能通过 ROLLBACK 语句回滚。
6. 不会返回被删除的行：TRUNCATE 不像 DELETE 那样返回被删除的行或受影响的行数。

使用示例

假设你有一个名为 my_table 的表，你可以使用以下语句来截断它：

TRUNCATE TABLE my_table;

这条语句将删除 my_table 中的所有记录，并重置任何AUTO_INCREMENT计数器。

注意事项

• 在使用 TRUNCATE 之前，请确保你确实想要删除表中的所有记录，并且已经备份了任何重要的数据。
• 由于 TRUNCATE 是一种DDL操作，它可能会受到某些数据库权限的限制。确保你有足够的权限来执行此操作。
• 如果你只是想删除表中的某些记录而不是全部记录，请使用 DELETE 语句。
• 在生产环境中使用 TRUNCATE 时，请确保你了解它的影响，并考虑在低流量时段执行此操作，以减少对数据库性能的影响。

👉🏻聚合函数

在SQL中，聚合函数用于对一组值执行计算，并返回单个值。这些函数在数据分析、报告和数据库管理中非常有用。以下是一些常见的聚合函数：

1. COUNT()

   • COUNT() 函数用于计算行数。它经常与 GROUP BY 子句一起使用，以计算每个组的行数。
   • 示例：SELECT COUNT(*) FROM orders; （计算orders表中的所有行）
   • 示例（分组）：SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id; （计算每个客户的订单数）

2. SUM()

   • SUM() 函数返回某列数值的总和。
   • 示例：SELECT SUM(quantity) FROM orders; （计算orders表中所有订单的数量总和）

3. AVG()

   • AVG() 函数返回某列数值的平均值。
   • 示例：SELECT AVG(price) FROM orders; （计算orders表中所有订单的平均价格）

4. MAX()

   • MAX() 函数返回某列的最大值。
   • 示例：SELECT MAX(price) FROM orders; （找到orders表中价格最高的订单）

5. MIN()

   • MIN() 函数返回某列的最小值。
   • 示例：SELECT MIN(quantity) FROM orders; （找到orders表中数量最少的订单）

6. GROUP_CONCAT()（某些数据库系统特有，如MySQL）

   • GROUP_CONCAT() 函数将多个行的值连接成一个字符串。它通常用于将多个文本值组合成一个单一的、由逗号或其他分隔符分隔的列表。
   • 示例：SELECT customer_id, GROUP_CONCAT(product_id) FROM orders GROUP BY customer_id; （为每个客户列出其所有订单的产品ID）

7. FIRST_VALUE() 和 LAST_VALUE()（在支持窗口函数的数据库系统中，如SQL Server、PostgreSQL、Oracle等）

   • 这些函数返回在有序窗口中的第一行或最后一行的值。它们经常与 OVER() 子句一起使用来定义窗口。

8. 其他特定于数据库的聚合函数

   • 不同的数据库系统可能有自己特有的聚合函数。例如，SQL Server 中的 STRING_AGG() 函数与 MySQL 的 GROUP_CONCAT() 类似，但提供了更多的功能和灵活性。

在使用这些聚合函数时，重要的是要理解它们如何与 GROUP BY 子句一起工作，以及它们如何影响查询的结果集。此外，聚合函数的结果通常与原始数据集中的行数不同，因为它们返回的是计算后的单个值，而不是多行数据。

group by

当我们在SQL中处理数据时，经常需要按照某些列的值对数据进行分组，并对每个组执行某些计算或操作。这就是GROUP BY子句的用武之地。

🍇基本概念

GROUP BY子句用于结合聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()等），根据一个或多个列对结果集进行分组。对于每个分组，聚合函数会对该组中的所有行执行计算。

🍇 语法

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s);

🍇 示例

假设我们有一个名为orders的表，其中包含以下列：order_id, customer_id, product_id, quantity, 和 price。

如果我们想要找出每个客户购买的总数量，我们可以使用以下查询：

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id;

在这个查询中：

• 我们选择了customer_id列和SUM(quantity)（作为total_quantity的别名）。
• 我们从orders表中选择数据。
• 我们没有使用WHERE子句，但如果有需要，我们可以添加它来过滤结果。
• 我们使用GROUP BY customer_id来按照customer_id列的值对数据进行分组。

GROUP BY主要避免了将那些id相同的行视为不同的组

🍇 注意事项

1. SELECT列表中的非聚合列：当你在SELECT列表中使用GROUP BY时，除了聚合函数之外，你只能选择那些在GROUP BY子句中指定的列。如果你选择了一个不在GROUP BY子句中的列，并且没有对该列使用聚合函数，大多数数据库系统会抛出一个错误。
2. HAVING子句：与WHERE子句类似，但HAVING子句在数据分组之后应用，用于过滤分组后的结果。例如，如果你想要找出购买总数量超过100的客户，你可以使用HAVING子句：

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id
HAVING total_quantity > 100;

3. ORDER BY子句：你可以使用ORDER BY子句对分组后的结果进行排序。例如，如果你想要按照购买总数量降序排列客户，你可以添加ORDER BY子句：

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id
HAVING total_quantity > 100
ORDER BY total_quantity DESC;

having子句及和where的不同应用场景

当我们在SQL中查询数据库时，WHERE 和 HAVING 子句都是用于过滤结果的，但它们在使用的上下文和目的上有一些重要的区别。

WHERE 子句

• 使用场景：WHERE 子句主要用于在数据选择（SELECT）之前对表中的行进行过滤。
• 过滤对象：WHERE 子句过滤的是表中的行（记录）。
• 与聚合函数的关系：通常，WHERE 子句中不使用聚合函数（如 SUM(), COUNT(), AVG(), MAX(), MIN() 等）。
• 执行时机：WHERE 子句在数据被选取出来之前执行，也就是说，它是数据检索的第一步。

HAVING 子句

• 使用场景：HAVING 子句主要用于在数据聚合（如 GROUP BY）之后对结果进行过滤。
• 过滤对象：HAVING 子句过滤的是聚合后的结果集（通常是分组后的结果）。
• 与聚合函数的关系：HAVING 子句中经常与聚合函数一起使用，因为它是在数据被聚合之后进行过滤的。
• 执行时机：HAVING 子句在数据被选取和聚合之后执行，它通常与 GROUP BY 子句一起使用。

示例

假设我们有一个名为 Orders 的表，其中记录了订单的信息，包括订单ID（OrderID）、订单金额（Amount）和订单日期（OrderDate）。

如果我们想查询总订单金额大于1000的订单ID，我们可以使用 GROUP BY 和 HAVING 子句：

SELECT OrderID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY OrderID
HAVING TotalAmount > 1000;

但是，如果我们想查询订单日期在2023年之前的订单，并且这些订单的总金额大于1000，我们需要使用 WHERE 和 HAVING 子句组合：

SELECT OrderID, SUM(Amount) AS TotalAmount
FROM Orders
WHERE OrderDate < '2023-01-01'
GROUP BY OrderID
HAVING TotalAmount > 1000;

在这个例子中，WHERE 子句首先过滤出2023年之前的订单，然后 GROUP BY 子句对这些订单进行分组，最后 HAVING 子句过滤出总金额大于1000的订单。

如上便是本期的所有内容了，如果喜欢并觉得有帮助的话，希望可以博个点赞+收藏+关注🌹🌹🌹❤️ 🧡 💛,学海无涯苦作舟,愿与君一起共勉成长