二.ALTER语句(修改)

   ALTER语句可以修改数据库和表大小,文件位置或其他特性。

  修改数据库的大小:

  图片 1

  修改表(增加一列)

  图片 2

其他参考:

区(extent)

extent是给表或索引分配存储空间的单位,也是管理空间的基本单位。
  在SQL Server中,extent的大小是固定的8个连续的数据页,64KB,这意味着
SQL Server 数据库中每 MB 有 16
个区。在创建文件组时,不能指定类似Oracle中的autoallocate或uniform
size子句定义extent的大小,在这方面,SQL Server的灵活性稍差一些。

图片 3

  SQL Server对表的分配extent的方式与Oracle不同。为了使空间分配有效,SQL
Server 不会将所有区分配给包含少量数据的表,所以SQL
Server不会对空表分配extent,extend的分配会延迟到对表添加记录时。
  SQL Server 有两种类型的区:

  • 混合区(mixed
    extent):混合区由多个表或索引共用,最多可由八个对象共享。
    区中八页的每页可由不同的对象所有。
  • 统一区(uniform extent):统一区由由单个对象所有。区中的所有 8
    页只能由一个表或索引专用。

通常对表或索引分配的前8个数据页会在混合区内分配,以后的数据页则在统一区内分配,这种方式与Oracle不同,Oracle的一个区只能分配给一个表或索引,不能多个对象共用,或者也可以说,Oracle只有SQL
Server中的统一区一种类型。

4.FILENAME

  实际的操作系统文件在磁盘的名字,如果不写默认放在安装SQL
Server的文件夹中,默认的数据库文件是.mdf后缀,日志是.ldf文件。

 

数据库文件

SQL Server 数据库具有三种类型的文件:

  • 主数据文件
    主数据文件是数据库的起点。除了存储系统以及用户数据以外,主数据文件还存储了数据库中的所有辅助数据文件以及重做日志文件的路径、名称、大小等信息。SQL
    Server通过读取主数据文件得到其他数据文件及重做日志文件的信息,这个功能与Oracle控制文件相似。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是
    .mdf。
  • 次要数据文件
    除主数据文件以外的所有其他数据文件都是次要数据文件,次数据文件一般只存储用户数据。某些数据库可能不含有任何次要数据文件,而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是
    .ndf。
  • 日志文件
    日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件,当然也可以有多个。日志文件的推荐文件扩展名是
    .ldf。

SQL Server 不强制使用 .mdf、.ndf 和 .ldf
文件扩展名,但使用它们有助于标识文件的各种类型和用途。
  在 SQL Server 中,数据库中所有文件的位置都记录在数据库的主文件和
master 数据库中。大多数情况下,SQL Server 数据库引擎使用 master
数据库中的文件位置信息。但是,在下列情况下,数据库引擎使用主文件的文件位置信息初始化
master 数据库中的文件位置项:

  • 使用带有 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 选项的 CREATE
    DATABASE 语句来附加数据库时。
  • 从 SQL Server 2000 版或 7.0 版升级时。
  • 还原 master 数据库时。

5.SIZE

  数据库大小,如果没写,默认与用图形创建的大小相同。

其中,database_name为新建数据库的逻辑名称,在一个SQL
Server上,必须保证各数据库名称是唯一的。
ON
子句显示指定存储数据库资料部分所使用的数据文件和文件组列表,PRIMARY关键词说明其后的数据文件属于主文件组。如果PRIMARY关键词未被指
定,则关键词CREATE
DATABASE后的第一个文件列表将成为主数据文件。<filespec>定义数据文件列表中各数据文件项,有多个数据文件项时,相互之间
以逗号分隔。
其中,logical_file_name参数指出数据文件的逻辑名称,数据文件的逻辑名称应用在Transact-SQL语句中。在同一个数据库中,必须保持数据文件的逻辑名称是唯一的。
os_file_name参数说明数据文件对应的操作系统文件名称,即数据文件的物理文件名称及其路径。
size
参数指定数据文件的初始长度,其单位为MB或KB,默认时为MB。对于主数据文件,其size参数的最小值应等于model数据库中主数据文件的长度。对
于其它数据文件,其长度最小为512KB。size参数默认时,对于辅数据文件和日志文件,SQL
Server将其长度设置为1MB,而对于主数据文件,SQL
Server将其长度设为model数据库中主数据文件的长度。
SQL
Server中,如果打开数据库的autoshrink选项,当数据库文件空间用尽时,系统将自动增加数据文件的大小。max_size参数定义数据文件
可以增加到的最大尺寸,其单位为MB或KB。如果未定义max_size参数,数据库文件的长度可根据需要一直增加,直到磁盘空间用尽为止。这时它等同于
MAXSIZE = UNLIMITED。
growth_increment参数说明数据文件空间的每次增加量,其单位为MB,KB或%,默认为
MB。使用%时说明数据文件每次增加的长度等于增加时文件现有长度的百分比,growth_increment参数的默认值为10%。用MB或KB表示
时,其增加值应为64KB或其倍数。
ON子句中的<filegroup>参数用于指出数据库的数据文件组,其中,filegroup_name为文件组名称。文件组中各文件的定义格式与上面介绍的数据文件的定义格式相同。
CREATE DATABASE语句中的LOG
ON子句用于定义数据库日志文件。各个日志文件的定义格式与数据文件相同。当未使用LOG
ON子句指定日志文件时,SQL
Server将自动为数据库建立一个日志文件,文件名称由系统产生,其长度等于数据库所有数据文件长度之和的25%。

页(data page)

