Mysql必知必會練習

第三章 使用MySQL

# 顯示所有的資料庫
show databases ;

# 使用指定資料庫
use crashcourse;

# 檢視所有表
show tables ;

# 檢視錶的所有列資訊
show columns from customers;

# show columns from的快捷方式
describe customers;

# describe的縮寫
desc customers;

# 顯示mysql服務狀態資訊
show status ;

# 顯示授予使用者的安全許可權
show grants ;

# 顯示錯誤資訊
show errors ;

# 顯示警告資訊
show warnings ;

第四章 檢索資料

# 檢視錶的所有列資訊
DESCRIBE products;

# 從products表中查詢pro_name列的資訊
SELECT prod_name FROM products;

# 從products表中查詢pro_name，vend_id列的資訊
SELECT prod_id, vend_id FROM products;

# 查詢products表中的全部資訊
SELECT * FROM products;

# 從products表中查詢vend_id列後去重
SELECT DISTINCT vend_id FROM products;

#  從products表中查詢vend_id列後去重(會應用於所有的列, 而不是隻有第一列)
SELECT DISTINCT vend_id, prod_id FROM products;

# 從products表中查詢prod_name的前5行(如果沒有明確排序查詢結果，則返回的資料沒有特殊意義）
# 返回資料的順序可能是資料被新增的順序，也可能不是，只要返回相同數目的行，就是正常的
SELECT prod_name FROM products LIMIT 5;

# 從products表中查詢prod_name的6-10行（從第5行開始,然後再返回5行）
SELECT prod_name FROM products LIMIT 5, 5;

# 同時返回兩列的五行
SELECT prod_name, vend_id FROM products LIMIT 5, 5;

# 使用完全限定的名字(同時使用表名和列名)
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcourse.products;

第五章 排序檢索資料

# 對prod_name列按字母序排序（預設升序）
SELECT prod_name FROM products ORDER BY prod_name;
# 先按價格排序, 如果價格相同則按名稱排序
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 ;

# 先按價格降序,如果價格一樣,按名稱升序(升序是預設的)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

# 按照價格降序後選出第一個，也就是價格最貴的
SELECT prod_ID, prod_price, prod_name FROM products ORDER BY prod_price DESC LIMIT 1;

第六章 過濾資料

# 查詢價格為2.5的產品資訊
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price=2.5;

# 查詢名稱為FUSES的產品資訊
# 實際名稱是Fuses,但是MYSQL不區分大小寫
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_name='FUSES';

# 查詢產品id不是1003的產品資訊
SELECT vend_id, prod_price, prod_name FROM products WHERE prod_id <> 1003  ;

# 查詢產品id不是1003的產品資訊，按vend_id降序輸出
SELECT vend_id, prod_price, prod_name FROM products WHERE prod_id <> 1003 ORDER BY vend_id DESC;

第七章 資料過濾

# 查詢價格為5-10的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

# 查詢價格為5-10的產品資訊，按vend_id降序輸出
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ORDER BY vend_id DESC ;

# 查詢價格為空產品資訊 注意NULL就是為空，既不是空格符也不是0
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price IS NULL ;

# 從customers中查詢cust_email為空的人的編號
SELECT cust_id FROM customers WHERE cust_email IS NULL ;

# 查詢vend_id=1003且prod_price<=10的產品資訊
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

# 查詢vend_id = 1002 或者 vend_id = 1003的產品資訊
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

# ！！！本意是 查詢prod_price >= 5 且 vend_id = 1001 和 prod_price >= 5 且 vend_id = 1的產品資訊
# 但是返回結果出現了prod_price < 5 的產品,說明語句有問題
# 原因是AND的優先順序高，mysql理解為vend_id = 1003 且 prod_price >= 10 或者所有 vend_id = 1003的產品
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price >= 5 AND vend_id = 1001 OR vend_id=1003;

# 使用括號來限定優先順序
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id=1003) AND prod_price >= 10 ;
SELECT vend_id, prod_name, prod_price FROM products WHERE  prod_price >= 10 AND (vend_id = 1002 OR vend_id=1003)  ;

# IN 指定條件範圍 查詢vend_id的範圍在1002, 1003的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_price;

# 使用OR和上面用IN的效果一樣，但是IN更快
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_price;

# NOT 否定後面跟的條件 查詢vend_id的範圍不在1002, 1003的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_price DESC ;

第八章 用萬用字元過濾

# 萬用字元 % 表示任何字元出現任何次數(不區分大小寫，我們可以配置區分大小寫)
# 所有名字以jet開頭的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 'jet%';

# 同上
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 'JET%';

# 所有名字包含anvil的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '%anvil%';

# 所有名字以s開頭，e結尾的產品資訊
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 's%e';

# 下劃線 _ 匹配任意一個字元 查詢_ ton anvil格式的產品資訊 _ 可以是任意字元
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '% ton anvil';

第九章 用正規表示式搜尋

# 正則 REGEXP 告知後面跟的是正規表示式
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000';

# . 表示匹配任意一個字元
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '.000';

# like要求整個資料都要匹配，而REGEXP只需要部分匹配即可。
# 第一句只能匹配1000，第二句能匹配包含1000字串的文字
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '1000';
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000';

# 匹配名為JetPack .000的產品，BINARY表示區分大小寫
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP BINARY 'JetPack .000';

# | 表示正則中的 或者
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000|2000';

# [123]表示匹配 1 Ton 或者 2 Ton 或者 3 Ton
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[123] Ton';

# 錯誤，這麼些只能表示 匹配1 或者 2 或者 3 Ton
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1|2|3 Ton';

# ^ 否匹配
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[^123] Ton';
# 匹配範圍
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[1-5] Ton';

# 匹配.
# 特殊字元匹配，需要轉義，正則需要一個\,mysql需要一個\,所以是兩個\
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\.';

# 匹配-
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\-';

# ? 表示?的前一位字元出現0次或者1次
# \\( 匹配( [0-9]匹配0-9  STICKS?匹配STICK或者STICKS  \\)匹配)
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\([0-9] STICKS?\\)';

# 匹配字元類(預定義的字符集)
# 任意數字
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:digit:]]';
# {4}要求它前面的字元（任意數字）出現4次，所以[[:digit:]]{4}匹配連在一起的任意4位數字
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';

# 任意字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:alpha:]]';

# {9}要求它前面的字元（任意數字）出現9次，所以[[:alpha:]]{9}匹配連在一起的任意9位字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:alpha:]]{9}';

# 任意小寫字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:lower:]]';

# 任意大寫字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:lower:]]';

# ^文字的開始, $文字的結束 ^在集合[]中表示否定,否則用來表示字串的開始
# 使用定位符 匹配以數字或者.開關的所有產品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;