MySQL必知必會2_ZenDei技術網路在線

使用數據處理函數函數與其他大多數電腦語言一樣，SQL支持利用函數來處理數據。函數一般是在數據上執行的，他給數據的轉換和處理提供了方便，在前一章中用來去掉尾空格的RTrim()就是一個函數的例子文本處理函數輸入： SELECT vend_name,Upper(vend_name) AS ve ...

使用數據處理函數

函數

與其他大多數電腦語言一樣，SQL支持利用函數來處理數據。函數一般是在數據上執行的，他給數據的轉換和處理提供了方便，在前一章中用來去掉尾空格的RTrim()就是一個函數的例子

文本處理函數

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

分析： 將列 vend_name_upcase轉換為大寫

示例：

mysql> SELECT NAME,UPPER(NAME) AS name_upcase FROM account ORDER BY NAME;
+------+-------------+
| NAME | name_upcase |
+------+-------------+
| aaa  | AAA         |
| aest | AEST        |
| bbb  | BBB         |
| ccc  | CCC         |
| ccc  | CCC         |
| ddd  | DDD         |
| ddd  | DDD         |
| t57L | T57L        |
| test | TEST        |
| tsdf | TSDF        |
+------+-------------+

常用的文本處理函數

函數	說明
Left()	返回串左邊的字元
Length()	返回串的長度
Locate()	找出串的一個子串
Lower()	將串轉為小寫
LTrim()	去掉串左邊的空格
Right()	返回串右邊的字元
RTrim()	去掉串右邊的空格
Soundex()	返回串的SOUNDEX的值
SubString()	返回子串的字元
Upper()	將串轉換為大寫

日期和時間處理函數

日期和時間採用相應的數據類型和特殊的格式存儲，以便於能快速和有效的排序或過濾，並且節省物理存儲空間

常用日期和時間處理函數

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

彙總數據

聚集函數 運行在行組上，計算和返回單個值的函數

AVG函數

輸入： SELECT AVG(prod_price) AS avg_price FROM products;

分析： 此SELECT語句返回值avg_price，它包含products表中所有產品的平均價格

只用於單個列 AVG()只能用來確定特定數值的平均值，而且列名必須作為函數參數給出

NULL值 AVG()函數忽略列值的NULL的行

COUNT函數

輸入 SELECT COUNT(*) AS num_cust FROM customers;

分析利用COUNT對所有行計數，不管行中各列有什麼值。包含NULL值

輸入 SELECT COUNT(cust_email) AS num_cust FROM customers;

分析使用COUNT對cust_email列中有值的行進行計數，不包含NULL值

聚集不同的值

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

分析排除掉相同的prod_price的值

分組數據

創建分組

輸入 SELECT vend_id,COUNT(*) AS num_prods FROM proucts GROUP BY vend_id;

分析上面的SELECT語句指定了兩個列，vend_id包含產品供應商的ID，num_prods為計算欄位。GROUP BY 子句指示MySQL按vendid排序並分組數據。

過濾分組

輸入：

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

分析 HAVING子句，他過濾 count(*)>=2的那些分組

HAVING和WHERE的差別：HAVING用於分組後過濾，WHERE用於分組前過濾

分組和排序

輸入

 SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;

輸出

mysql>  SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

使用子查詢

利用子查詢進行過濾

輸入

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

輸出

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

分析

為了執行上述SELECT語句；MySQL實際上必須執行3條SELECT語句，最裡邊的子查詢返回訂單號列表，此列表用於其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表，此客戶ID列表用於最外層的WHERE子句，最外層的查詢確實返回所需的數據

連接表

外鍵

外鍵為某個表中的一列，他包含另一個表的主鍵值

創建聯結

輸入

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

分析

最大的差別是在於所指定的兩個列(prod_name和prod_price)在一個表中，二另一個列(vend_name)在另一個表中

