SQL--多的是，你不知道的事

 

本文不是SQL教程，只是关于T-SQL的一点小结及吐槽。

对于SQL入门的学习，推荐一本32开大小的小巧书目《SQL Server编程必知必会》，对于每个知识点介绍得很简单但很到位，如果你不想阅读那些动辄四五百页的大作的话，这本书完全够用。另外一本进阶的书目《The Art of SQL》很出名，那是我打算看的但还没有看。

这里讨论和所有例子都基于微软对SQL的一个实现版本T-SQL。表是基于manage sutio自带的Northwind数据库。

对于初学者来说，只知道SQL不知道T-SQL，T-SQL是什么，感觉像方言。对头，它就是个方言。

SQL的全称是结构化查询语言Structured Query Language，专门设计来与数据进行通信，就好比HTML被设计来展示网页。它不是一种专利语言,不属于哪个公司，好比你无法说出HTML是微软的，还是IBM的。有专门的标准委员会来制定SQL语言的标准及对它进行维护。而我们真正使用的，是各个数据库管理软件对它的一个实现。就是说SQL是标准，其他人在遵守这一标准的前提下给出自己的一个实现版本。微软的数据库产品所实现的版本就叫Transact-SQL，简称T-SQL，Oracle的是PL/SQL等。当然，各个DBMS版本间会新增自己的一些特性，这也是为什么Oracle下写的SQL脚本可能在SQL Server上不兼容的原因，但大部分语句都是可以在各平台移植的。

SQL中的随机

SQL中，大部分查询都是通过SELECT语句返回，最熟悉的莫过于从一张表中进行查询返回结果，但其实还有一些返回是可以通过启用函数的，比如GETDATE（）。

GETDATE（）方法返回当前系统时间，准确说是你的数据库服务器的时间。如果有人问程序员What’s the time? 那我想《程序员装逼指南》（如果有这本书的话）会告诉你，程序员才不会带表或者看手机呢，他会优雅地输入SELECT GETDATE（）;

只是在单独使用诸如GETDATE()，RAND()等函数时表忘了加上SELECT .

上面提到RAND()方法，它默认生成的是0到1且后面拖了一长串精度的浮点数。同时它也可以接收一个种子值，如果指定了种子值，居然每次产生的随机数都是一样的！真的是坑爹的随机函数.

但其实很多时候我们需要让这个产生的随机数更符合我们的需要一点，比如只想要一个0到10之间的随机数，这时候可以联想到CEILING()和FLOOR()这两个函数，前者返回大于等于给定参数的最小整数，后者相反，返回小于等于给定参数的最大整数。有点绕口，比如CEILING(0.3)，那么比0.3大的整数就是1，2，3….最小的是1，返回1.  而FLOOR(1.5)呢，对头，返回1.

接着上面的思路，要返回0到10之间的一个随机数，我们需要做的就是把RAND()返回的数先乘以10，这样返回的就是几点几的一个数，再用CEILING()或者FLOOR()就可以只取到一个整数部分的数字。

 用CEILING()还是FLOOR(), 抑或两者没有区别。必需有区别，用CEILING()你永远得不到0这个数，而且FLOOR()又永远得不到10这个数。

还有种方法就是使用字符串处理函数LEFT()或者RIGHT()，将RAND()返回的数同样乘10后用LEFT()取左边一位就是我们想要的了，RIGHT()同理。

想到这里，甚至可以用SUBSTRING(),它接收三个参数，第一个是字符串，所以首先我们需要把RAND()返回的数通过CONVERT()转成字符型，第二参数是要取的子字符串的开始位置，第三个参数为子字符串长度。

除非你真的是闲的，否则没有必需用这种方法来做，这里只是提供一种思路，说明科学的道路其实很宽阔的。

至于要返回1到100，10到100随机数等的情况，相信通过各位码农的智慧也是完全可能的了，这里就不赘述鸟。

SQL主要功能不是返回数字，更多是返回表中的行。同样，也可以实现从表中返回随机抽取的数据行。

通过NEWID（）这个方法用如下语句轻松实现。

NEWID（）是个什么样的函数，可以实验一把来看看：

返回一串字符类似于GUID，唯一的标识。每次运行返回的都是一个不同的值。在上面的例子中，从ORDERS表中取三条数据，数据是通过NEWID（）产生的值到表中取到的，当然就是随机的了。

其实，SQL SERVER有个专门从大量数据中返回随机样本的函数TABLESAMPLE(),通过参数可以指定返回多少条随机样本数据或者百分之几的数据：

