第三章 SQL语言元素（一）

InterSystems SQL命令（也称为SQL语句）以关键字开头，后跟一个或多个参数。其中一些参数可能是子句或函数，由它们自己的关键字标识。

InterSystems SQL关键字包括命令名称，函数名称，谓词条件名称，数据类型名称，字段约束，优化选项和特殊变量。它们还包括 AND ， OR 和 NOT 逻辑运算符， NULL 列值指示符以及ODBC函数构造，例如 {d dateval} 和 {fn CONCAT（str1，str2）} 。

聚合函数是SQL固有函数，它计算列的所有值并返回单个聚合值。

这种写法只能在mac routine里，类文件里编译报错。

如果将用户提供的（外部）函数的使用配置为系统范围的选项，则该SQL语句只能调用用户提供的（外部）函数。默认为“否”。默认情况下，尝试调用用户提供的函数会发出 SQLCODE -372 错误。可以使用 ％SYSTEMSQL类的SetAllowExtrinsicFunctions（） 方法在系统范围内配置SQL对外部函数的使用。若要确定当前设置，请调用 $SYSTEMSQLCurrentSettings（） ，该显示显示“允许在SQL语句中使用外部函数”选项。

不能使用用户提供的函数来调用 %routine (名称以%字符开头的例程)。

尝试这样做会发出 SQLCODE -373 错误。

InterSystems SQL文字具有以下语法：

文字是一系列代表实际（文字）值的字符。它可以是数字或字符串。

空字符串是文字字符串；它由两个单引号字符（ '' ）表示。 NULL 不是文字值；它表示没有任何值。

注意：在嵌入式SQL中，不允许在字符串文字中使用以 ## 开头的一些字符序列，如“使用嵌入式SQL”一章的“文字值”中所述。此限制不适用于其他SQL调用，例如动态SQL。

使用单引号（ ' ）字符作为字符串定界符。 SQL兼容性支持双引号字符（ “ ）的使用，但由于与定界标识符标准冲突，因此强烈建议不要使用。将一对双引号字符 "" 解析为无效的定界标识符。并生成 SQLCODE -1 错误。

要在字符串中指定单引号字符作为字面字符，请指定一对这样的字符作为字面转义序列。

例如， 'a 'normal' string' 。

双竖条( || )是首选的SQL连接 *** 作符。

它可以用于连接两个数字、两个字符串或一个数字和一个字符串。

下划线( _ )作为SQL连接 *** 作符提供，以保证ObjectScript的兼容性。

此连接 *** 作符只能用于连接两个字符串。

如果两个 *** 作数都是字符串，并且两个字符串都具有相同的排序规则类型，则所得的级联字符串具有该排序规则类型。在所有其他情况下，连接的结果是排序类型 EXACT 。

使用 NULL 关键字表示没有指定值。

在SQL中， NULL 始终是表示数据值因任何原因未指定或不存在的首选方式。

SQL零长度字符串(空字符串)由两个单引号字符指定。

