Single Line Processing Function

Data processing functions are also known as one-line processing functions

Features of one-line processing functions: one input corresponds to one output.

As opposed to single-line processing functions, multiline processing functions. (Multi-line processing function features: multiple inputs, corresponding to one output!)

What are the common one-line processing functions?

lower Convert lowercase
		mysql> select lower(ename) as ename from emp;
		+--------+
		| ename  |
		+--------+
		| smith  |
		| allen  |
		| ward   |
		| jones  |
		| martin |
		| blake  |
		| clark  |
		| scott  |
		| king   |
		| turner |
		| adams  |
		| james  |
		| ford   |
		| miller |
		+--------+
		14 There are 14 inputs and 14 outputs. This is a feature of one-line processing functions.

	upper Convert Uppercase
		mysql> select * from t_student;
		+----------+
		| name     |
		+----------+
		| zhangsan |
		| lisi     |
		| wangwu   |
		| jack_son |
		+----------+

		mysql> select upper(name) as name from t_student;
		+----------+
		| name     |
		+----------+
		| ZHANGSAN |
		| LISI     |
		| WANGWU   |
		| JACK_SON |
		+----------+

	substr Take Substring ( substr( Intercepted String, Start Subscript,Length of intercept))
		select substr(ename, 1, 1) as ename from emp;
		Note: Start subscript starts from 1, No 0.
		Find out if the first letter of the employee's name is A Employee information for?
			First: Fuzzy Query
				select ename from emp where ename like 'A%';
			Second way: substr function
				select 
					ename 
				from 
					emp 
				where 
					substr(ename,1,1) = 'A';

		Capitalize the first letter?
			select name from t_student;
			select upper(substr(name,1,1)) from t_student;
			select substr(name,2,length(name) - 1) from t_student;
			select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;
			+----------+
			| result   |
			+----------+
			| Zhangsan |
			| Lisi     |
			| Wangwu   |
			| Jack_son |
			+----------+
		
	concat Function for string stitching
		select concat(empno,ename) from emp;
		+---------------------+
		| concat(empno,ename) |
		+---------------------+
		| 7369SMITH           |
		| 7499ALLEN           |
		| 7521WARD            |
		| 7566JONES           |
		| 7654MARTIN          |
		| 7698BLAKE           |
		| 7782CLARK           |
		| 7788SCOTT           |
		| 7839KING            |
		| 7844TURNER          |
		| 7876ADAMS           |
		| 7900JAMES           |
		| 7902FORD            |
		| 7934MILLER          |
		+---------------------+

	length Take Length
		select length(ename) enamelength from emp;
		+-------------+
		| enamelength |
		+-------------+
		|           5 |
		|           5 |
		|           4 |
		|           5 |
		|           6 |
		|           5 |
		|           5 |
		|           5 |
		|           4 |
		|           6 |
		|           5 |
		|           5 |
		|           4 |
		|           6 |
		+-------------+

	trim Strip Spaces
		mysql> select * from emp where ename = '  KING';
		Empty set (0.00 sec)

		mysql> select * from emp where ename = trim('   KING');
		+-------+-------+-----------+------+------------+---------+------+--------+
		| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
		+-------+-------+-----------+------+------------+---------+------+--------+
		|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
		+-------+-------+-----------+------+------------+---------+------+--------+

	str_to_date Convert string to date
	date_format format date
	format Set thousands

	case..when..then..when..then..else..end
		When an employee's job is MANAGER When the salary is increased by 10%，When the job is SALESMAN When the salary is increased by 50%,Other normal.
		(Note: Do not modify the database, just show the query result as a pay increase)
		select 
			ename,
			job, 
			sal as oldsal,
			(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
		from 
			emp;
		
		+--------+-----------+---------+---------+
		| ename  | job       | oldsal  | newsal  |
		+--------+-----------+---------+---------+
		| SMITH  | CLERK     |  800.00 |  800.00 |
		| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
		| WARD   | SALESMAN  | 1250.00 | 1875.00 |
		| JONES  | MANAGER   | 2975.00 | 3272.50 |
		| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
		| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
		| CLARK  | MANAGER   | 2450.00 | 2695.00 |
		| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
		| KING   | PRESIDENT | 5000.00 | 5000.00 |
		| TURNER | SALESMAN  | 1500.00 | 2250.00 |
		| ADAMS  | CLERK     | 1100.00 | 1100.00 |
		| JAMES  | CLERK     |  950.00 |  950.00 |
		| FORD   | ANALYST   | 3000.00 | 3000.00 |
		| MILLER | CLERK     | 1300.00 | 1300.00 |
		+--------+-----------+---------+---------+

	round Rounding
		select field from Table Name;
		select ename from emp;
		select 'abc' from emp; // select is followed directly by Literal/Literal Value

		mysql> select 'abc' as bieming from emp;
		+---------+
		| bieming |
		+---------+
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		| abc     |
		+---------+

		mysql> select abc from emp;
		ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
		This will definitely make a mistake, because it will abc As the name of a field, go emp Find in Table abc Field gone.

		select 1000 as num from emp; // 1000 is also treated as a literal quantity/literal value.
		+------+
		| num  |
		+------+
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		| 1000 |
		+------+

		Conclusion: select It can be followed by a table's field name (which can be equated to a variable name) or a literal quantity/Literal value (data).
		select 21000 as num from dept;
		+-------+
		| num   |
		+-------+
		| 21000 |
		| 21000 |
		| 21000 |
		| 21000 |
		+-------+

		mysql> select round(1236.567, 0) as result from emp; //Keep integer digits.
		+--------+
		| result |
		+--------+
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		+--------+

		select round(1236.567, 1) as result from emp; //Keep 1 decimal
		select round(1236.567, 2) as result from emp; //Keep 2 decimals
		select round(1236.567, -1) as result from emp; // Keep to ten.
		+--------+
		| result |
		+--------+
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		+--------+

		select round(1236.567, -2) as result from emp;
		+--------+
		| result |
		+--------+
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		+--------+

	rand() Generate Random Numbers
		mysql> select round(rand()*100,0) from emp; // Random numbers within 100
		+---------------------+
		| round(rand()*100,0) |
		+---------------------+
		|                  76 |
		|                  29 |
		|                  15 |
		|                  88 |
		|                  95 |
		|                   9 |
		|                  63 |
		|                  89 |
		|                  54 |
		|                   3 |
		|                  54 |
		|                  61 |
		|                  42 |
		|                  28 |
		+---------------------+
		
	ifnull You can null Convert to a specific value
		ifnull Is an empty handler. Empty is handled exclusively.
		Of all databases, as long as there are NULL Participate in mathematical operations, and the end result is NULL. 
		mysql> select ename, sal + comm as salcomm from emp;
		+--------+---------+
		| ename  | salcomm |
		+--------+---------+
		| SMITH  |    NULL |
		| ALLEN  | 1900.00 |
		| WARD   | 1750.00 |
		| JONES  |    NULL |
		| MARTIN | 2650.00 |
		| BLAKE  |    NULL |
		| CLARK  |    NULL |
		| SCOTT  |    NULL |
		| KING   |    NULL |
		| TURNER | 1500.00 |
		| ADAMS  |    NULL |
		| JAMES  |    NULL |
		| FORD   |    NULL |
		| MILLER |    NULL |
		+--------+---------+

		Calculate the annual salary of each employee?
			Annual salary = (A monthly salary + Monthly allowance) * 12
			
			select ename, (sal + comm) * 12 as yearsal from emp;
			+--------+----------+
			| ename  | yearsal  |
			+--------+----------+
			| SMITH  |     NULL |
			| ALLEN  | 22800.00 |
			| WARD   | 21000.00 |
			| JONES  |     NULL |
			| MARTIN | 31800.00 |
			| BLAKE  |     NULL |
			| CLARK  |     NULL |
			| SCOTT  |     NULL |
			| KING   |     NULL |
			| TURNER | 18000.00 |
			| ADAMS  |     NULL |
			| JAMES  |     NULL |
			| FORD   |     NULL |
			| MILLER |     NULL |
			+--------+----------+

			Be careful: NULL As long as it takes part in the operation, the end result must be NULL. To avoid this, you need to use ifnull Function.
			ifnull Function usage: ifnull(data, What value is considered)
				If Data is NULL Then, what is the value of this data structure?
			
			Subsidies are NULL When, treat grants as 0
				select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;
				+--------+----------+
				| ename  | yearsal  |
				+--------+----------+
				| SMITH  |  9600.00 |
				| ALLEN  | 22800.00 |
				| WARD   | 21000.00 |
				| JONES  | 35700.00 |
				| MARTIN | 31800.00 |
				| BLAKE  | 34200.00 |
				| CLARK  | 29400.00 |
				| SCOTT  | 36000.00 |
				| KING   | 60000.00 |
				| TURNER | 18000.00 |
				| ADAMS  | 13200.00 |
				| JAMES  | 11400.00 |
				| FORD   | 36000.00 |
				| MILLER | 15600.00 |
				+--------+----------+

Grouping function

The features of a multiline processing function are that multiple lines are input and one line is output.

Five:
Count count
sum of sums
avg mean
max maximum
min minimum

Be careful:
Grouping functions must be grouped before they can be used. If you do not group data, the whole table defaults to one group.

Find the maximum salary?
		mysql> select max(sal) from emp;
		+----------+
		| max(sal) |
		+----------+
		|  5000.00 |
		+----------+
	
	Find the minimum wage?
		mysql> select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+
	
	Calculate wages and:
		mysql> select sum(sal) from emp;
		+----------+
		| sum(sal) |
		+----------+
		| 29025.00 |
		+----------+
	
	Calculate average wage:
		mysql> select avg(sal) from emp;
		+-------------+
		| avg(sal)    |
		+-------------+
		| 2073.214286 |
		+-------------+
		14 Add up all the wages and divide by 14.
	
	Calculate the number of employees?
		mysql> select count(ename) from emp;
		+--------------+
		| count(ename) |
		+--------------+
		|           14 |
		+--------------+

What do you need to be aware of when using grouping functions?

		First point: Grouping functions are automatically ignored NULL，You don't need to pair in advance NULL Processing.
		mysql> select sum(comm) from emp;
		+-----------+
		| sum(comm) |
		+-----------+
		|   2200.00 |
		+-----------+
		
		mysql> select count(comm) from emp;
		+-------------+
		| count(comm) |
		+-------------+
		|           4 |
		+-------------+
		mysql> select avg(comm) from emp;
		+------------+
		| avg(comm)  |
		+------------+
		| 550.000000 |
		+------------+

		Second point: in the grouping function count(*)and count(Specific fields)What's the difference?
			mysql> select count(*) from emp;
			+----------+
			| count(*) |
			+----------+
			|       14 |
			+----------+

			mysql> select count(comm) from emp;
			+-------------+
			| count(comm) |
			+-------------+
			|           4 |
			+-------------+

			count(Specific fields): Indicates statistics for all not- NULL The total number of elements of.
			count(*): The total number of rows in the statistics table. (As long as there is one row of data count be++)
						Because every row of records cannot be NULL，One column in a row of data is not NULL，This row of data is valid.
		
		Third point: Grouping functions cannot be used directly in where In clauses.
			Find out about employees who are above the minimum wage.
				select ename,sal from emp where sal > min(sal);
				Surface OK, run it?
					ERROR 1111 (HY000): Invalid use of group function
		?????????????????????????????????????????????????????????????????????
			Finish Grouping Query(group by)Then it became clear.

		Fourth point: All grouping functions can be combined.
			select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
			+----------+----------+----------+-------------+----------+
			| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
			+----------+----------+----------+-------------+----------+
			| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
			+----------+----------+----------+-------------+----------+

Group Query

In practical applications, there may be a need to group and then manipulate each group of data.

We need to use group query at this time. How to do group query?

			select
				...
			from
				...
			group by
				...

For example:

Calculate wages and salaries for each department?
Calculate average salary per job?
Find the highest salary for each job?
...

Be careful

Put all the previous keywords together to see their execution order?

		select
			...
		from
			...
		where
			...
		group by//Grouping
			...
		order by//Sort Output
			...

	The order of the above keywords cannot be reversed and needs to be remembered.
	What is the execution order?
		1. from
		2. where
		3. group by
		4. select
		5. order by

Why can't grouping functions be used directly behind where?
Select ename, Sal from EMP where Sal > min(sal);// Report errors.
Grouping functions must be grouped before they can be used.
When where is executed, there are no groups. So no grouping function can appear after where.

​ select sum(sal) from emp;
This is not grouped. Why can the sum() function be used?
Because select executes after group by.

Find out what the salary is for each job and what?

The way to achieve this is to divide jobs into groups and then sum up wages.

			select 
				job,sum(sal)
			from
				emp
			group by
				job;
			
			+-----------+----------+
			| job       | sum(sal) |
			+-----------+----------+
			| ANALYST   |  6000.00 |
			| CLERK     |  4150.00 |
			| MANAGER   |  8275.00 |
			| PRESIDENT |  5000.00 |
			| SALESMAN  |  5600.00 |
			+-----------+----------+

Execution order of the above statement?
1. Query the data from the emp table first.
2. Grouping according to job field.
3. Then sum(sal) each group of data

select ename,job,sum(sal) from emp group by job;

		+-------+-----------+----------+
		| ename | job       | sum(sal) |
		+-------+-----------+----------+
		| SCOTT | ANALYST   |  6000.00 |
		| SMITH | CLERK     |  4150.00 |
		| JONES | MANAGER   |  8275.00 |
		| KING  | PRESIDENT |  5000.00 |
		| ALLEN | SALESMAN  |  5600.00 |
		+-------+-----------+----------+

The above statement can be executed in mysql, but it doesn't make sense.
The above statement executes an error in oracle.
oracle's syntax is stricter than mysql's. (The syntax of MySQL is relatively loose!)

Key conclusions:
In a select statement, if there is a group by statement, the select can only be followed by fields that participate in grouping and grouping functions. Nothing else can follow.

Find the highest salary for each department
What is the implementation idea?
Grouped by department number to maximize each group.

mysql> select deptno,max(sal) from emp group by deptno;
			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			|     20 |  3000.00 |
			|     30 |  2850.00 |
			+--------+----------+

Find the highest salary for "every department, different job"?

Tip: Two fields are combined into one field to see. (Joint grouping of two fields)

		select 
			deptno, job, max(sal)
		from
			emp
		group by
			deptno, job;

	+--------+-----------+----------+
	| deptno | job       | max(sal) |
	+--------+-----------+----------+
	|     10 | CLERK     |  1300.00 |
	|     10 | MANAGER   |  2450.00 |
	|     10 | PRESIDENT |  5000.00 |
	|     20 | ANALYST   |  3000.00 |
	|     20 | CLERK     |  1100.00 |
	|     20 | MANAGER   |  2975.00 |
	|     30 | CLERK     |   950.00 |
	|     30 | MANAGER   |  2850.00 |
	|     30 | SALESMAN  |  1600.00 |
	+--------+-----------+----------+

Use having to further filter the data after grouping.
Having cannot be used alone, having cannot replace where, having must
Use in conjunction with group by.

Find the highest salary for each department and ask that it be shown that the highest salary is greater than 3000?

Step 1: Find the highest salary for each department
			Grouped by Department number, maximize each group.
			select deptno,max(sal) from emp group by deptno;
			
			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			|     20 |  3000.00 |
			|     30 |  2850.00 |
			+--------+----------+
		
		Step 2: Require maximum salary above 3000
			select 
				deptno,max(sal) 
			from 
				emp 
			group by 
				deptno
			having
				max(sal) > 3000;

			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			+--------+----------+

Think about a question: Is the above sql statement inefficient to execute?
At a lower level, you can actually think about this: find out all the more than 3000 before grouping them.

			select 
				deptno,max(sal)
			from
				emp
			where
				sal > 3000
			group by
				deptno;		

		+--------+----------+
		| deptno | max(sal) |
		+--------+----------+
		|     10 |  5000.00 |
		+--------+----------+

Optimize strategy:
Where and have, give priority to where, where can't be done, and have.

where can't
Find out the average salary per department and ask to show that the average salary is above 2500.

		Step 1: Find out the average salary per department
			select deptno,avg(sal) from emp group by deptno;
			+--------+-------------+
			| deptno | avg(sal)    |
			+--------+-------------+
			|     10 | 2916.666667 |
			|     20 | 2175.000000 |
			|     30 | 1566.666667 |
			+--------+-------------+

		Step 2: Require that the average salary is above 2500
			select 
				deptno,avg(sal) 
			from 
				emp 
			group by 
				deptno
			having
				avg(sal) > 2500;
		
		+--------+-------------+
		| deptno | avg(sal)    |
		+--------+-------------+
		|     10 | 2916.666667 |
		+--------+-------------+

Summary (Queries on single sheets finished)

	select 
		...
	from
		...
	where
		...
	group by
		...
	having
		...
	order by
		...

The above keywords can only be in this order, not reversed.

Execution order?

1.from
2. where
3. group by
4. having
5. select
6. order by

Query data from a table.
Valuable data is filtered first by where criteria.
Grouping these valuable data.
After grouping, you can use having to continue filtering.
select query out.
Last sorted output!

Comprehensive case: Find out the average salary for each job, ask to show that the average salary is greater than 1500, except for MANAGER jobs, require that the average salary be in descending order.

		select 
			job, avg(sal) as avgsal
		from
			emp
		where
			job <> 'MANAGER'
		group by
			job
		having
			avg(sal) > 1500
		order by
			avgsal desc;

		+-----------+-------------+
		| job       | avgsal      |
		+-----------+-------------+
		| PRESIDENT | 5000.000000 |
		| ANALYST   | 3000.000000 |
		+-----------+-------------+

Remove duplicate records

Remove duplicate records from query results
Note: The original table data will not be modified, only the query results will be redundant.
Reduplication requires a keyword: distinct

mysql> select distinct job from emp;
	+-----------+
	| job       |
	+-----------+
	| CLERK     |
	| SALESMAN  |
	| MANAGER   |
	| ANALYST   |
	| PRESIDENT |
	+-----------+

distinct can only appear at the front of all fields

// distinct appears before the job,deptno fields, indicating that the two fields are combined to weight.
	mysql> select distinct job,deptno from emp;
	+-----------+--------+
	| job       | deptno |
	+-----------+--------+
	| CLERK     |     20 |
	| SALESMAN  |     30 |
	| MANAGER   |     20 |
	| MANAGER   |     30 |
	| MANAGER   |     10 |
	| ANALYST   |     20 |
	| PRESIDENT |     10 |
	| CLERK     |     30 |
	| CLERK     |     10 |
	+-----------+--------+

Count the number of jobs?

		select count(distinct job) from emp;
		
		+---------------------+
		| count(distinct job) |
		+---------------------+
		|                   5 |
		+---------------------+

join query

What is a join query?

A single query from a single table is called a single table query.
The emp and Dept tables combine to query data, take employee names from the emp table, and department names from the dept table.
This cross-table query, where multiple tables join together to query data, is called a join query.

Classification of join queries?

According to the age of grammar:
Syntax that appeared in SQL92:1992
Syntax for SQL99:1999
Here we focus on SQL99. (This process simply demonstrates an example of SQL92)
​

Classify by how tables are joined

Internal connection:
Equal Connection
Non-equivalent connections
Self-connection

Outer connection:
Left Outer Connection (Left Connection)
Right Outer Connection (Right Connection)

Full Connection

What happens when two tables are joined and queried without any conditional restrictions?

When two tables are joined and queried without any restrictions, the final number of query results is the product of the number of two tables.

This phenomenon is called the Cartesian product phenomenon. (Cartesian discovered that this is a mathematical phenomenon.)

How to avoid the Cartesian product phenomenon?

Conditions are added when connecting, and records that meet these conditions are filtered out!

Case study: Query the Department name of each employee?

	select 
		ename,dname 
	from 
		emp, dept
	where
		emp.deptno = dept.deptno;

Think: The number of results in the final query is 14, but during the matching process, does the number of matches decrease?
Fifty-six times, just four choices. The number of times did not decrease.

Note: From the Cartesian product phenomenon, the more times a table is joined, the less efficient it is to avoid the number of times a table is joined.

// Alias the table. Very important. Efficiency issues.
select 
	e.ename,d.dname 
from 
	emp e, dept d
where
	e.deptno = d.deptno; //SQL92 syntax.
	
	+--------+------------+
	| ename  | dname      |
	+--------+------------+
	| CLARK  | ACCOUNTING |
	| KING   | ACCOUNTING |
	| MILLER | ACCOUNTING |
	| SMITH  | RESEARCH   |
	| JONES  | RESEARCH   |
	| SCOTT  | RESEARCH   |
	| ADAMS  | RESEARCH   |
	| FORD   | RESEARCH   |
	| ALLEN  | SALES      |
	| WARD   | SALES      |
	| MARTIN | SALES      |
	| BLAKE  | SALES      |
	| TURNER | SALES      |
	| JAMES  | SALES      |
	+--------+------------+

Equivalent Connections of Inner Connections

Case: Query the name of each employee's department to display the employee name and department name?

emp e and dept d tables are joined. The condition is: e.deptno = d.deptno

SQL92 Grammar:
	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;
	
	sql92 Disadvantages: unclear structure, joining conditions for tables, and conditions for further screening later are all in place where Behind.

SQL99 Grammar:
	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;
		
	//Inner can be omitted (better readability with inner!!! You can see at a glance that it is an inner connection)
	select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno; // Conditions are equivalence relationships, so they are called equivalence joins.

sql99 Advantages: The conditions for table joins are independent, and after joins, if further filtering is required, they can be added later where

	SQL99 Grammar:
		select 
			...
		from
			a
		join
			b
		on
			a and b Connection Conditions
		where
			Filter Criteria

Non-equivalent connections within connections

Case study: Find out each employee's salary level and ask for employee name, salary, salary level?

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; // Conditions are not an equivalence relationship and are called non-equivalence connections.
	
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

Self-connection of inner connection

Self-join, Tip: One table is considered two tables.

Case study: Query the employee's supervisor and ask for the employee's name and the corresponding leader's name to be displayed?

select 
	a.ename as 'Employee Name', b.ename as 'Leadership Name'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; //Employee's Leadership Number = Leader's Employee Number

+--------+--------+
| Employee Name | Leadership Name|
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

External Connection

Outer Connection (Right Outer Connection)

select 
	e.ename,d.dname
from
	emp e 
right join 
	dept d
on
	e.deptno = d.deptno;

// outer can be omitted with readability.
select 
	e.ename,d.dname
from
	emp e 
right outer join 
	dept d
on
	e.deptno = d.deptno;

What does right mean: It means that the table to the right of the join keyword is treated as the main table, mainly to query out all the data of this table, bringing along the table to the left of the associated query.

Among the outer joins, two tables are joined, resulting in a primary-secondary relationship.

Outer Connection (Left Outer Connection)

select 
	e.ename,d.dname
from
	dept d 
left join 
	emp e
on
	e.deptno = d.deptno;

// outer can be omitted with readability.
select 
	e.ename,d.dname
from
	dept d 
left outer join 
	emp e
on
	e.deptno = d.deptno;

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+

Be careful:

A right outer connection, also known as a right connection, has a right outer connection.
A left outer connection with a left is also called a left connection.
Any right connection has a left connection.
Any left connection has a right connection.

Reflection:

The number of query result bars for outer join must be >=the number of query result bars for inner join? Correct.

Case study: Query each employee's supervisor and ask that all employee's name and leader's name be displayed?

select 
		a.ename as 'Employee Name', b.ename as 'Leadership Name'
	from
		emp a
	left join
		emp b
	on
		a.mgr = b.empno; 
	
	+--------+--------+
	| Employee Name      | Leadership Name     |
	+--------+--------+
	| SMITH  | FORD   |
	| ALLEN  | BLAKE  |
	| WARD   | BLAKE  |
	| JONES  | KING   |
	| MARTIN | BLAKE  |
	| BLAKE  | KING   |
	| CLARK  | KING   |
	| SCOTT  | JONES  |
	| KING   | NULL   |
	| TURNER | BLAKE  |
	| ADAMS  | SCOTT  |
	| JAMES  | BLAKE  |
	| FORD   | JONES  |
	| MILLER | CLARK  |
	+--------+--------+

Three tables, how do four tables connect?

select 
			...
		from
			a
		join
			b
		on
			a and b Connection Conditions
		join
			c
		on
			a and c Connection Conditions
		right join
			d
		on
			a and d Connection Conditions
		
		//An internal and external connection in SQL can be mixed. Can appear!

Case: Find out the Department name and salary level of each employee, and ask for the employee name, Department name, salary, salary level?

select 
		e.ename,e.sal,d.dname,s.grade
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;
	
	+--------+---------+------------+-------+
	| ename  | sal     | dname      | grade |
	+--------+---------+------------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 |
	| ALLEN  | 1600.00 | SALES      |     3 |
	| WARD   | 1250.00 | SALES      |     2 |
	| JONES  | 2975.00 | RESEARCH   |     4 |
	| MARTIN | 1250.00 | SALES      |     2 |
	| BLAKE  | 2850.00 | SALES      |     4 |
	| CLARK  | 2450.00 | ACCOUNTING |     4 |
	| SCOTT  | 3000.00 | RESEARCH   |     4 |
	| KING   | 5000.00 | ACCOUNTING |     5 |
	| TURNER | 1500.00 | SALES      |     3 |
	| ADAMS  | 1100.00 | RESEARCH   |     1 |
	| JAMES  |  950.00 | SALES      |     1 |
	| FORD   | 3000.00 | RESEARCH   |     4 |
	| MILLER | 1300.00 | ACCOUNTING |     2 |
	+--------+---------+------------+-------+

Case study: Find out the Department name and salary level of each employee, as well as the supervisor, who is required to show the employee name, leader name, Department name, salary, salary level?

select 
		e.ename,e.sal,d.dname,s.grade,l.ename
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join
		emp l
	on
		e.mgr = l.empno;
	
	+--------+---------+------------+-------+-------+
	| ename  | sal     | dname      | grade | ename |
	+--------+---------+------------+-------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
	| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
	| WARD   | 1250.00 | SALES      |     2 | BLAKE |
	| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
	| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
	| BLAKE  | 2850.00 | SALES      |     4 | KING  |
	| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
	| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
	| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
	| TURNER | 1500.00 | SALES      |     3 | BLAKE |
	| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
	| JAMES  |  950.00 | SALES      |     1 | BLAKE |
	| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
	| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
	+--------+---------+------------+-------+-------+

Subquery

What is a subquery?

The select statement is nested within the select statement, and the nested select statement is called a subquery.

Where can subqueries appear?

	select
		..(select).
	from
		..(select).
	where
		..(select).

Subqueries in where clause

Case study: Find out the name and salary of an employee who is higher than the minimum wage?

Ideas for implementation:
		Step 1: Query what the minimum wage is
			select min(sal) from emp;
			+----------+
			| min(sal) |
			+----------+
			|   800.00 |
			+----------+
		Step 2: Find out>800 Of
			select ename,sal from emp where sal > 800;
		
		Step 3: Merge
			select ename,sal from emp where sal > (select min(sal) from emp);
			+--------+---------+
			| ename  | sal     |
			+--------+---------+
			| ALLEN  | 1600.00 |
			| WARD   | 1250.00 |
			| JONES  | 2975.00 |
			| MARTIN | 1250.00 |
			| BLAKE  | 2850.00 |
			| CLARK  | 2450.00 |
			| SCOTT  | 3000.00 |
			| KING   | 5000.00 |
			| TURNER | 1500.00 |
			| ADAMS  | 1100.00 |
			| JAMES  |  950.00 |
			| FORD   | 3000.00 |
			| MILLER | 1300.00 |
			+--------+---------+

Subqueries in the from clause

Note: Subqueries after from can treat the query result of a subquery as a temporary table. (Skills)

Case study: Find out the average salary scale for each job.

Step 1: Find out the average salary per job (average by job group)
		select job,avg(sal) from emp group by job;
		+-----------+-------------+
		| job       | avgsal      |
		+-----------+-------------+
		| ANALYST   | 3000.000000 |
		| CLERK     | 1037.500000 |
		| MANAGER   | 2758.333333 |
		| PRESIDENT | 5000.000000 |
		| SALESMAN  | 1400.000000 |
		+-----------+-------------+t surface

	Step 2: Overcome psychological barriers and consider the above query results as a real table t. 
	mysql> select * from salgrade; s surface
	+-------+-------+-------+
	| GRADE | LOSAL | HISAL |
	+-------+-------+-------+
	|     1 |   700 |  1200 |
	|     2 |  1201 |  1400 |
	|     3 |  1401 |  2000 |
	|     4 |  2001 |  3000 |
	|     5 |  3001 |  9999 |
	+-------+-------+-------+
	t Tables and s Tables are joined if: t surface avg(sal) between s.losal and s.hisal;
		
		select 
			t.*, s.grade
		from
			(select job,avg(sal) as avgsal from emp group by job) t
		join
			salgrade s
		on
			t.avgsal between s.losal and s.hisal;
		
		+-----------+-------------+-------+
		| job       | avgsal      | grade |
		+-----------+-------------+-------+
		| CLERK     | 1037.500000 |     1 |
		| SALESMAN  | 1400.000000 |     2 |
		| ANALYST   | 3000.000000 |     4 |
		| MANAGER   | 2758.333333 |     4 |
		| PRESIDENT | 5000.000000 |     5 |
		+-----------+-------------+-------+

Subquery that appears after select (this content does not need to be mastered, just understand!!!)

Case: Find out the Department name of each employee, ask to show the employee name, Department name?
	select 
		e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
	from 
		emp e;


	+--------+--------+------------+
	| ename  | deptno | dname      |
	+--------+--------+------------+
	| SMITH  |     20 | RESEARCH   |
	| ALLEN  |     30 | SALES      |
	| WARD   |     30 | SALES      |
	| JONES  |     20 | RESEARCH   |
	| MARTIN |     30 | SALES      |
	| BLAKE  |     30 | SALES      |
	| CLARK  |     10 | ACCOUNTING |
	| SCOTT  |     20 | RESEARCH   |
	| KING   |     10 | ACCOUNTING |
	| TURNER |     30 | SALES      |
	| ADAMS  |     20 | RESEARCH   |
	| JAMES  |     30 | SALES      |
	| FORD   |     20 | RESEARCH   |
	| MILLER |     10 | ACCOUNTING |
	+--------+--------+------------+

//Error: ERROR 1242 (21000): Subquery returns more than 1 row
	select 
		e.ename,e.deptno,(select dname from dept) as dname
	from
		emp e;
	
	Note: For select For subsequent subqueries, this subquery can only return one result at a time, more than one result is wrong

union merge query result set

Case: Query the employees whose jobs are MANAGER and SALEESMAN?

	select ename,job from emp where job = 'MANAGER'
	union
	select ename,job from emp where job = 'SALESMAN';
	
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| JONES  | MANAGER  |
	| BLAKE  | MANAGER  |
	| CLARK  | MANAGER  |
	| ALLEN  | SALESMAN |
	| WARD   | SALESMAN |
	| MARTIN | SALESMAN |
	| TURNER | SALESMAN |
	+--------+----------+

union is more efficient. For table joins, every time a new table is joined, the number of matches satisfies the Cartesian product, doubling it.
However, union can reduce the number of matches. The splicing of two result sets can also be completed with fewer matches.

a Connect b Connect c
	a 10 Bar records
	b 10 Bar records
	c 10 Bar records
	The number of matches is: 1000

	a Connect b One result: 10 * 10 --> 100 second
	a Connect c One result: 10 * 10 --> 100 second
	Use union The words are: 100 times + 100 second = 200 Second. ( union Turn multiplication into addition)

Do you have any precautions when using union?

//Wrong: union requires the same number of columns in both result sets when doing a result set union.
	select ename,job from emp where job = 'MANAGER'
	union
	select ename from emp where job = 'SALESMAN';

	// MYSQL can, oracle syntax is strict, not, error. Requirements: The data types of columns and columns should also be the same when the result set is joined.
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,sal from emp where job = 'SALESMAN';
	+--------+---------+
	| ename  | job     |
	+--------+---------+
	| JONES  | MANAGER |
	| BLAKE  | MANAGER |
	| CLARK  | MANAGER |
	| ALLEN  | 1600    |
	| WARD   | 1250    |
	| MARTIN | 1250    |
	| TURNER | 1500    |
	+--------+---------+