mysql中null(IFNULL,COALESCE和NULLIF)相關知識點總結

發布時間: 2019-12-31 13:26:37 來源: 互聯網 欄目: Mysql 點擊:

這篇文章主要介紹了mysql中null(IFNULL,COALESCE和NULLIF)相關知識點,結合實例形式總結分析了mysql中關于null的判斷、使用相關操作技巧與注意事項,需要的朋友可以參考下

本文實例講述了mysql中null(IFNULL,COALESCE和NULLIF)相關知識點。分享給大家供大家參考,具體如下:

在MySQL中,NULL值表示一個未知值,它不同于0或空字符串'',并且不等于它自身。

我們如果將NULL值與另一個NULL值或任何其他值進行比較,則結果為NULL,因為一個不知道是什么的值(NULL值)與另一個不知道是什么的值(NULL值)比較,其值當然也是一個不知道是什么的值(NULL值)。

然而我們通常,使用NULL值來表示數據丟失,未知或不適用的情況。 例如,潛在客戶的電話號碼可能為NULL,并且可以稍后添加。所以我們創建表時,可以通過使用NOT NULL約束來指定列是否接受NULL值。接下來,我們來創建一張leads表,并且以此為依據來具體了解下:

CREATE TABLE leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  source VARCHAR(255) NOT NULL,
  email VARCHAR(100),
  phone VARCHAR(25)
);

我們可以看出來,id是主鍵列,它不接受任何NULL值,然后first_name,last_name和source列使用NOT NULL約束,因此,不能在這些列中插入任何NULL值,而email和phone列則可接受NULL值。

所以,我們可以在insert語句中使用NULL值來指定數據丟失。 例如,以下語句將一行插入到線索表中。 因為電話號碼丟失,所以使用NULL值:

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','[email protected]',NULL);

因為email列的默認值為NULL,可以按照以下方式在INSERT語句中省略電子郵件:

INSERT INTO leads(first_name,last_name,source,phone)
VALUES('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');

完事如果我們要將列的值設置為NULL,可以使用賦值運算符(=)。 例如,要將David William的手機(phone)更新為NULL,請使用以下UPDATE語句:

UPDATE leads 
SET 
  phone = NULL
WHERE
  id = 3;

但是如果使用order by子句按升序對結果集進行排序,則MySQL認為NULL值低于其他值,因此,它會首先顯示NULL值。以下查詢語句按照電話號碼(phone)升序排列:

SELECT 
  *
FROM
  leads
ORDER BY phone;

執行上面查詢語句,結果如下:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 1 | John    | Doe    | Web Search  | [email protected] | NULL      |
| 3 | David   | William  | Web Search  | NULL        | NULL      |
| 2 | Lily    | Bush   | Cold Calling | NULL        | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

如果使用ORDER BY DESC,NULL值將顯示在結果集的最后:

SELECT 
  *
FROM
  leads
ORDER BY phone DESC;

執行上面查詢語句,結果如下:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 2 | Lily    | Bush   | Cold Calling | NULL        | (408)-555-1234 |
| 1 | John    | Doe    | Web Search  | [email protected] | NULL      |
| 3 | David   | William  | Web Search  | NULL        | NULL      |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

我們如果要在查詢中測試NULL,可以在where子句中使用IS NULL或IS NOT NULL運算符。例如,要獲得尚未提供電話號碼的潛在客戶,請使用IS NULL運算符,如下所示:

SELECT 
  *
FROM
  leads
WHERE
  phone IS NULL;

執行上面查詢語句,結果如下:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source   | email        | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | John    | Doe    | Web Search | [email protected] | NULL |
| 3 | David   | William  | Web Search | NULL        | NULL |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

我們還可以使用IS NOT運算符來獲取所有提供電子郵件地址的潛在客戶:

SELECT 
  *
FROM
  leads
WHERE
  email IS NOT NULL;

執行上面查詢語句,結果如下:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source   | email        | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | John    | Doe    | Web Search | [email protected] | NULL |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

然而,即使NULL不等于NULL,GROUP BY子句中視兩個NULL值相等,來看下sql實例:

SELECT 
  email, count(*)
FROM
  leads
GROUP BY email;

該查詢只返回兩行,因為其郵箱(email)列為NULL的行被分組為一行,結果如下所示:

+---------------------+----------+
| email        | count(*) |
+---------------------+----------+
| NULL        |    2 |
| [email protected] |    1 |
+---------------------+----------+
2 rows in set

我們要知道在列上使用唯一約束或UNIQUE索引時,可以在該列中插入多個NULL值,在這種情況下,MySQL認為NULL值是不同的。接下來我們通過為phone列創建一個UNIQUE索引來驗證這一點:

