mysql 连接查询 join

本文用到的表

CREATE TABLE `cls` (
  `cls_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`cls_id`)
)

CREATE TABLE `stu` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` smallint(6) DEFAULT NULL,
  `cls_id` int(11) DEFAULT NULL,
  `score` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`stu_id`),
  KEY `fk_stu_cls` (`cls_id`),
  CONSTRAINT `fk_stu_cls` FOREIGN KEY (`cls_id`) REFERENCES `cls` (`cls_id`)
)

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade` varchar(10) DEFAULT NULL,
  `min_sc` tinyint(4) DEFAULT NULL,
  `max_sc` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

 

1、连接查询简介

1）连接查询分为

• • 内连接查询 inner  join
  • 外连接查询  
  • 左连接查询  left  outer  join
  • 右连接查询  right  outer  join

其中iner/outer 可以省略，因为区分内外连接查询的不在于inner/outer，而是判断有无left/right，有就是外连接查询，没有则为内链接查询。

2）内连接查询与外连接查询的区别：假设A/B两张表做连接查询

①  A/B能够全部匹配的记录查询，内连接查询

②  除了将A/B全部匹配的查询出来之外，还将其中一张表的记录全部显示出来，对方表没有记录则用null值表示

③  外连接查询结果数  >=  内连接查询结果数

④  任何一个左连接都可以写成右连接，任何一个右连接也都可以写成左连接

 

1、内连接查询

select 
    xxx
from
    A表
inner join
    B表
on
    连接条件
where
    查询条件;

1）查询每个学生的姓名与班级，要求显示：学生姓名  班级名 两列

mysql> select s.name, c.name from stu as s inner join cls as c on s.cls_id=c.cls_id;
+-----------+-----------+
| name      | name      |
+-----------+-----------+
| zhangsan  | 火箭班    |
| lisi      | 提高班    |
| wangwu    | 速成班    |
| zhaoliu   | 提高班    |
| guojing   | 速成班    |
| huangrong | 提高班    |
| xiaolongn | 火箭班    |
+-----------+-----------+
7 rows in set (0.00 sec)

 

2、外连接查询

select 
    xxx
from
    A表
left join
    B表
on
    连接条件
where
    查询条件;

1）left join，左连接查询，除了匹配结果外，还将左边表未能匹配上的值显示，这里全都匹配，看不到效果

mysql> select s.name, c.name from stu as s left join cls as c on s.cls_id=c.cls_id;
+-----------+-----------+
| name      | name      |
+-----------+-----------+
| zhangsan  | 火箭班    |
| xiaolongn | 火箭班    |
| wangwu    | 速成班    |
| guojing   | 速成班    |
| lisi      | 提高班    |
| zhaoliu   | 提高班    |
| huangrong | 提高班    |
+-----------+-----------+

2）right join，右连接查询，除了匹配结果外，还将右边表未能匹配上的值显示，如最后一行

mysql> select s.name, c.name from stu as s right join cls as c on s.cls_id=c.cls_id;
+-----------+-----------+
| name      | name      |
+-----------+-----------+
| zhangsan  | 火箭班    |
| lisi      | 提高班    |
| wangwu    | 速成班    |
| zhaoliu   | 提高班    |
| guojing   | 速成班    |
| huangrong | 提高班    |
| xiaolongn | 火箭班    |
| NULL      | 补习班    |
+-----------+-----------+

3）right join <-->  left join 之间的相互转换，将left/right互换，A/B表位置互换即可。

mysql> select s.name, c.name from cls as c left join stu as s on s.cls_id=c.cls_id;
+-----------+-----------+
| name      | name      |
+-----------+-----------+
| zhangsan  | 火箭班    |
| lisi      | 提高班    |
| wangwu    | 速成班    |
| zhaoliu   | 提高班    |
| guojing   | 速成班    |
| huangrong | 提高班    |
| xiaolongn | 火箭班    |
| NULL      | 补习班    |
+-----------+-----------+

 

3、三张表以上的连接查询

select 
    xxx
from
    A表
inner join
    B表
on
    连接条件
inner join
    C表
on
    连接条件
where
    查询条件;

 

这里A-B表先连接查询，然后A-C表再连接查询

1）查询显示结果。“学生姓名，班级名，成绩等级”，学生姓名，班级名，成绩等级在不同的表中

select 
    s.name, c.name, sc.grade
from
    stu as s
inner join 
    cls as c
on
    s.cls_id=c.cls_id
inner join
    score as sc
on 
    s.score between sc.min_sc and max_sc;

 

+-----------+-----------+--------+
| name      | name      | grade  |
+-----------+-----------+--------+
| zhangsan  | 火箭班    | 及格   |
| lisi      | 提高班    | 中     |
| wangwu    | 速成班    | 中     |
| zhaoliu   | 提高班    | 优     |
| guojing   | 速成班    | 及格   |
| huangrong | 提高班    | 良     |
| xiaolongn | 火箭班    | 差     |
+-----------+-----------+--------+