编程知识 cdmana.com

The way to learn and summarize MySQL (Chapter 5: functions)

Catalog

MySQL The road of learning and summing up ( Chapter one : Services and database management )

MySQL The road of learning and summing up ( Chapter two : surface )

MySQL The road of learning and summing up ( The third chapter : data type )

MySQL The road of learning and summing up ( Chapter four : Operator )

MySQL The road of learning and summing up ( The fifth chapter : function )

1、MySQL String functions commonly used in

function function
CANCAT(S1,S2,…Sn) 
Connect S1,S2,…Sn For a string
INSERT(str,x,y,instr) 
The string str From x Position start ,y Substrings of characters long are replaced with strings instr
LOWER(str) 
The string str All characters in are lowercase
UPPER(str) 
The string str All characters in are capitalized  
LEFT(str ,x)
Return string str The leftmost x Characters
RIGHT(str,x) 
Return string str Far right x Characters
LPAD(str,n ,pad) 
Use string pad Yes str Fill on the far left , Until the length is n Character length
RPAD(str,n,pad) 
Use string pad Yes str Fill on the far right , Until the length is n Character length
LTRIM(str) 
Remove string str The space on the left
RTRIM(str)
Remove string str The space at the end of the line
REPEAT(str,x) 
return str repeat x Results of
REPLACE(str,a,b) 
Use string b Replace string str All the strings that appear in a 
STRCMP(s1,s2) 
Compare strings s1 and s2 
TRIM(str) 
Remove the spaces at the end of a string and at the beginning of a line
SUBSTRING(str,x,y) 
Return from string str x From the position y A character length string

1:CANCAT(S1,S2,…Sn) function , Concatenate the incoming parameters into a string . Be careful : and null Put it together and go back NULL

mysql> select concat('aaa','bbb','d'),concat('dd',null);
+-------------------------+-------------------+
| concat('aaa','bbb','d') | concat('dd',null) |
+-------------------------+-------------------+
| aaabbbd                 | NULL              |
+-------------------------+-------------------+
1 row in set (0.00 sec)

2:INSERT(str ,x,y,instr) function : The string str From x Position start ,y Substrings of characters long are replaced with strings instr.

The string “123456” From the first 3 Character start , hinder 3 Replace the characters with “me”

mysql> select insert('123456',3,3,'me');
+---------------------------+
| insert('123456',3,3,'me') |
+---------------------------+
| 12me6                     |
+---------------------------+
1 row in set (0.02 sec)

3:LOWER(str) and UPPER(str) function : Convert a string to lowercase or uppercase .

mysql> select lower("ZHANG"),upper("zhang");
+----------------+----------------+
| lower("ZHANG") | upper("zhang") |
+----------------+----------------+
| zhang          | ZHANG          |
+----------------+----------------+
1 row in set (0.00 sec)

4:LEFT(str,x) and RIGHT(str,x) function : Return the leftmost x Characters and the rightmost x Characters . Be careful : If the second parameter is NULL, Then no string will be returned . 

mysql> select left('zhang',2),left('zhang',null),right('zhang',2),left('zhang',10);
+-----------------+--------------------+------------------+------------------+
| left('zhang',2) | left('zhang',null) | right('zhang',2) | left('zhang',10) |
+-----------------+--------------------+------------------+------------------+
| zh              | NULL               | ng               | zhang            |
+-----------------+--------------------+------------------+------------------+
1 row in set (0.00 sec)

 5:LPAD(str,n ,pad) and RPAD(str,n ,pad) function : Use string pad Yes str Fill in the leftmost and rightmost , Until the length is n Character length .

mysql> select lpad('bin',8,'zhang'),rpad('zhang',8,'bin'),rpad('beijing',20,'2008');
+-----------------------+-----------------------+---------------------------+
| lpad('bin',8,'zhang') | rpad('zhang',8,'bin') | rpad('beijing',20,'2008') |
+-----------------------+-----------------------+---------------------------+
| zhangbin              | zhangbin              | beijing2008200820082      |
+-----------------------+-----------------------+---------------------------+
1 row in set (0.00 sec)

6:LTRIM(str) and RTRIM(str) function : Remove string str Left and right spaces .

mysql> select '   zhang',ltrim('   zhang'),rtrim('zhang    ');
+----------+-------------------+--------------------+
| zhang    | ltrim('   zhang') | rtrim('zhang    ') |
+----------+-------------------+--------------------+
|    zhang | zhang             | zhang              |
+----------+-------------------+--------------------+
1 row in set (0.00 sec)

7:REPEAT(str,x) function : return str repeat x Results of .

