mysql多表查询的技巧（07-查询操作DQL-多表查询）

mysql多表查询的技巧（07-查询操作DQL-多表查询）1. 笛卡尔积 1 CREATE TABLE `product` ( 2 `id` bigint(11) NOT NULL AUTO_INCREMENT 3 `productName` varchar(50) DEFAULT NULL 4 `dir_id` bigint(11) DEFAULT NULL 5 `salePrice` double(10 2) DEFAULT NULL 6 `supplier` varchar(50) DEFAULT NULL 7 `brand` varchar(50) DEFAULT NULL 8 `cutoff` double(2 2) DEFAULT NULL 9 `costPrice` double(10 2) DEFAULT NULL 10 PRIMARY KEY (`id`) 11 ) ENGINE=MyISAM A

一. 综述

查询操作主要从两个方面来说：单表查询和多表查询。 多表查询包括：笛卡尔积、外键约束、内连接查询、外链接查询、自连接查询。

二 . 案例设计

1. 设计产品表（product）。包括：主键id、产品名称（productName）、分类编号（dir_id）、零售价（salePrice）、供应商（supplier）、品牌（brand）、折扣（cutoff）、成本价（costPrice）。

设计产品产品编号表( productdir)。 包括：主键id、编号名称( dirName)、父id( parent_id) 。

设计产品库存表( productstock)。包括：主键id、产品id( product_id )、库存数量( storeNum)、上次进库时间（lastIncomeDate）、上次出库时间( lastOutcomeDate)、预警数量（warningNum）。

对应的SQL语句：

