Sql Server中的谓词和运算符

谓词一般有 where和having,check  谓词只计算 TRUE ,FALSE或者UNKNOWN 
逻辑表达式  如 AND 和OR

  

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

图片 1

 

1.IN 谓词的用法

   谓词和运算符配合使用是我们得到理想数据的最佳途径。

目录

  • 函数
  • 谓词
  • CASE 表达式

 

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);

 

一、函数

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

 

  2.函数的种类:

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

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

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

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

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

 

查询返回 订单id等于10248或者10249,10250的订单

一、浅谈谓词

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

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

图片 2图片 3

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(数值)

图片 4

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

   

  (2)MOD –
取余、求余

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

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

图片 5

 

  (3)ROUND –
四舍五入

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

 

2.BETWEEN 谓词的用法

  谓词的概念:一个运算结果为True、False或Unknown的逻辑表达式。它的运用范围有:where子句、Having子句、Check约束、联接查询的联接条件等。

   4.字符串函数

图片 7图片 8

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)拼接:+

图片 9

 

  (2)LEN –
字符串长度

--语法: LEN(字符串)

图片 10

 

  (3)LOWER –
小写转换

--语法:LOWER(字符串)

图片 11

 

  (4)REPLACE –
字符串的替换

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

图片 12

 

  (5)SUBSTRING –
字符串的截取

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

图片 13

 

  (6)UPPER –
大写转换

--语法:UPPER(字符串)

图片 14

 

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;

示例1,用于实施数据完整性为数据表添加约束:在员工表中,仅允许工资大于0的员工存储在表中。其中的谓词是“工资大于0”(SQL表达式:工资>0)。

   5.日期函数

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

图片 15

 

  (2)DATEPART
– 截取日期元素

图片 16

 

查询包含10300——10310范围订单

示例2,用于查询筛选数据的条件:查询员工表要求只返回销售部的员工。其中谓词是“部门等于销售部”(SQL表达式:部门=’销售部’)。

  6.转换函数

  (1)CAST – 类型转换

图片 17

 

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

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

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

图片 18

图片 19

 

3.LIKE谓词的用法

  

二、谓词

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

 

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';

  谓词和运算符的相互使用:

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

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

图片 20图片 21

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 个字符。

图片 22

图:前部分一致

图片 23

图:中间一致

图片 24

图:后部分一致

图片 25

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

 

注意的是 N  代表  该数据是NCHAR 或者NVARCHAR 而不是常规的字符数据类型
char或varchar

    1.通过逻辑运算符连接多个谓词(逻辑表达式),如使用AND和OR。

   3.BETWEEN – 范围查询

图片 26

 

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

图片 27

 

4.运算符

    2.谓词中使用比较运算符,如某个属性值大于或小于某指定个值。

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

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

图片 28

 

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

图片 29

 

select 5/2  结果为2   5/2.0 却是 2.5 为什么呢  

  

  5.IN – OR 的简便用法

图片 30

 

  用 IN 替换上述语句:

图片 31

 

  否定形式 NOT IN:

图片 32

 

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

  

图片 33图片 34

  SQL Server中有一些关键字可以替代谓词语义的含义,也可以称为SQL
Server中的谓词。

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

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

图片 35图片 36

-- 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);

测试数据

图片 37

图片 38

 

实际上是 数据类型优先级  在同一层计算是 5的优先级会被默认提升为 5.0

  其中常用的包括:

  7.EXIST 

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

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

图片 39

图片 40

 

  NOT EXIST 与 EXIST 相反,不存在:

图片 41

 

— 顺序: =, >, <, >=, <=, <>, !=, !>, !< 

    1.IN,在…之内,检查一个值是否存在于指定的集合内。     

三、CASE 表达式

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

 

   2.语法

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

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

图片 42

 

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

图片 43

 

如果相对数据类型进行显示转换可以用到

    2.BETWEEN,检查一个值是否在指定的区间范围内,范围包括两个边界值。

  3.行转列

图片 44

 

图片 45

 

select cast(5 as numeric(12,2))

    3.LIKE,判断字符串是否满足指定的格式,例如,查询名字姓氏为黄的员工(where
name like ‘黄%’)。

备注

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

 

图片 46

 

《SQL 基础知识梳理》系列

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

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

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

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

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

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

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

 

 


【博主】反骨仔

【原文】

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

 

 

 

二、运算符

    比较运算符:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website