大型数据库应用-第2章

大型数据库应用

——SQLServer主讲教师：肖连2第二章数据库和表的创建目的与要求：掌握基本概念：逻辑数据库、物理数据库、数据库对象、数据文件、文件组掌握使用SSMS创建、修改和删除数据库和表的操作方法掌握创建、修改和删除数据库、表的命令3第二章数据库创建教学内容：SQLServer数据库基本概念界面创建数据库命令方式创建数据库42.1SQLServer基本概念数据库系统包括：数据库、数据库管理系统、应用程序、用户数据库管理系统(DBMS)数据库管理系统是一个软件系统，能够对数据库进行有效管理(存储、任务、安全性、完整性、并发控制等)；通常能方便用户快速建立、维护、修改、检索和删除数据库中的数据。52.1SQLServer基本概念数据库数据库是相互关联的数据集合，通常是一系列表的结合。数据库是具有逻辑关系和确定意义的数据集合，在逻辑上没有关系的数据集合不是数据库。数据库是针对明确的应用目标而设计的，每一个数据库都有自己的一组用户以及为这组用户服务的应用程序。62.1SQLServer基本概念逻辑数据库和物理数据库数据库通常划分为用户视图（逻辑数据库）和物理视图（物理数据库）。用户视图（逻辑数据库）是用户看到和操作的数据库。物理视图（物理数据库）是数据库在磁盘上的文件存储。Sales数据库SQLServer表视图索引存储过程……数据库的逻辑组件（数据库对象）用户视图数据库的物理实现（数据库文件）物理视图SalesDat1.mdfSalesDat2.ndfSalesLog1.ldf……主数据文件次数据文件日志文件72.1SQLServer基本概念逻辑数据库逻辑数据库是DBMS对数据库中信息的封装，是DBMS提供给用户或数据库应用程序的统一访问接口。逻辑数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。组成逻辑数据库的逻辑成分称为数据库对象。数据库对象说明表由行和列构成的集合，用来存储数据数据类型定义列或变量的数据类型，SQLServer提供了系统数据类型，并允许用户自定义数据类型视图由表或其他视图导出的虚拟表索引为数据快速检索提供支持且可以保证数据唯一性的辅助数据结构约束用于为表中的列定义完整性的规则默认值为列提供的缺省值存储过程存放于服务器的预先编译好的一组T-SQL语句触发器特殊的存储过程，当用户表中数据改变时，该存储过程被自动执行82.1SQLServer基本概念数据据库对象每个数据库对象都有名称，用户可以给出两种对象名：完全限定名和部分限定名。完全限定名是对象的全名，而且每个对象都必须有一个唯一的完全限定名：服务器名.数据库名.数据库架构名.对象名根据系统的当前工作环境可以省略全名的前3个部分，这是部分限定名。对象名是逻辑名，最长为30个字符，不区分大小写。92.1SQLServer基本概念逻辑数据库也可分为两类：系统数据库和用户数据库。用户数据库：用户创建的数据库。系统数据库：存储有关SQLServer的系统信息，SQLServer使用系统数据库来管理系统。系统数据库有：master,model,msdb,tempdb102.1SQLServer基本概念master(控制)数据库总控数据库，它是最重要的系统数据库，记录系统中所有系统级的信息model(模板)数据库为用户新创建的数据库提供模板和原型，它包含了用户数据库中应该包含的所有系统表的结构msdb数据库供SQLServer代理程序调度警报作业以及记录操作时使用tempdb数据库保存所有的临时表和临时存储过程112.1SQLServer基本概念物理数据库物理数据库是从数据库管理员的观点出发的，即数据库是存储逻辑数据库的各种对象的实体。它包括文件及文件组，还有页和盘区，主要涉及SQLServer如何为数据库分配空间。了解数据库的物理实现有助于规划和分配给数据库的磁盘容量。在SQLServer中，数据存储的基本单位是页,页的大小是8KB。即SQLServer数据库每兆字节有128页。122.1SQLServer基本概念文件SQLServer是用一组系统文件来存储数据库的各种逻辑成分，它包括：主数据文件、辅助数据文件和日志文件。一个数据库的文件集(仅有一个)主文件组主数据文件*.mdf仅有一个次数据文件*.ndf零到多个(零到多个)次文件组次数据文件*.ndf零到多个事务日志文件*.ldf一到多个事务日志132.1SQLServer基本概念创建一个数据库后，该数据库中至少包括一个主文件和一个日志文件。这些文件不是用户使用的，而是由系统使用的。142.1SQLServer基本概念主数据文件每个数据库都必须包括且仅包扩一个主数据文件，默认扩展名为.MDF。主数据文件是数据库中关键文件，包含了数据库的启动信息，并且存储数据。152.1SQLServer基本概念辅助数据文件（次数据文件）辅助数据文件用于存储未包括在主文件内的其他数据，默认扩展名为.NDF。辅助文件是可选的。如果数据库较小，就可以不用辅助文件；如果数据库较大，根据需要可以创建多个辅助文件。采用多个数据文件来存储数据使得数据文件可以不断扩充而不受操作系统文件大小的限制，将数据文件存储在不同的硬盘使得DBMS可以同时对几个硬盘进行数据存取，提高了数据处理效率。在FAT32（windows）格式的文件系统下,单个文件最大只能是2GB162.1SQLServer基本概念事务日志文件日志文件用于保存恢复数据库所需的事务日志信息。每个数据库至少有一个日志文件。日志文件的扩展名为.LDF。日志文件包含一系列数据库更新信息的纪录，不属于任何文件组，也不以页为存储单位。172.1SQLServer基本概念文件组文件组是为了管理和分配数据而将文件组织在一起，通常可以为一个磁盘驱动器创建一个文件组，然后将特定的表、索引等与该文件组相关联，则对这些表的存储、查询和修改等操作都在该文件组中。文件组包括主文件组和用户定义文件组。主文件组中包含主数据文件和任何没有明确指派给其他文件组的文件。用户定义文件组是使用CREATDATABASE语句和ALTERDATABASE语句创建或修改数据库时指定的文件组。182.1SQLServer基本概念文件组每个数据库中都有一个文件组作为默认文件组运行。如果没有指定默认文件组，则主文件组是默认文件组。若不指定用户定义文件组，则所有数据文件都包含在主文件组中。只有数据文件才能作为文件组的成员；日志文件不能作为文件组成员。设计文件和文件组时，一个文件只能属于一个文件组。192.1SQLServer基本概念表表示用来存储和操作数据的一种逻辑结构。表由行和列组成。每个数据库包含了若干个表。表的概念：记录字段关键字（主键）202.2数据库的创建、修改和删除例[1]创建数据库XSCJ。主数据文件（默认逻辑名称为XSCJ_Data）初始大小为5MB，数据库自动增长，增长方式按10%比例增长；日志文件（默认逻辑名称为XSCJ_LOG）初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按10%比例增长）；所有者是Administrator。数据库按百分比增长：假如文件初始大小是100MB，百分比是10%，那么下次自动增长时，文件大小将变为110MB，即增加原大小的10%，再发生增长时，文件大小变为121MB。以Administrator身份登录计算机，并启动SQLServer服务。212.2数据库的创建、修改和删除修改数据库在数据库创建后，数据文件和日志文件名就不能改变了。数据库可进行的修改操作有：增加或删除数据文件（只针对次数据文件）改变数据文件的大小和增长方式改变日志文件的大小和增长方式增加或删除日志文件增加或删除文件组222.2数据库的创建、修改和删除例[2]将数据库XSCJ的主数据文件XSCJ_Data.mdf的最大大小修改为不受限制；增加数据文件XSCJBAK，其属性均取系统默认值；增加一个名为FGroup的文件组，并在该文件组中增加数据文件XSCJ2；分别删除数据文件XSCJBAK和文件组FGroup。232.2数据库的创建、修改和删除删除数据库删除数据库后，该数据库的所有对象均被删除，将不能再对数据库进行任何操作。例[3]将数据库XSCJ删除。242.2数据库的创建、修改和删除使用CREATEDATABASE创建数据库语法格式：CREATEDATABASEdatabase_name

