Share 6 useful MySQL statements

Today to introduce six more useful MySQL SQL statements, probably a lot of people through PHP to achieve these functions.

1. Calculation of years

You want to figure out how old this person is by birthdays.

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

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.

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

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 database is

SELECT

Table_schema as ' Db Name ',

Round (Sum (data_length + index_length)/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;

I hope it will help you.