MySQL common function 2

MySQL common functions (2) use the help documentation provided by MySQL to list common functions in MySQL databases. Facts have proved that MySQL's online help materials are very practical. On which day would you like to learn from a cute Oracle database like MySQL, she allows you to get a full view of the basic usage and examples of functions without looking at other information. Speak nonsense,

MySQL common functions (2) use the help documentation provided by MySQL to list common functions in MySQL databases. Facts have proved that MySQL's online help materials are very practical. On which day would you like to learn from a cute Oracle database like MySQL, she allows you to get a full view of the basic usage and examples of functions without looking at other information. Speak nonsense,

MySQL common functions 2

Use the help documentation provided by MySQL to list some common functions in MySQL databases.
Facts have proved that MySQL's online help materials are very practical. On which day would you like to learn from a cute Oracle database like MySQL, she allows you to get a full view of the basic usage and examples of functions without looking at other information.
Let's talk about theme.

I. common string functions

Ii. Numeric Functions

Iii. Date and Time Functions

Iv. MySQL control flow Functions

1. IF (expr1, expr2, expr3) function: IF expr1 is true, expr2 is returned; otherwise, expr3 is returned.
2. IFNULL (expr1, expr2) function: If expr1 is NULL, expr2 is returned.
3. NULLIF (expr1, expr2) function: If expr1 = expr2, NULL is returned; otherwise, expr1 is returned.

4. CASE Function

?

5. Other common functions

Time Format: or compare the time format with java to lower case.

? Format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December )?
% W name of the Week (Sunday ...... Saturday )?
% D Date of the month with an English prefix (1st, 2nd, 3rd, etc .)?
% Y years, number, 4 digits?
% Y years, number, 2 digits?
% A abbreviated name of the Week (Sun ...... Sat )?
% D number of days in the month (00 ...... 31 )?
% E number of days in the month (0 ...... 31 )?
% M month, number (01 ...... 12 )?
% C month, number (1 ...... 12 )?
% B abbreviated month name (Jan ...... Dec )?
% J days in a year (001 ...... (366 )?
% H hour (00 ...... 23 )?
% K hour (0 ...... 23 )?
% H hour (01 ...... 12 )?
% I hour (01 ...... 12 )?
% L hour (1 ...... 12 )?
% I minute, number (00 ...... 59 )?
% R time, 12 hours (hh: mm: ss [AP] M )?
% T time, 24 hours (hh: mm: ss )?
% S seconds (00 ...... 59 )?
% S seconds (00 ...... 59 )?
% P AM or PM?
% W days in a week (0 = Sunday ...... 6 = Saturday )?
% U Week (0 ...... 52) is Sunday the first day of the week?
% U Week (0 ...... 52) is Monday the first day of the week?
% A text "% ".

All other characters are not interpreted and copied to the result.

Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % y ');?
-> 'Saturday October 1997 '?
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % s ');?
-> '22: 23: 00 '?
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',?
'% D % y % a % d % m % B % J ');?
-> '4th 97 Sat 04 10 Oct 277 '?
Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',?
'% H % k % I % r % T % S % W ');?
-> '22 22 10 10:23:00 PM 22:23:00 6'

I. common string functions
1. CONCAT (str1, str2 ,...)
Mysql>? Concat;
Mysql> select concat ('My, s', 'ql ');
+ ------------------------- +
| CONCAT ('My, s', 'ql ') |
+ ------------------------- +
| MySQL ??????????????????? |
+ ------------------------- +

2. INSERT (str, pos, len, newstr)
Mysql>? Insert function;
Mysql> select insert ('quadratic ', 3, 4, 'wh ');
+ ----------------------------------- +
| INSERT ('quadratic ', 3, 4, 'wh') |
+ ----------------------------------- +
| QuWhattic ????????????????????????? |
+ ----------------------------------- +

3. LOWER (str)
Mysql>? Lower
Mysql> select lower ('quadratically ');
+ ------------------------ +
| LOWER ('quadratically ') |
+ ------------------------ +
| Quadratically ?????????? |
+ ------------------------ +

