主頁 >  其他 > MySQL 這些知識要搞懂才行【精簡總結】

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

2020-10-26 21:46:18 其他

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

一、了解資料庫和表

# 回傳可用資料表串列

USE testdb;

# 使用資料庫testdb

USE testdb;

# 顯示資料庫中表

SHOW TABLES;

# 顯示customers表的資訊

SHOW COLUMNS FROM customers;

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

DESCRIBE customers;

二、檢索資料

# 檢索單個列

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;

三、排序檢索資料

# 對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;

四、過濾資料

# 使用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;

五、資料過濾

# 組合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;

六、用通配符進行過濾

# 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';

# 通配符使用注意點：

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

七、用正則運算式進行搜索

# 基本字符匹配

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;

八、創建計算欄位

# 拼接欄位，輸出效果：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;

九、使用資料處理函式

# 函式的移植性不是很強，如果你決定使用函式，應該保證做好代碼注釋，以便以后你（或其他人）能確切地知道所編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()回傳一個角度的正切

十、匯總資料

聚集函式
函式名稱函式功能
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;

十一、分組資料

# 創建分組，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 要檢索的行數否

十二、使用子查詢

# 利用子查詢進行過濾
# 列出訂購物品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;

十三、聯結表

外鍵（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';

十四、創建高級聯結

# 使用表別名

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;

十五、組合查詢

# 使用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;

十六、全文本搜索

# 啟用全文本搜索支持
一般在創建表時啟用全文本搜索，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);

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

十七、插入資料

# 插入完整行

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;

十八、更新和洗掉資料

# 更新資料

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將不允許洗掉具有與其他表相關聯的資料的行，

十九、創建和操縱表

# 創建表
為利用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;

二十、使用視圖

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

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

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;

二十二.使用存盤程序

存盤程序簡單來說，就是為以后的使用而保存的一潭訓多條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;

二十三、使用游標

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

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

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

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

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

標籤：其他

上一篇：MySQL炒雞詳細常用語法陳述句

