MYSQL笔记:创建计算字段，数据函数，聚集函数，分组数据,子查询

创建计算字段

计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。
字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

拼接字段

拼接（concatenate） 将值联结到一起构成单个值。
在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。

select Concat(name,'(',sortb_order,')') from tb_category order by name;

通过删除数据右侧多余的空格来整理数据，这可以使用MySQL的RTrim()函数来完成

select Concat(RTrim(name),'(',RTrim(sortb_order),')') from tb_category order by name;

使用别名

从前面的输出中可以看到，SELECT语句拼接地址字段工作得很好。但此新计算列的名字是什么呢？实际上它没有名字，它只是一个值
为了解决这个问题，SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予

select Concat(RTrim(name),'(',RTrim(sortb_order),')') AS category_title from tb_category order by name;

执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算

select name,quantiy,iten_price,quantiy*item_price AS expanded_price from tb_category order by name;

算术操作符有:+,-,*,/

使用数据处理函数

与其他大多数计算机语言一样，SQL支持利用函数来处理数据。函数一般是在数据上执行的，它给数据的转换和处理提供了方便。
在刚刚用来去掉串尾空格的RTrim()就是一个函数的例子
大多数SQL实现支持以下类型的函数。

• 用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
• 返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

文本处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。
日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
Locate()	找出串的一个子串
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

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

日期和时间处理函数

AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

数值处理函数

Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

汇总数据

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提
供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分
析和报表生成。这种类型的检索例子有以下几种。

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）。
• 获得表中行组的和。
• 找出表列（或所有行或某些特定的行）的最大值、最小值和平均值

聚集函数

AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG()函数

AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
使用AVG()返回products表中所有产品的平均价格：

selec AVG(prod_price) AS avg_price from products;

COUNT()函数

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空
  值（NULL）还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略
  NULL值。

MAX()函数

MAX()返回指定列中的最大值。

MIN()函数

MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与
MAX()一样，MIN()要求指定列名

SUM()函数

SUM()用来返回指定列值的和（总计）。
SUM()也可以用来合计计算值

selec SUM(prod_price*quantity) AS total_price from products;

聚集不同值

• 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认
  行为）
• 只包含不同的值，指定DISTINCT参数。

下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格

selec AVG(DISTINCT prod_price) AS avg_price from products;

如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
将DISTINCT用于MIN()和MAX() 虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。

组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数。但实际上SELECT语句可根据需要包含多个聚集函数。
聚集函数用来汇总数据。MySQL支持一系列聚集函数，可以用多种方法使用它们以返回所需的结果。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多

分组数据

数据分组

SQL聚集函数可用来汇总数据。这使我们能够对行进行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。

创建分组

分组是在SELECT语句的GROUPBY子句中建立的。

selec ven_id,COUNT(*) AS num_prods from products GROUP BY vend_id;

• GROUPBY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
• 如果在GROUPBY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
• GROUPBY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUPBY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUPBY子句必须出现在WHERE子句之后，ORDERBY子句之前。

使用ROLLUP使用WITHROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

selec ven_id,COUNT(*) AS num_prods from products GROUP BY vend_id WITH ROLLUP;

过滤分组

除了能用GROUPBY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
我们已经看到了WHERE子句的作用。但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
那么，不使用WHERE使用什么呢？MySQL为此目的提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

selec ven_id,COUNT(*) AS num_prods from products GROUP BY vend_id HAVING COUNT(*) >=2;

HAVING和WHERE的差别这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

分组和排序

虽然GROUPBY和ORDERBY经常完成相同的工作，但它们是非常不同的。

ORDERBY	GROUP
排序产生的输出 任意列都可以使用（甚至非选择的列也可以使用）	分组行。但输出可能不是分组的顺序 只可能使用选择列或表达式列，且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	行级过滤	仅在从表选择数据时使用
WHERE	从中检索数据的表	否
GROUPBY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDERBY	输出排序顺序	否
LIMIT	要检索的行数	否

使用子查询

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

利用子查询进行过滤

selec ven_id from products where order_num IN (select order_num FROM 	orderitems WHERE prod_id = '1';

在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
这里给出的代码有效并获得所需的结果。但是.使用子查询并不总是执行这种类型的数据检索的最有效的方法。

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。为了执行这个操作，遵循下面的步骤。
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目。
正如前两章所述，可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID，可仅对该客户的订单进行计数。

selec cust_name,cust_state,(SELECT COUNT(*)
FROM orders
WHERE order.cust_id = customers.cust_id) AS orders from customers
order by cust_name;