主页 > 知识库 > 数据库 > SQL server >

SQL--多的是,你不知道的事(2)

来源:博客园 作者:Wayou 发表于:2012-11-09 12:34  点击:
繁体中文: 一个有意思的事情是,让你看一下日语情况下的所有星期: 是不是很有意思,华夏博大精深的五行在日本那里成了星期。如果我不是这么晚研究SQL这么入神,我也不知道今天是火什么日。 对 于上面所执行的语句


繁体中文:

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

是不是很有意思,华夏博大精深的五行在日本那里成了星期。如果我不是这么晚研究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 ,但你如果尝试通过这样的语句直接加上想要显示的字符串时:

有帮助
(0)
0%
没帮助
(0)
0%