mysql多表查询

周sir | 2016年11月3日 | 数据库 | 0 条评论 | 1711

多表查询就是在多个有逻辑联系的表之间进行的查询，逻辑关系主要是指主外键之间的联系，在主表中的某个字段的值取自另一个表中的一个字段，就是说主表中的一个字段的值包含于另一个表的一个字段的值的集合里。这样的话，如果要在主表里插入数据，就必须是对应的字段的值在相应的表中存在，才有可能插入成功，如果不存在，则肯定插入不成功（除非此字段可以为空）。要实现多表之间的查询就要依靠表连接或者是子查询的方式实现：

假设有两张表员工表和部门表,表结构如下:

[java]
部门表:

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`)
)

=================插入记录=========================

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 |
+—-+——+——+
[/java]

表连接查询：

普通多表查询：

[java] select 列1… from 表1，表2… where condition…[/java]
语法：在from之后可以有很多表，每个表之间用，隔开，from后面的表会做笛卡尔积
表与表之间做笛卡尔积，然后就能查询出来了
[java]
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 | 制订规章制度 |
+—-+——+——+—-+—————-+—————+—————+[/java]

内连接：

[java]select 列名1,… from 表1 inner join 表2 on 表1.列=表2.列，…condition…
[/java]
语法：from开始，表1与表2进行笛卡尔积，每匹配一行的数据，就会通过on后条件判断
是否成立，就将该行匹配的信息存放到临时表，否则不存放
[java]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 | 制订规章制度 |
+—-+——+——+—-+—————-+—————+—————+
[/java]

外连接：

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

[java]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 |
+——+—————-+[/java]

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

[java]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 | 销售部 |
+——+—————-+
[/java]

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

自连接：

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

[java]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=’刘主管’;
+——–+——–+
| 姓名 | 领导
+——–+——–+
| 刘主管 | 何经理
+——–+——–+[/java]

商城商品分类实例

[java]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)

查询女士下装分类的上级分类:

子查询：

在查询中还可以有查询。外查询叫主查询，内部的叫子查询
子查询先运行，子查询的结果作为主查询的值使用
–子查询返回单个值 =
–子查询返回多行一列 in
–子查询返回一行多列（，）in（，）
–子查询返回多行多列将条件一个一个拆开，然后将子查询得到的表在进行连接
连接之后可按下一步的条件进行查询，以此类推下去即可得到
符合条件的结果。在条件拆分的时候就是要月简单越好，并且
条件之间有相应的关系，最好是包含的关系，这样查询出来的
结果肯定是没有错的，并且在逻辑上也是很通的。也就各种子查询
并列或者是一个是另一个的子查询。

查询人事部所有员工姓名:
[java]
mysql> SELECT name AS 姓名 FROM employe AS e
-> WHERE e.d_id = (SELECT id FROM deparment AS d WHERE d.deparment_name=’人事部’);
+——+
| 姓名 |
+——+
| 张三 |
| 李四 |
| 王二 |
+——+
[/java]

注意：笛卡尔积又叫直积。
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。