[ON [<filespec>[,…n]] [,<filegroup>[,…n]]][LOGON{<filespec>[,…n]}][FORATTACH]252.2数据库的创建、修改和删除Transact-SQL语法规则大写

Transact-SQL关键字。小写

Transact-SQL语法中用户提供的参数。|

（竖线）分隔方括号或大括号内的语法项目。表示只能选择一个项目。[]（方括号）可选语法项目。{}（大括号）必选语法项。[,...n]

表示前面的项可重复n

次。每一项由逗号分隔。262.2数据库的创建、修改和删除Transact-SQL语法规则[...n] 表示前面的项可重复n

次。每一项由空格分隔。<标签>::=语法块的名称。此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组和标记。适合使用语法块的每个位置由括在尖括号内的标签表示：<标签>，如<filespec>。272.2数据库的创建、修改和删除使用CREATEDATABASE创建数据库语法格式：CREATEDATABASEdatabase_name

[ON [<filespec>[,…n]] [,<filegroup>[,…n]]][LOGON{<filespec>[,…n]}][FORATTACH]Database_name是要创建的数据库的名称。数据库名称在服务器中必须唯一。名称要符合标志命名规则，最多包含128个字符。282.2数据库的创建、修改和删除使用CREATEDATABASE创建数据库语法格式：CREATEDATABASEdatabase_name

