Functions in MySQL

Functions in MySQL include character function, numeric operator and function, comparison operator and function, date time function, information function, aggregate function, encryption function, etc.

1. Character function

//For example, when you need to output last name and first name together
 SELECT CONCAT(frist_name,last_name) AS fullname FROM test;

 SELECT FORMAT(12345.678,1);//12,345.7
 SELECT LEFT('mysql',2);//my

Leading space refers to the space before the first character, and subsequent space refers to the space after the last character.
Note: the string number in mysql starts with 1.

 SELECT SUBSTRING('mysql',1,2);//my, the first number represents the number of bits to intercept, and the second data represents the number of bits to intercept. In fact, the position can be negative.

SELECT * FROM tdb_goods WHERE goods_name LIKE '%Notebook%';//%Represents any character, and UU represents any character.

2. Numerical operators and functions

SELECT 3/4;// 0.7500
SELECT 3 DIV 4;//0
SELECT 5%3;//2
SELECT 5 MOD 3;//2
SELECT POWER(3,3)//27
SELECT TRUNCATE(123.456,2);//123.45

3. Comparison operators and functions

SELECT * FROM test WHERE first_name IS NULL;

4. Date time function

mysql> SELECT NOW(); //2018-02-28 10:37:55
mysql> SELECT CURTIME(); //10:38:03
mysql> SELECT CURDATE(); //2018-02-28

SELECT DATE_ADD('2014-3-2',INTERVAL 365 DAY); //2015-03-02 

SELECT DATEDIFF('2014-3-2','2015-03-02'); //-365
SELECT DATE_FORMAT('2014-3-12','%M/%D/%Y'); // 'March/12th/2014'

5. Information function

6. Aggregate function

A typical feature of aggregate functions is that there is only one return value.

7. Encryption function

What's the difference between MD5() and PASSWORD()? If the password information currently stored is for Web page preparation, MD5 () is recommended. Password () is mostly used to modify the password of MySQL client users.

SELECT MD5('admin');
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+

SET PASSWORD = PASSWORD('123456');//Change the login password of mysql to 123456.

8. Custom function

User defined function (UDF) is a way to extend MySQL. Its usage is the same as built-in function.

CREATE FUNCTION function_name 
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

Routine body refers to the body of a function. About function bodies:
(1) The function body is composed of legal SQL statements;
(2) The function body can be a simple select or insert statement;
(3) Use begin if the function body is a composite structure.. end statement;
(4) A composite structure can contain declaration, loop, and control structures.

//Create a function to format the current date
  CREATE FUNCTION f2() RETURNS VARCHAR(30)
    RETURN DATE_FORMAT(NOW(),'%Y year%m month%d day');
//Call function
SELECT f2();
+----------------+
| f2()           |
+----------------+
| 2018 February 28, 2010 |
+----------------+
//Create a function with parameters
CREATE FUNCTION f3(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    RETURNS FLOAT(10,2) UNSIGNED
    RETURN (num1+num2)/2;
 SELECT f3(8,9);
+---------+
| f3(8,9) |
+---------+
|    8.50 |
+---------+

You can modify the end character of mysql command through the delete statement.
Create a composite function as follows:

CREATE FUNCTION ADDUSER(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES (username);
RETURN LAST_INSERT_ID()
END