Six useful SQL statement summaries for MySQL database operations

In MySQL database operations, we often write SQL statements to do what we want. But for beginners in the MySQL database this seems to be a bit of a challenge. In this article, we have summarized six useful SQL statements, which beginners can use in the following format. Let's take a look at this part in 1.
Count the years
If you want to calculate the person's age on their birthday, you can do this with the following statements:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
2. The difference between two times
Get the difference between the two datetime values. Assuming that dt1 and dt2 are of type datetime in the form 'yyyy-mm-dd hh:mm:ss', the number of seconds they differ is: UNIX_TIMESTAMP(dt2) -UNIX_TIMESTAMP (dt1) divided by 60 is the number of minutes they differ, divided by 3600 is the number of hours they differ, and divided by 24 is the number of days they differ.
3. Display the value of N in a column

 
SELECT id 
FROM tbl 
GROUP BY id 
HAVING COUNT(*) = N; 

4. Calculate the working days between the two days
The working day is Sunday except Saturday and holidays.

 
SELECT COUNT(*) 
FROM calendar 
WHERE d BETWEEN Start AND Stop 
AND DAYOFWEEK(d) NOT IN(1,7) 
AND holiday=0; 

5. Look up 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' 

Check how big your database is

 
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 ; 

SQL statement about MySQL database is useful introduction to here, if you want to learn more MySQL database knowledge, l can see the article here: / / www ofstack. com/list/list_112_1 htm, believe that one can bring you a harvest!