MySQL Storage Process-principles, syntax, functions detailed description

The MySQL storage process is a set of SQL statements to accomplish a particular function, compiled and stored in a database, and when the user needs to use that group of SQL statements, it can be called by specifying the name of the stored procedure and given the parameters to execute it, in short, a set of commands that have been written, Use it when you need it. Looking for a quick overview of the MySQL storage process, take a look at the following "MySQL storage process-principles, syntax, function details" bar!

First, the MySQL storage process introduction:
A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed. Stored procedures often have the following advantages:

1) the stored procedure can achieve a faster execution speed.
If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
Experience: compile and optimize, fast!

2) stored procedures allow standard components to be programmed.
After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. and database professionals can modify stored procedures at any time, without affecting the source code of the application.
Experience: encapsulation and abstraction, simple invocation

3) Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations .
Experience: Powerful , logical and powerful

4) stored procedures can be used as a security mechanism to make full use of them.
The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.
Experience: Limitations and security

5) stored procedures can reduce network traffic.
For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
Experience: Reduce network traffic (encapsulation is good)

 DELIMITER // create  procedure  Proc1 (out s int  )  begin  select  count  (* ) into  s from   User  ;  end  //  DELIMITER;  

Variables in the stored program
1) Declare local variables
DECLARE var_name[,...] Type [DEFAULT value]
This statement is used to declare a local variable.
To provide a default value for a variable, include one of the defaults clauses. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null. The scope of the local variable in its declared begin ... End block. It can be used in nested blocks, except those that declare variables with the same name.
2) Variable Set statement
SET var_name = expr [, var_name = expr]
The SET statement in the stored program is an extended version of the generic SET statement.
The referenced variable may be a variable declared within a subroutine, or a global server variable. The SET statement in the stored program is implemented as part of the pre-existing set syntax. This allows set a=x, B=y, ... Such an extension syntax. The different types of variables (local declaration variables and global and collective variables) can be mixed together. This also allows the merging of local variables and some options that are meaningful only for system variables.
3) SELECT ... INTO statement
SELECT col_name[,...] Into var_name[,...] table_expr
This select syntax stores the selected columns directly into the variable. Therefore, only a single row can be retrieved. SELECT id,data into X, y from test.t1 LIMIT 1;

MySQL storage functions-basic common functions
1. String class
1) MySQL Stored procedure function-string function:
CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//returns the position where substring first appeared in string, no return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length of the string from string2
Length (String)//string
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position
Lpad (string2, length, pad)//repeat pad to start with string until string length
LTRIM (string2)//Remove front-end spaces
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str
Rpad (string2, length, pad)//after STR with pad supplement until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//starting with the position of STR, taking a length character,
TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Take string2 last length character
Space (count)//Generate Count of spaces
Note: When working with strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1

2. Math class
1) MySQL storage function-Math function:
ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal into binary
CEILING (NUMBER2)//Up rounding
CONV (number2,from_base,to_base)//Binary conversion
Floor (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//number of decimal digits reserved
Hex (Decimalnumber)//Turn hex
Note: Hex () can pass in a string, then return its ASC-11 code, such as Hex (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) to return 19
LEAST (number, number2 [,..])//Find minimum
MOD (numerator, denominator)//redundancy
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal place]
Note: Return types are not all integers, such as the following:
Sign (NUMBER2)//

3. Date and Time class
Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//current timestamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time to Date2
Date_format (datetime, Formatcodes)//Use formatcodes format to display datetime
Date_sub (Date2, INTERVAL d_value d_type)//Subtract one time from Date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)/days of return date
Dayname (date)//English Week
DAYOFWEEK (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//Extract the specified part of the date
Makedate (year, day)//gives the first days of the years and years, generating a date string
Maketime (hour, minute, second)//Generate time string
MONTHNAME (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//seconds turn into time
Str_to_date (string, format)//string turns into time, displayed in format
Timediff (datetime1, datetime2)//Two time difference
Time_to_sec (time)//times to seconds]
WEEK (Date_time [, Start_of_week])//weeks
Year (DateTime)//Years
DayOfMonth (DateTime)/day of the month
HOUR (DateTime)//hour
Last_day (date)//date The last date of the month
Microsecond (DateTime)//microseconds
Month (datetime)//month
MINUTE (DateTime)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

Reference:
Http://www.cnblogs.com/hsqzzzl/archive/2008/02/21/1076646.html

MySQL Storage Process-principles, syntax, functions detailed description