Six useful SQL statements for MySQL database operations-MySQL
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
Summary of six useful SQL statements for MySQL database operations bitsCN.com in MySQL database operations, we often write some SQL statements to implement the desired functions. However, it seems difficult for beginners of MySQL databases. This article summarizes six useful SQL statements. beginners can use the following format. let's take a look at this part.
1. calculate the number of years If you want to calculate the person's age by birthday, you can use the following statement:
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 are in the format of 'yyyy-mm-dd hh: mm: SS'. The difference between them is in the number of seconds: 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
Group by id
Having count (*) = N;
4. calculate the workdays between two days Workdays are divided into Saturday and Sunday holidays.
Select count (*)
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.
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 )'
Group by table_schema;
This is the introduction of useful SQL statements for MySQL databases. if you want to learn more about MySQL databases, you can refer to the article http://www.bitscn.com/list/list_assist_1.htm, which will surely bring you the opportunity! BitsCN.com
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to email@example.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
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:
and provide relevant evidence. A staff member will contact you within 5 working days.