2021-06-01 MySQL连接查询学习

重点掌握：

1.进行连接查询时返回的结果集，是两个表中所有相匹配的数据，而舍弃不匹配的数据，这种连接称为交叉连接。

2.在自然连接中两张表的所有名称相同的列都将被比较，并且在结果集中把重复的列去掉，结果集中两张表中名称相同的列仅出现一次。

3.如果查询的信息来源于多表，则可通过多表连接查询方式建立多表连接查询。

4.左外连接的结果集 包括左表的所有记录和右表中满足连接条件的记录，结果集中那些不符合连接条件的来源于右表的列表为null。

5.综合使用连接查询、聚合函数和分组查询可以实现很多复杂的查询需求。

首先 需要了解概念并且熟记语法：

1.交叉连接（Cross Join），又称“笛卡尔连接（Cartesian Join）”或“叉乘（Product）”，它是所有内连接的基础。如果把表视为行记录的集合，那么交叉连接即返回这两个集合的笛卡尔积，返回到结果集合中的数据行数等于第一张表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。这其实等价于内连接的连接条件为“永真”，或连接条件不存在。如果A和B是两个集合，它们的交叉连接就记为：A×B。
通俗而言，笛卡尔积就是两个集合中的每一个成员都与对方集合中的任意一个成员有关联。

语法：

select * from 表1 CROSS JOIN 表2 

等价于：

select * from 表1,表2

 

2.内连接是应用程序中非常常见的连接操作，它一般都是默认的连接类型。内连接基于连接谓词，它将两张表（如A和B）的列组合在一起，产生新的结果表。内连接查询会将A表的每一行和B表的每一行进行比较，找出满足连接谓词的组合。当连接谓词被满足，A和B中的匹配的行会按列组合（并排组合）成结果集中的一行。内连接分为三种：交叉连接、相等连接和自然连接。

语法：

select 表1和表2中的字段列 from 表1 (INNER可以省略) JOIN 表2 ON 表1.列1=表2.列2 （where 查询条件表达式）

注释：若取两张表的所有列，则可用“*”代替，此时会出现连接依据列重复，即表1的列1和表2的列2为重复列。

括号（）内的内容视情况运用，可写可省略。

注意：连接依据的列可能包含null值，null值不与任何值匹配（甚至和它本身）

此外，连接查询的where条件表达式可以用on子句中的条件表达式进行替换，条件之间用and连接。

 

3.自然连接（Natural Join）是一种特殊的内连接，它要求相连接的两张表的链接依据列必须是相同的字段（字段名相同，字段属性相同）。在自然连接中两张表的所有名称相同的列都将被比较，并且在结果集中把重复的列去掉，结果集中两张表中名称相同的列仅出现一次。而普通的内连接并不去掉重复的列。（部分数据库不支持自然连接，如SQL Server等。）

语法：

select 表1和表2的字段列 from 表1 NATURAL JOIN 表2 (where 条件表达式)
括号内的where语句视情况而写，亦可省略

 

4.多表连接查询：如果查询的信息来源于多张表，则可通过两两相连的方式建立多表连接查询。
简单多表连接查询：如果在from子句中，直接列出所有要连接的表，然后在where子句中指定连接条件，此为简单多表查询，它与内连接功能相同。

语法：

select 表中的字段列 from 表1 JOIN 表2 ON 表1.列1=表2.列2_1 JOIN 表3 ON 表2.列2_2=表3.列3 (where 条件)

select 表中的字段列 from 表1,表2,表3
where 表1.列1=表2.列2_1 and 表2.列2_2=表3.列3 (and 其他条件)

 

作业示例

1、查询每件商品的商品名和分类名

结果示例

2、查询每个分类的分类名和有多少件商品

结果示例

3、查询每个订单是谁买的，什么时候买的，买了什么，买了多少个（花了多少钱）

结果示例

完成上述操作代码如下：

select goodsname 商品,typename 分类 from goods join type on goods.typeid=type.typeid

select typename 分类,count(*) 数量 from goods join type on goods.typeid=type.typeid group by typename with rollup

select user.name,orders.ordersdate,goods.goodsname,ordersdetail.quantity from user,orders,ordersdetail,goods where ordersdetail.ordersid=orders.ordersid and user.userid=orders.userid and goods.goodsid=ordersdetail.goodsid