Ten practical and simple MySQL functions and ten mysql Functions

Ten practical and simple MySQL functions and ten mysql Functions

Function

0. display the current time

Command: select now ().

Purpose: display the current time.

Use Cases: default values such as creation time and modification time.

Example:

mysql> select now();+---------------------+| now()  |+---------------------+| 2017-12-27 20:14:56 |+---------------------+1 row in set (0.00 sec)

1. Character Length

Command: select char_length ('andyqanc ').

Purpose: display the length of a specified character.

Application Scenario: when viewing the character length.

Example:

mysql> select char_length('andyqian');+-------------------------+| char_length('andyqian') |+-------------------------+|   8 |+-------------------------+1 row in set (0.00 sec)

2. Date formatting

Command: select date_format (now (), '% y-% m-% d ).

Purpose: format the date.

Application Scenario: When formatting a date.

Example:

mysql> select date_format(now(),'%y-%m-%d');+-------------------------------+| date_format(now(),'%y-%m-%d') |+-------------------------------+| 17-12-28   |+-------------------------------+1 row in set (0.00 sec)

The supported formats include:

% Y: year (two digits), for example, 17 years.
% Y: indicates the year in four digits, for example, January 1, 2017.
% M: Month (1-12)
% D: indicates the day in the month.
% H: hour (0-23)
% I: minute (0-59)
% S: seconds (0-59)

Year, month, day, hour, minute, second: % y-% m-% d % H: % I: % s,

As follows:

mysql> select DATE_FORMAT(now(),'%y-%m-%d %H:%i:%s');+----------------------------------------+| DATE_FORMAT(now(),'%y-%m-%d %H:%i:%s') |+----------------------------------------+| 17-12-27 20:28:54   |+----------------------------------------+1 row in set (0.00 sec)

3. Add/Remove Date and Time

Command:

DATE_ADD(date,interval expr unit)DATE_SUB(date,interval expr unit)

Purpose: increase/decrease the date and time

Application Scenario: the day before the current time, a few minutes before. It is often used for data statistics.

Example:

mysql> select date_add(now(),interval 1 day);+--------------------------------+| date_add(now(),interval 1 day) |+--------------------------------+| 2017-12-28 20:10:17  |+--------------------------------+1 row in set (0.00 sec)

Date indicates the Date format, which includes:

, Now () and other formats.

Expr: indicates the quantity.

Unit: indicates the unit, which can be Millisecond (microsecond), second (second), hour (hour), day (day), week (week), and year (year.

4. type conversion

Command: CAST (expr AS type)

Role: Mainly used for display type conversion

Application Scenario: display type conversion

Example:

mysql> select cast(18700000000 as char);+---------------------------+| cast(18700000000 as char) |+---------------------------+| 18700000000  |+---------------------------+1 row in set (0.00 sec)

Note that not all basic data types are supported by type. For details about supported data types, see the previous article "MySQL display type conversion".

5. encryption functions

Command: md5 (data)

Role: used to encrypt data

Application Scenario: encryption. Some private data, such as bank card numbers and ID cards, must be stored in ciphertext. (Of course, database-layer encryption is not recommended and should be encrypted at the application layer)

Example:

mysql> select md5("andyqian");+----------------------------------+| md5("andyqian")   |+----------------------------------+| 8a6f60827608e7f1ae29d1abcecffc3a |+----------------------------------+1 row in set (0.00 sec)

Note: If the data in your database is still in plain text, you can use the database encryption algorithm to encrypt it.

For example: (for demonstration only ):

update t_base_user set name=md5(name),updated_time=now() where id=1;

Supported encryption functions include:

Md5 () des_encrypt (encrypted)/des_decrypt (decrypted); sha1 () password () and so on

I will not describe them here. If you are interested, you can go to the official website to learn more.

6. String connection

Command: concat (str, str2, str3)

Purpose: concatenate a string

Application Scenario: concatenate a string, for example, adding a specified string to a field.

Example:

mysql> select concat("andy","qian");+-----------------------+| concat("andy","qian") |+-----------------------+| andyqian  |+-----------------------+1 row in set (0.00 sec)

This function is usually used in many cases. Basically, the scenario is to add specific strings to some data. The method is as follows:

7. JSON functions (supported in version 5.7)

Command: json_object (function)

Purpose: convert a json string.

Application Scenario: Specify the json string for Data Conversion

Example:

mysql> select json_object("name","andyqian","database","MySQL");+---------------------------------------------------+| json_object("name","andyqian","database","MySQL") |+---------------------------------------------------+| {"name": "andyqian", "database": "MySQL"}  |+---------------------------------------------------+1 row in set (0.00 sec)

Including json_array:

mysql> select json_array("name","andyqian","database","MySQL");+--------------------------------------------------+| json_array("name","andyqian","database","MySQL") |+--------------------------------------------------+| ["name", "andyqian", "database", "MySQL"] |+--------------------------------------------------+1 row in set (0.00 sec)

Determine whether the json_valid () is a json string ():

select json_valid('{"name": "andyqian", "database": "MySQL"}');

The valid json string is 1.

The value of json string is 0.

There are still many ways to do this.

8. Aggregate functions

Command: sum (), count (), avg (), max (), min ()

Purpose: calculate the average value, maximum value, and minimum value.

Application Scenario: This type of function is very common and mainly used for data statistics. It is also applicable to SQL optimization.

Example:

mysql> select max(id) from t_base_user;+---------+| max(id) |+---------+| 2 |+---------+1 row in set (0.00 sec)

Here is a tip. If the primary key is sequentially incrementing, you can use max (id) to replace the count (*) function when the number of users is required.

9. distinct ()

Command: distinct

Role: deduplication

Application Scenario: Statistical type, status, and computing discrimination are required.

Example:

mysql> select count(distinct(name))/count(*) from t_base_user;+--------------------------------+| count(distinct(name))/count(*) |+--------------------------------+|    0.6667 |+--------------------------------+1 row in set (0.00 sec)

Summary

The above is a series of ten practical and simple MySQL functions introduced by xiaobian. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!