MySQL必知必會筆記——查詢的基礎知識

kylinwms 發表於 2021-06-11
MySQL

查詢基礎知識

第七章 資料過濾

組合where子句

MySQL允許給出多個WHERE子句。這些子 句可以兩種方式使用:以AND子句的方式或OR子句的方式使用。

AND操作符

可使用AND操作符給WHERE子句附加條件

-- 檢索由1003製造且價格小於等於10美元的所有產品的名稱和價格
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

OR操作符

OR用來表示檢索匹配任一給定條件的行。

-- 檢索由1002和1003製造的產品的名稱和價格
SELECT prod_name, prod_price FROM products
WHERE vend_id = 1002 or vend_id = 1003;

計算次序

WHERE可包含任意數目的AND和OR操作符。允許兩者結合以進行復雜 和高階的過濾。

AND的優先順序高於OR

-- 列出價格為10美元(含)以上且由1002或1003製造的所有產品
SELECT prod_name, prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

可以用括號括起來優先讓某一部分先計算。

IN操作符

用來指定條件範圍,取合法值的由逗號分隔的清單全部在圓括號中。

-- 檢索供應商1002和1003製造的所有產品。
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

上述語句中WHERE vend_id IN (1002, 1003)等同於WHERE vend_id=1002 OR vend_id=1003)

IN比OR執行更快,最大的優點是可以包含其他SELECT語句,能夠更動態地建立WHERE子句。

  • 在使用長的合法選項清單時,IN操作符的語法更清楚且更直觀。
  • 在使用IN時,計算的次序更容易管理(因為使用的操作符更少)。
  • IN操作符一般比OR操作符清單執行更快。
  • IN的最大優點是可以包含其他SELECT語句,使得能夠更動態地建 立WHERE子句。

NOT操作符

-- 列出除1002,1003之外所有供應商供應的產品
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

用萬用字元進行過濾

萬用字元(wildcard) 用來匹配值的一部分的特殊字元。

LIKE操作符

LIKE指示MYSQL,後跟的搜尋模式利用萬用字元匹配而不是直接相等匹配進行比較。

百分號(%)萬用字元

表示任何字元出現任意次數

-- 例:找出所有jet起頭的產品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
-- 例:使用多個萬用字元,匹配任何位置包含anvil的值,不論它之前或之後出現什麼字元
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
-- 例:找出s起頭e結尾的所有產品
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

雖然似乎%萬用字元可以匹配任何東西,但有一個例外,即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作為產品名的行。

下劃線(_)萬用字元

-- 只匹配單個字元而不是多個字元
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

使用萬用字元的技巧

  • 不要過度使用萬用字元,如果其他操作符能夠達到目的應該使用其他操作符
  • 在確實需要使用萬用字元時,除非絕對有必要,否則不要把它們用在搜尋的開始處。 把萬用字元置於搜尋模式的開始處搜尋起來是最慢的。
  • 仔細注意萬用字元的位置

第九章 用正規表示式進行搜尋

使用MySQL正規表示式

基本字元匹配

MySQL 中使用 REGEXP 關鍵字指定正規表示式的字元匹配模式

-- 例:檢索prod_name包含文字1000的所有行
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

.表示匹配任意一個字元

SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
  • LIKE和REGEXP的區別: LIKE '1000'匹配整個列值,等於'1000'時才會返回相應行,而REGEXP '1000'在列值內進行匹配,如果包含'1000'則會返回相應行。

也就是說,LIKE 匹配整個列,如果被匹配的文字在列值中出現,LIKE 將不會找到它,相應的行也不會被返回(除非使用萬用字元)。而 REGEXP 在列值內進行匹配,如果被匹配的文字在列值中出現,REGEXP 將會找到它,相應的行將被返回,並且 REGEXP 能匹配整個列值(與 LIKE 相同的作用)。

看個實際例子就好理解了。

這是orders表:

image-20210608205727457

我們使用like來查詢:

select * from orders where cust_id like "100";

其結果如下,為空:

image-20210608213851428

使用regexp實驗:

select * from orders where cust_id REGEXP "100";

其結果如下:

image-20210608213822110

MySQL 的正規表示式匹配(自3.23.4版本後)不區分大小寫(即大寫和小寫都匹配)。為區分大小寫,可以使用 BINARY 關鍵字

WHERE prod_name REGEXP BINARY 'JetPack .000';

進行OR匹配

|為正規表示式的OR操作符,表示匹配其中之一

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

可以給出兩個以上的OR條件1000|2000|3000

匹配幾個字元之一

