MySQL入門系列：查詢簡介（三）之表示式和函式

小孩子4919發表於2019-03-11

MySql函式

上篇回顧

之前兩篇介紹了基本的查詢語句以及基本的帶有搜尋條件的查詢語句，本篇繼續深入介紹各種眼花繚亂的查詢規則。

運算元

MySQL中運算元可以是下邊這幾種型別：

常數

常數很好理解，我們平時用到的數字、字串、時間值什麼的都可以被稱為常數，它是一個確定的值，比如數字1，字串'abc'，時間值2018-03-05 16:12:46啥的。
列名

針對某個具體的表，它的列名可以被當作表示式的一部分，比如對於student_info表來說，number、name都可以作為運算元。
函式呼叫

MySQL中有函式的概念，比方說我們前邊提到的獲取當前時間的NOW就算是一個函式，而在函式後邊加個小括號就算是一個函式呼叫，比如NOW()。
```
如果你不清楚函式的概念，我們之後會詳細嘮叨的，現在不知道也可以～
複製程式碼
```
子查詢

這個子查詢我們稍後會詳細嘮叨的～

操作符

至於操作符我們也都瞭解了一些，我們需要掌握的大致是下邊這3種：

算術操作符

就是加減乘除法那一堆，我們看一下MySQL中都支援哪些：

操作符示例描述

+ a + b 加法

- a - b 減法

* a * b 乘法

/ a / b 除法

DIV a DIV b 除法，取商的整數部分

% a % b 取餘

- -a 負號

在使用MySQL中的算術操作符需要注意，DIV和/都表示除法操作符，但是DIV只會取商的整數部分，/會保留商的小數部分。比如表示式 2 DIV 3的結果是0，而2 / 3的結果是0.6667。

操作符	示例	描述
`+`	`a + b`	加法
`-`	`a - b`	減法
`*`	`a * b`	乘法
`/`	`a / b`	除法
`DIV`	`a DIV b`	除法，取商的整數部分
`%`	`a % b`	取餘
`-`	`-a`	負號

比較操作符

就是在搜尋條件中我們已經看過了比較操作符，我們把常用的都抄下來看一下：

操作符	示例	描述
`=`	`a = b`	等於
`<>`或者`!=`	`a <> b`	不等於
`<`	`a < b`	小於
`<=`	`a <= b`	不大於
`>`	`a > b`	大於
`>=`	`a >= b`	不小於
`BETWEEN`	`a BETWEEN b AND c`	滿足 b <= a <= c
`NOT BETWEEN`	`a NOT BETWEEN b AND c`	不滿足 b <= a <= c
`IN`	`a IN (b1, b2, ...)`	a是b1, b2, ... 中的某一個
`NOT IN`	`a NOT IN (b1, b2, ...)`	a不是b1, b2, ... 中的任意一個
`IS NULL`	`a IS NULL`	a的值是`NULL`
`IS NOT NULL`	`a IS NOT NULL`	a的值不是`NULL`
`LIKE`	`a LIKE b`	a匹配b
`NOT LIKE`	`a NOT LIKE b`	a不匹配b

由比較操作符連線而成的表示式也稱為布林表示式，表示真或者假，在MySQL中也稱為TRUE或者FALSE。比如1 > 3就代表FALSE，3 != 2就代表TRUE。

邏輯操作符

邏輯操作符是用來將多個布林表示式連線起來，我們需要了解這幾個邏輯操作符：

操作符	示例	描述
`AND`	`a AND b`	只有a和b同時為真，表示式才為真
`OR`	`a OR b`	只要a或b有任意一個為真，表示式就為真
`XOR`	`a XOR b`	a和b有且只有一個為真，表示式為真

表示式的使用

只要把這些運算元和操作符相互組合起來就可以組成一個表示式。表示式主要以下邊這兩種方式使用：

作為查詢物件

我們前邊都是以列名作為查詢物件的(*號代表所有的列名～)。列名只是表示式中超級簡單的一種，我們可以將任意一個表示式作為查詢物件來處理，比方說我們可以在查詢student_score表時把score欄位的資料都加100，就像這樣：

mysql> SELECT  number, subject, score + 100 FROM student_score;
+----------+-----------------------------+-------------+
| number   | subject                     | score + 100 |
+----------+-----------------------------+-------------+
| 20180101 | 母豬的產後護理              |         178 |
| 20180101 | 論薩達姆的戰爭準備          |         188 |
| 20180102 | 母豬的產後護理              |         200 |
| 20180102 | 論薩達姆的戰爭準備          |         198 |
| 20180103 | 母豬的產後護理              |         159 |
| 20180103 | 論薩達姆的戰爭準備          |         161 |
| 20180104 | 母豬的產後護理              |         155 |
| 20180104 | 論薩達姆的戰爭準備          |         146 |
+----------+-----------------------------+-------------+
8 rows in set (0.00 sec)

