如何学习SQL.docx

1、如何学习SQL第一部分：SQL基础1. 为什么学习SQL自人类社会形成之日起，社会的运转就在不断地产生和使用各种信息（文献、档案、资料、数据等）；在如今所谓的信息时代，由于计算机和互联网的作用，信息的产生和使用达到前所未有的广度和深度。如何管好和用好信息，是（而且将一直是）IT行业一块重要的领域。在过去几十年中，关系数据库一直在这一领域占主导地位，而建立在关系理论基础之上的SQL也成为数据库领域的既定标准。目前的数据存储领域可称为三分天下：a. 少量数据的存储：自定义数据文件或通用数据文件（单机数据库），通过自定义接口或通用API访问数据。如需要存储数据的单机软件或小型的动态网站。b. 对一致

2、性要求高的大量数据的存储：关系数据库管理系统(RDBMS)。如各种传统的信息系统（ERP、CRM、HRMS、MIS等）。c. 对并发性要求高的大量数据的存储：NoSQL数据库系统。如Web2.0网站的后台数据系统。以上，“狐假虎威”地借数据存储的重要性来阐述了一番学习SQL的伟大意义。但重要的是，对SQL善于做什么和不善于做什么有个清楚的认识。2. 学习SQL的参考资料不久前整理了数据库图书ABC一文，对数据库相关的参考资料作了粗略的分类。对于初学者而言，可以结合着数据库系统概念(第5版)一书和某个DBMS平台的入门技术手册练习，自行寻找或构思一个小需求，建一个数据库，创建几个表和视图，练习写

3、查询和修改语句。基础理论和技术实践可以相互促进。3. 几组基本概念3.1. 单机数据库与服务器级数据库单机数据库（如sqlite、Access等，Excel也勉强可以算是）是应用于单个计算机的数据库引擎，通常不具备网络连接功能，适用于小型应用；程序部署时，一般只需要附带数据文件即可。有时也称作桌面数据库。服务器级数据库（如Oracle、DB2、SQL Server、MySQL、PostgreSQL等）是具备网络连接功能、可作为单独数据库服务器的DBMS，适用于大型信息系统；程序部署时，需要专门安装相应的DBMS，甚至要单独进行数据库服务器的架构设计。此类数据库是我们讨论的重点。3.2. 服务器

4、(Server)与客户端(Client)数据库服务器是运行在一台主机(Host)（或主机集群）上的服务程序，维护着一个或多个数据库，并通过网络连接响应数据库客户端提交的SQL语句。数据库客户端是向数据库服务器发送查询请求的应用程序，可能是DBMS的GUI管理界面或命令行应用程序，也可能是前端的Web服务器。数据库客户端和数据库服务器可能是在同一台主机上，但更多情况下则是位于不同的主机上，通过局域网访问。例如对于SQL Server来说，一个服务器实例(Instance)即是一个数据库服务器，一台主机上可以安装多个服务器实例；而查询分析器或SSMS、sqlcmd、以及连接数据库服务器的IIS，都

5、是数据库客户端。比如你在SSMS中备份/还原/附加一个数据库，或是通过xp_cmdshell执行一个命令程序，所操作的都是你所连接的数据库服务器所在主机的文件，而不是你运行SSMS数据库客户端所在主机的文件。一台主机有时候会被称作一台（操作系统）服务器，而数据库服务器和Web服务器都是运行在主机之上的应用服务器。它们都被称作服务器，不要因此搞混了。一个典型的基于SQL Server的网站系统的架构示例如下：用户浏览器(Web Client) IIS(Web Server/DB Client) SQL Server(DB Server)3.3. 数据库(DB)与数据库管理系统(DBMS)“数据库

