Describes six useful MySQL SQL statements.

This article introduces six useful MySQL SQL statements. Many may use PHP to implement these functions.

1. Calculate the number of years

You figured out how old this person is on your birthday.

 
 

  
  
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 their format is 'yyyy-mm-dd hh: mm: ss'. The difference between them is:

 
 

  
  
UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ) 
 
 

Dividing by 60 is the number of minutes in the difference, dividing by 3600 is the number of hours in the difference, and dividing 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 ;