当前位置:网站首页>Mysql database explanation (8)

Mysql database explanation (8)

2022-04-23 15:19:00 C chord~

Catalog

One . Regular expressions

 ​

Two . Operator  

1. Arithmetic operation

2. Comparison operations

 2.1 be equal to (=)

2.2  It's not equal to (!= or <>)

2.3  Greater than 、 Greater than or equal to 、 Less than 、 Less than or equal to

2.4  Judge a value as / Not for null(is null,is not null)

2.5  Between the two (between … and …)

2.6  When there are two or more parameters , Returns the largest of / minimum value , If one of them is null, Then return to null(least、greatest)

2.7  stay / Not in the assembly (in/not in) 

2.8  Wildcard match

3. Logical operations

3.1 Logic is not (not or !)

3.2  Logic and (and or &&)

 3.3  Logic or (or)

3.4  Logical XOR (xor)

4、 An operator  

5. priority

3、 ... and .. Link query

1、 Internal connection

2、 Left connection

3、 The right connection

summary


One . Regular expressions

  • MysQL Regular expressions are usually used when retrieving database records , Match the special string in the record that meets the requirements according to the specified matching pattern .
  •  MysQL Regular expressions for use REGEXP Use this keyword to specify the matching pattern of the regular expression
 Matching mode 	 describe 
^	     Match the start character of the text 
$	     Match the end character of the text 
.	     Match any single character 
*	     Match zero or more characters before it 
+	     Match preceding characters  1  Times or times 
 character string 	 Match contains the specified string 
p1Ip2	 matching  p1  or  p2
[…]	     Match any character in the character set 
[^…]	 Match any characters that are not in brackets 
{n}	     Match the previous string  n  Time 
{n,m}	 Match the previous string at least  n  Time , at most m  Time 

Don't talk much , Last case

 

 

 

Two . Operator  

  • MysQL There are four kinds of operators , Namely : Arithmetic operator 、 Comparison operator 、 Logical and bitwise operators .

1. Arithmetic operation

 Operator 	 describe 
 +	     Add 
 -	     Subtraction 
 *	     Multiplication 
 /	     division 
 %	     Remainder 


 In Division , The divisor cannot be zero 0, If the divisor is 0, The result returned is  null .
  If there are multiple operators , According to the priority of multiplication and division before addition and subtraction , Operations of the same priority , There is no order 

2. Comparison operations

 Common comparison operators 	 explain 
  =	             be equal to 
 != or <>	         It's not equal to 
 >	             Greater than 
 >=	             Greater than or equal to 
 <	             Less than 
 <=	             Less than or equal to 
 is null	     Judge whether it is null
 is not null     Judge whether it is not null
 between and 	 Between the two 
 greatest	     Returns the maximum value when two or more parameters 
 least	         Returns the minimum value when two or more parameters 
 in	             In the assembly 

 2.1 be equal to (=)

Equal sign (=) To judge numbers 、 Whether the string and expression are equal , If equal, return 1, Returns if not equal 0.
  If one of the two values compared is NULL, The result of the comparison is NULL.
  The comparison of characters is based on ASCII Code to judge , If ASCIl The code is equal , It means that the two characters are the same ; If ASCII The codes are not equal , It means that the two characters are different . Like strings ( Letter ) Compare : (‘a’ > ‘b’) In fact, the comparison is the bottom ASCll Code needs attention ASCII The code has :a、A、0

summary : Conditional return 1, Not established to return 0, Yes null return null


2.2  It's not equal to (!= or <>)

  • Used for numbers 、 Comparison of unequal strings and expressions , Returns if not equal 1, If equal, return 0
  •   It's not equal to (!=,<>) Cannot be used to determine whether it is null

summary : Set up return 1, Not established to return 0, Yes null return null

2.3  Greater than 、 Greater than or equal to 、 Less than 、 Less than or equal to

  • Greater than (>) Operator is used to determine whether the operand on the left is greater than the operand on the right , If greater than, return 1, Otherwise return to 0, Also cannot be used to judge NULL
  •   Less than (<) Operator is used to determine whether the operand on the left is less than the operand on the right , If less than, return 1, Otherwise return to 0, Also cannot be used to judge NULL
  •   Greater than or equal to (>=) Judge whether the operand on the left is greater than or equal to the operand on the right , If greater than or equal to, return 1, Otherwise return to 0, Cannot be used to judge NULL
  •   Less than or equal to (<=) Judge whether the operand on the left is less than or equal to the operand on the right , If less than or equal to, return 1, Otherwise return to 0, Cannot be used to judge NULL

summary : Conditional return 1, Not established to return 0, Yes null return null

2.4  Judge a value as / Not for null(is null,is not null)

  • IS NULL Determines whether a value is NULL, If NULL return 1, Otherwise return to 0
  •  IS NOT NULL Judge whether a value is not NULL, If not for NULL return 1, Otherwise return to 0

 

 

2.5  Between the two (between … and …)

  • Usually used to determine whether a value is between two values .

 

2.6  When there are two or more parameters , Returns the largest of / minimum value , If one of them is null, Then return to null(least、greatest)

