MySQL必知必會詳細總結

不懶人發表於2020-12-10

MySql

一.檢索資料

1.檢索單個列：SELECT prod_name FROM products;

2.檢索多個列：SELECT prod_id，prod_name，prod_price FROM products;

3.檢索所有列：SELECT * FROM products;

4.檢索不同的行：SELECT DISTINCT vend_id FROM products;

5.限制結果：SELECT prod_nameFROM products LIMIT 5,5;

6.使用完全限定的表名：SELECT products.prod_name FROM products;

二.排序檢索資料

1.排序資料：SELECT prod_name FROM products ORDER BY prod_name;

2.按多個列排序：SELECT prod_id，prod_price，prod_name FROM products ORDER BY prod_price，prod_name;

3.指定排序方向

降序：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 ASC;

三.過濾資料

1.使用WHERE子句：SELECT prod_name，prod_price FROM products WHERE prod_price = 2.50;

2.WHERE子句操作符

SELECT prod_name，prod_price FROM products WHERE prod_price < 10;

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 FROM products WHERE prod_price IS NULL;

四.資料過濾

1.組合WHERE子句

（1）AND操作符：SELECT prod_id，prod_price，prod_name FROM products WHERE vend id = 1003 AND prod_price <= 10;

（2）OR操作符：SELECT prod_name，prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

（3）計算次序：例如SELECT prod_name，prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;先執行AND再執行OR

2.IN操作符：SELECT prod_name，prod_price FROM products WHERE vend_id IN (1002,1003)ORDER BY prod_name;

3.NOT操作符：SELECT prod_name，prod_price FROM products WHERE vend_id NOT IN (1002,1003)ORDER BY prod_name;

五.用萬用字元進行過濾

1.LIKE操作符

（1）.百分號（%）萬用字元：SELECT prod_id，prod_name FROM products WHERE prod_name LIKE 'jet%' ;

（2）.下劃線（_）萬用字元（匹配單個字元）：SELECT prod_id，prod_name FROM products WHERE prod_name LIKE '_ ton anvil ';

2.使用萬用字元的技巧

不要過度使用萬用字元。如果其他操作符能達到相同的目的，應該使用其他操作符。
在確實需要使用萬用字元時，除非絕對有必要，否則不要把它們用在搜尋模式的開始處。把萬用字元置於搜尋模式的開始處，搜尋起來是最慢的。
仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料。

六.用正規表示式進行搜尋

1.基本字元匹配（包含文字1000）：SELECT prod_name FROM products WHERE prod_name REGEXP '1000'ORDER BY prod_name;

2.進行OR匹配：SELECT prod_name FROM products WHERE prod_name REGEXP '1000| 2000' ORDER BY prod_name;

3.匹配幾個字元之一：SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

4.匹配範圍：SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5]Ton' ORDER BY prod_name;

5.匹配特殊字元：SELECT vend_name FROM vendors WHERE vend_name REGEXP '\ \.' ORDER BY vend_name;

6.匹配字元類：

7.匹配多個例項

例：SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\ \)' ORDER BY prod_name;

8.定位符

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

七.建立計算欄位

1.拼接欄位Concat（顯示vend_name(vend_country)格式）：SELECT Concat(vend_name,' ( '， vend_country，') ') FROM vendors ORDER BY vend_name;

2.執行算術計算：SELECT prod_id,quantity,item_price,quantity*item_price As expanded_price FROM orderitems WHERE order_num = 20005;

八.使用資料處理函式

1.文字處理函式

2.日期和時間處理函式

例如查詢order_date為2005-09-01的資料：SELECT cust_id，order_num FROM orders WHERE Date(order_date) = '2005-09-01';

3.數值處理函式

九.彙總資料

1.聚集函式

(1).AVG()函式：SELECT AVG(prod_price) AS avg _price FROM products;

(2).COUNT()函式：SELECT COUNT(*) AS num__cust FROM customers;

