MySQL基础笔记(2)-连接查询

目录

MySql的连接查询

一、连接查询的作用

二、建表语句和数据

三、七种连接查询介绍

使用连接查询的格式

1、全连接

2、内连接

3、左外连接

4、左内连接

5、右外连接、右内连接即把右表视为主表，左表视为从表。

 

---

MySql的连接查询是多表查询的基础，也是查询语句中较难理解的一块内容，也是项目实际中常常使用到的，把连接查询的几种形式弄明白是十分必要的，这篇文章是我的MySql进阶专栏的第一篇文章，希望对你有帮助 。                             

---

MySql的连接查询

一、连接查询的作用

连接查询的使用场景：当我们有两张表，如一张部门表，一张员工表如图：

 

当我们需要查询出每个员工的信息和部门信息时，我们就需要使用连接查询，将两张表“连接”起来，让返回的查询结果中包含两张表的数据，这就是连接查询的作用。

 

 

二、建表语句和数据

为了方便我们的测试，我准备了两张表，一张是员工表，一张是部门表
 

CREATE TABLE `department` (

  `id` int(11) NOT NULL,

  `name` varchar(32) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `department`(`id`,`name`) values

(1,'前台部'),

(2,'后台部'),

(3,'算法部'),

(4,'运维部'),

(5,'运营部');


CREATE TABLE `stuff` (

  `id` int(11) NOT NULL,

  `dep_id` int(11) NOT NULL,

  `name` varchar(32) DEFAULT NULL,

  `gender` char(1) DEFAULT NULL,

  `salary` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `dep_id` (`dep_id`),

  CONSTRAINT `stuff_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `stuff`(`id`,`dep_id`,`name`,`gender`,`salary`) values

(1,1,'小王','男',1000),

(2,1,'小李','男',1500),

(3,2,'小陈','女',2000),

(4,3,'小良','女',2000),

(5,4,'小张','女',3500),

(6,3,'小星','男',1500),

(7,1,'小彭','男',3400),

(8,2,'小龙','男',6000),

(9,4,'小玉','女',7600),

(10,3,'小陈','男',4555);

 

 

三、七种连接查询介绍

Mysql主要使用的连接查询有几种连接形式，分为：全连接、内连接、外连接、左外连接、右外连接、左内连接、右内连接；

 

使用连接查询的格式

SELECT * FROM A a xxx JOIN B b ON  a.xx = b. xx;

JOIN左侧的表称为左表，右侧的表称为右表，ON后边是连接的条件。

 

1、全连接

查询出两张表中所有的组合，这种数据毫无意义，在开发中不会使用

SELECT * FROM stuff s CROSS JOIN department d ;

 

2、内连接

在上面的全连接中可以看出，一共生成了50条数据，也就是每个表中的每条数据都相互连接了，但是这样毫无意义，所以衍生出了内连接，即根据条件查询出两张表中共有的部分，如查询出员工表和部门表,连接条件是员工部门id和部门表id相同，这样的数据才是有用的

 

 

SELECT * FROM stuff s INNER JOIN department d ON s.`dep_id` = d.`id`

 

3、左外连接

这是开发中常用的一种连接方式，左外连接时，左表是主表，右表是从表，通过ON条件连接后，主表的所有记录都会被查询出来，而从表只会显示符合ON条件的记录，查不到的数据以null填充。

 

如以员工为主表，通过左外连接部门表为从表，那么将查询出员工表所有的记录

SELECT * FROM stuff s left outer JOIN department d ON s.`dep_id` = d.`id`

 

当使用部门表为主表时，查询出以下结果：

SELECT * FROM  department d LEFT OUTER JOIN stuff s ON s.`dep_id` = d.`id`

注意，使用左外或者右外连接时，OUTER可不写

4、左内连接

这种连接用得比较少，它以左表为主表，查询出自身独有的那部分记录，从表的数据以null表示

SELECT * FROM  department d LEFT JOIN stuff s ON s.`dep_id` = d.`id` WHERE s.`dep_id` IS NULL

 

5、右外连接、右内连接即把右表视为主表，左表视为从表。