当前位置: 开发笔记 > 编程语言 > 正文

Mysql基础（四）——多表查询内键外键、union、join、子查询、in/exists

作者：cool瀚_325 | 来源：互联网 | 2023-09-25 12:38

1.到底什么是主键，外键？基本概念：MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须

Mysql基础（四）—— 多表查询内键外键、union、join、子查询、in/exists

1. 到底什么是主键，外键？

基本概念：

MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。

用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接

主键作用：

能够唯一表示数据表中的每个记录的字段或字段组合；
与其他表中的数据进行关联（不同表中各记录间的简单指针）；
主键不能是空值；
唯一约束是指用于指定一个或多个列的组合值具有唯一性，防止重复，所有找主键值对用户而言没有什么意义的。

外键：

C是表A的主键，B中也有C字段，则C是表B的外键
外键约束主要用来维护两个表之间数据的一致性
A为基本表，B为信息表，两个表的C字段列必须是数据类型相似，比如 int和tinyint可以，而int和char则不可以

方法1

建表指明外键：

create table 表B（... ,

　　　　　　　　　CONSTRAINT `外键名称` FOREIGNKEY(`外键字段名称`) REFERENCES 外键A表名(`主键字段名称`) ON DELETE SET NULL ON UPDATE SET NULL

　　　　　　　　) charset=utf8mb4;

例如：　　　　　　　　

create table employee(

　　　　　　　　　　　no int comment "员工编号",

　　　　　　　　　　　name varchar(20) comment "姓名",

　　　　　　　　　　　 job varchar(40) comment "职位",

　　　　　　　　　　　mgr int comment "上司id",

　　　　　　　　　　　hiredate date comment "雇佣时间",

　　　　　　　　　　　sal double(10, 2) comment "工资",

　　　　　　　　　　 comm double(10, 2) comment "奖金", 　　

　　　　　　　　　　deptno int comment "部分id",

　　　　　　　　　　CONSTRAINT `fk` FOREIGNKEY(`deptno`) REFERENCES dept(`deptno`) ONDELETESET NULL ONUPDATESET NULL

　　　　　　　　　　) charset=utf8mb4;

方法2

后期为表添加外键语法：

alter table 表B add constraint `外键名称` foreign key(`外键字段名称`) references 外键A表名(`主键字段名称`) on delete set null on update set null;

例如：

alter table employee add CONSTRAINT `fkdd` FOREIGN KEY(`deptno`) REFERENCES dept(`deptno`) ON DELETE SET NULL ON UPDATE SET NULL;

外键的几种模式：

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动，默认值）
CASCADE（跟随外键改动，表A影响到表B）
SET NULL（设空值，B表的外键列不能为NOT NULL）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）

外键优点：数据一致性，减少写代码的量，保证数据可靠性。

外键缺点：会影响性能，为保证数据完整性会使得并发性降低。

外键与id方式优缺点相反

2. 并集（union）　　　　　　　　

并集（union）也叫联合查询

union和union all 区别：

union:可以去重

union all:返回所有记录，效率高于union

在多个select语句中，第一个语句中的字段将被用于结果的字段名称显示

例如：

SELECT id, name, age FROM student

UNION ALL

SELECT id, name, age FROM teacher;

SELECT id, name, age FROM student 
UNION
SELECT age, name, id FROM teacher;

在联合查询中，当使用 ORDER BY 的时候，需要对 SELECT 语句添加括号，并且与LIMIT结合使用才生效

如：

(SELECT classId, id, name, age FROM student WHERE classId = 1 ORDER BY age DESC LIMIT 2) 
UNION
(SELECT classId, id, name, age FROM student WHERE classId = 2 ORDER BY age);

也可对最终结果进行排序：（...) union (...) order by id;

3. 笛卡尔积

先确定数据要用到哪些表，将多个表先通过笛卡尔积变成一个表

然后去除不符合逻辑的数据（根据两个表的关系去掉）

最后当做一个虚拟表来加上条件即可

A表m条数据，B表n条数据，一共产生m*n条数据

当然也可自交

例如：select * from 表A,表B；

4. 内连接外连接

内连接：join，inner join

外连接：left join，left outer join，right join，right outer join，union，union al

交叉连接：cross join

on: ...... on A.id=B.id;

用using简化当两张表的列相同：.......using (id)

where:也可用where 代替on

先看图

内连接

select * from a inner join b on a.id = b.id;
select * from a join b on a.id = b.id;
select * from a, b where a.id = b.id;

当表A中的一条记录对应表B中的多条记录时，会以重复的方式对应多条表B记录出现在结果集中。

当表B中的一条记录对应表A中的多条记录时，会以重复的方式对应多条表A记录出现在结果集中。

左连接：

也叫左外连接

select * from a left join b on a.id = b.id;
select * from a left outer join b on a.id = b.id;

左外连接，会以左边的表A为主表，返回所有行，即使右表B中没有匹配的行。

如果左边的表A在右表B中找不到一条记录，则返回表A所有记录并且表B相应的字段设为null。

如果左边的表A在右表B中找到多条记录，则以相同表A记录和不同表B记录多条显示在结果集中。

这种情况下，其实是把表A中所有记录都查询出来了，包括不满足条件的记录。

如果我们只想查出表A中满足条件的：

select * from a left join b on a.id = b.id where b.id is null;

右连接：

也叫右外连接

select * from a right join b on a.id = b.a_id;
select * from a right outer join b on a.id = b.a_id;

如果只想查表B满足要求的：

select * from a right join b on a.id = b.id where a.id is null;

全连接：

mysql并不支持全连接，不过有相应的替代方案，就是left join union right join 来代替。

select * from a left join b on a.id = b.id
union
select * from a right join b on a.id = b.id;

如果只想显示所有不满足条件的记录，则通过如下语句：

select * from a left join b on a.id = b.a_id where b.a_id is null
union
select * from a right join b on a.id = b.a_id where a.id is null;

交叉连接：

交叉连接实际上就是表A与表B的笛卡尔乘积

select * from a cross join b;
select * from a, b;

5. 子查询

概念：当一个查询时另一个查询的条件时，称之为子查询（内层查询）

使用原因：很多时候两张表可能非常大，做笛卡尔积之后，更大，会死机

子查询优点：减少查询次数！

好习惯：先通过select count(*)的方式查看记录数。

一般在where或者from子句中出现，外层的select为主查询。

子查询出现位置：

where子句子查询：返回单行单列，多行单列，单行多列数据.多行单列子查询通常包含 in ,any,all,exists关键字
from子句子查询：返回多行多列的数据，可以做一张临时表，要求必须起别名
select后面:仅支持标量子查询

#案例1：谁的工资比 Abel 高?

#①查询Abel的工资

SELECT salary
FROM employees
WHERE last_name = "Abel"；

#②查询员工的信息，满足 salary>①结果

SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = "Abel"
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

#①查询141号员工的job_id

SELECT job_id
FROM employees
WHERE employee_id = 141；

#②查询143号员工的salary

SELECT salary
FROM employees
WHERE employee_id = 143

#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);

#案例3：返回公司工资最少的员工的last_name,job_id和salary

#①查询公司的最低工资

SELECT MIN(salary)
FROM employees；

#②查询last_name,job_id和salary，要求salary=①

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#①查询50号部门的最低工资

SELECT  MIN(salary)
FROM employees
WHERE department_id = 50;

#②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

#③ 在②基础上筛选，满足min(salary)>①

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 50;

#案例：查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);

