Catalog

introduction

One . function

1. Mathematical functions

2. Aggregate functions  

3. String function

4. Date string function

Two . stored procedure

1. brief introduction

2. advantage

3. grammar

4. Parameter classification

5. Stored procedure with parameters

6. Modify stored procedure

7. Delete stored procedure

summary

---

introduction

Mysql Database functions provide methods to realize various functions , So that we can output more efficiently when querying records .Mysql Many functions are built in , Commonly used include mathematical functions 、 Aggregate functions 、 String function and date time function

One . function

1. Mathematical functions

 Mathematical functions           	 describe 
abs(x)	             return  x  The absolute value of 
rand()	             return  0  To  1  The random number 
mod(x,y)	         return  x  Divide  y  The remainder after 
power(x,y)	         return  x  Of  y  Power 
round(x)	         Return from  x  The nearest integer 
round(x,y)	         Retain  x  Of  y  The rounded value of a decimal place 
sqrt(x)	             return  x  The square root of 
truncate(x,y)	     Return to digital  x  Truncated to  y  A decimal value 
ceil(x)	             Returns greater than or equal to  x  Minimum integer of 
floor(x)	         Returns less than or equal to  x  Maximum integer for 
greatest(x1,x2…)	 Returns the maximum value in the collection 
least(x1,x2…)	     Returns the smallest value in the set 

2. Aggregate functions  

 Aggregate functions 	        describe 
avg()	        Returns the average value of the specified column 
count()         Returns the value of the specified column  NULL  The number of values 
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 in the specified column 

3. String function

 String function 	   describe 
trim()	       Returns a value with the specified format removed 
concat(x,y)	   The parameters that will be provided  x  and  y  Concatenate into a string 
substr(x,y)	   Get from string  x  No  y  A string starting at a position , Follow substring() Functions work the same 
substr(x,y,z)  Get from string  x  No  y  The starting length of a position is  z  String 
length(x)	   Return string  x  The length of 
replace(x,y,z)	 The string  z  Alternative string  x  String in  y
upper(x)	  The string  x  All of the letters of the are changed into lower case letters 
left(x,y)	  Return string  x  Before  y  Characters 
right(x,y)	  Return string  x  After  y  Characters 
repeat(x,y)	  The string  x  repeat  y  Time 
space(x)	  return  x  A space 
strcmp(x,y)	  Compare  x  and  y, The value returned can be -1,0,1
reverse(x)	  The string  x  reverse 

4. Date string function

 Date time function 	    describe 
curdate()	    Returns the date of the current time 
curtime()	    Return to the current market price, your hours, minutes and seconds 
now()	        Returns the date and time of the current time 
month(x)	    Return date x The month in is worth 
week(x)	        Return date x It's the first few weeks of the year 
hour(x)	        return x The hour value in 
minute(x)  	    Return date x Minutes in 
second(x)	     Return date x Second value in 
dayotweek(x)	 return x What day is ,1 For Sunday ,2 For Monday 
replace(x,y,z)	 The character z Alternative string x String in y
dayotmonth(x)	 Calculate the date x It's the day of the month 
dayotyear(x)	 Calculate the date x It's the day of the year 

Two . stored procedure

1. brief introduction

• MysQL A database stored procedure is a set of procedures designed to perform specific functions SQL Collection of statements .
•   The function of stored procedure is from 5.0 The version just started to support , It can speed up database processing , Enhance the flexibility of database in practical application .
•   In the process of using stored procedures, common or complex work is used in advance SQL The statement is written and stored with a specified name , This process is compiled and optimized and stored in the database server . When you need to use this stored procedure , Just call it .
•   The traditional method of operating a database SQL The statement needs to be compiled before execution , Then go ahead and do it , Compare with stored procedures , Obviously, stored procedures are faster to execute , More efficient . Stored procedures are created and saved in the database , It's not just SQL Collection of statements , Some special control structures can also be added , You can also control how data is accessed .
   

2. advantage

• After one execution , The generated binary code will reside in the buffer , Improve execution efficiency
•  SQL Statement plus a collection of control statements , High flexibility
•   Store on the server side , When called by the client , Reduce network load
•   Can be called repeatedly , Can be modified at any time , Does not affect client calls
•   Can complete all database operations , You can also control the information access rights of the database

3. grammar

create procedure < The process of > ([ Process parameters ……]) < The process of body >
[ Process parameters ……]  Format 
< The process of >： Try to avoid duplicate names with built-in functions or fields 
< The process of body >： sentence 
[in|out|inout] < Parameter name >< type >

 example ：
delimiter &&    								# Change the closing sign of the statement from a semicolon ; Temporary modification , In case something goes wrong , You can customize 
create procedure ee(in inname varchar(16))  	# Create stored procedure , Custom procedure name ,（） With parameters 
 	begin   		    						# The process body takes the keyword BEGIN Start 
 	select * from test1 where a_name='aaaa';  	# Process style sentences 
 	end&&    									# The process body takes the keyword END ending 
delimiter ; 			    				# Returns the ending symbol of the statement to a semicolon （ There should be a space before the semicolon ）
call ee('aaaa');	  							# Calling stored procedure 

-------------- View stored procedures --------------
show create procedure XXX\G               		 # View the specific information of a stored process 

4. Parameter classification

• The main part of the stored procedure , Process body
•   With begin Start ,end end , If there is only one SQL sentence , Omission begin and end
•   With delimiter Start and end

5. Stored procedure with parameters

• Input parameters ：in Indicates that the caller passes a value... To the procedure （ The incoming value can be literal or variable ）
•   Output parameters ：out Indicates that the procedure passes out a value to the caller （ Multiple values can be returned ）（ Outgoing values can only be variables ）
•   Input / Output parameters ：inout , This means that the caller passes in a value to the procedure , It also indicates that the procedure passes a value to the caller （ It can only be a variable ）

6. Modify stored procedure

alter procedure < The process of > [< features >……]
alter procedure ff 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  when , As long as the executor has the power to execute , You can successfully execute 

7. Delete stored procedure

drop procedure if exists  The process of ;

summary

The function classification ：

• Mathematical functions
• Aggregate functions
• String function
• Date time function

stored procedure ;

MysQL A database stored procedure is a set of procedures designed to perform specific functions SQL Collection of statements .