SQL的一些函数

朱雀 2024-03-25 23:59 7阅读 0赞

#### SQL的一些函数 ####

*  INSTR 字符查找函数
 *  CONCAT\_WS 拼接函数
 *  REVERSE 返回字符串的逆序
 *  WITH AS 子查询
 *  临时函数

## INSTR 字符查找函数 ##

-- instr语法
    instr(string1, string2, start_position, nth_appearance)

*  string1：源字符串，要在此字符串中查找。
 *  string2：要在string1中查找的字符串 。
 *  start\_position：代表从 string1 的哪个位置开始查找。此参数可选，如果省略默认为1，若是0则返回结果也是0。字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始位置。
 *  nth\_appearance：代表要查找第几次出现的string2。此参数可选，如果省略，默认为 1.如果为负数系统会报错。
 *  注意后两个参数不是范围。查找的范围是【start\_position,length(string1)】，最后一个参数是次数

SELECT 
     INSTR('SQL数据库开发SQL数据库开发','数据') is_4
    ,INSTR('SQL数据库开发SQL数据库开发','数据',6) is_12
    ,INSTR('SQL数据库开发SQL数据库开发','数据',1,20) is_0
    from dual 
    
    -- MySQL只能是两个参数。也是返回string2在第一个表达式中第一次出现的位置。如果没找到就返回0
    SELECT  INSTR('SQL数据库开发SQL数据库开发','数据') is_4

## CONCAT\_WS 拼接函数 ##

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

注：CONCAT\_WS 会忽略列中的 NULL 值。 用 ISNULL 函数包装可以为 null 的列，并提供默认值。

-- 输出 SQL-数据库-开发
    SELECT CONCAT_WS('-','SQL','数据库',NULL,'开发')
    -- 输出 语文，数学，英语，体育
    SELECT CONCAT_WS('，','语文','数学','英语','体育')
    
    -- CONCAT 与 CONCAT_WS 函数相似。只不过它是将参数逐个拼接。
    -- 输出 null  （有一个为空，则结果就是空）
    SELECT CONCAT('-','SQL','数据库',NULL,'开发')
    -- 输出 -SQL数据库开发
    SELECT CONCAT('-','SQL','数据库','开发')

## REVERSE 返回字符串的逆序 ##

Oracle中英文正常，中文会乱码。

-- 输出 -SQL数据库开发
    SELECT reverse('SQL数据库开发')

## WITH AS 子查询 ##

在Where子句中使用子查询进行以下查询

SELECT 
     name, salary  
    FROM
     People 
    WHERE
     NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto" ) 
     AND salary >= ( SELECT AVG( salary ) FROM salaries WHERE gender = "Female" )

使用WITH AS 提高可读性

with toronto_ppl as (
       SELECT DISTINCT name
       FROM population
       WHERE country = "Canada" AND city = "Toronto"
    )
    , avg_female_salary as (
       SELECT AVG(salary) as avgSalary
       FROM salaries
       WHERE gender = "Female"
    )
    SELECT 
    	name, salary
    FROM 
     People
    WHERE name in (SELECT name FROM toronto_ppl)
      AND salary >= (SELECT avgSalary FROM avg_female_salary)

## 临时函数 ##

*  它允许您将代码的块分解为较小的代码块
 *  它适用于写入清洁代码
 *  它可以防止重复，并允许您重用类似于使用Python中的函数的代码。

比如以下示例：

SELECT name, 
           CASE WHEN score>= 90 THEN "优"
                WHEN scoreBETWEEN 70 and 89 THEN "良"
                WHEN scoreBETWEEN 60 and 69 THEN "合格"
                WHEN score< 60 THEN "不合格"
                ELSE "n/a"
           END AS grade
    FROM stu

利用临时函数来捕获案例子句。  
通过临时函数，查询本身更简单，更可读，可以重复使用资历函数！

CREATE TEMPORARY FUNCTION get_grade(score INT64) AS (
    	   CASE WHEN score>= 90 THEN "优"
                WHEN scoreBETWEEN 70 and 89 THEN "良"
                WHEN scoreBETWEEN 60 and 69 THEN "合格"
                WHEN score< 60 THEN "不合格"
                ELSE "n/a"
           END);
    SELECT name, get_grade(score) as grade FROM stu