The first 03 Chapter _ Basic SELECT sentence

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

1. SQL summary

1.1 SQL Background knowledge

• 1946 year , The world's first computer was born , Now , The Internet developed by this computer has become a Jianghu . In these decades , Countless technologies 、 Industry rises and falls in this Jianghu , Some are in the ascendant , Some have gone up and down for several scenes . But in this vast wave , One technology has never disappeared , even to the extent that “ Hale and hearty ”, That's it SQL.

  • 45 Years ago , That is to say 1974 year ,IBM The researcher published a paper on Database Technology 《SEQUEL： A structured English Query Language 》, Until today, this structured query language has not changed much , Compared with other languages ,SQL The half-life of can be said to be very long 了 .

• Whether it's a front-end Engineer , Back end algorithm engineer , Are bound to deal with data , We all need to know how to extract the data we want quickly and accurately . Not to mention data analysts , Their job is to deal with data , Organize different reports , To guide business decisions .

• SQL（Structured Query Language, Structured query language ） yes Database application language using relational model , Dealing directly with data , from IBM In the last century 70 Developed in the s . Then by the National Bureau of standards （ANSI） Start working on SQL standard , have SQL-86,SQL-89,SQL-92,SQL-99 Equal standard .

  • SQL There are two important criteria , Namely SQL92 and SQL99, They respectively represent 92 Years and 99 enacted SQL standard , What we use today SQL Language still follows these standards .

• Different database manufacturers support SQL sentence , But they all have their own content .

1.2 SQL Language charts

since SQL Joined the TIOBE Programming language leaderboards , Just keep it Top 10.

1.3 SQL classification

SQL The function of language is mainly divided into the following 3 Categories: ：( Different people may be classified differently )

• DDL（Data Definition Languages、 Data definition language ）, These statements define different databases 、 surface 、 View 、 Index and other database objects , It can also be used to create 、 Delete 、 Modify the structure of database and data table .

  • The main statement keywords include CREATE、DROP、ALTER etc. .

• DML（Data Manipulation Language、 Data operation language ）, Used to add 、 Delete 、 Update and query database records , And check data integrity .

  • The main statement keywords include INSERT、DELETE、UPDATE、SELECT etc. .
  • SELECT yes SQL The basis of language , Above all .

• DCL（Data Control Language、 Data control language ）, Used to define the database 、 surface 、 Field 、 User's access rights and security level .

  • The main statement keywords include GRANT( Give relevant permissions to the transaction )、REVOKE、COMMIT、ROLLBACK、SAVEPOINT etc. .

Because query statements are used very frequently , So many people bring out the query statement list ：DQL（ Data query language ）.

And alone will COMMIT（ Submit ）、ROLLBACK（ Roll back ） Take it out and call it TCL （Transaction Control Language, Transaction control language ）.

2. SQL Rules and norms of language

2.1 The basic rule ( Mandatory compliance )

• SQL It can be written on one or more lines . To improve readability , Write each clause separately , Use indent if necessary
• Every order is made with ; or \g or \G end ( If there is only one statement to write, you can't use a semicolon , If multiple statements must be written with semicolons .)
• keyword Can not be abbreviation Also can not branch
• About punctuation
  • All must be guaranteed ()、 Single quotation marks 、 Double quotes end in pairs
  • Half angle input mode in English must be used
  • sql This is how language standards are listed Double quotes , Date and time sheet quotation marks . It's just mysql Yes SQL The standard support is not strict ,oracle Strict support .
  • mysql Loose single and double quotation marks ,oracle If you write the single and double quotation marks incorrectly, you will report an error , recommend MySQL The data of string type and date time type in Single quotation marks , The alias of the column uses Double quotes
  • Single quotation marks can be used for string and date time data （’ '） Express ( namely ： Some versions of database double quotes can also , But single quotes are recommended )
  • Alias of column , Try to use double quotes （" "）, And it is not recommended to omit as
  • When customizing the name , Don't name humps for more than one word , It's connected with underscores .( for example ：tab_name, instead of tabName )

2.2 SQL Case specification （ It is recommended that ）

• MySQL stay Windows The environment is case insensitive
• MySQL stay Linux The environment is case sensitive
  • Database name 、 Table name 、 The table alias 、 Variable names are strictly case sensitive
  • keyword 、 Function name 、 Name ( Or field name )、 Alias of column ( The alias of the field ) It's case insensitive .
• A unified writing standard is recommended ：
  • Database name 、 Table name 、 Table alias 、 Field name 、 Fields, aliases, etc. are all lowercase
  • SQL keyword 、 Function name 、 Bound variables, etc., are all capitalized

2.3 notes Interpretation of the

You can use the annotation structure in the following format

 Single-line comments ：# Note text (MySQL In a particular way )
Single-line comments ：-- Note text (-- Must be followed by a space .)
Multiline comment ：/* Note text */

2.4 Naming rules （( Mandatory compliance )）

• database 、 The table name must not exceed 30 Characters , Variable names are limited to 29 individual
• Must contain only A–Z, a–z, 0–9, _ common 63 Characters
• Database name 、 Table name 、 Do not include spaces in object names such as field names
• The same MySQL In software , The database cannot have the same name ; In the same library , A watch cannot have the same name ; In the same table , The field cannot have the same name
• You must ensure that your field has no and reserved words 、 Database systems or common methods conflict . If you insist on using , Please be there. SQL Use in statement `（ mark of emphasis ） Lead up
• Keep field names and types consistent , When naming fields and specifying data types for them, be sure to ensure consistency . If the data type is an integer in a table , Then don't turn into character in another table

give an example ：

# The following two sentences are the same , Case insensitive 
show databases;
SHOW DATABASES;
# Create a table 
#create table student info(...); # Table name error , Because the table name has spaces 
create table student_info(...);
# among order Use `` Floating horn , because order It has the same name as the predefined identifier such as system keyword or system function name 
CREATE TABLE `order`(
id INT,
lname VARCHAR(20)
);
select id as " Number ", `name` as " full name " from t_stu; # When it comes to aliases ,as All can be omitted 
select id as Number , `name` as full name from t_stu; # If there is no space in the field alias , Then you can omit ""
select id as Ed Number , `name` as surname name from t_stu; # error , If there are spaces in the field alias , Then you can't omit ""

2.5 Data import instruction

Mode one ：Dos Window command line ：source + The full pathname of the file .
Mode two ： Using graphical tools , such as SqlYog: choice " Tools "–“ perform sql Script ”—“ Choose xxx.sql that will do ”

Log in to the command line client mysql, Use source Command import

mysql> source d:\mysqldb.sql

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

3. Basic SELECT sentence

3.0 SELECT…

SELECT 1; # There are no clauses 
SELECT 9/2; # There are no clauses 
# Equivalent to 
select 1 from dual; #dual False watch , Simple operation, there is no need to query the table, so use pseudo table instead .

3.1 SELECT … FROM

• grammar ：

# Fields can be written in multiple fields , Separated by commas 
SELECT Field 1, Field 2,... FROM Table name

• Select all columns ：

# *: All fields in the table ( Column )
# The result of a database query is called a result set 
SELECT * FROM departments;

In general , Unless you need to use all the field data in the table , It is best not to use wildcards ‘*’. Using wildcards can save time in entering query statements , But get unnecessary column data It usually reduces the efficiency of queries and applications used . The advantage of wildcards is , When you don't know the name of the desired column , You can get them through it .

In production environment , It is not recommended that you use SELECT * The query .

• Select a specific column ：

# You can wrap 
SELECT department_id, location_id
FROM departments;

MySQL Medium SQL Statements are case insensitive , therefore SELECT and select The effect is the same , however , Many developers are used to capitalize keywords 、 Data columns and table names are lowercase , Readers should also develop a good programming habit , The code written in this way is easier to read and maintain .

3.2 Alias of column

• Rename a column

• Easy to calculate

• Keep up with the column name , It's fine too Add keywords between column names and aliases AS, Aliases use double quotes , To include spaces or special characters in the alias and be case sensitive .

• AS It can be omitted

• It is recommended that aliases be short , See the name and know the meaning

• give an example

 SELECT last_name AS name, commission_pct comm
FROM employees;

 # Be careful ： If an alias is a word, you don't need to add "", If there are multiple separate words "Annual Salary" You need to add "", Otherwise, I think Annual Alias , Overall error reporting .
# List the recommended double quotes , Don't use single quotes .mysql Grammar is not rigorous in oracle If you use single quotation marks in, you will report an error .
# Single quotation marks are recommended for strings and dates 
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

3.3 Remove duplicate lines

By default , The query will return all rows , Include repeating lines .

# Query which departments are in the employee table id Well ？
# FALSE : No weight loss 
SELECT department_id FROM employees;

stay SELECT Use keywords in statements DISTINCT Remove duplicate lines

# Correct ： De duplication 
SELECT DISTINCT department_id FROM employees;

Aim at ：

# Just not reporting an error , But it has no practical significance . Different means different as a whole ： As long as the colleague 2 If the two columns are not completely consistent, it will be displayed .
SELECT DISTINCT department_id,salary FROM employees;

Here are two points to note ：

1. DISTINCT Need to precede all column names , If written SELECT salary, DISTINCT department_id FROM employees Will report a mistake .
2. DISTINCT In fact, it is to de duplicate the combination of all the following column names , You can see that the final result is 74 strip , Because of this 74 Departments id Different , There are salary This property value . If you want to see what different departments there are （department_id）, You just need to write DISTINCT department_id that will do , There is no need to add other column names .

3.4 Null values participate in the operation

• All operators or column values encountered null value , The result of the operation is null

# 1. Null value ：null
# 2. null Not equal to 0,'','null'
SELECT * FROM employees;
#3. Null values participate in the operation ： The result must also be empty .
SELECT employee_id,salary " Monthly wages ",salary * (1 + commission_pct) * 12 " Annual wage ",commission_pct
FROM employees;
# Solutions to practical problems ： introduce IFNULL
SELECT employee_id,salary " Monthly wages ",salary * (1 + IFNULL(commission_pct,0)) * 12 " Annual wage ",commission_pct

Here you must pay attention to , stay MySQL Inside , A null value is not equal to an empty string . The length of an empty string is 0, The length of a null value is null . and , stay MySQL Inside , Null values take up space .

3.5 mark of emphasis

• FALSE

# When querying, it indicates that the keyword is the same as that of the database , Just a couple `` After marking , You can query successfully .
mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1

• Correct

mysql> SELECT * FROM `ORDER`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)

• Conclusion

We need to ensure that the fields in the table 、 Table names, etc. have no and reserved words 、 Database systems or common methods conflict . If it's really the same , Please be there. SQL A pair of... Is used in the statement ``（ mark of emphasis ） Lead up .

3.6 Query constant

SELECT The query can also query constants . Right , Is in the SELECT Add a fixed constant column to the query result . The value of this column is specified by us , Not dynamically extracted from the data table .

You might ask why we have to query constants ？

SQL Medium SELECT Grammar does provide this function , Generally speaking, we only query data from one table , There is usually no need to add a fixed constant column , But if we want to integrate different data sources , Use the constant sequence as the marker of this table , You need to query constants .

for instance , We want to be right employees Query the employee name in the data table , At the same time, add a column of fields corporation, The fixed value of this field is “ Silicon Valley ”, It can be written like this ：

# It means if ` Silicon Valley ' as long as 1 That's ok ,123,employee_id There are many lines in other fields 107 That's ok , Then the result of the query is 107 Rows, and each row adds ` Silicon Valley '
SELECT ' Silicon Valley ',123,employee_id,last_name
FROM employees;

4. Display table structure

Use DESCRIBE or DESC command , Express Table structure .

# You can view data types , Primary key , Is it allowed to be Null, Constraints etc. .
DESCRIBE employees;
or
DESC employees;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

among , The meanings of each field are explained as follows ：

• Field： Field name .
• Type： Represents the field type , here barcode、goodsname It's textual ,price It's of type integer .
• Null： Indicates whether the column can store NULL value .
• Key： Indicates whether the column is indexed .PRI Indicates that the column is part of the table primary key ;UNI Indicates that the column is UNIQUE Part of index ;MUL Indicates that a given value is allowed to appear more than once in a column .
• Default： Indicates whether the column has a default value , If there is , So what's the value .
• Extra： Represents additional information about a given column that can be obtained , for example AUTO_INCREMENT etc. .

5. Filtering data

• background ：

• grammar ：

 SELECT Field 1, Field 2
FROM Table name
WHERE Filter conditions

• Use WHERE Clause , Filter out rows that do not meet the criteria

• WHERE Clause in the wake of FROM Clause

• give an example

# Filter conditions , The statement in FROM Behind the structure 
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

The first 04 Chapter _ Operator ( Listen again )

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

1. 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 （/） And modulus （%） operation .

1． Addition and subtraction operators

#1. Arithmetic operator ： + - * / div % mod
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)
# stay SQL in ,+ No connection , It means addition . here , Will convert the string to a numeric value （ Implicit conversion ）
SELECT 100 + '1' # stay Java In language , The result is ：1001. 
FROM DUAL;
SELECT 100 + 'a' # At this time will be 'a' regard as 0 Handle 
FROM DUAL;
SELECT 100 + NULL # null Value participates in the operation , The result is null
FROM DUAL;