4. UPPER (str)
Mysql>? Upper
Mysql> select upper ('hej ');
+ -------------- +
| UPPER ('hej') |
+ -------------- +
| HEJ ?????????? |
+ -------------- +

5. LEFT (str, len)
Mysql>? Left
Mysql> select left ('foobarbar', 5 );
+ ---------------------- +
| LEFT ('foobarbar', 5) |
+ ---------------------- +
| Fooba ???????????????? |
+ ---------------------- +

6. RIGHT (str, len)
Mysql>? Right
Mysql> select right ('foobarbar', 4 );
+ ----------------------- +
| RIGHT ('foobarbar', 4) |
+ ----------------------- +
| Rbar ?????????????????? |
+ ----------------------- +

7. LPAD (str, len, padstr)
Mysql>? Lpad
Mysql> select lpad ('hi', 4 ,'?? ');
+ ------------------- +
| LPAD ('hi', 4 ,'?? ') |
+ ------------------- +
| ?? Hi ?????????????? |
+ ------------------- +

8. RPAD (str, len, padstr)
Mysql>? Rpad
Mysql> select rpad ('hi', 5 ,'? ');
+ ------------------ +
| RPAD ('hi', 5 ,'? ') |
+ ------------------ +
| Hi ??????????????? |
+ ------------------ +

9. LTRIM (str)
Mysql>? Ltrim
Mysql> select ltrim ('?? Barbarbar ');
+ ------------------- +
| LTRIM ('?? Barbarbar') |
+ ------------------- +
| Barbar ???????????? |
+ ------------------- +

10. RTRIM (str)
Mysql>? Rtrim
Mysql> select rtrim ('barbar ??? ');
+ -------------------- +
| RTRIM ('barbar ??? ') |
+ -------------------- +
| Barbar ????????????? |
+ -------------------- +

11. TRIM (str)
Mysql>? Trim
Mysql> select trim ('?? Bar ??? ');
+ ------------------ +
| TRIM ('?? Bar ??? ') |
+ ------------------ +
| Bar ?????????????? |
+ ------------------ +

12. REPEAT (str, count)
Mysql>? Repeat function;
Mysql> select repeat ('mysql', 3 );
+ -------------------- +
| REPEAT ('mysql', 3) |
+ -------------------- +
| MySQLMySQLMySQL ???? |
+ -------------------- +

13. REPLACE (str, from_str, to_str)
Mysql>? Replace
Mysql> select replace ('www .mysql.com ', 'w', 'ww ');
+ ------------------------------------- +
| REPLACE ('www .mysql.com ', 'w', 'ww') |
+ ------------------------------------- +
| WwWwWw.mysql.com ???????????????????? |
+ ------------------------------------- +

14. STRCMP (expr1, expr2)
Mysql>? Strcmp
Mysql> select strcmp ('text', 'text2'), STRCMP ('text2', 'text'), STRCMP ('text', 'text ');
+ ----------------------- + ---------------------- +
| STRCMP ('text', 'text2') | STRCMP ('text2', 'text') | STRCMP ('text', 'text') |
+ ----------------------- + ---------------------- +
| ???????????????????? -1 | ?????????????????????? 1 | ????????????????????? 0 |
+ ----------------------- + ---------------------- +

15. SUBSTRING
Mysql>? Substring
SUBSTRING (str, pos), SUBSTRING (str FROM pos), SUBSTRING (str, pos, len ),
SUBSTRING (str FROM pos FOR len)
Mysql> select substring ('secoler', 3,4), SUBSTRING ('secoler', 6 );
+ --------------------------- + ------------------------- +
| SUBSTRING ('secoler', 3,4) | SUBSTRING ('secoler', 6) |
+ --------------------------- + ------------------------- +
| Cool ?????????????????????? | Ler ????????????????????? |
+ --------------------------- + ------------------------- +

Ii. Numeric Functions
1. ABS (X) returns the absolute value function.
Mysql>? Abs
Mysql> select abs (-32 );
+ ---------- +
| ABS (-32) |
+ ---------- +
| ??????? 32 |
+ ---------- +

