MySQL Study Notes _ 7_MySQL common built-in functions and Study Notes _ 7_mysql

MySQL Study Notes _ 7_MySQL common built-in functions and Study Notes _ 7_mysql
 MySQL common built-in functions

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) # concatenates 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 ('20140901', 3), right ('20160901', 3), left ('20160901', 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 );

5. rand () # returns 0 ~ Random number between 1

Selectrand ();

Selectceil (rand () * 100); #0 ~ Integer random number between 100

Selectfloor (rand () * 100 );

6. round (n, m) # returns the m decimal point value after n rounding. The default m value is 0.

Selectround (1.23 );

Selectround (1.456, 2 );

7. truncate (n, m) # Return the number where n is truncated to m decimal places.

Selecttruncate (1.234, 2 );

Selecttruncate (1.235, 2), round (1.235, 2 );

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 ");