mysql>
複製程式碼

其中的number、subject、score + 100都是表示式，查詢結果的列的名稱也將預設使用這些表示式的名稱，所以如果你覺得原名稱不好，我們可以使用別名：

mysql> SELECT  number, subject, score + 100 AS score FROM student_score;
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母豬的產後護理              |   178 |
| 20180101 | 論薩達姆的戰爭準備          |   188 |
| 20180102 | 母豬的產後護理              |   200 |
| 20180102 | 論薩達姆的戰爭準備          |   198 |
| 20180103 | 母豬的產後護理              |   159 |
| 20180103 | 論薩達姆的戰爭準備          |   161 |
| 20180104 | 母豬的產後護理              |   155 |
| 20180104 | 論薩達姆的戰爭準備          |   146 |
+----------+-----------------------------+-------+
8 rows in set (0.00 sec)

mysql>
複製程式碼

這樣score + 100列就可以按照別名score來展示了！

需要注意的是，如果查詢物件中不涉及表的資料的話，查詢語句不指定從哪個表中查詢，比如我們可以直接這麼寫：

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>
複製程式碼

雖然可以這麼寫，但是貌似沒啥實際意義吧～

作為搜尋條件

我們在介紹搜尋條件的時候介紹的都是帶有列名的表示式，搜尋條件也可以不帶列名，比如這樣：

mysql> SELECT number, name, id_number, major FROM student_info WHERE 2 > 1;
+----------+-----------+--------------------+--------------------------+
| number   | name      | id_number          | major                    |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子騰    | 158177199901044792 | 電腦科學與工程         |
| 20180102 | 杜琦燕    | 151008199801178529 | 電腦科學與工程         |
| 20180103 | 範統      | 17156319980116959X | 軟體工程                 |
| 20180104 | 史珍香    | 141992199701078600 | 軟體工程                 |
| 20180105 | 範劍      | 181048199308156368 | 飛行器設計               |
| 20180106 | 朱逸群    | 197995199501078445 | 電子資訊                 |
+----------+-----------+--------------------+--------------------------+
6 rows in set (0.00 sec)

mysql>
複製程式碼

由於我們的搜尋條件是2 > 1，這個條件對於表中的每一條記錄都成立，所以最後的查詢結果就是全部的記錄。不過這麼寫有點兒傻哈，沒有一毛錢卵用，沒一點實際意義～所以通常情況下搜尋條件中都會包含列名的。

函式

對於某些我們會經常遇到的問題，MySQL內建了許多函式來幫我們解決這些問題。比方說UPPER函式是用來把給定的文字中的小寫字母轉換成大寫字母，MONTH函式是用來把某個日期資料中的月份值提取出來等等。

如果我們想使用這些函式，可以在函式名後加一個小括號()就表示函式呼叫。比方說NOW()就代表呼叫NOW函式來獲取當前時間。下邊來介紹一些常用的MySQL內建函式：

文字處理函式

名稱	呼叫示例	示例結果	描述
`LEFT`	`LEFT('abc123', 3)`	`abc`	返回從左邊取指定長度的子串
`RIGHT`	`RIGHT('abc123', 3)`	`123`	返回從左邊取指定長度的子串
`LENGTH`	`LENGTH('abc')`	`3`	返回字串的長度
`LOWER`	`LOWER('ABC')`	`abc`	返回小寫格式的字串
`UPPER`	`UPPER('abc')`	`ABC`	返回大寫格式的字串
`LTRIM`	`LTRIM(' abc')`	`abc`	將指定字串左邊空格去除後返回
`RTRIM`	`RTRIM('abc ')`	`abc`	將指定字串右邊空格去除後返回
`SUBSTRING`	`SUBSTRING('abc123', 2, 3)`	`bc1`	返回指定字串從指定位置擷取指定長度的子串
`CONCAT`	`CONCAT('abc', '123', 'xyz')`	`abc123xyz`	將給定的各個字串引數拼接程一個新字串

我們呼叫一下SUBSTRING函式：

mysql> SELECT SUBSTRING('abc123', 2, 3);
+---------------------------+
| SUBSTRING('abc123', 2, 3) |
+---------------------------+
| bc1                       |
+---------------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

