MySQl learning (from entry to proficiency 1.2)

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 3_Operators

1. Arithmetic operators

Arithmetic operators are mainly used for mathematical operations, which can connect two values or expressions before and after the operator, and perform addition (+), subtraction (-), multiplication (*), division (/) and extraction of values or expressions Modulo (%) operation.

1. Addition and Subtraction Operators

mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM dual;
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.00 sec)

The following conclusions can be drawn from the operation results:

A value of integer type performs addition and subtraction operations on integers, and the result is still an integer;
A value of integer type performs addition and subtraction operations on floating-point numbers, and the result is a floating-point number;
Addition and subtraction have the same priority, and the result of adding first and then subtracting is the same as performing first subtraction and then adding;
In Java, if there are strings on the left and right sides of +, it means the splicing of strings. But in MySQL, + only means adding values. If a non-numeric type is encountered, try to convert it to a numeric value first, and if the conversion fails, it will be calculated as 0. (Supplement: String concatenation in MySQL is implemented using the string function CONCAT())

#The operator dual creates a virtual table

SELECT 100,100+0,100+20,100+20*30,100+22.2 FROM DUAL;

#Indicates the addition operation is not like java to get the string concatenation, pay attention  
SELECT 100+'1' FROM DUAL;

#By default, characters are treated as 0
SELECT 100+'a' from DUAL;

#Null The results of participating operations are all null
SELECT 100+NULL FROM DUAL;

2. Multiplication and Division Operators

+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3 | 100
DIV 0 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
| 100 | 100 | 100.0 | 100.0000 | 50.0000 | 105.0000 | 33.3333 |
NULL |
+-----+---------+-----------+-----------+---------+-----------------+---------+-------
----+
1 row in set (0.00 sec)

#div=/ denominator is 0 value is 0
SELECT  100 , 100 * 1 , 100 * 1.0 ,100 / 1.0 , 100 / 2 , 100 + 2 * 5 / 2 , 100 /3 ,
100 DIV 0 FROM DUAL;

#Calculate the employee's annual base salary
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;

The following conclusions can be drawn from the operation results:

After multiplying a number by integer 1 and dividing by integer 1, the original number is still obtained;
A number multiplied by floating point number 1 and divided by floating point number 1 becomes a floating point number, and the value is equal to the original number;
After a number is divided by an integer, no matter whether it can be divided completely, the result is a floating point number;
When one number is divided by another number, the result is a floating point number, and the result is kept to 4 decimal places;
The priority of multiplication and division is the same, and the result of multiplying first and then dividing is the same as that of dividing first and then multiplying.
In mathematical operations, 0 cannot be used as a divisor, in MySQL In , a number divided by 0 is NULL.

3. modulo (remainder) operator

Perform a modulo (remainder) operation on field i in the t22 table for 3 and 5.

mysql> SELECT 12 % 3, 12 MOD 5 FROM dual;
+--------+----------+
| 12 % 3 | 12 MOD 5 |
+--------+----------+
| 0 | 2 |
+--------+----------+
1 row in set (0.00 sec)

#Modulo operation: % mod The number in front of the divisor is a negative number, then the value he gets is a negative number
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12%-5 FROM DUAL;

#Query the value of employee_id in the employees table that is an even number
 SELECT employee_id,last_name,salary FROM employees
 WHERE employee_id % 2 = 0;

#Filter out employees whose employee_id is an even number
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;

It can be seen that the result of 100 modulo 3 is 3, and the result of modulo 5 is 0.

2. Comparison operators

The comparison operator is used to compare the operand on the left side of the expression with the operand on the right side. If the result of the comparison is true, it will return 1, if the result of the comparison is false, it will return 0, and otherwise it will return NULL.

Comparison operators are often used as the conditions of the SELECT query statement to return the result records that meet the conditions.

#Comparison operator 0 is false, 1 is ftrue, there is an implicit conversion of the string, and it is 0 if the conversion is unsuccessful

SELECT 1=2, 1 !=2,2='a',0='a' FROM DUAL;

1. equals operator

