首页 > 代码库 > [SQL] SQL 基础知识梳理(六)- 函数、谓词、CASE 表达式

[SQL] SQL 基础知识梳理(六)- 函数、谓词、CASE 表达式

SQL 基础知识梳理(六)-  函数、谓词、CASE 表达式

技术分享

 

目录

  • 函数
  • 谓词
  • CASE 表达式

 

一、函数

  1.函数:输入某一值得到相应输出结果的功能,输入值称为“参数”,输出值称为“返回值”。

 

  2.函数的种类

  (1)算术函数 - 数值计算

  (2)字符串函数 - 字符串操作

  (3)日期函数 - 日期操作

  (4)转换函数 - 转换数据类型

  (5)聚合函数 - 数据聚合

 

  3.算术函数(加、减、乘、除):+、-、*、/

  【备注】数据类型 NUMBERIC(全体位数,小数位数)可以指定数值的大小。

技术分享
CREATE TABLE SampleMath    (      m NUMERIC(10, 3) ,      n INTEGER ,      p INTEGER    );BEGIN TRAN;INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( 500, -- m - numeric          0, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( -180, -- m - numeric          0, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( NULL, -- m - numeric          NULL, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( NULL, -- m - numeric          7, -- n - integer          3  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( NULL, -- m - numeric          5, -- n - integer          2  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( NULL, -- m - numeric          4, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( 8, -- m - numeric          NULL, -- n - integer          3  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( 2.27, -- m - numeric          1, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( 5.555, -- m - numeric          2, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( NULL, -- m - numeric          1, -- n - integer          NULL  -- p - integer          );INSERT INTO dbo.SampleMath        ( m, n, p )VALUES  ( 8.76, -- m - numeric          NULL, -- n - integer          NULL  -- p - integer          );COMMIT;
初始化数据

  (1)ABS - 绝对值:不考虑数值的符号,表示一个数到原点距离的数值。

  绝对值的计算方法:0 和正数的绝对值就是其本身,负数的绝对值就是去掉符号后的结果。

--语法: ABS(数值)

技术分享

图:第 2 行:-180 的绝对值为 180

   

  (2)MOD - 取余、求余

--语法: MOD(被除数,除数)

  【备注】Oracle、DB2、PostgreSQL、MySQL 支持该函数,而 SQL Server 不支持该函数,所以这里用“%”代替。

技术分享

 

  (3)ROUND - 四舍五入

   如果指定四舍五入的位数为 1,那么会对小数点第 2 位进行四舍五入;如果指定位数为 2,那么就会对第 3 位进行四舍五入。技术分享

 

   4.字符串函数

技术分享
CREATE TABLE SampleStr(    str1 VARCHAR(40),    str2 VARCHAR(40),    str3 VARCHAR(40))BEGIN TRAN;INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( opx, -- str1 - varchar(40)          rt, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( abc, -- str1 - varchar(40)          def, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( aaa, -- str1 - varchar(40)          NULL, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( aaa, -- str1 - varchar(40)          NULL, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( NULL, -- str1 - varchar(40)          xyz, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( @!#$%, -- str1 - varchar(40)          NULL, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( ABC, -- str1 - varchar(40)          NULL, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( aBC, -- str1 - varchar(40)          NULL, -- str2 - varchar(40)          NULL  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( abc太郎, -- str1 - varchar(40)          abc, -- str2 - varchar(40)          ABC  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( abcdefabc, -- str1 - varchar(40)          abc, -- str2 - varchar(40)          ABC  -- str3 - varchar(40)          );INSERT INTO dbo.SampleStr        ( str1, str2, str3 )VALUES  ( micmic, -- str1 - varchar(40)          i, -- str2 - varchar(40)          T  -- str3 - varchar(40)          );COMMIT;
初始化数据

  (1)拼接:+

技术分享

 

  (2)LEN - 字符串长度

--语法: LEN(字符串)

技术分享

 

  (3)LOWER - 小写转换

--语法:LOWER(字符串)

技术分享

 

  (4)REPLACE - 字符串的替换

--语法:REPLACE(对象字符串, 替换前的字符串, 替换后的字符串)

技术分享

 

  (5)SUBSTRING - 字符串的截取

--语法:SUBSTRING(对象字符串,截取的起始位置,截取的字符数)

技术分享

 

  (6)UPPER - 大写转换

--语法:UPPER(字符串)

技术分享

 

   5.日期函数

  (1)获取当前日期和时间

技术分享

 

  (2)DATEPART - 截取日期元素

技术分享

 

  6.转换函数

  (1)CAST - 类型转换

技术分享

 

  (2)COALESCE - 将 NULL 转换为其他值

  作用:返回可变参数中左侧开始的第一个不是 NULL 的值(参数是可变的,即可以个数是无限的)。

--语法:COALESCE(数据1, 数据2, 数据3 ...)

技术分享

技术分享

 

二、谓词

  1.谓词:返回值为真值(TRUE/FALSE/UNKNOWN)的函数。

 

  2.LIKE - 字符串的部分一致查询

  【备注】= 运算符:字符串完全一致。

技术分享
CREATE TABLE SampleLike(strcool VARCHAR(6) NOT NULL,PRIMARY KEY(strcool))BEGIN TRAN;INSERT INTO dbo.SampleLike        ( strcool )VALUES  ( abcddd  -- strcool - varchar(6)          );INSERT INTO dbo.SampleLike        ( strcool )VALUES  ( dddabc  -- strcool - varchar(6)          );INSERT INTO dbo.SampleLike        ( strcool )VALUES  ( abdddc  -- strcool - varchar(6)          );INSERT INTO dbo.SampleLike        ( strcool )VALUES  ( ddabc  -- strcool - varchar(6)          )INSERT INTO dbo.SampleLike        ( strcool )VALUES  ( abddc  -- strcool - varchar(6)          )COMMIT;
初始化数据

  %:0 字符以上的任意字符串。

  _:任意 1 个字符。

技术分享

图:前部分一致

技术分享

图:中间一致

技术分享

图:后部分一致

技术分享

  后面 ddd 是 3 个字符,所以“abc__(2个 _)”不满足条件。

 

   3.BETWEEN - 范围查询

技术分享

 

  BETWEEN 会在结果中包含临界值(100 和 1000)。如果不想包含临界值可以使用 < 和 >。

技术分享

 

  4.IS NULL、IS NOT NULL - 判断是否为 NULL

  为了选取部分值为 NULL 的列的数据,不能使用 =,只能使用 IS NULL。

技术分享

 

  取反(不为空的数据),请使用 IS NOT NULL。

技术分享

 

  5.IN - OR 的简便用法

技术分享

 

  用 IN 替换上述语句:

技术分享

 

  否定形式 NOT IN:

技术分享

 

   【备注】IN 和 NOT IN 是无法选取 NULL 数据的。

  

  6.使用子查询作为 IN 谓词的参数

  IN 和 NOT IN 谓词具有其它谓词没有的用法,它的参数可以是子查询。 

技术分享
-- DDL:创建表CREATE TABLE TenpoShohin(tenpo_id  CHAR(4)       NOT NULL, tenpo_mei  VARCHAR(200) NOT NULL, shohin_id CHAR(4)       NOT NULL, suryo     INTEGER       NOT NULL, PRIMARY KEY (tenpo_id, shohin_id));-- DML:插入数据INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000A,    东京,        0001,    30);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000A,    东京,        0002,    50);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000A,    东京,        0003,    15);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000B,    名古屋,    0002,    30);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000B,    名古屋,    0003,    120);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000B,    名古屋,    0004,    20);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000B,    名古屋,    0006,    10);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000B,    名古屋,    0007,    40);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000C,    大阪,        0003,    20);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000C,    大阪,        0004,    50);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000C,    大阪,        0006,    90);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000C,    大阪,        0007,    70);INSERT INTO TenpoShohin (tenpo_id, tenpo_mei, shohin_id, suryo) VALUES (000D,    福冈,        0001,    100);
测试数据

技术分享

技术分享

 

  7.EXIST 

  很多时候基本上可以使用 IN 或 NOT IN 来代替该谓词。

  作用:判断是否存在满足某种条件的记录。

技术分享

技术分享

 

  NOT EXIST 与 EXIST 相反,不存在:

技术分享

 

三、CASE 表达式

   1.CASE 表达式:(条件)分歧。

 

   2.语法

--语法--CASE WHEN <判断表达式> THEN <表达式>--     WHEN <判断表达式> THEN <表达式>--     ...--     ELSE <表达式>--END

  判断表达式类似“键 = 值”的形式,返回值为真值(TRUE/FALSE/UNKNOW)的表达式。如果结果为真,就会返回 THEN 子句中的表达式;如果不为真,就跳转到下一条 WHEN 子句的判断中;如果到最后的 WHEN 子句都不为真,就执行最后一条 ELSE 的表达式。

技术分享

 

  下面是简化版的 CASE 表达式:

技术分享

 

  3.行转列

技术分享

 

技术分享

 

备注

  这里采用 MS SQL Server 进行验证,不保证所有的 DBMS 执行结果正确。

 

《SQL 基础知识梳理》系列

  《SQL 基础知识梳理(一) - 数据库与 SQL》

  《SQL 基础知识梳理(二) - 查询基础》

  《SQL 基础知识梳理(三) - 聚合和排序》

  《SQL 基础知识梳理(四) - 数据更新》

  《SQL 基础知识梳理(五) - 复杂查询》

  《SQL 基础知识梳理(六)- 函数、谓词、CASE 表达式》

  《SQL 基础知识梳理(七)- 集合运算》

 

 


【博主】反骨仔

【原文】http://www.cnblogs.com/liqingwen/p/6572284.html

【参考】《SQL ゼロからはじめるデータベース操作》

 

[SQL] SQL 基础知识梳理(六)- 函数、谓词、CASE 表达式