MySQL必知必会之10-14_MySQL

10．创建计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在你SELECT语句内创建的。

字段（filed）：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

10.1拼接字段

拼接：将&＃20540;联结到一起构成单个&＃20540;。

Concat()函数：多数DBMS使用&＃43;或||实现拼接，而MYSQL使用Concat()函数实现。

Rtrim()函数：去掉&＃20540;右边的所有空&＃26684;

mysql> SELECT CONCAT(RTrim(vend_name),' (',RTrim(vend_country),')')FROM vendors

ORDER BY vend_name;

&＃43;-------------------------------------------------------&＃43;

| CONCAT(RTrim(vend_name),'(',RTrim(vend_country),')') |

&＃43;-------------------------------------------------------&＃43;

| ACME (USA) |

| Anvils R Us (USA) |

| Furball Inc. (USA) |

| Jet Set (England) |

| Jouets Et Ours (France) |

| LT Supplies (USA) |

&＃43;-------------------------------------------------------&＃43;

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;

&＃43;-------------------------&＃43;

| vend_title |

&＃43;-------------------------&＃43;

| ACME (USA) |

| Anvils R Us (USA) |

| Furball Inc. (USA) |

| Jet Set (England) |

| Jouets Et Ours (France) |

| LT Supplies (USA) |

&＃43;-------------------------&＃43;

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;

&＃43;---------&＃43;----------&＃43;------------&＃43;------------------&＃43;

| prod_id | quantity | item_price |ASexpanded_price |

&＃43;---------&＃43;----------&＃43;------------&＃43;------------------&＃43;

| ANV01 | 10 | 5.99 | 59.90 |

| ANV02 | 3 | 9.99 | 29.97 |

| TNT2 | 5 | 10.00 | 50.00 |

| FB | 1 | 10.00 | 10.00 |

&＃43;---------&＃43;----------&＃43;------------&＃43;------------------&＃43;

4 rows in set (0.03 sec)

11.使用数据处理函数

使用函数

大多数SQL实现支持以下类型的函数：

1） 用于处理文本串（如删除或填充&＃20540;，转换&＃20540;大小写）的文本函数

2） 用于在数&＃20540;数据上进行算术操作（如返回绝对&＃20540;，进行代数运算的数&＃20540;函数）

3） 用于出来日期和时间并从这些&＃20540;中提取特定成分（如返回日期之差）的日期和时间函数

4） 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数

11.1文本处理函数

Left():返回串左边的字符

Length()：返回串的长度

Locate():找出串的一个字串

Lower():将串转换为小写

LTrim():去掉左边的空&＃26684;

Right():返回串右边的字符

Rtrim()：去除列&＃20540;右边的空&＃26684;

Soundex():返回串的SOUNDEX&＃20540;，将任何文本串转换为描述其语音表示的字母数字模式算法

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。

&＃43;-------------&＃43;--------------&＃43;

| cust_name | cust_contact |

&＃43;-------------&＃43;--------------&＃43;

| Coyote Inc. | Y Lee |

&＃43;-------------&＃43;--------------&＃43;

1 row in set (0.00 sec)

11.2日期和时间处理函数

AddDate() 增加一个日期

AddTime() 增加一个时间

CurDate() 返回当前日期

Data() 返回日期时间的日期部分

DataDiff() 返回两个日期之差

Data_Add() 高度灵活的日期运算函数

Data_Format() 返回一个&＃26684;式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek () 对于一个日期，返回对应的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Mounth() 返回一个日期的月份部分

Now() 返回当前的日期和时间

Second() 返回一个时间秒部分

Time（） 返回一个时间的实践部分

Year() 返回一个日期的年份部分

MYSQL日期&＃26684;式为yyyy-mm-dd

mysql> SELECT cust_id,order_num FROMorders WHERE Date(order_date) = '2005-09-01

';//输出结果如下

&＃43;---------&＃43;-----------&＃43;

| cust_id | order_num |

&＃43;---------&＃43;-----------&＃43;

| 10001 | 20005 |

&＃43;---------&＃43;-----------&＃43;

1 row in set (0.01 sec)