2. CEILING (X) and CEIL (X) Take the canopy Function
Mysql>? Ceil
Mysql> select ceiling (1.23), CEIL (-1.23 );
+ --------------- + ------------- +
| CEILING (1.23) | CEIL (-1.23) |
+ --------------- + ------------- +
| ????????????? 2 | ?????????? -1 |
+ --------------- + ------------- +

3. FLOOR (X) FLOOR function
Mysql>? Floor
Mysql> select floor (1.23), FLOOR (-1.23 );
+ ------------- + -------------- +
| FLOOR (1.23) | FLOOR (-1.23) |
+ ------------- + -------------- +
| ??????????? 1 | ??????????? -2 |
+ ------------- + -------------- +

4. MOD (N, M), N % M, n mod m modulo Function
Mysql>? Mod
Mysql> select mod (234, 10), 253% 7, MOD (), 29 MOD 9;
+ -------------- + --------- + ----------- + ---------- +
| MOD (234, 10) | 253% 7 | MOD () | 29 MOD 9 |
+ -------------- + --------- + ----------- + ---------- +
| ???????????? 4 | ??????? 1 | ????????? 2 | ???????? 2 |
+ -------------- + --------- + ----------- + ---------- +

5. RAND (), RAND (N) Take the random number function between 0 and 1.
Mysql>? Rand
Mysql> select rand (), RAND ();
+ ------------------ + ----------------- +
| RAND ()??????????? | RAND ()?????????? |
+ ------------------ + ----------------- +
| 0.77874226009356/0.5317868818825 |
+ ------------------ + ----------------- +

6. TRUNCATE (X, D) returns the result where the number X is truncated to a decimal number of D.
Mysql>? Truncate
Mysql> select truncate (1.223, 1), TRUNCATE (1.999, 1), TRUNCATE (-1.999, 2 );
+ ------------------- + -------------------- +
| TRUNCATE (1.223, 1) | TRUNCATE (1.999, 1) | TRUNCATE (-1.999, 2) |
+ ------------------- + -------------------- +
| ??????????????? 1.2 | ??????????????? 1.9 | ?????????????? -1, 1.99 |
+ ------------------- + -------------------- +

Iii. Date and Time Functions
1. CURDATE () current date function
Mysql>? Curdate
Mysql> select curdate (), CURDATE () + 0;
+ ------------ + --------------- +
| CURDATE ()?? | CURDATE () + 0 |
+ ------------ + --------------- +
| ?????? 20090703 |
+ ------------ + --------------- +

2. CURTIME () Current Time Function
Mysql>? Curtime
Mysql> select curtime (), CURTIME () + 0;
+ ----------- + --------------- +
| CURTIME () + 0 |
+ ----------- + --------------- +
| 12:07:08 ?? | ???????? 120708 |
+ ----------- + --------------- +

3. NOW () current date and time functions
Mysql>? Now
Mysql> select now (), NOW () + 0;
+ --------------------- + ---------------- +
| NOW ()??????????????? | NOW () + 0 ?????? |
+ --------------------- + ---------------- +
| 12:07:54 | 20090703120754 |
+ --------------------- + ---------------- +

4. The timestamp of UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date) date
Mysql>? Unix_timestamp
Mysql> SELECT UNIX_TIMESTAMP (), UNIX_TIMESTAMP ('2017-02-15 23:23:00 ');
+ ------------------ + ----------------------------------------- +
| UNIX_TIMESTAMP () | UNIX_TIMESTAMP ('2017-02-15 23:23:00 ') |
+ ------------------ + ----------------------------------------- +
| ??????? 1246594366 | ????????????????????????????? 351098580 |
+ ------------------ + ----------------------------------------- +

5. FROM_UNIXTIME (unix_timestamp), FROM_UNIXTIME (unix_timestamp, format) return the date value of the timestamp (Inverse Function of UNIX_TIMESTAMP)
Mysql>? From_unixtime
Mysql> SELECT FROM_UNIXTIME (1246594135), FROM_UNIXTIME (351098580 );
+ --------------------------- + -------------------------- +
| FROM_UNIXTIME (1246594135) | FROM_UNIXTIME (351098580) |
+ --------------------------- + -------------------------- +
| 12:08:55 ??????? | 23:23:00 ?????? |
+ --------------------------- + -------------------------- +

