SQL-多表查询

于 2024-08-20 04:56:33 发布
阅读量443 收藏 6
点赞数 16
文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Broken_x/article/details/141339616
版权

        在关系型数据库中，多表查询是一个至关重要的概念，它允许我们从多个表中提取相关数据。今天将详细介绍多表查询的基本概念、不同的多表关系类型、常用的SQL关键字以及如何通过SQL语句实现这些查询。

1. 多表关系类型

多表关系主要分为以下几种类型：

  • 一对一关系：每个表中的记录与另一个表中的记录一一对应。例如，一个用户可以有一个唯一的教育记录。
  • 一对多关系：一个表中的记录可以与另一个表中的多条记录相关联。例如，一个部门可以有多个员工。
  • 多对多关系：两个表中的记录可以相互关联，通常通过第三张表来实现。例如，学生和课程之间的关系，一个学生可以选修多门课程，而一门课程也可以被多个学生选修。

1.1 多对多关系示例

        在多对多关系中，我们需要建立第三张表来存储两个表之间的关联。以下是关于学生、课程及其关系的示例。

学生表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    no VARCHAR(10) COMMENT '学号'
) COMMENT = '学生表';

INSERT INTO student (id, name, no) VALUES
(NULL, '熊绮丝', '2000100101'),
(NULL, '谢进', '2000100102'),
(NULL, '殷天正', '2000100103'),
(NULL, '夷一笑', '2000100104');
课程表 
CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '课程名称'
) COMMENT = '课程表';

INSERT INTO course (id, name) VALUES
(NULL, 'Java'),
(NULL, 'PHP'),
(NULL, 'MySQL'),
(NULL, 'Hadoop');
学生课程关系表
CREATE TABLE student_course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student(id),
    CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course(id)
) COMMENT = '学生课程关系表';

INSERT INTO student_course VALUES
(NULL, 1, 1),
(NULL, 1, 2),
(NULL, 1, 3),
(NULL, 2, 2),
(NULL, 2, 3),
(NULL, 3, 4);

1.2. 一对一关系示例

        一对一关系指的是两个表中的一条记录与另一条记录相对应。以下是用户基本信息表和用户教育信息表的示例。

用户基本信息表
CREATE TABLE tb_user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    age INT COMMENT '年龄',
    gender CHAR(1) COMMENT '1: 男, 2: 女',
    phone CHAR(11) COMMENT '手机号'
) COMMENT = '用户基本信息表';

INSERT INTO tb_user (id, name, age, gender, phone) VALUES
(NULL, '黄斌', 45, '1', '18800001111'),
(NULL, '冰冰', 35, '2', '18800002222'),
(NULL, '码云', 55, '1', '18800008888'),
(NULL, '李彦宏', 50, '1', '18800009999');
用户教育信息表
CREATE TABLE tb_user_edu (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    degree VARCHAR(20) COMMENT '学历',
    major VARCHAR(50) COMMENT '专业',
    primaryschool VARCHAR(50) COMMENT '小学',
    middleschool VARCHAR(50) COMMENT '中学',
    university VARCHAR(50) COMMENT '大学',
    userid INT UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id)
) COMMENT = '用户教育信息表';

