Data preparation

Create table department(id int, name varchar(20)); create table staff( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); Insert into department values (200,' team '), (201,' human resources '), (202,' sales '), (203,' operation ') Insert into staff (name, sex, age, dep_id) values (' cheng bite gold 'and' male ', 38200), (" luna ", 'female', 26201), (' li bai 'and' male ', 38201). (' the temple ', the 'female', 28202), (' dian wei 'and' male ', 118200), (' coach ', 'female', 16204); Mysql > select * from 'mysql'; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.06 SEC) mysql > desc staff; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra  | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11)  | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.04 SEC) # table department and staff mysql> select * from department; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 200 | mining unit | | 201 | human resources | | 202 | sales | | 203 | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC) mysql > select * from the staff; +----+-----------+--------+------+--------+ | id | name | sex | age | dep_id | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | | cheng bite gold male 38 | 200 | | | | | luna 2 female 26 | | 201 | | 3 | li bai | male 38 | | 201 | | | | wang zhaojun 4 female 28 202 | | | | | 5 dian wei | male | 118 | 200 | | 6 | | coach getting 16 | 204 | | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 6 rows in the set (0.00 SEC)Copy the code

Check the table

  • The cross-join does not apply to any matching conditions and produces a Cartesian product

  • Select * from table1, table2;

  • Inner links (common) join only matching rows

  • Select * from staff inner join department on Fields = Table 2. Fields)

  • Summary: find the common part of two tables, and use the condition to screen out the correct result from cartesian product results

  • Outer join

  • Left outer join (common) Displays all records in the left table first
  • Select * from staff left join department on condition (table 1) Fields = Table 2. Fields)

  • The right outer link preferentially displays all records in the right table right Join
  • Select * from staff right join department on condition (Table 1) Fields = Table 2. Fields)

  • The full external join displays all records of the left and right tables
  • Mysql > select * from ‘full join’; select * from ‘full join’;
Select * from table1 left join table2 Union select * from table 1 right join table 2 on condition ((table 1) Field = table 2. Field));Copy the code

Mysql does not support full join; mysql does not support full join; mysql does not support full join; mysql does not support full join. The union will remove the same recordCopy the code
  • practice
1. Find all the employees of the mining team. Mysql > select * from staff inner join department on department.id=staff.dep_id where department.name=' '; +----+-----------+------+------+--------+------+-----------------+ | id | name | sex | age | dep_id | id | name | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | cheng bite gold male | | 200 | | 200 38 mining unit | | | 5 dian wei | male | 118 | 200 | 200 | mining unit | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC) 2. Search hr for all employee names Mysql > select staff.name from staff inner join department as dep on dep.id=staff.dep_id WHERE mysql> select staff.name from staff inner join department as dep on dep.id=staff Dep. name=' human resources '; + -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- + | | luna li bai | | + -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC). 3. Find the name of the employee who is older than 38 and the name of the department. mysql> select staff.name,dep.name from staff inner join department as dep on dep.id=staff.dep_id where age>38; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | name | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | dian wei | mining unit | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 Row in set (0.00 SEC) 4 Select * from staff; select * from staff; select * from staff; mysql> select * from staff inner join department as dep on dep.id=staff.dep_id order by age; +----+-----------+--------+------+--------+------+-----------------+ | id | name | sex | age | dep_id | id | name | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | luna 2 female 26 201 | | 201 | | human resources | | | 4 Wang zhaojun | getting 28 202 | | 202 | | sales | | 1 | | cheng bite gold male | | 200 | | 200 38 mining unit | | 3 | li bai | male | | 201 | | 201 38 human resources | | | 5 dian wei | male | 118 | 200 | 200 | mining unit | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 Rows in set (0.05 SEC) 5. Find the names of all employees in the department for mining teams and human resources mysql> select staff.name from staff inner join department as dep on dep.id = staff.dep_id where dep.name in (' Mining team ',' Human Resources '); + -- -- -- -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- -- -- -- + | cheng yaojin | | luna | | li bai | | dian wei | + -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

The subquery

#1: A subquery is a query statement nested within another query statement. #2: the result of the inner query statement can provide the query conditions for the outer query statement. A subquery can contain the following keywords: IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS =, >, < etc #5: use continuous table query, because continuous table query is more efficient than subquery 1. Mysql > select id from department where name = 'sales '; mysql> select id from department where name =' sales '; + -- -- -- -- -- -- + | id | + -- -- -- -- -- -- + | | + -- -- -- -- -- - 202 + 1 row in the set (0.00 SEC), (2) in the table to find the staff department dep_id = 202 mysql > select the name from the staff  where dep_id = 202; + -- -- -- -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- -- -- -- + | | wang zhaojun + -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC), (3) the word table check mysql > select the name from the staff Where dep_id =(select id from department where name = 'sales '); + -- -- -- -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- -- -- -- + | | wang zhaojun + -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) 2. Mysql > select id from department where name = 'sales' or name from department where name =' sales' or name = 'human resources '; + -- -- -- -- -- -- + | | + -- -- -- -- -- - id + | 201 | | 202 | + -- -- -- -- -- - + 2 rows in the set (0.00 SEC), (2) the subquery mysql > select the name from the staff where Dep_id in (select id from department where name = 'sales' or name =' human resources '); + -- -- -- -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- -- -- -- + | | luna li bai | | | | wang zhaojun + -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

Select id,name from department where id IN (select dep_id from staff group by dep_id having avg(age) > 28); # results + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id name | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 200 | mining unit | | 201 | | human resources +------+-----------------+ 2 rows in set (0.00 SEC) select name from staff where dep_id in (select id From department where name=' 1 '); Result # + -- -- -- -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- -- -- -- + | | cheng bite gold | dian wei | + -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC) Select name from department where id not in (select distinct dep_id from staff); select * from department where id not in (select dep_id from staff); Result # + -- -- -- -- -- -- -- -- + | name | + -- -- -- -- -- -- -- -- + | | + -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC) 2. Subqueries with comparison operators # comparison operators: =,! Mysql > select name,age from staff where age > (select avg(age) from staff where age > (select avg(age) from staff); + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | age | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- + | dian wei | 118 | + -- -- -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC) Select t1.name,t1.age from staff T1 inner join (select dep_id,avg(age) avg_age from staff group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; Result # + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | name | age | + -- -- -- -- -- -- -- -- + | + -- -- -- -- -- - li bai 38 | | | dian wei | 118 | + -- -- -- -- -- -- -- - + -- -- -- -- -- - + 2 rows in the set (0.04) sec) 3. A subquery with the EXISTS key The EXISTS key indicates that it EXISTS. When the EXISTS keyword is used, the inner query statement does not return the query record. Instead, it returns a true or false value. True or False When True is returned, the outer query will be queried. Select * from dept_id where dept_id=203; Ture select * from staff where exists (select id from department where id=200); # + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | id name | sex | age | dep_id | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | 1 | | cheng bite gold male 38 | 200 | | | | | luna 2 female 26 | | 201 | | 3 | li bai | male 38 | | 201 | | | | wang zhaojun 4 female 28 202 | | | | | 5 dian wei | male | 118 | 200 | | 6 | | coach getting 16 | 204 | | + - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + 6 rows in the set # (0.00 SEC) existing in the department table dept_id = 205, False mysql> select * from staff where exists (select id from department where id=204); The Empty set (0.00 SEC)Copy the code