SQL语言

组成数据库的对像!
A:表(table)具体组织和存储数据的对像,由列和行组成.
1:行和列顺序可以是任意的.在同一个表中列名必须是唯一的.
2:在同一个数据库里面表名也必须要是唯一.

B:主键(primary key)
1:表中一列或多列的组合,其值唯一确定表的一行.
2:主键不能重复
3:作为主键各列都不能为空.

C:外键(foreign key)
表中一列或多列的组合,它本身不是本表的主键,而是另外一个表的主键.[一对多关系]  
外键是两个表发生关系一个纽带.

D:视图(view)从一个或几个基本表中根据用户需要而做成一个虚表.
1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据
2:视图只在刚刚打开的一瞬间,通过定义从表中搜集数据,并展现给用户

E:约束(constraint)
对表中各列的取值范围进行限制,以确保表中数据的合理有效.

F:默认值(default)
在向表中插入数据时,为没有指定数据的列提供一个默认的数据

G:事务(constran)
1:在关系型数据库里面一般使用“事务“来处理用户提交的修改表和数据的操作
2:事务就是一次性完成的一组数据的操作.
3:事务的持征:位于事务中的操作要么全部都做,要么全部取消,从而维护了数据库中数据的合理性.
4:关系数据库中一般按照先写日志的方法进行事务操作.即:在实施事务之前先将要进行操作的记录写入日志,再进行实际的数据修改.
5:是记录关系数据修改的情况的笔记.
6:在用户对数据库实施之前,sql会把两种形式的情况写入日志.  
   1:实施事务之间的数据状态.
   2:实施事务之后的数据状态.
当事务由于中途被破坏时,sqlserver会利用日志记录把数据恢复成实施事务之前的数据状态,即撤消事务(回滚);
如果事务顺利的执行则把数据恢复成实施事务之后的状态,即执行事务(前滚).

H:规则(rule)同约束一样,对特定的列进行限制:单价*数量=总额
  
I:索引(index)提供一种无需扫描整张表就能实现对数据快速访问的途径.可以使用索引来优化查询.[用索引可以实现排序]

J:存储过程(store procedure)
   一组经过编译好的可以重复使用的sql代码.存储过程是在服务器中执行,用户可以调用存储过程并接收存储过程返回的结果.
   1:由于存储过程是存储在服务器,所有可以让每一个客户重复使用,减少sql代码书写量.
   2:由于存储过程是事先编译好的sql代码,所以在执行时,并不做语法检验,可以大大提高sql语句的执行效率.

K:触发器(trigger)
  一种特殊的存储过程.
  1:触发器与表相关.
  2:当用户对指定的表进行某种操作时,触发器会自动执行,去做它里面sql代码

L:数据类型(data type)
  两种类型:系统数据类型,用户自定义数据类型.
  系统数据类型:int,float,char,varchar,text[备注,工作简历],image[存储图像]

M:日志(log)
  sql server使用日志记载用户对数据库进行的一系列的操作.日志是维护数据库完整性的重要工具.sql server遵守先写日志再对数据库进行操作.所以某一天,如果数据库损坏,但可以通过日志进行恢复.
SQL系统组成
1:sql server系统数据库.
  A:master:最重要的一个数据库.保存了:
     服务器的用户名密码.
     服务器的配置信息
     使用的数据类型
     错误信息
     系统或者用户建立的所有对像
   说明:由于master数据库比较重要所以不要在里面建表,不要修改里面的任何内容.  
  
  B:tempdb:临时数据库
    1:记录所有的临时表格或者数据.
    2:tempdb是一个全局资源,任何连接到sql server都可以使用.
    3:该数据库所放有的数据全部是临时,这就是说当sql server服务停止之后,所有数据将会消失

  C:model数据库:模板数据库.
    1:是建立新的数据库一个样式.
    2:当用户建立一个数据库的时候,首先要从model数据库里面复制所有的表.

  D:msdb数据库:  
    主要被sql server agent服务用于操作作业调度以及管理报警.
  
2:系统表
  A:服务器级系统表:[master数据库里面]
     1:sysdatabases:记录所有的数据库的相关信息
     2:sysmessages:记录了系统错误和警告信息
     3:syslangues:记录了服务器能够识别的语言
     4:sysconfigures:记录了服务器配置情况
     5:systype:记录了服务器所有数据类型

  B:数据库级系统表:[保存在不同的数据库里面]
     1:sysbojects:所有数据库的对像的相关资料.[类型:u:表,V:视图,P:存储过程,tr:触发器]
     2:sysusers:所有数据库的用户的信息.
     3:syscolumns:所有数据库表中的列.

3:系统的存储过程:  
  1:sp_help 表名:查看表的信息:
  2:sp_renamedb 旧名字,新名字:把数据库更名.

4:示列数据库:
    pubs,northwind.


创建和维护数据库(上)
1:数据库的逻辑结构:
2:数据库的物理结构:
3:如何创建数据库:
4:如何删除数据库:


A:数据库的逻辑结构:
通常我们在企业管理器中看到的数据库和里面的对像都只是数据库的逻辑结构.也就是它们并不像在企业管理器中所看到的那样的结构去存储在计算机中.企业管理器只是为了方便用户查看数据和操作数据而在前台表象上作了一个“虚假”的显示结构即为数据库的逻辑结构.而数据库中真正的数据是存储在硬盘中.而且是以文件的形式来保存的.于是有了数据库的物理结构.

B:数据库的物理结构:
既然数据库里面的所有数据是以文件的形式存储在硬盘中,那么究竟一个数据库库由那样文件组成呢?组成一个数据库的文件由以下三种文件组成:
1:主要数据文件:
  作用:主要数据文件包含数据库的初始信息.记录数据库还拥有那些文件,并且用来存储数据.如用户建的表等数据.
  特点:每一个数据库有且只有一个主要数据文件.扩展名为 mdf
2:次要数据文件:
  作用:次要文件也是用来存储数据库中的各类信息的,用户可以选择是否需要次要数据文件.也就是说它是可选的,它的扩展名为 ndf
  为什么要引入次要数据文件:
    1:或许你的主要文件的数据非常庞大,以至于超过所在的硬盘分区.如果没有次要数据文件帮助则用户添加的数据将不能加入到数据库里面去.
    2:可以为一个数据库创建多个次要文件,并把这些文件放到不同的硬盘中,既可将一个数据库中的数据存储在多个不同的物理硬盘中.[可以提高查询效率:以下再作解释]
    3:日志文件:我们知道由于关系型数据库遵循“先写日志再操作”的原则,所以必须有一个文件来存储用户对该数据库所作的一切操作的笔记的文件,这就是日志文件.扩展名为:ldf.说明:一个数据库必须至少有一个日志文件,也可以有多个日志文件.

所有数据库基本上由这个三类文件组成.另外补充一点:当你创建数据库并指定一个数据文件或日志文件时,系统会让你输入两种表示形式的文件名:一个是不带路径的文件名,一个是要带路径的文件.它们分别是:逻辑文件名,物理文件名
  1:逻辑文件名:用于在sql语句命令中对该文件进行访问,即不需要指明路径.
  2:物理文件名:是文件实际存储在磁盘上的文件名,必须包括路径,指明该文件在磁盘中的什么位置.
总结:逻辑文件名与物理文件名就像一个房主与房子地址一样,我们用房子的主人就可以代表房子本身即:房子的地址.

文件组
文件组:如果一个数据库有很多文件就不便于管理了,为了管理和分配的目的,sql server允许你将多个文件归纳为同一个组.这样文件组中的文件就和其它文件组中的文件没有直接关系.这就是文件组.
说明:
   1:一个数据库里面必须有且仅有一个主文件组为:primary filegroup.用户可以根据需要来定义其它文件组.
   2:一个文件只能属于一个文件组,它不能同时属于两个文件组.
   3:文件组只能够管理数据文件,也就是日志文件不能放入文件组,因为日志文件是分开管理的
   4:所有建的次要数据文件如果不特别指定组则自动被放入primary组.

sql server对文件组中的每个文件的填充策略:
如果用户有数据要写入文件组时(比如用户建一个表,并指定它属于的文件组时,之后用户对该表加入记录)sql server会采用“按比例填充”的原则:即以属于该组每个文件的可用空间比例来将数据分割并分别写入每一个文件中.而不是先写入第一个文件,第一个文件写满之后再写第二文件.但是sql server对日志文件的数据分配是采用“写满为止“的策略.

为什么要引入文件组:
理由:
1)用户可以在一个数据库里面建多个数据文件并指定它们的“物理名”为不同的磁盘,然后再把它们放入一个组中.这样当用户在这个组中创建一个表时这个表中的数据将按“比例填充”方式放入该组中所有的数据文件中,这样就可以实现把一个表中的数据放入多个磁盘,即可以实现数据库的“广域”存储.此时如果要对该表进行查询,则多个磁盘的读写磁头将能够同时并行的访问数据,从而加快数据库的操作.
2)通过为某些数据容量很大且经常需要使用的表直接存储在一个特定的高速的磁盘上,而把一些不经常使用且容量较小的表存储另一个磁盘上,就能提供工作效率.

怎样使用文件组:
  1:大多数的数据库只需要一个主要数据文件和一个日志文件就中以非常好的运行.
  2:如果在创建数据库之前估计某个对像(如表)以后会占用大量数据则在数据库创建时,在多个磁盘分别建立一个数据文件,然后把它们设为属于同一个组.然后把该对像放于该组,则因为多个磁盘能并行操作而加快操作速度.
  3:如果某些表经常要执行“连接查询”且容量很大,则应该把这些表分别存储在不同的文件组中,如此一来因为能对被连接的数据实施并行磁盘搜索而提高效率.

C:如何创建数据库:
  1:通过企业管理器:
  2:sql语句:
     格式:create database 数据库名
          on

          primary
         (
          name=/'数据文件1的逻辑文件名/',
          filename=/'数据文件1的物理文件名/',
          size=大小,
          maxsize=大小,
          filegrowth=大小
         ),
         (
          name=/'数据文件2的逻辑文件名/',
          filename=/'数据文件2的物理文件名/',
          size=大小,
          maxsize=大小,
          filegrowth=大小
         ),
          ............

          filegroup 用户自定义文件组名
         (
          name=/'数据文件1的逻辑文件名/',
          filename=/'数据文件1的物理文件名/',
          size=大小,
          maxsize=大小,
          filegrowth=大小
         ),
        (
          name=/'数据文件2的逻辑文件名/',
          filename=/'数据文件2的物理文件名/',
          size=大小,
          maxsize=大小,
          filegrowth=大小
         ),

          log on
         (
          name=/'日志文件1的逻辑文件名/',
          filename=/'数据文件1的物理文件名/',
          size=大小,
          maxsize=大小,
          filegrowth=大小
         )

说明:
1:数据文件名之间不能同名,数据文件名与日志文件名也不能同名.
2:在给一个组加上文件时,每个文件之间用“逗号”隔开.
3:数据文件与日志文件的分界时不要加逗号.
4:由于日志文件不能放入任何组,所以不要为日志文件设定组.
5:如果不指明组则添加的数据文件都自动放入primary组.
6:必须有一个主数据文件.
7:如果数据文件还有较大的空间但是日志文件的空间已占满则对数据进行的所有更新操作将都因无法写入日志而终止.
注意:数据文件和文件组绝对不能与日志文件存储在相同的磁盘上.

