Common mysql statements at work can be achieved without using php _ MySQL
Source: Internet
Author: User
Commonly used mysql statements at work can be shared without using php. bitsCN.com
1. calculate the number of years You figured out how old this person is on your birthday.
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;
BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.