Commonly used mysql SQL statements

The following describes how to calculate the birthday and the two time difference. It shows the value of a column that has appeared N times. If you want to check the size of your database and other commonly used SQL operations, you can refer to it.

1. Calculate the number of years

You figured out how old this person is on your birthday.

The Code is as follows:	Copy code
SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (now ()-TO_DAYS (@ dateofbirth), '% y') + 0;

2. The difference between the two datetime values is obtained.

Assume that dt1 and dt2 are of the datetime type, and their format is 'yyyy-mm-dd hh: mm: ss'. The difference between them is:

The Code is as follows:	Copy code
UNIX_TIMESTAMP (dt2)-UNIX_TIMESTAMP (dt1)

　　
Dividing by 60 is the number of minutes in the difference, dividing by 3600 is the number of hours in the difference, and dividing by 24 is the number of days in the difference.

3. display the value of a column that appears N times

The Code is as follows:	Copy code
SELECT id FROM tbl group by id having count (*) = N;

4. Calculate the working days between two days.

The Code is as follows:	Copy code
Select count (*) FROM calendar WHERE d BETWEEN Start AND Stop and dayofweek (d) not in (1, 7) holiday AND = 0;

5. Search for 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. Check the size of your database.

The 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 Space (MB )' FROM information_schema.tables group by table_schema;