MySQL学习笔记第六天

news/2025/2/14 0:12:08/

第06章多表查询

5. 7种SQL JOINS的实现

在这里插入图片描述
A是员工表，B是部门表。
在这里插入图片描述

5.7.1 代码实现

#8. UNION 和 UNION ALL的使用
# UNION：会执行去重操作
# UNION ALL:不会执行去重操作，效率优于前者，开发中优先使用
#结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，
#则尽量使用UNION ALL语句，以提高数据查询的效率。# 9.7种JOIN的连接
#A是员工表，B是部门表#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;#106条记录#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;#107条记录#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;#122#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;#1条记录#右中图：B - A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;#16条记录#左下图：满外连接
#方式1：左上图 UNION ALL 右中图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;#123条记录
#方式2：左中图 UNION ALL 右上图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL	#没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;#123条记录#右下图
#左中图 UNION ALL 右中图	
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;#17条记录

5.7.2 语法格式小结

在这里插入图片描述

左中图

#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

右中图

#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

左下图

#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

右下图

#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

6. SQL99语法新特性

6.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。
在这里插入图片描述
员工表和部门表中有两个字段可以连接：department_id 和 manager_id
在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;

在这里插入图片描述

6.2 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;
#或
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

在这里插入图片描述

第06章多表查询

7.章节小结

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

  • WHERE：适用于所有关联查询
  • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等
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;
#或
SELECT last_name,job_title,department_name 
FROM employees INNER JOIN departments 
ON employees.department_id = departments.department_id 
INNER JOIN jobs 
ON employees.job_id = jobs.job_id;

在这里插入图片描述
注意：
我们要控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让SQL查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

附录：常用的 SQL 标准有哪些

在正式开始讲连接表的种类时，我们首先需要知道 SQL 存在不同版本的标准规范，因为不同规范下的表连接操作是有区别的。
SQL 有两个主要的标准，分别是 SQL92 和 SQL99 。92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。当然除了 SQL92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。

这么多标准，到底该学习哪个呢？实际上最重要的 SQL 标准就是 SQL92 和 SQL99。 一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂，但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。只需要掌握一些核心的功能，满足日常工作的需求即可。

SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。 也正是在这两个标准发布之后，SQL 影响力越来越大，甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言，还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使用。

第07章单行函数

1.函数的理解

1.1 什么是函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率 ，又提高了可维护性 。在SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。
在这里插入图片描述
从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。

1.2 不同DBMS函数的差异

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

1.3 MySQL的内置函数及分类

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。

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

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

2. 数值函数

2.1 基本函数

函数用法
ABS(x)返回x的绝对值
SIGN(X)返回x的符号。正数返回1，负数返回-1，0返回0
PI()返回圆周率的值
CEIL(x)，CEILING(x)返回大于或等于某个值的最小整数
FLOOR(x)返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)返回列表中的最小值
GREATEST(e1,e2,e3…)返回列表中的最大值
MOD(x,y)返回x除以Y后的余数
RAND()返回0~1的随机值
RAND(x)返回0~1的随机值，其中x的值用作种子值，相同的x值会产生相同的随机数
ROUND(x)返回一个对x的值进行四舍五入后，最接近于x的整数
ROUND(x,y)返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面Y位
TRUNCATE(x,y)返回数字x截断为y位小数的结果
SQRT(x)返回x的平方根。当x的值为负数时，返回NULL

举例：

