Six useful SQL statements for MySQL database operations

InMySQL databaseOperations, we often write someSQL statementTo implement the functions you 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

 
 

  
  
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 ; 
 
 

This is the introduction of useful SQL statements for MySQL databases. If you want to learn more about MySQL databases, please refer to the article http://database.51cto.com/mysql/, which will surely be useful to you!