Chapter 07 MySQL single line function

1. Understanding of function

1.1 what is a function

Function runs through the use of computer language. What is the role of function? It can encapsulate the code we often use and call it directly when necessary. This not only improves the code efficiency, but also improves the maintainability. In SQL, we can also use functions to operate on the retrieved data. Using these functions can greatly improve the efficiency of user management of the database.

From the perspective of function definition, we can divide functions into built-in functions and user-defined functions. The SQL language also includes built-in functions and custom functions. Built in functions are general functions built in the system, while custom functions are written according to our own needs. This chapter and the next chapter explain the built-in functions of SQL.

1.2 differences of different DBMS functions

When we use SQL language, we do not directly deal with this language, but use different database software, namely DBMS. There are great differences between DBMS, which is much greater than the differences between different versions of the same language. In fact, only a few functions are supported by DBMS at the same time. For example, most DBMS use (|) or (+) as the splicer, while the string splicing function in MySQL is concat(). Most DBMS will have their own specific functions, which means that the code portability of SQL functions is very poor, so special attention should be paid to when using functions.

1.3 built in functions and classification of MySQL

MySQL provides rich built-in functions, which make data maintenance and management more convenient, can better provide data analysis and statistics functions, and improve the efficiency of data analysis and statistics for developers to a certain extent.

The built-in functions provided by MySQL can be divided into numerical functions, string functions, date and time functions, process control functions, encryption and decryption functions, MySQL information acquisition functions, aggregation functions, etc. Here, I divide these rich built-in functions into two categories: single line functions and aggregate functions (or grouping functions).

Two SQL functions

Single-Row Functions

Operation data object
Accept parameters and return a result
Transform only one row
One result per row
Can be nested
The parameter can be a column or a value

2. Numerical function

2.1 basic functions

function	usage
ABS(x)	Returns the absolute value of x
SIGN(X)	Returns the symbol of the X. A positive number returns 1, a negative number returns - 1, and 0 returns 0
PI()	Returns the value of PI
CEIL(x)，CEILING(x)	Returns the smallest integer greater than or equal to a value
FLOOR(x)	Returns the largest integer less than or equal to a value
LEAST(e1,e2,e3...)	Returns the minimum value in the list
GREATEST(e1,e2,e3...)	Returns the maximum value in the list
MOD(x,y)	Returns the remainder of X divided by Y
RAND()	Return random values from 0 to 1
RAND(x)	Returns a random value of 0 ~ 1, where the value of X is used as the seed value, and the same x value will produce the same random number
ROUND(x)	Returns the integer closest to X after rounding the value of X
ROUND(x,y)	Returns the value closest to X after rounding the value of X and retains it to the Y digit after the decimal point
TRUNCATE(x,y)	Returns the result of truncating the number x to y decimal places
SQRT(x)	Returns the square root of X. NULL is returned when the value of X is negative

give an example:

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

2.2 angle and radian exchange function

function	usage
RADIANS(x)	Converts an angle to radians, where parameter x is the angle value
DEGREES(x)	Converts radians to angles, where parameter x is the radian value

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

2.3 trigonometric function

function	usage
SIN(x)	Returns the sine of X, where x is the radian value
ASIN(x)	Returns the arcsine value of x, that is, the value whose sine is x. Returns NULL if the value of x is not between - 1 and 1
COS(x)	Returns the cosine value of X, where x is the radian value
ACOS(x)	Returns the inverse cosine of x, that is, gets the value whose cosine is x. Returns NULL if the value of x is not between - 1 and 1
TAN(x)	Returns the tangent of X, where x is the radian value
ATAN(x)	Returns the arctangent of x, that is, the value whose tangent is x
ATAN2(m,n)	Returns the arctangent of two parameters
COT(x)	Returns the cotangent of X, where x is the radian value

give an example:

