SQL数据库技术

SQL数据库技术（共12篇）

SQL数据库技术 篇1

0 引言

“大集中”部署模式的企业级管理信息系统往往承载数以千计或万计的用户负载,海量数据集中存储,集中处理“井喷式”的用户请求,这些系统普遍面临以下问题:

1)历史及实时数据量非常大,后台处理能力受多种客观因素牵制,效率低,响应速度慢;

2)系统难以支持多并发、大结果集的数据查询,耗时往往超出用户容忍度;

3)同时支撑省级、市级和县级单位访问,用户覆盖面广,业务繁忙时段系统响应缓慢,极大地降低了用户满意度。

信息系统性能优化是解决上述问题的关键,同时也是知识密集、综合性强的工作,需立足于系统工程的角度,从硬件(服务器、存储系统、网络、安全系统)和软件(操作系统、中间件、数据库、应用程序)全方位开展优化[1]。但优化措施的实施难易程度通常与性能改善程度并不成正比,如改善硬件配置对系统性能的提升可能不如优化应用软件代码明显。而源代码优化则因涉及程序逻辑改写,势必付出较高的时间成本与人力成本。

折衷考虑,制定性能优化策略时应将重点放在平台类软件的优化上,如操作系统、中间件和数据库等。其中,数据库作为企业IT基础设施的核心部件之一,与网络、操作系统、存储设备皆有密切关联。数据库依照某种数据模型组织、存储及管理业务数据,其性能优化已成为保证企业IT基础平台健康运营的关键技术之一,对提升信息系统整体性能起到至关重要的作用。

1 数据库SQL审查

针对未上线的信息系统数据库执行SQL审查,目的是提前发现数据结构设计和SQL语句存在的导致数据库性能降低的隐患及缺陷,本质上属于一种性能评估方法。审查方式有人工核查和自动化工具核查,审查结果能为后续性能优化工作提供直接依据。由于性能调优成本随软件生命周期的推进呈几何增加趋势,因此审查活动介入的阶段越早越有利,应尽量避免系统投运后再为调优付出高昂的代价。

1.1 基于定量分析的SQL审查指标体系

本文汇集了影响数据库性能的主要因素作为审查对象(即指标),为每一个对象制定审查规则,并根据其对性能的影响程度赋予权重,建立评分制度,最后形成了一整套SQL审查指标体系。该体系可根据数据库领域专家的经验灵活地进行完善,如在数据建模阶段开展审查可适当增加表设计类的审查对象和规则,在系统开发结束后的测试阶段则增加索引类和SQL类的审查对象和规则。数据库SQL审查指标集如图1 所示。

数据库SQL审查规则及评分规则见表1 所列。数据库SQL审查评定等级见表2 所列。从权重分配看,影响数据库性能的主要因素来自SQL语句和表索引,这2 个方面作为审查的重点,必然是后续性能调优工作的重点。数据库SQL审查评分满分为100 分,每个指标分配相应分值和权重,每发现一个违反审查规则的对象就相应扣分,扣减后的分值作为该指标得分,每项指标得分再乘以权重,最后求和。总得分可表示为:总分= ∑(单个指标得分 × 指标权重),其中单个指标得分= 指标分值 – 单项扣分× 违规数量。

1.2 应用效果

为了验证SQL审查指标体系的应用效果,笔者在广西电网年度应用系统性能评估优化专项工作中选取省级大集中部署的某管理信息系统开展上线前SQL审查。该系统数据库被审查的对象共计75 138个,其中违反审查规则的对象有768 个,审查总分为71.19 分,表明数据库性能处于中等级别,存在一些严重的性能隐患及缺陷影响系统运行的稳定性。某企业管理信息系统SQL审查结果见表3 所列。

对违规对象进行分析和统计,发现扣分较多的审查对象来自索引设计、SQL语句结构不合理、SQL语句执行计划不正确等。为了解决这些性能缺陷及隐患,选取核心或热点业务表的索引进行优化,对高消耗的SQL语句进行调优。

本次SQL审查取得了以下效果。

1)改变了当前单纯靠压力测试判断系统性能是否满足上线条件的现状。压力测试通过模拟用户负载和典型业务操作向系统施压,模拟程度有限,测试场景与投运场景必然存在相当差距,性能缺陷或隐患未能暴露出来。而SQL审查发现性能隐患和缺陷的几率远远超过模拟压力测试,从定量角度评估数据库性能表现,评分高于基准才准予系统上线,这样的衡量准则更为可靠。

2)利于有效、精准地解决应用性能问题。应用程序包括高级语言编写的代码和SQL代码,中间件访问数据库的所有操作均通过SQL代码来实现。因此索引类、SQL类审查结果能精确定位相当一部分应用性能问题,为后续优化工作提供清晰的方向与思路。

3)降低了系统上线风险。相较于黑盒级性能测试方法,白盒级的SQL代码审查能发现80% 潜藏的应用性能问题,经过优化能大幅降低系统上线后出现性能故障的几率。

2 数据库性能优化解决方案

基于系统工程的理念,具有普适性的数据库性能优化思维是围绕数据库生命周期的多个阶段,从设计、开发、安装部署到运行维护阶段,持续地探寻性能问题及解决方案。因此,根据优化工作介入的时间大致划分3 个阶段:一是设计与开发阶段,主要对数据库逻辑和物理结构设计进行优化,使其在满足业务需求的前提下,性能达到最佳且开销最小;二是安装部署阶段,主要对数据库管理系统自身的参数、操作系统资源分配及内核参数进行优化,为应用提供良好的支撑平台;三是投运阶段,优化的范畴涵盖数据库、操作系统、应用、服务器、网络、统一存储等。根据业界统计的软件生命周期调优成本与调优收益变化曲线,发现调优成本通常随着软件生命周期的推进呈上升趋势,调优收益却随之逐渐降低并趋于零[2]。因此,数据库性能优化的整体解决方案是:在设计与开发阶段开展数据库SQL审查,并在此阶段完成表设计、索引设计和SQL语句等方面的性能缺陷及隐患整改;在数据库安装配置阶段,对主要性能参数进行优化;在数据库投运阶段,利用先进的数据库性能监控工具对其运行情况进行多维度监控与分析,分析维度包括数据库碎片、计算资源使用情况、等待事件、锁分析、表空间碎片、缓存命中率、内存管理、文件I/O、SQL执行计划的效率等,并关注业务高峰时段的数据库日志,随后针对表现异常的方面制定优化措施。常见的数据库性能优化技术的效果比重如图2 所示。

由图2 可知,设计开发阶段执行数据建模时,对数据模型进行优化的效果最明显,对应用性能的改善程度最大;其次是表索引及SQL语句优化技术;然后是改进SQL语句的执行计划(从数据库管理系统层面);收益最小的则是参数优化[3]。从职责界定的角度来说,数据库设计开发阶段和安装部署阶段的性能优化工作应由信息建设部门主导,数据库投运阶段的性能优化由信息运维部门主导。本文将从信息运维的视角阐述数据库性能优化技术——索引优化及SQL优化。

2.1 从战略的角度设计表索引

2.1.1 索引设计现状

纵观企业级大型管理信息系统数据库,索引创建的普遍现状如下:(1)随需而建,即发现某条SQL语句执行效率低、执行计划存在全表扫描时,通过查看where子句中的列、列上使用的运算符、列值的离散度等信息直接创建索引;(2)盲目而建,当系统功能增加或变更需要新增或修改SQL语句时,首选是新增索引,而未考虑利用现有索引,或者未定期删除不再使用的索引;(3)索引列的顺序设置欠佳,开发人员单纯通过SQL语句中列字段的离散度来决定索引列顺序,对性能产生了很大影响。

上述现状对应存在的劣势主要体现为以下3 点:(1)随需而建的索引仅能满足单条SQL语句的使用,同张表的其他SQL语句在执行过程中可能缺少适宜的索引;(2)盲目而建的索引虽然能顾及某张表每条SQL语句的效率,但大量索引创建后可能存在重复、冗余,或是某些字段在不同索引里重复度高的现象,这不仅会导致索引对象占用庞大的存储空间,也会使数据库在制定SQL执行计划时经常选择错误的索引,从而严重影响执行效率;(3)未能基于所有的应用场景通盘考虑索引列的顺序,导致索引的列序设置不合理,比如未选择离散度高的列作为前导列,SQL语句在执行过程中会读取大量无效数据块,磁盘吞吐量高,易出现性能瓶颈。

为了解决这些现状,通过大量数据库优化的实践工作,建立了一套关系型数据库索引的设计方法,用于表索引设计及优化。

2.1.2 战略索引设计技术

战略索引设计,即从全局的角度设计索引,不是单纯为了某条SQL语句的效率去设计索引,而是通盘考虑一张表相关的所有SQL语句的性能需求,制定最佳的索引设计方案,力求提高SQL语句的整体执行效率,降低总体资源开销[4]。战略索引设计包括以下3 个步骤。

1)核心表的精确定位。应用系统的数据库表数量多,小型应用通常有上百张,大型应用则有上千张。根据“二八原则”——选取百分之二十的表,解决百分之八十的应用性能问题,设计战略索引之前需准确定位哪些是核心业务表。可通过3 种维度来筛选核心表:(1)业务的重要程度,表对应的功能模块用以完成客户的核心业务;(2)业务密集程度,表相关的SQL语句数量越多,说明该表汇集的业务操作越多;(3)表的数据量,数据量越大,说明表读写越频繁。

以某企业级管理信息系统为例,定位了30 余张核心表,且从中根据重要程度区分为高、中、低作为优化的顺序。待优化的核心业务表如图3 所示。

2)SQL语句搜集与解析。 核心业务表相关SQL语句的搜集与解析指的是搜集所有数据库对象和数据库内存缓存区中出现的SQL语句,继而筛选出与核心表相关的SQL语句并进行解析,最终获得核心业务表的访问路径。SQL语句与访问路径的关系可能为1:1 或1:M,即一条语句可能对应一条访问路径,亦或对应多条访问路径。

3)战略索引设计。战略索引设计的首要原则是以最少的索引满足尽可能多的访问路径[5]。基于每张核心业务表对SQL语句进行分组,使用自动化分析工具对每组SQL语句的where条件子句进行解析,解析结果为访问路径,采用列名+ 运算符缩写来标记。在解析过程中,自动化分析工具能自动合并相同的访问路径,并将运算符替换成缩写符号。具体替换规则为:“=”替换为(=);IN替换为(IN);BETWEEN替换为(B);<> 也替换为(B);LIKE替换为(L);JOIN替换为(J)。