CREATE UNIQUE INDEX idx_phone ON leads(phone);

這里我們要注意,如果使用BDB存儲引擎的話,mysql會認為NULL值相等,因此我們不能將多個NULL值插入到具有唯一約束的列中。

既然知道了null的好處和壞處,我們就來看下在mysql中應該如何處理它吧。mysql一共提供了三個函數,分別是IFNULL,COALESCE和NULLIF。

我們來分別看下,首先,IFNULL函數接受兩個參數。 如果IFNULL函數不為NULL,則返回第一個參數,否則返回第二個參數。例如,如果不是NULL,則以下語句返回電話號碼(phone),否則返回N/A,而不是NULL。來看個實例:

SELECT 
  id, first_name, last_name, IFNULL(phone, 'N/A') phone
FROM
  leads;

執行上面查詢語句,得到以下結果:

+----+------------+-----------+----------------+
| id | first_name | last_name | phone     |
+----+------------+-----------+----------------+
| 1 | John    | Doe    | N/A      |
| 2 | Lily    | Bush   | (408)-555-1234 |
| 3 | David   | William  | N/A      |
+----+------------+-----------+----------------+
3 rows in set

完事就是COALESCE函數,它接受參數列表,并返回第一個非NULL參數。 例如,可以使用COALESCE函數根據信息的優先級按照以下順序顯示線索的聯系信息:phone, email和N/A。以下是案例:

SELECT 
  id,
  first_name,
  last_name,
  COALESCE(phone, email, 'N/A') contact
FROM
  leads;

執行上面查詢語句,得到以下代碼:

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact       |
+----+------------+-----------+---------------------+
| 1 | John    | Doe    | [email protected] |
| 2 | Lily    | Bush   | (408)-555-1234   |
| 3 | David   | William  | N/A         |
+----+------------+-----------+---------------------+
3 rows in set

最后就是NULLIF函數了,它接受兩個參數。如果兩個參數相等,則NULLIF函數返回NULL。 否則,它返回第一個參數。在列中同時具有NULL和空字符串值時,NULLIF函數很有用。 例如,我們錯誤地將以下行插入到leads表中:

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','[email protected]','');

因為phone是一個空字符串:'',而不是NULL。所以,如果我們想獲得潛在客戶的聯系信息,則最終得到空phone,而不是電子郵件,如下所示:

SELECT 
  id,
  first_name,
  last_name,
  COALESCE(phone, email, 'N/A') contact
FROM
  leads;

執行上面查詢語句,得到以下代碼:

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact       |
+----+------------+-----------+---------------------+
| 1 | John    | Doe    | [email protected] |
| 2 | Lily    | Bush   | (408)-555-1234   |
| 3 | David   | William  | N/A         |
| 4 | Thierry  | Henry   |           |
+----+------------+-----------+---------------------+

我們如果要解決這個問題,就要使用NULLIF函數將電話與空字符串('')進行比較,如果相等,則返回NULL,否則返回電話號碼:

SELECT 
  id,
  first_name,
  last_name,
  COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
  leads;

執行上面查詢語句,得到以下代碼:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | contact         |
+----+------------+-----------+--------------------------+
| 1 | John    | Doe    | [email protected]   |
| 2 | Lily    | Bush   | (408)-555-1234      |
| 3 | David   | William  | N/A           |
| 4 | Thierry  | Henry   | [email protected] |
+----+------------+-----------+--------------------------+
4 rows in set

好啦,本次記錄就到這里了。

更多關于MySQL相關內容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數據庫鎖相關技巧匯總》及《MySQL常用函數大匯總》

希望本文所述對大家MySQL數據庫計有所幫助。

本文標題: mysql中null(IFNULL,COALESCE和NULLIF)相關知識點總結
本文地址: http://www.1921352.live/shujuku/mysql/296429.html

如果認為本文對您有所幫助請贊助本站

支付寶掃一掃贊助微信掃一掃贊助

  • 支付寶掃一掃贊助
  • 微信掃一掃贊助
  • 支付寶先領紅包再贊助
    聲明:凡注明"本站原創"的所有文字圖片等資料,版權均屬編程客棧所有,歡迎轉載,但務請注明出處。
    mysql日期和時間的間隔計算實例分析使用MySQL實現一個分布式鎖
    Top 网上挖矿机赚钱 大公司投资的理财平台 福建体彩3 选7走势图 陕西11选5查询 休闲挂机游戏 什么板块股票推荐 广西快3福彩快三官网 仙梦心水七尾中特 北京11选5重复号 网上幸运快三是真的吗 股票推荐群诈骗