`
womendu
  • 浏览: 1474702 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQL数据类型与三值逻辑

阅读更多

数据类型

在数据库理论中,关系模型和数据类型这两部分内容是正交的(参看《程序员修炼之道》第8节关 于“正交性”的讨论),互不依赖。换言之,关系模型并不关心每个表的字段的数据类型是什么,是整数、字符串等基本类型也好,是组合类型、类等自定义类型也 好,关系模型只要求每个字段是原子的。

在数据库理论中,数据类型又被称为域,但域是更为严格的定义。比如一个班级的学生个数和学生平均分 可能都是int类型,但这是两个不同的域,“学生个数 * 学生平均分 = 学生总分”,但“学生个数 + 学生平均分”是没有意义的。目前的主流DBMS似乎尚未对域有很好的支持,但未来的情况可能会有所改变,而且,设计自定义类型也需要对这一问题有充分的认 识。详见《深度探索关系数据库》第2章。

对于数据库和SQL的应用来说,除掌握关系模型的原理,还需要对DBMS支持的数据类型及其转换规则有所认识。

1. 基本数据类型

一个DBMS通常都会支持以下几类基本数据类型(以SQL Server为例):
- 精确数字:整数(bigint/int/smallint/tinyint/bit),定点小数(decimal),货币(money/smallmoney)
- 近似数字:浮点数(float)
- 日期和时间:datetime/smalldatetime; date, time, datetime2, datetimeoffset(后4种为SQL Server 2008的新增类型)
- 字符串和Unicode字符串:varchar/nvarchar, char/nchar(text/ntext已不再建议使用,用varchar(max)/nvarchar(max)代替)
- 二进制串(即字节流):varbinary, binary(image已不再建议使用,用varbinary(max)代替)
- 其他数据类型:具有特殊功能的类型(sql_variant, timestamp, uniqueidentifier, xml),不能用于表的特殊类型(cursor, table)

2. 关于数据类型 需要注意的问题

a. 两类特殊的数据类型
- 日期和时间类型 的数据存储方式和可用值范围、相关的计算、比较、显示(转换为指定格式的字符串)都比较复杂,还涉及一组日期时间函数。参看datetime类型分析 一帖。
- 字符串类型 涉及到字符编码和排序规则,比较操作还包含LIKE匹配(未来还可能会支持正则表达式匹配),非常需要注意。参看理解字符编码SQLServer中文处理 二帖。

b. 如果对不同排序规则的两个字符串进行计算或比较,将会根据排序规则优先顺序 来决定计算结果的排序规则或比较的方法。

c. 如果对不同类型的两个值进行计算或比较,将会根据数据类型优先级 进行隐式转换 。数据类型优先级基本规则如下:
- 大 > 小(>指优先级高于,下同):如bigint > int > smallint > tinyint > bit,varchar(20) > varchar(10),datetime > smalldatetime,等等。
- 可变 > 固定:如float > decimal,varchar > char,nvarchar > nchar,varbinary > binary。
- 各类型大类的优先级:datetime > float > decimal > integer > unicode string > ansi string > binary。
- 特殊数据类型的优先级和转换规则需要特殊考虑,详见联机丛书。

d. 如果对不同大小的两个值进行计算,将会根据精度、小数位数和长度 的规则来产生新的类型大小。

e. 常量 的数据类型(可以通过SELECT col = 常量值 INTO testdt然后查看testdt表col字段的数据类型来观察)
- 如果不显式指定和隐式转换,NULL会按int类型处理。
- '', N'', 1, 0x01, 1.0, 1E0, $1分别对应varchar, nvarchar, int, varbinary, decimal, float, money类型,并且长度是存储相应值所需要的最小长度。

f. 在软件开发领域众所周知:“隐式转换是bug的源泉” 。因此,有两个建议:
- 使用常量时,最好使用对应类型的常量。 比如,如果table.col是varchar类型,那么WHERE table.col = 10的查询将不能使用索引,而且当遇到col中存放有不能转换为数字的值时将出错。
- 除非相应值的隐式转换非常直观,否则宁可用CAST()/CONVERT()指定明确的显式转换

以上内容中,加下划线的粗体是联机丛书的标题。详细分析参看《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》第1章。


8. NULL与三值逻辑

三值逻辑(3VL, Three-valued Logic)绝对是SQL修炼中的一个紧要关卡,值得特别注意,闭关静修。待冲破这一关卡之后,SQL中的NULL与NOT NULL将别无二致。

关于SQL是否应该允许NULL,在数据库领域已经近乎一个信仰式的争论。E.F.Codd认为NULL有存在的必要,但他的好友C.J.Date认为NULL完全可以取消。最终结果是,SQL标准支持NULL。

理论上的争论且不管。但在实践中,一定要知道NULL的三值逻辑会带来很多困扰的问题。
a. 不使用NULL的理由:
- NULL会引入复杂的三值逻辑。
- NULL在查询条件、外键和CHECK约束、唯一约束、GROUP BY、ORDER BY中的行为都是不一致的。
b. 使用NULL的理由:
- 当需要表示一个未知的、不确定的值时,用NULL更自然。比如一个现在职员工的离职时间、顶级员工(BOSS)的上级员工,等等。
- 外联接通常会引入NULL,即使所有表的字段都定义为NOT NULL。

首先,如果可能,尽量让所有字段都声明为NOT NULL 。除非是更适合使用NULL的场合(从业务出发)。

其次,在使用NULL时,一定要搞清楚三值逻辑和数据库引擎对NULL的处理
(SQLServer有一个选项SET ANSI_DEFAULTS,默认为ON,即与SQL标准一致。设为OFF的效果详见联机丛书。)

1. NULL与别的值进行+-*/等计算操作(包括在大多数函数中使用NULL)后,结果是NULL(标量表达式)。NULL与别的值进行=、>、<等比较操作后,结果是Unknown(断言)。
Unknown相关的逻辑运算:
[code=sql]
NOT Unknown --> Unknown
Unknown AND/OR Unknown --> Unknown
Unknown OR TRUE --> TRUE
Unknown AND TRUE --> Unknown
Unknown OR FALSE --> Unknown
Unknown AND FALSE --> FALSE
[/code]
具体可查三值逻辑的真值表。

2. 在where/on/having和if/case when中,只有True才使条件成立(即Unknown当作False来处理)。比如:
where column = value:表中column为NULL的行永远不会返回,即使value是NULL;
case value when NULL then XXX when ... end:XXX永远不会执行,即使value是NULL;
if <Unknown> XXX else YYY end或case when <Unknown> then XXX else YYY end:这两种情况下,YYY会执行。

3. 包含外键约束和Check约束的字段允许NULL(即约束只当条件为False时出错,Unknown是不管的)。
4. 包含唯一约束(unique index)的字段只允许一个NULL的行,再插入或更新该字段为NULL的行会报字段重复的错误。
5. GROUP BY时,所有NULL被视为一组。
6. ORDER BY时,所有NULL排在一起,但NULL排在非空值的前面(如SQL Server)还是后面(如Oracle),SQL标准未规定。
7. 聚集函数(SUM/AVG/MAX/MIN/COUNT)忽略NULL的行。
8. declare的变量,在未赋值之前为NULL。
9. 与NULL处理相关的运算符和函数:
- IS NULL/IS NOT NULL:用这两个运算符来判断一个值是否为NULL,而不是=或<>。
- ISNULL/COALESCE:取第一个非空值(注意两个函数的数据类型转换规则不同)。
- NULLIF(a,b):等价于CASE WHEN a = b THEN NULL ELSE a END。

1
1
分享到:
评论

相关推荐

    SQL Server存储过程中使用表值作为输入参数示例

    在2008之前如果我们想要将表作为输入参数传递给SQL Server存储过程使比较困难的,可能需要很多的逻辑处理将这些表数据作为字符串或者XML传入。 在2008中提供了表值参数。使用表值参数,可以不必创建临时表或许多参数...

    Microsoft SQL Server 2008技术内幕:T-SQL查询(第二卷)

    10.3.3 更新大值数据类型 10.3.3 用SELECT和UPDATE语句进行赋值 10.4 合并数据 10.4.1 MERGE语句基础 10.4.2 额外增加一个谓词 10.4.3 多个WHEN子句 10.4.4 WHEN NOT MATCHED BY SOURCE子句 10.4.5 MERGE ...

    SQLSserver2008 上

    8、SQL 2008视频教程-常用数据类型 ? 9、SQL 2008视频教程-常用数据类型2 ? 10、标识种子和标识自增量 ? 11、SQL 2008视频教程-字段默认值 ? 12、利用T-SQL语句创建数据库 ? 13、利用T-SQL语句更改数据库...

    收获不止SQL优化

    第6章 且慢,体验逻辑结构让SQL飞 132 6.1 逻辑结构 132 6.2 体系细节与SQL优化 133 6.2.1 Block 133 6.2.2 Segment与extent 137 6.2.3 Tablespace 139 6.2.4 rowid 139 6.3 相关优化案例分析 140 6.3.1 块...

    实验一 数据定义与简单查询实验

    要求:① 对每个属性选择合适的数据类型;② 定义每个表的主码、是否允许空值和默认值等列级数据约束;③ 对每个表的名字和表中属性的名字尽可能用英文符号标识。 4、实现相关约束:①使用企业管理器来建立上述三...

    数据库图书信息管理数据库SQL语句.pdf

    2、 熟悉 SQL Server2005 中的数据类型; 3、 熟悉使用 SQL 语句创建和删除模式和索引; 4、 掌握使用 SQL 语句创建、修改和删除数据表; 5、 掌握使用 SQL 语句查询表中的数据; 6、 掌握使用 SQL 语句插入、修改...

    SQL Server 2008数据库设计与实现

    这个PDF文件是我花钱买来的,现在为了挣积分,拿出来与大家分享!! 本书深入浅出地介绍了目前世界上最受欢迎的数据库管理系统之一——SQL Server。全书共分三个部分:第一部分阐释了数据库...附录B 标量数据类型参考

    程序员的SQL金典.rar

     1.2.4 数据类型(DataType)  1.2.5 记录(Record)  1.2.6 主键(PrimaryKey)  1.2.7 索引(Index)  1.2.8 表关联  1.2.9 数据库的语言——SQL  1.2.10 DBA与程序员 第2章 数据表的创建和管理  2.1 数据...

    Oracle_PLSQL语言基础

    PL/SQL支持所有SQL数据类型和所有SQL函数,同时支持所有ORACLE对象类型  . PL/SQL块可以被命名和存储在ORACLE服务器中,同时也能被其他的PL/SQL程序或SQL命令调用,任何客户/服务器工具都能访问PL/SQL程序,具有...

    达梦数据库_SQL语言手册

    数据类型是可表示值的集。值的逻辑表示是字值。值的物理表示依赖于实现。系统具 有 的绝大部分数据类型,以及部分 和 的数据类型。 常规数据类型 字符数据类型 类型 语法:长度 功能: 数据类型指定定长字符...

    SQL21日自学通

    逻辑数据类型378 ROWID379 PL/SQL 块的结构379 注释380 DECLARE 部分380 变量声明380 常量定义381 指针定义381 %TYPE 属性382 %ROWTYPE 属性382 %ROWCOUNT 属性383 Procdure 部分383 BEGIN … … END383 指针控制...

    Microsoft SQL Server 2005技术内幕: T-SQ程序设计.pdf

    本书涵盖了T-SQL程序设计的方方面面,如基于集合的编程技术、日期和时间相关的XML和CLR数据类型的使用、临时对象、T-SQL和CLR用户自定义函数、存储过程、触发器、事务和新的错误处理结构、应用并发模型支持并发用户...

    程序员的SQL金典4-8

     1.2.4 数据类型(DataType)  1.2.5 记录(Record)  1.2.6 主键(PrimaryKey)  1.2.7 索引(Index)  1.2.8 表关联  1.2.9 数据库的语言——SQL  1.2.10 DBA与程序员 第2章 数据表的创建和管理  2.1 数据...

    SQLServer2008技术内幕T-SQL查询包含源代码及附录A

    10.3.3 更新大值数据类型461 10.3.3 用SELECT和UPDATE语句进行赋值462 10.4 合并数据465 10.4.1 MERGE语句基础467 10.4.2 额外增加一个谓词470 10.4.3 多个WHEN子句471 10.4.4 WHEN NOT MATCHED BY SOURCE子句472 ...

    SQL入门常见问题总结与实用技巧介绍.docx

    数据类型与查询优化 适当的数据类型选择: 根据实际数据范围和精度选择合适的数值类型。 避免过度使用TEXT/BLOB类型,可能影响索引和查询速度。 数据过滤: 使用谓词推导和SARGable表达式来提升查询筛选性能。 SQL...

    收获,不止SQL优化--抓住SQL的本质

    第6章 且慢,体验逻辑结构让SQL飞 132 6.1 逻辑结构 132 6.2 体系细节与SQL优化 133 6.2.1 Block 133 6.2.2 Segment与extent 137 6.2.3 Tablespace 139 6.2.4 rowid 139 6.3 相关优化案例分析 140 6.3.1 块...

    精通sql结构化查询语句

    1.5 SQL开发环境 1.5.1 SQL环境介绍 1.5.2 SQL的层次结构 1.5.3 SQL环境中的对象 1.5.4 SQL环境中的程序系统 1.6 SQL语句基础 1.6.1 SQL常量 1.6.2 SQL表达式 1.6.3 SQL数据类型 1.6.4 注释符 1.6.5 批处理 1.6.6 ...

    Microsoft+SQL+Server+2008技术内幕:T-SQL查询_源代码及附录 中文版

    10.3.3 更新大值数据类型461 10.3.3 用SELECT和UPDATE语句进行赋值462 10.4 合并数据465 10.4.1 MERGE语句基础467 10.4.2 额外增加一个谓词470 10.4.3 多个WHEN子句471 10.4.4 WHEN NOT MATCHED BY SOURCE子句...

    SQL_Sever数据库语句大全.zip

    SQL_Sever数据库语句大全.zip 具体SQL语句实例如下 获取当前日期的星期 获取某个字段排序的行号 ...And逻辑值使用 Between And查询区间数据 Case When判断语句 Cast数值转文本函数 distinct去重 等等

    经典SQL语句大全

    DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 7、说明:添加主键: Alter table tabname add primary key(col) 说明:删除主键: Alter table tabname drop primary key(col) 8、说明...

Global site tag (gtag.js) - Google Analytics