MySQL学习笔记05-统计函数_字符串_日期_数学函数

267次阅读
一条评论

内容有点多,专心的写,厚积薄发,你才是王者,嘿嘿

以下介绍的所有函数,均既可以在查询中使用,又可以在插入数据中使用。

dual表是一个 MySQL 内置的一个不存在的虚表,方便做测试用的。

写作环境:

MacOS、MySQL8.0

写SQL工具:Navicat15.0和电脑自带终端

写作软件:Typora

作者:西园公子:www.zwjjiaozhu.top

1、统计函数

用来测试代码例子的表t9,表内容如下,本章所有操作均是使用该表。

mysql> SELECT * FROM t9;
+-----------------+------+---------+---------+
| name            | math | chinese | english |
+-----------------+------+---------+---------+
| 关羽            | NULL |      58 |       0 |
| 宋江            |   98 |      96 |      82 |
| 我是及时雨      |   78 |      86 |      92 |
| 宋无缺          |   98 |      96 |      92 |
+-----------------+------+---------+---------+

1.1 Count (统计数量)

1)COUNT(*):统计满足条件的行数量*表示匹配所有。

小例子1:统计数学在 90 到 100 之间的行数量?

mysql> SELECT COUNT(*) AS "数学高于70分" FROM t9 WHERE math BETWEEN 90 AND 100;
+-------------------+
| 数学高于70分        |
+-------------------+
|                 2 |
+-------------------+

2)count(column_name):统计满足列column_name不为NULL的数量。

小例子2:统计数学不为的行数量?

mysql> SELECT COUNT(math) AS "数学有分数的" FROM t9;
+--------------------+
| 数学有分数的         |
+--------------------+
|                  3 |
+--------------------+

mysql> SELECT COUNT(*) AS "数学有分数的" FROM t9;
+--------------------+
| 数学有分数的         |
+--------------------+
|                  4 |
+--------------------+

1.2 Sum(求和)

SUM(column_name):求出所有满足条件的列(column_name)对应的数值相加的总和

小例子3:求出满足数学在 90 到 100 之间所有行的英语之和?

mysql> SELECT SUM(english) as '90以上的英语总和' FROM t9 WHERE math BETWEEN 90 AND 100;
+-------------------------+
| 90以上的英语总和          |
+-------------------------+
|                     174 |
+-------------------------+

1.3 Avg(求平均值)

AVG(column_name):求出所有满足条件的列(column_name)对应的数值相加总和的平均值

小例子4:求出满足数学在 90 到 100 之间所有行的英语之和的平均值?

mysql> SELECT AVG(english) as '英语平均值' FROM t9 WHERE math BETWEEN 90 AND 100;
+-----------------+
| 英语平均值        |
+-----------------+
|         87.0000 |
+-----------------+

1.4 Max/Min(求最大和最小)

MAX([distinct] column_name):求出所有满足条件的列(column_name)对应的数值相的最大值

MIN(column_name):求出所有满足条件的列(column_name)对应的数值相的最小值

mysql> SELECT MAX(chinese),MIN(chinese) FROM t9;
+--------------+--------------+
| MAX(chinese) | MIN(chinese) |
+--------------+--------------+
|           96 |           58 |
+--------------+--------------+

2、分组

超级重点,必须要掌握!

为什么重要呢?那就是大多数的操作均是在建立在

mysql> SELECT * FROM t9;
+-----------------+------+---------+---------+
| name            | math | chinese | english |
+-----------------+------+---------+---------+
| 关羽            | NULL |      58 |       0 |
| 宋江            |   98 |      96 |      82 |
| 我是及时雨      |   78 |      86 |      92 |
| 宋无缺          |   98 |      96 |      92 |
+-----------------+------+---------+---------+

1)GROUP BY column_name:按照 column_name 为一组进行分组

小例子5:按照英语分数进行分组,求出每组的英语总和、英语数量、英语的平均值?

mysql> SELECT SUM(english), COUNT(english), AVG(english) FROM t9 GROUP BY english;
+--------------+----------------+--------------+
| SUM(english) | COUNT(english) | AVG(english) |
+--------------+----------------+--------------+
|            0 |              1 |       0.0000 |
|           82 |              1 |      82.0000 |
|          184 |              2 |      92.0000 |
+--------------+----------------+--------------+

2)HAVING :指定分组的过滤条件,通常与GROUP BY配合使用。

小例子6:按照英语分数进行分组,且英语总和大于100分,求出每组的英语总和、数学总和?