The following conclusions can be drawn from the calculation results ：

• An integer type value adds and subtracts integers , The result is still an integer ;
• An integer type value adds and subtracts floating-point numbers , The result is a floating point number ;
• Addition and subtraction have the same priority , The result of adding before subtracting is the same as that of subtracting before adding ;
• 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 + It only means that the values are added . 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 ）

2． Multiplication and division operators

# If the denominator is 0, The result is 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;
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
| 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)

# Calculate the annual basic salary of employees
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;

The following conclusions can be drawn from the calculation results ：

• A number times an integer 1 And divided by an integer 1 After that, you still get the original number ;
• 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 ;
• A number divided by an integer , Whether or not we can eliminate , The result is a floating point number ;
• 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 ;
• Multiplication and division have the same priority , Perform the multiply then divide operation and the divide before multiply operation , The results are the same .
• In mathematics ,0 Cannot be used as a divisor , stay MySQL in , A number divided by 0 by NULL.

3． modulus （ Seeking remainder ） Operator
take t22 Fields in the table i Yes 3 and 5 Perform modulus calculation （ Seeking remainder ） operation .

# Modulus operation ： % mod
# You can see that the positive and negative of the query result have nothing to do with the divisor , Just the same as the divisor .
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL;

# select employee_id It's an even number of employees 
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;

You can see ,100 Yes 3 The result after finding the module is 3, Yes 5 The result after finding the module is 0.

2. 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.

Comparison operators are often used as SELECT Use the conditions of the query statement , Return qualified result records .

1． The equal sign operator

• 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.

• When using the equal sign operator , Follow these rules ：

  • 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 .
  • 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 .
  • 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 .
  • If the values on both sides of the equal sign 、 One of the strings or expressions is NULL, The comparison result is NULL.

• contrast ：SQL The assignment symbol in... Is used :=

SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' # The string has an implicit conversion . If the conversion of values is unsuccessful , As 0
FROM DUAL;
SELECT 'a' = 'a','ab' = 'ab','a' = 'b' # If there are strings on both sides , According to ANSI Compare with the rules of comparison .
FROM DUAL;
SELECT 1 = NULL,NULL = NULL # As long as there is null Participate in judgment , The results for null
FROM DUAL;
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct = NULL; # Execute at this time , There will be no result 

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)

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

2． Security equals operator
Security equals operator （<=>） And equals operator （=） It's similar , The only difference yes ‘<=>’ 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.

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)

# Inquire about commission_pct be 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;
# If you put 0.40 Change to NULL Well ？

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 .

3． Not equal to the 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.
SQL Examples of statements are as follows ：

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

Besides , There are also non symbolic operators ：

4. Air transport operator
Air transport operator （IS NULL perhaps ISNULL） Determines whether a value is NULL, If NULL Then return to 1, Otherwise return to 0.
SQL Examples of 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)

# Inquire about commission_pct be equal to NULL. Compare the following four ways of writing 
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;

5. Non air transport operators
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.
SQL Examples of 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)

# 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);

6. 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 .

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)

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.

7. 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.

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 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.

8. 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.

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;

9. IN Operator
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. If the given value is NULL, perhaps IN List exists NULL, 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 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)

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

10. NOT IN Operator
NOT IN Operator is used to determine whether a given value is not IN A value in the list , If not IN A value in the list , Then return to 1, Otherwise return to 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)

11. LIKE Operator
LIKE Operators are mainly used to match strings , Usually used for fuzzy matching , If the condition is met, return 1, Otherwise return to 0. If the given value or matching condition is NULL, Then the returned result is NULL.

LIKE Operators usually use the following wildcards ：

“%”： matching 0 Characters or more .
“_”： Only one character can be matched .

SQL Examples of 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

• Avoiding special symbols ： Use escape characters . for example ： take [%] To [ %]、[] To [ ], And then add [ESCAPE‘$’] that will do .

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

• If you use \ To signify an escape , Omit ESCAPE. If not \, Then add ESCAPE.

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

12. REGEXP Operator

REGEXP Operators are used to match strings , The grammar format is ：expr REGEXP Matching condition . If expr Meet the matching conditions , return 1; If not satisfied , Then return to 0. if expr Or any one of the matching conditions is NULL, The result is NULL.

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 ‘-’.“[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 .

SQL Examples of 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)

3. Logical operators

Logical operators are mainly used to judge whether an expression is true or false , stay MySQL in , The return result of the logical operator is 1、0 perhaps NULL.

MySQL Chinese support 4 The logical operators are as follows ：

1． Logical nonoperator
Logic is not （NOT or !） Operator means when the given value is 0 When to return to 1; When the given value is non 0 Value returns 0; When the given value is NULL when , return 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)

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

2． Logic and operators
Logic and （AND or &&） Operator is when all values given are non 0 value , And not for NULL when , return 1; When a given value or values are 0 When you return to 0; Otherwise return to 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． Logical or operator
Logic or （OR or ||） The operator is when the given value is not NULL, And any value is non 0 When the value of , Then return to 1, Otherwise return to 0; When a value is NULL, And the other value is non 0 When the value of , return 1, Otherwise return to NULL; When both values are NULL when , return 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 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;

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

Be careful ：

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 .

4． Logical XOR operator
Logical XOR （XOR） Operator is when any one of the given values is NULL when , Then return to NULL; If two are not NULL The values are all 0 Or it doesn't mean 0 when , Then return to 0; If a value is 0, The other value is not 0 when , Then return to 1.

mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;
+----------+---------+---------+------------+---------------+---------------+
| 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;

4. An operator

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 ：

1． Bitwise and operator
Bitwise AND （&） Operator performs logical and operation bit by bit on the binary number corresponding to the given value . When the value of the binary bit corresponding to the given value is 1 when , Then the bit returns 1, Otherwise return to 0.

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

1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 & 10 As the result of the 0000, The corresponding decimal number is 0.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 & 30 As the result of the 10100, The corresponding decimal number is 20.

2. bitwise or operator
Press bit or （|） Operator performs logical or operation bit by bit on the binary number corresponding to a given value . When one or two of the binary values corresponding to a given value are 1 when , Then the bit returns 1, Otherwise return to 0.

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

1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 | 10 As the result of the 1011, The corresponding decimal number is 11.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 | 30 As the result of the 11110, The corresponding decimal number is 30.

3. bitwise exclusive or operator
Bitwise XOR （^） Operator performs logical XOR operation bit by bit on the binary number corresponding to the given value . When the value of the binary bit corresponding to the given value is different , Then the bit returns 1, Otherwise return to 0.

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

1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 ^ 10 As the result of the 1011, The corresponding decimal number is 11.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 ^ 30 As the result of the 01010, The corresponding decimal number is 10.

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)

4. Bitwise negation operator
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;
+---------+
| 10 & ~1 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)

Because bitwise inversion （~） The operator takes precedence over bitwise AND （&） Operator precedence , therefore 10 & ~1, First , Pairs of numbers 1 Reverse by bit , The result is except that the lowest is 0, The others are 1, Then with 10 Do bit and operation , The result is 10.

5. Bitwise shift right operator
Right shift to position （>>） Operator shifts all bits of the binary number of a given value to the right by the specified number of bits . Shift the specified number of bits to the right , The lower value on the right is removed and discarded , The left high position is empty with 0 A filling .

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

1 The binary number of is 0000 0001, Move right 2 Position as 0000 0000, The corresponding decimal number is 0.4 The binary number of is 0000 0100, Move right 2 Position as 0000 0001, The corresponding decimal number is 1.

6. Bitwise shift left operator
Move left according to position （<<） Operator shifts all bits of the binary number of a given value to the left by the specified number of bits . Move the specified number of bits left , The higher left value is removed and discarded , On the right side, the low position is left by 0 A filling .

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

1 The binary number of is 0000 0001, Move two to the left to 0000 0100, The corresponding decimal number is 4.4 The binary number of is 0000 0100, Move two to the left to 0001 0000, The corresponding decimal number is 16.

5. Operator precedence

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 .

expand ： Using regular expression queries

Regular expressions are often used to retrieve or replace text content that conforms to a pattern , Matches a special string in the text that meets the requirements according to the specified matching pattern . for example , Extract phone numbers from a text file , Find repeated words in an article or replace some sensitive words entered by the user , Regular expressions can be used in these places . Regular expressions are powerful and flexible , It can be applied to very complex queries .

MySQL Use in REGEXP Keyword specifies the character matching pattern of the regular expression . The following table lists them REGEXP A list of common character matches in the operator .

1. Query records starting with a specific character or string
character ‘^’ Matches text that begins with a specific character or string .

stay fruits In the table , Inquire about f_name Fields are represented by letters ‘b’ The first record ,SQL The statement is as follows ：

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

2. Query records ending with a specific character or string
character ‘$’ Matches text that ends with a specific character or string .

stay fruits In the table , Inquire about f_name Fields are represented by letters ‘y’ The record at the end ,SQL The statement is as follows ：

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

3. Use symbols "." To replace any character in the string
character ‘.’ Matches any character .
stay fruits In the table , Inquire about f_name The field value contains the letters ‘a’ And ‘g’ And there is only one letter between two letters ,SQL The statement is as follows ：

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

4. Use "*“ and ”+" To match multiple characters
asterisk ‘*’ Match the preceding character any number of times , Include 0 Time . plus ‘+’ Match the preceding character at least once .

stay fruits In the table , Inquire about f_name Field values are in letters ‘b’ And ‘b’ The letters appear after them ‘a’ The record of ,SQL The statement is as follows ：

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

stay fruits In the table , Inquire about f_name Field values are in letters ‘b’ And ‘b’ The letters appear after them ‘a’ At least one record ,SQL The statement is as follows ：

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

5. Matches the specified string
A regular expression can match a specified string , As long as this string is in the query text , To match multiple strings , Use separator between multiple strings ‘|’ separate .

stay fruits In the table , Inquire about f_name The field value contains a string “on” The record of ,SQL The statement is as follows ：

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

stay fruits In the table , Inquire about f_name The field value contains a string “on” perhaps “ap” The record of ,SQL The statement is as follows ：

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

Previously introduced ,LIKE Operator can also match the specified string , But with REGEXP Different ,LIKE If the matching string appears in the middle of the text , You can't find it , The corresponding line will not return .REGEXP Match within text , If the matched string appears in the text ,REGEXP It will be found , The corresponding line will also be returned . The comparison results are as follows .

stay fruits In the table , Use LIKE Operator query f_name The field values for “on” The record of ,SQL The statement is as follows ：

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

6. Matches any one of the specified characters
square brackets “[]” Specify a character set , Match only any of these characters , This is the text you are looking for .

stay fruits In the table , lookup f_name The field contains letters ‘o’ perhaps ‘t’ The record of ,SQL The statement is as follows ：

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