The ATAN2(M,N) function returns the arctangent of two parameters.
Compared with ATAN(X) function, ATAN2(M,N) requires two parameters. For example, there are two points point(x1,y1) and point(x2,y2). Using ATAN(X) function to calculate the arctangent is ATAN((y2-y1)/(x2-x1)), and using ATAN2(M,N) to calculate the arctangent is ATAN2(y2-y1,x2-x1). It can be seen from the usage that when x2-x1 is equal to 0, ATAN(X) function will report an error, while ATAN2(M,N) function can still be calculated.

The usage example of ATAN2(M,N) function is as follows:

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
FROM DUAL;

2.4 exponent and logarithm

function	usage
POW(x,y)，POWER(X,Y)	Returns the y power of x
EXP(X)	Returns the X power of E, where e is a constant, 2.718281828459045
LN(X)，LOG(X)	Returns the logarithm of X based on e. when X < = 0, the returned result is NULL
LOG10(X)	Returns the logarithm of X based on 10. When X < = 0, the returned result is NULL
LOG2(X)	Returns the logarithm of X based on 2. When X < = 0, NULL is returned

mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
    -> FROM DUAL;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2)           | LN(10)            | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
|       32 |         16 | 7.38905609893065 | 2.302585092994046 |         1 |       2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)

2.5 conversion between hexadecimals

function	usage
BIN(x)	Returns the binary encoding of x
HEX(x)	Returns the hexadecimal encoding of x
OCT(x)	Returns the octal encoding of x
CONV(x,f1,f2)	Returns a binary number from f1 to f2

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
    -> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010    | A       | 12      | 2            |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

3. String function

function	usage
ASCII(S)	Returns the ASCII value of the first character in string S
CHAR_LENGTH(s)	Returns the number of characters in the string s. Function and CHARACTER_LENGTH(s) is the same
LENGTH(s)	Returns the number of bytes of string s, which is related to the character set
CONCAT(s1,s2,......,sn)	Connect S1, S2,..., Sn as a string
CONCAT_WS(x, s1,s2,......,sn)	The same as the CONCAT(s1,s2,...) function, but add x between each string
INSERT(str, idx, len, replacestr)	Replace the string str with the string replacestr starting from the idx position and the substring of len characters long
REPLACE(str, a, b)	Replace all occurrences of string a in string str with string b
UPPER(s) or UCASE(s)	Converts all letters of the string s to uppercase letters
LOWER(s) or LCASE(s)	Converts all letters of the string s to lowercase
LEFT(str,n)	Returns the leftmost n characters of the string str
RIGHT(str,n)	Returns the rightmost n characters of the string str
LPAD(str, len, pad)	Fill the leftmost part of str with the string pad until the length of str is len characters
RPAD(str ,len, pad)	Fill the rightmost part of str with the string pad until the length of str is len characters
LTRIM(s)	Remove the space to the left of the string s
RTRIM(s)	Remove the space to the right of the string s
TRIM(s)	Remove the spaces at the beginning and end of the string s
TRIM(s1 FROM s)	Remove s1 from the beginning and end of string s
TRIM(LEADING s1 FROM s)	Remove s1 at the beginning of string s
TRIM(TRAILING s1 FROM s)	Remove s1 at the end of string s
REPEAT(str, n)	Returns the result of str repeated n times
SPACE(n)	Return n spaces
STRCMP(s1,s2)	Compare the size of ASCII values of strings S1 and S2
SUBSTR(s,index,len)	Returns len characters from the index position of string s, which is the same as SUBSTRING(s,n,len) and MID(s,n,len)
LOCATE(substr,str)	Returns the position where the string substr first appears in the string str, which has the same effect on POSITION(substr IN str) and INSTR(str,substr). Not found, return 0
ELT(m,s1,s2,...,sn)	Returns the string at the specified position. If m=1, s1 is returned. If m=2, s2 is returned. If m=n, sn is returned
FIELD(s,s1,s2,...,sn)	Returns the first occurrence of the string s in the string list
FIND_IN_SET(s1,s2)	Returns the position where the string s1 appears in the string s2. Where s2 is a comma separated string
REVERSE(s)	Returns the s inverted string
NULLIF(value1,value2)	Compare two strings. If value1 is equal to value2, NULL is returned; otherwise, value1 is returned

