MySQL子查询，正则表达式查询，触发器总结

目录

一. MySQL子查询

1. 子查询可以出现在SELECT、FROM、WHERE、HAVING和INSERT语句的子句中。

2. 子查询的返回结果：

3. 子查询与外部查询的关系：

注意事项：

二：正则表达式查询

1. 查询以特定字符串或字符开头的记录：

2. 查询以特定字符或字符串结尾的记录：

3. 以符号 "." 来代替任意一个字符：

4. 以符号 "*"、"+" 来匹配任意一个字符：

5. 匹配指定字符串：

6. 匹配指定字符串任意一个：

7. 匹配指定字符串以外的字符：

8. 使用 `{n,}`、`{n,m}` 来指定字符串连续出现的次数：

三：触发器

1. 触发器的语法：

2. 创建多个执行语句的触发器：

3. 查看触发器：

4. 使用 `INFORMATION_SCHEMA` 表查看触发器：

5. 触发器的使用：

5.1. 触发器的事件：

5.2. 触发器的类型：

6. 删除触发器：

7. 触发器的应用场景：

---

一. MySQL子查询

MySQL子查询是指在一个查询语句中嵌套另一个查询语句。子查询在外部查询中充当一个整体，可以用来获取更复杂的查询结果。

1. 子查询可以出现在SELECT、FROM、WHERE、HAVING和INSERT语句的子句中。

• 在SELECT语句中使用子查询作为列的值：

SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS column_alias FROM table1;

• 在FROM子句中使用子查询作为派生表（derived table）：

SELECT * FROM (SELECT column1, column2 FROM table1) AS derived_table;

• 在WHERE子句中使用子查询来过滤数据：

SELECT column1 FROM table1 WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

• 在HAVING子句中使用子查询来过滤聚合结果：

SELECT column1, COUNT(column2) FROM table1 GROUP BY column1 HAVING COUNT(column2) > (SELECT AVG(column3) FROM table2);

• 在INSERT语句中使用子查询来插入数据：

 INSERT INTO table1 (column1, column2) SELECT column3, column4 FROM table2;

2. 子查询的返回结果：

• 标量子查询：返回单个值（一行一列），可在SELECT、WHERE、HAVING和FROM子句中使用。
• 行子查询：返回多行结果集，可在IN、ANY、ALL和EXISTS等运算符中使用。
•  表子查询：返回完整的结果集，可在FROM子句中使用。

示例：
下面是一个使用子查询的示例，假设有两个表`orders`和`customers`，分别包含订单和顾客信息。我们想获取订单总数最多的顾客信息。