The equal sign operator (=) judges whether the values, strings, or expressions on both sides of the equal sign are equal, and returns 1 if they are equal, and 0 if they are not equal.
When using the equality operator, follow these rules:

1. If the values, strings or expressions on both sides of the equal sign are all strings, MySQL will compare them according to the strings, which compares whether the ANSI codes of the characters in each string are equal.
2. If the values on both sides of the equal sign are integers, MySQL will compare the size of the two values according to the integer.
3. If one of the values on both sides of the equal sign is an integer and the other is a string, MySQL will convert the string to a number for comparison.
4. If one of the values, strings or expressions on both sides of the equal sign is NULL, the comparison result is NULL.
Contrast: When the assignment symbol in SQL uses :=, the return value is 0, not NULL.

mysql> SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL =
NULL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 | 1 | 0 | 1 | 1 | NULL | NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
| 0 | 1 | 0 |
+-------+-----------+-----------+
1 row in set, 2 warnings (0.00 sec)

#Query salary=10000, note that the comparison in Java is ==
SELECT employee_id,salary FROM employees WHERE salary = 10000;

#Comparison operator 0 is false, 1 is ftrue, there is an implicit conversion of the string, and it is 0 if the conversion is unsuccessful

#As long as null participates in the comparison judgment, the result will be empty
SELECT 1=NULL, NULL=NULL
FROM DUAL;


SELECT last_name,salary 
FROM employees 
WHERE salary = 6000;



SELECT last_name,salary 
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct=NULL;

2. Safe Equals Operator

mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL
<=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+-------------
--+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=>
NULL |
+-----------+---------+-------------+---------------------+-------------+-------------
--+
| 1 | 0 | 1 | 1 | 0 |
1 |
+-----------+---------+-------------+---------------------+-------------+-------------
--+
1 row in set (0.00 sec)

#Query commission_pct is equal to 0.40
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
#What if we change 0.40 to NULL?

It can be seen that when using the safe equal operator, when the values of the operands on both sides are NULL, the returned result is 1 instead of NULL, and the other returned results are the same as the equal operator.

#<=> : Security is equal to the difference that the judgment of NULl will also be judged  
#   born for null


SELECT 1<=>1, 1 <=>2,2<=>'a',0<=>'a' FROM DUAL;

#Empty can be compared normally, add <=>

SELECT 1<=> NULL, NULL<=> NULL
FROM DUAL;

#Can query the data whose commission_pct is empty in the table
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct<=>NULL;

3. not equal to operator

The not equal operators (<> and !=) are used to judge whether the values of numbers, strings or expressions on both sides are not equal, and return 1 if they are not equal, and return 0 if they are equal. The not equal operator cannot evaluate NULL values. If either or both values are NULL, the result is NULL. Examples of SQL statements are as follows:

As you can see, when using the safe equal operator, the values of the operands on both sides are NULL , returns a result of 1 instead of NULL，other
 Returns the same result as the equals operator.
3．Not equal to operator Not equal to operator (<>and!=)It is used to judge whether the values of numbers, strings or expressions on both sides are not equal,
Returns 1 if not equal, 0 if equal. The not-equal-to operator cannot evaluate NULL value. If any of the values on both sides is NULL，
or both sides as NULL，then the result is NULL.  SQL An example statement is as follows:

# <> != not equal to
SELECT 3<>2 ,3!=2,2<>2 ,2!=2 FROM DUAL;

#Empty is empty when compared with any data
SELECT  '4'<>NULL,''!=NULL,NULL!=NULL FROM DUAL;

Additionally, there are operators for non-symbolic types:

#Query commission_pct is empty
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct  IS NULL;

#or as keywords
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE ISNULL(commission_pct);

#Query commission_pct is not empty
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct  IS NOT NULL;

#or as keywords
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE NOT commission_pct <=>NULL;

#LEAST(value1,value2,...) minimum value GREATEST(value1,value2,...) maximum value
SELECT LEAST('a','c','d','z','v'),
GREATEST('a','c','d','z','v') FROM DUAL;


