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

This article mainly introduces the commonly used MySQL functions, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand it.

Function

0. Show current time

Command: select now ().

Function: displays the current time.

Application scenario: default values such as creation time, modification time and so on.

Example:

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

1. Character length

Command: select char_length ('andyqan').

Function: displays the specified character length.

Application scenario: when viewing character length.

Example:

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

two。 Date formatting

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

Function: format the date.

Application scenario: when formatting a date.

Example:

Mysql > select date_format (now (),'% ymuri% mme% d') +-- + | date_format (now () '% yMel% mMel% d') | +-+ | 17-12-28 | +-- + 1 row in set (0.00 sec)

The formats supported here are:

% y: represents a year (double digits), for example: 17 years.

% Y: represents a 4-digit year, for example: 2017

% m: represents month (1-12)

% d: represents the day of the month

% H: hours (0-23)

% I: minutes (0-59)

% s: seconds (0-59)

Month, day, hour and second:% y-%m-%d% H:%i:%s

As follows:

Mysql > select DATE_FORMAT (now (),'% y-%m-%d% HRV% iRO% s') +-- + | DATE_FORMAT (now () '% y-%m-%d% HRV% iRV% s') | +-- + | 17-12-27 20:28:54 | +-- + 1 row in set (0.00 sec)

3. Add / decrease date time

Command:

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

Function: increase / decrease date time

Application scenario: the day before the current time, a few minutes ago. Often used for data statistics.

Example:

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

Where Date represents the date format, including:

2017-12-27 format such as now ().

Expr: indicates quantity.

Unit: represents a unit that supports milliseconds (microsecond), seconds (second), hours (hour), days (day), weeks (week), years (year), etc.

4. Type conversion

Command: CAST (expr AS type)

Function: mainly used for display type conversion

Application scenarios: display type conversion

Example:

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

It should be noted that type does not support all basic data types and supports detailed types. Please refer to the previous article "talking about MySQL display type conversion".

5. Encryption function

Command: md5 (data)

Function: used to encrypt data

Application scenarios: encryption, some private data, such as bank card number, ID card, etc., need to store ciphertext (of course, it is not recommended to use database layer encryption, it 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 clear text, you can use the database encryption algorithm to encrypt it at this time.

For example: (demonstration only):

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

The supported encryption functions are:

Md5 () des_encrypt (encryption) / des_decrypt (decryption); sha1 () password (), etc.

I will not introduce them one by one here. Students who are interested can go to the official website to learn more about it.

6. String concatenation

Command: concat (str,str2,str3)

Function: concatenating strings

Application scenarios: concatenate strings, such as adding specified strings to some fields.

Example:

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

This function is usually used a lot, and basically the scenario is to add a specific string to some data. The methods are as follows:

7. JSON function (only supported in version 5.7)

Command: json_object (function)

Function: convert json strings

Application scenario: specify data conversion json string

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)

These include 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 it is the json_valid () of the json string:

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

1 if it is a valid json string.

0. 0 if the json string is invalid.

There are many ways not to demonstrate them one by one.

8. Aggregate function

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

Function: statistics, average, maximum and minimum

Application scenario: this kind of function is very common and is mainly used for data statistics. It is also applicable to SQL optimization.

Example:

Mysql > select max (id) from tasking baseband username + | max (id) | +-+ | 2 | +-+ 1 row in set (0.00 sec)

Here's a tip: if the primary key is incremented in order, you can use max (id) instead of the count (*) function when it comes to how many users are needed.

9. Distinct ()

Command: distinct

Function: weight removal

Application scenario: when statistical type, status, and discrimination are needed.

Example:

Mysql > select count (distinct (name)) / count (*) from t_base_user +-- + | count (distinct (name)) / count (*) | +-+ | 0.6667 | +-+ 1 row in Set (0.00 sec) Thank you for reading this article carefully. I hope the article "what are the commonly used MySQL functions" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.