但诡异的是，居然提示语法错误。这有点让人摸不着北。后来发现，TABLESAMPLE() 是SQL SERVER 2005 新增的内容，而Northwind数据库又是2005版本以前自带的一个样本数据库，所以它只能兼容级别为80，也就是说在这个数据库上能执行2000或更老的SQL SERVER所以支持的命令但无法执行更新的命令。像SQL SERVER 2005之后的版本中都用Adventureworks数据库代替了Northwind的。于是转到Adventureworks数据库进行尝试。

 

果然是兼容性的问题，那么能不能在老版本SQL SERVER中创建的数据库上执行呢，其实还是可以的，只要把数据库的兼容性改到更高即可：

需要说明的是TABLESAMPLE()每次返回的结果数目是不一样的，在给定的参数上下跳动，所以如果表本身的数据量很小的话，比如只有两三百条，使用此命令一行结果也没有返回是很正常的。

日期与时间

GETDATE()可以方便地获得时间日期，DATEPART（）则会返回指定的日期的某部分，比如只想要一条完事日期信息中的月分，星期等：

但我们看到返回的星期其实不够人性化，贴心的显示应该是星期几而不是本周的第几天，这时就可以用DATENAME()了，它返回的是日期的名字。

其实更人性化一点的显示应该是这样的，如果在中国，应该返回星期一，十月！

运行Sp_helplanguage可以看到SQL SERVER中保存了各种语言对日期时间的显示设置。当然中文的习惯称呼也可以在里面找到。

再看我们数据库当前的语言环境：

当前语言环境是英语，所以返回的是英文的日期相关名称，那思路应该就是把默认语言环境改成中文。

可以看到，现在系统默认语言已经设置成了简体中文，NOW，就是见证奇迹的时刻。

繁体中文：

  一个有意思的事情是，让你看一下日语情况下的所有星期：

是不是很有意思，华夏博大精深的五行在日本那里成了星期。如果我不是这么晚研究SQL这么入神，我也不知道今天是火什么日。

对于上面所执行的语句，我不得不多说几句，首先行1设置系统默认语言为日本儿语，行2行3定义了两个变量一个保存当前日期，另一个用作下面循环输出星期时的计数器，在对这两个变量进行初始化时有个技巧，一般是用SET来进行变量初始化，但一次只能初始化一个，而SELECT语句里可以初始化多个变量。以前我甚至不知道SELECT可以进行变量初始化！！行6开始循环函数，每输出一次后把日期加一天并且计数器加一，这里日期加一天又用了个默默无闻的函数DATEADD()，你可以指定一个数加到一个日期上的哪部分，这里是加一天。

还有个常见的问题就是，很多时候我们只希望返回日期中的时间，或者日期中的日期不带时间。

实现的方法很多，但基本上都是先把日期转换成字符后操作。对于字符你就可以用LEFT()或者RIGHT()抑或者SUBSTR()来取一个日期数据中的任何部分了。

对于实现这个功能，一个印度阿三给出了如下的解法：

注意，上面那样需要SQL SERVER 2008及以上。

无可否认，印度阿三的实力就是那样让人肃然起敬。如果你很不幸的干上了程序员这一行，终有一天你会感叹既生我，何生印度阿三。

其实对于一个日期中想取天，月等，有直接的函数可用：

其实这三个函数就是DATEPART()的简化而以，因为你完全可以用DATEPART()来完成。

只是DATEPART()更灵活，能取到的部分更多。

根据笔划排序

如果你注意到了SQL SERVER里面有个collation属性的话，这个功能实现起来不那么难。并且对于中文环境的开发者来说，这样的功能可能会时不时的碰到。

右击任何一个数据库，查看其属性，你会发现在选项里可以看到collation这个属性并且可以进行设置。

 

Collation所规定的就是一种字符集的排序规则。这个东西通常是被很多开发人员忽略而不知的。在这里所以设定的值会应用到所选数据库的默认排序规则上。通常对于一种语言比如中文，不止一种规则，通过点开下拉列表你可以看到里面分得很细，包括简体繁体，香港大陆台湾新加坡等，其中带’stroke’就是涉及笔划的。

除了通过上面的属性窗口可以了解到collation外，还可以通过SELECT * FROM   Fn_helpcollations(); 查看数据库服务器所支持的字符集。

如你看到的这个Chinese_PRC_CS_AI_WS，前面CHINESES_PRC(中国大陆)当然是指国家地区了，而后面跟的后缀，也有其基体的含义，可以从SQL Server的帮助文档中了解到，这里不说开了。正是后面这些后缀对排序起了很大的作用。

如果你只是临时想在检索结果中对列以笔划排序，可以在SELECT语句后通过COLLATE指定相应的collation（为了演示临时建了个表并插入了中文数据）：

在我的映像中笔划顺序貌似是横竖撇捺折，这里看来我碰到了点认知上的疑惑。

同样，你在列表中可以看到带‘pingyin’的，那就是以拼音排序。