SELECT LEAST(first_name,last_name) , LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#BETWEENt condition 1 AND condition 2 (query the value of the range from condition 1 to condition 2, including boundary values. For example, if condition 2 is less than 5000, it includes 5000)
#Condition 1 must be the start condition, condition 2 must be the end condition Note otherwise no data can be found
#The value of salary in the query table employees is 6000-8000, including 6000 and 8000

SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6600 AND 8000;

#or below

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>=6000 && salary<=8000;


#Error case data cannot be queried
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>=8000 && salary<=6000;


#Query data not between 6000 and 8000

SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6600 AND 8000;

#or or means AND and && means and
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<6000  or salary>8000;

#in (collection) not in (collection)
#in multi-condition value query query value is 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id =10 OR department_id=20 OR department_id=30 ;

SELECT department_id,last_name,salary
FROM employees
WHERE department_id in(10,20,30);

#Query not in which is not 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id NOT in(10,20,30);

#like fuzzy query query similar value
#%% represents an indeterminate number of characters, representing that there are several values before and after that may be 0, 1, or other
#Query name contains the value of a
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%';



#Query the value starting with a in name
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE 'a%';

#Query the value at the end of a in name
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a';

#query contains the value of a e
#Writing 1 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

#Writing 2 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';

#Query the second character is the value of a
#_ stands for undefined character
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '_a%'

#REGEXP RLIKE regular expression

#Query whether it starts with y, ends with O, contains wen
SELECT 'yanwenchao' REGEXP '^y','yanwenchao' REGEXP 'o$',
'yanwenchao' REGEXP 'wen' FROM DUAL;

#... 3 dots mean that there are 3 arbitrary values in the middle, which can be corresponding before and after  
#[af] Any one of the letters is passed or all 
SELECT 'yanwenchao' REGEXP 'yan...ch','yanwenchao' REGEXP '[af]'
FROM DUAL;

1 is true 2 is false

4. Null operator

The null operator (IS NULL or ISNULL) determines whether a value is NULL, returns 1 if it is NULL, and returns 0 otherwise. Examples of SQL statements are as follows:

mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)

#Query commission_pct equals NULL. Compare the following four writing methods
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);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

#Query commission_pct is empty
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct  IS NULL;

#or as keywords
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE ISNULL(commission_pct);

5. Not null operator

The not-null operator (IS NOT NULL) determines whether a value is not NULL, and returns 1 if it is not NULL, otherwise returns 0. Examples of SQL statements are as follows:

mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;
+------------------+-----------------+---------------+
| NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL |
+------------------+-----------------+---------------+
| 0 | 1 | 1 |
+------------------+-----------------+---------------+
1 row in set (0.01 sec)

SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

#Query commission_pct is not empty
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE commission_pct  IS NOT NULL;

#or as keywords
SELECT last_name,salary ,commission_pct
FROM employees 
#WHERE salary = NULL; #As long as null participates in the judgment, there will be no result
WHERE NOT commission_pct <=>NULL;

6. Minimum operator

The syntax format is: LEAST (value 1, value 2, ..., value n). Among them, "value n" indicates that there are n values in the parameter list. With two or more arguments, returns the smallest value.

mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)

#LEAST(value1,value2,...) minimum value GREATEST(value1,value2,...) maximum value
SELECT LEAST('a','c','d','z','v'),
GREATEST('a','c','d','z','v') FROM DUAL;


SELECT LEAST(first_name,last_name) , LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

As can be seen from the results, when the parameter is an integer or a floating point number, LEAST will return the smallest value; when the parameter is a string, it will return the first character in the alphabet; when there is NULL in the comparison value list, The size cannot be judged, and the return value is NULL.

7. Maximum value operator

The syntax format is: GREATEST (value 1, value 2, ..., value n). Among them, n means that there are n values in the parameter list. When there are two or more parameters, the return value is the maximum value. If any one of the arguments is NULL, the return value of GREATEST() is NULL.

mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)

As can be seen from the results, when the parameter is an integer or a floating point number, GREATEST will return the largest value; when the parameter is a string, it will return the last character in the alphabet; when there is NULL in the comparison value list , the size cannot be judged, and the return value is NULL.

