Common MySQL several SQL statements

1. Calculation of years

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

The code is as follows	Copy Code
SELECT Date_format (From_days to_days (now ())-To_days (@dateofbirth)), '%Y ') + 0;

2. The difference of two time obtains the difference of two datetime value.

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:

The code is as follows	Copy Code
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

The code is as follows	Copy Code
The SELECT ID from TBL GROUP by ID has COUNT (*) = N;

4. Calculating the working day of the two days is the so-called working day except for Saturday Sundays and holidays.

The code is as follows	Copy Code
SELECT COUNT (*) from the 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

The code is as follows	Copy Code
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

code is as follows	copy code
SELECT   &NBSP Table_schema as ' Db Name ', Round (Sum (data_length + index_length)/1024/1024, 3) A S ' Db Size (MB), Round (Sum (data_free)/1024/1024, 3) as ' Free spaces (MB) ' from information_schema.table s GROUP by Table_schema;