(1) Preparation environment 1) Create an employee table
mysql> create table company.employee6( -> emp_id int auto_increment primary key not null, -> emp_name varchar(10), -> age int, -> dept_id int);mysql> insert into employee6(emp_name,age,dept_id) values -> (‘tom‘,19,200), -> (‘jack‘,30,201), -> (‘alice‘,24,202), -> (‘robin‘,40,200), -> (‘natasha‘,28,204);
2) Create a departmental table
mysql> create table company.department( -> dept_id int, -> dept_name varchar(100));mysql> insert into department values (200,‘hr‘), (201,‘it‘), (202,‘sale‘), (203,‘fd‘);
(2) Cross connection
Generate Cartesian product with no matching criteria
Syntax: Select table 1. field 1, table 1. Field 2, table 2. Field 1 from Table 1, table 2;
Mysql> Select Employee6.emp_name,employee6.age,employee6.dept_id,department.dept_name from Employee6,department ;+----------+------+---------+-----------+| Emp_name | Age | dept_id | Dept_name |+----------+------+---------+-----------+| Tom | 19 | 200 | HR | | Tom | 19 | 200 | it | | Tom | 19 | 200 | Sale | | Tom | 19 | 200 | FD | | Jack | 30 | 201 | HR | | Jack | 30 | 201 | it | | Jack | 30 | 201 | Sale | | Jack | 30 | 201 | FD | | Alice | 24 | 202 | HR | | Alice | 24 | 202 | it | | Alice | 24 | 202 | Sale | | Alice | 24 | 202 | FD | | Robin | 40 | 200 | HR | | Robin | 40 | 200 | it | | Robin | 40 | 200 | Sale | | Robin | 40 | 200 | FD | | Natasha | 28 | 204 | HR | | Natasha | 28 | 204 | it | | Natasha | 28 |204 | Sale | | Natasha | 28 | 204 | FD |+----------+------+---------+-----------+
(3) Inner connection: Only matching rows are connected according to the same field in the two tables
Syntax: Select table 1. field N, table 2. field n from table 1, table 2 table 1. field = table 2. Field
Connect based on the dept_id of the employee table and the dept_id of the departmental table, matching only the same rows dept_id
mysql> select employee6.dept_id,employee6.emp_name,employee6.age,department.dept_name from employee6,department where employee6.dept_id = department.dept_id;+---------+----------+------+-----------+| dept_id | emp_name | age | dept_name |+---------+----------+------+-----------+| 200 | tom | 19 | hr || 201 | jack | 30 | it || 202 | alice | 24 | sale || 200 | robin | 40 | hr |+---------+----------+------+-----------+
(4) External connection
Syntax: Select field List from table 1 left|right join table 2 on table 1. field = table 2. Field
1) left side connection: Displays all values in the left table, regardless of the match on the right table
mysql> select emp_id,emp_name,age,dept_name from employee6 left join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age | dept_name |+--------+----------+------+-----------+| 1 | tom | 19 | hr || 4 | robin | 40 | hr || 2 | jack | 30 | it || 3 | alice | 24 | sale || 5 | natasha | 28 | NULL |+--------+----------+------+-----------+
2) Right connection of outer connection: All values in the right table will be displayed, regardless of the table in the left
mysql> select emp_id,emp_name,age,dept_name from employee6 right join department on employee6.dept_id = department.dept_id;+--------+----------+------+-----------+| emp_id | emp_name | age | dept_name |+--------+----------+------+-----------+| 1 | tom | 19 | hr || 2 | jack | 30 | it || 3 | alice | 24 | sale || 4 | robin | 40 | hr || NULL | NULL | NULL | fd |+--------+----------+------+-----------+
(5) Compound Condition connection Query
The EMPLOYEE6 and department tables are queried in a connected way, and the age field value in the Employee6 table must be greater than 25, sort
Mysql> Select Emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25;+--------+----------+------+-----------+| emp_id | Emp_name | Age | Dept_name |+--------+----------+------+-----------+| 4 | Robin | 40 | HR | | 2 | Jack | 30 | It |+--------+----------+------+-----------+2 rows in Set (0.00 sec) mysql> Select Emp_id,emp_name,age,dept_name From employee6,department where employee6.dept_id = department.dept_id and age >25 ORDER by age;+--------+----------+- -----+-----------+| emp_id | Emp_name | Age | Dept_name |+--------+----------+------+-----------+| 2 | Jack | 30 | it | | 4 | Robin | 40 | HR |+--------+----------+------+-----------+2 rows in Set (0.00 sec) mysql> Select Emp_id,emp_name,age,dept_name From employee6,department where employee6.dept_id = department.dept_id and age >25 order by age desc;+--------+-------- --+------+-----------+|emp_id | Emp_name | Age | Dept_name |+--------+----------+------+-----------+| 4 | Robin | 40 | HR | | 2 | Jack | 30 | It |+--------+----------+------+-----------+
(6) Sub-query
A subquery is a query statement that is nested within another query statement. The query result of the inner query statement can provide the query condition for the outer query statement.
1) Sub-query with in
mysql> select * from employee6 where dept_id in (select dept_id from department);+--------+----------+------+---------+| emp_id | emp_name | age | dept_id |+--------+----------+------+---------+| 1 | tom | 19 | 200 || 2 | jack | 30 | 201 || 3 | alice | 24 | 202 || 4 | robin | 40 | 200 |+--------+----------+------+---------+4 rows in set (0.00 sec)
2) Sub-query with comparison operator
mysql> select dept_name from department where dept_id in (select dept_id from employee6 where age >25); +-----------+| dept_name |+-----------+| it || hr |+-----------+2 rows in set (0.00 sec)
(vii) MySQL data operation DQL: Multi-table Query 2