1, Database function

1. Mathematical function

The records stored in the database often need a series of arithmetic operations, so MySQL supports many mathematical functions.

• Common mathematical functions

Mathematical function	describe
abs (x)	Returns the absolute value of x; The absolute value is always positive, and the absolute value of 0 is 0
rand ()	Returns a random number from 0 to 1
mod(x, y)	Returns the remainder of x divided by y
power (X,y)	Returns the y power of x
round(x)	Returns the integer closest to x
round(x, y)	Keep the y decimal places of x and the value after rounding
sqrt (x)	Returns the square root of x
truncate(x,y)	Returns the value of the number x truncated to y decimal places
ceil (x)	Returns the smallest integer greater than or equal to x
floor (x)	Returns the largest integer less than or equal to x
greatest. (x1 2...)	Returns the largest value in the collection
least (x1, x2...)	Returns the smallest value in the collection

• case

#Returns the absolute value of - 2
mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#A random number of 0-1 (0 < = x < 1) returns a random number from 0 to 1
mysql> select rand();
+-------------------+
| rand()            |
+-------------------+
| 0.937660732248482 |
+-------------------+
1 row in set (0.00 sec)

#You can use operators to return random numbers from 0 to 100
mysql> select rand()*100;
+-------------------+
| rand()*100        |
+-------------------+
| 59.43208174727119 |
+-------------------+
1 row in set (0.00 sec)
------------------------------------------------------------------------------------------------
#The remainder of 5 divided by 2 returns the remainder after x divided by y
mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#the cube of 2
mysql> select power(2,3);
+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#Integer nearest 1.49, rounded
mysql> select round(1.49);
+-------------+
| round(1.49) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

#Integer nearest 1.5
mysql> select round(1.5);
+------------+
| round(1.5) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

#1.893 retains 2 digits after the decimal point, and 1.896 retains 2 digits after the decimal point, which will be rounded here
mysql> select round(1.893,2);
+----------------+
| round(1.893,2) |
+----------------+
|           1.89 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(1.896,2);
+----------------+
| round(1.896,2) |
+----------------+
|           1.90 |
+----------------+
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------------
#Return square root
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#Two decimal places are reserved, but the truncate function does not round (truncate)
mysql> select truncate(1.896,2);
+-------------------+
| truncate(1.896,2) |
+-------------------+
|              1.89 |
+-------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#Returns the smallest integer greater than or equal to 5.2
mysql> select ceil(5.2);
+-----------+
| ceil(5.2) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

#Returns the largest integer less than or equal to 5.2
mysql> select floor(5.2);
+------------+
| floor(5.2) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

#Returns the maximum value
mysql> select greatest(1,2,3);
+-----------------+
| greatest(1,2,3) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

#Returns the minimum value
mysql> select least(1,2,3);
+--------------+
| least(1,2,3) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

2. Aggregate function

There is a special group of functions in MySQL database functions, which are specially designed for summation of records in the database or centralized generalization of data in tables. These functions are called aggregate functions.

Aggregate function	describe
avg()	Returns the average value of the specified column
count()	Returns the number of non NULL values in the specified column
min()	Returns the minimum value of the specified column
max()	Returns the maximum value of the specified column
sum(x)	Returns the sum of all values for the specified column

#Returns the sum of scores
mysql> select sum(score) from info;
+------------+
| sum(score) |
+------------+
|     638.00 |
+------------+
1 row in set (0.00 sec)

#Returns the number of score fields
mysql> select min(score) from info;
+------------+
| min(score) |
+------------+
|      10.00 |
+------------+
1 row in set (0.00 sec)

#Returns the maximum value of the score
mysql> select max(score) from info;
+------------+
| max(score) |
+------------+
|     100.00 |
+------------+
1 row in set (0.00 sec)

#Returns the average of the scores
mysql> select avg(score) from info;
+------------+
| avg(score) |
+------------+
|  53.166667 |
+------------+
1 row in set (0.00 sec)

3. String function

• Common string functions

