建議開發人員：將SQL關鍵字使用大寫，而對所有列和表名使用小寫

一、前提知識

A、學習東西是有成本的，那我們為什么需要資料庫，它用來做什么？

MySQL 是最流行的關系型資料庫管理系統之一，MySQL資料庫擁有性能高，成本低，目前被廣泛應用于互聯網上的中小型網站上，它可以用來存盤、查詢、增加、洗掉、修改的資料管理系統，可以說是能滿足大部分業務需求，

B、既然知道它重要，那 MySQL 市場使用如何呢？排名多少嘞？

下面通過查詢：1、資料庫排名2020 及 2、百度指數（搜索量）進行確認，截圖如下

C、MySQL果然普及，使用量也廣，那我們應該怎樣學習呢？

首先需要安裝好：MySQL —— 詳細安裝
下載mysql 練習腳本【不需要積分的哦！】：mysql_scripts 超級好的資料.zip

二、基礎知識，多練習即可

2.1 了解資料庫和表

# 回傳可用資料庫串列

USE testdb;

# 使用資料庫testdb

USE testdb;

# 顯示資料庫中表

SHOW TABLES;

# 顯示customers表的資訊

SHOW COLUMNS FROM customers;

# 顯示customers表的資訊，簡略版本

DESCRIBE customers;

2.2 檢索資料【查詢資料】

# 檢索單個列

SELECT prod_name FROM products;

# 檢索多個列

SELECT prod_id, prod_name, prod_price FROM products;

# 檢索所有列（注意：降低檢索、應用程式性能）

SELECT * FROM products;

# 檢索不同的行(只回傳不同的值)，不能部分使用DISTINCT

SELECT DISTICT vend_id FROM products;

# LIMIT 5指示MySQL回傳不多于5行

SELECT prod_name FROM products LIMIT 5;

# LIMIT 5,5指示MySQL回傳從行5開始的5行，第一個數為開始的位置，第二個為要檢索的行數

SELECT prod_name FROM products LIMIT 5,5;

# 使用完全限定的表名（表名、列名都可能進行限定）

SELECT products.prod_name FROM products;
SELECT products.prod_name FROM testdb.products;

2.3 排序檢索資料

# 對prod_name列以字母順序排序資料

SELECT prod_name FROM products ORDER BY prod_name;

# 檢索3個列，并按其中兩個列對結果進行排序——首先按價格，然后再按名稱排序

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

# 指定排序方向,按價格以降序排序產品

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;

# DESC關鍵字只應用到直接位于其前面的列名,如果每個列降序排序,每個列都指定DESC關鍵字

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

# 使用ORDER BY 和 LIMIT 的組合,能夠找出一個列中最高或最低的值

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

2.4 過濾資料

# 使用WHERE子句，只回傳prod_price值為2.50的行

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

# 檢查單個值，單引號用來限定字串，如果將值與串型別的列進行比較，則需要限定引號，用來與數值列進行比較的值不用引號

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name, prod_price FROM products WHERE prod_price <10;

# 不匹配檢查

SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;

# 范圍值檢查

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

# 空值檢查

SELECT prod_name, prod_price FROM products WHERE prod_price IS NULL;
SELECT cust_id FROM customers WHERE cust_email IS NULL;

2.5 資料過濾

# 組合WHERE子句
# AND運算子

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

# OR運算子

SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

# 計算次序，AND運算子運算順序比OR高

SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

# 使用圓括號可以消除歧義

SELECT prod_name, vend_id, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id IN (1002, 1003) AND prod_price >= 10;

# IN運算子

SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;

# 上述兩個查詢陳述句結果輸出都一致的，為什么使用IN運算子呢？
# 清楚、直觀、容易管理；IN運算子一般比OR運算子清單執行更快；可以包含其他SELECT陳述句

# NOT運算子

SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

2.6 用通配符進行過濾

# LIKE運算子
# 百分號(%)通配符

SELECT prod_name, prod_price FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE '%anvil%'
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE 's%e';

# 下劃線(_)通配符，只匹配單個字符而不是多個字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% ton anvil';

# 通配符使用注意點：

不要過度使用通配符，如果其他運算子能達到相同的目的，應該使用其他運算子；
在確實需要使用通配符時，除非絕對有必要，否則不要把它們用在搜索模式的開始處，把通配符置于搜索模式的開始處，搜索起來是最慢的，因為是全表掃描，時間復雜度降為O(N)，
仔細注意通配符的位置，如果放錯地方，可能不會回傳想要的資料

