MySQL常用语句记录

模拟rownum

SELECT
	@rownum := @rownum + 1 rownum,
	ss.* 
FROM
	( 
		-- sql
	) ss,
( SELECT @rownum := 0 ) rn
-- 注：在sql字符串中，赋值需改为  @rownum \\:=

列转行

SELECT
	GROUP_CONCAT(
	DISTINCT ( id )) ids 
FROM
	(
	    -- sql
	) idList

模糊查询

concat(IFNULL(d.qybh,''), IFNULL(d.dwmc,'')) conditionstr
-- 注：cancat遇到null会自动停止，则需要IFNULL转为空串''

datetime时间转换

//  2019-03-26 09:42:05.0  ---->  2019-03-26 09:42:05
DATE_FORMAT(' ','%Y-%m-%d %T')

查询未结束事务并kill

select * from information_schema.innodb_trx;
kill {trx_mysql_thread_id}

update语句关联表

// 根据另一张表的数据关联修改表
UPDATE project_lib_plus p
INNER JOIN street s ON s.streetname LIKE concat( '%', substring( p.TOWNSHIP, 1, 2 ), '%' ) 
SET p.TOWNSHIP = s.streetcode 
WHERE
	p.YEAR = year1 
	AND p.MONTH = month1 
AND p.BATCHNO = batchno1;

存储过程游标+预处理语句

CREATE DEFINER=`root`@`%` PROCEDURE `project_in_lib_calc`( IN `YEAR1` INT, IN `MONTH1` INT )
BEGIN
	#Routine body goes here...
	
	DECLARE cname VARCHAR ( 50 ) DEFAULT ( '' );
	# 注： 获取字段类型为 double(15,2) 的金额字段 游标，并运算该字段
	# 定义游标
	DECLARE cursor_m CURSOR FOR ( SELECT column_name FROM information_schema.COLUMNS WHERE table_name = 'project_in_lib' AND table_schema = 'xmgl' AND data_type = 'double' and column_type='double(15,2)');
	# 游标执行到末尾时报错，设置判断条件为false
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cname = '';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET cname = '';
	
	START TRANSACTION;
	#开启游标
	OPEN cursor_m;
	    #获取游标的字段 column_name，插入到cname变量
		FETCH cursor_m INTO cname;
		WHILE cname <> '' DO
		    # 拼接预处理sql语句
			SET @_sql1 = concat(' UPDATE project_in_lib SET ', cname, ' = ', cname, ' * 10000 WHERE YEAR = ? AND MONTH = ? ');
			# 定义预处理语句
			PREPARE stmt FROM @_sql1;
			set @b = year1;
			set @c = month1;
			# 执行预处理语句
			EXECUTE stmt USING @b,@c;
			# 删除(释放)定义
			DEALLOCATE PREPARE stmt;
			
			# 获取下一条循环数据
			FETCH cursor_m INTO cname;
		END WHILE;
	CLOSE cursor_m;
	COMMIT;

END

获取表的字段

SELECT column_name FROM information_schema.COLUMNS 
WHERE table_name = 'project_in_lib' 
AND table_schema = 'xmgl' //数据库
AND data_type = 'double' 
AND column_type='double(15,2)'

分组查询 一个字段有多个值分别求和

// 先将字段以分隔符拆分为多行
SELECT
*,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.programtype, ',', b.help_topic_id + 1 ), ',',- 1 ) AS ids 
FROM
reportdetail AS a
JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.ids ) - length( REPLACE ( a.ids, ',', '' ) ) + 1 ) 

// 再进行group by
......

将从表多行拼接到主表查询结果

SELECT
	* 
FROM
	(
	SELECT
		s.xx
	FROM
		ssjg_spxx s
		LEFT JOIN ssjg_htxx h ON s.id_htxx = h.id
		LEFT JOIN ssjg_dw d ON s.id_dw = d.id
		LEFT JOIN ssjg_fpxx f ON s.id_fpxx = f.id 
	WHERE
		1 = 1 
	ORDER BY
		s.id 
	) sp left join   
	--此处拼接从表
	(
	SELECT
		sp.id_sp,
		GROUP_CONCAT( fp.fphm ) fphm  -- 列转行再分组查询
	FROM
		ssjg_sptofp sp,
		ssjg_fpxx fp 
	WHERE
		sp.id_fp = fp.id 
	GROUP BY
		id_sp   
	) sf on 
	sp.id = sf.id_sp