mysql 左,右,内连接

3231212

左右连接

全相乘方法（效率很低）
mysql> select * from test10;
+------+-------+
| id   | sname |
+------+-------+
|    1 | 云彩     |
|    2 | 月亮      |
|    3 | 星星      |
+------+-------+
3 rows in set (0.00 sec)

mysql> select * from test11;
+--------+-------+
| cat_id | cname |
+--------+-------+
|     95 | 猴子      |
|     96 | 老虎     |
+--------+-------+
2 rows in set (0.00 sec)

数据库中实现两表*的效果
mysql> select * from test10,test11;
+------+-------+--------+-------+
| id   | sname | cat_id | cname |
+------+-------+--------+-------+
|    1 | 云彩     |     95 | 猴子      |
|    1 | 云彩     |     96 | 老虎     |
|    2 | 月亮      |     95 | 猴子      |
|    2 | 月亮      |     96 | 老虎     |
|    3 | 星星      |     95 | 猴子      |
|    3 | 星星      |     96 | 老虎     |
+------+-------+--------+-------+
6 rows in set (0.03 sec)

分析：
test10看成集合有三个元素
test11看成集合有两个元素

test10*test11得到新集合是有六个元素
行 两个表行数相乘
列 两个表列数相加（可以重复）

多表查询时候列名重复的情况时候，需要明确指出取得是那个表
mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category limit 3;
+----------+--------+--------------------+--------+----------+
| goods_id | cat_id | goods_name         | cat_id | cat_name |
+----------+--------+--------------------+--------+----------+
|        1 |      4 | KD876              |      1 | 手机类型        |
|        4 |      8 | htcN85原装充电器           |      1 | 手机类型        |
|        3 |      8 | 诺基亚原装5800耳机            |      1 | 手机类型        |
+----------+--------+--------------------+--------+----------+


技巧 ：创建一个同样结构的表  create table [新表名] like [旧表名]
create table minigoods like goods;

复制一部分表的内容
mysql> insert into minigoods
    -> select * from goods limit 3;

       
取得有意义的两个表的对应 minigoods.cat_id=category.cat_id
mysql> select goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name from minigoods,category where minigoods.cat_id=category.cat_id ;
+----------+--------+--------------------+--------+----------+
| goods_id | cat_id | goods_name         | cat_id | cat_name |
+----------+--------+--------------------+--------+----------+
|        1 |      4 | KD876              |      4 | 3G手机      |
|        4 |      8 | htcN85原装充电器           |      8 | 耳机         |
|        3 |      8 | 诺基亚原装5800耳机            |      8 | 耳机         |
+----------+--------+--------------------+--------+----------+
3 rows in set (0.00 sec)

左连接语法
a表在左，不动
b表在右，动
a表和b表通过一个关系（自己设定），来筛选a所需要的b的行

a left join b on [条件]  ----条件为真，取出b的行

a left join b on [条件] 结果集也可以看成表（假设为c表），可以对其再查询

mysql> select goods_id,goods_name,cat_name  
    -> from
    -> (minigoods left join category on minigoods.cat_id=category.cat_id);
+----------+--------------------+----------+
| goods_id | goods_name         | cat_name |
+----------+--------------------+----------+
|        1 | KD876              | 3G手机      |
|        4 | htcN85原装充电器           | 耳机         |
|        3 | 诺基亚原装5800耳机            | 耳机         |
+----------+--------------------+----------+

[minigoods left join category on minigoods.cat_id=category.cat_id 看做c表]
验证：就是后面依然可以使用where 等等过滤条件

mysql> select goods_id,goods_name,cat_name from
                (minigoods left join category on minigoods.cat_id=category.cat_id )
                where 1 order by goods_id desc limit 2;
+----------+--------------------+----------+
| goods_id | goods_name         | cat_name |
+----------+--------------------+----------+
|        4 | htcN85原装充电器           | 耳机         |
|        3 | 诺基亚原装5800耳机            | 耳机         |
+----------+--------------------+----------+
2 rows in set (0.00 sec)

可以多张表左连接，就是把结果看成表
         看做一张表
{{a left join b on [条件]}  left join c on [条件]}

mysql> select goods.goods_id,goods.goods_name,goods.cat_id,cat_name
    -> from
    -> minigoods left join category on minigoods.cat_id=category.cat_id
    -> left join goods on minigoods.cat_id=4 limit 4;
+----------+--------------------+--------+----------+
| goods_id | goods_name         | cat_id | cat_name |
+----------+--------------------+--------+----------+
|        1 | KD876              |      4 | 3G手机      |
|        4 | htcN85原装充电器           |      8 | 3G手机      |
|        3 | 诺基亚原装5800耳机            |      8 | 3G手机      |
|        5 | 索爱原装M2卡读卡器               |     11 | 3G手机      |
+----------+--------------------+--------+----------+



左右连接的区别===================================================================================================

a left join b on 意思是查询的时候以a为基准查询
a right join b on 意思是查询的时候以b为基准查询

a left join b on  等同于 b right join a （都是以a为基准查询）

技巧：处于移植兼容性和理解上来说，最好一律使用左连接实现

create table boy(
bname varchar(20),
other char(1)
)engine myisam charset utf8;