6、”这个词已经被滥用，可能用来指一个数据系统（如中国移动的号码数据库），可能用来指一种数据存储技术（如关系数据库和NoSQL数据库），还可能用来指DBMS(如人们常说SQL Server是数据库软件）。这种混乱已然形成，恐怕难以改变（比如上文的描述即是如此）。我们只能根据上下文来判断具体含义。按最狭义的技术含义，数据库(Database)是指位于一个数据库服务器实例上的一个库，而DBMS则是指类似SQL Server、Oracle等等此类软件。初学者要注意这些概念之间的差别。论坛上常常见到这样的帖子：“连不上数据库”、“数据库打不开了”，又没有上下文，可见发问者概念混乱，搞得解答者也是一头雾水。

7、3.4. SQL与SQL方言(dialect)SQL是一个关系数据库查询语言的标准，而SQL方言则是各种DBMS在SQL标准上进行的扩展，如增加新的关键字、查询功能、特有的数据类型、支持过程化的控制流语句等。例如SQL Server的T-SQL和Oracle的PL/SQL都是常见的SQL方言。这就好比ANSI C标准与各种编译器实现的C语言的差别。但不同SQL方言之间的差异远大于不同C编译器之间的差异。SQL方言之间的差异，对于跨DBMS的学习和开发，都是必须注意的。3.5. 语句、表达式和断言语句(statement)是SQL中一个可以单独执行的单元。如SELECT * FROM table

8、;即是一个语句，其中包含了SELECT子句(clause)和FROM子句。SQL标准规定用分号作为语句的结束，但在目前的T-SQL中，语句结束的分号是可选的。表达式(expression)是SQL中的一个值（可能是变量、常量、查询字段或计算结果），对应一种特定的数据类型。SQL中的表达式分为标量表达式和表值表达式，其中表值表达式作为单独语句则是SELECT语句，作为语句的一部分则称为子查询。比如0, col + 2, DATEADD(second, 30, GETDATE()都是（标量）表达式。需要特别说明的是，SQL中的CASE WHEN是标题表达式，而不是条件语句。比如CASE WHEN中

9、可以使用表达式，却不能使用语句；CASE WHEN的结果是一个特定数据类型的标量值；CASE WHEN可以用在SELECT、GROUP BY或ORDER BY子句中，但IF ELSE则不行。断言(predicate)是SQL中进行比较的结果，即真值，可理解为布尔表达式，因为SQL中没有bool数据类型，所以将断言特别从表达式中区分出来。比如1是一个标量表达式，而1 = 1则是一个断言，后者可以用在WHERE、ON、HAVING、CHECK等需要真值条件的地方，但前者则不可以。由于NULL的存在，SQL中的断言是三值逻辑，即True/False/Unknown，详见下文“NULL与三值逻辑”。下

10、文中多次用到计算和比较两个词。表达式和表达式进行计算，结果是新的表达式；表达式和表达式进行比较，结果是一个断言；断言和断言可以进行逻辑运行(AND/OR/NOT)，结果是新的断言。注意其中的区别。4. SQL不同于一般编程语言的地方4.1. SQL操作的是数据SQL是数据库的查询语言，因而可以对系统数据产生持久化影响。在常规编程中，一个错误通常只会造成程序的crash或bug，修改并重新调试往往就可以了；而在SQL中，一个不小心就可能造成系统数据的破坏和丢失。常常有新手执行SQL时，不小心遗漏了DELETE或UPDATE语句中的WHERE子句，这往往是很大的麻烦。因此，学习和使用SQL，一开始

11、应该养成两个习惯：1. 细心。在执行SQL语句认真检查一下，要清楚自己在做什么。2. 及时备份，并考虑对系统的元数据进行版本控制。为偶尔的意外准备好后悔药。常见的可能造成破坏性影响的SQL关键词：DELETE, UPDATE, DROP, TRUNCATE TABLE。4.2. SQL是基于集合的说明式语言SQL擅长集合操作，而不是循环。所谓说明式语言，你只需要告诉SQL需要做什么，而不是怎么做。初学SQL的人，思维往往难免带有过程式语言的痕迹，解决问题时常常不由地考虑循环。在学习SQL编程时，每当想要用循环时，先反问一下自己：这个问题是不是必须用循环来解决？事实上，多数情况下，这个问题的答案

12、是否定的。有本书中讲，SQL代码中出现一次IF便要减一些分（比如-1），出现一次WHILE便要减很多分（比如-10或-50，不夸张）。可以尝试用这样的方法为自己的存储过程打分。5. SQL的三个子集SQL从功能上可以划分为三个子集：1. DML(Data Manipulation Language):数据操纵语言，是对数据进行查询和修改（增、删、改）操作的语言。包含语句：SELECT+INSERT/DELETE/UPDATE使用对象：DB User2. DDL(Data Definition Language):数据定义语言，是对域（数据类型）和关系（表）及其它数据库对象进行定义的语言。包含语

13、句：CREATE/DROP/ALTER使用对象：DB Designer/Developer3. DCL(Data Control Language):数据控制语言：是对数据的访问进行权限控制的语言。包含语句：GRANT/DENY/REVOKE使用对象：DBA补充说明：- 严格地说，DML只包含对数据进行修改的语句(INSERT/DELETE/UPDATE)，但SELECT语句与DML关系紧密、形式类似，故通常放在一起。既可以统称为DML，也可以并称为Query+DML。- 以上三个子集不包含BACKUP/RESTORE语句。第二部分：从关系角度理解SQL6. 从关系角度理解SQL6.1. 关系

14、和表众所周知，我们目前所用的数据库，通常都是关系数据库。关系自然在其中处于关键位置。初学数据库原理的人可能会很困惑关系和表是什么联系，如果没有清楚的理解，很可能会认为关系这个概念没有实际意义，只会引起混淆。其实这两组概念只是由于理论界与技术界的着重点不同。前者需要用一个专业的、没有歧义的概念来进行理论探讨，后者则希望在实际应用中能够使用一个直观的、容易理解的词汇。通常情况下，可以认为关系和表是一回事。就定义来说：关系是元组（即表的记录，或行）的集合。此外，关系还有以下特征：- 关系含有一组属性（即表的字段，或列），含有N个属性的关系可称为N元关系。- 一个关系的元组含有与关系相同的属性，N元关

15、系的元组都是N元组，一个元组中对应每个属性有一个值。- 一个属性的域（即字段的数据类型，但域的要求更严格，详见下文“数据类型”），即该属性所有可能的值的集合。从这里可以看出关系和表的区别：关系作为一种集合，不会包含重复元组；而表则可以包含重复记录。这是SQL面对的诸多指责之一，但有其技术合理性。这里的区别在理解上影响不大，不妨把表理解为“可能（但通常不应该）重复的集合”。注意到这点区别，以下我们便可以对关系和表不加区别的使用了。另外，这里的关系和表，指的是所有表值的东西，包含物理表、虚拟表（视图）、派生表（一个用在FROM子句的子查询）、表变量、表值函数、等等。它们在物理上有区别，但在逻辑上是

16、等价的。6.2. 关系模型数据库建模（即表结构设计）的过程，是根据现实世界的业务需求，设计一个表示和存储业务数据的关系数据模型。在设计过程中可以借助E-R模型来简化问题，因为E-R模型可以更直观地对应于现实世界，也可以很容易地转化为关系模型。对于熟练的设计者，可以省略E-R模型，直接构建关系模型。而关系模型在关系数据库中基本上可以直接表示，所以关系模型与物理模型差别不大。物理模型通常只是根据需要添加必要的索引，或是将概念上的表在物理上映射为分区视图或分区表。以上几个模型的关系见下图：简单总结一下关系模型设计中的两个要点：1. 完整性约束(Integrity constraint)：完整性约束保

17、证数据的一致性（符合基本条件），包含以下3种类型：- 实体完整性（主键约束）：一个表的主键不能为空。- 参照完整性（外键约束）：一个表的外键必须存在于所参照的表中。- 自定义完整性（CHECK约束，UNIQUE约束）：即表中的数据不能违反约束定义的条件（不能使CHECK的表达式为False，不能使UNIQUE约束的字段或字段组合出现重复值）。完整性约束定义了系统概念模型的边界，很大程度上防止了脏数据进入系统，这是非常重要的，因为脏数据往往比没有数据还要讨厌（这与“错误的观点胜过没有观点”恰恰相反）。在设计表结构时，外键、CHECK、UNIQUE约束或许可以适当省略（出于运行性能和开发效率的考虑

18、，并且相信表数据只有统一存储过程修改，不会出现脏数据），但主键通常是一定需要的。主键不仅意味着可以高效查询（因为目前DBMS的主键通常都是通过B+树聚集索引实现的），更重要的是清楚地说明了表中数据的唯一标识是什么。（目前我只发现一种不需要主键的情况：日志表同一时刻可能有多笔记录，所以datetime不能作为主键；而一个递增的LogID也没有太大实际意义，参看关于聚集索引选择方案的疑问一帖。）关于主键的选择方案，详见一个基础问题一帖。给我看表的数据样本，以及（可能）过时的数据字典和程序文档，我仍然迷惑不解。如果给我看完整的表的定义（要包括各种完整性约束，特别是主键），通常就不需要查看表中的数据样

19、本了，甚至连文档也可以省去。（这两句话借鉴了Brooks在人月神话一书中的话。参见UNIX编程艺术1.6节脚注。）2. 范式(Normal Form, NF)：范式是一组关系（表）设计的原则，通过避免冗余防止出现数据的更新异常（即DRY原则的体现）。在实践上常用的是以下3个层次的范式：- 1NF：表中的字段都是原子的。- 2NF：表中的所有字段都可以由主键唯一决定（函数依赖）。- 3NF：除完整主键以外，其它字段（包括部分主键）之间不存在决定关系（函数依赖）。首先说明一下1NF的“原子的”。这个“原子的”是指业务需求不需要对这个值进行拆分（没有前提条件，“拆分”一词是有多种解释的，如字符串可以

20、拆分成字符，整数可以拆分成二进制的位串或素因子的乘积）。例如，城区、街道、门牌号是地址的三部分，如果地址只是作为一个记录，不需要更细粒度的处理，则可以将三部分存在一个字段；如果需要根据城区进行查询和分组统计，则至少需要把城区作为一个单独的字段。所以，一个字段是不是“原子的”必须根据业务需求这个条件来定义。实践中业务需求是会变化的，因而系统设计还需要一定的前瞻性。目前一个原子的字段可能随着需求变化而不再是原子的。范式给出了一组表应该怎样设计的原则，但没有说明如何把表设计成这样。数据库理论上的关系范式分解过于抽象，以下是一点实用性的思路：- 1NF：让表中的每个字段都不需要拆分处理（至少不需要太复

21、杂的拆分处理）。如姓名的结构很简单，通常不需要设计成姓和名两个字段，但如果是一个国际化的系统，不同文化中姓名的结构可以不同，这时则最好把LastName和FirstName分开存放，比如Facebook、Twitter等网站的设计。- 2NF：给表定义主键。参看上文关于实体完整性的讨论。- 3NF：不要在同一个表中存放相关数据或派生数据，只存放主要数据，其它数据通过联接查询或计算获得。如不要在员工表中同时存放部门ID和部门名称（相关数据）或同时存放出生年月和年龄（派生数据），其中部门ID或出生年月是主要数据，部门名称可以通过联接查询获得，年龄可以通过计算获得。有些情况下出于结构的直观或查询性能

22、的考虑，可能会需要反范式的设计。如在一个字符串字段中存放逗号分隔的多个值（形如1,2,3,5,8，违反1NF），或是在一个表中同时存放相关数据或派生数据来避免联接或计算开销（比如同时存放部门ID和部门信息来避免联接部门表，或同时存放员工各项薪酬福利和总薪资来避免复杂的薪资计算，违反3NF）。反范式的设计会带来复杂的查询处理或冗余，更好的方案是基本数据用符合范式的表存储，通过统一的过程来计算和刷新缓冲表来提高查询时的性能，参看程序员修炼之道第7节关于DRY原则的讨论。6.3. 关系运算表的查询，与关系代数(Relational Algebra)定义的关系运算是等价的。理解关系运算，或许可以简化对

23、查询的认识。常用的基本关系运算只有4类（够简单吧）：1. 基本的集合运算（双目运算）关系是元组的集合，所以关系也支持基本的集合运算：- 并(union)：对应SQL关键字UNION- 交(intersection)：对应SQL关键字INTERSECT- 差(difference)：对应SQL关键字EXCEPT不同的是，关系的集合运算，要求参与运算的两个关系必须含有相同属性集（属性的个数和类型都一样）。由于表允许重复记录，在SQL中以上三种操作还可以是UNION ALL/INTERSECT ALL/EXCEPT ALL的形式，结果不去除重复记录。2. 提取关系的一部分的运算（单目运算）- 选择(

24、selection)：根据条件过滤出指定的元组（行），对应SQL查询的WHERE子句- 投影(projection)：根据列表过滤出指定的属性（列），对应SQL查询的SELECT子句由于表允许重复记录，关系的投影运算事实上等价于SELECT DISTINCT的效果。而SELECT的默认效果是不去除重复记录。3. 两个关系的联接（双目运算）- 笛卡尔积(Cartesian product)：对应SQL关键字CROSS JOIN（与FROM后的多个表直接用逗号分隔效果相同）- 内联接(Inner Join)：对应SQL关键字INNER JOIN- 外联接(Outer Join)：对应SQL关键字L

25、EFT | RIGHT | FULL OUTER JOIN4. 聚合运算（单目运算）根据指定属性（列）分组，同时可以使用聚合函数。对应SQL查询的GROUP BY子句。以上4类关系运算，不管是单目运算还是双目运算，其结果依然是一个关系，因而可以继续进行运算。通常情况下的SQL查询，除一些特殊的语言特性外（如TOP、排序函数等），主要的查询逻辑都是这4类关系运算的组合。6.4. 数据查询1. 查询的逻辑处理过程以T-SQL为例，一个查询（完整SELECT语句）的逻辑处理过程如下（其中括号中的数字表示处理顺序）：code=sql (8) SELECT (9) DISTINCT (11) (1) F

26、ROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH CUBE | ROLLUP (7) HAVING (10) ORDER BY /code说明：+ 有些子句是可选的。比如JOIN可能出现0到多次，GROUP BY和HAVING可能出现0到1次。+ 从以上顺序可以看出，为何在WHERE子句不能使用SELECT的计算结果，但在ORDER BY子句却可以。+ 查询的逻辑处理过程与物理处理过程可能并不相同。但对于SQL的学习来说，先理解逻辑处理过程是必须的。先要知道怎样计算出正确的结果，才谈得上怎样更高效地计算出正确的结果。该内容详见Microso

27、ft SQL Server 2005技术内幕：T-SQL查询第1章。2. 查询条件在SQL Server联机丛书中，查询条件的BNF语法图如下：code=sql Search Condition := NOT | ( ) AND | OR NOT | ( ) ,.n := expression = | | ! = | | = | ! | | = | ! expression | string_expression NOT LIKE string_expression ESCAPE escape_character | expression NOT BETWEEN expression AND

28、expression | expression IS NOT NULL | expression NOT IN ( subquery | expression ,.n ) | expression = | | ! = | | = | ! | | = | ! ALL | SOME | ANY ( subquery ) | EXISTS ( subquery ) | CONTAINS ( column | * , ) | FREETEXT ( column | * , freetext_string ) /code其中：predicate为断言，expression为标量表达式，subquery为

29、子查询。查询语句返回使查询条件为True的结果。3. 子查询一个查询如果作为一个语句的一部分，则称为子查询。a. 按结果分类：- scalar subquery：如果查询结果是标量值，即只有一行一列，则为标量子查询（标量表达式）。- table-valued subquery：反之则是表值子查询（表值表达式）。b. 按查询是否涉及外层表分类：- self-contained subquery：不涉及外层表的子查询是不相关子查询，如SELECT a.* FROM a WHERE a.id IN (SELECT b.id FROM b)。- correlated subquery：反之则是相关子查询，如SELECT a.* FROM a WHERE EXISTS (SELECT * FROM b WHERE b.id = a.id)。c. 按子查询所在的位置分类：- In search_condition：在查询条件中的子查询，比如上文语法图中的所有subquery。- In FROM clause：在FROM子句中的子查询，又称派生表，如SELECT * FROM (SELECT * FROM a) tmp，派生表一定要指定表别名。（SQL Server 2005之后支持Common Table Express