1. 函数:输入某一值得到相应输出结果的功能,输入值称为 "参数",输出值称为 "返回值"。
2. 函数的种类:
(1)算术函数 - 数值计算
(2)字符串函数 - 字符串操作
(3)日期函数 - 日期操作
(4)转换函数 - 转换数据类型
(5)聚合函数 - 数据聚合
【备注】数据类型 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 位进行四舍五入。
初始化数据
- 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(字符串)
(1)获取当前日期和时间:
(2)DATEPART - 截取日期元素
(1)CAST - 类型转换
(2)COALESCE - 将 NULL 转换为其他值
作用:返回可变参数中左侧开始的第一个不是 NULL 的值(参数是可变的,即可以个数是无限的)。
- --语法:COALESCE(数据1, 数据2, 数据3 ...)
图
图
1. 谓词:返回值为真值(TRUE/FALSE/UNKNOWN)的函数。
【备注】= 运算符:字符串完全一致。
初始化数据
- 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 个 _)" 不满足条件。
BETWEEN 会在结果中包含临界值(100 和 1000)。如果不想包含临界值可以使用 <和>。
为了选取部分值为 NULL 的列的数据,不能使用 =,只能使用 IS NULL。
取反(不为空的数据),请使用 IS NOT NULL。
用 IN 替换上述语句:
否定形式 NOT IN:
【备注】IN 和 NOT IN 是无法选取 NULL 数据的。
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);
图
很多时候基本上可以使用 IN 或 NOT IN 来代替该谓词。
作用:判断是否存在满足某种条件的记录。
NOT EXIST 与 EXIST 相反,不存在:
1.CASE 表达式:(条件)分歧。
2. 语法
- --语法
- --CASE WHEN <判断表达式> THEN <表达式>
- -- WHEN <判断表达式> THEN <表达式>
- -- ...
- -- ELSE <表达式>
- --END
判断表达式类似 "键 = 值" 的形式,返回值为真值(TRUE/FALSE/UNKNOW)的表达式。如果结果为真,就会返回 THEN 子句中的表达式;如果不为真,就跳转到下一条 WHEN 子句的判断中;如果到最后的 WHEN 子句都不为真,就执行最后一条 ELSE 的表达式。
下面是简化版的 CASE 表达式:
这里采用 MS SQL Server 进行验证,不保证所有的 DBMS 执行结果正确。
《》
《》
《》
《》
《》
《》
《》
【博主】反骨仔
【参考】《SQL ゼロからはじめるデータベース操作》
来源: http://www.cnblogs.com/liqingwen/p/6572284.html