常用SQL汇总

mysql递归搜索

WITH RECURSIVE cte AS (
  SELECT id, pid, name
  FROM permission
  WHERE id = 1
  UNION ALL
  SELECT t.id, t.pid, t.name
  FROM permission t
  JOIN cte ON t.pid = cte.id
)
SELECT id, pid, name
FROM cte 

获取一天24小时

SELECT @s :=@s+1 AS `sDate` FROM mysql.help_topic,(SELECT @s :=-1) temp WHERE @s < 23

获取指定日期范围内所有日期

SELECT DATE(DATE_ADD(#{startDate}, INTERVAL @s DAY ) ) AS `sDate`, @s :=@s+1 AS `index` FROM mysql.help_topic,(SELECT @s :=0) temp 
WHERE @s <= DATEDIFF(#{endDate},#{startDate})

未来七天，不包含当天

SELECT
	DATE_SUB( curdate( ), INTERVAL - 1 DAY ) day1 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 2 DAY ) day2 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 3 DAY ) day3 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 4 DAY ) day4 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 5 DAY ) day5 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 6 DAY ) day6 UNION
SELECT
	DATE_SUB( curdate( ), INTERVAL - 7 DAY ) day7;

分组后合并

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 

-- 分组后合并名称,默认分隔符为逗号","
select id,group_concat(name) from db_name group by id
|1 | 10,20,20|

-- 分组后合并名称,默认分隔符为逗号";"
select id,group_concat(name separator ";") from db_name group by id
|1 | 10;20;20|

-- 分组后合并名称,默认分隔符为逗号";"去重
select id,group_concat(distinct name separator";") from db_name group by id
|1 | 10;20;20|

-- 分组后合并名称,默认分隔符为逗号";"排序
select id,group_concat(name order by name desc separator";") from db_name group by id
|1 | 20;20;10|

查询当前数据库中所有的表和视图

SELECT table_name AS 'name', table_comment AS 'comment' FROM information_schema.TABLES 
WHERE table_schema = (SELECT DATABASE ());

查询指定表中字段

SELECT DISTINCT COLUMN_NAME AS 'field',COLUMN_COMMENT AS 'description' FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = #{tableName} AND table_schema = (SELECT DATABASE ())