【SQL 初级语法 4】函数、谓词、CASE 表达式

【SQL 初级语法 4】函数、谓词、CASE 表达式
目录

4 函数、谓词、CASE表达式

4.1 各种各样的函数

1、函数的种类

2、算数函数

3、字符串函数

4、日期函数

5、转换函数

4.2 谓词

1、什么是谓词

2、like 谓词 —— 字符串的部分一致查询

3、between 谓词 —— 范围查询

4、is null、is not null —— 判断是否为null

5、in 谓词 —— or 的简便用法

6、使用子查询作为 in 谓词的参数

7、exists 谓词

4.3 case 表达式

1、case 表达式的语法

2、case 表达式的使用方法

4 函数、谓词、CASE表达式

4.1 各种各样的函数

1、函数的种类

算数函数、字符串函数、日期函数、转换函数（用来转换数据类型和值的函数）、聚合函数

2、算数函数

abs() 计算绝对值

mod(被除数，除数) 求余；mod(7,3) = 1

round(对象数值，保留小数的位数) 四舍五入

3、字符串函数

|| 拼接函数
```
abc || de = abcde
abc || de || fg = abcdefg
```
length(str1) 字符串长度

lower(str1) 小写转换

upper(str1) 大写转换

replace(对象字符串，替换前字符串，替换后字符串)
```
replace(abcdefabc,abc,ABC) = ABCdefABC
```
subtring(对象字符串 from 截取的起始位置 for 截取的字符串) 这是标准的SQL语法，但现在只有PostgreSQL 和MySQL支持该语法。

SQL server ： substring(对象字符串，截取的起始位置，截取的字符串)

Oracle/DB2：substr(对象字符串，截取的起始位置，截取的字符数)

4、日期函数

1.current_date 当前日期返回SQL执行的日期，该函数无法在 SQL server中执行。此外，在 Oracle 和 DB2 中的语法略有不同。

SQL Server 使用 current_timestamp 来获取当前日期。
```
select cast(current_timestamp as date) as cur_date
```
2.current_time 当前时间，该函数无法在 SQL server中执行。此外，在 Oracle 和 DB2 中的语法略有不同。
```
select cast(current_timestamp as time) as cur_time
```
3.current_timestamp 当前日期和时间，这个函数可以获取当前日期+时间，使用的时候可以根据需要截取日期或者时间。

4.extract(日期元素 from 日期) 截取日期元素
```
extract(year from current_timestamp)
extract(month from current_timestamp)
extract(day from current_timestamp)
extract(hour from current_timestamp)
extract(minute from current_timestamp)
extract(second from current_timestamp)
```
5、转换函数

“转换”这个词含义非常广泛，在 SQL 中主要有两层意思：1.数据类型的转换，英文叫cast；2.值的转换。

1.cast(转换前的值 as 想要转换的数据类型) 类型转换
```
cast('0001' as integr)
cast('0001' as signed integer)
cast('2022-01-01' as date)
```
2.COALESCE —— 将 NULL 转换为其他值

COALESCE(数据1，数据2，数据3，.......) 返回参数左侧开始第一个不是 NULL 的值，参数个数是可变的，因此可以根据需要无限增加。该函数使用还是比较频繁的。

现在多数的DBMS中都提供了特有的 COALESCE 的简化版函数，但由于这些函数都依存于各自的 DBMS ，因此还是推荐大家使用通用的 COALESCE 函数。

4.2 谓词

1、什么是谓词

谓词是函数的一种，是需要满足特定条件的函数，该条件就是返回值是真值。对通常的函数来说，返回值可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（true、false、unknown）。

常见谓词：like、between、is null、is not null、in、exists

2、like 谓词 —— 字符串的部分一致查询
```
where strcol like '%ddd%'
```
此外还可以使用 _ (下划线) 来代替 % ，它代表的是任意一个字符
```
select *
from SampleLike
where strcol like 'abc__'
```
选取出strcol列的值为“abc + 任意两个字符” 的记录

