学习笔记--Mysql操作总结

//取每天的最大时间,与最小时间

SELECT STR_TO_DATE(CONCAT(monDays,'08:00:00'),'%Y-%m-%d %H:%i:%s') AS BEGINTIME
    ,DATE_ADD(STR_TO_DATE(CONCAT(monDays,'23:59:59'),'%Y-%m-%d %H:%i:%s'), INTERVAL 5 HOUR) AS BEGINTIME
FROM (
    SELECT DATE_FORMAT(DATE_ADD('2019-08-01',INTERVAL t.help_topic_id DAY),'%Y-%m-%d') AS 'monDays'  
    FROM mysql.help_topic t 
    WHERE t.help_topic_id < DAY(LAST_DAY('2012-08-01'))
) contDay;

 

 

//判断非空及空串

SELECT IFNULL(IF(colName='',NULL,colName),0)

 

 

//查看mysql表空间大小

SELECT TABLE_NAME, CONCAT(TRUNCATE(data_length/1024/1024,2),' MB') AS data_size
       ,CONCAT(TRUNCATE(index_length/1024/1024,2),' MB') AS index_size
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'ecology'
GROUP BY TABLE_NAME 
ORDER BY data_length DESC