MYSQL常用高级sql

表定义查询：

-- 表字段分析
select column_name, column_comment from information_schema.columns where table_name = 'deb_userparam';

/* 查询数据库表注释 */
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_name = 'deb_userparam';

/* 查询数据库表字段注释 */
SELECT 
COLUMN_NAME,column_comment 
FROM INFORMATION_SCHEMA.Columns 
WHERE table_name='deb_userparam';
 
/* 查询数据库 表注释以及对应表字段注释 */
SELECT 
t.TABLE_NAME,
t.TABLE_COMMENT,
c.COLUMN_NAME,
c.COLUMN_TYPE,
c.COLUMN_COMMENT 
FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c 
WHERE c.TABLE_NAME=t.TABLE_NAME 
AND t.TABLE_NAME='deb_userparam' 
AND t.TABLE_SCHEMA = 'reda_econ';

-- 查询当前库中所有表
select 
table_name AS check_table,
table_schema AS data_base 
from information_schema.tables 
where table_schema=(select database()) 
and table_type='base table';

-- 查询当前库中所有字段名称和字段类型等等
SELECT 
table_schema AS data_base,
table_name,
COLUMN_NAME AS field,
COLUMN_COMMENT,
COLUMN_DEFAULT,
COLUMN_TYPE,
COLUMN_KEY,
CASE 
WHEN NUMERIC_PRECISION >= 0 THEN '1'  -- 数值型
WHEN DATETIME_PRECISION >= 0 THEN '2' -- 日期
WHEN CHARACTER_MAXIMUM_LENGTH >= 0 THEN '3' -- 字符型
ELSE '2'
END AS field_type
FROM information_schema.`COLUMNS` 
WHERE table_schema=(select database()) 
ORDER BY TABLE_NAME;

-- 获取当前库所有表中所有varchar类型的字段，字符集改为utf8mb4，排序规则改成utf8mb4_general_ci
SELECT 
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE,
'(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),' COMMENT \'', COLUMN_COMMENT , '\' ;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = (SELECT database())
AND DATA_TYPE = 'varchar';
-- AND CHARACTER_SET_NAME = 'utf8mb4';