1 CREATE TABLE `product` ( 2 `id` bigint(11) NOT NULL AUTO_INCREMENT 3 `productName` varchar(50) DEFAULT NULL 4 `dir_id` bigint(11) DEFAULT NULL 5 `salePrice` double(10 2) DEFAULT NULL 6 `supplier` varchar(50) DEFAULT NULL 7 `brand` varchar(50) DEFAULT NULL 8 `cutoff` double(2 2) DEFAULT NULL 9 `costPrice` double(10 2) DEFAULT NULL 10 PRIMARY KEY (`id`) 11 ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8; 12 13 -- ---------------------------- 14 -- Records of product 15 -- ---------------------------- 16 INSERT INTO `product` VALUES ('1' '罗技M90' '3' '90.00' '罗技' '罗技' '0.50' '35.00'); 17 INSERT INTO `product` VALUES ('2' '罗技M100' '3' '49.00' '罗技' '罗技' '0.90' '33.00'); 18 INSERT INTO `product` VALUES ('3' '罗技M115' '3' '99.00' '罗技' '罗技' '0.60' '38.00'); 19 INSERT INTO `product` VALUES ('4' '罗技M125' '3' '80.00' '罗技' '罗技' '0.90' '39.00'); 20 INSERT INTO `product` VALUES ('5' '罗技木星轨迹球' '3' '182.00' '罗技' '罗技' '0.80' '80.00'); 21 INSERT INTO `product` VALUES ('6' '罗技火星轨迹球' '3' '349.00' '罗技' '罗技' '0.87' '290.00'); 22 INSERT INTO `product` VALUES ('7' '罗技G9X' '3' '680.00' '罗技' '罗技' '0.70' '470.00'); 23 INSERT INTO `product` VALUES ('8' '罗技M215' '2' '89.00' '罗技' '罗技' '0.79' '30.00'); 24 INSERT INTO `product` VALUES ('9' '罗技M305' '2' '119.00' '罗技' '罗技' '0.82' '48.00'); 25 INSERT INTO `product` VALUES ('10' '罗技M310' '2' '135.00' '罗技' '罗技' '0.92' '69.80'); 26 INSERT INTO `product` VALUES ('11' '罗技M505' '2' '148.00' '罗技' '罗技' '0.92' '72.00'); 27 INSERT INTO `product` VALUES ('12' '罗技M555' '2' '275.00' '罗技' '罗技' '0.88' '140.00'); 28 INSERT INTO `product` VALUES ('13' '罗技M905' '2' '458.00' '罗技' '罗技' '0.88' '270.00'); 29 INSERT INTO `product` VALUES ('14' '罗技MX1100' '2' '551.00' '罗技' '罗技' '0.76' '300.00'); 30 INSERT INTO `product` VALUES ('15' '罗技M950' '2' '678.00' '罗技' '罗技' '0.78' '320.00'); 31 INSERT INTO `product` VALUES ('16' '罗技MX Air' '2' '1299.00' '罗技' '罗技' '0.72' '400.00'); 32 INSERT INTO `product` VALUES ('17' '罗技G1' '4' '155.00' '罗技' '罗技' '0.80' '49.00'); 33 INSERT INTO `product` VALUES ('18' '罗技G3' '4' '229.00' '罗技' '罗技' '0.77' '96.00'); 34 INSERT INTO `product` VALUES ('19' '罗技G500' '4' '399.00' '罗技' '罗技' '0.88' '130.00'); 35 INSERT INTO `product` VALUES ('20' '罗技G700' '4' '699.00' '罗技' '罗技' '0.79' '278.00');

CREATE TABLE `productdir` ( `id` bigint(11) NOT NULL auto_increment `dirName` varchar(30) default NULL `parent_id` bigint(11) default NULL PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `productdir` VALUES ('1' '鼠标' null); INSERT INTO `productdir` VALUES ('2' '无线鼠标' '1'); INSERT INTO `productdir` VALUES ('3' '有线鼠标' '1'); INSERT INTO `productdir` VALUES ('4' '游戏鼠标' '1');

CREATE TABLE `productstock` ( `id` bigint(11) NOT NULL auto_increment `product_id` bigint(11) default NULL `storeNum` int(10) default NULL `lastIncomeDate` datetime default NULL `lastOutcomeDate` datetime default NULL `warningNum` int(10) default NULL PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `productstock` VALUES ('1' '1' '182' '2015-03-12 20:33:00' '2015-03-12 20:33:04' '20'); INSERT INTO `productstock` VALUES ('2' '2' '27' '2015-03-02 20:33:28' '2015-03-09 20:33:40' '20'); INSERT INTO `productstock` VALUES ('3' '3' '89' '2015-02-28 20:34:13' '2015-03-12 20:34:19' '20'); INSERT INTO `productstock` VALUES ('4' '5' '19' '2015-03-01 20:34:43' '2015-03-12 20:34:48' '20'); INSERT INTO `productstock` VALUES ('5' '6' '3' '2015-02-01 20:35:12' '2015-03-02 20:35:16' '5'); INSERT INTO `productstock` VALUES ('6' '7' '2' '2015-02-02 20:35:59' '2015-02-27 20:36:05' '3'); INSERT INTO `productstock` VALUES ('7' '8' '120' '2015-03-12 20:36:31' '2015-03-12 20:36:33' '20'); INSERT INTO `productstock` VALUES ('8' '9' '58' '2015-03-02 20:36:50' '2015-03-12 20:36:53' '20'); INSERT INTO `productstock` VALUES ('9' '11' '28' '2015-03-02 20:37:12' '2015-03-12 20:37:15' '20'); INSERT INTO `productstock` VALUES ('10' '12' '8' '2015-03-02 20:37:35' '2015-03-09 20:37:38' '5'); INSERT INTO `productstock` VALUES ('11' '13' '3' '2015-03-02 20:37:58' '2015-03-12 20:38:01' '5'); INSERT INTO `productstock` VALUES ('12' '14' '6' '2015-03-02 20:38:20' '2015-03-07 20:38:23' '5'); INSERT INTO `productstock` VALUES ('13' '15' '2' '2015-02-02 20:38:38' '2015-02-24 20:38:44' '5'); INSERT INTO `productstock` VALUES ('14' '16' '3' '2015-02-02 20:39:05' '2015-02-06 20:39:09' '3'); INSERT INTO `productstock` VALUES ('15' '17' '49' '2015-03-02 20:39:36' '2015-03-12 20:39:40' '20'); INSERT INTO `productstock` VALUES ('16' '18' '14' '2015-03-02 20:39:57' '2015-03-09 20:40:01' '10'); INSERT INTO `productstock` VALUES ('17' '20' '7' '2015-03-02 20:40:22' '2015-03-03 20:40:25' '5');

三. 多表查询

1. 笛卡尔积

多表查询会产生笛卡尔积。 假设集合A={a b}，集合B={0 1 2}，则两个集合的笛卡尔积为{(a 0) (a 1) (a 2) (b 0) (b 1) (b 2)}。实际运行环境下，应避免使用全笛卡尔集。

解决笛卡尔积最有效的方法：等值连接。

-- 需求:查询所有的货品信息 对应的货品分类信息 SELECT productName dirName FROM product productdir WHERE dir_id = productdir.id

2. 外键约束

主键约束(PRIMARY KEY): 约束在当前表中 指定列的值非空且唯一.

外键约束(FOREIGN KEY): A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键).

注意：在MySQL中，InnoDB支持事务和外键.修改表的存储引擎为InnDB。

格式：ALTER TABLE 表名 ENGINE='InnoDB'。

主表：数据可以独立存在，就是被参考的表。 productdir

从表：表中的数据，必须参照于主表的数据。product

注意：在删除表的时候，先删除从表，再删除主表。

3. 多表查询详解

多表查询包括三类：内连接查询（隐式内连接和显示内连接）、外连接查询 (左外链接、右外链接、全链接 )、子连接查询。

(1). 内连接

隐式内连接：查询出来的结果是多表交叉共有的。

格式：

显式内连接：

格式：

注意：在做等值连接的时候，若A表中的和B表中的列相同，可以缩写为：

需求:查询所有商品的名称和分类名称: 隐式内连接: SELECT p.productName pd.dirName FROM product p productdir pd WHERE p.dir_id = pd.id 显示内连接: SELECT p.productName pd.dirName FROM product p INNER JOIN productdir pd ON p.dir_id = pd.id 显示内连接: SELECT p.productName pd.dirName FROM product p JOIN productdir pd ON p.dir_id = pd.id 需求: 查询零售价大于200的无线鼠标 SELECT * FROM product p productdir pd WHERE p.dir_id = pd.id AND p.salePrice >200 And pd.dirName ='无线鼠标' SELECT * FROM product p JOIN productdir pd on p.dir_id = pd.id WHERE p.salePrice >200 And pd.dirName ='无线鼠标' 需求: 查询每个货品对应的分类以及对应的库存 SELECT p.productName pd.dirName ps.storeNum FROM product p productdir pd productstock ps WHERE p.dir_id = pd.id AND p.id = ps.product_id SELECT p.productName pd.dirName ps.storeNum FROM product p JOIN productdir pd on p.dir_id = pd.id JOIN productstock ps on p.id = ps.product_id 需求: 如果库存货品都销售完成 按照利润从高到低查询货品名称 零售价 货品分类（三张表）. select * (p.salePrice - p.costPrice) * ps.storeNum lirun FROM product p productdir pd productstock ps WHERE p.dir_id = pd.id AND p.id = ps.product_id ORDER BY lirun DESC select * (p.salePrice - p.costPrice) * ps.storeNum lirun FROM product p JOIN productdir pd on p.dir_id = pd.id JOIN productstock ps on p.id = ps.product_id ORDER BY lirun DESC

(2). 外连接查询

左外连接：查询出JOIN左边表的全部数据，JOIN右边的表不匹配的数据用NULL来填充。

右外连接：查询出JOIN右边表的全部数据，JOIN左边的表不匹配的数据用NULL来填充。

eg： A LEFT JOIN B 等价于 B RIGHT JOIN A

-- 外链接 # 查询所有商品的名称和分类名称 左连接: SELECT * FROM product p LEFT JOIN productdir pd ON p.dir_id = pd.id -- 等价于 SELECT * FROM productdir pd RIGHT JOIN product p ON p.dir_id = pd.id 右连接: SELECT * FROM product p RIGHT JOIN productdir pd ON p.dir_id = pd.id

(3). 自连接查询：把一张表看成两张表来做查询