#第07章单行函数
#1.数值函数
#基本的操作
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23)
FROM DUAL;
#输出
+-----------+---------+-----------+----------+----------+-------------+-----------------+
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI()     | CEIL(32.32) | CEILING(-43.23) |
+-----------+---------+-----------+----------+----------+-------------+-----------------+
|       123 |      32 |        -1 |        1 | 3.141593 |          33 |             -43 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+
1 row in set (0.00 sec)SELECT FLOOR(32.32),FLOOR(-43.23),LEAST(1,2,3),GREATEST(1,2,3),MOD(12,5),12 MOD 5,12 % 5
FROM DUAL;
#输出
+--------------+---------------+--------------+-----------------+-----------+----------+--------+
| FLOOR(32.32) | FLOOR(-43.23) | LEAST(1,2,3) | GREATEST(1,2,3) | MOD(12,5) | 12 MOD 5 | 12 % 5 |
+--------------+---------------+--------------+-----------------+-----------+----------+--------+
|           32 |           -44 |            1 |               3 |         2 |        2 |      2 |
+--------------+---------------+--------------+-----------------+-----------+----------+--------+
1 row in set (0.00 sec)#取随机数(其中的参数称为种子，种子相同的两个RAND函数每次结果都一样且不变)
SELECT RAND(),RAND()
FROM DUAL;
#输出
+--------------------+---------------------+
| RAND()             | RAND()              |
+--------------------+---------------------+
| 0.4894483559666652 | 0.13874287171172245 |
+--------------------+---------------------+
1 row in set (0.00 sec)SELECT RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
#输出
+--------------------+--------------------+--------------------+--------------------+
| RAND(10)           | RAND(10)           | RAND(-1)           | RAND(-1)           |
+--------------------+--------------------+--------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)#四舍五入:RAND(操作数，保留小数的位数)
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2)
FROM DUAL;
#输出
+----------------+------------------+------------------+------------------+
| ROUND(123.556) | ROUND(123.456,0) | ROUND(123.456,1) | ROUND(123.456,2) |
+----------------+------------------+------------------+------------------+
|            124 |              123 |            123.5 |           123.46 |
+----------------+------------------+------------------+------------------+
1 row in set (0.00 sec)SELECT ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;
#输出
+-------------------+-------------------+
| ROUND(123.456,-1) | ROUND(153.456,-2) |
+-------------------+-------------------+
|               120 |               200 |
+-------------------+-------------------+
1 row in set (0.00 sec)#截断操作:TRUNCATE(操作数，保留小数的位数)
SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1),TRUNCATE(153.456,-2)
FROM DUAL;
#输出
+---------------------+---------------------+---------------------+----------------------+
| TRUNCATE(123.456,0) | TRUNCATE(123.496,1) | TRUNCATE(129.45,-1) | TRUNCATE(153.456,-2) |
+---------------------+---------------------+---------------------+----------------------+
|                 123 |               123.4 |                 120 |                  100 |
+---------------------+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;
#输出
+------------------------------+
| TRUNCATE(ROUND(123.456,2),0) |
+------------------------------+
|                          123 |
+------------------------------+
1 row in set (0.00 sec)

2.2 角度与弧度

函数用法
RADIANS(x)将角度转化为弧度，其中，参数x为角度值
DEGREES(x)将弧度转化为角度，其中，参数x为弧度值

举例：

#角度和弧度的互换
#1弧度=57度多，2pi=360度
SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90)
FROM DUAL;
#输出
+--------------------+--------------------+--------------------+--------------------+
| RADIANS(30)        | RADIANS(45)        | RADIANS(60)        | RADIANS(90)        |
+--------------------+--------------------+--------------------+--------------------+
| 0.5235987755982988 | 0.7853981633974483 | 1.0471975511965976 | 1.5707963267948966 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)SELECT DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;
#输出
+-----------------+----------------------+
| DEGREES(2*PI()) | DEGREES(RADIANS(60)) |
+-----------------+----------------------+
|             360 |    59.99999999999999 |
+-----------------+----------------------+
1 row in set (0.00 sec)

2.3 三角函数

函数用法
SIN(x)返回x的正弦值，其中，参数x为弧度值
ASIN(x)返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
cos(x)返回x的余弦值，其中，参数x为弧度值
ACOS(x)返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)返回x的正切值，其中，参数x为弧度值
ATAN(x)返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)返回两个参数的反正切值
COT(x)返回x的余切值，其中，x为弧度值

举例：