我們前邊在嘮叨表示式的說過，函式呼叫也算是一種表示式的運算元，它可以和其他運算元和操作符連線起來組成一個表示式來用到查詢物件和搜尋條件處。我們來舉個例子：

mysql> SELECT CONCAT('學號為', number, '的學生在《', subject, '》課程的成績是：', score) AS 成績描述 FROM student_score;
+---------------------------------------------------------------------------------------+
| 成績描述                                                                              |
+---------------------------------------------------------------------------------------+
| 學號為20180101的學生在《母豬的產後護理》課程的成績是：78                              |
| 學號為20180101的學生在《論薩達姆的戰爭準備》課程的成績是：88                          |
| 學號為20180102的學生在《母豬的產後護理》課程的成績是：100                             |
| 學號為20180102的學生在《論薩達姆的戰爭準備》課程的成績是：98                          |
| 學號為20180103的學生在《母豬的產後護理》課程的成績是：59                              |
| 學號為20180103的學生在《論薩達姆的戰爭準備》課程的成績是：61                          |
| 學號為20180104的學生在《母豬的產後護理》課程的成績是：55                              |
| 學號為20180104的學生在《論薩達姆的戰爭準備》課程的成績是：46                          |
+---------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql>
複製程式碼

日期和時間處理函式

下邊有些函式會用到當前日期，我編輯文章的日期是2018-02-28，在實際呼叫這些函式時以你的當前時間為準。

名稱	呼叫示例	示例結果	描述
`NOW`	`NOW()`	`2018-02-28 09:24:10`	返回當前日期和時間
`CURDATE`	`CURDATE()`	`2018-02-28`	返回當前日期
`CURTIME`	`CURTIME()`	`09:24:10`	返回當前時間
`DATE`	`DATE('2018-02-28 09:24:10')`	`2018-02-28`	將給定時間值的日期提取出來
`DATE_ADD`	`DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 DAY)`	`2018-03-02 09:24:10`	給日期新增指定的時間間隔
`DATE_SUB`	`DATE_SUB('2018-02-28 09:24:10', INTERVAL 2 DAY)`	`2018-02-26 09:24:10`	從日期減去指定的時間間隔
`DATEDIFF`	`DATEDIFF('2018-02-27', '2018-02-28');`	`-1`	返回兩個日期之間的天數
`DATE_FORMAT`	`DATE_FORMAT(NOW(),'%m-%d-%Y')`	`02-28-2018`	用不同的格式顯示日期/時間

在使用DATE_ADD和DATE_SUB這兩個函式時需要注意，增加或減去的時間間隔單位可以自己填寫，下邊是MySQL支援的一些時間單位：

時間單位	描述
`MICROSECOND`	毫秒
`SECOND`	秒
`MINUTE`	分鐘
`HOUR`	小時
`DAY`	天
`WEEK`	星期
`MONTH`	月
`QUARTER`	季度
`YEAR`	年

如果我們相讓2018-02-28 09:24:10這個時間值增加2分鐘，可以這麼寫：

mysql> SELECT DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 MINUTE);
+----------------------------------------------------+
| DATE_ADD('2018-02-28 09:24:10', INTERVAL 2 MINUTE) |
+----------------------------------------------------+
| 2018-02-28 09:26:10                                |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

在使用DATE_FORMAT函式時需要注意，日期和時間的顯式格式是我們自定義的，下邊時MySQL中常用的一些格式：

格式	描述
`%D`	帶有英文字首的月中的天
`%d`	月的天，數值(00-31)
`%f`	微秒
`%H`	小時 (00-23)
`%h`	小時 (01-12)
`%i`	分鐘，數值(00-59)
`%M`	月名
`%m`	月，數值(00-12)
`%p`	AM 或 PM
`%S`	秒(00-59)
`%s`	秒(00-59)
`%W`	星期名
`%w`	周的天（0=星期日, 6=星期六）
`%Y`	年，4 位
`%y`	年，2 位

比如我們再換一種格式輸出一下當前時間：

mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| Feb 28 2018 10:30 AM                   |
+----------------------------------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

數值處理函式

下邊列舉一些數學上常用到的函式，在我們的業務中有數學計算時會很有用的：

