Several small tips statements in MySQL

Summary of small tips commonly used in six MySQL statements

1) For example, to calculate the current size of a person, assume that the variable of the person's birthdate is @ dateofbirth,

Then

Select date_format (from_days (to_days (now ()-to_days (@ dateofbirth), '% y') + 0;

2 Calculate the date difference

Assume that the two dates are in the format of yyyy-mm-dd hh: mm: SS, you can calculate the number of seconds of the difference.

Unix_timestamp (dt2)-unix_timestamp (dt1)

Then remove 60, 3600, * 24 to get the minute, hour, and day.

3. Calculate the number of times a column value appears.

Select ID from TBL group by ID having count (*) = N;

4. Search for the primary key of a 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'

5 view the size of a 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;