多表查询
定义:
多表查询就是在多个有逻辑联系的表之间进行的查询,逻辑关系主要是指主外键的联系。
要实现多表之间的查询就要依靠表连接或者是子查询的方式实现:
假设有两张表,分别是员工表和部门表,表结构如下:
部门表:
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='人事部');
+------+
| 姓名 |
+------+
| 张三 |
| 李四 |
| 王二 |
+------+