沉梦听雨的编程指南
  • 数据库
  • Mysql
  • 实战篇
沉梦听雨
2023-10-24
目录

日常小记

# 实战篇

# 编写 Mysql 触发器

1、第一条，可执行

DELIMITER $$

create trigger update_ai_alarm_status
after update on t_ai_alarm_stat for each row
begin
	if new.status <> old.status then

        UPDATE t_ai_alarm_20231011
        SET status = NEW.status
        WHERE org_id = NEW.org_id
        AND dev_id = NEW.dev_id
        AND channel_id = NEW.channel_id
        AND alg_type = NEW.alg_type
        AND alert_type = NEW.alert_type
				AND NEW.alert_date = CONVERT(alarm_time, DATE);

        UPDATE t_ai_alarm_20231012
        SET status = NEW.status
        WHERE org_id = NEW.org_id
        AND dev_id = NEW.dev_id
        AND channel_id = NEW.channel_id
        AND alg_type = NEW.alg_type
        AND alert_type = NEW.alert_type
				AND NEW.alert_date = CONVERT(alarm_time, DATE);
        
    END IF;
END$$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

有个弊端，不灵活，需要写很多表进去

2、触发器中使用动态 sql，不可用，会报错

DELIMITER $$

CREATE TRIGGER update_ai_alarm_status 
AFTER UPDATE ON t_ai_alarm_stat FOR EACH ROW
BEGIN
  IF NEW.status <> OLD.status THEN
    SET @today = DATE_FORMAT(NEW.alert_date, '%Y%m%d');
    SET @update_status_sql = CONCAT('UPDATE t_ai_alarm_', @today, '
        SET status = NEW.status
        WHERE alarm_time = NEW.alert_date
        AND org_id = NEW.org_id
        AND dev_id = NEW.dev_id
        AND channel_id = NEW.channel_id
        AND alg_type = NEW.alg_type
        AND alert_type = NEW.alert_type');
    PREPARE create_stmt FROM @update_status_sql;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;
  END IF;
END$$

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

报错：

1336 - Dynamic SQL is not allowed in stored function or trigger
MySQL不允许在存储函数或触发器中使用动态SQL
1
2

参考：

# 联表查询问题

# 基础查询（49条记录）
			 SELECT 
        		 *
        FROM
            t_maintenance_enterprise ent
        WHERE
            ent.is_deleted = 0;
# 反例（18）
        SELECT
						*
        FROM
            t_maintenance_enterprise ent
                LEFT JOIN
            t_maintenance_maintainer main
            ON ent.id = main.enterprise_id
        WHERE ent.is_deleted = 0
						and main.is_deleted = 0;
# 正例（53）
        SELECT
						*
        FROM
            t_maintenance_enterprise ent
                LEFT JOIN
            t_maintenance_maintainer main
            ON ent.id = main.enterprise_id and main.is_deleted = 0
        WHERE ent.is_deleted = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# count(*) 和 count(1) 的区别

COUNT(*) 和 COUNT(1) 都是用于统计行数的聚合函数，但它们在实现方式上有一些区别。

  1. COUNT(*)：COUNT(*) 用于计算表中的所有行数，无论行中的数据是否为 NULL。它会对表的每一行进行计数，包括 NULL 值行。
    • 因此，COUNT(*) 返回的结果是表的总行数。
  2. COUNT(1)：COUNT(1) 也用于计算表中的行数，但它不关心行中的实际数据或 NULL 值。它会将一个常量值 1 应用于每一行，并对每一行进行计数。因为它只是对每一行应用一个固定值，而不需要访问实际的数据内容。
    • 所以在某些数据库系统中，COUNT(1) 的性能可能会比 COUNT(*) 稍微好一些。

两者的区别可以总结如下：

  • COUNT(*) 对表的每一行进行计数，包括 NULL 值行。
  • COUNT(1) 将一个常量值 1 应用于每一行进行计数，不考虑行中的实际数据或 NULL 值。

在实际使用中，通常使用 COUNT(*) 来统计行数，因为它更符合直觉和语义。而 COUNT(1) 则可以作为一种性能优化手段，在某些情况下可能会稍微提高查询的执行速度。

# 获取行号

利用 ROW_NUMBER() 函数。

SELECT
  ROW_NUMBER() over(order by town, market) rowNumber,
	town,
	market,
	count(*) 
FROM
	tbl_ent 
WHERE
	is_deleted = 0 
	AND create_time BETWEEN '2023-08-27 15:01:09' 
	AND '2023-08-29 15:01:09' 
GROUP BY
	town,
	market;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# DISTINCT 子句

  • 用于返回唯一不同的值，通常与 SELECT 子句一起使用，消除结果集中的重复行。

# 替换字段值

用 replace 方法：

UPDATE your_table_name
SET your_column_name = REPLACE(your_column_name, '替换前字符串', '替换后字符串')
1
2

用途：比如可以用来替换路径前缀

# 截取字段值

用 substring + LENGTH 方法：

UPDATE t_file 
SET no_prefix_path = SUBSTRING( file_path, LENGTH( 'http://172.16.27.40:9001' ) + 1 );

# file_path = http://127.0.0.1:8080/xxxx

-- 赋值之后
# no_prefix_path = /xxxx
1
2
3
4
5
6
7
8

# 分页查询的 sql 编写

分页查询的 SQL 语句通常包括以下部分：

  1. SELECT 语句：选择需要查询的字段。
  2. FROM 语句：指定要查询的表。
  3. WHERE 语句（可选）：添加筛选条件。
  4. ORDER BY 语句（可选）：对结果进行排序。
  5. LIMIT 和 OFFSET 语句：实现分页功能。

以下是一个分页查询的 SQL 示例：

SELECT * FROM 表名
WHERE 条件
ORDER BY 排序字段
LIMIT 每页数量 OFFSET (当前页数 - 1) * 每页数量；
1
2
3
4

注意：

实际使用中需要将 "表名"、"条件"、"排序字段"、"每页数量" 和 "当前页数" 替换为实际的值。

两种写法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
1

⚠️LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。

需注意以下几点：

  • 第一个参数指定第一个返回记录行的偏移量，注意从 0 开始
  • 第二个参数指定返回记录行的最大数目
  • 如果只给定一个参数：它表示返回最大的记录行数目
  • 第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行(目前 MySQL 已经不支持这种语法了)
  • 初始记录行的偏移量是 0 (而不是 1)

# SQL 的执行顺序⭐️

  1. FROM 子句：
    • 查询首先从 FROM 子句开始，确定要检索数据的表。
    • 确定要检索数据的表或多个表的组合。
  2. JOIN 子句：
    • 在选择的表中，根据 JOIN 子句中的条件，将相关联的行连接在一起。JOIN 的执行顺序可能受到优化器的干预，选择最优的连接方式。
  3. ON 子句：
    • （如果使用了 JOIN）用于指定连接表时的条件，决定了哪些行应该被包含在结果集中。
  4. WHERE 子句：
    • 接着，在 FROM 和 JOIN 的基础上，根据 WHERE 子句中的条件对表进行筛选，只保留符合条件的行。
  5. GROUP BY 子句：
    • 根据指定的 GROUP BY 列对数据进行分组。
  6. HAVING 子句：
    • 类似于 WHERE，但作用于 GROUP BY 的结果，用于过滤组。
    • 对【分组后的结果集】进行进一步的过滤，条件通常与聚合函数（如 COUNT, SUM 等）一起使用。
  7. SELECT 子句：
    • 根据 SELECT 子句中的列，选择要返回的结果列。
    • 确定要返回的列或计算结果，包括聚合函数和表达式。
  8. DISTINCT 子句：
    • 用于去除 SELECT 语句结果集中的重复行。
  9. ORDER BY 子句：
    • 如果有 ORDER BY 子句，则对结果集进行排序。
  10. LIMIT 子句：
  • 用于限制返回结果集的行数（仅在某些数据库系统中，如 MySQL）。
  1. OFFSET 子句：
    • 与 LIMIT 子句一起使用，用于指定返回结果集的起始行（仅在某些数据库系统中）。
    • 常用于分页（传统分页，游标分页）
  2. UNION/UNION ALL 子句：
    • 将多个 SELECT 语句的结果集合并在一起，去除重复的行（UNION）或包含所有行（UNION ALL）。

# 转换字段类型返回

CAST 函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST() 函数的参数是一个表达式，它包括用 AS 关键字分隔的源值和目标数据类型。

语法：

CAST (expression AS data_type)

  • expression：任何有效的 SQServer 表达式。
  • AS：用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。
  • data_type：目标系统所提供的数据类型，包括 bigint 和 sql_variant，不能使用用户定义的数据类型。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

  1. 二进制，同带 binary 前缀的效果: BINARY
  2. 字符型，可带参数：CHAR()
  3. 日期：DATE
  4. 时间: TIME
  5. 日期时间型：DATETIME
  6. 浮点数：DECIMAL
    • DECIMAL(m, n)，其中 m 和 n 是 DECIMAL 类型的小数位数和总位数。
  7. 整数：SIGNED
  8. 无符号整数：UNSIGNED

# 代码示例

CAST 函数在 MySQL 中用于将一个值从一种数据类型转换为另一种数据类型。以下是一些使用 CAST 函数的代码示例：

  1. 将字符串转换为整数：
SELECT CAST('123' AS SIGNED);
1

这将把字符串 '123' 转换为整数 123。

  1. 将浮点数转换为整数：
SELECT CAST(123.456 AS SIGNED);
1

这将把浮点数 123.456 转换为整数 123，小数部分被舍去。

  1. 将整数转换为浮点数：
SELECT CAST(123 AS DECIMAL(5, 2));
1

这将把整数 123 转换为浮点数 123.00，保留两位小数。

  1. 将字符串转换为日期：
SELECT CAST('2024-04-14' AS DATE);
1

这将把字符串 '2024-04-14' 转换为日期 2024-04-14。

  1. 将字符串转换为时间：
SELECT CAST('11:53:18' AS TIME);
1

这将把字符串 '11:53:18' 转换为时间 11:53:18。

  1. 将字符串转换为日期时间：
SELECT CAST('2024-04-14 11:53:18' AS DATETIME);
1

这将把字符串 '2024-04-14 11:53:18' 转换为【日期时间】 2024-04-14 11:53:18。

  1. 将字符串转换为二进制：
SELECT CAST('Hello World' AS BINARY(11));
1

这将把字符串 'Hello World' 转换为长度为 11 的二进制字符串。

# 正则表达式查询

参考：MySQL 正则表达式 | 菜鸟教程 (runoob.com) (opens new window)

# select 1

SELECT 1：用于快速检查记录是否存在，而不需要读取和返回实际的数据行。

  • 用途：主要用于检查记录的存在性、性能优化、锁定行、测试连接等。
  • 优点：减少数据传输量，提高查询性能，简化逻辑判断。

示例：

-- 判断是否存在符合条件的数据
-- 结合 LIMIT 1 使用时，一旦找到一条匹配的记录，查询就会立即停止，从而进一步提高性能。
SELECT 1
FROM large_table
WHERE some_column = 'some_value';
LIMIT 1;

-- 测试连接
SELECT 1;
1
2
3
4
5
6
7
8
9

# 学习参考

上次更新: 2024/10/18 18:00:32
临时表
JOIN 解析

← JOIN 解析→

Theme by Vdoing | Copyright © 2023-2024 沉梦听雨 | MIT License