introduction

Regular expressions are special strings used to match text (character set). If you want to extract a phone number from a text file, you can use a regular expression. If you need to find all files with numbers in the middle of the name, you can use a regular expression. If you want to find all duplicate words in a text block, you can use a regular expression. All kinds of programming languages, text editing Regular expressions are supported by processors, operating systems, etc.

1, Regular expression

MySQL regular expressions use the keyword REGEXP to specify the matching pattern of regular expressions. The matching patterns supported by the REGEXP operator are as follows

matching	describe	Example
^	Matches the start character of the text	'^ bd' matches a string beginning with bd
$	Matches the end character of the text	'qn $' matches a string ending in qn
.	Match any single character	's.t 'matches any string with one character between S and t
*	Matches zero or more characters before it	'fo*t' matches T with any o before it
%	Match all	\
+	Matches the preceding character 1 or more times	'hom +' matches a string starting with ho followed by at least one m
character string	Match contains the specified string	'clo' matches the string containing clo
p1Ip2	Match p1 or p2	\
[...]	Matches any character in the character set	'[abc]' matches a or b or c
[^...]	Matches any character that is not in parentheses	'[^ ab]' matches a string that does not contain a or b
{n}	Match the previous string n times	'g{2}' matches a string containing 2 g's
{n,m}	Match the previous string at least n times and at most m times	'f{1,3}' matches f at least once and at most three times

• case

#Query student information starting with li
mysql> select id,name from info where name regexp '^li';
+----+-------+
| id | name  |
+----+-------+
|  2 | lier  |
|  3 | lisan |
|  7 | lilei |
+----+-------+
3 rows in set (0.01 sec)
-----------------------------------------------------------------------------------------------
#Query student information ending with n
mysql> select id,name from info where name regexp 'n$';
+----+--------+
| id | name   |
+----+--------+
|  3 | lisan  |
|  5 | goudan |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#Query the student information contained in an's name
mysql> select id,name from info where name regexp 'n$';
+----+--------+
| id | name   |
+----+--------+
|  3 | lisan  |
|  5 | goudan |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#The query name starts with wang and ends with a. I don't know what character is in the middle
mysql> select id,name from info where name regexp 'wang.a';
+----+--------+
| id | name   |
+----+--------+
|  4 | wangya |
+----+--------+
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------
#Query the student information with sh or li in the name
mysql> select id,name from info where name regexp 'sh|li';
+----+--------+
| id | name   |
+----+--------+
|  2 | lier   |
|  3 | lisan  |
|  7 | lilei  |
|  9 | shabi  |
| 10 | shidan |
+----+--------+
5 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#Query the student information with SH and a in the name. The required part is' sh 'and' a 'is optional
mysql> select id,name from info where name regexp 'sha*';
+----+--------+
| id | name   |
+----+--------+
|  9 | shabi  |
| 10 | shidan |
+----+--------+
2 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------
#The query name contains information that go and u appear at least once
mysql> select id,name from info where name regexp 'gou+';
+----+--------+
| id | name   |
+----+--------+
|  5 | goudan |
| 11 | goush  |
+----+--------+
2 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------
#Query student information whose name starts with s-x
mysql> select id,name from info where name regexp '^[s-x]';
+----+--------+
| id | name   |
+----+--------+
|  4 | wangya |
|  9 | shabi  |
| 10 | shidan |
+----+--------+
3 rows in set (0.00 sec)
-----------------------------------------------------------------------------------------------
#Query information whose name is not lisi
mysql> select id,name from info where name regexp '[^lisi]';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | guyi      |
|  2 | lier      |
|  3 | lisan     |
|  4 | wangya    |
|  5 | goudan    |
|  6 | hanmeimei |
|  7 | lilei     |
|  8 | caicai    |
|  9 | shabi     |
| 10 | shidan    |
| 11 | goush     |
| 12 | gobud     |
+----+-----------+
12 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------
#The query name does not start with sh
mysql> select id,name from info where name regexp '^[^sh]';
+----+--------+
| id | name   |
+----+--------+
|  1 | guyi   |
|  2 | lier   |
|  3 | lisan  |
|  4 | wangya |
|  5 | goudan |
|  7 | lilei  |
|  8 | caicai |
| 11 | goush  |
| 12 | gobud  |
+----+--------+
9 rows in set (0.00 sec)

