跳转至

Topic 4.1 - SQL 中的数值运算

1. SQL 中的数据类型

在我们本门课，大家只需要掌握三种 SQL 中的数据类型即可：

  • 数字类型：例如 INT、FLOAT、DOUBLE 等等，这些数据类型的值是数字，可以进行数学运算
  • 字符串类型：例如 CHAR、VARCHAR、TEXT 等等，这些数据类型的值是字符串，可以进行字符串拼接等运算
  • 日期时间类型：例如 DATE、DATETIME 等等，这些数据类型的值是日期或者日期时间，可以进行日期时间的运算

我们本节将根据这三种数据类型，介绍在 SQL 中如何进行进阶的数据运算。

2. SQL 中数字类型的运算

(1) 数学运算符

数学运算符，我们在之前的课程中已经介绍过了，这里我们再简单回顾一下：

  • 加法符号：+
  • 减法符号：-
  • 乘法符号：*
  • 除法符号：/
  • 乘方符号：^
  • 括号：() 可以改变运算的优先级

我们来看以下当时写的一些例子，就不运行了：

SELECT
    TrackId,
    UnitPrice,
    UnitPrice * 1.1 AS NewPrice -- 新价格
FROM Track

SELECT
    InvoiceId,
    TrackId,
    UnitPrice * Quantity AS TotalPrice -- 总价
FROM InvoiceLine

SELECT
    TrackId,
    UnitPrice / Milliseconds * 1000 AS PricePerSecond
FROM Track

(2) 计算函数

除了运算符号之外，SQL 中还有一些内置的计算函数：

  • 注意，计算函数会根据数据库类型的不同而有所不同

  • 我们来列举一些 Azure SQL Database 中常用的计算函数：

    • ABS(x)：返回 x 的绝对值
    • ROUND(x, d)：将 x 四舍五入到 d 位小数
    • CEILING(x)：返回大于或等于 x 的最小整数
    • FLOOR(x)：返回小于或等于 x 的最大整数
    • POWER(x, y)：返回 x 的 y 次幂
    • SQRT(x)：返回 x 的平方根
    • LOG(x)：返回 x 的自然对数
    • EXP(x)：返回 e 的 x 次幂

  • 其实还有很多运算函数，比方说三角函数什么的，但是使用的频率实在太少了，这里我们就不列举了，大家可以自己查询一下，但是注意查询的时候一定要查询 Azure SQL Database 中的计算函数，因为不同数据库类型中的计算函数可能会有所不同

  • 注意，这里我们推荐大家，运算函数和关键字一样，也使用大写字母来书写，这样可以和列名区分开来，代码也更清晰易读

这里我们来在 Track 表中使用一些计算函数（数值可能没什么实际意义，我们只是展示函数功能）：

SELECT TOP 5
    TrackId,
    UnitPrice,
    SQRT(UnitPrice) AS SqrtUnitPrice, -- 计算 UnitPrice 的平方根
    LOG(UnitPrice) AS LogUnitPrice, -- 计算 UnitPrice 的自然对数
    EXP(UnitPrice) AS ExpUnitPrice, -- 计算 e 的 UnitPrice 次幂
    ROUND(UnitPrice, 1) AS RoundedUnitPrice, -- 将 UnitPrice 四舍五入到 1 位小数
    CEILING(UnitPrice) AS CeilingUnitPrice, -- 将 UnitPrice 向上取整
    FLOOR(UnitPrice) AS FloorUnitPrice -- 将 UnitPrice 向下取整
FROM Track

========================================================================================================================================