6. WEEK (date [, mode]) returns the WEEK number of the year for the given date.
Mysql>? Week
Mysql> select week ('2017-02-15 ');
+ -------------------- +
| WEEK ('2017-02-15 ') |
+ -------------------- +
| ?????????????????? 7 |
+ -------------------- +

7. YEAR (date)
Mysql>? Year
Mysql> select year ('81-02-15 ');
+ ------------------ +
| YEAR ('81-02-15 ') |
+ ------------------ +
| ????????????? 1981 |
+ ------------------ +

8. HOUR (time) returns the HOUR information of the time.
Mysql>? Hour
Mysql> select hour ('10: 05: 03 ');
+ ------------------ +
| HOUR ('10: 05: 03') |
+ ------------------ +
| ??????????????? 10 |
+ ------------------ +

9. MINUTE (time) returns the MINUTE information of the time.
Mysql> select minute ('98-02-03 10:05:03 ');
+ ----------------------------- +
| MINUTE ('98-02-03 10:05:03 ') |
+ ----------------------------- +
| ??????????????????????????? 5 |
+ ----------------------------- +

10. MONTHNAME (date) returns the full month name of the time.
Mysql> select monthname ('2017-02-15 ');
+ ------------------------- +
| MONTHNAME ('2017-02-15 ') |
+ ------------------------- +
| February ???????????????? |
+ ------------------------- +

11. DATE_FORMAT (date, format) display format based on format date
Mysql>? Date_format
Mysql> SELECT DATE_FORMAT ('2017-02-15 23:23:00 ',' % W % M % y ');
+ ------------------------------------------------ +
| DATE_FORMAT ('2017-02-15 23:23:00 ',' % W % M % y') |
+ ------------------------------------------------ +
| Sunday February 1981 ??????????????????????????? |
+ ------------------------------------------------ +

12. DATE_ADD (date, INTERVAL expr type) returns the time period of INTERVAL different from the given date.
Mysql> select now () current, date_add (now (), INTERVAL 31 day) after31days;
+ --------------------- +
| Current ????????????? | After31days ????????? |
+ --------------------- +
| 12:34:15 | 12:34:15 |
+ --------------------- +

13. DATEDIFF (expr, expr2) calculates the number of days for the difference between two dates
Mysql> select datediff (now (), '2017-02-15 23:23:00 ');
+ --------------------------------------- +
| DATEDIFF (now (), '2017-02-15 23:23:00 ') |
+ --------------------------------------- +
| ????????????????????????????????? 10365 |
+ --------------------------------------- +

Iv. MySQL control flow Functions
First, create the demo table salary.
Mysql> use test;
Mysql> create table salary (userid int, salary decimal (9, 2 ));
Mysql> insert into salary values (), (1, null );
Mysql> select * from salary;
+ -------- + --------- +
| Userid | salary ?? |
+ -------- + --------- +
| ?????? 1 | 1000.00 |
| ?????? Two | 2000.00 |
| ?????? Three | 3000.00 |
| ?????? 5 | 4000.00 |
| ?????? Five | 5000.00 |
| ?????? 1 | ???? NULL |
+ -------- + --------- +

1. IF (expr1, expr2, expr3) function: IF expr1 is true, expr2 is returned; otherwise, expr3 is returned.
Mysql>? IF FUNCTION
Mysql> select IF (salary> 2000, 'high', 'low') from salary;
+ ------------------------------------ +
| If (salary> 2000, 'high', 'low') |
+ ------------------------------------ +
| Low ???????????????????????????????? |
| Low ???????????????????????????????? |
| High ??????????????????????????????? |
| High ??????????????????????????????? |
| High ??????????????????????????????? |
| Low ???????????????????????????????? |
+ ------------------------------------ +

2. IFNULL (expr1, expr2) function: If expr1 is NULL, expr2 is returned.
Mysql>? IFNULL
Mysql> select ifnull (salary, 0) from salary;
+ ------------------ +
| Ifnull (salary, 0) |
+ ------------------ +
| ?????????? 1000.00 |
| ?????????? 2000.00 |
| ?????????? 3000.00 |
| ?????????? 4000.00 |
| ?????????? 5000.00 |
| ????????????? 0.00 |
+ ------------------ +

