关于数据库mysql多表查询

多表查询

定义：多表查询就是在多个有逻辑联系的表之间进行的查询，逻辑关系主要是指主外键的联系。

要实现多表之间的查询就要依靠表连接或者是子查询的方式实现：

假设有两张表，分别是员工表和部门表，表结构如下：

部门表: CREATE TABLE `deparment` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `deparment_name` varchar(255) NOT NULL,  `deparment_num` varchar(255) NOT NULL,  `deparment_des` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`) )  mysql> desc deparment;+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| id             | int(11)      | NO   | PRI | NULL    | auto_increment || deparment_name | varchar(255) | NO   |     | NULL    |                || deparment_num  | varchar(255) | NO   |     | NULL    |                || deparment_des  | varchar(255) | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+=================插入记录=========================INSERT INTO `deparment` VALUES ('1', '人事部', 'A1001', '处理人事关系');INSERT INTO `deparment` VALUES ('2', '财务部', 'B2001', '管理公司资产');INSERT INTO `deparment` VALUES ('3', '行政部', 'C3001', '制订规章制度');mysql> select * from employe;+----+------+------+| id | name | d_id |+----+------+------+|  5 | 张三     |    1 ||  6 | 李四     |    1 ||  7 | 王二     |    1 ||  8 | 麻子     |    2 ||  9 | 小明    |    3 |+----+------+------+ 员工表: CREATE TABLE `employe` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) NOT NULL,  `d_id` int(11) NOT NULL DEFAULT '1',  PRIMARY KEY (`id`),  KEY `fk` (`d_id`),) mysql> desc employe;+-------+--------------+------+-----+---------+----------------+| Field | Type         | Null | Key | Default | Extra          |+-------+--------------+------+-----+---------+----------------+| id    | int(11)      | NO   | PRI | NULL    | auto_increment || name  | varchar(255) | NO   |     | NULL    |                || d_id  | int(11)      | NO   | MUL | 1       |                |+-------+--------------+------+-----+---------+----------------+=================插入记录=========================INSERT INTO `employe` VALUES ('5', '张三', '1');INSERT INTO `employe` VALUES ('6', '李四', '1');INSERT INTO `employe` VALUES ('7', '王二', '1');INSERT INTO `employe` VALUES ('8', '麻子', '2');INSERT INTO `employe` VALUES ('9', '小明', '3');mysql> select * from employe;+----+------+------+| id | name | d_id |+----+------+------+|  5 | 张三     |    1 ||  6 | 李四     |    1 ||  7 | 王二     |    1 ||  8 | 麻子     |    2 ||  9 | 小明    |    3 |+----+------+------+

表连接查询：

普通多表查询：

select 列1... from 表1，表2... where condition...

语法： 在from之后可以有很多表，每个表之间用，隔开，from后面的表会做笛卡尔积
表与表之间做笛卡尔积，然后就能查询出来了

mysql> select * from employe,deparment where employe.d_id = deparment.id;+----+------+------+----+----------------+---------------+---------------+| id | name | d_id | id | deparment_name | deparment_num | deparment_des |+----+------+------+----+----------------+---------------+---------------+|  5 | 张三     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  6 | 李四     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  7 | 王二     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  8 | 麻子     |    2 |  2 | 财务部               | B2001         | 管理公司资产           ||  9 | 小明    |    3 |  3 | 行政部               | C3001         | 制订规章制度            |+----+------+------+----+----------------+---------------+---------------+

内连接：

select 列名1,... from 表1 inner join 表2 on 表1.列=表2.列，...condition...

语法：from开始，表1与表2进行笛卡尔积，每匹配一行的数据，就会通过on后条件判断
是否成立，就将该行匹配的信息存放到临时表，否则不存放

mysql> SELECT * FROM employe AS e INNER JOIN deparment AS d ON e.d_id = d.id;+----+------+------+----+----------------+---------------+---------------+| id | name | d_id | id | deparment_name | deparment_num | deparment_des |+----+------+------+----+----------------+---------------+---------------+|  5 | 张三     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  6 | 李四     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  7 | 王二     |    1 |  1 | 人事部              | A1001         | 处理人事关系            ||  8 | 麻子     |    2 |  2 | 财务部               | B2001         | 管理公司资产           ||  9 | 小明    |    3 |  3 | 行政部               | C3001         | 制订规章制度            |+----+------+------+----+----------------+---------------+---------------+

外连接：

左外连接：左表中所有的记录都会被放到结果`集中，无论是否在右表是否存在匹配记录
语法：select 列 from 表1 left outer join 表2 on 表1.列=表2.列
[left outer join 表3 on…]

