删除表中重复记录.doc

数据库中删除重复记录一直是件挺烦人的事，本人收集了Oracle跟SqlServer的快速删除重复记录的方法，供大家参考，希望对大家有所帮助。SQL SERVER想必每一位SQL SERVER开发人员都有过类似的经历，在对数据库进行查询或统计的时候不时地会碰到由于表中存在重复的记录而导致查询和统计结果不准确。解决该问题的办法就是将这些重复的记录删除，只保留其中的一条。在SQL Server中除了对拥有十几条记录的表进行人工删除外，实现删除重复记录一般都是写一段代码，用游标的方法一行一行检查，删除重复的记录。因为这种方法需要对整个表进行遍历，所以对于表中的记录数不是很大的时候还是可行的，如果一张表的数据达到上百万条，用游标的方法来删除简直是个噩梦，因为它会执行相当长的一段时间。 四板斧轻松消除重复记录殊不知在SQL Server中有一种更为简单的方法，它不需要用游标，只要写一句简单插入语句就能实现删除重复记录的功能。为了能清楚地表述，我们首先假设存在一个产品信息表Products，其表结构如下：CREATE TABLE Products (ProductID int,ProductName nvarchar (40),Unit char(2),UnitPrice money) 表中的数据如图1：图表 图1中可以看出，产品Chang和Tofu的记录在产品信息表中存在重复。现在要删除这些重复的记录，只保留其中的一条。步骤如下：第一板斧建立一张具有相同结构的临时表CREATE TABLE Products_temp (ProductID int,ProductName nvarchar (40),Unit char(2),UnitPrice money) 第二板斧为该表加上索引，并使其忽略重复的值方法是在企业管理器中找到上面建立的临时表Products _temp，单击鼠标右键，选择所有任务，选择管理索引，选择新建。如图2所示。按照图2中圈出来的地方设置索引选项。图2 第三板斧拷贝产品信息到临时表insert into Products_temp Select * from Products 此时SQL Server会返回如下提示：服务器: 消息 3604，级别 16，状态 1，行 1已忽略重复的键。它表明在产品信息临时表Products_temp中不会有重复的行出现。第四板斧将新的数据导入原表将原产品信息表Products清空，并将临时表Products_temp中数据导入，最后删除临时表Products_temp。delete Productsinsert into Products select * from Products_tempdrop table Products_temp 这样就完成了对表中重复记录的删除。无论表有多大，它的执行速度都是相当快的，而且因为几乎不用写语句，所以它也是很安全的。小提示：上述方法中删除重复记录取决于创建唯一索引时选择的字段，在实际的操作过程中读者务必首先确认创建的唯一索引字段是否正确，以免将有用的数据删除。 ORACLE在Oracle中，可以通过唯一rowid实现删除重复记录；还可以建临时表来实现.这个只提到其中的几种简单实用的方法，希望可以和大家分享（以表employee为例）。SQL desc employeeName Null? Typeemp_id NUMBER(10)emp_name VARCHAR2(20)salary NUMBER(10,2)可以通过下面的语句查询重复的记录：SQL select * from employee;EMP_ID EMP_NAME SALARY1 sunshine 100001 sunshine 100002 semon 200002 semon 200003 xyz 300002 semon 20000SQL select distinct * from employee;EMP_ID EMP_NAME SALARY1 sunshine 100002 semon 200003 xyz 30000SQL select * from employee group by emp_id,emp_name,salary having count (*)1EMP_ID EMP_NAME SALARY1 sunshine 100002 semon 20000SQL select * from employee e1 where rowid in (select max(rowid) from employe e2 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);EMP_ID EMP_NAME SALARY1 sunshine 100003 xyz 300002 semon 20000 2. 删除的几种方法：（1）通过建立临时表来实现SQLcreate table temp_emp as (select distinct * from employee) SQL truncate table employee; (清空employee表的数据）SQL insert into employee select * from temp_emp; (再将临时表里的内容插回来）( 2）通过唯一rowid实现删除重复记录.在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大或最小rowid的就可以了，其余全部删除。SQLdelete from employee e2 where rowid not in (select max(e1.rowid) from employee e1 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);-这里用min(rowid)也可以。SQLdelete from employee e2 where rowid delete from employee where rowid not in (select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);-这里用min(rowid)也可以。EMP_ID EMP_NAME SALARY1 sunshine 100003 xyz 300002 semon 20000 answer505 发表于:2005.10.26 15:24 :分类: ( 一般分类 ) :阅读:(114次) : 评论 (0) : 引用 (0) 从Oracle迁移到SQL Server的陷阱在把Oracle查询转换为SQL Server的时候要特别当心一些不容易注意到的问题。我们知道，T-SQL是SQL Server的语言引擎，而Oracle的语言引擎却是PLSQL。这两种查询语言都对ANSI SQL-92标准进行了扩展以提供额外的支持力度。你所创建的应用程序几乎都要用到这些补充特性。本文就对最常用的、非标准的Oracle扩展进行了说明，同时还要介绍下如何对这些扩展进行转化以用在SQL Server环境下。列的选择用PLSQL执行数据查询的时候，FROM子句是必须的，这同SQL Server的要求是一样的。 SELECT语句必须选择针对的数据表。在Oracle数据库内有一种特殊的表DUAL。DUAL表由Oracle连同数据字典一同创建，所有的用户都可以用名称DUAL访问该表。这个表里只有一列DUMMY，该列定义为VARCHAR2(1)类型，有一行值X。从DUAL表选择数据常被用来通过SELECT语句计算常数表达式，由于DUAL只有一行数据，所以常数只返回一次。Oracle下的DUAL查询如下所示：SELECT x FROM dual而对等的SQL Server查询则是下面这个样子：SELECT x连接Oracle用| 符号作为连接符，而SQL Server的连接符是加号：+ 。Oracle查询如下所示：Select Name | Last Name From tableName对应的SQL Server查询如下所示：Select Name + Last Name数字取舍Oracle数据库内有一个TRUNC函数，该函数返回m位十进制数的n位；如果省略m则n就是0位。m的值可以为负，表示截去小数点左边m位数字。在SQL Server下可以用Round或者Floor。以下是Oracle查询：SELECT TRUNC(15.79,1) Truncate FROM DUAL;下面是同类查询的SQL Server版本：SELECT ROUND(15.79, 0) rounded , ROUND(15.79, 0,1) truncatedSELECT FLOOR(ROUND(15.79, 0), FLOOR(ROUND(15.79, 0,1) )数字转换Oracle的TO_CHAR函数可以把n位NUMBER数据类型转换为VARCHAR2 数据类型，同时采用可选的数字格式。SQL Server则通过STR函数返回数字转换之后的字符数据。不过，该函数不具方便的Format参数。Oracle查询如下：SELECT to_char(123.45 ,99999999999999) from tabSELECT to_char(EXPIRY_DATE,DDMONYYYY) from tab以下是SQL Server版本的查询：SELECT STR(123.45, 14)SELECT STR(round(123.455 , 2),12,2)SELECT CAST(REPLACE(CONVERT(varchar(12) , EXPIRYDATE, 106 ), , ) as varchar(9)LENGTH和LEN以下是Oracle的查询：SELECT LENGTH(SQLMAG) Length in characters FROM DUAL;以上查询在SQL Server下是这样写的：SELECT LEN(SQLMAG) Length in characters日期以上两种系统都具有各自的当前日期和时间格式。Oracle取得日期和采用如下方式：SYSDATESQL Server则是这样的：GETDATE()你可以用各种语法操作日期。以下的代码对Oracle日期值中的月份进行了格式调整（返回日期再加上n月）：Select add_months(sysdate,12) from dualSQL Server则是如下完成同等功能的：Select dateadd(mm,12,getdate()数据的减法也不同。以下代码在Oracle中直接对数据进行减法操作：SELECT sysdate -add_months(sysdate,12) FROM dualSQL Server则是这样做的：SELECT datediff(dd, GetDate(),dateadd(mm,12,getdate()小结幸而，我在从Oracle迁移到SQL Server 2000的过程中并没有遭遇太棘手的问题。当然，一开始我就深知系统之间肯定存在显著的差异。我希望本文列出的差别有助于你避免一些常见的问题。answer505 发表于:2005.10.26 15:23 :分类: ( 一般分类 ) :阅读:(94次) : 评论 (0) : 引用 (0) 利用MSSQL sp自制未公开的加密函数如果对MSSQL的用户信息有兴趣的，可能会发现master.dbo.sysxlogins里面存放着用户的口令，可是呢，password字段如果不是null就是一堆看不懂的binary，这个口令是怎么加密的呢？其实只要仔细看看master.dbo.sp_addlogin就知道了，MSSQL的sp都可以看到代码，真是不错。让我们来看看它是怎么做的，注意这一行select passwd = pwdencrypt(passwd)，这个时后passwd就被加密了，让我们也来试一下DECLARE ClearPWD varchar(255) DECLARE EncryptedPWD varbinary(255)SELECT ClearPWD = testSELECT EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(ClearPWD)SELECT EncryptedPWD看上去不错，确实被加密了，可是我怎么还原呢？ 呵呵，这就没戏了，口令加密都是单向的，用加密后的密文来比较就可以了。继续看看其它用户相关的sp，可以发现master.dbo.sp_password里面有口令比较的内容。pwdcompare(old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)不用去理会xstatus，这是一个状态掩码，一般我们用的时候就直接用0就可以了DECLARE ClearPWD varchar(255) DECLARE EncryptedPWD varbinary(255)SELECT ClearPWD = testSELECT EncryptedPWD = CONVERT(varbinary(255), pwdencrypt(ClearPWD)SELECT pwdcompare(ClearPWD, EncryptedPWD, 0)SELECT pwdcompare(ErrorPassword, EncryptedPWD, 0)这样我们就可以使用这两个函数来加密自己的密码了:) 。answer505 发表于:2005.10.26 15:16 :分类: ( 一般分类 ) :阅读:(111次) : 评论 (0) : 引用 (0) 存储过程编写经验和优化措施一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对Sybase和SQL Server数据库，但其它数据库应该有一些共性。二、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。三、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。四、 内容：1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：a) SQL的使用规范：i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。vii. 尽量使用“=”，不要使用“”。viii. 注意一些or子句和union子句之间的替换ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。x. 注意存储过程中参数和数据类型的关系。xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。b) 索引的使用规范：i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引iii. 避免对大表查询时进行table scan，必要时考虑新建索引。iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。v. 要注意索引的维护，周期性重建索引，重新编译存储过程。c) tempdb的使用规范：i. 尽量避免使用distinct、order by、group by、having、join、*pute，因为这些语句会加重tempdb的负担。ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。d) 合理的算法使用：根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等%大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户 -更改某个表exec sp_changeobjectowner tablename,dbo-存储更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatchOldOwner as NVARCHAR(128),NewOwner as NVARCHAR(128)ASDECLARE Name as NVARCHAR(128)DECLARE Owner as NVARCHAR(128)DECLARE OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FOR select Name = name,Owner = user_name(uid)from sysobjectswhere user_name(uid)=OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO Name, OwnerWHILE(FETCH_STATUS=0)BEGIN if Owner=OldOwner beginset OwnerName = OldOwner + . + rtrim(Name)exec sp_changeobjectowner OwnerName, NewOwnerend- select name,NewOwner,OldOwnerFETCH NEXT FROM curObject INTO Name, OwnerENDclose curObjectdeallocate curObjectGOSQL SERVER中直接循环写入数据没什么好说的了，大家自己看，有时候有点用处 declare i intset i=1while i30begininsert into test (userid) values(i)set i=i+1end无数据库日志文件恢复数据库方法两则数据库日志文件的误删或别的原因引起数据库日志的损坏 方法一1.新建一个同名的数据库2.再停掉sql server(注意不要分离数据库)3.用原数据库的数据文件覆盖掉这个新建的数据库4.再重启sql server5.此时打开企业管理器时会出现置疑，先不管，执行下面的语句（注意修改其中的数据库名)6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了.USE MASTERGOSP_CONFIGURE ALLOW UPDATES,1 RECONFIGURE WITH OVERRIDEGOUPDATE SYSDATABASES SET STATUS =32768 WHERE NAME=置疑的数据库名Gosp_dboption 置疑的数据库名, single user, trueGoDBCC CHECKDB(置疑的数据库名) Goupdate sysdatabases set status =28 where name=置疑的数据库名Gosp_configure allow updates, 0 reconfigure with overrideGo sp_dboption 置疑的数据库名, single user, falseGo方法二事情的起因昨天，系统管理员告诉我，我们一个内部应用数据库所在的磁盘空间不足了。我注