SELECT customer_id, customer_name
FROM customers
WHERE customer_id = (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

在这个例子中，内部的子查询获取了订单总数最多的顾客ID，然后外部查询使用这个ID来获取相应的顾客信息。

3. 子查询与外部查询的关系：

• 子查询可以引用外部查询中的表和列，即子查询可以使用外部查询中定义的别名和条件。
• 子查询可以嵌套多层，内部子查询可以引用外部子查询的结果。

注意事项：

1. 子查询的性能通常较低，特别是在处理大数据量时。尽量避免在循环中使用子查询。
2. 尽量优化子查询，确保它只返回必要的结果，避免不必要的计算和数据读取。
3. 子查询通常需要适当的索引来提高性能。确保相关字段上存在适当的索引。
4. 确保子查询的返回结果与外部查询期望的结果匹配。否则可能会导致意外的结果或错误。
5. 子查询可以嵌套多层，但要注意嵌套层数不要过多，以免导致查询难以维护和理解。

二：正则表达式查询

正则表达式常用字符匹配列表

元字符	说明
*	0个或多个匹配
+	1个或多个匹配
？	0个或1个匹配
{n}	指定数目的匹配
{n，}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）
^	匹配开头的字符
$	匹配结尾的字符
.	代替任意一个字符
[]	匹配"[]"中任意一个字符
[^]	匹配[^]字符串以外的字符串

1. 查询以特定字符串或字符开头的记录：

• 使用 `^` 元字符来匹配开头的字符。
• 示例：查询以 "abc" 开头的记录

SELECT * FROM table_name WHERE column_name REGEXP '^abc';

2. 查询以特定字符或字符串结尾的记录：

•  使用 `$` 元字符来匹配结尾的字符。
•  示例：查询以 ".com" 结尾的记录

SELECT * FROM table_name WHERE column_name REGEXP '\.com$';

3. 以符号 "." 来代替任意一个字符：

• 使用 `\.` 来匹配实际的点字符，`.` 表示匹配任意字符。
• 示例：查询包含 "a.b" 这种模式的记录

SELECT * FROM table_name WHERE column_name REGEXP 'a\.b';

4. 以符号 "*"、"+" 来匹配任意一个字符：

• `*` 用于匹配前一个字符零次或多次。
• `+` 用于匹配前一个字符一次或多次。
• 示例：查询包含 "ab"、"abb"、"abbb" 等模式的记录

 SELECT * FROM table_name WHERE column_name REGEXP 'ab*';

•  示例：查询包含 "ab"、"abb"、"aabbb" 等模式的记录

SELECT * FROM table_name WHERE column_name REGEXP 'ab+';

5. 匹配指定字符串：

• 示例：查询包含 "abc" 的记录

SELECT * FROM table_name WHERE column_name REGEXP 'abc';

6. 匹配指定字符串任意一个：

• 使用 `[]` 字符类来匹配指定字符串中的任意一个字符。
• 示例：查询包含 "a" 或 "b" 的记录

SELECT * FROM table_name WHERE column_name REGEXP '[ab]';

7. 匹配指定字符串以外的字符：

• 使用 `[^]` 否定字符类来匹配不在指定字符串中的任意一个字符。
• 示例：查询不包含 "abc" 的记录

SELECT * FROM table_name WHERE column_name REGEXP '[^abc]';

8. 使用 `{n,}`、`{n,m}` 来指定字符串连续出现的次数：

•  `{n,}` 用于匹配前一个字符至少出现 n 次。
•  `{n,m}` 用于匹配前一个字符出现 n 到 m 次之间。
• 示例：查询包含至少3个连续的 "a" 的记录

SELECT * FROM table_name WHERE column_name REGEXP 'a{3,}';

示例：查询包含连续的 2 到 4 个 "a" 的记录

SELECT * FROM table_name WHERE column_name REGEXP 'a{2,4}';

三：触发器

触发器（Trigger）是数据库中的一种特殊对象，它与表相关联，并在表中的数据发生变化时触发与之相关的特定操作。

注意：

在MySQL中，默认的语句分隔符是分号 (;)，该分号用于标识SQL语句的结束。然而，在存储过程或触发器等数据库对象的定义中，可能会包含多条SQL语句，直接使用分号作为分隔符会导致解析错误。所以需要使用LIMITER重新设置分隔符，在结束定义时使用相同的分隔符。这样，MySQL解析器就可以正确地将多条SQL语句作为一个整体来处理，不会出现解析错误的情况发生

1. 触发器的语法：

   使用 `CREATE TRIGGER` 语句创建触发器，其基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
[trigger_body]

•  `trigger_name`：触发器的名称。
•  `BEFORE` 或 `AFTER`：触发器的类型，分别表示在触发事件之前或之后执行操作。
• `INSERT`、`UPDATE`、`DELETE`：触发器的事件，指定触发的操作类型。
•  `table_name`：与触发器关联的表名。
• `FOR EACH ROW`：指定触发器为每一行数据触发。

2. 创建多个触发器：

要创建一个触发器执行多个语句，你可以在触发器的 `BEGIN` 和 `END` 块中编写多个语句。

示例：

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 第一个语句
    INSERT INTO another_table (column1, column2) VALUES (NEW.column1, NEW.column2);
    
    -- 第二个语句
    UPDATE some_table SET column3 = NEW.column1 WHERE condition;
    
    -- 第三个语句
    DELETE FROM some_other_table WHERE column4 = OLD.column2;
    
    -- 更多语句...
END;

在上述示例中，我们定义了一个在插入数据前触发的触发器。在触发器的 `BEGIN` 和 `END` 之间，我们可以编写多个语句来执行不同的操作。这些语句可以是插入数据、更新数据、删除数据，或者执行其他计算和逻辑。

3. 查看触发器：

 使用 `SHOW TRIGGERS` 语句来查看数据库中的触发器的详细信息：

SHOW TRIGGERS LIKE 'table_name';

• 这将显示与指定表名相匹配的触发器的列表。

4. 使用 `INFORMATION_SCHEMA` 表查看触发器：

使用 `INFORMATION_SCHEMA.TRIGGERS` 表来查询数据库中的触发器信息：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'database_name' AND EVENT_OBJECT_TABLE = 'table_name';

• 将 `database_name` 和 `table_name` 替换为相应的数据库名和表名。

5. 触发器的使用：

5.1. 触发器的事件：

• `INSERT`：在向表中插入数据时触发触发器。
• `UPDATE`：在更新表中的数据时触发触发器。
• `DELETE`：在从表中删除数据时触发触发器。
•  可以指定多个事件，例如 `INSERT, UPDATE`，以在多个事件发生时触发触发器。

5.2. 触发器的类型：

• `BEFORE` 触发器：在触发事件之前执行操作，可以对新插入、更新或删除的数据进行修改。
• `AFTER` 触发器：在触发事件之后执行操作，可以对已插入、更新或删除的数据进行处理。
• 触发器通常在表的数据插入、更新或删除之前或之后执行相关操作。

        在触发器中，通过使用 `NEW` 和 `OLD` 这两个预定义的伪行记录，可以在触发操作中引用新值和旧值。

•   `NEW`：在 INSERT 和 UPDATE 操作中引用新值。
•   `OLD`：在 UPDATE 和 DELETE 操作中引用旧值。

 - 示例：在触发器中进行插入操作，使用 `NEW` 关键字表示插入的新值
 

INSERT INTO another_table (column1, column2) VALUES (NEW.column1, NEW.column2);

6. 删除触发器：

使用 `DROP TRIGGER` 语句来删除触发器：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

• `IF EXISTS`：可选参数，如果存在则删除触发器。
• `schema_name`：可选参数，指定触发器所属的模式或数据库的名称。
• `trigger_name`：要删除的触发器的名称。

7. 触发器的应用场景：

• 数据完整性约束：使用触发器对数据进行验证，确保满足相关约束条件。
• 日志记录：在触发器中记录数据的变化，用于审计或日志记录目的。
• 数据复制或同步：使用触发器在多个数据库或表之间同步数据。
• 触发业务逻辑：根据特定的业务需求，使用触发器触发相关的业务逻辑。

注意，触发器的用法可能会在不同的数据库管理系统中有所差异，建议参考数据库的文档与相关资料。