stay fruits In the table , Inquire about s_id The field contains 4、5 perhaps 6 The record of ,SQL The statement is as follows ：

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

7. Matches characters other than the specified characters
“[^ Character set ]” Matches any character that is not in the specified set .

stay fruits In the table , Inquire about f_id The field contains letters a_{e And number 1}2 Records of characters other than ,SQL The statement is as follows ：

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

8. Use {n,} perhaps {n,m} To specify the number of consecutive occurrences of the string
“ character string {n,}” Means at least match n The next preceding character ;“ character string {n,m}” Indicates that the matching preceding string is not less than n Time , Not more than m Time . for example ,a{2,} For letters a Continuous occurrence of at least 2 Time , It can also be greater than 2 Time ;a{2,4} For letters a Continuous occurrence of the least 2 Time , Not more than 4 Time .

stay fruits In the table , Inquire about f_name A letter appears in the field value ‘x’ At least 2 The second record ,SQL The statement is as follows ：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';

stay fruits In the table , Inquire about f_name The field value appears as a string “ba” least 1 Time 、 most 3 The second record ,SQL The statement is as follows ：

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

The first 05 Chapter _ Sorting and paging

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

1. Sorting data

1.1 Sort rule

• Use ORDER BY Clause ordering
  • ASC（ascend）: Ascending ( Default Omission )
  • DESC（descend）: Descending
• ORDER BY Clause in SELECT End of statement .

1.2 Single column sort

#1. Sort 
# If no sort operation is used , By default, the data returned by the query is displayed in the order of adding data .
# Yes ORDER BY The key word , Don't write in ascending order The descending keyword defaults to ASC Ascending , If even orderby Keywords are not written , By default, the data returned by the query is displayed in the order of adding data .
SELECT * FROM employees;
# 1.1 Basic use 
# Use ORDER BY Sort the queried data .
# Ascending ：ASC (ascend)
# Descending ：DESC (descend)
# practice ： according to salary Display employee information from high to low 
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
# practice ： according to salary Display employee information from low to high 
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; # If in ORDER BY If there is no explicit naming sort after , Sort in ascending order by default .

1.3 Multi column sorting

#2. We can use column aliases , Sort 
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
# Column aliases can only be used in ORDER BY Use in , Can't be in WHERE Use in .
# The following operation reports an error ！
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;
#3. Emphasis format ：WHERE Need to declare in FROM after ,ORDER BY Before .
# The sorted field is not necessarily the field of the query , No mistake. .
/* Why is it where Column aliases cannot be used in ？？？ because ：sql The query is not written according to sql From front to back : First step ： Go ahead from see It's the watch , Looking at the screening criteria where. The second step ： Let's see what you want to find out first select, At this point, you may define an alias . Third parts ： At this time, there is already an alias, and the column alias can only be used after sorting , Before using where There is no column alias yet, so you can't use . Similarly, the table alias is in where There was... Before , So you can use .*/
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;
#4. Secondary sorting 
# practice ： Show employee information , according to department_id Descending order of , stay department_id Based on the descending order of salary In ascending order 
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

• Can be used not in SELECT Sort the columns in the list .
• When sorting multiple columns , The first column sorted first must have the same column value , Will sort the second column . If all values in the first column of data are unique , The second column will no longer be sorted .

2. Pagination

2.1 background

background 1： The query returned too many records , It's inconvenient to check , How to implement paging query ？

background 2： There are 4 Data , We just want to show 2、3 What about this data ？

2.2 Implementation rules

• Pagination principle

  Pagination display , The result set in the database , Show the required conditions paragraph by paragraph .

• MySQL Use in LIMIT Implement paging

• Format ：

  LIMIT [ Position offset ,] Row number
  

  first “ Position offset ” Parameters indicate MySQL Which line to start with , Is an optional parameter , If you don't specify “ Position offset ”, It will start with the first record in the table （ The position offset of the first record is 0, The position offset of the second record is 1, And so on ）; The second parameter “ Row number ” Indicates the number of records returned .

• give an example

#2. Pagination 
#2.1 mysql Use limit Realize pagination display of data 
# demand 1： Each page shows 20 Bar record , The... Is displayed 1 page 
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
# demand 2： Each page shows 20 Bar record , The... Is displayed 2 page 
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;
# demand 3： Each page shows 20 Bar record , The... Is displayed 3 page 
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;
# demand ： Each page shows pageSize Bar record , The... Is displayed pageNo page ：
# The formula ：LIMIT (pageNo-1) * pageSize,pageSize;
#2.2 WHERE ... ORDER BY ...LIMIT The order of declaration is as follows ：
# LIMIT The format of ： Strictly speaking ：LIMIT Position offset , Number of entries 
# structure "LIMIT 0, Number of entries " Equivalent to "LIMIT Number of entries "
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;
# practice ： There are 107 Data , We just want to show 32、33 What about this data ？
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
#2.3 MySQL8.0 New characteristics ：LIMIT ... OFFSET ... 
# LIMIT Followed by the number displayed on each page ,OFFSET Write the position offset later , It's equivalent to reversing .
# practice ： There are 107 Data , We just want to show 32、33 What about this data ？
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;
# practice ： Query the information of the highest paid employee in the employee table 
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

MySQL 8.0 Can be used in “LIMIT 3 OFFSET 4”, It means to get from 5 A record starts after 3 Bar record , and “LIMIT 4,3;” The results returned are the same .

• Paging explicit formula **：（ The current number of pages -1） Number of entries per page , Number of entries per page *

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

• Be careful ：LIMIT Clause must be placed throughout SELECT At the end of the sentence ！
• Use LIMIT The benefits of

The number of results returned by the constraint can Reduce the network transmission of data tables , It's fine too Improve query efficiency . If we know that the return result is only 1 strip , You can use LIMIT 1, tell SELECT Statement only needs to return a record . The good thing is SELECT There is no need to scan the complete table , You only need to retrieve a qualified record to return .

2.3 expand

In different DBMS The keywords used in may be different . stay MySQL、PostgreSQL、MariaDB and SQLite Use in LIMIT keyword , And it needs to be put in SELECT At the end of the statement .

• If it is SQL Server and Access, Need to use TOP keyword , such as ：

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

• If it is DB2, Use FETCH FIRST 5 ROWS ONLY Such keywords ：

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

• If it is Oracle, You need to be based on ROWNUM To count the number of lines ：
• ROWNUM Equivalent to a hidden column , Each column has .

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

It should be noted that , This statement is taken out before 5 Data rows , And then follow hp_max Sort from high to low . But the result is different from that of the above method . I'll talk about sub query later , You can use

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

The results are consistent with the above methods .

The first 06 Chapter _ Multi-table query

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

Multi-table query , Also known as associative queries , It means that two or more tables complete the query operation together .

Prerequisite ： There is a relationship between these tables queried together （ one-on-one 、 One to many ）, There must be associated fields between them , This associated field may establish a foreign key , Or you may not have established a foreign key . such as ： Employee table and department table , These two tables depend on “ Department number ” Association .

1. Multi table connection caused by a case

1.1 Case description

Why multi table query ？？？

# Check the name of the employee 'Abel' Which city do people work in ？
SELECT *
FROM employees
WHERE last_name = 'Abel';# In the employee form, name according to known conditions , You can find out the of that department id by 80
SELECT *
FROM departments
WHERE department_id = 80;# In the Department table, according to the Department where this person belongs id, Find out the location of this department id
SELECT *
FROM locations
WHERE location_id = 2500;# In the location table, according to the location id, Query the city .
explain ： This is how to query 3 It can also be found out once , But each query requires a network connection, and the interaction takes time , Write 3 This query must be
Slower than once , So you don't have to query like this .

Why is it divided into 3 Zhang Biao , Can you make a table with me ？？？( A table is composed of several columns )

 Sure , But the advantages and disadvantages .
Scene one ： Suppose that the Department table IT department , Corresponding in the employee table 300 Data . The Department table writes the details of the Department , Now the Department table only needs to write 1 Data , The employee form needs to write
300 Data Only one department needs to be written after each data id Just number . If you write in one piece Each employee in the employee table should write a department simply from the original id Become a departmental
Details , Too many words make it redundant .
Scene two ： Suppose that many employees now have no department , Now you only need to display in the Department table as null that will do , If they are written together, the employees of the Department should write detailed information ( Location , department , department id Wait a lot of fields ) Employees without departments cannot write department information , As a result, many fields are empty , Obviously not . This is the 2 A watch , More tables are more serious .
More fields, more questions ：io The fields loaded at one time are limited , The more fields, the more loading times , Less efficient .

Getting data from multiple tables ：3 The tables are ： The employee table Departmental table Location table

# Case study ： Query the employee's name and department name 
# Wrong way 1
SELECT last_name, department_name
FROM employees, departments; # Query out 2889 Bar record 
perhaps ：
# Wrong way 2
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;# Query out 2889 Bar record 
# analysis ： There is a Cartesian product error Reason for the error ： The connection condition of multiple tables is missing 

Query results ：

+-----------+----------------------+
| last_name | department_name |
+-----------+----------------------+
| King | Administration |
| King | Marketing |
| King | Purchasing |
| King | Human Resources |
| King | Shipping |
| King | IT |
| King | Public Relations |
| King | Sales |
| King | Executive |
| King | Finance |
| King | Accounting |
| King | Treasury |
...
| Gietz | IT Support |
| Gietz | NOC |
| Gietz | IT Helpdesk |
| Gietz | Government Sales |
| Gietz | Retail Sales |
| Gietz | Recruiting |
| Gietz | Payroll |
+-----------+----------------------+
2889 rows in set (0.01 sec)

Analyze the error ：

SELECT COUNT(employee_id) FROM employees;
# Output 107 That's ok The employee list has 107 That's ok 
SELECT COUNT(department_id)FROM departments;
# Output 27 That's ok The Department table has 27 That's ok 
SELECT 107*27 FROM dual; #107*27=2889, Explain that all employees are matched under each department , This is called the present Cartesian product error .

We call the problem in the above multi table query as ： The error of Cartesian product .

1.2 The cartesian product （ Or cross connect ） The understanding of the

Cartesian product is a mathematical operation . Suppose I have two sets X and Y, that X and Y The Cartesian product of is X and Y All possible combinations of , That is, the first object comes from X, The second object comes from Y All the possibilities of . The number of combinations is the product of the number of elements in the two sets .

SQL92 in , Cartesian product is also called Cross connect , English is CROSS JOIN. stay SQL99 It is also used in CROSS JOIN Means cross connect . Its function is to connect any table , Even if the two tables are not related . stay MySQL Cartesian product will appear in the following cases ：

# Query employee name and department name 
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

1.3 Case analysis and problem solving

• The error of Cartesian product will occur under the following conditions ：

  • Omit join conditions for multiple tables （ Or related conditions ）
  • Connection condition （ Or related conditions ） Invalid
  • All rows in all tables are interconnected

• To avoid Cartesian product , Sure stay WHERE Add a valid connection condition .

• After adding connection conditions , The query syntax ：

 SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; # Connection condition 

• stay WHERE Clause .

• Write it correctly ：

 # Case study ： Query the employee's name and its corresponding department name 107 An employee should find out 107 Data and corresponding department 
#1. The employee table has 107 Data , Query out 106 Data , Because an employee has no department for null.
SELECT last_name, department_name
FROM employees, departments
# Join condition of two tables 
WHERE employees.department_id = departments.department_id;
#2. If there are fields in multiple tables in the query statement , You must specify the table where this field is located .
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
# Suggest ： from sql Optimization angle , It is recommended to query multiple tables , Each field is preceded by the table in which it is located .
reason ： If there is no indication First, find out whether there is this field in each table for comparison . If specified, no other table will be found .
#3. You can alias a table , stay SELECT and WHERE Aliases for tables used in .
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
# If you alias a table , Once in a SELECT or WHERE If table names are used in , You must use the alias of the table , Instead of using the original name of the table . Column names do not have this usage .
# The following operation is wrong ：
# reason ： still sql The question of execution order , Go ahead from And alias the table , To walk again where At this time, it has been overwritten with aliases. Only table aliases can be used , To walk again select.
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;
#4. Conclusion ： If there is n Multiple tables realize the query of multiple tables , At least n-1 Connection conditions 
# practice ： Check the employee's employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

