Several SQL query statements commonly used in MySQL development

1. Calculating age

If you have a person's birthday and need to calculate the age of this person, replace the @dateofbirth in the following statement with a birthday

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

2. Calculate the difference of two dates

Calculates the difference between minutes, seconds, hours, and days for two dates, and if the format of DT1 and DT2 is ' Yyyy-mm-dd hh:mm:ss ', then the difference between the number of seconds between two dates is

The code is as follows	Copy Code
Unix_timestamp (DT2)-Unix_timestamp (DT1)

3. Show the value of the columns that appear n times

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

4. Calculation of workdays between two dates

The easiest way to calculate a working day between two dates is a calendar that contains a D-date column and another column that has a list of all days in a given year, and then the following query finds all the working days between start and stop two dates

The code is as follows	Copy Code
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 of a 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. Query the total space occupied by your database data

code is as follows	copy code
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 spaces (MB) ' from Information_schema.tables GROUP by Table_schema;