MySQL多表查询

@听风吟

已于 2024-07-15 19:31:13 修改

阅读量542

点赞数 21

分类专栏：数据库文章标签： mysql 数据库

于 2024-07-15 18:23:18 首次发布

本文链接：https://blog.csdn.net/m0_70882914/article/details/140445728

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

SQL多表查询
扩展: 就想要使用别名
优先级: from > where > select

SQL多表查询

表与表之间的关系

这种关系指的是表与表之间数据的关联性，分别有：

（1）一对一关系(1:1)

（2）一对多关系(1:N) [常见]

（3）多对多关系(M:N) [常见]

一对一关系，就是在数据表A中的一条数据，在数据表B中都有一个与之对应的结果。

例如，用户表与用户详情表的关系。
在这里插入图片描述

简化：

在这里插入图片描述

总结：

（1）表与表之间的关系有（3）种，分别是一对一、一对多、多对多；

（2）注意：在实际应用中，一对一关系极为少见，因为可以通过创建一张数据表来完成。

一对多关系

一对多关系是实际开发中，是表与表之间关系里应用最多的。例如，分类表category与商品表product，其中，商品表里的cno就是外键约束字段。

在这里插入图片描述

说明：

分类表、商品表这两张表都有主键，而商品表提供的cno是外键字段。

总结：

（1）通常情况下，一对多关系建表原则：在【多】的一方有个字段，作为外键字段指向【一】的一方的主键字段。

多对多关系

多对多关系理解起来比较复杂，但通常会分解成多个一对多关系来关联处理。

比如，老师表与学生表。

值得注意的是，多对多关系创建表原则：

通常需要创建第三张表（综合表），并作为中间表，且该表至少有两个字段（都是外键），分别作为外键字段指向各自一方的主键。

通俗地说，就是将一个多对多关系拆分成：两个一对多关系的数据表。

例如，学生和课程的关系，即多个学生对应多个课程。

在这里插入图片描述

说明：

学生表、课程表这两张表都有主键，而第三张表【学生选课表】提供的两个字段sno、cno都是外键字段。

==注意：==为了提升多对多关系表的理解，通常做法：引入一个第三方表。

关联表的注意事项

当数据表之间产生关联后，需要注意：

（1）不能给表B（外键）添加一个表A不存在的主键字段值；

（2）不能删除表B（外键）已使用表A存在的主键字段值。

说明：

当数据表产生关联后，要多观察能否添加或删除数据值。

总结：

（1）如果无法给多张表添加或删除数据时，记得查看外键约束字段的数值是否有误；

（2）注意：如果要让有关联的两张表进行处理时，要将MySQL设定为InnoDB引擎。

子查询

子查询指的是将一条select语句的结果，作为另一条select语句的一部分（如：查询条件、查询结果、表等），来进行数据查询。

子查询语法：

select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select语句的结果);

说明：

在MySQL中，查询操作支持嵌套使用，其中，子查询就是嵌套使用的例子。

例如，使用命令完成：

（1）使用数据库db_product3下的商品表和分类表来操作；

（2）查询商品表、分类表的所有数据信息；

（3）查询分类为"服装"的所有商品信息；[分类id、商品]

（4）查询商品名称为"格力"的分类信息；

（5）仅查询分类为"化妆品"下的所有商品名称。

#############子查询###########
# 1.使用库
use db_product3;
# 2.查看所有数据
select * from category;
select * from product;
# 3.查看分类为服装的商品信息   --通过分类表来查看商品
# a.先查找分类为服装的所有分类信息
# select * from category where cname='服装';
select cid from category where cname='服装';
# b.查找分类为服装的商品信息
select * from product where category_id='c200';
# 综合
select * from product where category_id=(select cid from category where cname='服装');
# 4.查看格力的分类信息  --通过商品表找分类信息
select * from category where cid=(select category_id from product where pname='格力');
# 5.查看化妆品的所有商品信息  -通过分类表查找商品信息
select * from product where category_id =(select cid from category where cname='化妆品');

多表查询

多表查询，也可称为连接查询，可用于实现多个数据表之间的数据查询。

注意：当查询的字段数据来自不同的数据表时，就可以使用多表查询来完成。

多表查询可以分为4类：

（1）交叉连接查询
（2）内连接查询
（3）外连接查询
（4）自连接查询

例如，使用命令完成：

