AI 助理
备案控制台
开发者社区 数据库 文章 正文

mysql多表查询、函数查询

2024-04-08 126
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，高可用系列 2核4GB
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： mysql多表查询、函数查询

一、多表查询

1、多表查询概述

1.1、为什么要多表查询

执行多条单表查询语句延时

数据放在一个表出现字段数据冗余

1.2、笛卡尔积错误

select userid depname

from user ,dep

出现每个员工会出现在所有部门错误

正确的方式是需要有连接条件

select userid depname

from user ,dep

where user.depid=dep.id

1.3、多表查询注意

从sql优化角度而言，多表查询时，每个字段加上其所在的表

可以给表起别名，在select和where中使用别名，一旦起了别名，在select和where必须使用别名，原因还是根sql执行顺序有关

1.4、多表查询分类

角度1：等值连接、非等值

//等值

select userid depname

from user ,dep

where user.depid=dep.id

//非等值

select userid depname

from user ,dep

where user.id>12


角度2：自连接、非自连接

//自连接 查询员工id以及管理者id

select emp.id,mgr.id

from employee emp ,employee mgr

角度3：内连接 、外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

如：

select userid depname from user ,dep where user.depid=dep.id

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为主表 ，左边的表称为 从表 。

注意：一般涉及到所有字样就要使用外连接

sql92实现内连接如上

sql92实现外连接使用（+），但是mysql不支持sql92的外连接，Oracle 对 SQL92 支持较好

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

在mysql中，通常使用sql99语法查询，sql99支持内连接同时也支持外连接

2、sql99实现多表查询

内连接

SELECT 字段列表

FROM A表 INNER JOIN B表

ON 关联条件

WHERE 等其他子句;


注意：inner可以省略

左外连接 (left OUTER JOIN)

SELECT 字段列表

FROM A表 LEFT OUTER JOIN B表

ON 关联条件

WHERE 等其他子句;


注意：OUTER可以省略

右外连接(RIGHT OUTER JOIN)

FROM A表 RIGHT OUTER JOIN B表

ON 关联条件

WHERE 等其他子句;


注意：OUTER可以省略

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

3、UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT column,... FROM table1

UNION [ALL]

SELECT column,... FROM table2

查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'

UNION ALL

SELECT id,tname FROM t_usmale WHERE tGender='male';


4、 7种SQL JOINS的实现

这就是两张表共有的部分(内连接)，取交集。

SQL语句：

SELECT * FROM TABLEA A INNER JOIN TABLEB B ON A.KEY=B.KEY;

A独有的部分加上和A和B公共 的部分。也叫左外连接。

SQL语句：

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY;

这张图恰好跟左外连接相反（右外连接）。

SQL语句如下：

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY = B.KEY;

这张图就是A表独有的部分。

SQL语句如下：

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY

WHERE B.KEY IS NULL;

这张图是B表独有的部分。

SQL语句如下：

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY B.KEY

WHERE A.KEY IS NULL;

上面这张图表示的是两张表的所有部分。就是左外连接+右外连接在去重一次就搞定了（全连接，mysql中不支持，oracle中是支持的）。虽然MySQL不支持全连接的直接实现方式，但是提供了间接的实现方式，就是A表独有+B表独有，在去重一次。

SQL语句如下（正常全连接的SQL语句）：

SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B

ON A.KEY = B.KEY;

但是，在mysql中不支持上面这条语句。

MySQL实现全连接的SQL语句：

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY

UNION

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY = B.KEY;

这里解释一下关键字union：就是连接并去重的意思。

同理，这个模型是一个全外连接。

SQL语句如下：

SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B

ON A.KEY = B.KEY

WHERE A.KEY IS NULL OR B.KEY IS NULL;

在MySQL中上面这条语句还是不支持。但是，我们还是有间接的实现方式。其实就是第4和第5张图加起来去重就OK了。

MySQL中的语句如下：

SELECT * FROM TABLEA A LEFT JOIN TABLEB B

ON A.KEY = B.KEY

WHERE B.KEY IS NULL

UNION

SELECT * FROM TABLEA A RIGHT JOIN TABLEB B

ON A.KEY = B.KEY

WHERE A.KEY IS NULL;

UNOIN 关键字跟上面的作用一样。

5、 SQL99语法新特性

NATURAL

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。 在SQL92标准中：

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

ON e.`department_id` = d.`department_id`

AND e.`manager_id` = d.`manager_id`;


在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name

FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

USING (department_id);


能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name

FROM employees e ,departments d

WHERE e.department_id = d.department_id;

表连接的约束条件可以有三种方式：

WHERE, ON, USING WHERE：适用于所有关联查询

ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起 写，但分开写可读性更好。

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字 段值相等

#关联条件

#把关联条件写在where后面

SELECT last_name,department_name

FROM employees,departments

WHERE employees.department_id = departments.department_id;

#把关联条件写在on后面，只能和JOIN一起使用

SELECT last_name,department_name

FROM employees INNER JOIN departments

ON employees.department_id = departments.department_id;

SELECT last_name,department_name

FROM employees CROSS JOIN departments

ON employees.department_id = departments.department_id;

SELECT last_name,department_name

FROM employees JOIN departments

ON employees.department_id = departments.department_id;

#把关联字段写在using()中，只能和JOIN一起使用

#而且两个表中的关联字段必须名称相同，而且只能表示=

#查询员工姓名与基本工资

SELECT last_name,job_title

FROM employees INNER JOIN jobs USING(job_id);

#n张表关联，需要n-1个关联条件

#查询员工姓名，基本工资，部门名称