以某企业级管理信息系统中的一张核心业务表A为例,对表A相关的41 条SQL语句进行解析,获取共计11 条访问路径,见表4 所列。

基于以上11 条访问路径,使用下列通用准则(见表5)设计核心业务表的战略索引。

设计过程分为2 步,第1 步为基于不同的列对访问路径进行分组,访问路径分组表见表6 所列。

第2 步为按分组完成战略索引设计,战略索引设计见表7 所列。

从设计结果来看,一张拥有40 条SQL语句和11 条访问路径的核心表仅需创建4 个索引,便可以满足其所有SQL语句的性能要求。

2.2 战略索引设计应用效果

战略索引设计的预期目的是大幅度降低数据库逻辑读写量和物理读写量(以数据块为单位),因此其应用效果可通过业务表使用战略索引后数据库产生的读写(I/O)量的缩减程度来体现。以核心业务表A为例,通过Oracle EM展现优化索引前后数据库产生的I/O总量(包括逻辑IO与物理IO)变化,分别如图3 和图4 所示。

由图4 和图5 可知,优化前数据库产生的I/O总量峰值达54.6,优化后I/O总量的峰值降至16。由此可见,采用战略索引优化方案能使数据库产生的I/O总量缩减三分之二。相对硬件升级方案,此优化技术的优势在于:

1)实施成本低,无需修改应用代码,极大地节省了人力成本与时间成本;

2)优化效果明显,持续时间长,在系统业务需求没有变更的前提下,能保证系统在1~3 年内具有良好的性能表现。

3 SQL优化技术

3.1 SQL优化原理

SQL语句是应用与数据库之间交互的唯一桥梁,其执行效率直接影响到系统响应时间及用户体验。SQL语句的执行计划由数据库自身的优化器来制定,基于成本的优化器按照数学中排列组合原理对SQL语句中所包含的要素进行排列组合,并快速计算出各排列组合所消耗的资源和时间,最后从中选择一个它认为消耗资源最少和时间最短的执行计划[6]。优化器是一个智能程度很高的计算机程序,它存在一定的局限性:当SQL语句不具备良好要素时,难以为其制定高效的执行计划。而影响执行计划效率的要素包括索引、统计信息、SQL语法结构等[7]。

SQL语句优化首先要判断哪些SQL要素存在优化空间。选择哪些优化点遵循的原则是观察SQL语句的执行计划,对比最初处理的数据行数、中间处理的行数及最终输出行数三者之间是否存在明显差异。若三者之间差距很明显,则说明在SQL语句执行过程中存在大量无效处理,通过人工干预能够去除这些无效部分,从而提高其执行效率。

在一个业务周期里(即包括业务高峰期和低峰期),可利用数据库性能监控工具筛选出执行耗时大于某基准值的TOP SQL语句列表,对其开展优化。具体优化步骤如下。

1)定位问题。详细分析执行计划,定位导致SQL语句执行时间长或数据库I/O负荷高的环节。

2)分析原因。分析SQL语句执行计划引起高消耗的具体原因,通常包括没有使用恰当的索引;使用无效索引导致读取了大量无效的数据块;没有选择恰当的表连接方式,执行了大量无效循环;表连接顺序不合适;过滤能力强的条件没有被优先执行;欠缺可选的索引,导致全表扫描等[8]。

3)提出解决方案。解决方案按照SQL优化的角度可分为4 类:调整索引、改变表连接方式或顺序、调整SQL语法结构、改进执行计划。

4)测试效果。以Oracle数据库的表连接技术为例,可结合不同的应用场景,强制改变多个源表之间的连接方式,以获得执行的高效率。Oracle数据库常见的表连接使用准则见表8 所列。

完成同样功能的SQL语句,在语法结构上未必是单一的,可以有几种不同的写法,写法不同会导致SQL语句在执行效率上千差万别。在数据库性能优化的过程中,本文总结归纳了一些通用的SQL语句编写准则,依据这些准则改变SQL语句的语法结构,可适当改善SQL语句耗时、耗资源多的现象[9,10,11,12]。高效SQL语句编写准则见表9 所列。

3.2 SQL优化的应用效果

以某企业级管理信息系统为例,采用以上优化策略对36 条SQL语句开展优化,通过比较优化前后语句的耗时及其产生的数据库I/O吞吐量可知,耗时整体缩减了大约92%,I/O吞吐量整体降低了大约93%,相当于执行效率整体提升了将近12 倍,具体统计结果如图6 所示。其中,单条SQL的执行效率最大提升达千倍,I/O吞吐量最大降幅近乎100%(图6红框选中行)。

4 结语

本文提出了一套基于定量分析的数据库性能审查指标体系,在系统投运前评估数据库性能,挖掘数据库在设计和编码方面潜藏的性能缺陷和隐患,并提出了基于系统工程理念的数据库性能优化思路,重点阐述了2 种高收益、低投入的性能优化技术——战略索引设计与SQL语句优化。这些技术的应用效果表明,与单纯提高硬件配置相比,除了节省高额硬件投资,还可从整体上显著地提升企业级管理信息系统性能,提升幅度可达10 倍以上,并且相关方法论具有普适性,适用于不同的关系型数据库,具有很好的应用价值。

参考文献

[1]文平.Oracle数据库性能优化的艺术[M].北京:机械工业出版社,2012.

[2]王继业,程志华,张宗华,等.大型管理信息系统数据库优化方法研究[J].电力信息与通信技术,2015,13(8):1-4.WANG Ji-ye,CHENG Zhi-hua,ZHANG Zong-hua,et al.Research on the database optimization method of large management information system[J].Electric Power Information and Communication Technology,2015,13(8):1-4.

[3]KUHN D,ALAPATI S R,PADFIELD B.Oracle索引技术[M].卢涛,译.北京:人民邮电出版社,2013.

[4]LAHDENMAKI T,LEACH M.数据库索引设计与优化[M].曹怡倩,赵建伟,译.北京:电子工业出版社,2015.

[5]李华植.海量数据库解决方案[M].北京:电子工业出版社,2010.

[6]崔华.基于Oracle的SQL优化[M].北京:电子工业出版社,2014.

[7]LEWIS J.基于成本的Oracle优化法则[M].赵恒,李政仪,译.北京:清华大学出版社,2007.

[8]白鳝.Oracle优化日记[M].北京:人民邮电出版社,2010.

[9]盖国强.Oracle DBA手记3:数据库性能优化与原理解析[M].北京:电子工业出版社,2011.

[10]杨小宁,邰炜,蔡忠林.地市供电公司信息系统数据库优化实践[J].电力信息与通信技术,2015,13(5):105-108.YANG Xiao-ning,TAI Wei,CAI Zhong-lin.Optimization practice of information system database in local power supply company[J].Electric Power Information and Communication Technology,2015,13(5):105-108.

[11]罗伟,蒋苏湘,周沿东,等.湖南电力营销系统数据库性能优化研究[J].电力信息与通信技术,2014,12(4):30-34.LUO Wei,JIANG Su-xiang,ZHOU Yan-dong,et al.Research on database performance optimization for Hunan power marketing system[J].Electric Power Information and Communication Technology,2014,12(4):30-34.

[12]张云翔.任务分发策略在Oracle数据库集群中的应用研究[J].电力信息与通信技术,2015,13(1):90-94.ZHANG Yun-xiang.Research on application of task distribution strategy in Oracle RAC[J].Electric Power Information and Communication Technology,2015,13(1):90-94.

SQL数据库技术 篇2

