数据库及oledb类别知识汇总.docx

数据库及oledb类别知识汇总.docx

《数据库及oledb类别知识汇总.docx》由会员分享，可在线阅读，更多相关《数据库及oledb类别知识汇总.docx（35页珍藏版）》请在冰豆网上搜索。

数据库及oledb类别知识汇总

数据库及OLEDB类别知识汇总

一、_variant_t

_variant_t变体的成员中：

bVal并不是bool型的，而是一个无符号的整型。

通过SQLServer返回值时，如果是bit型的1，bVal会返回255。

boolVal也不是bool型的，而是short型的。

二、ADODataSetBackup

ADODataSetBackup.close;

ADODataSetBmandtext='Selectfromtablename';

ADODataSetBackup.open;

...

备份

ADODataSetBackup.savetofile（'备份文件名'）;

...

恢复

ADODataSetRestore.loadfromfile（'备份文件名'）;

ADODataSetRestore.post;

三、char和varchar的区别

CHAR的长度是固定的，VARCHAR2的长度是可以变化的。

比如，存储字符串“abc"，对于CHAR（10），存储的字符将占10个字节（包括7个空字符），VARCHAR2（10）只占用3个字节的长度，10只是最大值，当存储的字符小于10时，按实际长度存储。

CHAR的效率比VARCHAR2的效率稍高。

目前VARCHAR是VARCHAR2的同义词。

工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。

Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。

如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

CHAR与VARCHAR2是一对矛盾的统一体，两者是互补的关系。

VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的“以空间换效率”。

VARCHAR2虽然比CHAR节省空间，但如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”（RowMigration）现象，造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

四、表结构里的默认值的设定

正数：

（默认值）

负数：

（（默认值））

字符串：

（'默认值'）

五、二进制字符串的提取

当必须从二进制的字符串、表列中的字符串值或是字符串表达式中提取部分字符串时，有以下两种办法：

一、如果提取的是以从起始字节开始的子串，那么可以利用varbinary变量来截取。

二、如果提取的是中间的某一部分，则需要用SubString函数。

该函数语法如下：

SubString（,,）

start并不是指的起始字符，而是指的起始位置。

即selectSubString（'UserDefData','7','6'）是指从UserDefData中取出第七个字符开始的6个字符组成的字串（7~12）

如果不知道字符串的实际长度，只要指定让SubString函数返回的最大数目的字符号串。

当SubString函数发现从（起点）到字符串尾部的长度比个字符串短时，就返回从位置到结尾时的字符串（不报告错误）。

由此，可以通过同一语句来获取诸如Pcm360、Pcm360DB、Pcm360DB_1这三个结果。

如果是NULL，SubString函数将返回NULL值。

类似的，如果指定的比的长度还大，某些DBMS产品将返回NULL结果。

另一些，例如MS-SQLServer将返回空的字符串（长度为0）。

六、经典SQL语句集锦

SQL分类：

DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）

DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）

DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）

--备份库

USEmaster

execsp_addumpdevice'disk','testBack','C:

\...'

BACKUPDATABASEpubsTOtestBack

--创建表

CREATETABLEtab_newliketab_old

CREATETABLEtab_newAS

SELECTColumn1,Column2…FROMtab_oldDEFINITIONONLY

--修改表

ALTERTABLETableNameADDCOLUMNColumnNameElemTypeAllowNull

ALERTTABLETableNameADDPRIMARYKEY（ColumnName）

ALERTTABLETableNameDROPPRIMARYKEY（ColumnName）

注1：

所有DBMS中，列增加后将不能删除。

注2：

DB2中列增加后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

--创建索引

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXIndexName

ONTableName（Column1,Column2…）

注：

索引是不可更改的，想更改必须删除重新建。

--创建和删除视图

CREATEVIEWViewNameASSELECT…

DROPVIEWViewName

UNION运算符：

UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。

当ALL随UNION一起使用时，不消除重复行。

两种情况下派生表的每一行不是来自TABLE1就是来自TABLE2。

EXCEPT运算符：

EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。

当ALL随EXCEPT一起使用时（EXCEPTALL），不消除重复行。

INTERSECT运算符：

INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。

当ALL随INTERSECT一起使用时（INTERSECTALL），不消除重复行。

注：

使用运算符的几个查询结果行必须是一致的。

LEFTOUTERJOIN：

结果集既包括连接表的匹配行，也包括左连接表的所有行。

RIGHTOUTERJOIN：

结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

FULLOUTERJOIN：

不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

复制表（只复制结构。

源表名：

a，新表名：

b）（Access可以用）

法一：

SELECT*INTObFROMaWHERE1<>1

法二：

SELECTTOP0*INTObFROMa

