数据库的创建与使用

第5章 数据库的创建与使用 5.1 SQL Server数据库的结构 1.逻辑数据库逻辑数据库(数据库对象数据库对象)SQL Server数据库是存储数据的容器，是一个存放数据的表和支持这数据库是存储数据的容器，是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。数据库对象说明表由行和列构成的集合，用来存储数据数据类型定义列或变量的数据类型，SQL Server提供了系统数据类型，并允许用户自定义数据类型视图由表或其他视图导出的虚拟表索引为快速检索提供支持且可以保证数据唯一性的辅助数据结构约束用于为表中的列定义完整性的规则用户该数据库中所有能使用的用户信息存储过程存放于服务器的预先编译好的一组T-SQL语句触发器特殊的存储过程，当数据改变时，该存储过程被自动执行2.物理数据库（数据库文件）主数据文件（Primary Database File）：扩展名为.mdf，是数据库的起点，并指向其他文件，每个数据库只有一个主文件。次文件（Secondary Database File):.ndf,是主文件的辅助文件，可以在不同的磁盘上创建数据库文件，以提高处理效率,当主文件的大小超过操作系统的限制时,必须使用次文件。事务日志文件(Log)：.ldf，用于存储数据库的更新情况等事务日志信息。如insert,delete,update操作都会记录在案。通常,这三个文件都在SQL的Data文件夹中SQL Server事务日志 SQL Server事务日志存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。SQL Server事务日志采用提前写入的方式。事务日志的工作方式事务日志的工作方式 应用程序发送更改请求应用程序发送更改请求磁盘磁盘将更改记录在磁盘上的事务将更改记录在磁盘上的事务日志中日志中定位数据页，将其读入缓存中定位数据页，将其读入缓存中并进行更改并进行更改缓存缓存磁盘磁盘检查点进程将已提交检查点进程将已提交的事务写入数据库的事务写入数据库3.数据库文件组 为了便于分配和管理,SQL Server允许将多个数据文件归为一组,并赋予一个名称,即文件组.文件组有两种:主文件组:包含主数据文件和任何其他不属于另一个文件组的文件,系统表都包含在主文件组中.用户定义文件组:在语句中使用关键字filegroup指定文件组存储结构示意图 数据库对象:表，索引，视图，触发器，存储过程，约束，用户，图表多个次文件多个次文件.ndf日志文件日志文件.ldf 主文件主文件.mdf主文件组主文件组 次文件组次文件组DDEC一个文件只能属于一一个文件只能属于一个文件组个文件组;一个文件一个文件组也只能被一个数据组也只能被一个数据库使用库使用;日志文件不日志文件不属于任何文件组属于任何文件组.12 文件组的好处:可以改善数据库的查询性能可以改善数据库的查询性能.如：可以将数据文件、和分别创建在三个盘上，这将数据文件、和分别创建在三个盘上，这三个文件组成文件组三个文件组成文件组fgroup1，在创建表的时候，在创建表的时候，就可以指定一个表创建在文件组就可以指定一个表创建在文件组fgroup1上。这上。这样该表的数据就可以分布在三个盘上，在对该表样该表的数据就可以分布在三个盘上，在对该表执行查询时，可以并行操作，大大提高了查询效执行查询时，可以并行操作，大大提高了查询效率率。5.1.2 数据库存储结构 -逻辑对象与物理存储的关系 数据库中的物理存储单位是页面和盘区，可用于估算数据所占用的空间。页面：是数据库中使用的最小数据单元，占8K字节，用于存储数据，如数据表的一行或若干行，一行不允许跨页面。盘区：是SQL Server创建数据逻辑对象时使用的分配单位，有8个页面64K，若新建一个数据库中创建了一个表、两个索引，在表中有一条记录，则总共占用3X64K=192K的空间。数据的存储方式扩展盘区扩展盘区（8 个连续页，个连续页，每页每页 8 KB）数据页数据页（8 KB）表，索引表，索引数据库数据库数据（文件）数据（文件）.mdf 或或.ndf日志（文件）日志（文件）.ldf行的最大容量行的最大容量=8060 字节字节即，数据库是这样的数据结构：一个数据库是由若干文件组成，一个文件由若干盘区组成，一个盘区有8个页面。SQL Server的数据库结构小结的数据库结构小结逻辑存储结构（数据库对象）指的是数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。数据库对象如表，索引，视图，触发器，存储过程，约束，用户，图表等.物理存储结构（数据库文件）则是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。5.1.3 Sql server系统数据库与系统表系统数据库系统数据库用户数据库用户数据库mastermodeltempdbmsdbpubsNorthwindUser1distribution Sql server数据库的类型数据库的类型1.SQL Server 系统数据库简介系统数据库简介 SQL Server主要有4个系统数据库 1.Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。2.Model数据库是所有用户数据库和Tempdb数据库的模板数据库，它含有Master数据库所有系统表的子集，这些系统表是每个用户定义数据库需要的。3.Msdb数据库是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。4.Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其它临时操作提供存储空间。每次启动SQL Server时都会清空。Pubs和Northwind数据库是示例数据库，它们可以作为SQL Server的学习工具。Sql server数据表的分类系统表系统表用户表用户表 在SQL Server 中，每个数据库中最多可以创建200万个表，用户创建的数据库表中，最多可以定义1024列（字段）。系统表DBMS的整个环境也是一个需要管理的环境，它自己也有很多的整个环境也是一个需要管理的环境，它自己也有很多数据和信息，也储存在一些表中，这些表就是系统表。数据和信息，也储存在一些表中，这些表就是系统表。存储与存储与整个系统有关整个系统有关的系统表的系统表-构成构成系统目录系统目录 只存储在只存储在 master 数据库中，如：数据库中，如：Syslogins（登录信息）、（登录信息）、sysmessages（各种消息内容）（各种消息内容）存储存储当前数据库当前数据库的系统表的系统表-构成构成数据库目录数据库目录 每个数据库（包括每个数据库（包括 master 数据库）都有，如：数据库）都有，如：Sysusers（用户信息）、（用户信息）、sysobjects（对象信息）（对象信息）SQL Server 经常访问这些目录，检索运行所需的必要信息。几个重要的系统表几个重要的系统表 系统目录中的系统表（在 master 数据库中）：syslogins：包含所有的用户帐户信息，包括名字、口令和配置信息。sysmessages：包含所有SQL Server 可用的系统错误消息。sysservers：包含本地服务器和远程服务器的信息。sysdatabases：包含服务器上所拥有的所有数据库有关信息。对SQL Server 系统上的每个系统数据库和用户自定义的数据库含有一行记录。sysusers：它对整个数据库中的每个Windows NT用户、Windows NT用户组、SQL Server 用户或者SQL Server 角色含有一行记录。数据库目录中的系统表（在各个数据库中都有）sysusers：它对数据库中每一个有权限访问数据库的用户都对应一行记录。sysfilegroups：数据库中每个文件组的信息。sysconstraints：数据库中所有约束的信息。sysprotects：数据库中用户对于对象所有的权限的信息。sysobjects：它对数据库中每个数据库对象含有一行记录。syscolumns：它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。1.sysindexes：它对每个索引和没有聚簇索引的每个表含有一行记录，它还对包括文本/图像数据的每个表含有一行记录。Compact Edition SQL Server Compact Edition也不是一个新东西，它的前身是主要是为嵌入式系统和智能设备提供本地数据存储的引擎。与SQL Server其它版本相比，它最大的不同是它不是作为一个服务存在的，而是一种轻量级的，依附在应用程序进程内的数据库引擎。SQL Server Express Edition一定需要通过MS提供的安装包安装；Compact Edition可以通过安装包安装，也可以将支持文件集成到应用程序的安装包中，因为非常小，才10来个文件，2M都不到。5.2 界面方法创建和管理数据库5.2.1 创建数据库创建数据库 创建数据库需要一定许可，在默认情况下，只有系统管理员和拥有数据库创建权限的人才可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者dbo。创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等。几种方法：通过管理平台创建数据库(1)使用Transact-SQL语言创建数据库。一、通过管理平台创建数据库步骤：(1)(1)启动启动SQL ServerSQL Server管理平台管理平台 (2)(2)展开服务器，右击【数据库】快捷菜单选择【新建数据库】，展开服务器，右击【数据库】快捷菜单选择【新建数据库】，弹出【数据库属性】窗口。弹出【数据库属性】窗口。(3)(3)在【数据库属性】窗口中选【常规】选项卡，在【名称】栏在【数据库属性】窗口中选【常规】选项卡，在【名称】栏中输入数据库的名称。中输入数据库的名称。(4)(4)选择【数据文件】选项卡，指定数据库文件名称及存储位置，选择【数据文件】选项卡，指定数据库文件名称及存储位置，设置其他属性。设置其他属性。(5)(5)选择【事务日志】选项卡，指定事务日志文件名称及保存位选择【事务日志】选项卡，指定事务日志文件名称及保存位置，并可以设置日志文件的初始大小、增长方式。置，并可以设置日志文件的初始大小、增长方式。(6)(6)设置完成之后，单击【确定】按钮完成设置完成之后，单击【确定】按钮完成。通过管理平台创建数据库通过管理平台创建数据库关于数据库的默认存储位置：主数据文件默认存储位置和文件如下：主数据文件默认存储位置和文件如下：SQL Server根目录根目录data数据库名数据库名.MDF日志文件的默认存储位置和文件如下：日志文件的默认存储位置和文件如下：SQL Server根目录根目录data数据库名数据库名_ 选择“数据文件”和“事务日志”两个标签栏，显示系统的默认设置，用户可以更改这两个文件的存放位置和文件名。有关参数的默认值：对于新创建的数据库，系统对默认值为：数据文件初始对于新创建的数据库，系统对默认值为：数据文件初始大小大小3MB，日志文件的初始大小，日志文件的初始大小1MB,最大大小不限制，而最大大小不限制，而实际上仅受硬盘空间的限制，允许数据库自动增长，增长实际上仅受硬盘空间的限制，允许数据库自动增长，增长方式为按方式为按10%比例增长；比例增长；注意：SQL Server中的数据和事务日志文件不能存放在压缩文件系统或象共享网络目录等远程的网络驱动器上。SQL Server的文件拥有两个名称：逻辑名称、物理文件名 当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，路径是文件的完整磁盘目录。通过管理平台创建数据库举例通过管理平台创建数据库举例【例】【例】创建数据库CJGL，初始大小为5MB，最大大小50MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB（默认为不限制），按1MB增长（默认是按10%比例增长）；所有者是Administrator。以Administrator身份登录计算机，并启动SQL Server服务。5.2.2 修改数据库方法：“管理平台”中在该数据库名上点击鼠标右键，选择“属性”。对已存在的数据库可以进行的修改包括：l 增加或删除数据文件l 增加或删除日志文件l 增加或删除文件组l 改变数据文件的大小和增长方式(只能小大）l 改变日志文件的大小和增长方式注意：修改时是不能改变逻辑名和物理名。利用管理平台修改数据库(1)改变数据文件的大小和增长方式(只能小大）【例【例1】将CJGL数据库的主数据文件文件的最大尺寸由50MB修改为不限制。(2)增加数据文件(增加的只能是辅助数据文件.ndf)【例【例2】在CJGL数据库中增加数据文件CJGLBAK，其属性均取系统默认值。操作方法：在“数据文件”选项卡点击空白行，在“文件名”一栏中输入数据文件名，并可设置文件的初始大小和增长属性利用管理平台修改数据库(3)删除数据文件【例【例3】将CJGL数据库中刚增加的辅助文件CJGLBAK删除。(4)增加或删除文件组【例【例4】设要在数据库CJGL中增加一个名为FGroup的文件组。操作方法为：选择“文件组”标签栏，在PRIMARY行的下面一行输入“FGroup”文件组，单击“确定”按钮。关于默认的文件组关于默认的文件组 数据库建立时的默认文件组为主文件组数据库建立时的默认文件组为主文件组PRIMARY，未未指定文件组的对象都将放到默认文件组中。指定文件组的对象都将放到默认文件组中。系统对象和系统表永远保留在 PRIMARY 文件组内。可用 ALTER DATABASE 语句更改默认文件组，更改默认文件组可防止用户对象与系统对象争夺数据空间 调整主文件组中文件的大小调整主文件组中文件的大小 若主文件组没有空间，就不能往系统表中写入任何信息若主文件组没有空间，就不能往系统表中写入任何信息 在下列情况下，主文件组会填满：关闭了自动增长选项 在主文件组中的文件的所有磁盘空间耗尽。解决办法：重新打开自动增长 将其它文件从磁盘上移走以释放更多的空间。删除数据库【例】利用管理平台删除CJGL数据库。在弹出的快捷菜单上选择“删除”5.2.4 分离和附加数据库 SQL Server 允许分离数据库的数据和事务日志文件，然后允许分离数据库的数据和事务日志文件，然后将其重新附加到另一台服务器，或者同一台服务器上将其重新附加到另一台服务器，或者同一台服务器上 分离数据库分离数据库 分离数据库是将数据库从 SQL Server环境中移除，但是保持组成该数据库的数据和事务日志文件中的数据库完好无损 分离数据库要求没有用户正在使用数据库，数据库中没有未完成的事务，并且在内存中没有这个数据库的未提交的数据（脏页）一旦数据库被分离，其条目就从 master 数据库的 sysdatabases 表中被移除。从 SQL Server 的角度看，数据库似乎被删除了但是其物理文件仍然保存在磁盘上分离和附加数据库（续）附加数据库附加数据库 当附加数据库时，必须指定主数据文件（.mdf）的名称和物理位置 无论原来分离时数据库的名称是什么，在“附加为”处可以重新指定不同于原来的数据库名。若附加的文件超过了16个，可使用 CREATE DATABASE 语句并指定 for attach 选项 操作方法操作方法附加数据库：附加数据库：右键单击“数据库”“附加”分离数据库：分离数据库：指定数据库右键单击“任务”分离“5.3 使用T-SQL（命令方式）创建和管理数据库5.3.1 T-SQL语言简介语言简介 历史 SQL:Struceured Query Languang 1974年，由Boyce和Chamber提出，称为SEQUEL语言 1975-1979年，由IBM的San Jose研究室研制，在System R上实现（吉姆格雷Jim Gray主持），称为SEQUEL2，即目前的SQL语言。1982年美国国家标准化组织ANSI确认SQL为数据库系统的工业标准，现在许多关系型数据库供应商都在自己的数据库中支持SQL语言有关标准化 有关组织 ANSI (American Natural Standard Institute)ISO (International Organization for Standardization)有关标准 ANSI SQL-86 ANSI SQL-89：增加了对完整性约束的支持 ISO SQL-92（SQL2）：增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。ISO SQL-99（SQL3）：增加对面向对象模型的支持 ISO SQL-2003 新的标准1T-SQL 简介 Transact SQL是微软对是微软对ANSI SQL-99标准的扩展，是标准的扩展，是SQL Server的核心，它扩展的主要功能是：加入了程序的核心，它扩展的主要功能是：加入了程序的控制结构，如的控制结构，如 IFELSE、WHILE语句等；加入了局部语句等；加入了局部变量和全局变量等。变量和全局变量等。利用利用Transact SQL可以编写出复杂的查询语句，可以建可以编写出复杂的查询语句，可以建立驻留在立驻留在SQL Server上的数据库对象，如触发器、存储上的数据库对象，如触发器、存储过程等过程等 注意：如希望开发的程序具有更好的可移植性，那么应尽注意：如希望开发的程序具有更好的可移植性，那么应尽量使用标准的量使用标准的SQL，否则就可考虑使用，否则就可考虑使用Transact SQL。SQL语言集数据查询、数据操纵、数据定义和数据控制功语言集数据查询、数据操纵、数据定义和数据控制功能于一体，是一个综合的、通用的、功能极强，同时又简能于一体，是一个综合的、通用的、功能极强，同时又简洁易学的语言。洁易学的语言。T-SQL语言由以下几部分组成。语言由以下几部分组成。SQL功能功能动词动词数据定义数据定义DDL（Data Definition Language）CREATE，DROP，ALTER数据操纵数据操纵DML(Data Manipulation Language）INSERT，UPDATE，DELETE；SELECT数据控制数据控制DCL（Data Control Language）GRANT，REVOKEDENY 其他的语言元素（如常量，变量，运算符，控制语句等）其他的语言元素（如常量，变量，运算符，控制语句等）Transact-SQL 语句的功能分类数据查询数据查询DQL（Data Query Language）SELECT2.标识符标识符标识符 用来定义服务器、数据库、数据库对象和变量等的名称用来定义服务器、数据库、数据库对象和变量等的名称标识符的命名规则标识符的命名规则:首字符必须是其中之一首字符必须是其中之一:字母字母,汉字汉字,_,#以后可以是以后可以是:字母字母,数字数字,$,#,_不能是保留字不能是保留字,不区分大小写不区分大小写,不允许有空格不允许有空格所有标识符长度为所有标识符长度为128。SQL Server标识符有两种标识符有两种常规标识符常规标识符:严格遵守命名规则严格遵守命名规则.定界标识符定界标识符:可以不遵守命名规则，使用双引号可以不遵守命名规则，使用双引号“”“”或中括或中括弧弧 括起来，如括起来，如“my table”,select SQL Server规定规定:没有使用定界符的标识符没有使用定界符的标识符,一律按常规对待一律按常规对待.3.逻辑数据库对象的完全限定名与部分限定名（1）完全限定名：是对象的全名完全限定名：是对象的全名包括四个部分：包括四个部分：服务器名、数据库名、所有者名和对象名服务器名、数据库名、所有者名和对象名其格式为：其格式为：server.database.owner.Object（2）部分限定名部分限定名使用全名往往很繁琐且没有必要，所以常省略其使用全名往往很繁琐且没有必要，所以常省略其中的某些部分，对象全名的四个部分中的前三个中的某些部分，对象全名的四个部分中的前三个部分均可以被省略，当省略中间的部分时，圆点部分均可以被省略，当省略中间的部分时，圆点符符“.”不可省略。不可省略。对象名的有效格式可以是：.objectserver.server.objectdatabase.objectowner.Object如:以sa身份登录服务器后，作以下查询：SELECT*FROM WINXP-SP2.学分制选课.DBO.学生信息SELECT *FROM ABC.TABLE1 说明：创建一个对象必须具有唯一合法的完全限定限定名（四部分合起来唯一），同一服务器数据库不能重名，同一所有者的对象名（表，视图，存储过程等）不能重名。在部分限定名中，未指出的部分使用以下默认值：在部分限定名中，未指出的部分使用以下默认值：服务器名称默认为本地服务器上的当前实例服务器名称默认为本地服务器上的当前实例 数据库名称默认为当前数据库数据库名称默认为当前数据库 所有者名称默认为指定数据库中与当前连接的登录账所有者名称默认为指定数据库中与当前连接的登录账户相关联的用户名，或者数据库所有者（户相关联的用户名，或者数据库所有者（dbo）。）。4.语法格式约定语法格式约定约定约定用于用于|（竖线）分隔的语法项，多选一。表示只能使用括号或大括号中的其中一项。（方括号）可选语法项。不要键入方括号。（大括号）必选语法项。不要键入大括号。,.n指示前面的项可以重复 n 次。各项之间以逗号分隔。.n指示前面的项可以重复 n 次。每一项由空格分隔。:=语法块的名称。用于对在语句中多个位置上使用的过长语法段或语法单元进行分组和标记。5.3.2 使用T-SQL（命令方式）创建数据库语法格式：语法格式：CREATE DATABASE database_name ON /*指定数据文件和文件组属性*/,.n ,.n /*必须先有主文件组PRIMARY*/LOG ON ,.n /*指定日志文件属性*/COLLATE collation_name FOR LOAD|FOR ATTACH :=PRIMARY(NAME=logical_file_name,FILENAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment ):=FILEGROUP filegroup_name 5.3.2 使用CREATE DATABASE创建数据库【例【例5.2】创建一个名为CJGL的数据库，其初始大小为5MB，最大大小50MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。假设SQL Server服务已启动，并以Administrator身份登录计算机。5.3.2 使用CREATE DATABASE创建数据库在T-SQL语句输入窗口中输入如下语句：CREATE DATABASE CJGLON(NAME=CJGL_Data,FILENAME=,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=10%)LOG ON(NAME=CJGL_Log,FILENAME=,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO主文件组名主文件组名PRIMARY可以省略可以省略在数据文件结在数据文件结束时无逗号束时无逗号在右括号在右括号前无逗号前无逗号不宜为根部，即不宜为根部，即5.3.1 使用CREATE DATABASE创建数据库【例】【例】创建TEST1的数据库。CREATE DATABASE TEST1 ON (NAME=TEST1_data,FILENAME=e:sqlt1.mdf )GO此处，日志文件没有特别指此处，日志文件没有特别指定，而是使用默认方式创建定，而是使用默认方式创建【例【例5.3】创建一个名为TEST2的数据库，它有三个数据文件，其中主数据文件为100MB，最大大小为200MB，按20MB增长；2个辅数据文件为20MB，最大大小不限，按10%增长；有2个日志文件，大小均为50MB，最大大小均为100MB，按10MB增长。CREATE DATABASE TEST2ON PRIMARY(NAME=TEST2_data1,FILENAME=e:sqltest2_data1.mdf,SIZE=100MB,MAXSIZE=200MB,FILEGROWTH=20MB),(NAME=TEST2_data2,FILENAME=e:sqltest2_data2.ndf,SIZE=20MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),此处加上 filegroup secfg 就可以自定义一个文件组secfg (NAME=TEST2_data3,FILENAME=e:sqltest2_data3.ndf,SIZE=20MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOG ON (NAME=TEST2_log1,FILENAME=e:sqltest2_log1.ldf,SIZE=50MB,MAXSIZE=100MB,FILEGROWTH=10MB ),(NAME=TEST2_log2,FILENAME=e:sqltest2_log2.ldf,SIZE=50MB,MAXSIZE=100MB,FILEGROWTH=10MB)GO创建多文件组数据库举例Create database salesOn primary(name=spri1_dat,Filename=e:,Size=10,maxsize=50,filegrowth=15%),(name=spri2_dat,Filename=e:,Size=10,maxsize=50,filegrowth=15%),Filegroup salesgroup1(name=sgrp1fi1_dat,Filename=e:,Size=10,maxsize=50,filegrowth=5),(name=sgrp1fi2_dat,Filename=e:,Size=10,maxsize=50,filegrowth=5)Log on(name=sale_log,Filename=e:,Size=5,maxsize=25,filegrowth=5)