D:如何删除数据库:
   1:通过企业管理器:
   2:sql语句:
    格式:drop database 数据库名
    说明:
      1:数据库删除后,数据库里面的数据文件与日志文件就会被删除,系统数据库master关于该数据库的信息也被删除了.
      2:建议在删除数据库之后立即备份master数据库.否则你在删除了一个数据库之后而后却因为某种原因还原了过去所备份的master数据库,则会出错.
      3:master,model,msdb,tempdb这四个数据库不能删除.
      4:如果数据库正在被使用则不能删除.
创建和维护数据库(下)
5:如何修改数据库:
6:快速查看数据库的内容:
7:附加和分离数据库:
8:生成数据库的sql脚本:
9:数据库复制向导:

E:如何修改数据库
1:更改数据库的名字:sp_renamedb 旧名,新名
  说明:修改数据库的名字一定要小心,因为客户端应用程序都是以原先的名字来访问的.
       必须确认没以任何人连接以数据库中才可以更名.

2:扩充分配给数据库的数据文件或日志文件的空间:
  你在创建数据时,可以设置数据库文件的自动增长空间,事实上除了让sql server自动去扩充之外,还可以人工的方式来扩展数据库,还可以为已经创立的数据库增加新的文件.
   方法:企业管理器:数据库,属性
   说明:在给已经存在数据文件重新分配大小时,分配的大小不能比原来的小.

3:收缩分配给数据库的数据文件或日志文件.
  当你觉得你的数据库的初始值设定过于大,或者增长方式设置不符合你的业务,以致使数据库产生多余空间时,就可以收缩数据库了.
方法:企业管理器  “收缩数据库”
说明:1:收缩后文件的最大可用空间:可以指定一个数值来决定收缩后文件的可用空间,数值越大,收缩越小,反之收缩越大.
     2:在收缩前将移动页文件起始位置:在收缩之前会把位于页里面的数据集中到文件开头从而得到更多的可用空间,但由于要做移动数据等动作,则会花费较多的时间.
     3:调度:可以让sql server定期来收缩数据库,从而实现自动化管理.
     4:压缩页然后截断文件中的可用页:收缩数据页,然后将收缩页产生的可用空间删除.
     5:从文件结尾处截断可用空间.
   6:清空文件:清空数据文件或日志文件而该文件中的数据迁移到文件组中的其他文件中.
     7:收缩文件至:将选定的文件收缩到特定的大小.
     8:在一段时间之后收缩:到了某一时间之后再收缩.[预定义]
思考:能不能通过删除数据文件或日志文件的方法来收缩数据库.

4:添加或删除数据文件和事务日志:
   你可以添加数据文件或事务日志文件来扩充数据库的大小,也可以通过删除数据文件和事务日志文件来收缩数据库.下面着重来讲解数据库文件的删除:
     1:对于数据文件的写入方式是“按比例”写入,所以如果用户要建一个表则该表所属的文件组中的任何一个数据文件都将或多或小的写入数据.                      
     2:而日志文件的写入方式是“写满为止”的策略来写入,即只有当第一个日志文件写满了之后才去写下一个日志,这就是说如果你为一个数据库建多个日志文件,有可能有些日志文件是空的.
     3:要删除数据库文件必须保证数据库文件里面没有任何数据.
     4:要得到删除数据库文件必须首先将它里面的数据迁移动同一个文件组中的其它文件里面去.
         格式:dbcc shrinkfile (数据文件名,emptyfile)
     5:方法:数据库====属性:

5:更改数据库的所有者:
  1:什么是所有者:数据库的创建都就是数据库的所有者(dbo)
  2:数据库所有者和sa对该数据库有任何操作权限.
  3:数据库所有者与sa的区别在于sa是全局的,而数据库所有者是局部的.
  4:格式:sp_changedbowner 登录用户名
    说明:1:只能够更改当前数据库的dbo
         2:如果指定的登录名已经是当前数据库的用户,则不能更改
6:更改数据库的配置:
  A:仅仅供数据库拥有者或者sa能够使用:
     用途:当你作为一名数据库管理员需要改变表的结构时,你不希望别的用户来使用该表,可以把此项设置,当你把表的结构改了之后再把它改回来.
     sql语句:sp_dboption /'数据库名/',/'dbo use only/',/'true/'[false]
  B:单用户:同一时间只能够有一位用户连接到数据库来访问它:
     用途:更改数据库名称,还原数据库的时候.
            sp-dboption /'数据库名/',/'single user/',/'true/'[false]
  C:只读:使数据库只读.
            sp-dboption /'数据库名/',/'read only/',/'true/'[false]

F:快速查看数据库的内容
    1:企业管理器:任务板:
           A:常规:给出关于数据库一些详细资料.
           B:表:查看数据库中各个表的详细资料.
           C:向导:很方便的打开向导和工具来完成操作.
    2:sql语句:
           A:sp_helpdb 数据库名[指定数据库的详细资料,包括各数据库文件]
           B:sp_helpdb [当前服务器中所有数据库的详细资料,不包括数据库文件]
           C:sp_databases[显示当前服务器所有可以使用的数据库]
           D:sp_helpfile [查看当前数据库的所有文件的资料]

G:附加和分离数据库:
   目的:就是移动数据库的位置.
   注意:在sql里面没有复制,剪切,粘贴等命令.
   用途:
    1:将数据库移动到其它已经注册到你的企业管理器的其它的sql server 服务器中.
     问题:要把服务器www中chen数据库移动到test服务器中.
     比较:常规方法:
     本方法:先让chen与www分离,然后再把chen的数据文件和日志全部移到test服务器对应的路径,接着再把www附加到test服务器.
    2:改变数据库中数据文件或日志文件的磁盘的目录:
     比如存储数据或日志文件的空间已不足了时你便要移动数据文件或日志文件的位置到其它有更大的空间的位置上.
      A:数据库创建之后数据文件和日志文件的位置便不能更改.
      B:只有把数据库分离之后才可以改变.

H:生成数据库的sql脚本.
通过生成数据库的sql脚本我们能够产生创建现有数据库各个数据库对像的sql语句.然后可以把此脚本再次运行.
  例能够产生出一个和原数据库有相同结构(不是数据也相同)的新数据库.
用途:
   1:重新创建一个与某个数据库结构完全相同的数据库.
      比如:你在服务器A里面建了一个数据库test,你现在要把test的结构复制到B服务器里面去(注意:不是数据,仅仅是结构,也就是说对于表而言将不会有数据存在),那么你可以先在服务器A中生成一个关于test数据库的sql脚本,然后再在服务器B中去运行.
   2:备份一个脚本,以便允许用户可以重建所有的用户,角色,登录与权限.
      比如:你好不容易在某个服务器上创立了一些数据库用户或角色,或登录或权限.这个时候你可以生成一个你当前创建这些对像的脚本,如果以后有人恶意删除了这些对像,则可以通过把这些代码重新运行一次,重而免去重新创建这些对像的麻烦.
   3:更新数据库开发代码.
      比如:你可以在生成sql脚本的基础上,再对源代码进行修改,从而可以在源数据库的基础上扩展一些功能
方法:企业管理器:
  说明:
     1:为每个对像创建create命令:为用户在常规选项卡里面选择的各对像创建create语句.
     2:生成全部相关脚本:生成在常规选卡中选中与之相关的对像. 比如:如果你在常规选项卡里只选择了视图,而没选择了表,当你运行这种sql代码就会出错.因为视图需要表支持.如果选中此项则sql会自动将支持视图的相关对像一起创建.
     3:在脚本中包含说明性的标题:在生成代码的同时,加上一些说明性的文字
     4:编写数据库脚本:要生成数据库的代码.
     5:编写数据库的用户和角色脚本:要生成数据库的用户和角色的代码.
     6:编写sql server登录脚本:要生成服务器登录的脚本.
     7:编写对像级别权限的脚本:为你在“常规”页面中选取的各个数据库对像设置权限而编写的sql代码

I:数据库复制向导
利用数据库复制向导,我们可以很轻易地把一个服务器的数据库复制到另外一个服务器中.
1:选择是复制服务器的登录用户:
  A:包括运行时检测到的所有登录:将要复制的数据库所在的服务器全部登录帐户复制到另外一个服务器中
  B:仅包括数据库使用的登录:只复制“要复制的数据库”使用的登录的部分帐户.
2:选择master数据库里面的共享存储过程:
  A:包括运行时检测到的所有的存储过程:将把要复制的数据库所有服务器中master数据库里面的存储过程都复制到目的服务器所在的master数据库中.
  B:用户选定的存储过程:可以决定要复制的数据库所有服务器中master数据库里面的存储哪些要复制哪些不要复制.

创建和维护表(tables)
1:表的结构:
2:创建表:
3:自动编号字段:
4:活用用户定义数据类型:
5:创建计算字段:
6:快速查看、添加、修改与删除数据记录:
7:更改表的结构:
8:更改表的名称:
9:更改表的所有者:
10:删除表:
11:用户表与临时表与系统表:

A:表的结构:表的结构基本决定了表拥有哪些字段名、使用的数据类型.
   1:字段名(field)
     1)字段名最长可达128个字符
     2)同一个表中字段名绝对不能重复
   2:长度、精度和小数位数,NULL值.
     1)长度:指字段所能容纳的最大数据量.对于不同类型的数据,长度对字段的意义可能有些不同
       一:对于字符型数据类型而言:长度代表字段所能容纳的字符的数目,因此它会限制用户所能输入的的文本长度[长度可以修改]
       二:对于数值型数据类型而言:长度代表字段使用多少个字节来存放数据,它决定了字段所能存放的最大数最小数[长度固定不能更改].比如: int 4字节=4*8=32位,则最大的数为: - 2^(32-1)-----2^(32-1)
     2)精度:一般精度是相对于小数而言,表示数中数字的个数.
     3)小数位数:小数点后数字的位数.
          比如:1234.678 其精度是:7(不包括小数点),小数位数是:3
      说明:有些数据类型精度,小数位数都不能改,有些可以改.decimal,numeric
   3:数据类型(datatype)
      特殊的类型:char,varchar的区别,精确数据类型:decimal,numeric [都可以指定小数数位数和精度]
                 image,money,datatime[固定保留四位小数],bigint,int,smallint,tinyint
   4:NULL值:
     1:NULL值并不等于数值0,空格,它是虚无值.
     2:NULL值是说此字段中的数据是否可以是未知的或未定义的,如果允许为NULL则该字段的数据是允许未知,否则只能是已知的  
     3:快捷方式:ctrl+0

B:创建表:
     1:通过企业管理器:
        1:添加或插入字段:
        2:删除字段:
        3:表的属性窗口:所有者[该表所在的数据库里必须有用户],表名,表文件组,备注,是否为空
        4:描述,默认值,精度,小数位数,标识,标识种子,标识递增量.[如果把某一个列定义成为标识列,则该列不能编辑]
     说明:一个数据库包括表最多只能够有2^(32-1)个对像  每一个表最多可以拥有最多1024个字段
     2:通过sql语句.
        格式:create table 表名
            (
             列名1 数据类型(长度) not NULL [NULL],
             列名2 数据类型(长度) not NULL [NULL],
             ................
            )
             [on] [放入指定的文件组]
       说明:
        1:对于字符型要指明长度,比如:职工号 char(20),姓名 vchar(20)
        2:对于除decimal,numeric(6,2)以外的数值型类型,不需要指明指定长度.比如:基本工资 int,单价 float
        3:可以对每个字段指定是否允许为空,如果省略则可以为空.[not null]
        4:可以指定把创建的表放入一个文件组,如果省略则自动放入primary组.[该组必须存在]