3. NULLIF (expr1, expr2) function: If expr1 = expr2, NULL is returned; otherwise, expr1 is returned.
Mysql>? NULLIF
Mysql> select nullif (salary, 2000) from salary;
+ --------------------- +
| Nullif (salary, 2000) |
+ --------------------- +
| ????????????? 1000.00 |
| ???????????????? NULL |
| ????????????? 3000.00 |
| ????????????? 4000.00 |
| ????????????? 5000.00 |
| ???????????????? NULL |
+ --------------------- +

4. CASE Function
Syntax:
CASE? Value? WHEN [compare_value] THEN result [WHEN? [Compare_value]?THEN? Result...] [ELSE? Result]?END
Or
CASE WHEN? [Condition]?THEN? Result [WHEN [condition]?THEN? Result...] [ELSE? Result]?END
Mysql>? CASE FUNCTION
Mysql> select case when salary <= 2000 then 'low' else 'high' end from salary;
+ ----------------------------------------------------- +
| Case when salary <= 2000 then 'low' else 'high' end |
+ ----------------------------------------------------- +
| Low ?????????????????????????????????????? ??????????? |
| Low ?????????????????????????????????????? ??????????? |
| High ?????????????????????????????????????? ?????????? |
| High ?????????????????????????????????????? ?????????? |
| High ?????????????????????????????????????? ?????????? |
| High ?????????????????????????????????????? ?????????? |
+ ----------------------------------------------------- +

Mysql> select case salary when 1000 then 'low' when 2000 then 'mid 'else' High 'end from salary;
+ ----------------------------------------------------------------------- +
| Case salary when 1000 then 'low' when 2000 then 'mid 'else' High 'end |
+ ----------------------------------------------------------------------- +
| Low ?????????????????????????????????????? ????????????????????????????? |
| Mid ?????????????????????????????????????? ????????????????????????????? |
| High ?????????????????????????????????????? ???????????????????????????? |
| High ?????????????????????????????????????? ???????????????????????????? |
| High ?????????????????????????????????????? ???????????????????????????? |
| High ?????????????????????????????????????? ???????????????????????????? |
+ ----------------------------------------------------------------------- +

5. Other common functions
1. DATABASE () function: Used to query the name of the currently used DATABASE (similar to the Oracle show user ;)
Mysql>? Database
Mysql> select database ();
+ ------------ +
| DATABASE () |
+ ------------ +
| Test ??????? |
+ ------------ +

2. VERSION () function: You can query the VERSION of the database in use.
Mysql>? Version
Mysql> select version ();
+ ------------ +
| VERSION ()?? |
+ ------------ +
| 5.0.22-log |
+ ------------ +

3. USER () function: query the current Login USER Name
Mysql> select user ();
+ ---------------- +
| USER ()????????? |
+ ---------------- +
| Root @ localhost |
+ ---------------- +

4. INET_ATON (expr) function: query the network byte representation of the IP address. The inverse function is INET_NTOA.
Mysql> SELECT INET_ATON ('194.192.183 ');
+ ------------------------------ +
| INET_ATON ('194.192.183 ') |
+ ------------------------------ +
| ??????????????????? 2428682423 |
+ ------------------------------ +

5. INET_NTOA (expr) function: queries the IP address represented by the byte sequence of the network. It is the inverse function of INET_ATON.
Mysql>? Inet_ntoa
Mysql> SELECT INET_NTOA (2428682423 );
+ ----------------------- +
| INET_NTOA (2428682423) |
+ ----------------------- +
| 144.194.192.183 ??????? |
+ ----------------------- +

Vi. Summary
If you have something to do, please ask for help. Maybe this is the benefit of open source. She will try her best to consider the convenience of your query and reference. MySQL functions are rich, and the functions used in the above experiments are very common.
This is a simple example. If you can think of the functions provided by MySQL and check the help system, you will get everything.
If these functions still do not meet your desire for knowledge, please refer to the official documentation chapter 12th: Functions and operators, address: http://dev.mysql.com/doc/refman/5.1/zh/functions.html

Source: http://my.oschina.net/taisha/blog/63451

?

?