名稱	呼叫示例	示例結果	描述
`ABS`	`ABS(-1)`	`1`	取絕對值
`Pi`	`PI()`	`3.141593`	返回圓周率
`COS`	`COS(PI())`	`-1`	返回一個角度的餘弦
`EXP`	`EXP(1)`	`2.718281828459045`	返回e的指定次方
`MOD`	`MOD(5,2)`	`1`	返回除操作的餘數
`RAND`	`RAND()`	`0.7537623539136372`	返回一個隨機數
`SIN`	`SIN(PI()/2)`	`1`	返回一個角度的正弦
`SQRT`	`SQRT(9)`	`3`	返回一個數的平方根
`TAN`	`TAN(0)`	`0`	返回一個角度的正切

聚集函式

如果將上邊介紹的那些函式用作查詢物件，那麼會為表中的每一條記錄呼叫一次該函式。比方說這樣：

mysql> SELECT LEFT(name, 1) FROM student_info;
+---------------+
| LEFT(name, 1) |
+---------------+
| 杜            |
| 杜            |
| 範            |
| 史            |
| 範            |
| 朱            |
+---------------+
6 rows in set (0.00 sec)

mysql>
複製程式碼

student_info表中的每一條記錄name欄位都會呼叫一次LEFT函式，所以結果就是把所有人名字的首個字元給提取出來了。但是有些函式是用來彙總資料的，比方說統計一下表中的行數，某一列資料的最大值是什麼，我們把這種函式稱之為聚集函式，下邊介紹一些MySQL中常用的幾種聚集函式：

函式名	描述
`COUNT`	返回某列的行數
`MAX`	返回某列的最大值
`MIN`	返回某列的最小值
`SUM`	返回某列值之和
`AVG`	返回某列的平均值

COUNT函式

COUNT函式使用來統計行數的，它有下邊兩種使用方式：

COUNT(*)：對錶中行的數目進行計數，不管列中包含的是不是NULL值。
COUNT(列名)：對特定的列進行計數，會忽略掉NULL值的行。

兩者的區別是會不會忽略列中的NULL值！兩者的區別是會不會忽略列中的NULL值！兩者的區別是會不會忽略列中的NULL值！重要的事情說了3遍，希望你能記住。我們來數一下student_info表中有幾行記錄吧：

mysql> SELECT COUNT(*) FROM student_info;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql>
複製程式碼

MAX函式

MAX函式是用來檢視某列資料中的最大值，以student_score表中的score列為例來看一下：

mysql> SELECT MAX(score) FROM student_score;
+------------+
| MAX(score) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

最大值100就被查詢出來了～

MIN函式

MIN函式是用來檢視某列資料中的最小值，以student_score表中的score列為例來看一下：

mysql> SELECT MIN(score) FROM student_score;
+------------+
| MIN(score) |
+------------+
|         46 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

最小值46就被查詢出來了～

SUM函式

SUM函式是用來計算某列資料的和，還是以student_score表中的score列為例來看一下：

mysql> SELECT SUM(score) FROM student_score;
+------------+
| SUM(score) |
+------------+
|        585 |
+------------+
1 row in set (0.01 sec)

mysql>
複製程式碼

所有學生的成績總和585就被查詢出來了，比我們用自己算快多了哈～

AVG函式

AVG函式是用來計算某列資料的平均數，還是以student_score表中的score列為例來看一下：

mysql> SELECT AVG(score) FROM student_score;
+------------+
| AVG(score) |
+------------+
|    73.1250 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

可以看到平均分就是73.1250.

指定搜尋條件下聚集函式的使用

聚集函式並不是一定要計算全部的記錄，我們也可以指定搜尋條件來限定這些聚集函式作用的範圍。比方說我們只想統計'母豬的產後護理'這門課程的平均分可以這麼寫：

mysql> SELECT AVG(score) FROM student_score WHERE subject = '母豬的產後護理';
+------------+
| AVG(score) |
+------------+
|    73.0000 |
+------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

聚集函式中DISTINCT的使用

預設情況下，上邊介紹的聚集函式將計算指定列的所有非NULL資料，如果我們指定的列中有重複資料的話，可以選擇使用DISTINCT來過濾掉這些重複資料。比方說我們想檢視一下student_info表中儲存了多少個專業的學生資訊，就可以這麼寫：

mysql> SELECT COUNT(DISTINCT major) FROM student_info;
+-----------------------+
| COUNT(DISTINCT major) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.01 sec)

mysql>
複製程式碼

可以看到一共有4個專業。

組合聚集函式

這些聚集函式也可以集中在一個查詢中使用，比如這樣：

