项目二 数据库创建与管理(课件）-《网站数据库应用技术-SQL Server》同步教学（电子工业版）

项目二 数据库创建与管理 1 任务2.1 查看数据库服务器信息 任务2.2 创建数据库 任务2.3 管理数据库 1 2 3 目录页 CONTENTS PAGE 2 主要知识点 SQL SERVER2022数据库的基本组成 运用SSMS创建和管理数据库 使用T-SQL命令创建和管理数据库 数据库的分离和附加 学习目标 SQL SERVER2022数据库的组成 各个系统数据库的功能 会创建数据库 会查看和修改数据库属性 会管理数据库 会分离数据库 会附加数据库 任务2.1 查看数据库服务器信息 2.1.1 SQL Server 2022体系结构 2.1.2 SQL Server2022的数据库组成 2.1.3 SQL SERVER2022服务器身份验证模式 过渡页 TRANSITION PAGE 4 查看数据库服务器信息 2.1.1 SQL Server 2022体系结构 SQL Server以数据库引擎为基础，通过集成界面提供数据存储与分析、报表服务、数据挖掘、云存储、人工智能等全方位服务。 数据库引擎SSDE是SQL Server的核心服务，负责完成业务数据的存储、处理、查询和安全管理，创建数据库、创建表、执行各种数据查询、访问数据库等基础操作，均由数据库引擎完成，很多时候，使用数据库系统主要是使用数据库引擎服务。 任务2.1 5 查看数据库服务器信息 2.1.1 SQL Server 2022体系结构 集成服务（SSIS）：用于提取、转换和加载（ETL）操作的平台，使操作数据仓库与其同步，数据仓库里的数据来源于企业商业应用所使用的孤立数据源。 分析服务（SSAS）：针对个人、团队和公司商业智能的分析数据平台和工具集，提供了用于联机分析处理（Online Analytical Processing，OLAP）的引擎。 报表服务（SSRS）：数据输出的报表解决方案，提供企业级Web报表功能，可以创建从多个数据源提取数据的表。 分析服务（SSAS）：用于数据建模和分析，根据数据仓库表格设计、创建和管理多维数据集的功能，是商业智能战略的基础。 主数据服务（MDS）：针对主数据管理的SQL Server解决方案，通过配置MDS来管理任何领域，可包括层次结构、各种级别的安全性、事务、数据版本控制和业务规则，可用于管理数据的Excel外接程序。 SQL Server配置管理器：为SQL Server服务、服务器协议、客户端协议和客户端别名提供基本配置管理。 数据库引擎优化顾问：用于优化数据库引擎，协助创建索引、索引视图和分区。 SQL Server代理服务：是一项 Microsoft Windows服务，允许自动执行某些管理任务。可代理运行作业、监视SQL Server并警报。 任务2.1 6 查看数据库服务器信息 2.1.2 SQL Server 2022数据库的组成 安装SQL Server2022软件的机器称为数据库服务器，SQL Server2022的数据库包括三类：系统数据库、数据库快照和用户数据库。 系统数据库是安装后系统自动建立的数据库，存放系统的核心信息，SQL Server2022使用这些信息来管理和控制整个数据库服务器系统。 master：最重要的系统数据库，记录SQL Server系统的所有系统级信息包括登录账号、密码、用户和角色、权限设置、链接服务器和系统配置信息。 model：模板数据库，存储可以作为模板的数据库对象和数据，用户在创建数据库时，系统自动调用此数据库中的相关信息。 msdb：与代理服务有关的数据库，主要完成定时、预处理等操作，记录有关作业、警报、操作员、调度等信息。 tempdb：临时数据库，存储查询过程中所形成的中间数据或结果。 任务2.1 7 查看数据库服务器信息 2.1.2 SQL Server 2022数据库的组成 安装SQL Server2022软件的机器称为数据库服务器，SQL Server2022的数据库包括三类：系统数据库、数据库快照和用户数据库。 系统数据库是安装后系统自动建立的数据库，存放系统的核心信息，SQL Server2022使用这些信息来管理和控制整个数据库服务器系统。 系统数据库的组成与用户数据库基本相同，包括表、视图、同义词、可编程性、Service Broker、存储、安全性。 数据库快照SQL Server数据库的只读静态视图。自创建快照起，数据库快照在事务上与源数据库一致，始终与其源数据库位于同一服务器实例上。 数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。快照存储原始页，保留它们在创建快照时的数据记录。 用户数据库是用户通过SSMS窗口或T-SQL命令创建的数据库，用户可以创建多个数据库。 任务2.1 8 查看数据库服务器信息 2.1.3 SQL Server 2022服务器验证模式 系统提供Windows身份验证、SQL SERVER身份验证、Azure活动目录等8种身份验证模式，最常用的前两种。 （1）Windows身份验证：适合于域内连接，SQL Server使用Windows用户信息验证账户名和密码，即SQL SERVER信任Windows用户 （2）SQL SERVER身份验证：是一种混合验证模式，允许用户使用Windows身份验证，也允许远程用户通过SQL Server身份验证进行连接，这时需要输入登录名和密码。 任务2.1 9 查看数据库服务器信息 2.1.3 SQL Server 2022服务器验证模式 设置登录账号的方法是： 右击服务器名--【属性】--【安全性】选项卡，在服务器身份验证方式栏中选择一种验证模式。 任务2.1 10 创建数据库 2.2.1 文件与文件组 创建数据库有2种方法： 通过SSMS管理器窗口的交互方式 命令方式，命令方式用途更广，适合于程序员开发软件使用 数据库包括逻辑结构和物理结构两部分，对应的文件也有逻辑文件和物理文件之分。 一个数据库对应的物理文件主要有3种类型。 （1）主数据库文件：类型名是.mdf，是最重要的数据库文件，存储数据库启动信息和全部数据，一个数据库至少要有一个mdf文件。 （2）辅助数据库文件：也称次要数据库文件.ndf，用于存储除了主数据库文件之外的其它文件信息，保存主库中没有存贮的数据，一个数据库可以有一个或者多个.ndf文件，也可以没有.ndf文件。 （3）事务日志文件：记录对数据库的操作情况，类型名是.ldf。 一个数据库至少包含一个主数据库文件和一个事务日志文件。当一个数据库数据内容非常多时，数据文件也会有多个，为方便管理，可以将文件分成若干组，称为文件组filegroup，每一个数据文件必须属于且只能属于一个组，系统默认的文件组是PRIMARY，即主文件组，主数据库文件就放在这个组中，用户还可以建立新文件组，并将其它文件存入进来。 日志文件不适用于文件组，它独立存在。 任务2.2 11 创建数据库 2.2.2 用SSMS管理器窗口建立数据库 新建数据库时，需指定数据库名，对应的逻辑名，物理文件名及存储位置，初始大小，最大存储容量及增长速度等参数，同一数据库服务器的数据库名不允许相同，物理文件存放的文件夹要事先建立好。 任务2.2 例2-1：利用管理器窗口建立libsys数据库。主要参数有： （1）主数据文件的逻辑名为libsys，对应的物理文件存放在d:\data文件夹中，文件名为libsys_data.mdf，初始大小为10MB，最大容量是100MB，增长速度为10MB。 （2）日志文件的逻辑名为libsys_log，对应的物理文件名libsys_log.ldf，初始容量5MB，增长速度15%，最大容量50MB。 12 创建数据库 2.2.2 用SSMS管理器窗口建立数据库 说明： 数据文件和日志文件最好保存在同一个文件夹，便于管理。 文件大小的默认单位是MB，必须为整数值，如果用MB作为容量单位，则MB可以省略，还可以用GB、TB作为单位。 数据库不允许重名，如果数据库已经存在，必须先删除后才能建立。 数据库名和逻辑名都必须符合标识符的规定，以英文字母或者汉字开头，后面可以跟英文字母、数字、汉字、下划线，最长128个字符，不可用数字开头，标识符中不可出现其它标点符号。 任务2.2 练习：利用管理器窗口建立AddressBook（通信录）数据库。主要参数： （1）主数据文件逻辑名为ABMIS，对应的物理文件存放在d:\abdata文件夹中，文件名为ab_data.mdf，最大容量是150MB。 （2）日志文件的逻辑名为ABMIS_log，对应的物理文件名ab_log.ldf，初始容量15MB，增长速度12%，最大容量75MB。 （3）缺省参数全部取默认值。 13 创建数据库 2.2.3 用SQL命令建立数据库 建立数据库的SQL命令格式是： CREATE DATABASE 数据库名 [ON [PRIMARY] （主数据文件标识） …… ] [LOG ON （日志文件标识） …… ] 其中： <文件标识>包括5个参数，即 [NAME=逻辑名] [，FILENAME='磁盘文件名'] [，SIZE=初始容量] [，MAXSIZE={最大容量|UNLIMITED}] [，FILEGROWTH=增长速度] 任务2.2 说明： （1）命令格式中的方括号[ ]表示本项可以缺省，但有和没有的结果不同，缺省时，系统会取默认值，否则就是指定的值。 （2）PRIMARY表示主数据库文件，因为是默认值，可以省略。 （3）“……”表示可以有多个文件，各个文件的格式相同。 （4）常量UNLIMITED表示最大容量无限制。 （5）磁盘文件中可以带路径，表示存储位置，如果不带路径，则存储到默认文件夹中。 （6）增长速度可以用百分数n%表示，也可以是nMB。 14 创建数据库 2.2.3 用SQL命令建立数据库 例2-4 建立一个库company，主文件逻辑名为company_data，磁盘文件为company.mdf，初始大小为20M，最大不限，日志文件逻辑名为company_log，物理文件为company.ldf，初始大小为2M，最大10M，增加速度为1M。物理文件放在d:\mydb中，写出SQL命令。 先检查D盘下有无mydb文件夹，若无，则建立。 输入建库代码。 任务2.2 CREATE DATABASE company ON ( NAME=company_data, FILENAME='d:\mydb\company.mdf', SIZE=20, MAXSIZE=UNLIMITED ) LOG ON ( NAME=company_log, FILENAME='d:\mydb\company.ldf', SIZE=2MB, FILEGROWTH=1, MAXSIZE=10 ) 15 创建数据库 2.2.3 用SQL命令建立数据库 练习： 建立数据库libsys，主文件逻辑名为libsys，磁盘文件为libsysdata.mdf，初始大小为100M，增长速度10MB，最大不限，日志文件逻辑名为libsyslog，物理文件为libsyslog.ldf，初始大小为20M，最大1GB，增加速度为默认值，物理文件放在d:\data中，写出SQL命令。 任务2.2 16 管理数据库 2.3.1 修改数据库 修改数据库是指修改数据库名、修改物理文件对应的参数、向数据库添加文件及文件组、删除文件及文件组等参数。 修改数据库有2种方法：用SSMS管理器的菜单方式+用命令方式。 任务2.3 1、用SSMS管理器窗口方式修改数据库 在SSMS管理器窗口中，找到要修改的数据库名，在其【属性】对话框中修改。 17 管理数据库 2.3.1 修改数据库 任务2.3 2、用命令修改数据库 格式是： ALTER DATABASE <数据库名> { ADD FILE <文件标识> [ ,…n ] | ADD LOG FILE <文件标识> [ ,…n ] | REMOVE FILE 逻辑文件名 | MODIFY FILE <文件标识> | MODIFY NAME=新数据库名 } 参数说明： （1）ADD FILE：指定要添加的主数据文件。 （2）ADD LOG FILE：将日志文件添加到指定的数据库。 （3）REMOVE FILE：从数据库系统表中删除文件。 （4）MODIFY FILE：指定要更改给定的文件，包括逻辑名、初始大小、增长速度、最大容量和存储位置。 （5）不能够在一个命令中同时修改2个文件的属性，如果要修改两个文件的属性，要两次使用ALTER DATABASE命令，每个命令只修改一个文件的属性。 （6）若要更改数据文件或日志文件的逻辑名称，应在 NAME 选项中指定要改名的逻辑文件名称，并在 NEWNAME 选项中指定文件的新逻辑名称。其格式是： ALTER DATABASE <数据库名> MODIFY FILE (NAME = 原逻辑名, NEWNAME = 新逻辑名) 18 管理数据库 2.3.1 修改数据库 2、用命令修改数据库 例2-6：将数据库company改名为comp，写出SQL命令。 ALTER DATABASE company MODIFY NAME=comp 任务2.3 例2-7 对于例2-3中建立的数据库student，将数据文件的最大容量修改为200MB；每次以10 MB的空间增长；日志文件修改为每次以15%的空间增长。 在写SQL命令前，必须了解数据文件和日志文件的逻辑名（分别为student_data和student_log）。 1. ALTER DATABASE student 2. MODIFY FILE 3. ( NAME='student_data', 4. MAXSIZE=200MB, 5. FILEGROWTH=10MB 6. ) 7. GO 8. ALTER DATABASE student --此行不能省略 9. MODIFY FILE --此行也不能省略 10. ( NAME='student_log', 11. FILEGROWTH=15% 12. ) 19 管理数据库 2.3.2 删除数据库 1、用SSMS窗口删除数据库 在SSMS管理器窗口中，找到要修改的数据库名，右击它，在弹出的快捷菜单中选择【删除】。 任务2.3 2、用命令删除数据库 命令是： DROP DATABASE 数据库名 [ ,…n ] 可以用一条命令同时删除多个数据库。 例2-9 删除用户数据库test。 USE master GO DROP DATABASE test GO 例2-10 同时删除数据库test1和test2，假设test1和test2都已经存在。 DROP DATABASE test,test2 20 管理数据库 2.3.3 查看数据库 1、用SSMS窗口查看数据库 在SSMS管理器窗口中，在指定数据库的属性窗口即可查看。 任务2.3 2、用命令查看数据库 查看数据库属性的命令是sp_helpdb，格式是： EXECUTE sp_helpdb [数据库名] 例2-11 查看数据库libsys的属性。 sp_helpdb libsys 例2-12 查看所有数据库的属性。 EXECUTE sp_helpdb 21 管理数据库 2.3.4 分离与附加数据库 1、分离数据库 例2-13 分离数据库libsys。 （1）用SSMS窗口实现：在数据库属性窗口中，依次执行【任务】-【分离】。 任务2.3 （2）用命令分离数据库 使用系统存储过程sp_detach_db，其格式是： EXECUTE sp_detach_db 数据库名 USE master GO EXECUTE sp_detach_db libsys 22 管理数据库 2.3.4 分离与附加数据库 2、附加数据库 例2-14 将例1-12中分离的数据库libsys附加到SQL SERVER系统，物理文件存放在D盘data文件夹中，分别是libsysdata.mdf和libsyslog.ldf。 （1）用SSMS窗口实现：在数据库属性窗口中，依次执行【附加】-【定位数据库】。 任务2.3 （2）用CREATE database命令完成，格式是： CREATE DATABASE 数据库名 ON (FILENAME=主数据库物理文件名) FOR ATTACH 例2-14的命令是： CREATE DATABASE libsys ON (FILENAME= 'D:\data\libsysdata.mdf') FOR ATTACH 23 数据库的创建与管理 项目总结 项目二 1、主要内容 任务2.1 查看数据库服务器信息 任务2.2 创建数据库 任务2.3 管理数据库 2、自测 管理数据库的系统存储过程有哪些？ 创建数据库时，需要提供哪些参数？ 如何实现将数据库转移到其它机器上？ 在Windows中删除对应的文件以后，数据库还能正常使用吗？ 24 $$