Java开发中常用sql语句

一.查询类

1.将用SQL查询出表中的的某个字段, 并将结果拼接成字符串

SELECT GROUP_CONCAT(id) as id from sys_menu;

2. MySQL查询表的所有列名(字段名)，用逗号拼接

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name'

3. 查询出的结果按照日期(年月日),类型等分组

SELECT
	dealer_id,
	page_type,
	DATE(create_time),
	count( id ) 
FROM
	cloud_filling_point 
	where dealer_id is not null
GROUP BY
	dealer_id,
	page_type,
	DATE(create_time)
	order by create_time,dealer_id asc

二. 增删改类

1.多表联合修改

update 表1 ,表2 set 表1.字段 = 表2.字段 where 表1.字段=表2.字段(两者的关联关系) and ....

SELECT
	* 
FROM
	cloud_user_dealer cud
	INNER JOIN cloud_user cu ON cud.phone = cu.userPhone 
WHERE
	cud.userId IS NULL 

• 例子

UPDATE cloud_user_dealer cud ,cloud_user cu
SET cud.userId = cu.id 
WHERE
cu.userPhone = cud.phone 
and
	cud.userId IS NULL

##或者使用下面这种
	UPDATE cloud_user_dealer cud 
		inner join cloud_user cu on cu.userPhone = cud.phone 
		SET cud.userId = cu.id 
		WHERE cud.userId IS NULL

多表联合修改使用inner join on

update 
	cloud_user cu
		INNER JOIN cloud_user_dealer cud ON cu.userPhone = cud.phone 
		set cu.userType = 3
	WHERE
		cu.userType = 0 
		AND cud.authFlag = 2