①. Arithmetic operator (+ - * /)

• ①. Arithmetic operators are mainly used for mathematical operations , It can connect two values or expressions before and after the operator , Add... To a value or expression (+)、 reduce (-)、 ride (*)、 except (/、div) And modulus (%、mod) operation

• ②. Addition and subtraction operators , The following conclusions can be drawn from the calculation results :

1. An integer type value adds and subtracts integers , The result is still an integer ;
2. An integer type value adds and subtracts floating-point numbers , The result is a floating point number ;
3. Addition and subtraction have the same priority , The result of adding before subtracting is the same as that of subtracting before adding ;
4. stay Java in ,+ If there is a string on the left and right sides of , Then it represents the splicing of strings . But in MySQL in + Only value plus . If a non numeric type is encountered , First try to convert to a value , If the transfer fails , Just press the 0 Calculation ( Add :MySQL String functions are used in string splicing CONCAT() Realization )

SELECT 
100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 
FROM dual;

• ③. Multiplication and division operators

1. A number times an integer 1 And divided by an integer 1 After that, you still get the original number ;
2. A number times a floating point number 1 And divided by floating point numbers 1 Then it becomes a floating point number , The value is equal to the original number ;
3. A number divided by an integer , Whether or not we can eliminate , The result is a floating point number ;
4. Divide one number by another , When there is no end to it , The result is a floating point number , And keep it after the decimal point 4 position ;
5. Multiplication and division have the same priority , Perform the multiply then divide operation and the divide before multiply operation , The results are the same .
6. In mathematics ,0 Cannot be used as a divisor , stay MySQL in , A number divided by 0 by NULL.
   

mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;

• ④. modulus ( Seeking remainder ) Operator
  You can see ,100 Yes 3 The result after finding the module is 3, Yes 5 The result after finding the module is 0.

SELECT 12%2,12%5,-12%2,-12%5,-12%-2,-12%-5 FROM dual;

②. Comparison operator

• ①. The comparison operator is used to compare the operands on the left and right of the expression , If the comparison result is true, it returns 1, If the comparison result is false, it returns 0, Other cases return to NULL.
  

• ②. The equal sign operator

1. The equal sign operator (=) Judge the values on both sides of the equal sign 、 Whether the string or expression is equal , If equal, return 1, Return if not equal 0
2. When using the equal sign operator , Follow these rules :
   2.1 If the values on both sides of the equal sign 、 The string or expression is a string , be MySQL Will compare by string , It compares the number of characters in each string ANSI Whether the codes are equal .
   2.2 If the values on both sides of the equal sign are integers , be MySQL The size of the two values will be compared according to integers .
   2.3 If the value on both sides of the equal sign is an integer , The other is the string , be MySQL Will convert strings to numbers for comparison .
   2.4 If the values on both sides of the equal sign 、 One of the strings or expressions is NULL, The comparison result is NULL.
   

# Inquire about salary=10000, Pay attention to Java The comparison is == 
SELECT employee_id,salary FROM employees WHERE salary = 10000;

• ③. Security equals operator Security equals operator (<=>) And equals operator (=) It's similar , The only difference is ‘<=>’ It can be used for NULL Judge . In both operands are NULL when , Its return value is 1, Not for NULL; When an operand is NULL when , Its return value is 0, Not for NULL
  ( You can see , When using the safe equals operator , The values of the operands on both sides are NULL when , The result returned is 1 instead of NULL, Other returned results are the same as the equal operator )
  

• ④. Not equal to the operator (<> and !=) Used to judge the numbers on both sides 、 Whether the values of strings or expressions are not equal , Returns if not equal 1, Equal returns 0. Not equal to operator cannot judge NULL value . If either of the values on both sides is NULL, Or on both sides NULL, The result is NULL
  

• ⑤. There are also non symbolic operators :
  

• ⑥. Air transport operator (IS NULL perhaps ISNULL) Determines whether a value is NULL, If NULL Then return to 1, Otherwise return to 0
  