#LEAST(value1,value2,...) minimum value GREATEST(value1,value2,...) maximum value
SELECT LEAST('a','c','d','z','v'),
GREATEST('a','c','d','z','v') FROM DUAL;


SELECT LEAST(first_name,last_name) , LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;

8. BETWEEN AND operator

The format used by the BETWEEN operator is usually SELECT D FROM TABLE WHERE C BETWEEN AAND B. At this time, when C is greater than or equal to A, and C is less than or equal to B, the result is 1, otherwise the result is 0.

mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';
+-------------------+----------------------+-------------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+
| 1 | 0 | 1 |
+-------------------+----------------------+-------------------------+
1 row in set (0.00 sec)

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

#BETWEENt condition 1 AND condition 2 (query the value of the range from condition 1 to condition 2, including boundary values. For example, if condition 2 is less than 5000, it includes 5000)
#Condition 1 must be the start condition, condition 2 must be the end condition Note otherwise no data can be found
#The value of salary in the query table employees is 6000-8000, including 6000 and 8000

SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6600 AND 8000;

#or below

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>=6000 && salary<=8000;


#Error case data cannot be queried
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>=8000 && salary<=6000;


#Query data not between 6000 and 8000

SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6600 AND 8000;

#or or means AND and && means and
SELECT employee_id,last_name,salary
FROM employees
WHERE salary<6000  or salary>8000;

9. IN operator

The IN operator is used to determine whether the given value is a value in the IN list, and returns 1 if it is, and 0 otherwise. If the given value is NULL, or if NULL is present in the IN list, the result is NULL.

mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+

WHERE manager_id IN (100, 101, 201);

#in (collection) not in (collection)
#in multi-condition value query query value is 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id =10 OR department_id=20 OR department_id=30 ;

SELECT department_id,last_name,salary
FROM employees
WHERE department_id in(10,20,30);

#Query not in which is not 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id NOT in(10,20,30);

10. NOT IN operator

The NOT IN operator is used to determine whether the given value is not a value in the IN list, and if it is not a value in the IN list, it returns 1, otherwise it returns 0.

mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
| 0 | 1 |
+--------------------------+----------------+
1 row in set (0.00 sec)

#in (collection) not in (collection)
#in multi-condition value query query value is 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id =10 OR department_id=20 OR department_id=30 ;

SELECT department_id,last_name,salary
FROM employees
WHERE department_id in(10,20,30);

#Query not in which is not 10 20 30 
SELECT department_id,last_name,salary
FROM employees
WHERE department_id NOT in(10,20,30);

11. LIKE operator

The LIKE operator is mainly used to match strings, usually for fuzzy matching, and returns 1 if the condition is met, otherwise returns 0. If the given value or matching condition is NULL, the result is NULL.
The LIKE operator usually uses the following wildcards:

"%": Matches 0 or more characters.
"_": Can only match one character.

Examples of SQL statements are as follows:

mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL;
+-----------------+-----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL |
+-----------------+-----------------+
| NULL | NULL |
+-----------------+-----------------+
1 row in set (0.00 sec)

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';

ESCAPE

To avoid special symbols: use escape characters. For example: convert [%] to [ %], [] to [ ], and then add [ESCAPE '$'].

SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT\_%';

If \ is used for escaping, ESCAPE is omitted. If it is not \, add ESCAPE.

SELECT job_id
FROM jobs
WHERE job_id LIKE 'IT$_%' escape '$';

#like fuzzy query query similar value
#%% represents an indeterminate number of characters, representing that there are several values before and after that may be 0, 1, or other
#Query name contains the value of a
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%';



#Query the value starting with a in name
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE 'a%';

#Query the value at the end of a in name
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a';

#query contains the value of a e
#Writing 1 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

#Writing 2 
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';

#Query the second character is the value of a
#_ stands for undefined character
SELECT department_id,last_name,salary
FROM employees
WHERE last_name LIKE '_a%'

12. REGEXP operator

The REGEXP operator is used to match strings, and the syntax format is: expr REGEXP matching conditions. If expr satisfies the matching condition, return