下一篇：sql 中如何將千分號數值，轉化成decimal型，或int型計算。如何對列值進行處理，如果是中文就是0，如果是數值就是數值

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • 網閘典型架構簡述

    網閘架構一般分為兩種：三主機的三系統架構網閘和雙主機的2+1架構網閘。 三主機架構分別為內端機、外端機和仲裁機。三機無論從軟體和硬體上均各自獨立。首先從硬體上來看，三機都用各自獨立的主板、記憶體及存盤設備。從軟體上來看，三機有各自獨立的作業系統。這樣能達到完全的三機獨立。對于“2+1”系統，“2”分為 ......

    uj5u.com 2020-09-10 02:00:44 more
  • 如何從xshell上傳檔案到centos linux虛擬機里

    如何從xshell上傳檔案到centos linux虛擬機里及：虛擬機CentOs下執行 yum -y install lrzsz命令，出現錯誤：鏡像無法找到軟體包 前言 一、安裝lrzsz步驟 二、上傳檔案 三、遇到的問題及解決方案 總結 前言 提示：其實很簡單，往虛擬機上安裝一個上傳檔案的工具 ......

    uj5u.com 2020-09-10 02:00:47 more
  • 一、SQLMAP入門

    一、SQLMAP入門 1、判斷是否存在注入 sqlmap.py -u 網址/id=1 id=1不可缺少。當注入點后面的引數大于兩個時。需要加雙引號， sqlmap.py -u "網址/id=1&uid=1" 2、判斷文本中的請求是否存在注入 從文本中加載http請求，SQLMAP可以從一個文本檔案中 ......

    uj5u.com 2020-09-10 02:00:50 more
  • Metasploit 簡單使用教程

    metasploit 簡單使用教程 浩先生， 2020-08-28 16:18:25 分類專欄： kail 網路安全 linux 文章標簽： linux資訊安全 編輯 著作權 metasploit 使用教程 前言 一、Metasploit是什么？ 二、準備作業 三、具體步驟 前言 Msfconsole ......

    uj5u.com 2020-09-10 02:00:53 more
  • 游戲逆向之驅動層與用戶層通訊

    驅動層代碼： #pragma once #include <ntifs.h> #define add_code CTL_CODE(FILE_DEVICE_UNKNOWN,0x800,METHOD_BUFFERED,FILE_ANY_ACCESS) /* 更多游戲逆向視頻www.yxfzedu.com ......

    uj5u.com 2020-09-10 02:00:56 more
  • 北斗電力時鐘（北斗授時服務器）讓網路資料更精準

    北斗電力時鐘（北斗授時服務器）讓網路資料更精準 北斗電力時鐘（北斗授時服務器）讓網路資料更精準 京準電子科技官微——ahjzsz 近幾年，資訊技術的得了快速發展，互聯網在逐漸普及，其在人們生活和生產中都得到了廣泛應用，并且取得了不錯的應用效果。計算機網路資訊在電力系統中的應用，一方面使電力系統的運行 ......

    uj5u.com 2020-09-10 02:01:03 more
  • 【CTF】CTFHub 技能樹 彩蛋 writeup

    ?碎碎念 CTFHub：https://www.ctfhub.com/ 筆者入門CTF時時剛開始刷的是bugku的舊平臺，后來才有了CTFHub。 感覺不論是網頁UI設計，還是題目質量，賽事跟蹤，工具軟體都做得很不錯。 而且因為獨到的金幣制度的確讓人有一種想去刷題賺金幣的感覺。 個人還是非常喜歡這個 ......

    uj5u.com 2020-09-10 02:04:05 more
  • 02windows基礎操作

    我學到了一下幾點 Windows系統目錄結構與滲透的作用 常見Windows的服務詳解 Windows埠詳解 常用的Windows注冊表詳解 hacker DOS命令詳解（net user / type /md /rd/ dir /cd /net use copy、批處理 等） 利用dos命令制作 ......

    uj5u.com 2020-09-10 02:04:18 more
  • 03.Linux基礎操作

    我學到了以下幾點 01Linux系統介紹02系統安裝，密碼啊破解03Linux常用命令04LAMP 01LINUX windows： win03 8 12 16 19 配置不繁瑣 Linux：redhat,centos(紅帽社區版)，Ubuntu server,suse unix:金融機構，證券，銀 ......

    uj5u.com 2020-09-10 02:04:30 more
  • 05HTML

    01HTML介紹 02頭部標簽講解03基礎標簽講解04表單標簽講解 HTML前段語言 js1.了解代碼2.根據代碼 懂得挖掘漏洞 （POST注入/XSS漏洞上傳）3.黑帽seo 白帽seo 客戶網站被黑帽植入劫持代碼如何處理4.熟悉html表單 <html><head><title>TDK標題，描述 ......

    uj5u.com 2020-09-10 02:04:36 more