function	describe
length(x)	Returns the length of the string x
trim()	Returns a value in the specified format
concal (x,y)	Splice the supplied parameters x and y into a string
upper (x)	Turns all letters of string x into uppercase letters
lower (x)	Turns all letters of string x into lowercase letters
left (x,y)	Returns the first y characters of string x
right (x, y)	Returns the last y characters of string x
repeat (x,y)	Repeat the string x y times
space (x)	Returns x spaces
replace(x, y, z)	Replaces string y in string x with string z
strcmp(x, y)	Compare x and y, less than - 1, equal to 0, greater than 1, and compare the first different number
substring (x,y,z)	Gets a string of length z starting from the y-th position in string x
reverse (x)	Invert string x

#Returns the length of abcd, and spaces count as one character
mysql> select length('abcd');
+----------------+
| length('abcd') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select length('ab cd');
+-----------------+
| length('ab cd') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)
------------------------------------------------------------------------------------------------
#Returns a formatted value
mysql> select trim('  sheng');
+-----------------+
| trim('  sheng') |
+-----------------+
| sheng           |
+-----------------+
1 row in set (0.00 sec)

mysql> select '  sheng';
+---------+
| sheng   |
+---------+
|   sheng |
+---------+
1 row in set (0.00 sec)
------------------------------------------------------------------------------------------------
#concat(x,y) splices the supplied parameters X and Y into a string
mysql> select concat('abc','def');
+---------------------+
| concat('abc','def') |
+---------------------+
| abcdef              |
+---------------------+
1 row in set (0.00 sec)

mysql> select concat('abc',' def');
+----------------------+
| concat('abc',' def') |
+----------------------+
| abc def              |
+----------------------+
1 row in set (0.00 sec)

#Combine with other functions, such as trim (remove the format of the following functions)
mysql> select concat('abc',trim(' def'));
+----------------------------+
| concat('abc',trim(' def')) |
+----------------------------+
| abcdef                     |
+----------------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#upper(x) turns all letters of string x into uppercase letters
mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC          |
+--------------+
1 row in set (0.00 sec)

#lower(x) turns all letters of string x into lowercase letters
mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc          |
+--------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#left(x,y), returns the first y characters of string X
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc               |
+-------------------+
1 row in set (0.00 sec)

#left(x,y), returns the last y characters of string X
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg                |
+--------------------+
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------------
#Splice the first three letters and the last three letters of the string
mysql> select concat(left('abcdefg',3),right('abcdefg',3));
+----------------------------------------------+
| concat(left('abcdefg',3),right('abcdefg',3)) |
+----------------------------------------------+
| abcefg                                       |
+----------------------------------------------+
1 row in set (0.00 sec)

#repeat(x,y) repeats the string x y times
mysql> select repeat('abc',2);
+-----------------+
| repeat('abc',2) |
+-----------------+
| abcabc          |
+-----------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#space(x) returns x spaces
mysql> select length(space(3));
+------------------+
| length(space(3)) |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

-------------------------------------------------------------------------------------------------
#replace(x,y,z) 	 Replaces string y in string x with string Z
mysql> select replace('hello','ll','aa');
+----------------------------+
| replace('hello','ll','aa') |
+----------------------------+
| heaao                      |
+----------------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#strcmp(x,y) 	 Comparing X and y, the returned value can be - 1,0,1
 Compare 17 and 18, less than return-1，If it is equal to 0 and greater than 1, only these three values will be returned. It is the first different number to compare
mysql> select strcmp(17,18);
+---------------+
| strcmp(17,18) |
+---------------+
|            -1 |
+---------------+
1 row in set (0.00 sec)

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

mysql> select strcmp(19,18);
+---------------+
| strcmp(19,18) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#substring(x,y,z) 	 Gets a string of length Z starting from the y-th position in string X
 Returns 4 characters starting from the third character in a string
mysql> select substring('abcdefg',3,4);
+--------------------------+
| substring('abcdefg',3,4) |
+--------------------------+
| cdef                     |
+--------------------------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------
#reverse(x) 	 Invert string x
mysql> select reverse('gfedcba');
+--------------------+
| reverse('gfedcba') |
+--------------------+
| abcdefg            |
+--------------------+
1 row in set (0.00 sec)