以拼音排序的结果：

Soundex

我也是在想系统地学习一下SQL然后看了本儿书后才发现原来还有这么个神奇的函数。

SOUNDEX将字符转换成表示其发音的字母与数字的组合，所以用它进行字符串的匹配是基于字符串的读音而不是字符本身，够强大吧。其实SOUNDEX并不属于SQL里的概念，但T-SQL提供了对它的支持。下面来做个实验。

在Categories表中CategoryName最后列有个seafood，我在其后面添加了四列，按照英语音标习惯这些读音都和seafood差不多，看是否能通过soundex把它们检索出来。

执行下面的名句后及结果如图：

可以看到，检索出了3行新增用来实验的数据，效果还是非常令人满意的。

从这里似乎可以窥见到语音搜索的端倪了：当你点击谷歌搜索框旁边的话筒，它把你的声音编码成文本返回服务器然后通过上面类似的方法进行数据检索。这就是语音搜索。

当然，这只是一种猜想，具体算法当然要复杂得多。只是从这里可以看出，任何你需要的一个小功能都可以从一些不起眼但已经存在的函数上来做文章。还有前面提到的汉字排序问题，如果你不知道这些知识点，面对一些需要真的就无从下手。

保存特殊类型（img/mp3）

基本上我们在SQL SERVER中碰到和操作的都是文本，数字等类型的数据，但图片也是经常要面对的一种文件类型，比如我们需要保存用户的头像。

对于图片，可以保存在数据库，那就是保存它们的文本编码形式，而不是文件本身。比如用户上传了一张图片，其实从前端传送到服务器的过程中也是以文本编码的形式，SQL SERVER也确实提供了image这种格式来方便保存图片到数据库。

另外一种方案就是保存路径。比如前端用户上传了图片，服务器指定图片保存到某个文件夹下，并且对图片进行一个统一有序的命名，然后将这个图片的路径和重命名后的信息可在到数据库表中，以后使用是通过查找相应的表获得路径及文件名，然后去调用真正的文件。

不说说第二种方案是更加合理的，试想谁也不可能把mp3甚至视频文件编码保存到数据库表中的一列，那是怎样的一个表！

不算格式化的格式化

对检索出的结果一般是时间日期金钱等需要进行一些格式化操作以符合阅读习惯。这里不打算讲开了。

一般，搜索出的id我们习惯要加上编号方便阅读，比如XXXX号，No.XXX ，但你如果尝试通过这样的语句直接加上想要显示的字符串时：

会出错，OrderId不是字符串，一个不是字符串的元素想跟一个字符串连接当然会出错，需要将不是字符串的OrderId转换成字符串后方可进行上述操作。刚好有个STR()可以用来完成需要。

但STR（）也不是一个长久之计，因为碰到日期时，它就无法转换了。日期还需要转换嘛，兴许你会认为日期本来就是字符串了，那我们可以先看一下：

所以以日期不是字符串，日期是一种专门的格式。看着像字符串而以。这就叫长得像也是错。

对于日期我们可以使用比较万能的CONVERT（）来进行转换。

或者你会问，在SQL里加字符串整理好我们需要的数据没必需，完全可以在应用程序中格式化，怎么加都行，但SQL是专门设计来处理数据的，它对这些操作的执行效率非常高，如果你一开始就把数据整理好再送到应用程序便可直接使用，将会使你的程序性能有较大提高（这也是SQL必知必会一书中的观点）.

聚合函数用在多列

你可以猜想一下 SELECT SUM(c1*c2) 的结果是什么。或者你还没有这样的概念，SUM就是对一列的数据进行求和嘛。AVG（）也是对于一列求平均。但其实聚合函数是可以用在多列上的。

就比如上面的SELECT SUM(c1*c2)，先是把c1列和c2列对应的每行相乘，最后再把每行的乘积相加。

请看下面这个测试用列：

关于自增列

对于自增列，我们知道一般用来当表的主键，并且它的值被指定为按给定的种子值自动递增下去，在你向表中插入一行数据后，它自动增加，确定的说它会去找还没有被使用的数中最小的数来做为自己的值。但是我们对表中数据进行了删除某行的操作后，比如我们有5条数据，有一列ID它的值是从1到5由SQL SERVER生成的，我们执行delete将ID为5的行删除，但我们再次插入数据时，ID是6而不会是5. 虽然5已经被删了但5已经被使用过了，所以新插入的行不再取5而从5后面一个开始。

那我非要接着之前的序号开始插入行呢，答案是不行的。曾经我也思考过这个问题，相信很多人开始也寻求过这个问题的答案，都无功而返。