拷贝表（拷贝数据。

源表名：

a，目标表名：

b）（Access可以用）

INSERTINTOb（a,b,c）SELECTd,e,fFROMb

跨数据库之间表的拷贝（数据使用绝对路径）（Access可以用）

INSERTINTOb（a,b,c）

SELECTd,e,fFROMbIN'"&Server.MapPath（"."）&"\data.mdb"&"'WHERE…

子查询（表名1：

a表名2：

b）

SELECTa,b,cFROMaWHEREaIN（SELECTdFROMb）

SELECTa,b,cFROMaWHEREaIN（1,2,3）

外连接查询（表名1：

a表名2：

b）

SELECTa.a,a.b,a.c,b.c,b.d,b.fFROMaLEFTOUTJOINbONa.a=b.c

在线视图查询（表名1：

a）

SELECT*FROM（SELECTa,b,cFROMa）TWHEREt.a>1

BETWEEN限制查询数据范围时包括了边界值，NOTBETWEEN不包括边界值。

SELECT*FROMtable1WHEREtimeBETWEENtime1ANDtime2

SELECTa,b,cFROMtable1WHEREaNOTBETWEENa1ANDa2

Conn.Execute说明

Execute方法

该方法用于执行SQL语句。

根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：

1．执行SQL查询语句时，将返回查询得到的记录集。

用法为：

Set对象变量名=连接对象.Execute（"SQL查询语言"）

Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。

2．执行SQL的操作性语言时，没有记录集的返回。

此时用法为：

连接对象.Execute"SQL操作性语句"[,RecordAffected][,Option]

RecordAffected为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。

通过访问该变量，就可知道SQL语句队多少条记录进行了操作。

Option可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。

通过指定该参数，可使执行更高效。

BeginTrans、RollbackTrans、CommitTrans方法

这三个方法是连接对象提供的用于事务处理的方法。

BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。

事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。

BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。

判断事务处理是否成功，可通过连接对象的Error集合来实现，若Error集合的成员个数不为0，则说明有错误发生，事务处理失败。

Error集合中的每一个Error对象，代表一个错误信息。

七、哪种命令可以用来压缩数据库容量

数据库在使用一段时间后，时常会出现因数据删除而造成数据库中空闲空间太多的情况，这时就需要减少分配给数据库文件和事务日志文件的磁盘空间，以免浪费磁盘空间。

当数据库中没有数据时，可以修改数据库文件属性直接改变其占用空间，但当数据库中有数据时，这样做会破坏数据库中的数据，因此需要使用压缩的方式来缩减数据库空间。

可以在数据库属性选项中选择“Autoshrink”选项，让系统自动压缩数据库，也可以用人工的方法来压缩。

人工压缩数据库可以用EnterpriseManager压缩数据库，也可以用Transact-SQL命令压缩数据库：

DBCCSHRINKDATABASE：

对数据库进行压缩。

命令语法如下：

DBCCSHRINKDATABASE（

database_name[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}]）

各参数说明如下：

target_percent：

指定将数据库压缩后，未使用的空间占数据库大小的百分之几。

如果指定的百分比过大，超过了压缩前未使用空间所占的比例，则数据库不会被压缩。

并且压缩后的数据库不能比数据库初始设定的容量小。

NOTRUECATE：

将数据库缩减后剩余的空间保留在数据库，中不返还给操作系统。

如果不选择此选项，则剩余的空间返还给操作系统。

TRUNCATEONLY：

将数据库缩减后剩余的空间返还给操作系统。

使用此命令时SQLServer将文件缩减到最后一个文件分配，区域但不移动任何数据文件。

选择此项后，target_percent选项就无效了。

DBCCSHRINKDATABASE（mytest,20）：

数据库mytest的未使用空间为数据库大小的20%。

运行结果如下：

DBCCexecutioncompleted.IfDBCCprintederrormessages,contactyoursystemadministrator.

DBCCSHRINKFILE：

对数据库中指定的文件进行压缩。

其语法如下：

DBCCSHRINKFILE（

{file_name|file_id}

{[,target_size]|[,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]}）

各参数说明如下：

file_id指定要压缩的文件的鉴别号（Identificationnumber，即ID）。

文件的ID号可以通过FILE_ID（）函数或如本章前面所讲述的Sp_helpdb系统存储过程来得到。

target_size指定文件压缩后的大小。

以MB为单位。

如果不指定此选项，SQLServer就会尽最大可能地缩减文件。

EMPTYFILE指明此文件不再使用，将移动所有在此文件中的数据到同一文件组中的其它文件中去。

执行带此参数的命令后，此文件就可以用ALTERDATABASE命令来删除了。