現在來看FROM子句。與以前的SELECT語句不一樣，這條語句的FROM子句列出了兩個表，分別是vendors表和products表。他們就是這條SELECT語句聯結的兩個表的名字。這兩個表用WHERE子句正確聯結，WHER子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id

**內部聯結*

輸入

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

分析此語句中的SELECT域前面的SELECT語句相同，但FROM子句不同，這裡，兩個表之間的關係是FROM子句的組成部分，以INNER JOIN指定，在使用這種語法時，聯結條件用特定的ON子句而不是WHERE子句給出

創建高級聯結

使用表別名

輸入

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

自聯結

輸入

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 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 oi.order_num=o.order_num
AND prod_id='FB';

外部聯結

輸入

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

組合查詢

多數SQL查詢都只包含從一個或多個表中返回數據的單條SELECT語句。MySQL也允許執行多個查詢(多條SELECT語句)，並將結果作為單個查詢結果集返回

創建組合查詢

使用UNION

UNION的使用很簡單。所需做的只是給出每條SELECT語句，在各條語句之間加上關鍵字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)

分析 UNION指示MySQL執行兩條語句，並把輸出組合成單個查詢結果集

全文本搜索

兩個最常用的引擎為MyISAM和InnoDB,前者支持全文本搜索，後者不支持

使用全文本搜索，必須索引被搜索的列，而且要隨著數據的改變不斷的重新索引

在對錶列進行適當的設計後，MySQL會自動進行所有索引和重新索引

在索引之後，SELECT可與Match()和Against()一起使用以實際執行搜索

啟用全文本搜索支持

一般在創建時啟用全文本搜索，CREATE TABLE語句接收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;

分析這些列中有一個名為note_text的列，為了進行全文本搜索，MySQL根據子句FULLTEXT(note_text)的指示對他進行索引。這裡的FULLTEXT索引單個列，如果需要也可以指定多個列

在定義之後MySQL自動維護該索引，在增加、更新、或刪除行時，索引隨之自動更新

進行全文本搜索

在索引之後，使用兩個函數Match()和Against()執行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表達式

輸入

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

分析此SELECT語句檢索單個列note_text。由於WHERE子句，一個全文本搜索被執行。Match(note_text)指示MySQL針對指定的列進行搜索，Against('rabbit')指定詞rabbit作文搜索文本。

使用查詢擴展

查詢擴展用來設法放寬所返回的全文本搜索結果的範圍，考慮下麵的情況。你想找出所有提到anvils的註釋。只有一個註釋包含詞anvils，但你還想找出可能與你的搜索有關的其他行，即使他們不包含詞anvils

這也是擴展的一項任務，在使用查詢擴展時，MySQL對數據和索引進行兩遍掃描來完成搜索：

首先，進行一個基於全文本的搜索，找出與搜索條件匹配的所有行

其次，MySQL檢查這些匹配並選擇所有有用的詞

在其次，MySQL再次進行全文搜索，這次不僅使用原來的條件，而且還使用所有有用的詞

利用查詢擴展，能找出可能相關的結果，即使他們並不精確包含所查找的詞

使用查詢擴展

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

布爾文本搜索

MySQL支持全文本搜索的另一種形式，稱為布爾方式，即使沒有全文本搜索也可以使用，但這是一種非常緩慢的操作

輸入

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

分析此全文本搜索檢索包含詞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_contact,
                      cust_email,
                      cust_address,
                      cust_city,
                      cust_state

)VALUES('pEP e.lAPew',
        NULL,
        NULL,
        '100 Main Street',
        'los Angeles',
        'CA'
)

插入多個行

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 Your',
    'NY',
    '11213',
    'USA'
);

更新和刪除數據

輸入

UPDATE customers
SET cust_email='[email protected]'
WHERE cust_id=10005;

更新多個列

UPDATE customers
SET cust_name='The Fudds',
    cust_email='[email protected]'
WHERE cust_id=10005;

刪除數據

DELETE FROM customers 
WHERE cust_id = 10006;