#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;
#输出
+---------------------+------------------+--------------------+------------------+
| SIN(RADIANS(30))    | DEGREES(ASIN(1)) | TAN(RADIANS(45))   | DEGREES(ATAN(1)) |
+---------------------+------------------+--------------------+------------------+
| 0.49999999999999994 |               90 | 0.9999999999999999 |               45 |
+---------------------+------------------+--------------------+------------------+
1 row in set (0.00 sec)

http://www.ppmy.cn/news/59952.html

相关文章

oracle 数据库创建表空间、用户、数据库实例、授权、数据导入

oracle 数据库创建表空间、用户、数据库实例、授权、数据导入

在安装完oracle 数据库后默认会创建一个orcl的数据库实例。除了这种方式外还可以通过别的方式创建数据库 一般导入数据通过以下五个步骤 创建导入的数据库实例，已有的话可以忽略创建表空间 2.1 创建临时表空间 2.2 创建数据表空间创建用户给用户授权导入数据库数据…
阅读更多...
idea修改 项目代码，浏览器页面不生效 解决方案

idea修改 项目代码，浏览器页面不生效 解决方案

使用快捷键ctrl shift delete，清理浏览器缓存 1、问题描述 idea修改前端项目代码，运行谷歌浏览器不起作用。 我也试过 rebuild project, 重启idea，等方法都不管用。 再次运行谷歌浏览器，还是没有变化。 2、尝试了以下方法&am…
阅读更多...
java版工程项目管理系统源码+spring cloud 系统管理+java 系统设置+二次开发

java版工程项目管理系统源码+spring cloud 系统管理+java 系统设置+二次开发

工程项目各模块及其功能点清单 一、系统管理 1、数据字典：实现对数据字典标签的增删改查操作 2、编码管理：实现对系统编码的增删改查操作 3、用户管理：管理和查看用户角色 4、菜单管理：实现对系统菜单的增删改查操…
阅读更多...
当无触控板和鼠标的情况下，如何开启触控板

当无触控板和鼠标的情况下，如何开启触控板

背景：一次出行匆忙，忘记带鼠标，周围也无可用工具，主要是触控板当时也被我关闭了，下面讲述一下我是如何解决在没有鼠标的情况下开启触控板的。 首先我们开启电脑后， 存在两种思路去开启触控板 第一种方案…
阅读更多...
系统分析师之需求工程（十四）

系统分析师之需求工程（十四）

目录 一、概述 二、需求获取 三、需求分析 3.1 需求分类 3.2 结构化需求分析（SA） 3.3 面向对象的需求分析OOA 四、需求定义 五、需求验证 六、需求管理 6.1 软件需求基线 6.2 需求跟踪 6.3 需求风险管理 一、概述 软件需求是指用户对系统在功…
阅读更多...
shell的基础学习一

shell的基础学习一

文章目录 一、shell的简介二、 Shell 变量三、Shell 传递参数总结 一、shell的简介 Shell 是一个用 C 语言编写的程序，它是用户使用 Linux 的桥梁。Shell 既是一种命令语言，又是一种程序设计语言。 Shell 是指一种应用程序，这个应用程序提供…
阅读更多...
虚拟机和Docker有什么区别？

虚拟机和Docker有什么区别？

虚拟机 对于虚拟机，抽象层或抽象软件成为管理程序。管理程序就是帮助虚拟机模拟物理计算机的东西。在管理程序下面，我们有些硬件。管理程序管理单个物理主机上不同虚拟机之间的资源分配。管理程序管理单个物理主机上不同虚拟机之间的资源分配。也就是管…
阅读更多...
提取Windows中系统自带的图标资源

提取Windows中系统自带的图标资源

写应用程序，如果想使用Windows下的图标，可以使用Visual Studio中的图标，比如VS2008的ImageLibrary（笔者已经打包上传到CSDN），也可以使用Windows系统自带的图标。 Windows系统自带了不少高质量的图标资源&a…
阅读更多...
最新文章

Copyright @ 2022~2023