理解内连接中的隐式内连接、显式内连接和外连接中的左外连接、右外连接

一、多表查询

二、在 MySQL 中创建 book 和 actor 两张表

1 创建 book 表及添加数据

CREATE TABLE `book` (
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMEN,
	`bookName` VARCHAR (60),
	`author` VARCHAR (60)
); 
INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('1','水浒传','施耐庵');
INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('2','三国演义','罗贯中');
INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('3','西游记','吴承恩');
INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('4','红楼梦','曹雪芹');

2 创建 actor 表及添加数据

CREATE TABLE `actor` (
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`actorName` VARCHAR (30),
	`gender` VARCHAR (3),
	`designation` VARCHAR (30),
	`bookId` INT 
); 
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('1','唐僧','男','旃檀功德佛','3');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('2','孙悟空','男','斗战胜佛','3');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('3','猪八戒','男','净坛使者','3');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('4','沙僧','男','金身罗汉','3');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('5','刘备','男','字玄德','2');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('6','关羽','男','字云长','2');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('7','张飞','男','字翼德','2');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('8','宋江','男','及时雨','1');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('9','林冲','男','豹子头','1');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('10','武松','男','行者','1');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('11','李逵','男','黑旋风','1');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('12','鲁智深','男','花和尚','1');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('13','贾宝玉','男','宝玉','4');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('14','林黛玉','女','黛玉','4');
INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('15','薛宝钗','女','宝钗','4');

三、内连接

1 隐式内连接：没有JOIN关键字，条件使用WHERE指定

SELECT 字段 FROM 表1, 表2 WHERE 条件;

SELECT * FROM book b, actor a WHERE a.bookId = b.id AND a.bookId = 3;

2 显式内连接：使用INNER JOIN ... ON语句, 可以省略INNER

SELECT 字段 FROM 左表 INNER JOIN 右表 ON 表连接条件 WHERE 查询条件;

SELECT * FROM book b INNER JOIN actor a ON a.bookId = b.id WHERE a.bookId = 2;

四、左外连接

1 左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略

SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件；

左外连接可以理解为：将满足要求的数据显示，左表不满足要求的数据也显示(在内连接的基础上,保证左表的数据全部显示)

2 在 book 表中添加新的书籍

INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('5','斗罗大陆','唐家三少');

3 使用内连接查询

SELECT * FROM book b INNER JOIN actor a ON a.bookId = b.id;

4 使用左外连接查询

SELECT * FROM book b LEFT OUTER JOIN actor a ON a.bookId = b.id;

五、右外连接

1 右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略

SELECT 字段 FROM 左表 RIGHT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件；

右外连接可以理解为：满足要求的数据显示,并且右表不满足要求的数据也显示(在内连接的基础上保证右边的数据全部显示)

2 在 actor 表中添加新的人物

INSERT INTO `actor` (`id`, `actorName`, `gender`, `designation`, `bookId`) VALUES('16','史湘云','女','湘云',NULL);

3 使用内连接查询

SELECT * FROM book b INNER JOIN actor a ON a.bookId = b.id;

4 使用右外连接查询

SELECT * FROM book b LEFT OUTER JOIN actor a ON a.bookId = b.id;