11.3数&＃20540;处理函数

Abs() 返回一个数的绝对&＃20540;

Cos() 一个角度的余弦&＃20540;

Exp() 一个数的指数&＃20540;

Mod() 除操作数的余数

Pi() 返回圆周率

Rand() 返回一个随机数

Sin() 一个角度的正弦

Sqrt() 一个数的平方根

Tan() 一个角度的正切

12.汇总数据

12.1聚集函数

聚集函数：运行在行组山，计算和返回单个&＃20540;的函数

AVG() 某列的平均&＃20540;，忽略&＃20540;为NULL的行

COUNT() 某列的行数,忽略&＃20540;为NULL的行

MAX() 某列的最大&＃20540;

MIN() 某列的最小&＃20540;

SUM() 某列&＃20540;之和

还支持一些标准偏差聚集函数，这里不涉及。

SELECT AVG(PROD_PRICE) AS avg_price FROMproducts;

&＃43;-----------&＃43;

| avg_price |

&＃43;-----------&＃43;

| 16.133571 |

&＃43;-----------&＃43;

1 row in set (0.03 sec)

12.聚集不同的&＃20540;

SELECT AVG(DISTINCT prod_price) AS avg_priceFROM products WHERE vend_id = 1003;

&＃43;-----------&＃43;

| avg_price |

&＃43;-----------&＃43;

| 15.998000 |

&＃43;-----------&＃43;

1 row in set (0.03 sec)

可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价&＃26684;。

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;

&＃43;---------&＃43;-----------&＃43;

| vend_id | num_prods |

&＃43;---------&＃43;-----------&＃43;

| 1001 | 3 |

| 1002 | 2 |

| 1003 | 7 |

| 1005 | 2 |

&＃43;---------&＃43;-----------&＃43;

4 rows in set (0.05 sec)

GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果进行聚集。

使用GROUP BY的重要规则：

1） 可以包含任意数目的列，可以嵌套分组

2） 除聚集语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出

3） 如果分组中有NULL&＃20540;，则将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;

&＃43;---------&＃43;--------&＃43;

| cust_id | orders |

&＃43;---------&＃43;--------&＃43;

| 10001 | 2 |

&＃43;---------&＃43;--------&＃43;

1 row in set (0.00 sec)

这里的最后一行它过滤COUNT(*)>=2(两个以上的订单)的那些分组。

HAVING和WHERE的区别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

13.3分组和排序

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

&＃43;-----------&＃43;------------&＃43;

| order_num | ordertotal |

&＃43;-----------&＃43;------------&＃43;

| 20005 | 149.87 |

| 20006 | 55.00 |

| 20007 | 1000.00 |

| 20008 | 125.00 |

&＃43;-----------&＃43;------------&＃43;

4 rows in set (0.00 sec)

13.4 SELECT子句的顺序

SELECT->FROM->WHERE->GROUP BY->HAVING->ORDERBY->LIMIT

14 使用子查询

14.1利用子查询进行过滤

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROMorderitems WHERE prod

_id = 'TNT2'));

&＃43;----------------&＃43;--------------&＃43;

| cust_name |cust_contact |

&＃43;----------------&＃43;--------------&＃43;

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

&＃43;----------------&＃43;--------------&＃43;

2 rows in set (0.11 sec)

虽然子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

14.2作为计算字段使用子查询

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHEREorders.cust_id = customers.cust_id) AS orders FROM customers ORDER BYcust_name;

&＃43;----------------&＃43;------------&＃43;--------&＃43;

| cust_name | cust_state| orders |

&＃43;----------------&＃43;------------&＃43;--------&＃43;

| Coyote Inc. | MI | 2 |

| E Fudd | IL | 1 |

| Mouse House | OH | 0 |

| Wascals | IN | 1 |

| Yosemite Place | AZ | 1 |

&＃43;----------------&＃43;------------&＃43;--------&＃43;

5 rows in set (0.00 sec)

这条select语句对customers表中每个客户返回3列：cust_name,cust_state和orders.orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了5次，因为检索除了5个客户。

==参考MySQL必知必会