mysql> SELECT SUM(english) AS sum_e, SUM(math) AS sum_m FROM t9 GROUP BY english HAVING sum_e>100;
+-------+-------+
| sum_e | sum_m |
+-------+-------+
|   184 |   176 |
+-------+-------+

3、字符串函数

3.1 Charset(字符集)

charset(column_name):返回列字符段对应的字符集

mysql> SELECT CHARSET(`name`) FROM t9;
+-----------------+
| CHARSET(`name`) |
+-----------------+
| utf8mb3         |
| utf8mb3         |
| utf8mb3         |
| utf8mb3         |
+-----------------+

3.2 Concat(拼接字符串)

concat(column_name, str, ……):拼接字符串,可以拼接列字段。

小例子7:对表中的每个字段都拼接出形如 “数学:98,英语:82” 并输出?

mysql> SELECT CONCAT('数学:', math, ',英语:',  english) FROM t9;
+-----------------------------------------------------+
| CONCAT('数学:', math, ',英语:',  english)          |
+-----------------------------------------------------+
| NULL                                                |
| 数学:98,英语:82                                    |
| 数学:78,英语:92                                    |
| 数学:98,英语:92                                    |
+-----------------------------------------------------+

mysql> SELECT CONCAT('love', 'MySQL') FROM DUAL;  # loveMySQL

1.3 Length(计算字节数)

length(str):返回字符串 str 的字节数

# length
SELECT LENGTH('章') FROM DUAL;  # 3
SELECT LENGTH('zz') FROM DUAL;  # 2
SELECT LENGTH(1) FORM DUAL;  # ❌ 只可以是字符串

注:汉字一般是 3 个字节,一个字母是 1 个字节。

3.4 Replace(替换字符串函数)

replace(str, old, new):将字符串 str 中的 old 字符串替换成 new 字符串。

mysql> SELECT REPLACE('喜欢编程吗', '编程', 'MySQL') AS `replace` FROM DUAL;
+----------------+
| replace        |
+----------------+
| 喜欢MySQL吗     |    
+----------------+

3.5 Substring(切片取子串)

substring(str, position, len):返回字符串 str 的从位置$[positon, position+len]$的子串。

SELECT SUBSTRING('Love MySQL', 1, 4) FROM DUAL; # LoveSELECT SUBSTRING('Love MySQL', 6) FROM DUAL;  # MySQL

注:如果不设置 len 的值则默认是到最后。

3.6 Left|right(从首尾取子串)

left(str, len):从最左边开始取 len 长度的字符串并返回。right(str, len):从最右边开始取 len 长度的字符串并返回。

SELECT LEFT('Love MySQL', 4) FROM DUAL;   # Love
SELECT RIGHT('Love MySQL', 5) FROM DUAL;  # MySQL

3.7 Lcase|ucase(字符串大小写)

lcase(str):转成小写,ucase(str):转成大写。

# lcase、ucase
mysql> SELECT LCASE('LOVE'), UCASE('love') FROM DUAL;
+---------------+---------------+
| LCASE('LOVE') | UCASE('love') |
+---------------+---------------+
| love          | LOVE          |
+---------------+---------------+

3.8 Ltrim|rtrim|trim(去除首尾空格)

ltrim(str):去除 str 最左边的空格,rtrim(str):去除 str 最右边的空格,trim(str):去除 str 首尾两端的空格。

SELECT LTRIM(' LOVE '), RTRIM(' LOVE '), TRIM(' LOVE ') FROM DUAL;
#  `LOVE `, ` LOVE`, `LOVE`
mysql> SELECT LENGTH(LTRIM(' LOVE ')), LENGTH(RTRIM(' LOVE ')), LENGTH(TRIM(' LOVE ')) FROM DUAL;
+-------------------------+-------------------------+------------------------+
| LENGTH(LTRIM(' LOVE ')) | LENGTH(RTRIM(' LOVE ')) | LENGTH(TRIM(' LOVE ')) |
+-------------------------+-------------------------+------------------------+
|                       5 |                       5 |                      4 |
+-------------------------+-------------------------+------------------------+

3.9 Strcmp(比较两字符串的大小)

strcmp(str1, str2)逐字符比较 str1 和 str2 的大小。

mysql> SELECT STRCMP('ab', 'bc'), STRCMP('bc', 'ab') FROM DUAL;
+--------------------+--------------------+
| STRCMP('ab', 'bc') | STRCMP('bc', 'ab') |
+--------------------+--------------------+
|                 -1 |                  1 |
+--------------------+--------------------+

注:后面 str2 的比前一个 str1 大则返回 1 , 否则返回 -1

3.10 instr(索引子串的位置)

instr(str, substr):返回子串 substr 在 str 中的首位置。