# Inquire about commission_pct be equal to NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);

• ⑦. Non air transport operators (IS NOT NULL) Judge whether a value is not NULL, If not for NULL Then return to 1, Otherwise return to 0
  

# Inquire about commission_pct It's not equal to NULL 
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL; 
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

• ⑧. Minimum operator The grammar format is :LEAST( value 1, value 2,…, value n). among ,“ value n” Indicates that there is... In the parameter list n It's worth . In the case of two or more parameters , Return minimum .
  ( As can be seen from the results , When the parameter is an integer or floating point number ,LEAST Will return the smallest value ; When the parameter is a string , Returns the first character in the alphabet ; When there is... In the comparison value list NULL when , Cannot judge size , The return value is NULL)
  
• ⑨. Maximum operator The grammar format is :GREATEST( value 1, value 2,…, value n). among ,n Indicates that there is... In the parameter list n It's worth . When there are two or more parameters , The return value is the maximum value . If any of the independent variables is NULL, be GREATEST() The return value of is NULL
  ( As can be seen from the results , When the parameter is an integer or floating point number ,GREATEST Will return the largest value ; When the parameter is a string , Returns the last character in the alphabet ; When there is... In the comparison value list NULL when , Cannot judge size , The return value is NULL)
  
• ⑨. BETWEEN AND Operator BETWEEN The format used by the operator is usually SELECT D FROM TABLE WHERE C BETWEEN A AND B, here , When C Greater than or equal to A, also C Less than or equal to B when , The result is 1, Otherwise, the result is 0

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;

• ⑩. IN Operator is used to determine whether a given value is IN A value in the list , If so, return 1, Otherwise return to 0

SELECT 
employee_id, last_name, salary, manager_id
FROM employees 
WHERE manager_id IN (100, 101, 201);

• ⑩①. LIKE Operator LIKE Operators are mainly used to match strings , Usually used for fuzzy matching ,LIKE Operators usually use the following general characters :
  “%”: matching 0 Characters or more
  “_”: Only one character can be matched
  Avoiding special symbols : Use escape characters . for example : take [%] To [$%]、[] To [$], And then add [ESCAPE‘$’] that will do
  If you use \ To signify an escape , Omit ESCAPE. If not , Then add ESCAPE

# Case study 1: Query employee name contains characters a Employee information 
SELECT * from employees where last_name like '%a%';
# Case list 2: Find out that the second character in the employee name is a, The third character is v Employees and wages 
SELECT last_name,salary from employees where last_name like '_av%'
# Case list 3: The second character in the employee name is _ Employee name of 
SELECT  last_name from employees where last_name like '_$_%' ESCAPE '$';

• ⑩②. REGEXP Operator when matching , There are several commonly used wildcards :

1. ‘^’ Matches a string that begins with the character after the character
2. ‘$’ Matches a string that ends with a character that precedes the character
3. ‘.’ Match any single character
4. “[…]” Match any character in square brackets . for example ,“[abc]” matching “a” or “b” or “c”. To name the range of characters , Use one individual ‘-’.“[a-z]” Match any letter , and “[0-9]” Match any number
5. ‘*’ Match zero or more characters before it . for example ,“x*” Match any number of ‘x’ character ,“[0-9]*” Match any number of numbers , and “*” Match any number of any characters
   

③. Logical operators

• ①. MySQL Chinese support 4 The logical operators are as follows :
  
• ②. About NOT、AND、OR、XOR Grammar demonstration of

SELECT 
last_name, job_id 
FROM employees 
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

SELECT 
employee_id, last_name, job_id, salary 
FROM employees 
WHERE salary >=10000 AND job_id LIKE '%MAN%';

#  Query basic salary is not in 9000-12000 Between employee number and base salary  
SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees WHERE salary <9000 OR salary > 12000; 
SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND 12000;

