第五章-SQL-Server-2008数据库和表

数据库实用技术

SQLServer2008第五章SQLServer2008数据库和表第五章SQLServer2008数据库和表SQLServer2008数据库概述1数据库基本管理2数据库高级管理3SQLServer2008SQLServer2008数据表4数据表的创建5数据表的管理6表数据的管理75.1SQLServer2008数据库概述5.1.1数据库存储结构：数据库的逻辑结构数据库由若干个用户可视的对象构成，主要包括下列数据库对象：

数据表、视图、约束、规则、默认、索引、存储过程、触发器等。数据库的物理结构数据库的物理结构表现为操作系统文件，一个数据库由一个或多个磁盘上的文件组成。SQLServer2008数据库的文件有两种类型：数据文件日志文件第五章SQLServer2008数据库和表SQLServer2008SQLServer2008对象作用表数据库中数据的实际存放处所。视图定制复杂或常用的查询，以便用户使用；限定用户只能查看表中的特定行或列；为用户提供统计数据而不展示细节。索引加快从表或视图中检索数据的效率。存储过程提高性能；封装数据库的部分或全部细节；帮助在不同的数据库应用程序之间实现一致的逻辑。约束、规则、默认值和触发器确保数据库的数据完整性；强制执行业务规则。登录、用户、角色和组保障数据安全的基础。5SQLServer中常用的数据库对象数据库对象的标识符例如数据库名、表名、视图名、列名等。SQLServer标识符的命名遵循以下规则：(1)标识符包含的字符数必须在1-128之间。(2)标识符的第一个字符必须是字母、下划线(_)、at符号(@)或者数字符号(#)。(3)标识符的后续字符可以为字母、数字或“@”符号、“$”符号、数字符号或下划线。(4)标识符不能是Transact-SQL的保留字，也不能包含空格。65.1SQLServer2008数据库概述5.1.2数据库文件：主数据文件是数据库的起点，包含数据库的启动信息、数据信息。每个数据库有且只有一个主数据文件。扩展名为.mdf。次要数据文件存储主数据文件未存储的所有其他数据和对象。不是数据库必需的文件,

0个、一个或多个。扩展名是.ndf。事务日志文件每个数据库至少拥有一个自己的日志文件。日志文件的大小最少是1MB。默认扩展名是.ldf。第五章SQLServer2008数据库和表SQLServer20085.1SQLServer2008数据库概述5.1.3数据库文件组：文件组是数据文件的逻辑组合,将多个数据库文件集合起来形成的一个整体。每个文件组有一个组名。数据库文件组有三类：主要文件组（primary）：所有系统表都被分配到主要文件组中。一个数据库有1个主要文件组。用户定义文件组（user_defined）：是用户首次创建数据库时，或修改数据库时自定义的，其目的在于数据分配，以提高表的读写效率。默认文件组（default）:每个数据库中均有一个文件组被指定为默认文件组。如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。第五章SQLServer2008数据库和表SQLServer2008文件组的作用：文件组可以把一些指定的文件组合在一起，方便管理和分配数据。当对数据库对象写操作时,数据库会根据组内数据文件的大小,按比例写入组内所有数据文件中。当查询数据时,系统会创建多个单独的线程来并行读取分配在不同物理磁盘上的每个文件,从一定程度上提高了查询速度。9文件组的使用场合当有多个磁盘，希望把文件分布在这些磁盘上以提高性能。10Student数据库Student_1.mdfStudent_2.ndfStudent_3.ndfStudent_log.ldfC盘d盘e盘f盘primaryStudgrpStudgrp在使用文件组时，应当注意以下几个准则：文件或文件组不能由一个以上的数据库使用。文件只能是一个文件组的成员。数据和事务日志信息不能属于同一文件或文件组。事务日志文件不能属于任何文件组。11SQLServer中的数据库12数据库的逻辑组件(数据库对象)…Sales数据库SQLServer

表

视图数据库的物理实现(数据库文件)…SalesDat1.mdfSalesDat2.ndfSalesLog1.ldf主数据文件次数据文件日志文件

索引存储过程用户视图物理视图图5-1数据库的用户视图和物理视图SQLServer中的数据库文件13主数据文件*.mdf仅有一个事务日志文件*.ldf一到多个

次数据文件*.ndf零到多个次数据文件*.ndf零到多个(仅有一个)主文件组(零到多个)次文件组事务日志一个数据库的文件集图5-2数据库的文件组成5.2SQLServer2008数据库基本管理在SQLServer2008中，对数据库管理操作有两种方式：SQLServerManagementStudio（SSMS）图形化界面方式。T-SQL代码命令方式。5.2.1创建用户数据库1.规划数据库需要考虑的问题：数据库名称、数据库所有者。数据文件和事务日志文件的逻辑名、物理名、初始大小、增长方式和最大容量。数据库用户数量和用户权限。数据库大小与硬件配置的平衡、是否使用文件组。数据库的备份与恢复。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理2.创建用户数据库操作：创建用户数据库：【例5-1】创建计费系统数据库：数据库名称为“BillingSys”。主要数据文件逻辑名为“BillingSys.mdf”，保存路径为“C:\BillingSystem\Data”，日志文件逻辑名为“BillingSys_log.ldf”，保存路径为“C:\BillingSystem\Data_Log”。主要数据文件初始大小为3MB，最大大小为不受限制，增长量为1MB；日志文件的初始大小为1MB，最大大小为20MB，增长比例为10%。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：(1)利用SSMS创建用户数据库：在SSMS窗口的“对象资源管理器”中选择“数据库”文件夹并右击，在弹出快捷菜单中选择“新建数据库”命令。打开“新建数据库”的对话框，默认进入选择页的“常规”栏，设置新建数据库的名字，如左图所示。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：(1)利用SSMS创建用户数据库：在“数据库文件”中，可以设置文件的名称、位置及大小。在“自动增长”列中，可以选择文件是否自动增长和是否有最大限制。在“路径”列中可以通过单击按钮来指定文件所在的位置，如左下图所示。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：(1)利用SSMS创建用户数据库：单击选择页中的“选项”栏，打开“选项”选项。在“选项”页中，选择“文件组”选项，打开“文件组”选项卡，可以对文件组进行设置。设置完成后，单击“确定”按钮，即可创建数据库。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：(2)利用T-SQL语句创建用户数据库关于T-SQL语句格式约定：SQL语句在书写时不区分大小写，为了清晰，一般都用大写表示系统保留字，

用小写表示用户自定义的名称。“[]”表示该项可省略，省略时各参数取默认值;a|b表示a或b任选其一，不能同时都选。“{}”表示是必选的；[，…n]”表示大括号括起来的内容可以重复写多次。<>括起来的内容表示在实际编写语句时，用相应的内容替代；第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：新建数据库语句格式：CREATEDATABASE数据库名[ON{[PRIMARY](NAME=逻辑文件名,FILENAME=物理文件名[,SIZE=初始大小][,MAXSIZE={最大大小|UNLIMITED}][,FILEGROWTH=文件增长幅度])}[,…n]LOGON{[PRIMARY](NAME=逻辑文件名,FILENAME=物理文件名[,SIZE=初始大小][,MAXSIZE={最大大小|UNLIMITED}][,FILEGROWH=文件增长幅度])}[,…n]][；]第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理创建用户数据库操作：参数说明如下：数据库名：新建的数据库名称。数据库名称在SQLServer的实例中必须唯一，并且必须符合标识符规则。最长为128个字符。每个服务器管理的数据库最多为32767个。ON：指定显式定义用来存储数据库数据部分的磁盘文件（数据文件）。PRIMARY：在主文件组中指定文件。LOGON：指定显式定义用来存储数据库日志的磁盘文件（日志文件）。NAME：指定文件的逻辑名称。FILENAME：指定操作系统（物理）文件名称，即包含路径和文件名，且指定的路径必须存在。SIZE：指定文件的初始大小，用户可以以兆字节（MB）或千字节（KB）为单位。MAXSIZE：指定文件可增大到的最大大小。UNLIMITED：指定文件将增长到整个磁盘。FILEGROWTH：指定文件的自动增量。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理使用CREATEDATABASE语句完成例5-1中的数据库创建：打开SSMS，并用“Windows身份验证”登录，单击标准工具栏的“新建查询”按钮，打开查询编辑窗口，在查询编辑窗口中输入如下的T-SQL语句，单击工具栏上的“执行”按钮执行输入的SQL语句。CREATEDATABASEBillingSys --数据库名称ONPRIMARY --在主文件组中建立主要数据文件(NAME=BillingSys, --主要数据逻辑文件名FILENAME='c:\BillingSystem\Data\BillingSys.mdf',--逻辑文件名的物理文件路径SIZE=3, --初始大小3MBFILEGROWTH=1 --增长量为MB)LOGON --建立日志文件(NAME=BillingSys_log, --日志文件名FILENAME='c:\BillingSystem\Data_Log\BillingSys_log.ldf',--日志文件名的路径SIZE=1, --初始大小1MBMAXSIZE=20, --最大容量为MBFILEGROWTH=10% --增长速度为%第五章SQLServer2008数据库和表SQLServer2008补例5-1最简形式的创建数据库(不指定文件)语句。CREATEDATABASESales补例5-2不指定SIZE创建数据库。CREATEDATABASESales2ON(NAME=Sales2_dat,FILENAME='C:\DataBase\Sales2.mdf')24补例5-3创建简单的数据库。CREATEDATABASESales3ON(NAME=Sales3_dat,FILENAME='C:\DataBase\Sales3.mdf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)2526补例5-4：用T-SQL语句创建学生信息管理数据库，数据库名为student,将数据文件和日志文件存放在d:\stud中,数据文件和日志文件的初始大小为3MB，增长幅度为1MB，文件大小限制为20MB。CREATEDATABASEstudentON(NAME=’student_data’,FILENAME=’d:\stud\student_data.mdf’,SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)LOGON(NAME=’student_log’,FILENAME=’d:\stud\student_log.ldf’,SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)GO27补例5-5：某公司生产许多产品，数据量大，需要建立产品信息数据库。数据库命名为product.其数据文件和事务文件分别需要3个，文件信息为:逻辑名物理名文件组初始大小增长率最大值p1_datad:\product\product_data1.mdfprimary315%p2_datad:\product\product_data2.ndfusergroup315%10p3_datad:\product\product_data3.ndfusergroup315%20p1_logd:\product\product_log1.ldf210%p2_logd:\product\product_log2.ldf210%28CREATEDATABASEproductONPRIMARY(NAME='p1_DATA',FILENAME='d:\product\product_DATA1.mdf',SIZE=3MB,FILEGROWTH=15%),FILEGROUPUSERGROUP(NAME='p2_DATA',FILENAME='d:\product\product_DATA2.ndf',SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=15%),

(NAME='p3_DATA',FILENAME='d:\product\product_DATA3.ndf',SIZE=3MB,MAXSIZE=20MB,FILEGROWTH=15%)29LOGON(NAME='p1_log',FILENAME='d:\product\product_log1.ldf',SIZE=2MB,FILEGROWTH=10%),(NAME='p2_log',FILENAME='d:\product\product_log2.ldf',SIZE=2MB,FILEGROWTH=10%)补例5-6创建指定数据文件和事务日志文件的数据库。CREATEDATABASESales4ON(NAME=Sales4_dat,FILENAME='C:\DataBase\sales4dat.mdf',SIZE=10000KB,MAXSIZE=500000KB,FILEGROWTH=5)LOGON(NAME='Sales4_log',FILENAME='C:\DataBase\sales4log.ldf',SIZE=5,MAXSIZE=25,FILEGROWTH=5)30SQLServer2008数据库基本管理5.2.2查看、修改或删除用户数据库属性：1.利用SSMS对用户数据库进行基本管理：查看或配置用户数据库：打开SSMS，在“对象资源管理器”窗口展开“数据库”节点，选择数据库，右键选择“属性”命令，打开“数据库属性”对话框：第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用SSMS对用户数据库进行基本管理：删除用户数据库：在SSMS的“对象资源管理器”中展开“数据库”节点，选择数据库，右键选择“删除”命令，打开“删除对象”窗口，如下图。在“删除对象”窗口，确认是否要删除的数据库，可选择“关闭现有连接”复选框决定是否要删除备份及关闭已存在的数据库连接。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用T-SQL语句对用户数据库进行基本管理：选择数据库：语句格式：USE数据库名其中，数据库名为需要选择的数据库名称。例：useBillingSys第五章SQLServer2008数据库和表SQLServer20082023/9/2734在SSMS中打开数据库。在“对象资源管理器”窗格中展开“数据库”结点，单击要打开的数据库，如图所示。此时右窗格中列出的是当前打开的数据库的对象。图

在“对象资源管理器”窗格中打开数据库2023/9/2735在查询分析器中，可以通过使用USE语句打开并切换数据库，也可以直接通过数据库下拉列表打开并切换，如图所示。图3-10在“查询分析器”窗格中切换数据库查看数据库属性：执行存储过程用Exec语句。与数据库属性相关的系统提供存储过程如：sp_helpdb：显示有关数据库和数据库参数信息。sp_spaceused：查看数据库空间信息。sp_dboptions：查看数据库选项信息。SQLServer2008SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用T-SQL语句对用户数据库进行基本管理：查看数据库属性：执行存储过程用Exec语句。【例5-2】查询数据库billingSys的相关参数信息。UsebillingSysExecsp_helpdb第五章SQLServer2008数据库和表SQLServer2008Execsp_helpdbbillingSys【例5-3】查询数据库billingSys的空间信息。UsebillingSysExecsp_spaceused【例5-4】查询数据库billingSys的选项信息。Execsp_dboptionbillingSysautocreatestatistics,如果为true，将在优化过程中自动生成优化查询所需的任何缺少的统计信息。autoupdatestatistics,如果为true，将在优化过程中自动生成优化查询所需的任何过期统计信息。SQLServer2008SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用T-SQL语句对用户数据库进行基本管理：修改数据库：ALTERDATABASE语句格式：ALTERDATABASE数据库名{ADDFILE数据文件名[,…,n][TOFILEGROUP文件组名]|ADDLOGFILE日志文件名[,…,n]|REMOVEFILE逻辑文件名|MODIFYFILE数据文件|ADDFILEGROUP文件组名|REMOVEFILEGROUP文件组名|MODIFYFILEGROUP文件组名{文件组属性|NAME=新文件组名}}第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用T-SQL语句对用户数据库进行基本管理：修改数据库：ALTERDATABASE语句参数说明如下：ADDFILE：向指定的数据库文件组添加新的数据文件。ADDLOGFILE：向数据库添加事务日志文件。REMOVEFILE：从SQLServer的实例中删除逻辑文件说明并删除物理文件。注意，只有文件为空时才能被删除。MODIFYFILE：修改某一文件的属性。ADDFILEGROUP：向数据库添加文件组。REMOVEFILEGROUP：从SQLServer的实例中删除文件组。MODIFYFILEGROUP：修改某一文件组的属性。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理【例5-5】按下列要求修改数据库BillingSys：添加两个次要数据文件BS_data1，BS_data2和一个事务日志文件BS_log1；次要数据文件BS_data1.ndf和BS_data2.ndf的保存路径为“C:\BillingSystem\Data”；文件初始大小分别为5MB和3MB，最大大小分别为100MB和10MB，增长量分别为5MB和1MB；日志文件BS_log1.ldf的保存路径为“C:\BillingSystem\Data_Log”，文件的初始大小为5MB，最大大小为100MB，增长比例为15%。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理为数据库添加次要文件和事务日志文件，需要修改数据库的参数，于是ALTERDATABASE命令代码如下：/*******添加两个次要数据文件******/ALTERDATABASEBillingSysADDFILE(NAME=BS_data1,FILENAME='F:\BillingSystem\Data\BS_data1.ndf', SIZE=5,MAXSIZE=100,FILEGROWTH=5),(NAME=BS_data2,FILENAME='F:\BillingSystem\Data\BS_data2.ndf', SIZE=3,MAXSIZE=10,FILEGROWTH=1)GO第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理/*******添加一个事务日志文件******/(ALTERDATABASEBillingSysADDLOGFILE(NAME=BS_log1,FILENAME='C:\BillingSystem\Data_Log\BS_log1.ldf',SIZE=5,MAXSIZE=100,FILEGROWTH=15% )

第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理【例5-6】修改数据库BillingSys的数据文件BS_data2的属性，将其初始大小改为10MB，最大容量100MB，增长幅度5MB：代码如下：ALTERDATABASEBillingSysMODIFYFILE(NAME＝BS_data2,SIZE=10,MAXSIZE＝100,FILEGROWTH=5)第五章SQLServer2008数据库和表SQLServer2008补例5-8更改数据库名称。ALTERDATABASESales

MODIFYNAME=NewSales补例5-9从数据库中删除文件。ALTERDATABASESalesREMOVEFILESalesG1F2_dat45SQLServer2008数据库基本管理查看、修改或删除用户数据库属性：利用T-SQL语句对用户数据库进行基本管理：删除用户数据库：语句格式：DROPDATABASE数据库名[,数据库名…]其中，数据库名是要删除的数据库名称。补例5-10删除单个数据库。DROPDATABASEBillingSys补例5-11删除多个数据库。DROPDATABASESales2,Sales3第五章SQLServer2008数据库和表SQLServer20085.3SQLServer2008数据库高级管理5.3.1收缩用户数据库：利用SSMS收缩用户数据库：自动收缩用户数据库：在SSMS的“对象资源管理器”窗口展开“数据库”节点，选择数据库，右击选择“属性”命令，打开“数据库属性”窗口。在“数据库属性”窗口中，选择“选项”选项卡，单击自动收缩旁的下拉列表框，选择True就可设定数据库为自动收缩，如左图所示。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理收缩用户数据库：利用SSMS收缩用户数据库：手动收缩用户数据库：在“对象资源管理器”中选择数据库，右键，依次选择“任务”→“收缩”→“数据库”命令，打开“收缩数据库”对话框，如左图所示。完成设置后，单击“确定”按钮。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理收缩用户数据库：1.利用SSMS收缩用户数据库：手动收缩数据文件：在SSMS的“对象资源管理器”窗口选择数据库，右击，依次选择“任务”→“收缩”→“文件”命令，打开“收缩文件”对话框，如左图所示。在“收缩文件”对话框中，确定“文件类型”、“文件组”、“文件名”、“位置”。完成设置后，单击“确定”按钮。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理收缩用户数据库：2.利用T-SQL语句收缩用户数据库：自动收缩数据库：使用ALTERDATABASE语句自动收缩用户数据库。语句格式：ALTERDATABASE数据库名SETAUTO_SHRINKON/OFF参数说明如下：ON：将数据库设为自动收缩。OFF：将数据库设为不自动收缩。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理收缩用户数据库：利用T-SQL语句收缩用户数据库：手动收缩数据库：使用DBCCSHRINKDATABASE语句实现用户数据库手动收缩。语句格式：DBCCSHRINKDATABASE(数据库名|数据库id|0[,目标比例][,{NOTRUNCATE|TRUNCATEONLY}])参数说明如下：数据库名：要收缩的数据库名称或ID。0为使用当前数据库。目标比例：收缩后的数据库文件中所需的剩余可用空间百分比。NOTRUNCATE：通过从文件末移动到文件前的未分配页来压缩数据。TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不执行任何页移动。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理收缩用户数据库：利用T-SQL语句收缩用户数据库：手动收缩数据库：【例5-7】收缩数据库BillingSys大小，使得数据库中的文件有20％可用空间。DBCCSHRINKDATABASE(BillingSys,20)第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理收缩用户数据库：利用T-SQL语句收缩用户数据库：收缩指定数据文件：使用DBCCSHRINKFILE语句可以实现收缩指定数据文件。语句格式：DBCCSHRINKFILE({逻辑文件名|文件id}{[,EMMPTYFILE]|0[[,文件大小][,{NOTRUNCATE|TRUNCATEONLY}]]})[WITHNO_INFOMSGS]参数说明如下：逻辑文件名：要收缩的文件的逻辑名称或文件的标识（ID）号。文件大小：用兆字节表示的文件大小（用整数表示）。如果未指定，则将文件大小减少到默认文件大小。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理收缩用户数据库：利用T-SQL语句收缩用户数据库：收缩指定数据文件：【例5-8】将数据库BillingSys中名为BS_data2的数据文件收缩到7MB。DBCCSHRINKFILE（BS_data2，7）第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理5.3.2分离与附加用户数据库：利用SSMS分离与附加用户数据库：分离用户数据库：在SSMS的“对象资源管理器”窗口中选择数据库，右击选择“任务”→“分离”命令，打开“分离数据库”对话框。在“分离数据库”对话框中，“要分离的数据库”列表框中的“数据库名称”栏中显示了所选数据库的名称。设置完毕后，单击“确定”按钮。DBMS将执行分离数据库任务。如果分离成功，在“对象资源管理器”中将不会出现被分离的数据库。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理分离与附加用户数据库：利用SSMS分离与附加用户数据库：附加用户数据库：在SSMS的“对象资源管理器”窗口中选择数据库实例下的“数据库”项，右击选择“附加数据库”命令，打开“附加数据库”对话框。在“附加数据库”对话框中，单击“添加”按钮，打开“定位数据库文件”对话框。在“定位数据库文件”对话框中，选择数据库所在的磁盘驱动器并展开目录树定位到数据库的.mdf文件。设置完毕后，单击“确定”按钮。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理2.利用系统存储过程分离与附加用户数据库：(1)分离用户数据库：使用系统存储过程sp_detach_db来执行分离用户数据库的操作。语句格式：sp_detach_db[@dbname=]‘database_name’[,[@skipchecks=]‘skipchecks’][,[@keepfulltextindexfile=]‘KeepFulltextindexFile’参数说明如下：[@dbname=]‘database_name’：要分离的数据库的名称。[@skipchecks=]‘skipchecks’：指定跳过还是运行UPDATESTATISTIC。[@keepfulltextindexfile=]‘KeepFulltextIndexFile’：指定在数据库分离操作过程中不会删除与所分离的数据库关联的全文索引文件。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库基本管理利用系统存储过程分离与附加用户数据库：分离用户数据库：【例5-9】分离用户数据库BillingSys。EXECsp_detach_db@dbname='BillingSys‘第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理(2)附加用户数据库：使用系统存储过程sp_attach_db来执行附加用户数据库的操作。语句格式：sp_attach_db[@dbname=]’dbname’,[@filename1=]’filename_n’[,…,16]参数说明如下：[@dbname=]’dbname’：要附加到该服务器的数据库的名称。该名称必须是唯一的。[@filename1=]’filename_n’：数据库文件的物理名称，包括路径。第五章SQLServer2008数据库和表SQLServer2008【例3-10】将d:\sqllx\test.mdf和d:\sqllx\test.ldf附加为数据库test。代码如下：EXECsp_attach_db@dbname=N'test',@filename1=N'd:\sqllx\test_Data.mdf',@filename2=N'd:\sqllx\test_log.ldf';使用CREATEDATABASE语句【例】CREATEDATABASEstudentsysON(FILENAME='D:\sql\JX.mdf')LOGON(FILENAME='D:\sql\JX.ldf')FORATTACHSQLServer2008补充：数据库重命名1.使用“对象资源管理器”

在“对象资源管理器”中，右击要重命名的数据库，在弹出菜单中选择“重命名”，输入新的数据库名称，按“Enter”键即可，如图所示。图3-12数据库重命名窗口2.使用T-SQL语句语法格式如下：sp_renamedboldname,newname语法中的各参数说明如下：sp_renamedb：系统存储过程。oldname：更改前的数据库名。newname：更改后的数据库名。【例3-11】更改jxgl数据库的名称为“jsjxjxgl”。代码如下：EXECsp_renamedb'jsjx_db','jsjxjxgl'GOSQLServer2008数据库高级管理5.3.3数据库快照：1.数据库快照的作用：数据库快照就是数据库的一个只读副本。数据库快照就是保存某个数据库在快照那一瞬间的状态.数据库快照是数据库（源数据库）的只读、静态视图。创建数据库快照是保证数据安全手段之一。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理2.创建用户数据库快照：使用CREATEDATABASE语句的ASSNAPSHOTOF子句来创建用户数据库快照。语句格式：CREATEDATABASE新数据库快照名称ON(NAME=逻辑文件名,FILENANE=物理文件名)[,…,n]ASSNAPSHOTOF源数据库名参数说明如下：NAME：源数据库中数据文件逻辑名称。FILENAME：新数据库快照的物理文件名称。ASSNAPSHOTOF：指定要创建的数据库快照所对应的源数据库名称，快照和源数据库必须位于同一实例中。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据库高级管理创建用户数据库快照：【例5-10】对BillingSys数据库创建数据库快照。快照名称BillingSys_dbss及其稀疏文件的名称BillingSys_data.ss。CREATEDATABASEBillingSys_dbssON(NANE=BillingSys_data,FILENANE='F:\BillingSys_snapshot\Data\BillingSys_data.ss’)ASSNAPSHOTOFBillingSys;GO第五章SQLServer2008数据库和表SQLServer2008SQLServer2008补：使用数据库快照恢复数据库例如：从“BillingSys_dbss”数据库快照中恢复BillingSys数据库，语句如下：RESTOREDATABASEdatabase_nameFROMDATABASE_SNAPSHOT=database_snapshot_nameRESTOREDATABASEBillingSysFROMDATABASE_SNAPSHOT='BillingSys_dbss'作业：实验练习1、设有一学籍管理系统，其数据库名为“EDUC”，初始大小为

10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”,物理文件名为“student_data.mdf，存放路径为“E:\sql_data”。日志文件的逻辑文件名为“student_log”,物理文件名为“student_log.ldf”，存放路径为“E:\sql_data”。(1)使用向导创建上述描述的数据库（上机练习）。(2)使用向导删除上面建立的数据库（上机练习）。(3)用T—SQL命令建立上述描述的数据库（上机练习）。(4)用T—SQL命令删除上面建立的数据库（上机练习）。SQLServer20082、以下是创建数据库userdb1的SQL语句，createdatabaseuserdb1on(name=userdb4_data,--数据文件的逻辑名称,注意不能与日志逻辑同名filename='e:\xygl\userdb1.mdf',--物理名称，注意路径必须存在size=5,--数据初始长度为Mmaxsize=10,--最大长度为Mfilegrowth=1)--数据文件每次增长Mlogon(name=userdb4_log,filename='e:\xygl\userdb1.ldf',size=2,maxsize=5,filegrowth=1)(1)运行上述语句建立数据库userdb1(2)查看userdb1数据库基本信息。SQLServer2008(3)将userdb1数据库的分配空间增加至30M。(4)为数据库userdb1增加数据文件userdb1_data1，初始大小10M，最大50M，按照5%增长。(5)将userdb1数据库改名为userdb2。(6)分离userdb2数据库。(7)附加userdb2数据库。(8)用SQL语句删除数据库userdb2。SQLServer2008SQLServer2008数据库高级管理数据库快照：删除用户数据库快照：例如：删除名为dbss的数据库快照命令：DROPDATABASEBillingSys_dbss第五章SQLServer2008数据库和表SQLServer20085.4SQLServer2008数据表5.4.1数据表的基本概念：1.数据表：表是数据的集合，是用来存储数据和操作数据的逻辑结构。2.永久数据表：永久数据表是指创建后一直存储在数据库文件中，直至用户删除为止的数据表。3.临时数据表：临时数据表指用户临时建立的数据表，这些数据表随着用户的退出或系统修复而被自动删除。

本地临时表：名称以#

打头；它们仅对当前的用户连接是可见的；当用户从SQLServer实例断开连接时被删除。

全局临时表：以##打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQLServer断开连接时被删除。第五章SQLServer2008数据库和表SQLServer20084.表的构成：表由表头和若干行数据构成。5.表的特性：表中每一行代表一条唯一的记录，每列代表一个域。例1：学生成绩表表名表头数据行列5.对表的操作：填表：将数据写入表中。修改：改正表中的数据信息。删除：删除表中记录信息。查询：在表中按某些条件查找记录信息。SQLServer2008数据表5.4.2数据类型：概念：定义每个列所能存放的数据值和存储格式。精度：指数值数据中所存储的十进制数据的总位数。

例如：tinyint类型可以表示范围是0－255，其精度为3。小数位数：指数值数据中小数点右边可以有的数字位数的最大值。

例：89.658，其精度为5，小数位数为3。长度：存储数据所用的字节数。例：tinyint所用长度为1字节。

第五章SQLServer2008数据库和表erv4r20085.4SQLServer2008数据表5.4.2数据类型：1.表中的数据类型：7种精确数字数据类型：int：整数数值，占4字节存储空间。bigint：整数数值，占8字节存储空间。smallint：短整数数值，占2字节存储空间。tinyint：小整数数值，占1字节存储空间。decimal/numeric：有一定精度和范围的数据，占5～17字节存储空间。近似数字数据类型：float：小数点不固定的数值，占4～8字节存储空间。real：小数点不固定的数值，占4字节存储空间。货币数据类型：money：将数值存储到小数点后4位，占8字节存储空间。smallmoney：将数值存储到小数点后4位，占8字节存储空间。第五章SQLServer2008数据库和表erv4r2008SQLServer2008数据表数据类型：表中的数据类型：字符数据类型：char：使用ANSI编码，固定长度的，最多可定义8000个字符。nchar：使用Unicode编码，固定长度，最多可定义4000个字符。varchar：使用ANSI编码，不固定长度，最多可定义8000个字符。nvarchar：与varchar相似，但使用Unicode编码，占用两倍的存储空间。text：长度大于8000字符的数据。ntext：与text数据类型一样，ntext是Unicode版本。日期和时间数据类型：date：仅用来存储日期，从0001年1月1日到9999年12月31日。time：只存储基于24小时制的时间，其格式为hh:mm:ss[.nnnnnnn]。datetime：从1753年1月1日到9999年12月31日的日期时间。datetime2：与datetime类似，但其秒的小数部分的精度更高。smalldatetime：从1900年1月1日到2079年6月6日的日期时间。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据表数据类型：表中的数据类型：二进制数据类型：binary：固定大小的二进制格式数据，最多可存储8000字节。varbinary：不固定大小的二进制格式数据，最多可存储2GB。image：与text十分相似，但用于存储任何类型的二进制数据。专用数据类型：bit：存储的值为0或1。uniqueidentifier：存储16位全局唯一标识符（GUID）。XML：存储一个XML，最大大小为2GB。2.程序中的数据类型：3种cursor：即游标，与表类似，能以驻留内存的状态进行存储。table：用于存储行和列的数据，但不能在数据上建索引。sql_variant：用于存储一些不同类型数据的数据类型。第五章SQLServer2008数据库和表SQLServer2008SQLServer2008数据表5.4.3列的其它属性：默认值：当向表中插入一行时，可以由SQLServer向列中加入数据值，而不是必须由用户提供，此时可以在该列中放置默认值来实现。生成IDENTITY值：当向SQLServer的表中加入新行时，可能希望给行一个唯一而又容易确定的ID号，并且该ID号不由用户输入，而是由SQLServer自动创建。如：学号。NULL值：NULL值意味着绝对没有任何信息输入到列中。NULL值不占用任何存储空间。第五章SQLServer2008数据库和表SQLServer2008四、约束

定义了关于列中允许值的规则，SQLServer通过限制列中数据、行中数据和表之间数据来保证数据的完整性。(1)非空值约束(NotNull)(2)默认约束(Default)(3)惟一性约束(Unique）(4)主键约束(PrimaryKey，也称主关键字约束)：使用数据表中的一列数据或者多列数据来惟一标识一行数据。选择主键的原则：最少性：尽量选择单个键作为主键稳定性：尽量选择数值更新少的列作为主键。(5)外键约束(ForeignKey，也称为外部关键字约束)：定义了表之间的关系，主要用来维护两个表之间的一致性。

1、创建表的步骤创建表一般要经过定义表结构、设置约束和添加数据三步，其中设置约束可以在定义表结构时或定义完成之后建立。定义表结构：给表的每一列取字段名，并确定每一列的数据类型、数据长度、列数据是否可以为空等。设置约束：设置约束来限制该列输入值的取值范围，以保证输入数据的正确性和一致性。

主键约束、外键约束、默认值和规则等。添加数据：表结构建立完成之后，应该向表中输入数据。5.5数据表的创建SQLServer中创建表的限制每个数据库里最多有20亿个表。每个表上最多可以创建一个聚集索引，249个非聚集索引。每个表最多可以设置1,024个字段。每条记录最多占8,060B，但不包括text字段和image字段。创建表前的考虑数据库中要存放哪些数据，这些数据如何划分到表中。确定每个表需要哪些列，即决定表所包含的属性有哪些。5.5数据表的创建5.5.1利用表设计器创建数据表1.定义表的列启动SSMS，连接到SQLServer2008数据库实例。在对象资源管理器中展开“数据库”节点，依次选择“数据库”→“BillingSys”→“表”，右击选择“新建表”命令，打开“表设计器”。在“表设计器”中，定义各列的名称、数据类型、长度、是否允许为空等属性。当完成设置后，单击工具栏上的“保存”按钮，弹出“选择名称”对话框，输入新建表名Customer，然后单击“确定”。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表定义表的列第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表2.创建完整性约束：实体完整性创建主键（PRIMARYKEY）约束在表设计器中，选择需要设置主键的列，右击，选择“设置主键”命令。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表创建完整性约束：实体完整性创建唯一性（UNIQUE）约束在Customer的表设计器中，右击选择“索引/键”命令，打开“索引/键”对话框。在弹出的“索引/键”对话框中，单击“添加”按钮添加新的主/唯一键或索引；在（常规）的“类型”右边选择“唯一键”，在“列”的右边单击按钮，选择列名“CName”和排序规律ASC（升序）或DESC（降序），如左图所示。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表创建完整性约束：实体完整性定义标识列第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表创建完整性约束：域完整性创建CHECK（检查）约束在表设计器中右击某列，选择“CHECK约束”命令，在打开的“CHECK约束”对话框中单击“添加”按钮，在“表达式”文本框中输入检查表达式，如左图所示。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表创建完整性约束：域完整性默认值若将表中某列定义了DEFAULT约束后，用户在插入新的数据行时，如果没有为该列指定数据，系统将默认值赋给该列，默认值可以是空值（NULL）。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表参照完整性：创建外键（FOREIGNKEY）约束外键用于建立和加强两个表（主表与从表）的一列或多列数据之间的链接，以保证它们之间数据的一致性。例如：在客户表（Customer）中的“RID”（外键）的取值必须参照联系人基本信息表（Relationer）中的“RID”（主键）的有效值，与其保持一致性，其操作步骤如下：在Customer的表设计器中，选择需要设置外键的列RID，右击选择“关系”命令，打开“外键关系”对话框。在“外键关系”对话框中，单击“添加”按钮，增加新的外键关系。在“表和列”对话框中，如果想重新命名外键约束名，可以在“关系名”文本输入框中输入新的名称；在“主键表”下拉列表框中选择Relationer表，并单击“主键表”下的下拉按钮选择其中的RID作为被参照列；在“外键表”文本框中已经输入当前表名Customer，并单击“外键表”下的下拉按钮选择其中的RID作为参照列，如下页图所示。第五章SQLServer2008数据库和表SQLServer2008数据表的创建利用表设计器创建数据表参照完整性：创建外键（FOREIGNKEY）约束建立客户表（Customer）中的“RID”（外键）的约束列关系：在Customer的表设计器中，选择需要设置外键的列RID，右击选择“关系”命令，打开“外键关系”对话框。设置完成后，单击“确定”按钮返回“外键关系”对话框，主键设置，检查表和列规范、关系名等属性设置无误后，然后单击“确定”按钮。第五章SQLServer2008数据库和表SQLServer2008数据表的创建5.5.2使用T-SQL语句创建数据表利用CREATETABLE语句创建数据表：基本语法如下：CREATETABLE表名(列名1数据类型列级完整性约束[,…n]表级完整性约束[,…n])[ON{filegroup|“default”}]第五章SQLServer2008数据库和表SQLServer2008数据表的创建使用T-SQL语句创建数据表利用CREATETABLE语句创建数据表：参数说明如下：表名：在当前数据库中新建的表名称。ON{filegroup|“default”}：指定存储表的文件组。列级完整性约束如下：默认值约束、空值/非空值约束、主键约束、外键约束、唯一性约束、检查约束。表级完整性约束如下：UNIQUE(列名1，列名2，…，列名n)：多个列名单值约束。PRIMARYKEY(列名1，列名2，…，列名n)：多个列名组合主键约束。FOREIGNKEY(外键)REFERENCES主键表(主键)：多个列名组合外键约束。CHECK(逻辑表达式)：含有多个列名逻辑表达式的检查约束。第五章SQLServer2008数据库和表SQLServer2008数据表的创建【例5-11】创建计费系统数据库BillingSys的客户信息表及其约束。客户信息表的关系模式为：Customer(CID,CName,RID,CPassword,CRegistrationDate,CTyp,CStatus,CAccountBalance)PK：CID，FK：RID打开相应的数据库BillingSys，在“查询编辑器”中输入代码：USEBillingSysGOCREATETABLECustomer(CIDintNOTNULLPRIMARYKEY,CNamevarchar(16)NULL,RIDintCONSTRAINTfk_RIDFOREIGNKEYREFERENCESRelationer(RID),CPasswordchar(6)NULL,CRegistrationDatedatetimeNULL,CTypechar(6)NULL,CStatusbitNULLCONSTRAINTck_CStatusCHECK(CStatus=0orCStatus=1),CAccountBalancenumeric(6,2)NULL);第五章SQLServer2008数据库和表SQLServer2008【补例1】写出在jxgl数据库中创建teachers表的SQL命令。代码如下：USEjxglGOCREATETABLEdbo.teachers(teach_idchar(6)NOTNULL,teach_namevarchar(30)NULL,teach_sexchar(10)NULL,dept_idchar(10)NULL,teach_birthdatetimeNULL,teach_birthplacevarchar(30)NULL,teach_emailvarchar(20)NULL,teach_telephonevarchar(20)NULL,teach_p