One ： precondition ： database

# Create department table 
CREATE TABLE dept(
 did INT(11) PRIMARY KEY AUTO_INCREMENT,
  dname VARCHAR(20), des VARCHAR(30) 
  );

INSERT INTO dept(dname,des)VALUES(" R & D department "," Lu code "),(" The executive branch "," Growing flowers ") ,
(" The personnel department "," Pressure wage ")
	



# Create an employee table  
CREATE TABLE emp( 
eid INT(11) PRIMARY KEY AUTO_INCREMENT, 
ename VARCHAR(20), 
esal DOUBLE, edesc VARCHAR(20) ,
did int (20));


INSERT INTO emp(ename,esal,edesc,did)VALUES
(" ma ",2000," ha-ha ",1) ,
(" Jack ma, ",3000," ha-ha ",1),
(" SuHua ",4000,"kk",2),
(" Qiang Dong Liu ",5000,"ndn",2), 
(" Cong Ge ",6000," Wanghong is a ",3),
(" Wu queer ",9000," Eighteen year old girl ",3)

II. Non equivalent query ：

1. grammar :
select * from surface 1, surface 2
2. Case study :
# Search for employee information And department information
SELECT d.,e. FROM dept d,emp e

result ：

summary ： As shown above , All in all 18 Data , These data are
dept： Table 3 data
emp: Table 6 data ,
That is, each row of each table multiplies each other , Cartesian product 3*6 =18

defects ： Such a connection will cause a lot of errors ：, Pictured

3、 ... and . Link query

1. Display inner connection
Concept : Use mysql in inner join Keyword for connection query Is to show the internal connection
# Query the information of all employees and corresponding departments
SELECT d.,e. FROM dept d INNER JOIN emp e ON d.did=e.did
.
.
2. Implicit inner join
Concept : Don't use inner join Keyword for connection query Is implicit inner connection , use where Conditional substitution
SELECT d.,e. FROM dept d,emp e WHERE d.did=e.did

The query results of display connection and implicit connection are shown in the figure ：

Four ： The left outer join

1. grammar ：select * from The left table left join Right table on Matching condition
2. Matching rules ： Based on the left table The right table matches one by one If it can match Then the two tables display If you can't match , The left table still shows The right table is set to null
# Whether there are employees in the department or not You need to find out the information of the Department
SELECT d.,e. FROM dept d LEFT JOIN emp e ON d.did=e.did

5、 ... and ： Right connection

1. grammar :select * from The left table right join Right table on Matching condition
2. Matching rules ： Based on the right The left table matches one by one If it can match Then the two tables display If you can't match The right table still shows The left table is set to null
3. Query the information of all employees Whether there is a corresponding department
SELECT d.,e. FROM dept d RIGHT JOIN emp e ON d.did=e.did

6、 ... and . Subquery

1. Concept : One query statement is nested with another completed query statement
2. Two embodiments :A. The result of one subquery is used as the condition of another query statement B. The result of a query statement is currently another query Sentence column to use

The first embodiment :

A. Query all employees in the R & D department
B. analysis : a. Check all employees b. The Department found is the R & D department c. The associated condition did
SELECT * FROM emp e WHERE e.did IN(SELECT d.did FROM dept d WHERE d.dname=“ R & D department ” )

The second embodiment

A. Find out the information of all employees And the name of the Department
B. analysis : Query the information of all employees b. Find out the name of the Department c. The associated condition did
C.SELECT e.*,(SELECT d.dname FROM dept d WHERE d.did=e.did) AS dname FROM emp e