2, Operator

MySQL operators are used to operate on field values in records. There are four types of MySQL operators: arithmetic operator, comparison operator, logical operator and bit operator

1. Arithmetic operator

The SELECT command is used to realize the most basic addition, subtraction, multiplication and division. MySQL supports the arithmetic operators used, as shown in the table:

operator	describe
+	addition
-	subtraction
*	multiplication
/	division
%	Surplus

• In division and remainder operations, the divisor cannot be 0. If the divisor is 0, the returned result will be NULL. It should be noted that if there are multiple operators, they operate according to the priority of multiplication and division before addition and subtraction, and the operators with the same priority have no order.

mysql> select 1+1,3-2,2*3,6/3,7%2;
+-----+-----+-----+--------+------+
| 1+1 | 3-2 | 2*3 | 6/3    | 7%2  |
+-----+-----+-----+--------+------+
|   2 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

2. Comparison operator

• Comparison operator is a kind of operator often used when querying data records
• By using the comparison operator, you can determine which records in the table meet the conditions. If the comparison result (return judgment in the form of Boolean value) is true, it returns 1, if it is false, it returns 0, and if the comparison result is uncertain, it returns NULL
• Strings are case insensitive by default when compared. If you want to be case sensitive, you can use the binary keyword

operator	describe
=	be equal to
>	greater than
<	less than
>=	Greater than or equal to
<=	Less than or equal to
! = or < >	Not equal to
is null	Determines whether a value is NULL
IS NOT NULL	Determines whether a value is not NULL
BETWEEN AND	Between the two
IN	In collection
LIKE	Wildcard matching
GREATEST	Returns the maximum value for two or more parameters
LEAST	Returns the minimum value for two or more parameters
REGEXP	regular expression

• Equal sign (=):

• It is used to judge whether numbers, strings and expressions are equal. If they are equal, it returns 1(true). If they are not equal, it returns 0 (flash).
  If one of the two values compared is NULL, the result of the comparison is NULL.
  The comparison of characters is based on ASCII codes. If the ASCII codes are equal, it means that the two characters are the same; if the ASCII codes are not equal, it means that the two characters are different
• For example, string (letter) comparison:
  ('a '>'b') in fact, the comparison is the underlying ASCII code. It should be noted that ASCII codes include: A, a, 0 (97, 65, 48)
  Then: if multiple strings are compared, such as' abc '=' acb ', how to compare (number of characters, character order)
  If multiple characters are compared, such as' abc '<' baa ', how to compare
• Contrary to the expression of linux return value, the return value of normal operation in linux is 0, and the return value of abnormal operation is non-0

mysql> select 'abc'='acb';
+-------------+
| 'abc'='acb' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select 'abc'<'baa';
+-------------+
| 'abc'<'baa' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

• case

mysql> select 2=4,2=2,2='2','e'='e','r'=null;
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)

#If both are integers, the comparison is based on integer values.
#If an integer is a string, the string will be automatically converted into a number and compared. (in programs, the two are generally not compared)
#If both are strings, they are compared by string.
#If at least one of the two values is NULL, the result of the comparison is NULL.

• Not equal to (< >,! =)

There are two ways to write the unequal sign, namely < > or! =, which is used for the comparison of unequal numbers, strings and expressions.
Returns 1 if not equal and 0 if equal, which is the opposite of the return value of equal.
Note that the not equal operator cannot be used to determine NULL
In other words, if it is not equal, it will return 1; if it is equal, it will return 0

mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null;
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)

mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null;
+-----+-----+---------+-------+-----------+
| 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null |
+-----+-----+---------+-------+-----------+
|   1 |   1 |       1 |     1 |      NULL |
+-----+-----+---------+-------+-----------+
1 row in set (0.00 sec)

#The greater than (>) operator is used to determine whether the operand on the left is greater than the operand on the right. If it is greater than 1, otherwise it returns 0, which cannot be used to determine NULL.
#The less than (<) operator is used to determine whether the operand on the left is less than the operand on the right. If it is less than 1, otherwise it returns 0, which cannot be used to determine NULL.
#Greater than or equal (> =) determines whether the operand on the left is greater than or equal to the operand on the right. If greater than or equal, it returns 1, otherwise it returns 0 and cannot be used to determine NULL.
#Less than or equal (< =) determines whether the operand on the left is less than or equal to the operand on the right. If less than or equal, it returns 1, otherwise it returns 0 and cannot be used to determine NULL

