Chapter 7 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 database management.
From the perspective of function definition, we can divide functions into builtin functions and userdefined functions. In SQL language, it also includes builtin functions and userdefined 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 builtin 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 a wealth of builtin 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 of developers to a certain extent.
The builtin 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 builtin functions into two categories: single line functions and aggregate functions (or grouping functions or multi line functions).
Two SQL functions
SingleRow Functions

Operation data object

Accept parameters and return a result

Transform only one row

Return one result per line

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 from 0 to 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:
#rounding SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2), ROUND(123.456,1),ROUND(153.456,2) FROM DUAL;
#Truncation operation SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,1) FROM DUAL;
#Single line functions can be nested SELECT TRUNCATE(ROUND(123.456,2),0) FROM DUAL;
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 value 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 value of two parameters 
COT(x)  Returns the cotangent value 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((y2y1)/(x2x1)), and using ATAN2(M,N) to calculate the arctangent is ATAN2(y2y1,x2x1). It can be seen from the usage that when x2x1 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 yth 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 with e as the base. 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 
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL;
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 
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL;
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 x should be added 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 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 the 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 appears for the first time 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, it returns s1. If m=2, it returns s2. If m=n, it returns sn 
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 string after s inversion 
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:
#String function SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('We'),LENGTH('hello'),LENGTH('We') FROM DUAL;
CHAR_LENGTH gets the number of characters, and LENGTH gets the number of bytes. English letters occupy one place in UTF8 and Chinese letters occupy three places.
SELECT CONCAT(emp.last_name,' work for ',mgr.last_name) "detials" FROM employees emp JOIN employees mgr WHERE emp.manager_id = mgr.employee_id;
#The index of string in SQL starts from 1! SELECT INSERT('helloworld',2,3,'aaaaa') FROM DUAL;
#LPAD: achieve right alignment effect #RPAD: achieve left alignment effect SELECT employee_id,last_name,LPAD(salary,10,'*') FROM employees;
SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM DUAL;
SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
In the numerical function, the frequency of basic function is higher; String function is also high; The date time function should also be mastered; Process control functions should also be understood.
Only get the most commonly used date.
4. Date and time function
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;
SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 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:
SELECT UNIX_TIMESTAMP(now()); SELECT UNIX_TIMESTAMP(CURDATE()); SELECT UNIX_TIMESTAMP(CURTIME()); SELECT UNIX_TIMESTAMP('20111111 11:11:11')； SELECT FROM_UNIXTIME(1576380910);
4.3 get month, week, number of weeks, days and other functions
function  usage 

YEAR(date) / MONTH(date) / DAY(date)  Return specific date value 
HOUR(time) / MINUTE(time) / SECOND(time)  Return specific time value 
MONTHNAME(date)  Return month: January 
DAYNAME(date)  Return to the 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 date of return is the day of the year 
DAYOFMONTH(date)  The return date is the day of the month 
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('20220126'),DAYNAME('20220126'),WEEKDAY('20220126'), 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)  Convert time to seconds and return 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:
SELECT TIME_TO_SEC(NOW()); SELECT SEC_TO_TIME(78774);
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('20211021 23:32:12',INTERVAL 1 SECOND) AS col2, ADDDATE('20211021 23:32:12',INTERVAL 1 SECOND) AS col3, DATE_ADD('20211021 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('20210121',INTERVAL 31 DAY) AS col1, SUBDATE('20210121',INTERVAL 31 DAY) AS col2, DATE_SUB('20210121 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 and date2 
TIMEDIFF(time1, time2)  Returns the interval between time1 and 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 a given hour, minute, and second 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(),'20211001'), TIMEDIFF(NOW(),'20211025 22:10:10'),FROM_DAYS(366),TO_DAYS('00001225'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101, 10) FROM DUAL;
SELECT ADDTIME(NOW(), 50); SELECT ADDTIME(NOW(), '1:1:1'); SELECT SUBTIME(NOW(), '1:1:1'); SELECT SUBTIME(NOW(), '1:1:1'); SELECT FROM_DAYS(366); SELECT MAKEDATE(2020,1); SELECT MAKEDATE(2020,32); SELECT MAKETIME(1,1,1); SELECT PERIOD_ADD(20200101010101,1); SELECT TO_DAYS(NOW());
4.7 date formatting and parsing
#Formatting and parsing of date #Format: date  > string #analysis : String  > date #At this point, we are talking about the explicit formatting and parsing of dates #Previously, we have been exposed to implicit formatting or parsing SELECT * FROM employees WHERE hire_date = '19930113';
The socalled implicit conversion is to get a date. In fact, what you get is a timestamp. SQL will automatically convert it into a fixed format string. Explicit conversion is to customize the string format to be converted.
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 digit year 
%M  Month name means month (January,...)  %m  Two digit numbers indicate the month (01, 02, 03...) 
%b  Abbreviated month name (Jan., Feb.,...)  %c  Numbers represent months (1, 2, 3,...) 
%D  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, 24hour system (01,02...)  %h and% I  Two digits represent hours, 12 hour system (01,02...) 
%k  Hours in digital form, 24hour 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  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  Number the week ordinal of the year, (1,2,3.) Sunday is the first day of the week 
%u  Number the week ordinal of the year, (1,2,3.) Monday is the first day of the week  
%T  24hour 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:
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'); 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('20140422 15:47:06','%Y%m%d %H:%i:%s') FROM DUAL; SELECT GET_FORMAT(DATE, 'USA'); SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')) FROM DUAL; SELECT STR_TO_DATE('20200101 00:00:00','%Y%m%d');
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 
Example:
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 '' WHEN 2 THEN 'I'm 2' ELSE 'who are you' END #I'm 1 SELECT employee_id,salary,CASE WHEN salary >= 15000 THEN 'High salary' WHEN salary >= 10000 THEN 'potential share' WHEN salary >= 8000 THEN 'green hand' ELSE 'Grassroots' END "describe" FROM employees; SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END; SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END; SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE 1 END; SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE 1 END; SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) FROM employees; #Exercise: query the information of employees with department numbers of 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. 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;
The process control phase does not involve loops, because the SQL statement itself is executed in loops.
The process control function can not use ELSE, and END is followed by the name of this column.
**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.
be careful:
 PASSWORD(str), ENCODE(str,pass_str), and code (crypt_str, pass_str) are available on MySQL 8 Deprecated in 0
 Both MD5 and SHA are irreversible