-- 获取当前库中所有表，修改默认字符集为utf8mb4，排序规则为utf8mb4_general_ci
SELECT 
CONCAT('ALTER TABLE `', table_name, '` character SET utf8mb4 COLLATE utf8mb4_general_ci', ';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = (SELECT database());

正则匹配：

-- 以下两条sql实现功能一样，para_label格式：aaa,bb,cc,dd,ee,f
-- find_in_set 
SELECT 
para_label,
para_id 
FROM econ_para_flight 
WHERE 
FIND_IN_SET('aaa',para_label) 
OR FIND_IN_SET('bb',para_label) 
OR FIND_IN_SET('cc',para_label);

-- 正则匹配
SELECT 
para_label,
para_id 
FROM econ_para_flight 
WHERE 
para_label REGEXP 'aaa|bb|cc'

时间函数：

-- 查询当前 日期和时间,日期,时间
SELECT NOW(),CURRENT_DATE(),CURRENT_TIME();

-- DATE(date) 函数返回日期部分
SELECT DATE('2020-06-01 20:59:59')；

-- EXTRACT(unit FROM date) 函数用于返回日期/时间的单独部分 
-- unit参数常用值：SECOND MINUTE HOUR DAY MONTH YEAR
SELECT EXTRACT(YEAR FROM '2020-06-01'),EXTRACT(SECOND FROM '2020-06-01 20:59:59');

-- 使用DATEDIFF(date1,date2) 判断两个时间相差天数（1.date1-date2 2.只有年月日部分参与计算）
select DATEDIFF('2018-03-22 23:59:59', '2018-03-24 00:00:00');

-- 查询两个时间相差多少秒
select LAND_TIME,TAKEOFF_TIME, TIMESTAMPDIFF(SECOND,TAKEOFF_TIME,LAND_TIME) from der_certain_flight LIMIT 100;

-- 使用 DATE_FORMAT(date,format) 格式化日期

-- 使用 FROM_UNIXTIME(unix_timestamp,format) 格式化时间戳
select FROM_UNIXTIME(3600*24),FROM_UNIXTIME(3600*25),FROM_UNIXTIME(3600*25,'%Y-%m-%d') from dual;

-- 使用 DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。
-- type参数常用值：SECOND MINUTE HOUR DAY MONTH YEAR
select NOW() from dual;
select DATE_SUB(NOW(), INTERVAL 2 HOUR) from dual;

-- 使用 DATE_FORMAT(date,format)格式化时间
select DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') from dual;
select DATE_FORMAT(NOW(), '%Y-%m-%d') from dual;

SELECT a.ACT_LONG,date_format(date_sub(from_unixtime(a.t), INTERVAL 8 HOUR), '%Y-%m-%d %H:%i:%s')
FROM (select ACT_LONG, SUM(TIMESTAMPDIFF(SECOND,TAKEOFF_TIME,LAND_TIME)) AS t from der_certain_flight GROUP BY ACT_LONG) a;

select FROM_UNIXTIME(a.t,'%Y-%m-%d %h:%i:%s') 
from (select ACT_LONG, SUM(TIMESTAMPDIFF(SECOND,TAKEOFF_TIME,LAND_TIME)) AS t from der_certain_flight GROUP BY ACT_LONG) a;

SELECT 
a.ACT_LONG,
a.t,
CAST(a.t/86400 AS INTEGER) day,
CAST(a.t  % 86400 / 3600 AS INTEGER) hour,
CAST(a.t  % 86400 % 3600 / 60 AS INTEGER) minute,
CAST(a.t  % 86400 % 3600 % 60 AS INTEGER) second
FROM (select ACT_LONG, SUM(TIMESTAMPDIFF(SECOND,TAKEOFF_TIME,LAND_TIME)) AS t from der_certain_flight GROUP BY ACT_LONG) a;

-- 显示当前库时区（UTC:世界标准时）
show variables like '%time_zone%';
-- 仅修改当前会话的时区，停止会话失效
set time_zone = '+8:00';
-- 修改全局的时区配置（需要高级权限）
set global time_zone = '+8:00';
flush privileges;

获取最新生效的数据：

-- 月度平均价格算法
SELECT
	avg( t.price ) 
FROM
	(
	SELECT
		airport,
		SUBSTRING_INDEX( group_concat( fuel_price ORDER BY effect_date DESC ), ',', 1 ) price 
	FROM
		econ_cost_fuel 
	WHERE
		effect_date <= '' 
	GROUP BY
	airport 
	)t;

验证表，字段，索引是否存在（批量）

-- 验证表
SELECT
	(
	SELECT
	IF ( count( 1 ) > 0, 'pass', 'fail' ) 
	FROM information_schema.TABLES 
	WHERE 
		table_name = 'sys_dict' 
		AND table_schema = (SELECT DATABASE())
	) AS 'sys_dict' 
FROM DUAL;

-- 验证字段
SELECT
	(
	SELECT
	IF ( count( 1 ) > 0, 'pass', 'fail' ) 
	FROM information_schema.COLUMNS 
	WHERE
		table_name = 'sys_dict' 
		AND column_name = 'id' 
		AND table_schema = (SELECT DATABASE())
	) AS 'id' 
FROM DUAL;

-- 验证索引
SELECT
	(
	SELECT
	IF ( count( 1 ) > 0, 'pass', 'fail' ) 
	FROM information_schema.STATISTICS 
	WHERE
		table_name = 'sys_dict' 
		AND Index_name = 'IK_ID' 
		AND table_schema = (SELECT DATABASE())
	) AS 'IK_ID' 
FROM DUAL

-- 验证字段各种属性是否一致
SELECT IF ( count( 1 ) > 0, 'pass', 'fail' )
FROM information_schema.COLUMNS
WHERE table_schema = (SELECT DATABASE())
	AND table_name = 'econ_param_me'
	AND column_name = 'apu_time_on_pre_m' 
	AND column_default = 'NUll'
	AND is_nullable = 'YES'
	AND column_type = 'datetime'
	AND column_comment = '说明信息'