Six frequently-used MySQL queries _ MySQL

6 commonly used MySQL queries bitsCN.com

Six common MySQL queries

It is an art to query architectural art by using structured query languages to develop correct and effective database problems and commands. In a SELECT query, you can use JOIN, WHERE, and HAVING clauses to analyze and summarize results rows and columns, and UNION. For the INSERT, DELETE, and UPDATE commands, refer to the connections. INSERT the query results of INSERT... SELECT into another table. Delete the WHERE clause within the specified range.

1. age

If you know the date of birth, you need to calculate the person's age. Assume that your date of birth is 1986/05/25:

SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (NOW ()-TO_DAYS (19860525), '% Y') + 0;

2. differences between two dates

Find the difference between two date and time values in seconds, minutes, hours, or days. If dt1 and dt2 are datetime values in the form of "YYYY-MM-DD HH: MM: SS", the number of seconds between dt1 and dt2

UNIX_TIMESTAMP (1358498312)-UNIX_TIMESTAMP (517378271 );

UNIX_TIMESTAMP (dt2)-UNIX_TIMESTAMP (dt1)

Divide the number of minutes by 60, divided by 3600, and the number of hours of the day by 3600*24.

3. the column value is displayed N times.

SELECT id

FROM tbl

Group by id

Having count (*) = N;

4. calculate the number between two dates

The simplest supported workdays between any two dates are columns D and holiday Boolean filled all days in all possible year calendars. The following query will start and stop during the business days:

Select count (*)

FROM calendar

WHERE d BETWEEN Start AND Stop

And dayofweek (d) not in (1, 7)

AND holiday = 0;

5. search for tables with primary keys

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. How big is your database?

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;

BitsCN.com