MySQL 查询汇总

1. 基础查询

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...
GROUP BY column_name(s)
HAVING condition
ORDER BY column1, column2, ... ASC|DESC
LIMIT number

执行顺序

(8) SELECT (9) DISTINCT 
(1) FROM 
(3)  JOIN 
(2) on 
(4) WHERE 
(5) GROUP BY 
(6) WITH {CUBE|ROLLUP}
(7) HAVING 
(10) ORDER BY 
(11) LIMIT 

1. limit 限制查询行

语法 limit [offset,] N
offset: 偏移量，计数从 0 开始
N：取出条目

其他：limit N 等价 limit 0, N

-- 检索记录行 6 ~ 15，按每页 10 行
select * from table limit 5, 10;

-- 检索记录行 96 ~ last, 指定 N 为 -1 
select * from table limit 95, -1;

1.2 count()

COUNT(1) 和 COUNT(*): 包括NULL值的行
COUNT(column_name): 计算指定列非NULL值的行数

1.3 like 查询

语法 WHERE column LIKE pattern
pattern：匹配模式，可以包含两种通配符：
%：代表零个、一个或多个任意字符
_：代表一个任意字符

select * from boot_indicator where boot_indicator.receiverTown like '%街道%';

xml 查询配置

user_name LIKE CONCAT('%', #{userName} ,'%')

user_name LIKE CONCAT('%', '#{userName}' ,'%')

1.4 其他

select sum(if(quantity > 0, quantity, 0)) as sum from boot_indicator;

select * from boot_order
WHERE ( attributes LIKE '%;city:杭州市;%');

select * from boot_indicator where tpCode IS NOT NULL;

2. in 查询

2.1 in (常量列表)

in 查询相当于多个 or 条件的叠加

select * from boot_indicator where receiverCity in ('杭州市', '温州市');

2.2 in (子查询)

select * from user where userId in (select id from B);

select * from user where userId in (select id, age from B);
此语句有问题，in查询的子条件返回结果必须只有一个字段

3. 分组

3.1 按日期分组

select DATE_FORMAT(payDate,'%Y-%m-%d') as payTime, count(1)
from boot_indicator
group by payTime;

补充：MySQL 里 group by 可以使用别名；where中不能使用别名；order by中可以使用别名

3.2 联合分组

SELECT tpCode, receiverCity, receiverDistrict, count(1) as total
FROM boot_indicator
group by tpCode, receiverCity, receiverDistrict;

SELECT tpCode, receiverCity, receiverDistrict,
       sum(if(express = 1, 1, 0)) as sum, count(1) as total
FROM boot_indicator
group by tpCode, receiverCity, receiverDistrict;

返回结果：8 条

3.3 统计分组的总行数

SELECT COUNT(DISTINCT tpCode, receiverCity, receiverDistrict) as total
FROM boot_indicator;

返回结果：total=5 不符合预期，为 null 的数据被过滤了

分析：

SELECT DISTINCT tpCode, receiverCity, receiverDistrict
FROM boot_indicator;

SELECT DISTINCT IFNULL(tpCode, 'null'), IFNULL(receiverCity, 'null'),
             IFNULL(receiverDistrict, 'null')
FROM boot_indicator;

解决方式：

SELECT COUNT(DISTINCT IFNULL(tpCode, 'null'), IFNULL(receiverCity, 'null'),
    IFNULL(receiverDistrict, 'null')) as total
FROM boot_indicator;

返回结果：total=8

优化查询：

select count(1) from (
    select count(1) from boot_indicator
    group by tpCode, receiverCity, receiverDistrict
) t;

3.4 组合

select tpCode, express, count(distinct tradeId) as tradeCount, count(1) as total
from boot_indicator
group by tpCode, express
ORDER BY count(1) DESC
limit 0, 10;

3.5 group_concat()

group_concat()函数用于将一个分组内的多个值连接成一个字符串，通常以逗号分隔

select group_concat(receiverTown)
from boot_indicator
group by tpCode;

默认情况下，group_concat()会忽略 NULL 值, 可结合 IFNULL() 或 COALESCE()

select group_concat(ifnull(receiverTown, 'null'))
from boot_indicator
group by tpCode;

select group_concat(coalesce(receiverTown, 'null'))
from boot_indicator
group by tpCode;

补充：
CONCAT() 函数用于在单行级别上拼接字符串，适用于构造或格式化单个记录的输出。
GROUP_CONCAT() 函数用于在多行（同一分组内）级别上拼接字符串，常用于数据聚合场景，生成每个分组内相关数据的列表表示

-- CONCAT(str1, str2, ..., str_n)
select concat(receiverProvince, ',', receiverCity, '_', receiverDistrict)
from boot_indicator;

-- CONCAT_WS(separator, str1, str2, ...) 第一个参数为分隔符
select concat_ws(',', receiverProvince, receiverCity, receiverDistrict)
from boot_indicator;

4. 条件函数

4.1 if()

语法 IF(expr1,expr2,expr3);

4.2 case when

CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END

CASE 表示函数开始，END 表示函数结束
WHEN…THEN 类似于 Java 的 switch

4.3 case when 替代 if()

select tpCode , count(1),
       sum(if(express = 1, 1, 0)) as ifSum,
       sum(case when express = 1 then 1 else 0 end ) as caseSum
from boot_indicator
group by tpCode;

4.4 case when 替代子查询

5. JSON 字段查询

4.1 JSON访问运算符 -> 和 ->>

-> 用于提取JSON文档中的一个键值，返回一个JSON值
如果值是字符串，它会保持被引号包围的状态，就像在原始JSON文档中一样

->>类似于 ->，但它会进一步解引用JSON值，如果提取的值是JSON字符串，它会去掉引号并尝试将其转换为相应的SQL数据类型（如数字、布尔值或日期等）

注：这些语法糖在MySQL 5.7.9及更高版本中可用

示例：

tradeId	json1
20240301001	{“express”: 1, “cpResult”: “TO_HOME”, “official”: 1}

select json_extract(json1, '$.express') from boot_indicator where tradeId = 20240301001;
SELECT json1 -> '$.express' FROM boot_indicator where tradeId = 20240301001;
SELECT json1 -> '$.cpResult' FROM boot_indicator where tradeId = 20240301001;

SELECT json1 ->> '$.express' FROM boot_indicator where tradeId = 20240301001;
SELECT json1 ->> '$.cpResult' FROM boot_indicator where tradeId = 20240301001;

select * from boot_indicator where json1 -> '$.express' = 1;
select * from boot_indicator where json1 -> '$.express' = '1'; -- 查不出数据
select * from boot_indicator where json1 ->> '$.express' = '1';

【信息由网络或者个人提供，如有涉及版权请联系COOY资源网邮箱处理】