Total directory level
1.MySQl learning (from entry to proficiency 1.1)
1.1 Database overview
1.2 MYSQL environment construction
1.3 Basic select statement
2.MySQl learning (from entry to proficiency 1.2)
2.1 Operators
2.2 Sorting and paging
MySQl learning (from entry to proficiency 1.4)
4.1 Single-row functions
MySQl learning (from entry to proficiency 1.5)
5.1 Aggregate functions
MySQl learning (from entry to proficiency 1.6)
6.1 Subqueries
The article is temporarily updated here, and will be updated later
THIS IS END!
Chapter 07_One-line letter
1. Understanding of functions
1.1 What is a function
Functions are used throughout the use of computer languages. What is the role of functions? It can encapsulate the code we often use and call it directly when needed. This not only improves code efficiency, but also improves maintainability. In SQL, we can also use functions to perform functional operations on the retrieved data. Using these functions can greatly improve the user's management efficiency of the database.
From the perspective of function definition, we can divide functions into built-in functions and custom functions. In the SQL language, built-in functions and custom functions are also included. Built-in functions are common functions built into 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 between different DBMS functions
When we use the SQL language, we don't directly deal with this language, but use different database software, namely DBMS, through it. The differences between DBMS s are vast, far greater than the differences between different versions of the same language. In fact, only a few functions are supported by the DBMS at the same time. For example, most DBMS s use (||) or (+) as concatenation characters, while the string concatenation function in MySQL is concat(). Most DBMS s have their own specific functions, which means that the portability of code using SQL functions is very poor, so you need to pay special attention when using functions.
MySQL provides a wealth of built-in functions, which make data maintenance and management more convenient, better provide data analysis and statistical 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, functions for obtaining MySQL information, aggregation functions, etc. from the perspective of realized functions. Here, I divide these rich built-in functions into two categories: single-row functions and aggregate functions (or grouping functions).
Two SQL functions
one-line function
Manipulate data objects
transform only one row
returns one result per row
can be nested
parameter can be a column or a value
2. Numeric functions
2.1 Basic functions
ABS(x) | returns the absolute value of x |
---|---|
SIGN(X) | Returns the sign of X. Return 1 for positive numbers, -1 for negative numbers, and 0 for 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 smallest value in a list |
GREATEST(e1,e2,e3...) | Returns the largest value in a list |
MOD(x,y) | Returns the remainder after dividing X by Y |
RAND() | Returns a random value 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 value of X will generate the same random number |
ROUND(x) | Returns an integer that is closest to X after rounding the value of x |
ROUND(x,y) | Returns a value of x rounded to the nearest value to X, rounded to Y places after the decimal point |
TRUNCATE(x,y) | Returns the result of the number x truncated to y decimal places |
SQRT(x) | Returns the square root of x. Returns NULL when the value of X is negative |
Example:
#basic operation 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;
The corresponding value can be produced; (the above table)
#Random number Every time the value in the brackets is the same, the random number he gets is the same (pseudo-random number) SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;
#rounding truncation operation SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) FROM DUAL;
#EG:ROUND(12.343,2) The following 2 means to keep two decimal places -1 is an integer removed to make 12.324, if 2 does not meet 5, it will be 10, if it is satisfied, it will be 20 SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),ROUND(16.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) FROM DUAL;
function | usage |
---|---|
RADIANS(x) | Convert the angle to radians, where the parameter x is the angle value |
DEGREES(x) | Convert radians to angles, where the parameter x is the radian value |
I don't understand, watch the video https://www.gulixueyuan.com/course/501/task/21478/show
# 2*PI()=2π=360° #45°=1 #90° is converted into a radian of about 1.5 SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;
Interchange between angle and radian;
2.3 Trigonometric functions
function | usage |
---|---|
SIN(x) | Returns the sine of x, where the parameter x is a radian value |
ASIN(x) | Returns the arcsine of x, that is, gets the value whose sine is x. Returns NULL if the value of x is not between -1 and 1 |
COS(x) | Returns the cosine of x, where the parameter x is the radian value |
ACOS(x) | Returns the arccosine 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 the parameter x is a radian value |
ATAN(x) | Returns the arctangent of x, that is, returns the value whose tangent is x |
ATAN2(m,n) | Returns the arctangent of the two arguments |
COT(x) | Returns the cotangent of x, where X is in radians |
Example:
The ATAN2(M,N) function returns the arc tangent of the two arguments. Compared with the ATAN(X) function, ATAN2(M,N) requires two parameters, for example, there are two points point(x1,y1) and point(x2,y2), use the ATAN(X) function to calculate the arctangent value ATAN((y2-y1)/(x2-x1)), using ATAN2(M,N) to calculate the arc tangent value is ATAN2(y2-y1,x2-x1). It can be seen from the usage method that when x2-x1 is equal to 0, the ATAN(X) function will report an error, while the ATAN2(M,N) function can still be calculated.
An example of using the ATAN2(M,N) function is as follows:
ATAN2(M,N)An example of using the function is as follows: SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) ) FROM DUAL;
function | usage |
---|---|
POW(x,y),POWER(X,Y) | returns x raised to the y th power |
EXP(X) | Returns e raised to the X power, 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 base 2 logarithm of X, or NULL when X <= 0 |
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)
Conversion between 2.5 bases
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) | Return f1 base number to f2 base number |
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 functions
function | usage |
---|---|
ASCII(S) | Returns the ASCII code value of the first character in the string S |
CHAR_LENGTH(s) | Returns the number of characters in the string s. Same effect as CHARACTER_LENGTH(s) |
LENGTH(s) | Returns the number of bytes in the string s, which is related to the character set |
CONCAT(s1,s2,...,sn) | Concatenate s1,s2,...,sn into a string |
CONCAT_WS(x,s1,s2,...,sn) | Same as CONCAT(s1,s2,…) function, but add x between each string |
INSERT(str, idx, len,replacestr) | Replace the substring of the string str starting from the idx position with a length of len characters to the string replacestr |
REPLACE(str, a, b) | Replaces all occurrences of the string a in the string str with the string b |
UPPER(s) or UCASE(s) | Convert all letters of the string s to uppercase |
LOWER(s) or LCASE(s) | Convert 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) | Pad the leftmost side of str with the string pad until the length of str is len characters |
RPAD(str ,len, pad) | Pad the rightmost side of str with the string pad until the length of str is len characters |
LTRIM(s) | Remove spaces from the left side of the string s |
RTRIM(s) | Remove spaces from the right side of the string s |
TRIM(s) | Remove the spaces at the beginning and end of the string s |
TRIM(s1 FROM s) | Remove s1 at the beginning and end of the string s |
TRIM(LEADING s1 FROM s) | Remove s1 at the beginning of the 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 the ASCII code value of strings s1 and s2 |
SUBSTR(s,index,len) | Return the len characters from the index position of the string s, the function is the same as SUBSTRING(s,n,len), MID(s,n,len) |
LOCATE(substr,str) | Returns the position of the first occurrence of the string substr in the string str, which is the same for POSITION(substr IN str) and INSTR(str,substr). not found, return 0 |
ELT(m,s1,s2,...,sn) | Return the string at the specified position, if m=1, return s1, if m=2, return s2, if m=n, return sn |
FIELD(s,s1,s2,...,sn) | Returns the position of the first occurrence of the string s in the list of strings |
FIND_IN_SET(s1,s2) | Returns the occurrence of string s1 in string s2. where the string s2 is a comma-separated string |
REVERSE(s) | Returns the reversed string of s |
NULLIF(value1,value2) | Compares two strings and returns NULL if value1 is equal to value2, otherwise returns value1 |
Notice: MySQL In , the position of the string starts from 1.
Example:
If you don’t understand the meaning, see the explanation in the above table, a Chinese character has 3 bytes
SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('us'), LENGTH('hello'),LENGTH('us') FROM DUAL;
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 Get date and time
function | usage |
---|---|
CURDATE() ,CURRENT_DATE() | Returns the current date, containing only the 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 (Coordinated Universal Time) date |
UTC_TIME() | Returns UTC (Coordinated Universal Time) time |
example
FROM DUAL;
4.2 Conversion of date and time stamp
function | usage |
---|---|
UNIX_TIMESTAMP() | Return the current time as a UNIX timestamp. SELECT UNIX_TIMESTAMP() ->1634348884 |
UNIX_TIMESTAMP(date) | Return the time date as a UNIX timestamp. |
FROM_UNIXTIME(timestamp) | Convert the time of UNIX timestamp to the time in common format |
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) | FROM_UNIXTIME(1576380910) | +---------------------------+ | 2019-12-15 11:35:10 | +---------------------------+ 1 row in set (0.00 sec)
4.3 Obtain functions such as month, week, week number, day number, etc.
function | usage |
---|---|
YEAR(date) / MONTH(date) / DAY(date) | Return a specific date value |
HOUR(time) / MINUTE(time) / | |
SECOND(time) | return specific time value |
MONTHNAME(date) | Returns the month: January, ... |
DAYNAME(date) | Returns the day of the week: MONDAY, TUESDAY…SUNDAY |
WEEKDAY(date) | Returns the day of the week. Note that week 1 is 0 and week 2 is 1. . . Sunday is 6 |
QUARTER(date) | Returns the quarter corresponding to the date, ranging from 1 to 4 |
WEEK(date) , WEEKOFYEAR(date) | Returns the week of the year |
DAYOFYEAR(date) | The return date is the day of the year |
DAYOFMONTH(date) | Returns the day of the month in which the date is located |
DAYOFWEEK(date) | Returns the day of the week, note: Sunday is 1, Monday is 2,. . . Saturday is 7 |
Example:
mysql> SELECT FROM_UNIXTIME(1576380910); +---------------------------+ | FROM_UNIXTIME(1576380910) | +---------------------------+ | 2019-12-15 11:35:10 | +---------------------------+ 1 row in set (0.00 sec) 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;
function | usage |
---|---|
EXTRACT(type FROM date) | Returns a specific part of the specified date, and the type specifies the returned value |
The value and meaning of type in the 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 Functions for converting time and seconds
function | usage |
---|---|
TIME_TO_SEC(time) | Convert time to seconds and return the resulting value. The conversion formula is: hours 3600+ minutes 60+ seconds |
SEC_TO_TIME(seconds) | Convert the seconds description to a time containing hours, minutes and seconds |
| 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 Functions for calculating dates and times
Group 1:
function | usage |
---|---|
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type) | Returns a datetime with an INTERVAL period difference from a given datetime |
DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type) | Returns the date that differs from date by the INTERVAL interval |
The value of type in the above function:
Example:
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 quotes 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 time1 plus time2. When time2 is a number, it represents seconds, which can be negative |
SUBTIME(time1,time2) | Returns the time after subtracting time2 from time1. When time2 is a number, it represents seconds, which can be negative |
DATEDIFF(date1,date2) | Returns the number of days between date1 - date2 |
TIMEDIFF(time1, time2) | Returns the time interval of time1 - time2 |
FROM_DAYS(N) | Returns the date after N days from January 1, 0000 |
TO_DAYS(date) | Returns the number of days from date date to January 1, 0000 |
LAST_DAY(date) | Returns the date of the last day of the month of date |
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 adding time to n |
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) +-------------------------+ | 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) | 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)
Example: Query the number of new users within 7 days?
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
4.7 Formatting and parsing of dates
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) | Parse str according to the string fmt and parse it into a date |
Commonly used format symbols for fmt parameters in the above non-GET_FORMAT functions:
formula symbol | illustrate | formula symbol | illustrate |
---|---|---|---|
%Y | 4 digits for the year | %y | Indicates two digits indicate the year |
%M | The month name indicates the month (January,…) | %m | Two digits for the month (01,02,03...) |
%b | Abbreviated month name (Jan., Feb., ...) | %c | A number representing the month (1,2,3,…) |
%D | The English suffix indicates the number of days in the month (1st,2nd,3rd, ...) | %d | Two digits indicating the day of the month (01,02…) |
%e | The number of days in the month in numeric form (1,2,3,4,5…) | ||
%H | Two digits for decimals, 24-hour format (01,02…) | %h and %I | Two-digit hour, 12-hour clock (01,02…) |
%k | Hour as a number, 24-hour format (1,2,3) | %l | Hours in digital form, 12-hour clock (1,2,3,4…) |
%i | Two digits for minutes (00,01,02) | %S and %s | Two digits for seconds (00,01,02…) |
%W | Day of the week name (Sunday...) | %a | Day of the week abbreviation (Sun., Mon.,Tues., ...) |
%w | Day of the week as a number (0=Sunday,1=Monday...) | ||
%j | Day of the year as 3 digits (001,002…) | %U | The number of weeks in the year is represented by numbers, (1,2,3..) where Sunday is the first day of the week |
%u | Indicates the week of the year in numbers, (1,2,3..) where Monday is the first day of the week | ||
%T | 24 hours | %r | 12 hours |
%p | AM or PM | %% | express% |
The values of date_type and format_type parameters in the GET_FORMAT function are as follows:
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;
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 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)
Process processing functions can execute different processing processes according to different conditions, and can implement different condition selections in SQL statements.
The process processing functions in MySQL mainly include IF(), IFNULL() and CASE() functions.
function | usage |
---|---|
IF(value,value1,value2) | If the value of value is TRUE, return value1, otherwise return value2 |
IFNULL(value1, value2) | If value1 is not NULL, return value1, otherwise return value2 |
CASE WHEN condition 1 THEN result 1 WHEN condition 2 THEN result 2... [ELSE resultn] END | Equivalent to Java's 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's switch...case... |
SELECT IF(1 > 0,'correct','mistake') ->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 am 2' ELSE 'Who are you'
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN 'high salary' WHEN salary>=10000 THEN 'Potential stock' WHEN salary>=8000 THEN 'dick' ELSE 'grassroots' END "describe" FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN 'Unpaid' WHEN 2 THEN 'Already paid' WHEN 3 THEN 'Shipped' WHEN 4 THEN 'confirm the receipt of goods' 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 | +---------------------------------------------------------------------+ 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;
6. Encryption and decryption functions
Encryption and decryption functions are mainly used to encrypt and decrypt data in the database to prevent data from being stolen by others. These functions are very useful in securing the database.
function | usage |
---|---|
PASSWORD(str) | Returns an encrypted version of the string str, a 41-bit long string. The encryption result is irreversible, 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, it will return NULL |
SHA(str) | Calculate and return the encrypted password string from the original plaintext password str. When the parameter is NULL, return NULL. The SHA encryption algorithm is more secure than MD5. |
ENCODE(value,password_seed) | Returns the encrypted value using password_seed as the encrypted password |
DECODE(value,password_seed) | Returns the value decrypted using password_seed as the encrypted password |
It can be seen that the ENCODE(value,password_seed) function and the DECODE(value,password_seed) function are inverse functions of each other.
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 some built-in functions that can query MySQL information. These functions are mainly used to help database development or operation and maintenance personnel to better maintain the database.
VERSION() | Returns the current MySQL version number |
---|---|
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 username currently connected to MySQL, and the returned result format is "hostname@username" |
CHARSET(value) | Returns the character set of the string value argument |
COLLATION(value) | Returns the comparison rules for the string value |
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)
Some functions in MySQL cannot be classified specifically, but these functions cannot be ignored in the development and operation and maintenance of MySQL
of.
function | usage |
---|---|
FORMAT(value,n) | Returns the result data after formatting the numeric value. n means rounded to n digits after the decimal point |
CONV(value,from,to) | Convert the value of value between different bases |
INET_ATON(ipvalue) | Convert dot-separated IP addresses to a number |
INET_NTOA(value) | Convert numeric IP addresses to dot-separated IP addresses |
BENCHMARK(n,expr) | Repeat the expression expr n times. Used to test the time it takes for MySQL to process the expr expression |
CONVERT(value USINGchar_code) | Change the character encoding used by value to char_code |
Example:
# If the value of n is less than or equal to 0, only the integer part is kept 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) # Taking "192.168.1.100" as an example, the calculation method is 192 times 256 to the 3rd power, plus 168 times 256 to the 2nd power, plus 1 times 256, plus 100.
| 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)
To be continued;