2.7 用正則運算式進行搜索

# 基本字符匹配

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;

# 進行OR匹配，｜為正則運算式的OR運算子

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

# 匹配幾個字符之一，通過指定一組用[和]括起來的字符來完成

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[1|2|3] Ton' ORDER BY prod_name;

# 字符集合也可以被否定，即，它們將匹配除指定字符外的任何東西，在集合的開始處放置一個^即可

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

# 匹配范圍
#[0123456789]相當于[0-9]；范圍不限于完整的集合，[1-3]和[6-9]也是合法的范圍；[a-z]匹配任意字母字符

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

# 匹配特殊字符
# 為了匹配特殊字符，必須用\\為前導，\\-表示查找-， \\.表示查找.

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

#匹配字符類

運算式	運算式含義
[:alnum:]	任意字母和數字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意數字（同[0-9]）
[:graph:]	與[:print:]相同，但不包括空格
[: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;
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

#定位符

定位符
定位符名稱	功能說明
^	文本的開始
$	文本的結尾
[[:<:]]	詞的開始
[[:>:]]	詞的結尾

#找出以一個數（包括以小數點開始的數）

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

2.8 創建計算欄位

# 拼接欄位，輸出效果：ACME(USA)

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

# 使用別名

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 expand_price FROM orderitems WHERE order_num = 20005;

2.9 使用資料處理函式

# 函式的移植性不是很強，如果你決定使用函式，應該保證做好代碼注釋，以便以后你（或其他人）能確切地知道所編SQL代碼的含義
# Upper()將文本轉換為大寫

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

常見字串處理函式
處理字串函式名	功能說明
Left()	回傳串左邊的字符
Length()	回傳串的長度
Locate()	找出串的一個子串
Lower()	將串轉換為小寫
LTrim()	去掉串左邊的空格
Right()	回傳串右邊的字符
RTrim()	去掉串右邊的空格
Soundex()	回傳串的SOUNDEX值
SubString()	回傳子串的字符
Upper()	將串轉換為大寫

#日期和時間處理函式，日期必須為格式yyyy-mm-dd

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

# 如果要的是日期，請使用Date()，應該使用第二種方式，查詢2005-09-01當天的記錄

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

# 檢索出2005年9月下的所有定單

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

# 一種不需要記住每個月中有多少天或不需要操心閏年2月的辦法

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

# 數值處理函式

數值處理函式
函式名稱	函式功能
Abs()	回傳一個數的絕對值
Cos()	回傳一個角度的余弦
Exp()	回傳一個數的指數值
Mod()	回傳除操作的余數
Pi()	回傳圓周率
Rand()	回傳一個亂數
Sin()	回傳一個角度的正弦
Sqrt()	回傳一個數的平方根
Tan()	回傳一個角度的正切

2.10 匯總資料

聚集函式
函式名稱	函式功能
AVG()	回傳某列的平均值
COUNT()	回傳某列的行數
MAX()	回傳某列的最大值
MIN()	回傳某列的最小值
SUM()	回傳某列值之和

# AVG()函式

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

# COUNT()函式

SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;

# MAX()函式

SELECT MAX(prod_price) AS max_prod_price FROM products;

# MIN()函式

SELECT MIN(prod_price) AS min_price FROM products;

# SUM()函式

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

# 聚集不同值

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

# 組合聚集函式

SELECT COUNT(*) AS num_items, MIN(prod_price) AS min_price, MAX(prod_price) AS max_price, AVG(prod_price) AS avg_price FROM products;

2.11 分組資料

# 創建分組，GROUP BY子句指示MYSQL按vend_id排序并分組資料

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

# 過濾分組
# WHERE過濾行，而HAVING過濾分組

SELECT cust_id, COUNT(*) AS orders_num FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
SELECT cust_id, COUNT(*) AS orders_num FROM orders GROUP BY cust_id HAVING cust_id = 10001;
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;

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

# 不要忘記ORDER BY，一般在使用GROUP BY 子句時，應該也給出ORDER BY 子句，

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

2.12 使用子查詢

# 利用子查詢進行過濾
# 列出訂購物品TNT2的所有客戶

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

# 作為計算欄位使用
# 需要顯示customers表中每個客戶的訂單總數

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders_num FROM customers ORDER BY cust_name;

2.13 聯結表

外鍵（foreign key）外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關系，

為什么要使用聯結？

正如所述，分解資料為多個表能更有效地存盤，更方便地處理，并且具有更大的可伸縮性，但這些好處是有代價的，如果資料存盤在多個表中，怎樣用單條SELECT陳述句檢索出資料？答案是使用聯結，簡單地說，聯結是一種機制，用來在一條SELECT陳述句中關聯表，因此稱之為聯結，使用特殊的語法，可以聯結多個表回傳一組輸出，聯結在運行時關聯表中正確的行，

# 創建聯結

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

# 利用WHERE子句建立聯結，必須WHERE子句作為過濾條件
不要忘了WHERE子句應該保證所有聯結都有WHERE子句，否則MySQL將回傳比想要的資料多得多的資料，同理，應該保證WHERE子句的正確性，不正確的過濾條件將導致MySQL回傳不正確的資料，

# 內部聯結(與上述運行結果一致)

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

# 使用哪種語法 ANSI SQL規范首選INNER JOIN語法，此外，盡管使用WHERE子句定義聯結的確比較簡單，但是使用明確的聯結語法能夠確保不會忘記聯結條件，有時候這樣做也能影響性能，

# 性能考慮 MySQL在運行時關聯指定的每個表以處理聯結，這種處理可能是非常耗費資源的，因此應該仔細，不要聯結不必要的表，聯結的表越多，性能下降越厲害，

SELECT prod_name, vend_name, prod_price, quantity 
FROM 
products, orderitems, vendors 
WHERE products.prod_id = orderitems.prod_id AND vendors.vend_id = products.vend_id AND order_num = 20005;

# 查詢訂購產品TNT2的客戶串列

SELECT cust_name, cust_address 
FROM customers 
WHERE cust_id IN 
    (SELECT cust_id 
     FROM orders 
     WHERE order_num IN 
           (SELECT order_num   
            FROM orderitems 
            WHERE prod_id = 'TNT2'));

SELECT cust_name, cust_address 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND orderitems.prod_id = 'TNT2';

2.14 創建高級聯結

# 使用表別名

SELECT cust_name, cust_contact 
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE o.cust_id = c.cust_id AND o.order_num = oi.order_num AND oi.prod_id = 'TNT2';

# 自聯結

SELECT prod_id, prod_name 
FROM products 
WHERE vend_id = 
    (SELECT vend_id 
     FROM products 
     WHERE prod_id = 'DTNTR');

SELECT p1.prod_id, p1.prod_name 
FROM products AS p1, products AS p2 
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

用自聯結而不用子查詢自聯結通常作為外部陳述句用來替代從相同表中檢索資料時使用的子查詢陳述句，雖然最終的結果是相同的，但有時候處理聯結遠比處理子查詢快得多，應該試一下兩種方法，以確定哪一種的性能更好，

# 自然聯結

無論何時對表進行聯結，應該至少有一個列出現在不止一個表中（被聯結的列），標準的聯結（前一章中介紹的內部聯結）回傳所有資料，甚至相同的列多次出現，自然聯結排除多次出現，使每個列只回傳一次，怎樣完成這項作業呢？答案是，系統不完成這項作業，由你自己完成它，自然聯結是這樣一種聯結，其中你只能選擇那些唯一的列，這一般是通過對表使用通配符（SELECT *），對所有其他表的列使用明確的子集來完成的，下面舉一個例子：

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price 
FROM customers AS c, orders AS o, orderitems AS oi 
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND prod_id = 'FB';

# 外部聯結
# 如下是內部聯結

SELECT customers.cust_id, orders.order_num 
FROM customers INNER JOIN orders 
ON customers.cust_id = orders.cust_id;

# 如下是外部聯結

SELECT customers.cust_id, orders.order_num 
FROM customers LEFT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id;

在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表），

# 檢索所有客戶及每個客戶所下的訂單數

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS total_order_num 
FROM customers LEFT OUTER JOIN orders 
ON orders.cust_id = customers.cust_id GROUP BY customers.cust_id;

2.15 組合查詢

# 使用UNION，UNION的使用很簡單，所需做的只是給出每條SELECT陳述句，在各條陳述句之間放上關鍵字UNION，

# UNION規則，UNION必須由兩潭訓兩條以上的SELECT陳述句組成，陳述句之間用關鍵字UNION分隔（因此，如果組合4條SELECT陳述句，將要使用3個
UNION關鍵字）， UNION中的每個查詢必須包含相同的列、運算式或聚集函式（不過分析各個列不需要以相同的次序列出），列資料型別必須兼容：型別不必完全相同，但必須是DBMS可以隱含地轉換的型別（例如，不同的數值型別或不同的日期型別），如果遵守了這些基本規則或限制，則可以將并用于任何資料檢索任務，

# 包含或取消重復的行，如果想回傳所有匹配行，可使用UNION ALL而不是UNION，默認是取消重復行

# 對組合查詢結果排序，SELECT陳述句的輸出用ORDER BY子句排序，在用UNION組合查詢時，只能使用一條ORDER BY子句，它必須出現在最后一條SELECT陳述句之后，對于結果集，不存在用一種方式排序一部分，而又用另一種方式排序另一部分的情況，因此不允許使用多條ORDER BY子句，

SELECT vend_id, prod_id, prod_price 
FROM products WHERE prod_price <= 5 
UNION ALL 
SELECT vend_id, prod_id, prod_price 
FROM products 
WHERE vend_id IN (1001,1002) 
ORDER BY vend_id, prod_id;

2.16 全文本搜索

# 啟用全文本搜索支持
一般在創建表時啟用全文本搜索，CREATE TABLE陳述句接受FULLTEXT子句，它給出被索引列的一個逗號分隔的串列，

# 進行全文本搜索，在索引之后，使用兩個函式Match()和Against()執行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索運算式，

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

全文本搜索的一個重要部分就是對結果排序，具有較高等級的行先回傳（因為這些行很可能是你真正想要的行），

# 使用查詢擴展

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

# 布爾文本搜索

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

# 為了匹配包含heavy但不包含任意以rope開始的詞的行

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

布爾運算子說明
運算子名稱	功能說明
+	包含，詞必須存在
-	排除，詞必須不出現
>	包含，而且增加等級值
<	包含，且減少等級值
()	把詞組成子運算式（允許這些子運算式作為一個組被包含、排除、排列等）
~	取消一個詞的排序值
*	詞尾的通配符
""	定義一個短語（與單個詞的串列不一樣，它匹配整個短語以便包含或排除這個短語）

2.17 插入資料

# 插入完整行

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES
('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

# 插入多個行

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
VALUES
('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),  
('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

注意：提高 Insert 的性能此技術可以提高資料庫處理的性能，因為MySQL用單條 Insert陳述句處理多個插入比使用多條Insert陳述句快，

# 插入檢索出的資料
INSERT一般用來給表插入一個指定列值的行，但是，INSERT還存在另一種形式，可以利用它將一條SELECT陳述句的結果插入表中，這就是所謂的INSERT SELECT，顧名思義，它是由一條INSERT陳述句和一條SELECT陳述句組成的，

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT 
cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country 
FROM custNew;

2.18 更新和洗掉資料

# 更新資料

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

# 為了洗掉某列的值，可設定它為NULL（假如表定義允許NULL值）

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

# 洗掉資料

DELECT FROM customers WHERE cust_id = 10006;

# 洗掉表所有內容(第二條陳述句更快)，（TRUNCATE實際是洗掉原來的表并重新創建一個表，而不是逐行洗掉表中的資料），

DELECT FROM customers;
TRUNCATE TABLE customers;

下面是許多SQL程式員使用UPDATE或DELETE時所遵循的習慣，除非確實打算更新和洗掉每一行，否則絕對不要使用不帶WHERE 子句的UPDATE或DELETE陳述句，保證每個表都有主鍵，盡可能像WHERE子句那樣使用它（可以指定各主鍵、多個值或值的范圍），在對UPDATE或DELETE陳述句使用WHERE子句前，應該先用SELECT進行測驗，保證它過濾的是正確的記錄，以防撰寫的WHERE子句不正確，使用強制實施參考完整性的資料庫，這樣MySQL將不允許洗掉具有與其他表相關聯的資料的行，

2.19 創建和操縱表

# 創建表
為利用CREATE TABLE創建表，必須給出下列資訊：新表的名字，在關鍵字CREATE TABLE之后給出；表列的名字和定義，用逗號分隔，

CREATE TABLE IF NOT EXISTS customers_Test(
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name char(50)NOT NULL,
  cust_address char(50)NULL,
  cust_city char(50) NULL,
  cust_state char(5) NULL,
  cust_zip char(10)   NULL,
  cust_country char(50)NULL,
  cust_contact char(50)NULL,
  cust_email   char(255)  NULL,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

# 使用NULL值
NULL值就是沒有值或缺值，允許NULL值的列也允許在插入行時不給出該列的值，不允許NULL值的列不接受該列沒有值的行，換句話說，在插入或更新行時，該列必須有值，每個表列或者是NULL列，或者是NOT NULL列，這種狀態在創建時由表的定義規定，

CREATE TABLE IF NOT EXISTS orders_test (
  order_num intNOT NULL AUTO_INCREMENT,
  order_date datetimeNOT NULL,
  cust_id int NOT NULL,
  PRIMARY KEY (order_num)
) ENGINE = InnoDB;

# 主鍵再介紹
主鍵值必須唯一，即，表中的每個行必須具有唯一的主鍵值，如果主鍵使用單個列，則它的值必須唯一，如果使用多個列，則這些列的組合值必須唯一，迄今為止我們看到的CREATE TABLE例子都是用單個列作為主鍵，其中主鍵用以下的類似的陳述句定義：為創建由多個列組成的主鍵，應該以逗號分隔的串列給出各列名，

# 使用AUTO_INCREMENT
每個表只允許一個AUTO_INCREMENT列，而且它必須被索引（如，通過使它成為主鍵），

# 指定默認值

CREATE TABLE IF NOT EXISTS orderitems_test (
  order_num intNOT NULL,
  order_item int NOT NULL,
  prod_id char(10)NOT NULL,
  quantity int NOT NULL DEFAULT 1,
  item_price decimal(8,2)NOT NULL,
  PRIMARY KEY (order_num, order_item)
) ENGINE = InnoDB;

# 引擎型別，以下是幾個需要知道的引擎：【必須掌握】

InnoDB是一個可靠的事務處理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于資料存盤在記憶體（不是磁盤）中，速度很快（特別適合于臨時表）；
MyISAM是一個性能極高的引擎，它支持全文本搜索，但不支持事務處理，

# 更新表，增加列

ALTER TABLE vendors ADD vend_phone CHAR(20);

# 洗掉列

ALTER TABLE vendors DROP COLUMN vend_phone;

注意：小心使用ALTER TABLE 使用ALTER TABLE要極為小心，應該在進行改動前做一個完整的備份（模式和資料的備份），資料庫表的更改不能撤銷，如果增加了不需要的列，可能不能洗掉它們，類似地，如果洗掉了不應該洗掉的列，可能會丟失該列中的所有資料，

# 洗掉表

DROP TABLE orderitems_test;

# 重命名表

RENAME TABLE customers2 TO customers;

2.20 使用視圖

重要的是知道視圖僅僅是用來查看存盤在別處的資料的一種設施，視圖本身不包含資料，因此它們回傳的資料是從其他表中檢索出來的，在添加或更改這些表中的資料時，視圖將回傳改變過的資料，

性能問題因為視圖不包含資料，所以每次使用視圖時，都必須處理查詢執行時所需的任一個檢索，如果你用多個聯結和過濾創建了復雜的視圖或者嵌套了視圖，可能會發現性能下降得很厲害，因此，在部署使用了大量視圖的應用前，應該進行測驗，

CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orders.order_num  = orderitems.order_num;

SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

創建可重用的視圖創建不受特定資料限制的視圖是一種好辦法，例如，上面創建的視圖回傳生產所有產品的客戶而不僅僅是生產TNT2的客戶，擴展視圖的范圍不僅使得它能被重用，而且甚至更有用，這樣做不需要創建和維護多個類似視圖，

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

SELECT * FROM vendorlocations;

CREATE VIEW customers_email_list AS 
SELECT cust_name, cust_email 
FROM customers WHERE cust_email IS NOT NULL;

select * from customers_email_list;

CREATE VIEW orderitems_expand_price AS 
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;
SELECT * FROM orderitems_expand_price WHERE order_num = 20005;

2.21 使用存盤程序

存盤程序簡單來說，就是為以后的使用而保存的一潭訓多條MySQL陳述句的集合，可將其視為批檔案，雖然它們的作用不僅限于批處理，

# 創建存盤程序

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END; //
DELIMITER ;

# 使用存盤程序，存盤程序實際上是一種函式，所以存盤程序名后需要有()符號（即使不傳遞引數也需要），

CALL productpricing();

# 洗掉存盤程序

DROP PROCEDURE productpricing;
DROP PROCEDURE IF EXISTS productpricing;

# 使用引數

DELIMITER //
CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price)
    INTO pl
  FROM products;
  SELECT Max(prod_price)
    INTO ph
  FROM products;
  SELECT Avg(prod_price)
    INTO pa
  FROM products;
END; //
DELIMITER ;

此存盤程序接受3個引數：pl存盤產品最低價格，ph存盤產品最高價格，pa存盤產品平均價格，每個引數必須具有指定的型別，這里使用十進制值，關鍵字OUT指出相應的引數用來從存盤程序傳出一個值（回傳給呼叫者），MySQL支持IN（傳遞給存盤程序）、OUT（從存盤程序傳出，如這里所用）和INOUT（對存盤程序傳入和傳出）型別的引數，存盤程序的代碼位于BEGIN和END陳述句內，如前所見，它們是一系列SELECT陳述句，用來檢索值，然后保存到相應的變數（通過指定INTO關鍵字），

CALL productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @priceaverage;
SELECT @pricehigh, @pricelow, @priceaverage;

# 使用IN和OUT引數，ordertotal接受訂單號并回傳訂單的合計：

DELIMITER //
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN
  SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO    ototal;
END; //

DELIMITER ;
CALL ordertotal(20005, @total);
SELECT @total;

# 建立智能存盤程序
考慮這個場景，你需要獲得與以前一樣的訂單合計，但需要對合計增加營業稅，不過只針對某些顧客（或許是你所在州中那些顧客），那么，你需要做下面幾件事情：獲得合計（與以前一樣）；把營業稅有條件地添加到合計；回傳合計（帶或不帶稅），存盤程序的完整作業如下：

DELIMITER //
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  Declare total DECIMAL(8,2);
  Declare taxrate INT DEFAULT 6;

SELECT Sum(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;

IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;

SELECT total INTO ototal;
END; //
DELIMITER ;

CALL ordertotal(20005, 0, @total);
SELECT @total;

2.22 使用游標

游標（cursor）是一個存盤在MySQL服務器上的資料庫查詢，它不是一條SELECT陳述句，而是被該陳述句檢索出來的結果集，在存盤了游標之后，應用程式可以根據需要滾動或瀏覽其中的資料，
游標主要用于互動式應用，其中用戶需要滾動螢屏上的資料，并對資料進行瀏覽或做出更改，

三、高級部分

索引（Index）是幫助MySQL高效獲取資料的資料結構，底層是通過B+樹（多路平衡搜索樹）來實作；非葉子節點不存盤data，只存盤key，可以增大度；葉子節點通過指標可實作范圍查找

3.1 索引優化

1、建立索引：

索引最好設定在需要經常查詢的欄位中、保證Join陳述句中被驅動表上Join條件欄位已經被索引
對于單鍵索引，盡量選擇針對當前query過濾性更好的索引
在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好
在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where子句中更多欄位的索引
盡可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的

2、防止索引失效：

全值匹配我最愛，最左前綴要遵守；沒有遵守最左前綴匹配；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；索引列上使用了函式計算、型別轉換、范圍查找，索引失效
LIKE百分寫最右，覆寫索引不寫星；沒有使用覆寫索引，查詢了非索引欄位上的資料；字串未使用單引號；使用or; like ‘%xx' 索引失效
不等空值還有or，索引失效要少用；

3、Mysql事務如何實作

資料庫的事務是指一組sql陳述句組成的資料庫邏輯處理單元，在這組的sql操作中，要么全部執行成功，要么全部執行失敗，

文章最后，給大家推薦一些受歡迎的技術博客鏈接：

JAVA相關的深度技術博客鏈接
Flink 相關技術博客鏈接
Spark 核心技術鏈接
設計模式 —— 深度技術博客鏈接
機器學習 —— 深度技術博客鏈接
Hadoop相關技術博客鏈接
超全干貨--Flink思維導圖，花了3周左右撰寫、校對
深入JAVA 的JVM核心原理解決線上各種故障【附案例】
請談談你對volatile的理解？--最近小李子與面試官的一場“硬核較量”
聊聊RPC通信，經常被問到的一道面試題，原始碼+筆記，包懂
深入聊聊Java 垃圾回識訓制【附原理圖及調優方法】

歡迎掃描下方的二維碼或搜索公眾號“大資料高級架構師”，我們會有更多、且及時的資料推送給您，歡迎多多交流！

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/193769.html

標籤：其他

上一篇：opensips服務器環境搭建

下一篇：使用mvn deploy命令上傳jar包到nexus私服

MySQL 這些知識要搞懂才行【精簡總結】