• When there are the same columns in the table , Prefix the column name with the table name .

2. Multi table query classification explanation

classification 1： Equivalent connection vs Non equivalent connection

Equivalent connection

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

expand 1： Multiple connection conditions and AND The operator

expand 2： Distinguish between duplicate column names

• When there are the same columns in multiple tables , The column name must be prefixed with the table name .
• Columns with the same column name in different tables can be used Table name Distinguish .

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

expand 3： The table alias

• Using aliases can simplify queries .

• Using table name prefix before column name can improve query efficiency .

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

It should be noted that , If we use the alias of the table , In the query field 、 Only aliases can be used in the filter conditions , Cannot use the original table name , Otherwise you will report an error .

Ali Development Specification ：

【 mandatory 】 For queries and changes to table records in the database , As long as multiple tables are involved , You need to add the alias of the table before the column name （ or Table name ） To limit .

explain ： Query records for multiple tables 、 Update record 、 When deleting records , If there is no alias of the qualified table for the operation column （ Or watch name ）, And when the operation column exists in multiple tables , I'm going to throw an exception .

Example ：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

Counter example ： In a business , Because the multi table associated query statement does not add the alias of the table （ Or watch name ） The limitation of , Two years after normal operation , Recently Add a field with the same name to a table , After making database changes in the pre release environment , The online query statement appears 1052 abnormal ：Column ‘name’ in field list is ambiguous.

expand 4： Join multiple tables

summary ： Connect n Tables , Need at least n-1 Connection conditions . such as , Join three tables , At least two join conditions are required .

practice ： Query the employee's last_name,department_name, city

Non equivalent connection

# The employee table Wage register this 2 There is no association relationship between tables 
SELECT *
FROM job_grades;
# Find out the salary grade of the employee table ？ The results displayed are from 2 A watch , this 2 The tables are not related to each other. You can use non equivalent queries .
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;

classification 2： Self join vs Non self connecting

• When table1 and table2 It's essentially the same table , Just use the alias to form two virtual tables to represent different meanings . Then the two tables are connected internally , External connection and other queries .

subject ： Inquire about employees surface , return “Xxx works for Xxx”

# Query employees and their superiors , The data displayed in the query comes from the same table , Aliases need to be distinguished 2 A watch , When using equivalent query criteria .
SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

practice ： Query out last_name by ‘Chen’ Of our employees manager Information about .

classification 3： Internal connection vs External connection

In addition to querying the records that meet the conditions , External join can also query records that one party does not meet the conditions .

• 1. Internal connection : Merging rows of more than two tables with the same column , There are no rows in the result set that do not match one table with another

• 2. External connection : In addition to returning rows that meet the connection conditions during the connection of two tables Also return to the left （ Or right ） Rows in the table that do not meet the criteria , This connection is called left （ Or right ） External connection . When there are no matching rows , The corresponding column in the result table is empty (NULL).

• 3. Classification of external connections ： The left outer join 、 Right connection 、 Full outer join

• 3.1 The left outer join ： In addition to the rows that meet the connection conditions, the two tables also return the rows that do not meet the conditions in the left table , This connection is called the left outer connection .

• 3.2 In addition to the rows that meet the connection conditions, the two tables also return the rows that do not meet the conditions in the right table , This connection is called the right outer connection .

• 4. If it's a left outer connection , The table on the left of the join condition is also called Main table , The table on the right is called From the table .

  If it's a right outer connection , The table on the right in the join condition is also called Main table , The table on the left is called From the table .

SQL92： Use (+) Create an external connection

• stay SQL92 Used in （+） Represents the location of the slave table . That is, in the left or right outer connection ,(+) Indicates which is from the table .

• Oracle Yes SQL92 Better support , and MySQL Do not support SQL92 External connection of .

• Be careful ： The multi table query connections mentioned above are SQL92 Standard internal connection .

 #SQL92 Syntax implementation external connection ： Use + ----------MySQL I won't support it SQL92 The writing of Chinese and foreign connections in grammar ！oracle Support 
# The left outer join 
# That is to say + The representative from the table 
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);# Main table From the table ： The description is the left outer connection 
# Right connection 
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;# From the table Main table ： Description is the right outer connection 

• And in SQL92 in , Only the left outer connection and the right outer connection , Not full （ Or all ） External connection .

3. SQL99 Syntax to implement multi table query

3.1 Basic grammar

• Use JOIN…ON Clause creates the syntax structure of the join ：

 SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 and table2 The connection condition of
JOIN table3 ON table2 and table3 The connection condition of

Its nesting logic is similar to what we use FOR loop ：

 for t1 in table1:
for t2 in table2:
if condition1:
for t3 in table3:
if condition2:
output t1 + t2 + t3

SQL99 This nested structure is very refreshing 、 More hierarchical 、 More readable , Even if more tables are connected, they are clearly visible . If you take SQL92, Readability will be greatly reduced .

• Syntax description ：

  • have access to ON Clause specifies additional join conditions .
  • This connection condition is separate from other conditions .
  • ON Clause makes the statement more readable .
  • keyword JOIN、INNER JOIN、CROSS JOIN It means the same thing , Both represent internal connections

3.2 Internal connection (INNER JOIN) The implementation of the

• grammar ：

/* The keywords of the previous conditional statements are where, And in the sql99 It is customary to write conditional statements and keywords in grammar : Association conditions use on( Association statement ： In general, and join In combination with ) Other common conditions use where, Of course where It's fine too , But separate writing is more readable .*/
SELECT Field list
FROM A surface INNER JOIN B surface
ON The associated condition
WHERE And so on ;

subject 1：

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

subject 2：

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

3.3 External connection (OUTER JOIN) The implementation of the

3.3.1 The left outer join (LEFT OUTER JOIN)

• grammar ：

# The query result is A
SELECT Field list
FROM A surface LEFT JOIN B surface
ON The associated condition
WHERE And so on ;

• give an example ：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

3.3.2 Right connection (RIGHT OUTER JOIN)

• grammar ：

# The query result is B
SELECT Field list
FROM A surface RIGHT JOIN B surface
ON The associated condition
WHERE And so on ;

• give an example ：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

It should be noted that ,LEFT JOIN and RIGHT JOIN Is only found in SQL99 And later standards , stay SQL92 Does not exist in the , Only use (+) Express .

3.3.3 Full outer join (FULL OUTER JOIN)

• The result of full external connection = Data matching the left and right tables + There is no matching data in the left table + There is no matching data in the right table .
• SQL99 It supports full external connection . Use FULL JOIN or FULL OUTER JOIN To achieve .
• It should be noted that ,MySQL I won't support it FULL JOIN, But you can use LEFT JOIN UNION RIGHT join Instead of .

# Full outer join ：mysql I won't support it FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

4. UNION Use

Merge query results
utilize UNION keyword , You can give more than one SELECT sentence , And combine their results into a single result set . When merging , The number of columns and data types of the two tables must be the same , And correspond to each other . each SELECT Use... Between statements UNION or UNION ALL Keyword separation .

Grammar format ：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION The operator

UNION Operator returns the union of the result sets of two queries , Remove duplicate records .

advantage ： It's going to be weightless . shortcoming ： Low efficiency .

UNION ALL The operator

UNION ALL Operator returns the union of the result sets of two queries . For duplicate parts of two result sets , No weight removal .

advantage ： Efficient . shortcoming ： No weight removal .

Because use UNION ALL The query efficiency of is higher , However, the disadvantage of query is that it does not need to redo , Then we want to use UNION ALL Query improves efficiency , And can remove weight

How to do it ？？？

You can use the left outer connection to remove the weight + The right external connection does not remove the duplication, so it realizes the whole external connection , Or the left outer connection is not de duplicated + The right outer connection is de duplicated . See below for details 5.7 All external connection query in .

Be careful ： perform UNION ALL Statement requires more resources than UNION Few statements . If you know clearly that there is no duplicate data in the result data after data consolidation , Or you don't need to remove duplicate data , Try to use UNION ALL sentence , To improve the efficiency of data query .

give an example ： Check department number >90 Or the mailbox contains a Employee information

# The way 1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

# The way 2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

give an example ： Query the information of men in Chinese users and middle-aged men in American users

SELECT id,cname FROM t_chinamale WHERE csex=' male '
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

5. 7 Kind of SQL JOINS The implementation of the

5.7.1 Code implementation

# Chinese ： Internal connection A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# Top left ： The left outer join 
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# Top right ： Right connection 
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# Middle left ：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

# Middle right ：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

# Bottom left ： Full outer join 
# The way 1： Top left UNION ALL Middle right 
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# The way 2： Middle left UNION ALL Top right 
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# Bottom right 
# Middle left + Middle right A ∪B- A∩B perhaps (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

5.7.2 Syntax format summary

• Middle left

# Realization A - A∩B
select Field list
from A surface left join B surface
on The associated condition
where Associate fields from the table is null and And so on ;

• Middle right

# Realization B - A∩B
select Field list
from A surface right join B surface
on The associated condition
where Associate fields from the table is null and And so on ;

• Bottom left

# The query result is A∪B
# Use the left outside A,union Right outside B
select Field list
from A surface left join B surface
on The associated condition
where And so on
union
select Field list
from A surface right join B surface
on The associated condition
where And so on ;

• Bottom right

# Realization A∪B - A∩B or (A - A∩B) ∪ （B - A∩B）
# Use the left outside (A - A∩B) union Right outside （B - A∩B）
select Field list
from A surface left join B surface
on The associated condition
where Associate fields from the table is null and And so on
union
select Field list
from A surface right join B surface
on The associated condition
where Associate fields from the table is null and And so on

5.8 Multi table query mind map

6. SQL99 New features of grammar

6.1 Natural join

SQL99 stay SQL92 Provides some special syntax based on , such as NATURAL JOIN Used to represent natural connections . We can understand natural connection as SQL92 The equivalent connection in . It will help you automatically query the two connection tables All the same fields , Then proceed Equivalent connection .

stay SQL92 In the standard ：

#2 Connection conditions , More flexible , Write just a few... Using a few connection conditions .( Recommended )
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

stay SQL99 You can write ：

# It will automatically help you query all connection conditions . shortcoming ： inflexible , Force all connection conditions , But if you want to use a connection condition, there is no way to solve .
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2 USING Connect

When we connect ,SQL99 Also supports the use of USING Specify... In the data table Fields with the same name Make an equivalent connection . But only with JOIN Use it together . such as ：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

You can see the connection with nature NATURAL JOIN The difference is ,USING Specifies the specific same field name , You need to USING The brackets of () Fill in the field with the same name to be specified . Use at the same time JOIN...USING Can be simplified JOIN ON Equivalent connection . It is related to the following SQL The query results are the same ：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

7. Chapter summary

There are three ways to constrain table joins ：WHERE, ON, USING

• WHERE： Applicable to all associated queries

• ON： And the only JOIN Use it together , Only association conditions can be written . Although Association conditions can be combined to WHERE Write... With other conditions , But it's better to write separately .

• USING： And the only JOIN Use it together , And ask for Two Associated fields have the same name in the associated table , And it can only mean that the associated field values are equal

# The associated condition 
# Write the correlation condition in where Back 
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
# Write the correlation condition in on Back , And the only JOIN Use it together 
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
# Write the associated fields in using() in , And the only JOIN Use it together 
# And the associated fields in the two tables must have the same name , And it can only mean =
# Query employee name and basic salary 
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n Zhang table correlation , need n-1 Two association conditions 
# Check the name of the employee , Basic salary , Department name 
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
# 3 A table query can write a inner join stay on Write it separately , You can also write one like this on
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;

Be careful ：

We want to Control the number of join tables . Multiple table join is equivalent to nesting for The cycle is the same , Very resource intensive , Will make SQL Query performance has been severely degraded , So don't join unnecessary tables . In many DBMS in , There will also be a limit on the maximum connection table .

