MySQL functions and stored procedures

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

• 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.

#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

#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

#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)

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)

Add new comment

0 comments