This article describes six useful SQL statements in the MySQL database. For Beginners, you can directly apply the following format to complete the corresponding functions, we hope this introduction will help you.
This article describes six useful SQL statements in the MySQL database. For Beginners, you can directly apply the following format to complete the corresponding functions, we hope this introduction will help you.
In MySQL database operations, we often write some SQL statements to implement the functions we want. 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
The Code is as follows:
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.
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. Search for the primary key in the 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. Check the size of your database.
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;
Here is an introduction to useful SQL statements of MySQL databases. If you want to learn more about MySQL databases, read the following articles :, I believe it will surely bring you some benefits!