【 mandatory 】 More than three tables prohibited join. need join Field of , Data types are absolutely consistent ; When multi table associated query , Ensure that the associated field needs to have an index .

explain ： Even if the double meter join Also pay attention to table indexes 、SQL performance .

source ： Alibaba 《Java Development Manual 》

appendix ： frequently-used SQL What are the standards

When we officially start talking about the types of connection tables , We need to know first SQL There are different versions of standard specifications , Because the table join operations under different specifications are different .

SQL There are two main criteria , Namely SQL92 and SQL99.92 and 99 Represents the time when the standard was proposed ,SQL92 Namely 92 Standards and specifications proposed in . Except, of course, SQL92 and SQL99 outside , There is still SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 and SQL:2016 Other standards .

So many standards , Which should we learn ？ Actually, the most important thing is SQL The standard is SQL92 and SQL99. Generally speaking SQL92 The form is simpler , But it's written SQL The statements will be longer , Poor readability . and SQL99 Compared with SQL92 Come on , The grammar is more complicated , But more readable . We can also see from the number of pages published by these two standards ,SQL92 There are 500 page , and SQL99 The standard exceeds 1000 page . Actually from SQL99 after , Few people can master all the content , Because there are so many . It's like we use Windows、Linux and Office When , Few people can master all the content . We just need to master some core functions , Just meet the needs of daily work .

**SQL92 and SQL99 It's classic SQL standard , Also called SQL-2 and SQL-3 standard .** It was after the release of these two standards ,SQL More and more influence , Even beyond the database field . Today, SQL It is not only the mainstream language in the field of database , It is also the mainstream language of information processing in the information field . In graphics retrieval 、 Image retrieval and voice retrieval can see SQL The use of language .

The first 07 Chapter _ One line function

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

1. The understanding of function

1.1 What is a function

Function runs through the use of computer language , What does the function do ？ It can encapsulate the code we often use , Call directly when necessary . This is both Improved code efficiency , also Improved maintainability . stay SQL In, we can also use functions to operate on the retrieved data . Use these functions , Can greatly Improve the efficiency of user management of database .

From the perspective of function definition , We can divide the function into Built in functions and Custom function . stay SQL In language , It also includes built-in functions and custom functions . Built in function is a general function built in the system , The custom function is written according to our own needs , This chapter and the next chapter explain SQL Built in functions for .

1.2 Different DBMS The difference between functions

We are using SQL When it comes to language , Not dealing directly with the language , Instead, it uses different database software , namely DBMS.DBMS There are great differences between , Much greater than the difference between different versions of the same language . actually , Only a few functions are called DBMS Supported at the same time . such as , majority DBMS Use （||） perhaps （+） To make a splice , And in the MySQL The string splicing function in is concat(). Most of the DBMS Will have their own specific functions , That means use SQL The code portability of functions is very poor , Therefore, special attention should be paid to when using functions .

1.3 MySQL Built in functions and categories

MySQL Provides a wealth of built-in functions , These functions make data maintenance and management more convenient , It can better provide data analysis and statistical functions , To some extent, it improves the efficiency of data analysis and Statistics .

MySQL The built-in functions provided are from From the perspective of function Can be divided into numerical functions 、 String function 、 Date and time functions 、 Process control functions 、 Encryption and decryption functions 、 obtain MySQL Information functions 、 Aggregate functions, etc . here , I subdivide these rich built-in functions into two categories ： Multiline functions 、 Aggregate functions （ Or grouping function or single line function ）.

Two kinds of SQL function

One line function

• Manipulate data objects
• Accept parameters and return a result
• Change only one line
• Each line returns a result
• Can be nested
• The parameter can be a column or a value

2. Numerical function

2.1 Basic functions

function	usage
ABS(x)	return x The absolute value of
SIGN(X)	return X The symbol of . A positive number returns 1, A negative number returns -1,0 return 0
PI()	Returns the value of the pi , Note that there is no need to write parameters
CEIL(x),CEILING(x)	Returns the smallest integer greater than or equal to a value
FLOOR(x)	Returns the largest integer less than or equal to a value
LEAST(e1,e2,e3…)	Returns the minimum value in the list
GREATEST(e1,e2,e3…)	Returns the maximum value in the list
MOD(x,y)	return X Divide Y Remainder after
RAND()	return 0~1 The random value
RAND(x)	return 0~1 The random value , among x The value of is used as the seed value , same X Values produce the same random number
ROUND(x)	Returns a pair x After rounding the value of , Closest to X The integer of
ROUND(x,y)	Returns a pair x The value of is rounded to the nearest X Value , And keep it after the decimal point Y position
TRUNCATE(x,y)	Return to digital x Truncated to y The result of decimal places
SQRT(x)	return x The square root of . When X When the value of is negative , return NULL

give an example ：

# Basic operation 
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
# Explain incomprehensible functions ：
CEIL(x),CEILING(x)：2 Two functions are equivalent , Rounding up . example ：32 ,33 Whether it's 32.1, still 32.9 Round up to 33.
FLOOR(x)： Just the opposite , Rounding down , example ：32 ,33 Whether it's 32.1, still 32.9 Rounding down is 32.
MOD(x,y): return X Divide Y Remainder after .

# Take random number 
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
# explain ：
RAND()： obtain 0~1 Random number between
RAND(10),RAND(10)： If 2 The parameters in a random function are the same , Then the random number obtained is the same .

 rounding , Truncation operation
SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
explain ： rounding
SELECT ROUND(123.556)： By default, only integers are retained , Round the decimal .123.556---->124
ROUND(123.456,0): Retain 0 Decimal place ,123.456--->123
ROUND(123.456,1): Retain 1 Decimal place ,123.456--->123.5
ROUND(123.456,2): Retain 2 Decimal place ,123.456--->123.46
ROUND(123.456,-1): Keep until 2 This position ,3 dissatisfaction 5 Give up ,123.456--->120
ROUND(153.456,-2): Keep until 1 This position ,5 Satisfy ,3 dissatisfaction 5 Give up ,123.456--->200
explain ： Truncation operation . There is no way to write a parameter .
TRUNCATE(123.456,0)： Keep to integer , 123.456--->123
TRUNCATE(123.496,1)： Keep until 1 Decimal place ,123.496--->123.4
TRUNCATE(129.45,-1)： Keep until 2 The location of ,129.45 --->120
# Single line functions can be nested 
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;

2.2 Angle and radian exchange function

function	usage
RADIANS(x)	Convert angles to radians , among , Parameters x Is the angle value
DEGREES(x)	Convert radians to angles , among , Parameters x Is the radian value

# Exchange of angle and radian 
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

2.3 Trigonometric functions

function	usage
SIN(x)	return x The sine of , among , Parameters x Is the radian value
ASIN(x)	return x The arcsine of , That is, the sine obtained is x Value . If x The value of is not in -1 To 1 Between , Then return to NULL
COS(x)	return x Cosine of , among , Parameters x Is the radian value
ACOS(x)	return x The arccosine of , That is, get the cosine as x Value . If x The value of is not in -1 To 1 Between , Then return to NULL
TAN(x)	return x The tangent of , among , Parameters x Is the radian value
ATAN(x)	return x The arctangent of , That is, the tangent value returned is x Value
ATAN2(m,n)	Returns the arctangent of two parameters
COT(x)	return x Cotangent of , among ,X Is the radian value

give an example ：

ATAN2(M,N) Function returns the arctangent of two parameters .
And ATAN(X) Compared to the function ,ATAN2(M,N) Two parameters are required , For example, there are two points point(x1,y1) and point(x2,y2), Use ATAN(X) The function calculates the arctangent as ATAN((y2-y1)/(x2-x1)), Use ATAN2(M,N) Calculate the arctangent as ATAN2(y2-y1,x2-x1). It can be seen from the way of use , When x2-x1 be equal to 0 when ,ATAN(X) The function will report an error , and ATAN2(M,N) Function can still calculate .

ATAN2(M,N) An example of using the function is as follows ：

SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
FROM DUAL;

2.4 Exponents and logarithms

function	usage
POW(x,y),POWER(X,Y)	return x Of y Power
EXP(X)	return e Of X Power , among e It's a constant ,2.718281828459045
LN(X),LOG(X)	Return to e At the bottom of the X The logarithmic , When X <= 0 when , The result returned is NULL
LOG10(X)	Return to 10 At the bottom of the X The logarithmic , When X <= 0 when , The result returned is NULL
LOG2(X)	Return to 2 At the bottom of the X The logarithmic , When X <= 0 when , return NULL

mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
-> FROM DUAL;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)

2.5 Conversion between bases

function	usage
BIN(x)	return x Binary code of
HEX(x)	return x Hexadecimal encoding
OCT(x)	return x Octal encoding
CONV(x,f1,f2)	return f1 The base number becomes f2 Hexadecimal number

mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
-> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010 | A | 12 | 2 |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)

3. String function

function	usage
ASCII(S)	Return string S Of the first character in ASCII Code value
CHAR_LENGTH(s)	Return string s The number of characters . The functions and CHARACTER_LENGTH(s) identical
LENGTH(s)	Return string s Bytes of , It's about character sets
CONCAT(s1,s2,…,sn)	Connect s1,s2,…,sn For a string
CONCAT_WS(x, s1,s2,…,sn)	Same as CONCAT(s1,s2,…) function , But add... Between each string x
INSERT(str, idx, len, replacestr)	The string str From idx Position start ,len Substrings of characters long are replaced with strings replacestr
REPLACE(str, a, b)	Use string b Replace string str All the strings that appear in a
UPPER(s) or UCASE(s)	The string s All the letters of are converted to capital letters
LOWER(s) or LCASE(s)	The string s All the letters of are converted to lowercase letters
LEFT(str,n)	Return string str The leftmost n Characters
RIGHT(str,n)	Return string str Far right n Characters
LPAD(str, len, pad)	Use string pad Yes str Fill on the far left , until str The length of is len Characters
RPAD(str ,len, pad)	Use string pad Yes str Fill on the far right , until str The length of is len Characters
LTRIM(s)	Remove string s The space on the left
RTRIM(s)	Remove string s The space on the right
TRIM(s)	Remove string s Start and end spaces
TRIM(s1 FROM s)	Remove string s The beginning and the end s1
TRIM(LEADING s1 FROM s)	Remove string s At the beginning s1
TRIM(TRAILING s1 FROM s)	Remove string s At the end s1
REPEAT(str, n)	return str repeat n Results of
SPACE(n)	return n A space
STRCMP(s1,s2)	Compare strings s1,s2 Of ASCII The size of the code value
SUBSTR(s,index,len)	Return from string s Of index Position it len Characters , The functions and SUBSTRING(s,n,len)、MID(s,n,len) identical
LOCATE(substr,str)	Return string substr In string str The first place in , Act on POSITION(substr IN str)、INSTR(str,substr) identical . Not found , return 0
ELT(m,s1,s2,…,sn)	Returns the string at the specified location , If m=1, Then return to s1, If m=2, Then return to s2, If m=n, Then return to sn
FIELD(s,s1,s2,…,sn)	Return string s The position of the first occurrence in the string list
FIND_IN_SET(s1,s2)	Return string s1 In string s2 Where in . among , character string s2 Is a comma separated string
REVERSE(s)	return s The inverted string
NULLIF(value1,value2)	Compare two strings , If value1 And value2 equal , Then return to NULL, Otherwise return to value1

Be careful ：MySQL in , The position of the string is from 1 At the beginning .

give an example ：

mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma')
-> FROM DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
| 0 | 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)

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

4. Date and time functions

4.1 Get date 、 Time

function	usage
CURDATE() ,CURRENT_DATE()	Return current date , Only years 、 month 、 Japan
CURTIME() , CURRENT_TIME()	Return current time , When only 、 branch 、 second
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	Returns the current system date and time
UTC_DATE()	return UTC（ World standard time ） date
UTC_TIME()	return UTC（ World standard time ） Time

give an example ：

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

4.2 Date and time stamp conversion

