首页 > itarticle > mysql 连接 join 详解 DELETE

mysql 连接 join 详解 DELETE

admin 11月 13, 2020 0

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

INNER JOIN 内连接（等值连接）获取两个表中字段匹配关系的记录
LEFT JOIN 左连接获取左表所有记录，即使右表没有对应的匹配记录
RIGHT JOIN 右连接与上边的相反

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);

详解

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

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)