Mysql research-full parsing of MySQL built-in functions

Mysql research description of complete parsing of commonly used built-in functions in MySQL: 1) It can be used in SELECTUPDATEDELETE, and where, orderby, and having. 2) the field name is used as a parameter in the function, the value of the variable is the value of each row corresponding to the field. 3) In programming languages such as functions provided in C ++, most MySQL also provides

Full parsing of Mysql built-in functions: 1) It can be used in SELECT/UPDATE/DELETE, and where, orderby, and having. 2) Field names are used as parameters in the function, the value of the variable is the value of each row corresponding to the field. 3) In programming languages such as functions provided in C ++, most MySQL also provides

Mysql research-MySQL common built-in function full Parsing

Note:

1) it can be used in SELECT/UPDATE/DELETE and where, orderby, and having.

2) use the field name as a parameter in the function. The value of the variable is the value of each row corresponding to the field.

3) In programming languages such as functions provided in C ++, most MySQL functions are provided. For complete information about MySQL functions, see MySQL reference manual.

I. String functions [more common and need to be mastered]

1. concat (s1, s2 ,..., Sn) # concatenate input parameters into a string

Selectconcat ('abc', 'def ');

Selectconcat (name, 'Age is ', age) from users;

2. insert (str, m, n, inser_str) # Replace the n characters starting from the m position of str with inser_str

Selectinsert ('abcdef', 2, 3, '123 ′);

Selectinsert (name, 3, 2, 'hahaha') from users;

Selectinsert (name, 2, 2, '00') from users;

3. lower (str)/upper (str) # convert string str to lower case/upper case

Selectlower ('hello'), upper ('hello ');

Selectlower ('hello') as 'hello', upper ('hello') as 'hello ';

Select * from users where upper (name) = 'aaa ';

4. left (str, n)/right (str, n) # Return n characters on the leftmost/rightmost sides of str. If n <=> NULL, nothing is returned.

Selectleft ('000000', 3), right ('000000', 3), left ('000000', NULL );

5. lpad (str, n, pad)/rpad (str, n, pad) # Fill the leftmost/rightmost of str with the string pad, the str must contain n characters.

Selectname, lpad (name, 10, '#'), rpad (name, 10, '@') from users;

6. trim (str)/ltrim (str)/rtrim (str) # Remove the Left and Right spaces/left spaces/right spaces of the str string

Selectconcat ('#', trim ("abc"), '#'), concat ('#', ltrim ('abc '),'#'), concat ('#', rtrim ('abc '),'#');

7. replace (str, sear_str, sub_str) # replace all the sear_str strings in the str string with sub_str

Select replace ('abcdefgabcd', 'cd', 'xxx ');

8. strcmp (str1, str2) # returns-1 (str1 <str2)/0 (str1 = str2)/1 (str1> str2) when comparing strings str1 and str2 with ASCII codes)

Selectstrcmp ('A', 'bb '), strcmp ('A', 'A'), strcmp ('bb', 'A ');

9. substring (str, n, m) # returns a string of string length from n to m in str.

Selectsubstring ('abcdef', 2, 3 );

Selectname, substring (name, 1, 2) as subname from users;

Ii. Numeric Functions

1. abs (x) # returns the absolute value of x.

Selectabs (10), abs (-10 );

Selectabs (age) from users;

2. ceil (x) # returns the smallest integer greater than x.

3. floor (x) # returns the largest integer smaller than x.

Selectceil (2.1), ceil (2.5), ceil (2.9), floor (2.1), floor (2.5), floor (2.9)

4. mod (x, y) # returns the modulo of x/y, which is the same as x % y.

Selectmod (null, 11 );

Game Programming Network www.cgzhw.com has a detailed description, here it will not be repeated.

Iii. Date Functions

1. curdate () # Return the current date

2. curtime () # returns the current time

Selectcurdate (), curtime ();

3. now () # returns the current date + time

Selectnow ();

4. unix_timestamp (now () # returns the timestamp of the current unix time.

Selectunix_timestamp (now (); # number of seconds from the computer's first year (1971-1-100:00:00) to the present

5. from_unixtime () # convert the timestamp (integer) to the form of "date + time (xx-xxxx: xx )"

Selectfrom_unixtime (1392853616 );

6. week (now () # returns the week of the current time

7. year (now () # returns the current value of XX years.

8. hour (now ()/hour (curtime () # returns the hour of the current time

9. minute (curtime () # returns the number of minutes in the current period.

...

Selectweek (now (), year (now (), hour (now ());

Selectweek (from_unixtime (1392853616); # returns the number of cycles in the unix timestamp.

10. monthname (now ()/monthname (curdate () # returns the English name of the current month.

11. date_format (now (), "% Y-% M-% D % H: % I % S") # format the current time

Selectdate_format (now (), "% Y-% m-% d % H: % I % s ");

Selectdate_format (now (), "% y % m % d % H: % I % s ");

Iv. Process Control Functions

1. if (value, true, false) # true is returned if the value is true. Otherwise, false is returned.

Selectif (salary> 3000, 'higint', 'low') from salary;

Selectid, salary, if (salary <=> NULL, 'null', 'not null') from salary;

2. ifnull (value1, value2) # If value1 is not empty, value1 is returned; otherwise, value2 is returned.

# Can be used to replace null values

Selectifnull (salary, 0.00) from salary;

3. casewhen [value] then... Else... End # If the value is true, run the statement after then. Otherwise, do not forget the end statement after eles execution!

Selectcase when salary <= 3000 then "Low" else "Hight" end from salary;

V. Other functions

1. database () # Current database

2. version () # current database version

3. user () # current Login user

Selectdatabase ();

4. inet_aton (ip) # network byte sequence of ip addresses

Selectinet_aton ('192. 168.139.1 ′);

5. inet_ntoa # Return the ip address represented by the number

Selectinet_ntoa (3232271105 );

6. password (str) # Return the encrypted str string

Selectpassword ("123456"); # returns a 41-bit long encryption string, which is only used to encrypt MySQL users.

7. md5 () # encrypt data in the application, for example, in the C ++ Program

Selectmd5 ("123456 ");