一文完整MySQL连接查询,笛卡尔乘积,内连接外连接交叉连接

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一文完整MySQL连接查询,笛卡尔乘积,内连接外连接交叉连接

文章目录

笛卡尔乘积

连接查询分类

等值连接

非等值连接

自连接

外连接

交叉连接

连接查询又称为多表查询,当查询的字段来自于多个表时,使用连接查询。


笛卡尔乘积


笛卡尔乘积现象:表1有m行,表2有n行,结果有m*n行


发生原因:没有有效的连接条件


避免:添加有效的连接条件


SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;


d82ca5f4511547298473892a236a8559.png


连接查询分类


按年代分:92标准(①),99标准(①②中的左右③)


按照功能分类:


①内连接:等值连接,非等值连接,自连接


②外连接:左外连接,右外连接,全外连接


③交叉连接


MySQL支持内连接,外连接(左外和右外),交叉连接


等值连接


1、92标准


①多表等值连接的结果为多表的交集部分

②n表连接,至少需要n-1个连接条件

③多表的顺序没有要求

①一般需要为表起别名

⑤可以搭配前面介绍的所有子句


SELECT 表1中字段,表2中字段
FROM 表1,表2 (表顺序可以换)
WHERE 表1.依据的字段=表2.依据的字段; (连接条件)


SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;


字段有歧义时,可以用表.限制,如果表面太长,可以给表起别名,而起别名后就不能用原表名了,需要使用别名。


SELECT `name`,`boyName`
FROM `beauty` AS a,`boys` As o
WHERE a.`boyfriend_id`=o.`id`;


可以添加筛选条件。


SELECT `last_name`,`department_name`,`commission_pct`
FROM `departments` AS d,`employees` AS e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct` IS NOT NULL;


组合分组查询。


#查询每个城市有多少个部门
SELECT COUNT(*) AS 个数,`city`
FROM `departments` AS d,`locations` AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY `city`;


2、99标准


SELECT `last_name`,`department_name`
FROM `employees` AS e
INNER JOIN `departments` AS d           #inner为内连接类型标识
ON e.`department_id`=d.`department_id`; #连接条件


#多表
SELECT `last_name`,`department_name`,`city`
FROM `employees` AS e
INNER JOIN `departments` AS d ON e.`department_id`=d.`department_id`
INNER JOIN `locations` AS l ON d.`location_id`=l.`location_id`
ORDER BY `department_name` DESC;


①可以添加排序、分组、筛选等子句

②inner可以省略

③筛选条件放在where后面, 连接条件放在on后面,提高分离性,便于阅读

④inner join连接和sq192语法中的等值连接效果是一样的,都是查询多表的交集


非等值连接


区别于等值连接在于连接条件不是=。


1、92标准


SELECT salary,grade_level
FROM employees AS e,job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;


也可以组合前面讲到的子句。


2、99标准


SELECT `salary`,`grade_level`
FROM `employees` AS e
INNER JOIN `job_grades` AS j
ON `salary` BETWEEN `lowest_sal` AND `highest_sal`;


也可以组合前面讲到的子句。


自连接


相当于是一张表里面的等值连接,即一张表里面的某两个字段相等。


1、92标准


SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` AS e,`employees` AS m
WHERE e.`manager_id`=m.`employee_id`


2、99标准


SELECT e.`last_name` AS 员工,m.`last_name` AS 领导
FROM `employees` AS e
INNER JOIN `employees` AS m
ON e.`manager_id`=m.`employee_id`;


同样可以组合其他子句。


外连接


应用场景:用于查询一个表中有,另一个表没有的记录


特点:

①外连接的查询结果为主表中的所有记录

②如果从表中有和它匹配的,则显示匹配的值

③如果从表中没有和它匹配的,则显示null

④外连接查询结果=内连接结果+主表中有而从表没有的记录


左外连接,left join左边的是主表

右外连接,right join右边的是主表

左外和右外交换两个表的顺序,可以实现同样的效果


#左外连接
SELECT be.`name`,bo.`boyName`
FROM `beauty` AS be
LEFT JOIN `boys` AS bo
ON be.`boyfriend_id`=bo.`id`;


#右外连接
SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
RIGHT JOIN `beauty` AS be
ON be.`boyfriend_id`=bo.`id`;


MySQL不支持全外连接

全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的


交叉连接


SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
CROSS JOIN `beauty` AS be;


相当于就是99标准的笛卡尔乘积



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5天前
|
关系型数据库 MySQL 数据库连接
Unity连接Mysql数据库 增 删 改 查
在 Unity 中连接 MySQL 数据库,需使用 MySQL Connector/NET 作为数据库连接驱动,通过提供服务器地址、端口、用户名和密码等信息建立 TCP/IP 连接。代码示例展示了如何创建连接对象并执行增删改查操作,确保数据交互的实现。测试代码中,通过 `MySqlConnection` 类连接数据库,并使用 `MySqlCommand` 执行 SQL 语句,实现数据的查询、插入、删除和更新功能。
|
20天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
本文详细介绍了MySQL中的多表查询,包括多表关系、隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询及其实现方式,一文全面读懂多表联查!
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
|
24天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
177 0
|
2月前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
74 8
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
108 11
|
6天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
73 42
|
2月前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
71 3
|
2月前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
111 3
|
2月前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
109 2