所在位置：

mysql的多表查询

admin mysql mysql

在掌握了单表查询的基础再来学习多表查询是比较容易的，这一节不会分析单表查询和多表查询的性能，还是会用一些例子来说明多表查询的用法

创建表和测试数据

在本例子中，我们创建一个用户表，一个商品表，一个分类表来用演示子查询的各种用法：

DROP TABLE IF EXISTS `user`;
DROP TABLE IF EXISTS `category`;
DROP TABLE IF EXISTS `product`;

CREATE TABLE `user` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int(3) NOT NULL,
  `sex` varchar(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `category` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `name` varchar(64) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `name` varchar(64) NOT NULL,
    `price`  float,
    `user_id` int(3),
    `category_id` int(3),
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user(name, age, sex) VALUES('tom', 18, '男');
INSERT INTO user(name, age, sex) VALUES('andy', 28, '女');
INSERT INTO user(name, age, sex) VALUES('jim', 21, '女');
INSERT INTO user(name, age, sex) VALUES('lily', 20, '男');
INSERT INTO user(name, age, sex) VALUES('amy', 18, '男');
INSERT INTO user(name, age, sex) VALUES('Apple', 19, '女');
INSERT INTO user(name, age, sex) VALUES('keny', 22, '男');
INSERT INTO user(name, age, sex) VALUES('jimmy', 18, '男');

INSERT INTO category(name) VALUE('男装');
INSERT INTO category(name) VALUE('女装');
INSERT INTO category(name) VALUE('家电');
INSERT INTO category(name) VALUE('户外运动');
INSERT INTO category(name) VALUE('其它');

INSERT INTO product(name, price, user_id, category_id) VALUE('灯笼裤', 17.5, 1, 2);
INSERT INTO product(name, price, user_id, category_id) VALUE('女装牛仔', 20.5, 2, 2);
INSERT INTO product(name, price, user_id, category_id) VALUE('电视', 50.5, 1, 3);
INSERT INTO product(name, price, user_id, category_id) VALUE('电脑', 3000.0, 3, 3);
INSERT INTO product(name, price, user_id, category_id) VALUE('足球', 400.4, 1, 4);
INSERT INTO product(name, price, user_id, category_id) VALUE('手机', 5999.4, 5, 3);
INSERT INTO product(name, price, user_id, category_id) VALUE('男装裤子', 333.2, 2, 1);

子查询

IN子查询
SELECT * FROM 表1 WHERE 字段 IN (SELECT 字段 FROM 表2 [条件]);

举例： 查询用户名为 tom 的用户所购买的商品

SELECT * FROM product WHERE user_id IN (SELECT id FROM user WHERE name='tom');

或者使用内连接

SELECT product.* FROM user, product WHERE user.name='tom' AND user.id = product.user_id;

返回结果：

+----+-----------+-------+---------+-------------+
| id | name      | price | user_id | category_id |
+----+-----------+-------+---------+-------------+
|  1 | 灯笼裤    |  17.5 |       1 |           2 |
|  3 | 电视      |  50.5 |       1 |           3 |
|  5 | 足球      | 400.4 |       1 |           4 |
+----+-----------+-------+---------+-------------+

注意： 表1中 WHERE 的 字段 必须要跟 表2 中 SELECT 的字段 类型相同

ANY 或者 SOME 子查询
SELECT * FROM 表1 WHERE 字段 > [ANY|SOME] (SELECT 字段 FROM 表2 [条件]);

说明： 只要表1中的字段大于表2中字段的任意一个值就可以

举例： 查询在产品中用户的 user_id 大于任意一个在用户中的 id 的所有产品

SELECT * FROM product WHERE user_id > ANY (SELECT id FROM USER) ORDER BY user_id;

返回结果：

+----+--------------+--------+---------+-------------+
| id | name         | price  | user_id | category_id |
+----+--------------+--------+---------+-------------+
|  2 | 女装牛仔     |   20.5 |       2 |           2 |
|  7 | 男装裤子     |  333.2 |       2 |           1 |
|  4 | 电脑         |   3000 |       3 |           3 |
|  6 | 手机         | 5999.4 |       5 |           3 |
+----+--------------+--------+---------+-------------+

注意： 表1中 WHERE 的 字段 必须要跟 表2 中 SELECT 的字段 类型相同

EXISTS 或者 NOT EXISTS 子查询
SELECT * FROM 表1 WHERE EXISTS (SELECT * FROM 表2 [条件]);

举例： 查询哪些用户买了女装

SELECT name FROM user WHERE EXISTS (SELECT * FROM category, product WHERE product.category_id = category.id AND category.name = '女装' AND user.id = product.user_id);

返回结果：

+------+
| name |
+------+
| tom  |
| andy |
+------+
UNION 子查询
SELECT * FROM 表1 UNION [ALL] SELECT * FROM 表2;

举例： 把两个类似的表合并

先根据 user 表创建另外一个表

CREATE TABLE user1 like user;

INSERT INTO user1(name, age, sex) VALUES('tom', 18, '男');
INSERT INTO user1(name, age, sex) VALUES('marry', 39, '女');

SELECT * FROM user UNION SELECT * FROM user1;

返回结果：

+----+-------+-----+-----+
| id | name  | age | sex |
+----+-------+-----+-----+
|  1 | tom   |  18 | 男  |
|  2 | andy  |  28 | 女  |
|  3 | jim   |  21 | 女  |
|  4 | lily  |  20 | 男  |
|  5 | amy   |  18 | 男  |
|  6 | Apple |  19 | 女  |
|  7 | keny  |  22 | 男  |
|  8 | jimmy |  18 | 男  |
|  2 | marry |  39 | 女  |
+----+-------+-----+-----+

注意： 我们看到了用户 tome 只显示了一条，说明有去重的功能，如果不想有去重功能的话，可以使用 UNION ALL

显示字段使用子查询
SELECT 字段1, 字段2, (子查询) as 字段3 FROM 表1 [条件];

举例： 统计用户购买商品的总数，并且商品总数是大于0的用户信息

SELECT NAME, AGE, SEX, (SELECT COUNT(*) FROM product WHERE user.id=product.user_id) AS total FROM user HAVING total > 0 ORDER BY total DESC;

+-------+-----+-----+-------+
| name  | age | sex | total |
+-------+-----+-----+-------+
| tom   |  18 | 男  |     3 |
| andy  |  28 | 女  |     2 |
| amy   |  18 | 男  |     1 |
| jim   |  21 | 女  |     1 |
+-------+-----+-----+-------+

内连接

简单写法
SELECT 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 FROM 表1, 表2 [条件];

举例： 查询在分类表中有商品的所有信息，如果某商品不存在的分类不显示出来

SELECT * FROM category, product WHERE category.id = product.category_id ORDER BY category.id;

返回结果：

+----+--------------+----+--------------+--------+---------+-------------+
| id | name         | id | name         | price  | user_id | category_id |
+----+--------------+----+--------------+--------+---------+-------------+
|  1 | 男装         |  7 | 男装裤子     |  333.2 |       2 |           1 |
|  2 | 女装         |  1 | 灯笼裤       |   17.5 |       1 |           2 |
|  2 | 女装         |  2 | 女装牛仔     |   20.5 |       2 |           2 |
|  3 | 家电         |  6 | 手机         | 5999.4 |       5 |           3 |
|  3 | 家电         |  3 | 电视         |   50.5 |       1 |           3 |
|  3 | 家电         |  4 | 电脑         |   3000 |       3 |           3 |
|  4 | 户外运动     |  5 | 足球         |  400.4 |       1 |           4 |
+----+--------------+----+--------------+--------+---------+-------------+

说明： 从上表中可以看到，把一个表的所有行跟另外一个表的所有行进行匹配，如果 category.id = product.category.id ，就会组成一条数据

注意： 如果 表1 和 表2 的字段 不相同，字段前面可以不写表名

标准写法
SELECT 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 FROM 表1 INNER JOIN 表2 [条件];

举例： 查询在分类表中有商品的所有信息，如果某商品不存在的分类不显示出来

SELECT * FROM category INNER JOIN product ON category.id = product.category_id ORDER BY category.id;

返回结果：

+----+--------------+----+--------------+--------+---------+-------------+
| id | name         | id | name         | price  | user_id | category_id |
+----+--------------+----+--------------+--------+---------+-------------+
|  1 | 男装         |  7 | 男装裤子     |  333.2 |       2 |           1 |
|  2 | 女装         |  1 | 灯笼裤       |   17.5 |       1 |           2 |
|  2 | 女装         |  2 | 女装牛仔     |   20.5 |       2 |           2 |
|  3 | 家电         |  6 | 手机         | 5999.4 |       5 |           3 |
|  3 | 家电         |  3 | 电视         |   50.5 |       1 |           3 |
|  3 | 家电         |  4 | 电脑         |   3000 |       3 |           3 |
|  4 | 户外运动     |  5 | 足球         |  400.4 |       1 |           4 |
+----+--------------+----+--------------+--------+---------+-------------+

说明： 从上表中可以看到，把一个表的所有行跟另外一个表的所有行进行匹配，如果 category.id = product.category.id ，就会组成一条数据

注意： 如果 表1 和 表2 的字段 不相同，字段前面可以不写表名

外连接

左外连接
SELECT 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 FROM 表1 LEFT OUTER JOIN 表2 [条件];

举例： 查询在分类表中有产品的所有信息，不管在商品中有没有该分类

SELECT * FROM category LEFT OUTER JOIN product ON category.id = product.category_id ORDER BY category.id;

或者可以使用 右外连接，只要两边的位置交接，这两个语句就是相等的

SELECT * FROM product RIGHT OUTER JOIN category ON category.id = product.category_id ORDER BY category.id;

返回结果：

+----+--------------+------+--------------+--------+---------+-------------+
| id | name         | id   | name         | price  | user_id | category_id |
+----+--------------+------+--------------+--------+---------+-------------+
|  1 | 男装         |    7 | 男装裤子     |  333.2 |       2 |           1 |
|  2 | 女装         |    1 | 灯笼裤       |   17.5 |       1 |           2 |
|  2 | 女装         |    2 | 女装牛仔     |   20.5 |       2 |           2 |
|  3 | 家电         |    6 | 手机         | 5999.4 |       5 |           3 |
|  3 | 家电         |    3 | 电视         |   50.5 |       1 |           3 |
|  3 | 家电         |    4 | 电脑         |   3000 |       3 |           3 |
|  4 | 户外运动     |    5 | 足球         |  400.4 |       1 |           4 |
|  5 | 其它         | NULL | NULL         |   NULL |    NULL |        NULL |
+----+--------------+------+--------------+--------+---------+-------------+

说明： 左外连接就是以左边为准，所以以 category 表为准，把 category 的各行跟 product 的各行匹配，如果不匹配也会显示出来，不过，右边表的显示信息为 NULL

注意： 如果 表1 和 表2 的字段 不相同，字段前面可以不写表名

右外连接
SELECT 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 FROM 表1 RIGHT OUTER JOIN 表2 [条件];

举例： 查询在所有产品的所有分类，不管在商品中有没有该分类

SELECT * FROM category RIGHT OUTER JOIN product ON category.id = product.category_id ORDER BY category.id;

或者可以使用 右外连接，只要两边的位置交接，这两个语句就是相等的

SELECT * FROM product LEFT OUTER JOIN category ON category.id = product.category_id ORDER BY category.id;

返回结果：

+------+--------------+----+--------------+--------+---------+-------------+
| id   | name         | id | name         | price  | user_id | category_id |
+------+--------------+----+--------------+--------+---------+-------------+
|    1 | 男装         |  7 | 男装裤子     |  333.2 |       2 |           1 |
|    2 | 女装         |  2 | 女装牛仔     |   20.5 |       2 |           2 |
|    2 | 女装         |  1 | 灯笼裤       |   17.5 |       1 |           2 |
|    3 | 家电         |  6 | 手机         | 5999.4 |       5 |           3 |
|    3 | 家电         |  4 | 电脑         |   3000 |       3 |           3 |
|    3 | 家电         |  3 | 电视         |   50.5 |       1 |           3 |
|    4 | 户外运动     |  5 | 足球         |  400.4 |       1 |           4 |
+------+--------------+----+--------------+--------+---------+-------------+

说明： 右外连接就是以右边为准，所以以 product 表为准，把 product 的各行跟 category 的各行匹配，如果不匹配也会显示出来，不过，左边表的显示信息为 NULL

注意： 如果 表1 和 表2 的字段 不相同，字段前面可以不写表名

自连接

SELECT b.字段1, b.字段2 FROM 表1 as a, 表1 as b [条件];

举例： 查询在商品价格大于足球的价格的所有商品

SELECT b.* FROM product AS a, product AS b WHERE a.name='足球' AND a.price < b.price;

或者可以使用子连接

SELECT * FROM product WHERE price > (SELECT price FROM product WHERE name='足球');

返回结果：

+----+--------+--------+---------+-------------+
| id | name   | price  | user_id | category_id |
+----+--------+--------+---------+-------------+
|  4 | 电脑   |   3000 |       3 |           3 |
|  6 | 手机   | 5999.4 |       5 |           3 |
+----+--------+--------+---------+-------------+

【上一篇】mysql的单表查询

【下一篇】自动电子书发送到kindle

相关文章