function	usage
UNIX_TIMESTAMP()	With UNIX Returns the current time in the form of a timestamp .SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	Time date With UNIX Return in the form of a timestamp .
FROM_UNIXTIME(timestamp)	take UNIX The time of the timestamp is converted to the time of the normal format

give an example ：

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
| 1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)

4.3 Get month 、 week 、 Weeks 、 Functions such as days

function	usage
YEAR(date) / MONTH(date) / DAY(date)	Return the specific date value
HOUR(time) / MINUTE(time) / SECOND(time)	Return specific time value
MONTHNAME(date)	Return to the month ：January,…
DAYNAME(date)	Day of the week ：MONDAY,TUESDAY…SUNDAY
WEEKDAY(date)	What day of the week , Be careful , Zhou 1 yes 0, Zhou 2 yes 1,... Sunday is 6
QUARTER(date)	Returns the quarter corresponding to the date , The scope is 1～4
WEEK(date) , WEEKOFYEAR(date)	Go back to the week of the year
DAYOFYEAR(date)	The return date is the day of the year
DAYOFMONTH(date)	The return date is the day of the month
DAYOFWEEK(date)	What day of the week , Be careful ： Sunday is 1, Monday is 2,... Saturday is 7

give an example ：

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

4.4 Operation function of date

function	usage
EXTRACT(type FROM date)	Returns a specific part of a specified date ,type Specifies the value to be returned

EXTRACT(type FROM date) Function type The value and meaning of ：

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

4.5 Time and second conversion function

function	usage
TIME_TO_SEC(time)	take time Convert to seconds and return the result value . The conversion formula is ： Hours *3600+ minute *60+ second
SEC_TO_TIME(seconds)	take seconds The description is converted to include hours 、 Minutes and seconds

give an example ：

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
| 78774 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54 |
+--------------------+
1 row in set (0.12 sec)

4.6 A function that calculates the date and time

The first 1 Group ：

function	usage
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type)	Returns the difference between the given date and time INTERVAL Date time of the time period
DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type)	Return and date Difference between INTERVAL The date of the interval

In the above functions type The value of ：

give an example ：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, # It could be negative 
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 # Need single quotes 
FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

The first 2 Group ：

function	usage
ADDTIME(time1,time2)	return time1 add time2 Time for . When time2 When it is a number , It stands for second , Can be negative
SUBTIME(time1,time2)	return time1 subtract time2 After time . When time2 When it is a number , It stands for second , Can be negative
DATEDIFF(date1,date2)	return date1 - date2 Number of days between dates
TIMEDIFF(time1, time2)	return time1 - time2 Time interval of
FROM_DAYS(N)	Return from 0000 year 1 month 1 The date of ,N Days later
TO_DAYS(date)	Return date date distance 0000 year 1 month 1 Days of the day
LAST_DAY(date)	return date The date of the last day of the month
MAKEDATE(year,n)	Returns a date for a given year and the number of days in the year
MAKETIME(hour,minute,second)	Set the given hour 、 Minutes and seconds are combined into time and return
PERIOD_ADD(time,n)	return time add n After time

give an example ：

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
| 20200101010102 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
| 737773 |
+----------------+
1 row in set (0.00 sec)

give an example ： Inquire about 7 How many new users are there in a day ？

SELECT COUNT(*) as num
FROM new_user
WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

4.7 Formatting and parsing of dates

function	usage
DATE_FORMAT(date,fmt)	According to the string fmt Format date date value
TIME_FORMAT(time,fmt)	According to the string fmt Format time time value
GET_FORMAT(date_type,format_type)	Returns the display format of the date string
STR_TO_DATE(str, fmt)	According to the string fmt Yes str To analyze , Resolve to a date

Above Not GET_FORMAT Function fmt Common format characters for parameters ：

Format symbol	explain	Format symbol	explain
%Y	4 The number of digits indicates the year	%y	Two digits for the year
%M	The name of the month means the month （January,…）	%m	Two figures indicate the month （01,02,03...）
%b	Abbreviated month name （Jan.,Feb.,…）	%c	The number represents the month （1,2,3,…）
%D	The English suffix indicates the number of days in the month （1st,2nd,3rd,…）	%d	Two figures indicate the number of days in the month (01,02…)
%e	The number of days in the month （1,2,3,4,5…）
%H	Two figures represent decimals ,24 hourly （01,02…）	%h and %I	Two figures represent hours ,12 hourly （01,02…）
%k	Hours in digital form ,24 hourly (1,2,3)	%l	The number represents the hour ,12 hourly （1,2,3,4…）
%i	Two figures represent minutes （00,01,02）	%S and %s	Two digits are seconds (00,01,02…)
%W	The name of the week of the week （Sunday…）	%a	The abbreviation of a week （Sun.,Mon.,Tues.,…）
%w	Number the days of the week (0=Sunday,1=Monday…)
%j	With 3 The number of digits represents the number of days in the year (001,002…)	%U	Number the week of the year ,（1,2,3..） among Sunday For the first day of the week
%u	Number the week of the year ,（1,2,3..） among Monday For the first day of the week
%T	24 hourly	%r	12 hourly
%p	AM or PM	%%	Express %

GET_FORMAT Function date_type and format_type The parameter values are as follows ：

give an example ：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34 |
+--------------------------------+
1 row in set (0.00 sec)

SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
1 row in set (0.00 sec)
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;

mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5. Process control functions

Process processing functions can be based on different conditions , Perform different processes , Can be in SQL Statement to implement different conditional choices .MySQL The process processing functions in mainly include IF()、IFNULL() and CASE() function .

function	usage
IF(value,value1,value2)	If value The value of is TRUE, return value1, Otherwise return to value2
IFNULL(value1, value2)	If value1 Not for NULL, return value1, Otherwise return to value2
CASE WHEN Conditions 1 THEN result 1 WHEN Conditions 2 THEN result 2 … [ELSE resultn] END	amount to Java Of if…else if…else…
CASE expr WHEN Constant values 1 THEN value 1 WHEN Constant values 1 THEN value 1 … [ELSE value n] END	amount to Java Of switch…case…

SELECT IF(1 > 0,' correct ',' error ')
-> correct

SELECT IFNULL(null,'Hello Word')
->Hello Word

SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
->1 > 0

SELECT CASE 1
WHEN 1 THEN ' I am a 1'
WHEN 2 THEN ' I am a 2'
ELSE ' who are you '

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN ' High salaries '
WHEN salary>=10000 THEN ' potential share '
WHEN salary>=8000 THEN ' Prick silk '
ELSE ' grassroots ' END " describe "
FROM employees;

SELECT oid,`status`, CASE `status` WHEN 1 THEN ' Unpaid '
WHEN 2 THEN ' Paid '
WHEN 3 THEN ' Shipped '
WHEN 4 THEN ' Confirm receipt '
ELSE ' Invalid order ' END
FROM t_order;

mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
| -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;

SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

practice ： The inquiry department number is 10,20, 30 Employee information , If the department number is 10, Print their salary 1.1 times , 20 Department No , Print their salary 1.2 times , 30 Department No. prints its salary 1.3 Multiple .

6. Encryption and decryption functions

The encryption and decryption function is mainly used to encrypt and decrypt the data in the database , To prevent data from being stolen by others . These functions are very useful in ensuring database security .

function	usage
PASSWORD(str)	Return string str The encrypted version of ,41 Bit long string . Encryption result Irreversible , Commonly used for user password encryption
MD5(str)	Return string str Of md5 Encrypted value , It's also a form of encryption . If the parameter is NULL, Will return NULL
SHA(str)	From the original plaintext password str Calculate and return the encrypted password string , When the parameter is NULL when , return NULL.SHA Encryption algorithm than MD5 More secure .
ENCODE(value,password_seed)	Return to use password_seed Encrypt as an encryption password value
DECODE(value,password_seed)	Return to use password_seed Decrypt as an encrypted password value

You can see ,ENCODE(value,password_seed) Function and DECODE(value,password_seed) Functions are inverse functions to each other .

give an example ：

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)

SELECT md5('123')
->202cb962ac59075b964b07152d234b70

SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50

mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

7. MySQL Information functions

MySQL There are some built-in functions that can be queried MySQL Functions of information , These functions are mainly used to help database developers or operation and maintenance personnel better maintain the database .

function	usage
VERSION()	Returns the current MySQL Version number of
CONNECTION_ID()	Returns the current MySQL Number of connections to the server
DATABASE(),SCHEMA()	return MySQL The database where the command line is currently located
USER(),CURRENT_USER()、SYSTEM_USER(),SESSION_USER()	Return to the current connection MySQL Username , The format of the returned result is “ Host name @ user name ”
CHARSET(value)	Return string value The character set of the argument
COLLATION(value)	Return string value Comparison rules of

give an example ：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+

mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC') |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

8. Other functions

MySQL There are some functions in that cannot be specifically classified , But these functions are MySQL It can not be ignored in the process of development, operation and maintenance .

function	usage
FORMAT(value,n)	Returns a pair of numbers value Formatted result data .n Express rounding Keep it after the decimal point n position
CONV(value,from,to)	take value The value of is converted between different hexadecimals
INET_ATON(ipvalue)	Will be separated by dots IP The address is converted to a number
INET_NTOA(value)	Put... In digital form IP The address is converted to dot separated IP Address
BENCHMARK(n,expr)	Expression expr repeat n Time . Used for testing MySQL Handle expr The time taken by the expression
CONVERT(value USING char_code)	take value The character encoding used is modified to char_code

give an example ：

# If n Is less than or equal to 0, Then only the integer part 
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12 | 124 | 123 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);+-----------------+------------------+-------------------+| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |+-----------------+------------------+-------------------+| 10000 | 22B8 | NULL |+-----------------+------------------+-------------------+1 row in set (0.00 sec)

mysql> SELECT INET_ATON('192.168.1.100');+----------------------------+| INET_ATON('192.168.1.100') |+----------------------------+| 3232235876 |+----------------------------+1 row in set (0.00 sec)# With “192.168.1.100” For example , The calculation method is 192 multiply 256 Of 3 Power , add 168 multiply 256 Of 2 Power , add 1 multiply 256, Plus 100.

mysql> SELECT INET_NTOA(3232235876);+-----------------------+| INET_NTOA(3232235876) |+-----------------------+| 192.168.1.100 |+-----------------------+1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(1, MD5('mysql'));+----------------------------+| BENCHMARK(1, MD5('mysql')) |+----------------------------+| 0 |+----------------------------+1 row in set (0.00 sec)mysql> SELECT BENCHMARK(1000000, MD5('mysql')); +----------------------------------+| BENCHMARK(1000000, MD5('mysql')) |+----------------------------------+| 0 |+----------------------------------+1 row in set (0.20 sec)

mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));+------------------+----------------------------------------+| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |+------------------+----------------------------------------+| utf8mb4 | utf8 |+------------------+----------------------------------------+1 row in set, 1 warning (0.00 sec)

The first 08 Chapter _ Aggregate functions

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

We talked about... In the last chapter SQL One line function . actually SQL There is another kind of function , Called polymerization （ Or gather together 、 grouping ） function , It is a function that summarizes a set of data , The input is a set of data , The output is a single value .

1. Aggregate function introduction

• What are aggregate functions

Aggregate functions act on a set of data , And return a value for a set of data .

• Aggregate function type

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • **COUNT() **
  • other ： variance 、 Standard deviation 、 Median

• Aggregate function syntax

• Aggregate functions cannot be nested . For example, there can be no similar “AVG(SUM( Field name ))” Call of form .
• A multiline function will do statistics on null Value to filter , Direct will null Worth discarding , Don't participate in Statistics .

1.1 AVG and SUM function

It can be done to Numerical data Use AVG and SUM function .

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
#1.1 AVG / SUM ： Only applicable to fields of numeric type （ Or variable ）
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
# The following actions are meaningless , Cannot use... On a string ,mysql The string used in is 0,oracle Direct error reporting in .
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;

1.2 MIN and MAX function

