Shulou(Shulou.com)06/01 Report--

1. background

* A multi-table union query is a way to group records from different tables

* Joins planned in the SQL standard are roughly internal joins, external joins, and full joins. Among them, the outer connection is divided into left outer connection and right outer connection.

2. inner join example [Employee--> Department]

* View employee table [ employees ] and department table [ departments ] structure

mysql> desc employees;+-----------+---------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+---------------+------+-----+---------+----------------+| id | bigint(20) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | || sex | enum('M','F') | NO | | NULL | || age | int(11) | NO | | NULL | || depart_id | bigint(20) | NO | | NULL | |+-----------+---------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> desc departments;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | bigint(20) | NO | PRI | NULL | auto_increment || name | varchar(64) | NO | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)

* View data from the employees table and departments table

mysql> select * from employees;+----+-------+-----+-----+-----------+| id | name | sex | age | depart_id |+----+-------+-----+-----+-----------+| 1 | tom | M | 25 | 1 || 2 | jak | F | 35 | 2 || 3 | lisea | M | 22 | 3 |+----+-------+-----+-----+-----------+3 rows in set (0.00 sec)mysql> select * from departments;+----+------+| id | name |+----+------+| 1 | dev || 2 | test || 3 | ops |+----+------+3 rows in set (0.00 sec)

* Query and display all employee id, name, surname, age, department (Method 1)

mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name -> from employees e, departments d -> where e.depart_id = d.id;+----+-------+--------+-----+------+| id | name | sex | age | name |+----+-------+--------+-----+------+| 1 | tom | male | 25 | dev || 2 | jak | female | 35 | test || 3 | lisea | male | 22 | ops |+----+-------+--------+-----+------+3 rows in set (0.03 sec)

* Query and display all employee id, name, surname, age, department (Method 2) [Method 1 is equivalent to Method 2]

Inner join can be omitted as join

mysql> select e.id id, e.name name, IF(e.sex = 'M', 'male', 'female') sex, e.age age, d.name -> from employees e inner join departments d -> on e.depart_id = d.id;+----+-------+--------+-----+------+| id | name | sex | age | name |+----+-------+--------+-----+------+| 1 | tom | male | 25 | dev || 2 | jak | female | 35 | test || 3 | lisea | male | 22 | ops |+----+-------+--------+-----+------+3 rows in set (0.00 sec)

3. Examples of outer connections

The left outer link [is based on the left table, all the data in the left table, and some combinations in the right table. None in the right table is null ]

Right outer join [based on the right table, all data in the right table, some combinations in the left table. None in the left table is null ]

* View table a and b structure

mysql> desc a;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| data | int(11) | YES | | NULL | |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> desc b;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| data | int(11) | YES | | NULL | |+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)

* View table a and b data

mysql> select * from a;+------+| data |+------+| 1 || 2 || 4 |+------+3 rows in set (0.00 sec)mysql> select * from b;+------+| data |+------+| 2 || 4 || 5 || 6 |+------+4 rows in set (0.01 sec)

* Left join query (left join) is based on table a, showing all data in table a, some combinations in table b, and not showing NULLL

Left outer join can be written as left join

mysql> select * from a left outer join b on a.data = b.data;+------+------+| data | data |+------+------+| 2 | 2 || 4 | 4 || 1 | NULL |+------+------+3 rows in set (0.00 sec)

* The right outer join query is based on table b and displays all data in table b. Some combinations in table a are not displayed.

Right outer join can be written as right join

mysql> select * from a right outer join b on a.data = b.data;+------+------+| data | data |+------+------+| 2 | 2 || 4 | 4 || NULL | 5 || NULL | 6 |+------+------+4 rows in set (0.00 sec)

* Complete (cross) join queries

A cross join without a where clause produces a Cartesian product of the tables involved in the join.

The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set.

(Cross-linking a and b yields 3*4=12 records)

mysql> select * from a corss join b;+------+------+| data | data |+------+------+| 1 | 2 || 2 | 2 || 4 | 2 || 1 | 4 || 2 | 4 || 4 | 4 || 1 | 5 || 2 | 5 || 4 | 5 || 1 | 6 || 2 | 6 || 4 | 6 |+------+------+12 rows in set (0.00 sec)

4. summary

Demand-driven technology, technology itself has no advantages, only business points.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.