sql模拟试题（精选6篇）
【机试】
[1] 在练习文件夹中有一个数据库sdb，其中有数据库表student2、sc和course2。3个表结构如下所示：
STUDENT2（学号，姓名，年龄，性别，院系编号）
SC（学号，课程号，成绩，备注)
COURSE2（课程号，课程名，选修课号，学分）
用SQL语句查询“计算机软件基础”课程的考试成绩在85分以下（含85分）的学生的全部信息并将结果按学号升序存入noex.dbf文件中（表的结构同student2，并在其后加入成绩字段）。答案： SELECT STUDENT2.*,成绩 FROM STUDENT2,SC,COURSE2 WHERE
STUDENT2.学号=SC.学号
AND SC.课程号=COURSE2.课程号 AND 课程名=“计算机软件
基础” AND 成绩<=85 ORDER BY STUDENT2.学号 INTO TABLE
NOEX。
[2] 将order_list1表中的全部记录追加到order_list表中，然后用SQL SELECT语句完成查询：按总金额降序列出所有客户的客户号、客户名及其订单号和总金额，并将结果存储到results表中（其中客户号、客户名取自customer表，订单号、总金额取自order_list表）答案：（1）SELECT * FROM ORDER_LIST1 INTO TABLE ORDER_LIST
（2）SELECT CUSTOMER.客户号, ORDER_LIST.客户名,订单号,总金额 FROM CUSTOMER, ORDER_LIST WHERE CUSTOMER.客户
号=ORDER_LIST.客户号 ORDER BY 总金额DESC INTO TABLE
RESULTS.[3]将customer1表中的全部记录追加到customer表中，然后用SQL SELECT语句完成查询：列出目前有订购单的客户信息（即有对应的order_list记录的customer表中的记录），同时要求按客户号升序排序，并将结果存储到results表中（表结构与customer表结构相同）。
答案：SELECT * FROM CUSTOMER1 INTO TABLE CUSTOMER
SELECT * FROM CUSTOMER WHERE 客户号 IN(SELECT 客户号
FROM ORDER_LIST)ORDER BY 客户号 INTO TABLE RESULTS
[4]列出总金额大于所有订购单总金额平均值的订购单(order_list)清单(按客户号升序排列)，并将结果存储到results表中(表结构与order_list表结构相同)。
在开发数据库应用程序的时候, 要求程序员具有SQL (Structured Query Language) 语言方面的基础知识。对于不熟悉SQL语言或希望快速开发数据库应用程序的程序员, 可以使用C++ Builder提供的SQL Builder辅助工具来自动生成SQL语句。SQL Builder整合在TQuery控件中, 用户需在TQuery控件上右键选择SQL Builder来进行相关操作。
SQL Builder自动生成SQL语句的相关功能与实现方法值得研究学习。本文介绍了在C++ Builder 6.0平台下模拟SQL Builder, 通过动态创建组件、异常处理技术以及泛型设计等技术探究SQL Builder功能的实现。原程序与模拟程序如图1所示。
1 SQL语言与SQL Builder
SQL (Structured Query Language) 结构化查询语言, 是一种数据库查询和程序设计语言, 用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。
SQL Builder整合在TQuery控件中, 通过选择数据库与表名与鼠标点击操作, 可以完成诸如查询、选择、分组、分组查询、排序以及联合等一系列SQL语句的生成, 而不需要程序员花费较多精力在编写SQL语句上。
2 SQL Builder创建SQL语句流程介绍
SQL Builder依据功能, 动态创建SQL语句, 创建SQL语句过程与所选择操作过程有关。创建的SQL语句存储在TMemo中, 通过TQuery执行SQL语句查询。
(1) 窗口创建过程中动态创建数据库表并添加数据库名到TComboBox
打开SQL Builder, 窗口创建过程激活FormCreate事件, 动态创建数据库表。表字段包括要进行比较操作的字段值, 属性设置为ftString类型、长度为32。通过TSession的GetAliasNames获取系统中数据库别名列表, 添加到TComboBox中。
(2) 选择数据库名获取表名并添加到TComboBox
选择上步添加的系统数据库名称或者本地数据库名称, 将该数据库中的所有表名添加到另一个TComboBox。
(3) 选择表名加入表中所有字段到动态创建的TPanel
这里动态创建的TPanel作为父对象, 再动态创建TCheckListBox之类的控件, 用来选择字段。
(4) 将选择的字段名处理后加入到SQL语句并写入到TDBGrid的下拉列表中
由于有些字段名如SIZE等式系统限制名, 因此SQL语句中此类语句需要经过修饰, 即加上双引号, 这里利用C++异常处理技术来实现。TDBGrid实现下拉列表功能需要用到其属性Columns下Items项PickList属性。
(5) 鼠标点击TPageControl中功能标签完成所需功能SQL语句生成
SQL语句存入一个Vector容器中, 这里利用泛型设计技术。每步执行生成SQL过程中, 动态写入SQL均存入底层类型为AnsiString的全局Vector容器。
(6) 查看或执行动态生成的SQL语句
执行SQL语句通过TQuery控件SQL属性下Text属性。
3 模拟SQL Builder创建SQL语句关键技术
模拟SQL Builder创建SQL语句, 难点与重点在与动态创建控件并处理消息函数、对系统限定字段名处理以及生成动态生成SQL语句采用的泛型设计技术。本文具体介绍上述技术在C++ Builder下实现过程。
3.1 动态创建控件并处理消息函数
首先声明全局变量TPanel*Panel。在ComboBox1Select事件写入:
这里各动态创建控件消息响应函数为自定义消息处理函数。其中PanelMouseDown处理TPanel上的鼠标右键消息, 代码如下:
3.2 系统限定字段名处理
字段名为系统限定字段名需要加双引号处理。下面代码利用异常处理技术, 首先接收输入字段名并执行SQL语句, 如果抛出字段名非法异常则捕获, 转交给程序为异常字段名添加双引号。代码如下:
3.3 泛型设计应用
模拟SQL Builder使用了泛型设计技术。泛型设计主要利用STL标准模板库来设计程序, 它可以极大简化程序设计流程, 降低程序员的设计强度。本文中提到的泛型设计主要使用了vector容器模板类。
//首先定义全局vector容器, 底层对象为AnsiString实例:
4 结束语
模拟SQL Builder程序与其他SQL生成工具不同, 它实现了因系统字段名限制, 自动处理字段名, 较好地体现了原SQL Builder工具实现过程。另外, 在模拟过程中, 使用先进的C++机制, 如STL泛型设计、异常处理、动态创建控件并处理控件自定义消息等, 给程序设计者提供了良好的思路。
参考文献
[1]李幼仪, 甘志.C++Builder高级应用开发指南[M].北京:清华大学出版社, 2002.
[2]颜庆茁.基于BCB的数据库的错误信息管理[J].集美大学学报 (自然科学版) , 2003 (1) .
[3]JARROD HOLLINGWORTH.C++Builder 5程序设计大全[M].北京:机械工业出版社, 2002.
[4]BORLAND/INPRISE.C++Builder 5开发人员指南[M].北京:机械工业出版社, 2000.
/*******导出到excel
EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c: emp1.xls -c -q -S”GNETDATA/GNETDATA“ -U”sa“ -P”“’
/***********导入Excel
SELECT *
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source=”c: est.xls“;User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’ ’ 转换后的别名
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source=”c: est.xls“;User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions
/** 导入文本文件
EXEC master..xp_cmdshell ’bcp ”dbname..tablename“ in c:DT.txt -c -Sservername -Usa -Ppassword’
/** 导出文本文件
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’
导出到TXT文本，用逗号分开
exec master..xp_cmdshell ’bcp ”库名..表名“ out ”d: t.txt“ -c -t ,-U sa -P password’
BULK INSERT 库名..表名
FROM ’c: est.txt’
WITH (
FIELDTERMINATOR = ’;’,
ROWTERMINATOR = ’ ’
)
--/* 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********************/
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表)
*********************导入 xml 文件
DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc =’
Customer was very satisfied
white red”>
Important
Happy Customer.
’
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, ’/root/Customer/Order’, 1)
WITH (oidchar(5),
amountfloat,
comment ntext ’text’)
EXEC sp_xml_removedocument @idoc
/********************导整个数据库*********************************************/
用bcp实现的存储过程
/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:zj.txt’,1
----导出整个数据库
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:docman’,1
--导入调用示例
----导入单个表
exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:zj.txt’,0
----导入整个数据库
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:docman’,0
*/
if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)--服务器名
,@username varchar(200)--用户名,如果用NT验证方式,则为空’’
,@password varchar(200)--密码
,@tbname varchar(500)--数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000)--导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit--1为导出,0为导入
as
declare @sql varchar(8000)
if @tbname like ’%.%.%’ --如果指定了表名,则直接导出单个表
begin
set @sql=’bcp ’+@tbname
+case when @isout=1 then ’ out ’ else ’ in ’ end
+’ “’+@filename+’” /w’
+’ /S ’+@servername
+case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end
+’ /P ’+isnull(@password,’’)
exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)’’ set @filename=@filename+’’
set @m_tbname=’declare #tb cursor for select name from ’+@tbname+’..sysobjects where xtype=’’U’’’
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql=’bcp ’+@tbname+’..’+@m_tbname
+case when @isout=1 then ’ out ’ else ’ in ’ end
+’ “’+@filename+@m_tbname+’.txt ” /w’
+’ /S ’+@servername
+case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end
+’ /P ’+isnull(@password,’’)
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
/**********************Excel导到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开，
(MS SQL Server)SQL语句导入导出大全数据库教程
，
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.
insert into
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Text;HDR=Yes;DATABASE=C:’
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls’
--,Sheet1$)
)...[Sheet1$]
如果你想直接插入并生成文本文件,就要用bcp
declare @sql varchar(8000),@tbname varchar(50)
--首先将excel表内容导入到一个全局临时表
select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’
,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into ’+@tbname+’ from
opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’
,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls’’
)...[Sheet1$]’
exec(@sql)
--然后用bcp从全局临时表导出到文本文件
set @sql=’bcp “’+@tbname+’” out “c:aa.txt” /S“(local)” /P“” /c’
exec master..xp_cmdshell @sql
--删除临时表
exec(’drop table ’+@tbname)
用bcp将文件导入导出到数据库的存储过程:
/*--bcp-二进制文件的导入导出
支持image,text,ntext字段的导入/导出
image适合于二进制文件;text,ntext适合于文本数据文件
注意:导入时,将覆盖满足条件的所有行
导出时,将把所有满足条件的行也出到指定文件中
此存储过程仅用bcp实现
邹建 2003.08-----------------*/
/*--调用示例
--数据导出
exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:zj1.dat’
--数据导出
exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:zj1.dat’,’’,0
--*/
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[p_binaryIO]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[p_binaryIO]
GO
Create proc p_binaryIO
@servename varchar (30),--服务器名称
@username varchar (30), --用户名
@password varchar (30), --密码
@tbname varchar (500),--数据库..表名
@fdname varchar (30),--字段名
@fname varchar (1000), --目录+文件名,处理过程中要使用/覆盖:@filename+.bak
@tj varchar (1000)=’’,--处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
@isout bit=1--1导出((默认),0导入
AS
declare @fname_in varchar(1000) --bcp处理应答文件名
,@fsize varchar(20)--要处理的文件的大小
,@m_tbname varchar(50)--临时表名
,@sql varchar(8000)
--则取得导入文件的大小
if @isout=1
set @fsize=’0’
else
begin
create table #tb(可选名 varchar(20),大小 int
,创建日期 varchar(10),创建时间 varchar(20)
,上次写操作日期 varchar(10),上次写操作时间 varchar(20)
,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int)
insert into #tb
exec master..xp_getfiledetails @fname
select @fsize=大小 from #tb
drop table #tb
if @fsize is null
begin
print ’文件未找到’
return
end
end
--生成数据处理应答文件
set @m_tbname=’[##temp’+cast(newid() as varchar(40))+’]’
set @sql=’select * into ’+@m_tbname+’ from(
select null as 类型
union all select 0 as 前缀
union all select ’+@fsize+’ as 长度
union all select null as 结束
union all select null as 格式
) a’
exec(@sql)
select @fname_in=@fname+’_temp’
,@sql=’bcp “’+@m_tbname+’” out “’+@fname_in
+’” /S“’+@servename
+case when isnull(@username,’’)=’’ then ’’
else ’” /U“’+@username end
+’” /P“’+isnull(@password,’’)+’” /c’
exec master..xp_cmdshell @sql
--删除临时表
set @sql=’drop table ’+@m_tbname
exec(@sql)
if @isout=1
begin
set @sql=’bcp “select top 1 ’+@fdname+’ from ’
+@tbname+case isnull(@tj,’’) when ’’ then ’’
else ’ where ’+@tj end
+’” queryout “’+@fname
+’” /S“’+@servename
+case when isnull(@username,’’)=’’ then ’’
else ’” /U“’+@username end
+’” /P“’+isnull(@password,’’)
+’” /i“’+@fname_in+’”’
exec master..xp_cmdshell @sql
end
else
begin
--为数据导入准备临时表
set @sql=’select top 0 ’+@fdname+’ into ’
+@m_tbname+’ from ’ +@tbname
exec(@sql)
--将数据导入到临时表
set @sql=’bcp “’+@m_tbname+’” in “’+@fname
+’” /S“’+@servename
+case when isnull(@username,’’)=’’ then ’’
else ’” /U“’+@username end
+’” /P“’+isnull(@password,’’)
+’” /i“’+@fname_in+’”’
exec master..xp_cmdshell @sql
--将数据导入到正式表中
set @sql=’update ’+@tbname
+’ set ’+@fdname+’=b.’+@fdname
+’ from ’+@tbname+’ a,’
+@m_tbname+’ b’
+case isnull(@tj,’’) when ’’ then ’’
else ’ where ’+@tj end
exec(@sql)
--删除数据处理临时表
set @sql=’drop table ’+@m_tbname
end
--删除数据处理应答文件
set @sql=’del ’+@fname_in
exec master..xp_cmdshell @sql
go
/** 导入文本文件
EXEC master..xp_cmdshell ’bcp “dbname..tablename” in c:DT.txt -c -Sservername -Usa -Ppassword’
改为如下，不需引号
EXEC master..xp_cmdshell ’bcp dbname..tablename in c:DT.txt -c -Sservername -Usa -Ppassword’
/** 导出文本文件
EXEC master..xp_cmdshell ’bcp “dbname..tablename” out c:DT.txt -c -Sservername -Usa -Ppassword’
第一次：查询（显示）、条件、排序、空记录
第二次：通配符、生成表、文件（记事本）、数组、临时表，数学函数、分组、嵌套查询
第三次：插入记录、删除记录、更新记录 第四次：超连接、新建（打开）数据库、表的建立、表的删除、第五次 表结构的修改，添加记录 显示同张芳同一天出生的学生的信息 格式：
1、查询（显示）记录
显示所有男学生的学号，姓名，性别 区分：字段、条件、表名
Select 字段名列表(*)from 表名； where 条件表达式;
into table（dbf）文件名;
into cursor 临时表名；
into array 数组名；
to file 文件名；
order by 字段名列表（asc desc）；
group by 字段名 having 条件；
显示所有大于平均分的学生的学号、姓名 显示同E4挣同样工资的职工信息
Select * from 职工 where 工资=(select 工资 from 职工 where 职工号=”E4”)职工(仓库号C（10）,职工号 C(10)，工资 N（10，2）)
显示所有工资大于1500的员工的仓库号、职工号及所在的城市
2、插入记录
Insert into 表名(字段名列表)values(记录值)
3、删除记录
Delete from 表名 where 条件
4、更新记录
Update 表名 set 字段名=记录值； Where 条件
5、表的删除
drop table 表名
6、表的建立
create table 表名（字段名 数据类型(宽度)）
7、表结构的修改select * from 职工where 职工号!=“E4” AND 工资=(select 工资 from 职工 where 职工号=“E4”)
alter table 表名
1）增加字段
alter table student add 身份证号 c(10)
2）删除字段
alter table student drop colum 身份证号
3）修改字段名
alter table student rename colum 出生日期 to 出生年月
4）增加字段有效性规则
alter table student alter 成绩 set check 成绩>=0 and 成绩<=100;
error “成绩输入错误，成绩应该在0-100之间”
5）删除字段有效性规则
alter table student alter 成绩 drop check 注意：
以上题目中用到的 SQL语句粘贴到计事本文件XY.TXT中（每行一条语句）。对两个表操作的方法：
1）同时打开两个表
select 仓库.仓库号,城市,职工号,工资 from 仓库,职工;
where 职工.仓库号=仓库.仓库号 and 工资=1250
2）超连接(内连接、左连接、右连接、全连
接)
内连接：只显示符合条件的记录
左连接：显示符合条件的记录及第一个表中不符合条件的记录
右连接：显示符合条件的记录及第二个表中不符合条件的记录
全连接：显示符合条件的记录及第一、二个表中不符合条件的记录
成绩表(score)
学号姓名课程名成绩 2001张三计算机10 2003张四英语20 2001张三语言30 2005张三数据40 2003张四计算机50
Select sum(成绩)from score group by 学号
Select sum(成绩)from score group by 课程名
select 字段名列表(*)from 表名;where 条件;
order by 字段名列表 asc|desc;
into table(dbf)表名;
into array 数组名;
to file 文件名;
into cursor 临时表名
题目练习
实现以下功能：在考生文件夹下，打开“陶的数据库”的数据库，并利用SQL语句在该数据库中新建一个表：gongzi（职工号 c(4),实发工资 n(7,0)）,将zhigong表中基本工资大于1330（含1330）的记录存储在gongzi表中，其中实发工资为:基本工资+加班费(白天20，晚上30)，最后将所有的SQL语句保存到考生目录下的sqlanswer.txt文件中（自行建立，一行一条语句）
open database 陶的数据库
create table gongzi(职工号 c(4),基本工资 n(7,2))
select 职工号,基本工资+白班*20+晚班*30 from zhigong;where 基本工资>1350 into array xy
sql学习心得:SQL SERVER 2005学习心得
一、数据库设计方面
1、字段类型。
varchar(max)nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操
作，这是一个亮点。但是这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否
会出现大规模的碎片？是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQL Server的字段类型更加简洁统一。
XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应
该是相当的熟了！）
2、外键的级联更能扩展
可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。但是
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SET NULL 和 SET DEFAULT 属性，能够
提供能好的级联设置。
3、索引附加字段
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了
很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。
4、计算字段的持久化
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算
字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使
用。
5、分区表
分区表是个亮点！从分区表也能看出微软要做大作强SQL Server的信心。资料很多，这里不详细说。但是重点
了解的是：现在的SQL Server2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性
对历史数据和实时数据的处理是很有帮助的。
但是需要注意的一点，也是我使用过程中发现的一个问题。在建立
function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。如果你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。
大家也可以试试。
分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未
分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）
6、CLR类型
微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数
据库的一些概念可以实现了。但是作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系
统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性
能问题！
其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口
。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！还不是性能有问题
！否则面向对象的数据库早就实现了！
建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。或者是要和操作系统进行Socket通讯的场景。否则建议慎重！
7、索引视图
索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面
。还有一大堆的环境参数和种种限制都让人对它有点却步。
8、语句和事务快照
语句级快照和事务级快照终于为SQL Server的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务
级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！
9、数据库快照
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪
回技术还是细粒度不够。可惜！
10、Mirror
Mirror可以算是SQL Server的Data guard了。但是能不能被大伙用起来就不知道了。
二、开发方面
1、Ranking函数集
其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQL Server2005的row_number比Oracle的更先进。因为它把Order by集成到了一起，不用像Oracle那样还要用子查询进行封装
。但是大家注意一点。如下面的例子：
select ROW_NUMBER()OVER(order by aa)
from tbl
order by bb
会先执行aa的排序，然后再进行bb的排序。
可能有的朋友会抱怨集成的order by，其实如果使用ranking函数,Order by是少不了的。如果担心Order
by会影响效率，可以为order by的字段建立聚集索引，查询计划会忽略order by 操作（因为本来就是排序的嘛）。
2、top
可以动态传入参数，省却了动态SQL的拼写。
3、Apply
对递归类的树遍历很有帮助。
4、CTE
个人感觉这个真是太棒了！阅读清晰，非常有时代感。
5、try/catch
代替了原来VB式的错误判断。比Oracle高级不少。
6、pivot/unpivot
个人感觉没有case直观。而且默认的第三字段（还可能更多）作为group by字段很容易造成新手的错误。
三、DBA管理方面
1、数据库级触发器
记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。
2、多加的系统视图和实时系统信息
这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。
3、优化器的改进
一直以来个人感觉SQL Server的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验
发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）
4、profiler的新事件观察
这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能
启动profiler。否则点击没有反应。
5、sqlcmd
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQL Server Management Studio的朋友
使用。
四、遗憾
1、登陆的控制
始终遗憾SQL Server的登陆无法分配CPU/内存占用等指标数。如果你的SQL Server给别人分配了一个只可以
读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。而SQL Server如果
能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。
2、数据库物理框架没有变动
undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个
数据库，可能能在一定程度上避免I/O效率问题。但是同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。
3、还是没有逻辑备份
备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才
能解决。
4、SSIS(DTS)太复杂了
SQL Server的异构移植功能个人感觉最好了。（如果对比过SQL Server的链接服务器和Oracle的透明网关的朋友会发现SQL Server的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）
1 使用索引
例如某表有4762207条记录,要从中查询某个字段为某个数值的记录,我们可以用如下的语句:
这条语句的执行时间是165.63秒。如果略作改动,语句:
select*from t_t where t_s_id=(select t_s_id from t_s where state='F0A'and aa1='6666666')的执行结果一样,所用时间只有0.06秒。产生这种化腐朽为神奇效果的原因,是后者使用了表t_t的索引(t_s_id)。
Oracle提供了两种方式访问全表:
1)全表扫描
全表扫描就是顺序遍历全表,Oracle采用一次读入多个数据块(database t_b)的方式对全表扫描做了优化,但是全表扫描的效率是比较低的。
2)通过row_id来访问
row_id包含了记录的物理地址信息,采用基于row_id的方式来访问表可以提高效率。而索引就是oracle联系数据和存放数据的物理地址的纽带,通常,索引提供了快速访问row_id的方法,所以基于索引列的查询就能实现效率的提升,通常大型表的索引对效率的提升作用特别明显。
索引的使用也要得当,不恰当的索引反而事倍功半,通常需要注意的是以下几点:
1)在经常进行连接的列上建立索引。
2)在经常进行group by或者order by操作的列上建立索引。
3)在where语句部分常用到的,并且有较多不同值的列建索引,不要在取值少的列建索引。
比如有的列只有少量几个不同值,比如只有“男”和“女”、“正常”和“作废”等少量取值的列,就没有必要建立索引,而且这样的情况下建立的索引,不但无助于提升效率,反而还会严重降低数据修改的速度。这是因为索引也需要空间用于存储,每当有insert、delete或者索引列的update操作的时候,索引也将随之改变,这就意味着每条记录的insert、delete或者update都多付出4,5次磁盘读写,所以要分析索引列的合理性,避免使用不恰当的索引。
4)要避免使用!=或<>等操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。
5)要避免在索引列上使用IS NULL和IS NOT NULL。对唯一索引,如索引列包含空值,那么索引中将不存在此记录;对复合索引,如果每个列均为空,索引中同样不存在此记录(如果索引列有一个不为空,则记录存在于索引中)。所以要避免在索引中使用可以为空的列,因为ORACLE将无法使用该索引。可以将索引列为空的记录进行赋值,或者直接指定索引列为NOT NULL。
6)如果常用的select语句需要按多个列排序列出结果,在这些列上建复合索引(compound index),可以提升效率。
7)对复合索引(S1,S2,S3)而言,where子句只有使用到s1列(索引的第一个列,也叫leading column),索引才会生效,只用S1性能较低,S1,S2,S3同时使用效率最高,没有用到s1的话,索引就不生效。
8)等式比较和范围比较
在WHERE子句用到多个索引列,但是ORACLE不能合并,将用范围比较:
例如:t_s表的aa1列有个非唯一索引IDX_AA1,a1也有个非唯一索引IDX_A1,
此处只有aa1索引被引用,接着全部记录逐一与a1的条件作比较。执行路径为:
INDEX RANGE SCAN ON IDX_AA1。
9)注意数据类型的匹配。
对不同类型的数据作比较时,ORACLE将自动对比较列做简单的类型转换。
例如a1是t_s表的一个数值类型的索引列。
实际上,经过ORACLE类型转换,语句转化为:
此处类型的转换在常量,而非索引列,所以索引是生效的,这两条语句的执行时间接近,约0.02秒。下面,我们把a1转换为字符类型再检索:
由于索引列做了类型转换,这个索引将不会被用到,所以花费的查询时间大幅增加为43.41秒。
10)表做了大量数据更新后,需要删除并重建索引。这不仅可以提高查询性能,还能增加索引表空间空闲空间大小。在ORA-CLE里大量删除记录后,表和索引里占用的数据块空间并没有释放,重建索引可以释放已删除记录索引占用的空间。
2 注意FROM子句的顺序
ORACLE采用从右到左的顺序来处理FROM子句中的表,写在最后的表会被最先处理。在需要从多张表查询数据的时候,按记录数多少降序列出各张表将获得最高效率。ORACLE在处理多张表时,用排序和合并的方式连接数据:先扫描第一张表,也就是FROM子句最后的那张表,并对数据进行排序,接着扫描第二张表,也就是FROM子句的倒数第二张表,然后将从第二张表取出的数据和第一张表取出的数据进行合并,依此类推。
3 注意WHERE子句的顺序
ORACLE使用自下而上的顺序来解析WHERE子句,所以,能过滤最多纪录的条件要写在WHERE子句的最后,几个表之间的连接则写在最前。
4 发生数据变化的时候,要使用COMMIT
编写程序和存储过程的时候,只要发生数据变化,就要记得写上COMMIT,这样程序和存储过程的性能会得到提升,随着COM-MIT释放资源,需求也会随之减少。
5 精简查询的次数
在包含子查询的SQL语句中,要注意精简查询的次数。
例如要统计出t_s表中an1,a1分别与t_b中aa1为6666666的用户的an1,a1相同的记录:
低效:
高效:
6 删除全表,用TRUNCATE,不用DELETE
通常在删除表中的记录的时候,回滚段(rollback segments)存放了可以恢复的信息,如果DELETE大数据量的表速度会很慢,同时在COMMIT之前,会占用很多的回滚段,ORACLE可以将数据恢复到执行DELETE之前的状况。而执行TRUNCATE时,回滚段不再存放可恢复的信息,执行命令后,数据无法恢复,因此调用的系统资源少,执行时间也快很多。
7 用>=替代>
比如A表的a1上有一个索引,
高效:
低效:
前者直接转到第一个a1=2000000000的记录,而后者先找到a1=2000000000的记录,然后再扫描到第一条比它大的记录。
8 EXISTS比IN快
通常,EXISTS比IN快,比如我们来统计数量,有如下语句:
低效:
高效:
同样得出422342的结果,前者用了39.78秒,比后者多花费5秒。
9 表连接比EXISTS快
通常,表连接比EXISTS快。比如,要查看在2013年3月20日13点0分0秒到13点59分59秒期间对哪些switch_id做过数据采集,可以用:
或者:
后者执行时间比前者短。
1 0 用UNION ALL替代UNION
UNION ALL只是做简单的合并,不做排序,而使用union,相当于union all之后还有个排序的动作,最后还去除重复记录。直接用union all可以省略这个操作,从而提升效率。
1 1 用外部联接替代NOT IN
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回。若二者均不带"+",则二者中无法匹配的均被返回。利用外部联接"+",可以替代效率十分低下的NOT IN运算,可以大大提高运行速度。
例如,可以用下面这条命令来查询异常资料
也可以利用外部联接,改写命令如下:
同样得出138条结果,前面一条的执行时间是15.37秒,用外部联接则缩短为7.42秒。
1 2 避免对搜索参数使用其他数学操作符
WHERE条件左边不使用数学操作符,可以略微提高执行速度。
由于系统在繁忙的应用中,服务器的CPU的使用率在不断变化中,同一SQL语句,两次执行的时间就可能不同,上述SQL优化的例子中的执行时间是我2次执行的平均时间,所以能反映出不同语句的效率差异。
上述优化规则有的能提高效率上千倍,有的只能提高效率百分之几,但是对多终端的系统而言,应用程序和复杂的存储过程如果都能应用以上规则,就可以提升整个系统的性能,从而能高效处理各项事务。
摘要：论文论述了SQL优化的方法,通过一些简单的SQL优化规则就可以不同程度地提升SQL语句的效率。
关键词：SQL,效率
参考文献
[1]崔群法.SQL Server2008中文版从入门到精通[M].北京:电子工业出版社,2009.
