Several SQL query statements commonly used in mysql Development

For more information about the SQL query statements commonly used in mysql development, see.

For more information about the SQL query statements commonly used in mysql development, see.

1. Calculate the age

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

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

2. Calculate the difference between the two dates

Calculate the difference between the minute, second, hour, and day of the two dates. If the format of dt1 and dt2 is 'yyyy-mm-dd hh: mm: ss ', the second difference between two dates is

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

3. Show the column values that appear N times

The Code is as follows:
SELECT id FROM tbl Group by id Having count (*) = N;

4. Calculate the workday between two dates

The simplest way to calculate the workday between two dates is a calendar table containing the dday section and the other that marks whether all the dates in a known year are off days, then the following query finds all workdays between Start and Stop.

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

The Code is as follows:
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;