mysql连接

逃离我推掉我的手 2022-01-06 09:39 207阅读 0赞

创建两张表，代表两个店铺，每个店铺有不同的，每个柜台卖不同的商品。

1 /*
     2    两个商店，每个店有不同的柜台，每个柜台上卖有不同的商品
     3 */
     4 create table tableA(
     5     id int(10) not null primary key auto_increment,
     6         monorail varchar(20),
     7         variety varchar(50)
     8 );
     9 insert into tableA 
    10 values (1,'a1','苹果'),(2,'a1','梨'),(3,'a3','香蕉'),(4,'a4','苹果'),(5,'a5','西瓜'),
    11 (6,'a6','苹果'),(7,'a7','葡萄'),(8,'a8','桃子');
    12 create table tableB(
    13     id int(10) not null primary key auto_increment,
    14         monorail varchar(20),
    15         variety varchar(50)
    16 );
    17 insert into tableB 
    18 values (1,'b1','虾'),(2,'b2','猪肉'),(3,'b3','苹果'),(4,'b4','西瓜'),(5,'b5','梨'),
    19 (6,'b6','香蕉');

两个表数据如下：

![931178-20190312173227318-795113055.png][]           ![931178-20190312173244303-1173074239.png][]

1.内连接

/*
      内连接，查处两个商店共有的商品
    */
    select * from tablea A INNER  JOIN tableb B
    on A.variety = B.variety ;

运行结果

![931178-20190312172305640-165945066.png][]

2.左连接

/*
      左连接，以A表为左表
    */
    select * from tablea A LEFT  JOIN tableb B
    on A.variety = B.variety ;

运行结果

![931178-20190312172357048-50883739.png][]

3.右连接

/*
      右连接，以A表为左表
    */
    select * from tablea A RIGHT  JOIN tableb B
    on A.variety = B.variety ;

运行结果

![931178-20190312172635591-1501672875.png][]

4.左外连接

/*
      左外连接，以A表为左表
    */
    select * from tablea A LEFT  JOIN tableb B
    on A.variety = B.variety Where B.variety is null;

运行结果

![931178-20190312172723110-328329137.png][]

5.右外连接

/*
      右外连接，以A表为左表
    */
    select * from tablea A RIGHT  JOIN tableb B
    on A.variety = B.variety Where A.variety is null;

![931178-20190312172757920-1229381133.png][]

6.全连接  mysql不支持full  (outer) join 故使用union代替

/*
      全连接
    */
    select * from tablea A LEFT  JOIN tableb B
    on A.variety = B.variety ;
    union
    select * from tablea A RIGHT  JOIN tableb B
    on A.variety = B.variety ;

运行结果

![931178-20190312172910304-2067584577.png][]

7.全外连接

select * from tablea A LEFT  JOIN tableb B
    on A.variety = B.variety Where B.variety is  null 
    union
    select * from tablea A RIGHT  JOIN tableb B
    on A.variety = B.variety Where A.variety is  null

运行结果

![931178-20190312173521430-197187837.png][]

转载于:https://www.cnblogs.com/xyzyj/p/10518193.html

[931178-20190312173227318-795113055.png]: https://img2018.cnblogs.com/blog/931178/201903/931178-20190312173227318-795113055.png
[931178-20190312173244303-1173074239.png]: /images/20211227/fd6c473f9f1e4c9f9df9084fa5f7b269.png
[931178-20190312172305640-165945066.png]: /images/20211227/97c74a586c944451b47f625244fafe1b.png
[931178-20190312172357048-50883739.png]: /images/20211227/a7b6ec25051345c29ac48a2fe05124b0.png
[931178-20190312172635591-1501672875.png]: /images/20211227/ada86db80b2d43548aaad6f47799e22f.png
[931178-20190312172723110-328329137.png]: /images/20211227/0e70b5a8596246ccb8d414cc577f9972.png
[931178-20190312172757920-1229381133.png]: /images/20211227/62854e67884043adaef470f862ce3a25.png
[931178-20190312172910304-2067584577.png]: /images/20211227/aebb60d928b64535bc6747186189a999.png
[931178-20190312173521430-197187837.png]: /images/20211227/749310dcc6f344b1a825488c945f60eb.png