mysql中实现并集、交集、差集、内联结、外联结、3张以上表的联结、交叉联结

傷城~ 2022-05-31 09:26 282阅读 0赞

一、mysql中实现并集

1.union函数

1.1

mysql> select product\_id,product\_name from product union select product\_id,product\_name from product2;

1.2order by只能使用一次，而且在最后使用

mysql> select product\_id,product\_name from product where product\_type='厨房用具' union select product\_id,product\_name from product2 where product\_type='厨房用具' order by product\_id;

1.union  all函数

mysql> select product\_id,product\_name from product union all select product\_id,product\_name from product2;

二、mysql中实现交集

1.INTERSECT函数无法在MySQL中使用，只能在orcal中使用

2.利用in实现交集

mysql> select product\_id,product\_name from product where product\_id in(select product\_id from product2);

三、mysql中实现差集

1.EXCEPT函数无法在MySQL中使用，只能在orcal中使用

2.利用not in实现差集

mysql> select product\_id,product\_name from product where product\_id not in(select product\_id from product2);

四、mysql中实现内联结

![70][]

![70 1][]

内联结和WHERE子句结合使用

![70 2][]

五、mysql中实现外联结

![70 3][]

![70 4][]

![70 5][]

六、3张以上表的联结

mysql> select sp.shop\_id,sp.shop\_name,sp.product\_id,p.product\_name,p.sale\_price,ip.inventory\_quantity from ShopProduct as sp INNER JOIN product as p ON sp.product\_id = p.product\_id INNER JOIN InventoryProduct as ip ON sp.product\_id = ip.product\_id where ip.inventory\_id='p001';

七、交叉联结

交叉联结的集合运算符是    笛卡儿积

mysql> select sp.shop\_id,sp.shop\_name,sp.product\_id,p.product\_name from ShopProduct as sp CROSS JOIN product as p;

[70]: /images/20220531/aabc25bc1468404ca177508a68a6baee.png
[70 1]: /images/20220531/ed31f043fa5840f6a1b4dbf9e855a07b.png
[70 2]: /images/20220531/4a3c26923e104a45887c50d44a580683.png
[70 3]: /images/20220531/af6bf38d5ba147cfa528e9852e44085e.png
[70 4]: /images/20220531/c21fc652715348d7abdaf7acbf85886a.png
[70 5]: /images/20220531/738d91a30b354309afa9b7433ce169be.png