SQL Server 中数据存储的基本单位是页。 为数据库中的数据文件(.mdf 或
.ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。 磁盘
I/O 操作在页级执行。 也就是说,页也是也是读写数据的单位。
  页是区段的分配单元。每一个区段包含8个页,每个页的大小固定为8KB,不能修改,这与Oracle数据库在创建表空间时可以指定数据库大小不同。

图片 4

  上图展示了数据是如何存放在页中的。对于插入的每一行,为了表明特定行的数据开始于页中的何处,每一页的末尾都用一小块空间记录的每一行相对于页头位置的偏移量。
  SQL Server 数据文件中的页按顺序编号,文件的首页以 0
开始。数据库中的每个文件都有一个唯一的文件 ID
号。若要唯一标识数据库中的页,需要同时使用文件 ID 和页码。

8.LOG ON   

  LOG ON 选项允许指定哪些文件需要日志,以及这些日志位于什么位置。

3)将数据库MYDB2中的文件组MYDB2_GROUP设置为默认文件组:
ALTER DATABASE MODIFY FILEGROUP MYDB2_GROUP DEFAULT
3.使用Transact-SQL语句删除数据库
  DROP DATABASE语句的语法格式为:
DROP DATABASE database_name [,…n]
其中,database_name为待删除的数据库名。在SQL
Server中只有系统管理员和数据库所有者才有删除数据库的权限。
(1)将以上建立的数据库MYDB2删除:
DROP DATABASE MYDB2
4.使用系统存储过程检索数据库的定义信息
SQL
Server提供了下列系统存储过程和语句,让用户检索服务器上的数据库定义信息以及每个数据库文件空间的使用情况:
?       
sp_helpdb:检索服务器上的所有数据库信息及单个数据库的定义信息;对应的语法格式为:sp_helpdb
[‘name’]
其中,name参数为选项,当提供该参数时,sp_helpdb检索name参数指定的数据库定义信息,否则它检索服务器上的所有数据库信息;
?       
sp_spaceused:检索数据库中资料空间的使用情况以及表所占用的空间;对应的语法格式为:sp_spaceused
[‘objname’] [,’updateusage’]
其中,objname是数据库中的表名,它要求sp_spaceusage显示系统分配给该表的空间及其使用状况。不指定objname参数时,系统存储过程sp_spaceused将统计当前数据库中的资料空间信息。
updateusage 参数说明是否在统计空间使用情况前执行DBCC
UPDATEUSAGE语句。默认时其值为false,即不执行DBCC
UPDATEUSAGE语句。将其值设置为true时,系统将对数据库执行DBCC
UPDATEUSAGE语句,这样所得到的空间使用信息将更为准确,但执行该语句要占用一定的时间,尤其是当数据库较大时,其执行时间会更长;
?        DBCC SQLPERF(LOGSPACE):检索数据库中的日志空间信息。

数据库文件组

为便于分配和管理,可以将数据库对象和文件一起分成文件组。SQL
Server的文件组由若干个数据文件组成。
  SQL
Server的文件组分为primary文件组和用户文件组,分别对应Oracle数据库中的system表空间和用户表空间。

  • primary文件组
    主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。与Oracle数据库的system表空间相似,primary文件组不能删除,其名称primary也是固定不能修改的。
  • 用户定义文件组
    用户定义文件组是通过在 CREATE DATABASE 或 ALTER DATABASE 语句中使用
    FILEGROUP 关键字指定的任何文件组。

日志文件不包括在文件组内。日志空间与数据空间分开管理。
  SQL Server数据库中没有对应于Oracle临时表空间的文件组,SQL
Server的多版本数据(undo)以及排序或散列操作所产生的临时数据都存储于tempdb系统数据库中,多个数据库共用tempdb数据库。

一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联。在这种情况下,它们的所有页将被分配到该文件组,或者对表和索引进行分区。已分区表和索引的数据被分割为单元,每个单元可以放置在数据库中的单独文件组中。
  在 SQL
Server数据库中,不允许删除包含表或索引的文件组,这与Oracle不同,在Oracle中,如果表空间中包含数据,使用drop
tablespace删除表空间时,可以附加including contents子句。
  每个数据库中均有一个文件组被指定为默认文件组。如果创建表或索引时未指定文件组,则将假定所有页都从默认文件组分配。一次只能有一个文件组作为默认文件组。如果没有指定默认文件组,则将主文件组作为默认文件组。db_owner
固定数据库角色成员可以将默认文件组从一个文件组切换到另一个。

文件和文件组的设计规则
下列规则适用于文件和文件组:

  • 一个文件或文件组不能由多个数据库使用。例如,任何其他数据库都不能使用包含
    sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。
  • 一个文件只能是一个文件组的成员。
  • 事务日志文件不能属于任何文件组。

四.使用管理界面来创建(推荐)

 

其中,UNIQUE、CLUSTERED和NONCLUSTERED指出所建立的索引类型,它们分别为唯一索引,簇索引和非簇索引。省略CLUSTERED和NONCLUSTERED选项时,SQL
Server所建立的为非簇索引。
index_name参数所建立的索引名称。在同一表中要保证索引名称的唯一性。
table和column说明被索引表及其列名,一个索引可以包含一列或多列(构成符合索引)。在建立索引时,不能对bit、text、ntext、image数据类型列和计算列建立索引。
FILLFACTOR
参数指出在创建索引时,每个索引页面的叶级填充度,它说明每次叶级索引页面填充多少时开始分页,从而在索引页面中保留一定的空间。索引页面中保留一定的空
间是非常有用的,它可以存储以后所插入新行的索引值,从而避免在每次插入新行时使系统都必须将原索引页面重新分页,从而提高系统的运行效率。
用户
定义的FILLFACTOR参数值为1到100(%),默认值为0。只有在不需要插入或修改资料的情况下,才将FILLFACTOR设为100。将
FILLFACTOR设为100时,在插入和修改操作时都回导致索引页的分页,从而大量占用系统时间。所以对于非只读表和数据库,应根据索引建立后需要插
入的资料量来估算FILLFACTOR 参数。
PAD_INDEX指出SQL Server在创建索引时,其内部节点页面是否也遵守FILLFACTOR
参数指定的填充度进行填充。所以,在使用PAD_INDEX选项时,必须同时设置FILLFACTOR
参数。默认时,SQL Server在索引节点上至少保留能够存储两个索引项的空间。
例如,对表spkfk建立索引,指定该索引的内部节点页面和叶级页面的填充度均为20%:
CREATE INDEX spkfk_index ON spkfk(spid)
WITH PAD_INDEX, FILLFACTOR = 20

