MySQL common operators and functions

Character functions
(1) CONCAT (): Character connection
SELECT CONCAT (' Imooc ', '-', ' MySQL ');//imooc-mysql
SELECT CONCAT (First_name,last_name) as FullName from test;
(2) Concat_ws (): Use the specified delimiter for character joins, (the first position specifies the delimiter, followed by the split content)
SELECT concat_ws ('% ', ' abc ', ' Def '); The first one is the specified delimiter;//abc%def
(3) format (): Numeric formatting
SELECT FORMAT (123560.75,2); 123,560.75; The second parameter, if 0 (rounded), displays an integer
(4) LOWER (): Convert lowercase
(5) UPPER (): Convert uppercase
SELECT UPPER (' MySQL ');//mysql
(6) Left (): Gets the character
(7) Right (): Get the character
SELECT left (' MYSQL ', 2); MY
SELECT LOWER (Left (' MYsql ', 2)); function nesting;//my
(8) Length (): Gets the string length
LENGTH (' MYSQL ');//6; add a space at the end
(9) LTRIM (): Empty the Front (left) space of the string
() RTRIM (): Empty space at the end of the string (right end)
TRIM (): Remove leading, trailing spaces, or specified characters
SELECT TRIM (' MySQL ');
SELECT TRIM (leading '? ') From '?? MYSQL??? '); Remove leading?
SELECT TRIM (TRAILING '? ') From '?? MYSQL??? '); Delete subsequent?
SELECT TRIM (BOTH '? ') From '?? MYSQL??? '); Deleted before and after, but cannot delete the middle?
() replace (): replace
SELECT REPLACE (' MY??? SQL ', '? ', "); Change the question mark to blank, or remove it?
SELECT REPLACE (' MY??? SQL ', '? ', ' ~ ~ '); Can you put n? Change to M combo symbol
SUBSTRING (): String intercept (starting from 1, programming may start from 0)
SELECT SUBSTRING (' MYSQL ', for each); Intercept 2 characters from the first position in a string my
SELECT SUBSTRING (' MYSQL ', 3); SQL starts at the 3rd position intercept to the end
SELECT SUBSTRING (' MYSQL ',-2); QL starts at the end of the 2nd position.
SELECT SUBSTRING (' MYSQL ', -3,-1); F, the length cannot be negative, but some programming languages are supported, and MySQL does not support
Like: Pattern match (percent semicolon% stands for any character; underscore _ for any character)
Example: "tom%"
SELECT * FROM Test WHERE first_name like '%1%% ' ESCAPE ' 1 '; Remove the compile after ' 1 ' to find the information containing '% ' symbol ' _ ' any one character

Numeric operator functions
SELECT ceil (3.01) ==>4/* have n.xx all n+1 into an entire rounding up
SELECT Floor (3.99) ==>3/* Take n.xx are N-Rounding rounding down
SELECT 3 DIV 4 ==> 0/* integer division, Fetch quotient
SELECT 3/4 ==>0.75/* Division
SELECT 2.1 MOD 2 ==>0.1/* Take remainder (modulo) divisor is an integer, decimal can be
SELECT% 2 ==>1/* Take remainder mod=%
4 Power operation of SELECT Power (3,4) ==>81/*3
SELECT ROUND (3.1415926,4) ==>3.1416/* Rounding, leaving 4 decimal places
SELECT TRUNCATE (123.89,1) ==>123.8/* Number of digits after the decimal point is intercepted
SELECT TRUNCATE (123.89,0) ==>123/* Intercept the position after the decimal point, 0 bits for the integer part
SELECT TRUNCATE (123.89,-1) ==>120/* intercept-1, remove the trailing value from the single-digit to replace with 0

comparison operators and functions
[NOT] Between ... And ... [NOT] within the range, the result returns 1 or 0.
For example: between and 16; 1.
[NOT] In () lists the value options.
For example: SELECT ten in (5,10,25); 1.
is [NOT] null: Only NULL is [NOT] NULL returns 1, other conditions (including ", 0) return 0.

Date-time functions
(1) SELECT now (); The current date time.
(2) SELECT curdate (); The current date.
(3) SELECT curtime (); The current time.
(4) SELECT date_add (' 2014-3-12 ', INTERVAL [-]365 Day); The 2015-03-12,day can also be replaced with week,month,year and so on. Date_add is not an increase in meaning, it is a change that can either be increased or subtracted.
(5) DATEDIFF (); The difference of the date.
DATEDIFF (' 2013-3-12 ', ' 2014-3-12 '); -365 Date Difference value.
(6) Date_format (); Date formatting.
SELECT date_format (' 2014-3-2 ', '%m%d%y '); 03/02/2014 ('% ' with leading 0 effect).

Information functions
SELECT connection_id (); ==>2/* Connection ID
SELECT DATABASE (); /* The currently open database
SELECT last_insert_id (); /* The last inserted record is written to multiple records at the same time to return only the ID of the first entry written. And you must have AutoNumber fields in the table.
DESC Tab_name; /* View database fields and properties to view table structure
SELECT USER (); /* Print Current user information
SELECT VERSION (); /* Print Current database information

Aggregation functions
Only one return value
AVG ()-Average
SELECT ROUND (AVG (Goods_price), 2) as Avg_price from Tdb_goods;
Count ()-Count
SELECT COUNT (goods_id) as counts from Tdb_goods;
Max ()-Maximum value
SELECT MAX (Goods_price) as counts from Tdb_goods;
MIN ()-Minimum value
SUM ()-sum
SELECT SUM (Goods_price) as counts from Tdb_goods;

Cryptographic functions
MD5 (): Information Digest algorithm
Select MD5 (' admin ');//Encrypt the admin. If the information in MySQL is intended for web development, use MD5 as much as possible
Password (): Cipher algorithm
Set Password=password (' 1236 ') modified the current login password to 1236

MySQL common operators and functions