C:自动编号字段
       说明:
        1:自动编号的字段类型只能是以下几种:int字样的数据
        2:如果把某一个字段设置为自动编号,则用户不能去更改里面的内容
        3:每一个表只能拥有一个自动编号字段

D:活用用户自定义类型
    1:为什么要使用用户自定义类型:
       理由:有时候为了建多个表,而这些表中都具有相同类型相同格式的某一种系统数据类型.如果每次建一个表都要去设置很不划算,可以引入用户自定义类型.
    2:怎么定义或使用用户自定义类型:
    3:删除用户自定义类型:
        1:显示相关性:
        2:如果该数据类型被其它对像使用,则不允许删除
           说明:
            1:在某一个数据库里创建的用户自定义数据类型只能够被该数据库的对像使用.不能被其它数据库使用.
            2:问题:如何创建一个数据类型可以让新建的数据库都可以使用?(在模板数据库中创建)

E:创建计算字段:
      计算字段:计算字段不是表中的字段,而是由表中其它字段通过计算而得来的.比如:单价*数量=销售量
        1:为什么要创建计算字段:
        2:怎么创建计算字段:创建表的时候才能够创建计算型字段
           A:通过sql语句:
                 比如:create table ww
                (
                 商品名 vchar(10),单价 float,销售量 int,总金额 as 单价*销售量
                )
           B:格式为:计算字段名 as 表中字段表达式.
           C:通过企业管理器:公式列中进行
             注意:计算字段也可以参与查询,但不能更新

F:快速查看、添加、修改、删除数据记录
       1:查看:打开表.
       2:添加:直接加.
       3:删除数据:删除一行,删除多行
       4:打开表,单右:菜单

G:更改表的结构 :
       1:通过企业管理器:
       2:使用sql语句
         格式:
          1:修改原有字段的数据类型
             alter table 表名
             alter column 列名 新的数据类型
           说明:
            1:一次只能修改一个字段的数据类型.
            2:不能修改计算字段,或使用在计算字段中的列.
            上述命令只能够修改数据类型不能修改字段名称.要修改字段名称可以用:sp_rename column 新的列名,
                  格式:sp_rename /'表名.旧列名/',/'新列名/',/'column/'
          2:在原有表的基础上添加新的字段.
             alter table 表名
             add 数据的字段名 数据类型 [添加时,没有column关键字]
                  可以一次添加多个字段,字段与字段之间用逗号隔开      
          3:删除原有表的字段:
             alter table 表名
             drop column 列名 [删除时,需要column关键字]
            1:可以一次删除多个字段,字段与字段之间用逗号隔开
            2:可以删除计算字段,但不能删除使用在计算字段中的列.

H:更改表的名称
    1:企业管理器:
    2:sql语句:  
         格式:sp_rename 旧表名,新表名
         说明:
           1:如果一个表被某个或多个对像引用,则不要改名,否则会使这些对象不可用.
           2:不要对系统表改名,这有可能会使系统损坏

I:更改表的所有者
    1:通过企业管理器:
    2:sql格式:sp_changeobjectsowner /'旧拥有者.表名/',/'新的数据库用户/'

J:删除表
    1:通过企业管理器:
        1:删除前的准备:查看有哪些对像在引用此表
        2:删除:
    2:sql语句:
       格式:drop table 表名    可以一次性删除多个表,表名和表名之间用逗号隔开

K:用户表临时表系统表
    sql server把整个表分成三种类型:用户表,系统表,临时表.用户表和系统表已经熟悉
     临时表:
       什么是临时表:就是临时存放数据的地方,一但不使用时会自动删除.
       为什么要使用临时表:有时候在进行数据操作时,希望产生一些阶段性或者过滤性的数据,这些数据只是暂时使用,而当工作结束时,我们希望它们能自动删除,而不是人为去删除.这个时候我们就需要临时表.
       临时表的分类:
         1:本地临时表:
            创建本地临时表:当你创建表时,在表名字前面加上“#”号,则这个表就成为本地临时表.
            特点:本地临时表只有创建它的用户可以访问它,其它用户将不能访问,而且一旦这位用户断开与sql server的连接,本地临时表会自动删除.
         2:全局临时表:
            创建全局临时表:当你创建本时,在表名字前面加上“##”号,则这个表就成为全局临时表.
            特点:当一个用户创建一个全局临时表时,其它连接到sql server服务器的用户都可以使用,当创建者与服务器断开后,sql server并不会立即删除该用户所创建的全局临时表,而是会去检查当前还没有别的用户的作业在访问该表,如果有,则暂时不删除全局临时表,但是不允许用户再去访问它,直到在sql server服务器再没有关于该表的作业时,才删除.



设计、创建和维护索引(index)(上)
1:索引的基本概念:
2:索引的设计准则:
3:索引的类型:
4:聚集索引和非聚集索引:
5:唯一索引和非唯一索引:

A:索引的基本概念
   索引在关系型数据库中扮演着极其重要的角色,它最主要的作用是:
     1:确保数据的唯一性:2:提高数据查询速度
        提高数据的查询速度
          为什么可以提高数据的访问速度:索引可以把表中的数据进行排序.
          举例:创建一个work表最好有80万行.执行:select * from work where 年龄<50
              1:[不建索引]观察所用时间
              2:[创建一个索引]观察所用时间
          
       1:表扫描:一行一行把表的数据全部扫描到.[tabscan]
       2:使用索引:把表的数据进行排序,从而可以提高数据的查询效率
       3:索引的结构:索引页,数据页
      举列说明:
      A:select * from work where 职工号=/'001/'.如果事先能够为职工号建立索引.则可以提高查询速度
      B:select * from work where 基本工资>1200 and 基本工资<3000.如果事先能够为基本工资创建索引则可以提高查询效率
      C:select * from work order by 基本工资.若能够事先为基本工资创建索引也能够提高查询效率

sql server在什么时候要使用索引:
不要以为只要为某个字段创建索引之后,sql server在查询的时候就一定要使用它.事实上sql server在执行查询之前会通过一个叫“查询优化器”的对像来判断是采用表扫描,还是使用索引,如果利用索引不能使查询得到优化,则不会使用索引,而改用表扫描.

确保数据的唯一性:
索引中有一类索引叫唯一索引(unique),利用它可以保证以该字段为索引的值不会出现重复但它可以为空.与主键有区别,当然如果你创建的索引不要求保持数据唯一性,只是为了提高查询效率你完全可以不建唯一索引,建一个一般的索引就行了.当然确保数据唯一性的方法有很多,比如可以创立主键等.索引只是确保数据唯一性的其中一种方法.

B:设计索引的准则
1:经常被用来查询数据记录的字段.
   说明:经常被用来查询数据记录的字段最好是识别度较高的字段(也就是数据内容重复性低).
   比如:使用员工编号或身份证号来作索引是非常恰当的.但如果使用家庭住址或使用基本工资来寻找员工就显然不恰当.家庭住址一般有很多字符,会使索引过于庞大,基本工资识别性太低.
2:主键所定义的字段一般应作索引,不过sql server自动帮我们作好了
3:在查询中用来连接表的字段:
4:经常用作排序的字段:
   说明:不能为bit,text,image等数据类型的字段创建索引.

索引的负面影响:
那既然索引可以加快查询效率,那我们就把索引建多,越多越好,这一种说法完全错误.
理由:
  1:索引显然会占用磁盘空间,创建不必要的索引只会浪费空间[任务面板中查看]
    一个索引的大小绝对大于以该字段作为索引的大小乘以记录条数.
  2:以某个字段创建一个索引之后,当以后修改或添加该字段时,为了让索引能够实时反映表的动态更新必须有相关的索引.[索引页]
    所以得到一个结论:如果一个表是作为引用性的,也就是说它仅仅只作为查询,或者说它的改动非常少,多几个索引倒也无所谓,但是一个经常性修改的表则请谨慎的创建必须的索引[举列说明]
    A:在work中执行update work set 年龄=年龄+1 where 年龄=20[观察使用时间]
    B:在work中创建一个索引以年龄为索引,再执行同样的语句[观察使用的时间]
  3:数据记录越多,索引提高数据访问效率越明显,反过来如果表内含有数据记录很少,建个索引是不会有任何作用的,所以不主张对一个记录很少的表创建索引

C:索引的类型
1:按存储结构区分:聚集索引(clustered index),非聚集索引(nonclustered index)
2:按数据唯一性分:唯一索引(unique index)和普通索引
3:以键列的个数分:单列索引与多列索引.

D:聚集索引和非聚集索引
聚集索引:数据页内容排列的次序与索引页的索引字段的值相同.即:聚集索引会改变表原先的排列顺序使之按照索引升序或降序排列.
正由于聚集索引会使得值相同的数据记录排列在一起,因此要搜索介于某范围的数据值时将会特别有效.因为一但使用聚集索引找到第一条符合条件的记录,则同范围之后的记录保证是相邻排列的,也就是说把这一范围找到之后就不会往下面找了.
怎么使用聚集索引
一般而言下列的数据访问操作非常适合使用聚集索引:
    1:如果某个字段所包含的有差别的数据较少,则非常适合以该字段创建聚集索引.比如:员工表中部门字段用来存放员工所在的部门.但一个单位的部门不会超过十个,则以部门创建聚集索引最好.[为什么?]
    2:使用betwwen,>,>=,<=,<等运算符查询[注意不是等于]
    3:会返回大结果集的查询 至少10万以上
   说明:
      1:每一个表最多只能有一个聚集索引
      2:聚集索引可以是一个唯一索引或普通索引
      3:聚集索引的大小平均是表大小的5%,在创建聚集索引期间,它需要使用数据库的一些磁盘空间来存放编制索引过程中的一些临时数据,所需的临时空间大约是表大小的1.2倍,这些临时数据会在索引创建完成后释放出来.
      4:如果某一个字段的内容经常变动则非常不适合给这个字段创建聚集索引.因为聚集索引要求表的记录按照索引列排序,所以如果一个字段的变动将会导致整个表的记录的要重新排列一次
  
非聚集索引:
与聚集索引不同的是,非聚集索引尽管可以按照升序或降序排列,但它丝毫不影响表中数据的实际的存放顺序[注意:索引的排序与表的实际数据的排序的区别]
所以非聚集索引并不会使表的记录排序[是不是就达不到提高数据搜索的效果呢?思考]
另外每一个表中可以拥有多个非聚集索引,正是由于一个表能拥有多个非聚集索引,所以如果你不需要用多种方式搜索数据记录时,非聚集索引便显得特别好用.比如你想先对年龄进行搜索而后又对基本工资进行搜索则我们可以分别为年龄和基本工资创建一个非聚集索引
怎么使用非聚集索引
一般而言下列情况非常适合使用非聚集索引:
    1:如果某字段包含的有差别数据非常多,则非常适合为该字段创建非聚集索引.比如:一个客户表的姓名而言,虽然也有相同的姓名,但是大多数的客户的姓名都不一样,所以就要以姓名作为非聚集索引
    2:所返回的结果量是不大的查询
    3:使用where子句的字段,并要求该字段等于特殊的某个值.比如:where 职称=/'经理/'
    4:每一个表最多能够拥有249个非聚集索引
    5:非聚集索引可以是唯一索引和普通索引

