Describes six useful MySQL SQL statements.

This article introduces six useful MySQL SQL statements. Many may use PHP to implement these functions. 1. Calculate the number of years. You can calculate the number of years of this person on your birthday. SELECTDATE_FORMAT (FROM_DAYS (TO_DAYS (now ())

This article introduces six useful MySQL SQL statements. Many may use PHP to implement these functions. 1. Calculate the number of years. You can calculate the number of years of this person on your birthday. SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (now ()-TO_DAYS (@ dateofbirth), % Y) + 0; 2. Get two datetim due to the difference of two times

This article introduces six useful MySQL SQL statements. Many may use PHP to implement these functions.

1. Calculate the number of years

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

SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (now ()-TO_DAYS (@ dateofbirth), '% y') + 0;

2. Difference between two time points

Returns the difference between two datetime values. 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:

UNIX_TIMESTAMP (dt2)-UNIX_TIMESTAMP (dt1) divided by 60 is the number of minutes in the difference, divided by 3600 is the number of hours in the difference, and then divided by 24 is the number of days in the difference.

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

SELECT id

FROM tbl

Group by id

Having count (*) = N;

4. Calculate the workdays between two days

Workdays are divided into Saturday and Sunday holidays.

Select count (*)

FROM calendar

WHERE d BETWEEN Start AND Stop

And dayofweek (d) not in (1, 7)

AND holiday = 0;

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

ELECT

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;

From: China IT lab