Category Archives: SQLServer

SQL事务

一、事务概念    事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。这特别适用于多用户同时操作的数据通信系统。例如：订票、银行、保险公司以及证券交易系统等。 二、事务属性事务4大属性：1   原子性(Atomicity):事务是一个完整的操作。2   一致性（Consistency）：当事务完成时，数据必须处于一致状态。3   隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。4   持久性(Durability):事务完成后，它对于系统的影响是永久性的。 三、创建事务T-SQL中管理事务的语句：1 开始事务: begin transaction2 提交事务：commit transaction3 回滚事务: rollback transaction 事务分类:1 显式事务:用begin transaction明确指定事务的开始。2 隐性事务：打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。3 自动提交事务：SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。 示例：张三转800元到李四帐户上。  use stuDBgo--创建帐户表bank--if exists(select* from sysobjects where name=’bank')    drop table bankcreate table bank(    customerName char(10),    --顾客姓名    currentMoney money        --当前余额)go/**//*--添加约束，帐户不能少于元--*/alter table bank add        constraint CK_currentMoney check(currentMoney>=1)/**//*--插入测试数据--*/insert into bank(customerName,currentMoney)select '张三',1000 unionselect '李四',1 select * from bankgo /**//*--使用事务--*/use stuDBgo--恢复原来的数据--update bank set currentMoney=currentMoney-1000 where customerName=’李'set nocount on    --不显示受影响的行数print '查看转帐事务前的余额'select * from bankgo /**//*--开始事务--*/begin transactiondeclare @errorSum int    --定义变量，用于累计事务执行过程中的错误/**//*--转帐--*/update bank set currentMoney=currentMoney-800 where customerName=’张三'set @errorSum=@errorSum+@@error    --累计是否有错误update bank set currentMoney=currentMoney+800 where customerName=’李四'set @errorSum=@errorSum+@@error --累计是否有错误 […]

注意那些容易被忽略的SQL注入技巧

导读：下面我要谈到一些Sqlserver新的Bug（注入），这些都是从长期的工作中总结出来的经验，现在拿出来与大家一起分享，希望能够对大家有所帮助。 1．关于Openrowset和Opendatasource 可能这个技巧早有人已经会了，就是利用openrowset发送本地命令。通常我们的用法是（包括MSDN的列子）如下： select * from openrowset('sqloledb',’myserver';’sa';",’select * from table') 可见（即使从字面意义上看)openrowset只是作为一个快捷的远程数据库访问，它必须跟在select后面，也就是说需要返回一个recordset 。 那么我们能不能利用它调用XP_cmdshell呢？答案是肯定的！ select * from openrowset('sqloledb',’server';’sa';",’set fmtonly off exec master.dbo.XP_cmdshel l "dir c:\"') 必须加上setfmtonlyoff用来屏蔽默认的只返回列信息的设置，这样XP_cmdshell返回的output集合就会提交给前面的select显示，如果采用默认设置，会返回空集合导致select出错，命令也就无法执行了。 那么如果我们要调用sp_addlogin呢，他不会像XP_cmdshell返回任何集合的，我们就不能再依靠fmtonly设置了，可以如下操作： select * from openrowset('sqloledb',’server';’sa';",’select "OK!" exec master.dbo.sp_addlogin Hectic') 这样，命令至少会返回select OK!’的集合，你的机器商会显示OK!，同时对方的数据库内也会增加一个Hectic的账号，也就是说，我们利用select 'OK!’的返回集合欺骗了本地的select请求，是命令能够正常执行，通理sp_addsrvrolemember和opendatasource也可以如此操作！至于这个方法真正的用处，大家慢慢想吧。 2．关于Msdasql两次请求的问题 不知道大家有没有试过用msdasql连接远程数据库，当然这个api必须是sqlserver的管理员才可以调用，那么如下： select * from openrowset('msdasql',’driver={sql server};server=server;address=server,1433;uid=sa;pwd=;database=master;network=dbmssocn',’s elect * from table1 select * from table2′) 当table1和table2的字段数目不相同时，你会发现对方的sqlserver崩溃了，连本地连接都会失败，而系统资源占用一切正常，用pskill杀死 sqlserver进程后，如果不重启机器，sqlserver要么无法正常启动，要么时常出现非法操作，我也只是碰巧找到这个bug的，具体原因我还没有摸透，而且很奇怪的是这个现象只出现在msdasql上，sqloledb就没有这个问题，看来问题不是在于请求集合数目和返回集合数目不匹配上，应该还是msdasql本身的问题，具体原因，大家一起慢慢研究吧。 3．可怕的后门 以前在网上看到有人说在 sqlserver上留后门可以通过添加triger、jobs或改写sp_addlogin和sp_addsrvrolemember做到，这些方法当然可行，但是很容易会被发现。不知道大家有没有想过sqloledb的本地连接映射。呵呵，比如你在对方的sqlserver上用sqlserver的管理员账号执行如下的命令： select * from openrowset('sqloledb',’trusted_connection=yes;data source=Hectic',’set fmtonly off exec master..XP_cmdshell "dir c:\"') 这样在对方的sqlserver上建立了一个名为Hectic的本地连接映射，只要sqlserver不重启，这个映射会一直存在下去，至少我现在还不知道如何发现别人放置的连接映射，好了，以上的命令运行过后，你会发现哪怕是sqlserver没有任何权限的guest用户，运行以上这条命令也一样能通过！而且权限是 localsystem！（默认安装）呵呵！这个方法可以用来在以被入侵过获得管理员权限的sqlserver上留下一个后门了。以上的方法在 sqlserver2000 sqlserver2000SP1上通过！ 另外还有一个猜测，不知道大家有没有注意过windows默认附带的两个dsn，一个是localserver一个是msqi，这两个在建立的时候是本地管理员账号连接sqlserver的，如果对方的 sqlserver是通过自定义的power user启动，那么sa的权限就和power user一样，很难有所大作为，但是我们通过如下的命令： select * from openrowset ('msdasql',’dsn=locaserver;trusted_connection=yes',’set fmtonly off execmaster..XP_cmdshell "dir c:\"') 应该可以利用localserver的管理员账号连接本地sqlserver然后再以这个账号的权限执行本地命令了，这是后我想应该能突破sa那个power user权限了。现在的问题是sqloledb无法调用dsn连接，而msdasql非管理员不让调用，所以我现在正在寻找guest调用msdasql […]

SQL语句的22条法宝

SQL语句的22条法宝： 1、说明：备份sql server— 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'— 开始 备份BACKUP DATABASE pubs TO testBack 2、说明：增加一个列Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 3、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 4、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：select * into b from a where 1<>1法二：select top 0 * into b from a 5、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b; 6、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 7、说明：显示文章、提交人和最后回复时间select a.title,a.username,b.adddate from […]

SQL Server数据库维度表和事实表概述

SQL Server数据库维度表和事实表概述: 一、事实表 每个数据仓库都包含一个或者多个事实数据表。事实数据表可能包含业务销售数据，如现金登记事务 所产生的数据，事实数据表通常包含大量的行。事实数据表的主要特点是包含数字数据(事实)，并且这些数字信息可以汇总，以提供有关单位作为历史的数据，每个事实数据表包含一个由多个部分组成的索引，该索引包含作为外键的相关性纬度表的主键，而维度表包含事实记录的特性。事实数据表不应该包含描述性的信息，也不应该包含除数字度量字段及使事实与纬度表中对应项的相关索引字段之外的任何数据。 包含在事实数据表中的“度量值”有两中：一种是可以累计的度量值，另一种是非累计的度量值。最有用的度量值是可累计的度量值，其累计起来的数字是非常有意义的。用户可以通过累计度量值获得汇总信息，例如。可以汇总具体时间段内一组商店的特定商品的销售情况。非累计的度量值也可以用于事实数据表，单汇总结果一般是没有意义的，例如，在一座大厦的不同位置测量温度时，如果将大厦中所有不同位置的温度累加是没有意义的，但是求平均值是有意义的。 一般来说，一个事实数据表都要和一个或多个纬度表相关联，用户在利用事实数据表创建多维数据集时，可以使用一个或多个维度表。 二、维度表 维度表可以看作是用户来分析数据的窗口，纬度表中包含事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构。例如，包含产品信息的维度表通常包含将产品分为食品、饮料、非消费品等若干类的层次结构，这些产品中的每一类进一步多次细分，直到各产品达到最低级别。 在维度表中，每个表都包含独立于其他维度表的事实特性，例如，客户维度表包含有关客户的数据。维度表中的列字段可以将信息分为不同层次的结构级。 三、结论: 1、事实表就是你要关注的内容; 2、维度表就是你观察该事务的角度，是从哪个角度去观察这个内容的。 例如，某地区商品的销量，是从地区这个角度观察商品销量的。事实表就是销量表，维度表就是地区表。 维度表和事实表在SQL Server数据库的操作中是很常用的两种表，学会了这两种表的应用，在操作SQL Server数据库时就会容易很多，初学者也可以首先学习这两种表的操作使用技巧。

在SQL Server数据库中拆分字符串函数

SQL Server数据库中拆分字符串函数的具体方法： CREATE FUNCTION uf_StrSplit '1.1.2.50','.'(@origStr varchar(7000), --待拆分的字符串@markStr varchar(100)) --拆分标记，如','RETURNS @splittable table(　 　str_id varchar(4000) NOT NULL, --编号ID　 　string varchar(2000) NOT NULL --拆分后的字符串)ASBEGINdeclare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid intSELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr=",@TEMPid=0if(RIGHT(@origStr,1)<>@markStr )beginset @origStr = @origStr + @markStrendWHILE((@postion<=@strlen) and (@postion !=0))BEGINIF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;ENDELSEBEGINSET @sublen=@strlen-@postion+1;ENDIF(@postion<=@strlen)BEGINSET @TEMPid=@TEMPid+1;SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);INSERT INTO @splittable(str_id,string)values(@TEMPid,@TEMPstr)IF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @postion=CHARINDEX(@markStr,@origStr,@postion)+1ENDELSEBEGINSET @postion=@postion+1ENDENDENDRETURNEND 例如：select * from uf_StrSplit('1，1，2，50′,’，') 输出结果： str_id string1 12 13 24 50 上文中涉及到很多的字符，对于一些初学者来说，可能是比较难理解，但是这个知识点确实是经常会用到的，而且相当实用，希望大家好好学习，争取能熟练的掌握。

SQL Server数据库多种方式查找重复记录

SQL Server数据库多种方式查找重复记录: 示例：表stuinfo，有三个字段recno(自增)，stuid，stuname 建该表的Sql语句如下： CREATE TABLE [StuInfo] ([recno] [int] IDENTITY (1， 1) NOT NULL ，[stuid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ，[stuname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL) ON [PRIMARY]GO 1.查某一列(或多列)的重复值(只可以查出重复记录的值，不能查出整个记录的信息) 例如:查找stuid，stuname重复的记录 select stuid，stuname from stuinfogroup by stuid，stunamehaving(count(*))>1 2.查某一列有重复值的记录(此方法查出的是所有重复的记录，如果有两条记录重复的，就查出两条) 例如:查找stuid重复的记录 select * from stuinfowhere stuid in (select stuid from stuinfogroup by stuidhaving(count(*))>1) 3.查某一列有重复值的记录(只显示多余的记录，也就是说如果有三条记录重复的，就显示两条) 前提：需有一个不重复的列，此示例为recno。 例如:查找stuid重复的记录 select * from stuinfo s1where recno not in (select max(recno) from stuinfo s2where s1.stuid=s2.stuid 关于SQL Server数据库中查询重复记录的方法就为大家介绍到这，这里介绍的方法可能也是不够全面的，以后如果有了更新的方法，我会及时与大家继续分享，希望对大家能有所帮助。

提高SQL执行效率的几点建议

提高SQL执行效率的几点建议: 尽量不要在where中包含子查询：关于时间的查询，尽量不要写成：where to_char(dif_date,’yyyy-mm-dd')=to_char('2007-07-01′,’yyyy-mm-dd'); 在过滤条件中，可以过滤掉最大数量记录的条件必须放在where子句的末尾：FROM子句中写在最后的表(基础表，driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有三个以上的连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表; 采用绑定变量 在WHERE中尽量不要使用OR 用EXISTS替代IN、用NOT EXISTS替代NOT IN; 避免在索引列上使用计算：WHERE SAL*12>25000; 用IN来替代OR： WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20 避免在索引列上使用IS NULL和IS NOT NULL; 总是使用索引的第一个列;          用UNION-ALL替代UNION; 避免改变索引列的类型：SELECT…FROM EMP WHERE EMPNO=’123’，由于隐式数据类型转换，to_char(EMPNO)=’123’，因此，将不采用索引，一般在采用字符串拼凑动态SQL语句出现; '!=' 将不使用索引; 优化GROUP BY; 避免带有LIKE参数的通配符，LIKE '4YE%’使用索引，但LIKE '%YE’不使用索引 避免使用困难的正规表达式，例如select * from customer where zipcode like "98___"，即便在zipcode上建立了索引，在这种情况下也还是采用顺扫描的方式。如果把语句改成select * from customer where zipcode>"98000"，在执行查询时就会利用索引来查询，显然会大大提高速度; 尽量明确的完成SQL语句，尽量少让数据库工作。比如写SELECT语句时，需要把查询的字段明确指出表名。尽量不要使用SELECT *语句。组织SQL语句的时候，尽量按照数据库的习惯进行组织。 相信每个人都想提高SQL执行效率，那么大家不妨试试这几条建议，肯定不会让大家失望的，希望这些小建议能够对大家有所帮助。

浅谈SQL Server2005的几种分页方法

SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。 用以下脚本生成测试数据：

1、基于CTE分页 1）用row_number()排名函数，派生表的方式分页

2）用CTE方式取代派生表

3）也是CTE方法，但是根据测试数据显示这种性能比前两种都好

2、  基于ROW_COUNT的分页

3、  TOP @X分页 SQL Server 2005中可以把返回行数做为参数传给top语句。

4、  Temp表分页