INSERT INTO tb_user_edu (id, degree, major, primaryschool, middleschool, university, userid) VALUES
(NULL, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
(NULL, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
(NULL, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
(NULL, '本科', '应用数学', '阳泉区第一小学', '阳泉区第一中学', '清华大学', 4);

2. 多表查询

多表查询可以分为两大类：连接查询和子查询。

2.1 连接查询

连接查询用于从多个表中提取相关数据，常见的连接类型包括内连接、外连接和自连接。

2.1.1 内连接

        内连接用于返回两个表中匹配的记录，可以理解为返回两个集合的交集部分。可以通过隐式和显式两种方式实现。

  • 隐式内连接：
SELECT emp.name, dept.name 
FROM emp, dept 
WHERE emp.dept_id = dept.id; -- 隐式内连接
  • 显式内连接：
SELECT e.name, d.name 
FROM emp AS e 
INNER JOIN dept AS d ON e.dept_id = d.id; -- 显式内连接
2.1.2 外连接

外连接用于返回一个表中的所有记录以及与另一个表中匹配的记录 

  • 左外连接：返回左表的所有记录，即使右表中没有匹配的记录。
SELECT e.*, d.name 
FROM emp e 
LEFT JOIN dept d ON e.dept_id = d.id; -- 左外连接
  • 右外连接：返回右表的所有记录，即使左表中没有匹配的记录。
SELECT e.*, d.name 
FROM emp e 
RIGHT JOIN dept d ON e.dept_id = d.id; -- 右外连接
 2.1.3 自连接

自连接是表与自身的连接，通常使用别名来区分

SELECT e.name, m.name 
FROM emp e 
INNER JOIN emp m ON e.managerid = m.id; -- 自连接

 2.2 子查询

子查询是嵌套在其他查询中的查询，可以分为内查询和外查询。

2.2.1 标量子查询

标量子查询返回单个值，通常用于比较。

SELECT * 
FROM emp 
WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部'); -- 查询销售部所有员工信息
2.2.2 列子查询

列子查询返回一列值，通常用于IN条件中。

SELECT * 
FROM emp 
WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('销售部', '研发部')); -- 查询销售部和研发部所有员工信息
2.2.3 行子查询

行子查询返回一行值，通常用于比较多列。

SELECT * 
FROM emp 
WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = '张无忌'); -- 查询和张无忌的直属领导工资相同的员工信息
2.2.4 表子查询

表子查询返回多行值，通常用于JOIN操作中。

SELECT e.*, d.name AS '部门名称' 
FROM emp e 
INNER JOIN dept d ON e.dept_id = d.id 
WHERE d.name = '研发部'; -- 查询研发部员工信息并显示部门名称

 3. 常用关键字

在多表查询中，有一些关键字是非常重要的，以下是一些常用的关键字及其说明：

  • ANY：用于比较子查询返回的多个值，至少有一个值满足条件。
SELECT * 
FROM emp 
WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));
  • ALL：用于比较子查询返回的所有值，所有值都必须满足条件。
SELECT * 
FROM emp 
WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));
  • JOIN：用于连接多个表，常见的有INNER JOIN、LEFT JOIN、RIGHT JOIN等。
  • UNION：用于合并多个SELECT语句的结果，默认去重。
SELECT * FROM emp WHERE salary < 5000 
UNION 
SELECT * FROM emp WHERE salary > 15000; -- 合并两张表，并过滤条件
  • UNION ALL：与UNION类似，但不去重。
SELECT * FROM emp WHERE salary < 5000 
UNION ALL 
SELECT * FROM emp WHERE salary > 15000; -- 合并两张表，包含重复记录

 还有一些 >= > < <= =

 4. 总结

        多表查询是数据库操作中不可或缺的一部分，通过合理使用连接查询和子查询，能够有效地提取和分析数据。理解多表关系及其查询方式，将有助于更高效地进行数据管理和分析。

        下面是所有源码：


--                      ---------------------------多表查询------------------------------

    -- 多表关系：一对一、一对多、多对多

    -- 多对多关系：
        -- 建立第三张表，第三张表中包含两个外键，分别指向两个主表


-- 学生表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    no VARCHAR(10) COMMENT '学号'
) COMMENT = '学生表';

INSERT INTO student (id, name, no) VALUES
(NULL, '熊绮丝', '2000100101'),
(NULL, '谢进', '2000100102'),
(NULL, '殷天正', '2000100103'),
(NULL, '夷一笑', '2000100104');

-- 课程表
CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '课程名称'
) COMMENT = '课程表';

INSERT INTO course (id, name) VALUES
(NULL, 'Java'),
(NULL, 'PHP'),
(NULL, 'MySQL'),
(NULL, 'Hadoop');

-- 学生课程关系表
CREATE TABLE student_course (
    id int auto_increment primary key comment '主键ID',
    student_id INT  not null COMMENT '学生ID',
    course_id INT not null COMMENT '课程ID',
    constraint fk_student_id foreign key (student_id) references student(id),
    constraint fk_course_id foreign key (course_id) references course(id)
) COMMENT = '学生课程表';

insert into student_course  values(null, 1, 1),(null, 1, 2),(null, 1, 3),(null, 2, 2),(null, 2, 3),(null, 3, 4);

    -- 一对一关系

create table tb_user (
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男, 2: 女',
    phone char(11) comment '手机号'
) comment = '用户基本信息表';


create table tb_user_edu (
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment = '用户教育信息表';

insert into tb_user (id, name, age, gender, phone) values
(null, '黄斌', 45, '1', '18800001111'),
(null, '冰冰', 35, '2', '18800002222'),
(null, '码云', 55, '1', '18800008888'),
(null, '李彦宏', 50, '1', '18800009999');

insert into tb_user_edu (id, degree, major, primaryschool, middleschool, university, userid) values
(null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
(null, '本科', '应用数学', '阳泉区第一小学', '阳泉区第一中学', '清华大学', 4);


    -- 多表查询

    -- 多表查询分类 ：连接查询、子查询

    -- 连接查询：
        -- 内连接：
            # 隐式内连接：select * from 表1,表2 where 表1.字段 = 表2.字段;
            # 显式内连接：select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;


select * from emp,dept where emp.dept_id = dept.id; -- 消除笛卡尔积，重复数据

-- 查询员工的姓名和所属部门的名称
select emp.name, dept.name from emp,dept where emp.dept_id = dept.id; -- 隐式内连接
select e.name, d.name from emp as e,dept as d where e.dept_id = d.id; -- 别名隐式连接

select e.name, d.name from emp as e inner join dept as d on e.dept_id = d.id; -- 显式内连接


     -- 外连接：select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
            -- 左外连接：select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
            -- 右外连接：select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

select e.*, d.name from emp e left outer  join dept d on e.dept_id = d.id;   -- 左外连接
select e.*, d.name from emp e right outer join dept d on e.dept_id = d.id;   -- 右外连接


    -- 自连接：当前表与自身的连接查询，必须使用别名
            # 自连接查询：select * from 表1 as t1 join 表1 as t2 on t1.字段 = t2.字段;
            # 自连接查询：select * from 表1 as t1 inner join 表1 as t2 on t1.字段 = t2.字段;
select e.name, m.name from emp e ,emp m where e.managerid = m.id;   -- 自连接
select e.name as '员工姓名', m.name as '直属领导姓名' from emp e inner join emp m on m.id = e.managerid;   -- 自连接


    -- 联合查询：查询多个表的数据，并将多个表的数据合并成一张表

#注意区分 union 和 union all 的区别，union 去重，union all 不去重

select * from emp where salary < 5000 union  select * from emp where salary > 15000;     -- 合并两张表，并过滤条件


    -- 子查询：
        -- 内查询：select * from 表1 where 字段 in (select 字段 from 表2);
        -- 外查询：select * from 表1 where 字段 in (select 字段 from 表2 where 条件);
        -- 子查询：select * from 表1 where 字段 in (select 字段 from 表2 where 条件);

        # 标量子查询  列子查询  行子查询  表子查询

-- 标量子查询：查询单个值
select * from emp where dept_id = (select id from dept where name = '销售部');   -- 销售部所有员工信息

-- 列子查询：查询一列值
select * from emp where dept_id in (select id from dept where name in ('销售部','研发部'));   -- 销售部和研发部所有员工信息
# 查询比研发部任意一个员工工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
# 查询比研发部所有员工工资高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '研发部'));

-- 行子查询：查询一行值
#和张无忌的直属领导，工资相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

-- 表子查询：查询多行值
# 查询部门为研发部的员工信息
select * from emp where dept_id = (select id from dept where name = '研发部');
# 查询部门为研发部的员工信息，并显示员工的部门名称
select e.*, d.name as '部门名称' from emp e inner join dept d on e.dept_id = d.id where d.name = '研发部';
# 查询部门为研发部的员工信息，并显示员工的部门名称，并显示员工的直属领导姓名
select e.*, d.name as '部门名称', m.name as '直属领导姓名' from emp e inner join dept d on e.dept_id = d.id inner join emp m on e.managerid = m.id where d.name = '研发部';

  最后制作不易，点赞支持一下吧……

Bro_cat
关注
  • 16
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
结合mybatis-plus实现简单不需要写sql的多表查询
08-25
通过这种方式，MyBatis-Plus 实现了不需要手写 SQL 的多表查询，极大地简化了开发过程。不过，值得注意的是，虽然这种方式方便快捷，但在复杂查询场景下可能不如直接编写 SQL 那样灵活。因此，理解 SQL 语句和数据库...
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
01-30
在学习和应用这些SQL标准时，理解BNF语法对于编写正确和高效的SQL查询至关重要。通过深入研究BNF，开发者可以更好地掌握SQL的底层逻辑，从而编写出符合标准、易于理解和维护的代码。在实际开发中，SQL的熟练运用能够...
SQL判断语句用法和多表查询
09-09
在SQL（Structured Query Language）中，判断语句和多表查询是两个非常重要的概念，它们在数据处理和分析中起到关键作用。以下是对这两个主题的详细说明。 1. **SQL判断语句** SQL中的判断语句通常指的是CASE语句...
sql-formatter：用于不同查询语言的空格格式化程序
01-30
SQL格式化程序 SQL Formatter是一个JavaScript库，用于漂亮地打印SQL查询。 它起初是的端口，但此后却大相径庭。 SQL格式化程序支持以下方言： sql- mariadb - mysql - PostgreSQL - db2 - plsql - n1ql - redshift-...
SQLSERVER课件-函数多表查询(ppt文档).ppt
11-16
SQLSERVER课件-函数多表查询(ppt文档).ppt
SQL FOREIGN KEY 约束
m0_50736744的博客
08-16 421
SQL FOREIGN KEY 约束一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
SQL，将多对多的关联记录按行输出
smilejingwei的博客
08-19 148
要求从两张表中找出符合条件W=100,H=500,D=300的记录，先输出Primay里的一行，再输出相关联的Secondary的每一行。A3：A2为空时返回空，A2不为空时，循环A1的每条记录 ~，合并 ~ 和 A2，最后再合并循环的结果。数据库的Primary表和Secondary表有相同的结构，其中W、H、D是主键。A1、A2：执行简单SQL，条件查询。
SQL - 基础大汇总
心如冰清，天塌不惊
08-16 456
【代码】数据库 - 基础。
SQL - 多表查询
心如冰清，天塌不惊
08-18 398
【代码】SQL - 多表查询。
浅谈SQL Server、Oracle和MySQL
a876106354的博客
08-15 781
SQL Server 是Microsoft公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点，可跨越从运行MicrosoftWindows 98的膝上型电脑到运行2012 的大型多处理器的服务器等多种平台使用。Microsoft SQL Server 是一个全面的数据库平台，使用集成的商业智能(BI)工具提供了企业级的数据管理。数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序。MySQL是一个。
SQLALchemy 数据的 CRUD 操作
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
08-15 327
SQLAlchemy 是一个流行的 SQL 工具包和对象关系映射（ORM）库，用于 Python。它提供了一个高级接口来与数据库交互，允许开发者通过 Python 对象来执行 CRUD（创建、读取、更新、删除）操作，而无需编写大量的 SQL 语句。
SQL的连接查询与pandas的对应关系
C'mon的博客
08-16 721
在SQL和Pandas中，连接查询（join）是处理数据集之间关系的重要工具。
鸿萌数据恢复服务：SQL Server 中的 GAM、SGAM、IAM，及数据库损坏的修复方法
sanyuan7783的博客
08-16 971
全局分配映射 (GAM) 负责处理数据库内的分配区，有助于在需要时识别空闲区。共享分配映射 (SGAM) 页类似于粒度图，处理数据库中特定文件组的扩展分配。索引分配映射 (IAM) 页是特殊的内部页，其作用类似于数据存储的注册表，跟踪数据页在数据库文件中的位置。
Python编码系列—Python SQL与NoSQL数据库交互：深入探索与实战应用
u013889591的专栏
08-17 841
在数据驱动的现代应用开发中，数据库扮演着核心角色。Python作为一种灵活的编程语言，提供了与SQL和NoSQL数据库交互的强大工具。本文将深入探讨Python如何与SQL和NoSQL数据库进行交互，并展示在实际项目中的应用案例。SQL（Structured Query Language）数据库以其结构化查询和事务性操作而广受欢迎。而NoSQL（Not Only SQL）数据库则以其灵活的数据模型、水平扩展能力和高性能等特点，应对了大数据和高并发的挑战。
HiveSQL\SparkSQL的json高性能解析方案
weixin_43451620的博客
08-16 339
Hive\Spark高性能解析json字符串
RazorSQL for Mac/Win：强大的跨平台多功能SQL数据库编辑器RazorSQL for Mac/Win：功能强大的跨平台 SQL 数据库编辑器
2401_82627223的博客
08-17 425
RazorSQL 是一款功能全面、性能卓越的 SQL 数据库编辑器，无论是专业的数据库开发人员，还是数据库管理的初学者，都能从中受益，是您处理数据库相关工作的得力助手。
SQL-约束篇
最新发布
Broken_x的博客
08-19 487
SQL约束是数据库设计中不可或缺的一部分，通过合理使用各种约束，可以确保数据的完整性和一致性。在实际应用中，开发者应根据需求灵活运用这些约束，以提高数据管理的效率和安全性。
[高频sql50ti]第1484题，按日期分组销售产品
weixin_45201305的博客
08-19 502
5. 长度限制：`GROUP_CONCAT()`的结果长度受限于系统变量`group_concat_max_len`，默认值为1024字节。对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。- SEPARATOR：可选，用于指定连接字符串的分隔符，默认为逗号`,`。- DISTINCT：可选，用于去除重复值。
《Linux运维实战：达梦DM8数据库（联机）物理备份之SQL方式归档备份与恢复》
东城绝神
08-14 541
《Linux运维实战：达梦DM8数据库（联机）物理备份之SQL方式归档备份与恢复》
sql join中能否使用case when_SQL---多表查询
05-27
是的，可以使用CASE WHEN语句在SQL JOIN中进行多表查询。在多表查询中，我们通常需要根据不同的条件来关联表格。CASE WHEN语句可以让我们根据条件返回不同的值，从而实现更加灵活的查询。 以下是一个示例代码： ```sql SELECT t1.id, t1.name, CASE WHEN t2.salary > 5000 THEN '高薪' WHEN t2.salary > 3000 THEN '中薪' ELSE '低薪' END AS salary_level FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id ``` 在上面的代码中，我们使用了CASE WHEN语句来根据t2表中的salary字段的值返回不同的薪资等级。在SQL JOIN中，我们使用LEFT JOIN将t1和t2表连接在一起，然后使用CASE WHEN语句在SELECT语句中返回计算后的结果。 需要注意的是，CASE WHEN语句可以在SELECT、WHERE、ORDER BY等语句中使用，可以根据需要进行灵活调整和修改。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值