E:唯一索引和非唯一索引
  唯一索引
如果在创建索引的时候加上unique关键字就变成的唯一索引,唯一索引的特点:
   1:唯一索引可以确保索引列不包含重复的值.在多列唯一索引的情况下,该索引可以确保索引列中每个值组合都是唯一的.
   2:聚集索引和非聚集索引都可以是唯一的.因此,只要列中的数据是唯一的,就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引.
   3:唯一索引不仅可以保证数据唯一性还可以提高查询效率[用执行计划的io成本来察看]
    思考:如果你创建唯一索引的目的仅仅是为了保证数据唯一性,没有必要创建索引,但如果你创建唯一索引的目的是既要保持数据唯一性又要提高查询效率,想一想应该创建聚集的还是非聚集的.
   4:当你为一个表创建一个主键时,sql server会自动帮你创建一个唯一索引
   5:唯一索引与主键都能够确保数据唯一性,但是它与主键有区别[思考有什么区别]
    思考:有work表有职工号,姓名,学历.假如本公司没有一个人同名,应该怎么做:
     方法1:创建一个由列职工号和姓名组成的主键.
     方法2:由创建一个主键职工号,再创建一个唯一索引,其索引列为:姓名
          分析两种方法的结果.
怎么使用唯一索引
1:虽然唯一索引可以保证数据的唯一性,但是如果你只想保证数据唯一性话,则不是必须要设置唯一索引,你可以使用sql server其它方法  比如:主键,约束等.
2:只有你既希望保证数据唯一又想提高查询效率时,才使用唯一索引.
   比如:work表中有职工号,姓名,性别,年龄,学历.假定本公司没有同名的人,为了在以后输入姓名的过程中避免出现复制错,而且你经常要用姓名列进行查询,则你可以在姓名列上创建一个唯一索引,这样既保证了数据的完整性又可以提高查询的效率

  非唯一索引
如果你在创建索引时没有使用unique关键字,则你创建的索引为非唯一索引,这样该索引的作用仅仅是为了提高查询的效率



设计、创建和维护索引(index)(中)
6:单列索引和多列索引:
7:填充因子:
8:统计信息:
9:创建索引:
10:删除索引:

F:单列索引和多列索引
  单列索引
可以为一个列创建一个索引也可以为多个列创建一个索引,为一个列创建的索引就为单列索引,为多个列创建的索引被称为多列索引.以上讨论的都是单列索引,在这里就不在讨论,下面讨论多列索引.

  多列索引:由多个列组成的索引被称为多列索引
  为什么要使用多列索引
    1:当你需要为多个列确保数据唯一性时[不是主键],你可以设置一个多列的唯一索引.
      比如:work:职工号,姓名,性别,年龄,学历,基本工资.假定本公司中规定姓名和性别都同时相同时,你可以作一个唯一的索引,这个索引以姓名和性别作为列.
    2:如果你想对某些列按照不同的字段排序时,请使用多列的聚集索引.
      比如:你想按年龄升序排列,而年龄相同的又按照基本工资降序排列,则你可以创建一个有年龄,基本工资的聚集索引  
      思考:在work表中有职工号,姓名,年龄,学历,基本工资,职称
           要做如下查询:select * from work where 基本工资<1200 and 年龄<25
      要提高效率有两种方法:
        1:创建一个多列索引,索引列为年龄 升序,基本工资 降序
        2:创建两个单列索引,一个索引的索引列为年龄,一个索引的索引列为基本工资.
          分析哪一种方法可以更好的提高查询效率
结论:覆盖的查询可以提高性能.覆盖的查询是指查询中所有指定的列都包含在同一个索引中.例如,如果在一个表的a、b和c列上创建了组合索引,则从该表中检索a和b列的查询被视为覆盖的查询.
          
G:填充因子:
    页拆分
在创建聚集索引时,表中的数据按照索引列中的值的顺序存储在数据库的数据页中.在表中插入新的数据行或更改索引列中的值时可能重新组织表中的数据存储,以便为新行腾出空间,保持数据的有序存储.向一个已满的索引页添加某个新行时,SQL Server会通过拆页以便为新行腾出空间.这种重组称为页拆分.页拆分会降低性能并使表中的数据存储产生碎片.

    解决办法:
创建索引时,如果在每个数据页预留一些空间,这样以后修改和增加数据时就没有必要移动每个页中的数据以便为新的数据腾出空间了,则可以大加快修改和增加数据的效率.在前面我们已经知道数据被分了多个页每一个页的大小为8kb,那么为每一个页已经填充的空间与整个页的空间的比例就是填充因子.
    特点:
  1:很显然填充因子越大,则意味着预留空间越小,当为100%时,表示填满了.
  2:填充因子值越小则数据页上的空闲空间越大,这样可以减少在索引增长过程中对数据页进行拆分[思考:是不是填充因子越小越好]
    怎么设置填充因子:
     1:对于一个经常要修改的表,由于要经常对数据进行修改,则拆分页的可能性越大,为了避免拆分页,可以把填充因子设置得小一点[预留空间越大],但是很浪费空间.这是以牺牲表的存储空间来提高数据的更新的效率的
     2:对于一个只读的表,或者说很少修改的表,为了不浪费空间建议把填充因子设得大一点.
     3:填充因子只在创建索引时执行,以后随着用户不断往表中添加或删除数据时,由于每一页的可用空间要减少或增加,所以填充因子会改变.所以如果对一个表作了大量的改动时,可以删除索引再重新分配数据
       思考:如果在不考虑存储空间的情况下,把填充因子设得很小,这样是否可以避免分页

H:统计信息:
    什么是统计信息
我们知道在用户创建索引之后,sql server在查询的时候并不一定要使用它.事实上sql server在执行查询之前会通过一个叫“查询优化器”的对像来估算按索引查询所需的成本和直接使用表扫描所需成本,如果发现使用索引所需要的成本大于使用表扫描使用的成本,则不会使用索引而改用表扫描,反之则使用索引.
那么查询优化器依赖什么来估算使用索引查询所需要的成本呢?它只能通过“每一个索引值在索引列中的分布情况的统计信息”估算.实现上在创建索引时,SQL Server会自动存储有关值在索引列中的分布情况的统计信息.SQL Server中的查询优化器使用这些统计信息估算使用该索引进行查询的成本.
但是随着用户对表的修改会导致列中数据的不断更新,则这种统计信息就会过时,从而导致查询优化器选择的查询处理方法不是最佳的,很显然使用过期的“统计信息”估算成本是不真实的,也就是说它不是最优的查询.

  自动更新统计信息与手动更新统计信息
    自动更新:
因此,随着表中的数据发生变化,SQL Server会自动定期更新这些统计信息.以保证“查询优化器”总是采用最优查询,但是如果用户每次对表进行一点点修改,如果sql server都要去更新统计信息这显然会加大sql server的负荷.所以并不是只要用户对一个表进行更改sql server就会马上去更新“统计信息”,而是更新的频率取决数据更改量.例如,对于一个包含10000行的表,当1000个索引值发生更改时,该表的统计信息便可能需要更新,因为1000个值可能在该表中占有很大的比例.而对于包含10000000个索引条目的表来说,有1000个索引值发生更改的意义要小得多,因此统计信息便可能不会自动更新.
 你可以选择是否要让sql server自动更新还是手动更新:数据库->属性->自动更新统计信息

    手动更新:
请注意:sql server在对“统计信息”进行更新时,它是不会分析表的所有数据的,而是会从表中随机抽样,这样可以极大地减少这种统计信息的自动更新所带来的成本.所以有可能在某些情况下,统计采样无法获得表中精确的“统计信息”,这个时候我们可以手动更新,比如:我们可以指定对表中所有数据进行抽样以取得精确的“统计信息”,或者指定对表的多少行进行采样.
    如何手动更新:
    update statistics 表名 索引名 [with fullscan或者sample 数值 percent或者rows]
  说明:
   1:如果省略索引名将对表的所有索引进行手动更新
  2:如果使用fullscan关键字,则表示对表中所有行进行采样,这样会费很长时间,但会得到更优的查询
  3:如果使用sample 数值 percent则表示对整个表行的百分之几进行采样
  4:如果使用sample 数值 rows则表示对整个表的只取多少行进行采样.
   比如: 在work表创立两个索引 年龄,基本工资
         如果执行:
                1:update statistics work with fullscan[表示什么意思]
                2:update statistics work with sample 10 percent[表示什么意思]
                3:update statistics work with sample 10 rows[表示什么意思]
    什么时候需要手动更新:如果索引列中添加、更改或删除大量数据请使用update statistics手动更新

I:创建索引
1:通过企业管理器:
   A:索引文件组:可以把索引也放在数据库的文件组中.你可以把关于一个表中创建的索引与表放在一个文件组中,也可以把基于一个表的索引放在不同的文件组中,这样可以在查询时由表所在的驱动器与该表索引所在的驱动器同时并行查询,从而提高查询效率.
   B:但是一个聚集索引只能够放在primary组中.
   C:不自动重新计算:如果选择该项则sql server不会自动更新“统计信息”,当用户在对表作了大量的修改之后再查询时因为“优化器”使用的是“过时的统计的信息”,所以可能得不到最优的查询,除非用户手动更新.
2:通过sql语句.
    格式:create [unique][clustered] index 索引名 on 表名(列[asc|desc]) with fillfactor=整数
      说明:
         A:如果省略unique则是非唯一索引
         B:如果省略clustered表示一个非聚集索引
         C:在列后面可以指明是升序还是降序,如果省略则为asc(升序),否则必须指明desc(降序)
         D:如果要创建多列索引可以写多个列,列与列之间用逗号隔开.
           例如:
             A:create unique clustered index 年龄 on work(年龄 desc)
             B:create unique clustered index 年龄,基本工资 on work(年龄 desc,基本工资)
           思考:
             假定你要创建一个聚集索引和一个非聚集索引,你是先创建非聚集索引还是先创建聚集索引
           结论:
             sql server会在删除或创建任何聚集索引的时候重新创建非聚集索引,所以在创建任何非聚集索引之前创建聚集索引.

J:删除索引:
  注意以下两点:
   1:当一个索引不再需要,可以将其从数据库中删除,以回收它当前使用的存储空间.这些回收的空间可以由数据库中任何对象使用.
   2:当你把一个聚集索引删除后,sql servr必须把以前的同一个表上的所有非聚集索引重新创建一次,所以删除一个聚集索引比删除一个非聚集索引要花费更多的时间.
   3:只有表的所有者或sa可以删除其索引.
  方法:
     A:通过企业管理器:打开表的设计窗口,单右->索引->删除
B:通过sql语句:格式:drop index 表名.索引名




设计、创建和维护索引(index)(下)
11:索引的维护操作:
12:索引的评估和分析:
13:为计算字段创建索引:
14:为视图创建索引:
15:索引优化向导.

