• 二、数据库查询语句(多表查询篇)

    二、数据库查询语句(多表查询篇)

    1、笛卡尔积

    ​ 前面涉及的都是单张表的查询，如果我们的查询条件相对比较复杂，需要涉及多张表进行查询，如果是两张无关的表联合查询，列出所有的可能的结果，如下图：

    在这里插入图片描述

    • 如果没有连接条件，则是以左表为驱动表，从左表的第一条数据开始和右表的每一条数据相拼接组成的集合，叫做笛卡尔积，如上图共有4种情况，但我们实际需要的只是两条。
    • 没有连接条件时，我们必须列举所有的可能性，就会产生上边的一张大表，如果两个表的数据量变大，比如每张表1000条数据，那笛卡尔积，就会扩张到1百万，如果是三张表关联，就必须再乘以1000，只要是多表连接，就会涉及到笛卡尔积，所以笛卡尔积是一个很恐怖的事情，我们要依据情况加上合适的连接条件，过滤掉我们不需要的数据。
    • 多表连接的方式主要分为：
      1. 交叉连接、自然连接、JOIN…USING、JOIN…ON
      2. 自连接、内连接、外连接

    2、多表连接

    该篇所需要的数据在文章最底部。

    1.CROSS JOIN

    简介：CROSS JOIN又称交叉连接，对连接的两张表记录做笛卡尔集，产生最终结果输出，分为无过滤条件和带过滤两种

    • 语法：select xxx from 模式.表1 cross join 模式.表2

    • 无过滤条件：student表和scores表做交叉连接

      • 代码：

        -- 交叉连接
-- 10条数据 42 条数据
select count(*) from other.student;
select count(*) from other.scores;

-- 无过滤条件,结果是420条数据
select count(*) from other.student cross join other.scores;
select count(*) from other.student,other.scores;

-- 带过滤条件,结果是42条数据
select count(*) from other.student t cross join other.scores s where t.id = s.s_id;

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11

      • 效果图：在这里插入图片描述

    2.NATURAL JOIN

    简介：NATURAL JOIN又称为自然连接，自然连接会自动把两种表的同名列进行连接条件，进行等值连接，我们称这样的连接为自然连接。

    自然连接具有以下特点：

    1. 连接表中存在同名列
    2. 如果有多个同名列，则会产生多个等值连接条件
    3. 如果连接表中的同名列类型不匹配，则报错处理
    • 语法：select xxx from 模式.表1 natural join 模式.表2

    • 代码：

      -- 自然连接
create table other.t1(id int,name varchar(32));
create table other.t2(id int,age int);
insert into other.t1 values(1,'张三');
insert into other.t1 values(2,'李四');
insert into other.t1 values(3,'王五');
insert into other.t2 values(2,18);
insert into other.t2 values(3,20);
commit;

select * from other.t1 natural join other.t2;

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11

    • 效果图：在这里插入图片描述

    3.JOIN…USING

    简介：这是自然连接的另一种写法，JOIN 关键字指定连接的两张表，USING 指明连接列。要求USING 中的列存在于两张连接表中

    • 语法：select xxx from 模式.表1 join 模式.表2 using(列名)

    • 代码：

      select * from other.t1 join other.t2 using(id);

      • 1

    • 效果图：在这里插入图片描述

    4.JOIN…ON

    简介：这是一种连接查询的常用写法，说明是一个连接查询。JOIN 关键字指定连接的两张表， ON 子句指定连接条件表达式，其中不允许出现 ROWNUM。具体采用何种连接方式，由数据库内部分析确定

    • 语法：select xxx from 模式.表1 join 模式.表2 on 连接条件

    • 代码：查询学生的姓名和对应的成绩

      -- 查询学生的姓名和对应的成绩
select st.name,s.score from other.student st join other.scores s on st.id = s.s_id;

      • 1
      • 2

    • 效果图：在这里插入图片描述

    5.自连接

    简介：数据表与自身进行连接，我们称这种连接为自连接。 自连接查询至少要对一张表起别名，否则，服务器无法识别要处理的是哪张表，太过简单，不做演示

    • 语法：select xxx from 模式.表1 别名, 模式.表2 别名
    select * from other.t1 m,other.t2 m where m.id = n.id;

    • 1

    6.内连接

    简介：根据连接条件，结果集仅包含满足全部连接条件的记录，我们称这样的连接为内连接，与上述join on相同，对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中

    • 语法：select xxx from 模式.表1 inner join 模式.表2 on 连接条件

         通俗讲就是根据条件，找到表 A 和 表 B 的数据的交集。

    在这里插入图片描述

    • 代码：下列三条sql语句等效

      -- 查询学生的姓名和对应的成绩
select st.name,s.score from other.student st inner join other.scores s on st.id = s.s_id;
select st.name,s.score from other.student st join other.scores s on st.id = s.s_id;
select st.name,s.score from other.student st,other.scores s where st.id = s.s_id;

      • 1
      • 2
      • 3
      • 4

    • 效果图：在这里插入图片描述

    7.外连接（常用）

    简介：外连接对结果集进行了扩展，会返回一张表的所有记录，对于另一张表无法匹配的字段 用 NULL 填充返回。

    DM 数据库支持三种方式的外连接：左外连接、右外连接、全外连接。

    外连接中常用到的术语：左表、右表。根据表所在外连接中的位置来确定，位于左侧的 表，称为左表；位于右侧的表，称为右表。例如 SELECT * FROM T1 LEFT JOIN T2 ON T1.C1=T2.D1，T1 表为左表，T2 表为右表，对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】

    • 语法：select xxx from 模式.表1 left/right join 模式.表2 on 连接条件

        我们可以使用一个图形来形容左外连接的效果：

    在这里插入图片描述

    • 代码：下面两条语句等效

      -- 外连接
