mysql基础(4)--计算字段、Concat()字段拼接、数据处理函数

应用场景

• 如果想在一个字段中既显示公司名,又显示公司的地址,但这两个信息一般包含在不同的表列中。
• 列数据是大小写混合的,但报表程序需要把所有数据按大写表示出来
• 物品订单表存储物品的价格和数量,但不需要存储每个物品的总价格(用价格乘以数量即可)。显示结果时我们需要物品的总价格
• 需要根据表数据进行总数、平均数计算或其他计算。

注解
SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说,在数据库服务器上完成这些操作比在客户机中完
成要快得多,因为DBMS(数据库管理工具)是设计来快速有效地完成这种处理的

1. 拼接字段(Concat()函数)

如果我们存在数据库中的内容是这样的

first_name	last_name
张	三
李	四

我们想要数据库中展示给我们完整的名字,而不是拿到 姓 和 名 自己去拼接,可以这么操作
SELECT Concat(first_name, last_name) FROM people;

Concat(first_name, last_name)
张三
李四

分析

• Concat() 拼接串,即把多个串连接起来形成一个较长的串
• Concat() 需要一个或多个指定的串,各个串之间用逗号分隔。

2. 使用别名(AS)

从前面的输出中可以看到, SELECT 语句拼接地址字段工作得很好。但此新计算列的名字是什么呢?实际上它没有名字,
它只是一个值。如果仅在SQL查询工具中查看一下结果,这样没有什么不好。但是,一个未命名的列不能用于客户机应用中,因为客户机没有办法引用它
这时候AS关键词就能体现作用了

SELECT Concat(first_name, last_name) AS name FROM people;

分析
SELECT 语句本身与以前使用的相同,只不过这里的语句中计算字段之后跟了文本 AS name 。它指示SQL创建一个包含指定计算的名为 name 的计算字段。从输出中可以看到,结果与以前的相同,但现在列名为 name ,任何客户机应用都可以按名引用这个列,就像它是一个实际的表列一样。

AS的其他用途

• 在实际的表列名包含不符合规定的字符(如空格)时重新命名它
• 在原来的名字含混或容易误解时扩充它

3. 执行算数运算
   mysql中支持加减乘除运算,还可以用小括号来区分优先顺序

例:orders 表包含收到的所有订单, orderitems 表包含每个订单中的各项物品。下面的SQL语句检索订单号 20005 中的所有物品:

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

prod_id	quantity	item_price
ANV01	10	5.99
ANV02	3	9.99
TNT2	5	10.00

item_price 列包含订单中每项物品的单价。如下汇总物品的价格(单价乘以订购数量):

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

prod_id	quantity	item_price	expanded_price
ANV01	10	5.99	59.90
ANV02	3	9.99	29.97
TNT2	5	10.00	50.00

测试计算
SELECT 提供了测试和试验函数与计算的一个很好的办法。虽然 SELECT 通常用来从表中检索数据,但可以
省略 FROM 子句以便简单地访问和处理表达式。例如, SELECT3*2; 将返回 6 , SELECT Trim(‘abc’); 将返回 abc ,而 SELECT Now() 利用 Now() 函数返回当前日期和时间

4. 函数

SQL支持利用函数来处理数据。函数一般是在数据上执行的,它给数据的转换和处理提供了方便
注意sql函数的移植性较差,有的sql函数可能在mysql中可以执行,但是在其他的DBMS就不能执行,所以一定要做好注释工作
大多数SQL实现支持以下类型的函数。

1. 常见的文本处理函数

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

说明
SOUNDEX 需要做进一步的解释。 SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似
的发音字符和音节,使得能对串进行发音比较而不是字母比较。虽然SOUNDEX 不是SQL概念,但MySQL(就像多数DBMS一样)都提供对SOUNDEX 的支持。
下面给出一个使用 Soundex() 函数的例子。 customers 表中有一个顾客 Coyote Inc. ,其联系名为 Y.Lee 。但如果这是输入错误,此联系名实际应该是 Y.Lie ,怎么办?显然,按正确的联系名搜索不会返回数据,如下所示:

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

这样搜索出的结果其实是空的

现在试一下使用 Soundex() 函数进行搜索,它匹配所有发音类似于Y.Lie 的联系名:
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex(‘Y.Lie’);
这时 Y Lee 就匹配出来了
分析:
WHERE 子句使用 Soundex() 函数来转换 cust_contact 列 值 和 搜 索 串 为 它 们 的 SOUNDEX 值 。 因 为 Y.Lee 和
Y.Lie 发音相似,所以它们的 SOUNDEX 值匹配,因此 WHERE 子句正确地过滤出了所需的数据。

2. 时间和日期处理函数

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

3. 数值处理函数

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