[ON [<filespec>[,…n]] [,<filegroup>[,…n]]][LOGON{<filespec>[,…n]}][FORATTACH]ON：指定一个或多个数据文件和一个或多个文件组（以逗号分隔）。<filespec>::=[PRIMARY]--用来指定主文件，若不指定默认第一个文件为主文件( [NAME=logical_file_name,] FILENAME=‘os_file_name’ [,SIZE=size] [,MAXSIZE={max_size|UNLIMITED}] [,FILEGROWTH=growth_increment])292.2数据库的创建、修改和删除使用CREATEDATABASE创建数据库语法格式：CREATEDATABASEdatabase_name

[ON [<filespec>[,…n]] [,<filegroup>[,…n]]][LOGON{<filespec>[,…n]}][FORATTACH]<filegroup>::=FILEGROUPfilegroup_name<fielspec>[,…n]定义文件组的属性。文件组中各文件的描述和数据文件描述相同。302.2数据库的创建、修改和删除使用CREATEDATABASE创建数据库语法格式：CREATEDATABASEdatabase_name

[ON [<filespec>[,…n]] [,<filegroup>[,…n]]][LOGON{<filespec>[,…n]}][FORATTACH]LOGON子句：用于指定数据库事务日志文件的属性312.2数据库的创建、修改和删除【例】forattach的使用说明createdatabasexscjon(filename='d:\xscj_data.mdf'),(filename='d:\xscj_log.ldf')forattach即根据已有的文件附加数据库。注1：必须指出主文件；注2：指定日志文件时不要加logon短语。322.2数据库的创建、修改和删除查看数据库信息（补充）使用系统存储过程sp_helpdb打开或切换数据库

USEdatabase_name332.2数据库的创建、修改和删除[例1]创建名为Sales的数据库，数据库的属性均采用默认值。该语句执行后，自动创建一个主数据文件Sales.mdf，一个日志文件Sales_log.ldf，文件存储在默认路径下。主数据文件和事务日志文件的大小及容量情况均与相同。CREATEDATABASE

Salesmodel数据库342.2数据库的创建、修改和删除[例2]创建名为Sales2的数据库，只指定数据文件的逻辑文件名和物理文件名。CREATEDATABASESales2ON(NAME=Sales2_dat,FILENAME=‘D:\sql\Sales2.mdf’)

