Today, we will introduce 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)
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
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.
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;
Hope to help you.