(1) '^' matches a string beginning with the character following this character.
(2) '$' matches a string ending with the character preceding it.
(3) '.' matches any single character.
(4) "[…]" matches any character within square brackets. For example, "[abc]" matches "a" or "b" or "c". To name ranges of characters, use a '-'. "[a-z]" matches any letter, and "[0-9]" matches any number.
(5) '' matches zero or more characters preceding it. For example, "x" matches any number of 'x' characters, "[0-9]" matches any number of digits, and "" matches any number of any character.

Examples of SQL statements are as follows:

mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
+------------------------+------------------------+-------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+-------------------------+
| 1 | 1 | 1 |
+------------------------+------------------------+-------------------------+
1 row in set (0.01 sec)

mysql> SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]';
+--------------------------+-------------------------+
| 'atguigu' REGEXP 'gu.gu' | 'atguigu' REGEXP '[ab]' |
+--------------------------+-------------------------+
| 1 | 1 |
+--------------------------+-------------------------+
1 row in set (0.00 sec)

#REGEXP RLIKE regular expression

#Query whether it starts with y, ends with O, contains wen
SELECT 'yanwenchao' REGEXP '^y','yanwenchao' REGEXP 'o$',
'yanwenchao' REGEXP 'wen' FROM DUAL;

#... 3 dots mean that there are 3 arbitrary values in the middle, which can be corresponding before and after  
#[af] Any one of the letters is passed or all 
SELECT 'yanwenchao' REGEXP 'yan...ch','yanwenchao' REGEXP '[af]'
FROM DUAL;

1 is true 2 is false

3. Logical operators

Logical operators are mainly used to judge the true or false of an expression. In MySQL, the return result of logical operators is 1, 0 or NULL. There are 4 logical operators supported in MySQL as follows:

1. logical NOT operator
The logical NOT (NOT or !) operator means that when the given value is 0, it returns 1; when the given value is non-zero, it returns 0; when the given value is NULL, it returns NULL.

mysql> SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
| 0 | 1 | 0 | 1 | NULL |
+-------+-------+----------+--------+----------+
1 row in set, 1 warning (0.00 sec)

WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

2. logical AND operator

The logical AND (AND or &&) operator returns 1 when all given values are non-zero and none of them are NULL; returns 0 when one or more given values are 0; otherwise returns NULL.

mysql> SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
| 1 | 0 | 0 | NULL |
+----------+---------+------------+------------+
1 row in set (0.00 sec)

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

3.1 Logical OR operator

The logical or (OR or ||) operator returns 1 when none of the given values are NULL, and any value is non-zero, otherwise it returns 0; when one value is NULL, and the other value is When the value is not 0, it returns 1, otherwise it returns NULL; when both values are NULL, it returns NULL.

mysql> SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL |
+---------+--------+-----------+-----------+--------------+
| 1 | 1 | 1 | NULL | NULL |
+---------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.00 sec)

#Query the employee ID and basic salary whose basic salary is not between 9000-12000
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;

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

Notice:
OR can be with AND Use together, but pay attention to the priority of the two when using, because AND takes precedence over OR，so first
 right AND The operands on both sides are operated on, and then with OR The operands in are combined.

4. Logical XOR operator
The logical exclusive OR (XOR) operator returns NULL when any of the given values is NULL; if both non-NULL values are 0 or are not equal to 0, then returns 0; if a value is 0, and another value is not 0, then returns 1.

| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
| 0 | 1 | 0 | NULL | 1 | 0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.00 sec)

select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;

Tidy up the code:

#or: or satisfy one and and both must be satisfied

SELECT department_id,last_name,salary
FROM employees
WHERE department_id =10 OR salary>6000;



#not not empty
SELECT department_id,last_name,salary
FROM employees
#WHERE department_id =10 OR salary>6000;
#HERE commission_pct is NULL;#empty 
WHERE commission_pct is not NULL;#non-empty
# WHERE Not commission_pct <=> NULL;#non-empty

#XOR Exclusive OR Understanding: Two conditions are met, one of which is definitely not satisfied 
#The query must be the data that satisfies one of them but not the other
SELECT department_id,last_name,salary
FROM employees
WHERE department_id =10 XOR salary>6000;

