MySQl learning (from entry to proficiency 1.4)

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

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;

Tags: Database MySQL

Posted by Edwin Okli on Thu, 09 Feb 2023 15:31:16 +0530

Programs Team

MySQl learning (from entry to proficiency 1.4)

Chapter 07_One-line letter

1. Understanding of functions

1.2 Differences between different DBMS functions

2. Numeric functions

2.1 Basic functions

2.3 Trigonometric functions

Conversion between 2.5 bases

3. String functions

4. Date and time functions

4.1 Get date and time

4.2 Conversion of date and time stamp

4.3 Obtain functions such as month, week, week number, day number, etc.

4.5 Functions for converting time and seconds

4.6 Functions for calculating dates and times

4.7 Formatting and parsing of dates

6. Encryption and decryption functions

7. MySQL information function

Hot Tags