mysql> select repeat('mysql',5);
+---------------------------+
| repeat('mysql',5)         |
+---------------------------+
| mysqlmysqlmysqlmysqlmysql |
+---------------------------+
1 row in set (0.00 sec)

8:REPLACE(str,a,b) function : Use string b Replace string str All the strings that appear in a.

mysql> select replace('mysql','sql','ddd');
+------------------------------+
| replace('mysql','sql','ddd') |
+------------------------------+
| myddd                        |
+------------------------------+
1 row in set (0.00 sec)

9:STRCMP(s1,s2) function : Compare strings s1 and s2 Of ASCII The size of the code value . If s1 Than s2 Small , Then the return -1; If s1 And s2 equal , Then the return 0; If s1 Than s2 Big , Then the return 1.

mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b'),strcmp('a','A');
+-----------------+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') | strcmp('a','A') |
+-----------------+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |               0 |
+-----------------+-----------------+-----------------+-----------------+
1 row in set (0.01 sec)

10:TRIM(str) function : Remove the spaces at the beginning and end of the target string .

mysql> select trim('  111 $  mysql $   ');
+-----------------------------+
| trim('  111 $  mysql $   ') |
+-----------------------------+
| 111 $  mysql $              |
+-----------------------------+
1 row in set (0.01 sec)

11:SUBSTRING(str,x,y) function : Return from string str No x From the position y A character length string .

mysql> select substring('mysqlisdd',4,4);
+----------------------------+
| substring('mysqlisdd',4,4) |
+----------------------------+
| qlis                       |
+----------------------------+
1 row in set (0.00 sec)

 2、 Numerical function

function
function
ABS(x) 
return x The absolute value of  
CEIL(x) 
Return is greater than the x The maximum integer value of
FLOOR(x) 
Back to less than x The maximum integer value of  
MOD(x,y) 
return x/y The mold
RAND() 
return 0 To 1 The random value in
ROUND(x,y) 
Returns the parameter x The rounding of is y A decimal value  
TRUNCATE(x,y) 
Return to digital x Truncated to y The result of decimal places

1: ABS(x) function : return x The absolute value of .

mysql> select abs(-0.3),abs(0.3);
+-----------+----------+
| abs(-0.3) | abs(0.3) |
+-----------+----------+
|       0.3 |      0.3 |
+-----------+----------+
1 row in set (0.36 sec)

2:CEIL(x) function , Return is greater than the x Maximum integer for

mysql> select ceil(-0.2),ceil(0.2);
+------------+-----------+
| ceil(-0.2) | ceil(0.2) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)

 3:FLOOR(x) function , Back to less than x Maximum integer for , and CEIL Just the opposite

mysql> select floor(-0.2),floor(0.2);
+-------------+------------+
| floor(-0.2) | floor(0.2) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

4:MOD(x,y) function : return x/y The mold . and x%y Same result for , Either the modulus or the modulus is NULL The result is NULL. As shown in the following example :

mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)

5:RAND() function , return 0 To 1 Random number between

mysql> select rand(),rand();
+-------------------+---------------------+
| rand()            | rand()              |
+-------------------+---------------------+
| 0.541937319135235 | 0.10546984067696132 |
+-------------------+---------------------+
1 row in set (0.00 sec)

6:ROUND(x,y) function , Returns the parameter x The rounding of is y A decimal value . If it's an integer , Will be retained y The number of digits 0; If you don't write y, By default y by 0, the x Round and round .

mysql> select round(1.2,2),round(1.3),round(1,2);
+--------------+------------+------------+
| round(1.2,2) | round(1.3) | round(1,2) |
+--------------+------------+------------+
|         1.20 |          1 |          1 |
+--------------+------------+------------+
1 row in set (0.00 sec)

7:TRUNCATE(x,y) function : Return to digital x Truncated to y The result of decimal places .( Be careful TRUNCATE and ROUND The difference is that TRUNCATE Just truncation , Instead of rounding .)

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+
1 row in set (0.31 sec)

 

3、 Date and time functions

MySQL Common date time functions in

function function
CURDATE()   Return current date
CURTIME()   Return current time
NOW()  Returns the current date and time
UNIX_TIMESTAMP(date)  Return date date Of UNIX Time stamp
FROM_UNIXTIME return UNIX The date value of the timestamp
WEEK(date) Return date date For the week of the year
YEAR(date) Return date date A year of
HOUR(time) return time The hour value of
MINUTE(time)   return time The minute value of
MONTHNAME(date)  return date The name of the month
DATE_FORMAT(date,fmt) Return by string fmt Format date date value
DATE_ADD(date,INTERVAL expr type)  Returns a date or time value plus the time value of an interval
DATEDIFF(expr,expr2) Return to start time expr And end time expr2 Days between