（1）商品类别表信息有分类编号cid、名称cname；

（2）商品表信息有商品编号pid、名称pname、价格price、是否上架flag、外键category_id（关联商品类别表的分类编号cid）；

（3）添加数据到两个数据表中。

###################添加数据##########################
create database if not exists bj96db_product4 charset utf8;
use bj96db_product4;
# 多表查询
create table table_category
(
    cid   varchar(20) primary key,
    cname varchar(40)
) engine = InnoDB default charset utf8;

create table table_product
(
    pid         varchar(20) primary key,
    pname       varchar(40),
    price       double,
    flag        varchar(2), # 是否上架标记为：1表示上架、0表示下架
    category_id varchar(20),
    foreign key (category_id) references table_category(cid)  # constraint products_fk
) engine = InnoDB default charset utf8;

# 分类
insert into table_category(cid, cname) values('c001', '电器');
insert into table_category(cid, cname) values('c002', '服装');
insert into table_category(cid, cname) values('c003', '化妆品');

# 商品
insert into table_product(pid, pname, price, flag, category_id) values('p001', '联想', 5000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p002', '海尔', 3000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p003', '雷神', 5000, '1', 'c001');

insert into table_product (pid, pname, price, flag, category_id) values('p004', 'JACK JONES', 800, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p005', '真维斯', 200, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p006', '花花公子', 440, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p007', '劲霸', 2000, '1', 'c002');

insert into table_product (pid, pname, price, flag, category_id) values('p008', '香奈儿', 800, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p009', '相宜本草', 200, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p010', '迪奥', 1600, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p011', '一叶子', 799, '1', 'c003');

交叉连接

通常地，交叉连接查询的结果是：两个数据表的并集，也称为笛卡尔积。

笛卡尔积是指在数学中，两个集合X和Y的笛卡尔乘积，又称直积。
表示为X × Y，第一个对象是X的成员，而第二个对象是Y的所有可能有序对的其中一个成员。

交叉连接查询的语法：

select 字段名 from A表名, B表名;

或者：

select 字段名 from A表名 cross join B表名;

例如，使用交叉查询命令完成：

（1）使用语法1查询类别表和商品表中的所有商品信息；

（2）使用语法2查询类别表和商品表中的所有商品信息。

########################交叉连接查询###########################################
# 1.使用库
use bj96db_product4;
select * from table_category;
select * from table_product;
# 2.使用语法1交叉查询结果
# select * from table_category,table_product;  # 查询结果没意义
# 3.使用语法2交叉查询结果
# select * from table_category cross join table_product;
# 扩展
select * from table_category,table_product;
# 表名 as 别名
# 表名 别名
select * from table_category as c,table_product as p;
select * from table_category c,table_product p;
# 建议:多表查询时给表名取别名

内连接查询

内连接查询指的是查询两个数据表中，符合条件的共有记录，即交集。

在这里插入图片描述

内连接查询需要使用join关键字。

对于内连接查询，可分为两类，分别是：隐式内连接和显式内连接。

（1）作为了解，隐式内连接是指没有inner join关键字，而条件直接使用where来指定，语法：

select 字段名 from A表名, B表名 where 条件;

（2）显式内连接是指需要使用inner join … on语句，可以省略inner，语法：

select 字段名 from A表名 别名 inner join B表名 别名 on 条件 [where 查询条件];

select 字段名 from A表名 别名 join B表名 别名 on 条件 [where 查询条件];

例如，使用内连接查询命令完成：

（1）使用隐式内连接查询类别表和商品表的共有数据信息；

（2）使用显式内连接来查询类别表和商品表的公共商品信息；

（3）使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息，并去除重复值。

########################内连接查询###################################
# 1.使用库
use bj96db_product4;
# 2.隐式内连接
# select * from table_category, table_product where table_category.cid = table_product.category_id;
# select * from table_category c, table_product p where c.cid = p.category_id;
# 3.显式内连接
select * from table_category c inner join table_product p on c.cid=p.category_id;
select * from table_category c join table_product p on c.cid=p.category_id;
# DG
select * from table_category c join table_product tp on c.cid = tp.category_id;
# 4.显式内连接，去重
select * from table_category tc join table_product tp on tc.cid = tp.category_id;
select distinct cid from table_category tc join table_product tp on tc.cid = tp.category_id;

外连接查询

左外连接

在多表查询操作中，外连接查询需要使用关键字outer join，一般应用中会省略outer，而直接使用join。

此外，外连接查询还可分为两类，分别是：

（1）左外连接查询

（2）右外连接查询

为了区分A表名和B表名，简易命名为左表和右表。

在这里插入图片描述

左外连接也简称为左连接，是以左表为主并根据条件来查询右表数据。

在这里插入图片描述

说明：

如果根据条件查询，当右表数据不存在时，则会使用null值来填充。

左外连接查询需要使用left join关键字，语法：

select 字段名 from A表名 别名 left [outer] join B表名 别名 on 条件;

说明：

（1）left join指的是左外连接查询关键字；

（2）on后面的是连接查询条件；

（3）A表名是指左表，B表名是指右表。

例如，使用左外连接查询命令完成：

（1）以左表为主，连接查询类别表和商品表中的所有商品数据信息。

################################左连接##############################################
# 1.使用库
use bj96db_product4;
# 2.查询结果
select * from table_category c left outer join table_product p on c.cid=p.category_id;
select * from table_category c left join table_product tp on c.cid = tp.category_id;  # 省略outer

右外连接

右外连接也简称为右连接，以右表为主并根据条件来查询左表数据。

在这里插入图片描述

说明：

如果根据条件查询，当左表数据不存在时，则会使用null填充值。

右外连接查询需要使用right join关键字，语法：

select 字段名 from A表名 别名 right [outer] join B表名 别名 on 条件;

说明：

（1）right join指的是右外连接查询关键字；

（2）on后面的是连接查询条件；

（3）A表名是指左表，B表名是指右表。

例如，使用右外连接查询命令完成：

（1）以右表为主，连接查询类别表和商品表中的所有类别数据信息。

####################右外连接查询##########################
# 1.使用库
use bj96db_product4;
# 2.右外连接查询数据
select * from table_category c right outer join table_product p on c.cid=p.category_id;
select * from table_category c right join table_product p on c.cid=p.category_id;

左、右外连接的区别

仔细观察操作的数据结果，会发现左外连接与右外连接都是一样的。

那么，它们有什么区别呢？

（1）当左表有数据为null时，左外连接查询的结果中，右表的数据结果都为null值；
（2）当右表有数据为null时，右外连接查询的结果中，左表的数据结果都为null值。

例如，使用命令完成：

（1）给类别表添加一条名称为null的数据；

（2）给商品表添加一条外键category_id为null的数据；

（3）查询类别表、商品表的数据结果，观察数据是否已成功添加；

（4）使用左外连接以左表为主，连接查询类别表和商品表中的所有商品数据信息，观察查询结果；

（5）使用右外连接以右表为主，连接查询类别表和商品表中的所有类别数据信息，观察查询结果。

######################了解左右外连接的查询区别###############################
# 1.使用库
use bj96db_product4;
# 2.设定类别表cname=null
desc table_category;
insert into table_category values ('A666',null);
# 3.设定商品表category_id=null
desc table_product;
insert into table_product(pid,pname, price, flag, category_id) values ('Y888','西红柿',5.9,'1',null);
# 4.查询结果
select * from table_category;
select * from table_product;
# 5.左外连接
select * from table_category c left join table_product tp on c.cid = tp.category_id;
# 6.右外连接
select * from table_category c right join table_product tp on c.cid = tp.category_id;

总结：

（1）注意：要验证左右外连接的区别，建议给某表的值设定为null，并观察效果。

自连接查询

介绍与添加数据

自连接查询,就是数据表自己和自己连接，然后进行数据比较。

其实，自连接查询指的是：左表和右表都是同一个表，根据连接查询条件来查询两个表中的数据。

特别注意：

在进行连接查询操作时，必须为数据表定义别名！

使用自连接完成简易区域表，效果图：

在这里插入图片描述

例如，使用命令完成：

（1）创建一个区域表tb_area，信息有省编号pid为整型自增且主键、名称name为字符串、城市编号city_id为整型；

（2）给区域表tb_area添加批量数据，以(1,‘广东省’,null)、(4,‘深圳市’,1)形式让城市和省份进行关联；

（3）查询所有数据结果。

#############################自连接###################################
# 1.新建库
create database if not exists bj96db_area charset utf8;
# 2.使用库
use bj96db_area;
# 3.创建表
create table if not exists tb_area(
    pid int primary key auto_increment,
    name varchar(20),
    city_id int
) engine = InnoDB default charset utf8;
desc tb_area;
# 4.插入数据   广东、河南、湖南
insert into tb_area(name, city_id) values ('广东省',null);  # 1
insert into tb_area(name, city_id) values ('河南省',null);  # 2
insert into tb_area(name, city_id) values ('湖南省',null);  # 3
# 深圳、广州、东莞
insert into tb_area(name, city_id) values ('广州市',1);
insert into tb_area(name, city_id) values ('深圳市',1);
insert into tb_area(name, city_id) values ('东莞市',1);

# 郑州、洛阳、商丘
insert into tb_area(name, city_id) values ('郑州市',2);
insert into tb_area(name, city_id) values ('洛阳市',2);
insert into tb_area(name, city_id) values ('商丘市',2);

# 长沙、岳阳、衡阳
insert into tb_area(name, city_id) values ('长沙市',3);
insert into tb_area(name, city_id) values ('岳阳市',3);
insert into tb_area(name, city_id) values ('衡阳市',3);

# 5.查看数据
select * from tb_area;

查询结果

内连接查询语法：

select * from 表A t1,表A t2 where t1.xxid=t2.xxid;

例如，使用自连接查询命令完成：

（1）把区域表tb_area分别理解成两个表：省表province、城市表city；

（2）自连接查询省份编号、省名、城市名、城市编号的展示结果；

（3）自连接查询省的名称为广东省的所有城市信息。

###########################自连接#####################################
# 1.使用库
use bj96db_area;
# 2.自连接: 省表、城市表
select
    *
from tb_area province
join tb_area city
on
    province.pid = city.city_id;

select
    province.pid,
    province.name,
    city.pid,
    city.name
from tb_area province
join tb_area city
on
    province.pid = city.city_id;
# 取别名给列
select
    province.pid as pid,
    province.name as pname,
    city.pid as cid,
    city.name as cname
from tb_area province
join tb_area city
on
    province.pid = city.city_id;
# 扩展：能否给列名取中文别名?  --不建议
select
    province.pid as 省ID,
    province.name as 省名称,
    city.pid as 城市ID,
    city.name as 城市名称
from tb_area province
join tb_area city
on
    province.pid = city.city_id;

# 3.显示名称为: 广东
select
    province.pid as pid,
    province.name as pname,
    city.pid as cid,
    city.name as cname
from tb_area province
join tb_area city
on
    province.pid = city.city_id
where province.name='广东省';

select
    city.pid as cid,
    city.name as cname
from tb_area province
join tb_area city
on
    province.pid = city.city_id
where province.name='广东省';

# 扩展: 就想要使用别名
select *
from
    (
        select
            province.pid  as pid,
            province.name as pname,
            city.pid      as cid,
            city.name     as cname
        from
            tb_area province
                join tb_area city
                     on
                         province.pid = city.city_id
    ) as temp
where
    pname = '广东省';
# 优先级: from > where > select

总结：

（1）自连接查询常用于省市的多级关联处理；

（2）注意：使用自连接查询可以形成一张虚拟的合并表。

tb_area city
on
province.pid = city.city_id
where province.name=‘广东省’;

扩展: 就想要使用别名

select *
from
(
select
province.pid as pid,
province.name as pname,
city.pid as cid,
city.name as cname
from
tb_area province
join tb_area city
on
province.pid = city.city_id
) as temp
where
pname = ‘广东省’;

优先级: from > where > select


> ==总结：==
>
> （1）自连接查询常用于省市的多级关联处理；
>
> （2）注意：使用自连接查询可以形成一张==虚拟==的合并表。

@听风吟

关注

21
点赞
踩
8

收藏

觉得还不错? 一键收藏
1
评论
MySQL多表查询

MySQL中的多表查询通常使用JOIN语句来实现，它允许你将两个或多个表通过表中的共同字段结合起来。
复制链接

扫一扫

专栏目录

MySQL多表查询

文章目录

SQL多表查询

表与表之间的关系

一对多关系

多对多关系

关联表的注意事项

子查询

多表查询

交叉连接

内连接查询

外连接查询

左外连接

右外连接

左、右外连接的区别

自连接查询

介绍与添加数据

查询结果

扩展: 就想要使用别名

优先级: from > where > select

“相关推荐”对你有帮助么？