3.《MySQL必知必会》函数与汇总

注：博客中总结《MySQL必知必会》中的知识点,第10,11,12章的核心内容；

涉及到的操作符： Concat，AS，数值函数，串函数，聚集函数；

书中用到的表的介绍及其脚本文件：《Mysql必知必会》中表的介绍

创建计算字段

1.计算字段

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

2.拼接字段

主要使用了三个函数concat，Trim，RTrim，LTrim

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

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

3.使用别名

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

4.执行算术运算

SELECT prod_id,quantity,item_price,
quantity*item_price AS extanded_price
FROM oderitems
WHERE order_num=20005;

使用数据处理函数

1.文本处理函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM venors
ORDER BY vend_name;
-- 常用文本函数，Left(),Length(),Lower(),Soundex,Substring(),...

2.日期和时间处理函数

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';
-- 常用日期和时间处理函数，Date(),Day(),Year(),Month(),AddDate(),AddTime(),,...

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

3.数值处理函数

-- 常用数值处理函数
-- Abs(),Exp(),Sqrt(),Mod(),
-- Pi(),Rand(),Cos(),Tan(),....

汇总数据

1.聚集函数

-- 聚集函数包括：AVG(),COUNT(),MAX(),MIN(),SUM()

SELECT AVG(prod_price) AS avg_price
FROM products;

SELECT COUNT(*) AS num_cust
FROM customers;

SELECT MAX(prod_price) AS max_price
FROM products;

SELECT MIN(prod_price) AS min_price
FROM products;

SELECT SUM(quntity) AS item_ordered
FROM ordeitems
WHERE order_num=20005;

2.聚集不同值

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

3.组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;