TrackId     UnitPrice   SqrtUnitPrice     LogUnitPrice          ExpUnitPrice       RoundedUnitPrice  CeilingUnitPrice  FloorUnitPrice
----------  ----------  ----------------  --------------------  -----------------  ----------------  ----------------  --------------
1           0.99        0.99498743710662  -0.01005033585350145  2.691234472349262  1.00              1                 0             
2           0.99        0.99498743710662  -0.01005033585350145  2.691234472349262  1.00              1                 0             
3           0.99        0.99498743710662  -0.01005033585350145  2.691234472349262  1.00              1                 0             
4           0.99        0.99498743710662  -0.01005033585350145  2.691234472349262  1.00              1                 0             
5           0.99        0.99498743710662  -0.01005033585350145  2.691234472349262  1.00              1                 0             
((5 rows affected))

3. SQL 中的条件计算

有时候我们需要根据某些条件来进行计算，这时候我们就可以使用 SQL 中的条件计算函数 CASE WHEN 来实现这个功能：

  • CASE WHEN 的语法如下：
SELECT
    ...,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE resultN
    END AS new_column
    ...
FROM table_name
  • 在这个语法中，整个这个代码块，从 CASE 到 END，就是一个条件计算的表达式，产生一个计算出来的新列
  • 这个列的计算逻辑是：
    • 当满足 condition1 条件时，这个新列的值就是 result1
    • 当满足 condition2 条件时，这个新列的值就是 result2
    • ...
    • 如果都不满足，那么这个新列的值就是 resultN

这里的条件运算和我们之前在 WHERE 语句中使用的条件表达式是一样的：

  • 可以使用比较运算符，例如 =、!=、>、<、>=、<= 判断数值
  • 可以使用 AND、OR、NOT 与括号 () 来组合多个条件
  • 可以使用 IN 来判断某个值是否在一个列表中
  • 可以使用 LIKE 来进行字符串模糊匹配
  • ...

我们来看以下一些例子：

  • 查询 Track 表中的每首歌的价格等级，如果价格小于等于 1 就是 "Cheap"，大于 1 就是 "Expensive"：
SELECT TOP 5
    TrackId,
    UnitPrice,
    CASE
        WHEN UnitPrice <= 1 THEN 'Cheap'
        ELSE                     'Expensive'  -- 这里我们可以多打点空格让值对齐
    END AS PriceLevel
FROM Track

==================================

TrackId     UnitPrice   PriceLevel
----------  ----------  ----------
1           0.99        Cheap     
2           0.99        Cheap     
3           0.99        Cheap     
4           0.99        Cheap     
5           0.99        Cheap     
((5 rows affected))
  • 查询 Invoice 表并创建一个新字段 IsEnglishSpeakingCountry，如果 BillingCountry 在 "USA"、"Canada"、"Australia"、"UK"、"New Zealand" 这五个国家中，那么这个新字段的值就是 1，否则就是 0：
SELECT TOP 5
    InvoiceId,
    BillingCountry,
    CASE
        WHEN BillingCountry IN ('USA', 'Canada', 'Australia', 'UK', 'New Zealand') THEN 1
        ELSE 0
    END AS IsEnglishSpeakingCountry
FROM Invoice

====================================================

InvoiceId   BillingCountry  IsEnglishSpeakingCountry
----------  --------------  ------------------------
1           Germany         0                       
2           Norway          0                       
3           Belgium         0                       
4           Canada          1                       
5           USA             1                       
((5 rows affected))
  • 查询 Track 表并创建一个新字段 TrackType，如果 Milliseconds 小于 180000 就是 "Short"，如果 Milliseconds 大于等于 180000 且小于 300000 就是 "Medium"，否则就是 "Long"：
SELECT TOP 5
    TrackId,
    Milliseconds,
    CASE
        WHEN Milliseconds < 180000                  THEN 'Short'
        WHEN Milliseconds BETWEEN 180000 AND 300000 THEN 'Medium'
        ELSE                                             'Long'
    END AS TrackType
FROM Track

====================================

TrackId     Milliseconds  TrackType 
----------  ------------  ----------
1           343719        Long      
2           342562        Long      
3           230619        Medium    
4           252051        Medium    
5           375418        Long      
((5 rows affected))