4. Bitwise operators

Bitwise operators are operators that perform calculations on binary numbers. Bitwise operators first convert the operands into binary numbers, then perform bit operations, and finally convert the calculation results from binary back to decimal numbers.

The bitwise operators supported by MySQL are as follows:

1. bitwise AND operator
The bitwise AND (&) operator performs logical AND operation bit by bit of the binary number corresponding to the given value. When the value of the binary bit corresponding to the given value is 1, the bit returns 1, otherwise it returns 0.

mysql> SELECT 1 & 10, 20 & 30;
+--------+---------+
| 1 & 10 | 20 & 30 |
+--------+---------+
| 0 | 20 |
+--------+---------+
1 row in set (0.00 sec)

The binary number of 1 is 0001, and the binary number of 10 is 1010, so the result of 1 & 10 is 0000, and the corresponding decimal number is 0. The binary number of 20 is 10100, and the binary number of 30 is 11110, so the result of 20 & 30 is 10100, and the corresponding decimal number is 20.

2. Bitwise OR operator
The bitwise OR (|) operator performs a bit-by-bit logical OR operation on the binary numbers corresponding to the given value. When one or both of the binary bits corresponding to the given value are 1, the bit returns 1, otherwise it returns 0.

mysql> SELECT 1 | 10, 20 | 30;
+--------+---------+
| 1 | 10 | 20 | 30 |
+--------+---------+
| 11 | 30 |
+--------+---------+
1 row in set (0.00 sec)

The binary number of 1 is 0001, and the binary number of 10 is 1010, so the result of 1|10 is 1011, which corresponds to 11 in decimal. The binary number of 20 is 10100, and the binary number of 30 is 11110, so the result of 20|30 is 11110, and the corresponding decimal number is 30.

mysql> SELECT 1 ^ 10, 20 ^ 30;
+--------+---------+
| 1 ^ 10 | 20 ^ 30 |
+--------+---------+
| 11 | 10 |
+--------+---------+
1 row in set (0.00 sec)

The binary number of 1 is 0001, and the binary number of 10 is 1010, so the result of 1^10 is 1011, which corresponds to 11 in decimal. The binary number of 20
The base number is 10100, and the binary number of 30 is 11110, so the result of 20^30 is 01010, which corresponds to 10 in decimal.

Another example:

mysql> SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
| 4 | 13 | 9 |
+--------+--------+--------+
1 row in set (0.00 sec)

SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;

4. Bitwise negation operator

The bitwise inversion (~) operator inverts the binary number of a given value bit by bit, that is, 1 is changed to 0, and 0 is changed to 1.

mysql> SELECT 10 & ~1;
+---------+
| 10 & ~1 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)

5. Bitwise right shift operator

The bitwise right shift (>>) operator shifts all bits of the binary number of the given value to the right by the specified number of bits. After the specified number of digits is shifted to the right, the value of the low bit on the right is shifted out and discarded, and the vacant position of the high bit on the left is filled with 0.

mysql> SELECT 1 >> 2, 4 >> 2;
+--------+--------+
| 1 >> 2 | 4 >> 2 |
+--------+--------+
| 0 | 1 |
+--------+--------+
1 row in set (0.00 sec)

The binary number of 1 is 0000 0001, shifted right by 2 bits is 0000 0000, and the corresponding decimal number is 0. The binary number of 4 is 0000 0100, shifted right by 2 bits is 0000 0001, and the corresponding decimal number is 1.

#Within a certain range, he is satisfied: each shift to the left is equivalent to multiplying by 2
#Moving one bit to the right is equivalent to dividing by 2 as can be seen in the figure
SELECT 1 >> 2, 4 >> 2;

6. Bitwise left shift operator

The bitwise left shift (<<) operator shifts all bits of the binary number of a given value to the left by the specified number of bits. After shifting the specified number of digits to the left, the value of the high bit on the left is shifted out and discarded, and the vacant position of the low bit on the right is filled with 0.