使用COUNT(*)對錶中行的數目進行計數,不管表列中包含的是空值(NULL）還是非空值。
使用COUNT ( column)對特定列中具有值的行進行計數，忽略NULL值。

(3).MAX函式：SELECT MAX(prod_price) AS max_price FROM products;

(4).MIN函式：SELECT MIN(prod_price) AS min_price FROM products;

(5).SUM函式：SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

2.聚集不同值DISTINCT（計算不同價格的平均數）：SELECT AVG(DISTINCT prod_price) As avg_price FROM products WHERE vend_id = 1003;

3.組合聚集函式：SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min，MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products;

十.分組資料

1.建立分組：SELECT vend_id，COUNT(*) AS num_prods FROM products GROUP BY vend_id;

2.過濾分組：SELECT cust_id，COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

3.分組和排序：SELECT order_num，SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*i tem_price) >= 50 ORDER BY ordertotal;

十一.使用子查詢

1.利用子查詢進行過濾

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

2.作為計算欄位使用子查詢：SELECT cust_name,cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

十二.聯結表

1.建立聯結：SELECT vend_name，prod_name，prod_price FROM vendors，products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name，prod_name;

十三.建立高階索引

1.外部聯結（LEFT OUTER J0IN）

SELECT customers.cust_id，orders.order_num FROM customers LEFT OUTER J0IN orders ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id，orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;

2.使用帶聚集函式的聯結

SELECT customers.cust_name, customers.cust_id， COUNT(orders.order_num) AS num_ord FROM customers INNER J0IN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

十四.組合查詢

1.使用UNION（去除重複行）

SELECT vend_id，prod_id，prod_price FROM products WHERE prod_price <= 5

UNION

SELECT vend_id，prod_id，prod_price FROM products WHERE vend_id IN (1001,1002);

2.使用UNION ALL（可以重複）

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);

3.對組合查詢結果排序：在用UNION組合查詢時，只能使用一條ORDER BY子句，它必須出現在最後一條SELECT語句之後。

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

十五.全文字搜尋

1.啟用全文字搜尋支援（FULLTEXT）

CREATE TABLE productnotes(
note_id int NOT NULL AUTO_INCREMENT ,

prod_id char(10) NOT NULL,

note_date datetime NOT NULL,

note_text text NULL ,

PRIMARY KEY(note_id)，
FULLTEXT(note_text))

ENGINE=MyISAM;

2.進行全文字搜尋（Match()指定被搜尋的列，Against()指定要使用的搜尋表示式）

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

3.使用查詢擴充套件

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

4.布林文字搜尋

匹配包含heavy：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);

全文字布林操作符：

十六.插入資料

1.插入完整的行：INSERT INTO customers (cust__name,cust_address,cust_city,cust_state,cust_zip,cust_country ,cust_contact,cust_emai1) VALUES( 'Pep E. LaPew ' ,'100 Main Street','Los Angeles','CA','90046','USA' ,NULL,NULL);

2.插入多個行

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

3.插入檢索出的資料

INSERT INTO customers(cust_id,cust_contact,cust__emai1,cust_name,cust_address,cust_city,cust_state,cust_zip,cust__country)

SELECT cust_id,cust_contact,cust_emai1,cust_name,cust_address,cust_city,cust_state,cust_zip，cust_countryFROM custnew;

十七.更新和刪除資料

1.更新資料：UPDATE customers SET cust_email = 'e1mer@fudd. com' WHERE cust_id = 10005;

2.刪除資料：DELETE FROM customersWHERE cust_id = 10006;

十八.建立和操作表

1.建立表基礎

CREATE TABLE customers(
　　cust_id int NOT NULL AUTO_INCREMENT,

　　cust_namechar(50)NOT NULL ,
　　cust_address char(50) NULL ,
　　cust_citychar(50) NULL ,cust_statechar(5) NULL ,cust_zip
　　char(10) NULL ,
　　cust_country char(50) NULL ,

　　cust_contact char(50) NULL ,

　　cust_emai1char(255) NULL ,

　　PRIMARY KEY (cust_id)

) ENGINE=InnoDB;

2.使用AUTO_INCREMENT：cust_id int NOT NULL AUTO_INCREMENT ,

3.新增一列：ALTER TABLE vendors ADD vend_phone CHAR(20);

4.刪除一列：ALTER TABLE VendorsDROP COLUMN vend_phone;

5.刪除表：DROP TABLE customers2 ;

6.重新命名錶：RENAME TABLE customers2 TO customers;

十八.使用檢視

理解檢視的最好方法是看一個例子：

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

