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表中存在。
感谢分享 有用
发表评论