/*******导出到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’

SQL数据库技术 篇3

关键词：大型二进制对象 image数据类型 文件流 数据库引擎

一、引言

随着信息技术的发展，越来越多的系统中需要存取图像数据，而对于图像数据的保存有两种方法：一种是将图像数据以文件形式存放在磁盘中，在数据库中只存储这些文件的路径和文件名；另一种是将图像数据和其他数据一样直接存储在数据库中。当要处理大量图像数据时，把图像数据直接存储在数据库中有许多优点：

易于管理 当图像数据与其他数据一起存储在数据库中时，图像数据与其他数据是一起备份和恢复的，这样就减少了图像数据与其他数据不同步的机会，也降低了其他用户无意中删除了文件系统中图像数据文件的风险。另外，由于图像数据与其他数据一起存储在数据库中，其插入、更新和删除可在同一个事务中实现，确保了数据的一致性。

可伸缩性 尽管文件系统被设计为能够处理大量不同大小的对象，但是文件系统不能对大量小文件进行优化。在这种情况下，数据库系统可以进行优化。

可用性 数据库复制允许在分布式环境中复制、分配和潜在地修改数据。在主系统失效的情况下，日志转移提供了保留数据库备用副本的方法。

因此，要真正做到各类数据在数据库中安全管理，研究和探索直接将图像数据存储在数据库中的方法是非常必要的。

二、 Microsoft SQL Server 系统中的 BLOB 数据及其存取机制

BLOB（binary large object，大型二进制对象）数据类型主要用于保存多媒体对象，比如图像、视频和声音。在Microsoft SQL Server系统中，BLOB可以是text、ntext或image数据类型。其中，image 数据类型用来存储变长的二进制数据，最大长度是2GB，非常适合存储图像数据。

BLOB数据在Microsoft SQL Server系统中的存储方式不同于普通的数据类型，对于普通类型的数据系统直接在用户定义的字段上存储数据值，而对于BLOB类型数据，系统开辟新的存储页面来存放这些数据，表中BLOB类型数据字段存放的仅是一个16字节的指针，该指针指向存放该条记录的BLOB数据的页面。

在Microsoft SQL Server系统中，当数据小于 8000 字节时，可以用普通的SQL语句（SELECT、INSERT、UPDATE、DELETE）来完成对数据的查询操纵，当数据大于8000字节时，Microsoft SQL Server提供了 WRITETEXT 、READTEXT和UPDATETEXT这三个函数来读取和修改数据。这三个函数的使用比较复杂，以 READTEXT 为例，其一般形式为

READTEXT {table.column text_ptr offset size} [HOLDLOCK]

其中，table.column为表中的字段，text_ptr为一个16字节的指针，offset 为偏移量，即从第几个字节开始读数据，size为要读的字节数，HOLDLOCK 为在读数据时是否允许其他用户修改该数据。

例：DECLARE @ptrval varbinary（16）

SELECT @ptrval = TEXTPTR（img_ct） FROM zy_ct WHERE id_ct = 20010101001

READTEXT zy_ct.img_ct @ptrval 1 25

但是READTEXT函数读取的数据无法直接传递回前端应用程序，本文将介绍一种使用Delphi存取Microsoft SQL Server 系统中图像数据的简单方法。在SQL Server 系统中建立表TEST，表中的字段及属性见表1。

表1 TEST的字段及属性

三、使用Delphi存取Microsoft SQL Server中的图像数据

图像数据的存储格式有很多种类，常见的有BMP、JPEG、GIF等等，如果按照每种格式去单独编写存取代码，就会使问题复杂化。但是，不论哪种格式的存盘文件，都可以看成文件“流”的形式。Delphi中的TFileStream数据类型以流的形式对字符或非字符数据进行处理，就像在内存中开辟了一个大小可变的临时缓存区。它不仅能方便地对外部文件进行读写，而且还可以将流中的全部数据直接转入数据库，可用它作桥梁完成图像数据的存取。

（一）数据集组件的选择

在Delphi 连接数据库的BDE方式中，通常数据集组件可以选择TTable或TQuery，如果使用TQuery组件存取image类型数据，不可避免地就要使用WRITETEXT 、READTEXT和UPDATETEXT这三个函数，不便于程序实现。而选择TTable组件，就可利用Delphi的强大数据引擎功能，自动完成image类型数据的存取。

（二）将图像数据保存到Microsoft SQL Server中

下面程序的功能是使用TFileStream读取图像文件“ c：＼source.jpg ”，再转储到String中，然后利用TTable组件将String中的图像数据存储到数据库中。

procedure TForm1.SaveToDBClick（Sender： TObject）；

var

FS： TFileStream；

ImageStr： String；

PImageStr： PChar；

begin

FS ：= TFileStream.Create（'c：＼source.jpg'， fmOpenRead）； //读取图像文件

FS.Seek（0， soFromBeginning）；

SetLength（ImageStr， FS.Size）；

PImageStr ：= PChar（ImageStr）；

FS.Read（PImageStr^， FS.Size）； //将TFileStream中的图像数据转储到String中

FS.Free；

//使用TTable连接数据库，将String中的图像数据存入数据库

Table1.Close；

Table1.Open；

Table1.Append；

Table1.FieldByName（'IMAGEID'）.AsString ：= Edit1.Text；

Table1.FieldByName（'IMAGEDATA'）.AsString ：= ImageStr；

Table1.Post；

end；

3.3从Microsoft SQL Server中取出图像数据

下面程序的功能是利用TTable组件读取数据库中的图像数据，并保存到String中，再使用TFileStream将String中的图像数据保存到文件“c：＼target.jpg”中，然后利用TImage显示图像文件“c：＼target.jpg”。

procedure TForm1.LoadFromDBClick（Sender： TObject）；

var

FS： TFileStream；

ImageStr： String；

PImageStr： PChar；

begin

//使用TTable查询数据库

Table1.Close；

Table1.Open；

//将数据库中的图像数据导出到String中

ImageStr ：= Table1.FieldByName（'IMAGEDATA'）.AsString；

//将String中的图像数据通过TFileStream写到文件中

PImageStr ：= PChar（ImageStr）；

FS ：= TFileStream.Create（'c：＼target.jpg'， fmCreate）；

FS.Write（PImageStr^， Length（ImageStr））；

FS.Free；

//使用TImage组件显示导出的图像数据

Image1.Picture.LoadFromFile（'c：＼target.jpg'）；

end；

4结束语

Microsoft SQL Server系统为保存BLOB型数据提供了存储平台，Delphi为存取这种数据提供了灵活的接口。本文介绍的用Delphi存取 Microsoft SQL Server中image类型数据的方法，不但适用于各种图像文件，同样适用于其它类型的文件。其原理同样适合Sybase、Oracle等数据库和Powerbuilder、VB等前端开发工具。

（作者单位：湖北省广播电视学校）

参考文献：

[1]闪四清.SQL Server系统管理和应用开发指南.北京：清华大学出版社，2000

SQL数据库技术 篇4

数据库技术自20世纪60年代产生以来, 无论是理论还是应用都已变得相当重要和成熟, 成为计算机领域发展最快的学科方向之一, 也是应用很广、实用性很强的一门技术。

各大学的计算机专业、信息管理专业以及相关专业都开设了《数据库技术及应用》这门课程, 是计算机科学与技术专业的核心必修课, 也是相关信息和管理类专业的重要课程。在信息时代, 数据库技术已成为软件系统的核心。

我校自从设置计算机专业, 一直开设着《SQL Server数据库应用技术》这门课程。计算机科学技术为本科专业, 学生们的先修课程为《数据库系统概论》, 学生们有了一定的理论基础, 《SQL Server数据库应用技术》课程的主要目的在于实践和应用。

1实验题目分析与设计

1.1实验现状分析

在实验项目方面, 从多年带毕业论文的情况看, 毕业生使用SQL Server只会做一个数据库, 添加几张表, 而对于SQL Server的存储过程、触发器等重要资源从来不用或者是不会用。以往的实验项目都是教材自带, 这些项目只是流于形式, 实质性的操作不多, 致使学生上机无所事事, 平时不加强练习, 做毕业设计和专业作品时学生才醒悟, 以前真该多学点本领。因此, 需加强实验项目, 改进实验内容。

根据课程实用性很强的特点, 通过实践教学, 培养学生的实践动手能力, 为学生的后续实践活动, 今后从事专业技术和科研工作打下良好基础。根据本校定位和人才培养方案, 选取适合自己学生的教材, 编写适合自己学生的实验项目, 包括基础实验和综合实验。

1.2合理设计上机实验, 编写典型实验项目

每一章中都设计典型的、能充分体现所学知识点的上机实验题目。课程即将结束时, 应该给学生实际演示一些项目, 例如用ASP做的系统项目, 让学生能看到它的底层设计, 让学生真正明白编程语言与SQL Server之间的关系, 为将来的专业作品和毕业设计做准备。由于编程开发工具ASP在本门课程之前, 学生具备了一定的编程能力, 所以结课之前, 可以让学生做成一个完整的系统项目, 前台开发工具任选, 但是后台需链接SQL Server数据库, 并且要用到所学的大部分数据库对象, 例如表、视图、完整性约束、存储过程、触发器、角色等。

一定要让学生明白, 平时学习、练习的这些实验项目实用性很强, 以后肯定要用, 尤其最后一年毕业, 要大量用到SQL Server, 不要到时悔之晚矣。根据课程实用性很强的特点, 通过实践教学, 培养学生的实践动手能力, 为学生的后续实践活动, 今后从事专业技术和科研工作打下良好基础。

以下为设计的实验项目:

1) 对student数据库进行如下操作

分离数据库, 看看资源管理器中还有文件吗;附加数据库;收缩数据库;删除数据库;给数据库改名。

2) 在d盘创建文件夹“SQL Server2000数据库”, 在此文件夹中创建数据库mydb。

方法1是在企业管理器中;方法2是在查询分析器中使用T-SQL语句。

3) 对三个表进行操作

(1) 在学生表中添加字段、性别默认值男;年龄要求年龄在18-30之间;身高decimal;再往表中输入两条记录。

(2) 分别在三个表的设计器窗口中, 查看三个表的主键和外键;查看三个表的关系;

(3) 给学生表创建索引、索引字段为学号, 簇索引;索引字段为姓名, 唯一索引;

查看学生表的记录按哪个字段排序;把姓名设置为簇索引;再查看学生表的记录按哪个字段排序;

(4) 创建数据表tmp、is_row uniqueidentifier;id int设置为标识种子, 基数为1, 递增量为2;位:bit数据类型。

(5) 删除tmp。

4) 在pubs数据库中进行操作

创建关系图;设置索引。

5) T-SQL语言实验

(1) 查询学生表的所有信息;查询学生表中的男生的信息;查询学生表中所有学生的姓名和出生日期;查询学生表中的人数, 并把结果保存到新表newtable中。

(2) 查询Lesson Table中的所有信息, 并按照课程名升序排列。

(3) 查询Lesson Select Table中12001课程的平均分。

(4) 查询学生表中所有姓王的学生。

(5) 查询学生表中男女生各多少人?

(6) 查询学生表中各班有多少人?

(7) 查询学生表中的姓名和年龄两列。

6) 索引和视图的实验

(1) 为Student Table的Student Name创建惟一索引。

(2) 创建视图Student View, 通过该视图只能看到学生姓名和生源地, 并且用中文字段名替代基础表中的英文字段名。保存为文件名Student View.sql。

(3) 创建视图Lesson View, 通过该视图可以

查询Lesson Table和Lesson Select Table中每门课的平均成绩 (要求该视图中包含课程名和平均成绩两列) 。保存为文件名Lesson View.sql。

(4) 创建视图Depart Lesson View, 通过该视图可以在Department Table和Lesson Table中查询每个系的开课情况 (要求该视图中包含系名和课程名两列) 。保存为文件名De Lesson View.sql。

7) 存储过程和触发器的实验

(1) 创建不带参数的简单存储过程procstu, 通过该存储过程只能看到学生表的姓名、性别、出生日期、籍贯。执行该存储过程。代码保存为文件名procstu.sql。

(2) 创建一个带有输入参数的存储过程procdep, 通过该存储过程根据给定的系名称, 可以查询到该系的学生的情况 (学号、姓名、性别、出生日期、系编号、系名称)

(3) 在职工和部门表的基础上创建存储过程procfind, 实现如下功能、给定一个部门名称, 得到该部门职工的编号、姓名、出生日期、工资。 (该存储过程带有一个输入参数)

(4) 在职工表的基础上创建存储过程procdep, 实现如下功能、得到Memo为“打字员”的职工的基本信息。 (本题为简单的存储过程, 不带输入参数)

(5) 创建带有一个输入参数和一个输出参数的存储过程 (stu) , 给定一个学号, 得到学生姓名。保存为stu.sql。执行该存储过程, 保存为stua.sql。

(6) 创建带有一个输入参数和一个输出参数的存储过程 (countnum) , 给定一个系的名称, 得到该系的学生人数。保存为countnum.sql。执行该存储过程, 保存为countnuma.sql。

(7) 创建一个带有返回值的存储过程 (less) , 给定一个课程名称, 看是否存在该门课程, 存在则显示“存在该门课程”, 否则显示“不存在该门课程”。保存为less.sql。执行该存储过程, 保存为lessa.sql。

SQL数据库技术 篇5

1,因为SQL2000安装过程中无法修改实例名称,故安装过程中必须先安装SQL2000,然后在安装SQL2005.安装SQL2005时必须修改SQL实例名称,否则将和SQL2000的实例名称冲突.

2,安装完SQL2005后,开始准备用SQL2000客户端工具连接SQL2005,结果报告不能用SQL2000客户端工具连接,只能用SQL2005客户端工具连接,后证明SQL2005客户端工具可以同时连接SQL2000和SQL2005.

3,服务器上安装完成后,准备用远程连接SQL服务器试试,结果始终连接不上SQL2005,只能连接上SQL2000.原来SQL2000服务器用的端口始终是1433端口,而SQL2005使用的是动态端口.那如果用程序连接SQL20005服务器的时候,程序如何知道SQL2005服务器用的是什么端口呢?原来SQL2005提供了一个SQL BROWER服务,开启这个服务后,就可以通过查询SQL BROWER服务就可以知道SQL2005现在正在使用哪个端口.因为项目需要,需要把这个SQL2005的动态端口改成静态的,修改过程如下:

运行SQL配置管理程序,找到SQL的实例名称下面的协议,双击右边的TCP/IP协议,在第二选项卡中IPALL里面输入SQL运行的端口就可以了(注意不能同时填写动态端口和静态端口,否则SQL下次将无法启动),修改完成后重新启动SQL服务即可生效.

4,修改端口后在连接发现仍然不能连接,原来还需要开启SQL2005的远程登陆服务.

在Microsoft SQL Server 2005中默认的是不允许远程登录的，会出现【provider: SQL 网络接口, error: 26 - 定位指定的服务器/实例时出错】的错误，下面的方法可以打开：

配置工具->sql server外围应用配置器->服务和连接的外围应用配置器->打开MSSQLSERVER节点下的Database Engine 节点,先择“远程连接”,接下建议选择“同时使用TCP/IP和named pipes”,确定后,重启数据库服务就可以，

同一服务器上安装SQL2000和SQL2005冲突解决方案数据库教程

，

SQL数据库技术 篇6

一、对 SQL Server 2000进行安全配置

首先对操作系统进行安全配置，保证操作系统处于安全状态。然后对要使用的操作数据库软件进行安全审核，如ASP、PHP脚本等，这是很多基于数据库的WEB应用可能常出现的安全隐患，过滤掉脚本中类似 @、 / 等字符，防止被构造恶意SQL语句。再安装SQL Server2000后打上sp1、 sp2补丁。

二、确认SQL服务器系统安装在NTFS分区，且应用权限控制列表

如果入侵者得到对数据库系统存取操作权限，该层权限可以阻止入侵者进一步破坏数据。

配置方法：找到SQL Server安装文件夹，默认安装在“C:Program Files Microsoft SQL Server”目录下。鼠标右键，选择“属性”。注意“共享”属性选项卡，设定为“不共享”。点击安全选项，只允许许可的用户访问该文件夹。

三、使用协议加密

SQL Server 2000使用Tabular Data Stream协议来进行网络数据交换，此协议所有的网络传输，如数据库内容、密码等都是明文的，是很大的安全隐患。它能被其他人在网络中截获，所以在条件容许情况下，最好使用SSL来加密协议。

四、修改或删除SA账户

修改或删除SA账户配置方法：打开SQL中的企业管理器，在工具选项卡中选择SQL server配置属性，点服务器设置，勾选允许“对系统目录直接进行修改”，然后打开查询分析器，登陆进去输入：

update sysxlogins set name=你要改成的名字 where sid=0x01

update sysxlogins set

sid=0xE765555BD44F054F89CD0076A06EA823where name=你要改成的名字。然后，在企业管理器中刷新登陆，可以看到SA变成了修改后的账户名，选中该账户名点击右键，出现删除的选项，即可执行删除SA账户操作。

五、使用安全的密码策略，禁用默认登录

很多数据库账号的密码过于简单，这跟系统密码过于简单是一个道理。SQL Server的“SA”账户在默认状态下是空密码，使用如下配置方法修改密码：

开始→所有程序→Microsoft SQL Server→企业管理器，展开控制台根目录，选择“安全性”，点击“登录”，用户列表中可以看到SA用户。双击打开，更改用户密码，设定一个足够复杂并且足够长度密码来加强其安全性，同时不要让该账号的密码写于应用程序或者脚本中。

另外可使用下面的SQL语句定期查看是否有不符合密码要求的账号：

Use master

Select name,Password from syslogins where password is null

在企业管理器中"安全选项" 之下禁用默认登录，使未经认可的不在 syslogins 表中的使用者无权登陆到有效的数据库服务器。

六、关闭允许对“系统目录直接进行修改”功能

配置方法：鼠标右键数据库服务器，选择“服务器设置”，在“服务器行为”字段下，关闭“系统目录直接进行修改”。如果开启此功能，在数据库架构内可以使用扩展存储过程对系统目录下的文件或者其他信息进行修改，或者在数据库架构内上传具有恶意功能的代码。严禁使用Xp_cmdshell命令。限制所有的账户拥有操作、因为如果恶意入侵者拥有操作该命令的权限，就可以轻松得到系统管理员的权限。

七、关闭远程服务器连接

如果允许使用远程服务器连接，入侵者在自己的机器上安装SQL Server就可以使用数据库服务器连接到你服务器的数据库上，从而造成级别很高的安全风险。

配置方法：展开控制台根目录，选择“（Local）Windows NT”，鼠标右键，选择“属性”，在配置窗口中选择“连接”，去掉“允许其他SQL Server使用RPC远程连接到本SQL Server”选项，防止他人使用数据库的恶意连接。

八、关闭远程终端服务和Web管理功能，禁用使用者交互式登录SQL Server

一旦其中某个使用者能够交互式进入一个服务器之内，就有可能利用管理员的存取特权得到管理员权限。

配置方法： 展开控制台根目录，选择“（Local）Windows NT”，鼠标右键，选择“属性”，在配置窗口中选择“安全性”选项卡，启用混合模式安全性认证，在默认状态只是执行失败的审核，使用“SQL Server和Windows”身份验证，审核级别使用“全部”， 记录所有的身份验证状况。“无”表示不执行审核；“成功”表示只审核成功的登录尝试；“失败”表示只审核失败的登录尝试；“全部”表示审核成功的和失败的登录尝试。

九、建立一个低权限用户做为SQL服务器服务的查询操作专用账户

最好不要用LocalSystem或SA。 这个账户应该设定作为一个服务运行的最小权利，注意当使用企业管理器做以上设置时，文件、注册表和使用者权利上的 ACLs也同时被处理。。

配置方法：展开控制台根目录，选择“数据库”，展开，点击“用户”，在右侧用户列表中可以看到已经授权的用户，在默认状态下只有“SA”和“Guest”两个用户。双击打开已经建立的低权限账户，打开数据库用户属性。选择数据库脚色，尽可能低的权限。打开“权限”按钮，赋予用户相应的权限。

十、不要让人随便探测到你的TCP/IP端口

默认情况下，SQL Server使用1433端口监听，配置SQL Server时 请改变该端口。

配置方法：在实例属性中选择TCP/IP协议的属性，选择隐藏 SQL Server 实例。如果隐藏了 SQL Server 实例，则禁止了对试图枚举网络上现有的 SQL Server 实例的客户端所发出的广播作出响应，这样，除非用Port Scan，否则别人无法用1434来探测你的TCP/IP端口了。

十一、管理扩展存储过程

对存储过程进行大手术，并且对账号调用扩展存储过程的权限要慎重。我们在大多数应用中其实用不到多少系统的存储过程，而SQL Server的这么多系统存储过程只是用来适应广大用户需求的，所以可以删除不必要的存储过程，因为有些系统的存储过程能很容易地被人利用起来提升权限或进行破坏。如果不需要扩展存储过程xp_cmdshell ，使用这个SQL语句把它去掉：

use master

exec sp_dropextendedproc'xp_cmdshell'

使用以下语句也可以恢复该存储过程：

Exec sp_addextendedproc ' xp_cmdshell ', 'xpsql70.dll'

如果不需要可停用部分对象连接与嵌入自动化储存程序( 注意： 当这些储存程序被停用的时候 , 部分企业管理器功能可能丢失)：

Sp_OASetProperty

Sp_OAStop

Sp_OADestroy

Sp_OAGetProperty

Sp_OACreate

Sp_OAMethod

Sp_OAGetErrorInfo

禁用你不需要的以下注册表存储过程，它们有些能够读出操作系统管理员的密码，如：

Xp_regdeletevalue

Xp_regremovemultistring

Xp_regenumvalues

Xp_regread

Xp_regwrite

Xp_regaddmultistring

Xp_regdeletekey

移除其他您认为会造成威胁的系统储存进程如：

xp_enumqueuedtasks

xp_grantlogin

xp_logevent

xp_loginconfig

xp_logininfo

sp_sdidebug

xp_availablemedia

xp_cmdshell

xp_deletemail

xp_dirtree

xp_fixeddrives

xp_getfiledetails

xp_getnetname

xp_dropwebtask

xp_dsninfo

xp_enumdsn

xp_eventlog

xp_findnextmsg

xp_enumerrorlogs

xp_enumgroups

xp_revokelogin

xp_runwebtask

xp_snmp_raisetrap

xp_sprintf

xp_sqlinventory

xp_sqlregister

xp_sqltrace

xp_schedulersignal

xp_sendmail

xp_sscanf

xp_startmail

十二、加强数据库日志的记录，定期检测SQL日志文件

审核数据库登录事件的“失败和成功”，在实例属性中选择“安全性”，将审核级别选定为全部，这样在数据库系统和操作系统日志里面，就会详细记录所有账号的登录事件。

定期查看SQL Server日志，检查是否有可疑登录事件发生，或者使用DOS命令。findstr /C:“登录” d:Microsoft SQL ServerMSSQLLOG*.* ，检索其中成功登录或者登录失败的信息，从中找到非法入侵所作的数据库登录尝试， 配置方法：展开控制台根目录，选择“管理”，展开，选择“SQL Server日志”，在右侧窗口列表中打开所选日志文件，会看到上面有详细的记录。

十三、将数据库文件和日志文件分开存储在不同的物理存储设备上

配置方法：双击数据库文件名，打开数据库属性，点击“数据文件”选项，设置数据文件存放的位置。点击“事务日志”选项，设置日至文件存放的位置。一旦发生灾难性故障，如硬件损坏，数据文件和日志存放在一起，没有任何手段可以恢复数据。但若是做过数据备份，同时日志文件保存在其它位置，日志文件没有损坏，就可能恢复数据。

十四、禁用SQL邮件功能

它的存在会使潜在的攻击者递送潜在的 trojans ，病毒或是简单实现一个DOS攻击成为可能。

十五、推荐使用SQL Server2000支持的“完全”数据库故障还原模型方式

SQL Server2000支持“简单”、“完全”和“大容量日志记录”三种数据库故障还原模型，配置方法：选中数据库名称，鼠标右键选择属性，点击“选项”选项卡，在故障还原子段下有‘模型栏目，选择“完全”方式。

第十六、制定严格的数据库备份还原策略

设计有效的备份和还原策略需考虑的因此包括：数据可用性和防数据丢失目标、数据库特性、资源约束等。当然其中最重要的一点是务必将将数据库和备份放置在不同的设备上。 否则，如果包含数据库的设备失败，备份也将不可用。 此外，将数据和备份放置在不同的设备上还可以提高写入备份和使用数据库时的 I/O 性能。

SQL数据库技术 篇7

1 SQL事件探查的基本功能

SQL server Profiler仅监视某些插入事件，这些事件主要有：登录连接的失败、成功或断开连接、“Delete、Insert、Update”命令，远程存储过程调用的状态，存储过程的开始或结束，以及存储过程中的每一条语句：写入SQL server错误日志的错误，打开游标，向数据库对象添加锁或释放锁等。

监视SQL Server事件，将监视事件的结果存储到一个跟踪文件中，分析跟踪文件可以得知数据库在运行当中的瓶颈，也可以用来诊断数据库在运行当中所遇到的某些问题。可以监视几乎所有在SQL Server中可能发生的事件。

在SQL Server profiler中可连续地按顺序重播一个或多个收集的滚动更新跟踪文件。利用Profiler既可打开扩展名为.trc的跟踪文件，也可打开扩展名为.log的日志文件及SQL脚本文件，所以通过打开表或文件就可查看、分析跟踪。

SQL Server profiler可以将跟踪到的显示计划另存为XML格式文件，以后可以加载供图形显示计划在查询编辑器中显示而无需基础数据库。SQL Server profiler还会在捕获显示计划XML事件的时候显示这些事件的图形表示形式。使用基于XML的定义，此定义使SQL Server profiler更易于从其他类型的服务器和编程接口捕获事件。

SQL server Profiler是图形化实时监视工具，能帮助系统管理员监视数据库和服务器的行为，跟踪Transact-SQL语句和存储过程，可以把这些监视数据存入表或文件中，通过单步执行语句来测试Transact-SQL语句和存储过程以确保代码按照其方式运行。

SQL Server profiler可以将性能监视器计数与SQL Server事件或SSAS事件相关联、管理是可以从预定义的性能监视器计数器集中选择，并将其按指定的时间间隔进行保存，同时仍可收集SQL Server或SSAS跟踪。

2 SQL事件探查器在数据库中的应用

2.1 SQL事件探查器跟踪SQL Server事件

SQL事件探查器的默认事件对跟踪用户活动非常有用。而其他事件则用于完成SQL事件探查—找出特定的问题。

例如：在HIS系统中经常出现此类问题，一个病人在门诊收费后，处方信息无法传送到门诊发药窗口，HIS报告“执行SQL语句失败”的错误。这时，就可用探查器进行监察。

运行事件探查器，在HIS系统中的门诊收费模块中进行病人收费，再对应打开门诊发药窗口，接收信息直至出现错误报告，回到事件探查器查看最近的几条记录。

″Null insert into Dpeople,Mzgl.dbo.win″,

把语句放到查询分析器执行得出：

表中字段Dover=“f”的行超长，对应的CKJLD表中查该病人Dpepole字段，内容为9999，不符合该字段定义的长度，对应CKJLD表的信息如下：

Dwin/Dpeople/…/Dno;00/9999/…/221251

原来是因为门诊发药窗口中的未发药病人的信息太多都没有发，新收病人信息无法写入C觹.yf表中，导致C觹.fy表中字段Dover=“f”的行超长，对应的CKJLD表中查此病人Dpepole字段，内容为9999也超长报错，由于发药员没有将未发药病人的信息挂起，造成了这个错误的发生。将CKJLD表中对应的Dpeople长度修改为‘2’后，此病人信息可以发送到门诊窗口进行发药。上面跟踪到的是一条语句，但实际上，跟踪到的也可能是一条存储过程。

如：门诊药房发药后，有时HIS系统无法报出明确的错误，系统管理员可以通过跟踪得到一个存储过程。直接执行这个存储过程并不能知道问题所在，但事件探查器却指出了发生问题的存储过程，并提供了调试所需要的现场参数。

2.2 系统分析

当SQL Server数据库系统运行缓慢时，可以使用SQL事件探查器来进行跟踪和分析，可以用来捕获发送到SQL Server的所有语句以及与语句的执行性能相关的数据。

在事件探查器中添加end time，故障过程中，对整个系统进行了跟踪。很快就能得到大量的跟踪信息。通过比较start time和end time的时间间隔来判断SQL的执行效率。性能良好的SQL执行时间不会超过1分钟。超过1分钟的SQL都是重点分析的对象。

将执行时间长的语句放到查询分析器中去，对此语句进行性能评估，查看此语句的执行方式及各步骤在评估中所占的性能比，进而分析查询语句的瓶颈是什么原因引起的。

通过评估发现，YK_YPMXK所有的评估都占用了10%的性能份额，进一步分析，此表的查询并没有使用正常的ypdm(药品代码)、pym(拼音码)、wbm(五笔码)索引进行检索，而只使用了PK_YK_YPMXK,对于SQL server数据库，不使用索引进行检索效率是非常低的。找到问题后可以使用SQL语句进行强制指定索引。

另外也可以使用查询分析器的管理索引功能，将YK_YPMXK的索引删除，并重新建立正常的ypdm(药品代码)、pym(拼音码)、wb(五笔码)等恰当的索引。使用索引后，再使用查询分析器对此SQL进行评估，YK-YPMXK的性能消耗降到了1%，此SQL执行时间恢复正常，从而解决SQL语句执行时间长的问题。

SQL事件探查器是SQL Server系统提供给系统管理员便利的工具。使系统管理员可以灵活充分地利用这些工具，了解数据库的原理和运行规则，深入掌握HIS系统，更准确、方便、快捷地维护好数据库系统。

3 结束语

数据库管理系统异常庞大和复杂，随着数据库中数据的不断增大，数据库的运行速度必然会变得缓慢，当SQL Server数据库系统运行缓慢的时候，可以使用SQL事件探查器来进行跟踪和分析，创建SQL事件探查器跟踪无疑对分析和优化数据库都提供了便利的条件。

摘要：本文通过对SQL Server profile的基本功能的了解及事件探查与跟踪技术在数据库中的应用,阐明了SQL server profile在数据库系统中的重要作用。

关键词：SQL Server profiler,事件探查,跟踪分析

参考文献

[1]微软公司.SQL server系统管理[M].北京:北京希望电子出版社,1999.

[2]微软公司.SQL server实现数据库设计[M].北京:北京希望电子出版社,1999.

[3]Paul Nielsen微软公司.Microsoft SQL server宝典2000[M].北京:中国铁道出版社,2005.

[4]Conte M O P.SQL server2000开发指南.北京:清华大学出版社,2002.

SQL数据库技术 篇8

关键词：SQL Server,ADO,ASP,数据库

1. 引言:

微软公司的SQL Server是目前小型网络中常用的数据库管理系统之一。面向这种网络数据库的应用程序也在日益增多, 这种网络数据库应用系统的正常运行, 一般都依赖于已经存在的用户数据库。创建数据库的工作当然可以用SQL Server提供的SQL Enterprise Manager工具来完成, 但是, 如果能够在网上远程触发SQL Server定制的管理数据库, 专门管理应用程序需要的数据库及其设备, 对用户来说无疑更加理想。

2. 现存问题

大家知道, 在使用CREATE DATABASE语句创建一个数据库之前, 必须存在一个有剩余空间的数据库设备, 或者事先使用DISK INIT语句创建一个新设备。但是这些语句含有很多必需的参数, 而且如果不使用SQL Server的管理工具, 很多参数值往往难以确定。以创建DISK INIT语句为例, 其完整语法如下:

其中NAME和SIZE这两个参数都很容易得到, 麻烦的是物理名PHYSNAME和虚拟设备号VDEVNO这两个参数。前者要求是一个服务器上的物理文件全路径名;后者要求在1～255之间找一个没有被别的设备占用的号码。而在编写数据库管理程序时, 用户的服务器上有哪些设备号已经被占用, SQL Server装在哪个驱动器上, 都是无法预料的。

虽然, 使用SQL Server的管理工具SQL Enterprise Manager, 可以非常方便地创建、删除数据库设备, 或者扩大一个已经存在的数据库, 也可以非常方便地创建、删除或者修改一个数据库, 但是, 这个工具仍然要求我们输入很多不太常用的参数, 界面稍显复杂。更何况还要在网上远程触发SQL Server 7.0, 情况将更加复杂。为了能够解决好应用程序远程触发SQL Server 7.0我们提出了以下方案。

3. 解决方案

为了实现这样的目标, 首先必须想办法解决SQL语句中的参数设置问题。

3.1 参数设置

1) 创建设备的语句参数:创建设备的语句即前面提到的DISK INIT语句。为了简化问题, 我们可以指定与数据库名相同的设备文件名, 并将设备文件保存在master设备所在的子目录中。数据库名在设计应用程序时已经确定;而master设备所在的子目录, 可以从系统表sysdevices中查询得到。这样, 设备文件的物理名参数就确定下来了。

虚拟设备号的问题则比较复杂, 因为sysdevices系统表中没有"虚拟设备号"这样一个字段, 因此, 必须另想办法。

对SQL Server的系统存储过程sp＿helpdevice进行分析之后, 我们发现, 虚拟设备号是"隐藏"在sysdevices系统表的low字段中的, 借助另一个系统表spt＿values, 可以找到每个设备的虚拟设备号。这样, 我们只需要在一个循环中找一下某个设备号是否存在于sysdevices中, 就可以确定我们现在可用的虚拟设备号。

至于数据库设备的大小, 不妨设得大一些, 或者让用户指定一下也可以。

2) 创建数据库的语句参数:创建数据库的语句如下:

其中, 大部分参数都是可选的, 我们只需要指定一个设备名及数据库的大小即可, 而数据库名、设备名、大小在创建设备的时候已经确定好了, 所以, 这个语句的参数不存在问题。

3.2 远程定制管理数据库程序

用普通的网上开发语言Vbscript结合ADO和ASP, 我们就可以实现远程定制管理数据库程序。

为了实现与数据库服务器的连接, 我们必须选择一种数据库访问接口。虽然从网上访问SQL Server有很多接口可供选择, 但微软最新的数据库访问接口ADO (Active Data Objects) 无疑是最有前途的, 因为它为基于浏览器的数据库应用系统的实现提供了可能性。以下是一些用于数据库及其设备管理的常用函数。

1) 提取当前的工作数据库

由于管理任务一般都必须在master库中完成, 因此, 在执行管理任务之前, 最好保存当前工作库, 以便完成任务之后再切换回去。

2) 判断一个数据库设备是否存在

3) 判断一个虚拟设备号是否被占用:SQLExistDeviceNumber。

4) 找一个最小的尚未被占用的虚拟设备号:SQLGetUnusedDeviceNumber。

5) 取得SQL Server安装目录下的DATA子目录路径:SQL-GetDataPath。

6) 创建一个新设备:SQLCreateDevice。

7) 创建一个新的数据库:SQLCreateDatabase65。

8) 取数据库设备的详细信息:SQLGetDeviceInfo。

9) 扩大数据库设备的尺寸:SQLExpandDevice。

数据库应用系统在运行一段之后, 数据量的增大往往要求数据库增大, 进而要求扩大设备尺寸。可惜DISK RESIZE语句要求的尺寸参数为扩大后的新尺寸, 而非需要增加的尺寸。所以, 必须事先查到设备的原有尺寸, 才能使用DISK RESIZE语句

10) 判断一个数据库是否存在:SQLExistDatabase。

11) 删除一个数据库:SQLDropDatabase。

12) 删除一个数据库设备:SQLDropDevice。

13) 提取SQL Server的版本信息:SQLGetVersionString。

在新的SQL Server新版本中, 不再有数据库设备的概念, 创建数据库也将变得更加简单。在创建特定的用户数据库时, 为了区分不同的版本进行不同的操作, 取得SQL Server的版本是非常重要的。

在已有的数据库设备上远程触发SQL Server 7.0建立临时视图, 或者建立临时表都是相对简单的, 只要把ODBC配置好以后, 使用ASP中的Server对象建立连接就可以使用ADO对SQL Server 7.0数据库进行操作, 方法如下:

1) 生成可执行语句:

strSQLcreate="CREATE VIEW dbo."&session ("viewname") &"1AS SELECT TqueryView.*FROM TqueryView where"&textfield

2) 和已存在的数据库建立连接:

Set objPagingConn=Server.CreateObject ("ADODB.Connection")

objPagingConn.Open"Tlogin", "sa"

3) 执行应用程序建立临时视图:

objPagingRS.Open session ("querystring") , objPagingConn, 3, 1

4. 结束语

目前网络数据库已经发展的趋于完善, 但是如何提高网络数据库的查询速度和效率还有相当长的一段路要走, ADO网上管理数据库给网络开发人员提供了一个良好的手段, 使数据库的查询更加灵活方便, 建立临时表可以把上次查询的结果保存起来, 以供下一次查询, 这样既缩小了查询范围又提高了查询速度。相信随着网上技术的飞速发展, 越来越多的效率更高的方案将不断出现。

参考文献

[1].[美]Ron Soukup, Microsoft SQL Server 6.5技术内幕, 清华大学出版社, 1999

[2].《Visual C+++SQL Server数据库开发与实例》清华大学出版社, 2007.04.30

[3].汪晓平、吴勇强、张宏林等, ASP网络开发技术, 人民邮电出版社, 2000.1

[4].《SQL Server 2000数据库应用教程》邱李华, 李晓黎等编著

SQL数据库技术 篇9

近年来, 随着教育事业的飞速发展, 特别是高等教育的快速发展。教育管理信息化、现代化水平有了长足的进步。各高校在管理信息系统 (Management Information System, MIS) 方面已取得了可喜的成绩, 如大部分高校都在使用学生成绩管理系统、学籍管理系统、高校人事管理系统、高校学生就业管理系统等, 这些系统的成功实施为各高校的日常管理工作带来了很大的便利, 提高了管理效率和水平、在日常的工作中也积累了越来越多的数据。目前的这些数据库系统基本上都可以实现数据的录入、修改、统计、查询等功能。这些MIS大都是孤立的分属于不同的部门, 为完成某一个具体的工作而设计。如何方便、快捷、有效的统一利用这些数据, 为管理决策者提供事实依据, 成为一个亟待解决的问题。

因此, 建立一个企业级的高校管理决策支持系统 (Decision Support System, DSS) 则显得十分必要。这个管理决策支持系统应能满足日常简单的查询和维护, 同时能够向高校决策者提供有关教育形势的瞬时变化、发展趋势以及历史数据的深层次信息, 以利于决策。因此, 如何建立数据仓库, 更好的利用现有数据, 并在此基础上进行更高层次的处理--数据挖掘 (Data Mining, DM) 显得尤为重要。

2、系统基本结构

根据目前高校MIS的特点, 首先需要在较高层次上将不同信息系统中的数据综合、归类, 并进行分析利用的抽象, 即建立数据仓库 (Data Ware-house, DW) , 在DW的基础上进行联机分析处理 (OnLine Analysis Processing, OLAP) 和数据挖掘 (Data Mining, DM) , 为科学决策提供依据支持。

DW、OLAP和DM是三种相互独立又相互关联的技术。DW是从数据库技术发展中出现的一种为决策服务的数据组织、存储技术。DW由基本数据、历史数据、综合数据和元数据组成, 能提供综合分析、时间趋势分析等决策信息。OLAP是对多维数据进行分析的技术。由于大量数据集中于多维空间中, OLAP技术提供从多视角分析途径获取用户所需要的辅助决策的分析数据。DM对数据库或DW中的数据使用一系列方法进行挖掘、分析, 从中识别和抽取隐含的、潜在的有用信息, 并利用这些技术进行辅助决策。联机分析挖掘 (On Line Analytical Mining, OLAM是通过DM和OLAP有效结合而形成的技术。

他们从各自不同的角度辅助决策。DW是基础, OLAP和DM是两种不同的分析工具。根据高校自身特点, 采用DW+O-LAP+DM三者结合的方式, 构建决策支持系统.充分利用三者优点以提高辅助决策能力。基于以上情况, 高校管理决策支持系统结构如图1所示。其中通过元数据来支持和管理系统。

3、数据仓库

DW作为决策支持系统的基础, 它是面向主题的、集成的、稳定的、随时间变化的数据集合。DW不是业务数据的简单堆积, 而是从大量的事务型数据库中抽取数据, 并将其清理、转换为新的存储格式, 即为决策目标把数据聚合在一种特殊的格式中.随着此过程的发展和完善, 这种支持决策的、特殊的数据存储即被称为DW[1]。

DW把高校分散的、难以访问的日常营运数据转化为集中统一、随时可用的信息, 一般来说, 完整的DW具备建立、管理和使用全部成分。DW结构上可分为:数据源、后端加工、前端服务以及数据仓库的管理。数据源为DW提供源数据, 如各种子系统数据库、联机事务处理系统的事务数据、外部数据源等都可以作为DW的数据源。高校数据仓库的建立数据来源如图2所示。后端加工工具即数据ETL (抽取、转换、装载) :其功能是从数据源中抽取数据, 对数据进行检验和整理, 并根据DW的设计要求, 对数据进行重新组织和加工, 装载到DW的目标数据库中, 并且可以周期性地刷新DW以反映源的变化以及将DW中的数据作转储。数据仓库管理主要是通过元数据来实现的, 元数据仓储用于存储数据模型和元数据, 其中, 元数据描述了DW中源数据和目标数据本身的信息, 定义了从源数据到目标数据的转换过程, 这些可以通过数据建模工具管理以适应高校事务的发展, 来满足高校决策和综合分析的需要;

4、联机分析处理

利用关系数据库中的数据进行联机分析处理, 往往会因查询的数据信息不是缺乏分析能力, 就是因各信息系统之间相互隔离、结构各异, 使信息分析及决策人员不能据此对某一主题的相关数据进行多角度的比较、分析, 因而得出科学的分析结果。而基于DW的OLAP则很好的避免了该类问题的发生。它根据用户对企业业务理解的各种维度, 或者说从用户分析企业时的各种宏观领域出发, 从企业具有的各种原始数据转换出各类信息, 提供给分析、管理和领导人员快速、方便、交互地访问, 从不同领域、不同角度查看企业情况, 获得企业信息, 了解企业发展动态, 从而为快速响应市场、抢占市场先机、完善企业管理、提高企业竞争力和做出有效决策提供坚实的基础。

OLAP主要有两个特点, 一是在线性 (On-Line) , 体现为对用户请求的快速响应和交互式操作, 它的实现是由C/S这种体系结构来完成的;二是多维分析 (Multi＿Analysis) , 这也是OLAP技术的核心所在。

5、数据挖掘

DM是从大型数据库的数据中提取人们感兴趣的知识, 这些知识是隐含的、事先未知的潜在有用信息, 提取的知识可表示为概念、规律、模式、规则、约束、可视化等形式。DM意味着在一些事实或观察数据的集合中寻找模式的决策支持过程, DM的对象不仅是数据库, 也可以是文件系统、数据集合或DW。基于DW的DM技术, 其任务是发现DW中尚未被发现的知识。对于那些决策者明确了解的信息, 可以用查询、OLAP或其它工具直接获取, 而另外一些隐藏在大量数据中的关系、趋势等信息就需要DM技术。DM技术可从DW中找出大量真正有价值的信息和知识, 可以更好地对高校的发展历程和未来趋势做出定量分析和预测。为各高校的管理决策者提供更科学的决策依据, 从而有效的提高教学质量, 有针对性的加强教学管理。[2]

6、联机分析挖掘

联机在线分析挖掘可以表示为:OLAP+DM=OLAM, 即DM和OLAP的有效结合, 形成了联机在线分析挖掘。OLAP和DM属于两种不同的分析工具, 有各自的特色和缺点, 将它们结合起来, 这可以充分发挥各自特色。对于决策者来说, 利用OLAP工具对当前数据进行深入分析验证其提出的假设和问题之外, 也可以验证DM得出的预测性结论, 防止偏差。OLAP的在线性体现在与用户的交互和快速响应, 多维性则体现在它建立在多维视图的基础上.用户积极参与分析过程, 动态地提出分析要求、选择分析算法, 对数据进行由浅及深的分析。DM与OLAP不同, 主要体现在它分析数据的深入和分析过程的自动化, 自动化是说, 其分析过程不需要用户的参与, 这是它的优点, 也正是它的不足, 因为在实际中, 用户也希望参与到挖掘中来, 如只想对数据的某一子集进行挖掘, 以及对不同抽取、集成水平的数据进行挖掘, 还有想根据自己的需要动态选择挖掘算法等等。由此可见, OLAP与DM各有所长, 如果能将二者结合起来, 发展一种建立在OLAP和DW基础上的新的挖掘技术, 将更能适应实际的需要。而OLAM (OnLine Analytical Mining或OLAP Mining) , 正是这种结合的产物。也是这种新型的决策工具发展的方向。但是建立在庞大数据库或DW基础上的OLAM在实现过程中面临的最大挑战是执行效率的提高和对用户请求的快速响应, 目前还没有OLAM的产品出现。

7、挖掘实例

根据福建交通职业技术学院2006级学生的资料建立数据库, 主要数据内容为学号, 姓名, 学生所在学院, 高考成绩, 高考外语成绩, 成绩平均分, 英语四级, 英语四级通过的学期等。将相应字段的字符型数据经规范后, 转换成数值型数据进行编码。如将学生所在学院规范成以下编码信息:

在此基础上采用遗传算法进行关联模式的挖掘。在本例中遗传算法采用以下运行参数:初始种群M选为1000, 交叉概率PC设为85%, 变异概率Pm设为5%, 编码串长度L设为6, 终止条件是经过两代运算后, 没有小于用户给定阈值的规则。经运算后在学生的资料库中发现部分关联规则如下。

1) <1000>-><014> (4%support, 98%confidence) 该规则表明船政学院的学生在第四学期通过英语四级的可信度为98%, 支持度为4%也就是说船政学院的学生第四学期基本上都能通过大学英语四级考试。2) <OOO04>-><08> (2%support, 99%confidence) 该规则表明平均分低于70分的学生到第8学期仍未通过大学英语四级的可信度为99%, 支持度为2%也就是说平均分低于70分的学生基本上未通过四级考试。3) <0003>-><010> (12%support, 90%confidence) 该规则表明高考外语成绩高于90分 (150分制) 的学生大都能通过四级考试.其可信度为90%, 支持度为12%。通过以上的挖掘发现, 我们可以有针对性的调整教学计划, 有目的, 有方向的做到因材施教, 切实加强学生的素质教育, 提高学生的综合能力。

8、结束语

DM技术已经在商业、金融业、保险业、电信业等多个领域开始得到应用, 取得了令人满意的效果。在教育领域里, 随着MIS数据信息的不断增长, 把DM技术应用到MIS中, 以建立高校管理决策支持系统, 必将为高校各级领导部门的决策, 提供切实可行的提高教学质量、优化教学资源的依据, 为各高校在激烈的竞争中掌握主动, 在未来的发展中提供更广阔的空间, 发挥重要的作用。

参考文献

[1].INMON W H.王志海译.Building the Data Warehouse[M].北京:机械工业出版社, 2O00.

SQL数据库的并发控制 篇10

1 数据库的并发问题

如果没有锁定且多个用户同时访问一个数据库, 则当他们的事务同时使用相同的数据时可能会发生数据不一致问题, 这就是并发问题。他包括:

1.1 丢失更新。

当两个或多个事务选择同一行, 然后基于最初选定的值更新该行时, 会发生丢失更新问题

1.2 未确认的相关性 (脏读) 。

当第二个事务选择其他事务正在更新的行时, 会发生未确认的相关性问题。

1.3 不一致的分析 (非重复读) 。

当第二个事务多次访问同一行而且每次读取不同的数据时, 会发生不一致的分析问题。

1.4 幻象读。

当对某行执行插入或删除操作, 而该行属于某个事务正在读取的行的范围时, 会发生幻象读问题。

2 SQL Server的并发处理机制

SQL Server使用锁定确保事务完整性和数据一致性。锁定可以防止用户读取正在由其他用户更改的数据, 并可以防止多个用户同时更改相同的数据。大多数情况下, 你不用关心如何为资源加锁, 使用什么类型的锁, 这些都是SQL Server自动控制的。但可以通过了解锁并在应用程序中自定义锁来设计更有效的应用程序。

2.1 锁粒度

SQL Server具有多粒度锁定, 允许一个事务锁定不同类型的资源。锁定在较小的粒度可以增加并发但需要较大的开销, 因为如果锁定了许多行, 则需要控制更多的锁。锁定在较大的粒度就开发而言是相当昂贵的, 因为锁定整个表限制了其他事务对表中任意部分进行访问, 但要求的开销较低, 因为需要维护的锁较少。

SQL Server可以锁定的资源如表1所示:

2.2 锁模式

SQL Server使用不同的锁模式锁定资源, 这些锁模式确定了并发事务访问资源的方式。SQL Server支持的锁模式如表2所示:

2.3 隔离级别

前面已经介绍了锁模式和锁资源的粒度级别, 它们可以在一定程度上控制并发。在这个基础上, 隔离级别定义了各个并发事务之间的隔离程度。SQL Server定义了四种隔离级别, 分别是未提交读、提交读、可重复读和可串行读。这四种隔离级别允许不同类型的行为, 如表3所示:

2.4 新增的快照机制

SQL Server2005中引入了一项被称为行版本控制的技术。利用该技术, 在事务修改数据之前会在tempdb数据库中存储一份具有一致性的数据副本, 这样当进程需要读取数据时, 不必请求资源上的共享锁, 可以直接从tempdb数据库的版本存储区中读取, 因此它不会与写进程发生冲突, 从而提高了并发性。

3 提高系统并发性的一些措施

通过了解SQL的并发控制机制, 我们根据减少锁、减少保持锁的时间、减少冲突的原则, 提出一些提高系统并发性的措施:

3.1 优化SQL语句的性能, 尤其是SELECT语句的性能。首先, 减少SQL语句的运行时间可以缩短该语句在资源上保持锁的时间, 从而允许更多的互斥操作。其次, 多数SELECT语句的性能问题都是因没有合适的索引, 或者即使有索引但未利用索引所导致的。通过创建合适的索引或优化SQL语句使之利用索引, 从而也就避免了在表中的所有行或整个表上保持锁。

3.2 使用尽量短的事务, 尤其是要避免在事务执行期间与用户交互。这样可以减少锁的保持时间。

3.3 在读写操作过多导致大量死锁的系统中使用快照隔离级别或已提交读快照隔离级别, 但这时要特别注意优化tempdb数据库。

3.4 如果不能使用快照, 则尽量使用较低的隔离级别。隔离级别越低, 允许的并发性越高, 反之亦然。

3.5 采用合适的RAID磁盘阵列系统。数据文件适合使用RAID10磁盘阵列, 而日志文件通常使用RAID1就足够了。另外, 因为日志是连续写入的, 把日志文件和数据文件放在同一个磁盘驱动器会推迟数据操作, 所以, 最好把日志文件放到单独的磁盘驱动器。

3.6 对大量数据使用分区表, 通过把请求均匀分布到各个分区来提高并发性。但要注意把每个分区放到不同的磁盘驱动器才能最大限度地发挥它的优化作用。

3.7 通过复制把数据库发布到多个物理的服务器, 以实现数据库级的负载均衡。

参考文献

SQL数据库技术 篇11

关键词：SQL Server数据库 岗位能力 认知规律 课程开发

数据库技术是现代信息科学与技术的重要组成部分，是计算机处理数据和管理信息的基础，是数据库应用系统的核心部分。随着计算机技术与网络技术的飞速发展，数据库技术得到广泛的应用与发展，如今各类信息系统和网站的开发都需要使用后台数据库，各行各业的数据大多是利用数据库进行存储和管理的，数据库已经成为信息系统和动态网站不可或缺的组成部分。

1 课程设置和课程定位

1.1 职业岗位需求分析 高职院校每开设一门课程都应进行市场调研，进行职业岗位需求分析，了解市场对该课程的知识、技能有哪些具体要求，课程定位是否准确，课程内容是否流行或落后。每当给学生开设一门新课程或训练一种新技能时，首先应该让学生了解该课程在所学专业的课程体系中的地位和作用，对学习后续课程有哪些帮助，学生明确了学习目标，有助于提高学习的兴趣。通过走访调研企业以及对网上招聘信息进行分析等方式，确定数据库相关职业岗位及岗位能力需求。

1.2 课程定位 根据对数据库相关职业岗位的知识、技能和素质需求分析，同时充分了解高职学生的认知规律和专业技能的形成规律，为使学生熟练掌握数据库的基本理论和开发技术，高职院校一般会选用Access、SQL Server和Oracle三种主流数据库管理系统作为教学内容，其软件开发实训、信息系统和动态网页开发类毕业设计等教学环节一般选择这三种数据库作为后台数据库。许多高职院校计算机类专业开设了1～2门数据库技术方面的课程。其中，SQL Server数据库是软件开发和动态网站开发时经常使用的一种数据库管理系统，SQL Server数据库课程也是计算机类专业的一门核心课程。

2 课程教学设计和实施

2.1 教学设计思路 根据程序员和数据库管理员的岗位能力要求和学生的认知规律，以企业工程项目为导向，按照项目化任务驱动式教学设计，将项目分解为多个既独立又有一定联系的小任务，将知识的讲解和技能训练设计在同一教学单元，融教学做于一体。每个任务都是先提出任务目标，然后由教师演示任务完成过程，最后让学生模仿完成类似的任务，体现“做中教、做中学”的教学理念。

2.2 教学项目选取 教学项目的选取和设计至关重要，教师不能闭门造车，每个项目源于企业真实工程项目但又不能照搬，教师需要按照涵盖的知识点对其进行加工提炼。课程在教学项目的选取上，采用教学课堂示范项目、学生课堂实践项目和实训项目三个层次贯穿整个教学过程。笔者选取的三级项目分别是“示范项目-电子商城系统（webshop）”、“课堂实践项目-电子商城系统（webshop1）”和“实训项目-企业人事管理系统（Factory）等”。

2.3 教学内容设计 课程教学围绕三级项目展开，其中教学示范項目的设计最为关键。示范项目由教师在课堂上演示，引出学生应该掌握的知识点和技能。按照数据库创建与管理工作过程，将一个大的示范项目分解成若干小项目，每个小项目按照“任务目标-任务导航-任务描述-任务完成”等环节进行设计。以“子项目：webshop数据库创建”为例，下表给出项目化任务驱动式的具体设计：

■

课堂实践项目与示范项目间知识点要对应，适合学生在课堂上模仿完成。对课堂实践项目，教师需要设计好课堂实践日志，明确给出实践项目的各个任务描述，以便学生有的放矢地进行训练。当学生完成了课堂实践中规定的任务之后，在学完整个课程时会发现自己已经创建了一个完整的数据库应用系统。

示范项目和课堂实践项目穿插在一个教学单元中，以4学时作为1个教学单元，便于实现“讲-练”循环，理论与实践交互，融为一体。

实训项目供学生进行课外拓展，是为了开阔学生的思路，巩固所学知识。教师也可提供多个真实的项目，由学生根据自己所熟悉的行业选择一个项目，采用小组分工协作的方式，划分出每人的工作任务，在教师引导下逐步完成。

2.4 教学模式及教学组织环节设计 由于课程的实践性要求很强，知识点多，传统的授课方式是“多媒体课件+实例演示”，而且理论课和实验课往往隔了几天，不能做到及时练习，及时巩固，不利于对多知识点的记忆和应用。为了达到良好的教学效果，该课程采用教学做一体化教学，即上课上机均在机房进行。在机房利用电子教室软件进行广播教学，学生在听完老师讲解和演示后，可以马上上机实践，遇到问题，学生可以相互讨论，教师也可以现场进行解答。讲授与练习同时进行，边讲边练，讲练循环，学生的印象更深刻，学习效率更高。这种授课方式彻底解决了传统教学模式存在的理论与实践课程不论是时间还是地点上都过于分离的问题。课程教学组织与实施环节具体如下图所示：

2.5 课程考核设计 课程考核是教学过程中不可缺少的环节，是评价教师教学质量和学生学习效果的重要手段。与课程的教学设计和过程组织相适应，本课程采用形成性考核方式，用以加强课程学习过程的控制，注重学生学习能力的评价。课程考核成绩主要来自以下三个方面：

①课堂实践成绩：来自学生每次课堂实践日志中任务的完成情况。教师在设计课堂实践日志的任务时，就给该教学单元设计一定的分值，学生完成日志任务的同时也就获得了相应的分值，逐步累计到学期末。这种任务和分值驱动的考核方式极大激发了学生完成项目任务的热情。②实训项目成绩：实训项目由学生在课外以团队分工协作的形式完成，实训项目成绩由项目组长和项目组成员互评，主要考察学生在项目中承担的工作量以及技术贡献度。③课程答辩成绩：课程答辩主要围绕数据库知识、技能积累的程度、解决实际问题的能力以及课程学习的收获与体会等方面，答辩组由教师和各项目组长组成。

课程总评成绩按照课堂实践50%、实训项目30%、课程答辩20%的组成比例综合评定。

3 结束语

SQL Server数据库课程教学以职业岗位能力要求为目标，以学生的认知规律为基础，充分发挥理论与实践相结合的一体化教学优势，以学生为主体，学生在学中做，在做中学，在完成具体任务的过程中学习知识和技能，学习的积极性和主动性得到充分发挥，教师从传统的知识传授者转化为学习过程组织者、指导者和评价者，有效地将学习过程向学生自觉的学习过程转化，学生在相对开放的教学组织形式下，在轻松活跃的课堂氛围中，愉快地完成整个课程的学习。

参考文献：

[1]刘志成主编.SQL Server 2005实例教程[M].北京：电子工业出版社，2009年7月.

[2]刘育锋编著.面向世界的职业教育新探索[M].北京：北京理工大学出版社，2009.1.

[3]梁小晓著.高职基于工作过程导向的《SQL Server数据库》教学研究.中国西部科技[J]，2009年10月.

[4]李雄杰著.职业教育理实一体化课程微观设计研究.职教通讯[J]，2011年5月.

SQL攻击数据库的防范措施 篇12

数据库系统的安全除依赖自身的安全机制外, 还与外部网络环境、应用环境、从业人员素质等因素息息相关, 因此, 从广义上讲, 数据库系统的安全框架可以划分为五个层次, 这里主要讲其中的三个层次。

(1) 网络层安全

从广义上讲, 数据库的安全首先依赖于网络系统。随着Internet的发展和普及, 越来越多的公司将其核心业务向互联网转移, 各种基于网络的数据库应用系统如雨后春笋般涌现出来, 面向网络用户提供各种信息服务。可以说网络系统是数据库应用的外部环境和基础, 数据库系统要发挥其强大作用离不开网络系统的支持, 数据库系统的用户 (如异地用户、分布式用户) 也要通过网络才能访问数据库的数据。网络系统的安全是数据库安全的第一道屏障, 外部入侵首先就是从入侵网络系统开始的。

从技术角度讲, 网络系统层次的安全防范技术有很多种, 大致可以分为防火墙、入侵检测、VPN技术等。

(2) 操作系统层安全

操作系统是大型数据库系统的运行平台, 为数据库系统提供一定程度的安全保护。目前操作系统平台大多数集中在windows NT和UNIX, 安全级别通常为C1、C2级。主要安全技术有操作系统安全策略、安全管理策略、数据安全等方面。

操作系统安全策略用于配置本地计算机的安全设置, 包括密码策略、账户锁定策略、审核策略、IP安全策略、用户权利指派、加密数据的恢复代理以及其他安全选项。具体可以体现在用户账户、口令、访问权限、审计等方面。

(3) 数据库管理系统层安全

数据库系统的安全性很大程度上依赖于数据库管理系统。如果数据库管理系统安全机制非常强大, 则数据库系统的安全性能就较好。目前市场上流行的是关系式数据库管理系统, 其安全性功能很弱, 这就导致数据库系统的安全性存在一定的威胁。

数据库管理系统层次安全技术主要是用来解决这一问题的, 即当前面两个层次已经被突破的情况下仍能保障数据库数据的安全, 这就要求数据库管理系统必须有—套强有力的安全机制;解决这一问题的有效方法之—是数据库管理系统对数据库文件进行加密处理, 使得即使数据不幸泄露或者丢失, 也难以被人破译和阅读。

以上这三个层次构筑成数据库系统的安全体系, 与数据安全的关系是逐步紧密的, 防范的必要也逐层加强, 从外到内、内表及里保证数据的安全。

2 SQL注入攻击的防范

随着一些自动化注入攻击的出现, 目前针对Web应用的SQL注入攻击越来越普遍, 技术也在不断翻新。但是SQL注入的基本原理还是通过构造畸形的SQL语句, 绕过认证系统获得敏感信息。然而为了使用Web服务器和数据库服务器的功能, 实现信息交互的目的、就不可避免的暴露一些可以被攻击者非法利用的安全缺陷。如何采取有效的措施阻止内部信息泄露, 将系统的安全威胁降至最低是防护的关键。这需要从配置Web服务器、配置数据库和编写安全代码等多方面着手, 加强系统安全性, 这里仅仅从Web服务器的安全配置方面来简单阐述一下方法或者是措施。

由于Web服务器庞大而复杂的结构, 使得Web服务器在安全方面难免存在缺陷。正确配置Web服务器可以有效降低SQL注入的风险。

(1) 修改服务器初始配置

服务器在安装时会添加默认的用户和默认口令, 开启默认的连接端门等, 这些都会给攻击者留下入侵的可能。在安装完成后应该及时删除默认的账号或者修改默认登录名的权限。关闭所有服务端口后, 再开启需要使用的端口。

(2) 及时安装服务器安全补丁

及时对服务器模块进行必要的更新, 特别是官方提供的有助于提高系统安全性的补丁包。使服务器保持最新的补丁包, 运行稳定的版本。

(3) 关闭服务器的错误提示信息

错误提示信息对于调试中的应用程序有着很重要的作用, 但是当Web应用一旦发布, 这些错误提示信息就应该被关闭。详细的错误信息也会让攻击者获得很多重要信息。自行设置一种错误提示信息, 即所有错误都只返回同一条错误消息, 让攻击无法获得有价值的信息。

(4) 配置目录权限

对于Web应用程序所在的目录可以设置其为只读的。通过客户端上传的文件单独存放, 并设置为没有可执行权限, 并且不在允许Web访问的目录下存放机密的系统配置文件。这样是为了防止注入攻击者上传恶意文件, 例如, Webshell等。

(5) 删除危险的服务器组件

有些服务器组件会为系统管理员提供方便的配置途径, 比如通过Web页面配置服务器和数据库、运行系统命令等。但是这些组件可能被恶意用户加以利用, 从而对服务器造成严重的威胁。为安全起见, 应当及时删除这样的服务器组件。

3 结语

从技术上讲, 如今的SQL注入攻击者们在如何找出有漏洞的网站方面更加聪明, 也更加全面了。出现了一些新型的SQL攻击手段。黑客们可以使用各种工具来加速漏洞的利用过程。我们不妨看看the Asprox Trojan这种木马, 它主要通过一个发布邮件的僵尸网络来传播, 其整个工作过程可以这样描述:首先, 通过受到控制的主机发送的垃圾邮件将此木马安装到电脑上, 然后, 受到此木马感染的电脑会下载一段二进制代码, 在其启动时, 它会使用搜索引擎搜索用微软的ASP技术建立表单的、有漏洞的网站。搜索的结果就成为SQL注入攻击的靶子清单。接着, 这个木马会向这些站点发动SQL注入式攻击, 使有些网站受到控制、破坏。访问这些受到控制和破坏的网站的用户将会受到欺骗, 从另外一个站点下载一段恶意的JavaScript代码。最后, 这段代码将用户指引到第三个站点, 这里有更多的恶意软件, 如窃取口令的木马。

虽然以前Web应用程序的程序员们对其代码进行测试并打补丁, 虽然SQL注入漏洞被发现和利用的机率并不太高。但近来攻击者们越来越多地发现并恶意地利用这些漏洞。因此, 在部署其软件之前, 开发人员应当更加主动地测试其代码, 并在新的漏洞出现后立即对代码打补丁。

摘要：针对数据库的攻击是各种各样的, 由于目前互联网上SQL攻击比较多, 并且危害比较大, 因此我们对SQL攻击的防范措施应该作为重点进行研究。