Shulou(Shulou.com)05/31 Report--

What are the commonly used SQL statements in MySQL database operation? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

In MySQL database operations, we often write some SQL statements to achieve the functions we want. But for beginners of MySQL database, it seems to have some difficulty. What are the following SQL statements for more useful MySQL database operations?

What are the more useful SQL statements for MySQL database operations

1. Calculated number of years

If you want to calculate the person's age by birthday, you can use the following statement:

SELECTDATE_FORMAT (FROM_DAYS (TO_DAYS (now ())-TO_DAYS (@ dateofbirth)),'% Y') + 0

two。 The difference between the two times

Gets the difference between two datetime values. Suppose dt1 and dt2 are of type datetime with the format of 'yyyy-mm-ddhh:mm:ss',. Then the difference between them in seconds is: UNIX_TIMESTAMP (dt2)-UNIX_TIMESTAMP (dt1) divided by 60 is the difference in minutes, divided by 3600 is the difference in hours, and divided by 24 is the difference in days.

3. Displays values that have appeared N times in a column

SELECTidFROMtblGROUPBYidHAVINGCOUNT (*) = N

4. Calculate the working day between two days

A working day means excluding Saturdays, Sundays and holidays.

SELECTCOUNT (*) FROMcalendarWHEREdBETWEENStartANDStopANDDAYOFWEEK (d) NOTIN (1m 7) ANDholiday=0

5. Look up the primary key in the table

SELECTk.column_nameFROMinformation_schema.table_constraintstJOINinformation_schema.key_column_usagekUSING (constraint_name,table_schema,table_name) WHEREt.constraint_type='PRIMARYKEY'ANDt.table_schema='db'ANDt.table_name=tbl'

6. Check how big your database is.

ELECTtable_schemaAS'DbName',Round (Sum (data_length+index_length) / 1024 MB 1024 FROMinformation_schema.tablesGROUPBYtable_schema 3) AS'DbSize (MB)', Round (Sum (data_free) / 1024 lime 1024 charge 3) AS'FreeSpace (MB) 'FROMinformation_schema.tablesGROUPBYtable_schema

About the MySQL database operation of the commonly used SQL statements which questions are shared here, I hope the above content can be of some help to you, if you still have a lot of doubts unsolved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.