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

Source: Internet
Author: User
Tags mysql functions rtrim

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.