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

第2讲 SQL Server数据库及T-SQL语句,开始,结束,学习目标：,1、了解SQL Server数据库组织结构和存储方式。 2、熟练运用企业器和T-SQL语言建立和管理数据库。 3、熟悉查看和修改数据库选项。 4、熟练掌握T-SQL查询语句。,内容提要:,2.1 SQL Server数据库概念 2.2 创建、删除及修改数据库对象,2.1 SQL Server数据库概念,2.1.1数据库文件分类 SQL Server 2000数据库的3类文件: 1、主数据文件（Primary File） 数据库的起点，指向数据库中文件的其他部分，同时也用来存放用户数据。 每个数据库都有一个仅有一个主数据文件，扩展名为.mdf。,2、辅助数据文件（Secondary File） 专用于存放数据。 无或有一个或多个辅助数据文件，扩展名为.ndf。 用途：数据文件可放置不同的磁盘上，库容量则不再受磁盘空间的限制。 3、事务日志文件（Transaction Log File） 存放恢复数据库所需的所有信息（记录对数据库中的数据进行修改操作的信息，如插入、修改、删除等操作）。 每个数据库至少有一个事务日志文件，也可以有多个事务日志文件，其扩展名为.ldf。,主数据文件、事务日志文件、辅助文件默认存放位置是： C:Program FilesMicrosoft SQL ServerMssqlData 不能将数据文件和事务日志文件存放在压缩的文件系统上。 新创建数据库的第一部分内容系统通过复制系统数据库model中的内容创建，剩余部分由空页填充。 一个数据库的所有文件的位置都记录在系统数据库master中。,运行实例: 查看当前实例的数据库注册信息： select * from master.dbo.sysdatabases where name=master 获得当前实例数据库的名称、大小 、创建日期等属性。 exec sp_helpdb master,2.1.2页 1、页是数据库存储的基本单位，是一块大小为8KB的连续磁盘空间，即1MB（1MB=1024KB）空间可存储128个页。 2、每页开始部分有96字节的页首：用于存储系统信息（页的类型、可用空间、所有者ID）。 3、页的分类：数据页、索引页、文本和图像页等有8类。,4、事务日志文件不包含页，而包含一系列日志记录。 5、数据库中惟一标识一页：ID和页码。 运行实例: 6、查看某一数据库对象的使用的存储空间： use master -打开数据库master exec sp_spaceused,页码 01:0000,主数据文件:文件ID01,页码 01:0001,页码 01:0002,页码 02:0000,辅助数据文件:文件ID02,页码 02:0001,页码 02:0002,图1、数据库的文件存储页示例,2.1.3数据库文件组 1、目的：进行合理分配与管理，以提高数据库系统的性能。 2、文件组类型： （1）主文件组：包含主数据文件和任何没有明确指派给其他文件组的其他文件（还含系统表）。 （2）用户定义的文件组。是在Create DATABASE或ALTER DATABASE语句中，使用FILEGROUP关键指定的文件组。（未指定文件组，默认则为主文件组,其也是实现多个磁盘之间分配I/O的方法之一。）,数据库的 对象集合,主文件 .mdf,辅助 文件 .ndf,辅助 文件 .ndf,辅助 文件 .ndf,日志 文件 .ldf,PRIMARY组 （主文件一定 要属于该组）,用户命名的组,不属于任何组,图2、数据库的文件及其组的关系,2.1.3系统数据库及系统表,1、SQL SERVER的4个系统数据库： （1）master数据库：记录SQL SERVER系统的所有系统级信息。,（2）tempdb数据库：所有的临时表和临时存储过程，以及其他的临时存储空间的要求。 （每次启动时重新建立，启动时为空，空间不够，系统会自动扩展它的大小）。 （3）model数据库：模板数据库，当用户创建一个数据库时，model数据库的内容会自动复制到该数据库中。,（4）msdb数据库：用于存储作业、报警以及操作员信息，SQL SERVER代理服务通过这些信息调度作业、监视数据库系统的错误触发报警，并将作业或报报警的消息传递给操作员。 2、系统实例数据库 （1）Northwind:虚似公司的销售数据库（从事世界各地的特产食品进出贸易）。 （2）pubs图书出版公司销售管理系统数据库。,2.2 创建数据库对象,（1）创建数据库权限：登录账户须是sysadmin或dbcreator的服务器角色。 （2）创建数据库的二种方法： 1、企业管理器 2、执行CREATE DATABASE语句,语法格式： CREATE DATABASE 数据库名 ON PRIMARY ( NAME = 数据文件的逻辑名 , FILENAME = 数据文件的物理名(.mdf或.ndf) , SIZE = 文件初始大小 , MAXSIZE = 文件的最大容量 | UNLIMITED , FILEGROWTH = 文件空间的增长量 ) ,.n FILEGROUP 辅助文件的组名 LOG ON ( NAME = 日志文件的逻辑文件名, FILENAME = 逻辑文件的物理名 , SIZE = 文件的初始大小 , MAXSIZE = 文件的最大容量 | UNLIMITED , FILEGROWTH = 文件空间的增长量 ) ,.n COLLATE collation_name FOR LOAD | FOR ATTACH ,点击查看相关 Word文档,database_name 数据库名 在服务器中必须唯一，并且符合标识符的规则。最多可以包含 128 个字符。如果没有指定日志文件的逻辑名，则 Microsoft SQL Server 会通过向 database_name 追加后缀来生成逻辑名。该操作要求 database_name 在 123 个字符之内，以便生成的日志文件逻辑名少于 128 个字符。 ON 指定显式定义用来存储数据库数据文件。 项用以定义用户文件组及其文件。 n 占位符，表示可以为新数据库指定多个文件。,（课后阅读）,LOG ON 指定显式定义用来存储数据库日志文件。以逗号分隔 项列表， 项用以定义日志文件。未指定 LOG ON，将自动创建一个日志文件，系统生成的名称，大小为数据库中所有数据文件总大小的 25%。 FOR LOAD 为了与早期版本 Microsoft SQL Server 兼容。 FOR ATTACH 指定从现有的一组操作系统文件中附加数据库。必须有指定第一个主文件的 条目。至于其它 条目，只需要与第一次创建数据库或上一次附加数据库时路径不同的文件的那些条目。注：附加数据库常用 sp_attach_db 系统存储过程，只有必须指定 16 个以上的 项目时，才需要使用 CREATE DATABASE FOR ATTACH。,（课后阅读）,collation_name 指定数据库的默认排序规则。排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 PRIMARY 指定主文件。如果没有指定 PRIMARY，那么 CREATE DATABASE 语句中列出的第一个文件将成为主文件。 NAME 指定逻辑名称。如果指定了 FOR ATTACH，则不需要指定 NAME 参数。,（课后阅读）,逻辑文件名(logical_file_name) Transact-SQL 语句中引用文件的名称，在数据库中必须唯一。 FILENAME：指定操作系统文件名。 物理文件时使用的路径名和文件名。使用绝对路径。 SIZE：指定的文件的大小。未指定，使用 model 数据库中的主文件大小。如果次要文件或日志文件没有指定 SIZE 参数，则为1MB。 MAXSIZE：文件可以增长到的最大大小，默认为。 UNLIMITED：定义的文件将增长到磁盘变满为止。 FILEGROWTH：定义的文件的增长增量。文件的 FILEGROWTH 设置不能超过 MAXSIZE 设置，最小值为 64 KB。默认值为 10%。,（课后阅读）,创建数据库实例： A. 不指定文件创建数据库 下面的示例创建名为 mytest 的数据库，并创建相应的主文件和事务日志文件。 CREATE DATABASE mytest,实例一:,B.实例：不指定 SIZE 创建数据库 下面的示例创建名为 products2 的数据库。文件 prods2_dat 将成为主文件，大小等于 model 数据库中主文件的大小。（事务日志文件会自动创建，其大小为主文件大小的 25% 或 512 KB 中的较大值。因为没有指定 MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止）。 USE master GO create database Products2 on (name=prods2_dat,filename=d:program filesmicrosoft sql servermssqldataprods2.mdf),C. 实例： 本例创建名为 Products 的数据库，并指定单个文件大小，最大大小与增量。 USE master GO CREATE DATABASE Products ON ( NAME = prods_dat, FILENAME = d:program filesmicrosoft sql servermssqldataprods.mdf, SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1 ) 注：默认为,D. 创建指定数据文件和事务日志文件的数据库 下面的示例创建名为 Sales 的数据库。Sales_dat 文件的 SIZE 参数没有指定 MB 或 KB，因此默认为 MB，以兆字节为单位进行分配。Sales_log 文件以兆字节为单位进行分配，因为 SIZE 参数中显式声明了 MB 后缀。）,USE master GO /*将master数据库置为当前数据库*/ CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = d:Program FilesMicrosoft SQL ServerMSSQLDatasaledat.mdf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = d:Program FilesMicrosoft SQL ServerMSSQLDatasalelog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ),E. 指定多个数据文件和事务日志文件创建数据库 下面的示例使用三个 100 MB 的数据文件和两个 100 MB 的事务日志文件创建了名为 Archive 的数据库。主文件是列表中的第一个文件，并使用 PRIMARY 关键字显式指定。事务日志文件在 LOG ON 关键字后指定。注意 FILENAME 选项中所用的文件扩展名： 主要数据文件使用 .mdf，次要数据文件(辅助数据文件)使用 .ndf，事务日志文件使用 .ldf。,USE master GO CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat1.mdf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat2.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch3, FILENAME = c:program filesmicrosoft sql servermssqldataarchdat3.ndf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),LOG ON ( NAME = Archlog1, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog1.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Archlog2, FILENAME = c:program filesmicrosoft sql servermssqldataarchlog2.ldf, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),F. 使用文件组创建数据库 下面的示例使用三个文件组创建名为 sales 的数据库： 主文件组包含文件 Spri1_dat 和 Spri2_dat。指定这些文件的 FILEGROWTH 增量为 15%。 名为 SalesGroup1 的文件组包含文件 SGrp1Fi1 和 SGrp1Fi2。 名为 SalesGroup2 的文件组包含文件 SGrp2Fi1 和 SGrp2Fi2。,CREATE DATABASE Sales ON PRIMARY ( NAME = SPri1_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSPri1dat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), ( NAME = SPri2_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSPri2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ),FILEGROUP SalesGroup1 ( NAME = SGrp1Fi1_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSG1Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp1Fi2_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSG1Fi2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),FILEGROUP SalesGroup2 ( NAME = SGrp2Fi1_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSG2Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = SGrp2Fi2_dat, FILENAME = c:program filesmicrosoft sql servermssqldataSG2Fi2dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),LOG ON ( NAME = Sales_log, FILENAME = c:program filesmicrosoft sql servermssqldatasalelog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ),G. 分离与附加数据库 示例创建一个包含下列物理文件的名为 Archive 的数据库： c:program filesmicrosoft sqlservermssqldataarchdat1.mdf c:program filesmicrosoft sqlservermssqldataarchdat2.ndf c:program filesmicrosoft sqlservermssqldataarchdat3.ndf c:program filesmicrosoft sqlservermssqldataarchlog1.ldf c:program filesmicrosoft sqlservermssqldataarchlog2.ldf,）使用sp_detach_db系统存储过程分离该数据库，然后使用带有 FOR ATTACH 子句的 CREATE DATABASE 重新附加。 sp_detach_db Archive GO CREATE DATABASE Archive ON PRIMARY (FILENAME = d:program filesmicrosoft sql servermssqldataarchdat1.mdf) FOR ATTACH GO ）使用企业管理器分离数据库,二、查看数据库信息的方法： 、企业管理器 服务器组-服务器-数据库节点，右击“属性”。 、Transact-SQL 调用系统存储过程： Sp_helpdb:查看所有数据库定义信息。 Sp_helpdb 数据库名,删除和修改SQL SERVER数据库 1、删除数据库 DROP DATABASE database_name ,n 2、修改数据库,ALTER DATABASE 提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力。 ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,（1）扩充数据库空间 使用企业管理器增加数据库空间 注：重新指定的数据库空间必须大于现有空间。 使用T-SQL命令增加数据库空间 ALTER DATABASE 数据库名 MODIFY FILE （NAME=逻辑文件名，SIZE=文件大小，MAXSIZE=增长限制）,实例：将数据库Archive的数据文件Arch1的初始空间、最大空间分别由原来的100MB和200MB修改为120MB和220MB。 ALTER DATABASE Archive MODIFY FILE (NAME=Arch1, SIZE=120, MAXSIZE=220),（2）向数据库中添加文件 下例创建数据库，并更改该数据库以添加一个 5 MB 大小的新数据文件。 USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat1.mdf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO,ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = c:Program FilesMicrosoft SQLServerMSSQLDatat1dat2.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ),（3）向数据库中添加由两个文件组成的文件组 下例在示例（）中所创建的 Test1 数据库中创建一个文件组，并将两个 5 MB 的文件添加到该文件组。然后下例将 Test1FG1 设置为默认文件组。 USE master GO ALTER DATABASE Test1 ADD FILEGROUP Test1FG1 GO,ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT ALTER DATABASE Test1 ADD FILE ( NAME = test1dat3, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat3.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), ( NAME = test1dat4, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat4.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP Test1FG1,（4）向数据库中添加两个日志文件 下例向数据