mysql> SELECT COUNT(*) AS 成績記錄總數, MAX(score) AS 最高成績, MIN(score) AS 最低成績, AVG(score) AS 平均成績 FROM student_score;
+--------------------+--------------+--------------+--------------+
| 成績記錄總數       | 最高成績     | 最低成績     | 平均成績     |
+--------------------+--------------+--------------+--------------+
|                  8 |          100 |           46 |      73.1250 |
+--------------------+--------------+--------------+--------------+
1 row in set (0.00 sec)

mysql>
複製程式碼

總結

表示式由運算元和操作符構成，單個的運算元也可以被當作是一個表示式，通常將表示式用在查詢列表或者搜尋條件處。

常用的操作符可以是下邊這幾種型別：
- 算數操作符
- 比較操作符
- 邏輯操作符
運算元可以是下邊這幾種型別：
- 常數
- 列名
- 函式呼叫
- 子查詢
MySQL內建了許多函式來幫我們解決一些我們經常遇到的問題，我們常用到的函式有下邊這些：
- 文字處理函式
- 日期和時間處理函式
- 數值處理函式
- 聚集函式
其中，聚集函式比較特殊，它是用來統計資料的。

小冊

本系列專欄都是MySQL入門知識，想看進階知識可以到小冊中檢視：《MySQL是怎樣執行的：從根兒上理解MySQL》的連結。小冊的內容主要是從小白的角度出發，用比較通俗的語言講解關於MySQL進階的一些核心概念，比如記錄、索引、頁面、表空間、查詢優化、事務和鎖等，總共的字數大約是三四十萬字，配有上百幅原創插圖。主要是想降低普通程式設計師學習MySQL進階的難度，讓學習曲線更平滑一點～

MySQL入門系列：查詢簡介（七）之組合查詢
2019-03-14
MySql
MySQL入門系列：查詢簡介（二）
2019-03-09
MySql
MySQL入門系列：查詢簡介（五）之子查詢
2019-03-13
MySql
MySQL入門系列：儲存程式（二）之儲存函式簡介
2019-03-20
MySql儲存函式
函式表示式和函式宣告簡單介紹
2017-03-20
函式
TypeScript基礎入門-函式-簡介
2018-08-15
TypeScript函式
瞎說系列之正規表示式入門
2019-02-03
MySQL之集合函式與分組查詢
2017-05-11
MySql函式
《MySQL 入門教程》第 14 篇 MySQL 常用函式之數學函式
2022-02-07
MySql函式
《MySQL 入門教程》第 13 篇 CASE 表示式與控制流函式
2022-02-06
MySql函式
DMSFrame 之查詢表示式用法（一）
2018-04-21
C# Lambda表示式和linq表示式之匿名物件查詢接收
2019-01-18
C#物件
Hive內部函式簡介及查詢語法
2018-07-02
Hive函式
MySQL入門系列：儲存程式（三）之儲存過程簡介
2019-03-21
MySql儲存過程
正規表示式使用replace()函式簡單介紹
2017-02-17
函式
C++中scanf和printf系列函式簡介
2018-06-12
C++函式
Java-MySql-函式、多表查詢
2020-12-09
JavaMySql函式
[Mysql 查詢語句]——集合函式
2017-03-18
MySql函式
PHP入門之函式
2020-07-29
PHP函式
MongoDB入門系列（三）：查詢（SELECT）
2018-02-09
MongoDB
Lambda表示式入門--函數語言程式設計與函式式介面
2020-12-31
函數程式設計函式
MySQL全面瓦解10：分組查詢和聚合函式
2020-11-16
MySql函式
re正規表示式庫的簡介、入門、使用方法
2019-08-31
go語言入門之-函式和方法
2019-04-20
Go函式
ORACLE函式介紹第三篇著名函式之聚合函式
2007-08-30
Oracle函式
JAVA基礎之四-函式式介面和流的簡介
2024-09-04
Java函式
Go語言入門系列(六)之再探函式
2020-08-11
Go函式
函式式 JavaScript 簡介
2016-03-10
函式JavaScript
函式式API簡介
2024-05-31
函式API
MongoDB之資料查詢（正規表示式）
2017-08-24
MongoDB
雞啄米：C++程式設計入門系列之五（運算子和表示式）
2020-11-09
C++程式設計
正規表示式系列之初級入門篇
2018-07-09
《MySQL 入門教程》第 16 篇 MySQL 常用函式之日期函式
2022-02-09
MySql函式
正規表示式簡介
2009-05-17
JavaScript函式宣告和函式表示式區別
2018-08-22
JavaScript函式
函式宣告和函式表示式的區別
2016-08-17
函式
VFS簡介和核心操作函式
2024-03-25
函式
SQL入門之3 函式2
2007-04-03
SQL函式