#  This only satisfies one of two conditions , Can't satisfy at the same time 
#  Or department_id yes 10、20 
#  Or salary>8000
select 
last_name,department_id,salary
from employees 
where department_id in (10,20) XOR salary > 8000;

• ③. OR You can talk to AND Use it together , But pay attention to the priority of the two when using , because AND Has a higher priority than OR, So first of all AND The operands on both sides operate , And again OR The operands in .

④. An operator ( understand )

• ①. Bit operators are operators that evaluate on binary numbers . Bit operators first convert operands to binary numbers , And then we do bit operations , Finally, the calculation result is changed from binary to decimal
  MySQL The supported bitwise operators are as follows :
  

• ②. About &、|、^ The presentation of
  
  

• ③. According to the not (~) Operator negates the binary number of a given value bit by bit , the 1 Turn into 0, take 0 Turn into 1

mysql> SELECT 10 & ~1;

• ④. Meet... Within a certain range : Every move to the left 1 position , Equivalent to times 2; Every move to the right , Equivalent to divided by 2.

# Meet... Within a certain range : Every move to the left 1 position , Equivalent to times 2; Every move to the right , Equivalent to divided by 2.
SELECT 4 << 1 , 8 >> 1
FROM DUAL;

⑤. Operator precedence

• ①. There is no need to memorize the contents of this department , You can open it when you don't remember

• ②. The larger the number , The higher the priority , Operators with higher priority are calculated first . You can see , The assignment operator has the lowest priority , Use “()” Enclosed expressions have the highest priority
  

⑥. Operator corresponding exercises

• ①. The corresponding topic

# 1. The choice of salary is not in 5000 To 12000 The name and salary of the employee  
# 2. Choice in 20 or 50 Name and department number of the employee working in department No  
# 3. Select the name of the employee who has no manager in the company and job_id 
# 4. Choose the name of the employee in the company who has the bonus , Salary and bonus levels  
# 5. The third letter of the employee's name is a Of employees  
# 6. Choose a name with letters a and k Of employees  
# 7. Show the table  employees  In the table  first_name  With  'e' Employee information at the end  
# 8. Show the table  employees  The department number is in  80-100  Names between 、 Type of work  
# 9. Show the table  employees  Of  manager_id  yes  100,101,110  Of employees 、 Wages 、 managers id

• ②. Corresponding topic analysis

# 1. The choice of salary is not in 5000 To 12000 The name and salary of the employee 
SELECT last_name,salary
FROM employees
#where salary not between 5000 and 12000;
WHERE salary < 5000 OR salary > 12000;

# 2. Choice in 20 or 50 Name and department number of the employee working in department No 
SELECT last_name,department_id
FROM employees
# where department_id in (20,50);
WHERE department_id = 20 OR department_id = 50;

# 3. Select the name of the employee who has no manager in the company and job_id
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IS NULL;

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id <=> NULL;

# 4. Choose the name of the employee in the company who has the bonus , Salary and bonus levels 
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;

# 5. The third letter of the employee's name is a Of employees 
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';


# 6. Choose a name with letters a and k Of employees 
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
#where last_name like '%a%' and last_name LIKE '%k%';

# 7. Show the table  employees  In the table  first_name  With  'e' Employee information at the end 
SELECT first_name,last_name
FROM employees
WHERE first_name LIKE '%e';

SELECT first_name,last_name
FROM employees
WHERE first_name REGEXP 'e$'; #  With e How to write the beginning :'^e'

# 8. Show the table  employees  The department number is in  80-100  Names between 、 Type of work 
SELECT last_name,job_id
FROM employees
# The way 1: recommend 
WHERE department_id BETWEEN 80 AND 100;
# The way 2: recommend , And way 1 identical 
#where department_id >= 80 and department_id <= 100;
# The way 3: Only applicable to this question .
#where department_id in (80,90,100);
SELECT * FROM departments;

# 9. Show the table  employees  Of  manager_id  yes  100,101,110  Of employees 、 Wages 、 managers id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);