6 Summary of SQL statements for more useful MySQL database operations

In MySQL database operations, we often write some SQL statements to achieve the desired functionality. But for the novice MySQL database, this seems to have a certain degree of difficulty. In this article we have summed up six of the more useful SQL statements, beginners can apply the following format, then let us take a look at this part of the content.

1. Calculation of years

If you want to calculate the age of the person by birth, you can use the following statement:

SELECT Date_format (From_days to_days (now ())-To_days (@dateofbirth)), '%Y ') + 0;

2. Two difference in time

Gets the difference of two datetime values. Assuming that DT1 and DT2 are datetime types, and their format is ' Yyyy-mm-dd hh:mm:ss ', then the difference in seconds between them is: Unix_timestamp (DT2)-Unix_timestamp (DT1) divided by 60 is the bad The number of minutes divided by 3600 is the difference between the number of hours, divided by 24 is the difference in the number of days.

3. Show the value of n times a column appears

SELECT ID From TBL GROUP by ID Having COUNT (*) = N;

4. Calculation of working days between two days

The so-called working day is the addition of Saturday Sundays and holidays.

SELECT COUNT (*) From calendar WHERE d BETWEEN Start and Stop and DayOfWeek (d) not in (1,7) and holiday=0;

5. Find 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. See how big your number library 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;

An introduction to the more useful SQL statements about the MySQL database is here.