mysql> SELECT 1 << 2, 4 << 2;
+--------+--------+
| 1 << 2 | 4 << 2 |
+--------+--------+
| 4 | 16 |
+--------+--------+
1 row in set (0.00 sec)

The binary number of 1 is 0000 0001, shifted two bits to the left is 0000 0100, and the corresponding decimal number is 4. The binary number of 4 is 0000 0100, shifted two bits to the left is 0001 0000, and the corresponding decimal number is 16.

5. Operator precedence

The higher the number, the higher the priority, and the operator with the higher priority is evaluated first. It can be seen that the assignment operator has the lowest priority, and the expression enclosed by "()" has the highest priority.

Extension: use regular expression query

Regular expressions are usually used to retrieve or replace text content that matches a certain pattern, and match special strings that meet the requirements in the text according to the specified matching pattern. For example, extracting phone numbers from a text file, finding repeated words in an article, or replacing certain sensitive words entered by users, etc., these places can use regular expressions. Regular expressions are powerful and flexible, and can be applied to very complex queries.

MySQL uses the REGEXP keyword to specify the character matching mode of a regular expression. The following table lists the common character matching list in the REGEXP operator.

1. Query records starting with a specific character or string

The character '^' matches text beginning with a specific character or string. In the fruits table, query the records whose f_name field starts with the letter 'b', the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';

2. Query for records ending with a specific character or string

The character '$' matches text ending with a specific character or string.
In the fruits table, query the records whose f_name field ends with the letter 'y', the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';

3. Use the symbol "." to replace any character in the string

The character '.' matches any character. In the fruits table, query the f_name field value contains the letters 'a' and 'g' and there is only one letter between the two letters. The SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';

4. Use "*" and "+" to match multiple characters

The asterisk '*' matches the preceding character any number of times, including 0 times. The plus sign '+' matches the preceding character at least once. In the fruits table, query the records whose f_name field value starts with the letter 'b' and the letter 'a' appears after 'b', the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';

5. Match the specified string

A regular expression can match a specified string, as long as the string is in the query text. To match multiple strings, use the delimiter '|' to separate multiple strings.

In the fruits table, query the records whose f_name field value contains the string "on", the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';

In the fruits table, query the records whose f_name field value contains the string "on" or "ap", the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';

As mentioned before, the LIKE operator can also match the specified string, but unlike REGEXP, if the string matched by LIKE appears in the middle of the text, it will not be found, and the corresponding line will not be returned. REGEXP matches within the text, if the matched string is in the text
appears, REGEXP will find it, and the corresponding row will be returned. The comparison results are shown below.

In the fruits table, use the LIKE operator to query the records whose f_name field value is "on", the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name like 'on';
Empty set(0.00 sec)

6. Match any one of the specified characters

Square brackets "[]" specify a set of characters, and only match any one of them, which is the text you are looking for.

In the fruits table, look for records containing the letter 'o' or 't' in the f_name field, the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';

In the fruits table, query the records containing 4, 5 or 6 in the s_id field, the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';

7. Match characters other than the specified characters

"[^character set]" matches any character not in the specified set.
In the fruits table, query the f_id field contains the records of characters other than the letter ae and the number 12, the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

8. Use {n,} or {n,m} to specify the number of consecutive occurrences of a string

"String {n,}" means to match the previous character at least n times; "string {n,m}" means to match the previous character string no less than n times and no more than m times. For example, a{2,} means that the letter a appears at least 2 times in a row, and it can be more than 2 times; a{2,4} means that the letter a appears in a row at least 2 times, but not more than 4 times.

In the fruits table, query the records where the letter 'x' appears at least twice in the f_name field value, the SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';

In the fruits table, query the records in which the string "ba" appears at least once and at most three times in the value of the f_name field. The SQL statement is as follows:

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';

Chapter 4_Sorting and Paging

1. Sort the data

1.1 Collation rules

Sorting with an ORDER BY clause
ASC (ascend): ascending order
DESC (descend): descending order
ORDER BY clause at the end of the SELECT statement.

1.2 Single column sorting

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal;

1.3 Multi-column sorting

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

