创建与管理数据库.ppt

SQLServer2005T-SQL数据库设计主讲：毛嘉莉1Chapter第1章内容回顾n数据库的基本概念n数据、表、数据库、数据库管理系统、DBAnSQLServer2005的主要版本n企业版n软硬件要求n安装过程n预安装n正式安装nSQLServer的管理工具nSQLServer数据存储结构2Chapter第第2 2章章 管理数据库管理数据库3Chapter本章目标n掌握创建和修改数据库的方法 n掌握创建和修改表的方法n掌握插入、修改、删除和查询数据的方法n备份、还原数据库4Chapter本章结构管理数据库管理数据库分离和附加数据库分离和附加数据库 收缩数据库收缩数据库 扩展数据库扩展数据库 创建数据库创建数据库修改数据库修改数据库删除数据库删除数据库 操作数据操作数据更改数据更改数据查询数据查询数据新建表新建表表的维护表的维护新建用户数据库新建用户数据库5Chapter新建数据库n新建用户数据库n所有者n一个数据文件n一个日志文件n初始大小n自动增长n默认路径主文件主文件事务日志文件事务日志文件初始大小初始大小自动增长自动增长数据库名称数据库名称所有者所有者6Chapter修改数据库n扩展数据库n收缩数据库n分离附加数据库n删除数据库 SQL Server7Chapter扩展数据库n当数据库中的数据文件或日志文件的空间被占满n自动扩展 n设置文件自动增长 n指定允许文件增长到的最大大小n手动扩展n数据库的大小n添加数据库文件添加数据文件添加数据文件自动增自动增长长最大文件大小最大文件大小启动自动增长启动自动增长辅助文件辅助文件8Chapter收缩数据库n收缩数据库可以把不使用的空间释放出来 n自动数据库收缩 n将自动收缩选项设置为 TRUE n手动数据库收缩 n收缩数据库n收缩某个数据库文件当前分配的空当前分配的空间间选择收缩的文件选择收缩的文件选择文件类选择文件类型型收缩的选项收缩的选项可用空间可用空间手动收缩数据库手动收缩数据库9Chapter分离和附加数据库n用于移动数据库 n分离数据库n将数据库从 SQL Server 实例中移除n除了系统数据库之外n保持数据文件和事务日志文件的完整性和一致性 n附加数据库n分离后的数据库的数据和事务日志文件n同一个 SQL Server 实例n不同的 SQL Server 实例要分离的数据库添加文件要附加的数据库分离和附加数据库分离和附加数据库10Chapter删除文件和数据库n删除文件n文件必须完全为空，才能够删除 n删除数据库n文件及其数据都从服务器上的磁盘中删除 n一旦删除数据库，即被永久删除 n不能删除系统数据库 要删除的数据库要删除的数据库删除删除11Chapter阶段总结新建数据库更改数据库扩展数据库收缩数据库分离和附加数据库删除数据库12Chapter阶段练习J背景JBenet公司已建立一个TestDB数据库用于应用开发，TestDB包含一个数据文件和一个日志文件。现在要：n设置TestDB数据文件初始大小为5M，自动增长，最大大小为50Mn设置TestDB日志文件初始大小为5M，自动增长，限制文件增长为20Mn设置TestDB为自动收缩J目标J扩展数据库J收缩数据库13Chapter新建表n表和列的名称必须遵守标识符的规定n列名在表中必须是唯一的 n必须为每列指定数据类型列名列名选择数据类选择数据类型型是否允许为是否允许为空空14Chapter常用数据类型n精确数字n如：int-231到231-1n日期和时间n如：datetime1753年1月1日到9999年12月31日n字符串n如：Varchar最大存储大小是231-1个字节nUnicode字符串n如：nvarchar最大存储大小为231-1字节15Chapter向表添加数据插入新行插入新行查询设计器查询设计器16ChapterSQL概述nSQL：（Structured Query Language结构化查询化语言），不仅仅具有查询功能。它是一个通用的、功能极强的关系数据库操作语言。1974年Boyce和Chamberlin提出，首先在IBM 公司的关系数据库系统System R上实现1986年10月ANSI（美国国家标准局）的数据库委员会批准了SQL作为关系数据库语言的美国标准1987年ISO（国际标准化组织）也通过了这一标准。目前的大中型数据库基本都支持于1992年发布的SQL-92标准17Chapter标准化n有关组织nANSI(AmericanNaturalStandardInstitute)nISO(InternationalOrganizationforStandardization)n有关标准nSQL-86n“数据库语言SQL”nSQL-89n“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持nSQL-92n“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。nSQL-3n正在讨论中的新的标准，将增加对面向对象模型的支持18ChapterSQL对关系数据库模式的支持用户1用户2用户4视图V1视图V1用户3基本表B1基本表B2基本表B4基本表B3存储文件S1存储文件S2存储文件S4存储文件S3SQL用户VIEWBase TableStored fileSQL数据库体系结构19ChapterSQL的特点n面向集合操作n一次一集合n高度非过程化n用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径n集数据定义、数据查询和数据控制功能于一体n统一语法结构的两种使用方式，简单易学20ChapterSQL语言的动词SQL功能功能操作符操作符数据查询数据查询SELECT数据定义数据定义CREATE，ALTER，DROP数据操纵数据操纵INSERT，UPDATE，DELETE数据控制数据控制GRANT，REVOKE21ChapterSQL语言书写注意事项1.大小写不敏感2.sql语句中出现的所有单双引号，逗号，分号必须为半角字符。3.字符常量要用引号引起4.数字常量不用加引号5.不同具体RDBMS中，命令可能有差异，以该RDBMS命令手册为准 22ChapterTransact-SQL语句n可以实现数据的查询和数据的更改等操作 nSELECT nINSERT nUPDATE nDELETE23Chapter使用查询编辑器查询窗口查询窗口执行按钮执行按钮查询结果查询结果打开查询编辑器打开查询编辑器打开查询编辑器打开查询编辑器24Chapter查询语句n命令格式SELECT select_listFROM table_list WHERE search_conditions ORDER BY order_list ASC|DESC 25Chapter查询示例3-1n例1：查询所有员工信息 n SELECT*FROM Employees n*表示对所有列的引用n例2：如果只是想要得到某几列的值 nSELECT Name,Title FROM Employees n明确地列出指定的列内容26Chapter查询示例3-2n例3：查询所有工资大于4000的员工 nSELECT*FROM Employees WHERE Salary 4000nWhere 子句-按指定的条件返回数据行n操作符：=、BETWEEN AND、NOT、LIKE等n例4：查询工资在3000到4000的员工 nSELECT*FROM Employees WHERE Salary Between 3000 and 4000nBETWEEN AND：指定值的范围n例5：查找雇员表中姓张的员工 nSELECT*FROM Employees WHERE Name LIKE 张%;nLIKE：字符串进行模式匹配27Chapter查询示例3-3n例6：返回按工资降序排序的结果 nSELECT*FROM Employees ORDER BY Salary DESC nORDER BY：对行进行排序n关键字DESC/ASC：指降序/升序排列28Chapter插入数据n例7：在雇员信息表中加入一个新员工 nINSERT INTO Employees(lastname,firstname,title,birthdate,extension)VALUES(王,燕,销售员,1986-11-30,4500)n向表中插入一行值n例8：把Employees表内容导入到EmployeesNew新表中 nSELECT*INTO EmployeesNew FROM Employeesn从另一个表插入行 插入数据插入数据插入数据插入数据29Chapter更新数据n例9：将所有销售员的工资提高 10%nUPDATE Employees SET Salary=Salary*1.1 WHERE Title=销售员；要更新的列和新值的列表要更新的列和新值的列表搜索条件搜索条件更新数据更新数据更新数据更新数据30Chapter删除数据n例10：当销售员李芳离职后，将其从表中删除 nDELETE FROM Employees WHERE name=李芳n删除符合条件的行n例11：从 EmployeesNew 表中删除所有信息 nDELETE FROM EmployeesNewn删除所有行 删除数据删除数据删除数据删除数据31Chapter表的维护n修改表n使用表设计器n增加列n删除列n删除表添加新列添加新列删除列删除列确认要删除的表确认要删除的表32Chapter阶段总结新建表查询数据筛选列筛选行排序更改数据库插入数据更新数据删除数据33Chapter阶段练习J 背景J Benet公司在应用开发数据库TestDB上建立了一个销售部门人员信息表，现在要：n由于销售经理调职，删除其记录n将销售员提升为销售经理，更改其职位信息n查询年龄在30-40岁的员工信息n更改销售人员信息J 目标J删除记录J更改记录J查询记录34Chapter本章总结管理数据库管理数据库分离和附加数据库分离和附加数据库 收缩数据库收缩数据库 扩展数据库扩展数据库 创建数据库创建数据库修改数据库修改数据库删除数据库删除数据库 操作数据操作数据更改数据更改数据查询数据查询数据新建表新建表表的维护表的维护新建用户数据库新建用户数据库一个新建的数据库一个新建的数据库至少包含一个至少包含一个.mdf.mdf 后缀的数据文件和后缀的数据文件和一个一个.ldf.ldf后缀的日后缀的日志文件。志文件。扩展数据库可以扩展数据库可以设置自动扩展，设置自动扩展，也可以手动扩展也可以手动扩展或添加文件扩展或添加文件扩展 收缩数据库可以自收缩数据库可以自动收缩，也可以手动收缩，也可以手动收缩，手动收缩动收缩，手动收缩包括收缩数据库和包括收缩数据库和收缩文件收缩文件2 2种方法种方法 如果要移动数据库，如果要移动数据库，可以使用分离和附可以使用分离和附加数据库。加数据库。可以使用可以使用SELECT语句及其语句及其FROM、WHERE、ORDER BY子句查询数据子句查询数据可以使用可以使用UPDATE、INSERT、DELETE语句更改数据语句更改数据 35Chapter实验n任务1 创建和修改数据库 n任务2 操作数据 36Chapter任务1 创建和修改数据库 n背景nBenet公司已经安装好一台SQLServer服务器，现在需要建立1个数据库，具体要求如下：n新建数据库名为Benetn设置数据文件初始文件大小为10M，不自动增长n设置日志文件初始文件大小为10M，不自动增长n添加数据文件Data2，初始文件大小为25M，不自动增长n验证新建的数据库。n设置数据库为自动收缩n完成标准n根据要求新建一个用户数据库n根据要求配置用户数据库参数37Chapter任务2 操作数据 n背景n已建立好Benet数据库，现在需要n创建一个人员信息表叫Employeen使用表设计器添加员工信息记录n查询所有人员的信息，n查询所有在1993年1月到1994年6月入职的人员记录。n查询销售部男性员工的姓名n由于员工“金鹏”离职，删除其记录信息。n销售部新招聘一名男销售员叫林青，需插入其一条记录n新建一个表只包括“姓名”和“部门”列n销售员孙林调到市场部做市场专员，需要更改其记录信息n由于公司需要，给表中添加一列为分机号n完成标准n根据要求新建一个表n根据要求对表数据进行查询、更改38Chapter备份和还原39Chapter备份和还原数据丢失的原因数据丢失的原因程序错误程序错误 人为错误人为错误 计算机失败计算机失败 磁盘失败磁盘失败 灾难（如火灾、地震）和偷窃灾难（如火灾、地震）和偷窃 什么是备份什么是备份数据的副本数据的副本用于在系统发生故障后还原和恢复数据用于在系统发生故障后还原和恢复数据文件复制文件复制备份备份40Chapter备份类型备份类型备份类型说明完整备份完整备份备份完成时刻的数据库备份完成时刻的数据库 提供了任何其他备份的基准提供了任何其他备份的基准差异备份差异备份事先已执行过一次完整备份事先已执行过一次完整备份 对上一次完整备份之后所有更改的数据对上一次完整备份之后所有更改的数据作备份作备份事务日志备事务日志备份份事先已执行过一次完整备份事先已执行过一次完整备份与与WindowsWindows增量备份类似增量备份类似从上一次事务日志备份到当前事务日志从上一次事务日志备份到当前事务日志的尾部的尾部 截断已确认的事务日志截断已确认的事务日志41Chapter42Chapter43Chapter点击点击“确确定定”44Chapter45Chapter恢复模式恢复模式2-12-1恢复模式恢复模式说明说明简单简单事务日志不能用于还原数据事务日志不能用于还原数据用于小型数据库和不经常更改的数据库用于小型数据库和不经常更改的数据库完整完整所有事务都被记录到日志中所有事务都被记录到日志中保留所有日志，直到事务日志备份保留所有日志，直到事务日志备份用于生产数据库用于生产数据库大容量日志大容量日志完整恢复模式的补充完整恢复模式的补充 只对大容量操作进行最小记录只对大容量操作进行最小记录 46Chapter回顾数据库的基础知识n数据库文件的组成：n主数据文件：*.mdfn次要数据文件：*.ndfn日志文件：*.ldfn数据库的其他属性：n文件存放位置，分配的初始空间，属于哪个文件组n文件增长：可以按百分比或实际大小指定增长速度n文件容量设置：可以指定文件增长的最大值或不受限47Chapter数据库的建立与撤消-SQL语句n建立一个新数据库create databasecreate database 数据库名n撤消一个数据库drop databasedrop database 数据库名48Chapter举例:创建sales数据库nUse masternGonCreate database salesnOnn(name=sales_data,n filename=d:saledat.mdf,n size=10,n maxsize=50,n filegrowth=5)nLog onn(name=sales_log,nfilename=d:saledat.ldf,n size=5MB,n maxsize=25MB,n filegrowth=5MB)nGo49Chapter创建数据库 CREATE DATABASE 数据库名 ON PRIMARY(，n )LOG ON (，n)T-SQL创建数据库的语法：主文件组，可选参数，默认50Chapter创建数据库示例 1CREATE DATABASE stuDB ON PRIMARY -默认就属于PRIMARY主文件组，可省略(NAME=stuDB_data,-主数据文件的逻辑名 FILENAME=D:projectstuDB_data.mdf,-主数据文件的物理名 SIZE=5mb,-主数据文件初始大小 MAXSIZE=100mb,-主数据文件增长的最大值 FILEGROWTH=15%-主数据文件的增长率)LOG ON(NAME=stuDB_log,FILENAME=D:projectstuDB_log.ldf,SIZE=2mb,FILEGROWTH=1MB)GO 数据文件的具体描述日志文件的具体描述演示创建数据库1：一个数据文件和一个日志文件 51Chapter创建数据库示例 2CREATE DATABASE employees ON (/*-主数据文件的具体描述-*/NAME=employee1,FILENAME=D:projectemployee1_Data.mdf,SIZE=10,FILEGROWTH=10%),(/*-次要数据文件的具体描述-*/NAME=employee2,FILENAME=D:projectemployee2_Data.ndf,SIZE=20,MAXSIZE=100,FILEGROWTH=1 )LOG ON (/*-日志文件1的具体描述-*/NAME=employeelog1,FILENAME=D:projectemployeelog1_Log.ldf,SIZE=10,FILEGROWTH=1 ),(/*-日志文件2的具体描述-*/NAME=employeelog2,FILENAME=D:projectemployeelog2_Log.ldf,SIZE=10,MAXSIZE=50,FILEGROWTH=1 )GO 演示创建数据库2：多个数据文件和多个日志文件 52Chapter确认数据库的当前状态要确认数据库的当前状态，通过“数据库属性”窗口的“常规”选项卡查看数据库属性以外，还可以选择 sys.databases 目录视图中的 state_desc 列。在查询设计器窗口中输入如下代码并执行，如图所示。Select name,state,state_desc From sys.databases53Chapter查看数据库状态信息 可以在查询设计器窗口中输入如下代码并执行，即可查看到相关数据文件的状态信息。Select name,physical_name,type,type_desc,state,state_descFrom sys.master_file 54ChapterIF EXISTS(select*from sys.databases WHERE name=stuDB)DROP DATABASE stuDBCREATE DATABASE stuDBON(.)LOG ON()GO删除数据库n删除数据库的语法：DROP DATABASE 数据库名 EXISTS（）语句：检测是否存在stuDB数据库如果存在stuDB数据库，则删除 55Chapter数据库更名 两种方法：n一种方法是SQL ServerManagement Studio中选中此数据库，右击鼠标，在弹出的快捷菜单中选择“重命名”。n另一种方法是使用系统存储过程sp_renamedb更改数据库的名称。系统存储过程sp_renamedb语法如下：sp_renamedb dbname=old_name,newname=new_name sp_renamedb sales,saler例使用ALTER DATABASE改名程序代码如下：ALTER DATABASE salerMODIFY NAME=salesdb56Chapter限制用户对数据库的访问在SQL Server 2005 的运行过程中，有时需要限制用户的访问，例如，管理员要维护数据库，系统需要升级等。这时可以设置限定只能由特定用户访问数据库。57ChapternMultiple：数据库处于正常生产状态，允许数据库处于正常生产状态，允许多个用户同时访问数据库。多个用户同时访问数据库。nSingle：指定一次只能一个用户访问。：指定一次只能一个用户访问。nRestricted：限制除：限制除db_ower（数据库所有（数据库所有者）、者）、dbcreator（数据库创建者）和（数据库创建者）和sysadmin（系统管理员）以外的角色成员（系统管理员）以外的角色成员访问数据库。访问数据库。58Chapter修改数据库的排序规则（1）了解排序规则的含义。以Chinese_PRC_CS_AI_WS为例，该规则可以分成两部分来理解。前半部分指排序规则所支持的字符集，如Chinese_PRC_表示对中国大陆地区简体字UNICODE的排序规则，而后半部分常见的组合的含义如下。n_BIN：二进制排序。n_CI(CS)：是否区分大小写，CI不区分，CS区分。n_AI(AS)：是否区分重音，AI不区分，AS区分。n_KI(KS)：是否区分假名类型，KI不区分，KS区分。n_WI(WS)：是否区分宽度，WI不区分，WS区分。59Chapter（2）排序规则的层次。SQL Server 2005 的排序规则分为3个层次：服务器排序规则、数据库排序规则和表的排序规则。n当排序规则在层次之间发生冲突时，以低层次、细粒度为准。假如服务器的排序规则和数据库的排序规则不一致，在数据库中自然以数据库的排序规则为准。60Chapter总结nSQL Server 2000是Microsoft公司提供的关系型数据库管理系统，SQL Server也是当今流行的数据库n数据库是表和数据库访问对象的集合，其中表分类存储了不同的实体信息，每一行数据对应一个实体的描述信息n数据冗余是指数据库中存在一些重复的数据，数据完整性是指数据库中的数据能够正确反应实际情况，数据库中允许有一些数据冗余，但是要保持数据的完整性n企业管理器是SQL Server 2000最常用的操作环境之一，能够执行对数据库的日常管理操作nSQL Server数据库的物理文件包括数据文件和日志文件两部分，并且在创建数据库的时候就需要指定n数据库的操作包括创建、收缩、移动、配置选项和删除61Chapter课程上机实验在查询分析器中创建数据库test，基本信息要求如下表所示参数参数值数据库名称test1数据库逻辑文件名称 test1_data操作系统数据库文件C:mssqldatatest1_data.mdf数据文件初始化大小10M数据文件最大值及文件增长50M/20%练习练习1、数据库的创建和管理、数据库的创建和管理参数参数值日志逻辑文件名称 test1_ log操作系统日志文件C:mssqldatatest1_log.ldf数据文件初始化大小5M数据文件最大值及文件增长5M/25M表（一）表（一）表（二）表（二）62Chapter课程上机实验 将刚建立的test1数据库进行如下的修改，见表练习练习2、修改数据、修改数据库库表（三）表（三）参数参数值数据库名称test1增加的文件组名称 test1_fg1增加的文件1的逻辑名Test1_dat3增加的文件1在磁盘中的路径C:mssqldatatest1_dat3.ndf增加的文件1初始化大小5M文件1最大值及文件增长50M/5M增加的文件2的逻辑名Test1_dat4增加的文件2在磁盘中的路径C:mssqldatatest1_dat4.ndf增加的文件2初始化大小5M文件2最大值及文件增长50M/5M增加的日志文件名Test1_log2增加的日志文件在磁盘中路径C:mssqldatatest1_log2.ldf增加的日志文件初始化大小5M增加的日志文件最大值及文件增长100M/5M63Chapter