MySQL多表连接查询（第九章）

1 多表链接查询的介绍

SELECT * FROM bookinfo;
-- 多表连接
-- 通过查看图书信息表和图书类别表来获取图书编号、图书名称、图书类别
SELECT book_id, book_name, category FROM bookinfo INNER JOIN bookcategory ON bookinfo.book_category_id = bookcategory.category_id;

结果：

2 内连接

-- 查询未归还图书的图书编号，图书名称，身份证号，姓名，电话，归还日期, 是否归还
SELECT borrowinfo.book_id, book_name, borrowinfo.card_id, NAME, tel, return_date, STATUS FROM borrowinfo
INNER JOIN bookinfo ON borrowinfo.book_id = bookinfo.book_id
INNER JOIN readerinfo ON borrowinfo.card_id = readerinfo.card_id
WHERE STATUS = '否';

结果：

上述SQL语句还可以简化为如下：

SELECT t1.book_id, book_name, t1.card_id, NAME, tel, return_date, STATUS FROM borrowinfo t1
JOIN bookinfo t2 ON t1.book_id = t2.book_id
JOIN readerinfo t3 ON t1.card_id = t3.card_id
WHERE STATUS = '否';

查询结果是一样的。

3 外连接

-- 左连接
-- 查看图书类别表中的所有类别下都有哪些图书
SELECT category, book_id, book_name FROM bookcategory
LEFT JOIN bookinfo ON bookcategory.category_id = bookinfo.book_category_id;

结果：

左连接以左表为准：那哪个是左表呢？答案是：关键字‘LEFT’左边的表就是左表。如本例中的‘bookcategory’就是左表，显示出左表中的所有数据，注意：左表中的数据可以重复显示，然后显示右表中与连接条件相符合的数据进行拼接显示，如果左表中有数据但是右表中没有符合条件的数据，那么右表中的数据就显示为null。
右连接则相反！！！
先看例子：

-- 右连接实现左连接的查询效果
SELECT category, book_id, book_name FROM bookinfo
RIGHT JOIN bookcategory ON bookcategory.category_id = bookinfo.book_category_id;

结果：

我们仅仅把‘LEFT’换成了‘RIGHT’，接着把两个表的位置互换了，就得到了上边的查询结果，它和使用左连接查询效果一样。那到底是 为什么呢？
右连接以右表为准：那哪个是右表呢？答案是：关键字‘RIGHT’右边的表就是右表。如本例中的‘bookcategory’就是右表，显示出右表中的所有数据，注意：右表中的数据可以重复显示，然后显示左表中与连接条件相符合的数据进行拼接显示，如果右表中有数据但是左表中没有符合条件的数据，那么左表中的数据就显示为null。
大家有没有明白呢？

4 自连接

-- 查询所有图书类别的图书类别编号，类别名称，上级分类名称
SELECT s.category_id AS '图书类别编号', s.category AS '类别名称', p.category AS '上级分类名称' FROM bookcategory s
LEFT JOIN bookcategory p ON s.parent_id = p.category_id;

结果：

5 多表更新

由于借阅信息表我们之前已经将状态更改过。这里就完成2和3需求：
首先在多表更新之前我们先看一下表readerinfo和readerfee表的数据：
readerinfo:

readerfee:

现在更新表数据：

-- 2.更新罚款记录信息表，更新实际还书日期和罚款金额，罚款金额为每超出一天扣0.2元;
-- 3.同时更新读者信息表的余额。（在余额中扣除罚款金额）
UPDATE readerfee t1 JOIN readerinfo t2 ON t1.card_id = t2.card_id
SET actual_return_date = DATE_FORMAT(SYSDATE(), '%Y%m%d'), book_fee = DATEDIFF(SYSDATE(), return_date) * 0.2,
balance = balance - book_fee WHERE t1.book_id = 20151101 AND t1.card_id = '210210199901012222';

多表更新后的表数据：
readerinfo:

readerfee:

6 多表删除

首先，准备2个表 bookcategory_bak和表bookinfo_bak：

-- 创建图书类别表bookcategory_bak
CREATE TABLE bookcategory_bak AS SELECT * FROM bookcategory;
-- 创建图书信息表bookinfo_bak
CREATE TABLE bookinfo_bak AS SELECT * FROM bookinfo;

查看表bookcategory_bak，会发现：

-- 1.由于业务需求，需要删除图书类别表中在图书信息表中没有图书记录的类别
DELETE t1 FROM bookcategory_bak t1
LEFT JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_id
WHERE parent_id != 0 AND book_id IS NULL;

删除之后再查看bookcategory_bak：

会发现，列“眼科学”已经被删除了。
对于要求2，我们先进行查看表数据：

SELECT book_id, book_name, category FROM bookcategory_bak t1
INNER JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_id ORDER BY category DESC;

结果:

DELETE t1, t2 FROM bookcategory_bak t1 INNER JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_id
WHERE t1.category_id = 3;

再次查看数据：

发现编程语言分类和相关的图书信息都已经被删除了。我们还可以查看表bookinfo_bak和表bookcategory_bak验证我们的结果。