一、多表查询
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/1a673b5ea8647ee7864344809b92f47c.png#pic_center)
二、在 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','红楼梦','曹雪芹');
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e30503becd327ce6f45e0aeaf1807150.png#pic_center)
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');
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/11608563e5ca014298dd19a608b19caf.png#pic_center)
三、内连接
1 隐式内连接:没有JOIN
关键字,条件使用WHERE
指定
SELECT 字段 FROM 表1, 表2 WHERE 条件;
SELECT * FROM book b, actor a WHERE a.bookId = b.id AND a.bookId = 3;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/12b0179e04a5040533a840c93880aa30.png#pic_center)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/d15017801ddeb103661f947b94b89c23.png#pic_center)
四、左外连接
1 左外连接:使用LEFT OUTER JOIN ... ON
,OUTER
可以省略
SELECT 字段 FROM 左表 LEFT OUTER JOIN 右表 ON 表连接条件 WHERE 查询条件;
左外连接可以理解为:
将满足要求的数据显示,左表不满足要求的数据也显示(在内连接的基础上,保证左表的数据全部显示)
2 在 book 表中添加新的书籍
INSERT INTO `book` (`id`, `bookName`, `author`) VALUES('5','斗罗大陆','唐家三少');
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/0ffff28f271363ac293109e13f139d15.png#pic_center)
3 使用内连接查询
SELECT * FROM book b INNER JOIN actor a ON a.bookId = b.id;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/ac8ba81717e6adc5903fca7f735a3329.png#pic_center)
4 使用左外连接查询
SELECT * FROM book b LEFT OUTER JOIN actor a ON a.bookId = b.id;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/541fb51e7491bbaadaab6c31ea74c1f3.png#pic_center)
五、右外连接
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);
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/fa9f28ff4903a93218ec2f675d7d0d7a.png#pic_center)
3 使用内连接查询
SELECT * FROM book b INNER JOIN actor a ON a.bookId = b.id;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/ac8ba81717e6adc5903fca7f735a3329.png#pic_center)
4 使用右外连接查询
SELECT * FROM book b LEFT OUTER JOIN actor a ON a.bookId = b.id;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/61961b0208c902f1d700a7f3bec69eda.png#pic_center)