Detailed explanation of database _ MySQL: mysql function

1. Built-in functions

1. Mathematical functions

rand()	round(num)	ceil(num)	floor(num)
随机	4舍5入	向上取整	向下取整

2. String functions

length () byte length
char_length () character length

ucase () Capital
lcase () Lowercase

concat (character, …, character n) concatenation string

replace (String, Old Character, New Character) String Replacement

Intercept string
left (string, truncated length)
right (string, truncated length)
substring (string, start position, intercept length) # contains start position

mysql> select left('123456',4);
+------------------+
| left('123456',4) |
+------------------+
| 1234    |
+------------------+
1 row in set (0.00 sec)

mysql> select right('123456',4);
+-------------------+
| right('123456',4) |
+-------------------+
| 3456    |
+-------------------+
1 row in set (0.00 sec)

mysql> select substring('123456',2,4); 
+-------------------------+
| substring('123456',2,4) |
+-------------------------+
| 2345     |
+-------------------------+
1 row in set (0.00 sec)

3. Date function

now()	unix_timestamp()	from_unixtime()
当前时间	时间戳	格式化时间戳

mysql> select now();
+---------------------+
| now()    |
+---------------------+
| 2019-03-16 14:55:42 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|  1552719356 |
+------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1552719356);
+---------------------------+
| from_unixtime(1552719356) |
+---------------------------+
| 2019-03-16 14:55:56  |
+---------------------------+
1 row in set (0.00 sec)

year()	month()	day()	hour()	minute()	second()
年	月	日	时	分	秒

mysql> select 
 -> year(now()) as ' Year ',
 -> month(now()) as ' Month ',
 -> day(now()) as ' Day ',
 -> hour(now()) as ' Hour ',
 -> minute(now()) as ' Points ',
 -> second(now()) as ' Seconds ';
+------+------+------+------+------+------+
|  Year  |  Month  |  Day  |  Hour  |  Points  |  Seconds  |
+------+------+------+------+------+------+
| 2019 | 3 | 16 | 14 | 59 | 12 |
+------+------+------+------+------+------+

4. Encryption function

md5 (data)
password (data)

5. Conditional judgment function

1). Syntax: if (data, value 1, value 2) # Determines whether the specified data is true: true-value 1, false-value 2

mysql> select if(null,1,2);
+--------------+
| if(null,1,2) |
+--------------+
|   2 |
+--------------+
1 row in set (0.00 sec)

mysql> select if(1,0,2);
+-----------+
| if(1,0,2) |
+-----------+
|   0 |
+-----------+
1 row in set (0.00 sec)

2). Syntax: IFNULL (data, value 2) # Determines whether the specified data is null: null-value 2, non-null-itself

mysql> select ifnull(0,123);
+---------------+
| ifnull(0,123) |
+---------------+
|    0 |
+---------------+
1 row in set (0.00 sec)

mysql> select ifnull('a',123);
+-----------------+
| ifnull('a',123) |
+-----------------+
| a    |
+-----------------+
1 row in set (0.00 sec)

2. Custom functions

Syntax:

# Modify Terminator 
delimiter //
create function  Function name (parameter name)   Type ,..., Parameter name n  Type n )  returns  Return data type 
begin
#SQL Statement 
return  Return value ;
end //
delimiter ;

# Call 
select  Function name ();

Output "hello world" (function without parameters)

# Determine whether the function exists, and delete it if it exists 
drop function if exists f1;

delimiter //
create function f1() returns varchar(30)
begin
 return 'hello world';
end //
delimiter ;

select f1();

+-------------+
| f1()  |
+-------------+
| hello world |
+-------------+

Pass the sum of two integers (function with parameters)

drop function if exists f2;

delimiter //

create function f2(num1 int, num2 int) returns int
begin
  return num1 + num2;
end //
delimiter ;

select f2(8, 2);

+----------+
| f2(8, 2) |
+----------+
|  10 |
+----------+

3. Customize function-related syntax

Show all the functions: show function status\ G # output a lot

Delete function: drop function [if exists] function name;

4. Differences between stored procedures and functions

Stored procedures can return multiple values, while custom functions can only return one value

Stored procedures are executed independently, while functions are often used as part of other SQL statements