SELECT last_name,job_title,department_name FROM employees,departments,jobs

WHERE employees.department_id = departments.department_id

AND employees.job_id = jobs.job_id;

SELECT last_name,job_title,department_name

FROM employees INNER JOIN departments INNER JOIN jobs

ON employees.department_id = departments.department_id

AND employees.job_id = jobs.job_id;


二、单行函数

1、函数分类

我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是 被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很 差的，因此在使用函数的时候需要特别注意。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了 内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写 的

MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两 类： 单行函数 、 聚合函数（或分组函数） 。

2、数值函数

基本函数

角度和弧度函数

三角函数

对数函数

进制转换函数

3、字符串函数

4、 日期和时间函数

获取日期 时间

日期和时间转换

获取月份、星期、星期数、天数等

日期的操作函数

时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

日期函数应用

mysql获取当天,昨天,本周,本月,上周,上月的起始时间函数

#今天
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始';
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
 
#昨天
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始';
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
 
#上周
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';
 
#本周
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS '本周末';
 
#上面的本周算法会有问题,因为mysql是按照周日为一周第一天,如果当前是周日的话,会把时间定为到下一周
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一';
SELECT DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';
 
#上月
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初';
SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';
 
#本月
SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初';
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';

 

5、流程控制函数

6、加密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取

7、 MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。

8、其他函数


文章标签：
云数据库 RDS MySQL 版
密钥管理服务
关系型数据库
MySQL
SQL
数据库管理
数据库
关键词：
云数据库 RDS MySQL 版查询
云数据库 RDS MySQL 版函数
云数据库 RDS MySQL 版多表查询
云数据库 RDS MySQL 版多表查询函数
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
程序员一博
目录
相关文章
艾伦~耶格尔
|
2月前
|
SQL 缓存 监控
MySQL缓存机制：查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系，包括已弃用的查询缓存和核心的InnoDB缓冲池，帮助理解缓存优化原理。通过合理配置，可显著提升数据库性能，甚至达到10倍以上的效果。
艾伦~耶格尔
223 1
艾伦~耶格尔
|
2月前
|
SQL 存储 关系型数据库
MySQL体系结构详解：一条SQL查询的旅程
本文深入解析MySQL内部架构，从SQL查询的执行流程到性能优化技巧，涵盖连接建立、查询处理、执行阶段及存储引擎工作机制，帮助开发者理解MySQL运行原理并提升数据库性能。
艾伦~耶格尔
158 0
蓝易云
|
2月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
蓝易云
201 14
蓝易云
|
2月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
蓝易云
93 15
艾伦~耶格尔
|
2月前
|
SQL 监控 关系型数据库
SQL优化技巧：让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧，涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面，帮助开发者显著提升数据库性能，解决慢查询问题，适用于高并发与大数据场景。
艾伦~耶格尔
191 2
艾伦~耶格尔
|
2月前
|
SQL 关系型数据库 MySQL
MySQL入门指南：从安装到第一个查询
本文为MySQL数据库入门指南，内容涵盖从安装配置到基础操作与SQL语法的详细教程。文章首先介绍在Windows、macOS和Linux系统中安装MySQL的步骤，并指导进行初始配置和安全设置。随后讲解数据库和表的创建与管理，包括表结构设计、字段定义和约束设置。接着系统介绍SQL语句的基本操作，如插入、查询、更新和删除数据。此外，文章还涉及高级查询技巧，包括多表连接、聚合函数和子查询的应用。通过实战案例，帮助读者掌握复杂查询与数据修改。最后附有常见问题解答和实用技巧，如数据导入导出和常用函数使用。适合初学者快速入门MySQL数据库，助力数据库技能提升。
艾伦~耶格尔
151 4
艾伦~耶格尔
|
2月前
|
SQL 监控 关系型数据库
MySQL高级查询技巧：子查询、联接与集合操作
本文深入解析了MySQL高级查询的核心技术，包括子查询、联接和集合操作，通过实际业务场景展示了其语法、性能差异和适用场景，并提供大量可复用的代码示例，助你从SQL新手进阶为数据操作高手。
艾伦~耶格尔
72 0
顾翔
|
2月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
110 3
翼龙云TG_yilongcloud
|
2月前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程，需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS，可快速、平滑完成迁移任务，将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例，实现间接迁移上云。
翼龙云TG_yilongcloud
96 1
游客vphb4ae2je2zi
|
2月前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格：MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法，支持集中式与分布式架构。标准版2核4G年费1116元起，企业版最高性能达4核16G，支持HTAP与多级高可用，广泛应用于金融、政务、互联网等领域，TCO成本降低50%。
游客vphb4ae2je2zi
432 6

热门文章

最新文章

  • 1
    MySQL 与 Redis 如何保证双写一致性？
  • 2
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
  • 3
    MySQL中日期和时间函数的使用指南
  • 4
    MySQL分布式集群
  • 5
    MySQL · 引擎特性 · 像NOSQL那样使用MySQL
  • 6
    使用Nginx的stream模块实现MySQL反向代理与RabbitMQ负载均衡
  • 7
    新版mysql授权命令，缺一不可
  • 8
    无聊的MYSQL 备份脚本，零时备用着
  • 9
    【MySQL】wait_timeout=120，是干什么的？底层原理是什么？
  • 10
    基于amoeba实现MySQL读写分离
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    184
  • 2
    MySQL中日期和时间函数的使用指南
    136
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    276
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    200
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    275
  • 6
    MySQL源码编译安装
    171
  • 7
    MySQL二进制包安装
    189
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    298
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    159
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    448

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    阿里云对象存储OSS收费标准：500G存储118元1年、