数据库单标查询和多表查询（07-查询操作DQL-多表查询）

一. 综述

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

二 . 案例设计

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). 自连接查询：把一张表看成两张表来做查询

,