第九章:多表连接查询
多表链接查询是数据分析师日常工作中一定会使用到的一个知识点,可见它的重要程度有多大。今天这个章节总结了MySQL多表查询的几种情况,你知道内连接、外连接、自连接、多表更新,多表删除吗?不要觉得某些知识点用的少,就可以不知道,其实不然,至少别人写出来后,你要知道是什么意思。
本节目标:理解多表连接的操作
多表连接:多表连接查询是从多个表中获取数据。
多表连接的语法结构
table_reference
{[INNER | CROSS] JOIN} | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON condtional_expr;
案例:
通过查看图书信息表和图书类别表来获取图书编号、图书名称、图书类别
select book_id,book_name,category from bookinfo inner join bookcategory on bookinfo.book_category_id = bookcategory.category_id;
本节目标:熟悉内连接的操作。
内连接:根据连接条件从多个表中查询选择数据,显示这些表中与连接条件相匹配的数据行,组合成新记录。
SELECT column_list
FROM t1
[INNER] JOIN t2 ON join_condition1
[INNER JOIN t3 ON join_condition2
…]
WHERE where_conditions;
案例:
由于图书借阅统计的需要,想查询未归还图书的图书编号,图书名称,身份证号,姓名,电话,归还日期, 是否归还。
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 borrowinfo.status = '否'; 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 t1.status = '否';
本节目标:熟悉外连接的使用。
外连接:外连接将查询多个表中相关联的行。
外连接分为:
▶左外连接LEFT [OUTER] JOIN
显示左表全部记录,右表满足连接条件的记录。
▶右外连接RIGHT [OUTER] JOIN
显示右表全部记录,左表满足连接条件的记录。
语法结构
SELECT column_list
FROM t1
LEFT|RIGHT[OUTER]JON t2 ON join_condition1;
案例:
根据业务需要,我们需要查看图书类别表中的所有类别下都有哪些图书。
select book_id, book_name, category from bookcategory left join bookinfo on bookcategory.category_id = bookinfo.book_category_id where parent_id<>0; select book_id, book_name, category from bookinfo a right join bookcategory b on b.category_id = a.book_category_id; select * from bookcategory;
本节目标:熟悉自连接的使用
自连接:如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接查询。
自连接是一种特殊的连接查询,它是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表。
同一个数据表对其自身进行连接。
案例:查询所有图书类别的图书类别编号,类别名称,上级分类名称。
select * from bookcategory; select s.category_id as'图书类别编号', s.category as '图书类别名称', p.category as'图书的上级分类名称' from bookcategory s inner join bookcategory p on s.parent_id = p.category_id;
本节目标:熟悉多表更新的操作。
多表更新语法结构:
UPDATE
table1{[INNER]JOIN|{LEFT|RIGHT}[OUTER]JOIN} table2
ON conditional_expr
SET col1={expr1|DEFAULT}
[,col2={expr2|DEFAULT}]…
[WHERE where_condition]
案例:
今天身份证号为210210199901012222的读者将超限的图书20151101归还,根据描述实现如下需求:
1、更新借阅信息表,将借阅状态(status)更新为‘是’。
2、更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
3、同时更新读者信息表的余额。(在余额中扣除罚款金额)
select * from borrowinfo; select * from readerfee; update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_id set actual_return_date = sysdate(),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance - book_fee where t1.book_id = 20151101 and t1.card_id = '210210199901012222'; select * from readerinfo;
本节目标:熟悉多表删除的操作
多表删除语法:
DELETE table1[.*],table2[.*]
FROM table1{[INNER]JOIN|{LEFT|RIGHT}[OUTER]JOIN} table2
ON contitional_expr
[WHERE where_condition]
案例:
1、创建备份表
create table bookcategory_bak as select * from bookcategory; create table bookinfo_bak as select * from bookinfo; select * from bookcategory_bak; select * from bookinfo_bak;
2、由于业务需求,需要删除图书类别表中在图书信息表中没有图书记录的类别。
select book_id,book_name,category from bookcategory_bak t1 left join bookinfo_bak t2 on t1.category_id = t2.book_category_id where parent_id<>0; 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; select * from bookcategory_bak;
3、需要删除图书类别表的编程语言的类别,以及图书信息表中关于编程语言的图书记录。
select book_id,book_name,category_id,category from bookcategory_bak t1 inner join bookinfo_bak t2 on t1.category_id = t2.book_category_id; 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;