多表关联查询-CSDN博客

# 创建主表
create table category(
    id int auto_increment,
    name varchar(20),
    primary key (id)
)default charset =utf8;

# 创建从表
create table products(
    id int auto_increment,
    title varchar(255),
    price decimal(11,2),
    category_id int,
    primary key (id),
    constraint fk_products_category foreign key(category_id) references category(id)
)default charset =utf8;

# 添加测试数据，一定要先添加主表数据
insert into category values (null,'手机');
insert into category values (null,'电脑');

# 再次添加从表数据
insert into products values (null,'小米Note 8',2499.00,1);
insert into products values (null,'华为Mate 60',6999.00,1);
insert into products values (null,'联想小新',4999.00,2);

2、查看外键约束以及删除外键约束

show create table products;
alter table products drop foreign key fk_products_category;

3、外键约束还可以实现级联更新以及级联删除操作

级联删除 => on delete cascade
级联更新 => on update cascade
级联更新 + 级联删除 => on update cascade on delete cascade

# 准备一张dage数据表
create table dage(
    id int auto_increment,
    name varchar(20),
    primary key(id)
)default charset =utf8;

# 准备一张xiaodi数据表
create table xiaodi(
    id int auto_increment,
    name varchar(20),
    dage_id int,
    primary key(id),
    constraint fk_xiaodi_dage foreign key(dage_id) references dage(id) on delete cascade
)default charset =utf8;

# 向主表中插入数据
insert into dage values (null,'张三');
insert into dage values (null,'李四');

# 向从表中插入数据
insert into xiaodi values (null,'小三',1);
insert into xiaodi values (null,'小四',1);
insert into xiaodi values (null,'小小',2);

级联删除主表中的李四，从表中的小小也被删除

# 将主表dage中李四删除，其从表xiaodi中，李四所属的小小也一并删除
delete from dage where name = '李四';

4、外键使用注意事项

注意：在实际应用中，MySQL底层有多种存储引擎（数据在计算机底层的存储方式） => myisam 和 innodb引擎
相同点：都可以用于存储数据
不同点：① myisam强调数据查询性能，支持全文索引；② innodb强调数据安全性，支持事务处理，支持外键约束；
设置方式
① 在MySQL安装配置时，强调底层存储引擎为InnoDB
② 在MySQL建表时，明确指定存储引擎为InnoDB引擎

三、关联查询操作

1、笛卡尔积（交叉连接）CROSS JOIN

2、有条件连接

有条件连接简介
内连接
左连接、右连接
全连接

笛卡尔积（交叉连接）

概念：也叫无条件连接，将左表的每一行数据无差别和右表的每一行数据分别进行关联，组成新的数据。

SELECT

*

FROM 表1 ——左表

JOIN 表2 ——右表 ; ---CROSS JOIN

或

SELECT

*

FROM 表1，表2 ;

交叉连接查询（cross join或者笛卡尔积查询）

基本语法：① select * from A表 cross join B表; ② select * from A表,B表;

select * from category cross join goods;
select * from category,goods;

结论：交叉连接，字段数 = A表字段数 + B表字段数；记录数 = A表记录数 * B表记录数

有条件连接

概念：在实际表数据连接的过程中，经常需要在连接时跟上一个限制的连接条件，满足条件的数据行才能进行连接，这就是有条件连接。

对于有条件连接，共分为4种不同的连接方式：

内连接（INNER JOIN）
左连接（LEFT JOIN）
右连接（RIGHT JOIN）
全连接（FULL JOIN）

SELECT

*

FROM 表1 ---左表

JOIN 表2 ---右表

ON 连接条件 ; ---比如：ON 表1.字段 = 表2.字段 ;

①内连接：INNER JOIN，简写为JOIN

返回两张表都满足连接条件的部分（交集）
左右两表关联时，满足关联条件的数据，才会出现在最终的关联结果中

内连接查询：在交叉连接的基础上，查询满足关联条件的结果。

例1：查询每个商品类别下有哪些商品（以分类为主，首先查询）

select * from category inner join goods on category.id = goods.category_id;

例2：查询每个商品所属的类别信息（以商品为主，首先查询）

select * from goods inner join category on goods.category_id = category.id;

例3：内连接查询的另外一种写法，交叉连接 + where子句实现
```
select * from category,goods where category.id = goods.category_id;
```

例4：使用表别名机制简化以上内连接查询

select
    *
from category c
join goods g on c.id = g.category_id;

例5:在多表连接时，实现字段筛选 => 筛选表中需要的字段即可（尽量不使用*号）

select
    c.id,
    c.name as cname,
    g.name as gname,
    g.price,
    g.score,
    g.is_self
from category c
join goods g on c.id = g.category_id;

②左外连接：LEFT OUTER JOIN ，简写为LEFT JOIN

左侧+交集部分
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左表中不能和右表关联的数据也会出现，右表侧自动填充为NULL

③右外连接：RIGHT OUTER JOIN ，简写为RIGHT JOIN

右侧+交集部分
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，右表中不能和左表关联的数据也会出现，左表侧自动填充为NULL

LEFT JOIN左外连接查询与 RIGHT JOIN右外连接查询

左外连接

select
    c.id,
    c.name as cname,
    g.name as gname,
    g.price,
    g.score
from category c
left join goods g on c.id = g.category_id;

右外连接

select 
    *
from category c 
right join goods g on c.id = g.category_id;

④全外连接：FULL OUTER JOIN ，简写为FULL JOIN

左侧 + 交集部分 + 右侧
左右两表关联时，除满足关联条件的数据会出现在最终的关联结果中，左右两表不能相互关联的数据也都会出现，对应侧自动填充为NULL
注意：MySQL数据库不支持全连接，需要将左连接和右连接的结果利用 UNION 关键字组合实现全连接的效果。