K:索引的维护
你创建了一个索引之后,用户可能要对表进行修改,而为了让索引能发挥它的最大用途,必须对它进行维护.
我们通常对索引有下面的维护:
  1:重建索引
     一个索引已经创建好了,为什么还要去重建索引呢?
    A:为什么要重建索引:
     在数据库中创建索引时,查询所使用的索引信息存储在索引页中,随着用户对表进行不断的修改,填充因子会不断的改变,这样就会生成很多碎片,这显然不利于查询.我们可以重建索引以重新组织索引数据来达到清除碎片的目的.
    B:怎么重建索引:
     思考:假设你已经为work表作了一个“年龄”聚集索引,随着用户不断更新该表,使它产生很多碎片,现在要重建你该怎么办:[首先删除原先建的年龄索引,再来创建一个年龄索引,行吗]
     结论:这样做虽然可以,但会花费更多的成本,因为我们已经知道,每用户删除一个聚集索引,时sql server会把该表所有的非聚集索引全部删除之后再重新创建,显然不划算,那么可以在不删除原有索引的基础上来新建一个与原索引一样的索引吗?当然可以.
   那么可以看到不删除原有的索引而重新创建一个索引的好处,有以下两点:
    1:免了删除和重新创建非聚集索引这些不必要的工作
    2:可以使用现有索引中的数据排序次序,从而避免了对数据重新排序.这对于聚集索引十分有用,可以显著减少重建索引的成本.
      方法:只需要在创建索引的时候加上with drop_existing关键字就行了.
      比如:create unique clustered index 年龄 on work(年龄 desc) with drop_existing
      思考:是否执行了这个命令之后就意味着所有的非聚集索引不会删除之后再创建了呢?
      结果:当建的新的聚集索引与原聚集索引的列相同时,才不会删除非聚集索引,否则还是会删除非聚集索引之后再创建.
  2:查看索引:
      在创建索引之后我们可能需要
        A:查看某个索引类型
        B:一个表的全部索引
        C:某个索引使用的数据库空间总量.
      方法:
        1:查看表中的索引
           格式:sp_helpindex 表名
        2:查看数据库中的全部索引及使用的空间
           通过:任务面板
  3:碎片整理
      我们已经知道,当用户对一个表创建一个索引之后,由于用户对表不断的改变,会导致产生很多碎片.你既可以重建索引,也可以选择不重建索引,而直接对索引进行碎片整理.
       方法:dbcc indexdefrag(数据库名,表名,表中的索引名)  
       比如:dbcc indexdefrag(陈文杰,work,年龄)[对数据库陈文杰表work里面的年龄索引进行碎片整理]
         显示的信息会给出:有多少页扫描了,有多少页被移动了,有多少页被删除了
         注意:若索引的碎片相对较少,则整理该索引的速度比生成一个新索引要快,这是因为碎片整理所需的时间与碎片的数量有关.对碎片太多的索引进行整理可能要比重建花更多的时间.
  4:更名
     方法:sp_rename /'表名.原索引名/',/'新索引名/',/'index/'
     例如:sp_rename /'work.年龄/',/'年/',/'index/'[把work表“年龄”索引名改为“年”]
  5:索引管理器:可以方便对选择的数据库中相应的索引进行修改,删除,还可以方便进行索引的新建删除等.
              操作方法:在企业管理器中选择某个表->单右->管理索引

L:索引的评估和分析:
实际上评估一个查询是不是最优查询,并不是由时间来决定的,因为相同的一个查询在计算机很忙的时候运行所花费的时间绝对要大于计算机很空闲的时候运行所花费的时间,因为当计算机很忙的时候cpu要把时间分给其它的程序,这显然会延长一个查询所花的时间,而当计算机很闲时,相当于把cpu的时间全部给这个查询,可以减少运行时间,所以仅凭运行时间来判定一个查询是否最优是不精确的,那么我们可以根据这个查询所花费的成本来决定一个查询的优劣.可以通过“执行计划”来察看
    方法:显示执行计划,快捷方式:ctrl+k
         A:查询分析器是采用表扫描还是采用索引来执行查询.
         B:如果采用索引查询,是用的哪个索引
         C:查询所花费的成本.
            i/o成本:用于操作的所有I/O活动的预计成本(I/O成本).该值应尽可能低.
            成本:查询优化器执行此操作的成本占查询总成本的百分比.由于查询引擎选择最高效的操作执行查询或执行语句,因此该值应尽可能低.

M:为计算字段创建索引
计算列虽然不是表的的字段,但是可以参与查询,所以为了加快查询效率,我们有时候希望在计算列上创建索引,在计算列上创建索引的方法与在非计算列中创建索引一样,但是需要注意二点:
    在创建表的时候[不是在表创建好之后],既在执行create table的时候必须满足:
        1:为计算列的表达式上使用的表的字段类型不能有float型,被称为不确定或不精确
        2:ANSI_NULLS:必须设置为on 方法:SET ANSI_NULLS on[一般情况下]
             说明:默认为off,表示可以在查询中使用=null或<>null条件,否则不允许使用

N:为视图创建索引
我们知道视图并没有存储数据,而是存储定义,所以当视图在刚刚打开的一瞬间会根据定义从相关的表中收集数据,相当于从相关表中做一个查询,然后再把数据导入到视图中,也就是要动态生成与视图定义相关的结果集.
于是用户要对一个视图进行查询时,将经过两步查询:
   表----------->视图---------->用户
         查询           查询
但是对一个很大的表来说,如果用户在查询之前都要从基本表是导入数据,将是很费时的事,如果我们把定义视图的数据直接存储在视图中这样当用户去查询时,就会直接从视图中搜索数据,这显然可提高效率.具备有这种特点的视图被称为索引视图.
   视图(包括数据)-------->用户
                   查询
  思考:怎么验证当创建索引视图之后,确实把数据存储到视图里面去了.[任务板]
  说明:  
     1:在视图上创建的第一个索引必须是唯一聚集索引.
     2:在创建唯一聚集索引后,可创建其它非聚集索引,也就是说在视图上不能创建单一的聚集索引.
  方法:
   A:通过企业管理器:
         单右-->设计视图---->单右---->索引管理:创建索引
   B:sql语句:
         格式:create [unique][clustered] index 索引名 on 视图名 (列[asc|desc])
         很显然:除去视图上的聚集索引将删除存储的结果集.[如何验证:删除索引视图的聚集索引]

索引优化向导
可以这样说,没有哪个人能够真正的设计一个非常优秀的索引,因为这需要深入了解SQL Server的数据库结构、工作负荷和内部原理.即使是微软高级工程师也不敢说自己创建的索引是最完美的,况且你只是一个一般的使用者呢?考虑到这些,微软为我们提供了一个能够让大多数用户操作,而又不需要去深入了解sql server内部结构、原理的操作平台,去做一个最好的索引,这就是“索引优化向导”.
  说明:
    要想让sql server建更好的索引,必须要指明:
      A:指明一个服务器以及该服务器中一个数据库,以及该数据库中一个或多个表.[因为索引是对表而言的]
      B:由于要创建索引,而我们知道创建什么样的索引是和所执行的sql语句有关的,所以必须要让sql server执行一个包括许多sql语句的一个sql脚本文件,我们把它叫工作负荷,为了让创建的索引能够真实的帮助你提高查询效率,要把与你工作相关的经常执行的sql语句放在一个脚本文件里面
      C:假设你在使用“优化向导”之前已经创建一些索引,那么你可以选择让sql server是保留原有索引还是让sql server“优化向导”通过分析来决定是如何对现在索引进行更正,而增加新的索引.很显然如果你选择“要保留现有索引”,也就是说不管你以前的索引做得好还是坏,sql server都不会对你以前的索引提出真实的建议,这也许会使sql server“优化向导”没有办法提出新的建议,因为有可能某些确实可以提高查询效率的的建议是与你以前的索引情况相反的,而你要选择了“保留了现有索引”,所以不主张保留“现有索引”.
      D:在sql server索引优化向导为你建议了新的索引之后会对以前的索引作一些操作比如:修改或删除,而创建新建议的索引
      E:同时为了让用户能够对使用“优化向导”之前与使用“优化向导”之后的结果作一个对比会给出一个对比列表
        方法:向导---------->优化向导  
        说明:
          A:优化模式:优化模式决定了“优化向导”建议索引的准确性.
            快速:建议的索引最优性不是最好的,但可以节约时间.
            彻底:建议的索引比起“快速”与“适中”而言更准确.
            建议:使用彻底
          B:将要抽样的工作负荷查询的数目限制为指定要抽样的工作负荷查询的数目.默认值为200个查询.从指定的工作负荷文件中随机选取查询.如果设置值超过工作负荷文件中的查询数,则优化所有查询.
          C:用于建议索引的最大空间(MB)
           “优化向导”建议的所有索引的最大空间(以MB为单位),默认空间大小是当前数据大小的三倍.
          D:每个索引的最大列数
            指定索引优化向导建议的多列索引中的列的最大数.允许的最大值是16,这是默认值.
          E:报表
            A:索引使用报表:给出在使用“优化向导”之前与建议之后的每个表中每个索引的使用率和大小.
            B:查询关系报表:给出在使用“优化向导”之前与建义之后每个查询使用的索引的情况.
            C:优化摘要表:给出在使用“优化向导”之后,有多少个索引被优化,删除了多少个以前的索引,新建了多少建议的索引,优化了多少个查询,分析的时间.
    试验:做一个work表:职工号,姓名,性别,年龄,学历,基本工资.有180万行.
       在查询分析器中写上:
          select * from work where 年龄<25
          select * from work where 基本工资<1500
       保存作为工作负荷
         分析:不用优化向导,要对提高这个查询效率而设置索引
         讨论方法:并按讨论的方法在work表创建索引.
         并运行:记下成本.
       通过“优化索引向导”来创建索引
         说明:在运行“优化索引向导”的时候,先后选择“保留索引”和“不保留索引”,看优化索引向导所提出的建议是否一样,执行完“优化索引向导”以后,再运行:记下成本.比较两次的成本. 2004-3-11 08:11
            冒顿
Moderator




积分 236
发贴 136
注册 2004-2-24
来自 湖北武汉
状态 离线 transact ---sql查询基础
1:select语句最简单的用法.
2:where子句的用法.
3:用in,not in和between关键字来缩小查询范围.
4:用like子句来实现模糊查询.
5:用order by子句来为结果排序.

A:select 语句最简单的用法
    work表:职工号,姓名,性别,年龄,学历,职称,基本工资
     A:简单的select查询格式:select 列名[1..n] from 表名 [从一个表中有选择性的查看某些列,而不是全部]
       比如:select 职工号,姓名,学历,基本工资 from work [从work表中检索出职工号,姓名,学历,基本工资的信息]
     B:select * from 表名 [从表中检索出全部的列]
     C:可以使用select语句进行无数据源查询,就是查询的不是表,而是系统变量.
       比如:select @@version[获得版本],select @@language [获得sql server使用的语言]
     D:使用top关键字.
       作用:用于只返回前面一定数量的数据.
       用法:1):top n值:返回一个表中前n行的数据.
            2):top n值 percent[返回一个表中百分之n的结果] 格式:必须紧跟在select的后面,所有列前面
                比如:1:select top 10 职工号,姓名,学历 from work[从work表中返回前10行职工号,姓名,学历等信息]
                       [不要写成:select 职工号,姓名,学历 top 10 from work]
                     2:select top 10 percent * from work [从work表中返回前10%的记录]              
                     3:使用计算列 [score表:姓名,语文,数学,化学]
                       比如:select 姓名,语文,数学,化学,语文+数学+化学 from score [会显示无列名]
                       或:select *,语文+数学+化学 from score
                     说明:可以在计算列上加上as指定列名,为计算列指定列名
                          可以计算列上使用:+,-,*,/,%[取余],或字符连接字符“+”
                     比如:select /'职工号: /'+职工号+/'姓名:/'+姓名+/'性别 /'+性别 from work
     E:使用distinct[种类]
       作用:返回无重复的行.
              检索出work表中有多少种学历
                select 学历 from work [将会得到重复的值]
                改为:select distinct 学历 from work    
                说明:distinct只能用于列名前面,是对整个列进行检验.
                比如:select 学历,distinct 职称 from work[分析会得到什么答案]
                只能改成:select distinct 学历,职称 from work
                  [则会显示出学历,职称没有全部都相同的记录,意义:可以检索出各学历各个职称的分布情况]
     F:操作查询的列名
       可以根据实际需要对查询数据的列标题进行修改或者为没有标题的列增加标题.对列名进行操作有三种方式:  
             1):原列名 新列名
                比如:select 职工号 /'职工编号/'(或职工编号),姓名,学历 from wrok[对已经存在的列名进行标题操作]
                再如:select *,语文+数学+化学 总分 from score[对没有标题的列增加标题]
             2):用“=”号  格式:新列名=旧列名
             3):用as关键字 格式:旧列名 as 新列名
             说明:
                1:当使用中文列名时,可以不加引号.
                2:当使用的英文列名中当中有空格时,必须加上引号或者中括号.
                3:可以在一个表达式中同时使用上述的三种方法:
                  比如:select 职工号 as 职工编号,姓名 员工姓名,员工年龄=年龄 from work

