MySQL各类操作

查询

行转列查询

源数据表如下

要求转为按照单独的街道统计，sql如下

SELECT 
  s.street old_street,
  SUBSTRING_INDEX(SUBSTRING_INDEX(s.street , ',', n), ',', -1) AS new_street,
  s.shop 
  FROM shop_info s
  JOIN (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
  ) AS numbers
  ON CHAR_LENGTH(s.street) - CHAR_LENGTH(REPLACE(s.street , ',', '')) >= numbers.n - 1
-- where s.street like '%人民路%'

转后结果如下图

查询后导出excel，在excel里面强制显示字符串，避免数字或者日期等类型

select 
	name , 
	concat(from_unixtime(CREATE_TIME/1000),'\t') 
from table_1

JSON字段查询

SELECT * FROM table_1 d where JSON_EXTRACT(d.json_field,'$[0].num') = 100

查监控、查执行的sql、查processlist

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

索引 

ALTER TABLE table_name ADD INDEX idx_table_name_column_name (column_name);

排序

字符串处理、聚合操作

GROUP_CONCAT

SELECT department, GROUP_CONCAT(employee_name) AS employees
FROM employees
GROUP BY department;


SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department;


SELECT department, GROUP_CONCAT(employee_name ORDER BY employee_name ASC) AS employees
FROM employees
GROUP BY department;

SUBSTRING

SELECT SUBSTRING(你的字符串字段，起始位置，字符长度) FROM 你的表名
SELECT SUBSTRING(你的字符串字段，起始位置) FROM 你的表名

SUBSTRING_INDEX

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(你的字符串字段，分隔符，-1), 分隔符, -2) FROM 你的表名;

LEFT()和RIGHT()

SELECT LEFT(你的字符串字段,字符长度) FROM 你的表名; -- 截取左边的字符
SELECT RIGHT(你的字符串字段, 字符长度) FROM 你的表名; -- 截取右边的字符

LOCATE()和INSTR()

SELECT SUBSTRING(你的字符串字段, 1, LOCATE('要查找的字符串', 你的字符串字段)) FROM 你的表名

字符集相关

-- 查看mysql中字符集相关设置
SHOW VARIABLES like '%char%';


-- 解决当前session的客户端显示
set character_set_client = 'utf8mb4';
set character_set_connection = 'utf8mb4';
set character_set_results = 'utf8mb4';

docker启动的mysql，执行sql

-- 如果mysql容器不支持中文，在容器外部执行sql
-- 多条sql执行，指定编码方式
 docker exec mysql mysql -uroot -p'密码' -e "set character_set_client = 'utf8mb4'; set character_set_connection = 'utf8mb4'; set character_set_results = 'utf8mb4'; select * from 库名.表名 where 字段名='xxx' ";