您现在的位置是：技术博客 > 数据库数据库

mysql连表查询

Lucas2020-11-05 22:06【代码】1502人已围观

简介在实际工作中经常要用到mysql连表查询，那么就必须搞清楚inner join，join，left join，right join，逗号，cross join，union的用法和之间的区别。用实际例子来分别实现下，深入理解下这几种查询。

#### 1、创建表结构

```
# 1、创建学生表
CREATE TABLE `lucas_student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `student_name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '学生姓名',
  `class_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '班级id',
  PRIMARY KEY (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

# 2、创建年级表
CREATE TABLE `lucas_class` (
  `class_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `class_name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '班级名称',
  `school_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '学校id',
  PRIMARY KEY (`class_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

# 3、创建学校表
CREATE TABLE `lucas_school` (
  `school_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学校id',
  `school_name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '学校名称',
  `area_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '地区id',
  PRIMARY KEY (`school_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

```

#### 2、创建数据
```
# 入库学生表
INSERT INTO `lucas_student` VALUES ('1', '李白', '1');
INSERT INTO `lucas_student` VALUES ('2', '杜甫', '1');
INSERT INTO `lucas_student` VALUES ('3', '白居易', '1');
INSERT INTO `lucas_student` VALUES ('4', '杜牧', '2');
INSERT INTO `lucas_student` VALUES ('5', '孟浩然', '2');
INSERT INTO `lucas_student` VALUES ('6', '王维', '2');
INSERT INTO `lucas_student` VALUES ('7', '李商隐', '3');
INSERT INTO `lucas_student` VALUES ('8', '韩愈', '3');
INSERT INTO `lucas_student` VALUES ('9', '刘禹锡', '3');
INSERT INTO `lucas_student` VALUES ('10', '贺知章', '4');
INSERT INTO `lucas_student` VALUES ('11', '孟郊', '4');
INSERT INTO `lucas_student` VALUES ('12', '刘长卿', '4');

# 入库年级表
INSERT INTO `lucas_class` VALUES ('1', '一年级一班', '1');
INSERT INTO `lucas_class` VALUES ('2', '一年级二班', '1');
INSERT INTO `lucas_class` VALUES ('3', '二年级一班', '2');
INSERT INTO `lucas_class` VALUES ('4', '二年级二班', '2');
INSERT INTO `lucas_class` VALUES ('5', '三年级一班', '3');
INSERT INTO `lucas_class` VALUES ('6', '三年级二班', '3');

# 入库学校表
INSERT INTO `lucas_school` VALUES ('1', '武汉大学', '1');
INSERT INTO `lucas_school` VALUES ('2', '北京大学', '2');
INSERT INTO `lucas_school` VALUES ('3', '中山大学', '3');
INSERT INTO `lucas_school` VALUES ('4', '浙江大学', '4');
```

#### 3、内连接：只显示符合条件的数据

```
# 内连接 + 内连接 （12条）取出a表和b表和c表相关联的所有数据，以最大数据表为主
select a.*,b.*,c.* from lucas_student a inner join lucas_class b on a.class_id = b.class_id inner join lucas_school c on b.school_id = c.school_id;

select a.*,b.*,c.* from lucas_student a, lucas_class b, lucas_school c where a.class_id = b.class_id and b.school_id = c.school_id;
```

MySQL 中 JOIN, CROSS JOIN 和 INNER JOIN 三者语法功能上相同，可互换，而 SQL 标准中，INNER JOIN 需要搭配 ON 语句。

在不使用on语法时，join、inner join、逗号、cross join结果相同，都是取2个表的笛卡尔积。逗号与其他操作符优先级不同，所以有可能产生语法错误，尽量减少用逗号。join、inner join、cross join支持on和using语法，逗号不支持on和using语法。

#### 4、左外连接：左边表中的数据优先全部显示
```
# 左外连接 + 左外连接 （12条）
select a.*,b.*,c.* from lucas_student a left join lucas_class b on a.class_id = b.class_id left join lucas_school c on b.school_id = c.school_id;
```

```
# 右外连接 + 右外连接 （15条）
select a.*,b.*,c.* from lucas_student a right join lucas_class b on a.class_id = b.class_id right join lucas_school c on b.school_id = c.school_id;
```

#### 5、右外连接：右边表中的数据优先全部显示

```
# 左外连接 + 右外连接 （14条）
select a.*,b.*,c.* from lucas_student a left join lucas_class b on a.class_id = b.class_id right join lucas_school c on b.school_id = c.school_id;
```
```
# 右外连接 + 左外连接 （14条）
select a.*,b.*,c.* from lucas_student a right join lucas_class b on a.class_id = b.class_id left join lucas_school c on b.school_id = c.school_id;
```

```
# 以班级为主表，学生按班级分组，一个班级一条记录，学生分组后随机取出一位 （4条）
select a.*,b.*,c.* from ( select * from lucas_student group by class_id ) as a inner join lucas_class b on a.class_id = b.class_id inner join lucas_school c on b.school_id = c.school_id;
```

很赞哦！ ( 0 )

上一篇：数据库死锁

下一篇：跨数据库实时同步表数据