-- 查询学生的姓名和对应的成绩
select st.name,s.score from other.student st left outer join other.scores s on st.id = s.s_id;
select st.name,s.score from other.student st left join other.scores s on st.id = s.s_id;

      • 1
      • 2
      • 3
      • 4

    • 效果图：在这里插入图片描述

    8.总结

    内连接和外连接的区别：

    • 对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。
    • 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】。

    所以我们可以得出以下结论：

    • 对于左外连接查询的结果会包含左表的所有数据
    • 对于右外连接查询的结果会包含右表的所有数据

    外连接的关键字是【outer join】 也可以省略outter，连接条件一样需要使用【on】关键字；

    3、所需数据

    学习之前我们需要创建数据库并填充部分数据：

    drop TABLE if EXISTS student;
CREATE TABLE other.student (
    id INT PRIMARY key,
    name VARCHAR (10),
    age INT  NOT NULL,
    gender varchar(2)
);
drop TABLE if EXISTS course;
CREATE TABLE other.course (
  id INT   PRIMARY key,
  name VARCHAR  ,
  t_id INT  
) ;
drop TABLE if EXISTS teacher;
CREATE TABLE other.teacher(
  id INT   PRIMARY key,
  name VARCHAR  
);
drop TABLE if EXISTS scores;
CREATE TABLE other.scores(
  s_id INT ,
  score INT ,
  c_id INT,
	PRIMARY key(s_id,c_id)
) ;
commit;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    表单填充数据：

    insert into  other.student (id,name,age,gender)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');

insert into  other.course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  other.teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');


insert into  other.scores (s_id,score,c_id)VALUES(1,80,1);
insert into  other.scores (s_id,score,c_id)VALUES(1,56,2);
insert into  other.scores (s_id,score,c_id)VALUES(1,95,3);
insert into  other.scores (s_id,score,c_id)VALUES(1,30,4);
insert into  other.scores (s_id,score,c_id)VALUES(1,76,5);

insert into  other.scores (s_id,score,c_id)VALUES(2,35,1);
insert into  other.scores (s_id,score,c_id)VALUES(2,86,2);
insert into  other.scores (s_id,score,c_id)VALUES(2,45,3);
insert into  other.scores (s_id,score,c_id)VALUES(2,94,4);
insert into  other.scores (s_id,score,c_id)VALUES(2,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(3,65,2);
insert into  other.scores (s_id,score,c_id)VALUES(3,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(3,37,4);
insert into  other.scores (s_id,score,c_id)VALUES(3,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(4,66,1);
insert into  other.scores (s_id,score,c_id)VALUES(4,39,2);
insert into  other.scores (s_id,score,c_id)VALUES(4,85,3);

insert into  other.scores (s_id,score,c_id)VALUES(5,66,2);
insert into  other.scores (s_id,score,c_id)VALUES(5,89,3);
insert into  other.scores (s_id,score,c_id)VALUES(5,74,4);


insert into  other.scores (s_id,score,c_id)VALUES(6,80,1);
insert into  other.scores (s_id,score,c_id)VALUES(6,56,2);
insert into  other.scores (s_id,score,c_id)VALUES(6,95,3);
insert into  other.scores (s_id,score,c_id)VALUES(6,30,4);
insert into  other.scores (s_id,score,c_id)VALUES(6,76,5);

insert into  other.scores (s_id,score,c_id)VALUES(7,35,1);
insert into  other.scores (s_id,score,c_id)VALUES(7,86,2);
insert into  other.scores (s_id,score,c_id)VALUES(7,45,3);
insert into  other.scores (s_id,score,c_id)VALUES(7,94,4);
insert into  other.scores (s_id,score,c_id)VALUES(7,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(8,65,2);
insert into  other.scores (s_id,score,c_id)VALUES(8,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(8,37,4);
insert into  other.scores (s_id,score,c_id)VALUES(8,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(9,66,1);
insert into  other.scores (s_id,score,c_id)VALUES(9,39,2);
insert into  other.scores (s_id,score,c_id)VALUES(9,85,3);
insert into  other.scores (s_id,score,c_id)VALUES(9,79,5);

insert into  other.scores (s_id,score,c_id)VALUES(10,66,2);
insert into  other.scores (s_id,score,c_id)VALUES(10,89,3);
insert into  other.scores (s_id,score,c_id)VALUES(10,74,4);
insert into  other.scores (s_id,score,c_id)VALUES(10,79,5);
commit;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61

    查看创建的表：

    select * FROM OTHER.student;
select * FROM OTHER.scores;
select * FROM OTHER.course;
select * FROM OTHER.teacher;

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

  • 相关阅读:
    kubernetes RBAC
    threejs实现3d全景看房
    VoxWeekly｜The Sandbox 生态周报｜20230904
    字节面试流程及面试题无私奉献，吐血整理
    zookeeper选举机制原理
    ModuleNotFoundError: No module named ‘pycocotools‘解决办法
    ChatGPT调教指南 | 咒语指南 | Prompts提示词教程（一）
    Jest单元测试相关
    Linux系统移植框架简介
    LeetCode 面试题 16.18. 模式匹配
  • 原文地址：https://blog.csdn.net/qq_45659753/article/details/127980324