insert into boy
values
('屌丝','A'),
('李四','B'),
('王五','C'),
('高富帅','D'),
('郑七','E');


create table girl(
gname varchar(20),
other char(1)
)engine myisam charset utf8;


insert into girl
values
('空姐','B'),
('大S','C'),
('阿娇','D'),
('张柏芝','D'),
('林黛玉','E'),
('宝钗','F');


取出所有男生的配偶（左连接）
select boy.*,girl.* from
boy left join girl on
boy.other=girl.other;

+--------+-------+--------+-------+
| bname  | other | gname  | other |
+--------+-------+--------+-------+
| 屌丝      | A     | NULL   | NULL  |
| 李四       | B     | 空姐      | B     |
| 王五       | C     | 大S      | C     |
| 高富帅     | D     | 阿娇       | D     |
| 高富帅     | D     | 张柏芝     | D     |
| 郑七      | E     | 林黛玉       | E     |
+--------+-------+--------+-------+

取出所有女生的配偶（左连接）
mysql> select girl.*,boy.* from
    -> girl left join boy on
    -> boy.other=girl.other;
+--------+-------+--------+-------+
| gname  | other | bname  | other |
+--------+-------+--------+-------+
| 空姐      | B     | 李四       | B     |
| 大S      | C     | 王五       | C     |
| 阿娇       | D     | 高富帅     | D     |
| 张柏芝     | D     | 高富帅     | D     |
| 林黛玉       | E     | 郑七      | E     |
| 宝钗       | F     | NULL   | NULL  |
+--------+-------+--------+-------+

取出所有女生的配偶（右连接，和上面左连接一致）
mysql> select girl.* ,boy.* from
    -> boy right join girl on
    -> boy.other=girl.other;
+--------+-------+--------+-------+
| gname  | other | bname  | other |
+--------+-------+--------+-------+
| 空姐      | B     | 李四       | B     |
| 大S      | C     | 王五       | C     |
| 阿娇       | D     | 高富帅     | D     |
| 张柏芝     | D     | 高富帅     | D     |
| 林黛玉       | E     | 郑七      | E     |
| 宝钗       | F     | NULL   | NULL  |
+--------+-------+--------+-------+


内连接===================================================================================================

取出有配偶的

select girl.*,boy.* from
boy inner join girl on
boy.other=girl.other;
+--------+-------+--------+-------+
| gname  | other | bname  | other |
+--------+-------+--------+-------+
| 空姐      | B     | 李四       | B     |
| 大S      | C     | 王五       | C     |
| 阿娇       | D     | 高富帅     | D     |
| 张柏芝     | D     | 高富帅     | D     |
| 林黛玉       | E     | 郑七      | E     |
+--------+-------+--------+-------+

内连接是左右连接的交集
（外连接是左右连接的并集，mysql不支持）可以使用用union实现


左连接应用===================================================================================================
create table match_t(
match_id int primary key auto_increment,
host_team_id int,
guest_team_id int,
match_result varchar(20),
match_time date
)engine myisam charset utf8;

insert into match_t
values
(1,1,2,'2:0','2006-05-21'),
(2,2,3,'1:2','2006-06-21'),
(3,3,1,'2:5','2006-07-21'),
(4,1,1,'3:2','2006-08-21');

create table team_t(
team_id int primary key auto_increment,
team_name varchar(20)
)engine myisam charset utf8;

insert into team_t
values
(1,'恒大'),
(2,'国安'),
(3,'申花'),
(4,'大连');


mysql> select * from match_t;
+----------+--------------+---------------+--------------+------------+
| match_id | host_team_id | guest_team_id | match_result | match_time |
+----------+--------------+---------------+--------------+------------+
|        1 |            1 |             2 | 2:0          | 2006-05-21 |
|        2 |            2 |             3 | 1:2          | 2006-06-21 |
|        3 |            3 |             1 | 2:5          | 2006-07-21 |
|        4 |            4 |             1 | 3:2          | 2006-08-21 |
+----------+--------------+---------------+--------------+------------+

mysql> select * from team_t;
+---------+-----------+
| team_id | team_name |
+---------+-----------+
|       1 | 恒大          |
|       2 | 国安          |
|       3 | 申花        |
|       4 | 大连          |
+---------+-----------+

技巧 ：善与给表其别名

2006-06-21日期之后 把表中对战换成队名
select host_t.team_name,guest_t.team_name,match_result,match_time from
match_t left join (team_t as host_t) on match_t.host_team_id=host_t.team_id
left join (team_t as guest_t) on match_t.guest_team_id=guest_t.team_id

where match_time>='2006-06-21';

+--------------+-----------+---------------+-----------+--------------+------------+
| host_team_id | team_name | guest_team_id | team_name | match_result | match_time |
+--------------+-----------+---------------+-----------+--------------+------------+
|            1 | 恒大          |             2 | 国安          | 2:0          | 2006-05-21 |
|            2 | 国安          |             3 | 申花        | 1:2          | 2006-06-21 |
|            3 | 申花        |             1 | 恒大          | 2:5          | 2006-07-21 |
|            4 | 大连          |             1 | 恒大          | 3:2          | 2006-08-21 |
+--------------+-----------+---------------+-----------+--------------+------------+