10.創建計算字段
計算字段並不實際存在於數據庫表中。計算字段是運行時在你SELECT語句內創建的。
字段(filed):基本上與列的意思相同,經常互換使用,不過數據庫列一般稱為列,而術語字段通常用在計算字段的連接上。
10.1拼接字段
拼接:將值聯結到一起構成單個值。
Concat()函數:多數DBMS使用+或||實現拼接,而MYSQL使用Concat()函數實現。
Rtrim()函數:去掉值右邊的所有空格
mysql> SELECT CONCAT(RTrim(vend_name),' (',RTrim(vend_country),')')FROM vendors
ORDER BY vend_name;
+-------------------------------------------------------+
| CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') |
+-------------------------------------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------------------------------------+
6 rows in set (0.00 sec)
10.2使用別名
別名用AS關鍵字賦予。
mysql> SELECT CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_titl
e FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title |
+-------------------------+
| ACME (USA) |
| Anvils R Us (USA) |
| Furball Inc. (USA) |
| Jet Set (England) |
| Jouets Et Ours (France) |
| LT Supplies (USA) |
+-------------------------+
6 rows in set (0.00 sec)
輸出結果與以前相同,但是現在列名為vend_title。
10.3執行算術計算
SELECTprod_id,quantity,item_price,quantity*item_price ASexpanded_price FROMorderitems WHERE order_num = 20005;
+---------+----------+------------+------------------+
| prod_id | quantity | item_price |ASexpanded_price |
+---------+----------+------------+------------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+------------------+
4 rows in set (0.03 sec)
11.使用數據處理函數
使用函數
大多數SQL實現支持以下類型的函數:
1) 用於處理文本串(如刪除或填充值,轉換值大小寫)的文本函數
2) 用於在數值數據上進行算術操作(如返回絕對值,進行代數運算的數值函數)
3) 用於出來日期和時間並從這些值中提取特定成分(如返回日期之差)的日期和時間函數
4) 返回DBMS正使用的特殊信息(如返回用戶登錄信息)的系統函數
11.1文本處理函數
Left():返回串左邊的字符
Length():返回串的長度
Locate():找出串的一個字串
Lower():將串轉換為小寫
LTrim():去掉左邊的空格
Right():返回串右邊的字符
Rtrim():去除列值右邊的空格
Soundex():返回串的SOUNDEX值,將任何文本串轉換為描述其語音表示的字母數字模式算法
SubString():返回子串的字符
Upper():將文本轉換為大寫
SELECT vend_name,Upper(vend_name) ASvend_name_upcase FROM vendors;
SELECT cust_name,cust_contact FROM customersWHERE Soundex(cust_contact)= Soundex('Y Lie');//結果如下,輸出cust_contact發音和'Y Lie'一致的結果,這裡發音一致的是Y Lee。
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee |
+-------------+--------------+
1 row in set (0.00 sec)
11.2日期和時間處理函數
AddDate() 增加一個日期
AddTime() 增加一個時間
CurDate() 返回當前日期
Data() 返回日期時間的日期部分
DataDiff() 返回兩個日期之差
Data_Add() 高度靈活的日期運算函數
Data_Format() 返回一個格式化的日期或時間串
Day() 返回一個日期的天數部分
DayOfWeek () 對於一個日期,返回對應的星期幾
Hour() 返回一個時間的小時部分
Minute() 返回一個時間的分鐘部分
Mounth() 返回一個日期的月份部分
Now() 返回當前的日期和時間
Second() 返回一個時間秒部分
Time() 返回一個時間的實踐部分
Year() 返回一個日期的年份部分
MYSQL日期格式為yyyy-mm-dd
mysql> SELECT cust_id,order_num FROMorders WHERE Date(order_date) = '2005-09-01
';//輸出結果如下
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
+---------+-----------+
1 row in set (0.01 sec)
11.3數值處理函數
Abs() 返回一個數的絕對值
Cos() 一個角度的余弦值
Exp() 一個數的指數值
Mod() 除操作數的余數
Pi() 返回圓周率
Rand() 返回一個隨機數
Sin() 一個角度的正弦
Sqrt() 一個數的平方根
Tan() 一個角度的正切
12.匯總數據
12.1聚集函數
聚集函數:運行在行組山,計算和返回單個值的函數
AVG() 某列的平均值,忽略值為NULL的行
COUNT() 某列的行數,忽略值為NULL的行
MAX() 某列的最大值
MIN() 某列的最小值
SUM() 某列值之和
還支持一些標准偏差聚集函數,這裡不涉及。
SELECT AVG(PROD_PRICE) AS avg_price FROMproducts;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.03 sec)
12.聚集不同的值
SELECT AVG(DISTINCT prod_price) AS avg_priceFROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.03 sec)
可以看到,在使用了DISTINCT後,此例子中的avg_price比較高,因為有多個物品具有相同的較低價格。
12.3組合聚集函數:
SELECT AVG(prod_price) ASavg_price,MIN(prod_price) AS price_min FROM products WHERE vend_id = 1003;
13.分組數據
分組允許把數據分為多個邏輯組,以便能對每個組進行聚集計算。
13.1創建分組
mysql> SELECT vend_id,COUNT(*) ASnum_prods FROM products GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
4 rows in set (0.05 sec)
GROUP BY子句指示MySQL分組數據,然後對每個組而不是整個結果進行聚集。
使用GROUP BY的重要規則:
1) 可以包含任意數目的列,可以嵌套分組
2) 除聚集語句外,SELECT語句中的每個列都必須在GROUPBY子句中給出
3) 如果分組中有NULL值,則將NULL作為一個分組
4) GROUP BY子句必須出現在WHERE子句之後,ORDER BY子句之前
13.2過濾分組
HAVING支持所有WHERE操作符。
mysql> SELECT cust_id,COUNT(*) AS ordersFROM orders GROUP BY cust_id HAVING COU
NT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+
1 row in set (0.00 sec)
這裡的最後一行它過濾COUNT(*)>=2(兩個以上的訂單)的那些分組。
HAVING和WHERE的區別:WHERE在數據分組前進行過濾,HAVING在數據分組後進行過濾。
13.3分組和排序