MySQL 连接 JOIN

发表于 | 更新于 | 分类于 ， | 评论数： | 阅读次数：

从多个表中读取数据，在 SELECT UPDATE DELETE 语句中使用 JOIN。

  • INNER JOIN 内连接（等值连接）获取两个表中字段匹配关系的记录

  • LEFT JOIN 左连接 获取左表所有记录，即使右表没有对应的匹配记录

  • RIGHT JOIN 右连接 与上边的相反

示例表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DROP TABLE tb1,tb2;

CREATE TABLE `tb1`(
  `id` INT AUTO_INCREMENT ,
  `title` VARCHAR(100) NOT NULL,
  `author` VARCHAR(100) NOT NULL,
  `data` DATE DEFAULT NULL,
  key (`id`)
);

INSERT tb1 VALUES(null,'学习 MySQL','001','2018-01-01'),
                 (null,'学习 PHP','001','2018-01-02'),
                 (null,'学习 Java','002','2018-01-02'),
                 (null,'学习 HTML','003','2018-01-03');

CREATE TABLE `tb2`(
  `author` VARCHAR(100) NOT NULL,
  `num_count` INT NOT NULL
);

INSERT tb2 VALUES('001',20),
                 ('002',21),
                 ('003',22);

详解

1
2
3
4
5
SELECT tb1.id,tb1.author,tb2.num_count FROM tb1 AS a INNER JOIN tb2 AS b ON a.author=b.author;

SELECT tb1.id,tb1.author,tb2.num_count FROM tb1 a LEFT JOIN tb2 b ON a.author=b.author;

SELECT tb1.id,tb1.author,tb2.num_count FROM tb1 a RIGHT JOIN tb2 b ON a.author=b.author;

DELETE

1
2
3
4
5
DELETE A FROM YSHA A LEFT JOIN YSHB B ON A.code=b.code WHERE b.code is NULL

DELETE FROM YSHA WHERE NOT EXISTS(SELECT 1 FROM YSHB B WHERE YSHA.code=b.code)

# 将 B 表中未使用的 A 表编号删除
0%