Note: in MySQL, the position of the string starts with 1.

give an example:

mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma')
    -> FROM DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
|                                0 |                                 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL                    | mysql               |
+-------------------------+---------------------+
1 row in set (0.00 sec)

4. Date and time functions

4.1 acquisition date and time

function	usage
CURDATE() ，CURRENT_DATE()	Returns the current date, including only year, month and day
CURTIME() ， CURRENT_TIME()	Returns the current time, including only hours, minutes and seconds
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	Returns the current system date and time
UTC_DATE()	Returns the UTC date
UTC_TIME()	Returns UTC time

give an example:

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

4.2 date and time stamp conversion

function	usage
UNIX_TIMESTAMP()	Returns the current time as a UNIX timestamp. SELECT UNIX_ TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	Returns the time date as a UNIX timestamp.
FROM_UNIXTIME(timestamp)	Convert UNIX timestamp time to normal format time

give an example:

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1576380910 |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
|                1576339200 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1576380969 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
|                            1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10       |
+---------------------------+
1 row in set (0.00 sec)

4.3 get month, week, number of weeks, days and other functions

function	usage
YEAR(date) / MONTH(date) / DAY(date)	Returns a specific date value
HOUR(time) / MINUTE(time) / SECOND(time)	Returns a specific time value
MONTHNAME(date)	Return month: January
DAYNAME(date)	Return day of the week: MONDAY, TUESDAY.....SUNDAY
WEEKDAY(date)	Return to the day of the week. Note that week 1 is 0 and week 2 is 1,... Sunday is 6
QUARTER(date)	The quarter corresponding to the return date ranges from 1 to 4
WEEK(date) ， WEEKOFYEAR(date)	Returns the week ordinal of the year
DAYOFYEAR(date)	The return date is the day of the year
DAYOFMONTH(date)	The day of the month in which the return date is located
DAYOFWEEK(date)	Return to the day of the week. Note: Sunday is 1 and Monday is 2,... Saturday is 7

give an example:

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

4.4 operation function of date

function	usage
EXTRACT(type FROM date)	Returns a specific part of a specified date, and type specifies the returned value

Value and meaning of type in EXTRACT(type FROM date) function:

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

4.5 function of time and second conversion

function	usage
TIME_TO_SEC(time)	Converts time to seconds and returns the result value. The conversion formula is: hour * 3600 + minute * 60 + second
SEC_TO_TIME(seconds)	Convert the seconds description to a time that contains hours, minutes, and seconds

give an example:

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|               78774 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54            |
+--------------------+
1 row in set (0.12 sec)

4.6 function for calculating date and time

Group 1:

function	usage
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	Returns the date and time of the INTERVAL period different from the given date and time
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	Returns the date with an INTERVAL different from date

The value of type in the above functions:

give an example:

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #Can be negative
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #Single quotation mark required
FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

Group 2:

function	usage
ADDTIME(time1,time2)	Returns the time of time1 plus time2. When time2 is a number, it represents seconds and can be negative
SUBTIME(time1,time2)	Returns the time after time1 minus time2. When time2 is a number, it represents seconds and can be negative
DATEDIFF(date1,date2)	Returns the number of days between date1 - date2
TIMEDIFF(time1, time2)	Returns the interval between time1 - time2
FROM_DAYS(N)	Returns the date N days after January 1, 0000
TO_DAYS(date)	Return date the number of days from January 1, 0000
LAST_DAY(date)	Returns the date of the last day of the month in which date is located
MAKEDATE(year,n)	Returns a date for a given year and the number of days in that year
MAKETIME(hour,minute,second)	Combines the given hours, minutes, and seconds into a time and returns
PERIOD_ADD(time,n)	Returns the time after time plus n

give an example:

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50)  |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); 
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11        |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01     |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01       |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01        |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01        |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
|               20200101010102 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|          737773 |
+----------------+
1 row in set (0.00 sec)