• Determine whether a value is not null

mysql> select 2 is null,'f' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'f' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)

• between and comparison operation

It is usually used to judge whether a value falls between two values; that is, the coverage range is > = and < = relationship
Judge whether a number is between two other numbers, or whether an English letter is between two other letters
Case:

#Judge whether 6 is between 5 and 7, and whether c is between a and b. Between and covers > = and < = relationships

mysql> select 6 between 5 and 7,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 6 between 5 and 7 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+

• least and greatest (minimum and maximum)

#Least:
LEAST: When there are two or more parameters, the minimum value is returned. If one of the values is NULL，The returned result is NULL. 
#Greatest:
GREATEST: When there are two or more parameters, the maximum value is returned. If one of the values is NULL， The returned result is NULL. 

case
mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.00 sec)

• IN,NOT IN

#Judge whether a value is in the corresponding list. If yes, it returns 1; otherwise, it returns 0.
#Judge whether a value is not in the corresponding list. If not, return 1, otherwise return 0
mysql>  select 3 in(1,2,3),1 not in(1,2,3);
+-------------+-----------------+
| 3 in(1,2,3) | 1 not in(1,2,3) |
+-------------+-----------------+
|           1 |               0 |
+-------------+-----------------+
1 row in set (0.00 sec)

• like,NOT LIKE

like
#It is used to match the string. If the match is successful, it returns 1, otherwise it returns 0
#LIKE supports two wildcards:
'%' : Used to match any number of characters(*Matches the previous character)
'_': Only one character can be matched.

NOT LIKE
#Just contrary to LIKE, if no match is successful, it returns 1, otherwise it returns 0

case
mysql> select 'abc' like 'ab%','abc' like 'a_c','abc' not like 'abc';
+------------------+------------------+----------------------+
| 'abc' like 'ab%' | 'abc' like 'a_c' | 'abc' not like 'abc' |
+------------------+------------------+----------------------+
|                1 |                1 |                    0 |
+------------------+------------------+----------------------+
1 row in set (0.00 sec)

3. Logical operator (Boolean)

Logical operators, also known as Boolean operators, are usually used to judge whether an expression is TRUE or FALSE. If it is TRUE, it returns 1, otherwise it returns 0. TRUE and FALSE can also be represented by TRUE and FALSE
There are four logical operators supported in MySQL, as shown in the table:

operator	describe
not or!	Logical non
and or&&	Logic and
or	Logical or
xor	Logical XOR

3.1 logical non

#The simplest logical operator is logical non, which uses NOT or! express
 Logical non reverses the logical test that follows it, turns truth into false, and turns false into true
 If NOT When the following operand is 0, the resulting value is 1; If the operand is non-0, the resulting value is 0; If the operand is NULL When, the resulting value is NULL

case
mysql> select not 0,!3,!null;
+-------+----+-------+
| not 0 | !3 | !null |
+-------+----+-------+
|     1 |  0 |  NULL |
+-------+----+-------+
1 row in set (0.00 sec)

3.2 logic and

#When all operands are non-zero and non null, the return value is 1, otherwise it is 0 (null and 0 are special)
Logic and use AND perhaps&&express

case
mysql> select 3 and 4,null and 5,0 && null,6 && 0;
+---------+------------+-----------+--------+
| 3 and 4 | null and 5 | 0 && null | 6 && 0 |
+---------+------------+-----------+--------+
|       1 |       NULL |         0 |      0 |
+---------+------------+-----------+--------+
1 row in set (0.00 sec)

#It can be seen from the output results that the functions of and and & & are the same
3 and 4 : No 0 or null，The return value is 1
null and 5: have null，Return value null
0 && null: The return value is 0
6 && 0: There are 0 in the, and the return value is 0

3.3 logical or

#Logical OR usually uses OR
 Logical or represents the contained operands, either of which is non-zero and not zero NULL Value, return 1, otherwise return 0.
