数据库编程总结
http://blog.csdn.net/byxdaz/archive/2010/04/11/5473557.aspx
当前各种主流数据库有很多，包括Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。数据库编程是对数据库的创建、读写等一列的操作。数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法；数据库服务端编程主要使用OLE DB等方法。数据库编程需要掌握一些访问数据库技术方法，还需要注意怎么设计高效的数据库、数据库管理与运行的优化、数据库语句的优化。
一、访问数据库技术方法
数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法；数据库服务端编程主要使用OLE DB等方法。
1、几种是数据库访问方法比较
ODBC API是一种适合数据库底层开发的编程方法,ODBC API 提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度。
DAO提供了很好的数据库编程的对象模型.但是,对数据库的所有调用以及输出的数据都必须通过Access/Jet 数据库引擎,这对于使用数据库应用程序,是严重的瓶颈。
OLE DB提供了COM接口，与传统的数据库接口相比，有更好的健壮性和灵活性，具有很强的错误处理能力,能够同非关系数据源进行通信。
ADO最主要的优点在于易于使用、速度快、内存支出少和磁盘遗迹小。
ADO.NET 是利用数据集的概念将数据库数据读入内存中，然后在内存中对数据进行操作，最后将数据集数据回写到源数据库中。
OTL 是 Oracle, Odbc and DB2-CLI Template Library 的缩写，是一个C++编译中操控关系数据库的模板库， OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行，进行操作DB2数据库则是通过CLI接口来进行，至于MS的数据库和其它一些数据库，则OTL只提供了ODBC来操作的方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式来进行操纵。具有以下优点：跨平台；运行效率高，与C语言直接调用API相当；开发效率高，起码比ADO.net使用起来更简单，更简洁；部署容易，不需要ADO组件，不需要.net framework 等。
2、 VC数据库编程几种方法
VC 数据库编程几种方法，包括ODBC连接、 MFC ODBC连接、DAO连接、OLE DB、OLE DB Templates连接、ADO、Oracle专用方法(OCI(Oracle Call Interface)访问、Oracle Object OLE C++ Class Library )。
<1.> 通用方法
1. ODBC连接
ODBC(Open DataBase Connectivity)是MSOA的一部分,是一个标准数据库接口。它提供对关系数据库访问的统一接口,实现对异构数据源的一致访问。
ODBC 数据访问由以下部分组成:
<1>句柄(Handles):ODBC使用句柄来标识ODBC环境、连接、语句和描述器.
<2>缓存区(Buffers):
<3>数据类型(Data types)
<4>一致性级别(Conformance levels)
用ODBC设计客户端的一般步骤:
<1>分配ODBC环境
<2>分配连接句柄
<3>连接数据源
<4>构造和执行SQL语句
<5>获得查询结果
<6>断开数据源的连接
<7>释放ODBC环境
ODBC API是一种适合数据库底层开发的编程方法,ODBC API提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度.因此,ODBC API编程属于底层编程。
2. MFC ODBC连接
MFC ODBC是MFC对ODBC进行的封装,以简化对ODBC API的 调用,从而实现面向对象的数据库编程接口.
MFC ODBC的封装主要开发了CDatabase类和CRecordSet类
(1) CDatabase类
CDatabase类用于应用程序建立同数据源的连接。CDatabase类中包含一个m_hdbc变量,它代表了数据源的连接句柄。如果要建立 CDatabase类的实例,应先调用该类的构造函数,再调用Open函数,通过调用,初始化环境变量,并执行与数据源的连接。在通过Close函数关闭数据源。
CDatabase类提供了对数据库进行操作的函数及事务操作。
(2) CRecordSet类
CRecordSet类定义了从数据库接收或者发送数据到数据库的成员变量,以实现对数据集的数据操作。
CRecordSet类的成员变量m_hstmt代表了定义该记录集的SQL语句句柄,m_nFields为记录集中字段的个数,m_nParams为记录集所使用的参数个数。
CRecordSet的记录集通过CDatabase实例的指针实现同数据源的连接,即CRecordSet的成员变量m_pDatabase.
MFC ODBC编程更适合于界面型数据库应用程序的开发,但由于CDatabase类和CRecordSet类提供的数据库操作函数有限,支持的游标类型也有限,限制了高效的数据库开发。在编程层次上属于高级编程。
应用实例：
1.打开数据库
CDatabase database;
database.OpenEx( _T( "DSN=zhuxue" ),CDatabase::noOdbcDialog);//zhuxue为数据源名称
2.关联记录集
CRecordset recset(&database);
3.查询记录
CString sSql1="";
sSql1 = "SELECT * FROM tablename" ;
recset.Open(CRecordset::forwardOnly, sSql1, CRecordset::readOnly);
int ti=0;
CDBVariant var;//var可以转换为其他类型的值
while (!recset.IsEOF())
{
//读取Excel内部数值
recset.GetFieldValue("id",var);
jiangxiang[ti].id=var.m_iVal;
recset.GetFieldValue("name", jiangxiang[ti].name);
ti++;
recset.MoveNext();
}
recset.Close();//关闭记录集
4.执行sql语句
CString sSql="";
sSql+="delete * from 院系审核";//清空表
database.ExecuteSQL(sSql);
sSql也可以为Insert ,Update等语句
5.读取字段名
sSql = "SELECT * FROM Sheet1" ; //读取的文件有Sheet1表的定义,或为本程序生成的表.
// 执行查询语句
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
int excelColCount=recset.GetODBCFieldCount();//列数
CString excelfield[30];
//得到记录集的字段集合中的字段的总个数
for( i=0;i数据提供程序
数据提供程序拥有自己的数据并把数据以表格的形式呈现给使用者使用.
<2>服务提供程序
服务提供程序是数据提供程序和使用者的结合。它是OLE DB体系结构中的中间件,它是OLE DB数据源的使用者和数据使用程序的提供者
<3>数据使用程序
数据使用程序对存储在数据提供程序中的数据进行使用和控制.
OLE DB开发程序的一般步骤:
<1>初始化COM环境
<2>连接数据源
<3>打开对话
<4>执行命令
<5>处理结果
<6>清除对象
应用实例：
使用OLEDB编写数据库应用程序
1 概述
OLE DB的存在为用户提供了一种统一的方法来访问所有不同种类的数据源。OLE DB可以在不同的数据源中进行转换。利用OLE DB，客户端的开发人员在进行数据访问时只需把精力集中在很少的一些细节上，而不必弄懂大量不同数据库的访问协议。
OLE DB是一套通过COM接口访问数据的ActiveX接口。这个OLE DB接口相当通用，足以提供一种访问数据的统一手段，而不管存储数据所使用的方法如何。同时，OLE DB还允许开发人员继续利用基础数据库技术的优点，而不必为了利用这些优点而把数据移出来。
2 使用ATL使用OLE DB数据使用程序
由于直接使用OLE DB的对象和接口设计数据库应用程序需要书写大量的代码。为了简化程序设计，Visual C++提供了ATL模板用于设计OLE DB数据应用程序和数据提供程序。
利用ATL模板可以很容易地将OLE DB与MFC结合起来，使数据库的参数查询等复杂的编程得到简化。MFC提供的数据库类使OLE DB的编程更具有面向对象的特性。Viual C++所提供用于OLE DB的ATL模板可分为数据提供程序的模板和数据使用程序的模板。
使用ATL模板创建数据应用程序一般有以下几步骤：
1）、 创建应用框架
2）、 加入ATL产生的模板类
3）、 在应用中使用产生的数据访问对象
3 不用ATL使用OLE DB数据使用程序
利用ATL模板产生数据使用程序较为简单，但适用性不广，不能动态适应数据库的变化。下面我们介绍直接使用MFC OLE DB类来生成数据使用程序。
模板的使用
OLE DB数据使用者模板是由一些模板组成的，包括如下一些模板，下面对一些常用类作一些介绍。
1）、 会话类
CDataSource类
CDataSource类与OLE DB的数据源对象相对应。这个类代表了OLE DB数据提供程序和数据源之间的连接。只有当数据源的连接被建立之后，才能产生会话对象，可以调用Open来打开数据源的连接。
CSession 类
CSession所创建的对象代表了一个单独的数据库访问的会话。一个用CDataSource类产生的数据源对象可以创建一个或者多个会话，要在数据源对象上产生一个会话对象，需要调用函数Open()来打开。同时，会话对象还可用于创建事务操作。
CEnumeratorAccessor 类
CEnumeratorAccessor类是用来访问枚举器查询后所产生的行集中可用数据提供程序的信息的访问器，可提供当前可用的数据提供程序和可见的访问器。
2）、 访问器类
CAcessor类
CAccessor类代表与访问器的类型。当用户知道数据库的类型和结构时，可以使用此类。它支持对一个行集采用多个访问器，并且，存放数据的缓冲区是由用户分配的。
CDynamicAccessor类
CDynamicAccessor 类用来在程序运行时动态的创建访问器。当系统运行时，可以动态地从行集中获得列的信息，可根据此信息动态地创建访问器。
CManualAccessor 类
CManualAccessor类中以在程序运行时将列与变量绑定或者是将参数与变量捆定。
3）、 行集类
CRowSet类
CRowSet 类封装了行集对象和相应的接口，并且提供了一些方法用于查询、设置数据等。可以用Move（）等函数进行记录移动，用GetData()函数读取数据，用 Insert()、Delete()、SetData()来更新数据。
CBulkRowset类
CBulkRowset类用于在一次调用中取回多个行句柄或者对多个行进行操作。
CArrayRowset类
CArrayRowset类提供用数组下标进行数据访问。
4）、 命令类
CTable类
CTable类用于对数据库的简单访问，用数据源的名称得到行集，从而得到数据。
CCommand类
CCommand 类用于支持命令的数据源。可以用Open()函数来执行SQL命令，也可以Prepare（）函数先对命令进行准备，对于支持命令的数据源，可以提高程序的灵活性和健壮性。
在stdafx.h头文件里，加入如下代码。
#include
extern CComModule _Module;
#include
#include
#include // if you are using schema templates
在stdafx.cpp文件里，加入如下代码。
#include
CComModule _Module;
决定使用何种类型的存取程序和行集。
获取数据
在打开数据源，会话，行集对象后就可以获取数据了。所获取的数据类型取决于所用的存取程序，可能需要绑定列。按以下步骤。
1、 用正确的命令打开行集对象。
2、 如果使用CManualAccessor，在使用之前与相应列进行绑定。要绑定列，可以用函数GetColumnInfo,如下所示：
// Get the column information
ULONG ulColumns = 0;
DBCOLUMNINFO* pColumnInfo = NULL;
LPOLESTR pStrings = NULL;
if (rs.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK)
AfxThrowOLEDBException(rs.m_pRowset, IID_IColumnsInfo);
struct MYBIND* pBind = new MYBIND[ulColumns];
rs.CreateAccessor(ulColumns, &pBind[0], sizeof(MYBIND)*ulColumns);
for (ULONG l=0; l与ATL中的CComPtr类似，是一个在析构时自动调用Release的类。CComPtr的代码在ATLBASE.H中定义。
2.以下代码均在UNICODE环境下编译，因为执行的SQL语句必须是UNICODE的。设置工程为UNICODE的方法是:首先在project->settings->C/C++的属性页中的Preprocessor中，删除_MBCS写入UNICODE,_UNICODE。然后在link属性页中Category中选择output，在Entry-Point symbol 中添加wWinMainCRTStartup。
EAutoReleasePtr pIDBInitialize;
HRESULT hResult = ConnectDatabase( &pIDBInitialize, _T("127.0.0.1"), _T(“sa”), _T("password") );
if( FAILED( hResult ) )
{
//失败，可能是因为数据库没有启动、用户名密码错等等
return;
}
EAutoReleasePtr pIOpenRowset;
hResult = CreateSession( pIDBInitialize, &pIOpenRowset );
if( FAILED( hResult ) )
{
//出错
return;
}
EAutoReleasePtr pICommand;
EAutoReleasePtr pICommandText;
hResult = CreateCommand( pIOpenRowset, &pICommand, &pICommandText );
if( FAILED( hResult ) )
{
//出错
return;
}
hResult = ExecuteSQL( pICommand, pICommandText, _T("USE PBDATA") );
if( FAILED( hResult ) )
{
//如果这里失败，那就是SQL语句执行失败。在此处，就是PBDATA还未创建
return;
}
// 创建表
ExecuteSQL( pICommand, pICommandText, _T("CREATE TABLE 2005_1(Volume real NOT NULL,ID int NOT NULL IDENTITY)") );
// 添加记录
ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO 2005_1 VALUES(100.0)") );
//...
其中几个函数的代码如下:
HRESULT ConnectDatabase( IDBInitialize** ppIDBInitialize, LPCTSTR pszDataSource, LPCTSTR pszUserID, LPCTSTR pszPassword )
{
ASSERT( ppIDBInitialize != NULL && pszDataSource != NULL && pszUserID != NULL && pszPassword != NULL );
UINT uTimeout ＝ 15U; // 连接数据库超时（秒）
TCHAR szInitStr[1024];
VERIFY( 1023 >= wsprintf( szInitStr, _T("Provider=SQLOLEDB;Data Source=%s;Initial Catalog=master;User Id=%s;Password=%s;Connect Timeout=%u"), pszDataSource, pszUserID, pszPassword, uTimeout ) );
//Initial Catalog=master指明连接成功后，"USE master"。
EAutoReleasePtr pIDataInitialize;
HRESULT hResult = ::CoCreateInstance( CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER,
IID_IDataInitialize, ( void** )&pIDataInitialize );
if( FAILED( hResult ) )
{
return hResult;
}
EAutoReleasePtr pIDBInitialize;
hResult = pIDataInitialize->GetDataSource( NULL, CLSCTX_INPROC_SERVER, ( LPCOLESTR )szInitStr,
IID_IDBInitialize, ( IUnknown** )&pIDBInitialize );
if( FAILED( hResult ) )
{
return hResult;
}
hResult = pIDBInitialize->Initialize( );
if( FAILED( hResult ) )
{
return hResult;
}
* ppIDBInitialize = pIDBInitialize.Detach( );
return S_OK;
}
HRESULT CreateSession( IDBInitialize* pIDBInitialize, IOpenRowset** ppIOpenRowset )
{
ASSERT( pIDBInitialize != NULL && ppIOpenRowset != NULL );
EAutoReleasePtr pSession;
HRESULT hResult = pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void** )&pSession );
if( FAILED( hResult ) )
{
return hResult;
}
EAutoReleasePtr pIOpenRowset;
hResult = pSession->CreateSession( NULL, IID_IOpenRowset, ( IUnknown** )&pIOpenRowset );
if( FAILED( hResult ) )
{
return hResult;
}
* ppIOpenRowset = pIOpenRowset.Detach( );
return S_OK;
}
HRESULT CreateCommand( IOpenRowset* pIOpenRowset, ICommand** ppICommand, ICommandText** ppICommandText )
{
ASSERT( pIOpenRowset != NULL && ppICommand != NULL && ppICommandText != NULL );
HRESULT hResult;
EAutoReleasePtr pICommand;
{
EAutoReleasePtr pICreateCommand;
hResult = pIOpenRowset->QueryInterface( IID_IDBCreateCommand, ( void** )&pICreateCommand );
if( FAILED( hResult ) )
{
return hResult;
}
hResult = pICreateCommand->CreateCommand( NULL, IID_ICommand, (IUnknown**)&pICommand );
if( FAILED( hResult ) )
{
return hResult;
}
}
EAutoReleasePtr pICommandText;
hResult = pICommand->QueryInterface( &pICommandText );
if( FAILED( hResult ) )
{
return hResult;
}
* ppICommand = pICommand.Detach( );
* ppICommandText = pICommandText.Detach( );
return S_OK;
}
HRESULT ExecuteSQL( ICommand* pICommand, ICommandText* pICommandText, LPCTSTR pszCommand, LONG* plRowsAffected )
{
ASSERT( pICommand != NULL && pICommandText != NULL && pszCommand != NULL && pszCommand[0] != 0 );
HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )pszCommand );
if( FAILED( hResult ) )
{
return hResult;
}
LONG lAffected;
hResult = pICommand->Execute( NULL, IID_NULL, NULL, plRowsAffected == NULL ? &lAffected : plRowsAffected, ( IUnknown** )NULL );
return hResult;
}
以上就是写数据库的全部代码了，是不是很简单呢？下面再来读的。
// 先用与上面代码中一样的步骤获取pICommand，pICommandText。此处省略
HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )_T("SELECT Volume FROM 2005_1 WHERE ID = @@IDENTITY") ); //取我们刚刚添加的那一条记录
if( FAILED( hResult ) )
{
return;
}
LONG lAffected;
EAutoReleasePtr pIRowset;
hResult = pICommand->Execute( NULL, IID_IRowset, NULL, &lAffected, ( IUnknown** )&pIRowset );
if( FAILED( hResult ) )
{
return;
}
EAutoReleasePtr pIAccessor;
hResult = pIRowset->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );
if( FAILED( hResult ) )
{
return;
}
// 一个根据表中各字段的数值类型而定义的结构，用于存储返回的各字段的值
struct CLoadLastFromDB
{
DBSTATUS dwdsVolume;
DWORD dwLenVolume;
float fVolume;
};
// 此处我们只查询了一个字段。如果要查询多个字段，CLoadLastFromDB中要添加相应的字段定义，下面的dbBinding也要相应扩充。dbBinding[].iOrdinal要分别指向各个字段，dbBinding[].wType要根据字段类型赋合适的值。
DBBINDING dbBinding[1];
dbBinding[0].iOrdinal = 1; // Volume 字段的位置，从 1 开始
dbBinding[0].obValue = offsetof( CLoadLastFromDB, fVolume );
dbBinding[0].obLength = offsetof( CLoadLastFromDB, dwLenVolume );
dbBinding[0].obStatus = offsetof( CLoadLastFromDB, dwdsVolume );
dbBinding[0].pTypeInfo = NULL;
dbBinding[0].pObject = NULL;
dbBinding[0].pBindExt = NULL;
dbBinding[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
dbBinding[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
dbBinding[0].eParamIO = DBPARAMIO_NOTPARAM;
dbBinding[0].cbMaxLen = 0;
dbBinding[0].dwFlags = 0;
dbBinding[0].wType = DBTYPE_R4; // float就是DBTYPE_R4，int就是DBTYPE_I4。参见MSDN
dbBinding[0].bPrecision = 0;
dbBinding[0].bScale = 0;
HACCESSOR hAccessor = DB_NULL_HACCESSOR;
DBBINDSTATUS dbs[1];
hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, dbBinding, sizeof( CLoadLastDataFromDB ), &hAccessor, dbs );
if( FAILED( hResult ) )
{
return;
}
ASSERT( dbs[0] == DBBINDSTATUS_OK );
ULONG uRowsObtained = 0;
HROW hRows[1]; // 这里我们只查询了最新的那一条记录
HROW* phRows = hRows;
CLoadLastFromDB rmd;
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );
if( SUCCEEDED( hResult ) && uRowsObtained != 0U )
{
hResult = pIRowset->GetData( phRows[0], hAccessor, &rmd );
if( FAILED( hResult ) )
{
ASSERT( FALSE );
}
ASSERT( rmd.dwdsVolume == DBSTATUS_S_OK );
// rmd.fVolume 就是我们要取的值
}
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIAccessor.Release( );
pIRowset.Release( );
读操作也完成了，是不是仍然很简单呢？下面我们再来看看最麻烦的二进制数据（text、ntext、image等）的读写。要实现BLOB数据的读写，我们需要一个辅助的类，定义如下：
class CSequentialStream : public ISequentialStream // BLOB 数据访问类
{
public:
CSequentialStream( );
virtual ~CSequentialStream( );
virtual BOOL Seek( ULONG uPosition );
virtual BOOL Clear( );
virtual ULONG GetLength( ) { return m_uBufferUsed; };
virtual operator void* const( ) { return m_pBuffer; };
STDMETHODIMP_( ULONG ) AddRef( ) { return ++ m_uRefCount; };
STDMETHODIMP_( ULONG ) Release( ) { ASSERT( m_uRefCount != 0U ); -- m_uRefCount; if( m_uRefCount == 0U ) { delete this; } return m_uRefCount; };
STDMETHODIMP QueryInterface( REFIID riid, LPVOID* ppv );
STDMETHODIMP Read( void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbRead );
STDMETHODIMP Write( const void __RPC_FAR* pv, ULONG cb, ULONG __RPC_FAR* pcbWritten );
void ResetPosition( ) { m_uPosition = 0U; };
HRESULT PreAllocBuffer( ULONG uSize );
private:
ULONG m_uRefCount; // reference count
void* m_pBuffer; // buffer
ULONG m_uBufferUsed; // buffer used
ULONG m_uBufferSize; // buffer size
ULONG m_uPosition; // current index position in the buffer
};
实现如下：
CSequentialStream::CSequentialStream( ) : m_uRefCount( 0U ), m_pBuffer( NULL ), m_uBufferUsed( 0U ), m_uBufferSize( 0U ), m_uPosition( 0U )
{
AddRef( );
}
CSequentialStream::~CSequentialStream( )
{
Clear( );
}
HRESULT CSequentialStream::QueryInterface( REFIID riid, void** ppv )
{
if( riid == IID_IUnknown || riid == IID_ISequentialStream )
{
* ppv = this;
( ( IUnknown* )*ppv )->AddRef( );
return S_OK;
}
* ppv = NULL;
return E_NOINTERFACE;
}
BOOL CSequentialStream::Seek( ULONG uPosition )
{
ASSERT( uPosition < m_uBufferUsed );
m_uPosition = uPosition;
return TRUE;
}
BOOL CSequentialStream::Clear( )
{
m_uBufferUsed = 0U;
m_uBufferSize = 0U;
m_uPosition = 0U;
( m_pBuffer != NULL ? CoTaskMemFree( m_pBuffer ) : 0 );
m_pBuffer = NULL;
return TRUE;
}
HRESULT CSequentialStream::PreAllocBuffer( ULONG uSize )
{
if( m_uBufferSize < uSize )
{
m_uBufferSize = uSize;
m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize );
if( m_pBuffer == NULL )
{
Clear( );
return STG_E_INSUFFICIENTMEMORY;
}
}
return S_OK;
}
HRESULT CSequentialStream::Read( void* pv, ULONG cb, ULONG* pcbRead )
{
( pcbRead != NULL ? ( * pcbRead = 0U ) : 0 );
if( pv == NULL ) { return STG_E_INVALIDPOINTER; }
if( cb == 0U ) { return S_OK; }
ASSERT( m_uPosition <= m_uBufferUsed );
ULONG uBytesLeft = m_uBufferUsed - m_uPosition;
if( uBytesLeft == 0U ) { return S_FALSE; } //no more bytes
ULONG uBytesRead = ( cb > uBytesLeft ? uBytesLeft : cb );
memcpy( pv, ( BYTE* )m_pBuffer + m_uPosition, uBytesRead );
m_uPosition += uBytesRead;
( pcbRead != NULL ? ( * pcbRead = uBytesRead ) : 0 );
return ( cb != uBytesRead ? S_FALSE : S_OK );
}
HRESULT CSequentialStream::Write( const void* pv, ULONG cb, ULONG* pcbWritten )
{
if( pv == NULL ) { return STG_E_INVALIDPOINTER; }
( pcbWritten != NULL ? ( * pcbWritten = 0U ) : 0 );
if( cb == 0U ){ return S_OK; }
ASSERT( m_uPosition <= m_uBufferUsed );
if( m_uBufferSize < m_uPosition + cb )
{
m_uBufferSize = m_uPosition + cb;
m_pBuffer = CoTaskMemRealloc( m_pBuffer, m_uBufferSize );
if( m_pBuffer == NULL )
{
Clear( );
return STG_E_INSUFFICIENTMEMORY;
}
}
m_uBufferUsed = m_uPosition + cb;
memcpy( ( BYTE* )m_pBuffer + m_uPosition, pv, cb );
m_uPosition += cb;
( pcbWritten != NULL ? ( * pcbWritten = cb ) : 0 );
return S_OK;
}
下面我们开始往一个包含ntext字段的表中添加记录。假设这个表（News）的结构为：ID int NOT NULL IDENTITY、Title nchar(80)、 Contents ntext。
// 先将记录添加进去，ntext字段留空。我们稍后再更新ntext的内容。
HRESULT hResult = ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO News VALUES('TEST','')") );
DBPROP dbProp;
dbPropSet.guidPropertySet = DBPROPSET_ROWSET;
dbPropSet.cProperties = 1;
dbPropSet.rgProperties = &dbProp;
DBPROPSET dbPropSet;
dbPropSet.rgProperties[0].dwPropertyID = DBPROP_UPDATABILITY;
dbPropSet.rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbPropSet.rgProperties[0].dwStatus = DBPROPSTATUS_OK;
dbPropSet.rgProperties[0].colid = DB_NULLID;
dbPropSet.rgProperties[0].vValue.vt = VT_I4;
V_I4( &dbPropSet.rgProperties[0].vValue ) = DBPROPVAL_UP_CHANGE;
EAutoReleasePtr pICommandProperties;
hResult = pICommandText->QueryInterface( IID_ICommandProperties, ( void** )&pICommandProperties );
// 设置 Rowset 属性为“可以更新某字段的值”
hResult = pICommandProperties->SetProperties( 1, &dbPropSet );
hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )L"SELECT Contents FROM News WHERE ID = @@IDENTITY" );
LONG lAffected;
EAutoReleasePtr pIRowsetChange;
hResult = pICommand->Execute( NULL, IID_IRowsetChange, NULL, &lAffected, ( IUnknown** )&pIRowsetChange );
EAutoReleasePtr pIAccessor;
hResult = pIRowsetChange->QueryInterface( IID_IAccessor, ( void** )&pIAccessor );
struct BLOBDATA
{
DBSTATUS dwStatus;
DWORD dwLength;
ISequentialStream* pISeqStream;
};
// 有关DBOBJECT、DBBINDING的设置，建议参考MSDN，很容易懂。
DBOBJECT dbObj;
dbObj.dwFlags = STGM_READ;
dbObj.iid = IID_ISequentialStream;
DBBINDING dbBinding;
dbBinding.iOrdinal = 1; // BLOB 字段的位置，从 1 开始
dbBinding.obValue = offsetof( BLOBDATA, pISeqStream );
dbBinding.obLength = offsetof( BLOBDATA, dwLength );
dbBinding.obStatus = offsetof( BLOBDATA, dwStatus );
dbBinding.pTypeInfo = NULL;
dbBinding.pObject = &dbObj;
dbBinding.pBindExt = NULL;
dbBinding.dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
dbBinding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
dbBinding.eParamIO = DBPARAMIO_NOTPARAM;
dbBinding.cbMaxLen = 0;
dbBinding.dwFlags = 0;
dbBinding.wType = DBTYPE_IUNKNOWN;
dbBinding.bPrecision = 0;
dbBinding.bScale = 0;
HACCESSOR hAccessor = DB_NULL_HACCESSOR;
DBBINDSTATUS dbs;
hResult = pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 1, &dbBinding, sizeof( BLOBDATA ), &hAccessor, &dbs );
EAutoReleasePtr pIRowset;
hResult = pIRowsetChange->QueryInterface( IID_IRowset, ( void** )&pIRowset );
ULONG uRowsObtained = 0;
HROW* phRows = NULL;
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );
CSequentialStream* pss = new CSequentialStream;
pss->PreAllocBuffer( 1024 ); // 预先分配好内存，并读入数据
pss->Write( pszSomebuffer, 512, NULL ); // pss->Write可以连续调用
pss->Write( pszSomebuffer+512, 512, NULL );
pss->ResetPosition( );
BLOBDATA bd;
bd.pISeqStream = ( ISequentialStream* )pss;
bd.dwStatus = DBSTATUS_S_OK;
bd.dwLength = pss->GetLength( );
// 将 BLOB 数据写入到数据库
hResult = pIRowsetChange->SetData( phRows[0], hAccessor, &bd );
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );
// pss was released by pIRowsetChange->SetData.
这样，我们就完成了一条记录的添加。读取BLOB字段的代码跟上面的完全类似，只要把
hResult = pIRowset->GetNextRows( NULL, 0, 1, &uRowsObtained, &phRows );
后面的那些改成下面的代码即可。
BLOBDATA bd;
hResult = pIRowset->GetData( phRows[0], hAccessor, &bd );
if( bd.dwStatus == DBSTATUS_S_ISNULL )
{
// 此字段为空
}
else if( bd.dwStatus != DBSTATUS_S_OK || bd.pISeqStream == NULL )
{
// 失败
}
else
{
// 从系统分配的 ISequentialStream 接口读入 BLOB 数据
BYTE szReadBuffer[1024];
for( ULONG uRead = 0U; ; )
{
if( FAILED( bd.pISeqStream->Read( szReadBuffer, 1024, &uRead ) ) )
{
break;
}
//szReadBuffer中就包含了BLOB字段的数据
if( uRead != 1024 )
{
break;
}
}
bd.pISeqStream->Release( );
}
pIAccessor->ReleaseAccessor( hAccessor, NULL );
pIRowset->ReleaseRows( uRowsObtained, phRows, NULL, NULL, NULL );
5. OLE DB Templates连接
使用OLE DB接口编程属于最低可能层，代码冗长并且很难维护。因此MS Visual Studio对OLE DB进一步抽象和封装，提供COM OLE DB Templates这个可行的中间层，从而简化了OLE DB应用程序的编写。
OLE DB Templates编写客户数据库程序方法:
<1>以MFC AppWizard为向导建立应用程序框架,添加OLE DB支持的头文件,然后使用OLE DB类进行数据库应用开发。
<2>以ATL COM AppWizard为向导建立应用程序框架,该框架直接支持OLE DB模板类。
OLE DB Templates包括:Consumer Templates和Provider Templates。
(1) Consumer Templates使用者模板
使用者模板(Consumer Templates)体系结构:
(2) Provider Templates服务器模板
服务器模板类体系结构：
6. ADO连接
ADO(ActiveX Data Object,ActiveX数据对象)是MS为最新和最强大的数据访问接口OLE DB而设计,是一个便于使用的应用程序层接口。ADO是一种面向对象的、与语言无关的(Language_Neutral)数据访问应用编程接口。它对 OLE DB API进行封装,实现对数据的高层访问,同时它也提供了多语言的访问技术，此外,由于ADO提供了访问自动化接口,它也支持脚本语言。ADO最主要的优点在于易于使用、速度快、内存支出少和磁盘遗迹小。ADO是用来访问OLE DB的数据库技术。在模型层次上它基于OLE DB,但在应用上又高于OLE DB,因此它简化了对对象模型的操作,并且不依赖于对象之间的相互层次关系。但是OLE的接口可以数据提供程序、服务提供程序和数据使用程序使用，而 ADO所提供的对象只能被数据应用程序使用。并且,ADO对象使用了OLE DB服务提供程序和OLE DB数据提供程序所提供的接口和服务。
(1)ADO访问数据库的结构原理图:
(2)ADO对象模型:
ADO对象模型包括以下关键对象：
<1>Connection对象:在数据库应用里操作数据源都通过该对象,这是数据交换的环境，代表与数据源的一个会话。
<2>Command对象:是一个对数据源执行命令的定义。
<3>Parameter对象:用于制定参数化查询或者存储过程的参数。
<4>Recordset对象:是执行结果集存储到本地的ADO对象。
<5>Field对象:ADO中对列进行操作的对象。
<6>Error对象:对ADO数据操作时发生错误的详细描述。
<7>Property对象:代表一个由提供者定义的ADO对象的动态特征。
ADO对象编程模型:
Parameters
Collection
Execute
Source
Error Collection
(Optional) Active Fields
Connection Collection
(3)ADO编程一般步骤：
<1>创建一个Connection对象。
<2>打开数据源，建立同数据源的连接。
<3>执行一个SQL命令。
<4>使用结果集。
<5>终止连接。
(4)ADO的数据库访问规范
引入ADO支持
#import Program Files\Common Files\System\ado\msado*.dll
初始化和释放ADO环境：
CoInitialize(NULL);
CoUninitialize();
封装的ADO类A set of ADO classes：
http://blog.csdn.net/byxdaz/archive/2008/06/19/2563174.aspx
ADO编程小结：http://hi.baidu.com/sunkanghome/blog/item/273171f9ffb4735c252df286.html
http://hi.baidu.com/sunkanghome/blog/item/cea70101bdb177031d95839a.html
应用实例：
在Visual C++中用ADO进行数据库编程
1. 生成应用程序框架并初始化OLE/COM库环境
创建一个标准的MFC AppWizard(exe)应用程序，然后在使用ADO数据库的InitInstance函数中初始化OLE/COM库（因为ADO库是一个COM DLL库）。
本例为：
BOOL CAdotestDlg::OnInitDialog()
{
::CoInitialize(NULL); //初始化OLE/COM库环境
}
程序最后要调用 ::CoUninitialize()；//释放程序占用的COM 资源。
另外：
m_pRecordset->Close(); 注意！！！不要多次关闭！！！！！！！！！！！！
m_pConnection->Close();
m_pRecordset = NULL;
m_pConnection = NULL;
2. 引入ADO库文件
使用ADO前必须在工程的stdafx.h文件最后用直接引入符号＃import引入ADO库文件，以使编译器能正确编译。代码如下：
#import "C:\Program Files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")
ADO类的定义是作为一种资源存储在ADO DLL(msado15.dll）中，在其内部称为类型库。类型库描述了自治接口，以及C＋＋使用的COM vtable接口。当使用＃import指令时，在运行时Visual C＋＋需要从ADO DLL中读取这个类型库，并以此创建一组C＋＋头文件。这些头文件具有.tli 和.tlh扩展名，读者可以在项目的目录下找到这两个文件。在C＋＋程序代码中调用的ADO类要在这些文件中定义。
程序的第三行指示 ADO对象不使用名称空间。在有些应用程序中，由于应用程序中的对象与ADO中的对象之间可能会出现命名冲突，所以有必要使用名称空间。如果要使用名称空间，则可把第三行程序修改为： rename_namespace("AdoNS")。第四行代码将ADO中的EOF(文件结束)更名为adoEOF，以避免与定义了自己的EOF的其他库冲突。
3．利用智能指针进行数据库操作
在CaboutDlg头文件中定义两个ADO智能指针类实例,并在对话框中加入一个ListCtrl。
class CAdotestDlg : public CDialog
{
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
ClistCtrl m_List;
......
}
ADO库包含三个智能指针:_ConnectionPtr、_CommandPtr和_RecordsetPtr。
_ConnectionPtr通常被用来创建一个数据连接或执行一条不返回任何结果的SQL语句，如一个存储过程。
_CommandPtr返回一个记录集。它提供了一种简单的方法来执行返回记录集的存储过程和SQL语句。在使用_CommandPtr接口时，可以利用全局_ConnectionPtr接口，也可以在_CommandPtr接口里直接使用连接串。_RecordsetPtr是一个记录集对象。与以上两种对象相比，它对记录集提供了更多的控制功能，如记录锁定、游标控制等。
在使用ADO程序的事件响应中OnButton1加入以下代码:
void CAdotestDlg::OnButton1()
{
m_List.ResetContent();
m_pConnection.CreateInstance(_uuidof(Connection)); //初始化Connection指针
m_pRecordset.CreateInstance(_uuidof(Recordset));// 初始化Recordset指针
try
{
m_pConnection->Open("DSN=ADOTest","","",0); //连接叫作ADOTest的ODBC数据源
//注意：这是连接不需要用户ID或密码的open 函数
// 否则形式为 ->Open("DSN=test;uid=sa;pwd=123;","","",0);
// 执行SQL语句得到一个记录集把其指针赋值给m_pRecordset
CString strSql="select * from middle";
BSTR bstrSQL = strSql.AllocSysString();
m_pRecordset->Open(bstrSQL,(IDispatch*)m_pConnection,adOpenDynamic,adLockOptimistic,adCmdText);
//adOpenDynamic：动态 adLockOptimistic乐观封锁法 adCmdText：文本查询语句
while(!m_pRecordset->adoEOF)// 遍历所有记录
{
//取纪录字段值方式之一
_variant_t TheValue; //VARIANT数据类型
TheValue = m_pRecordset->GetCollect("BIG_NAME");//得到字段BIG_NAME的值
if(TheValue.vt!=VT_NULL)
m_List.AddString((char*)_bstr_t(TheValue));
//将该值加入到列表控件中
//取纪录字段值方式之二
// _bstr_t TheValue1=m_pRecordset->Fields->GetItem("BIG_NAME")->Value;
// CString temp=TheValue1.copy();
// m_List.AddString(temp);
//数据类型转换
_variant_t vUsername,vBirthday,vID,vOld;
TRACE("id:%d,姓名:%s,年龄:%d,生日:%s\r\n",
vID.lVal,(LPCTSTR)(_bstr_t)vUsername,vOld.lVal,(LPCTSTR)(_bstr_t)vBirthday);
m_pRecordset->MoveNext();//转到下一条纪录
}
m_pRecordset->Close();
m_pConnection->Close();
}
catch (_com_error e)//异常处理
{
AfxMessageBox(e.ErrorMessage());
}
m_pRecordset->Close(); //注意！！！不要多次关闭！！！！否则会出错
m_pConnection->Close();
m_pRecordset = NULL;
m_pConnection = NULL;
}
程序中通过_variant_t和_bstr_t转换 COM对象和C＋＋类型的数据, _variant_t类封装了OLE自治VARIANT数据类型。在C++中使用_variant_t类要比直接使用VARIANT数据类型容易得多。
好，编译后该程序就能运行了，但记住运行前要创建一个叫ADOTest的ODBC数据源。该程序将把表middle中的BIG_NAME字段值显示在列表控件中。
4．执行SQL命令并取得结果记录集
为了取得结果记录集，我们定义一个指向Recordset对象的指针:_RecordsetPtr m_pRecordset;
并为其创建 Recordset对象的实例: m_pRecordset.CreateInstance("ADODB.Recordset");
SQL命令的执行可以采用多种形式，下面我们一进行阐述。
(1)利用Connection对象的Execute方法执行SQL命令
Execute 方法的原型如下所示:
_RecordsetPtr Connection15::Execute ( _bstr_t CommandText, VARIANT * RecordsAffected, long Options )
其中CommandText是命令字串，通常是SQL命令。
参数RecordsAffected是操作完成后所影响的行数,
参数Options表示CommandText中内容的类型，Options可以取如下值之一：
adCmdText:表明CommandText是文本命令
adCmdTable:表明CommandText是一个表名
adCmdProc:表明CommandText是一个存储过程
adCmdUnknown: 未知
Execute执行完后返回一个指向记录集的指针，下面我们给出具体代码并作说明。
_variant_t RecordsAffected;
///执行SQL命令：CREATE TABLE创建表格users,users包含四个字段:整形ID,字符串username,整形old,日期型birthday
m_pConnection->Execute("CREATE TABLE users(ID INTEGER,username TEXT,old INTEGER,birthday DATETIME)",
&RecordsAffected,
adCmdText);
///往表格里面添加记录
m_pConnection->Execute("INSERT INTO users(ID,username,old,birthday) VALUES (1, 'Washington',25,'1970/1/1')",&RecordsAffected,adCmdText);
///将所有记录old字段的值加一
m_pConnection->Execute("UPDATE users SET old = old+1",&RecordsAffected,adCmdText);
///执行SQL统计命令得到包含记录条数的记录集
m_pRecordset = m_pConnection->Execute("SELECT COUNT(*) FROM users",&RecordsAffected,adCmdText);
_variant_t vIndex = (long)0;
_variant_t vCount = m_pRecordset->GetCollect(vIndex);///取得第一个字段的值放入vCount变量
上两句可以写成— _variant_t vCount = m_pRecordset->GetCollect((_variant_t)((long)0));
m_pRecordset->Close();///关闭记录集
CString message;
message.Format("共有%d条记录",vCount.lVal);
AfxMessageBox(message);///显示当前记录条数
(2)利用Command对象来执行SQL命令
_CommandPtr m_pCommand;
m_pCommand.CreateInstance("ADODB.Command");
_variant_t vNULL;
vNULL.vt = VT_ERROR;
vNULL.scode = DISP_E_PARAMNOTFOUND;///定义为无参数
m_pCommand->ActiveConnection = m_pConnection;///非常关键的一句，将建立的连接赋值给它
m_pCommand->CommandText = "SELECT * FROM users";///命令字串
m_pRecordset = m_pCommand->Execute(&vNULL,&vNULL,adCmdText);///执行命令，取得记录集
在这段代码中我们只是用Command对象来执行了SELECT查询语句，Command对象在进行存储过程的调用中能真正体现它的作用。下次我们将详细介绍。
(3)直接用Recordset对象进行查询取得记录集
实例——
void CGmsaDlg::OnDBSelect()
{
// TODO: Add your control notification handler code here
_RecordsetPtr Rs1; //定义Recordset对象
_bstr_t Connect("DSN=GMS;UID=sa;PWD=;");//定义连接字符串
_bstr_t Source ("SELECT count(*) FROM buaa.mdb010"); //要执行的SQL语句
::CoInitialize(NULL); //初始化Rs1对象
HRESUL hr = Rs1.CreateInstance( __uuidof( Recordset ) );
//省略对返回值hr的判断
Rs1->Open( Source,
Connect,
adOpenForwardOnly,
adLockReadOnly,
-1 );
_variant_t temp=Rs1->GetCollect(_variant_t((long)0));
CString strTemp=(char* )(_bstr_t)temp;
MessageBox("OK!"+strTemp);
}
例如
m_pRecordset->Open("SELECT * FROM users",
_variant_t((IDispatch *)m_pConnection,true),
adOpenStatic,
adLockOptimistic,
adCmdText);
Open 方法的原型是这样的:
HRESULT Recordset15::Open ( const _variant_t & Source,
const _variant_t & ActiveConnection,
enum CursorTypeEnum CursorType,
enum LockTypeEnum LockType,
long Options )
其中：
①Source是数据查询字符串
②ActiveConnection是已经建立好的连接（我们需要用Connection对象指针来构造一个_variant_t对象)
③CursorType光标类型，它可以是以下值之一,请看这个枚举结构:
enum CursorTypeEnum
{
adOpenUnspecified = -1,///不作特别指定
adOpenForwardOnly = 0,///前滚静态光标。这种光标只能向前浏览记录集，比如用MoveNext向前滚动,这种方式可以提高浏览速度。但诸如 BookMark,RecordCount,AbsolutePosition,AbsolutePage都不能使用
adOpenKeyset = 1,///采用这种光标的记录集看不到其它用户的新增、删除操作，但对于更新原有记录的操作对你是可见的。
adOpenDynamic = 2,///动态光标。所有数据库的操作都会立即在各用户记录集上反应出来。
adOpenStatic = 3///静态光标。它为你的记录集产生一个静态备份，但其它用户的新增、删除、更新操作对你的记录集来说是不可见的。
};
④LockType 锁定类型，它可以是以下值之一，请看如下枚举结构：
enum LockTypeEnum
{
adLockUnspecified = -1,///未指定
adLockReadOnly = 1,///只读记录集
adLockPessimistic = 2,悲观锁定方式。数据在更新时锁定其它所有动作，这是最安全的锁定机制
adLockOptimistic = 3,乐观锁定方式。只有在你调用Update方法时才锁定记录。在此之前仍然可以做数据的更新、插入、删除等动作
adLockBatchOptimistic = 4，乐观分批更新。编辑时记录不会锁定，更改、插入及删除是在批处理模式下完成。
};
⑤Options可以取如下值之一：
adCmdText: 表明CommandText是文本命令
adCmdTable:表明CommandText是一个表名
adCmdProc:表明 CommandText是一个存储过程
adCmdUnknown:未知
5. 记录集的遍历、更新
根据我们刚才通过执行SQL命令建立好的users表，它包含四个字段:ID,username,old,birthday
以下的代码实现：打开记录集，遍历所有记录，删除第一条记录，添加三条记录，移动光标到第二条记录，
更改其年龄，保存到数据库。
_variant_t vUsername,vBirthday,vID,vOld;
_RecordsetPtr m_pRecordset;
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT * FROM users",
_variant_t((IDispatch*)m_pConnection,true),
adOpenStatic,
adLockOptimistic,
adCmdText);
while(!m_pRecordset->adoEOF)
{
vID = m_pRecordset->GetCollect(_variant_t((long)0));///取得第1列的值,从0开始计数，
///你也可以直接给出列的名称，如下一行
vUsername = m_pRecordset->GetCollect("username");///取得username字段的值
vOld = m_pRecordset->GetCollect("old");
vBirthday = m_pRecordset->GetCollect("birthday");
///在DEBUG方式下的OUTPUT窗口输出记录集中的记录
if(vID.vt != VT_NULL && vUsername.vt != VT_NULL && vOld.vt != VT_NULL && vBirthday.vt != VT_NULL)
TRACE("id:%d,姓名:%s,年龄:%d,生日:%s\r\n",
vID.lVal,
(LPCTSTR)(_bstr_t)vUsername,
vOld.lVal,
(LPCTSTR)(_bstr_t)vBirthday);
m_pRecordset->MoveNext();///移到下一条记录
}
m_pRecordset->MoveFirst();/// 移到首条记录
m_pRecordset->Delete(adAffectCurrent);///删除当前记录
///添加三条新记录并赋值
for(int i=0;i<3;i++)
{
m_pRecordset->AddNew();///添加新记录
m_pRecordset->PutCollect("ID",_variant_t((long)(i+10)));
m_pRecordset->PutCollect("username",_variant_t("叶利钦"));
m_pRecordset->PutCollect("old",_variant_t((long)71));
m_pRecordset->PutCollect("birthday",_variant_t("1930-3-15"));
}
m_pRecordset->Move(1,_variant_t((long)adBookmarkFirst));/// 从第一条记录往下移动一条记录,即移动到第二条记录处
m_pRecordset->PutCollect(_variant_t("old"),_variant_t((long)45));/// 修改其年龄
m_pRecordset->Update();///保存到库中
备注：多次查询可把查询过程做成一个函数ExecuteSQL让m_pRecordset获得连接指针m_pConnection查询结果
void ExecuteSQL(_ConnectionPtr m_pConnection, _RecordsetPtr m_pRecordset,CString strSql)
{
//执行Select 语句
BSTR bstrSQL = strSql.AllocSysString();
try
{
m_pRecordset->Open(bstrSQL,(IDispatch*)m_pConnection,adOpenDynamic,adLockOptimistic,adCmdText);
//adOpenDynamic：动态 adLockOptimistic乐观封锁法 adCmdText：文本查询语句
}
catch(_com_error error)
{
CString errorMessage;
errorMessage.Format("%s",(LPTSTR)error.Description());
AfxMessageBox(errorMessage);
}
}
//出错处理：
3127 ——没有找到目标表
3092——目标表已经存在
例如：
catch(const _com_error e)
{
AfxMessageBox(e.Description());
long errorCode=e.WCode();
if(3127==errorCode) AfxMessageBox("表不存在");
if(3092==errorCode) AfxMessageBox("表已经存在");
return FALSE;
}
7、ADO.NET
ADO.NET是一组用于和数据源进行交互的面向对象类库。
ADO.NET的主要对象有哪些？
Connection :用于连接到数据库和管理对数据库的事务；
Command :用于对数据库发出SQL命令；
DataReader :用于从数据源读取只进数据记录流；
DataSet :用于对单层数据、XML数据和关系数据进行存储、远程处理和编程；
DataAdapter :用于将数据推入DataSet，并使数据与数据库保持一致；
ADO.NET 2.0 快速入门：
http://tech.e800.com.cn/articles/2009/721/1248143977078_1.html
用VC轻松实现 ADO.net：
http://www.vckbase.com/document/viewdoc/?id=1714
<2.>Oracle专用方法
1. OCI(Oracle Call Interface)访问
OCI(Oracle Call Interface)是由Oracle提供的一系列用于访问Oracles数据库服 务器的标准接口，它可以使用户将Oracle调用直接嵌入到高级语言中。
使用OCI应用程序访问数据库原理：
在高级语言中使用OCI编程的原理图:
用OCI开发Oracle客户端软件的一般流程:
<1>初始化OCI编程环境
<2>分配必要的句柄,建立服务器连接和一个用户会话
<3>向服务器发出请求，进行必要的数据处理
<4>释放不再需要的语句和句柄
<5>终止会话和连接
2. Oracle Object OLE C++ Class Library
这个类库是一个提供编程接口访问Oracle对象服务器的C++类库，它是用OLE的方式实现的。Oracle提供的是一个进程内服务器，也就是服务器将与应用程序在同一个地址空间内, 它以DLL方式提供。应用程序在访问数据库之前必须先加载Oracle对象服务器(OStatup方法),然后与Oracle对象服务器通信，Oracle对象服务器其实是一些组件，它通过Oracle的OCI访问数据库。
Oracle对象服务器其实是一些COM组件，它通过Oracle的OCI访问数据库。
运用Oracle Objects for OLE C++ Class Library开发的步骤：
1>通过调用OStatup方法初始化类库。
2>连接数据库。
3>操纵数据库 断开数据库(类库自动为你自动执行)
4>通过调用OShutdown方法卸载类库。
<3>使用OTL进行数据库编程
OTL 是 Oracle, Odbc and DB2-CLI Template Library 的缩写，是一个C++编译中操控关系数据库的模板库，它目前几乎支持所有的当前各种主流数据库，例如Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行，进行操作DB2数据库则是通过CLI接口来进行，至于MS的数据库和其它一些数据库，则OTL只提供了ODBC来操作的方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式来进行操纵。
在MS Windows and Unix 平台下，OTL目前支持的数据库版本主要有：Oracle 7 (直接使用 OCI7), Oracle 8 (直接使用 OCI8), Oracle 8i (直接使用OCI8i), Oracle 9i (直接使用OCI9i), Oracle 10g (直接使用OCI10g), DB2 (直接使用DB2 CLI), ODBC 3.x ,ODBC 2.5。OTL最新版本为4.0，参见http://otl.sourceforge.net/，下载地址http://otl.sourceforge.net/otlv4_h.zip。
优点：
a. 跨平台
b. 运行效率高，与C语言直接调用API相当
c. 开发效率高，起码比ADO.net使用起来更简单，更简洁
d. 部署容易，不需要ADO组件，不需要.net framework 等
缺点：
a. 说明文档以及范例不足够丰富（暂时性的）
其实现在它提供有377个使用范例可参考，下载地址：http://otl.sourceforge.net/otl4_examples.zip。
建立数据源
1．依次点击“开始->控制面板”，打开“控制面板”界面，双击“管理工具”，然后再双击“数据源(ODBC)”，就打开了“ODBC数据源管理器”，选择“系统DSN”。
2．单击“添加”，弹出“创建新数据源”对话框，选择“Microsoft Access Driver(*.mdb)”。
3．点击“完成”，弹出“ODBC Microsoft Access安装”对话框，单击“创建”，开始创建数据库，弹出“新建数据库”对话框，添加数据库名称my_db和选择数据库存放目录，单击“确定”，创建完成，然后添加数据源名：my_db。点击“确定”。
4．然后在系统数据源中就有我们刚才添加的数据源。
5．单击“确定”，完成数据源的创建。
OTL编程
下面我们用一个实例来说明：
1． 创建数据表：TestTable ( ColumA int , ColumB varchar(50),ColumC varchar(50) )
2． 插入100条数据，ColumA 为数据的 id 范围：0-99 ， ColumB=”Test Data %d” , 其中 %d=id 。
3． 删除表中ColumA 中小于10和大于90的数据。
4． 将ColumA为3的倍数的记录中ColumC更新为ColumB的内容。
具体代码为：
#include
using namespace std;
#include
#include
#include
#define OTL_ODBC // 编译 OTL 4.0/ODBC
// #define OTL_ODBC_UNIX // 如果在Unix下使用UnixODBC，则需要这个宏
#include "otlv4.h" // 包含 OTL 4.0 头文件
otl_connect db; // 连接对象
//此函数完成插入100条数据，ComulA为数据的id，范围为0-99，
//ColumB="Test Data %d",其中%d=id
void insert()
// 向表中插入行
{
// 打开一个通用的流，以模板的方式向表中插入多项数据
otl_stream
o(1, // 流的缓冲值必须设置为1
"insert into TestTable values(:f1,:f2,:f3)",
// SQL 语句
db // 连接对象
);
char tmp1[32];
char tmp2[30];
for(int i=0;i<100;++i){
sprintf(tmp1,"Test Data %d",i);
sprintf(tmp2,"");
o<90");
// rpc是作用效果的返回值，otl_cursor::direct_exec为直接执行sql语句
cout<<"Rows deleted: "< "
" WHERE ColumA=:f1",
// UPDATE 语句
db // 连接对象
);
otl_stream c(1,"select ColumB from TestTable where ColumA=:f3",db);
char temp[10];
for(int i=10;i<91;i++)
{
if(i%3==0)
{
c << i;
c >> temp;
o << temp << i;
}
}
}
int main()
{
otl_connect::otl_initialize(); // 初始化 ODBC 环境
try{
db.rlogon("UID=scott;PWD=tiger;DSN=my_db"); // 连接到 ODBC
//或者使用下面的连接语句方式。
// db.rlogon("scott/tiger@firebird"); // connect to ODBC, alternative format
// of connect string
otl_cursor::direct_exec
(
db,
"drop table TestTable",
otl_exception::disabled // disable OTL exceptions
); // drop table
//这里完成表的创建
otl_cursor::direct_exec
(
db,
"create table TestTable(ColumA int, ColumB varchar(50),ColumC varchar(50))"
); // create table
insert(); // insert records into the table
// update(10); // update records in the table
delete_rows();
update();
}
catch(otl_exception& p){ // intercept OTL exceptions
cerr< ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引；②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。
◆2、IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
◆3、IN和EXISTS
EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
◆4、在海量查询时尽量少用格式转换。
◆5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。
◆6、ORDER BY和GROPU BY
使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。
◆7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
◆8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。
◆9、SET SHOWPLAN_ALL ON 查看执行方案。DBCC检查数据库数据完整性。
DBCC(DataBase Consistency Checker)是一组用于验证 SQL Server 数据库完整性的程序。
◆10、慎用游标
在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。
一些常用的SQL语句供大家参考，希望对大家有所帮助。
说明：存储过程的使用,CREATE PROC 创建存储过程,SQL2000中用sp_xxx和xp_xxx存储过程;一般来说,sp_xxx是一般的存储过程,而xp_xxx是扩展的存储过程。使用这些系统存储过程时，一般使用USE MASTER然后在使用sp_xxx或者xp_xxx。
说明：复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND B
WHERE A.NUM = B.NUM
说明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
说明：
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明：四表联查问题：
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
说明：得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
三、数据库优化
1、索引问题
在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多，这时缺少索引，对性能的影响便会越来越大了。
这个问题需要数据库设计人员和开发人员共同关注
法则：不要在建立的索引的数据列上进行下列操作:
◆避免对索引字段进行计算操作
◆避免在索引字段上使用not，<>，!=
◆避免在索引列上使用IS NULL和IS NOT NULL
◆避免在索引列上出现数据类型转换
◆避免在索引字段上使用函数
◆避免建立索引的列中使用空值。
2、在可以使用UNION ALL的语句里，使用了UNION
UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）
3、对Where 语句的法则
3.1 避免在WHERE子句中使用in，not in，or 或者having。
可以使用 exist 和not exist代替 in和not in。
可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理。
例子
SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN
(SELECT CUSTOMER_NAME FROM CUSTOMER)
优化
SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist
(SELECT CUSTOMER_NAME FROM CUSTOMER)
3.2 不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。
例子使用：
SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’
4、对Select语句的法则
在应用程序、包和过程中限制使用select * from table这种方式。看下面例子
使用SELECT empno,ename,category FROM emp WHERE empno = '7369‘
而不要使用SELECT * FROM emp WHERE empno = '7369'
5、排序
避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。
优化SQL Server数据库方法：
查询速度慢的原因很多，常见如下几种：
1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)
2、I/O吞吐量小，形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）
7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好，没有优化
可以通过如下方法来优化查询 :
1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.
2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)
3、升级硬件
4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。
5、提高网速;
6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。
7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。
8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。
9、DB Server 和APPLication Server 分离；OLTP和OLAP分离
10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件'分区视图'）
a、在实现分区视图之前，必须先水平分区表
b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。
11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的：
1、 查询语句的词法、语法检查
2、 将语句提交给DBMS的查询优化器
3、 优化器做代数优化和存取路径的优化
4、 由预编译模块生成查询规划
5、 然后在合适的时间提交给系统处理执行
6、 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。
12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。
14、SQL的注释申明对执行没有任何影响
15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 Select 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 Select 语句中的锁提示。锁提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新 NOLOCK 未锁定 未锁定未锁定 未锁定 HOLDLOCK 共享 共享 共享 更新 UPDLOCK 错误 更新 更新 更新 TABLOCKX 错误 未锁定 未锁定更新其它 未锁定 未锁定 未锁定 更新 *指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。
16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引
17、注意UNion和UNion all 的区别。UNION all好
18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的
19、查询时不要返回不需要的行、列
20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间
21、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行
22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，因为他们不走索引全是表扫描。也不要在Where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：Where SUBSTRING(firstname,1,1) = 'm'改为Where firstname like 'm%'（索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能优化她，而"<>"等还是不能优化，用不到索引。
23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。
24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')
25、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。
26、MIN() 和 MAX()能使用到合适的索引。
27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers, Constraint（约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。
28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。
29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。
30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。
31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。
32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。
select top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',
'JCNAD00333138','JCNAD00303570','JCNAD00303569',
'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933',
'JCNAD00254567','JCNAD00254585','JCNAD00254608',
'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',
'JCNAD00279196','JCNAD00268613') order by postdate desc
35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
36、当用Select INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中Select * from sysobjects可以看到 Select INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快
38、一次更新多条记录比分多次更新每次一条快,就是说批处理好
39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好
40、在SQL2000下，计算字段是可以索引的，需要满足的条件如下：
a、计算字段的表达是确定的
b、不能用在TEXT,Ntext，Image数据类型
c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….
41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程
42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快
43、Select COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！
44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。
45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现
46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。
Process:
1、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。
2、%Processor Time计数器 如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。
3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。
4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。
47、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。
48、查询的关联同写的顺序
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '号码'）
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '号码'， A = '号码'）
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码'， A = '号码'）
49、
(1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名 FROM P2000 Where 负责人代码>=:code1 AND负责人代码 <=:code2
(2)IF 没有输入负责人代码 THEN Select 负责人名 FROM P2000 ELSE code= 负责人代码 Select 负责人代码 FROM P2000 Where 负责人代码=:code END IF 第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件; 在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦
50、关于JOBCN现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。
begin
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by ReferenceID
select * from @local_variable where Fid > 40 and fid <= 60
end 和
begin
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from @local_variable where Fid > 40 and fid <= 60
end 的不同
begin
create table #temp (FID int identity(1,1),ReferenceID varchar(20))
insert into #temp (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from #temp where Fid > 40 and fid <= 60 drop table #temp
end
评论