SQLServer数据库及T-SQL语句.ppt

第2讲SQLServer数据库及T-SQL语句,开始,结束,学习目标：,1、了解SQLServer数据库组织结构和存储方式。2、熟练运用企业器和T-SQL语言建立和管理数据库。3、熟悉查看和修改数据库选项。4、熟练掌握T-SQL查询语句。,内容提要:,2.1SQLServer数据库概念2.2创建、删除及修改数据库对象,2.1SQLServer数据库概念,2.1.1数据库文件分类SQLServer2000数据库的3类文件:1、主数据文件（PrimaryFile）数据库的起点，指向数据库中文件的其他部分，同时也用来存放用户数据。每个数据库都有一个仅有一个主数据文件，扩展名为.mdf。,2、辅助数据文件（SecondaryFile）专用于存放数据。无或有一个或多个辅助数据文件，扩展名为.ndf。用途：数据文件可放置不同的磁盘上，库容量则不再受磁盘空间的限制。3、事务日志文件（TransactionLogFile）存放恢复数据库所需的所有信息（记录对数据库中的数据进行修改操作的信息，如插入、修改、删除等操作）。每个数据库至少有一个事务日志文件，也可以有多个事务日志文件，其扩展名为.ldf。,主数据文件、事务日志文件、辅助文件默认存放位置是：C:ProgramFilesMicrosoftSQLServerMssqlData不能将数据文件和事务日志文件存放在压缩的文件系统上。新创建数据库的第一部分内容系统通过复制系统数据库model中的内容创建，剩余部分由空页填充。一个数据库的所有文件的位置都记录在系统数据库master中。,运行实例:查看当前实例的数据库注册信息：select*frommaster.dbo.sysdatabaseswherename=master获得当前实例数据库的名称、大小、创建日期等属性。execsp_helpdbmaster,2.1.2页1、页是数据库存储的基本单位，是一块大小为8KB的连续磁盘空间，即1MB（1MB=1024KB）空间可存储128个页。2、每页开始部分有96字节的页首：用于存储系统信息（页的类型、可用空间、所有者ID）。3、页的分类：数据页、索引页、文本和图像页等有8类。,4、事务日志文件不包含页，而包含一系列日志记录。5、数据库中惟一标识一页：ID和页码。运行实例:6、查看某一数据库对象的使用的存储空间：usemaster-打开数据库masterexecsp_spaceused,页码01:0000,主数据文件:文件ID01,页码01:0001,页码01:0002,页码02:0000,辅助数据文件:文件ID02,页码02:0001,页码02:0002,图1、数据库的文件存储页示例,2.1.3数据库文件组1、目的：进行合理分配与管理，以提高数据库系统的性能。2、文件组类型：（1）主文件组：包含主数据文件和任何没有明确指派给其他文件组的其他文件（还含系统表）。（2）用户定义的文件组。是在CreateDATABASE或ALTERDATABASE语句中，使用FILEGROUP关键指定的文件组。（未指定文件组，默认则为主文件组,其也是实现多个磁盘之间分配I/O的方法之一。）,数据库的对象集合,主文件.mdf,辅助文件.ndf,辅助文件.ndf,辅助文件.ndf,日志文件.ldf,PRIMARY组（主文件一定要属于该组）,用户命名的组,不属于任何组,图2、数据库的文件及其组的关系,2.1.3系统数据库及系统表,1、SQLSERVER的4个系统数据库：（1）master数据库：记录SQLSERVER系统的所有系统级信息。,（2）tempdb数据库：所有的临时表和临时存储过程，以及其他的临时存储空间的要求。（每次启动时重新建立，启动时为空，空间不够，系统会自动扩展它的大小）。（3）model数据库：模板数据库，当用户创建一个数据库时，model数据库的内容会自动复制到该数据库中。,（4）msdb数据库：用于存储作业、报警以及操作员信息，SQLSERVER代理服务通过这些信息调度作业、监视数据库系统的错误触发报警，并将作业或报报警的消息传递给操作员。2、系统实例数据库（1）Northwind:虚似公司的销售数据库（从事世界各地的特产食品进出贸易）。（2）pubs图书出版公司销售管理系统数据库。,2.2创建数据库对象,（1）创建数据库权限：登录账户须是sysadmin或dbcreator的服务器角色。（2）创建数据库的二种方法：1、企业管理器2、执行CREATEDATABASE语句,语法格式：CREATEDATABASE数据库名ONPRIMARY(NAME=数据文件的逻辑名,FILENAME=数据文件的物理名(.mdf或.ndf),SIZE=文件初始大小,MAXSIZE=文件的最大容量|UNLIMITED,FILEGROWTH=文件空间的增长量),.nFILEGROUP辅助文件的组名LOGON(NAME=日志文件的逻辑文件名,FILENAME=逻辑文件的物理名,SIZE=文件的初始大小,MAXSIZE=文件的最大容量|UNLIMITED,FILEGROWTH=文件空间的增长量),.nCOLLATEcollation_nameFORLOAD|FORATTACH,点击查看相关Word文档,database_name数据库名在服务器中必须唯一，并且符合标识符的规则。最多可以包含128个字符。如果没有指定日志文件的逻辑名，则MicrosoftSQLServer会通过向database_name追加后缀来生成逻辑名。该操作要求database_name在123个字符之内，以便生成的日志文件逻辑名少于128个字符。ON指定显式定义用来存储数据库数据文件。项用以定义用户文件组及其文件。n占位符，表示可以为新数据库指定多个文件。,（课后阅读）,LOGON指定显式定义用来存储数据库日志文件。以逗号分隔项列表，项用以定义日志文件。未指定LOGON，将自动创建一个日志文件，系统生成的名称，大小为数据库中所有数据文件总大小的25%。FORLOAD为了与早期版本MicrosoftSQLServer兼容。FORATTACH指定从现有的一组操作系统文件中附加数据库。必须有指定第一个主文件的条目。至于其它条目，只需要与第一次创建数据库或上一次附加数据库时路径不同的文件的那些条目。注：附加数据库常用sp_attach_db系统存储过程，只有必须指定16个以上的项目时，才需要使用CREATEDATABASEFORATTACH。,（课后阅读）,collation_name指定数据库的默认排序规则。排序规则名称既可以是Windows排序规则名称，也可以是SQL排序规则名称。PRIMARY指定主文件。如果没有指定PRIMARY，那么CREATEDATABASE语句中列出的第一个文件将成为主文件。NAME指定逻辑名称。如果指定了FORATTACH，则不需要指定NAME参数。,（课后阅读）,逻辑文件名(logical_file_name)Transact-SQL语句中引用文件的名称，在数据库中必须唯一。FILENAME：指定操作系统文件名。物理文件时使用的路径名和文件名。使用绝对路径。SIZE：指定的文件的大小。未指定，使用model数据库中的主文件大小。如果次要文件或日志文件没有指定SIZE参数，则为1MB。MAXSIZE：文件可以增长到的最大大小，默认为。UNLIMITED：定义的文件将增长到磁盘变满为止。FILEGROWTH：定义的文件的增长增量。文件的FILEGROWTH设置不能超过MAXSIZE设置，最小值为64KB。默认值为10%。,（课后阅读）,创建数据库实例：A.不指定文件创建数据库下面的示例创建名为mytest的数据库，并创建相应的主文件和事务日志文件。CREATEDATABASEmytest,实例一:,B.实例：不指定SIZE创建数据库下面的示例创建名为products2的数据库。文件prods2_dat将成为主文件，大小等于model数据库中主文件的大小。（事务日志文件会自动创建，其大小为主文件大小的25%或512KB中的较大值。因为没有指定MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止）。USEmasterGOcreatedatabaseProducts2on(name=prods2_dat,filename=d:programfilesmicrosoftsqlservermssqldataprods2.mdf),C.实例：本例创建名为Products的数据库，并指定单个文件大小，最大大小与增量。USEmasterGOCREATEDATABASEProductsON(NAME=prods_dat,FILENAME=d:programfilesmicrosoftsqlservermssqldataprods.mdf,SIZE=4,MAXSIZE=10,FILEGROWTH=1)注：默认为,D.创建指定数据文件和事务日志文件的数据库下面的示例创建名为Sales的数据库。Sales_dat文件的SIZE参数没有指定MB或KB，因此默认为MB，以兆字节为单位进行分配。Sales_log文件以兆字节为单位进行分配，因为SIZE参数中显式声明了MB后缀。）,USEmasterGO/*将master数据库置为当前数据库*/CREATEDATABASESalesON(NAME=Sales_dat,FILENAME=d:ProgramFilesMicrosoftSQLServerMSSQLDatasaledat.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME=Sales_log,FILENAME=d:ProgramFilesMicrosoftSQLServerMSSQLDatasalelog.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),E.指定多个数据文件和事务日志文件创建数据库下面的示例使用三个100MB的数据文件和两个100MB的事务日志文件创建了名为Archive的数据库。主文件是列表中的第一个文件，并使用PRIMARY关键字显式指定。事务日志文件在LOGON关键字后指定。注意FILENAME选项中所用的文件扩展名：主要数据文件使用.mdf，次要数据文件(辅助数据文件)使用.ndf，事务日志文件使用.ldf。,USEmasterGOCREATEDATABASEArchiveONPRIMARY(NAME=Arch1,FILENAME=c:programfilesmicrosoftsqlservermssqldataarchdat1.mdf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch2,FILENAME=c:programfilesmicrosoftsqlservermssqldataarchdat2.ndf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Arch3,FILENAME=c:programfilesmicrosoftsqlservermssqldataarchdat3.ndf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),LOGON(NAME=Archlog1,FILENAME=c:programfilesmicrosoftsqlservermssqldataarchlog1.ldf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),(NAME=Archlog2,FILENAME=c:programfilesmicrosoftsqlservermssqldataarchlog2.ldf,SIZE=100MB,MAXSIZE=200,FILEGROWTH=20),F.使用文件组创建数据库下面的示例使用三个文件组创建名为sales的数据库：主文件组包含文件Spri1_dat和Spri2_dat。指定这些文件的FILEGROWTH增量为15%。名为SalesGroup1的文件组包含文件SGrp1Fi1和SGrp1Fi2。名为SalesGroup2的文件组包含文件SGrp2Fi1和SGrp2Fi2。,CREATEDATABASESalesONPRIMARY(NAME=SPri1_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSPri1dat.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=SPri2_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSPri2dt.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUPSalesGroup1(NAME=SGrp1Fi1_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSG1Fi1dt.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=SGrp1Fi2_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSG1Fi2dt.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPSalesGroup2(NAME=SGrp2Fi1_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSG2Fi1dt.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=SGrp2Fi2_dat,FILENAME=c:programfilesmicrosoftsqlservermssqldataSG2Fi2dt.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),LOGON(NAME=Sales_log,FILENAME=c:programfilesmicrosoftsqlservermssqldatasalelog.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB),G.分离与附加数据库示例创建一个包含下列物理文件的名为Archive的数据库：c:programfilesmicrosoftsqlservermssqldataarchdat1.mdfc:programfilesmicrosoftsqlservermssqldataarchdat2.ndfc:programfilesmicrosoftsqlservermssqldataarchdat3.ndfc:programfilesmicrosoftsqlservermssqldataarchlog1.ldfc:programfilesmicrosoftsqlservermssqldataarchlog2.ldf,）使用sp_detach_db系统存储过程分离该数据库，然后使用带有FORATTACH子句的CREATEDATABASE重新附加。sp_detach_dbArchiveGOCREATEDATABASEArchiveONPRIMARY(FILENAME=d:programfilesmicrosoftsqlservermssqldataarchdat1.mdf)FORATTACHGO）使用企业管理器分离数据库,二、查看数据库信息的方法：、企业管理器服务器组-服务器-数据库节点，右击“属性”。、Transact-SQL调用系统存储过程：Sp_helpdb:查看所有数据库定义信息。Sp_helpdb数据库名,删除和修改SQLSERVER数据库1、删除数据库DROPDATABASEdatabase_name,n2、修改数据库,ALTERDATABASE提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力。ALTERDATABASEdatabaseADDFILE,.nTOFILEGROUPfilegroup_name|ADDLOGFILE,.n|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_namefilegroup_property|NAME=new_filegroup_name|SET,.nWITH|COLLATE,（1）扩充数据库空间使用企业管理器增加数据库空间注：重新指定的数据库空间必须大于现有空间。使用T-SQL命令增加数据库空间ALTERDATABASE数据库名MODIFYFILE（NAME=逻辑文件名，SIZE=文件大小，MAXSIZE=增长限制）,实例：将数据库Archive的数据文件Arch1的初始空间、最大空间分别由原来的100MB和200MB修改为120MB和220MB。ALTERDATABASEArchiveMODIFYFILE(NAME=Arch1,SIZE=120,MAXSIZE=220),（2）向数据库中添加文件下例创建数据库，并更改该数据库以添加一个5MB大小的新数据文件。USEmasterGOCREATEDATABASETest1ON(NAME=Test1dat1,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDatat1dat1.mdf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)GO,ALTERDATABASETest1ADDFILE(NAME=Test1dat2,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDatat1dat2.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),（3）向数据库中添加由两个文件组成的文件组下例在示例（）中所创建的Test1数据库中创建一个文件组，并将两个5MB的文件添加到该文件组。然后下例将Test1FG1设置为默认文件组。USEmasterGOALTERDATABASETest1ADDFILEGROUPTest1FG1GO,ALTERDATABASETest1MODIFYFILEGROUPTest1FG1DEFAULTALTERDATABASETest1ADDFILE(NAME=test1dat3,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDatat1dat3.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),(NAME=test1dat4,FILENAME=c:ProgramFilesMicrosoftSQLServerMSSQLDatat1dat4.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TOFILEGROUPTest1FG1,（4）向数据库中添加两个日