When an operand is null If the other operand is a non-zero value, the return value is 1, otherwise null
 If both operands are null，The return value is null

case
mysql> select 3 or 4,null or 5,0 or null,6 or 0,0 or 0;
+--------+-----------+-----------+--------+--------+
| 3 or 4 | null or 5 | 0 or null | 6 or 0 | 0 or 0 |
+--------+-----------+-----------+--------+--------+
|      1 |         1 |      NULL |      1 |      0 |
+--------+-----------+-----------+--------+--------+
1 row in set (0.00 sec)

#Multiple comparisons
mysql> select 0 or null or 2;
+----------------+
| 0 or null or 2 |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

3.4 logic difference (xor)

#Operands with two non NULL values. If both are 0 or both are non-0, 0 is returned
 If one is 0 and the other is non-0, the returned result is 1
 When any value is NULL When, the return value is NULL

case
mysql> select 0 xor 0,0 xor 5,3 xor 4,3 xor null;
+---------+---------+---------+------------+
| 0 xor 0 | 0 xor 5 | 3 xor 4 | 3 xor null |
+---------+---------+---------+------------+
|       0 |       1 |       0 |       NULL |
+---------+---------+---------+------------+
1 row in set (0.00 sec)

4. Bitwise operator

• Bit operators are actually operators that evaluate binary numbers
• MySQL internal bit operation will first change the operand into binary format (1010 1111), then perform bit operation, and finally change the calculation result from binary to decimal format for users to view
• MySQL supports six bit operators, as shown in the table below:

Bitwise Operators	describe
&	Bitwise AND
I	Bitwise OR
~	Bitwise inversion
^	Bitwise XOR
<<	Shift left by bit
>>	Shift right by bit

Case:
mysql> select 10 & 15,10 | 15,10^15,5 &~1;
+---------+---------+-------+-------+
| 10 & 15 | 10 | 15 | 10^15 | 5 &~1 |
+---------+---------+-------+-------+
|      10 |      15 |     5 |     4 |
+---------+---------+-------+-------+
1 row in set (0.00 sec)

Specific process:
10 The conversion to binary number is 1010, 15 The binary number is 1111

#The bitwise and operation (&) are corresponding, and the binary bits are 1. Their operation result is 1, otherwise it is 0, so the result of 10 & 15 is 10
#Bitwise OR operation (|) is the corresponding binary bit. If one or two bits are 1, the operation result is 1, otherwise it is 0, so the result of 10 | 15 is 15
#The bitwise XOR operation (^) means that the corresponding binary bits are different. The operation result is 1, otherwise it is 0, so the result of 10 ^ 15 is 5
#Bitwise inversion (~) is the bit by bit inversion of the corresponding binary number, that is, 1 becomes 0 after inversion, and 0 becomes 1 after inversion.
The binary of number 1 is 0001, which becomes 1110 after being reversed, The binary of number 5 is 0101. Sum 1110 and 0101,
The result is binary 0100, which is converted to decimal 4

5. Priority

• No matter which operator is used, there is a priority problem.
• The priority of operators determines the order of different operators in the calculation process
• Operators with higher levels will be calculated first. If the operators have the same level, MySQL will calculate from left to right in order
• The priority is shown in the following table

priority	operator
1	!
2	~
3	^
4	*,/,%
5	+,-
6	>>,<<
7	&
8	I
9	=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10	BETWEEN,CASE,WHEN,THEN,ELSE
11	NOT
12	&&,AND
13	II,OR,XOR
14	:=

3, Connection query

MySQL connection query usually combines the record rows from two or more tables to splice data based on the common fields between these tables.
First, determine a main table as the result set, and then selectively connect the rows of other tables to the selected main table result set.
Connection queries that are frequently used include inner connection, left connection and right connection

The left table is the main table, which is called left join query
The right table is the main table, which is called right join query

• Case preparation

mysql> create database info;
mysql> use info;
mysql> create table test1 (
    -> a_id int(11) default null,
    -> a_name varchar(32) default null,
    -> a_level int(11) default null);

mysql> create table test2 (
    -> b_id int(11) default null,
    -> b_name varchar(32) default null,
    -> b_level int(11) default null);