注意：数据文件指定的路径必须已经存在。数据库的逻辑名称用来在T-SQL代码中引用数据库，在数据库中该名称应保持唯一。逻辑名可以省略引号定界符。物理文件名指数据库文件在硬盘上的存放路径与文件名称，此目录必须是本地目录。物理文件名必须加引号定界符。352.2数据库的创建、修改和删除[例3]创建名为Sales3的数据库，指定数据文件的逻辑文件名和物理文件名，并指定数据文件的初始容量大小、最大容量以及增长方式。CREATEDATABASESales3ON(NAME=Sales3_dat,FILENAME=‘D:\sql\Sales3.mdf’,SIZE=4,MAXSIZE=10,FILEGROWTH=1)

注意：该语句中涉及文件大小的单位是MB。文件增长的单位可以是KB,MB,或%,不指定时默认为MB。36CREATEDATABASESales4ON(NAME=Sales4_dat,FILENAME=‘D:\sql\Sales4.mdf’,SIZE=10000KB,MAXSIZE=500000KB,FILEGROWTH=5%)LOGON(NAME=‘Sales4_log’,FILENAME=‘D:\sql\Sales4.ldf’,size=5,MAXSIZE=25,FILEGROWTH=5)[例4]创建名为Sales4的数据库，指定数据文件和日志文件。372.2数据库的创建、修改和删除[例5]创建名为Sales5的数据库，指定多个数据文件和多个日志文件。CREATEDATABASESales5ONPRIMARY(NAME=Sales5_1,FILENAME=‘D:\sql\Sales5Dat1.mdf’,SIZE=100,MAXSIZE=200,FILEGROWTH=20)，(NAME=Sales5_2,FILENAME=‘D:\sql\Sales5Dat2.ndf’,SIZE=100,MAXSIZE=200,FILEGROWTH=20)，(NAME=Sales5_3,FILENAME=‘D:\sql\Sales5Dat3.ndf’,SIZE=100,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME=‘Sales5_log1’,FILENAME=‘D:\sql\Saleslog1.ldf’,size=5,MAXSIZE=25,FILEGROWTH=5)，(NAME=‘Sales5_log2’,FILENAME=‘D:\sql\Saleslog2.ldf’,size=5,MAXSIZE=25,FILEGROWTH=5)38CREATEDATABASESales6ONPRIMARY(NAME=Sales6_1,FILENAME='D:\sql\Sales6Dat1.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=10%),(NAME=Sales6_2,FILENAME='D:\sql\Sales6Dat2.ndf',SIZE=100,MAXSIZE=200,FILEGROWTH=20),FILEGROUPSales6_group1(NAME=Sales6_group1_dat1,FILENAME='c:\Sales6group1Dat1.ndf',SIZE=100,MAXSIZE=200,FILEGROWTH=20),FILEGROUPSales6_group2(NAME=Sales6_group2_dat1,FILENAME='c:\Sales6group2Dat1.ndf',SIZE=100,MAXSIZE=200,FILEGROWTH=20),(NAME=Sales6_group2_dat2,FILENAME='c:\Sales6group2Dat2.ndf',SIZE=100,MAXSIZE=200,FILEGROWTH=20)LOGON(NAME='Sales6_log1',FILENAME='D:\sql\Sales6log1.ldf',size=5,MAXSIZE=25,FILEGROWTH=5)[例6]创建名为Sales6的数据库，增加用户定义文件组。392.2数据库的创建、修改和删除使用ALTERDATABASE修改数据库语法格式：ALTERDATABASEdatabase_name

{ADDFILE<filespec>[,…n][TOGILEGROUPfilegroup_name]|REMOVEFILElogical_file_name|MODIFYFILE<filespec>|ADDLOGFILE<filespec>[,…n]|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}|MODIFYNAME=new_dbname|SET<optionspec>[,…n]}402.2数据库的创建、修改和删除[例1]将数据库名Sales变成Sales1。注：该语句要求当前数据库只有一个用户连接，否则该语句将失败。也可使用系统存储过程sp_renamedb实现：