3、between 谓词 —— 范围查询

该谓词与其他谓词或者函数的不同之处在于它使用了3个参数。
```
select 
    product_name
    ,sale_price
from Product
where sale_price between 100 and 1000
# between 包含 两端的值
如果不想要包含两端的值，就只能使用<和>了
where sale_price > 100
and sale_price < 1000  
```
4、is null、is not null —— 判断是否为null

选取值为null 的数据不能使用 = ，必须使用 is null。

5、in 谓词 —— or 的简便用法
```
select product_name, purchase_price
from Product
where purchase_price = 320
or purchase_price = 500
or purchase_price = 5000
# 这样写 也没错
 
# 可以改写成 in
where purchase_price in (320,500,5000)
```
与之相对应的还有 not in，但要注意，在使用 in 和 not in 时是无法选取 null 数据的。

6、使用子查询作为 in 谓词的参数

in 和 not in 具有其他谓词没有过的用法，可以使用子查询作为其参数。
```
select product_name, sale_price
from Product
where product_id in (select product_id
                    from ShopProduct
                    where shop_id = 'oooC')
```
7、exists 谓词

exists 的使用方法与之前的都不相同；语法理解起来比较困难；实际上即使不使用exists，基本上也都可以使用 in （或者 not in）来代替，尽管不能完全替代；如果一旦能够熟练使用 exists ，就能体现到它很大的便利性，因此，达到 SQL 中级水平时掌握此工具。现在只是简单介绍基本使用方法。

1. exists 谓词的使用方法

一言以蔽之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回 true，如果不存在就返回 false，exists 谓词的主语是“记录”。
```
select product_name,sale_price
from Product p
where exists (select *
              from ShopProduct as sp
              where sp.shop_id = '000C'
              and sp.product_id = p.product_id) 
```
exists 左侧没有任何参数，exists 是只有一个参数的谓词，exists 只需要在右侧书写一个参数，该参数通常都会是一个子查询。

2. 子查询中的 select *

exists 指挥关心记录是否存在，因此返回哪些列都没有关系。exists 只会判断是否存在满足子查询中 where 子句指定的条件 “商店编号（000C）“，商品表和商店商品表（ShopProduct）中的商品编号（product_id）相同” 的记录，只有存在这样的记录时，才会返回真 true。
```
select product_name,sale_price
from Product p
where exists (select 1
              from ShopProduct as sp
              where sp.shop_id = '000C'
              and sp.product_id = p.product_id) 
# 即使写成 select 1 结果也不会有任何改变
```
作为 exists 参数的子查询中经常会使用 select *。

3. 使用 not exists 代替 not in
```
select product_name,sale_price
from Product p
where not exists (select 1
              from ShopProduct as sp
              where sp.shop_id = '000A'
              and sp.product_id = p.product_id) 
```
not exists 与 exists 相反，当“不存在”满足子查询中指定条件的记录时返回true。

那么用 in 和 exists 的 select 语句进行比较时，会得到怎样的结果呢？in 理解起来会更容易，但 exists 拥有 in 所不具有的便利性，严格来说两者并不相同，所以等中级 SQL 的时候掌握这两种谓词的使用方法。

4.3 case 表达式

1、case 表达式的语法
```
case when <求值表达式> then <表达式>
     when <求值表达式> then <表达式>
     when <求值表达式> then <表达式>
     ...
     else <表达式>
end
```
case 表达式会从最初的 when 子句中的 <求值表达式> 进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为 true 就返回 then 子句中的表达式，case 表达式的执行到此为止。如果结果不为真，那么就会跳转到下一条 when 子句的求值中。如果直到最后的 when 子句为止返回结果都不为真，那么就会返回 else 中的表达式，执行终止。