[]表示匹配[]中的任何單一字元,可以理解為另一種形式的OR語句。

另外,[123][1|2|3]的縮寫

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

-- 結果

+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
+-------------+

記得一定要加方括號,以下是不加方括號的結果

SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name

-- 結果

+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
|JetPack 1000 |
|JetPack 2000 |
|TNT (1 stick)|
+-------------+

不加方括號它匹配的含義是1 OR 2 OR 3 Ton,所以還會檢索出JetPack 1000等不符合要求的行。

字符集合也可以被否定,為否定一個字集,在集合的開始處放置^,例如[^123]匹配除這些字元的任何東西

匹配範圍

匹配0到9,可以用[0123456789],為了簡化,可以用-來定義範圍,可以寫成[0-9]。同理,a到z的範圍就可以寫成[a-z]

SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name

-- 結果

+-------------+
|  prod_name  |
+-------------+
| .5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
+-------------+

匹配特殊字元

特殊字元,比如我們前文說到的

  • .:匹配任一字元
  • []:匹配幾個字元中是某個字元
  • -:指定範圍

如下面的例子:

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
ORDER BY vend_name;

-- 結果

+---------------+
|   vend_name   |
+---------------+
| ACME          |
| Anvils R Us   |
| Furball Inc.  |
| Jet Set       |
| Jouets Et Ours|
| LT Supplies   |
+---------------+

因為'.'為匹配任意字元,所以匹配的結果不是我們想要的結果。

如果只想匹配帶.的結果,必須用\\為前導。同理,匹配其他特殊字元也要用\\為前導.

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

-- 結果

+---------------+
|   vend_name   |
+---------------+
| Furball Inc.  |
+---------------+

正規表示式中具有特殊意義的所有字元都要通過這種方式轉義 \\也用來引用元字元

元字元 說明
\\f 換頁
\\n 換行
\\r 回車
\\t 製表
\\v 縱向製表
為了匹配\本身,需要使用\\\

匹配字元類

說明
[:alnum:] 任意字母和數字(同[a-zA-Z0-9])
[:alpha:] 任意字元(同[a-zA-Z])
[:cntrl:] 空格和製表(同[\t])
[:digit:] ASCII控制字元(ASCII)0到31和127
[:graph:] 任意數字(同[0-9])
[:lower:] 任意小寫字母(同[a-z])
[:print:] 任意可列印字元
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字元
[:space:] 包括空格在內的任意空白字元(同[\f\n\r\t\v])
[:upper:] 任意大寫字母(同[A-Z])
[:xdigit:] 任意十六進位制數字(同[a-fA-F0-9])

匹配多個例項

元字元 說明
* 0個或多個匹配
+ 1個或多個匹配(等於{1,})
0個或1個匹配(等於{0,1})
{n} 指定數目的匹配
{n,} 不少於指定數目的匹配
{n.m} 匹配數目的範圍(m不超過255)

例:

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name

-- 結果

+---------------+
|   prod_name   |
+---------------+
| TNT (1 stick) |
| TNT (5 sticks)|
+---------------+

說明:

  • \\(表示匹配左括號
  • [0-9]表示匹配0到9的任意數字
  • stick?匹配'stick'和'sticks'
  • \\)表示匹配右括號

例:匹配連在一起的4位數字

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

-- 結果

+---------------+
|   prod_name   |
+---------------+
| JetPack 1000  |
| JetPack 2000  |
+---------------+
-- 也可以寫成 '[0-9][0-9][0-9][0-9]'

定位符

元字元 說明
^ 文字的開始
$ 文字的結尾
[:<:] 詞的開始
[:>:] 詞的結尾
例:找出以一個數(包括小數點開頭)開始的所有產品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

-- 結果

+---------------+
|   prod_name   |
+---------------+
| .5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
+---------------+

第十章 建立計算欄位

拼接欄位

拼接:將值聯結到一起構成單個值

在SELECT語句中,可使用Concat()函式來拼接兩個列。Concat()函式需要一個或多個指定的串,各個串之間用逗號分隔。

SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
ORDER BY vend_name;

-- 結果

+-----------------------------------------+
| Concat(vendname,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+

刪除欄位多餘空格

函式 說明
Trim() 去掉兩邊的空格
LTrim() 去掉左邊的空格
RTrim() 去掉右邊的空格

示例:使用 RTrim()函式刪除右側多餘的空格。

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;

使用別名

可以用AS關鍵字賦予別名

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS
vend_title
FROM vendors
ORDER BY vend_name;

-- 結果

+----------------------------+
| vend_title                 |
+----------------------------+
| ACME (USA)                 |
| Anvils R Us (USA)          |
| Furball Inc. (USA)         |
| Jet Set (England)          |
| Jouets Et Ours (France)    |
| LT Supplies (USA)          |
+----------------------------+

執行算術計算

-- 彙總物品的價格(單價乘以訂購數量)
SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;

-- 結果

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+

如上程式碼的第四行中出現了quantity * item_price,我們可以對檢索出的資料進行算術計算,常用的操作符如下:

操作符 說明
+
-
*
/

第十一章 使用資料處理函式

文字處理函式

常用的文字處理函式

函式 說明
Left() 返回串左邊的字元
Length() 返回串的長度
Locate() 找出串的一個子串
Lower() 將串轉換為小寫
LTrim() 去掉串左邊的空格
Right() 返回串右邊的字元
RTrim() 去掉串右邊的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字元
Upper() 將串轉換為大寫
-- 示例:將文字轉換為大寫
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

上表中Soundex()的補充說明:

SOUNDEX是一個將任何文字轉換為描述其語音表示的字母數字模式的演算法,使得能對串進行發音比較而不是字母比較。MySQL提供對SOUNDEX的支援。

看一下下面的例子

表中有一個使用者的名字為Y.Lee,通過select查詢聯絡人時輸入錯誤為Y.Lie。此時Y.Lee這一行時不會被檢索出來的,但使用SOUNDEX檢索,可以匹配發音類似於Y.Lie的聯絡名:

SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact)= Soundex('Y Lie');

-- 結果

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

日期和時間處理函式

函式 說明
AddDate() 增加一個日期(天、周等)
AddTime() 增加一個時間(時、分等)
CurDate() 返回當前日期
CurTime() 返回當前時間
Date() 返回日期時間的日期部分
DateDiff() 計算兩個日期之差
Date_Add() 高度靈活的日期計算函式
Date_Format() 返回一個格式化的日期或時間串
Day() 返回一個日期的天數部分
DayOfWeek() 對於一個日期,返回對應的星期幾
Hour() 返回一個時間的小時部分
Minute() 返回一個時間的分鐘部分
Month() 返回一個日期的月份部分
Now() 返回當前日期和時間
Second() 返回一個時間的秒部分
Time() 返回一個日期時間的時間部分
Year() 返回一個日期的年份部分

使用日期格式的注意點:

  • 日期必須為格式yyyy-mm-dd

  • 關於datetime

    • SELECT cust_id, order_num FROM orders
      WHERE order_date = '2021-01-02';
      
    • sql語句中的order_date型別為datetime,它具有時間值00:00:00。生成時間資料時,比如生成"2021-01-02",生成的資料除了年月日還會自動生成時分秒,預設是00:00:00。

    • image-20210611162750885

    • 此時,如果你檢索的值為2021-01-02 14:06:29,則上面的WHERE order_date = '2021-01-02'不會檢索出這一行

    • 要使用Date()函式,才能找到值為2021-01-02 14:06:29的行

    • SELECT cust_id, order_num FROM orders
      WHERE Date(order_date) = '2021-01-02';
      

示例:檢索出2005年9月下的所有訂單

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

或者

SELECT cust_id, order_num FROM orders
WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;

數值處理函式

函式 說明
Abs() 返回一個數的絕對值
Cos() 返回一個角度的餘弦
Exp() 返回一個數的指數值
Mod() 返回除操作的餘數
Pi() 返回圓周率
Rand() 返回一個隨機數
Sin() 返回一個角度的正弦
Sqrt() 返回一個數的平方根
Tan() 返回一個角度的正切

第十二章 彙總函式

聚集函式

聚集函式(aggregate function):執行在行組上,計算和返回單個值的函式。

函式 說明
AVG() 返回某列的平均值
COUNT() 返回某列的行數
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

VG()函式

例:返回products表中所有產品的平均價格

SELECT AVG(prod_price) AS avg_price FROM products;

例:返回特定供應商所提供產品的平均價格

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

COUNT()函式

例:返回customer表中客戶的總數

SELECT COUNT(*) AS num_cust FROM customers;

例:只對具有電子郵件地址的客戶計數

SELECT COUNT(cust_email) AS num_cust
FROM customers;

MAX()函式

例:返回products表中最貴的物品價格

SELECT MAX(prod_price) AS max_price
FROM products;

對非數值資料使用MAX() MySQL允許將它用來返回任意列中的最大值,包括返回文字列中的最大值。在用於文字資料時,如果資料按相應的列排序,則MAX()返回最後一行。MAX()函式忽略列值為NULL的行。

MIN()函式

例:

