mysql 小知识

1. 逗号拼接字段

• 使用CONCAT（）函数即可 [分组的情况下使用GROUP_CONCAT()]

SELECT
	 CONCAT( su.nick_name ,',' ) 
FROM
	sys_user su 
WHERE
	su.del_flag = '0' 
	AND su.user_id IN ( 101, 102 )

2. 字段类型转换

• 使用CONVERT（）函数 或者CAST（）函数 或者CONCAT（）即可

1.将数据库的int类型转为char类型排序
(第一中写法)
SELECT
	u.bn_user_worker_type
FROM
	busi_bn_user u 
WHERE
	u.bn_user_produce_id = 131 
ORDER BY
	CONVERT(u.bn_user_worker_type , CHAR )  
===============================================================================================
(第二种写法)
SELECT
	u.bn_user_worker_type 
FROM
	busi_bn_user u 
WHERE
	u.bn_user_produce_id = 131 
ORDER BY
	CAST( u.bn_user_worker_type AS CHAR )
===============================================================================================
(第三种写法)
SELECT
	u.bn_user_worker_type 
FROM
	busi_bn_user u 
WHERE
	u.bn_user_produce_id = 131 
ORDER BY
	  CONCAT(u.bn_user_worker_type,'')	

3.查询逗号拼接的两个字符串是否存在相同内容(只要一个相同就可以)

SELECT CONCAT( ',', '1,2,3', ',' ) REGEXP CONCAT( ',', REPLACE ( '10,11,2', ',', ',|,' ), ',' )

4. 常用函数

	1.SYSDATE()和NOW()区别
	 	SELECT  SYSDATE(),NOW()
	 	NOW()是sql语句开始执行的时候就获取到的值
	 	SYSDATE()是sql语句执行的过程中动态的值
	2. LENGTH()获取长度
	3. SUBSTR(xxx,xxx) 截取 	
	4. CURDATE()获取当前日期(yyyy-MM-dd)
	5. CURTIME()获取当前时分秒 (HH:mm:ss)
	6. select YEAR(SYSDATE()) 获取当前年份；select MONTH(SYSDATE()) 获取当前月份
	7. SELECT str_to_date('2020-07-06 8:20:30','%Y-%m-%d %H:%i:%s') 将字符串转为datetime类型
	8. SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d') 格式化日期
	9. SELECT DATEDIFF(SYSDATE(),'2022-07-04') 计算和当前日期差几天 负的代表还未到

5.高频使用函数

	1.CASE语句
		注意点: (1)由 CASE 开始 END 结束;(2)WHEN 和 THEN 成对出现，WHEN 后边为条件，可以使用表中的所有字段，THEN 后为最终输出的值;(3)ELSE 为兜底逻辑，直接给出值，ELSE 可以没有 END后也可以不起别名
	  example:
		SELECT
			s.sex,
			( CASE s.sex WHEN '0' THEN 'man' END ) NAME 
		FROM
			sys_user s
			
  	 2.RIGHT()函数: 从右边截取 RIGHT(str,len) str就是要截取的字符串 len就是截取的长度
       example:
  	     SELECT
            *
         FROM
              yl_material_in_detail
         WHERE
              is_delete = 0
              AND mid_barcode LIKE CONCAT( '%', RIGHT ( #{no}, 6 ), '%' )
         ORDER BY
              mid_barcode DESC
         LIMIT 1			

6.日常使用

1.	limit 1,2 [跳过第一个取后面2个数据]
	limit 2 offset 1  [offset标识跳过几个，第一个参数是取几个数据]
2.  创建表
	 1).create table if not exist tableName_copy like (tableName)  创建一个和tableName一样的表结构的表	
	 2).create table if not exist tableName_copy (select * from tableName) 创建一个和tableName一样结构的表并且数据填充一样
3.  创建视图 
	 1).CREATE VIEW <视图名> AS <SELECT语句>	 AS 不能省略
	 2).CREATE VIEW <试图名>(字段名) AS <SELECT语句> 该方法可以在试图名后写字段名称
4. 创建触发器
		CREATE TRIGGER trigger_name 
		trigger_time trigger_event ON tbl_name 
		FOR EACH ROW 
		trigger_stmt
		
		trigger_name：标识触发器名称，用户自行指定；
		trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
		trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
		tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
		trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
		
		example: 
			CREATE TRIGGER testTrigger 
			AFTER INSERT ON demo 
			FOR EACH ROW
			BEGIN
					INSERT INTO test
				VALUES
					( new.id, new.NAME );
			END
			