For example: how many new users are there in 7 days?

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

4.7 date formatting and parsing

function	usage
DATE_FORMAT(date,fmt)	Format the date value according to the string fmt
TIME_FORMAT(time,fmt)	Format the time value according to the string fmt
GET_FORMAT(date_type,format_type)	Returns the display format of the date string
STR_TO_DATE(str, fmt)	str is parsed according to the string fmt to a date

Above non GET_FORMAT characters commonly used for fmt parameters in format function:

Formatter	explain	Formatter	explain
%Y	Four digits indicate the year	%y	Two digits for year
%M	Month name means month (January,...)	%m	Two digits represent the month (01, 02, 03...)
%b	Abbreviated month name (Jan., Feb.,...)	%c	Numbers represent months (1, 2, 3,...)
%D	The English suffix indicates the number of days in a month (1st,2nd,3rd,...)	%d	Two digits indicate the number of days in the month (01,02...)
%e	The number of days in a month (1,2,3,4,5,...)
%H	Two digits represent decimals, 24-hour system (01,02..)	%h and% I	Two digits represent hours, 12 hour system (01,02...)
%k	Hours in digital form, 24-hour system (1,2,3)	%l	Digital form represents hour, 12 hour system (1,2,3,4...)
%i	Two digits represent minutes (00,01,02)	%S and% s	Two digits represent seconds (00,01,02...)
%W	Name of the week in the week (Sunday...)	%a	Abbreviation of week in a week (Sun., Mon.,Tues.,..)
%w	The number of days in the week (0=Sunday,1=Monday...)
%j	The number of days in the year is expressed in 3 digits (001002...)	%U	The number indicates the week of the year, (1,2,3.) where Sunday is the first day of the week
%u	The number indicates the week of the year, (1,2,3.) where Monday is the first day of the week
%T	24-hour system	%r	12 hour system
%p	AM or PM	%%	Represents%

GET_ Date in format function_ Type and format_ The value of the type parameter is as follows:

give an example:

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34                        |
+--------------------------------+
1 row in set (0.00 sec)

SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+
1 row in set (0.00 sec)

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;

mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); 
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5. Process control function

The process processing function can execute different processing processes according to different conditions, and can realize different condition selection in SQL statements. The process processing functions in MySQL mainly include IF(), IFNULL() and CASE() functions.

function	usage
IF(value,value1,value2)	If value is TRUE, value1 is returned; otherwise, value2 is returned
IFNULL(value1, value2)	If value1 is not NULL, value1 is returned; otherwise, value2 is returned
CASE WHEN condition 1 THEN result 1 WHEN condition 2 THEN result 2... [else resultn] end	Equivalent to Java if...else if...else
Case expr when constant value 1 THEN value 1 WHEN constant value 1 THEN value 1... [else value n] END	Equivalent to Java switch...case

SELECT IF(1 > 0,'correct','error')    
->correct

SELECT IFNULL(null,'Hello Word')
->Hello Word

SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

SELECT CASE 1 
　　WHEN 1 THEN 'I am 1'
　　WHEN 2 THEN 'I'm 2'
ELSE 'who are you'

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN 'High salary' 
				  WHEN salary>=10000 THEN 'potential share'  
				  WHEN salary>=8000 THEN 'loser' 
				  ELSE 'Grassroots' END  "describe"
FROM employees;

SELECT oid,`status`, CASE `status` WHEN 1 THEN 'Unpaid' 
								   WHEN 2 THEN 'Paid' 
								   WHEN 3 THEN 'Shipped'  
								   WHEN 4 THEN 'Confirm receipt'  
								   ELSE 'Invalid order' END 
FROM t_order;

mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes                                                                  |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;  
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown                                                             |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
|                                               1 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
|                                               -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       			   ELSE      salary END     "REVISED_SALARY"
FROM   employees;

Exercise: query the information of employees with department numbers 10, 20 and 30. If the department number is 10, print 1.1 times of their salary, if the department number is 20, print 1.2 times of their salary, and if the department number is 30, print 1.3 times of their salary.

