SQL必知必会

一、字符串截取函数

# 截取指定长度的字符串
substr(str,start,len)

# str：字符串
# start：开始截取的字符位置
# len：截取字符串的长度

# 从字符串某一位置开始截取
substr(str,start)

二、分组拼接

原始数据：

处理后数据：

SELECT
	sell_date,
	COUNT(DISTINCT product) AS nums,
	GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
FROM
	Activities
GROUP BY
	sell_date
ORDER BY
	sell_date

三、正则表达式

like：模糊匹配

rlike、regexp：正则匹配

# 正则
SELECT
    patient_id, patient_name,conditions
FROM
    Patients
WHERE
    conditions rlike '^DIAB1|\\sDIAB1'

# 模糊匹配
SELECT
    patient_id, patient_name,conditions
FROM
    Patients
WHERE
    conditions like 'DIAB1%' or conditions like '% DIAB1'

四、mysql行转列和列转行

（一）行转列

原始数据：

转换后数据：

代码：

# SUM( CASE WHEN)
SELECT
	userid,
	SUM(CASE subjectName WHEN '语文' THEN score ELSE 0 END) AS '语文',
	SUM(CASE subjectName WHEN '数学' THEN score ELSE 0 END) AS '数学',
	SUM(CASE subjectName WHEN '英语' THEN score ELSE 0 END) AS '英语',
	SUM(CASE subjectName WHEN '政治' THEN score ELSE 0 END) AS '政治'
FROM
	tb_score
GROUP BY
	userid

# SUM(IF())
SELECT
	userid,
	SUM(IF(subjectName =  '语文', score, 0 )) AS '语文',
	SUM(IF(subjectName =  '数学', score, 0 )) AS '数学',
	SUM(IF(subjectName =  '英语', score, 0 )) AS '英语',
	SUM(IF(subjectName =  '政治', score, 0 )) AS '政治'
FROM
	tb_score
GROUP BY
	userid

# 必须要用GROUP BY

（二）列转行

# UNION

SELECT
	userid,
	'语文' AS subject_Name,
	'语文' AS score
FROM
	tb_score
UNION
SELECT
	userid,
	'数学' AS subject_Name,
	'数学' AS score
FROM
	tb_score
UNION
SELECT
	userid,
	'英语' AS subject_Name,
	'英语' AS score
FROM
	tb_score	
UNION
SELECT
	userid,
	'政治' AS subject_Name,
	'政治' AS score
FROM
	tb_score	

五、去重

distinct 放在select后，是对后面跟着的所有字段进行去重

原始数据：

# 返回两行
SELECT DISTINCT
	userid,
    salary
FROM
    test

# 返回一行	
SELECT DISTINCT
    salary
FROM
    test