MySQL必知必会之10-14_MySQL

bitsCN.com

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分组和排序

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

+-----------+------------+

| order_num | ordertotal |

+-----------+------------+

| 20005 | 149.87 |

| 20006 | 55.00 |

| 20007 | 1000.00 |

| 20008 | 125.00 |

+-----------+------------+

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

+----------------+--------------+

| cust_name |cust_contact |

+----------------+--------------+

| Coyote Inc. | Y Lee |

| Yosemite Place | Y Sam |

+----------------+--------------+

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;

+----------------+------------+--------+

| cust_name | cust_state| orders |

+----------------+------------+--------+

| Coyote Inc. | MI | 2 |

| E Fudd | IL | 1 |

| Mouse House | OH | 0 |

| Wascals | IN | 1 |

| Yosemite Place | AZ | 1 |

+----------------+------------+--------+

5 rows in set (0.00 sec)

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

==参考MySQL必知必会

bitsCN.com