B:where 子句的用法
    使用select语句可以对表中的列进行取舍,而不能对表中的行进行取舍,而使用where子句可以对表的行进行筛选
      比如:1:从work中检索出所有年龄大于25岁的职工.
             select * from work where 年龄>25
           2:从work中检索出所有年龄在20与40之间的职工.
             select * from work where 年龄>25 and 年龄<40
           3:从work中检索出学历是本科和大专的职工.
             select * from work where 学历=/'本科/' or 学历=/'大专/'
      说明:在where中可以运用关系运算符和逻辑运算符
              关系运算符: >,>=,<,<=,=,<>等.
              逻辑运算符:and or not
           比如:检索出学历是本科,大专,中专的职工.
             select * from work where not 学历=/'中专/'

C:使用in 或 not in,between  and
        in:表示位于集合里面.
        not in:表示不位于集合里面.
        between and:表示在两者之间 [格式:列 between 上界值 and 下界值]
       举例:
          1:检索出work表学历是大专,本科的职工.
             select * from work where 学历 in(/'大专/',/'本科/') [注意引号不能少]
          2:检索出职称经理,厂长,文员的职工. [假定职称只有:经理,厂长,文员,主管,秘书]          
             select * from work where 职称 not in(/'主管/',/'秘书/')
          3:检索出年龄在20到40之间的职工.
             select * from work where 年龄 between 20 and 40

D:使用like子句进行模糊查询.
  说明:like子句中的查询条件通过与通配符一起使用.sql server为我们提供了以下四种作用的通配符.
       A:%(百分号)表示0个或多个任意字符.
       B:_(下划线):表示单个的任意字符.
       C:[]表示方括号里面列出的任意一个字符.
       D:[^]表示任意一个没有在方括号里面列出的字符.
      举例:
          1:检索出姓张或姓李的人.
              select * from work where 姓名 like /'张%/' or 姓名 like /'王%/'
              或者:select * from work where 姓名 like /'[张王]%/'
          2:检索出姓名为两个字的职工.
              select * from work where 姓名 like /'__/'
          3:检索出姓名为三个字符且第二个字符为“小”的职工.
              select * from work where 姓名 like /'_小_/'
          4:检索姓名为二个字或三个字的职工.[假设没有姓名为四个字的职工]
              select * from work where 姓名 like /'__/' or 姓名 like /'___/'
              或者:select * from work where len(姓名 ) in (2,3)
          5:检索出姓为“张、黄、刘”的职工.
              select * from work where 姓名 like /'[张黄刘]%/'
          6:检索出既不姓“刘”与不姓“张”的职工.
              select * from work where 姓名 like /'[^张刘]%/'
          7:假设work表中有基本工资等级字段取值可以从A--Z的任意字符.要检索出工资等级为A到G之间的职工.
              1:select * from work where 基本工资等级=/'A/' or 基本工资等级=/'B/'...or 基本工资等级=/'G/'
              2:select * from work where 基本工资等级 in (/'A/',/'B/',/'C/'.../'G/')
              3:select * from work where 基本工资等级 like /'[A-G]/'
                说明:可以用方括号和连字符“-”来指定一个范围.
                再如:select * from work where 年龄 like /'2[1-5]/'
                     检索出年龄在21到25之间的职工.

E:转义字符的使用:
  使用通配符可能会产生这样的问题,万一查询的字符中包括了通配符作为字符串的一部分,该如何处理,transact-sql为我们提供的转义字符可以解决这个问题: 用关键字escape.
    用法:凡是遇到escape后第一个字符,表示通配符的字符仅作普通字符处理.
    举列:1:检索出转义字符表b列中包括“%”字符的所有记录
           select * from 转义字符 where b like %A%% escape /'A/'
         2:检索出转义字符表b中以“c]/'”结尾的记录
           select * from 转义字符 where b like /'%c]/'/'/'  
             说明:在sql中“/'/'”才算“/'”

F:用order by子句来排序.
  说明:可以用order by子句来为查询之后的结果来排序.需要注意的是:执行该查询之后,虽然显示在用户面前是排好序的列,但它不会改变原表的排列情况
  格式:order by 列名 asc|desc
  说明:1:对一个列指定排序次序时,默认为升序(asc),如果要特别指明降序必须指明desc关键字.
        比如:检索出所有职工,并按基本工资降序排序.
             select * from work order by 基本工资 desc  
      2:一般情况都是对一个列进行排序,也可对多个列进行排序,此时只写一个order by就行了,列与列之间用逗号隔开        
        比如:检索出所有职工,先按年龄降序排序,年龄相同再按基本工资升序排序.
             select * from work order by 年龄 desc,基本工资[asc]
        注意:select * from work order by 基本工资 desc,年龄[asc] 完全不一样.
      3:可以使用列的序号来进行排序列的指定.
        比如:select 职工号,姓名,年龄,基本工资 order by 4 desc [4:表示“基本工资”的位置]
      4:可以根据未曾出现在select列表中的值进行排列.
        比如:select 职工号,姓名,职称 from work order by 基本工资 [基本工资并没有出现在select中]
      5:如果有where子句则order by必须放在where的后面.
        比如:按基本工资的降序检索性别是男的所有职工.
             select * from work where 性别=/'男/' order by 基本工资 desc
             select * from work order by 基本工资 desc where 性别=/'男/' [错错错错]
      6:order by子句后面是排序的列,也可是计算列,支持别名.
        比如:检索出score表中所有同学的总分并按总分升序排序
             select 姓名,语文+数学+化学 as 总分 from score order by 总分




--  作者:hanzhufeng
--  发布时间:2004-8-29 10:17:25

--  
编辑维护表格数据
1:利用insert语句向表中插入新行.
2:利用update语句实现表中数据的修改.
3:利用delete语句实现表中数据的删除.

A:使用insert语句向表中添加数据
   格式:insert  表名(列名1,列名2....列名n) values(表达式1,表达式2...表达式n)
   比如:1:insert work(职工号,姓名,性别) values(/'0001/',/'陈小表/',/'男/')
          说明:如果表中没有被指定的列,其列值为null,也就是如果没有被指定列中有不能为空的列,则插入失败
        2:“值列表”必须和被指定的列有相同的个数.
        3:可以省略insert里面的/'列说明/',条件是必须为表中每一个列提供值.
          比如:insert work values(/'034/',/'陈立/',/'男/',23,/'文员/',/'经理/',1400)  不能少,否则会发生错误
        4:注意类型必须能够相互转化.
          比如:insert work(职工号,基本工资) values(/'001/',/'abc/')  因为/'abc/'无法与int型转换而出错
          上述语句一次只能插入一行语句,下面的语句一次可以一次性插入多行数据.
          格式:insert 目的表名(目的表列名1...目的表列名n)
               select 源表列名1...源表列名n from 源表名 [where 条件]
          比如:有一空的address表:职工号,姓名,婚否,住址,联系方式.现在要把work表中学历是大专或者本科的职工的职工号和姓名列的值全部都插入address表.
               insert address(职工号,姓名)
               select 职工号,姓名 from work where 学历 in(/'本科/',/'大专/')

B:利用update实现数据的修改
  格式:update 表名 set 列名1=表达式,列名2=表达式....列名n=表达式 [where 条件]
  比如:1:需要把work表学历是本科的基本工资提高1%
         update work set 基本工资=基本工资*1.01 where 学历=/'本科/'
       2:有一个录取表:姓名,性别,学历,所学专业,是否有工作经验,录取否
         要求把符合:学历是大专或大专以上的,并且所学专业与计算机相关的,有工作经验的人录取.
             update 录取 set 录取否=/'是/'
             where 学历 not in(/'高中/',/'中专/') and 所学专业 like /'%计算机%/' and 有无工作经验=/'有/'
         说明:1:如果不加条件会更新表中每一行,但只能更新一个表.
              2:可以更新一个表中的多个字段,字段与字段之间用逗号隔开.
              3:可以在update后面使用[from 表名]表示以其中一个表作为一个条件而去更新另一张表.
                举例:A:把work销售部的员工的基本工资加上100.[work,部门表]
                       update work set 基本工资=基本工资+100 from 部门
                       where 部门.部门编号=work.部门编号 and 部门名称=/'销售部/'
                     B:更新work表前5条记录,把基本工资加上100
                       update work set 基本工资=基本工资+100 from (select top 5 * from work) as temp  
                       where temp.职工号=work.职工号

C:使用delete删除表中的数据
  格式:delete 表名 where 条件 [注意:不要from]
  例如:删除work表职称是“经理”的职工
       delete work where 职称=/'经理/'
  说明:如果省略where条件则删除表中的所有记录
  例如:delete work [删除work表的全部记录]
       可以加上[from 表名 where 条件],这样是以后面一个表作条件来删除前面一个表相应的记录.
  比如:delete work from 部门
       where work.部门编号 not in(select 部门编号 from 部门)

 

SQLServer2000中的触发器使用

触发器是数据库应用中的重用工具,它的应用很广泛。这几天写一个化学数据统计方面的软件,需要根据采样,自动计算方差,在这里,我使用了触发器。

   下面我摘录了SQL Server官方教程中的一段关于触发器的文字,确实有用的一点文字描述。

   可以定义一个无论何时用INSERT语句向表中插入数据时都会执行的触发器。

   当触发INSERT触发器时,新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表,它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。

   日志记录了所有修改数据的动作(INSERT、UPDATE和DELETE语句),但在事务日志中的信息是不可读的。然而,inserted表允许你引用由INSERT语句引起的日志变化,这样就可以将插入数据与发生的变化进行比较,来验证它们或采取进一步的动作。也可以直接引用插入的数据,而不必将它们存储到变量中。

   示例

   在本例中,将创建一个触发器。无论何时订购产品(无论何时向Order Details表中插入一条记录),这个触发器都将更新Products表中的一列(UnitsInStock)。用原来的值减去订购的数量值即为新值。

