Common Mysql Statements in the work to share without PHP can also achieve the effect of _mysql

1. Calculation of years
You want to figure out how old this person is by birthdays.

Copy Code code as follows:

SELECT Date_format (From_days to_days (now ())-To_days (@dateofbirth)), '%Y ') + 0;

2. Two difference in time
Gets the difference of two datetime values. Assuming that DT1 and DT2 are datetime types, and their format is ' Yyyy-mm-dd hh:mm:ss ', the number of seconds between them is:
Unix_timestamp (DT2)-Unix_timestamp (DT1) divided by 60 is the difference in the number of minutes, divided by 3600 is the difference in the number of hours, and then divided by 24 is the difference in the number of days.
3. Show the value of n times a column appears

Copy Code code as follows:

SELECT ID
From TBL
GROUP by ID
Having COUNT (*) = N;

4. Calculation of working days between two days
The so-called working day is the addition of Saturday Sundays and holidays.

Copy Code code as follows:

SELECT COUNT (*)
From calendar
WHERE d BETWEEN Start and Stop
and DayOfWeek (d) not in (1,7)
and holiday=0;

5. Find the primary key in the table

Copy Code code as follows:

SELECT K.column_name
From Information_schema.table_constraints t
JOIN Information_schema.key_column_usage K
USING (Constraint_name,table_schema,table_name)
WHERE t.constraint_type= ' PRIMARY KEY '
and t.table_schema= ' DB '
and T.table_name=tbl '

6. See how big your number library is

Copy Code code as follows:

elect
Table_schema as ' Db Name ',
Round (Sum (data_length + index_l Ength)/1024/1024, 3 as ' Db Size (MB) ',
Round (Sum (data_free)/1024/1024, 3) as ' free space (MB) '
From Information_schema.tables
GROUP by Table_schema;