最新发布
  • 2023年最新微信小程式抓包教程

    01 開門見山 隔一個月發一篇文章，不過分。 首先回顧一下《微信系結手機號資料庫被脫庫事件》，我也是第一時間得知了這個訊息，然后跟蹤了整件事情的經過。下面是這起事件的相關截圖以及近日流出的一萬條資料樣本： 個人認為這件事也沒什么，還不如關注一下之前45億快遞資料查詢渠道疑似在近日復活的訊息。 訊息是 ......

    uj5u.com 2023-04-20 08:48:24 more
  • web3 產品介紹：metamask 錢包 使用最多的瀏覽器插件錢包

    Metamask錢包是一種基于區塊鏈技術的數字貨幣錢包，它允許用戶在安全、便捷的環境下管理自己的加密資產。Metamask錢包是以太坊生態系統中最流行的錢包之一，它具有易于使用、安全性高和功能強大等優點。 本文將詳細介紹Metamask錢包的功能和使用方法。 一、 Metamask錢包的功能 數字資 ......

    uj5u.com 2023-04-20 08:47:46 more
  • vulnhub_Earth

    前言 靶機地址->>>vulnhub_Earth 攻擊機ip：192.168.20.121 靶機ip：192.168.20.122 參考文章 https://www.cnblogs.com/Jing-X/archive/2022/04/03/16097695.html https://www.cnb ......

    uj5u.com 2023-04-20 07:46:20 more
  • 從4k到42k，軟體測驗工程師的漲薪史，給我看哭了

    清明節一過，盲猜大家已經無心上班，在數著日子準備過五一，但一想到銀行卡里的余額……瞬間心情就不美麗了。最近，2023年高校畢業生就業調查顯示，本科畢業月平均起薪為5825元。調查一出，便有很多同學表示自己又被平均了。看著這一資料，不免讓人想到前不久中國青年報的一項調查：近六成大學生認為畢業10年內會 ......

    uj5u.com 2023-04-20 07:44:00 more
  • 最新版本 Stable Diffusion 開源 AI 繪畫工具之中文自動提詞篇

    🎈 標簽生成器 由于輸入正向提示詞 prompt 和反向提示詞 negative prompt 都是使用英文，所以對學習母語的我們非常不友好 使用網址：https://tinygeeker.github.io/p/ai-prompt-generator 這個網址是為了讓大家在使用 AI 繪畫的時候 ......

    uj5u.com 2023-04-20 07:43:36 more
  • 漫談前端自動化測驗演進之路及測驗工具分析

    隨著前端技術的不斷發展和應用程式的日益復雜，前端自動化測驗也在不斷演進。隨著 Web 應用程式變得越來越復雜，自動化測驗的需求也越來越高。如今，自動化測驗已經成為 Web 應用程式開發程序中不可或缺的一部分，它們可以幫助開發人員更快地發現和修復錯誤，提高應用程式的性能和可靠性。 ......

    uj5u.com 2023-04-20 07:43:16 more
  • CANN開發實踐：4個DVPP記憶體問題的典型案例解讀

    摘要：由于DVPP媒體資料處理功能對存放輸入、輸出資料的記憶體有更高的要求（例如，記憶體首地址128位元組對齊），因此需呼叫專用的記憶體申請介面，那么本期就分享幾個關于DVPP記憶體問題的典型案例，并給出原因分析及解決方法。 本文分享自華為云社區《FAQ_DVPP記憶體問題案例》，作者：昇騰CANN。 DVPP ......

    uj5u.com 2023-04-20 07:43:03 more
  • msf學習

    msf學習 以kali自帶的msf為例 一、msf核心模塊與功能 msf模塊都放在/usr/share/metasploit-framework/modules目錄下 1、auxiliary 輔助模塊，輔助滲透（埠掃描、登錄密碼爆破、漏洞驗證等） 2、encoders 編碼器模塊，主要包含各種編碼 ......

    uj5u.com 2023-04-20 07:42:59 more
  • Halcon軟體安裝與界面簡介

    1. 下載Halcon17版本到到本地 2. 雙擊安裝包后 3. 步驟如下 1.2 Halcon軟體安裝 界面分為四大塊 1. Halcon的五個助手 1) 影像采集助手：與相機連接，設定相機引數，采集影像 2) 標定助手：九點標定或是其它的標定，生成標定檔案及內參外參，可以將像素單位轉換為長度單位 ......

    uj5u.com 2023-04-20 07:42:17 more
  • 在MacOS下使用Unity3D開發游戲

    第一次發博客，先發一下我的游戲開發環境吧。 去年2月份買了一臺MacBookPro2021 M1pro(以下簡稱mbp)，這一年來一直在用mbp開發游戲。我大致分享一下我的開發工具以及使用體驗。 1、Unity 官網鏈接： https://unity.cn/releases 我一般使用的Apple ......

    uj5u.com 2023-04-20 07:40:19 more

Copyright © 2010-2020 有解無憂