MySQL's Common SQL statements

1, by the hour statistics statement

Select
Concat (Date_format (gmt_create, "%y-%m-%d%k:00~"), Hour (gmt_create) +1, ": xx") as ' time ',
COUNT (*) as Num
From T_order
where gmt_create>= ' 2016-03-06 00:00:00 ' and gmt_create<= ' 2016-03-06 23:59:59 '
Group by left (Gmt_create, 13);

Select
Concat (Date_format (gmt_create, "%y-%m-%d%k:00~"), Hour (gmt_create) +1, ": xx") as ' time ',
COUNT (*) as Num
From T_order
where gmt_create>= ' 2016-03-06 00:00:00 ' and gmt_create<= ' 2016-03-06 23:59:59 '
Group BY Date_format (A.gmt_create, '%y-%m-%d%h:00 ');

2, plus serial number
Select
(@rowNO: = @rowNo + 1) as RowNO,
Concat (Date_format (a.gmt_create, "%y-%m-%d%k:00~"), Hour (a.gmt_create) +1, ": xx") as ' time ',
COUNT (*) as Num
From T_order A, (select @rowNO: =0) b
where a.gmt_create>= ' 2016-03-06 00:00:00 ' and a.gmt_create<= ' 2016-03-06 23:59:59 '
Group BY Date_format (A.gmt_create, '%y-%m-%d%h:00 ');

3, the chain, is the comparison of the adjacent time period. such as: April 14 and March 14 is the adjacent time period, the two time periods of data comparison, is the chain.
Select Date_format (a.m_adddate, '%y-%m ') as time, COUNT (*) as ' month ',
(SELECT COUNT (*) from job_myreceive where Date_format (a.m_adddate, '%y%m ') = Date_format (Date_add (m_adddate,interval 1 month), '%y%m ') as last month
From Job_myreceive a GROUP by 1

4, yoy, refers to a time in the adjacent period of a similar point of comparison.
such as: 13 and 14 is the adjacent period, March 13 and March 14 is the same time point of the two adjacent periods, are March, the two time period to compare data, that is, the year.

5, the size of the view database, the result is in bytes, except 1024 is K, except 1048576 is M.
Select Table_schema ' Database name ', (SUM (data_length) +sum (index_length))/1048576 ' size (M) ' from Information_schema.tables where Table_schema= ' Xiancheng ';

6. View the size of the table, the result is in bytes, except that 1024 is K, except that 1048576 is M.
Select table_name ' table name ', (data_length+index_length)/1048576 ' size (M) ' from Information_schema.tables where table_schema= ' Xiancheng ';

MySQL's Common SQL statements