insert into test1 values (1,'aaaa',10);
insert into test1 values (2,'bbbb',20);
insert into test1 values (3,'cccc',30);
insert into test1 values (4,'dddd',40);
insert into test2 values (2,'bbbb',20);
insert into test2 values (3,'cccc',30);
insert into test2 values (5,'eeee',50);
insert into test2 values (6,'ffff',60);

mysql> select * from test1;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    1 | aaaa   |      10 |
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    4 | dddd   |      40 |
+------+--------+---------+
4 rows in set (0.00 sec)

mysql> select * from test2;
+------+--------+---------+
| b_id | b_name | b_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
|    5 | eeee   |      50 |
|    6 | ffff   |      60 |
+------+--------+---------+
4 rows in set (0.00 sec)

1. Internal connection

An internal connection in MySQL is a combination of data records in two or more tables that meet certain conditions at the same time
In general, the keyword INNER JOIN is used in the FROM clause to connect multiple tables, and the ON clause is used to set the connection conditions. INNER JOIN is the default table connection of the system. Therefore, the INNER keyword can be omitted after the FROM clause, and only the keyword JOIN can be used
When there are multiple tables at the same time, you can also continuously use INNER JOIN to realize the internal connection of multiple tables. However, for better performance, it is recommended not to exceed three tables
In short:
Inline query is to output the record rows of the same field specified by two tables through inner join

Syntax format:
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

mysql> select a.a_id,a.a_name,a.a_level from test1 a inner join test2 b on a.a_id=b.b_id;
+------+--------+---------+
| a_id | a_name | a_level |
+------+--------+---------+
|    2 | bbbb   |      20 |
|    3 | cccc   |      30 |
+------+--------+---------+
2 rows in set (0.00 sec)

2. Left connection

• LEFT JOIN can also be called LEFT OUTER JOIN, which is represented by LEFT JOIN or LEFT OUTER JOIN keywords in the FROM clause
• The left connection takes the left table as the base table, receives all rows of the left table, and uses these rows to match the records in the right reference table, that is, match all rows in the left table and qualified rows in the right table.

mysql> select * from test1 a left join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
|    1 | aaaa   |      10 | NULL | NULL   |    NULL |
|    4 | dddd   |      40 | NULL | NULL   |    NULL |
+------+--------+---------+------+--------+---------+
#The test1 table of the left table of the join will be displayed as the main table, and the test2 table on the right will only display the records that meet the search criteria. The insufficient records in the right table are NULL

3. Right connection

• Right joins are also called right outer joins, which are represented by the RIGHT JOIN or RIGHT OUTER JOIN keywords in the FROM clause.
• The right join is opposite to the left join. It is based on the right table. It is used to receive all rows in the right table and match these records with the rows in the left table

mysql> select * from test1 a right join test2 b on a.a_name=b.b_name;
+------+--------+---------+------+--------+---------+
| a_id | a_name | a_level | b_id | b_name | b_level |
+------+--------+---------+------+--------+---------+
|    2 | bbbb   |      20 |    2 | bbbb   |      20 |
|    3 | cccc   |      30 |    3 | cccc   |      30 |
| NULL | NULL   |    NULL |    5 | eeee   |      50 |
| NULL | NULL   |    NULL |    6 | ffff   |      60 |
+------+--------+---------+------+--------+---------+
#The test2 table on the right side of the join will be displayed as the main table, and the test1 table on the left will only display the records that meet the search conditions. The places where the sitting table records are insufficient are NULL

summary

• Join query is to query multiple tables at the same time, and get the final result through the relationship between multiple tables.
• Internal connection: take out each record from the left table and match it with all records in the right table; The matching condition must be the same in the left table and the right table before the result is retained. Otherwise, it is not retained.
• External connection is to take a table as the main table, take out all records in the main table, and then connect each record with another table. All the results in the main table will be retained, and the matched results in the table will be retained. The unmatched results will be set to null.
• There are two types of external connections, left and right
• Left external connection: all records in the left table will be displayed, while the right table will only display records that meet the search criteria. The insufficient records in the right table are NULL.
• Right external connection: Contrary to the left connection, the right connection will only display the records that meet the search criteria in the left table, while all the records in the right table will be displayed, and the insufficient records in the left table are NULL.