开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MYSQL必知必会2 -> 正文阅读

[大数据]MYSQL必知必会2

使用数据处理函数

MySQL中常用的文本处理函数:
在这里插入图片描述

Left(“xxxx”,len) :表示从左边开始算起获取xxx字符串的len个字符.如果len是0或者负数，那么返回的就是一个空串，否则len大于xxx的长度，返回就是xxx字符串，否则就是len个字符.
Right(“xxx”,len):和Left(“xxx”,len)用法基本相同，只不过是从右边开始算起的。
Length(“xxx”)：获取xxx的长度
Lower(“xxx”)：获取xxx的小写
Upper(“xxx”)：获取xxx的大写
Trim(“xxx”)：去除xxx首尾的空格
RTrim(“xxx”): 去除xxx右边的空格
LTrim(“xxx”):去除xxx左边的空格
Locate(“x”,“yyyy”):获取x是yyy的第几个字符，如果在yyy中没有，则返回0，如果有，并且如果有多个的话，那么返回的是第一次出现x的位置.
Substring(“xxx”,positon):获取xxx的一个子串。
如果position等于0或者绝对值大于xxx的长度，那么得到的是空串；
否则，如果position是大于0，并且小于xxx长度的正数，那么表示从左边开始算起的第position个字符，到xxx最后一个字符结束的子串。
如果position是一个负数，并且绝对值小于xxx的长度，得到的是从右边开始往左边算第positon个字符开始，一直到xxx最后一个字符结束的子串。
Soundex(“xxx”):通过发音进行比较的。从而获取和xxx发音相似的数据。

在这里插入图片描述
在MySQL中，日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。而在MySQL中，因为日期和时间函数总是被用来读取、统计和处理一些数值，所以，日期和时间函数在MySQL中具有重要的作用。
常见的日期和时间处理函数:
在这里插入图片描述

但是假设我们需要检索某一个月下的数据，应该怎么办呢？

有人会说，我是用BETWEEN … AND …不就好啦。例如2001-08月下的数据，我们只要BETWEEN “2001-08-01” AND "2001-08-31"既可以检索到在2001-08的所有数据了。然而，这样写存在一些问题：我们需要明确知道当前这个月份有多少天才可以确保BETWEEN… AND… 的范围是正确的，并且如果是2月，那么我们还需判断年份是否为闰年。所以使用BETWEEN…AND…很麻烦。所以MySQL推荐我们使用Year(date)来获取date的年份，使用Month(date)来获取date的月份，通过这样的操作，我们就可以检索出给出日期这一个月份的数据了。
在这里插入图片描述

在MySQL中常见的数值处理函数有:
在这里插入图片描述

汇总数据

在MySQL中常见的聚集函数有:
在这里插入图片描述
值得注意的是，上面4个聚集函数（除了Count函数比较特殊）对于NULL是忽略的，而Count()之所以说是特殊，是因为如果Count(*)这样写的话，那么不会忽略NULL值，而是将NULL作为新的一行输出，并且会统计NULL的值，而如果是Count(column)统计column这一列的时候，就会忽略这一列的NULL值。

在这里插入图片描述

MAX()返回指定列中的最大值，所以必须要指定列名。MIN()函数返回指定列的最小值，所以同样需要指定列名。用法和MAX()函数相似，不仅可以进行数字或者日期的比较，同样可以对非数值数据进行比较。
在这里插入图片描述

然而将聚集函数使用在WHERE中会怎样呢？
例如下面的例子:

这时候出现了InValid use of group function,提示我们聚集函数的不合法利用。但是为什么呢？
这是因为聚集函数是不可以使用在WHERE子句后面的,具体原因如果有大佬知道的话，请指教哈！

分组数据

分组是在SELECT语句中的GROUP BY子句中建立的。
在这里插入图片描述
在讲解GROUP BY的用法的时候，我们先来看一下它的使用准则：

GROUB BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中含有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，他们将分为一组。但是是否对NUL进行统计，这时候需要取决于聚集函数Count()，例如提到了Count()聚集函数相对特殊，如果是Count(*)，那么这时候并不会忽略NULL，否则，如果是Count(column)来计算column这一列，那么可以忽略NULL值。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。尽管上面的例子中利用GROUP BY输出的vent_id是有序的，但是也有可能输出的不是有序的，所以需要利用ORDER BY来实现分组的有序。

除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如可能想要列出至少有两个订单的所有顾客。为了得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
因为WHERE过滤的是指定的行而不是分组，所以此时需要利用到了HAVING.两者的唯一区别在于WHERE过滤的是行，而HAVING过滤的是分组.所以此时，在利用GROUP BY进行分组之后，利用HAVING过滤掉一些不符合条件的分组，此时作用和WHERE一样，同样是实现过滤的效果，只是针对的对象不同而已。

那么是不是说，有了HAVING，就不需要使用WHERE了吗？答案是否定的，因为在一些情况下，需要针对行来进行过滤，此时就会用到了WHERE.
在这里插入图片描述

所以学到现在，我们将可以知道SELECT语句格式将进一步完善为:

SELECT
XXX1,XXX2,XXX3,…XXXN
FROM table_name
WHERE XXXXX – 利用WHERE来对行进行过滤
GROUP BY yyy – 利用yyy进行分组
HAVING ZZZ – 利用HAVING来对分组进行过滤
ORDER BY TTT – 利用TTT进行升序排序，如果需要实现降序排序，那么需要在TTT地后面添加DESC关键词
LIMIT m OFFSET n; – 从行n开始检索（即第n + 1行）,返回不多于m行，之所以说不多于，是因为可能最后数据少于m行，所以只能将所有的数据返回

使用子查询

SQL允许创建子查询，即嵌套在其他查询中的查询。而子查询主要用在下面几种地方：

使用子查询进行过滤
例如下面的例子，需要查询每个部门的员工信息的时候，我们使用子查询：
作为计算字段使用子查询

联结表

在了解联结之前，有必要了解一下关系表。
在这里插入图片描述
分解数据为多个表能更有效的存储和处理，并且有更大的可伸缩性。但是这些好处是有代价的。如果将数据从存放再多个表中怎样用单条SELECT语句检索出数据？这时候就要依赖联结了。
而创建联结的方式有两种：

SELECT XXX1,XXX2,…XXXN FROM talbe_name1,table_name2 WHERE table_name1的某一列 = table_name2的主键（table_name2的主键也是table_name1的外键）。
SELECT XXX1,XXX2,…XXXN FROM
table_name1 INNER JOIN table_name2
ON table_name1的某一列 = table_name2的主键（table_name2的主键也是table_name1的外键）.此时的ON相当于WHERE，所以对应的条件就是上面WHERE的条件

在这里插入图片描述

而对于第一条语法中，WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件的行），如果没有使用WHERE，那么就会将第一个表中的每一行和第二个表中的每一行进行配对，所以会输出table_row1 * table_row2行数据。这就是笛卡尔积。
在这里插入图片描述

值得注意的是，并不是说使用了ON就不可以在使用WHERE了，只是两者的顺序有要求，如果需要在ON中使用WHERE进行过滤的话，那么需要将WHERE放在ON之后，否则就会发生报错。
在这里插入图片描述
所以在明白WHERE需要放在ON子句之后，那么我们也将知道了GROUP BY\HAVING\ORDER BY\LIMIT这些子句同样放在ON之后。