USE Northwind
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = P.UnitsInStock – I.Quantity
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID

   DELETE触发器的工作过程

   当触发DELETE触发器后,从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表,它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。

   使用DELETE触发器时,需要考虑以下的事项和原则:

   ·当某行被添加到deleted表中时,它就不再存在于数据库表中;因此,deleted表和数据库表没有相同的行。

   ·创建deleted表时,空间是从内存中分配的。deleted表总是被存储在高速缓存中。

   ·为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句,原因在于日志不记录TRUNCATE TABLE语句。

   示例

   在本例中,将创建一个触发器,无论何时删除一个产品类别(即从Categories表中删除一条记录),该触发器都会更新Products表中的Discontinued列。所有受影响的产品都标记为1,标示不再使用这些产品了。

USE Northwind
CREATE TRIGGER Category_Delete
ON Categories
FOR DELETE
AS
UPDATE P SET Discontinued = 1
FROM Products AS P INNER JOIN deleted AS d
ON P.CategoryID = d.CategoryID

   UPDATE触发器的工作过程

   可将UPDATE语句看成两步操作:即捕获数据前像(before image)的DELETE语句,和捕获数据后像(after image)的INSERT语句。当在定义有触发器的表上执行UPDATE语句时,原始行(前像)被移入到deleted表,更新行(后像)被移入到inserted表。

   触发器检查deleted表和inserted表以及被更新的表,来确定是否更新了多行以及如何执行触发器动作。

   可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样,就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时,触发器就会进一步执行适当的动作,例如发出错误信息指出该列不能更新,或者根据新的更新的列值执行一系列的动作语句。

   语法

IF UPDATE (<column_name>)

   例1

   本例阻止用户修改Employees表中的EmployeeID列。

USE Northwind
GO
CREATE TRIGGER Employee_Update
ON Employees
FOR UPDATE
AS
IF UPDATE (EmployeeID)
BEGIN
RAISERROR ('Transaction cannot be processed./
***** Employee ID number cannot be modified.', 10, 1)
ROLLBACK TRANSACTION
END

   INSTEAD OF触发器的工作过程

   可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE),每一个表或视图只能有一个INSTEAD OF触发器。

   INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如,通常不能在一个基于连接的视图上进行DELETE操作。然而,可以编写一个INSTEAD OF DELETE触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中,就像AFTER触发器一样。相似地,在UPDATE INSTEAD OF触发器或者INSERT INSTEAD OF触发器中,你可以访问inserted表中的新行。

   不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。

示例

   在本例中,创建了一个德国客户表和一个墨西哥客户表。放置在视图上的INSTEAD OF触发器将把更新操作重新定向到适当的基表上。这时发生的插入是对CustomersGer表的插入而不是对视图的插入。

   创建两个包含客户数据的表:

 

SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country = 'Germany'
SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country = 'Mexico'

GO

   在该数据上创建视图:

CREATE VIEW CustomersView AS
SELECT * FROM CustomersGer
UNION
SELECT * FROM CustomersMex
GO

   创建一个在上述视图上的INSTEAD OF触发器:

CREATE TRIGGER Customers_Update2

ON CustomersView

INSTEAD OF UPDATE AS

DECLARE @Country nvarchar(15)

SET @Country = (SELECT Country FROM Inserted)

IF @Country = 'Germany'

BEGIN

UPDATE CustomersGer

SET CustomersGer.Phone = Inserted.Phone

FROM CustomersGer JOIN Inserted

ON CustomersGer.CustomerID = Inserted.CustomerID

END

ELSE

IF @Country = 'Mexico'

BEGIN

UPDATE CustomersMex

SET CustomersMex.Phone = Inserted.Phone

FROM CustomersMex JOIN Inserted

ON CustomersMex.CustomerID = Inserted.CustomerID

END

   通过更新视图,测试触发器:

UPDATE CustomersView SET Phone = ' 030-007xxxx'
WHERE CustomerID = 'ALFKI'

SELECT CustomerID, Phone FROM CustomersView
WHERE CustomerID = 'ALFKI'

SELECT CustomerID, Phone FROM CustomersGer
WHERE CustomerID = 'ALFKI'

 

 

 

关于索引

建立“适当”的索引是实现查询优化的首要前提。

索引(index)是除表之外另一重要的、用户定义的存储在物理介质上的数据结构。当根据索引码的值搜索数据时,索引提供了对数据的快速访问。事实上,没有索引,数据库也能根据SELECT语句成功地检索到结果,但随着表变得越来越大,使用“适当”的索引的效果就越来越明显。注意,在这句话中,我们用了“适当”这个词,这是因为,如果使用索引时不认真考虑其实现过程,索引既可以提高也会破坏数据库的工作性能。

(一)深入浅出理解索引结构

实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。

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

如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。

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

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

进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

(二)何时使用聚集索引或非聚集索引

下面的表总结了何时使用聚集索引或非聚集索引(很重要)。

动作描述

使用聚集索引

使用非聚集索引

列经常被分组排序

返回某范围内的数据

不应

一个或极少不同值

不应

不应

小数目的不同值

不应

大数目的不同值

不应

频繁更新的列

不应

外键列

主键列

频繁修改索引列

不应

事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

(三)结合实际,谈索引使用的误区

理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

1、主键就是聚集索引

这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。

显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。

在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。

通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。

在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

(1)仅在主键上建立聚集索引,并且不划分时间段:

Select gid,fariqi,neibuyonghu,title from tgongwen

用时:128470毫秒(即:128秒)

(2)在主键上建立聚集索引,在fariq上建立非聚集索引:

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时:53763毫秒(54秒)

(3)将聚合索引建立在日期列(fariqi)上:

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用时:2423毫秒(2秒)

虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。

得出以上速度的方法是:在各个select语句前加:declare @d datetime

set @d=getdate()

并在select语句后加:

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能显著提高查询速度

事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。

从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度

上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。

很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列)

