Useful MySQL Statements

Excerpted from onefish database

1. Calculate the number of years
You want to count the age of the person by birthday.

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

2. Two time difference
Gets the difference of two datetime values. Assuming that DT1 and DT2 are datetime types with the format ' Yyyy-mm-dd hh:mm:ss ', the number of seconds between them is:

Unix_timestamp (DT2)-Unix_timestamp (DT1)

Dividing by 60 is the number of minutes that are bad, divided by 3600 is the number of hours that are bad, divided by 24 is the number of days that are bad.

3. Show values for a column that have occurred n times

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

4. Calculate working days between two days
The so-called weekday 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 digital library 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;

Useful MySQL Statements