catalogue
1, Regular expression
1. Introduction
MySQL regular expressions usually match the required special strings in the records according to the specified matching pattern when retrieving database records. MySQL regular expressions use the keyword regexp to specify the matching pattern of regular expressions
2. Common matching types
| matching | describe |
| ^ | Matches the start character of the text |
| $ | Matches the end character of the text |
| . | Match any single character |
| * | Matches zero or more characters before it |
| + | Matches the preceding character 1 or more times |
| character string | Match contains the specified string |
| | | or |
| [...] | Matches any character in the character set |
| [^...] | Matches any character that is not in parentheses |
| {n} | Match the previous string n times |
| {n,m} | Match the previous string at least n times and at most m times |
3. Query mode
What does it start with select * from ly where name regexp '^sh';
What does it end with select * from ly where name regexp 'n$';
lu Having any character in the middle select * from ly where name regexp 'l.u';
Match there ha of select * from ly where name regexp 'han*';
Match there han of select * from ly where name regexp 'han+';
contain an select * from ly where name regexp 'an';
have han perhaps li of select * from ly where name regexp 'han|li';
Match contains a perhaps l Equialphabetic select * from ly where name regexp '[al]';
Not with a perhaps g Ending with select * from ly where name regexp '[^ag]$'; #Note that '[^ sss]' can only be exactly reversed
2, Operator
1. Arithmetic operator
operator describe + addition - subtraction * multiplication / division % Surplus select 1+1,2-1,3*2,4/2,5/2;
2. Comparison operator
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 Judge whether a value is NULL is not null Judge whether a value is not NULL between and Between the two in In collection like Wildcard matching grertest Returns the maximum value for two or more parameters least Returns the minimum value for two or more parameters regexp regular expression Here we use ascii The codes are: a,A,0(97,65,48)
If both are integers, the comparison is based on integer values
If an integer a string, the string will be automatically converted to a number for comparison. (in the procedure, the two are generally not compared)
If both are strings, the comparison is performed by string
If at least one of the two values is NULL, the result of the comparison is NULL
3. Logical operator (Boolean)
operator describe not or ! Logical non have null return null，If 0 returns 1, if not 0 returns 0 and or && Logic and have null return null，If 0 returns 0, if not 0 returns 1 or Logical or There is a non-zero null Return 1, yes null return null，All 0 returns 0 xor Logical XOR have null return null，0 returns 1, all 0, non-0 returns 0
4. Bitwise operator
Bitwise Operators describe & Bitwise AND 11 is 1, otherwise 0 | Bitwise OR 1 with 1, otherwise 0 ~ Bitwise inversion Inverse calculation ^ Bitwise XOR Different 1, otherwise 0 << Shift left by bit >> Shift right by bit Bit operators are actually operators that evaluate binary numbers priority operator 1 ! 2 ~ 3 ^ 4 *,/,% 5 +,- 6 >>,<< 7 & 8 | 9 =,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN 10 BETWEEN,CASE,WHEN,THEN,ELSE 11 NOT 12 &&,AND 13 ||,OR,XOR 14 :=
3, Connection query
1. Inner connection
Internal join in MySQL is a combination of data records in two or more tables that meet certain conditions at the same time. When there are multiple tables at the same time, internal join can also be used continuously to realize internal join of multiple tables. However, for better performance, it is recommended not to exceed three tables
SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; #In short, it is the intersection of the common conditions of the two tables and displays the results
select * from shi inner join shi02 on shi.id=shi02.id;
2. Left connection
The left connection can also be called the left outer connection. 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
select * from shi02 left join shi on shi.id=shi02.id; #The left table displays the information of the left table, the right table displays the conditional intersection, and the others are null filled
3. Right connection
The right connection is also called the right outer connection. The right connection is just the opposite of the left connection. 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
select * from shi02 left join shi on shi.id=shi02.id; #Right displays the information in the right table, the left table displays the conditional intersection, and the others are null filled
summary
Regular expressions: mainly normal queries, plus where The field regexp is used for matching query
Operators are mainly common arithmetic, comparison, logic and bit operators
Arithmetic operator: select Number (+ - * /%) number
Comparison operators: select characters (>, < >, is null, between and, in, like, greenest, leastd, etc.); The statement displays 1 correctly, 0 incorrectly, or maximum and minimum
Logical operator: logical non not or!, It means taking negation, null returns null, 0 returns 1, and non-0 returns 0; Logic and and or & &, which means intersection. Null returns null, 0 returns 0, and non-0 returns 1; Logical or or is the union set. If there is non-0null return 1, if there is null return null, all 0 return 0; Logical xor XOR
Bit operator: convert to binary operation& Press bit and 11 to get 1, otherwise 0| By bit or, 1 gets 1, otherwise 0~ Inverse calculation by bit^ Bitwise XOR, different 1, otherwise 0;
Internal connection: mainly refers to the intersection of two table conditions. Left connection means that the data in the right table is obtained according to the left condition without null complementation, that is, the data in the left table is obtained according to the right condition without null complementation