MySQL常用语句（四）：连接查询与子查询

一、创建表及写入数据方便后续使用

create table student(`id` int(11) NOT NULL AUTO_INCREMENT,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB CHARSET=utf8;

create table score(`id` int(11) NOT NULL AUTO_INCREMENT,score int,PRIMARY KEY (`id`))ENGINE=InnoDB CHARSET=utf8;

insert into student values(1,'zhang'),(2,'wang'),(3,'zhao');

insert into score values(1,88),(2,90),(4,92);

select * from student;

+----+-------+

| id | name |

+----+-------+

| 1 | zhang |

| 2 | wang |

| 3 | zhao |

+----+-------+

select * from score;

+----+-------+

| id | score |

+----+-------+

| 1 | 88 |

| 2 | 90 |

| 4 | 92 |

+----+-------+

二、连接查询

1、内连接（inner join）

select * from student inner join score on student.id = score.id;

+----+-------+----+-------+

| id | name | id | score |

+----+-------+----+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

+----+-------+----+-------+

可以看出，在内查询中，只有满足条件（两表id相等）的数据才会出现。

2、外连接

2.1、左连接(left join)

select * from student left join score on student.id=score.id;

+--+-------+------+-------+

| id | name | id | score |

+--+-------+------+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

| 3 | zhao | NULL | NULL |

+----+-------+------+-------+

可以看出，左连接查询的结果为左表的全部记录，即使右表中没有对应的匹配记录

2.2、右连接(right join)

select * from student right join score on student.id=score.id;

+------+-------+----+-------+

| id | name | id | score |

+------+-------+----+-------+

| 1 | zhang | 1 | 88 |

| 2 | wang | 2 | 90 |

| NULL | NULL | 4 | 92 |

+------+-------+----+-------+

可以看出，右连接查询的结果为右表的全部记录，即使左表中没有对应的匹配记录

三、子查询

1、where子查询

字句结果只匹配一条数据的情况

select * from student where id = (select id from score where score = 90);

+----+------+

| id | name |

+----+------+

| 2 | wang |

+----+------+

子句结果匹配多行数据的情况

select * from student where id in (select id from score where score <= 90);

+----+-------+

| id | name |

+----+-------+

| 1 | zhang |

| 2 | wang |

+----+-------+

2、from子查询

select id,score from (select id,score-1 as score from score) as score_tmp where score >=90;

+----+-------+

| id | score |

+----+-------+

| 4 | 91 |

+----+-------+

可理解为将score表中score字段的每个值减1生成一个临时表，然后查询这个临时表中score大于或等于90的。

3、exists子查询

select * from score where exists (select * from student where student.id=score.id);

+----+-------+

| id | score |

+----+-------+

| 1 | 88 |

| 2 | 90 |

+----+-------+

查出score表中的数据，但必须score的id在student表中存在。