1:CURDATE() function : Return current date , Include only mm / DD / yyyy

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-05-25 |
+------------+
1 row in set (0.00 sec)

2:CURTIME() function : Return current time , Only hours, minutes, seconds

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:07:06  |
+-----------+
1 row in set (0.00 sec)

3:NOW() function : Returns the current date and time , Year, month, day, hour, minute and second all contain .

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-25 14:07:33 |
+---------------------+
1 row in set (0.00 sec)

4:UNIX_TIMESTAMP(date) function : Return date date Of UNIX Time stamp .

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1621922906 |
+-----------------------+
1 row in set (0.30 sec)

5:FROM_UNIXTIME ( unixtime ) Letter Count : return return UNIXTIME when between stamp Of Japan period value , and UNIX_TIMESTAMP(date) Reverse each other .

mysql> select from_unixtime(1621922906);
+---------------------------+
| from_unixtime(1621922906) |
+---------------------------+
| 2021-05-25 14:08:26       |
+---------------------------+
1 row in set (0.31 sec)

6:WEEK(DATE) and YEAR(DATE) function : The former returns the given date as the week of the year , The latter returns the date given .

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          21 |        2021 |
+-------------+-------------+
1 row in set (0.00 sec)

7:HOUR(time) and MINUTE(time) function : The former returns the hours of the given time , The latter returns the minutes of the given time .

mysql> select hour(now()),minute(now());
+-------------+---------------+
| hour(now()) | minute(now()) |
+-------------+---------------+
|          14 |            11 |
+-------------+---------------+
1 row in set (0.00 sec)

8:MONTHNAME(date) function : return date The English name of the month .

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| May              |
+------------------+
1 row in set (0.30 sec)

9:DATE_FORMAT(date,fmt) function : By string fmt Format date date value , This function can display the date in the specified format , The available formants are shown in the table :

Format symbol Format specification
%S,%s Two digit seconds (00,01,...,59)
%i  Two digit division (00,01,...,59)
%H Two digit hours ,24 Hours (00,01,...,23)
%h,%I Two digit hours ,12 Hours (01,02,...,12)
%k Hours in digital form ,24 Hours (0,1,...,23)
%l  Hours in digital form ,12 Hours (1,2,...,12)
%T 24 Hours of time (hh:mm:ss)
%r 12 Hours of time (hh:mm:ssAM or hh:mm:ssPM)
%p  AM or PM
%W  The name of each day of the week (Sunday,Monday,...,Saturday)
%a Abbreviation for the name of each day of the week (Sun,Mon,...,Sat)
%d Two figures indicate the number of days in the month (00,01,...,31)
%e The number of days in the month (1,2,...,31)
%D The English suffix indicates the number of days in the month (1st,2nd,3rd,...)
%w  Number the days of the week (0=Sunday,1=Monday,...,6=Saturday)
%j  With 3 The number of digits represents the number of days in the year (001,002,...,366)
%U Zhou (0,1,52), among Sunday For the first day of the week
%u  Zhou (0,1,52), among Monday For the first day of the week
%M Month name (January,February,...,December)
%b  Abbreviated month name (January,February,...,December)
%m The month represented by two figures (01,02,...,12)
%c The number of months (1,2,...,12)
%Y 4 The year in digits
%y Two digit year
%% Direct value “%”
mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| May,25th,2021                 |
+-------------------------------+
1 row in set (0.00 sec)

 10:DATE_ADD(date,INTERVAL expr type) function : Return with the given date date Difference between INTERVAL The date of the time period .

            among INTERVAL Is the interval type keyword ,expr It's an expression , This expression corresponds to the following type ,type
    It's the interval type ,MySQL Provides 13 There are three types of intervals , The following table

MySQL The date interval type in

Expression type describe Format
HOUR Hours hh
MINUTE branch mm
SECOND second    ss
YEAR year YY
MONTH month MM
DAY Japan DD
YEAR_MONTH Year and month YY-MM
DAY_HOUR Days and hours DD hh
DAY_MINUTE Days and minutes DD hh:mm
DAY_ SECOND Days and seconds DD hh:mm:ss
HOUR_MINUTE Hours and minutes hh:mm
HOUR_SECOND Hours and seconds hh:ss
MINUTE_SECOND Minutes and seconds mm:ss

example : The first 1 Column returns the current date time , The first 2 Column returns the distance from the current date 31 Days later, the date and time , The first 3 Column returns the date time one year or two months after the current date .

mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2021-05-25 14:32:30 | 2021-06-25 14:32:30 | 2022-07-25 14:32:30    |
+---------------------+---------------------+------------------------+
1 row in set (0.03 sec)

You can also use a negative number to return a previous date and time , No 1 Column returns the current date time , The first 2 Column returns the distance from the current date 31 The date and time of the day before , The first 3 Column returns the date time one year or two months before the current date .

mysql> select now() current,date_add(now(),interval -31 day) bef31days,date_add(now(),interval '-1_-2' year_month) bef_oneyear_twomonth;
+---------------------+---------------------+----------------------+
| current             | bef31days           | bef_oneyear_twomonth |
+---------------------+---------------------+----------------------+
| 2021-05-25 14:34:38 | 2021-04-24 14:34:38 | 2020-03-25 14:34:38  |
+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)

 11:DATEDIFF(date1,date2) function : The number of days used to calculate the difference between two dates

mysql> select datediff('2013-09-01',now());
+------------------------------+
| datediff('2013-09-01',now()) |
+------------------------------+
|                        -2823 |
+------------------------------+
1 row in set (0.30 sec)

 

4、 Flow function

MySQL Process functions in

function function
IF(value,t f) If value Is the true , return t; Otherwise return to f
IFNULL(value1,value2) If value1 Not empty return value1, Otherwise return to value2

CASE WHEN [value1]
THEN[result1]…ELSE[default]END

If value1 Is the true , return result1, Otherwise return to default

CASE [expr] WHEN [value1]
THEN[result1]…ELSE[default]END

If expr be equal to value1, return result1, Otherwise return to default

1:IF(value,t f), Example : Create and initialize an employee payroll , Insert some test data , We think the monthly salary is 2000 Employees with a salary of more than RMB are highly paid , use “high” Express ; and 2000 Staff below $1 are low paid , use “low” Express

mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.47 sec)

mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from salary;
+--------+---------+
| userid | salary  |
+--------+---------+
|      1 | 1000.00 |
|      2 | 2000.00 |
|      3 | 3000.00 |
|      4 | 4000.00 |
|      5 | 5000.00 |
|      1 |    NULL |
+--------+---------+
6 rows in set (0.00 sec)

mysql> select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low                          |
| low                          |
| high                         |
| high                         |
| high                         |
| low                          |
+------------------------------+
6 rows in set (0.31 sec)

2:IFNULL(value1,value2) function , Usually used to replace NULL It's worth it , We know NULL It's not worth it Can participate in numerical calculation , The following sentence is to put NULL Value to use 0 To replace

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|             0.00 |
+------------------+
6 rows in set (0.00 sec)

3:CASE WHEN [value1] THEN[result1]…ELSE[default]END Letter Count : I People also can With use case when…then Function to realize the problem of high salary and low salary in the above example

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                                              |
+---------------------------------------------------+
6 rows in set (0.29 sec)

4:CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END function : There are also many kinds of situations in which the post can be transferred There are many levels of salary , For example, the following example is divided into high 、 in 、 low 3 In this case .

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                                                                  |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

 5、 Other common functions

MySQL Other common functions in

function   function
DATABASE()  Returns the current database name
VERSION() Return the current database version
USER() Return the current login user name
INET_ATON(IP)  return IP The number of the address means
INET_NTOA(num) Return the... Represented by the number IP Address
PASSWORD(str) Return string str The encrypted version of
MD5()  Return string str Of MD5 value

1:DATABASE() function : Returns the current database name .

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

2:VERSION() function : Return the current database version .

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26    |
+-----------+
1 row in set (0.00 sec)

3:USER() function : Return the current login user name .

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4:INET_ATON(IP) function : return IP Network byte order representation of address .

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.30 sec)

5:INET_NTOA(num) function : Returns the network byte order represented by IP Address .

mysql> select inet_ntoa('3232235777');
+-------------------------+
| inet_ntoa('3232235777') |
+-------------------------+
| 192.168.1.1             |
+-------------------------+
1 row in set (0.00 sec)

6:PASSWORD(str) function : Return string str The encrypted version of , One 41 Bit long string .

mysql> select password('1223456');
+-------------------------------------------+
| password('1223456')                       |
+-------------------------------------------+
| *3B5C2394E86BB91F1D03C5A1F2D3962BB287590B |
+-------------------------------------------+
1 row in set, 1 warning (0.33 sec)

7:MD5(str) function : Return string str Of MD5 value , It is often used to encrypt the data in the application .

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.30 sec)

End of this chapter !!!

版权声明
本文为[Go far with the wind]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/06/20210606221229841y.html

Scroll to Top