Shulou(Shulou.com)06/01 Report--

I. built-in function

Official document: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

Common built-in functions in MySQL:

CHAR_LENGTH (str): the return value is the length of the incoming string, in characters

CONCAT (str1,str2,...): string concatenation

CONCAT_WS (separator,str1,str2,...): string concatenation of customizable connectors

Example: mysql > select concat_ws ('-', 'admin','123') +-- + | concat_ws ('-', 'admin','123') | +-+ | admin-123 | +-+

CONV (num,from_base,to_base): binary conversion

Example: convert 20 from decimal to binary mysql > select conv; +-+ | conv (20re10 row in set 2) | +-+ | 10100 | +-+ 1 row in set (0.00 sec)

INSERT (str,x_path,y_len,new_str): specify the location to insert the string

Example: mysql > select insert ('1234567, two, two, three, four, six, four, six, four, two, two, three, four, two, four, +-- + | insert ('1234567 records, 2 records, 3' 'new') | +-+ | 1new567 | +-+ 1 row in set (0.00 sec)

INSTR (str,substr): returns the first occurrence of the string str substring

LOWER (str): lowercase the string str

UPPER (str): capitalizes the string str

TRIM (str): returns the string str and removes the leading and trailing space characters

LTRIM (str): returns the string str and removes the first space character

Example: mysql > select ltrim ('1 2 3'); +-+ | ltrim ('1 2 3') | +-+ | 1 23 | +-+

RTRIM (str): returns the string str and removes trailing space characters

LEFT (str,len): returns len characters to the left of the string str. If len is null, null is returned.

RIGHT (str,len): returns len characters to the right of the string str. If len is null, null is returned.

REPLACE (str,old_str,new_str): new_str string replaces old_str string

REVERSE (str): returns the string str, in reverse order of characters

REPEAT (str,count): the string str that returns the number of times count is repeated

Example: mysql > select repeat ('hey',10); +-+ | repeat (' hey',10) | +-+ | heyheyheyheyheyheyheyheyheyhey | +-+

SUBSTRING (str,pos,len): returns a substring of length len from the position pos in the string str

RPAD (str,len,pad): padding the str string with pad from the right to the len length

LPAD (str,len,pad): padding the str string with pad from the left to the len length

Example: mysql > select lpad ('hello',10,'#'); +-- + | lpad (' hello',10,'#') | +-+ | # hello | +-+ II. Custom function

The MySQL custom function stores a series of sql statements, similar to stored procedures, except that the function returns only one value, and the stored procedure can have not only the return value, but also the output of the result set.

1. To create a function F1, you can pass in two values of int type, and the result returned by the function is also int type delimiter\\ create function F1 (i1 int, i2 int) returns intBEGIN declare num int; set num = i1 + i2; return (num); END\\ delimiter; 2. Execute function # query using select F1 (10Magin11); # sql statement block assignment using declare num int;select nid into num from student where nid = 1 END 3, delete function drop function func_name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.