It can be done to Any data type Data usage of MIN and MAX function .

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
#1.2 MAX / MIN : For numeric types 、 String type 、 Fields of date time type （ Or variable ）
# explain , Can sort, you can find the maximum and minimum . For example, by name abcd Alphabetical order .
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;

1.3 COUNT function

• COUNT(*) Returns the total number of records in the table , Apply to Any data type .

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

• COUNT(expr) return expr Not empty Total records for .

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
#1.3 COUNT：
# ① effect ： Calculate the number of specified fields in the query structure （ It doesn't contain NULL It's worth it ）
# Can a field be a field in a table .COUNT(1)
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;
SELECT *
FROM employees;
# If there are many records in the calculation table , How to achieve ？
# The way 1：COUNT(*)
# The way 2：COUNT(1) Represents a record as a 1, Figure out how many 1.COUNT(2) Represents a record as a 2 To view .
# The way 3：COUNT( Specific fields ) : Not necessarily ！
#② Be careful ： When calculating the number of occurrences of the specified field , It is not calculated NULL It's worth it .
SELECT COUNT(commission_pct) #35
FROM employees;
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#③ The formula ：AVG = SUM / COUNT
# Above these AVG SUM MAX MIN COUNT Will automatically filter out null values null
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;
# demand ： Query the average bonus rate in the company 
# FALSE ！
SELECT AVG(commission_pct)
FROM employees;
# Correct ：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0)) # by null A column of as 0, There are several records 0 Record . Not for Null Several records of , Add up to all the records .
FROM employees;
# How to count the number of records in a table , Use COUNT(*)、COUNT(1)、COUNT( Specific fields ) Which is more efficient ？
# If you are using MyISAM Storage engine , Then the efficiency of the three is the same , All are O(1)
# If you are using InnoDB Storage engine , Then the efficiency of the three ：COUNT(*) = COUNT(1)> COUNT( Field )

• problem ： use count(*),count(1),count( Name ) Who's good ?

  Actually , about MyISAM There's no difference between the engine's tables . There is a counter inside the engine that maintains the number of rows .

  Innodb The watch of the engine count(*),count(1) Read lines directly , Complexity is O(n), because innodb I really have to count it . But better than concrete count( Name ).

• problem ： Can I use count( Name ) Replace count(*)?

  Do not use count( Name ) To replace count(*),count(*) yes SQL92 The syntax for defining the number of standard statistics lines , It's not about the database , Follow NULL He Fei NULL irrelevant .

  explain ：count(*) The statistical value is NULL The line of , and count( Name ) This column is not counted NULL Row of values .

2. GROUP BY

2.1 Basic use

have access to GROUP BY Clause divides the data in a table into groups

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

clear ：WHERE It must be on FROM Back

stay SELECT All columns in the list that are not included in the group function should be included in GROUP BY clause

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

Included in GROUP BY Columns in Clause need not be included in SELECT In the list

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
#2. GROUP BY Use 
# demand ： Query the average salary of each department , Maximum wage 
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id
# demand ： Check each one job_id The average wage of 
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;

2.2 Use multiple columns to group

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
# demand ： Check each one department_id,job_id The average wage of ( Multiple grouping conditions , For example, the salary of the same type of work in the same department )
# The way 1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
# The way 2： The first division of labor has the same effect in sub sectors , Because no matter the Department before the type of work , Or the type of work first and then the Department , The final result is the same department and the same type of work together .
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

2.3 GROUP BY Use in WITH ROLLUP

Use WITH ROLLUP After keyword , Add a record after all the queried grouping records , This record calculates the sum of all the queried records , That is, count the number of records .

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
# FALSE ！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
# Conclusion 1：SELECT The fields of non group functions appearing in must be declared in GROUP BY in .
# conversely ,GROUP BY Fields declared in can not appear in SELECT in .
Non group functions ： Fields that do not evaluate multiline functions , AVG SUM MAX MIN COUNT.
# Conclusion 2：GROUP BY The statement in FROM Back 、WHERE Back ,ORDER BY front 、LIMIT front ,
namely ：from where grouping Sort Pagination . If there are all, sort according to this .
# Conclusion 3：MySQL in GROUP BY Use in WITH ROLLUP
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
# demand ： Query the average salary of each department , In ascending order of average wage 
# AVG(salary) avg_sal Calculate the average salary of everyone in the company .
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
# explain ： When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive .
# FALSE ：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;

Be careful ：

When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive .

3. HAVING

3.1 Basic use

Filter grouping ：HAVING Clause

1. Rows have been grouped .
2. The aggregate function is used .
3. Satisfy HAVING The grouping of conditions in the clause will be displayed .
4. HAVING Not to be used alone , Must follow GROUP BY Use it together .

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

• ** Illegal use of aggregate function ： Can't be in WHERE The aggregate function is used in clause .** as follows ：

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
#3. HAVING Use ( effect ： Used to filter data )
# practice ： Query the highest salary ratio in each department 10000 High department information 
# The wrong way to write ：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;
# requirement 1： If an aggregate function is used in the filter condition , Must be used HAVING To replace WHERE. otherwise , Report errors .
# requirement 2：HAVING It must be stated that in GROUP BY Behind .
# Correct writing ：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
# requirement 3： In development , We use HAVING The premise is SQL Used in GROUP BY.
# practice ： Inquiry Department id by 10,20,30,40 this 4 The highest wage ratio in a department 10000 High department information 
# The way 1： recommend , Execution efficiency is higher than mode 2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
# The way 2：
problem 1：where Multiline functions cannot be used in ( Group ) function , and having You can write group functions or ordinary conditions in , that
still more where What for? , All use having Not to go ？？？？
answer ： First having Used with groups , If there is no grouping, you can only use where Filter conditions , Moreover, even if there is 2 Filter conditions , One
Is a grouping function for having in , The other is the common filter condition, which is recommended to use where Not all written in having in , because where Of
Efficient .
problem 2： Since the way 1 And way 2 That's right , What about the more efficient one ????
answer ： The way 1, The general filter conditions are written in where Zhongbi is written in having Medium efficiency , Explain the following SQL The underlying execution principle .
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
# Conclusion ： When there is an aggregate function in the filter condition , Then this filter condition must be declared in HAVING in .
# When there is no aggregate function in the filter condition , Then this filter condition is declared in WHERE Medium or HAVING Either way . however , It is suggested that you declare in WHERE in .
/* WHERE And HAVING Comparison of 1. In terms of scope of application ,HAVING Has a wider scope of application . 2. If there is no aggregate function in the filter condition ： In this case ,WHERE The efficiency of implementation is higher than HAVING */
#4. SQL The underlying execution principle 
#4.1 SELECT The complete structure of the statement 
/* #sql92 grammar ： SELECT ....,....,....( There are aggregate functions ) FROM ...,....,.... WHERE Connection conditions of multiple tables AND Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... #sql99 grammar ： SELECT ....,....,....( There are aggregate functions ) FROM ... (LEFT / RIGHT)JOIN ....ON Connection conditions of multiple tables (LEFT / RIGHT)JOIN ... ON .... WHERE Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... */
#4.2 SQL Statement execution ：
#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> 
# ORDER BY -> LIMIT
explain 1： Why do filter criteria place where Medium efficiency :. Because of the where Filter out unnecessary data before grouping ,
And put it in having You need to filter by grouping first , Such unnecessary data should also be grouped. Naturally, the efficiency is slow .
explain 2： Why? where If you include an aggregate function, you can't put it in where What about China? , Because the execution order of grouping functions is having After or select Back , All are
After grouping , and where Of course, you can't use grouping functions before grouping .

3.2 WHERE and HAVING Comparison of

difference 1：WHERE You can directly use the fields in the table as filter criteria , However, you cannot use the calculation function in the group as the filter condition ;HAVING Must be with GROUP BY In combination with , You can use the grouping calculation function and grouping field as the filter criteria .

To determine the , When it is necessary to make grouping statistics on data ,HAVING Can finish WHERE Tasks that cannot be completed . This is because , In the query syntax structure ,WHERE stay GROUP BY Before , Therefore, the grouping results cannot be filtered .HAVING stay GROUP BY after , You can use group fields and calculation functions in groups , Filter the grouped result set , This function is WHERE What can't be done . in addition ,WHERE Excluded records are no longer included in the group .

difference 2： If you need to obtain the required data from the associated table through connection ,WHERE Filter first and then connect , and HAVING Connect first and then filter . This point , This determines that in the association query ,WHERE Than HAVING More efficient . because WHERE You can screen first , Join with a smaller filtered dataset and associated tables , This takes up less resources , The implementation efficiency is also relatively high .HAVING You need to prepare the result set first , That is, association with unfiltered data sets , Then filter the large data set , This takes up more resources , Execution efficiency is also low .

The summary is as follows ：

	advantage	shortcoming
WHERE	Filter the data before associating , High execution efficiency	You cannot filter using the calculation function in the group
HAVING	You can use the calculation function in the group	Filter in the final result set , Low execution efficiency

Choices in development ：

WHERE and HAVING It's not mutually exclusive , We can use both in one query WHERE and HAVING. The conditions containing grouping statistical functions are HAVING, For ordinary conditions WHERE. such , We're taking advantage of WHERE Efficient and rapid of conditions , Played again HAVING You can use the advantages of query conditions that include grouping statistics functions . When the amount of data is very large , There will be a big difference in operating efficiency .

4. SELECT Implementation process of

4.1 The structure of the query

# The way 1：
SELECT ...,....,...
FROM ...,...,....
WHERE Connection conditions of multiple tables
AND Filter conditions that do not contain group functions
GROUP BY ...,...
HAVING Contains filter conditions for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...
# The way 2：
SELECT ...,....,...
FROM ... JOIN ...
ON Connection conditions of multiple tables
JOIN ...
ON ...
WHERE Filter conditions that do not contain group functions
AND/OR Filter conditions that do not contain group functions
GROUP BY ...,...
HAVING Contains filter conditions for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...
# among ：
#（1）from： From which tables to filter 
#（2）on： When associating multiple table queries , Remove Cartesian product 
#（3）where： Conditions filtered from the table 
#（4）group by： Group by 
#（5）having： Sift through the statistical results again 
#（6）order by： Sort 
#（7）limit： Pagination 

4.2 SELECT Execution order

You need to remember SELECT Two order of query ：

1. The order of keywords cannot be reversed ：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT The order in which statements are executed （ stay MySQL and Oracle in ,SELECT The execution sequence is basically the same ）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT Field of -> DISTINCT -> ORDER BY -> LIMIT

For example, you wrote a SQL sentence , Then its keyword order and execution order are as follows ：

SELECT DISTINCT player_id, player_name, count(*) as num # The order 5
FROM player JOIN team ON player.team_id = team.team_id # The order 1
WHERE height > 1.80 # The order 2
GROUP BY player.team_id # The order 3
HAVING num > 2 # The order 4
ORDER BY num DESC # The order 6
LIMIT 2 # The order 7

stay SELECT When the statement executes these steps , Each step produces a Virtual table , Then pass this virtual table into the next step as input . It should be noted that , These steps are implicit in SQL During the execution of , It's invisible to us .

4.3 SQL Implementation principle of

SELECT It's to execute first FROM This step of . At this stage , If multiple tables are used for associated query , The following steps will be followed ：

1. First pass CROSS JOIN Find the Cartesian product , Equivalent to getting a virtual table vt（virtual table）1-1;
2. adopt ON Screening , In virtual tables vt1-1 On the basis of , Get the virtual table vt1-2;
3. Add external row . If we use the left connection 、 Right link or full link , It will involve external lines , That is, in the virtual table vt1-2 Add external rows based on , Get the virtual table vt1-3.

Of course, if we operate more than two tables , The above steps will be repeated , Until all tables are processed . This process yields our raw data .

When we get the original data of the query data table , That is, the final virtual table vt1, On this basis WHERE Stage . At this stage , Will be based on vt1 Filter the results of the table , Get the virtual table vt2.

Then go to step 3 and step 4 , That is to say GROUP and HAVING Stage . At this stage , It's actually in the virtual table vt2 Group and group filtering based on , Get the middle virtual table vt3 and vt4.