(1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

查询速度:2513毫秒

(2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'

查询速度:2516毫秒

(3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'

查询速度:60280毫秒

从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

(四)其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快

下面是实例语句:(都是提取25万条数据)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用时间:3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用时间:4470毫秒

这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用时:12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用时:18843

这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用时:6343毫秒(提取100万条)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用时:3170毫秒(提取50万条)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

用时:3280毫秒

4 、日期列不会因为有分秒的输入而减慢查询速度

下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

用时:6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

用时:6453毫秒

(五)其他注意事项

“水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。

当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。

 

改善SQL语句

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

select * from table1 where name='zhangsan' and tID > 10000

和执行:

select * from table1 where tID > 10000 and name='zhangsan'

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

事实上,这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”,它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间,也就是说,它能实现自动优化。

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

在查询分析阶段,查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG),那么就称之为可优化的,并且可以利用索引快速获得所需数据。

SARG的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的AND连接。形式如下:

列名 操作符 <常数 或 变量>

<常数 或 变量> 操作符列名

列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:

Name=’张三’

价格>5000

5000<价格

Name=’张三’ and 价格>5000

如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。

介绍完SARG后,我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验:

1、Like语句是否属于SARG取决于所使用的通配符的类型

如:name like ‘张%’ ,这就属于SARG

而:name like ‘%张’ ,就不属于SARG。

原因是通配符%在字符串的开通使得索引无法使用。

2、or 会引起全表扫描

Name=’张三’ and 价格>5000 符号SARG,而:Name=’张三’ or 价格>5000 则不符合SARG。使用or会引起全表扫描。

3、非操作符、函数引起的不满足SARG形式的语句

不满足SARG形式的语句最典型的情况就是包括非操作符的语句,如:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等,另外还有函数。下面就是几个不满足SARG形式的例子:

ABS(价格)<5000

Name like ‘%三’

有些表达式,如:

WHERE 价格*2>5000

SQL SERVER也会认为是SARG,SQL SERVER会将此式转化为:

WHERE 价格>2500/2

但我们不推荐这样使用,因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。

4、IN 的作用相当与OR

语句:

Select * from table1 where tid in (2,3)

Select * from table1 where tid=2 or tid=3

是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。

5、尽量少用NOT

6、exists 和 in 的执行效率是一样的

很多资料上都显示说,exists要比in的执行效率要高,同时应尽可能的用not exists来代替not in。但事实上,我试验了一下,发现二者无论是前面带不带not,二者之间的执行效率都是一样的。因为涉及子查询,我们试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开。

(1)select title,price from titles where title_id in (select title_id from sales where qty>30)

该句的执行结果为:

表 'sales'。扫描计数 18,逻辑读 56 次,物理读 0 次,预读 0 次。

表 'titles'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

   

(2)select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

第二句的执行结果为:

表 'sales'。扫描计数 18,逻辑读 56 次,物理读 0 次,预读 0 次。

表 'titles'。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

我们从此可以看到用exists和用in的执行效率是一样的。

7、用函数charindex()和前面加通配符%的LIKE执行效率一样

前面,我们谈到,如果在LIKE前面加上通配符%,那么将会引起全表扫描,所以其执行效率是低下的。但有的资料介绍说,用函数charindex()来代替LIKE速度会有大的提升,经我试验,发现这种说明也是错误的:

select gid,title,fariqi,reader from tgongwen where charindex('刑侦支队',reader)>0 and fariqi>'2004-5-5'

用时:7秒,另外:扫描计数 4,逻辑读 7155 次,物理读 0 次,预读 0 次。

select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑侦支队' + '%' and fariqi>'2004-5-5'

用时:7秒,另外:扫描计数 4,逻辑读 7155 次,物理读 0 次,预读 0 次。

8、union并不绝对比or的执行效率高

我们前面已经谈到了在where子句中使用or会引起全表扫描,一般的,我所见过的资料都是推荐这里用union来代替or。事实证明,这种说法对于大部分都是适用的。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

用时:68秒。扫描计数 1,逻辑读 404008 次,物理读 283 次,预读 392163 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

用时:9秒。扫描计数 8,逻辑读 67489 次,物理读 216 次,预读 7499 次。

看来,用union在通常情况下比用or的效率要高的多。

但经过试验,笔者发现如果or两边的查询列是一样的话,那么用union则反倒和用or的执行速度差很多,虽然这里union扫描的是索引,而or扫描的是全表。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

用时:6423毫秒。扫描计数 2,逻辑读 14726 次,物理读 1 次,预读 7176 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where  fariqi='2004-2-5'

用时:11640毫秒。扫描计数 8,逻辑读 14806 次,物理读 108 次,预读 1144 次。

9、字段提取要按照“需多少、提多少”的原则,避免“select *”

我们来做一个试验:

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用时:4673毫秒

select top 10000 gid,fariqi,title from tgongwen order by gid desc

用时:1376毫秒

select top 10000 gid,fariqi from tgongwen order by gid desc

用时:80毫秒

由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。

10、count(*)不比count(字段)慢

某些资料上说:用*会统计所有列,显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看:

select count(*) from Tgongwen

用时:1500毫秒

select count(gid) from Tgongwen

用时:1483毫秒

select count(fariqi) from Tgongwen

用时:3140毫秒

select count(title) from Tgongwen

用时:52050毫秒

从以上可以看出,如果用count(*)和用count(主键)的速度是相当的,而count(*)却比其他任何除主键以外的字段汇总速度要快,而且字段越长,汇总的速度就越慢。我想,如果用count(*), SQL SERVER可能会自动查找最小字段来汇总的。当然,如果您直接写count(主键)将会来的更直接些。

11、order by按聚集索引列排序效率最高

我们来看:(gid是主键,fariqi是聚合索引列)

select top 10000 gid,fariqi,reader,title from tgongwen

用时:196 毫秒。 扫描计数 1,逻辑读 289 次,物理读 1 次,预读 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

用时:4720毫秒。 扫描计数 1,逻辑读 41956 次,物理读 0 次,预读 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用时:4736毫秒。 扫描计数 1,逻辑读 55350 次,物理读 10 次,预读 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

用时:173毫秒。 扫描计数 1,逻辑读 290 次,物理读 0 次,预读 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

用时:156毫秒。 扫描计数 1,逻辑读 289 次,物理读 0 次,预读 0 次。

从以上我们可以看出,不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的,但这些都比“order by 非聚集索引列”的查询速度是快得多的。

同时,按照某个字段进行排序的时候,无论是正序还是倒序,速度是基本相当的。

12、高效的TOP

事实上,在查询和提取超大容量的数据集时,影响数据库响应时间的最大因素不是数据查找,而是物理的I/0操作。如:

select top 10 * from (

select top 10000 gid,fariqi,title from tgongwen

where neibuyonghu='办公室'

order by gid desc) as a

order by gid asc

这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是10000条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用,发现TOP确实很好用,效率也很高。但这个词在另外一个大型数据库ORACLE中却没有,这不能说不是一个遗憾,虽然在ORACLE中可以用其他方法(如:rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中,我们就将用到TOP这个关键词。

 

到此为止,我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然,我们介绍的这些方法都是“软”方法,在实践中,我们还要考虑各种“硬”因素,如:网络性能、服务器的性能、操作系统的性能,甚至网卡、交换机等。

 

关于分页

建立一个web 应用,分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。但这种分页方法仅适用于较小数据量的情形,因为游标本身有缺点:游标是存放在内存中,很费内存。游标一建立,就将相关的记录锁住,直到取消游标。游标提供了对特定集合中逐行扫描的手段,一般使用游标来逐行遍历数据,根据取出数据条件的不同进行不同的操作。而对于多表和大表中定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等待甚至死机。

更重要的是,对于非常大的数据模型而言,分页检索时,如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据,而非检索所有的数据,然后单步执行当前行。

    最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标,由于游标的局限性,所以这个方法并没有得到大家的普遍认可。

后来,网上有人改造了此存储过程,下面的存储过程就是结合我们的办公自动化实例写的分页存储过程:

CREATE procedure pagination1

(@pagesize int,  --页面大小,如每页存储20条记录

@pageindex int   --当前页码

)

as

set nocount on

begin

declare @indextable table(id int identity(1,1),nid int)  --定义表变量

declare @PageLowerBound int  --定义此页的底码

declare @PageUpperBound int  --定义此页的顶码

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end

set nocount off

以上存储过程运用了SQL SERVER的最新技术――表变量。应该说这个存储过程也是一个非常优秀的分页存储过程。当然,在这个过程中,您也可以把其中的表变量写成临时表:CREATE TABLE #Temp。但很明显,在SQL SERVER中,用临时表是没有用表变量快的。所以笔者刚开始使用这个存储过程时,感觉非常的不错,速度也比原来的ADO的好。但后来,我又发现了比此方法更好的方法。

笔者曾在网上看到了一篇小短文《从数据表中取出第n条到第m条的记录的方法》,全文如下:

从publish 表中取出第 n 条到第 m 条的记录:
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
    (SELECT TOP n-1 id
     FROM publish))

id 为publish 表的关键字

我当时看到这篇文章的时候,真的是精神为之一振,觉得思路非常得好。等到后来,我在作办公自动化系统(ASP.NET+ C#+SQL SERVER)的时候,忽然想起了这篇文章,我想如果把这个语句改造一下,这就可能是一个非常好的分页存储过程。于是我就满网上找这篇文章,没想到,文章还没找到,却找到了一篇根据此语句写的一个分页存储过程,这个存储过程也是目前较为流行的一种分页存储过程,我很后悔没有争先把这段文字改造成存储过程:

CREATE PROCEDURE pagination2
(
 @SQL nVARCHAR(4000),    --不带排序语句的SQL语句
 @Page int,              --页码
 @RecsPerPage int,       --每页容纳的记录数
 @ID VARCHAR(255),       --需要排序的不重复的ID号
 @Sort VARCHAR(255)      --排序字段及规则
)
AS

DECLARE @Str nVARCHAR(4000)

SET @Str='SELECT   TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN
(SELECT   TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str
GO

其实,以上语句可以简化为:

SELECT TOP 页大小 *

FROM Table1

WHERE (ID NOT IN

          (SELECT TOP 页大小*页数 id

         FROM 表

         ORDER BY id))

ORDER BY ID

但这个存储过程有一个致命的缺点,就是它含有NOT IN字样。虽然我可以把它改造为:

SELECT TOP 页大小 *

FROM Table1

WHERE not exists

(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )

order by id

即,用not exists来代替not in,但我们前面已经谈过了,二者的执行效率实际上是没有区别的。

既便如此,用TOP 结合NOT IN的这个方法还是比用游标要来得快一些。

虽然用not exists并不能挽救上个存储过程的效率,但使用SQL SERVER中的TOP关键字却是一个非常明智的选择。因为分页优化的最终目的就是避免产生过大的记录集,而我们在前面也已经提到了TOP的优势,通过TOP 即可实现对数据量的控制。

在分页算法中,影响我们查询速度的关键因素有两点:TOP和NOT IN。TOP可以提高我们的查询速度,而NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就要彻底改造NOT IN,同其他方法来替代它。

我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,那么就可以利用这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以用操作符“>”或“<”号来完成这个使命,使查询语句符合SARG形式。如:

Select top 10 * from table1 where id>200

于是就有了如下分页方案:

select top 页大小 *

from table1

where id>

      (select max (id) from

      (select top ((页码-1)*页大小) id from table1 order by id) as T

       )    

  order by id

在选择即不重复值,又容易分辨大小的列时,我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表,在以GID(GID是主键,但并不是聚集索引。)为排序列、提取gid,fariqi,title字段,分别以第1、10、100、500、1000、1万、10万、25万、50万页为例,测试以上三种分页方案的执行速度:(单位:毫秒)

页  码

方案1

方案2

方案3

1

60

30

76

10

46

16

63

100

1076

720

130

500

540

12943

83

1000

17110

470

250

1万

24796

4500

140

10万

38326

42283

1553

25万

28140

128720

2330

50万

121686

127846

7168

从上表中,我们可以看出,三种存储过程在执行100页以下的分页命令时,都是可以信任的,速度都很好。但第一种方案在执行分页1000页以上后,速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势,后劲仍然很足。

在确定了第三种分页方案后,我们可以据此写一个存储过程。大家知道SQL SERVER的存储过程是事先编译好的SQL语句,它的执行效率要比通过WEB页面传来的SQL语句的执行效率要高。下面的存储过程不仅含有分页方案,还会根据页面传来的参数来确定是否进行数据总数统计。

-- 获取指定页的数据

CREATE PROCEDURE pagination3

@tblName   varchar(255),       -- 表名

@strGetFields varchar(1000) = '*',  -- 需要返回的列

@fldName varchar(255)='',      -- 排序的字段名

@PageSize   int = 10,          -- 页尺寸

@PageIndex  int = 1,           -- 页码

@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回

@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序

@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where)

AS

declare @strSQL   varchar(5000)       -- 主语句

declare @strTmp   varchar(110)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型

 

if @doCount != 0

  begin

    if @strWhere !=''

    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

    else

    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end 

--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况

else

begin

 

if @OrderType != 0

begin

    set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0,就执行降序,这句很重要!

end

else

begin

    set @strTmp = ">(select max"

    set @strOrder = " order by [" + @fldName +"] asc"

end

 

if @PageIndex = 1

begin

    if @strWhere != ''  

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder

     else

     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder

--如果是第一页就执行以上代码,这样会加快执行速度

end

else

begin

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

 

if @strWhere != ''

    set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["

        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

end  

exec (@strSQL)

GO

上面的这个存储过程是一个通用的存储过程,其注释已写在其中了。

在大数据量的情况下,特别是在查询最后几页的时候,查询时间一般不会超过9秒;而用其他存储过程,在实践中就会导致超时,所以这个存储过程非常适用于大容量数据库的查询。

笔者希望能够通过对以上存储过程的解析,能给大家带来一定的启示,并给工作带来一定的效率提升,同时希望同行提出更优秀的实时数据分页算法。

 

四、聚集索引的重要性和如何选择聚集索引

在上一节的标题中,笔者写的是:实现小数据量和海量数据的通用分页显示存储过程。这是因为在将本存储过程应用于“办公自动化”系统的实践中时,笔者发现这第三种存储过程在小数据量的情况下,有如下现象:

1、分页速度一般维持在1秒和3秒之间。

2、在查询最后一页时,速度一般为5秒至8秒,哪怕分页总数只有3页或30万页。

虽然在超大容量情况下,这个分页的实现过程是很快的,但在分前几页时,这个1-3秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢,借用户的话说就是“还没有ACCESS数据库速度快”,这个认识足以导致用户放弃使用您开发的系统。

笔者就此分析了一下,原来产生这种现象的症结是如此的简单,但又如此的重要:排序的字段不是聚集索引!

本篇文章的题目是:“查询优化及分页算法方案”。笔者只所以把“查询优化”和“分页算法”这两个联系不是很大的论题放在一起,就是因为二者都需要一个非常重要的东西――聚集索引。

在前面的讨论中我们已经提到了,聚集索引有两个最大的优势:

1、以最快的速度缩小查询范围。

2、以最快的速度进行字段排序。

第1条多用在查询优化时,而第2条多用在进行分页时的数据排序。

而聚集索引在每个表内又只能建立一个,这使得聚集索引显得更加的重要。聚集索引的挑选可以说是实现“查询优化”和“高效分页”的最关键因素。

但要既使聚集索引列既符合查询列的需要,又符合排序列的需要,这通常是一个矛盾。

笔者前面“索引”的讨论中,将fariqi,即用户发文日期作为了聚集索引的起始列,日期的精确度为“日”。这种作法的优点,前面已经提到了,在进行划时间段的快速查询中,比用ID主键列有很大的优势。

但在分页时,由于这个聚集索引列存在着重复记录,所以无法使用max或min来最为分页的参照物,进而无法实现更为高效的排序。而如果将ID主键列作为聚集索引,那么聚集索引除了用以排序之外,没有任何用处,实际上是浪费了聚集索引这个宝贵的资源。

为解决这个矛盾,笔者后来又添加了一个日期列,其默认值为getdate()。用户在写入记录时,这个列自动写入当时的时间,时间精确到毫秒。即使这样,为了避免可能性很小的重合,还要在此列上创建UNIQUE约束。将此日期列作为聚集索引列。

有了这个时间型聚集索引列之后,用户就既可以用这个列查找用户在插入数据时的某个时间段的查询,又可以作为唯一列来实现max或min,成为分页算法的参照物。

经过这样的优化,笔者发现,无论是大数据量的情况下还是小数据量的情况下,分页速度一般都是几十毫秒,甚至0毫秒。而用日期段缩小范围的查询速度比原来也没有任何迟钝。

聚集索引是如此的重要和珍贵,所以笔者总结了一下,一定要将聚集索引建立在:

1、您最频繁使用的、用以缩小查询范围的字段上;

2、您最频繁使用的、需要排序的字段上。

关于分页的问题:为什么参数(需要返回的列)中使用了as成中文后,第一次用没有问题,第二次调用的时候就找不到了?我还原成原数据库中的字段名后就没有问题了。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值