Mysql常用指令(持续更新)

1.获取表中字段数：

select count(*) from information_schema.columns where table_schema='databaseName' and table_name='tableName' 

2.按每天0-23个时段查询数据：

SELECT B.hourTime as hourTime,
IFNULL(A.flowRebate,0) as flowRebate,
IFNULL(A.beforeRebateMoney,0) as beforeRebateMoney,
IFNULL(A.afterRebateMoney,0)as afterRebateMoney,
IFNULL(A.discountMoney,0) as discountMoney from
(
SELECT 
HOUR(t.ExitTime)as hourTime,
COUNT(*) as flowRebate,
SUM(t.PDiscountToll) as beforeRebateMoney,
SUM(t.TotalToll) as afterRebateMoney,
SUM(t.PDiscountToll - t.TotalToll) as discountMoney
FROM
(select ExitTime,PDiscountToll,TotalToll FROM aexit2019070150004 
where ExitDate = 20190701 and ExitStation = 150004
)  as t GROUP BY hourTime) as A
right JOIN (select DISTINCT HOUR(EXITTIME) as hourTime FROM aexit2019070150004 order by HOUR(EXITTIME)) as B
on A.hourTime = B.hourTime  
ORDER BY B.hourTime;

3.更新数据库配置

flush privileges;

4.设置数据库远程访问

use mysql;
update user set host = "%" where user = "root";