It is possible to sort using columns not in the SELECT list.
When sorting multiple columns, the first column to be sorted must have the same column value before the second column is sorted. If all values in the first column of data are unique, the second column will no longer be sorted.

#If no sorting operation is used, the data returned by default is in the order in which the data was added
SELECT * FROM employees;


#The operation of sorting the queried data using ORDER BY
# ASC Ascending Descending DESC

#Sort by the value of salary in descending order 
#Descending DESC
SELECT department_id,last_name,salary
FROM employees
ORDER BY salary DESC;

#Sort by the value of salary in ascending order 
# ASC ascending 
SELECT department_id,last_name,salary
FROM employees
ORDER BY salary ASC;


#There is no sorting method specified after ORDER BY, and the default is ascending
SELECT department_id,last_name,salary
FROM employees
ORDER BY salary;


#Use aliases for default ascending ranking. Remember that aliases can only be used in ORDER BY but not where
SELECT department_id,last_name,salary  lee
FROM employees
ORDER BY lee;


#Secondary sorting Multi-column sorting Add the content to be sorted after the comma
SELECT department_id,last_name,salary
FROM employees
ORDER BY salary DESC,department_id ASC;

2. Pagination

2.1 Background

Background 1: There are too many records returned by the query, and it is very inconvenient to view. How can we realize paging query?
Background 2: There are 4 pieces of data in the table, what if we only want to display the 2nd and 3rd pieces of data?

2.2 Implementation Rules

paging principle
The so-called paging display is the condition required to display the result set in the database segment by segment.
Use LIMIT to implement pagination in MySQL
Format:

LIMIT [position offset,] Rows

1, and so on); the second parameter "Number of rows" indicates the number of records returned.

--Top 10 records:
SELECT * FROM Table Name LIMIT 0,10;
or
SELECT * FROM Table Name LIMIT 10;
--Records 11 to 20:
SELECT * FROM Table Name LIMIT 10,10;
--Records 21 to 30:
SELECT * FROM Table Name LIMIT 20,10;

MySQL 8.0 can use "LIMIT 3 OFFSET
4", means to get the 3 records starting from the 5th record, and the result returned by "LIMIT4,3;" is the same.

Paging explicit formula: (current page number - 1) * number of entries per page, number of entries per page

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;

Note: The LIMIT clause must be placed at the end of the entire SELECT statement!
Benefits of using LIMIT

Constraining the number of returned results can reduce the network transmission volume of the data table, and can also improve query efficiency. If we know that there is only 1 result returned, we can use LIMIT 1
, telling the SELECT statement to return only one record. The advantage of this is that SELECT does not need to scan the entire table, it only needs to retrieve a qualified record before returning.

#Paging mysql uses limit for paging
SELECT employee_id,last_name 
FROM employees 
LIMIT 0,20;# The data on the first page starts from 0 data, 20 data per page



#Paging mysql uses limit for paging
SELECT employee_id,last_name 
FROM employees 
LIMIT 20,20;# The data on the second page starts from 0 data, 20 data per page


#Paging mysql uses limit for paging
SELECT employee_id,last_name 
FROM employees 
LIMIT 40,20;# The data on the third page starts from 0 data, 20 data per page

#Formula: display pageSize records on each page, and display the data of pageNo at this time;
#The formula is derived: LIMIT(PageNo - 1)*PageSize,PageSize

#to integrate

2.3 Expansion

The keywords used may differ in different DBMS s. The LIMIT keyword is used in MySQL, PostgreSQL, MariaDB and SQLite, and needs to be placed at the end of the SELECT statement.

For SQL Server and Access, you need to use the TOP keyword, such as:

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

In case of DB2, use a keyword like FETCH FIRST 5 ROWS ONLY:

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

If it is Oracle, you need to count the number of rows based on ROWNUM:

SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

It should be noted that this statement first fetches the first 5 data rows, and then sorts them according to the order of hp_max from high to low. However, the result produced by this is not the same as that of the above method. I'll talk about subqueries later, you can use

SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;

The results consistent with the above method were obtained.

Tags: Java MySQL

Posted by ganeshasri on Thu, 09 Feb 2023 20:01:43 +1030

Programs Wiki