Java开发中常用sql语句

常用sql总结

一.查询类1.将用SQL查询出表中的的某个字段, 并将结果拼接成字符串2. MySQL查询表的所有列名(字段名)，用逗号拼接3. 查询出的结果按照日期(年月日),类型等分组 二. 增删改类1.多表联合修改

一.查询类

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