立唯一索引后,每次使用INSERT语句或UPDATE语句向表中添加或修改资料时,系统将自动检查这些资料在索引列中是否存在重复键值。如果没有重复键
值,资料将被成功地添加或修改;否则,系统将根据是否设置了IGNORE_DUP_KEY索引选项,对所执行的语句做以下两种不同的处理:
(1)       
如果未设置IGNORE_DUP_KEY选项,系统将取消INSERT语句或UPDATE语句的执行。对于影响多行的INSERT语句或UPDATE语
句,在遇到重复的索引值时,所有已修改的行将被回滚,数据库中的其它修改(如索引页的改变等)也将被撤消,表中资料被恢复到语句执行前的状态。
(2)       
如果设置了IGNORE_DUP_KEY选项,当INSERT语句或UPDATE语句影响多行时,所插入的包含重复键值的资料行将被忽略,而修改时,所修改的行将被删除,而其它行则被正常插入或修改。
UPDATE语句的修改操作实际是先删除旧行,然后再插入新行。所以,如果所插入的行存在重复的键值,并且设置了IGNORE_DUP_KEY选项,那么SQL
Server将取消UPDATE语句对该行的插入操作,并导致被修改行的删除。
注意:只有当INSERT语句和UPDATE语句一次操作多行时,IGNORE_DUP_KEY选项才有意义。
例如,对表spkfjc中的spid和spbh列建立复合式非簇索引index_spid,并使用PAD_INDEX和FILLFACTOR参数要求索引文件的节点页面和叶级页面的填充度均为30%:
CREATE NONCLUSTERED INDEX index_spid
                ON spkfjc(name, phone)
                WITH PAD_INDEX, FILLFACTOR = 30
CREATE INDEX语句所创建的索引可以调用DROP INDEX语句删除,但是DROP
INDEX语句不能删除SQL Server为PRIMARY
KEY约束和UNIQUE约束所建立的索引,这些索引只能通过删除约束或删除表的方法删除。
DROP INDEX语句的语法格式为:
DROP INDEX ‘table.index’ [,…n]
其中,INDEX为待删除的索引名称,table为索引所属表名。
在同一个数据库中可能存在多个同名约束,但是同一个表中的索引是唯一的,所以,在DROP
INDEX语句中需要使用table参数限制索引所属表名。
例如,删除前面所建立的index_spid索引:
DROP INDEX spkfjc.index_spid
通过执行系统存储过程sp_helpindex能够检索资料表目前所建立的索引(或约束)的索引类型、存储位置和被索引列。sp_helpindex的语法格式为:
sp_helpindex [ @objname = ] ‘name
其中,name为当前数据库中的表名称’。

SQL Server的逻辑存储结构为文件组(file group)、区(extent)、数据页(data
page)。
  SQL Server
将数据库映射为一组操作系统文件。数据和日志信息绝不混合在同一个文件中,而且一个文件只由一个数据库使用。文件组是文件的命名集合,用于简化数据存放和管理任务(例如,备份和还原操作)。

三.DROP语句(删除)

图片 5

图片 6

 

SQL Server中的数据库文件组有以下三种类型:
?       
主文件组:其中包数据库的主数据文件和不属于其它文件组的数据库文件,数据库系统表的所有页面存储在主文件组中;
?        用户定义文件组:数据库创建语句(CREATE
DATABASE)或修改语句(ALTER
DATABASE)中使用FILEGROUP关键词所指定的文件组;
?       
默认文件组:在创建数据库对象时,如果没有为它们指定文件组,它们将被存储在默认文件组中。可以使用ALTER
DATABASE语句修改数据库的默认文件组设置,但每个数据库同时最多只能有一个默认文件组。当数据库没有指定默认文件组时,主文件组将被作为默认文件
组使用。
由于默认文件组的特殊作用,所以在创建数据库对象时,即使不指定用户文件组,SQL
Server也能照常执行。

管理SQL Server文件组及文件组

ALTER DATABASE database_name   
{  
    <add_or_modify_files>  
  | <add_or_modify_filegroups>  
}  
[;]  

<add_or_modify_files>::=  
{  
    ADD FILE <filespec> [ ,...n ]   
        [ TO FILEGROUP { filegroup_name } ]  
  | ADD LOG FILE <filespec> [ ,...n ]   
  | REMOVE FILE logical_file_name   
  | MODIFY FILE <filespec>  
}  

