MySQL之函數

1、簡介

MySQL内置了很多函數用于各種場景資料處理。函數往往能夠處理文本資料、日期資料、數值資料以及傳回系統相關資訊；除普通函數之外MySQL還内置了一些聚集函數，用于對表中資料進行彙總、求和等操作。

聚集函數：

指的是運作在行組上，計算和傳回單個值的函數。

使用MySQL的函數有很多好處：

1. SQL編寫簡易、美觀
2. 計算速度快于客戶機
3. 減少資料傳回，節省網絡帶寬資源

不過MySQL函數不一定在其他資料庫系統支援（文法不同），是以SQL的移植性比較差。

2、正文

2.1 文本處理函數

文本處理函數能夠對文本進行大小寫轉換、組合、去除空格等操作。

文本函數表：

函數	函數說明
left()	傳回左邊的字元
right()	傳回右邊的字元
substring()	字元串截取
trim()	去除兩邊空格
rtrim()	去除右邊空格
ltrim()	去除左邊空格
length()	傳回字元串長度
lower()	将字元串轉換成小寫
upper()	将字元串轉換成大寫
locate()	找出字元串的子串
soundex()	傳回字元串的soundex值

舉例：

轉小寫lower()

mysql> select lower('HUAWEI');

+-----------------+

| lower('HUAWEI') |

| huawei          |

轉大寫upper()

mysql> select upper('alibaba');

+------------------+

| upper('alibaba') |

| ALIBABA          |

傳回字元串長度length()

mysql> select length('liziba');

| length('liziba') |

|                6 |

字元串截取substring()

mysql> select substring('I am Liziba', 6, 10);

+---------------------------------+

| substring('I am Liziba', 6, 10) |

| Liziba                          |

soundex()可以找到發音相似字元串（這個挺有意思的！）

mysql> select soundex('liziba') = soundex('leezibe');

+----------------------------------------+

| soundex('liziba') = soundex('leezibe') |

|                                      1 |

mysql> select soundex('liziba') = soundex('lizijiu');

| soundex('liziba') = soundex('lizijiu') |

|                                      0 |

2.2 日期處理函數

日期函數在MySQL中非常重要，因為我們經常需要對日期進行格式化、提取日期的年月日、計算日期的産值等等。

日期函數表：

CurDate()	傳回目前日期
CurTime()	傳回目前時間
AddDate()	增加一個日期的天、周等
AddTime()	增加一個時間的時、分等
Date()	傳回日期的時間的部分
DateDiff()	計算兩個日期之差
Date_Add()	日期運算函數
Date_Format()	日期格式化函數
Day()	傳回日期天數部分
DayOfWeek()	傳回日期屬于星期幾
Year()	傳回一個日期的年份部分
Month()	傳回一個日期的月份部分
Time()	傳回一個日期的時間部分
Hour()	傳回時間的小時部分
Minute()	傳回時間的分鐘部分
Second()	傳回時間的秒鐘部分
Now()	傳回目前日期和時間

查詢目前日期和時間

mysql> select now() as now;

+---------------------+

| now                 |

| 2021-11-12 23:39:49 |

查詢目前年份

mysql> select year(now()) as year;

+------+

| year |

| 2021 |

查詢目前星期幾（注意MySQL中計算的星期幾，傳回的是工作日索引，星期日等于1，星期六等于7）

mysql> select dayOfWeek(now()) as week;

| week |

|    6 |

日期格式化

mysql> select date_format(now(), '%Y-%m-%d') as date;

+------------+

| date       |

| 2021-11-12 |

計算日期之差

mysql> select DateDiff('2021-11-12 11:00:00', '2000-12-12 11:00:00') as dateDiff;

+----------+

| dateDiff |

|     7640 |

2.3 數值處理函數

數值處理函數用于數值的處理；一般用于代數運算、三角運算、幾何運算等。

數值函數表：

abs()	絕對值
cos()	餘弦值
sin()	正弦值
tan()	正切
exp()	指數值
sqrt()	平方根
rand()	随機數
pi()	圓周率
mod()	除數的餘數

計算絕對值

mysql> select abs(-1998) as abs;

| abs  |

| 1998 |

傳回π的值

mysql> select pi() as pi;

| pi       |

| 3.141593 |

傳回一個小于1大于0随機數

mysql> select rand();

+--------------------+

| rand()             |

| 0.7134191456375822 |

計算除數的餘數

mysql> select mod(8, 2) as remain;

+--------+

| remain |

|      0 |

mysql> select mod(8, 3) as remain;

|      2 |

2.4 聚集函數

有些時候我們不需要傳回表資料中所有的列，而隻需要對表中的資料進行彙總，或者對表行組資料執行相關計算；此時可以使用MySQL的聚集函數。

聚集函數表：

avg()	求平均值
count()	傳回列的行數
max()	計算列的最大值
min()	計算列的最小值
sum()	計算列的值之和

準備一張産品表，如下所示：

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for product

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product`  (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '産品名稱',

  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '産品價格',

  `number` int(11) NOT NULL COMMENT '産品數量',

  PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- Records of product

INSERT INTO `product` VALUES (1, 'Apple iPhone 13 (A2634)', 6799.00, 22);

INSERT INTO `product` VALUES (2, 'HUAWEI P50 Pro', 6488.00, 88);

INSERT INTO `product` VALUES (3, 'MIX4', 4999.00, 30);

INSERT INTO `product` VALUES (4, 'OPPO Find X3', 3999.00, 15);

INSERT INTO `product` VALUES (5, 'vivo X70 Pro+', 5999.00, 27);

SET FOREIGN_KEY_CHECKS = 1;

求單價平均數

mysql> select avg(price) as avg_price from product ;

+-------------+

| avg_price   |

| 5656.800000 |

1 row in set (0.00 sec)

求産品表行數（三種辦法，大家可以自取，具體性能方面這是個需要深究的問題，這裡不讨論）

mysql> select count(*) from product;

| count(*) |

|        5 |

mysql> select count(price) from product;

+--------------+

| count(price) |

|            5 |

mysql> select count(1) from product;

| count(1) |

求價格最大值

mysql> select max(price) max_price from product;

+-----------+

| max_price |

|   6799.00 |

單價求和

mysql> select sum(price) sum_price from product;

| sum_price |

|  28284.00 |

MySQL中的聚集函數可以組合使用，比如上面求平均值、求行數、求最大值、求和可以一起查詢。

mysql> select avg(price) as avg_price, count(*) as row_size, max(price) as max_price, sum(price) as sum_price from product;

+-------------+----------+-----------+-----------+

| avg_price   | row_size | max_price | sum_price |

| 5656.800000 |        5 |   6799.00 |  28284.00 |

注意：上面有說到count()函數可以統計表的行數，但是如果我們希望統計的是針對某一列去重的行數，此時可以使用distinct關鍵字，但是它隻能作用于指定字段，不能作用于*。

正确用法：

mysql> select count(distinct price) from product;

+-----------------------+

| count(distinct price) |

|                     5 |

錯誤用法：

mysql> select count(distinct *) from product;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from product' at line 1