如何学习SQL.docx
《如何学习SQL.docx》由会员分享，可在线阅读，更多相关《如何学习SQL.docx（18页珍藏版）》请在冰点文库上搜索。
如何学习SQL
如何学习SQL
在论坛中不断看到有新人问一些基础的问题。
于是抽空整理了本帖，希望对新人有所帮助。
本文无意取代任何一本数据库或SQL的参考书，主要是提供一点学习方向的指导和技术心得的分享。
地图和指南针并不能代替其它的野营工具，经验和心得也只能来自于亲身走过的旅途。
本文主要以SQLServer2005的T-SQL为示例，但原理并不局限于这一平台。
由于全文过长，故拆分成四部分发帖：
TableofContent
第一部分：
SQL基础
1.为什么学习SQL
2.学习SQL的参考资料
3.几组基本概念
3.1.单机数据库与服务器级数据库
3.2.服务器（Server）与客户端（Client）
3.3.数据库（DB）与数据库管理系统（DBMS）
3.4.SQL与SQL方言（dialect）
3.5.语句、表达式和断言
4.SQL不同于一般编程语言的地方
4.1.SQL操作的是数据
4.2.SQL是基于集合的说明式语言
5.SQL的三个子集
第二部分：
从关系角度理解SQL
6.从关系角度理解SQL
6.1.关系和表
6.2.关系模型
6.3.关系运算
6.4.数据查询
6.5.数据修改
6.6.表的逻辑含义
第三部分：
SQL数据类型与三值逻辑
7.数据类型
8.NULL与三值逻辑
第四部分：
DBMS扩展功能与SQL高级话题
9.DBMS提供的扩展功能
9.1.控制流
9.2.动态语句
9.3.DBMS支持的数据库对象
9.4.DBMS提供的系统函数、系统视图和系统存储过程
9.5.DBMS提供的工具
10.高级话题
第一部分：
SQL基础
1.为什么学习SQL
自人类社会形成之日起，社会的运转就在不断地产生和使用各种信息（文献、档案、资料、数据等）；在如今所谓的信息时代，由于计算机和互联网的作用，信息的产生和使用达到前所未有的广度和深度。
如何管好和用好信息，是（而且将一直是）IT行业一块重要的领域。
在过去几十年中，关系数据库一直在这一领域占主导地位，而建立在关系理论基础之上的SQL也成为数据库领域的既定标准。
目前的数据存储领域可称为三分天下：
a.少量数据的存储：
自定义数据文件或通用数据文件（单机数据库），通过自定义接口或通用API访问数据。
如需要存储数据的单机软件或小型的动态网站。
b.对一致性要求高的大量数据的存储：
关系数据库管理系统（RDBMS）。
如各种传统的信息系统（ERP、CRM、HRMS、MIS等）。
c.对并发性要求高的大量数据的存储：
NoSQL数据库系统。
如Web2.0网站的后台数据系统。
以上，“狐假虎威”地借数据存储的重要性来阐述了一番学习SQL的伟大意义。
但重要的是，对SQL善于做什么和不善于做什么有个清楚的认识。
2.学习SQL的参考资料
不久前整理了数据库图书ABC一文，对数据库相关的参考资料作了粗略的分类。
对于初学者而言，可以结合着《数据库系统概念（第5版）》一书和某个DBMS平台的入门技术手册练习，自行寻找或构思一个小需求，建一个数据库，创建几个表和视图，练习写查询和修改语句。
基础理论和技术实践可以相互促进。
3.几组基本概念
3.1.单机数据库与服务器级数据库
单机数据库（如sqlite、Access等，Excel也勉强可以算是）是应用于单个计算机的数据库引擎，通常不具备网络连接功能，适用于小型应用；程序部署时，一般只需要附带数据文件即可。
有时也称作桌面数据库。
服务器级数据库（如Oracle、DB2、SQLServer、MySQL、PostgreSQL等）是具备网络连接功能、可作为单独数据库服务器的DBMS，适用于大型信息系统；程序部署时，需要专门安装相应的DBMS，甚至要单独进行数据库服务器的架构设计。
此类数据库是我们讨论的重点。
3.2.服务器（Server）与客户端（Client）
数据库服务器是运行在一台主机（Host）（或主机集群）上的服务程序，维护着一个或多个数据库，并通过网络连接响应数据库客户端提交的SQL语句。
数据库客户端是向数据库服务器发送查询请求的应用程序，可能是DBMS的GUI管理界面或命令行应用程序，也可能是前端的Web服务器。
数据库客户端和数据库服务器可能是在同一台主机上，但更多情况下则是位于不同的主机上，通过局域网访问。
例如对于SQLServer来说，一个服务器实例（Instance）即是一个数据库服务器，一台主机上可以安装多个服务器实例；而查询分析器或SSMS、sqlcmd、以及连接数据库服务器的IIS，都是数据库客户端。
比如你在SSMS中备份/还原/附加一个数据库，或是通过xp_cmdshell执行一个命令程序，所操作的都是你所连接的数据库服务器所在主机的文件，而不是你运行SSMS数据库客户端所在主机的文件。
一台主机有时候会被称作一台（操作系统）服务器，而数据库服务器和Web服务器都是运行在主机之上的应用服务器。
它们都被称作服务器，不要因此搞混了。
一个典型的基于SQLServer的网站系统的架构示例如下：
用户浏览器（WebClient）<---->IIS（WebServer/DBClient）<---->SQLServer（DBServer）
3.3.数据库（DB）与数据库管理系统（DBMS）
“数据库”这个词已经被滥用，可能用来指一个数据系统（如中国移动的号码数据库），可能用来指一种数据存储技术（如关系数据库和NoSQL数据库），还可能用来指DBMS（如人们常说SQLServer是数据库软件）。
这种混乱已然形成，恐怕难以改变（比如上文的描述即是如此）。
我们只能根据上下文来判断具体含义。
按最狭义的技术含义，数据库（Database）是指位于一个数据库服务器实例上的一个库，而DBMS则是指类似SQLServer、Oracle等等此类软件。
初学者要注意这些概念之间的差别。
论坛上常常见到这样的帖子：
“连不上数据库”、“数据库打不开了”，又没有上下文，可见发问者概念混乱，搞得解答者也是一头雾水。
3.4.SQL与SQL方言（dialect）
SQL是一个关系数据库查询语言的标准，而SQL方言则是各种DBMS在SQL标准上进行的扩展，如增加新的关键字、查询功能、特有的数据类型、支持过程化的控制流语句等。
例如SQLServer的T-SQL和Oracle的PL/SQL都是常见的SQL方言。
这就好比ANSIC标准与各种编译器实现的C语言的差别。
但不同SQL方言之间的差异远大于不同C编译器之间的差异。
SQL方言之间的差异，对于跨DBMS的学习和开发，都是必须注意的。
3.5.语句、表达式和断言
语句（statement）是SQL中一个可以单独执行的单元。
如SELECT*FROMtable;即是一个语句，其中包含了SELECT子句（clause）和FROM子句。
SQL标准规定用分号作为语句的结束，但在目前的T-SQL中，语句结束的分号是可选的。
表达式（expression）是SQL中的一个值（可能是变量、常量、查询字段或计算结果），对应一种特定的数据类型。
SQL中的表达式分为标量表达式和表值表达式，其中表值表达式作为单独语句则是SELECT语句，作为语句的一部分则称为子查询。
比如0,col+2,DATEADD（second,30,GETDATE（））都是（标量）表达式。
需要特别说明的是，SQL中的CASEWHEN是标题表达式，而不是条件语句。
比如CASEWHEN中可以使用表达式，却不能使用语句；CASEWHEN的结果是一个特定数据类型的标量值；CASEWHEN可以用在SELECT、GROUPBY或ORDERBY子句中，但IFELSE则不行。
断言（predicate）是SQL中进行比较的结果，即真值，可理解为布尔表达式，因为SQL中没有bool数据类型，所以将断言特别从表达式中区分出来。
比如1是一个标量表达式，而1=1则是一个断言，后者可以用在WHERE、ON、HAVING、CHECK等需要真值条件的地方，但前者则不可以。
由于NULL的存在，SQL中的断言是三值逻辑，即True/False/Unknown，详见下文“NULL与三值逻辑”。
下文中多次用到计算和比较两个词。
表达式和表达式进行计算，结果是新的表达式；表达式和表达式进行比较，结果是一个断言；断言和断言可以进行逻辑运行（AND/OR/NOT），结果是新的断言。
注意其中的区别。
4.SQL不同于一般编程语言的地方
4.1.SQL操作的是数据
SQL是数据库的查询语言，因而可以对系统数据产生持久化影响。
在常规编程中，一个错误通常只会造成程序的crash或bug，修改并重新调试往往就可以了；而在SQL中，一个不小心就可能造成系统数据的破坏和丢失。
常常有新手执行SQL时，不小心遗漏了DELETE或UPDATE语句中的WHERE子句，这往往是很大的麻烦。
因此，学习和使用SQL，一开始应该养成两个习惯：
1. 细心。
在执行SQL语句认真检查一下，要清楚自己在做什么。
2. 及时备份，并考虑对系统的元数据进行版本控制。
为偶尔的意外准备好后悔药。
常见的可能造成破坏性影响的SQL关键词：
DELETE,UPDATE,DROP,TRUNCATETABLE。
4.2.SQL是基于集合的说明式语言
SQL擅长集合操作，而不是循环。
所谓说明式语言，你只需要告诉SQL需要做什么，而不是怎么做。
初学SQL的人，思维往往难免带有过程式语言的痕迹，解决问题时常常不由地考虑循环。
在学习SQL编程时，每当想要用循环时，先反问一下自己：
这个问题是不是必须用循环来解决？
事实上，多数情况下，这个问题的答案是否定的。
有本书中讲，SQL代码中出现一次IF便要减一些分（比如-1），出现一次WHILE便要减很多分（比如-10或-50，不夸张）。
可以尝试用这样的方法为自己的存储过程打分。
5.SQL的三个子集
SQL从功能上可以划分为三个子集：
1.DML（DataManipulationLanguage）:
数据操纵语言，是对数据进行查询和修改（增、删、改）操作的语言。
包含语句：
SELECT+INSERT/DELETE/UPDATE
使用对象：
DBUser
2.DDL（DataDefinitionLanguage）:
数据定义语言，是对域（数据类型）和关系（表）及其它数据库对象进行定义的语言。
包含语句：
CREATE/DROP/ALTER
使用对象：
DBDesigner/Developer
3.DCL（DataControlLanguage）:
数据控制语言：
是对数据的访问进行权限控制的语言。
包含语句：
GRANT/DENY/REVOKE
使用对象：
DBA
补充说明：
-严格地说，DML只包含对数据进行修改的语句（INSERT/DELETE/UPDATE），但SELECT语句与DML关系紧密、形式类似，故通常放在一起。
既可以统称为DML，也可以并称为Query+DML。
-以上三个子集不包含BACKUP/RESTORE语句。
第二部分：
从关系角度理解SQL
6.从关系角度理解SQL
6.1.关系和表
众所周知，我们目前所用的数据库，通常都是关系数据库。
关系自然在其中处于关键位置。
初学数据库原理的人可能会很困惑关系和表是什么联系，如果没有清楚的理解，很可能会认为关系这个概念没有实际意义，只会引起混淆。
其实这两组概念只是由于理论界与技术界的着重点不同。
前者需要用一个专业的、没有歧义的概念来进行理论探讨，后者则希望在实际应用中能够使用一个直观的、容易理解的词汇。
通常情况下，可以认为关系和表是一回事。
就定义来说：
关系是元组（即表的记录，或行）的集合。
此外，关系还有以下特征：
-关系含有一组属性（即表的字段，或列），含有N个属性的关系可称为N元关系。
-一个关系的元组含有与关系相同的属性，N元关系的元组都是N元组，一个元组中对应每个属性有一个值。
-一个属性的域（即字段的数据类型，但域的要求更严格，详见下文“数据类型”），即该属性所有可能的值的集合。
从这里可以看出关系和表的区别：
关系作为一种集合，不会包含重复元组；而表则可以包含重复记录。
这是SQL面对的诸多指责之一，但有其技术合理性。
这里的区别在理解上影响不大，不妨把表理解为“可能（但通常不应该）重复的集合”。
注意到这点区别，以下我们便可以对关系和表不加区别的使用了。
另外，这里的关系和表，指的是所有表值的东西，包含物理表、虚拟表（视图）、派生表（一个用在FROM子句的子查询）、表变量、表值函数、等等。
它们在物理上有区别，但在逻辑上是等价的。
6.2.关系模型
数据库建模（即表结构设计）的过程，是根据现实世界的业务需求，设计一个表示和存储业务数据的关系数据模型。
在设计过程中可以借助E-R模型来简化问题，因为E-R模型可以更直观地对应于现实世界，也可以很容易地转化为关系模型。
对于熟练的设计者，可以省略E-R模型，直接构建关系模型。
而关系模型在关系数据库中基本上可以直接表示，所以关系模型与物理模型差别不大。
物理模型通常只是根据需要添加必要的索引，或是将概念上的表在物理上映射为分区视图或分区表。
以上几个模型的关系见下图：
简单总结一下关系模型设计中的两个要点：
1. 完整性约束（Integrityconstraint）：
完整性约束保证数据的一致性（符合基本条件），包含以下3种类型：
-实体完整性（主键约束）：
一个表的主键不能为空。
-参照完整性（外键约束）：
一个表的外键必须存在于所参照的表中。
-自定义完整性（CHECK约束，UNIQUE约束）：
即表中的数据不能违反约束定义的条件（不能使CHECK的表达式为False，不能使UNIQUE约束的字段或字段组合出现重复值）。
完整性约束定义了系统概念模型的边界，很大程度上防止了脏数据进入系统，这是非常重要的，因为脏数据往往比没有数据还要讨厌（这与“错误的观点胜过没有观点”恰恰相反）。
在设计表结构时，外键、CHECK、UNIQUE约束或许可以适当省略（出于运行性能和开发效率的考虑，并且相信表数据只有统一存储过程修改，不会出现脏数据），但主键通常是一定需要的。
主键不仅意味着可以高效查询（因为目前DBMS的主键通常都是通过B+树聚集索引实现的），更重要的是清楚地说明了表中数据的唯一标识是什么。
（目前我只发现一种不需要主键的情况：
日志表——同一时刻可能有多笔记录，所以datetime不能作为主键；而一个递增的LogID也没有太大实际意义，参看关于聚集索引选择方案的疑问一帖。
）
关于主键的选择方案，详见一个基础问题一帖。
给我看表的数据样本，以及（可能）过时的数据字典和程序文档，我仍然迷惑不解。
如果给我看完整的表的定义（要包括各种完整性约束，特别是主键），通常就不需要查看表中的数据样本了，甚至连文档也可以省去。
（这两句话借鉴了Brooks在《人月神话》一书中的话。
参见《UNIX编程艺术》1.6节脚注。
）
2. 范式（NormalForm,NF）：
范式是一组关系（表）设计的原则，通过避免冗余防止出现数据的更新异常（即DRY原则的体现）。
在实践上常用的是以下3个层次的范式：
-1NF：
表中的字段都是原子的。
-2NF：
表中的所有字段都可以由主键唯一决定（函数依赖）。
-3NF：
除完整主键以外，其它字段（包括部分主键）之间不存在决定关系（函数依赖）。
首先说明一下1NF的“原子的”。
这个“原子的”是指业务需求不需要对这个值进行拆分（没有前提条件，“拆分”一词是有多种解释的，如字符串可以拆分成字符，整数可以拆分成二进制的位串或素因子的乘积）。
例如，城区、街道、门牌号是地址的三部分，如果地址只是作为一个记录，不需要更细粒度的处理，则可以将三部分存在一个字段；如果需要根据城区进行查询和分组统计，则至少需要把城区作为一个单独的字段。
所以，一个字段是不是“原子的”必须根据业务需求这个条件来定义。
实践中业务需求是会变化的，因而系统设计还需要一定的前瞻性。
目前一个原子的字段可能随着需求变化而不再是原子的。
范式给出了一组表应该怎样设计的原则，但没有说明如何把表设计成这样。
数据库理论上的关系范式分解过于抽象，以下是一点实用性的思路：
- 1NF：
让表中的每个字段都不需要拆分处理（至少不需要太复杂的拆分处理）。
如姓名的结构很简单，通常不需要设计成姓和名两个字段，但如果是一个国际化的系统，不同文化中姓名的结构可以不同，这时则最好把LastName和FirstName分开存放，比如Facebook、Twitter等网站的设计。
- 2NF：
给表定义主键。
参看上文关于实体完整性的讨论。
- 3NF：
不要在同一个表中存放相关数据或派生数据，只存放主要数据，其它数据通过联接查询或计算获得。
如不要在员工表中同时存放部门ID和部门名称（相关数据）或同时存放出生年月和年龄（派生数据），其中部门ID或出生年月是主要数据，部门名称可以通过联接查询获得，年龄可以通过计算获得。
有些情况下出于结构的直观或查询性能的考虑，可能会需要反范式的设计。
如在一个字符串字段中存放逗号分隔的多个值（形如'1,2,3,5,8'，违反1NF），或是在一个表中同时存放相关数据或派生数据来避免联接或计算开销（比如同时存放部门ID和部门信息来避免联接部门表，或同时存放员工各项薪酬福利和总薪资来避免复杂的薪资计算，违反3NF）。
反范式的设计会带来复杂的查询处理或冗余，更好的方案是基本数据用符合范式的表存储，通过统一的过程来计算和刷新缓冲表来提高查询时的性能，参看《程序员修炼之道》第7节关于DRY原则的讨论。
6.3.关系运算
表的查询，与关系代数（RelationalAlgebra）定义的关系运算是等价的。
理解关系运算，或许可以简化对查询的认识。
常用的基本关系运算只有4类（够简单吧）：
1.基本的集合运算（双目运算）
关系是元组的集合，所以关系也支持基本的集合运算：
-并（union）：
对应SQL关键字UNION
-交（intersection）：
对应SQL关键字INTERSECT
-差（difference）：
对应SQL关键字EXCEPT
不同的是，关系的集合运算，要求参与运算的两个关系必须含有相同属性集（属性的个数和类型都一样）。
由于表允许重复记录，在SQL中以上三种操作还可以是UNIONALL/INTERSECTALL/EXCEPTALL的形式，结果不去除重复记录。
2.提取关系的一部分的运算（单目运算）
-选择（selection）：
根据条件过滤出指定的元组（行），对应SQL查询的WHERE子句
-投影（projection）：
根据列表过滤出指定的属性（列），对应SQL查询的SELECT子句
由于表允许重复记录，关系的投影运算事实上等价于SELECTDISTINCT的效果。
而SELECT的默认效果是不去除重复记录。
3.两个关系的联接（双目运算）
-笛卡尔积（Cartesianproduct）：
对应SQL关键字CROSSJOIN（与FROM后的多个表直接用逗号分隔效果相同）
-内联接（InnerJoin）：
对应SQL关键字INNERJOIN
-外联接（OuterJoin）：
对应SQL关键字{LEFT|RIGHT|FULL}OUTERJOIN
4.聚合运算（单目运算）
根据指定属性（列）分组，同时可以使用聚合函数。
对应SQL查询的GROUPBY子句。
以上4类关系运算，不管是单目运算还是双目运算，其结果依然是一个关系，因而可以继续进行运算。
通常情况下的SQL查询，除一些特殊的语言特性外（如TOP、排序函数等），主要的查询逻辑都是这4类关系运算的组合。
6.4.数据查询
1.查询的逻辑处理过程
以T-SQL为例，一个查询（完整SELECT语句）的逻辑处理过程如下（其中括号中的数字表示处理顺序）：
[code=sql]
（8）SELECT（9）DISTINCT（11）
（1）FROM
（3）JOIN
（2）ON
（4）WHERE
（5）GROUPBY（6）WITH{CUBE|ROLLUP}
（7）HAVING
（10）ORDERBY
[/code]
说明：
+有些子句是可选的。
比如JOIN可能出现0到多次，GROUPBY和HAVING可能出现0到1次。
+从以上顺序可以看出，为何在WHERE子句不能使用SELECT的计算结果，但在ORDERBY子句却可以。
+查询的逻辑处理过程与物理处理过程可能并不相同。
但对于SQL的学习来说，先理解逻辑处理过程是必须的。
先要知道怎样计算出正确的结果，才谈得上怎样更高效地计算出正确的结果。
该内容详见《MicrosoftSQLServer2005技术内幕：
T-SQL查询》第1章。
2.查询条件
在SQLServer联机丛书中，查询条件的BNF语法图如下：
[code=sql]
SearchCondition
:
:
=
{[NOT]|（）}
[{AND|OR}[NOT]{|（）}]
[,...n]
:
:
=
{expression{=|<>|!
=|>|>=|!
>|<|<=|!
<}expression
|string_expression[NOT]LIKEstring_expression[ESCAPE'escape_character']
|expression[NOT]BETWEENexpressionANDexpression
|expressionIS[NOT]NULL
|expression[NOT]IN（subquery|expression[,...n]）
|expression{=|<>|!
=|>|>=|!
>|<|<=|!
<}{ALL|SOME|ANY}（subquery）
|EXISTS（subquery） }
|CONTAINS（
升级会员 