execsp_renamedb'sales','sales1'

ALTERDATABASESalesMODIFYNAME=Sales1412.2数据库的创建、修改和删除[例2]向数据库Sales中添加数据文件。alterdatabasesales

addfile

(name=sales1_dat1,filename='d:\sql\sales1.mdf',size=5,maxsize=50,filegrowth=10%)422.2数据库的创建、修改和删除[例3]删除数据库Sales中数据文件alterdatabasesales

removefilesales1_dat1432.2数据库的创建、修改和删除[例4]向数据库Sales中添加两个文件组成的文件组。alterdatabasesalesaddfilegroupsales_groupgoalterdatabasesalesaddfile

(name=sales_group1,filename='d:\sql\sales_g_dat1.ndf',size=2,maxsize=50,filegrowth=10%),(name=sales_group2,filename='d:\sql\sales_g_dat2.ndf',size=2,maxsize=50,filegrowth=10%)tofilegroupsales_groupgoalterdatabasesalesmodifyfilegroupsales_groupdefaultgo完成文件组的添加向文件组中添加文件将Sales_group设为默认文件组442.2数据库的创建、修改和删除[例4]向数据库Sales中添加两个文件组成的文件组。alterdatabasesalesaddfilegroupsales_groupgoalterdatabasesalesaddfile(name=sales_group1,filename='d:\sql\sales_g_dat1.ndf',size=2,maxsize=50,filegrowth=10%),(name=sales_group2,filename='d:\sql\sales_g_dat2.ndf',size=2,maxsize=50,filegrowth=10%)tofilegroupsales_groupgoalterdatabasesalesmodifyfilegroupsales_groupdefaultgoGO是一个SQLServer命令，是批处理的结束标志。GO命令不能与SQLServer命令在同一行上。45批处理批处理是包含一个或多个T-SQL语句的组，它将一次性地发送到SQLServer中执行。用GO通知SQLServer一批T-SQL语句的结束。大多数CREATE命令要在单个批命令中执行，如CREATEVIEW，但CREATEDATABASE,CREATETABLE,CREATEINDEX例外。462.2数据库的创建、修改和删除[例5]删除数据库Sales6中的文件组sales6_group1。alterdatabasesales6removefileSales6_group1_dat1goalterdatabasesales6removefilegroupSales6_group1go

要删除文件组必须先删除文件组中的所有文件。472.2数据库的创建、修改和删除[例6]向数据库Sales中添加日志文件Sale_log2。alterdatabasesalesaddlogfile(name=sales_log2,filename='d:\sql\sales_log2.ldf')482.2数据库的创建、修改和删除[例7]修改数据库Sales中日志文件Sale_log2的初始大小。alterdatabasesalesmodifyfile(name=sales_log2,size=10,maxsize=50,filegrowth=10)49增缩数据库容量（补充）【例1】

book数据库的数据库文件book.mdf的初始分配空间大小为55MB，现在想将其大小扩充到60MB，则具体语句如下：【例2】将book数据库的空间缩减至最小容量。

DBCCSHRINKDATABASE('book')--以每个文件为单位对数据文件进行收缩。ALTERDATABASEbookMODIFYFILE(NAME=book_data,SIZE=60)50查看和更改数据库选项（补充）【例3】将book数据库设为只读状态EXECsp_dboption'book','readonly',True【例4】将book数据库设为单用户状态EXECsp_dboption‘book','singleuser','true‘为了不影响使用需改回默认的多用户状态EXECsp_dboption‘book’,‘singleuser’,‘false‘512.2数据库的创建、修改和删除使用DROPDATABASE删除数据库语法格式：DropDATABASEdatabase_name[,…n]

删除数据库时，组成该数据库的所有磁盘文件将同时被删除。522.2数据库的创建、修改和删除[例1]删除数据库Sales。dropdatabasesales[例2]删除数据库Sales2，Sales3。dropd