SELECT MIN(prod_price) AS min_price FROM products;

SUM()函式

返回指定列值的和(總計) 例:檢索所訂購物品的總數

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;

例:合計計算值,合計每項物品item_price*quantity,得出訂單總金額

SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;

聚集不同值(適用於5後的版本)

上述五個聚集函式都可以如下使用:

  • 對所有的行執行計算,指定ALL引數或不給引數(ALL為預設)
  • 只包含不同的值,指定DISTINCT引數
-- 物品的平均價格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

如果指定列名,則DISTINCT只能用於COUNT()。DISTINCT不能用於COUNT(*),因此不允許使用COUNT(DISTINCT), 否則會產生錯誤。類似地,DISTINCT必須使用列名,不能用於計算或表示式。

組合聚集函式

SELECT語句可根據需要包含多個聚集函式

SELECT COUNT(*) AS num_items;
	   MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

-- 結果

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.50 | 16.133571 |
+-----------+-----------+-----------+-----------+

第十三章 分組資料

資料分組

分組允許把資料分為多個邏輯組,以便能對每個組進行聚集計算。

建立分組

例:根據vend_id分組,對每個分組分別計算總數

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

-- 結果

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

結果表明vend_id為1001有3個,vend_id為1002有2個,vend_id為1003有7個,vend_id為1005有2個。

在具體使用GROUP BY子句前,需要知道一些重要的規定。

  • GROUP BY 子句可以包含任意數目的列,使得能對分組進行巢狀,為資料分組提供更細緻的控制
  • 如果GROUP BY子句中中巢狀了分組,資料將在最後規定的分組上進行彙總。換句話說,在建立分組時,指定的所有列都一起計算(所以不能從個別的列取回資料)。
  • GROUP BY子句中列出的每個列都必須是檢索列或有效的表示式(但不能是聚集函式)。如果在SELECT中使用表示式,則必須在GROUP BY子句中指定相同的表示式。不能使用別名。
  • 除聚集計算語句外,SELECT語句中的每個列都必須在GROUP BY子句中給出。
  • 如果分組列中具有NULL值,則NULL將作為一個分組返回。如果列中有多行NULL值,它們將分為一組。
  • GROUP BY子句必須出現在WHERE子句之後,ORDER BY子句之前。

過濾分組

WHERE指定的是行,不是分組,WHERE沒有分組的概念

使用HAVING過濾分組

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

-- 結果

+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+

WHERE不起作用,因為過濾是基於分組聚集值而不是特定行值的。

-- 列出具有2個(含)以上、價格為10(含)以上的產品的供應商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2

-- 結果

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

分組和排序

雖然GROUP BY和ORDER BY經常完成相同的工作,但它們是非常不同的。

ORDER BY與GROUP BY的區別:

ORDER BY GROUP BY
排序產生的輸出 分組行。但輸出可能不是分組的順序
任意列都可以使用(甚至 非選擇的列也可以使用) 只可能使用選擇列或表示式列,而且必須使用每個選擇 列表示式
不一定需要 如果與聚集函式一起使用列(或表示式),則必須使用

一般在使用GROUP BY子句時,應該也給出ORDER BY子句。這是保證資料正確排序的唯一方法。千萬不要僅依賴GROUP BY排序資料。

-- 檢索總計訂單價格大於等於50的訂單的訂單號和總計訂單價格
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;

SELECT子句順序

SELECT子句及其順序

子句 說明 是否必須使用
SELECT 要返回的列或表示式
WHERE 從中檢索資料的表 僅在從表選擇資料時使用
GROUP BY 分組說明 盡在按組計算聚集是使用
HAVING 組級過濾
ORDER BY 輸出排序順序
LIMIT 要檢索的行數

上述子句使用時必須遵循該順序

第十四章 使用子查詢

SELECT order_num FROM orderitems
WHERE prod_id = 'TNT2';

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);

可以使用如下方式

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

再加一個條件:

SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)

合併為一句sql

SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
				 WHERE order_name IN(SELECT order_num FROM orderitems
                 					 WHERE prod_id ='TNT2'));
  • 在WHERE子句中使用子查詢應保證SELECT語句有與WHERE子句中相同數目的列。
  • 這裡給出的程式碼有效並獲得所需的結果。但是,使用子查詢並不總是執行這種型別的資料檢索的最有效的方法。(即這樣寫執行的效能不一定最好)

作為計算欄位使用子查詢

# 對客戶10001的訂單進行計數
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
# 為了對每個客戶執行COUNT(*)計算,應該將COUNT(*)作為一個子查詢
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;

相關文章