A) Create a stored procedure times SQL error (with an error in your SQL syntax; ...)
Delimiter//should be added to the procedure and/or end of the type process, for example
Delimiter//
CREATE PROCEDURE Update_processdefid ()
BEGIN
.......
END;
//
In the command-line client, if a single line of commands ends with a semicolon, the command will be executed by MySQL when the carriage returns.
Delimiter tells the client that this range is only a fame, not a meeting;
II) Variable Scope
Within a stored procedure, variables defined using declare can either use @ Access or not @,
The difference is that using @ accesses a session-level variable. Visible throughout the session, not only in your stored procedures.
So the stored procedure accesses declare defined variables, do not use @
In addition, using @@ 是 a system-level variable
If a variable is used in the console, it is not declare defined beforehand, the direct set @var1 = ' AA '; Can
c) commonly used string processing functions
1.ASCII (str)
The return value is the number of the leftmost character of the string str, which is the ASCII code that obtains the leftmost character.
If Str is an empty string, the return value is 0. If STR is NULL, the return value is null.
ASCII () is used for characters with numeric values from 0 to 255.
2.BIN (N)
Returns a string representation of a binary value that has a value of N, which is converted to binary.
Where N is a longlong (BIGINT) number. This equates to CONV (n,10,2). If n is null, the return value is null.
3.bit_length (str)
Returns the value of the binary string str length.
4.CHAR (N,... [USING CharSet])
CHAR () understands each parameter n as an integer whose return value is a string containing the characters given by the code value of those integers. Null values are omitted.
All parameters are concatenated after they are converted to characters.
5.char_length (str)
The return value is the length of the string str, and the length of the unit is a character.
6.character_length (str)
Character_length () is a synonym for Char_length ().
7.COMPRESS (string_to_compress)
Compresses a string.
8.CONCAT (STR1,STR2,...)
Returns the string that results from the connection parameter.
9.concat_ws (SEPARATOR,STR1,STR2,...)
Concat_ws () represents CONCAT with Separator, which is a special form of the CONCAT ().
The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated.
The delimiter can be a string, or it can be another parameter. If the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters.
10.CONV (N,from_base,to_base)
Convert numbers between different number bases. The return value is the n string representation of a number, converted from from_base to To_base base. If any one of the arguments is NULL, the return value is null.
The argument N is understood as an integer, but can be specified as an integer or a string. The minimum cardinality is 2, and the maximum cardinality is 36.
11.ELT (N,STR1,STR2,STR3,...)
If n = 1, the return value is str1, and if n = 2, the return value is str2, and so on. If n is less than 1 or greater than the number of parameters, the return value is NULL.
12.export_set (Bits,on,off[,separator[,number_of_bits])
The return value is a string, where for each bit group in the bits value, you can get an on string, and for each clear 0 bit, you can get an off string.
Bit values in bits are tested in right-to-left order (from low bits to high bits).
Strings are separated by delimited strings (by default, comma ', ') and are added to the results in order from left to right. Number_of_bits will give the number of bits tested (default is 64).
13.FIELD (STR,STR1,STR2,STR3,...)
The return value is STR1, str2, Str3,...... The str index (position) in the list. If STR is not found, the return value is 0.
If all arguments to field () are strings, all parameters are compared by string.
If all the parameters are numbers, the comparisons are by numbers. Otherwise, the parameters are compared by double.
14.find_in_set (Str,strlist)
If the string str is in the string list strlist consisting of N substrings, the return value ranges from 1 to N (that is, the position of STR in strlist).
A list of strings is a string of self-chains that are separated by ', ' symbols.
15.FORMAT (X,D)
Writes the format of the number x as ' #,###,###.## ', preserves the D-bit after the decimal point in a rounded manner, and returns the result as a string.
If D is 0, the result is returned without a decimal point, or with no fractional part.
16.HEX (n_or_s)
If n_or_s is a number, it returns a string representation of the hexadecimal value n, where n is the number of Longlong (BIGINT). This is equivalent to CONV (n,10,16).
17.INSERT (STR,POS,LEN,NEWSTR) (characters in Str are replaced by NEWSTR)
Returns the string str, whose substring starts at the POS position and is long replaced by the string Newstr len character.
If the POS exceeds the string length, the return value is the original string.
If Len is longer than the length of other strings, it is replaced from position pos. If either parameter is NULL, the return value is null.
18.INSTR (STR,SUBSTR)
Returns the first occurrence of a substring of string str. This is the same as the two-parameter form of locate (), unless the order of the arguments is reversed.
19.LCASE (str)
LCASE () is a synonym for LOWER ().
20.LEFT (Str,len)
Returns the leftmost character of Len beginning with the string str.
21.LENGTH (str)
The return value is the length of the string str, in bytes. One multibyte character is counted as multibyte.
This means that for a string containing 5 2-byte characters, the return value of LENGTH () is 10, and the return value of Char_length () is 5.
22.load_file (file_name) (good)
Reads the file and returns the file in the form of a string.
Example:mysql> UPDATE tbl_name
SET blob_column=load_file ('/tmp/picture ')
WHERE id=1;
23.LOCATE (SUBSTR,STR), LOCATE (Substr,str,pos)
The first syntax returns the first occurrence of the string str neutron string substr.
The second syntax returns the first occurrence of the string str neutron string substr, starting at Pos. If SUBSTR is not in Str, the return value is 0.
24.LOWER (str)
Returns the string str and all characters that have been changed to lowercase according to the most recent Character Set mapping table (default is cp1252 Latin1).
25.LPAD (STR,LEN,PADSTR)
Returns the string str, whose left side is filled by the string padstr to the Len character length. If STR is longer than Len, the return value is shortened to the Len character.
That is, add the Len-length padstr in front of str.
26.LTRIM (str)
Returns the string str, whose boot space character is deleted.
27.make_set (BITS,STR1,STR2,...)
Returns a set value (a string containing a string of characters separated by ', '), consisting of a string with the corresponding bits in the BITS group.
STR1 corresponds to bit 0, str2 corresponds to bit 1, and so on. STR1, str2, ... The null value in the is not added to the result.
28.MID (Str,pos,len)
MID (Str,pos,len) is a synonym for SUBSTRING (str,pos,len).
29.OCT (N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This equates to conv (n,10,8). If n is null, the return value is null.
30.octet_length (str)
Octet_length () is a synonym for LENGTH ().
31.ORD (str)
If the leftmost character of the string str is a multibyte character, the code for that character is returned,
If the leftmost character is not a multibyte character, then ORD () and function ASCII () return the same value.
32.POSITION (substr in str)
POSITION (substr in str) is a synonym for LOCATE (SUBSTR,STR).
33.QUOTE (str)
Quotes a string, resulting in a result that can be used as a fully escaped data value in an SQL statement.
34.REPEAT (Str,count)
Returns a string consisting of a repeating string str, with the number of string str equal to count.
If Count <= 0, an empty string is returned. If STR or count is NULL, NULL is returned.
35.REPLACE (STR,FROM_STR,TO_STR)
Returns the string str and all string from_str that are substituted by the string to_str.
36.REVERSE (str)
Returns the string str, in reverse order and character order.
37.RIGHT (Str,len)
Returns the right-most Len character starting from the string str.
38.RPAD (STR,LEN,PADSTR)
Returns the string str, whose right is padded with the string padstr to the Len character length.
If the length of the string str is greater than Len, the return value is shortened to the same length as the Len character.
39.RTRIM (str)
Returns the string str, trailing whitespace character is deleted.
40.SOUNDEX (str)
Returns a soundex string from Str.
41.SPACE (N)
Returns a string consisting of an n-spaced symbol.
42.SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for Len)
SUBSTR () is a synonym for SUBSTRING ().
The format without the Len parameter returns a substring from the string str, starting at position pos.
The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos.
Use the from format as standard SQL syntax. You may also use a negative value for the POS.
If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.
43.substring_index (Str,delim,count)
Returns the string from string str before the delimiter Delim and Count appear.
If Count is positive, it returns everything to the left of the final delimiter (starting from the left). If count is negative, it returns everything to the right of the delimiter (starting from the right).
44.TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str) TRIM (remstr from] str)
Returns the string str, where all remstr prefixes and/or suffixes have been deleted.
If none of the classifier both, leadin, or trailing is given, it is assumed to be both. REMSTR is optional and can be removed without specifying a space.
45.UCASE (str)
UCASE () is a synonym for upper ().
46.UNCOMPRESS (string_to_uncompress)
The string that is compressed by the compress () function is decompressed.
47.uncompressed_length (compressed_string)
Returns the length of the compressed string before compression.
48.UNHEX (str)
Performs a reverse operation from Hex (str). That is, it understands each pair of hexadecimal digits in the argument as a number and converts it to the character that the number represents.
The resulting character is returned as a binary string.
49.UPPER (str)
Returns the string str, and the characters converted to uppercase according to the latest character set mappings (default is cp1252 Latin1).
50. Pattern matching: wildcard characters
%-------Match any number of characters, even including 0 characters
_-------can match only one character
Escape with '/'
51.STRCMP (EXPR1,EXPR2)
If all strings are the same, returns strcmp (), or 1 if the first argument is less than the second, depending on the current sorting order, 1 is returned in other cases.
MySQL programming considerations and commonly used string processing functions
