使用數據處理函數 函數 與其他大多數電腦語言一樣,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;