2、case 表达式的使用方法
```
select 
    product_name
    ,case when product_type = '衣服' then 'A: ' || product_type
        when product_type = '办公用品' then 'B: ' || product_type
        when product_type = '厨房用具' then 'C: ' || product_type
        else null 
    end as abc_product_type
from product 
```
注意，这里使用了拼接函数 ||，输出结果会有：“A：衣服”、“B：办公用品”、“C：厨房用具”

else 子句可以省略不写，这时会被默认为 else null，但还是建议不要省略。

case 表达式中的 end 不能省略。

1. 可以利用 case 表达式，将 select 语句的结果中的行和列进行转换

如果将商品种类列，作为 group by 子句的聚合键来使用，但是这样得到的结果会以 “行” 的形式输出，而无法以列的形式进行排列。
```
select product_type,sum(sale_price) as sum_price
from product
group by product_type
```
输出为：
```
product_type | sum_price
-------------------------
衣服         |      5000
办公用品     |       600
厨房用具     |     11180
```
在 sum 函数中使用 case 表达式来获得一个 3 列的结果。
```
-- 对按照商品种类计算出的销售单价合计值进行行列转换
select sum(case when product_type = '衣服' 
            then sale_price else 0 end) as sum_price_clothes
       ,sum(case when product_type = '厨房用具' 
            then sale_price else 0 end) as sum_price_kitchen
       ,sum(case when product_type = '办公用品' 
            then sale_price else 0 end) as sum_price_office
from product
```
输出结果为：
```
sum_price_clothes  |  sum_price_kitchen  |  sum_price_office
-------------------------------------------------------------
             5000  |               11180 |              600
```
2. 简单case 表达式和搜索 case 表达式
```
# 简单 case 表达式
select 
    product_name
    ,case product_name 
        when '衣服'     then 'A: ' || product_type
        when '办公用品' then 'B: ' || product_type
        when '厨房用具' then 'C: ' || product_type
        else null 
    end as abc_product_type
from product 
 
 
# 搜索 case 表达式
select 
    product_name
    ,case when product_type = '衣服'     then 'A: ' || product_type
          when product_type = '办公用品' then 'B: ' || product_type
          when product_type = '厨房用具' then 'C: ' || product_type
          else null 
    end as abc_product_type
from product 
```
搜索 case 表达式可以在 when 子句中指定不同的列。

有些 DBMS 还提供了一些特有的 case 表达式的简化函数，例如，Oracle 中的 DECODE、MySQL 中的 IF 等，但上述函数只能在特定的 DBMS 中使用，并且能够使用的条件也没有CASE 表达式那么丰富，因此并没有什么优势。建议尽量不要使用这些特定的 SQL 语句。

———————————————————————————————————————————

点击链接查看SQL 专栏更多文章：https://blog.csdn.net/weixin_46249441/category_11913899.html?spm=1001.2014.3001.5482
相关阅读:
pymysql的使用
 gcc内联汇编
 工业智能网关BL110应用之四十四： COM口采集电表设备的配置
 SpringBoot实现注解方式日志log记录
 ESP8266-Arduino网络编程实例-远程固件升级
 浅谈余压监控系统在某高层住宅的应用方案
 el-table表格中加入输入框
 SlicePlane的Heading角度与Math.atan2(y,x)的对应转换关系
 814. 二叉树剪枝 : 简单递归运用题
 go sqlx 包
原文地址：https://blog.csdn.net/weixin_46249441/article/details/125938377

4 函数、谓词、CASE表达式

4.1 各种各样的函数

1、函数的种类

2、算数函数

3、字符串函数

4、日期函数

5、转换函数

4.2 谓词

1、什么是谓词

2、like 谓词 —— 字符串的部分一致查询

3、between 谓词 —— 范围查询

4、is null、is not null —— 判断是否为null

5、in 谓词 —— or 的简便用法

6、使用子查询作为 in 谓词的参数

7、exists 谓词

4.3 case 表达式

1、case 表达式的语法

2、case 表达式的使用方法