function  usage 

PASSWORD(str)  Returns the encrypted version of the string str, a 41 bit long string. The encryption result is irreversible, which is often used for user password encryption 
MD5(str)  Returns the md5 encrypted value of 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 
You can see that the ENCODE(value,password_seed) function and the ENCODE(value,password_seed) function are reciprocal functions of each other.
give an example:
SELECT PASSWORD('mysql'), PASSWORD(NULL); SELECT MD5('123') SELECT SHA('Tom123') SELECT ENCODE('mysql', 'mysql'); SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
Note: Although the same encryption method is irreversible, the same string generated by encryption is the same. Only in this way can password verification be carried out.
The database can be encrypted, but usually we have completed the encryption when the data is transmitted from the front end, so that even if the data is stolen in the process of network transmission, it can prevent the data from being stolen.
7. MySQL information function
MySQL has builtin 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 "hostname @ user name" 
CHARSET(value)  Returns the character set of the string value argument 
COLLATION(value)  Returns the comparison rule of the string value 
give an example:
SELECT DATABASE(); SELECT DATABASE(); SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(); SELECT CHARSET('ABC'); SELECT COLLATION('ABC');
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 keep to n decimal places after rounding 
CONV(value,from,to)  Convert the value of value between different base numbers 
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 SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, 2); SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2); #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. SELECT INET_ATON('192.168.1.100'); SELECT INET_NTOA(3232235876); SELECT BENCHMARK(1, MD5('mysql')); SELECT BENCHMARK(1000000, MD5('mysql')); SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
BENCHMARK(count,expr) is used to test the execution efficiency of expressions. See which expression takes less time to execute.
#BENCHMARK(count,expr) is used to test the execution efficiency of expressions SELECT BENCHMARK(10000,MD5('mysql')) FROM DUAL; #CONVERT(expr USING transcoding_name): it can realize the conversion of character set SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'utf8mb3')) FROM DUAL;
practice
#Query employee name, hire_date,department_id, the following conditions are met #Employed after 1997, department_id is 80, or 90 or 110, commission_pct is not empty SELECT last_name,hire_date,department_id FROM employees WHERE department_id IN (80,90,110) AND commission_pct IS NOT NULL #AND hire_date >= '19970101'; #There is an implicit conversion #AND DATE_FORMAT(hire_date,'%Y%m%d') >= '19970101';#Explicit conversion operation, format: date  > string #AND DATE_FORMAT(hire_date,'%Y') >= '1997';#Explicit conversion operations, formatting AND hire_date >= STR_TO_DATE('19970101','%Y%m%d');#Explicit conversion operation, parsing: String  > date
Exercise: grade different occupations
SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' ELSE 'UNDEFINED' END "Grade" FROM employees;