文章案例所需的SQL文件，點擊下載
使用MySQL
進入mysql安裝目錄下的bin目錄：
- 連接Mysql：
mysql -uroot -p123456; - 顯示Mysql下的所有數據庫：
show databases; - 切換數據庫：
use local; - 顯示數據庫下所有表名：
show tables; - 顯示表中字段名、數據 類型、是否允許NULL、鍵信息、默認值以及其他信息：
show columns from fee; - 顯示允許的SHOW語句：
help show; - 顯示創建數據庫的語句以及使用字符：
show create database local; - 顯示創建表的語句：
show create talbe fee; - 顯示授予用戶(所有用戶或特定用戶)的安 全權限：
show grants; - 顯示服務器錯誤信息：
show errors; - 顯示服務器警告信息：
show warnings;
檢索數據
selete
-
檢索單列：
select prod_name from products; -
檢索多列：
select prod_id,vend_id,prod_name from products; -
檢索所有列：
select * from products; -
檢索不同的行：
select distinct vend_id from products; -
限制結果：
select vend_id from products limit 5;select vend_id from products limit 5,5;第一個數為開始位置，第二個數為要檢索的個數。使用完全限定的表名：
select products.vend_id from mysql_crash_course.products;
排序檢索數據
order
-
排序數據：
select prod_name from products order by prod_name; -
按多個列排序：
select prod_id,prod_price,prod_name from products order by prod_name,prod_price;僅在多個行具有相同的prod_price 值時才對產品按prod_name進行排序。如果prod_price列中所有的值都是唯一的，則不會按prod_name排序。
-
指定排序方向：
select prod_id,prod_price,prod_name from products order by prod_price desc;按價格以降序排序
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;以降序排序產品 (最貴的在最前面)，然后再對產品名排序：
DESC關鍵字只應用到直接位于其前面的列名。在上例中，只對 prod_price列指定DESC，對prod_name列不指定。因此， prod_price列以降序排序，而prod_name列(在每個價格內)仍然按標準 的升序排序。 (默認升序)
在字典(dictionary)排序順序中， A被視為與a相同，這是MySQL (和大多數數據庫管理系統)的默認行為。但是，許多數據庫 管理員能夠在需要時改變這種行為(如果你的數據庫包含大量 外語字符，可能必須這樣做)。
這里，關鍵的問題是，如果確實需要改變這種排序順序，用簡 單的ORDER BY子句做不到。你必須請求數據庫管理員的幫助
-
使用ORDER BY和LIMIT的組合，能夠找出一個列中最高或最低的值。
select prod_price from products order by prod_price desc limit 1;
過濾數據
where
select prod_price,prod_name from products where prod_price = 2.50;
從products表中檢索兩個列，但不返回所有行，只返 回prod_price值為2.50的行
WHERE子句操作符
| 操作符 | 說明 |
|---|---|
| = | 等于 |
| <>，!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between | 在指定的兩值之間 |
-
檢查單個值：
select prod_price,prod_name from products where prod_name = 'fuses';檢查WHERE prod_name=‘fuses’語句，它返回prod_name的值 為Fuses的一行。MySQL在執行匹配時默認不區分大小寫，所 以fuses與Fuses匹配。
select prod_price,prod_name from products where prod_price < 10;價格小于10美元的所有產品
select prod_price,prod_name from products where prod_price <= 10;價格小于等于10美元的所有產品：
-
不匹配檢查
select vend_id,prod_name from products where vend_id <> 1003;不是由供應商1003制造的所有產品
-
范圍值檢查
select prod_name,prod_price from products where prod_price between 5 and 10;
檢索價格在5美元和10 美元之間的所有產品使用BETWEEN時，必須指定兩個值 ——所需范圍的低端值和高端值。這兩個值必須用AND關鍵字 分隔。BETWEEN匹配范圍中所有的值，包括指定的開始值和結束值。
-
空值檢查
select prod_name from products where prod_price is null;NULL 無值(no value)，它與字段包含0、空字符串或僅僅包含 空格不同。
is null子句用來檢查具有NULL值的列。
AND操作符
select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <= 10;
句檢索由供應商1003制造且價格小于等于10美元的所 有產品的名稱和價格
OR操作符
select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002;
檢索由任一個指定供應商制造的所有產品的產品 名和價格。
計算次序:where子句從左往右，不要過分依賴。
錯誤SQL：select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002 and prod_price >= 10;
正確SQL：select prod_id,prod_price,prod_name from products where (vend_id = 1003 or vend_id = 1002) and prod_price >= 10;
檢索價格為10美元(含)以上且由1002或1003制 造的所有產品
IN操作符
select prod_name,prod_price from products where vend_id in (1002,1003) order by prod_name;
句檢索供應商1002和1003制造的所有產品。
為什么要使用IN操作符？其優點具體如下。
- 在使用長的合法選項清單時，IN操作符的語法更清楚且更直觀。
- 在使用IN時，計算的次序更容易管理(因為使用的操作符更少)。
- IN操作符一般比OR操作符清單執行更快。
- IN的最大優點是可以包含其他SELECT語句，使得能夠更動態地建 立WHERE子句。
NOT操作符
select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;
檢索出除1002和1003之外的所有供應 商制造的產品
用通配符進行過濾
LIKE操作符
百分號(%)通配符
value%：表示以value開始任意字符結尾且不限次數。
%value%：表示字符包含value，不限開頭和結尾。
%value：表示以任意字符開頭，value結尾。
select prod_id,prod_name from products where prod_name like 'jet%';
檢索出所有以詞jet起頭的產品。%告訴MySQL接受jet之后的任意字符，不 管它有多少字符。
下劃線(_)通配符
下劃線的用途與%一樣，但下劃線只匹配單個字符而不是多個字符。
select prod_id,prod_name from products where prod_name like '_ ton anvil';
查詢第一個字符為任意字符，后
ton anvil
使用通配符的技巧
盡管MySQL通配符很有用，但它要比一般檢索所花的時間更長，應合理使用：
-
不要過度使用通配符。如果其他操作符能達到相同的目的，應該 使用其他操作符。
-
在確實需要使用通配符時，除非絕對有必要，否則不要把它們用 在搜索模式的開始處。把通配符置于搜索模式的開始處，搜索起 來是最慢的。
-
仔細注意通配符的位置。如果放錯地方，可能不會返回想要的數據。
總之，通配符是一種極重要和有用的搜索工具，以后我們經常會用 到它。
用正則表達式進行搜索
使用MySQL正則表達式
正則表達式的作 用是匹配文本，將一個模式(正則表達式)與一個文本串進行比較。MySQL 用WHERE子句對正則表達式提供了初步的支持，允許你指定正則表達式， 過濾SELECT檢索出的數據。
MySQL僅支持多數正則表達式實現的一個很小的子集。
基本字符匹配
select prod_name from products where prod_name regexp '1000' order by prod_name;
檢索列prod_name包含 文本1000的所有行
REGEXP后所跟的東西作 為正則表達式(與文字正文1000匹配的一個正則表達式)處理。
select prod_name from products where prod_name regexp '.000' order by prod_name;
正則表達式
.000。.是正則表達式語言中一個特殊 的字符。它表示匹配任意一個字符，因此，1000和2000都匹配且返回。
LIKE與REGEXP差別：
select prod_name from products where prod_name like '1000' order by prod_name;
select prod_name from products where prod_name regexp '1000' order by prod_name;
如果執行上述兩條語句，會發現第一條語句不返回數據，而第 二條語句返回一行。LIKE匹配整個列。如果被匹配的文本在列值 中出現，LIKE將不會找到它，相應的行也不被返回(除非使用 通配符)。而REGEXP在列值內進行匹配，如果被匹配的文本在 列值中出現，REGEXP將會找到它，相應的行將被返回。這是一 個非常重要的差別。
自版本 3.23.4后，MySQL中的正則表達式匹配不區分大小寫。為區分大 小寫，可使用BINARY關鍵字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。
進行OR匹配
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;
了正則表達式1000|2000。|為正則表達式的OR操作 符。它表示匹配其中之一，因此1000和2000都匹配并返回。
匹配幾個字符之一
select prod_name from products where prod_name regexp '[123]' order by prod_name;
正則表達式[123] Ton，[123]定義一組字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回。
select prod_name from products where prod_name regexp '1|2|3 Ton' order by prod_name;
匹配'1'或 '2'或'3 ton
匹配范圍
集合可用來定義要匹配的一個或多個字符。例如，下面的集合將匹 配數字0到9： [0123456789]簡化：[0-9]
select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;
正則表達式[1-5] Ton。[1-5]定義了一個范圍，這個 表達式意思是匹配1到5，因此返回3個匹配行。由于5 ton匹配， 所以返回.5 ton。
匹配特殊字符
查找字符包含.字符的值
錯誤：select vend_name from vendors where vend_name regexp '.' order by vend_name;
.匹配任意字符，需要使用轉義\\.
正確：select vend_name from vendors where vend_name regexp '\\.' order by vend_name;
創建計算字段
拼接字段
Concat()函數：將值聯結到一起構成單個值。
select concat(vend_name,'(',vend_country,')') from vendors order by vend_name;
RTrim()函數：刪除數據右側多余的空格來整理數據
select concat(vend_name,'(',RTrim(vend_country),')') from vendors order by vend_name;
MySQL除了支持RTrim()(正如剛才所見，它去掉 串右邊的空格)，還支持LTrim()(去掉串左邊的空格)以及 Trim()(去掉串左右兩邊的空格)
使用別名
別名(alias)是一個字段或值 的替換名。別名用AS關鍵字賦予。
select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title from vendors order by vend_name;
執行算術計算
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num = 20005;
匯總物品的價格(單 價乘以訂購數量)
使用數據處理函數
文本處理函數
Upper()函數：將文本轉換為大寫。
select upper(vend_name) from vendors;
常用文本處理函數：
| 函數 | 說明 |
|---|---|
| Left() | 返回串左邊的字符 |
| Length() | 返回串的長度 |
| Locate() | 找出串的一個子串 |
| Lower() | 將串轉換為小寫 |
| LTrim() | 去掉串左邊的空格 |
| Right() | 返回串右邊的字符 |
| RTrim() | 去掉串右邊的空格 |
| Soundex() | 返回串的SOUNDEX值 |
| SubString() | 返回子串的字符 |
| Upper() | 將串轉換為大寫 |
select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y Lie');
使用Soundex()函數進行搜索，它匹配所有發音類似于 Y.Lie的聯系名
日期和時間處理函數
常用日期和時間處理函數:
| 函數 | 說明 |
|---|---|
| AddDate() | 增加一個日期(天、周等) |
| AddTime() | 增加一個時間(時、分等) |
| CurDate() | 返回當前日期 |
| CurTime() | 返回當前時間 |
| Date() | 返回日期時間的日期部分 |
| DateDiff() | 計算兩個日期之差 |
| Date_Add() | 高度靈活的日期運算函數 |
| Date_Format() | 返回一個格式化的日期或時間串 |
| Day() | 返回一個日期的天數部分 |
| DayOfWeek() | 對于一個日期，返回對應的星期幾 |
| Hour() | 返回一個時間的小時部分 |
| Minute() | 返回一個時間的分鐘部分 |
| Month() | 返回一個日期的月份部分 |
| Now() | 返回當前日期和時間 |
| Second() | 返回一個時間的秒部分 |
| Time() | 返回一個日期時間的時間部分 |
| Year() | 返回一個日期的年份部分 |
select cust_id,order_num,order_date,Date(order_date) from orders where Date(order_date) = '2005-09-01';
數值處理函數
| 函數 | 說明 |
|---|---|
| Abs() | 返回一個數的絕對值 |
| Cos() | 返回一個角度的余弦 |
| Exp() | 返回一個數的指數值 |
| Mod() | 返回除操作的余數 |
| Pi() | 返回圓周率 |
| Rand() | 返回一個隨機數 |
| Sin() | 返回一個角度的正弦 |
| Sqrt() | 返回一個數的平方根 |
| Tan() | 返回一個角度的正切 |
匯總數據
聚集函數
聚集函數(aggregate function) 運行在行組上，計算和返回單 個值的函數。
常用SQL聚集函數：
| 函數 | 說明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行數 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
select avg(prod_price) avg_price from products where vend_id = 1003;
僅過濾出
vend_id為1003的產品的平均值。
聚集不同值
DISTINCT：只包含不同的值。
select avg(distinct prod_price) avg_price from products where vend_id = 1003;
**取別名：**在指定別名以包含某個聚集函數的結果時，不應該使 用表中實際的列名。雖然這樣做并非不合法，但使用唯一的名 字會使你的
SQL更易于理解和使用。
分組數據
GROUP BY子句
select vend_id,count(*) as num_prods from products group by vend_id;
因為使用了
GROUP BY，就不必指定要計算和估值的每個組了。系統 會自動完成。GROUP BY子句指示MySQL分組數據，然后對每個組而不是 整個結果集進行聚集。
在具體使用GROUP BY子句前，需要知道一些重要的規定。
GROUP BY子句可以包含任意數目的列。這使得能對分組進行嵌套， 為數據分組提供更細致的控制。- 如果在
GROUP BY子句中嵌套了分組，數據將在后規定的分組上 進行匯總。換句話說，在建立分組時，指定的所有列都一起計算 (所以不能從個別的列取回數據)。 GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式 (但不能是聚集函數)。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用別名。- 除聚集計算語句外，
SELECT語句中的每個列都必須在GROUP BY子 句中給出。 - 如果分組列中具有
NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。 GROUP BY子句必須出現在WHERE子句之后，ORDER BY子句之前。
WITH ROLLUP關鍵字
使用
WITH ROLLUP關鍵字，可以得到每個分組以 及每個分組匯總級別(針對每個分組)的值。
select vend_id,count(*) as num_prods from products group by vend_id with rollup;
過濾分組
HAVING：。HAVING非常類似于WHERE。事實上，目前為止所 學過的所有類型的WHERE子句都可以用HAVING來替代。唯一的差別是 WHERE過濾行，而HAVING過濾分組。
select vend_id,count(*) as num_prods from products group by vend_id having count(*) >= 2;
這條SELECT語句的前3行類似于上面的語句。后一行增加了 HAVING子句，它過濾COUNT(*) >= 2。
HAVING和WHERE的差別：這里有另一種理解方法，WHERE在數據 分組前進行過濾，HAVING在數據分組后進行過濾。這是一個重 要的區別，WHERE排除的行不包括在分組中。這可能會改變計 算值，從而影響HAVING子句中基于這些值過濾掉的分組。
分組和排序
GROUP BY和ORDER BY經之間的差別：
| ORDER BY | GROUP BY |
|---|---|
| 排序產生的輸出 | 分組行。但輸出可能不是分組的順序 |
| 任意列都可以使用(甚至 非選擇的列也可以使用) | 只可能使用選擇列或表達式列，而且必須使用每個選擇 列表達式 |
| 不一定需要 | 如果與聚集函數一起使用列(或表達式)，則必須使用 |
SELECT子句順序
| 子句 | 說明 | 是否必須使用 |
|---|---|---|
| SELECT | 要返回的列或表達式 | 是 |
| FROM | 從中檢索數據的表 | 僅在從表選擇數據時使用 |
| WHERE | 行級過濾 | 否 |
| GROUP BY | 分組說明 | 僅在按組計算聚集時使用 |
| HAVING | 組級過濾 | 否 |
| ORDER BY | 輸出排序順序 | 否 |
| LIMIT | 要檢索的行數 | 否 |