select后面/*仅仅支持标量子查询

#案例：查询每个部门的员工个数

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 个数
FROM departments d;

any关键字：

　　　　=any ：和in关键字一样

　　　　>any (>=any)：比最小的大

案例：查询工资大于最小的经理的员工姓名和工资

select name,sale from employ where sal>any(
select sal from employ where job="manager");

all关键字：

　　　　>all：大于所有

案例：查询薪资比任意一个经理都要高的员工和薪资

select name,sale from employ where sal>all(
select sal from employ where job="manager");

6. 关键字：in和exists

exists返回的是一个布尔类型 True False

案列：显示所有不在部门的员工信息

select * from dept where not exists( select * from employee where deptno=dept.deptno);

in和exists的区别1：执行原理

　　外表 in 内表

　　外表 exists 内表

in：先遍历内表，再索引外表，故外表用大表

exists：先遍历外表，再索引内表，故内表用大表

那么外表大的用in,内表大的用exists，增加效率

区别2：字段限制

in 有字段限制

exists没有字段限制

例如：

select * from tabA where tabA.x in (select x from tabB where y>0 );

参考文献：

https://blog.csdn.net/haluoluo211/article/details/52638366

https://blog.csdn.net/qq_34306360/article/details/79717682

https://www.jb51.net/article/180763.htm

https://blog.csdn.net/qq_26594041/article/details/89438382

推荐阅读

format
掌握MySQL数据库的基础语法与核心操作

本文详细介绍了MySQL数据库的基础语法与核心操作，涵盖从基础概念到具体应用的多个方面。首先，文章从基础知识入手，逐步深入到创建和修改数据表的操作。接着，详细讲解了如何进行数据的插入、更新与删除。在查询部分，不仅介绍了DISTINCT和LIMIT的使用方法，还探讨了排序、过滤和通配符的应用。此外，文章还涵盖了计算字段以及多种函数的使用，包括文本处理、日期和时间处理及数值处理等。通过这些内容，读者可以全面掌握MySQL数据库的核心操作技巧。 ... [详细]

蜡笔小新 2024-11-11 23:39:51
stream
MySQL初级篇——字符串、日期时间、流程控制函数的相关应用

文章目录：1.字符串函数2.日期时间函数2.1获取日期时间2.2日期与时间戳的转换2.3获取年月日、时分秒、星期数、天数等函数2.4时间和秒钟的转换2. ... [详细]

蜡笔小新 2024-11-14 10:57:02
text
IOS Run loop详解

为什么80%的码农都做不了架构师？转自http:blog.csdn.netztp800201articledetails9240913感谢作者分享Objecti ... [详细]

蜡笔小新 2024-11-13 12:14:35
timestamp
MySQL 5.7 学习指南：SQLyog 中的主键、列属性和数据类型

本文介绍了 MySQL 5.7 中主键（Primary Key）和自增（Auto-Increment）的概念，以及如何在 SQLyog 中设置这些属性。同时，还探讨了数据类型的分类和选择，以及列属性的设置方法。 ... [详细]

蜡笔小新 2024-11-12 15:57:04
text
php更新数据库字段的函数是,php更新数据库字段的函数是

php更新数据库字段的函数是,php更新数据库字段的函数是 ... [详细]

蜡笔小新 2024-11-12 11:37:31
timestamp
MySQL学习之路(二)——数据类型和操作数据表

数据类型和操作数据表2.1MySQL类型之整型2.2MySQL数据类型之浮点型2.3日期时间型DATE1支持时间：1000年1月1日~9999年12月31日DATETIME ... [详细]

蜡笔小新 2024-11-12 10:11:09
list
MySQL Decimal 类型的最大值解析及其在数据处理中的应用艺术

在关系型数据库中，表的设计与SQL语句的编写对性能的影响至关重要，甚至可占到90%以上。本文将重点探讨MySQL中Decimal类型的最大值及其在数据处理中的应用技巧，通过实例分析和优化建议，帮助读者深入理解并掌握这一重要知识点。 ... [详细]

蜡笔小新 2024-11-11 19:36:19
header
Java 中 com.apollographql.apollo.api.internal.Optional.orNull() 方法详解与示例

本文详细介绍了 com.apollographql.apollo.api.internal.Optional 类中的 orNull() 方法，并提供了多个实际代码示例，帮助开发者更好地理解和使用该方法。 ... [详细]

蜡笔小新 2024-11-14 15:03:23
format
Android Studio SQLite 数据库增删改查简单（代码参考）

一个建表一个执行crud操作建表代码importandroid.content.Context;importandroid.database.sqlite.SQLiteDat ... [详细]

蜡笔小新 2024-11-14 11:01:49
java
浅析python实现布隆过滤器及Redis中的缓存穿透原理_python

本文带你了解了位图的实现，布隆过滤器的原理及Python中的使用，以及布隆过滤器如何应对Redis中的缓存穿透，相信你对布隆过滤 ... [详细]

蜡笔小新 2024-11-13 16:43:07
include
Halcon之图像梯度、图像边缘、USM锐化

图像梯度、图像边缘、USM锐化图像梯度、图像边缘、USM锐化图像梯度、图像边缘、USM锐化图像卷积：1.模糊2.梯度3.边缘4.锐化1.视频教程：B站、 ... [详细]

蜡笔小新 2024-11-12 21:40:43
stream
Python 使用 DOM 和 SAX 解析 XML 的应用实例

本文介绍如何使用 Python 的 DOM 和 SAX 方法解析 XML 文件，并通过示例展示了如何动态创建数据库表和处理大量数据的实时插入。 ... [详细]

蜡笔小新 2024-11-12 16:10:39
list
如何在Java中使用DButils类

这期内容当中小编将会给大家带来有关如何在Java中使用DButils类，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。D ... [详细]

蜡笔小新 2024-11-12 13:46:11
text
优化后的标题：Apache Cassandra数据写入操作详解

本文详细解析了 Apache Cassandra 中的数据写入操作，重点介绍了 INSERT 命令的使用方法。该命令主要用于将数据插入到指定表的列中，其基本语法为 `INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...)`。通过具体的示例和应用场景，文章深入探讨了如何高效地执行数据写入操作，以提升系统的性能和可靠性。 ... [详细]

蜡笔小新 2024-11-11 20:05:30
settings
MDT2010实验部署手册（一）

MicrosoftDeploymentToolkit2010部署培训实验手册V1.0目录实验环境说明3实验环境虚拟机使用信息3注意：4实验手册正文说 ... [详细]

蜡笔小新 2024-11-12 20:02:27