搜索
第4章数据定义语言本章介绍数据定义语言（DDL）有关的SQL语句。4．1创建数据库对象一个数据库包含很多不同的对象，如数据库、表、表中的列、视图等都是数据库对象。首先要创建的对象就是数据库。SQLSERVER支持系统数据库和用户数据库。一名已经授权的用户能够创建用户数据库，系统数据库在系统安装时生成，如master、msdb、model、tempdb等。一、创建一个数据库创建数据库可以用两种方式：企业管理器和Transact-SQL语言。CREATEDATABASE创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件中附加数据库。语法CREATEDATABASEdatabase_name[ON[filespec[,...n]][,filegroup[,...n]]][LOGON{filespec[,...n]}][COLLATEcollation_name][FORLOAD|FORATTACH]filespec::=[PRIMARY]([NAME=logical_file_name,]FILENAME='os_file_name'[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]filegroup::=FILEGROUPfilegroup_namefilespec[,...n]参数database_name新数据库的名称。数据库名称在服务器中必须唯一，并且符合标识符的规则。database_name最多可以包含128个字符，除非没有为日志指定逻辑名。如果没有指定日志文件的逻辑名，则Microsoft®SQLServer™会通过向database_name追加后缀来生成逻辑名。该操作要求database_name在123个字符之内，以便生成的日志文件逻辑名少于128个字符。ON指定显式定义用来存储数据库数据部分的磁盘文件（数据文件）。该关键字后跟以逗号分隔的filespec项列表，filespec项用以定义主文件组的数据文件。主文件组的文件列表后可跟以逗号分隔的filegroup项列表（可选），filegroup项用以定义用户文件组及其文件。LOGON指定显式定义用来存储数据库日志的磁盘文件（日志文件）。该关键字后跟以逗号分隔的filespec项列表，filespec项用以定义日志文件。如果没有指定LOGON，将自动创建一个日志文件，该文件使用系统生成的名称，大小为数据库中所有数据文件总大小的25%。FORATTACH指定从现有的一组操作系统文件中附加数据库。必须有指定第一个主文件的filespec条目。至于其它filespec条目，只需要与第一次创建数据库或上一次附加数据库时路径不同的文件的那些条目。必须为这些文件指定filespec条目。附加的数据库必须使用与SQLServer相同的代码页和排序次序创建。应使用sp_attach_db系统存储过程，而不要直接使用CREATEDATABASEFORATTACH。只有必须指定16个以上的filespec项目时，才需要使用CREATEDATABASEFORATTACH。PRIMARY指定关联的filespec列表定义主文件。主文件组包含所有数据库系统表。还包含所有未指派给用户文件组的对象。主文件组的第一个filespec条目成为主文件，该文件包含数据库的逻辑起点及其系统表。一个数据库只能有一个主文件。如果没有指定PRIMARY，那么CREATEDATABASE语句中列出的第一个文件将成为主文件。NAME为由filespec定义的文件指定逻辑名称。如果指定了FORATTACH，则不需要指定NAME参数。logical_file_name用来在创建数据库后执行的Transact-SQL语句中引用文件的名称。logical_file_name在数据库中必须唯一，并且符合标识符的规则。该名称可以是字符或Unicode常量，也可以是常规标识符或定界标识符。'os_file_name'操作系统创建filespec定义的物理文件时使用的路径名和文件名。os_file_name中的路径必须指定SQLServer实例上的目录。os_file_name不能指定压缩文件系统中的目录。SIZE指定filespec中定义的文件的大小。如果主文件的filespec中没有提供SIZE参数，那么SQLServer将使用model数据库中的主文件大小。如果次要文件或日志文件的filespec中没有指定SIZE参数，则SQLServer将使文件大小为1MB。sizefilespec中定义的文件的初始大小。可以使用千字节(KB)、兆字节(MB)、千兆字节(GB)或兆兆字节(TB)后缀。默认值为MB。指定一个整数，不要包含小数位。size的最小值为512KB。如果没有指定size，则默认值为1MB。为主文件指定的大小至少应与model数据库的主文件大小相同。MAXSIZE指定filespec中定义的文件可以增长到的最大大小。max_sizefilespec中定义的文件可以增长到的最大大小。可以使用千字节(KB)、兆字节(MB)、千兆字节(GB)或兆兆字节(TB)后缀。默认值为MB。指定一个整数，不要包含小数位。如果没有指定max_size，那么文件将增长到磁盘变满为止。UNLIMITED指定filespec中定义的文件将增长到磁盘变满为止。FILEGROWTH指定filespec中定义的文件的增长增量。文件的FILEGROWTH设置不能超过MAXSIZE设置。growth_increment每次需要新的空间时为文件添加的空间大小。指定一个整数，不要包含小数位。0值表示不增长。该值可以MB、KB、GB、TB或百分比(%)为单位指定。如果未在数量后面指定MB、KB或%，则默认值为MB。如果指定%，则增量大小为发生增长时文件大小的指定百分比。如果没有指定FILEGROWTH，则默认值为10%，最小值为64KB。指定的大小舍入为最接近的64KB的倍数。注释使用一条CREATEDATABASE语句即可创建数据库以及存储该数据库的文件。SQLServer分两步实现CREATEDATABASE语句：1.SQLServer使用model数据库的复本初始化数据库及其元数据。2.然后，SQLServer使用空页填充数据库的剩余部分，除了包含记录数据库中空间使用情况以外的内部数据页。因此，model数据库中任何用户定义对象均复制到所有新创建的数据库中。可以向model数据库中添加任何对象，例如表、视图、存储过程、数据类型等，以将这些对象添加到所有数据库中。每个新数据库都从model数据库继承数据库选项设置（除非指定了FORATTACH）。例如，在model和任何创建的新数据库中，数据库选项selectinto/bulkcopy都设置为OFF。如果使用ALTERDATABASE更改model数据库的选项，则这些选项设置会在创建的新数据库中生效。如果在CREATEDATABASE语句中指定了FORATTACH，则新数据库将继承原始数据库的数据库选项设置。一台服务器上最多可以指定32,767个数据库。有三种类型的文件用来存储数据库：1.主文件包含数据库的启动信息。主文件还可以用来存储数据。每个数据库都包含一个主文件。2.次要文件保存所有主要数据文件中容纳不下的数据。如果主文件大到足以容纳数据库中的所有数据，就不需要有次要数据文件。而另一些数据库可能非常大，需要多个次要数据文件，也可能使用多个独立磁盘驱动器上的次要文件，以将数据分布在多个磁盘上。3.事务日志文件保存用来恢复数据库的日志信息。每个数据库必须至少有一个事务日志文件（尽管可以有多个）。事务日志文件最小为512KB。每个数据库至少有两个文件，一个主文件和一个事务日志文件。尽管'os_file_name'可以是任何有效的操作系统文件名，但如果使用以下建议的扩展名，则可以更加清楚地反映文件的用途。文件类型文件扩展名主要数据文件.mdf次要数据文件.ndf事务日志文件.ldf说明创建用户数据库后，应备份master数据库。SIZE、MAXSIZE和FILEGROWTH参数中不能指定分数。若要在SIZE参数中指定以兆字节为单位的分数，请将该数字乘以1,024转换成千字节。例如，指定1,536KB而不要指定1.5MB（1.5乘以1,024等于1,536）。如果仅指定CREATEDATABASEdatabase_name语句而不带其它参数，那么数据库的大小将与model数据库的大小相等。所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。如果使用指定用户定义文件组的ONfilegroup子句创建对象，则该对象的所有页均从指定的文件组中分配。所有没有使用ONfilegroup子句（或者使用ONDEFAULT子句）创建的用户对象的页将从默认文件组分配。数据库首次创建时，主文件组就是默认文件组。可以使用ALTERDATABASE将用户定义的文件组指定为默认文件组：ALTERDATABASEdatabase_nameMODIFYFILEGROUPfilegroup_nameDEFAULT每个数据库都有一个所有者，可在该数据库中执行某些特殊的活动。所有者是创建数据库的用户。可以使用sp_changedbowner更改数据库所有者。若要为SQLServer实例显示某个数据库或所有数据库的报表，请执行sp_helpdb。若要显示有关数据库中所占用空间的报表，请使用sp_spaceused。若要显示有关数据库中文件组的报表，请使用sp_helpfilegroup，使用sp_helpfile可显示数据库中文件的报表。示例A.不指定文件创建数据库下面的示例创建名为mytest的数据库，并创建相应的主文件和事务日志文件。因为该语句没有filespec项，所以主数据库文件的大小为model数据库主文件的大小。事务日志文件的大小为model数据库事务日志文件的大小。因为没有指定MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止。CREATEDATABASEmytestB.创建简单的数据库本例创建名为Products的数据库，并指定单个文件。指定的文件成为主文件，并会自动创建一个1MB的事务日志文件。因为主文件的SIZE参数中没有指定MB或KB，所以主文件将以兆字节为单位进行分配。因为没有为事务日志文件指定filespec，所以事务日志文件没有MAXSIZE，可以增长到填满所有可用的磁盘空间为止。USEmasterGOCREATEDATABASEProductsON(NAME=prods_dat,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\prods.mdf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)GOC.不指定SIZE创建数据库下面的示例创建名为products2的数据库。文件prods2_dat将成为主文件，大小等于model数据库中主文件的大小。事务日志文件会自动创建，其大小为主文件大小的25%或512KB中的较大值。因为没有指定MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止。USEmasterGOCREATEDATABASEProducts2ON(NAME=prods2_dat,FILENAME='c:\programfiles\microsoftsqlserver\mssql\data\prods2.mdf')GOD.创建指定数据文件和事务日志文件的数据库下面的示例创建名为Sales的数据库。因为没有使用关键字PRIMARY，第一个文件(Sales_dat)成为主文件。因为Sales_dat文件的SIZE参数没有