#Returns the first three characters of a string and inverts the output
mysql> select reverse(left('gfedcba',3));
+----------------------------+
| reverse(left('gfedcba',3)) |
+----------------------------+
| efg                        |
+----------------------------+
1 row in set (0.00 sec)

#First invert the string, and then output the first three characters
mysql> select left(reverse('gfedcba'),3);
+----------------------------+
| left(reverse('gfedcba'),3) |
+----------------------------+
| abc                        |
+----------------------------+
1 row in set (0.00 sec)

4. Date time function

String function	describe
curdate ()	Returns the date of the current time
curtime ()	Returns the hour, minute, and second of the current time
now ()	Returns the date and time of the current time
month (x)	Returns the month value in date x
week (x)	The return date x is the week ordinal of the year
hour (x)	Returns the hour value in x
minute (x)	Returns the minute value in x
second(x)	Returns the second value in x
dayofweek (x)	Return x is the day of the week, 1 Sunday, 2 Monday
replace(x, y, z)	Replaces string y in string x with string z
dayofmonth (x)	The calculation date x is the day of the month
dayofycar (X)	The calculation date x is the day of the current year. Return to mm / DD / yy

#Return to mm / DD / yy
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-10-29 |
+------------+
1 row in set (0.00 sec)

#Returns the current time
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 19:24:25  |
+-----------+
1 row in set (0.00 sec)

#Returns the current full time
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-10-29 19:27:40 |
+---------------------+
1 row in set (0.01 sec)

#Return month
mysql> select month('2021-10-29');
+---------------------+
| month('2021-10-29') |
+---------------------+
|                  10 |
+---------------------+
1 row in set (0.00 sec)

#Returns the week of the year the current date is
mysql> select week('2021-10-29');
+--------------------+
| week('2021-10-29') |
+--------------------+
|                 43 |
+--------------------+
1 row in set (0.00 sec)

#Returns the hour of the current time
mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
|              19 |
+-----------------+
1 row in set (0.00 sec)

#Returns the minutes of the current time
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
|                29 |
+-------------------+
1 row in set (0.00 sec)

#Returns the second of the current time
mysql> select second(curtime());
+-------------------+
| second(curtime()) |
+-------------------+
|                49 |
+-------------------+
1 row in set (0.00 sec)

#Returns the current day of the week
mysql> select dayofweek(curdate());
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    6 |
+----------------------+
1 row in set (0.00 sec)

#What day of the month is the current date
mysql> select dayofmonth(curdate());
+-----------------------+
| dayofmonth(curdate()) |
+-----------------------+
|                    29 |
+-----------------------+
1 row in set (0.00 sec)

#What day of the year is the current date
mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  302 |
+----------------------+
1 row in set (0.00 sec)

2, Stored procedure

1. General

• A stored procedure is a set of SQL statements to complete specific functions.

• The function of stored procedure has been supported since version 5.0. It can speed up the processing speed of database and enhance the flexibility of database in practical application.

• In the process of using stored procedures, common or complex work is written in SQL statements in advance and stored with a specified name. This process is compiled and optimized and stored in the database server. When you need to use the stored procedure, you just need to call it.

• The traditional SQL statements that operate the database need to be compiled before execution. Compared with stored procedures, it is obvious that stored procedures are faster and more efficient in execution

• Stored procedure is created and saved in the database. It is not only a collection of SQL statements, but also can add some special control structures and control the access mode of data.

• Stored procedures have a wide range of applications, such as encapsulating specific functions, executing the same functions on different applications or platforms, and so on.

2. Advantages of stored procedures

• After one execution, the generated binary code will reside in the buffer to improve the execution efficiency
• SQL statements plus a collection of control statements are highly flexible
• In the server-side storage, when the client calls, reduce the network load
• It can be called repeatedly and can be modified at any time without affecting the client call
• It can complete all database operations and control the information access permission of the database

3. Operation process

• Syntax format