mysql> SELECT INSTR('loveabcd', 'ab') FROM DUAL;
+-------------------------+
| INSTR('loveabcd', 'ab') |
+-------------------------+
|                       5 |
+-------------------------+

注:索引是从 1 开始计数

4、数学函数

4.1 Abs(取绝对值)

abs(num):返回数字类型的 num 的绝对值。

SELECT ABS(-23.884) FROM DUAL;   # 23.884
SELECT ABS(1) FROM DUAL;   # 1

4.2 进制转换

bin(num):十进制转 2 进制,oct(num):十进制转 8 进制,hex(num):十进制转 16 进制。(binary、octonary、hexadecimal)

# 十进制转2进制、十进制转8进制、十进制转16进制
mysql> SELECT BIN(10), OCT(10), HEX(10) FROM DUAL;
+---------+---------+---------+
| BIN(10) | OCT(10) | HEX(10) |
+---------+---------+---------+
| 1010    | 12      | A       |
+---------+---------+---------+

conv(num, from_base, to_base):将数字 num 从 from_base 进制转成 to_base 进制。(convert)

mysql> SELECT CONV(12, 8, 10) AS "8_to_10", CONV('A', 16, 10) AS "16_to_10",  CONV(12, 8, 16) AS "8_to_16" FROM DUAL;
+---------+----------+---------+
| 8_to_10 | 16_to_10 | 8_to_16 |
+---------+----------+---------+
| 10      | 10       | A       |
+---------+----------+---------+

4.3 上下取整

ceiling(num)向上取整,取大于 num 的最小整,floor(num)向下取整,取小于 num 的最大整数。

mysql> SELECT CEILING(1.4), FLOOR(-1.3) FROM DUAL;
+--------------+-------------+
| CEILING(1.4) | FLOOR(-1.3) |
+--------------+-------------+
|            2 |          -2 |
+--------------+-------------+

4.4 Format(格式化数字)

fromat(num, num_places):将数字 num 转成保留 num_places 位小数的数(四舍五入)

mysql> SELECT FORMAT(1.3, 4), FORMAT(1.314559, 4) FROM DUAL;
+----------------+---------------------+
| FORMAT(1.3, 4) | FORMAT(1.314559, 4) |
+----------------+---------------------+
| 1.3000         | 1.3146              |
+----------------+---------------------+

4.5 Mod(取余数)

mod(num, num2):返回 num 除以 num2 的余数。

mysql> SELECT MOD(12, 5), MOD(13, 2) FROM DUAL;
+------------+------------+
| MOD(12, 5) | MOD(13, 2) |
+------------+------------+
|          2 |          1 |
+------------+------------+

4.7 Greatest|least(求最大和最小)

greatest(value1, value2, ...):返回里面的最大值,least(value1, value2, ...):返回里面的最小值。

其中参数 value 可以是混合参数:

  • 如果有一个参数是 NULL ,则立即返回 NULL,不做任何比较。
  • 如果参数是数字字符串混合的,则按照数字进行进行比较。
mysql> SELECT GREATEST(12, 3, 4), LEAST(6, 2.6, 4) FROM DUAL;
+--------------------+------------------+
| GREATEST(12, 3, 4) | LEAST(6, 2.6, 4) |
+--------------------+------------------+
|                 12 |              2.6 |
+--------------------+------------------+

小例子8:使用greatest方法求出,每一个同学的英语、数学、68 中的最大值,并输出?

mysql> SELECT GREATEST(math, 68, chinese) '混合最大' FROM t9;
+--------------+
| 混合最大      |
+--------------+
|         NULL |
|           98 |
|           86 |
|           98 |
+--------------+

4.8 rand(随机数)

rand(send):若不设置 send 参数,会产生成一个 0 和 1之间的随机数,若设置 seed 则会生成一个定值

mysql> SELECT RAND() '变值', RAND(4) '定值' FROM DUAL;
+---------------------+---------------------+
| 变值                 | 定值                |
+---------------------+---------------------+
| 0.40154680088306477 | 0.15595286540310166 |
+---------------------+---------------------+

mysql> SELECT RAND() '变值', RAND(4) '定值' FROM DUAL;
+--------------------+---------------------+
| 变值                | 定值                |
+--------------------+---------------------+
| 0.8185506526553544 | 0.15595286540310166 |
+--------------------+---------------------+

总结:

内容有点多,需多多练习,熟能生巧~

5
西园公子
版权声明:本站原创文章,由西园公子2021-08-17发表,共计8308字。
转载提示:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(一条评论)
载入中...
西园公子 博主
2021-08-17 19:40:07 回复

:razz: