《MySQL必知必會》[01] 基本查詢

《MySQL必知必會》（點擊查看詳情）

---

1、寫在前面的話

這本書是一本MySQL的經典入門書籍，小小的一本，也受到眾多網友推薦。之前自己學習的時候是啃的清華大學出版社的計算機系列教材《數據庫系統概論》，基礎也算是半罐水，糊里糊涂，知識點欠缺梳理。于是，也算是借此機會，從這本書對數據庫和SQL部分的知識點進行梳理，記錄一下基礎的關鍵的東西，也便于以后翻閱查詢，好了，就不叨叨了。

---

2、MySQL基本操作

• 命令輸入在 mysql> 之后；
• 命令用；或 \g 結束，僅按Enter不執行命令；
• 輸入 help 獲得幫助；
• 輸入 quit 或 exit 退出命令行實用程序

{ }花括號中的內容表示實際內容，而非此處的固定字符。

操作	命令
進入MySQL ? ?	安裝目錄的bin下打開命令行，輸入mysql -u {yourAccountName} -p
顯示現有數據庫 ? ?	show databases;
選擇數據庫 ? ?	use {databaseName};
顯示已選數據庫的表	show tables;
顯示列（字段）	show columns from {tableName}; 或 describe {tableName};
顯示授權用戶的安全權限	show grants;

---

3、基本查詢 SELECT

操作 ? ?	語法
查詢單列 ? ?	SELECT {columnName} FROM {tableName};
查詢多列 ? ?	SELECT {columnName1, columnName2} FROM {tableName};
查詢所有列 ? ?	SELECT * FROM {tableName};

其他關鍵字：

• AS
• 放在字段名后或表名后，表示別名
• e.g.?SELECT {newTableName}.{columnName} AS {newColumnName} FROM {tableName} as {newTableName};
• e.g. SELECT b.author as 'zuozhe' FROM books as b;

• DISTINCT
• 放在SELECT之后，字段名之前，表示互異，使SQL對任何元組只生成一份副本，且應用于所有列
• e.g. SELECT DISTINCT {columnName} FROM {tableName};

• ORDER BY
• 排序，BY后面跟字段名，默認為升序（從小到大）排列，多個排序規則用逗號隔開依次滿足條件
• e.g. SELECT * FROM fruits ORDER BY price;

• DESC
• 配合ORDER BY，表示降序排列，放在字段名后，限制單列
• e.g.?SELECT * FROM fruits ORDER BY price DESC;

• LIMIT beginColumnNum, ColumnAmount
• 限制結果，指定輸出從beginColumnNum開始的（索引從0開始而不是1）的ColumnAmount行記錄（常用于分頁）
• e.g. SELECT * FROM {tableName} LIMIT 5, 10 ?（輸出表中包括第6行開始的10條數據）

另外：

• SQL語句不分大小寫，為了便于閱讀可以將所有關鍵字大寫，列和表名采用小寫；
• SQL語句可以分一行給出，也可以分成多行；
• 不需要查詢出明確的列時盡量采用通配符 * 可以提高性能。

---

4、條件查詢 WHERE

為了獲取特定的結果而指定條件，SQL中根據WHERE子句中指定的搜索條件進行過濾，WHERE跟在表名（FROM子句）之后。

基本操作符（1）	說明
=	等于 ? ?
<>	不等于 ? ?
!=	不等于 ? ?
<	小于 ? ?
<=	小于等于 ? ?
>	大于 ? ?
>=	大于等于 ? ?
BETWEEN	指定兩值之間（含開始和結束），配合AND（BETWEEN valA AND valB）
IS NULL ? ?	空值檢查

注意：

• 表示要查詢某字段為空值，不能使用"=NULL"或"<>NULL"，而是"IS NULL"或"IS NOT NULL"

基本操作符（2）	說明
AND ? ?	“與”，同時過濾多個條件子句
OR ? ?	“或”，配合檢索匹配任一條件的記錄
NOT ?	“非”，否定它之后所跟著的條件（常配合IN、BETWEEN、EXISTS等）
IN ? ?	指定條件范圍，多值之間逗號隔開（功能類似OR）

注意：

• NOT優先級高于AND，AND優先級高于OR

---

5、模糊查詢 LIKE

用于查詢包含某個關鍵字的記錄。

操作符	說明
LIKE ? ??	表示搜索模式利用通配符匹配

通配符	說明
%	不限長度的字符序列匹配
_	單個字符的序列匹配

轉義符	說明
\	表示某些特殊符號，如‘\%’表示匹配百分號字符，而不是通配符

---

6、分組查詢 GROUP BY?

關鍵字	說明
GROUP BY	根據字段進行分組，必須在WHERE子句之后，ORDER之前
HAVING	過濾分組，用法類似于WHERE，但分組只能用HAVING不能用WHERE

注意：

• GROUP BY 可以包含多列，即分組嵌套，且嵌套后數據在最后規定的分組上進行匯總
• 分組列如果有NULL，則NULL會作為一個分組返回（多行NULL將分為一組）
• GROUP BY 如果用于輸出，實際只會輸出每組首行，而不是全部

---

7、聚合函數

函數	說明
COUNT()	求行數，在使用 * 通配符時才不會忽略NULL值的行
AVG()?	求平均值，自動忽略NULL值的行
MAX()	求最大值，自動忽略NULL值的行
MIN() ? ?	求最小值，自動忽略NULL值的行
SUM() ? ?	求和，自動忽略NULL值的行

注意：

• 以上的算值函數，可以利用算術符作用多列，其含義是類似 “SELECT MAX(price*amount) FROM fruits” 如此用法

• 以上5種聚合函數，在MySQL 5.0 版本之后，其括號中的表達式可以使用前綴關鍵字DISTINCT
• 聚合函數多配合分組GROUP BY使用

---

8、子查詢

子查詢，即嵌套在其他查詢中的查詢。在SELECT語句中，子查詢總是從內向外分步進行處理。

8.1 子查詢過濾

SELECT  cust_id
FROMorders
WHEREorder_num IN 
(
SELECTorder_num
FROMorderitems
WHEREprod_id = 'TNT2'
)

14

1

SELECT ?

2

  cust_id

3

FROM

4

  orders

5

WHERE

6

  order_num IN 

7

(

8

SELECT

9

  order_num

10

FROM

11

  orderitems

12

WHERE

13

  prod_id = 'TNT2'

14

)

• 子查詢應與WHERE匹配相同數量的列，通常是單個列匹配，結合IN等關鍵字使用

8.2 子查詢作為計算字段

SELECTcust_name,cust_state,(SELECTCOUNT(*)FROMordersWHEREorders.cust_id = customers.cust_id) AS orders
FROMcustomers
ORDER BY cust_name

14

1

SELECT

2

  cust_name,

3

  cust_state,

4

  (

5

 ?SELECT

6

 ? ?COUNT(*)

7

 ?FROM

8

 ?  orders

9

 ?WHERE

10

 ?  orders.cust_id = customers.cust_id

11

  ) AS orders

12

FROM

13

  customers

14

ORDER BY cust_name

• 涉及外部查詢的子查詢叫做相關子查詢，每次取外部查詢的值和子查詢所有行分別匹配，再取外部查詢下一行和子查詢匹配，循環至結束
• 子查詢可以逐步建立用來進行測試和調試，這是很有技巧性的

---

9、創建計算字段

直接從數據庫中檢索出轉換、計算或格式化過后的數據，而不是檢索出數據然后再到客戶機程序中重新格式化。

9.1 拼接字段

輸出指定格式，如vendors供應商表包含name和location，希望name按照name(location)格式輸出，使用Concat函數：

（多數DBMS使用 + 或 || 實現拼接，而MySQL使用Concat()實現）

SELECTConcat(vend_name, '(', vend_country, ')')
FROMvendors
ORDER BY vend_name;

5

1

SELECT

2

  Concat(vend_name, '(', vend_country, ')')

3

FROM

4

  vendors

5

ORDER BY vend_name;

Concat()拼接串，把多個串連接起來形成長串，各串之間用逗號隔開。

還可以用類似去空格函數對數據進行進一步的整理，RTrim()刪除數據右側多余的空格，同理LTrim()刪除左側多余的空格，Trim()刪除兩端的空格：

SELECTConcat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROMvendors
ORDER BY vend_name;

5

1

SELECT

2

  Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title

3

FROM

4

  vendors

5

ORDER BY vend_name;

9.2 執行算術計算

可以針對檢索出來的數據直接進行基本的算術計算（加減乘除），如：

SELECTprod_id,quantity,item_price,quantity*item_price AS expanded_price
FROMorderitems
WHEREorder_num = 20005;