其余参数NOTRUNCATE和TRUNCATEONLY与DBCCSHRINKDATABASE命令中的含义相同。

例:

压缩数据库mydb中的数据库文件mydb_data2的大小到1MB。

USEmydb

DBCCSHRINKFILE（mydb_data2,1）

八、什么是聚集索引，什么是非聚集索引，什么又是主键？

使用聚集索引：

聚集索引确定表中数据的物理顺序。

聚集索引类似于电话簿，后者按姓氏排列数据。

由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。

但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。

使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。

这样有助于提高此类查询的性能。

同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。

例如，使用唯一雇员ID列emp_id查找特定雇员的最快速的方法，是在emp_id列上创建聚集索引或PRIMARYKEY约束。

如果该表上尚未创建聚集索引，且在创建PRIMARYKEY约束时未指定非聚集索引，PRIMARYKEY约束会自动创建聚集索引。

也可以在lname（姓氏）列和fname（名字）列上创建聚集索引，因为雇员记录常常是按姓名而不是按雇员ID分组和查询的。

使用非聚集索引：

非聚集索引与课本中的索引类似。

数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。

如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

与使用书中索引的方式相似，Microsoft®SQLServer™2000在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。

这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。

如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行ID（RID）。

例如，对于在emp_id列上有非聚集索引的表，如要搜索其雇员ID（emp_id），SQLServer会在索引中查找这样一个条目，该条目精确列出匹配的emp_id列在表中的页和行，然后直接转到该页该行。

多个非聚集索引：

有些书籍包含多个索引。

例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

对于非聚集索引也是如此。

可以为在表中查找数据时常用的每个列创建一个非聚集索引。

注意事项：

在创建非聚集索引之前，应先了解您的数据是如何被访问的。

可考虑将非聚集索引用于：

✓包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。

如果只有很少的非重复值，如只有1和0，则大多数查询将不使用索引，因为此时表扫描通常更有效。

✓不返回大型结果集的查询。

✓返回精确匹配的查询的搜索条件（WHERE子句）中经常使用的列。

✓经常需要联接和分组的决策支持系统应用程序。

应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。

✓在特定的查询中覆盖一个表中的所有列。

这将完全消除对表或聚集索引的访问。

PRIMARYKEY约束：

表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。

这样的一列或多列称为表的主键，通过它可强制表的实体完整性。

当创建或更改表时可通过定义PRIMARYKEY约束来创建主键。

一个表只能有一个PRIMARYKEY约束，而且PRIMARYKEY约束中的列不能接受空值。

由于PRIMARYKEY约束确保唯一数据，所以经常用来定义标识列。

当为表指定PRIMARYKEY约束时，Microsoft®SQLServer™2000通过为主键列创建唯一索引强制数据的唯一性。

当在查询中使用主键时，该索引还可用来对数据进行快速访问。

如果PRIMARYKEY约束定义在不止一列上，则一列中的值可以重复，但PRIMARYKEY约束定义中的所有列的组合的值必须唯一。

如下图所示，titleauthor表中的au_id和title_id列组成该表的组合PRIMARYKEY约束，以确保au_id和title_id的组合唯一。

什么是聚集索引和非聚集索引

SQLSERVER提供了两种索引：

聚集索引（clusteredindex，也称聚类索引、簇集索引）和非聚集索引（nonclusteredindex，也称非聚类索引、非簇集索引）。

其实，我们的汉语字典的正文本身就是一个聚集索引。

比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。

如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。

也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。

我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果您认识某个字，您可以快速地从自动中查到这个字。

但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。

但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。

很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。

我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。

进一步引申一下，我们可以很容易的理解：

每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

动作描述

使用聚集索引

使用非聚集索引

列经常被分组排序

应

应

返回某范围内的数据

应

不应

一个或极少不同值

不应

不应

小数目的不同值

应

不应

大数目的不同值

不应

应

频繁更新的列

不应

应

外键列

应

应

主键列

应

应

频繁修改索引列

不应

应

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。

如：

返回某范围内的数据一项。

比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

索引是如何工作的？

改善SQL语句

很多人不知道SQL语句在SQLSERVER中是如何执行的，他们担心自己所写的SQL语句会被SQLSERVER误解。

比如：

执行SELECT*FROMTable1WHEREName=‘ZhangSan’ANDtID>10000

和执行SELECT*FROMTable1WHEREtID>10000ANDName=‘ZhangSan’

一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个Name=‘ZhangSan’的，而后再根据限制条件条件tID>10000来提出查询结果。

事实上，这样的担心是不必要的。

SQLSERVER中有一个“查询分析优化器”，它可以计算出WHERE子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。

虽然查询优化器可以根据WHERE子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。

在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。

如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。

SARG的定义：

用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或