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

好大一个绿菠萝

于 2022-08-07 10:00:00 发布

阅读量244

点赞数 1

分类专栏： SQL 文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/weixin_46249441/article/details/125938377

版权

SQL 专栏收录该内容

9 篇文章 2 订阅

订阅专栏

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

3、between 谓词 —— 范围查询

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

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

好大一个绿菠萝

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【SQL 初级语法 4】函数、谓词、CASE 表达式

点击链接查看SQL 专栏更多文章：https://blog.csdn.net/weixin_46249441/category_11913899.html?spm=1001.2014.3001.5482
复制链接

扫一扫