MySQL 中数据的检索（六：计算字段）

雨点打透心脏的1/2处 2022-05-29 10:11 239阅读 0赞

存在数据库系统中的数据的默认展现方式不一定完全符合应用的要求，比如：

*  数据库系统中姓名、工号是单独存储在两个字段的，但是在显示的时候想显示成“姓名+工号”的形式。
 *  数据库系统中金额的显示格式是普通的数字显示方式（比如668186.99），但是显示的时候想以千分位的形式显示（比如668,186.99）。
 *  数据库系统中基本工资、奖金是单独存储在两个字段的，但是希望显示员工的工资总额。
 *  要检索工资总额的80%超过5000元的员工信息。
 *  要升级员工工号，需要将所有员工的工号前增加两位0。

因为需要的数据不是数据表中本来就有的，必须经过一定的计算、转换或者格式化，所有这些功能都不能通过简单的 SQL 语句来完成的。  
这种情况可以在宿主语言中通过编写代码的方式来进行这些计算、转换或者格式化的工作，但是当数据量比较大的时候这样处理的速度是非常慢的。  
计算字段是数据库系统提供的对数据进行计算、转换或者格式化的功能，由于是在数据库系统内部进行的这些工作，而且数据库系统都这些工作进行了优化，所以其处理效率比在宿主语言中通过编写代码的方式进行处理要高效的多。

一、常量字段  
要求各个公司提供所有员工的资料信息，其中包括公司名称、注册资本、员工姓名、年龄、所在子公司，而且出于特殊考虑，要求每个员工都列出这些资料信息。对于单个公司而言，公司名称、注册资本这两部分信息不是能从现有的 T\_Employee，但是它们是确定的值

SELECT ‘CowNew集团’,918000000,FName,FAge,FSubCompany FROM T\_Employee  
\#’CowNew集团’ 和 918000000 不是一个实际的存在的列，但是在查询出来的数据中它们看起来是一个实际存在的字段，这样的字段被称为“常量字段”（也称为“常量值”），它们完全可以被看成一个值确定的字段

为常量字段指定别名：  
SELECT ‘CowNew 集团’ AS CompanyName,918000000 AS  
RegAmount,FName,FAge,FSubCompany  
FROM T\_Employee

二、字段间计算  
要求统计全体员工的工资指数，工资指数的计算公式为年龄与工资的乘积，这就需要计算将 FAge 和 FSalary 的乘积做为一个工资指数列体现到检索结果中：  
SELECT FNumber,FName,FAge \* FSalary AS FSalaryIndex FROM T\_Employee

要求统计每个员工的工资幸福指数，工资幸福指数的计算公式为工资/（年龄-21），而且要求在每行数据前添加一列，这列  
的值等于 125 与 521 的和：  
SELECT 125+521,FNumber,FName,FSalary/(FAge-21) AS FHappyIndex  
FROM T\_Employee

计算字段也可以在WHERE语句等子句或者UPDATE、DELETE中使用：  
SELECT \* FROM T\_Employee  
WHERE FSalary/(FAge-21)>1000 \#检索所有资幸福指数大于1000的员工信息

三、数据处理函数

计算每一个名称不为空的员工的名字以及名字的长度（MSSQLServer中 LENGTH 替换为 LEN）：  
SELECT FName, LENGTH(FName) AS namelength FROM T\_Employee  
WHERE FName IS NOT NULL

主流系统都提供了取得字符串的子串的函数，在 MYSQL、MSSQLServer 中这个函数名称为 SUBSTRING。  
这个函数接受三个参数，第一个参数为要取的主字符串，第二个参数为字串的起始位置（从1开始计数），第三个参数为字串的长度。  
SELECT FName, SUBSTRING(FName,2,3) FROM T\_Employee  
WHERE FName IS NOT NULL \#取得每一个名称不为空的员工的名字以及名字中从第二个字符开始、长度为 3 的字串

多个函数还可以嵌套使用，如：  
主流系统都提供了计算正弦函数值的函数SIN和计算绝对值的函数ABS，它们都接受一个数值类型的参数。  
SELECT FName,FAge, SIN(FAge) , ABS(SIN(FAge)) FROM T\_Employee \#取得每个员工的姓名、年龄、年龄的正弦函数值以及年龄的正弦函数值的绝对值

四、字符串的拼接  
注：在 Java、C\# 等编程语言中字符串是用半角的双引号来包围的，但是在有的数据库系统的SQL语法中双引号有其他的含义（比如列的别名），而所有的数据库系统都支持用单引号包围的形式定义的字符串。

在MYSQL中进行字符串的拼接要使用 CONCAT 函数，CONCAT 函数支持一个或者多个参数，参数类型可以为字符串类型也可以是非字符串类型，对于非字符串类型的参数 MySQL 将尝试将其转化为字符串类型，CONCAT 函数会将所有参数按照参数的顺序拼接成一个字符  
串做为返回值。CONCAT 支持只有一个参数的用法，这时的 CONCAT 可以看作是一个将这个参数值尝试转化为字符串类型值的函数。  
如：  
SELECT CONCAT(‘工号为:’,FNumber,’的员工的幸福指数:’,FSalary/(FAge-21))  
FROM T\_Employee \#将用户的多个字段信息以一个计算字段的形式查询出来

MYSQL 中还提供了另外一个进行字符串拼接的函数 CONCAT\_WS。  
CONCAT\_WS 可以在待拼接的字符串之间加入指定的分隔符，它的第一个参数值为采用的分隔符，而剩下的参数则为待拼接的字符串值。  
SELECT CONCAT\_WS(‘,’,FNumber,FAge,FDepartment,FSalary) FROM T\_Employee

注：  
在MYSQL中，当用加号“+”连接两个字段（或者多个字段）的时候，MYSQL会尝试将字段值转换为数字类型（如果转换失败则认为字段值为0），然后进行字段的加法运算。  
SELECT ’12’+’33’,FAge+’1′ FROM T\_Employee \#第一列全为 45  
SELECT ‘abc’+’123′,FAge+’a’ FROM T\_Employee \#第一列全为 123

注：  
与MYSQL不同，MSSQLServer 中可以直接使用加号“+”来拼接字符串。如：  
SELECT ‘工号为’+FNumber+’的员工姓名为’+Fname FROM T\_Employee  
WHERE FName IS NOT NULL

五、计算字段的其他用途  
不仅能在 SELECT 语句中使用计算字段，同样可以在进行数据过滤、数据删除以及数据更新的时候使用计算字段。

在BETWEEN……AND……语句中使用了计算表达式：  
规定一个合理工资范围：上限为年龄的 1.8 倍加上 5000 元，下限为年龄的 1.5 倍加上 2000 元，介于这两者之间的即为合理工资。  
SELECT \* FROM T\_Employee  
WHERE Fsalary BETWEEN Fage\*1.5+2000 AND Fage\*1.8+5000 \#查询所有处于合理工资范围内的员工信息

在MAX、MIN函数中使用了计算字段：  
定义 “工资年龄指数” 为 “工资除以年龄”。  
SELECT MAX(FSalary/FAge) AS MAXVALUE,MIN(FSalary/FAge) AS MINVALUE  
FROM T\_Employee \#查询“工资年龄指数”的最高值和最低值

在UPDATE中使用计算字段：  
UPDATE T\_Employee SET FAge=FAge+1 \#在 SET 子句中用计算字段为 FAge 字段设定新值：新年时，自动将员工的年龄全部加1。