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之后。

高级联结

在讲解高级来联结之前，我们先来讲一下表的别名。我们之前讲到，可以利用AS来对列起别名，那么同样可以利用AS对表起别名。通过给表起别名，不仅可以缩短MySQL语句，同时含可以允许在单条SELECT语句中多次使用相同的表。

值得注意的是，表别名只在查询中使用。与列别名不同，表别名不会返回到客户机。

• 自联结：就是自己和自己进行连接。例如下面的例子，统计和name = "小胡"同一个部门的员工:

使用自联结中需要格外注意的问题是:因为是自联结，所以输出的时候必须明确输出的是哪个表中的数据，否则就会发生报错。因为两个表中都存在相同名字的列，从而导致MySQL无法辨认要输出的是哪张表的数据。同时过滤的时候，需要格外注意，不可以是利用输出表来进行判断过滤，即类似上面的例子，不可以利用e1.name = "小胡”过滤.

• 自然联结:所谓自然联结，就是说在输出联结表中的所有数据的时候，可能会出现多个名字相同的列，所以为了避免出现这种情况，推出了自然联结。

  

• 外联结：使用外部联结，最后输出的时候包括了没有关联的行。此时利用的不再是INNER JOIN (这个是内部联结），而是OUTER JOIN，并且常常配合LEFT \ RIGHT使用。而LEFT \ RIGNT用来表示最后输出的是OUTER JOIN哪边的表的所有行。例如是LEFT OUTER JOIN，最后输出的是包括左边表的所有行。否则，如果是RIGHT OUTER JOIN，那么输出的是包括右边表的所有行.

上面输出的列是普通的列，那么我们可以输出聚集函数的列吗？答案当然可以了，上面也已经提到了，因为ON得作用相当于WHERE，所以需要将GROUP BY\ HAVING\ORDER BY\LIMIT放在ON得后面。

然而下面的例子中发生了报错，错误原因请大佬指教.

通过上面得学习，我们需要总结一下使用联结和联结条件：