学习SQL应知道的动态SQL语句基本语法
1、普通SQL语句可以用Exec执行eg:Select*fromtableNameExec('select*fromtableName')Execsp_executesqlN'select*fromtableName'--请注意字符串前一定要加N2、字段名，表名，数据库名之类作为变量时，必须用动态SQL、字段名，表名，数据库名之类作为变量时，eg:declare@fnamevarchar(20)set@fname='FiledName'Select@fnamefromtableName--错误,不会提示错误，但结果为固定值FiledName,并非所要。Exec('select'+@fname+'fromtableName')--请注意加号前后的单引号的边上加空格当然将字符串改成变量的形式也可declare@fnamevarchar(20)set@fname='FiledName'--设置字段名declare@svarchar(1000)set@s='select'+@fname+'fromtableName'Exec(@s)--成功execsp_executesql@s--此句会报错
declare@sNvarchar(1000)--注意此处改为nvarchar(1000)set@s='select'+@fname+'fromtableName'Exec(@s)--成功execsp_executesql@s--此句正确3、输出参数、declare@numint,@sqlsnvarchar(4000)set@sqls='selectcount(*)fromtableName'exec(@sqls)--如何将exec执行结果放入变量中？declare@numint,@sqlsnvarchar(4000)set@sqls='select@a=count(*)fromtableName'execsp_executesql@sqls,N'@aintoutput',@numoutput
select@num此外，如果想要在SQL语句字符串中使用单引号''可以使用''''
SQLServer数据库中经常用到的identity列
发布时间：2008.03.2404:59
来源：赛迪网
作者：Alice
【赛迪网－IT技术报道】SQLServer中，经常会用到Identity标识列，这种自增长的字赛迪网－技术报道】段操作起来的确是比较方便。但它有时还会带来一些麻烦。示例一：当表中被删除了某些数据的时候，自增长列的编号就不再是一个连线的数列。这种时候我们可以用以下方案来解决。
SETIDENTITY_INSERT[TABLE][ON|OFF]
允许将显式值插入表的标识列中，当设置为ON时，这时可能在INSERT操作时手工指定插入到标识列中的编号，同时必须在操作完成后，将IDENTITY_INSERT还原成OFF，否则下次插入的时候必须指定编号，那不然就无法完成INSERT操作。示例二：示例二：当表中的记录被全部删除，但此时标识列的值越来越大的时候，如果不加以重置，它还会无休止的增长。这个时候我们就要用到:
DBCCCHECKIDENT（TABLE,[RESEED|NORESEED],[1])
将把指定表的种子值强制重设为1。然而，你可能不想将种子重设为1，在这种情况下，你可以用你想用的种子值替代第三个参数。有时候你可能想知道当前的种子，而不是想重设种子,这时你就要用到NORESEED，而不用再去顾忌第三个参数。
“一网打尽通用SQL数据库的查询语句（1）一网打尽”通用一网打尽）
发布时间：2008.01.0404:40
来源：赛迪网
作者：20933
通用SQL数据库的查询语句：（注释：本文中Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。）一、简单查询简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。例如，下面的语句查询testtable表中为“张三”的nickname字段和email字段。
SELECTFROMWHERE
(一)选择列表一
nickname,emailtesttablename='张三'
选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。1、选择所有列、例如，下面语句显示testtable表中所有列的数据:
SELECTFROM
2、选择部分列并指定它们的显示次序、
*
testtable
查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。
例如:
SELECT
nickname,email
FROM
3、更改列标题、
testtable
在选择列表中，可重新指定列标题。定义格式为:列标题=列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题:
SELECT
昵称=nickname,电子邮件=emailFROMtesttable
4、删除重复行、SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。5、限制返回的行数、使用TOPn[PERCENT]选项限制返回的数据行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。
例如:TOP
(二)FROM子句二
SELECT20
TOP
2*
*FROMFROM
testtabletesttable
SELECTPERCENT
FROM子句指定SELECT语句查询及与查询相关的表或视图。FROM子句中最多可在指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定:
SELECTFROMWHERE
username,citytable.cityidusertable,citytable
usertable.cityid=citytable.cityid
在FROM子句中可用以下两种格式为表或视图指定别名:表名as别名表名别名例如上面语句可用表的别名格式表示为:
SELECTFROM
username,b.cityida,citytableb
usertableWHERE
a.cityid=b.cityid
SELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。
例如:FROM
SELECTauthors(SELECTFROMWHERE)WHERE
a.au_fname+a.au_lnamea,titleauthortitle_id,titletitlesta
ytd_sales>10000ASt
a.au_id=ta.au_id
AND
ta.title_id=t.title_id
此例中，将SELECT返回的结果集合给予一别名t，然后再从中检索数据。(三)使用WHERE子句设置查询条件三WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据:
SELECTFROMWHERE
WHERE子句可包括各种条件运算符:
*
usertableage>20
比较运算符(大小比较):>、>=、=、<、<=、<>、!>、!<范围运算符(表达式值是否在指定的范围):BETWEEN…AND…NOTBETWEEN…AND…列表运算符(判断表达式是否为列表中的指定项):IN(项1,项2……)NOTIN(项1,项2……)模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOTLIKE空值判断符(判断表达式是否为空):ISNULL、NOTISNULL逻辑运算符(用于多条件的逻辑连接):NOT、AND、OR1、范围运算符例:ageBETWEEN10AND30相当于age>=10ANDage<=302、列表运算符例:countryIN('Germany','China')3、模式匹配符例:常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。可使用以下通配字符:
百分号%:可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%。下划线_:匹配单个任意字符，它常用来限制表达式的字符长度。方括号[]:指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。[^]:其取值也[]相同，但它要求所匹配对象为指定字符以外的任一个字符。例如:限制以Publishing结尾，使用LIKE'%Publishing'限制以A开头:LIKE'[A]%'限制以A开头外:LIKE'[^A]%'4、空值判断符例WHEREageISNULL5、逻辑运算符:优先级为NOT、AND、OR(四)查询结果排序四查询结果排序使用ORDERBY子句对查询返回的结果按一列或多列排序。ORDERBY子句的语法格式为:
ORDER
BY
{column_name[,…n]
[ASC&;brvbar;DESC]}
其中ASC表示升序，为默认值，DESC为降序。ORDERBY不能按ntext、和imagetext数据类型进行排序。
例如:FROMORDERBYage
SELECTusertable
*
desc,userid
ASC
另外，可以根据表达式进行排序。二、联合查询UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，即执行联合查询。UNION的语法格式为:
select_statementUNION[UNION[ALL][ALL]selectstatementselectstatement][…n]
其中selectstatement为待联合的SELECT查询语句。ALL选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中的重复行将只保留一行。联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。在使用UNION运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。例如:查询1UNION(查询2UNION查询3)三、连接查询通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。连接可以在SELECT语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。SQL-92标准所定义的FROM子句的连接语法格式为:
FROM
join_table
join_type
join_table
[ON
(join_condition)]
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。join_type指出连接类型，可分为三种:内连接、外连接和交叉连接。内连接(INNERJOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFTOUTERJOIN或LEFTJOIN)、右外连接(RIGHTOUTERJOIN或RIGHTJOIN)和全外连接(FULLOUTERJOIN或FULLJOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。交叉连接(CROSSJOIN)没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。连接操作中的ON(join_condition)子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如:
SELECTFROM
p1.pub_id,p2.pub_id,p1.pr_infopub_infoASp1ASONINNERp2JOIN
pub_info
DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
(一)内连接一内连接内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种:1、等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
2、不等连接:在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。3、自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社:
SELECTFROMauthorsASa
*INNERpJOIN
publishersON
AS
a.city=p.city
又如使用自然连接，在选择列表中删除authors和publishers表中重复列(city和state):
SELECTFROM
a.*,p.pub_id,p.pub_name,p.countryauthorsASaASINNERpJOIN
publishersON
(二)外连接二外连接
a.city=p.city
内连接时，返回查询结果集合中的仅是符合查询条件(WHERE搜索条件或HAVING条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。如下面使用左外连接将论坛内容和作者信息连接起来:
SELECT
a.*,b.*
FROM
luntanasb
LEFT
JOIN
usertableON
a.username=b.username
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市:
SELECTFROMcityasa
a.*,b.*FULLOUTERJOINuser
asON
(三)交叉连接三交叉连接
b
a.username=b.username
交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等。
于6*8=48行。FROMtitles
SELECTCROSSBY
type,pub_nameJOINtypepublishers
ORDER
[Post=0][/Post]
教你快速掌握一些异常精妙的"SQL"语句语句教你快速掌握一些异常精妙的
发布时间：2008.03.1104:58
来源：赛迪网
作者：钱海歌
【赛迪网－IT技术报道】精妙的精妙的"SQL"语句语句:精妙的语句◆复制表(只复制结构,源表名：a新表名：b)
SQL:select*intobfromawhere1<>1
◆拷贝表(拷贝数据,源表名：a目标表名：b)
SQL:insertintob(a,b,c)selectd,e,ffromb;
◆显示文章、提交人和最后回复时间
SQL:selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
◆说明：外连接查询(表名1：a表名2：b)
SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
◆日程安排提前五分钟提醒
SQL:select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
◆两张关联表，删除主表中已经在副表中没有的信息
SQL:deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)
◆说明：
SQL:SELECTA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATEFROMTABLE1,(SELECTX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATEFROM(SELECTNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHANDFROMTABLE2WHERETO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,(SELECTNUM,UPD_DATE,STOCK_ONHANDFROMTABLE2WHERETO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')&;brvbar;&;brvbar;'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y,WHEREX.NUM=Y.NUM（+）ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)BWHEREA.NUM=B.NUM
◆说明：
SQL:select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&;strdepartmentname&;"'and专业名称='"&;strprofessionname&;"'orderby性别,生源地,高考总成绩
实例讲解SQLServer中"Update"的用法的用法
发布时间：2008.02.2805:07
来源：赛迪网
作者：Alizze
SQLServer中"Update"的用法：的用法：的用法例子：例子：在表中有两个字段：id_no(varchar),in_date(datetime)，in_date相同的记录的in_date把依次累加1秒，使in_date没有相同的记录。以下为原始的数据:以下为原始的数据
id_no57912003-9-157922003-9-157942003-9-157952003-9-157962003-9-157972003-9-158312003-9-158322003-9-158332003-9-1
结果为：结果为：
in_date14:42:0214:42:0214:42:0214:42:0314:42:0314:42:0314:42:0414:42:1414:42:14
id_no57912003-9-157922003-9-157942003-9-157952003-9-157962003-9-157972003-9-158312003-9-158322003-9-158332003-9-1
in_date14:42:0214:42:0314:42:0414:42:0514:42:0614:42:0714:42:0814:42:1414:42:15
处理的方法：处理的方法：
--建立测试环境createtablea(id_novarchar(8),in_datedatetime)goinsertintoaselect'5791','2003-9-114:42:02'unionallselect'5792','2003-9-114:42:02'unionallselect'5794','2003-9-114:42:02'unionallselect'5795','2003-9-114:42:03'unionallselect'5796','2003-9-114:42:03'unionallselect'5797','2003-9-114:42:03'unionallselect'5831','2003-9-114:42:04'unionallselect'5832','2003-9-114:42:04'unionallselect'5833','2003-9-114:42:04'unionallselect'5734','2003-9-114:42:02'unionallselect'6792','2003-9-114:42:22'unionallselect'6794','2003-9-114:42:22'unionallselect'6795','2003-9-114:42:23'unionallselect'6796','2003-9-114:42:23'unionallselect'6797','2003-9-114:42:23'unionallselect'6831','2003-9-114:42:34'unionallselect'6832','2003-9-114:42:34'unionallselect'6833','2003-9-114:42:54'unionallselect'6734','2003-9-114:42:22'go--生成临时表，按照in_date排序select*into#fromaorderbyin_date--相同的时间，加一秒。加完了不带重复的declare@date1datetime,@date2datetime,@datedatetimeupdate#set@date=casewhen@date1=in_dateor@date2>=in_date
thendateadd(s,1,@date2)elsein_dateend,@date1=in_date,@date2=@date,in_date=@date--更新到基本表中去updateaseta.in_date=b.in_datefromaajoin#bona.id_no=b.id_noselect*fromadroptable#,a
三种数据库利用SQL语句进行高效果分页
发布时间：2008.01.2104:50
来源：赛迪网
作者：10687
在程序的开发过程中，处理分页是大家接触比较频繁的事件，因为现在软件基本上都是与数据库进行挂钓的。但效率又是我们所追求的，如果是像原来那样把所有满足条件的记录全部都选择出来，再去进行分页处理，那么就会多多的浪费掉许多的系统处理时间。为了能够把效率提高，所以现在我们就只选择我们需要的数据，减少数据库的处理时间，以下就是常用SQL分页处理：1、SQLServer、Access数据库、、这都微软的数据库，都是一家人，基本的操作都是差不多，常采用如下分页语句：PAGESIZE：每页显示的记录数CURRENTPAGE：当前页号数据表的名字是：components索引主键字是：id
selecttopPAGESIZE*fromcomponentswhereidnotin
(selecttop(PAGESIZE*(CURRENTPAGE-1))idfromcomponentsorderbyid)orderbyid
如下列：
selecttop10*fromcomponentswhereidnotin(selecttop10*10idfromcomponentsorderbyid)orderbyid从101条记录开始选择，只选择前面的10条记录
2、Oracle数据库、因为Oracle数据库没有Top关键字，所以这里就不能够像微软的数据据那样操作，这里有两种方法：(1)、一种是利用相反的。PAGESIZE：每页显示的记录数CURRENTPAGE：当前页号数据表的名字是：components索引主键字是：id
select*fromcomponentswhereidnotin(selectidfromcomponentswhererownum<=(PAGESIZE*(CURRENTPAGE-1)))andrownum<=PAGESIZEorderbyid;
如下例：
select*fromcomponentswhereidnotin(selectidfromcomponentswhererownum<=100)andrownum<=10orderbyid;
从101到记录开始选择，选择前面10条。(2)、使用minus，即中文的意思就是减去。
select*fromcomponentswhererownum<=(PAGESIZE*(CURRENTPAGE-1))minusselect*fromcomponentswhererownum<=(PAGESIZE*(CURRENTPAGE-2));如例：select*fromcomponentswhererownum<=10minusselect*fromcomponentswhererownum<=5;.
(3)、一种是利用Oracle的rownum，这个是Oracle查询自动返回的序号，一般不显示，但是可以通过selectrownumfrom[表名]看到，注意，它是从1到当前的记录总数。
select*from(selectrownumtid,components.*fromcomponentswhererownum<=100)wheretid<=10;
深入讲解SQLServer数据库的嵌套子查询
发布时间：2008.02.0205:05
来源：赛迪网
作者：Liulian
很多人对子查询(subqueries)的使用都感到很迷惑，尤其对于嵌套子查询(即子查询中包含一个子查询)。现在，就让我们追本溯源地探究这个问题。有两种子查询类型：标准和相关。标准子查询执行一次，结果反馈给父查询。相关子查询每行执行一次，由父查询找回。在本文中，我将重点讨论嵌套子查询(nestedsubqueries)(我将在以后介绍相关子查询)。试想这个问题:你想生成一个卖平垫圈的销售人员列表。你需要的数据分散在四个表格中:人员.联系方式(Person.Contact)，人力资源.员工(HumanResources.Employee)，销售.销售订单标题(Sales.SalesOrderHeader)，销售.销售订单详情(Sales.SalesOrderDetail)。在SQLServer中，你从内压式(outside-in)写程序，但从外压式(inside-out)开始考虑非常有帮助，即可以一次解决需要的一个语句。如果从内到外写起，可以检查Sales.SalesOrderDetail表格，在LIKE语句中匹配产品数(ProductNumber)值。你将这些行与Sales.SalesOrderHeader表格连接，从中可以获得销售人员IDs(SalesPersonIDs)。然后使用SalesPersonID连接SalesPersonID表格。最后，使用ContactID连接Person.Contact表格。
USEAdventureWorks;GOSELECTDISTINCTc.LastName,c.FirstNameFROMPerson.ContactcJOINHumanResources.EmployeeeONe.ContactID=c.ContactIDWHEREEmployeeIDIN(SELECTSalesPersonIDFROMSales.SalesOrderHeaderWHERESalesOrderIDIN(SELECTSalesOrderIDFROMSales.SalesOrderDetailWHEREProductIDIN(SELECTProductIDFROMProduction.ProductpWHEREProductNumberLIKE'FW%')));GO
这个例子揭示了有关SQLServer的几个绝妙事情。你可以发现，可以用IN()参数替代SELECT语句。在本例中，有两次应用，因此创建了一个嵌套子查询。我是标准化(normalization)的发烧友，尽管我不接受其荒谬的长度。由于标准化具有各
种查询而增加了复杂性。在这些情况下子查询就显得非常有用，嵌套子查询甚至更加有用。当你需要的问题分散于很多表格中时，你必须再次将它们拼在一起，这时你可能发现嵌套子程序就很有用。
使用SQL视图查出所有的数据库字典
发布时间：2008.01.0709:15
来源：赛迪网
作者：20936
本文中的SQL代码可以在企业管理器、查询分析器中简单执行，直接了当的查出SQLServer2000及SQLServer2005的所有数据字典。（注释：数据库字典包括表结构(分SQLServer2000和SQLServer2005)、索引和主键.外键.约束.视图.函数.存储过程.触发器。）SQLServer2000数据库字典表结构数据库字典—表结构表结构.sql
SELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,'')ELSE''ENDAS表说明,a.colorderAS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS标识,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'
WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主键,b.nameAS类型,a.lengthAS长度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小数位数,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允许空,ISNULL(e.text,'')AS默认值,ISNULL(g.[value],'')AS字段说明,d.crdateAS创建时间,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改时间FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOINdbo.syspropertiesgONa.id=g.idANDa.colid=g.smallidANDg.name='MS_Description'LEFTOUTERJOINdbo.syspropertiesfONd.id=f.idANDf.smallid=0ANDf.name='MS_Description'ORDERBYd.name,a.colorder
SQLServer2005数据库字典表结构数据库字典--表结构表结构.sql
SELECTTOP100PERCENT--a.id,CASEWHENa.colorder=1THENd.nameELSE''ENDAS表名,CASEWHENa.colorder=1THENisnull(f.value,
'')ELSE''ENDAS表说明,a.colorderAS字段序号,a.nameAS字段名,CASEWHENCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1THEN'√'ELSE''ENDAS标识,CASEWHENEXISTS(SELECT1FROMdbo.sysindexessiINNERJOINdbo.sysindexkeyssikONsi.id=sik.idANDsi.indid=sik.indidINNERJOINdbo.syscolumnsscONsc.id=sik.idANDsc.colid=sik.colidINNERJOINdbo.sysobjectssoONso.name=si.nameANDso.xtype='PK'WHEREsc.id=a.idANDsc.colid=a.colid)THEN'√'ELSE''ENDAS主键,b.nameAS类型,a.lengthAS长度,COLUMNPROPERTY(a.id,a.name,'PRECISION')AS精度,ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)AS小数位数,CASEWHENa.isnullable=1THEN'√'ELSE''ENDAS允许空,ISNULL(e.text,'')AS默认值,ISNULL(g.[value],'')AS字段说明,d.crdateAS创建时间,CASEWHENa.colorder=1THENd.refdateELSENULLENDAS更改时间FROMdbo.syscolumnsaLEFTOUTERJOINdbo.systypesbONa.xtype=b.xusertypeINNERJOINdbo.sysobjectsdONa.id=d.idANDd.xtype='U'ANDd.status>=0LEFTOUTERJOINdbo.syscommentseONa.cdefault=e.idLEFTOUTERJOIN
dbo.syspropertiesgONa.id=g.idANDa.colid=g.smallidANDg.name='MS_Description'LEFTOUTERJOINdbo.syspropertiesfONd.id=f.idANDf.smallid=0ANDf.name='MS_Description'ORDERBYd.name,a.colorder
SQLServer数据库字典索引数据库字典--索引索引.sql
SELECTTOP100PERCENT--a.id,CASEWHENb.keyno=1THENc.nameELSE''ENDAS表名,CASEWHENb.keyno=1THENa.nameELSE''ENDAS索引名称,d.nameAS列名,b.keynoAS索引顺序,CASEindexkey_property(c.id,b.indid,b.keyno,'isdescending')WHEN1THEN'降序'WHEN0THEN'升序'ENDAS排序,CASEWHENp.idISNULLTHEN''ELSE'√'ENDAS主键,CASEINDEXPROPERTY(c.id,a.name,'IsClustered')WHEN1THEN'√'WHEN0THEN''ENDAS聚集,CASEINDEXPROPERTY(c.id,a.name,'IsUnique')WHEN1THEN'√'WHEN0THEN''ENDAS唯一,CASEWHENe.idISNULLTHEN''ELSE'√'ENDAS唯一约束,a.OrigFillFactorAS填充因子,c.crdateAS创建时间,c.refdateAS更改时间FROMdbo.sysindexesaINNERJOINdbo.sysindexkeysbONa.id=b.idANDa.indid=b.indidINNERJOINdbo.syscolumnsdONb.id=d.idANDb.colid=
d.colidINNERJOINdbo.sysobjectscONa.id=c.idANDc.xtype='U'LEFTOUTERJOINdbo.sysobjectseONe.name=a.nameANDe.xtype='UQ'LEFTOUTERJOINdbo.sysobjectspONp.name=a.nameANDp.xtype='PK'WHERE(OBJECTPROPERTY(a.id,N'IsUserTable')=1)AND(OBJECTPROPERTY(a.id,N'IsMSShipped')=0)AND(INDEXPROPERTY(a.id,a.name,'IsAutoStatistics')=0)ORDERBYc.name,a.name,b.keyno
SQLServer数据库字典主键外键约束视图函数存储过程触发器数据库字典--主键外键.约束视图.函数存储过程.触发器主键.外键约束.视图函数.存储过程触发器.sql
SELECTDISTINCTTOP100PERCENTo.xtype,CASEo.xtypeWHEN'X'THEN'扩展存储过程'WHEN'TR'THEN'触发器'WHEN'PK'THEN'主键'WHEN'F'THEN'外键'WHEN'C'THEN'约束'WHEN'V'THEN'视图'WHEN'FN'THEN'函数-标量'WHEN'IF'THEN'函数-内嵌'WHEN'TF'THEN'函数-表值'ELSE'存储过程'ENDAS类型,o.nameAS对象名,o.crdateAS创建时间,o.refdateAS更改时间,c.textAS声明语句FROMdbo.sysobjectsoLEFTOUTERJOINdbo.syscommentscONo.id=c.idWHERE(o.xtypeIN('X','TR','C','V','F','IF','TF','FN','P','PK'))AND(OBJECTPROPERTY(o.id,N'IsMSShipped')=0)ORDERBYCASEo.xtypeWHEN'X'THEN'扩展存储过程'WHEN'TR'THEN'触发器'WHEN'PK'THEN'主键'WHEN'F'THEN'外键'WHEN'C'
THEN'约束'WHEN'V'THEN'视图'WHEN'FN'THEN'函数-标量'WHEN'IF'THEN'函数-内嵌'WHEN'TF'THEN'函数-表值'ELSE'存储过程'ENDDESC
两个表间不存在的insert与存在的update
发布时间：2008.02.2905:07
来源：赛迪网
作者：Alice
两个表间,不存在的示例：两个表间不存在的insert与存在的update示例：
IFOBJECT_ID('dbo.sp_showtable_insert')ISNOTNULLBEGINDROPPROCEDUREdbo.sp_showtable_insertIFOBJECT_ID('dbo.sp_showtable_insert')ISNOTNULLPRINT'<<>>'ELSEPRINT'<<>>'ENDgoSETANSI_NULLSONgoSETQUOTED_IDENTIFIERONgocreateprocedure[dbo].[sp_showtable_insert]@tablename1varchar(100),@tablename2varchar(100)asbeginDECLARE@MAX_IDNUMERIC(18,0)
DECLARE@MAX_ID2NUMERIC(18,0)createtable#ins_tab(fgint,col_name1nvarchar(150),col_name_valnvarchar(150),col_name2nvarchar(150),colidnumeric(18,0))insertinto#ins_tab(fg,col_name1,col_name_val,colid)values(0,'INSERTINTO'+@tablename1,'',10)insertinto#ins_tab(fg,col_name1,col_name_val)values(1,'(','')insertinto#ins_tab(fg,col_name1,col_name_val,colid)select2,b.name,'/*'+b.name+'_Value*/',b.colidfromsysobjectsa,syscolumnsbwherea.name=@tablename1anda.id=b.idanda.type='U'insertinto#ins_tab(fg,col_name1,col_name_val)values(3,')','')insertinto#ins_tab(fg,col_name1,col_name_val)values(4,'SELECT','')insertinto#ins_tab(fg,col_name1,col_name_val,colid)select5,'--'+b.name,b.name,b.colidfromsysobjectsa,syscolumnsbwherea.name=@tablename2
anda.id=b.idanda.type='U'
update#ins_tabsetcol_name2=b.namefromsysobjectsa,syscolumnsb,#ins_tabcwherea.name=@tablename2anda.id=b.idanda.type='U'andc.col_name1=b.nameandc.fg=2update#ins_tabsetcol_name_val=CASEwhenisnull(col_name2,'1')='1'THEN'null'+col_name_valelsecol_name2+col_name_valendwherefg=2
delete#ins_tabfrom#ins_tabawherea.fg=5andexists(select1from#ins_tabbwhereb.col_name1=a.col_name_valandb.fg=2)insertinto#ins_tab(fg,col_name1,col_name_val,colid)values(6,'FROM'+@tablename2,'',10)
insertinto#ins_tab(fg,col_name1,col_name_val,colid)values(7,'UPDATE'+@tablename1,'',10)insertinto#ins_tab(fg,col_name1,col_name_val)values(8,'SET','')insertinto#ins_tab(fg,col_name1,col_name_val,colid)SELECT9,''+substring(@tablename1+'.'+col_name1+'',1,60)+'='+@tablename2+'.'+col_name1,'',colidFROM#ins_tabwherefg=2ANDisnull(col_name2,'1')<>'1'insertinto#ins_tab(fg,col_name1,col_name_val)SELECT10,'FROM'+@tablename1+','+@tablename2,''insertinto#ins_tab(fg,col_name1,col_name_val)SELECT11,'WHERE'+@tablename1+'.='+@tablename2+'.',''
SELECT@MAX_ID=MAX(colid)from#ins_tabwherefg=2select0ASFG,'--INSERT'+@tablename1+'FROM'+@tablename2,0AScolidunionselectfg,col_name1,colidfrom#ins_tabwherefg=0unionselectfg,col_name1,colidfrom#ins_tabwherefg=1unionselectfg,CASEWHENcolid=@MAX_IDTHEN''+col_name1ELSE''+col_name1+','ENDAScol_name1,colidfrom#ins_tabwherefg=2unionselectfg,col_name1,colidfrom#ins_tabwherefg=3
unionselectfg,col_name1,colidfrom#ins_tabwherefg=4unionselect5asfg,CASEWHENcolid=@MAX_IDTHEN''+col_name_valELSE''+col_name_val+','ENDAScol_name1,colidfrom#ins_tabwherefg=2unionselect6asfg,col_name1,colidfrom#ins_tabwherefg=6unionselect6ASFG,'WHERENOTEXISTS(SELECT1FROM'+@tablename1+'WHERE'+@tablename1+'.='+@tablename2+'.',21AScolidunionselect8ASFG,'--UPDATE'+@tablename1+'FROM'+@tablename2,0AScolidUNIONselect7asfg,col_name1,colidfrom#ins_tabwherefg=5--UPDATEunionselect8asfg,col_name1,colidfrom#ins_tabwherefg=7unionselect9asfg,col_name1,colidfrom#ins_tabwherefg=8unionselect10asfg,CASEWHENcolid=@MAX_IDTHENcol_name1ELSEcol_name1+','ENDAScol_name1,colidfrom#ins_tabwherefg=9
unionselect11asfg,col_name1,colidfrom#ins_tabwherefg=10unionselect12asfg,col_name1,colidfrom#ins_tabwherefg=11unionselect13asfg,col_name1,colidfrom#ins_tabwherefg=12orderbyfg,coliddroptable#ins_tabendgoSETANSI_NULLSOFFgoSETQUOTED_IDENTIFIEROFFgoIFOBJECT_ID('dbo.sp_showtable_insert')ISNOTNULLPRINT'<<>>'ELSEPRINT'<<>>'go
实现跨多个表格的数据进行组合的SQL语句（1））
发布时间：2008.01.2507:38
来源：赛迪网
作者：武西
在对跨多个表格的数据进行组合时，有时很难搞清楚要使用哪一个SQL句法。我将在
这里对将多个表格中的查询合并至单一声明中的常用方式进行阐述。在这篇文章中的样本查询符合SQL92ISO标准。不是所有的数据库生产商都遵循这项标准，而且很多厂商采取的提升措施会带来一些意料不到的后果。如果你不确定你的数据库是不是支持这些标准，你可以参看生产厂商的有关资料。SELECT一个简单的SELECT声明就是查询多个表格的最基本的方式。你可以在FROM子句中调用多个表格来组合来自多个表格的结果。这里是一个它如何工作的实例：以下为引用的内容：以下为引用的内容：
SELECTtable1.column1,table2.column2FROMtable1,table2WHEREtable1.column1=table2.column1;
这个实例中，我使用点号(table1.column1)来指定专栏来自哪一个表格。如果所涉及的专栏只在一个参考的表格中出现，你就不需要加入完整的名称，但是加入完整名称会对可读性起到帮助。在FROM子句中表格之间由逗号来分隔，你可以加入所需的任意多的表格，尽管一些数据库有一个在引入正式的JOIN声明之前他们可以有效地处理的内容这方面的限制，这个将在下面谈到。这个句法是一个简单的INNERJOIN。一些数据库将它看成与一个外部的JOIN是等同的。WHERE子句告知数据库哪一个区域要做关联，而且它返回结果时，就像列出的表格在给定的条件下组合成一个单独的表格一样。值得注意的是，你的比较条件并不需要与你作为结果组返回的专栏相同。在上面的例子中，table1.column1和table2.column1用来组合表格，但是返回的却是table2.column2。你可以在WHERE子句中使用AND关键字来将这个功能扩展至多于两个的表格。你还可以使用这样的表格组合来限制你的结果而不用实际地从每个表格返回专栏。在下面的例子中，table3与table1匹配，但是我没有从table3返回任何东西来显示。我只是确保来自table1的有关专栏存在于table3之中。注意此例中table3需要在FROM子句中被引用。以下为引用的内容：以下为引用的内容：
SELECTtable1.column1,table2.column2FROMtable1,table2,table3WHEREtable1.column1=
table2.column1ANDtable1.column1=table3.column1;
然而，要注意的是，这个查询多个表格的方式是一个暗指的JOIN。你的数据库可能对事物进行不同的处理，这取决于它所使用的优化引擎。而且，忽略对与WHERE子句的相关特性的定义将会给你带来不愿看到的结果，例如从余下的查询中返回与每一个可能的结果相关的专栏的rogue域，就像在CROSSJOIN之中一样。如果你习惯于你的数据库处理这种类型的声明的方式，且你只对两个或是少数几个表格进行组合，一个简单的SELECT声明就可以达到目的。JOINJOIN的工作方式与SELECT声明是相同的，它从不同的表格中返回一个带有专栏的结果组。在暗含的JOIN之上使用外部JOIN的优势是对你的结果组的更好的控制，而且还可能在涉及很多个表格的情况下提升性能表现。JOIN的类型有几种：LEFT，RIGHT，FULLOUTER，INNER和CROSS。你所使用的类型是由你想要看到的结果所决定的。例如，使用LEFTOUTERJOIN将会从列出的第一个表格中返回所有有关的行，而同时如果没有信息与第一个表格相关的话将潜在地从所列出的第二个表格中加入行。在这里INNERJOIN和暗含的JOIN是不同的，INNERJOIN将只返回那些在两个表格中都有数据的行。对第一个SELECT查询使用如下JOIN声明：以下为引用的内容：以下为引用的内容：
SELECTtable1.column1,table2.column2FROMtable1INNERJOINtable2ONtable1.column1=table2.column1;
子查询子查询，或叫子选择声明，是在一个查询中将结果组作为资源使用的一个途径。他经常被用来对结果进行限制或定义，而不是运行多个查询或操纵应用软件之中的数据。有了子查询，你可以参考表格来决定数据的内含，或是在一些情况下，返回一个专栏，而这个专栏是一个子选择的结果。
下面的例子中使用了两个表格。一个表格中包含了我想要返回的数据，而另一个表格则给出一个比较点来确定什么数据是我确实感兴趣的。以下为引用的内容：
SELECTcolumn1FROMtable1WHEREEXISTS(SELECTcolumn1FROMtable2WHEREtable1.column1=table2.column1);
子查询很重要的一个方面就是性能表现。便利性是有代价的，它取决于你所使用的表格和声明的大小，数量和复杂性，还有你可能会允许你的应用软件做处理工作。每一个查询在被主查询作为资源使用之前，都将被完整地单独处理。如果可能的话，创造性地使用JOIN声明可以以较少的滞后时间提供出相同的信息。（责任编辑：卢兆林）
深入讲解SQLUnion和UnionAll的使用方法
发布时间：2008.03.1904:41
来源：赛迪网
作者：李思
UNION指令的目的是将两个SQL语句的结果合并起来。从这【赛迪网－IT技术报道】赛迪网－技术报道】个角度来看，我们会产生这样的感觉，UNION跟JOIN似乎有些许类似，因为这两个指令都可以由多个表格中撷取资料。UNION的一个限制是两个SQL语句所产生的栏位需要是同样的资料种类。另外，当我们用UNION这个指令时，我们只会看到不同的资料值(类似SELECTDISTINCT)。union只是将两个结果联结起来一起显示，并不是联结两个表……
UNION的语法如下：[SQL语句1]UNION[SQL语句2]假设我们有以下的两个表格，Store_Information表格LosAngelesSanDiegoLosAngelesBoston$250$300$700store_nameSalesDate$1500Jan-05-1999Jan-07-1999Jan-08-1999Jan-08-1999
InternetSales表格Jan-07-1999Jan-10-1999Jan-11-1999Jan-12-1999
Date$250$535$320$750
Sales
而我们要找出来所有有营业额(sales)的日子。要达到这个目的，我们用以下的SQL语句：
SELECTDateFROMStore_InformationUNIONSELECTDateFROMInternet_Sales结果:DateJan-05-1999Jan-07-1999Jan-08-1999Jan-10-1999Jan-11-1999Jan-12-1999
有一点值得注意的是，如果我们在任何一个SQL语句(或是两句都一起)用"SELECTDISTINCTDate"的话，那我们会得到完全一样的结果。SQLUnionAllUNIONALL这个指令的目的也是要将两个SQL语句的结果合并在一起。UNIONALL和UNION不同之处在于UNIONALL会将每一笔符合条件的资料都列出来，无论资料值有无重复。UNIONALL的语法如下：[SQL语句1]UNIONALL[SQL语句2]我们用和上一页同样的例子来显示出UNIONALL和UNION的不同。同样假设我们有以下两个表格:
Store_Information表格LosAngelesSanDiego$250
store_name
Sales
Date
$1500
Jan-05-1999Jan-07-1999
LosAngelesBoston
$300
Jan-08-1999
$700
Jan-08-1999Date$250$535$320$750Sales
InternetSales表格Jan-07-1999Jan-10-1999Jan-11-1999Jan-12-1999
而我们要找出有店面营业额以及网络营业额的日子。要达到这个目的，我们用以下的SQL语句：
SELECTDateFROMStore_InformationUNIONALLSELECTDateFROMInternet_Sales结果:DateJan-05-1999Jan-07-1999Jan-08-1999Jan-08-1999Jan-07-1999Jan-10-1999Jan-11-1999Jan-12-1999============表1AabcdeB10302
表2AceB04
合并两个表除去重复的数据（以表2的数据为主），我们将会得到以下的表:
Aabcde
B10004
selectA,Bfrom表1whereAnotin(selectAfrom表2)unionselectA,Bfrom表2
巧用一条SQL实现其它进制到十进制转换
发布时间：2007.09.2504:57
来源：赛迪网
作者：han
问：怎样实现ORACLE中用一条SQL实现其它进制到十进制的转换？答：具体示例如下：
-----二进制转换十进制----------------selectsum(data1)from(selectsubstr('1101',rownum,1)*power(2,length('1101')-rownum)data1fromdualconnectbyrownum<=length('1101'))
-----八进制转换十进制----------------selectsum(data1)from(selectsubstr('1101',rownum,1)*power(8,length('1101')-rownum)data1fromdualconnectbyrownum<=length('1101'))-----十六进制转换十进制----------------selectsum(data1)from(select(CASEupper(substr('2D',rownum,1))WHEN'A'THEN'10'WHEN'B'THEN'11'WHEN'C'THEN'12'WHEN'D'THEN'13'WHEN'E'THEN'14'WHEN'F'THEN'15'ELSEsubstr('2D',rownum,1)END)*power(16,length('2D')-rownum)data1fromdualconnectbyrownum<=length('2D'))
注释：对其它进制可以根据例子将power的底数改成相应的进制就可以了。本文只是一个例子，大家可以把它封装成一个通用函数进行实用。大家在试的时候将里面相应的其它进制的数值换成自己的数据就可以了（有多处）。
实例讲解如何才能让你的SQL运行得更快（1））
发布时间：2008.01.2204:52
来源：赛迪网
作者：10633
很多人在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结：为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过1秒的均表示为（<1秒）。---测试环境:主机：HPLHII----主频：330MHZ----内存：128兆---操作系统：Operserver5.0.4---数据库：Sybase11.0.3一、不合理的索引设计例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：1.在date上建有一非个群集索引在
selectcount(*)fromrecordwheredate>'19991201'anddate<'19991214'andamount>2000(25秒)selectdate,sum(amount)fromrecordgroupbydate(55秒)selectcount(*)fromrecordwheredate>'19990901'andplacein('BJ','SH')(27秒)
分析:date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，分析在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。2.在date上的一个群集索引在
selectcount(*)fromrecordwheredate>'19991201'anddate<'19991214'andamount>2000
（14秒）selectdate,sum(amount)fromrecordgroupbydate（28秒）selectcount(*)fromrecordwheredate>'19990901'andplacein('BJ','SH')（14秒）
分析：分析：在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。3.在place，date，amount上的组合索引在，，
selectcount(*)fromrecordwheredate>'19991201'anddate<'19991214'andamount>2000（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate>'19990901'andplacein('BJ,'SH')（<1秒）
分析：分析：这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。4.在date，place，amount上的组合索引在，，
selectcount(*)fromrecordwheredate>'19991201'anddate<'19991214'andamount>2000(<1秒)selectdate,sum(amount)fromrecordgroupbydate（11秒）selectcount(*)fromrecordwheredate>'19990901'andplacein('BJ','SH')（<1秒）
分析：这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，分析：并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。5.总结：总结：总结
缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询（between,>,<，>=,<=）和orderby、groupby发生的列，可考虑建立群集索引；②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。二、不充份的连接条件：不充份的连接条件：例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：
selectsum(a.amount)fromaccounta,cardbwherea.card_no=b.card_no（20秒）selectsum(a.amount)fromaccounta,cardbwherea.card_no=b.card_noanda.account_no=b.account_no（<1秒）
分析：分析：>在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：外层表account上的22541页+（外层表account的191122行*内层表card上对应外层表第一行所要查找的3页）=595907次I/O在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，I/O次数可由以下公式估算为：其外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）=33528次I/O可见，只有充份的连接条件，真正的最佳方案才会被执行。总结：总结：1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
2.查看执行方案的方法--用setshowplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,302)。三、不可优化的where子句1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
select*fromrecordwheresubstring(card_no,1,4)='5378'(13秒)select*fromrecordwhereamount/30<1000（11秒）select*fromrecordwhereconvert(char(10),date,112)='19991201'（10秒）
分析：分析：where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select*fromrecordwherecard_nolike'5378%'（<1秒）select*fromrecordwhereamount<1000*30（<1秒）select*fromrecordwheredate='1999/12/01'（<1秒）
你会发现SQL明显快起来！2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：selectcount(*)fromstuffwhereid_noin('0','1')（23秒）分析：----where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in('0','1')转化为id_no='0'orid_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果相加，这样可以利用id_no上的索引；
但实际上（根据showplan）,它却采用了"OR策略"，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完成时间还要受tempdb数据库性能的影响。实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时间竟达到220秒！还不如将or子句分开：
selectcount(*)fromstuffwhereid_no='0'selectcount(*)fromstuffwhereid_no='1'
得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：
createproccount_stuffasdeclare@aintdeclare@bintdeclare@cintdeclare@dchar(10)beginselect@a=count(*)fromstuffwhereid_no='0'select@b=count(*)fromstuffwhereid_no='1'endselect@c=@a+@bselect@d=convert(char(10),@c)print@d
直接算出结果，执行时间同上面一样快！总结：总结：大家可以看到，优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。3.要善于使用存储过程，它使SQL变得更加灵活和高效。从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
利用"SQL"语句自动生成序号的两种方式语句自动生成序号的两种方式利用
发布时间：2008.03.1304:55
来源：赛迪网
作者：Alizze
语句自动生成序号:【赛迪网－IT技术报道】SQLServer2005数据库中利用SQL语句自动生成序号赛迪网－技术报道】1.首先，我们来介绍第一种方式:首先，我们来介绍第一种方式首先◆查询的SQL语句如下:
selectrow_number()over(orderbyname)asrowid,sysobjects.[name]fromsysobjects
◆运行的结果:
rowid12345
2.最后，我们来介绍第二种方式:最后，我们来介绍第二种方式最后
nameall_columnsall_objectsall_parameters
all_sql_modulesall_views
在我们利用这种方式生成自动序号时，Test_Table必须在数据库中不能存在,因为在执行这些SQL语句的时后自动会创建表。
selectid=IDENTITY(int,1,1),sysobjects.[name]asnameintodbo.Test_Tablefromsysobjects
详细讲解有关获取当月天数的实用技巧
发布时间：2008.01.2404:39
来源：赛迪网
作者：陈莫
获取当月天数的实用技巧：获取当月天数的实用技巧：以下是引用片段：以下是引用片段：
selectday(dateadd(mm,1,getdate())-day(getdate()))--获得当月天数
分析如下:分析如下
selectgetdate()
--当前日期
selectday(getdate())--目前第几天selectgetdate()-day(getdate())天selectdateadd(mm,1,getdate())-day(getdate())加上一个月selectday(dateadd(mm,1,getdate())-day(getdate()))--获得当月天数---上个月最后一
以下是引用片段：以下是引用片段：
Dimdt1,dt2dt1=Datedt1=CDate(Year(dt1)&;"-"&;Month(dt1)&;"-1")'得到本月第一天
dt2=DateAdd("m",1,dt1)'得到上个月第一天MsgBoxDateDiff("d",dt1,dt2)'得到两个月的差
以下是引用片段：以下是引用片段：
vardt=newDate();//得到当前时间dt=newDate(dt.getFullYear(),dt.getMonth()+1,0);//得到本月最后一天alert(dt.getDate());//本月最后一天即为本月的天数
取一表前N条记录各个数据库的不同SQL写法
从别处看到的，本人在用的是DB2，竟然都不一样……看来是不能说“会SQL，所有的数据库用起来都一样”了。1.ORACLESELECT*FROMTABLE1WHEREROWNUM<=N2.INFORMIXSELECTFIRSTN*FROMTABLE13.DB2SELECT*ROW_NUMBER()OVER(ORDERBYCOL1DESC)ASROWNUMWHEREROWNUM<=NDB2SELECTCOLUMNFROMTABLEFETCHFIRSTNROWSONLY4.SQLSERVERSELECTTOPN*FROMTABLE15.SYBASESELECTTOPN*FROMTABLE16.mysql:select*fromtable_namelimitN
为什么SQL不许在视图定义ORDERBY子句
发布时间：2007.08.0305:01
来源：赛迪网
作者：luoyingshu
问：为什么SQLServer不允许在视图定义使用ORDERBY子句？答：SQLServer之所以不允许在视图定义中使用ORDERBY子句是为了遵守ANSISQL-92标准。因为对该标准的原理分析需要对结构化查询语言（SQL）的底层结构和它所基于的数学理论进行讨论，我们不能在这里对它进行充分的解释。但是，如果你需要在视图中指定ORDERBY子句，可以考虑使用以下方法：
USEpubsGOCREATEVIEWAuthorsByNameASSELECTTOP100PERCENT*FROMauthorsORDERBYau_lname,au_fnameGO
Microsoft在SQLServer7.0中引入的TOP结构在同ORDERBY子句结合使用时是非常有用的。只有在同TOP关键词结合使用时，SQLServer才支持在视图中使用ORDERBY子句。注意：TOP关键词是SQLServer对ANSISQL-92标准的扩展。
一条SQL语句变得巨慢的原因及其解决方法
发布时间：2008.01.3004:58
来源：赛迪网
作者：赵震
现象：现象：一条SQL突然运行的特别慢。
selectuidTable.column_value,first_name||''||last_name,company,job_title,
upper(member_level),upper(service_value)from(select*fromtable(selectcast(multiset(selectbfrombbb)asTaaa)fromdual))uidTable,memberwhereuidTable.column_value=member.login_id(+)andmember.site='alibaba'andmember.site='test';
出错原因：出错原因：用户增加了一个条件member.site=test,造成连接的顺序变化了，原来的驱动表是uidTable(最多1024条记录），现在变成了member表做驱动(600W条）。所以这条语句变的巨慢。但是既然是外连接，为什么连接的顺序会改变呢？因为外连接的连接顺序不是由COST决定的，而是由连接的条件决定的。发现执行计划如下：
------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|-------------------------------------------------------|0|SELECTSTATEMENT||1018|72278|8155||1|NESTEDLOOPS||1018|72278|8155||2|VIEW||4072|69224|11||3|COLLECTIONITERATORSUBQUERYFETCH||||||4|TABLEACCESSFULL|DUAL|4072||11||5|TABLEACCESSFULL|BBB|41|287|2||6|TABLEACCESSBYINDEXROWID|MEMBER|1|54|2||*7|INDEXUNIQUESCAN|MEMBER_SITE_LID_PK|4||1|------------------------------------------------为什么根本就没有执行外连接呢？问题出在member.site='test'这个条件上，因为对外连接的表加了条件，造成外连接失效。改为member.site(+)='test'后，问题彻底解决。
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------|0|SELECTSTATEMENT||1018|72278|8155||1|NESTEDLOOPS||1018|72278|8155|
|2|VIEW||4072|69224|11||3|COLLECTIONITERATORSUBQUERYFETCH||||||4|TABLEACCESSFULL|DUAL|4072||11||5|TABLEACCESSFULL|BBB|41|287|2||6|TABLEACCESSBYINDEXROWID|MEMBER|1|54|2||*7|INDEXUNIQUESCAN|MEMBER_SITE_LID_PK|4||1|-----------------------------------------------------------
语句各种写法的效率问题教你快速掌握SQL语句各种写法的效率问题
发布时间：2008.04.2208:49
来源：赛迪网
作者：科林
【赛迪网－IT技术报道】问题1：一次插入多条数据时下面这两种方法,哪种方法效率赛迪网－技术报道】：高?CREATETABLEtb(IDint,名称NVARCHAR(30),备注NVARCHAR(1000))INSERTtbSELECT1,'DDD',1UNIONALLSELECT1,'5100','D'UNIONALLSELECT1,'5200','E'也可以这样写也可以这样写:以这样写CREATETABLEtb1(IDint,名称NVARCHAR(30),备注NVARCHAR(1000))INSERTTB1(ID,名称,备注)VALUES(1,'DDD',1)INSERTTB1(ID,名称,备注)VALUES(1,'5100','D')INSERTTB1(ID,名称,备注)VALUES(1,'5200','E')解答：解答：第1种好一些,但也得有个量的控制,因为第1种的unionall是作为一个语句整体,查
询优化器会尝试做优化,同时,也要先算出这个结果再插入的。问题2：：赋值时:SELECT@a=N'aa'SET@a=N'aa'上面两种方法,哪种方法效率高?解答：解答：如果是单个赋值,没有什么好比较的话.不过,如果是为多个变量赋值,经测试，SELECT一次性赋值,比用SET逐个赋值效率好..问题3：取前几条数据时：setROWCOUNT2select*fromtborderbyfdselectTop2*fromtborderbyfd上面两种方法,哪种方法效率高?答：SETROWCOUNT和TOP是一样的,包括执行的计划等都是一样的问题4：条件判断时:：where0<(selectcount(*)fromtbwhere……）whereexists(select*fromtbwhere……）上面两种方法,哪种方法效率高?答：这个一般是exists快,当然,具体还要看你后面的子查询的条件,是否会引用外层查询中的对象的列.exists检查到有值就返回,而且不返回结果集,count需要统计出所有满足条件的,再返
回一个结果集,所以一般情况下exists快.问题5：：(5)NULLIF的使用----->同理它的反函数ISNULL的使用updatetbsetfd=casewhenfd=1thennullelsefdendupdatetbsetfd=nullif(fd,1)上面两种方法,哪种方法效率高?答：应该是一样的问题6：从字符串中取子字符串时：substring('abcdefg',1,3)left('abcderg',3)_上面两种方法,哪种方法效率高?答：基本上是一样的问题7：EXCEPT和Notin的区别?：答：except会去重复,notin不会(除非你在select中显式指定)except用于比较的列是所有列,除非写子查询限制列,notin没有这种情况问题8：INTERSECT和UNION的区别?：答：intersect是两个查询都有的非重复值(交集),union是两个查询结果的所有不重复值(并集)
通过两个例子讲解PIVOT/UNPIVOT的用法
发布时间：2008.03.1304:58
来源：赛迪网
作者：ChenJaYi
【赛迪网－IT技术报道】使用过SQLServer2000的人都知道，要想实现行列转换,必须综合利用聚合函数和动态SQL，具体实现起来需要一定的技巧，而在SQLServer2005中，使用新引进的关键字PIVOT/UNPIVOT，则可以很容易的实现行列转换的需求。在本文中我们将通过两个简单的例子详细讲解PIVOT/UNPIVOT的用法。PIVOT的用法：的用法：首先创建测试表，然后插入测试数据
createtabletest(idint,namevarchar(20),quarterint,profileint)insertintotestvalues(1,'a',1,1000)insertintotestvalues(1,'a',2,2000)insertintotestvalues(1,'a',3,4000)insertintotestvalues(1,'a',4,5000)insertintotestvalues(2,'b',1,3000)insertintotestvalues(2,'b',2,3500)insertintotestvalues(2,'b',3,4200)insertintotestvalues(2,'b',4,5500)select*fromtestidnamequarterprofile----------------------------------------------1a110001a220001a340001a450002b130002b235002b342002b45500
(8row(s)affected)使用PIVOT将四个季度的利润转换成横向显示:selectid,name,[1]as"一季度",[2]as"二季度",[3]as"三季度",[4]as"四季度"fromtestpivot(sum(profile)forquarterin([1],[2],[3],[4]))aspvtidname一季度二季度三季度四季度------------------------------------------------1a10002000400050002b3000350042005500(2row(s)affected)
UNPIVOT的用法：的用法：
首先建立测试表，然后插入测试数据droptabletest
createtabletest(idint,namevarchar(20),Q1int,Q2int,Q3int,Q4int)insertintotestvalues(1,'a',1000,2000,4000,5000)insertintotestvalues(2,'b',3000,3500,4200,5500)
select*fromtestidnameQ1Q2Q3Q4-----------------------------------------------1a10002000400050002b3000350042005500(2row(s)affected)使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：selectid,name,quarter,profilefromtestunpivot(profileforquarterin([Q1],[Q2],[Q3],[Q4]))asunpvtidnamequarterprofile------------------------------------------1aQ11000
1aQ220001aQ340001aQ450002bQ130002bQ235002bQ342002bQ45500(8row(s)affected)
用一个实例讲解GROUPBYCEIL的使用方法
发布时间：2008.01.3105:07
来源：赛迪网
作者：孙诗涵
GROUPBYCEIL的使用方法：的使用方法：
SQL>WITHAAS(SELECT'A'CDFROMDUAL2UNION3SELECT'B'CDFROMDUAL4UNION5SELECT'C'CDFROMDUAL6UNION7SELECT'D'CDFROMDUAL8UNION9SELECT'E'CDFROMDUAL10UNION11SELECT'F'CDFROMDUAL12UNION13SELECT'G'CDFROMDUAL14UNION15SELECT'H'CDFROMDUAL
16UNION17SELECT'I'CDFROMDUAL18)19selectmax(decode(mod(rownum,5),1,CD,null))ID1,20max(decode(mod(rownum,5),2,CD,null))ID2,21max(decode(mod(rownum,5),3,CD,null))ID3,22max(decode(mod(rownum,5),4,CD,null))ID4,23max(decode(mod(rownum,5),0,CD,null))ID524froma25groupbyceil(rownum/5)26;ID1ID2ID3ID4ID5--------------ABCDEFGHI
例二：withaas(select'01'ymfromdualunionselect'02'ymfromdualunionselect'03'ymfromdualunionselect'04'ymfromdualunionselect'05'ymfromdualunionselect'06'ymfromdualunionselect'07'ymfromdual
unionselect'08'ymfromdualunionselect'09'ymfromdualunionselect'10'ymfromdualunionselect'11'ymfromdualunionselect'12'ymfromdual)selectmax(decode(mod(rownum,6),1,ym,null))ID1,max(decode(mod(rownum,6),2,ym,null))ID2,max(decode(mod(rownum,6),3,ym,null))ID3,max(decode(mod(rownum,6),4,ym,null))ID4,max(decode(mod(rownum,6),5,ym,null))ID5,max(decode(mod(rownum,6),0,ym,null))ID6fromagroupbyceil(rownum/6)ID1ID2ID3ID4ID5ID6-----------------010203040506070809101112
解析SQL语句中INSERT语句的另一种写法
发布时间：2007.08.3005:17
来源：赛迪网
作者：xiaoqiao
今天一个偶然的机会中知道SQL中的INSERT语句还可以这样写：
INSERTINTOtbl_name(col1,col2)VALUES(value1_1,value1_2),(value2_1,value2_2),(value3_1,value3_2)...
这样的写法实际上类似于：
INSERTINTOtbl_name(col1,col2)VALUES(value1_1,value1_2)INSERTINTOtbl_name(col1,col2)VALUES(value2_1,value2_2)INSERTINTOtbl_name(col1,col2)VALUES(value3_1,value3_2)...
如果在执行过程中碰到一个值错误，则这个值以后的数据都不会被插入。
轻松解决“每个轻松解决每个Y的最新X”的SQL问题的
发布时间：2007.12.0505:02
来源：赛迪网
作者：36113
在实际的工作和学习中，“每个Y的最新X”是大家经常遇到的问题。请注意这不是“按Y分组求最新的X值”，而是要求最新X的那条记录或主键ID。下面我们用一条SQL语句来解决此问题。生成实例表和数据：
--创建表CREATETABLEdbo.Tab(IDintNOTNULLIDENTITY(1,1),Yvarchar(20)NOTNULL,XdatetimeNOTNULL)GO
--插入数据INSERTINTOTab(Y,X)values('BBB','2007-10-2311:11:11')INSERTINTOTab(Y,X)values('BBB','2007-10-2311:11:11')INSERTINTOTab(Y,X)values('BBB','2007-10-2310:10:10')INSERTINTOTab(Y,X)values('AAA','2007-10-2312:12:12')INSERTINTOTab(Y,X)values('AAA','2007-10-2310:10:10')INSERTINTOTab(Y,X)values('AAA','2007-10-2311:11:11')GO
解决“每个Y的最新X”SQL问题：
--一条SQL语句实现SELECTID,Y,XFROMTabTWHERE(NOTEXISTS(SELECT1FROMTabT2WHERE(T2.Y=T.Y)AND(T2.X>T.XORT2.X=T.XANDT2.ID>T.ID)))
注释：在Y列建立索引，可以很大的优化查询速度。（责任编辑：卢兆林）
教你快速掌握编写高效SQL语句的方法
发布时间：2008.02.0104:58
来源：赛迪网
作者：璞玉
高效的SQL语句示例：
createtablestudent(idvarchar(4)notnull,usernamevarchar(20),sexvarchar(50),agevarchar(4),classvarchar(50),constraintsy_test_keyprimarykey(id))
假设现在class条件如果传入空的话，就查询所有，如果不为空的话，就根据特定条件查找，一般的写好将对其进行判断，然后写两条SQL语句，例如：(假设传入的班级变量为classStr)if("".equals(classStr))//注意此处这种写好优于classStr.equals(""),这样写的话，假如classStr传入的为Null，则会报错。
sql="select*fromstudent";elsesql="select*fromstudentwherecl, ass='"+classStr+"'";
若采用逆向思维的话，则写一条语句就可以解决上面的问题。
sql="select*fromstudentwhere''='"+classStr+"'or'"+classStr+"'=class"
由上面语句可看出，如果classStr为空的话，则查询所有，若classStr不为空的话，则根据其值进行查询。
个人经验总结：个人经验总结：有关SQL语句的优化技术（1））
发布时间：2008.01.2204:51
来源：赛迪网
作者：10633
操作符优化◆IN操作符用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。推荐方案：在业务密集的SQL当中尽量不采用IN操作符。◆NOTIN操作符此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替◆<>操作符（不等于）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。推荐方案：用其它相同功能的操作运算代替，如
a<>0改为a>0ora<0a<>’’改为a>’’
ISNULL或ISNOTNULL操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。推荐方案：用其它相同功能的操作运算代替，如aisnotnull改为a>0或a>’’等。不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）◆>及<操作符（大于或小于操作符）大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。◆LIKE操作符LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，LIKE‘%5400%’这种查询不会引用索引，LIKE如而‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描，如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。◆UNION操作符UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select*fromgc_dfysunionselect*fromls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。推荐方案：采用UNIONALL操作符替代UNION，因为UNIONALL操作只是简单的将两个结果合并后就返回。
select*fromgc_dfysunionallselect*fromls_jg_dfys
◆SQL书写的影响同一功能同一性能不同写法SQL的影响如一个SQL在A程序员写的为
Select*fromzl_yhjbqk
B程序员写的为
Select*fromdlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为
Select*fromDLYX.ZLYHJBQK（大写表名）
D程序员写的为
Select*
fromDLYX.ZLYHJBQK（中间多了空格）
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。◆WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1Select*fromzl_yhjbqkwherexh_bz=1'1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。◆查询表顺序的影响在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）◆SQL语句索引的利用对操作符的优化（见上节）对条件字段的一些优化◆采用函数处理的字段不能利用索引，如：
anddy_dj=
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bhlike‘5400%’trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq>=trunc(sysdate)andsk_rq 进行了显式或隐式的运算的字段不能进行索引，如：
ss_df+20>50，优化处理：ss_df>30
‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5hbs_bh=5401002554，优化处理：hbs_bh=’5401002554’，
注：此条件对hbs_bh进行隐式的to_number转换，因为hbs_bh字段是字符型。条件内包括了多个本表的字段运算时不能进行索引，如：
ys_df>cx_df，无法进行优化qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’andkh_bh=’250000’
应用ORACLE的HINT（提示）处理提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示◆目标方面的提示：
COST（按成本优化）RULE（按规则优化）CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）ALL_ROWS（所有的行尽快返回）FIRST_ROWS（第一行数据尽快返回）
◆执行方法的提示：
USE_NL（使用NESTEDLOOPS方式联合）USE_MERGE（使用MERGEJOIN方式联合）USE_HASH（使用HASHJOIN方式联合）
◆索引提示：INDEX（TABLEINDEX）（使用提示的表索引进行查询）其它高级提示（如并行处理等等）
语句删除重复记录的四种好方法用SQL语句删除重复记录的四种好方法
发布时间：2008.03.0404:44
来源：赛迪网
作者：林夕
问题：问题：如何把具有相同字段的纪录删除，只留下一条。例如：test里有id,name字段,如果有name相同的记录只留下一条，表其余的删除。name的内容不定，相同的记录数不定。语句删除重复记录的四种方法：用SQL语句删除重复记录的四种方法：方法1:1、将重复的记录记入temp1表:、
select[标志字段id],count(*)intotemp1from[表名]groupby[标志字段id]havingcount(*)>1
2、将不重复的记录记入temp1表:、
inserttemp1select[标志字段id],count(*)from[表名]
groupby[标志字段id]havingcount(*)=1
3、作一个包含所有不重复记录的表：、作一个包含所有不重复记录的表：
select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
4、删除重复表:delete[表名、删除重复表表名]表名5、恢复表：、恢复表：
insert[表名]select*fromtemp2
6、删除临时表:、删除临时表
droptabletemp1droptabletemp2
方法2:
declare@maxinteger,@idintegerdeclarecur_rowscursorlocalforselectid,count(*)from表名groupbyidhavingcount(*)>1opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxdeletefrom表名whereid=@idfetchcur_rowsinto@id,@maxendclosecur_rows
setrowcount0
注:setrowcount@max-1表示当前缓冲区只容纳@max-1条记录﹐如果有十条重复的﹐就刪除10条，一定会留一条的。也可以写成deletefrom表名。方法3：：
createtablea_dist(idint,namevarchar(20))insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')insertintoa_distvalues(1,'abc')execup_distinct'a_dist','id'select*froma_distcreateprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))--f_key表示是分组字段﹐即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0
beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0endselect*fromsystypesselect*fromsyscolumnswhereid=object_id('a_dist')
方法4：：可以用IGNORE_DUP_KEY：
createtabledup(idintidentitynotnull,namevarchar(50)notnull)goinsertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('abc')
insertintodup(name)values('abc')insertintodup(name)values('abc')insertintodup(name)values('cdefg')insertintodup(name)values('xyz')insertintodup(name)values('xyz')goselect*fromdupgocreatetabletempdb..wk(idintnotnull,namevarchar(50)notnull)gocreateuniqueindexidx_remove_dupontempdb..wk(name)withIGNORE_DUP_KEYgoINSERTINTOtempdb..wk(id,name)selectid,namefromdupgoselect*fromtempdb..wkgodeletefromdupgosetidentity_insertduponINSERTINTOdup(id,name)selectid,namefromtempdb..wkgosetidentity_insertdupoffgoselect*
fromdupgo
注释：注释：此处delete原表，再加入不重复的值。大家也可以通过join只delete原表中重复的值。
不要在SQLServer中盲目地追求一句处理（1））
发布时间：2007.07.3105:10
来源：赛迪网
作者：shuijing
在日常的学习和工作中，我们可以经常发现在处理SQLServer的时，很多人都会有一句出结果的习惯，但值得注意的是，不恰当的合并处理语句，往往会产生负面的性能，本篇针对使用UNIONALL代替IF语句的合并处理做出一个简单的事例，用来说明这种方法会所带来的负面结果。示例：示例：表A和表B，这两个表结构一致，为不同的业务服务，现在写一个存储过程，存储过程接受一个参数，当参数为0时，查询表A，参数为1时，查询表B。1:一般处理方法一般处理方法:一般处理方法
IF@Flag=0SELECT*FROMdbo.AELSEIF@Flag=1SELECT*FROMdbo.B
2、一句处理方法:、一句处理方法
SELECT*FROMdbo.AWHERE@Flag=0
UNIONALLSELECT*FROMdbo.BWHERE@Flag=1
细化分析：细化分析：从语句的简捷性来看，方法b具有技巧性，它们两者之间，究竟那一个更好呢？你可能会从性能上来评估，以决定到底用那一种。单纯从语句上来看，似乎两者的效率差不多，下面通过数据测试来反映结果似乎和想像的一样。建立测试环境：建立测试环境：（注，此测试环境主要是为几个主题服务，因此结构看起来稍有差异）
USEtempdbGO
SETNOCOUNTON--======================================--创建测试环境--======================================RAISERROR('创建测试环境',10,1)WITHNOWAIT--TableACREATETABLE[dbo].A(
[TranNumber][int]IDENTITY(1,1)NOTNULL,[INVNO][char](8)NOTNULL,[ITEM][char](15)NULLDEFAULT(''),PRIMARYKEY([TranNumber]))
CREATEINDEX[indexONinvno]ON[dbo].A([INVNO])CREATEINDEX[indexOnitem]ON[dbo].A([ITEM])CREATEINDEX[indexONiteminnvo]ON[dbo].A([INVNO],[ITEM])GO
--TableBCREATETABLE[dbo].B([ItemNumber][char](15)NOTNULLDEFAULT(''),[CompanyCode][char](4)NOTNULL,[OwnerCompanyCode][char](4)NULL,PRIMARYKEY([ItemNumber],[CompanyCode])
)
CREATEINDEX[ItemNumber]ON[dbo].B([ItemNumber])CREATEINDEX[CompanyCode]ON[dbo].B([CompanyCode])CREATEINDEX[OwnerCompanyCode]ON[dbo].B([OwnerCompanyCode])GO
--======================================--生成测试数据--======================================RAISERROR('生成测试数据',10,1)WITHNOWAITINSERT[dbo].A([INVNO],[ITEM])SELECTLEFT(NEWID(),8),RIGHT(NEWID(),15)FROMsyscolumnsA,syscolumnsB
INSERT[dbo].B([ItemNumber],[CompanyCode],
[OwnerCompanyCode])SELECTRIGHT(NEWID(),15),LEFT(NEWID(),4),LEFT(NEWID(),4)FROMsyscolumnsA,syscolumnsBGO
进行性能测试：进行性能测试：
DECLARE@aintSET@a=1
DECLARE@tTABLE(idintIDENTITY,aint,bint)DECLARE@dtdatetime,@loopint,@idintSET@loop=0WHILE@loop<5BEGINSET@loop=@loop+1RAISERROR('test%d',10,1,@loop)WITHNOWAIT
SET@dt=GETDATE()SELECT[ITEM]FROMAWHERE@a=0AND[ITEM]<'A'UNIONALLSELECT[ItemNumber]FROMBWHERE@a=1AND[ItemNumber]<'A'INSERT@t(a)VALUES(DATEDIFF(ms,@dt,GETDATE()))SELECT@id=SCOPE_IDENTITY(),@dt=GETDATE()IF@a=0SELECT[ITEM]FROMAWHERE[ITEM]<'A'ELSEIF@a=1SELECT[ItemNumber]FROMBWHERE[ItemNumber]<'A'
UPDATE@tSETb=DATEDIFF(ms,@dt,GETDATE())WHEREid=@idENDSELECT*FROM@tUNIONALLSELECTNULL,SUM(a),SUM(b)FROM@t
性能测试结果：性能测试结果：
id
a
b
----------------12345NULL3410170317631800164310319206316561656179318569024
从结果看，两者的性能差异很小，所以两者从性能上比较，可以视为没有差异。问题所在：问题所在：虽然在性能上，两者没有什么差异，但另一个问题也许你从来没有考虑过，那就是对表的访问的问题，在方法A中，肯定只会访问到一个表；而在方法B中，情况还是如此吗？
答案是否定的，方法B始终会扫描两个表。而这样的潜台词是，即使在我的查询中，只会用到A表，但如果B表被下了锁的话，整个查询就会被阻塞，而方法A不会。为了证明这个问题，我们再做下面的测试BLOCK的测试为表A加锁：(查询窗口A)的测试—为表加锁：查询窗口
BEGINTRANUPDATEASET[ITEM]=RIGHT(NEWID(),4)WHERE[ITEM]BETWEEN'9'AND'A'--ROLLBACKTRAN--不回滚事务，让锁一直保持
BLOCK的测试测试查询方法A：(查询窗口B)的测试—测试查询方法：查询窗口
--runquerywindows2DECLARE@aintSET@a=1IF@a=0SELECT[TranNumber]FROMAWHERE[ITEM]<'A'ELSEIF@a=1SELECT[ItemNumber]FROMBWHERE[ItemNumber]<'A'
BLOCK的测试测试查询方法B(查询窗口C)的测试—测试查询方法查询窗口
--runquerywindows3DECLARE@aintSET@a=1
SELECT[ITEM]FROMAWHERE@a=0AND[ITEM]<'A'UNIONALLSELECT[ItemNumber]FROMBWHERE@a=1AND[ItemNumber]<'A'
结果：结果：可以看到，查询窗口B中的查询会及时地完成，而查询窗口C的查询会一直等待，你可以通过执行存储过程sp_who2，查看当前的BLOCK状况来确定查询窗口C的查询是否被查询窗口A的查询BLOCK住。最后结论：最后结论：不要使用查询方法B，它看起来很不错，实际的结果即则是会增加被BLOCK的机会。
用人工智能技术自动对SQL语句进行重写（1））
发布时间：2008.04.1104:56
来源：赛迪网
作者：Lynn
【赛迪网－IT技术报道】SQL语句的优化是将性能低下的SQL语句转换成目的相同的赛迪网－技术报道】性能优异的SQL语句。人工智能自动SQL优化就是使用人工智能技术，自动对SQL语句进行重写，从而找到性能最好的等效SQL语句。数据库性能的优化一个数据库系统的生命周期可以分成：设计、开发和成品三个阶段。在设计阶段进行数据库性能优化的成本最低，收益最大。在成品阶段进行数据库性能优化的成本最高，收益最小。数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。最常见的优化手段就是对硬件的升级。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升，全部加起来只占数据库系统性能提升的40%左右，其余的60%系统性能提升来自对应用程序的优化。许多优化专家认为，对应用程序的优化可以得到80%的系统性能的提升。应用程序的优化应用程序的优化通常可分为两个方面：源代码和SQL语句。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高，而对数据库系统性能的提升收效有限。为什么要优化SQL语句.SQL语句是对数据库进行操作的惟一途径，对数据库系统的性能起着决定性的作用。.SQL语句消耗了70%至90%的数据库资源。.SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑。.SQL语句有不同的写法，在性能上的差异非常大。.SQL语句易学，但难精通。优化SQL语句的传统方法是通过手工重写来对SQL语句进行优化。DBA或资深程序员通过对SQL语句执行计划的分析，依靠经验，尝试重写SQL语句，然后对结果和性能进行比较，以试图找到性能较佳的SQL语句。这种传统上的作法无法找出SQL语句的所有可能写法，且依赖于人的经验，非常耗费时间。
SQL优化技术的发展历程第一代SQL优化工具是执行计划分析工具。这类工具针对输入的SQL语句，从数据库提取执行计划，并解释执行计划中关键字的含义。第二代SQL优化工具只能提供增加索引的建议，它通过对输入的SQL语句的执行计划的分析，来产生是否要增加索引的建议。第三代SQL优化工具不仅分析输入SQL语句的执行计划，还对输入的SQL语句本身进行语法分析，经过分析产生写法上的改进建议。人工智能自动SQL优化人工智能自动SQL优化出现在90年代末。目前在商用数据库领域，LECCOTechnologyLimited(灵高科研有限公司)拥有该技术，并提供使用该技术的自动优化产品LECCOSQLExpert，它支持Oracle、Sybase、MSSQLServer和IBMDB2数据库平台。该产品针对数据库应用的开发和维护阶段提供的模块有：SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。其核心模块SQL语法优化器的工作原理为：①输入一条源SQL语句；②“人工智能反馈式搜索引擎”对输入的SQL语句，结合检测到的数据库结构和索引进行重写，产生N条等效的SQL语句输出；③产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写，直至无法产生新的输出或搜索限额满；④对输出的SQL语句进行过滤，选出具有不同执行计划的SQL语句；⑤对得到的SQL语句进行批量测试，找出性能最好的SQL语句。LECCOSQLExpert自动优化实例假设我们从源代码中抽取出这条SQL语句(也可以通过内带的扫描器或监视器获得SQL语句)：SELECTCOUNT(*)FROMEMPLOYEEswheresEXISTS(SELECT'X'FROMDEPARTMENTswheresEMP_DEPT=DPT_IDANDDPT_NAMELIKE'AC%')ANDEMP_IDIN(SELECTSAL_EMP_ID
FROMEMP_SAL_HISTBswheresSAL_SALARY＞70000)按下“优化”按钮后，经过10几秒，SQLExpert就完成了优化的过程，并在这10几秒的时间里重写产生了2267条等价的SQL语句，其中136条SQL语句有不同的执行计划。接下来，我们可以对自动重写产生的136条SQL语句进行批运行测试，以选出性能最佳的等效SQL语句。按下“批运行”按钮，在“终止条件”页选择“最佳运行时间SQL语句”，按“确定”。经过几分钟的测试运行后，我们可以发现SQL124的运行时间和反应时间最短。运行速度约有22.75倍的提升(源SQL语句运行时间为2.73秒，SQL124运行时间为0.12秒)。现在我们就可以把SQL124放入源代码中，结束一条SQL语句的优化工作了。“边做边学式训练提升SQL开发水平边做边学式训练”提升边做边学式训练LECCOSQLExpert不仅能够找到最佳的SQL语句，它所提供的“边做边学式训练”还能够教开发人员和数据库管理员如何写出性能最好的SQL语句。LECCOSQLExpert的“SQL比较器”可以标明源SQL和待选SQL间的不同之处。以上面优化的结果为例，为了查看源SQL语句和SQL124在写法上有什么不同，我们可以按下“比较器”按钮，对SQL124和源SQL语句进行比较。“SQL比较器”将SQL124相对于源SQL语句的不同之处以蓝颜色表示了出来。如果选择“双向比较”复选框，“SQL比较器”可以将两条SQL语句的不同之处以蓝色表示。当然，我们也可以从源语句和重写后的SQL语句中任选两条进行比较。从比较的结果可以看到，重写后的SQL124把第一个Exists改写成了In；在字段DPT_ID上进行了合并空字符串的操作，以诱导数据库先执行子查询中的(SELECTDPT_ID||''FROMDEPARTMENTWHEREDPT_NAMELIKE'AC%')在子查询完成后，再与EMPLOYEE表进行嵌套循环连接(NestedLoopJoin)。如果觉得对写法的改变难以理解，还可以点中“执行计划”复选框，通过比较两条SQL语句的执行计划的不同，来了解其中的差异。在查看执行计划过程中，如果有什么不明白的地方，可以点中“SQL信息按钮”，再点击执行计划看不明白的地方，LECCOSQLExpert的上下文敏感帮助系统将提供执行计划该处的解释。
在“SQL比较器”中，选中“统计信息”复选框后，可得到详细的两条SQL语句运行时的统计信息比较，这对于学习不同的SQL写法对数据库资源的消耗很有帮助。LECCOSQLExpert优化模块的特点LECCOSQLExpert优化模块的特点主要表现为：自动优化SQL语句；以独家的人工智能知识库“反馈式搜索引擎”来重写性能优异的SQL语句；找出所有等效的SQL语句及可能的执行计划；保证产生相同的结果；先进的SQL语法分析器能处理最复杂的SQL语句；可以重写SELECT、SELECTINTO、UPDATE、INSERT和DELETE语句；通过测试运行，为应用程序和数据库自动找到性能最好的SQL语句；提供微秒级的计时，能够优化Web应用程序和有大量用户的在线事务处理中运行时间很短的SQL语句；为开发人员提供“边做边学式训练”，迅速提高开发人员的SQL编程技能；提供上下文敏感的执行计划帮助系统和SQL运行状态帮助；不是猜测或建议，而是独一无二的SQL重写解决方案。写出专家级的SQL语句LECCOSQLExpert的出现，使SQL的优化变得极其简单，只要能够写出SQL语句，它就能帮用户找到最好性能的写法。LECCOSQLExpert不仅能在很短的时间内找到所有可能的优化方案，而且能够通过实际测试，确定最有效的优化方案。同以往的数据库优化手段相比较，LECCOSQLExpert将数据库优化技术带到了一个崭新的技术高度，依赖人的经验、耗费大量时间、受人的思维束缚的数据库优化手段已经被高效、省时且准确的自动优化软件所取代了。通过内建的“LECCO小助手”的帮助，即使是SQL的开发新手，也能快速且简单地写出专家级的SQL语句。