5. where多个条件组合in			
		SELECT
				* 
		FROM
			busi_produce p 
		WHERE
			( p.produce_org_id, equipment_no )
		IN ( ( 100, 1 ), ( 225, 6 ), ( 100, 16 ) )
		
6. 将逗号拼接的字符串数据拆分成多个	
		SELECT r.relation_user_id,ifnull(substring_index(substring_index(r.relation_sub_user_id,',',b.help_topic_id+1),',',-1),u.user_id) name,ifnull(length(r.relation_sub_user_id) - length(replace(r.relation_sub_user_id,',',''))+1,1)
		FROM sys_user u
		LEFT JOIN sys_user_relation r ON r.relation_user_id = u.user_id
		left join
		help_topic b
		on b.help_topic_id < (length(r.relation_sub_user_id) - length(replace(r.relation_sub_user_id,',',''))+1)
		WHERE u.user_id in (219,317,318)
7. 统计同一字段不同值的个数	（直接使用count()函数，count()函数可以使用表达式，也可以使用子查询嵌套，但是会慢很多，建议直接count()中使用case when判断）
		SELECT
			u.bn_user_org_id AS cropOrgId,
			u.bn_user_dept_name AS deptName,
			u.bn_user_worker_id AS userId,
			u.bn_user_worker_name AS userName,
			u.bn_user_material_name AS materialName,
			u.bn_user_material_batch_no AS materialBatchNo,
			CONCAT( u.bn_user_material_name, ' ', u.bn_user_material_batch_no ) AS materialNameAndBatchNo,
			u.bn_user_statistics_time AS statisticsTime,
			( SELECT item_bn_tube_type FROM busi_item WHERE item_id = u.bn_user_item_id AND is_delete = 0 ) AS highTempPipe,
			COUNT( CASE WHEN u.bn_user_worker_type = 4 THEN 1  ) * 0.5,
			COUNT( CASE WHEN u.bn_user_worker_type = 1 THEN 1  ) * 0.5,
			COUNT( CASE WHEN u.bn_user_worker_type = 11 THEN 1  ) * 0.5 
		FROM
			busi_bn_user u
			LEFT JOIN busi_bn_produce p ON u.bn_user_produce_id = p.bn_produce_id 
		WHERE
			u.is_delete = 0 
			AND p.is_delete = 0 
			AND p.bn_produce_status = 4 
			AND p.bn_produce_org_id = 225 
			AND u.bn_user_org_id = 225 
		GROUP BY
			u.bn_user_dept_name,
			u.bn_user_worker_id,
			date_format( u.bn_user_statistics_time, '%Y-%m-%d' ),
			u.bn_user_material_name,
			u.bn_user_material_batch_no 
		ORDER BY
			u.bn_user_statistics_time DESC,
			u.bn_user_worker_id DESC;

7.易错点

1.case when 统计数据
（1）第一种写法【错误】
	SELECT
	CASE WHEN
			inspect_custom_capacity IS NULL 
			THEN
				IFNULL( SUM( IFNULL( inspect_code_length, 0 ) - IFNULL( inspect_start_length, 0 ) ), 0 ) ELSE IFNULL( SUM( inspect_custom_capacity ), 0 ) 
			END inspectIndexCapacity,
		inspect_index inspectIndex,
		inspect_detail_user_id inspectDetailUserId 
		
		FROM  busi_inspect_detail
	WHERE
		inspect_detail_org_id = 102 
		AND inspect_detail_parent_id = 112
		AND inspect_detail_type = 2 
	GROUP BY
		inspect_index,
		inspect_detail_user_id
（2）第二种写法	【正确】
	SELECT
		SUM( CASE WHEN inspect_custom_capacity IS NULL THEN IFNULL( IFNULL( inspect_code_length, 0 ) - IFNULL( inspect_start_length, 0  ), 0 ) ELSE IFNULL( inspect_custom_capacity, 0 ) 
		END 
		) inspectIndexCapacity,
		inspect_index inspectIndex,
		inspect_detail_user_id inspectDetailUserId 
	FROM
		busi_inspect_detail 
	WHERE
		inspect_detail_org_id = 102 
		AND inspect_detail_parent_id = 112
		AND inspect_detail_type = 2 
	GROUP BY
		inspect_index,
		inspect_detail_user_id
tips:在外层SUM()时，会把分组了之后的数据进行求和，如果在内部SUM()时，数据可能是没分组的数据，所以数据可能不正确。