MySQL必知必会读书笔记-3（函数的使用）

注意：函数在各个DBMS之间是不通用的

1--拼接字段

多数DBMS使用+或者||来实现拼接，而MySQL使用Concat()函数来实现

以“名字（地址）”这样的格式输出各个生产商

SELECT CONCAT(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name

 

2--去掉空格

SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')')
FROM vendors
ORDER BY vend_name

去掉数据左侧的空格用LTRIM，去掉两侧的空格用TRIM

 

3--执行算数计算

列出某个订单中每种物品的总价

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

 

 

4--文本处理函数

将数据改为大写形式输出

SELECT vend_name, UPPER(vend_name) AS upcase_vend_name
FROM vendors
ORDER BY vend_name

 

  

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

实现对两个串的发音比较而不是字母比较

检索出一个联系名为Y Lee的人（输入一个发音相似的名字）

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y Lie';

SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie')

 

5--日期时间处理函数

日期的格式必须是yyyy-mm-dd

 

检索日期

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
#不安全的写法：WHERE order_date = '2005-09-01';

安全的写法：仅仅只比较日期的话，使用Date函数，仅仅比较时间的话用Time函数。

 

检索2005年9月的所有订单

SELECT cust_id, order_num, order_date
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'

还有一种方法让你不用关心每个月有多少天

SELECT cust_id, order_num, order_date
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9

 

6--数值处理函数

7--聚集函数

AVG()

注意：AVG()函数忽略值为null的行

检索出1003号供应商供应的产品的平均价格

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

 

COUNT()

COUNT(*)计算表中的行数，无论包含的是否是空值

COUNT(column)计算某一列中的值的个数，忽略null值

计算顾客个数

SELECT COUNT(*) AS sum
FROM customers

计算有电子邮件的顾客个数

SELECT COUNT(cust_email) AS customer_has_email
FROM customers

 

MAX()

忽略值为null的行, 对于数据类型是字符串之类的数据，这个函数返回最后一行

SELECT MAX(prod_price) AS max_price
FROM products

MIN()

和MAX()函数刚好相反。

忽略值为null的行, 对于数据类型是字符串之类的数据，这个函数返回最前一行

 

SUM()

忽略值为null的行

查询某个订单中的所有物品的总数

SELECT SUM(quantity) as items_ordered
FROM orderitems
WHERE order_num = 20005;

查询某个订单的总金额

SELECT SUM(quantity*item_price) as total_price
FROM orderitems
WHERE order_num = 20005;

 

以上所有聚集函数默认指定参数为ALL，要想对不同的值进行运算，需要指定DISTINCT函数(MySQL5或者更高版本)

 

查询某个供应商的所有产品的平均价格（去除重复的价格）

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

 

组合聚集函数

计算products表中物品的数目，最低价格，最高价格，平均价格

SELECT COUNT(*) AS num, 
       MIN(prod_price) AS min_price,
       MAX(prod_price) AS max_price, 
       AVG(prod_price) AS avg_price
FROM products

count和sum的区别

count计算查询结果中有多少行

sum计算查询结果中某个字段的数值的总和，即把所有行的该字段的数值相加