6. Encryption and decryption function

The encryption and decryption function is mainly used to encrypt and decrypt the data in the database to prevent the data from being stolen by others. These functions are very useful in ensuring database security.

function	usage
PASSWORD(str)	Returns the encrypted version of the string str, a 41 bit long string. The encryption result is irreversible and is often used for user password encryption
MD5(str)	Returns the md5 encrypted value of the string str, which is also an encryption method. If the parameter is NULL, NULL is returned
SHA(str)	Calculate and return the encrypted password string from the original plaintext password str. when the parameter is NULL, NULL is returned. SHA encryption algorithm is more secure than MD5.
ENCODE(value,password_seed)	Return to use password_seed is used as the encryption password to encrypt value
DECODE(value,password_seed)	Return to use password_seed decrypts value as an encrypted password

As you can see, the ENCODE(value,password_seed) function and the DECODE(value,password_seed) function are inverse functions of each other.

give an example:

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql')                         | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |                |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

SELECT md5('123')
->202cb962ac59075b964b07152d234b70

SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50

mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg　¼　ìÉ                  |
+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql                                   |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

7. MySQL information function

MySQL has built-in functions that can query MySQL information. These functions are mainly used to help database developers or operation and maintenance personnel better maintain the database.

function	usage
VERSION()	Returns the version number of the current MySQL
CONNECTION_ID()	Returns the number of connections to the current MySQL server
DATABASE()，SCHEMA()	Returns the database where the MySQL command line is currently located
USER()，CURRENT_USER(),SYSTEM_USER()，SESSION_USER()	Returns the user name currently connected to MySQL. The returned result format is "host name @ user name"
CHARSET(value)	Returns the character set of the string value argument
COLLATION(value)	Returns the comparison rule for the string value

give an example:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+

mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4        |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC')   |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

8. Other functions

Some functions in MySQL cannot be specifically classified, but these functions can not be ignored in the process of MySQL development, operation and maintenance.

function	usage
FORMAT(value,n)	Returns the result data after formatting the number value. n means to be rounded to n decimal places
CONV(value,from,to)	Convert the value of value between different hexadecimals
INET_ATON(ipvalue)	Converts IP addresses separated by dots to a number
INET_NTOA(value)	Converts a numeric IP address to an IP address separated by dots
BENCHMARK(n,expr)	Repeat the expression expr n times. Used to test the time spent by MySQL processing expr expressions
CONVERT(value USING char_code)	Change the character encoding used by value to char_code

give an example:

# If the value of n is less than or equal to 0, only the integer part is retained
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2); 
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12             | 124                | 123                 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000           | 22B8             | NULL              |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
|                 3232235876 |
+----------------------------+
1 row in set (0.00 sec)

# Take "192.168.1.100" as an example. The calculation method is 192 times 256 to the third power, plus 168 times 256 to the second power, plus 1 times 256, plus 100.

mysql> SELECT INET_NTOA(3232235876);
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100         |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1000000, MD5('mysql')); 
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.20 sec)

mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4          | utf8                                   |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

Posted on Tue, 23 Nov 2021 22:42:42 -0500 by dhie

Programmer Help

Chapter 07 MySQL single line function

Chapter 07 MySQL single line function

1. Understanding of function

1.1 what is a function

1.2 differences of different DBMS functions

1.3 built in functions and classification of MySQL

2. Numerical function

2.1 basic functions

2.2 angle and radian exchange function

2.3 trigonometric function

2.4 exponent and logarithm

2.5 conversion between hexadecimals

3. String function

4. Date and time functions

4.1 acquisition date and time

4.2 date and time stamp conversion

4.3 get month, week, number of weeks, days and other functions

4.4 operation function of date

4.5 function of time and second conversion

4.6 function for calculating date and time

4.7 date formatting and parsing

5. Process control function

6. Encryption and decryption function

7. MySQL information function

8. Other functions

Hot Tags