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 built-in functions and user-defined functions. In SQL language, it also includes built-in functions and user-defined 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 a wealth of 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 of 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 or multi line functions).

Two SQL functions

Single-Row 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((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-th 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 UTF-8 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('2011-11-11 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('2022-01-26'),DAYNAME('2022-01-26'),WEEKDAY('2022-01-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)	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('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 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(),'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;

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 = '1993-01-13';

The so-called 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, 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	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	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:

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('2014-04-22 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('2020-01-01 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 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 "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 >= '1997-01-01'; #There is an implicit conversion
#AND DATE_FORMAT(hire_date,'%Y-%m-%d') >= '1997-01-01';#Explicit conversion operation, format: date - > string
#AND DATE_FORMAT(hire_date,'%Y') >= '1997';#Explicit conversion operations, formatting
AND hire_date >= STR_TO_DATE('1997-01-01','%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;

Keywords: Database MySQL SQL

Added by Grego on Thu, 24 Feb 2022 13:28:08 +0200

Programming VIP

Chapter 7 single line function

Chapter 7 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

Two SQL functions

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 function

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

Group 1:

Group 2:

4.7 date formatting and parsing

5. Process control function

6. Encryption and decryption function

7. MySQL information function

8. Other functions

practice

Popular Keywords

Programming VIP

Chapter 7 single line function

Chapter 7 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

Two SQL functions

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 function

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

Group 1:

Group 2:

4.7 date formatting and parsing

5. Process control function

**6. ** Encryption and decryption function

7. MySQL information function

8. Other functions

practice

Popular Keywords

6. Encryption and decryption function