SQL语句优化总结.doc

SQL语句参考一、常用SQL语句1、 使用group by求和select WEIGHT_TIME=convert(datetime,convert(char(10),WEIGHT_TIME,20),Number=count(*),-重车数CONSIGNEE_WT_COUNT=count(distinct case CONSIGNEE_WT when 0 then 0 else CONSIGNEE_WT end),-收货方件数CONSIGNEE_WT=sum(CONSIGNEE_WT)-收货方吨位From MEASUREDATAWHERE WEIGHT_MODE = 重车 AND WEIGHT_TIME 2009-3-17 23:59:59group by convert(datetime,convert(char(10),WEIGHT_TIME,20)1、 按姓氏笔画排序 Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 1、 分页SQL语句 select * from(select (row_number() OVER (ORDER BY tab.ID Desc) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 结束位置 1、 获取当前数据库中的所有用户表 select * from sysobjects where xtype=U and category=0 1、 获取某一个表的所有字段 select name from syscolumns where id=object_id(表名) 1、 查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名% 1、 查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype=P 1、 查询用户创建的所有数据库 select * from master.sysdatabases D where sid not in(select sid from master.syslogins where name=sa) 或者 select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01 1、 查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = 表名 1、 使用事务 在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题 开始事务 Begin tran Insert Into TableName Values() SQL语句操作不正常，则回滚事务。 回滚事务 Rollback tran SQL语句操作正常，则提交事务，数据提交至数据库。 提交事务 Commit tran 1、 按全文匹配方式查询 字段名 LIKE N%a-zA-Z0-9Chinaa-zA-Z0-9% OR 字段名 LIKE N%a-zA-Z0-9China OR 字段名 LIKE NChinaa-zA-Z0-9% OR 字段名 LIKE NChina 1、 计算执行SQL语句查询时间 declare d datetime set d=getdate() select * from SYS_ColumnProperties select 语句执行花费时间(毫秒)=datediff(ms,d,getdate() 1、 说明：几个高级查询运算词 A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。三、数据库的导入、导出和保存一、 数据库Excel的相关操作(1) 在查询分析器下查询Excel文档 SELECT * FROM OpenDataSource(Microsoft.Jet.OLEDB.4.0,Data Source=c:测试.xls; User ID=Admin;Password=;Extended properties=Excel 8.0).Sheet1$ SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).Sheet1$下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT * FROM OpenDataSource ( Microsoft.Jet.OLEDB.4.0, Data Source=c:Financeaccount.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions(2) 从数据库中导出数据并存到文件中 EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN out c:temp1.xls -c -q -S. -Usa -P EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN out c:temp1.txt -c -q -S. -Usa -P (3) 从文件中导入数据到数据库对应表中 EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN in c:temp1.xls -c -q -S. -Usa -P EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN in c:temp1.txt -c -q -S. -Usa -P (4) 导出到excel EXEC master.xp_cmdshell bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q -SGNETDATA/GNETDATA -Usa -P (5) 导入Excel SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:test.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions (6) EXCEL导到远程SQL insert OPENDATASOURCE( SQLOLEDB, Data Source=远程ip;User ID=sa;Password=密码 ).库名.dbo.表名 (列名1,列名2) SELECT 列名1,列名2 FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:test.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions/*动态文件名 declare fn varchar(20),s varchar(1000) set fn = c:test.xls set s =Microsoft.Jet.OLEDB.4.0, Data Source=+fn+;User ID=Admin;Password=;Extended properties=Excel 5.0 set s = SELECT * FROM OpenDataSource (+s+).sheet1$ exec(s) */ SELECT cast(cast(科目编号 as numeric(10,2) as nvarchar(255)+ 转换后的别名 FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:test.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions 二、 文本文件的操作(1) 导入文本文件 EXEC master.xp_cmdshell bcp dbname.tablename in c:DT.txt -c -Sservername -Usa -Ppassword (2) 导出文本文件 EXEC master.xp_cmdshell bcp dbname.tablename out c:DT.txt -c -Sservername -Usa -Ppassword 或 EXEC master.xp_cmdshell bcp Select * from dbname.tablename queryout c:DT.txt -c -Sservername -Usa -Ppassword (3) 导出到TXT文本，用逗号分开 exec master.xp_cmdshell bcp 库名.表名 out d:tt.txt -c -t ,-U sa -P password BULK INSERT 库名.表名 FROM c:test.txt WITH ( FIELDTERMINATOR = ;, ROWTERMINATOR = n ) -/* dBase IV文件 select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0 ,dBase IV;HDR=NO;IMEX=2;DATABASE=C:,select * from 客户资料4.dbf) -*/ -/* dBase III文件 select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0 ,dBase III;HDR=NO;IMEX=2;DATABASE=C:,select * from 客户资料3.dbf) -*/ -/* FoxPro 数据库 select * from openrowset(MSDASQL, Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:, select * from aa.DBF) -*/ /*导入DBF文件*/ select * from openrowset(MSDASQL, Driver=Microsoft Visual FoxPro Driver; SourceDB=e:VFP98data; SourceType=DBF, select * from customer where country != USA order by country) go /* 导出到DBF */ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 insert into openrowset(MSDASQL, Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:, select * from aa.DBF) select * from 表 说明: SourceDB=c: 指定foxpro表所在的文件夹 aa.DBF 指定foxpro表的文件名. 三、 Access的操作/*导出到Access*/ insert into openrowset(Microsoft.Jet.OLEDB.4.0, x:A.mdb;admin;,A表) select * from 数据库名.B表 /*导入Access*/ insert into B表 selet * from openrowset(Microsoft.Jet.OLEDB.4.0, x:A.mdb;admin;,A表) 文件名为参数 declare fname varchar(20) set fname = d:test.mdb exec(SELECT a.* FROM opendatasource(Microsoft.Jet.OLEDB.4.0, +fname+;admin;, topics) as a ) SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=f:northwind.mdb;Jet OLEDB:Database Password=123;User ID=Admin;Password=;).产品 Transact-SQL语句进行导入导出：1.在SQL SERVER里查询access数据:- =SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:DB.mdb;User ID=Admin;Password=).表名-2.将access导入SQL server - =在SQL SERVER 里运行:SELECT *INTO newtableFROM OPENDATASOURCE (Microsoft.Jet.OLEDB.4.0, Data Source=c:DB.mdb;User ID=Admin;Password= ).表名-3.将SQL SERVER表里的数据插入到Access表中- =在SQL SERVER 里运行：insert into OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source= c:DB.mdb;User ID=Admin;Password=).表名 (列名1,列名2)select 列名1,列名2 from sql表实例：insert into OPENROWSET(Microsoft.Jet.OLEDB.4.0, C:db.mdb;admin;, Test) select id,name from TestINSERT INTO OPENROWSET(Microsoft.Jet.OLEDB.4.0, c:trade.mdb; admin; , 表名)SELECT *FROM sqltablename-二、SQL SERVER 和EXCEL的数据导入导出1、-2、将Excel的数据导入SQL server :- =SELECT * into newtableFROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).Sheet1$实例:SELECT * into newtableFROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:Financeaccount.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions-3、将SQL SERVER中查询到的数据导成一个Excel文件- =T-SQL代码：EXEC master.xp_cmdshell bcp 库名.dbo.表名out c:Temp.xls -c -q -Sservername -Usa -P参数：S 是SQL服务器名；U是用户；P是密码说明：还可以导出文本文件等多种格式实例:EXEC master.xp_cmdshell bcp saletesttmp.dbo.CusAccount out c:temp1.xls -c -q -Spmserver -Usa -PsaEXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout C: authors.xls -c -Sservername -Usa -Ppassword在VB6中应用ADO导出EXCEL文件代码： Dim cn As New ADODB.Connectioncn.open Driver=SQL Server;Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;cn.execute master.xp_cmdshell bcp SELECT col1, col2 FROM 库名.dbo.表名 queryout E:DT.xls -c -Sservername -Usa -Ppassword-4、在SQL SERVER里往Excel插入数据:- =insert into OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:Temp.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).table1 (A1,A2,A3) values (1,2,3)T-SQL代码：INSERT INTO OPENDATASOURCE(Microsoft.JET.OLEDB.4.0, Extended Properties=Excel 8.0;Data source=C:traininginventur.xls).Filiale1$ (bestand, produkt) VALUES (20, Test) -总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！EXEC master.xp_cmdshell bcp select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders queryout d:Oreders.txt -t| -c -q -S127.0.0.1 -Usa -P SELECT * FROM OpenDataSource(Microsoft.Jet.OLEDB.4.0,Data Source=xxxx.xls;Extended Properties=Excel 8.0;IMEX=1;Persist Security Info=False).a1$ 二、SQL SERVER 和EXCEL的数据导入导出1、在SQL SERVER里查询Excel数据:- =SELECT * FROM OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).Sheet1$下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。SELECT * FROM OpenDataSource ( Microsoft.Jet.OLEDB.4.0, Data Source=c:Financeaccount.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions-2、将Excel的数据导入SQL server :- =SELECT * into newtableFROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:book1.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).Sheet1$实例:SELECT * into newtableFROM OpenDataSource( Microsoft.Jet.OLEDB.4.0, Data Source=c:Financeaccount.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).xactions-3、将SQL SERVER中查询到的数据导成一个Excel文件- =T-SQL代码：EXEC master.xp_cmdshell bcp 库名.dbo.表名out c:Temp.xls -c -q -Sservername -Usa -P参数：S 是SQL服务器名；U是用户；P是密码说明：还可以导出文本文件等多种格式实例:EXEC master.xp_cmdshell bcp saletesttmp.dbo.CusAccount out c:temp1.xls -c -q -Spmserver -Usa -PsaEXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout C: authors.xls -c -Sservername -Usa -Ppassword在VB6中应用ADO导出EXCEL文件代码： Dim cn As New ADODB.Connectioncn.open Driver=SQL Server;Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;cn.execute master.xp_cmdshell bcp SELECT col1, col2 FROM 库名.dbo.表名 queryout E:DT.xls -c -Sservername -Usa -Ppassword-4、在SQL SERVER里往Excel插入数据:- =insert into OpenDataSource( Microsoft.Jet.OLEDB.4.0,Data Source=c:Temp.xls;User ID=Admin;Password=;Extended properties=Excel 5.0).table1 (A1,A2,A3) values (1,2,3)T-SQL代码：INSERT INTO OPENDATASOURCE(Microsoft.JET.OLEDB.4.0, Extended Properties=Excel 8.0;Data source=C:traininginventur.xls).Filiale1$ (bestand, produkt) VALUES (20, Test) 1.sql 导出到 access select * into goods from ODBC;Driver=SQL Server;Server=.;Database=kpsj;Uid=sa;Pwd=123456.goods 2.sql从access导入 insert into ODBC;Driver=SQL Server;Server=.;Database=kpsj;Uid=sa;Pwd=123456.goods values(kk) 四、其他常用SQL语句1. 分页查询数据 -查询到行的结果 select * from( select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass ) a where row between 20 and 30 select * from Sys_DocClass 2、增加异常处理 try . catch SET XACT_ABORT ON - 打开try功能 BEGIN TRY begin tran insert into Sys_DocClass values(.) -数据表操作语句 commit tran -提交事务 print commited END TRY BEGIN CATCH rollback tran -回滚事务 print rolled back END CATCH 3、通用表达式CTE，可以简化嵌套SQL -例：结合通用表达式进行分页 WITH DocClasses AS( select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass ) select ID,DocClassName,DocClassDesc from DocClasses where row between 20 and 301. select top m * from tablename where id not in (select top n id from tablename) 2. select top m * into 临时表(或表变量) from tablename order by columnname - 将top m笔插入 set rowcount n select * from 表变量 order by columnname desc 3. select top n * from (select top m * from tablename order by columnname) a order by columnname desc 4.如果tablename里没有其他identity列，那么： select identity(int) id0,* into #temp from tablename 取n到m条的语句为： select * from #temp where id0 =n and id0 (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); （9） 用TRUNCATE替代DELETE： 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML) （10） 尽量多使用COMMIT： 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 （11） 用Where子句替换HAVING子句： 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里 （12） 减少对表的查询： 在含有子查询的SQL语句中,要特别注意减少对表的查询.例子： SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) （13） 通过内部函数提高SQL效率.： 复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的 （14） 使用表的别名(Alias)： 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. （15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN： 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例子： （高效）SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB) (低效)SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = MELB) （16） 识别低效执行的SQL语句： 虽然目