CREATE PROCEDURE <Process name> ( [Process parameters[,...] ] ) <Process body>
[Process parameters[,...] ] format
<Process name>: Try to avoid duplicate names with built-in functions or fields
<Process body>: sentence
[ IN | OUT | INOUT ] <Parameter name><type>

• Example (creation without parameters)

mysql> delimiter $$											#The modification end character is$$
mysql> create procedure proc()								#Create a stored procedure named u without parameters
    -> begin												#The process starts with the keyword begin
    -> create table mk (id int(10),name char(10),score int(10));
    -> insert into mk values(1,'wangwu',13);
    -> select * from mk;									#Process style sentence
    -> end $$												#The process body ends with the keyword end
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;  										#Restore the closing symbol of the statement to a semicolon with a space before it

mysql> call pro();											#Call stored procedure
ERROR 1305 (42000): PROCEDURE gl.pro does not exist
mysql> call proc();
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | wangwu |    13 |
+------+--------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select * from mk;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | wangwu |    13 |
+------+--------+-------+
1 row in set (0.00 sec)

• View specific information about a stored procedure

#Syntax format
SHOW CREATE PROCEDURE [database.]Stored procedure name;		

• View specified stored procedure information

mysql> show procedure status like '%proc%'\G
*************************** 1. row ***************************
                  Db: gl
                Name: proc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-10-29 21:17:27
             Created: 2021-10-29 21:17:27
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

4. Parameters of stored procedure

• IN input parameter: indicates that the caller passes IN a value to the procedure (the passed IN value can be literal or variable)

• OUT output parameter: indicates that the procedure sends OUT a value to the caller (multiple values can be returned) (the outgoing value can only be a variable)

• INOUT input / output parameter: it indicates that the caller passes in a value to the procedure and the procedure passes out a value to the caller (the value can only be a variable)

• It means that the caller passes in a value to the procedure and the procedure passes out a value to the caller (only variables)

• case

use info;
mysql> select * from info;
+----+-----------+--------+----------+-------+----------+
| id | name      | score  | address  | hobby | addr     |
+----+-----------+--------+----------+-------+----------+
|  1 | guyi      |  99.00 | nanj     | 1     | yunnan   |
|  2 | lier      |  60.00 | beij     | 2     | yunnan   |
|  3 | lisan     | 100.00 | shanghai | 4     | yunnan   |
|  4 | wangya    |  66.00 | hangzhou | 5     | yunnan   |
|  5 | goudan    |  38.00 | suzhou   | 7     |          |
|  6 | hanmeimei |  10.00 | nanjing  | 3     | NULL     |
|  7 | lilei     |  11.00 | nanjing  | 5     | NULL     |
|  8 | caicai    |  16.00 | nanjing  | 5     | NULL     |
|  9 | shabi     |  60.00 | hangzhou | 7     | yunnan   |
| 10 | shidan    |  88.00 | zhejiang | 4     | alashang |
| 11 | goush     |  40.00 | shanghai | 6     | shangc   |
| 12 | gobud     |  50.00 | shanghai | 9     | nanj     |
+----+-----------+--------+----------+-------+----------+
mysql> delimiter @@
mysql> create procedure proc2 (in inname varchar(50))    						 #Line parameter    
    -> begin
    -> select * from info where name=inname;
    -> end @@
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc2('wangwu');													  #Argument
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call proc2('goudan');
+----+--------+-------+---------+-------+------+
| id | name   | score | address | hobby | addr |
+----+--------+-------+---------+-------+------+
|  5 | goudan | 38.00 | suzhou  | 7     |      |
+----+--------+-------+---------+-------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5. Modify stored procedure

alter procedure <Process name> [<features>......]
alter procedure proc modifies sql data sql security invoker;

modifies sql data: The table name subroutine contains the statements that write the program
security: Safety level
invoker: When defined as invoker As long as the executor has the right to execute, it can be executed successfully

6. Delete stored procedure

The method of modifying stored procedure content is to delete the original stored procedure, and then create a new stored procedure with the same name.

DROP PROCEDURE IF EXISTS Proc;

mysql> drop procedure if exists proc;
Query OK, 0 rows affected (0.00 sec)