2.7  stay / Not in the assembly (in/not in) 

Judge a value in (in) Or not (not in) Collection , If established, return to 1, If not, return to 0

2.8  Wildcard match

  •  LIKE Used to match strings , If the match is successful, it returns 1, Instead, return to 0
  •  LIKE Two wildcards are supported :‘%’ Used to match any number of characters , and ’_' Only one character can be matched .

 

 

3. Logical operations

  •   Logical operators are also called Boolean operators , It is usually used to judge whether an expression is true or false , If it is true to return 1, Otherwise return to 0, True and false can also be used TRUE and FALSE Express .
  • MySQL There are four logical operators supported in
 Operator 			 describe 
not  or  !	     Logic is not 
and  or  &&		 Logic and 
or 				 Logic or 
xor				 Logical XOR 

3.1 Logic is not (not or !)

  • Logical non negates the value following him , If NOT The following operands are 0 when , The results are as follows 1
  •   If the operand is not 0 when , The results are as follows 0
  •   If the operand is NULL when , The results are as follows NULL
  • summary : The following value is not 0 Then return to 0, by 0 Then return to 1, by null Then return to null

3.2  Logic and (and or &&)

  • When all operands are non-zero and not NULL when , The return value is 1
  •   When one or more operands are 0 when , The return value is 0
  •   Any one of the operands is NULT when , The return value is NULL
  • summary : All values are non-zero and not null return 1, Yes null Then return to null, Otherwise return to 0.

 3.3  Logic or (or)

  • When both operands are not NULL When the value of , If any of the operands is nonzero , The return value is 1, Otherwise, the result is 0
  •   When one of the operands is NULL when , If the other operand is nonzero , The return value is 1, Otherwise, the result is NULL
  •   Suppose both operands are NULL when , The return value is NULL.
  • summary : One of the operands is not 0 And not for null Then return to 1, All for null Then return to null

 

3.4  Logical XOR (xor)

  • When any one of the operands is NULL when , The return value is NULL
  •   For non NULL The number of operations , If both operands are not 0 Value or both 0 value , The return value is 0
  •   If one is for 0 value , The other is not 0 value , The return value is 1
  • summary : There is... In any one of the operands null、 return null, None of the operands are 0 Or both 0 When to return to 0, There is only one for 0 When to return to 1

4、 An operator  

  • Bit operators are operators that evaluate on binary numbers
  •   Bit operations first convert operands into binary numbers , Do bit operations
  •   Then change the result from binary to decimal
 An operator 	     describe 
&	         Bitwise AND 
I	         Press bit or 
^	         Bitwise XOR 
!	         Take the opposite 
<<	         Move left 
>>           Move right 

summary : Bit operators need to be converted to binary for calculation

Bitwise AND (&):1*1 have to 1, otherwise 0

Press bit or (|): Yes 1 Of 1, otherwise 0

Bitwise XOR (^): Different 1, otherwise 0

According to the not (~): Take the opposite first , after 1*1 have to 1, otherwise 0

5. priority

!
~
^
*、/、%
+、-
<<、>>
&
|
=、<=>、>=、>、<=、<、<>、!=、like、regexp、in
between、case、when、then、else
not
&&、and
or、xor
:=

3、 ... and .. Link query

  • MysQL Connection query for , It's usually a combination of rows from two or more tables , Based on the common fields between these tables , Data splicing .
  •   First , To determine a primary table as the result set , Then selectively connect the rows of other tables to the selected main table result set .
  •   More frequently used join queries include : Internal connection 、 Left connection and right connection

1、 Internal connection

grammar

select  Field  from  surface 1 inner join  surface 2 on  surface 1. Field = surface 2. Field 

  •  MySQL An inner join in a table is a combination of data records in two or more tables that meet certain conditions at the same time .
  •   Usually in from Use keywords in clauses inner join To connect multiple tables , And use on Clause sets the join condition
  •   Internal connection is the default table connection , So in from Clause can be omitted inner keyword , Use keywords only join , When there are multiple tables at the same time , It can also be used continuously inner join To realize the internal connection of multiple tables , But for better performance , It is recommended not to exceed three tables

2、 Left connection

  •   The left connection can also be called the left outer connection , stay from Clause left join perhaps left out join Keyword to represent .
  •   The left join is based on the left table , Receive all rows of the left table , And use these lines to match the records in the reference table on the right , That is, match all rows in the left table and the qualified rows in the right table .
  •   Left connection , All the records in the left table will be shown , The right table will only display the records that meet the search criteria , The deficiencies are null

3、 The right connection

  •   The right connection is also called the right outer connection , stay from Clause right join perhaps right out join Keyword to represent .
  •   The right connection is the opposite of the left connection , It is based on the right table , Used to receive all rows in the right table , And use these records to match the rows in the left table

summary

For many judgments in this article, the return value can obey an atom ; Whether the command is true ( That is, whether it can achieve , Whether it conforms to common sense, such as 3>4 For false ,3<4 It's true ), If true, return to 1, False returns 0.

版权声明
本文为[C chord~]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231406064014.html