MySQL表联结的多种方式

悠悠 2022-12-21 10:56 132阅读 0赞

在涉及多个表通过表关系或者多次select语句来查询相关信息时，有子查询和表联结的方法。

### 子查询 ###

定义：在一个select语句中嵌套另一个select语句。  
子查询的方式有：

*  作为过滤条件，参与查询。当一个select语句查询的结果是另一个select语句的过滤条件时，可以直接将该select语句嵌套在另一个select语句上，作为过滤条件。  
    例如：一个 customers 表存储客户信息 ，  
    ![在这里插入图片描述][20201116105750880.png_pic_center]  
    另一个orders表存储订单信息：  
    ![在这里插入图片描述][2020111610592380.png_pic_center]  
    orders存储有：客户的id。 要求找出购买过商品的客户，并显示客户信息：  
    按寻常查询：  
    第一步： 找出购买过商品的客户。  
    select cust\_id from orders group by cust\_id;  
    结果如下：  
    ![在这里插入图片描述][202011161104522.png_pic_center]  
    第二步：根据上图所示的结果，查询相关客户：  
    select \* from customers where cust\_id in (10001,10003,10004,10005);  
    ![在这里插入图片描述][20201116110852835.png_pic_center]  
    结果如图所示，达到了查询要求 ，但对其分析可知，第一步查询出的结果刚好就是第二步查询的过滤条件：所以，利用子查询：  
    select \* from customers where cust\_id in ( select cust\_id from orders group by cust\_id);  
    结果如下：  
    ![在这里插入图片描述][20201116111151841.png_pic_center]
 *  作为计算字段参与查询。  
    例如：假如需要显示customers客户的订单总数。  
    这个时候，既需要计算orders表里面相关客户的订单总数，同时也需要在customers里面显示相关客户信息；  
    找出相关客户的订单总数的select语句：  
    select count(*) from orders where cust\_id = 10001;  
    显示客户信息的select语句:  
    select \* from customers ;  
    还需要将每个人的订单总数显示，这时，可以将第一个select语句嵌套进去。  
    select customers.cust\_id,customers.cust\_name,(select count(*) as count\_name from orders where customers.cust\_id = orders.cust\_id)  
    from customers;  
    结果：  
    ![在这里插入图片描述][202011161152253.png_pic_center]  
    在这个语句中，where orders.cust\_id = customers.cust\_id ，每次计算都赋给customers.cust\_id一个新的值，从而将过滤条件进行更新。  
    MySQL中的查询不是一步到位的，而是一行一行的执行，每次取出一行符合条件的记录，就会有一个新的cusstomers.cust\_id。  
    而order by ，group by，聚集函数等是对结果集即结果进行处理？

在多个表根据表关系或者多个select语句查询中，子查询的方式不总是最有效率的方法。

### 表联结 ###

简单地说，联结是⼀种机制，⽤来在⼀条SELECT语句中关联表，因此称之为联  
结。  
使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。  
在表1和表2的联结过程中，表1上的每一行记录都会匹配表2上的所有行记录，即表1中的一行记录，每次与表2 中的一行记录匹配组成新的一行，依次执行，直到将表2中的所有行记录全部匹配，转而表1中转向下一行记录，再次与表2匹配。最后形成的新的表记录的行数有 表1中的行数 n X 表2中的行数m = nm；即笛卡尔积。当n ，m达到一定大小时，产生的数据量是很大的。所以，表联结中总是提供过滤条件。  
表联结的三种联结方式：

*  联结：2个及多个表进行联结。  
    例如我们需要查询出所有的商品及对应的供应商信息。  
    select vend\_name,prod\_name,prod\_price from vendors,products  
    where vender.vend\_id = products.vend\_id;  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDgxNzk3Mw_size_16_color_FFFFFF_t_70_pic_center]  
    联结的另一个关键字为join,可以将上面语句改为：  
    select vend\_name,prod\_name,prod\_price from vendors join products  
    on vender.vend\_id = products.vend\_id;  
    过滤条件由on之后提供，在联结多个表时，可以将两个表之间的过滤条件分开写对应的on之后。
 *  自联结：对于一个表，也可以通过联结的方式进行查询。  
    假如你发现某物品（其ID为DTNTR）存在问题，因此想知道⽣产该物品的供应商⽣产的其他物品是否也存在这些问题。此查询要求⾸先找到⽣产ID为DTNTR的物品的供应商，然后找出这个供应商⽣产的其他物品。  
    select p1.prod\_id,p1.prod\_name  
    from products as p1,products as p2  
    where p1.vend\_id = p2.vend\_id and p2.prod\_id = ‘DTNTR’;  
    ![在这里插入图片描述][20201116133513731.png_pic_center]  
    SQL允许起别名 用 as 指代，例如上面的p1,p2。这样就可以将同一个表多次使用，达到一个表通过联结过滤的目的。
 *  外部联结：在以上案例中，当一个表中的一行记录没有满足过滤条件会被过滤掉，但有时需要全记录。例如：对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；  
    select customers.cust\_id,orders.order\_num from customers  
    **left** **join** orders on customers.cust\_id = orders.cust\_id;  
    ![在这里插入图片描述][20201116134255745.png_pic_center]  
    left join 和 right join就是解决这种情况的：  
    left join 会以左边的表为基准，联结右边的表，假如出现，左边的记录被过滤掉，也不会将记录删掉，只会在联结成的表中，右边表的字段会用null代替。  
    right join 会以右边的表为基准，联结左边的表，假如出现，右边的记录被过滤掉，也不会将记录删掉，只会在联结成的表中，左边表的字段会用null代替。  
    联结形成的表可以作为一个新的表参与另一个表的联结。

[20201116105750880.png_pic_center]: /images/20221120/fbfb8c6999454cf3838fed72b3a56712.png
[2020111610592380.png_pic_center]: /images/20221120/dec7f079b2ec4545bf512d1992c2f2ee.png
[202011161104522.png_pic_center]: /images/20221120/435e580989e74032aa379311265fecac.png
[20201116110852835.png_pic_center]: /images/20221120/1399de283fb04a8bb36e83daf64edd91.png
[20201116111151841.png_pic_center]: /images/20221120/8b6fd92f05fa472cb3a22d7f21751569.png
[202011161152253.png_pic_center]: /images/20221120/67c8cb4d46be479f82120473d67773c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDgxNzk3Mw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/1de00d0e99a64708b70cc22330446ea9.png
[20201116133513731.png_pic_center]: /images/20221120/40599fd402474db4b851b716548e6d7e.png
[20201116134255745.png_pic_center]: https://img-blog.csdnimg.cn/20201116134255745.png#pic_center