<filespec>::=   
(  
  NAME = logical_file_name
  [ , NEWNAME = new_logical_name ]
  [ , FILENAME ={'os_file_name'|'filestream_path'|'memory_optimized_data_path'}]
  [ , SIZE = size [ KB | MB | GB | TB ] ]
  [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
  [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
  [ , OFFLINE ]
)

<add_or_modify_filegroups>::=  
{  
    | ADD FILEGROUP filegroup_name   
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
    | REMOVE FILEGROUP filegroup_name   
    | MODIFY FILEGROUP filegroup_name  
        { <filegroup_updatability_option>  
        | DEFAULT  
        | NAME = new_filegroup_name   
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
        }  
}  
<filegroup_updatability_option>::=  
{  
    { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }  
}  
  • <add_or_modify_files>::=</add_or_modify_files>:指定要添加、删除或修改的文件。
  • database_name:要修改的数据库的名称。
  • ADD FILE:向数据库中添加文件。
  • TO FILEGROUP { filegroup_name }:指定要将指定文件添加到的文件组。
  • ADD LOG FILE:将要添加的日志文件添加到指定的数据库。
  • REMOVE FILE logical_file_name:从 SQL Server
    的实例中删除逻辑文件说明并删除物理文件。
    除非文件为空,否则无法删除文件。
  • logical_file_name:在 SQL Server 中引用文件时所用的逻辑名称。
  • MODIFY FILE:指定应修改的文件。 如果指定了
    SIZE,那么新大小必须比文件当前大小要大。
    若要修改数据文件或日志文件的逻辑名称,请在 NAME
    子句中指定要重命名的逻辑文件名称,并在 NEWNAME
    子句中指定文件的新逻辑名称。 例如:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 

若要将数据文件或日志文件移至新位置,请在 NAME
子句中指定当前的逻辑文件名称,并在 FILENAME
子句中指定新路径和操作系统(物理)文件名称。 例如:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ')
  • { ‘os_file_name’ | ‘filestream_path’ |
    ‘memory_optimized_data_path’}
  • os_file_name:对于标准 (ROWS)
    文件组,这是在创建文件时操作系统所使用的路径和文件名。
  • ‘ filestream_path ‘:对于 FILESTREAM 文件组,FILENAME 指向将存储
    FILESTREAM 数据的路径。
  • memory_optimized_data_path:对于内存优化文件组,FILENAME
    会引用将存储内存优化数据的路径。SIZE、MAXSIZE 和 FILEGROWTH
    属性不适用于内存优化文件组。
  • FILEGROWTH:用于指定每次文件增长大小,如果未指定确定的值,则默认为1MB,如果指定为0,则数据文件不能自动增长。可以使用MB、KB、GB、TB或百分比(%)为单位,默认值为MB。如果指定%,则增量大小为发生增长时文件大小的指定百分比。指定的大小舍入为最接近64KB的倍数。
  • OFFLINE:将文件设置为脱机并使文件组中的所有对象都不可访问。
  • <add_or_modify_filegroups>::=</add_or_modify_filegroups>:在数据库中添加、修改或删除文件组。
  • CONTAINS FILESTREAM:指定文件组在文件系统中存储 FILESTREAM
    二进制大型对象 (BLOB)。
  • CONTAINS
    MEMORY_OPTIMIZED_DATA:指定文件组在文件系统中存储内存优化数据。每个数据库只能有一个
    MEMORY_OPTIMIZED_DATA 文件组。
    在创建内存优化表时,文件组不能为空,其中必须至少包含一个文件。
  • REMOVE FILEGROUP
    filegroup_name:删除文件组filegroup_name从数据库中删除文件组。
    除非文件组为空,否则无法将其删除。 首先从文件组中删除所有文件。
  • MODIFY FILEGROUP filegroup_name:修改文件组。
  • DEFAULT:更改默认的数据库文件组到filegroup_name。
    数据库中只能有一个文件组作为默认文件组。
  • AUTOGROW_SINGLE_FILE:在文件组中的文件符合自动增长阈值时,仅该文件是增长。
    这是默认设置。
  • AUTOGROW_ALL_FILES:如果文件组中的文件达到了自动增长阈值,文件组中的所有文件都增长。
  • <filegroup_updatability_option>:对文件组设置只读或读/写属性。
  • READ_ONLY | READONLY:指定文件组为只读。 不允许更新其中的对象。
    主文件组不能设置为只读。
    若要更改此状态,您必须对数据库有独占访问权限。
  • 因为只读数据库不允许数据修改,所以将发生以下情况:
    系统启动时,将跳过自动恢复。
    不能收缩数据库。
    在只读数据库中不会进行锁定。 这可以加快查询速度。

【示例】

A. 向数据库中添加由两个文件组成的文件组
  以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个
5 MB 的文件添加到该文件组。

USE master  
ALTER DATABASE AdventureWorks2012  
ADD FILEGROUP Test1FG1;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1dat4,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAt1dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP Test1FG1;  
GO  

B.向数据库中添加两个日志文件

USE master;  
ALTER DATABASE AdventureWorks2012   
ADD LOG FILE   
(  
    NAME = test1log2,  
    FILENAME = 'D:Microsoft SQL ServerMSSQLDATAtest2log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1log3,  
    FILENAME = 'D:Microsoft SQL ServerDATAtest3log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO  

C.从数据库中删除文件

USE master;  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE test1dat4;  
GO  

D.修改文件
  以下示例添加的一个文件的大小。ALTER DATABASE MODIFY FILE
命令与可以使文件大小更大,因此如果你需要使文件大小更小你需要使用 DBCC
SHRINKFILE。

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO  

此示例中收缩数据文件的大小为 100 MB,然后指定在该数量的大小。

USE AdventureWorks2012;
DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

E.将文件移至新位置
  下面以把AdventureWorks数据中的数据文件E:t1dat2.ndf移动到C:t1dat2.ndf为例,说明移动数据文件的过程。
首先把数据库脱机:

alter database AdventureWorks set offline

在操作系统中把E:t1dat2.ndf移动到C:t1dat2.ndf:

!! move E:t1dat2.ndf C:t1dat2.ndf

修改数据库中对此文件路径的记载:

ALTER DATABASE AdventureWorks 
MODIFY FILE  
(  
    NAME = Test1dat2,  
    FILENAME = N'C:t1dat2.ndf'  
);  
GO  

最后再把数据库重新联机:

alter database AdventureWorks set online

然后查询t1dat2的物理文件路径:

select name,physical_name from sys.database_files where name ='C:t1dat2.ndf'

F.使文件组成为默认文件组
  下面的示例使Test1FG1成为默认文件组。 然后,默认文件组被重置为 PRIMARY
文件组。 请注意,必须使用括号或引号分隔 PRIMARY。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP Test1FG1 DEFAULT;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP [PRIMARY] DEFAULT;  
GO  

2.ON

  ON用于两个地方,第一个是存储数据的文件的位置,第二个是存储日志的文件的位置。
ON 后面的 PRIMARY的概念:希望将所有的内容存放在一个文件里。

图片 7ALTER DATABASE MYDB2
图片 8        ADD LOG FILE
图片 9  (NAME = MYDB2_LOG2,
图片 10    FILENAME = ‘c:mssql7dataMYDB2_LOG2.ldf’, 
图片 11         SIZE = 10MB,
图片 12    MAXSIZE = 50MB,
图片 13    FILEGROWTH = 2MB
图片 14)
图片 15

查询指定表被分配的extent信息

在SQL Server可以使用dbcc extentinfo命令查询表被分配的extent信息。

dbcc extentinfo(数据库名,表名)

9.COLLATE

  该选项处理排序,字母大小,以及是否对重音敏感的问题。

 

 

图片 16图片 17

 

在建立一个表后,在使用过程中经常会发现原来创建的表可能存在结构、约束等方面的问题。在这种情况下,如果用一个新表替换原来的表,将造成表中资料
的丢失。使用ALTER
TABLE语句可以在保留表中原有资料的基础上修改表结构,打开、关闭或删除已有的约束,或增加新的约束。
其中,table_name参数说明所修改的表名。
WITH CHECK 和WITH
NOCHECK选项说明向表中添加新的或打开表中原有的CHECK约束和FOREIGN
KEY约束时,是否对表中已有资料进行约束检查。使用WITH
NOCHECK选项可以禁止对表中已有数据进行约束检查,但该选项对新添加的数据无效,新插入的资料必须接受约束检查。
ALTER
COLUMN子句说明修改表中column_name参数所指定列定义,它可以改变列数据类型和空值设置,添加或删除ROWGUIDCOL属性。但下列类型不能被修改:
?        text、ntext、image、timestamp列;
?        计算列或用于计算的常规列;
?        复制列;
?       
用于索引列,但如果这些列为varchar或varbinary数据类型,可以增加它们的列长度;
?        用在CHECK、FOREIGN KEY、UNIQUE或PRIMARY
KEY约束中的列,但如果这些列为变长列,可以使用ALTER
TABLE语句改变这些列的定义长度;
?        关联有默认值的列。
使用ALTER
COLUMN子句修改列数据类型时,new_data_type参数必须符合以下条件:
?        原数据类型必须能够转换为新的数据类型;
?        修改后的数据类型不能为timestamp;
?        ANSI null default选项是打开的或被修改列允许空值;
注意:如果表中的对应列原定义为NULL并列中不存在资料时,将此列更改为NOT
      NULL时是允许的。
?        ANSI_PADDING选项是打开的;
?        对于被修改的IDENTITY列,必须具有有效的IDENTITY数据类型。
ADD子句说明向表中添加新列,新列的定义方法与CREATE
TABLE语句中的相同,包括列名、数据类型、约束条件等。
注意:在ALTER
TABLE语句中,对于新增加的列,必须允许空值,或关联一个默认值。
      无论此列原定义是否为NULL或此列中是否存在资料。
DROP { [CONSTRAINT] constraint | COLUMN column
}子句说明从表中删除指定约束或列。一个表中的下列类型不能被删除:
?        复制列;
?        用于索引列;
?        用于CHECK、FOREIGN KEY、UNIQUE或 PRIMARY KEY约束中的列;
?        定义有默认值或关联有默认对象的列;
?        关联有规则的列。
      { CHECK | NOCHECK } CONSTRAINT { ALL | constraint
}子句说明打开或关闭表中所有或constraint参数指定的FOREIGN
KEY和CHECK约束。当使用NOCHECK
CONSTRAINT关闭约束时,之后所插入到表中的资料不再接受该约束检查。在打开或关闭表中约束时,可以使用WITH
CHECK或WITH NOCHECK子句对表中的资料进行(或不进行)检查。
3.使用Transact-SQL语句删除资料表
  DROP TABLE 语句的语法格式为:
  DROP TABLE table_name
   其中,table_name为待删除表的名称。

删除一个表时,表之定义和表中的所有数据、以及该表的索引、许可设置、约束、触发器等均被自动删除,与该表相关联的规则和默认对象失去与它的关联关系。但
是,使用DROP TABLE语句不能删除SQL Server系统表和被FOREIGN
KEY约束所参照的用户表。

7.FILEGROWTH

  提供一个值来说明文件每次增大多少字节或者多少百分比。

图片 18CREATE TABLE
图片 19[
图片 20    database_name.[owner].
图片 21    | owner.
图片 22] table_name
图片 23(
图片 24    {    <column_definition>
图片 25        | column_name AS computed_column_expression
图片 26        | <table_constraint>
图片 27    } [,图片 28n]
图片 29)
图片 30[ON {filegroup | DEFAULT} ]
图片 31[TEXTIMAGE_ON {filegroup | DEFAULT} ]
图片 32

一.CREATE语句(创建)

 

6.MAXSIZE  

  允许数据库的最大大小。

对于其它数据类型列,其空值属性则由连接选项ANSI_NULL_DFLT_ON和数据库选项‘ANSI
null default’决定。
说明:①通过设置连接选项SET ANSI_NULL_DFLT_ON
ON|OFF对列的默认空值属性进行切换;
      ②通过设置数据库选项sp_dboption ‘database’,‘ANSI null
default’,‘true’或sp_dboption ‘database’,‘ANSI null default’,‘false’
对列的默认空值属性进行切换。

1.创建DataBase

图片 33

 

1.CONTAINMENT

  SQL Server 2012 新功能 , 默认值是OFF 。(太高级
书上也没有详细介绍)。

在SQL Server中建立文件和文件组时,应注意以下两点:
?       
每个文件或文件组只能属于一个数据库,每个文件也只能成为一个文件组的成员,文件和文件组不能跨数据库使用;
?       
日志文件是独立的,它不能成为文件组的成员。也就是说,数据库的资料内容和日志内容不能存入相同的文件或文件组。

2.创建表

图片 34

 

f.基于多个条件选择查询结果。
       
   在WHERE语句中,可以用逻辑运算符来连接多个条件,构成一个复杂条件  
           进行查询。主要有三个逻辑运算符:
           1 AND
:它连接两个条件,如果两个条件都成立,则组合起来的条件成立。
           2
OR  :它连接两个条件,如果其中一个条件成立,则组合条件成立。
           3 NOT :它引出一个条件,将该条件的值取反。         
             查询没有发生在’2002-04-01′ 和 ‘2002-05-22’之间的资料
             select *  from cwk where rq not between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
             查询发生在’2002-04-01′ 和 ‘2002-05-22’之间的资料
             select  * from cwk  where rq  between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
             查询发生在小于等于’2002-04-01′ 和大于等于
‘2002-05-22’的资料
              select * from cwk
                where  rq<=’2002-04-01′ or rq>=’2002-05-22′
   g.查询结果排序
     格式:
       SELECT select_list
              FROM   talbe_source
              WHERE  search_condition
              ORDER BY order_expression [ASC  | DESC]
         
其中,order_expression是排序依据的列名,可以有多个列名。ASC按升      
          序排序,DESC按降序排序,默认为ASC
     查询满足WHERE条件的资料,按降序排列。  
          select  * from cwk  where rq  between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
                order by rq desc
 (3)资料分组(northwind数据库)
      a. GROUP BY
       
 利用GROUP BY可以按一定的条件对查询到的结果进行分组,再对每一组数   
         据计算统计。
     格式:SELECT select_list
               FROM table_source
               WHERE search_condition
               GROUP BY group_by_expression
          比如:查看每一种卖出商品的总数.
        select spid,sum(shl) from ywmxk
            group by spid
   b.HAVING
    
   HAVING子句用来向使用GROUP BY子句的查询中添加过滤准则,语法与WHERE 
 
        一样,但WHERE是针对单个行而言,HAVING是针对一组而言。
    区别:1、查询时,先滤掉不满足WHERE中条件的记录,而HAVING子句在分
                 组之后使用。
       2、HAVING可以在子句中包含聚合函数,但WHERE 不行。      
          比如:查看每一种卖出商品的总数,卖出数量大于30.
          select spid,sum(shl) as shl from ywmxk
                group by spid
                having sum(shl)>30
      c.COMPUTE 与COMPUTE BY
        COMPUTE子句可以用来计算汇总资料。
    比如:查看日期1994/09/13以后卖出商品的总数,
         select spid,shl as shl from ywmxk  where djbh like
‘jha%’
                compute sum(shl)
        COMPUTE BY可以按给定的条件将查询结果分组,并为每组计算汇总资料 
    
             select spid,shl from ywmxk where djbh like ‘jha%’
                     order by spid
                     compute sum(shl) by spid   
    若使用了COMPUTE BY,则必须使用ORDER BY,并且COMPUTE BY后面出现 
         
        的列的顺序必须与ORDER BY后出现的顺序相同。
 (4)T_SQL中的子查询
    子查询有两种类型:简单型和关联型。
          
 子查询是指SELECT子句嵌在另一个T-SQL语句中,一般情况下,子查      
    
   询用于另一个SELECT,INSERT,UPDATE或者DELETE语句中的WHERE或HAVING  
        短语中。
   a.简单子查询
    例如:查询销售商品数量大于100的销售出库单信息
select djbh,hsje from cwk where  djbh in
(select djbh as shl from ywmxk where djbh like ‘xsa%’
group by djbh  having sum(shl)>100 ))
      b.联结和子查询
         例如:查询销售单据信息同时显示出单位名称
           select a.djbh,a.hsje,b.dwmch from cwk a join mchk b  
              on a.dwbh=b.dwbh
               where a.djbh like ‘xsa%’
    
   在大多情况下,在SELECT语句中的联结比一个等效子查询便容易,效率高,我 
    
      们应尽可能地使用联结来代替子查询以取得更好的性能。
      c.为什么有时必须使用子查询。
    例如:select a.djbh,a.hsje,b.dwmch from cwk a join mchk b  
              on a.dwbh=b.dwbh where a.djbh like ‘xsa%’   
             and  a.hsje<(select avg(hsje) from cwk where
a.djbh  like ‘xsa%’)
       因为我们不能比较聚合值--avg(hsje),以及非聚合值hsje.
  
   (6)外联结,交叉联结和自联结。
   a外联结:
对于绝大多数的查询执行,当用户在多个表检索资料时标准的内部   
             
 联结就足够了。但有些情况是当用户要检索的信息在两个表中没有公  
               共资料时,使用外联结和交叉联结是很有用的。
    外联结的类型:左联结,右联结,全外联结。
           左联结和右联结的不同之处仅在于FROM短语中表排列次序不同。
全外联结是同时使用左联接和右联接。
   例1 使用左联结选择所有商品及其销量。如果没有卖出我们也想看一看。
       select a.spid,a.spmch,isnull(sum(b.shl),0) as shl
from spkfk a left join ywmxk b on a.spid=b.spid
               where b.djbh like ‘xsa%’
               group by a.spid,a.spmch
          如果使用内联结没有卖出的商品的信息就不会被显示出来,
     使用isnull函数的目的:把NULL变为0。ISNULL(check_expression, 
 
               replacement_value).
      例2 只选择没有卖出的商品名称。
             select a.spid,a.spmch,isnull(sum(b.shl),0) as shl
from spkfk a left join ywmxk b on a.spid=b.spid
                where b.djbh like ‘xsa%’ and b.spid is null
                group by a.spid,a.spmch

3.NAME

  一个逻辑名称,即SQL
Server在内部使用该名称引用该文件。当需要修改数据库大小时,需要使用这个名称

4、        规则的应用
在建立规则后,应将它们关联到列或用户定义数据类型才能使它们发挥作用,执行系统存储过程sp_bindrule建立规则关联。在用输入或修改数据时激活规则,系统将自动检查列值是否在规则指定的范围内、或是否与规则指定的数据格式相匹配。
系统存储过程sp_bindrule语法格式为:
sp_bindrule [@rulename] ‘rule_name’,[@objname=] ‘object_name’
   说明:rule_name为规则名称
    
   object_name为“表名.列名”格式时,说明它为一列名,否则,sp_bindrule将
object_name参数视作用户定义数据类型。
    例如:在SHIKONG数据库中建立一个shl_rule规则,然后关联到SPKFK表中的shl列
      CREATE RULE shl_rule
               AS @shl>0
            Sp_bindrule shl_rule,’spkfk.shl’
5、        解除规则
在删除规则前必须执行系统存储过程sp_unbindrule解除规则与列或用户定义数据库类型之间的关联.sp_unbindrule语句的格式为:
sp_unbindrule [@rulename] ‘rule_name’,[@objname=] ‘object_name’
解除关联后,规则仍存储在当前数据库中,这时可执行DROP RULE语句将它删除。在DROP RULE语句的语法格式为:
语法:DROP RULE {rule_name}[,…n]
在一个DROP RULE语句中,可以一次同时删除多个规则,但必须保证这些规则没有相关联的列或用户定义数据类型,否则,系统将取消DROP RULE语句的执行,并返回一条错误信息。
五、默认
 
  默认对象所执行的功能与默认值约束完全一样。但默认值约束是在CREATE TABLE或ALTER TABLE时定义,它与表定义存储在一起,所
以,删除表时,默认值约束被自动删除。而默认对象则需要使用CREATE DEFAULT语句定义,它作为一种数据对象单独存储,所以它可以被多次应用于
不同列。在删除表时不能删除默认对象,而需要使用DROP DEFAULT语句删除。
1、        创建默认对象
CREATE DEFAULT [owner.]default_name
AS  constant_expression
2、        说明:default_name为所建立的默认对象名称。
 
   Constant_expression参数为默认对象所提供数据。它可以是由常量、SQL
Server内置函数、算术表达式和全局变量等所组成的常量表达式,但是不能包含任何列名或其它数据库对象。在Constant_expression表
达式中,对于字符和日期类型数据应引在单引号内,二进制数据必须以0X作前导符,货币类型以美元符号$开头,对于其它数据类型(如整数和浮点数等)则不需
要定界符。
    CREATE DEFAULT语句只能在当前数据库中创建默认对象。对每个用户来说,他在同一个数据库中所创建的默认对象名称必须保持唯一。
    例如:下面语句分别建立一个字符、日期、二进制、货币和整数等数据类型的默认对象
    CREATE DEFAULT DF_char AS ‘UNKNOWN’
        GO
        CREATE DEFAULT DF_date AS ‘Dec 12 2002 6:00 AM’
        GO
        CREATE DEFAULT DF_binary AS 0Xffffffffff
        GO
CREATE DEFAULT DF_money AS $1000
GO
CREATE DEFAULT DF_int AS 0
GO
3、默认的应用
    在创建默认后,必须将它与列或用户定义数据类型关联起来才能使之发挥作用。执行系统存储过程sp_bindefault在默认对象和列或默认对象和用户定义数据类型间建立关联。Sp_bindefault
的语法格式:
    sp_bindefault [@defname] ‘default_name’
             [@objname=] ‘object_name’
             [,[@futureonly=] ‘futureonly_flag’]
   说明:default_name为所关联的默认对象名称
        object_name为默认对象所关联到的列名或用户定义数据类型名称。
    
   Sp_bindefault只能将默认与当前数据库表中的列或用户定义的数据类型相关联,在关联时,默认对象的数据类型必须与其所关联的列的数据类
型相同或兼容。在未解除已有默认关联之前,可以将一个新的默认对象关联到该列,这时,旧的关联将自动解除,只有最近一次关联的默认对象起作用。
4、删除默认对象 
      
在删除默认对象时,首先要执行系统存储过程sp_unbindefault解除默认对象与列和用户定义数据类型之间的关联,然后才能执行DROP DEFAULT语句删除默认对象,sp_unbindefault的语法格式:
    sp_unbindefault [@objname=] ‘object_name’
              [,[@futureonly]=] ‘futureonly_flag’
      
解除默认对象的关联后,该对象仍存在于当前数据库中,这时可执行DROP DEFAULT语句将其删除。DROP DEFAULT语句的语法格式为:
    DROP DEFAULT {default_name} [,…n] 
六、索  引
如 果对一个未建立索引的表执行查询操作,SQL
Server将逐行扫描表数据页面中的资料行,并从中挑出符合条件的资料行。当一个表中有很多行时,执行一次查询将耗费大量的时间。然而,在建立索引
后,SQL Server则根据索引指示,直接定位到所要查找的资料行,从而加快SQL
Server的资料检索操作。此外,索引还能够加快ORDER BY和GROUP
BY子句的执行、强制实施行的唯一性。
SQL Server中的索引类型包括以下几种:
?        唯一索引;
?        簇索引;
?        非簇索引。
1.        唯一索引
唯一索引要求所有资料行中的任意两行中的被索引列不能存在重复值(包括空值NULL)。有以下两种方法建立唯一索引:
?        在CREATE TABLE或ALTER TABLE语句中设置列级或表级PRIMARY
KEY约束或UNIQUE约束时,SQL Server自动为这些约束建立唯一索引;
?        在CREATE INDEX语句中使用UNIQUE选项创建唯一索引。
使用CREATE
INDEX语句对一个已存在的表创建唯一索引时,系统首先检查表中已有数据,如果被索引列存在重复键值,系统将停止建立索引。在这种情况下,只有删除已存在的重复行后,才能对这些列建立唯一索引。
表创建唯一索引后,SQL
Server将禁止INSERT语句或UPDATE语句向表中添加重复的键值行。

 IDENTITY

  设定一个列为标识列,开始计数的数字称为种子值,每行增加或减少的数量称为增量。

  标识列必须是数值类型。

 图片 35

图片 36ALTER DATABASE database
图片 37{    ADD FILE <filespec> [,图片 38n] [TO FILEGROUP filegroup_name]
图片 39    | ADD LOG FILE <filespec> [,图片 40n]
图片 41    | REMOVE FILE logical_file_name 
图片 42    | ADD FILEGROUP filegroup_name
图片 43    | REMOVE FILEGROUP filegroup_name
图片 44    | MODIFY FILE <filespec>
图片 45    | MODIFY FILEGROUP filegroup_name filegroup_property
图片 46}
图片 47<filespec> ::=
图片 48(NAME = logical_file_name
图片 49  [, FILENAME = ‘os_file_name’ ]
图片 50  [, SIZE = size]
图片 51  [, MAXSIZE = { max_size | UNLIMITED } ]
图片 52  [, FILEGROWTH = growth_increment] )
图片 53

十、函数数据整理
ABS:ABS函数返回数值表达的绝对值
语法:ABS(numeric_expression)可以是整型、短整型、微短整型、小数、浮点数、货币、实型或小货币型数。
ACOS:ACOS函数是一个数学函数,它返回以弧度表示的角度,即:ARCCOS。
语法:ACOS(float_expression)变量是浮点数数据类型,表示角度的余弦。
ASIN:反正弦函数。
SIN:它返回表达式中的正弦值。
语法:SIN(        float_expression)float_expression是浮点型的资料
COS:它返回表达式中的余弦值。
语法:COS(       
float_expression)float_expression是浮点型的资料。
TAN:它返加表达式的正切值。
COT:它返回表达式中的余切值。
ATAN:反正切函数。
SIGN:如果表达式为正,SIGN函数返回1,如果表达为负,SIGN返回-1,如果表达式为0,则函数返回0。
CEILING:返回不小于给定的数值表达式的最小整数。
语法:CEILING(numeric_expression)小数、浮点、整数、实数、货币等类型。
例如:SELECT CEILING(1.45)返回值为2
      SELECT CEILING (-1.45)返回值为-1
FLOOR:返回不大于给定的数值表达式的最大整数。
语法:FLOOR(numeric_expression)小数、浮点、整数、实数、货币等类型。
例如:SELECT FLOOR(1.45)返回值为1
          SELECT FLOOR (-1.45)返回值为-2
ASCII:ASCII函数返回整数,表示字符表达式最左边的字符的ASCII代码值。
语法:ASCII(character_expression)
      例:ASCII(‘AB’)返回65。
CHAR:字符串函数,用于将ASCII码整数转换为字符。
语法:CHAR(integer_expression)变量是0~255间的正整数。
例如:CHAR(65)返回‘A’
CHARINDEX:字符串函数,返回想要的查找字符串,在目标字符中的起始位置。
语法:CHARINDEX(‘PATTERN’,EXPRESSION[,START_LOCATION]);
例如:CHARINDE(‘A’,‘BCDEA’,2)返回值为5,其2为从第几个字符进行搜索不写默认为从第一个位置。
AVG:AVG是个集合函数,它计算数值列的平均值,忽略空值,如果使用了DISTINCT则对重复的值只取一次进行平均。
语法;AVG([ALL|DISTINCT] expression)
      ALL是缺省值。
例如:SELECT DISTINCT(AGE)FROM TABLE1
如果任意行有NULL值,则此行被忽略。
COUNT:是一个集合函数,它返回的整数代表表达式中值的个数通为一个列,DISTINCT和COUNT一起使用时,返回单一值的个数。
    语法:COUNT({[ALL|DISTINCT] EXPRESSION |*})
    例如:SELECT COUNT(COLUMN1)FROM TABLE1,
         
返回COLUMN1中没有空值的行数,如果是使用*将返回表中所有行的数目。
         使用DISTINCT则返回唯一值的个数。
SUM:SUM是一个集合函数,返回表格中选定的数值型列中的值的总和。
    语法:SUM([ALL|DISTINCT]expression)
MAX:MAX返回表达式中的最大值,
    语法:MAX(expression)
MIN:MIN返回表达式中的最小值,
    语法:MIN(expression)
GETDATE:GETDATE是一个函数,它返回系统日期和时间。
    语法:GETDATE()
DATEADD:DATEADD返回根据日期时间添加的日期,
    语法:DATEADD(datepart,number,date)
    变量:DATEPART是用于计算的日期单位。有一定的格式。
                NUMBER是指添加至日期变量中的日期单位数。
               
DATE:格式化为日期的字符串,或日期时间型的列名、变量或参数。
    例如:SELECT DATEADD(DAY,7,GETDATE())
DATEDIFF:返回两个指定日期的日期单位差别。
    语法:DATEDIFF(datepart,date,date)
    例如:DATEDIFF(day,datevar,getdate())   
          返回值为DATEVAR和当前日期相差的天数。
DATEPART:返回的整数表示日期中的日期单位。
    语法:DATEPART(datepart,date)
    例如:DATEPART(month,getdate())  
      返回值为当前日期的月份数。
YEAR:显示所提供日期的年,
    语法:YEAR(DATE)  
MONTH:显示所提供日期的月,
    语法:MONTH(DATE)

转自:

发表评论

电子邮件地址不会被公开。 必填项已用*标注