MySQL必知必会-9MySQL常用函数及数学计算

拼接字段

使用concat()函数来拼接多个列

   
   
SELECT
	Concat(
		vend_name,
		'(',
		vend_country,
		')',
		vend_address
	)
FROM
	vendors
ORDER BY
	vend_name;

删除右侧多余的空格来整理数据，使用RTrim()函数

   
   
SELECT
	Concat(
		RTrim(vend_name),
		'(',
		RTrim(vend_country),
		')'
	)
FROM
	vendors
ORDER BY
	vend_name;

支持去掉右侧空格的RTrim()

和

去掉左侧空格的LTrim()

以及Trim()去掉左右两边的空格

使用别名

别名是一个字段或值的替换名，别名用AS关键字赋予。

   
   
SELECT
	Concat(
		RTrim(vend_name),
		'(',
		RTrim(vend_country),
		')'
	) AS vend_title
FROM
	vendors
ORDER BY
	vend_name;

执行算术计算

MySQL提供的算术操作符，加减乘除

+

-

*

/

   
   
SELECT
	prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price
FROM
	orderitems
WHERE
	order_num = 20005;

如何测试计算

SELECT可以省略FROM子句以便简单地访问和处理表达式。例如：

SELECT 3 * 2; 将返回6

SELECT Trim('abc');将返回abc，而SELECT Now()，利用Now()函数返回当前日期和时间。

可以通过SELECT进行试验。

使用数据处理函数

Upper()函数将文本转换成大写。

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

常用的文本处理函数

  SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似发音字符和音节，使得能对串进行发音比较而不是字母比较。

使用Soundex()函数匹配类似的发音

（MySQL官方原话：This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.

This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8.

We hope to remove these limitations in a future release. See Bug #22638 for more information.

大致意思是：仅对英文有支持，其他语言可能会不可靠，且不能保证对多字节字符集字符串（包括UTF08）提供一致的结果。会在未来取消掉这些限制，详细看Bug #22638。

经过本人测试，中文的确不可靠，目前来看就是鸡肋，以后可能会支持 ）

   
   
SELECT
	cust_name,
	cust_contact
FROM
	customers
WHERE
	Soundex(CUST_CONTACT) = Soundex('Y Lie');

这个例子中，因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配。

日期处理函数

不管插入或更新表还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd，因此2017年2月5日，给出为2017-02-05，虽然其他格式也可以，但这是首选的格式。

   
   
SELECT
	cust_id,
	order_num
FROM
	orders
WHERE
	order_date = '2005-09-01';

但更为可靠的方法使用DATE函数，以免日后修改order_date为DATETIME类型(日期和时间) 。

   
   
SELECT
	cust_id,
	order_num
FROM
	orders
WHERE
	DATE(order_date) = '2005-09-01';

检索2005年9月的所有订单

   
   
SELECT
	cust_id,
	order_num
FROM
	orders
WHERE
	YEAR (order_date) = 2005
AND MONTH (order_date) = 9;

常用日期和时间处理函数

 

数值处理函数

 

汇总数据

聚集函数

MySQL提供了5个聚集函数

AVG()  返回某列的平均值

COUNT()  返回某列的行数

MAX()   返回某列的最大值

MIN()  返回某列的最小值

SUM()  返回某列值值之和

AVG()只能确定数列的平均值，而且列名必须作为函数参数给出，但

AVG()函数忽略值为NULL的行

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

COUNT()函数

COUNT()函数有两种使用方式。

使用COUNT(*)对表中的行数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

使用COUNT(COLUMN)对特定列中具有值得行进行计数，忽略NULL值。

   
   
SELECT
	COUNT(*) AS num_cust
FROM
	customers;

    
    
SELECT
	COUNT(cust_email) AS num_cust
FROM
	customers;

这两者还是有区别的，因为部分cust_email为NULL，所以两者的总数不一样。

利用标准算术符，所有聚集函数都可以用来执行多个列上的计算。

   
   
SELECT
	COUNT(*),
	AVG(quantity),
	MAX(item_price),
	MIN(item_price),
	SUM(quantity * item_price)
FROM
	orderitems;

聚集不同值

对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）

只包含不同的值，指定DISTINCT参数

ALL参数不需要指定，因为它是默认行为，如果不指定DISTINCT，则假定为ALL

使用AVG()函数返回特定供应商提供的产品的平均价格。

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

DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT)

聚集函数是高效设计的，如果可以最好使用，返回结果一般比在自己的客户机应用程序中要快得多。