1. MySQL多表查询

1.1 外键约束

为了消除多张表查询出现的笛卡尔积的现象，MySQL在建表并进行多表之间的关键查询可以使用外键关联查询。

外键：从表1（sub）的某列引用（ref）另外一个表2（main）的某列的值，把表1的这列叫做表2这列的外键。

1.2 外键的设置使用

比如上述最简单的员工(employee)和部门表(department)，设置外键dept_id与id相关联。

步骤如下：

①在多方表中设置fk外键，使用外键dept_id关联一方表中的主键id，并选择好参考表；

 

②修改选项设置中的存储引擎为InnoDB，支持设置外键操作；

 

注意:在MySQL中,InnoDB支持事务和外键.MyISAM 不支持事务和外键。

 

上述操作也可以使用SQL语句方式修改存储引擎为InnDB：

ALTER TABLE 表名 ENGINE='InnoDB';

如在命令行界面输入如下语句：

 

同样使用SQL语句创建外键关联：

ALTER TABLE employee ADD CONSTRAINT employee_fk(外键名) FOREIGN KEY (dept_id) REFERENCES  dept(dept_id)；

 

1.3 连接查询分类

连接查询总的分类可以用一张图来简单描述，主要分为外连接查询（左外连接、右外连接、全连接）、内连接查询（内连接、自连接）。

 

1.4 内连接查询

内连接两张表的情况如下图，连接查询得到的是两张表的交集部分。

 

SQL语句写法上可分为显式和隐式写法：

隐式内连接写法：

select <selectList>
From table1,table2 where table1.列 = table2.列;

显式内连接写法（推荐写法）：

select <selectList>
From table1 [inner] join table2 on table1.列 = table2.列;

查询实例：

说明：本例以下所有查询以product、product_category、product_stock三张表为例；

 

 

①需求:查询所有商品的名称和分类名称:

隐式写法：

SELECT p.product_name, pc.category_name

FROM product p, product_category pc

WHERE p.category_id = pc.id;

显式写法：

SELECT p.product_name, pc.category_name

FROM product p INNER JOIN product_category pc

ON p.category_id = pc.id;

 
SELECT p.product_name, pc.category_name

FROM product p JOIN product_category pc

ON p.category_id = pc.id;

②需求: 查询货品id，货品名称，货品所属分类名称；

隐式写法：

SELECT *

FROM product p, product_category pc

WHERE p.category_id = pc.id AND

p.sale_price > 200 AND pc.category_name = '无线鼠标';

 

显式写法：

SELECT *

FROM product p

JOIN product_category pc ON p.category_id = pc.id

AND p.sale_price > 200 AND pc.category_name = '无线鼠标';

1.5 外连接查询

外连接查询分为左外连接查询和右外连接查询。

左外连接：查询出JOIN左边表的全部数据与右表满足ON条件的部分，JOIN右边表不匹配的数据使用NULL来填充数据行。

右外连接：查询出JOIN右边表的全部数据与左表满足ON条件的部分，JOIN左边表不匹配的数据使用NULL来填充数据行。

 

语法格式：

select <selectList>

from table1 left/right [outer] join table2

on table1.列 = table2.列；

查询实例：

查询每种商品名称，分类的名称和包含的具体商品总数（storeNum）

 

SELECT p.product_name, pc.category_name, IFNULL(ps.store_num,0)

FROM product p

LEFT JOIN product_category pc

ON p.category_id = pc.id

LEFT JOIN product_stock ps

ON p.id = ps.product_id;

注：IFNULL(expr1,expr2)的使用是如果当前expr1为NULL，则显示expr2的值。

 

1.6 自连接查询

在特定的查询场景下（商品分类、地区、权限），需要设计将表中的数据进行分类或二级关联时，可能会用到自连接查询的表设计方法。

自连接方式：

查询实例：

查询每个商品分类的名称和父分类名称

#隐式写法

SELECT pare.category_name, sub.category_name FROM

product_category sub, product_category pare

WHERE sub.id = pare.parent_id;

 

# 显示写法

SELECT pare.category_name, sub.category_name FROM

product_category sub JOIN product_category pare

ON sub.id = pare.parent_id;

查询结果：

 

1.7 子查询

子查询（嵌套查询）：一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。（相当于查询出来一个结果，然后把结果当着一张表在进行查询）

一般的，嵌套在WHERE或者FROM字句中。

 

子查询（嵌套查询）一般分为单行单列子查询和单行多列子查询。

 

查询实例：

① 单行单列子查询

# 单行单列子查询

# 查询零售价比罗技MX1100更高的所有商品信息

SELECT * FROM product WHERE

sale_price > ( SELECT sale_price FROM product WHERE product_name = '罗技MX1100' );

查询结果：

 

②单行多列子查询

# 查询分类编号和折扣与罗技M100相同的所有商品信息

SELECT * FROM product WHERE (category_id, cutoff) IN (SELECT category_id, cutoff FROM product WHERE product_name = '罗技MX1100