空字符串( " )与空字符串是不同的。

空字符串是一个已定义的值，一个不包含字符的字符串，一个长度为0的字符串。

一个零长度的字符串在内部由非显示字符 $CHAR(0) 表示。

注意:不建议使用SQL零长度字符串作为字段输入值或字段默认值。

使用 NULL 表示数据值的缺失。

在SQL编码中应避免使用SQL零长度字符串。

但是，由于许多SQL *** 作都会删除末尾的空格，所以只包含空格字符(空格和制表符)的数据值可能会导致SQL的零长度字符串。

注意，不同的SQL length函数返回不同的值: length 、 CHAR_LENGTH 和 DATALENGTH 返回SQL长度。

$LENGTH 返回ObjectScript表示长度。

长度不计算尾随空格;

所有其他长度函数都计算末尾的空格。

NOT NULL数据约束要求字段必须接收一个数据值;

不允许指定NULL而不是值。

这个约束不阻止使用空字符串值。

SELECT 语句的 WHERE 或 HAVING 子句中的 IS NULL 谓词选择空值;

它不选择空字符串值。

IFNULL 函数计算一个字段值，如果字段值为 NULL ，则返回第二个参数中指定的值。

它不会将空字符串值视为非空值。

COALESCE 函数从提供的数据中选择第一个非空值。

它将空字符串值视为非空值。

当 CONCAT 函数或 concenate *** 作符( || )连接一个字符串和一个 NULL 时，结果是 NULL 。

如下面的例子所示:

AVG、COUNT、MAX、MIN 和 SUM 聚合函数在执行 *** 作时忽略 NULL 值。

( COUNT 统计所有行，因为不可能有一个所有字段都为空值的记录。)

SELECT 语句的 DISTINCT 关键字在其 *** 作中包含 NULL ;

如果指定的字段有空值， DISTINCT 返回一个空行

AVG 、 COUNT 和 MIN 、聚合函数受空字符串值的影响。

MIN 函数将空字符串视为最小值，即使存在值为0的行。

MAX 和 SUM 聚合函数不受空字符串值的影响。

对大多数SQL函数提供 NULL 作为 *** 作数将返回 NULL 。

任何以NULL作为 *** 作数的SQL算术 *** 作都返回 NULL 值。

因此,7 +零=零。

这包括二元运算加法( + )、减法( - )、乘法( )、除法( / )、整数除法( \ )和取模( # )，以及一元符号运算符加号( + )和减号( - )。

算术 *** 作中指定的空字符串将被视为0(零)值。

除法( / )，整数除法( \ )，或对空字符串( 6/ " )取模( # )会导致 <DIVIDE> 错误。

在SQL中， NULL 的长度是没有定义的(它返回 < NULL > )。

然而，空字符串的长度被定义为长度为0。

如下面的例子所示:

如本例所示，SQL LENGTH 函数返回SQL长度。

可以使用 ASCII 函数将SQL的零长度字符串转换为 NULL ，示例如下:

但是，对标准 SQL 的某些系统间IRIS扩展对 NULL 和空字符串的长度的处理是不同的。

$LENGTH函数返回这些值的InterSystems IRIS内部表示: NULL 表示为长度为0的已定义值，SQL空字符串表示为长度为0的字符串。

该功能与ObjectScript兼容。

这些值的内部表示方式的另一个重要位置是 %STRING 、 %SQLSTRING 和 %SQLUPPER 函数，它们将空格附加到值中。

因为 NULL 实际上没有值，所以在它后面添加一个空格会创建一个长度为1的字符串。

但是一个空字符串确实有一个字符值，所以在它后面加上一个空格会创建一个长度为2的字符串。

如下面的例子所示:

注意，这个例子使用的是 CHAR_LENGTH ，而不是 LENGTH 。

因为 LENGTH 函数删除了末尾的空格，所以 LENGTH(%STRING(NULL)) 返回长度为0的字符串;

LENGTH(%STRING(")) 返回长度为2的字符串， 因为 %STRING 追加的是前导空格，而不是尾随空格。

当SQL NULL 输出到ObjectScript时，它由ObjectScript空字符串( "" )表示，长度为0的字符串。

当SQL零长度字符串数据输出到ObjectScript时，它由包含 $CHAR(0) 的字符串表示，该字符串长度为1。

在ObjectScript中，没有值通常用空字符串( "" )表示。

当这个值被传递到嵌入式SQL中时，它会被视为空值，如下面的例子所示:

如果指定了一个未定义的输入主机变量，嵌入式SQL将其值视为 NULL 。

当将NULL或空字符串值从嵌入式SQL传递到ObjectScript时， NULL 被转换为长度为0的字符串，空字符串被转换为长度为1的字符串。

如下面的例子所示:

在下面的例子中，SQL的空字符串加上一个空格被传递为长度为2的字符串:

第三十一章 SQL函数 CONVERT

将给定表达式转换为指定数据类型的函数。

CONVERT(datatype,expression[,format-code])

{fn CONVERT(expression,datatype)}

参数

expression - 要转换的表达式。

datatype - 要将表达式转换为的数据类型。

format - 可选-指定日期和时间格式的整数代码，用于在日期/时间/时间戳数据类型和字符数据类型之间进行转换。 此参数仅用于通用标量语法形式。

描述

这里描述了CONVERT函数的两种不同实现。 两者都将一种数据类型中的表达式转换为另一种数据类型中的相应值。 两者都执行日期和时间转换。

注意:这两个CONVERT实现中的参数以不同的顺序表示。 第一个是与MS SQL Server兼容的通用 IRIS标量函数，它接受三个参数。 第二个是带有两个参数的 ODBC标量函数。 下面的文本将分别处理这两种形式的CONVERT。

CONVERT(datatype,expression)支持流数据的转换。 例如，可以将字符流字段的内容转换为数据类型为VARCHAR的字符串。

{fn CONVERT(expression,datatype)}不支持流数据的转换; 指定要表达的流字段将导致SQLCODE -37错误。

为两个版本的CONVERT指定一个无效值将导致SQLCODE -141。

如果表达式没有定义的数据类型(例如ObjectScript提供的主机变量)，则其数据类型默认为字符串数据类型。

CONVERT(datatype,expression,format-code)

可以通过执行VARCHAR-to-VARCHAR转换来截断字符串，指定输出字符串长度小于表达式字符串长度。

在使用CONVERT(或CAST)时，如果字符数据类型(如CHAR或VARCHAR)没有指定长度，则默认的最大长度为30个字符。 如果二进制数据类型(如binary或VARBINARY)没有指定长度，则默认的最大长度为30个字符。 否则，这些没有指定长度的数据类型将被映射到一个1个字符的MAXLEN，如data types表所示。

可以执行BIT数据类型转换。 允许的值为1、0或NULL。 如果指定任何其他值，IRIS将发出SQLCODE -141错误。 在下面的嵌入式SQL示例中，两者都是一个NULL的BIT转换:

ClassMethod Convert()

{

s a=""

&sql(

SELECT CONVERT(BIT,:a),

CONVERT(BIT,NULL)

INTO :x,:y)

w !,"SQLCODE=",SQLCODE

w !,"the host variable is:",x

w !,"the NULL keyword is:",y

}

可选的format-code参数指定日期、datetime或时间格式。 该格式既可用于定义从日期/时间/时间戳数据类型转换为字符串时的输出，也可用于定义从字符串转换为日期/时间/时间戳数据类型时的输入。 支持以下格式代码; 输出两位数年份的格式代码列在第一列; 输出四位数年或不输出年的格式列在第二列:

Two-digit year codes Four-digit year codes Format

以下是日期和时间转换的特性:

取值范围:允许的日期范围为0001-01-01 ~ 9999-12-31。

默认值:

将时间值转换为TIMESTAMP、POSIXTIME、DATETIME或SMALLDATETIME时，日期默认为1900-01-01。 注意，对于{fn CONVERT()}，日期默认为1841-01-01。

将日期值转换为TIMESTAMP、POSIXTIME、DATETIME或SMALLDATETIME时，时间默认为00:00:00。

Default Format:如果没有指定Format -code, CONVERT将尝试从指定的值确定格式。 如果不能，则默认为格式代码100。

两位数年份:从00到49的两位数年份转换为21世纪的日期(2000到2049); 从50到99的两位数年份转换为20世纪的日期(1950到1999)。

分数秒:分数秒前可以加句号()或冒号(:)。 这些符号有不同的含义:

句点是默认值，可用于所有格式代码。 句号表示标准分数; 因此，12:00:004表示十分之四秒，而12:00:00004表示千分之四秒。 分数精度的位数没有限制。

冒号只能用于以下格式代码值:9/109、13/113、14/114、130和131。 冒号表示后面的数字是千分之一秒; 因此12:00:00:4表示四万分之一秒(12:00:00004)。 冒号后面的数字限制为3位。

当指定表达式的格式无效或格式与格式代码不匹配时，将产生SQLCODE -141错误。 指定一个不存在的格式代码将返回1900-01-01 00:00:00。

{fn CONVERT(expression,datatype)}

这是ODBC标量函数。 它支持以下ODBC显式数据类型转换。 必须使用“SQL_”关键字指定这种形式的CONVERT的数据类型转换。 在下表中，有两组转换数据类型，第一组转换数据值和数据类型，第二组转换数据类型，但不转换数据值:

Source Conversion

SQL_VARCHAR是标准的ODBC表示。 在转换为SQL_VARCHAR时，日期和时间被转换为相应的ODBC表示; 数字数据类型值转换为字符串表示。 从SQL_VARCHAR转换时，该值必须是有效的ODBC Time、Timestamp或Date表示。

当将时间值转换为SQL_TIMESTAMP或SQL_POSIXTIME时，未指定的日期默认为1841-01-01。 注意，对于CONVERT()，日期默认为1900-01-01。

将date值转换为SQL_TIMESTAMP或SQL_POSIXTIME时，时间默认为00:00:00。

在这种语法形式中，小数秒前面可以加句号()或冒号(:)。 这些符号有不同的含义。 句号表示标准分数; 因此，12:00:004表示十分之四秒，而12:00:00004表示千分之四秒。 冒号表示接下来的是千分之一秒; 因此12:00:00:4表示千分之四秒。 冒号后面的数字限制为3位。

在转换为整数数据类型或SQL_DOUBLE数据类型时，数据值(包括日期和时间)将转换为数字表示。 对于SQL_DATE，这是自1841年1月1日以来的天数。 对于SQL_TIME，这是自午夜以来的秒数。 当遇到非数字字符时，输入字符串将被截断。 整数数据类型还截断十进制数字，返回数字的整数部分。

{fn CONVERT(expression,datatype)}不支持流数据的转换; 指定要表达的流字段将导致SQLCODE -37错误。

转换成任何数据类型的NULL仍然是NULL。

空字符串(")，或任何非数字字符串值转换如下:

SQL_VARCHAR和SQL_TIMESTAMP返回提供的值。

数字数据类型转换为0(零)。

SQL_DATE和SQL_TIME转换为NULL。

CONVERT 类方法

还可以使用CONVERT()方法调用执行数据类型转换，使用" SQL_"关键字指定数据类型:

$SYSTEMSQLFunctionsCONVERT(expression,convert-to-type,convert-from-type)

如下示例所示:

WRITE $SYSTEMSQLCONVERT(60945,"SQL_VARCHAR","SQL_DATE")

2007-11-11

示例

CONVERT() 示例

下面的示例使用标量语法形式的CONVERT。

下面的示例比较了使用DECIMAL和DOUBLE数据类型对小数的转换:

SELECT CONVERT(DECIMAL,-1234567890000123456789) AS DecimalVal,

CONVERT(DOUBLE,-1234567890000123456789) AS DoubleVal

下面的示例将字符流字段转换为VARCHAR文本字符串。 它还使用CHAR_LENGTH显示字符流字段的长度:

SELECT Notes,CONVERT(VARCHAR(80),Notes) AS NoteText,CHAR_LENGTH(Notes) AS TextLen

FROM SampleEmployee WHERE Notes IS NOT NULL

下面的例子展示了几种将出生日期字段(DOB)转换为格式化字符串的方法:

SELECT DOB,

CONVERT(VARCHAR(20),DOB) AS DOBDefault,

CONVERT(VARCHAR(20),DOB,100) AS DOB100,

CONVERT(VARCHAR(20),DOB,107) AS DOB107,

CONVERT(VARCHAR(20),DOB,114) AS DOB114,

CONVERT(VARCHAR(20),DOB,126) AS DOB126

FROM SamplePerson

默认格式和代码100格式是相同的。 因为DOB字段不包含时间值，所以显示时间的格式(这里包括默认值100、114和126)提供一个零值，它表示12:00AM(午夜)。 代码126格式提供了一个不包含空格的日期和时间字符串。

{fn CONVERT()} 示例

下面的示例使用了ODBC语法形式的CONVERT。

下面的嵌入式SQL示例将混合字符串转换为整数。 IRIS在第一个非数字字符处截断字符串，然后将结果数字转换为规范形式:

ClassMethod Convert1()

{

s a="007 James Bond"

&sql(SELECT {fn CONVERT(:a, SQL_INTEGER)} INTO :x)

w !,"SQLCODE=",SQLCODE

w !,"the host variable is:",x

}

DHC-APP>d ##class(PHATESTSQLCommand)Convert1()

SQLCODE=0

the host variable is:7

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_TIMESTAMP数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_TIMESTAMP)} AS DOBtoTstamp

FROM SamplePerson

生成的时间戳格式为“yyyy-mm-dd hh:mm:ss”。

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_INTEGER数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_INTEGER)} AS DOBtoInt

FROM SamplePerson

下面的示例将“DOB”(出生日期)列中的日期转换为SQL_VARCHAR数据类型。

SELECT DOB,{fn CONVERT(DOB,SQL_VARCHAR)} AS DOBtoVChar

FROM SamplePerson

生成的字符串格式为:yyyy-mm-dd。

将值排序为字符串的排序规则函数。

%SQLSTRING 将表达式转换为按（区分大小写）字符串排序的格式。 %SQLSTRING 从字符串中去除尾随空格（空格、制表符等），然后在字符串的开头添加一个前导空格。这个附加的空格强制将 NULL 和数值作为字符串进行整理。从数字中删除前导零和尾随零。

因为 %SQLSTRING 将空格附加到所有值，所以它将 NULL 值整理为空格，字符串长度为 1 。 %SQLSTRING 将任何仅包含空格（空格、制表符等）的值整理为 SQL 空字符串( '' )。当 %SQLSTRING 将空格附加到空（零长度）字符串时，它会将空格加上空字符串的内部表示形式 $CHAR(0) 进行整理，从而导致字符串长度为 2 。

可选的 maxlen 参数在索引或整理时将表达式字符串截断为指定的字符数。例如，如果插入带有 maxlen 截断的字符串，则插入完整的字符串，并且可以通过 SELECT 语句检索；此字符串的全局索引被截断为指定长度。这意味着 ORDER BY 和比较 *** 作仅评估截断的索引字符串。这种截断对于对超过下标的最大字符长度的字符串进行索引特别有用。使用 maxlen 参数，如果需要对长字段进行索引，可以使用截断长度参数。

%SQLSTRING 在转换表达式后执行 maxlen 截断；如果 maxlen 超过转换后的表达式的长度，则不添加填充。最大字符串长度为 3,641,144 个字符； maxlen 没有强制执行最大值。

可以使用 %SYSTEMUtil 类的 Collation() 方法在 ObjectScript 中执行相同的排序规则转换：

也可以使用 SQLSTRING() 方法调用从 ObjectScript 调用此函数：

这两种方法都支持 SQLSTRING 转换后的截断。请注意，截断长度必须包括附加的空白：

注意：要将系统范围的默认排序规则从 %SQLUPPER （不区分大小写）更改为 %SQLSTRING （区分大小写），请使用以下命令：

发出此命令后，必须清除索引，重新编译所有类，然后重建索引。不要在其他用户访问表的数据时重建索引。这样做可能会导致查询结果不准确。

以下查询在 WHERE 子句中使用 %SQLSTRING 来执行区分大小写的选择：

默认情况下， %STARTSWITH 字符串比较不区分大小写。此示例使用 %SQLSTRING 格式使此比较区分大小写。它返回所有以 “Al” 开头的名称（例如 Allen 、 Alton 等）。请注意，使用 %STARTSWITH 时，应将 %SQLSTRING 排序规则应用于语句的两侧。

以下示例使用带有字符串截断的 %SQLSTRING 来返回每个名称的前两个字符。请注意，字符串截断是 3 （不是 2 ），因为 %SQLSTRING 添加了前导空格。 ORDER BY 子句使用这个两个字符的字段将行放在一个粗略的排序顺序中：

此示例在不更改字母大小写的情况下返回截断的值。

以下示例将 %SQLSTRING 应用于子查询：

把日期转成字符串

char(10)，表示转换后的字符串长度为10

rq,就是被转换的日期参数

21代表格式

convert的用法，及日期格式，见下：

语法

使用 CONVERT：

CONVERT (数据类型[长度], 表达式 [, 日期格式样式])

转换为日期型时可使用日期格式样式

不带世纪数位 (yy) 带世纪数位 (yyyy) 标准 输入/输出

- 0 或 100 () 默认值 mon dd yyyy hh:miAM（或 PM）

1 101 美国 mm/dd/yyyy

2 102 ANSI yymmdd

3 103 英国/法国 dd/mm/yy

4 104 德国 ddmmyy

5 105 意大利 dd-mm-yy

6 106 - dd mon yy

7 107 - mon dd, yy

8 108 - hh:mm:ss

- 9 或 109 () 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）

10 110 美国 mm-dd-yy

11 111 日本 yy/mm/dd

12 112 ISO yymmdd

- 13 或 113 () 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)

14 114 - hh:mi:ss:mmm(24h)

- 20 或 120 () ODBC 规范 yyyy-mm-dd hh:mm:ss[fff]

- 21 或 121 () ODBC 规范（带毫秒） yyyy-mm-dd hh:mm:ss[fff]

- 126() ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格）

- 130 科威特 dd mon yyyy hh:mi:ss:mmmAM

- 131 科威特 dd/mm/yy hh:mi:ss:mmmAM

select  ID,UserName，namer=（ case when score <= '50'） then  '实习'

                                                      when score > '50' and score <= '500'   then '村卫生员'

                                                      when score > '500' and score <= '1000'   then '村卫生员'

                                                      when score > '1000' and score <= '1500'  then '乡卫生员'

                                                       else  '健康大使'   end

from table

where

参数一：check_expression

               将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。

参数二：replacement_value

                在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。

返回类型：

                 返回与 check_expression 相同的类型。

注释：

           如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。

           例：ISNULL(Tsum, 0)

例如：a / b 但是b有可能是0，可以将表达式改为：

            case when b = 0 then 0 else   a/b   end

例如：a/b 但是a和b都是整型，那么就需要用到decimal进行转换

            case when b<>0 then convert(decimal(6,2))

例如：

          SELECT 1010/4    结果为2500000

          所以，如果我们要保留2位小数的话，语句如下：

          SELECT CAST (1010/4 AS DECIMAL (18,2))

① SELECT 可以在一条语句里对多个变量同时赋值，而SET只能一次对一个变量赋值

     例如：SELECT @A = 'Y',@B = 'M'

                而SET要达到同样的效果，需要：

                SET @A = 'Y'

                SET @B = 'M'

②表达式返回多个值时，用SET将会出错，而SELECT将取最后一个值

     例如：假设Table这个表中有多个IsRight记录

                SELECT @A = IsRight  FROM Table ---- 将取最后一个值

                SET @A = IsRight  FROM Table ---- 将报错

③表达式无返回值，用SET将置变量值为NULL，用SELECT交保持变量值

    以下假定Table记录为空

    SET @A = '初始值'

    SELECT @A = IsRight FROM Table  ---此时@A为'初始值'

    SET @A =（SELECT IsRight FROM Table）--此时@A为NULL

④使用标量子查询时，如果无返回值，SET和SELECT一样，都将置为NULL

    以下假定Table记录为空

    SET @A = '初始化'

    SELECT @A = （SELECT IsRight FROM Table） ---此时@A为NULL

    SET @A  = （SELECT IsRight FROM Table）  ---此时@A为NULL

例如：select from systemInfo as a

           select a

如图1所示的RANK结果列，有三位同学是100分的，位于第一名状态，93分的同学则是第4名的状态

如图1而DENSE_RANK()函数则不同，三名100分的同学还是位于第一名状态，93分的同学位于第2的状态

如图1对所有记录进行依次排序（1、2、3、4、5、、、）

如实际应用中需要进行分页处理，就会用到这个函数。

例子如下：一页显示20行数据

set @sql = @sql + ' select  ROW_NUMBER() OVER ( ORDER BY '

if(@sorts <> '')

begin

set @sql = @sql + ' ave desc, '

end

set @sql = @sql + ' FatDate desc,CreateDate desc ) as no, '

set @sql = @sql + ' ID, TourCode, LinesName, FatDate into #tempA’

//通过行号控制，这里只显示20行

set @sql = @sql + ‘select top（20）from #tempAwhere 1=1 ’

if（@rows > 0）

begin

set @sql  = @sql + ‘AND （no >’ + LTRIM(@rows) + ‘）’

end

如图1 ，对学生的成绩进行分组，NTILE(常量)，也就是说括号里面的常量是多少， 就分为几组。

SQL CHECK 约束：CHECK 约束用于限制列中的值的范围。

                                  如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

                                  如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

SQL DEFAULT 约束：DEFAULT 约束用于向列中插入默认值。

                                    如果没有规定其他的值，那么会将默认值添加到所有的新记录。

函数coalesce功能是返回参数中第一个非NULL值。

例如：coalese(boss,0) ：如果boss为空，则返回0；如果boss不为空，则返回boss

           coalese(boss,null,1)：如果boss为空，看下一个参数为null，则返回最后一个参数1；如果boss

                                                 不为空，则返回boss这个参数。

意思是name=apple，那么返回0；否则的话返回null

意思是 如果 bonus is null ， 那么返回 0， 否则返回 bonus

例：select ID from table where ID in （‘+ LTRIM（@ID）+’）

有时也需注意类型转换问题。

if（@dateS <> ‘’）

begin

set @sql = @sql + ‘AND （DATEDIFF(day，‘’‘+@dateS+’’’,FatDay) >=0）’

end

if（@dateE <> ‘’）

begin

set @sql = @sql + ‘AND （DATEDIFF(day，‘’‘+@dateE+’’’,FatDay) <=0）’

end

1、①年龄：

select YEAR(GETDATE()) - substring('IDCard',7,4) as Age

     ②年龄：（精确到天）

select  datediff( year, '1988-1-18' , getdate())  -  case when datediff ( day, getdate(),  dateadd( year, datediff( year, '1988-1-18', getdate()), '1988-1-18')) >= 0  then  1  else  0  end

2、性别：

case when len(身份z号) = 15 and cast(substring(身份z号,15,1) as int) % 2 = 0 then '女'

         when len(身份z号) = 15 and cast(substring(身份z号,15,1) as int) % 2 = 1 then '男'

         when len(身份z号) = 18 and cast(substring(身份z号,17,1) as int) % 2 = 0 then '女'

         when len(身份z号) = 18 and cast(substring(身份z号,17,1) as int) % 2 = 1 then '男'

else null end  as Sex

3、所属省份：

CASE LEFT(`card_id`,2) WHEN 11 THEN '北京' WHEN 12 THEN '天津' WHEN 13 THEN '河北' WHEN 14 THEN '山西' WHEN 15 THEN '内蒙古' WHEN 21 THEN '辽宁'

    WHEN 22 THEN '吉林' WHEN 23 THEN '黑龙江' WHEN 31 THEN '上海' WHEN 32 THEN '江苏' WHEN 33 THEN '浙江' WHEN 34 THEN '安徽' WHEN 35 THEN '福建' WHEN      36 THEN '江西'

    WHEN 37 THEN '山东' WHEN 41 THEN '河南' WHEN 42 THEN '湖北' WHEN 43 THEN '湖南' WHEN 44 THEN '广东' WHEN 45 THEN '广西' WHEN 46 THEN '海南' WHEN 50 THEN '重庆' WHEN 51 THEN '四川'

    WHEN 52 THEN '贵州' WHEN 53 THEN '云南' WHEN 54 THEN '西藏' WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃' WHEN 63 THEN '青海' WHEN 64 THEN '宁夏' WHEN 65 THEN '新疆' END AS 所属省份

一般写程序时会出现一个页面当中只显示固定多少行，这个时候sql语句中就会出现通过判断行号来实现

例如：

图3即是对行号的sql语句实现。

接下来说说页数的实现方法：

页数则是类似于网页上面分页功能的实现。

select a, b, case b when '申请人' then 0 else 1 end as row 

from table

where 。。。

order by row，date asc

select sum(atime-btime)

from(select ElogID,time from 表名 where EventSubType = 1) a,(select data,time from 表名 where EventSubType = 2) b

where aElogID=bElogID

未完待续。。。。

　本篇文章还是学习《程序员的SQL金典》内容的记录 此次将讲解的是SQL SERVER常用的其它函数 （其它数据库这里就不罗列了 想看更多的可以关注《程序员的SQL金典》）

　具体的其他函数包括 类型转换的函数 空值处理的函数 流程控制函数 SQL SERVER独有函数

　类型转换的函数

　CAST ( expression AS data_type)函数         CONVERT ( data_type expression)函数

　上面两个函数都是SQL SERVER提供的支持类型转换的函数 参数expression为待进行类型转换的表达式(即需要待转换的数据) 而data_type为转换的目标类型(即待转换后的类型)

　SELECTCAST(&# ; &# ; AS INTEGER) as i CONVERT(DECIMAL &# ; &# ;) as d CONVERT(DATETIME &# ; : : &# ;) as dt

　空值处理的函数

　COALESCE ( expression value value …… valuen)函数 处理空值问题的函数 返回包括expression在内的所有参数中的第一个非空表达式 其中expression为待检测的表达式 而其后的参数个数不固定 可以多个 如果expression不为空值则返回expression 否则判断value 是否为空 如果不为空值则返回value 否则判断value 是否为空 如果不为空值则返回value ……以此类推 如果COALESCE函数里的参数全为NULL就会出错了 因为COALESCE函数的功能其实就是为了避免出现不想要的NULL值

　SELECT FName FBirthDay FRegDay COALESCE(FBirthDay FRegDay &# ; &# ;) AS ImportDayFROM T_Person

　ISNULL(expression value)函数 这个函数也是空值处理的函数 是COALESCE( )函数的简化版 只支持两个参数 其中expression为待检测的表达式 如果expression不为空值则返回expression 否则判断value是否为空 如果不为空值则返回value 如果都为空 则返回空

　SELECT FBirthDay FRegDay ISNULL(FBirthDay FRegDay) AS ImportDayFROM T_Person

　NULLIF ( expression expression )函数 这个函数也是空值处理的函数 主要是判断两个表达式是否等价 如果等价 则返回空 如果不等价 侧返回第一个expression 的值 需要注意的意 第一个表达式expression 不能为空

　SELECT FBirthDay FRegDay NULLIF(FBirthDay FRegDay)FROM T_Person

　流程控制函数

　SQL SERVER提供了流程控制函数 类似于我们代码里面的的SWITCH……CASE语句 那就是CASE函数 这个函数有如下两种运用方法

　CASE函数的语法如下

　用法一:

　CASE expression WHEN value THEN returnvalue WHEN value THEN returnvalue WHEN value THEN returnvalue …… ELSE defaultreturnvalue END

　CASE函数对表达式expression进行测试 如果expression等于value 则返回returnvalue 如果expression等于value 则返回returnvalue expression等于value 则返回returnvalue ……以此类推 如果不符合所有的WHEN条件 则返回默认值defaultreturnvalue

　SELECT FName (CASE FName WHEN Tom THEN GoodBoy WHEN Lily THEN GoodGirl WHEN Sam THEN BadBoy WHEN Kerry THEN BadGirl ELSE Normal END) as isgood FROM T_Person

　用法二

　CASE WHEN condition THEN returnvalue WHEN condition THEN returnvalue WHEN condition THEN returnvalue …… ELSE defaultreturnvalue END

　其中的condition condition condition ……为条件表达式 CASE函数对各个表达式从前向后进行测试 如果条件condition 为真则返回returnvalue 否则如果条件condition 为真则返回returnvalue 否则如果条件condition 为真则返回returnvalue ……以此类推 如果不符合所有的WHEN条件 则返回默认值defaultreturnvalue

　SELECT FName FWeight (CASE WHEN FWeight< THEN thin WHEN FWeight> THEN fat ELSE ok END) as isnormal FROM T_Person

　SQL SERVER独有函数

　PATINDEX ( &# ;%pattern%&# ; expression )函数 不同于CHARINDEX()函数 只能计算字符串中指定表达式的开始位置 PATINDEX ( &# ;%pattern%&# ; expression )函数提供了更加灵活的方式 它返回指定表达式中模式&# ;%pattern%&# ;第一次出现的起始位置 如果在全部有效的文本和字符数据类型中没有找到该模式 则返回零 在模式中可以使用通配符

　SELECT FName PATINDEX(&# ;%_m%&# ; FName)FROM T_Person

　REPLICATE (str count)函数 用来得到一个由子字符串重复了若干次所组成的字符串 其中参数str为子字符串 而count为重复次数 （其实就是一个复制函数）

　SELECT FName FWeight CAST(FWeight/ AS INT) REPLICATE(FName CAST(FWeight/ AS INT))FROM T_Person

　REVERSE(expression)函数 将一个字符串的顺序颠倒

　SELECT FName REVERSE(FName)FROM T_Person

　ISDATE(expression)函数 用来确定输入表达式是否为有效日期 如果输入表达式是有效日期 那么ISDATE 返回 否则 返回 expression参数为要验证其是否为日期的表达式 expression可以是text ntext 表达式和image 表达式以外的任意表达式 可以隐式转换为nvarchar

　SELECT ISDATE(NULL) as d ISDATE( / / ) as d ISDATE( a ) as d ISDATE( ) as d ISDATE( / / ) as d ISDATE( ) as d ISDATE( ) as d ISDATE( Abc ) as d

　ISNUMERIC ( expression )函数 用来确定表达式是否为有效的数值类型 如果输入表达式的计算值为有效的整数 浮点数 money 或decimal 类型时 ISNUMERIC 返回 否则返回

lishixinzhi/Article/program/SQL/201405/30848