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

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

来源:博客园 作者:Wayou 发表于:2012-11-09 12:34  点击:
会出错,OrderId不是串,一个不是串的元素想跟一个串连接当然会出错,需要将不是串的OrderId转换成串后方可进行上述操作。刚好有个STR()可以用来完成需要。 但STR()也不是一个长久之计,因为碰到日期时,它就无法

会出错,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

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