When we have finished the condition screening section , You can filter the fields extracted from the table , That is to say, to enter SELECT and DISTINCT Stage .

First, in the SELECT The phase will extract the desired fields , And then in DISTINCT Stage filters out duplicate rows , Get the middle virtual table vt5-1 and vt5-2.

When we extract the desired field data , You can sort according to the specified fields , That is to say ORDER BY Stage , Get the virtual table vt6.

Last in vt6 On the basis of , Take out the record of the specified line , That is to say LIMIT Stage , Get the final result , The corresponding virtual table vt7.

Of course we're writing SELECT At the time of statement , Not all keywords exist , The corresponding stage will be omitted .

Also because SQL It is a structured query language similar to English , So we're writing SELECT At the time of statement , Also pay attention to the corresponding keyword order , The so-called principle of bottom operation , That's the execution sequence we just talked about .

The first 09 Chapter _ Subquery

lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）

Official website ：http://www.atguigu.com

---

Subquery refers to a query that is nested within another query statement by one query statement , This feature comes from MySQL 4.1 Start introducing .

SQL The use of neutron queries has greatly enhanced SELECT The ability to query , Because many times queries need to get data from the result set , Or you need to calculate a data result from the same table first , Then with this data result （ It could be some scalar , It could also be a collection ） Compare .

1. Demand analysis and problem solving

1.1 Practical problems

Existing solutions ：

#1. By a specific need , Introduce subquery 
# demand ： Whose pay ratio is Abel The height of ？
# The way 1： Write 2 strip sql Low efficiency , Because you need to query 2 Time , You need to interact in the background 2 Time , Considering network connection, etc .
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
# The way 2： Self join 
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` # Connection conditions of multiple tables 
AND e1.last_name = 'Abel';

# The way 3： Subquery 
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#2. The standard of appellation ： External query （ Or main query ）、 Internal query （ Or subquery ）
/* - Subquery （ Internal query ） Execute once before the main query . - The results of the sub query are by the main query （ External query ） Use . - matters needing attention - Subqueries should be enclosed in parentheses - Place the subquery to the right of the comparison criteria - Single line operators correspond to single line subqueries , Multiline operators correspond to multiline subqueries */
# Not recommended ： The condition is written on the left , Poor readability 
SELECT last_name,salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
) < salary;

1.2 The basic use of subqueries

• The basic syntax structure of subquery ：

• Subquery （ Internal query ） Execute once before the main query .
• The results of the sub query are by the main query （ External query ） Use .
• matters needing attention
  • Subqueries should be enclosed in parentheses
  • Place the subquery to the right of the comparison criteria
  • Single line operators correspond to single line subqueries , Multiline operators correspond to multiline subqueries

1.3 Classification of subqueries

Classification 1：

We return one or more records according to the results of internal query , Sub queries are divided into Single line sub query 、 Multi line sub query .

• Single line sub query

• Multi line sub query

Classification 2：

We check whether the query is executed more than once , Divide the subquery into relevant ( Or associated ) Subquery and Unrelated ( Or unrelated ) Subquery .

The subquery queries the data results from the data table , If this data result is executed only once , The data result is then executed as a condition of the main query , Then such a subquery is called an unrelated subquery .

Again , If the subquery needs to be executed multiple times , That is, in the form of circulation , Start with an external query , Every time a subquery is passed in to query , The results are then fed back to the outside world , This nested execution is called related subquery .

/* 3. Classification of subqueries angle 1： The number of entries of results returned from the internal query Single line sub query ( It turns out that 1 individual ) vs Multi line sub query ( It turns out that there are multiple ) angle 2： Whether the internal query is executed more than once Correlation subquery vs Uncorrelated subqueries such as ： Requirements for related sub queries ： Query the information of employees whose salary is greater than the average salary of their department .( The results of subqueries vary with departments , Compare it with the value of this change every time ) Requirements for unrelated subqueries ： Query the information of employees whose salary is greater than the average salary of the company .( The result of the subquery is a constant , Compare with this fixed value every time ) */

2. Single line sub query

2.1 Single line comparison operator

The operator	meaning
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<> or !=	not equal to

2.2 Code example

subject ： Query salary greater than 149 Information of the employee whose salary is No

# Writing skills of sub query （ Or steps ）：① Write from the inside out ② Write from the outside to the inside 
#4. Single line sub query 
#4.1 Single line operators ： = != > >= < <= 
# subject ： Query salary greater than 149 Information of the employee whose salary is No 
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
);

subject ： return job_id And 141 Same as employee No ,salary Than 143 The name of the employee with a large number of employees ,job_id And wages

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);

subject ： Return to the lowest paid employees of the company last_name,job_id and salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

subject ： Query and 141 Number or 174 Of employee No manager_id and department_id Of the same other employees employee_id,manager_id,department_id

Realization way 1： Unpaired comparison

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);

Realization way 2： Compare in pairs ： If the conditions are the same, you can write together , Almost efficient .

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

2.3 HAVING Subqueries in

• First, execute the subquery .
• To the... In the main query HAVING Clause returns the result .

subject ： The minimum wage is greater than 50 The Department of minimum wage id And its minimum wage

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);

2.4 CASE Subqueries in

stay CASE Single column subqueries are used in expressions ：

subject ： Explicit employee employee_id,last_name and location. among , If employee department_id And location_id by 1800 Of department_id identical , be location by ’Canada’, The rest are ’USA’.

SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

2.5 Null value problem in subquery

# The inner layer of the subquery does not return a row, which is equivalent to null, The outer query does not return any rows 
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

The subquery does not return any rows

2.6 Illegal use of subquery

# problem ： The return value of the inner sub query is multi line, which indicates that it is a multi line sub query , and = It is a single line sub query symbol, so an error is reported 
# solve ： Use multiline subquery symbols 
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

Multi line subqueries use single line comparators

3. Multi line sub query

• Also known as set comparison subquery
• Inner query returns multiple rows
• Use the multiline comparison operator

3.1 Multiline comparison operator

The operator	meaning
IN	Equal to... In the list Any one
ANY	It needs to be used with the single line comparison operator , And subqueries return One of them Value comparison
ALL	It needs to be used with the single line comparison operator , And subqueries return all Value comparison
SOME	It's actually ANY Another name for , The same effect , Commonly used ANY

experience ANY and ALL The difference between

3.2 Code example

# IN:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

subject ： Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of any low paid employee in the Department 、 full name 、job_id as well as salary

# ANY / ALL:
# subject ： Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of any low paid employee in the Department 、
# full name 、job_id as well as salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

subject ： Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of all employees in the Department with low salary 、 full name 、job_id as well as salary

# subject ： Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of all low paid employees in the Department 、
# full name 、job_id as well as salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);

subject ： Query the Department with the lowest average wage id

# Wrong writing :MySQL Aggregate functions in cannot be nested , Single line functions can .oracle Can be used without requirements .
select min(AVG(salary))
from employees
GROUP BY department_id;
# Solutions ： Treat the result of the query as a new table , There is only one ordinary column in it , Of course, you can use multiline functions .
step ：
First, query the average salary of each department ： List the query as an alias ：avg_sal
select AVG(salary) avg_sal
from employees
GROUP BY department_id;
secondly ： Treat the results of the whole query as a table , And need a table alias , Otherwise, the report will be wrong ：
select avg_sal
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id) t_dept_avg_sal;
Then query the minimum wage of the average wage of each department ： This column should be an ordinary column , You can use multiline functions .
select min(avg_sal)
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id) t_dept_avg_sal;
Finally, query the minimum wage of the average wage of each department id： The result of the above operation is the minimum wage of the average wage of the Department , Which department do you need to find the minimum salary now .
# The way 1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =( select min(avg_sal)
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id
) t_dept_avg_sal
);
# explain ：3 Layer loop nesting , From inside to outside ： The average salary of each department , The average wage of the smallest Department , The average wage in that department is the lowest 

# The way 2： Optimize , Outer layer ： Query the average salary of a department is less than or equal to Inner layer ： So the average salary , Only one of the query results of the outer layer is less than all the average wages 
Then the average wage of this outer department is the smallest .
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)

3.3 The null problem

# Multi line sub query , There are multiple results in the query , One of the multiple results of the internal query is null, This whole is null.
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);

4. Correlation subquery

4.1 Related sub query execution process

If the execution of a subquery depends on an external query , Usually, it is because the tables in the subquery use external tables , Conditional correlation is carried out , So every time you execute an external query , All subqueries have to be recalculated , Such a subquery is called Associated subquery .

Related sub queries are executed row by row , Each row of the main query executes a subquery .

explain ： The columns in the main query are used in the subquery

4.2 Code example

subject ： Query the salary of employees whose salary is greater than the average salary of the Department last_name,salary And its department_id

Mode one ： Correlation subquery

#6. Correlation subquery 
# review ： Query the salary of employees whose salary is greater than the average salary of the company last_name,salary And its department_id
#6.1 
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
# subject ： Query the salary of employees whose salary is greater than the average salary of the Department last_name,salary And its department_id
# The way 1： Use related subqueries 
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE department_id = e1.`department_id`
);

Mode two ： stay FROM Use subqueries in

# The way 2： stay FROM Zhongshengmingzi query 
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

from Subquery of type ： Subquery is used as from Part of , The subquery should use () Lead up , And alias this subquery ,
Think of it as a “ Temporary virtual table ” To use .

stay ORDER BY Use subqueries in ：

subject ： Check the employee's id,salary, according to department_name Sort

SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);
# Conclusion ： stay SELECT in , except GROUP BY and LIMIT outside , Other locations can be queried by the voice ！
/* SELECT ....,....,....( There are aggregate functions ) FROM ... (LEFT / RIGHT)JOIN ....ON Connection conditions of multiple tables (LEFT / RIGHT)JOIN ... ON .... WHERE Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... */

subject ： if employees In the table employee_id And job_history In the table employee_id The same number is not less than 2, Output these same id Of our employees employee_id,last_name And its job_id

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);

4.3 EXISTS And NOT EXISTS keyword

• Associated subqueries are usually associated with EXISTS Operators are used together , Used to check whether there are qualified rows in the sub query .
• If there are no qualified rows in the subquery ：
  • Conditional return FALSE
  • Continue to find... In subqueries
• If there are qualified rows in the subquery ：
  • Do not continue to find in subquery
  • Conditional return TRUE
• NOT EXISTS Keyword means that if there is no condition , Then return to TRUE, Otherwise return to FALSE.

subject ： Query company managers employee_id,last_name,job_id,department_id Information

Mode one ：

# The way 1： Self join 
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

Mode two ：

# The way 2： Subquery 
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);

Mode three ：

# The way 3： Use EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e1.`employee_id` = e2.`manager_id`
);

subject ： Inquire about departments In the table , There is no in employees Of the departments in the table department_id and department_name

# The way 1：
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# The way 2：
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
/* Writing skills of sub query （ Or steps ）：① Write from the inside out ② Write from the outside to the inside How to choose ？ ① If the subquery is relatively simple , It is suggested to write from the outside to the inside . Once the subquery structure is complex , It is suggested to write from the inside out ② If it is a related subquery , It's usually written from the outside to the inside . */

4.4 Related updates

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

Use related subqueries to update data from one table to another .

subject ： stay employees Add one department_name Field , The data is the Department name corresponding to the employee

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

4.4 Relevant delete

 DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

Use related subqueries to delete data from one table based on data from another .

subject ： Delete table employees in , And emp_history Data from all tables

DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);

5. Throw a question

** problem ：** Whose pay ratio is Abel The height of ？

answer ：

# The way 1： Self join 
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

# The way 2： Subquery 
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

** problem ：** Are the above two methods good or bad ？

** answer ：** Good self connection mode ！

Subqueries can be used in the title , You can also use self connection . In general, it is recommended that you use self connection , Because in many DBMS In the process of processing , The processing speed of self join is much faster than sub query .

It can be understood in this way ： Sub query is actually the condition judgment after query through unknown table , The self connection is to judge the conditions through the known self data table , So in most cases DBMS Self join processing has been optimized in .