mysql> SELECT e.name,d.deparment_name FROM employe AS e    -> LEFT OUTER JOIN deparment AS d    -> ON e.d_id = d.id;+------+----------------+| name | deparment_name |+------+----------------+| 张三     | 人事部              || 李四     | 人事部              || 王二     | 人事部              || 麻子     | 财务部               || 小明    | 行政部               || 小丽    | NULL           |+------+----------------+

右外连接：不管是否成功匹配连接条件都会返回右表中的所有记录
语法： select 列 from 表1 right outer join 表2 on 表1.列=表2.列
[right outer join 表3 on…]

mysql> SELECT e.name,d.deparment_name FROM employe AS e    -> RIGHT OUTER JOIN deparment AS d    -> ON e.d_id = d.id;+------+----------------+| name | deparment_name |+------+----------------+| 张三     | 人事部              || 李四     | 人事部              || 王二     | 人事部              || 麻子     | 财务部               || 小明    | 行政部               || NULL | 销售部              |+------+----------------+

全外连接：在mysql中没有全外连接

自连接：

一张表与自己进行连接，
ex:查找每个员工的直接上级领导的姓名

mysql> CREATE TABLE people(    -> id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> name varchar(50) NOT NULL,    -> parent_id int DEFAULT 0);mysql> DESC people;+-----------+-------------+------+-----+---------+----------------+| Field     | Type        | Null | Key | Default | Extra          |+-----------+-------------+------+-----+---------+----------------+| id        | int(11)     | NO   | PRI | NULL    | auto_increment || name      | varchar(50) | NO   |     | NULL    |                || parent_id | int(11)     | YES  |     | 0       |                |+-----------+-------------+------+-----+---------+----------------+mysql> SELECT * FROM people;+----+--------+-----------+| id | name   | parent_id |+----+--------+-----------+|  1 | 范总       |         0 ||  2 | 何经理      |         1 ||  3 | 麻主管       |         2 ||  4 | 刘主管       |         2 ||  5 | 李高级      |         3 ||  6 | 王中级      |         3 ||  7 | 张初级      |         4 |+----+--------+-----------+查询刘主管的上级领导:mysql> SELECT s.name AS 姓名,f.name AS 领导 FROM people AS s    -> INNER JOIN people AS f    -> ON s.parent_id = f.id    -> AND s.name='刘主管';+--------+--------+| 姓名    | 领导    +--------+--------+| 刘主管  | 何经理    +--------+--------+

商城商品分类实例

mysql> select * from product;+------------+----------------+-------------------+| product_id | product_name   | product_parent_id |+------------+----------------+-------------------+|          1 | 天猫商品             |                 0 ||          2 | 女装/内衣            |                 1 ||          3 | 男装/运动户外            |                 1 ||          4 | 家具建材             |                 1 ||          5 | 汽车/配件/用品            |                 1 ||          6 | 图书音像              |                 1 ||          7 | 当季流行               |                 2 ||          8 | 精选上装             |                 2 ||          9 | 浪漫裙装             |                 2 ||         10 | 女士下装            |                 2 ||         11 | 成套家具             |                 4 ||         12 | 客厅餐厅               |                 4 |+------------+----------------+-------------------+12 rows in set (0.02 sec)查询女士下装分类的上级分类:mysql> SELECT s.product_name, f.product_name FROM product AS s    -> INNER JOIN product AS f    -> ON s.product_parent_id = f.product_id    -> AND s.product_name='女士下装';+--------------+--------------+| product_name | product_name |+--------------+--------------+| 女士下装          | 女装/内衣          |+--------------+--------------+

子查询：

在查询中还可以有查询。外查询叫主查询，内部的叫子查询
子查询先运行，子查询的结果作为主查询的值使用

查询人事部所有员工姓名:

mysql> SELECT name AS 姓名 FROM employe AS e    -> WHERE e.d_id = (SELECT id FROM deparment AS d WHERE d.deparment_name='人事部');+------+| 姓名     |+------+| 张三     || 李四     || 王二     |+------+