9

1

SELECT

2

  prod_id,

3

  quantity,

4

  item_price,

5

  quantity*item_price AS expanded_price

6

FROM

7

  orderitems

8

WHERE

9

  order_num = 20005;

---

10、處理數據的函數

寫在前面的話，SQL語句的通用性很強，可以說是可移植的，雖然部分數據庫之間存在差異，可是通常并不是那么難以處理。但是函數的可移植性就比較差了，幾乎每種主要的DBMS的實現都支持其他實現不支持的函數，有時候差異還很大。

如果你決定在SQL中使用函數，應該確保做好代碼注釋，以便將來你或者他人能確切地知道SQL代碼的含義。

10.1 文本處理函數

Left() ? ?	返回串左邊的字符
Length() ? ?	返回串的長度 ? ?
Locate() ? ?	找出串的一個子串
Lower() ? ?	將串轉換為小寫 ? ?
LTrim() ? ?	去掉串左邊的空格 ? ?
Right() ? ?	返回串右邊的字符 ? ?
RTrim() ? ?	去掉串右邊的空格 ? ?
Soundex() ? ?	返回串的soundex值（將任何文本串轉換為描述其語音表示的字母數字模式，你可以理解為，變成讀音）p70
SubString() ? ?	返回子川的字符 ? ?
Upper()	將串轉換為大寫

10.2 日期和時間處理函數

寫在前面的話，MySQL使用的日期格式，無論什么時候都必須為 yyyy-mm-dd，雖然其他日期格式可能也行，但是這個是首選日期格式。

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

WHERE order_date = '2005-09-01' 可靠嗎？假如時間是 2005-09-01 11:30:05，則該匹配會失敗；如果我們使用Date()提取日期部分，則 WHERE Date(order_date) = '2005-09-01' 則更可靠了。

檢索某年2月的所有數據，WHERE Date(order_date) BETWEEN '2005-02-01' AND '2005-02-28'，這種方式可行，但是需要你自己去計算是閏年還是平年，以確定這個月份到底有多少天，比較麻煩，實際上你可以用更簡單的方式：WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

10.3 數值處理

數值處理一般用于代數、三角或幾何運算，使用并不頻繁：

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

---

11、用正則表達式進行搜索

正則表達式的作用是匹配文本，其概念和基本使用，可以參考在JS篇中的一篇博客《03標準對象-02-RegExp 正則表達式》，這跟在JS還是在SQL里都沒有關系，因為幾乎所有種類的程序設計語言、文本編輯器和操作系統等，都支持正則表達式。

正則表達式在SQL中使用時要使用REGEXP關鍵字（Regular Expression，正則表達式），用法類似LIKE：

SELECTprod_name
FROMproducts
WHEREprod_name REGEXP '.000'
ORDER BY prod_name;

7

1

SELECT

2

  prod_name

3

FROM

4

  products

5

WHERE

6

  prod_name REGEXP '.000'

7

ORDER BY prod_name;

上條SQL你可以得到價格尾數帶000的記錄，如1000或2000都會得到匹配返回。

除了在延伸閱讀的博客里的內容，還有一些需要提到的不同的：

（1）匹配特殊字符

用\\為前導，如希望查找 '-'，要使用\\- （正則一般只用一個\做轉義，但MySQL要求兩個，因為它自己要解釋一個，正則要解釋一個）

（2）匹配字符類

[:alnum:] ? ?	任意字母和數字，同[a-zA-Z0-9]
[:alpha:] ? ?	任意字符，同[a-zA-Z]
[:blank:] ? ?	空格和制表
[:cntrl:]	ASCII控制字符
[:digit:]	任意數字
[:graph:]	與print相同但不包括空格
[:lower:]	任意小寫字母
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]也不在[:cntrl:]中的任意字符
[:space:]	包括空格在內的任意空白字符
[:upper:]	任意大寫字母
[:xdigit:]	任意十六進制數字，同[a-fA-F0-9]

一個例子：

SELECTprod_name
FROMproducts
WHEREprod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;

1

SELECT

2

  prod_name

3

FROM

4

  products

5

WHERE

6

  prod_name REGEXP '\\([0-9] sticks?\\)'

7

ORDER BY prod_name;

輸出結果：

TNT (1 stick)

TNT (5 sticks)

---

12、其他

12.1 SELECT子句的順序

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT