【MySQL 第九天 多表连接|内连接|外连接|自连接|多表更新|多表删除】

【MySQL 第九天 多表连接|内连接|外连接|自连接|多表更新|多表删除】

【1】mysql多表连接查询的介绍

【1】inner:内部的
【2】join:连接
【3】outer:外部的

-- 将bookparent表的book_id设置为bookinfo表的外键
alter table bookparent add foreign key(book_id) references bookinfo(book_id);
-- 多表连接
-- 通过查看图书信息表和图书类别表来获取图书编号-图书名称-图书类别
-- 简单来说将不同表的数据重新组合成一张新表
select book_copy_id,author,parent_id,price from bookinfo inner join bookparent on 
bookinfo.book_id = bookparent.book_id;

on 后面的比较数据必须要有相同的值和数据类型 不然也会报警

【2】mysql内连接

select borrowinfo.book_id,bookinfo.book_name,borrowinfo.card_id,author,return_date,status
from borrowinfo inner join bookinfo on bookinfo.book_id = borrowinfo.book_id
inner join readerinfo on borrowinfo.card_id = readerinfo.card_id
where borrowinfo.status ='否';

select …中间这段表示想要显示的列，来自于不同的表 … inner join
inner join 代表内连接 那个表
on 后面这段表示两个表之间存在相同的数据类型和值 显示的也是只有两者都有的数据
最后的where 表示在这些列中只看没有归还书的

【3】mysql外连接

-- 左连接 需要显示全部的在左边即bookparent 显示满足条件的在bookinfo
select bookparent.book_id,bookparent.book_name,bookinfo.book_name from 
bookparent left join bookinfo on bookparent.book_id = bookinfo.book_id;

-- 右连接 需要显示全部的在右边即bookparent 显示满足条件的在bookinfo
select bookparent.book_id,bookparent.book_name,bookinfo.book_name from 
bookinfo right join bookparent on bookparent.book_id = bookinfo.book_id;

【4】mysql自链接

-- 查看图书类别信息表的数据
select * FROM bookparent;

-- 什么是上级分类名称：可以说是优先级从属的关系 比如：编程语言 数据库的上级是计算机
-- 眼科学 临床医学上级是医学  而计算机 医学就没有上级了最大级 这个分类是数据库的额功能
-- 自连接 [外连接的一种特殊形式]其实是看成内外连接的扩展 只不过操作的表都是同一张表 as是给列名起别名

select a.book_id as '图书类别编号',a.book_name as '图书类别名称',b.book_name 
as '图书的上级分类名称' from bookparent a left join bookparent b on a.parent_id = b.book_id;

-- 自连接 [内连接的一种特殊形式] 过滤掉上级的名称
select a.book_id as '图书类别编号',a.book_name as '图书类别名称',b.book_name 
as '图书的上级分类名称' from bookparent a inner join bookparent b on a.parent_id = b.book_id;

== 三张图，从上到下==

知道条件就不会太理解： a.parent_id = b.book_id;
你比如parent_id = 0时 他的book_id = 1 —>=3和4的parent_id =1

【5.1】mysql表起别名 列起别名

表起别名：tab_name tab_alias
列起别名：column_name as column_alias

【5】mysql多表更新

-- 查看借阅信息表
select *from borrowinfo;
-- 查看扣费信息表
select *from readerfee;

-- 多表连接 改变  actual_return_date  book_fee  balance 的列的值
update readerfee t1 inner 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 =1 and t1.card_id ='52214685259788';

【6】mysql多表删除

【6.1】mysql表的快捷复制和创建

-- 创建一个表 并将已存在的表作为这个表的别名 即复制
create table book_bak
as 
select *from bookparent;

create table bookinfo_bak
as 
select *from bookinfo;

-- 查看是否存在
select *from book_bak;select *from bookinfo_bak;

-- 删除业务需求 需要删除图书类别表中的在图书信息表中没有图书记录的类别
select bookinfo_bak.book_id,bookinfo_bak.book_name,book_bak.book_name from book_bak 
left join bookinfo_bak 
on book_bak.book_id = bookinfo_bak.book_id where parent_id<>0;

-- 通过以上查询出来后 开始删除
delete t1 from book_bak t1 left join bookinfo_bak t2 on t1.book_id = t2.book_id
where parent_id<>0 and t2.book_id is null;

-- 例如：眼科学已经删除
select *from book_bak;

下一节 自定义函数，持续更新