此查詢用來檢索訂購了某個特定產品的客戶。任何需要這個資料的人都必須理解相關表的結構，並且知道如何建立查詢和對錶進行聯結。為了檢索其他產品（或多個產品）的相同資料，必須修改最後的WHERE子句。
現在，假如可以把整個查詢包裝成一個名為productcustomers的虛擬表，則可以如下輕鬆地檢索出相同的資料:

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

1.建立檢視

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

十九.使用儲存過程

儲存過程簡單來說，就是為以後的使用而儲存的一條或多條MySQL語句的集合。

1.執行儲存過程：CALL productpricing(@pricelow,@pricehigh,@priceaverage);

其中，執行名為productpricing的儲存過程，它計算並返回產品的最低、最高和平均價格。

2.建立儲存過程

CREATE PROCEDURE productpricing()

BEGIN
　　SELECT Avg(prod_price) AS priceaverage FROM products;
END;

我此儲存過程名為productpricing，用CREATE PROCEDURE productpricing( )語句定義。如果儲存過程接受引數，它們將在()中列舉出來。此儲存過程沒有引數，但後跟的()仍然需要。BEGIN和END語句用來限定儲存過程體，過程體本身僅是一個簡單的SELECT語句。

使用：CALL productpricing();

3.刪除儲存過程：DROP PROCEDURE productpricing;

4.使用引數

一般，儲存過程並不顯示結果，而是把結果返回給你指定的變數。

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;

此儲存過程接受3個引數:pl儲存產品最低價格，ph儲存產品最高價格，pa儲存產品平均價格。每個引數必須具有指定的型別，這裡使用十進位制值。關鍵字OUT指出相應的引數用來從儲存過程傳出一個值（返回給呼叫者)。MySQL支援IN（傳遞給儲存過程)、OUT（從儲存過程傳出，如這裡所用）和INOUT(對儲存過程傳入和傳出）型別的引數。儲存過程的程式碼位於BEGIN和END語句內，如前所見，它們是一系列SELECT語句，用來檢索值，然後儲存到相應的變數（通過指定INTO關鍵字)。

呼叫：CALL productpricing(@pricelow,@pricehigh,@priceaverage);

SELECT @pricehigh，@pricelow，@priceaverage;

5.建立智慧儲存過程

CREATE PROCEDURE ordertotal(
　　IN onumber INT,
　　IN taxable BOOLEAN,

　　OUT otota1 DECIMAL(8,2)
) COMMENT ‘0btain order total， optiona1ly adding tax'

BEGIN
　　-- Declare variable for total

　　DECLARE total DECIMAL(8,2);

　　-- Declare tax percentage
　　DECLARE taxrate INT DEFAULT 6;
　　-- Get the order total
　　SELECT Sum(item_price*quantity)

　　FROM orderitems
　　WHERE order_num = onumber

　　INTO total;
　　-- Is this taxab1e?

　　IF taxable THEN
　　　　-- Yes,so add taxrate to the total
　　SELECT total+(total/100*taxrate) INTO total;

　　END IF;

　　-- And final1y，save to out variable

　　SELECT total INTO ototal;
END;

此儲存過程有很大的變動。首先，增加了註釋（前面放置--)。在儲存過程複雜性增加時，這樣做特別重要。新增了另外一個引數taxable，它是一個布林值（如果要增加稅則為真，否則為假)。在儲存過程體中，用DECLARE語句定義了兩個區域性變數。DECLARE要求指定變數名和資料型別，它也支援可選的預設值（這個例子中的taxrate的預設被設定為6%)。SELECT語句已經改變，因此其結果儲存到total(區域性變數）而不是ototal。IF語句檢查taxable是否為真，如果為真，則用另一SELECT語句增加營業稅到區域性變數total。最後，用另一SELECT語句將total （它增加或許不增加營業稅）儲存到ototal。

呼叫：CALL ordertotal(20005，o，@total);

6.檢查儲存過程：SHOW CREATE PROCEDURE ordertotal;

十九.使用遊標

遊標(cursor)是一個儲存在MySQL伺服器上的資料庫查詢,它不是一條SELECT語句，而是被該語句檢索出來的結果集。在儲存了遊標之後，應用程式可以根據需要滾動或瀏覽其中的資料。

1.建立遊標

CREATE PROCEDURE processorders()

BEGIN
　　DECLARE ordernumbers CURSOR

　　FOR
　　SELECT order_num FROM orders;