MySql日常使用不到的语句

MySql 查询表字段数

SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=‘test_cases’ AND table_name=‘cases_send’ ;
test_cases 为数据库名称
cases_send 为表名
列出表字段名称

Mysql（hive） 查询当月，某个小时之间的商户信息

获取表中4月每天1点到3点的商户号和商户名称：
hive版：
select merchant_no,merchant_name,count() cnt
from jh_all_data
where created_at >= ‘2023-04-01 00:00:00’
and hour(created_at) between 1 and 3
group by merchant_no,merchant_name
注意：其中created_at 为string类型
mysql版：
select merchant_no,merchant_name,count() cnt
from jh_all_data
where CREATED_AT >= DATE_FORMAT(20230401000000, ‘%Y-%m-%d %H:%i:%s’)
and hour(created_at) between 1 and 3
group by merchant_no,merchant_name

Mysql 获取当前数据库下有多少张表

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES
WHERE table_schema = ‘数据库名称’ GROUP BY table_schema;

Mysql 获取当前数据库下有多少张表名为 'T_%'开头

SELECT * FROM information_schema.TABLES
where table_schema = ‘数据库名称’ AND TABLE_NAME LIKE ‘T_%’