后来想了一下把问题想通了，这样的设计是很合理的。 接着上面的情境，假如我们删除了ID为2的行呢，如果仅仅为了保持ID这个序号值的连续性，那2后面所有行都要自动将自己的ID减一，然后我们插入的数据呢再接着最后一行的ID（现在变为4）插入。那可以想象，这是个多么耗费资源而又没有任何意义的事情。变更了一行就要变更所有行，如果数据量大，那我们仅仅是做一个插入操作就要等半天。更不用说在高访问量的服务器上怎么办了。

HAVING or WHERE

HAVING or WHERE:其实这两个关键字的功能是一样的，甚至语法也完全一样，唯一不同的是前者对分组进行过滤。后者对行进行过滤，也是我们用得最多的，似乎忽略了HAVING的存在。何时用HAVING，当你需要在分组内进行过滤时就需要它，因为WHERE其实是没有分组的概念的。

究其根本，WHERE 是先过滤，再分组，HAVING先分组，再过滤。

结果集中重复的行

UNION完成多条WHERE语句相同的功能，结果集都已经自动去除了重复的行，如果需要返回全部的行，需要使用UNION ALLl 。

对于上面这句话，我们一点点来看。

比如对于Orders表,先看一下里面有什么：

现在我们想返回Orderid=10249的那条数据和所有OrderId小于 10252的数据。

很明显可以用两条带WHERE的语句来完成。

通过UNION可以把上面两条独立的SELECT语句组合起来，但组合前请先注意一下，上面一个返回了1行数据，另一个返回了4行数据。

组合后返回了相同的数据，只是OrderId为10249那行因为重复被自动去除了。如果确实需要保留完整的查询结果不希望去掉重复行，可以用UNION ALL, 此时为原汁原味的把两个SELECT语句返回的结果相连接，真的是1加1等于二。

注意到我在后面一条SELECT语句使用了ORDER BY，看似它是属于第二条SELECT 语句的，只对第二条语句检索出来的结果进行排序，其实不是，它对连接后的整体进行排序。

如果你使用WHERE，则无法做到保留重复行。

用选择的结果来填充

用CREATE TALBE 创建一张表后，为了插入数据，你不得不写一大堆SQL语句。当然，除了这种手动写INSERT 语句来填充表的情况外，还有更加便捷的方式，可以把另一张表的数据全部或者有选择地返回然后填充到新表。当然，前提是两张表的结构要一样，不然相应字段对应不上如何填充。

下面拿NorthWind中的Region表为列，我们新建一张结构跟他一样的表，并插入一些数据先。

但先看一下Region表是个什么情况先：

之所以选它就是因为简单，除了一个自增的主键列，只包含一列数据格式为文本的列。

下面我们就用这张RegionNew表中的数据来填充原来的Region表，确切地说是往Region表中再插入一些数据。先看INSERT INTO语句。

 但如你所见，出错了。原因是原来的Region表中的RegionDescription列允许空值，而我们新建的表中有一行的对应列为空值。这就是前面提到的，两张表的结构必需要一致才能进行这样的导入操作，这里的一致不是说表名列名完全一样，列名其实可以不一样，只顺序对的就行，一致是指两张表列数相同并且相应列保存的数据类型要一致。

把RegionNew表中的NULL改后再执行一下，如果如下 ：

数据已经插入了。在新建RegionNew这张表时，你也看到了我并没有让ID列自动从1开始，而是通过SET IDENTITY_INSERT语句来让我可以手动编写ID列的值。不那样的话RegionNew表中的ID列就会是1，2，3，这将会在你进行导入到Region表时发生冲突，因为这些数字已经在Region中使用过了。

INSERT SELECT是用一张表填充另一张表，派生出一个SELECT的变种语法SELECT INTO. 使用此语句时目标表可以不存在，INTO后只需指定一个表名，系统会自动创建，当然，也可以跟一张已经存在的表，已经存在的表同样需要满足相应列的要求。

现在把更新后的Region表选择出来导入到一张新表中，在进行这个操作前这张表并不存在。

执行完上述语句后在数据库中新建了一张名为Mew的喵星表，并且拥有了Region表的全部数据。同时你可以加上WHERE等过滤语句来选择性地导入数据：

再发散一下，你还可以在SELECT时指定多张其他表这样就可以从多个不同来源把数据汇总到一张表上，这个功能确实是很赞的。在你需要一些临时表备份表进行分析时非常有用。

到这里，你可以把SELECT INTO理解为导出，而INSERT SELECT 视为一种导出操作。

重命名表

系统提供了一个存储过程来实现对表重命名 EXEC SP_RENAME ‘oldtablename’,’newtablename’; 重命名还不简单嘛，直接点击表就可以了，但命令是用在编程当中的，所以在写SQL脚本时会非常有用。

可以参考的文章：

SQL Server Collations

http://sqlserverlearner.com/sql-server-link/sql-server-collations