ms sqlserver.doc

第一章、sql安装和规划课程结构图SQL Serever 2005的主要版本版本适用范围企业版(Enterprise)超大型/企业级应用32&64位，集群、数据库镜像，数据库快照标准版(Standard)工作组/部门级应用 32&64位工作组版(Workgroup)入门级 32位精简版(Express)简单易用 32位，低端用户软件要求IE 6.0+Internet Information Services 5.0+TCP/IPMicrosoft .NET Framework 2.0（如果没有回自动安装）Microsoft Windwos Installer 3.1（如果没有回自动安装）SQL Serever 2005安装组件安装组件说明数据库服务数据库引擎，复制，全文检索Integration Services数据转换Analysis Services在线分析和数据挖掘Notification Services应用程序发送通知Reporting Services制作和发布报告客户端组件，文档，工具工具和文档安装过程分为2个阶段预安装阶段必备组件更新系统一致性检查正式安装阶段安装所选功能安装的类型：默认或命名实例服务账户身份验证模式排序规则设置开始安装完成安装验证数据库安装成功验证的三种方法【开始】|【程序】中可以看到Microsoft SQL Server 2005的程序组查看安装日志文件信息安装日志组件安装日志启动和停止数据库服务启动或停止的三种方式在服务管理上中停止或启动在【SQL Server配置管理器】中启动或停止在【SQL Server外围配置应用配置器】中启动或停止SQL Server 2005管理工具管理工具说明SQL Server Management Studio用于访问，配置，控制，管理和开发SQL Server的所有组件。如编辑和执行查询，启动标准向导任务。Business Intelligence Development Studio用于Analysis Services和Integration Services解决方案的集成开发环境SQL Server Profiler用于监视SQL Server数据库引擎实例或Analysis Services实例的图形用户界面SQL Server配置管理器管理服务器和客户端网络配置设置SQL Server外围应用配置器用于启动或停止SQL Server安装的一些功能，服务和远程连接，提高SQL Server的安全性SQL Server Management Studio注册服务器保存SQL Server实例信息连接或分组SQL Server实例查看SQL Server实例的运行状态对象资源管理器管理SQL Server实例中所有对象的用户界面查询编辑器运行Transact-SQL通过SQL Server Management Studio注册服务器可以保存实例连接信息，连接和分组实例，查看实例运行状态注册服务器必须指定服务器类型服务器的名称登陆到服务器时使用的身份验证SQL Server中的数据库系统数据库master：记录SQL Server所有系统级别的信息，所有登陆账户，系统配置设置model：创建的所有数据库的模板tempdb：用于保存临时对象或中间结构集msdb：用于SQL Server代理计划报警和作业resource：一个只读数据库，包含SQL Server 2005所有的系统对象用户数据库AdventureWorks：示例数据库User1，User2：用户自定义的数据库SQL Server数据库存储结构一个数据库至少包含一个数据文件和一个日志文件数据文件是指存储数据的日志文件是指存储日志的数据库文件主数据文件.mdf辅助数据文件.ndf事务日志文件.ldf数据文件结构组成页SQL Server最小存储数据单元一个页大小是8K区管理空间的基本单位8个物理上连续的页（64KB）表被存储在区SQL Server数据存储结构第二章、sql管理数据库新建数据库新建用户数据库所有者一个数据文件一个日志文件初始大小自动增长默认路径修改数据库扩展数据库当数据库中的数据文件或日志文件的空间被占满自动扩展设置文件自动增长指定允许文件增长到的最大大小手动扩展数据库的大小添加数据库文件收缩数据库收缩数据库可以把不使用的空间释放出来自动数据库收缩将“自动收缩”选项设置为TURE手动数据库收缩收缩数据库收缩某个数据库文件分离和附加数据库用于移动数据库分离数据库将数据库从SQL Server实例中移除除了系统数据库之外保持数据文件和事务日志文件的完整性和一致性附加数据库分离后的数据库的数据和事务日志文件同一个SQL Server实例不同的SQL Server实例删除文件和数据库删除文件文件必须完全为空，才能够被删除删除数据库文件及其数据都从服务器上的磁盘中删除一旦删除数据库，即被永久删除不能删除系统数据库阶段练习背景Benet公司建立一个TestDB数据库用于应用开发，TestDB包含一个数据文件和一个日志文件。现在要：设置TestDB数据文件初始大小为5M，自动增长，最大大小为50M设置TestDB日志文件初始大小为5M，自动增长，限制文件增长为20M设置TestDB为自动收缩目标扩展数据库收缩数据库新建表表和列的名称必须遵守标识符的规定列名在表中必须是唯一的必须为每列指定数据类型常用数据类型精确数字int-231到231-1日期和时间datetime 1753-1-1到9999-12-31字符串varchar 最大存储大小是231-1个字节Unicode字符串nvarchar最大存储大小是231-1个字节向表添加数据使用查询设计器使用查询编辑器Transact-SQL语句可以实现数据的查询和数据的更改等操作SELECTINSERTUPDATEDELETE查询语句命令格式SELECT select_list查询语句FROM table_list来自哪个表WHERE search_conditions指定的查询条件ORDER BY order_listASC | DESC排列顺序（降序或升序）例1、 查询所有员工的信息SELECT * FROM Employees“*”表示对所有列的引用例2、 如果只是想要得到某几列的信息SELECT Name,Title FROM Employees明确地列出指定的列的内容列3、查询所有工资大于4000的员工SELECT * FROM Employees WHERE Salay4000where 子句-按指定的条件返回数据行操作符：=、BETWEEN AND、NOT、LIKE等。例4、查询工资在3000-4000的员工SELECT * FROM Employees WHERE Salary Between 3000 and 4000BETWEEN AND：指定值的范围例5、查找雇员表中姓张的员工SELECT * FROM Employees WHERE Name LIKE 张%;/“%”代表多个字符，“_”代表一个字符LIKE：字符串进行模式匹配例6、返回按工资降序排序的结果SELECT * FROM Employees ORDER BY Salary DESCORDER BY：对行进行排序关键字DESC/ASC：降序/升序排列例7、在雇员信息表中加入一个新员工INSERT INTO Employees VALUES(10,王燕,销售员,1986-11-30,4500)例8、把Employees表内容导入到EmployesNew新表中SELECT * INTO EmployeesNew FROM Employees从另一个表插入行例9、将所有销售员的工资提高到10%UPDATE Employees SET Salary=Salary *1.1 WHERE Title=销售员/SET后面的意思是要更新的列和新值的列表；WHERE后的意思是搜索条件例10、当销售员李芳离职后，将其从表中删除DELETE FROM Employees WHERE name=李芳删除符合条件的行例11、从EmployeesNew表中删除所有信息DELETE FROM EmployeesNew删除所有行表的维护修改表增加列删除列删除表主键：标识唯一的PK(唯一标识符)，每个表都要有一个主键外键：创建表与表之间的互连，表与表之间是有关系的（FK）定义主键和外键外键所在的值，如果是主键那它就是从表SQL Server 2005安全机制身份验证模式windows身份验证模式SQL Server和Windows身份验证模式修改：服务器的角色什么是服务器角色执行服务器级管理操作的用户权限的集合是系统内置的不能创建服务器角色每个角色有一定的权限登陆账户可以添加到服务器角色固定服务器角色描述sysadmin执行任何活动dbcreator可以创建，更改数据库serveradmin可以更改服务器范围的配置选项和关闭服务器security管理和审核登陆账户processadmin可以终止SQL Server实例中运行的进程setupadmin配置复制和链接服务器diskadmin用于管理磁盘文件bulkadmin可以运行BULK INSERT语句以有完全权限的角色登陆，以下是新建一个登陆（就像windows server 2003新建一个用户，然后给相应的权限）数据库用户访问数据库需要在哪个数据库上建立数据库用户从登陆中映射特殊用户Dboguest数据库角色固定数据库角色一组SQL Server预定义的数据库角色固定数据库角色描述db_owner拥有数据库的所有许可db_securityadmin能建立架构，更改角色db_accessadmin能添加或删除用户，组或角色db_backupoperator能备份数据库db_datareader能从数据库表中读数据db_datawriter能修改数据库中的数据db_ddladmin能添加，修改或删除数据库对象db_denydatareader不能从数据库表中读数据db_denydatawriter不修改数据库表中的数据public维护默认的权限用户定义数据库权限一组用户具有相同的权限数据库用户账户和角色添加数据库角色成员例如：添加数据库用户Tom和Frank到Develop角色权限的例子表权限描述Select查询表Insert插入表Update更新表Delete删除表数据库权限描述Control数据库的所有权限Create Table在数据库中建表为表授权例如，在Benet数据库中授予用户TESTSQLSQLadmin在Employees表如下权限SelectUpdateDelete表权限设置实例这样子qq用户只能在csun数据库下查询表，但不能修改任何东西可重新编辑qq用户属性备份与还原为什么需要数据备份数据丢失的原因程序错误人为错误计算机失效磁盘失败灾难（如火灾，地震等）和偷窃什么是备份数据的副本用于在系统发生故障后还原和恢复数据文件复制备份备份类型备份类型说明完整备份备份完成时刻的数据库，提供了任何其他备份的基准差异备份事先已执行一次完整备份，对上一次完整备份之后所有更改的数据作备份事务日志备份事先已执行一次完整备份，与windows增量备份类似，从上一次事务日志备份到当前事务日志的尾部，截断已确认的事务日志恢复模式恢复模式说明简单事务日志不能用于还原数据，用于小型数据库和不经常更改的数据库完整所有事务都记录到日志，保留所有日志直到事务日志备份用于产生数据库大容量日志完整恢复模式的补充，只对大容量操作进行最小记录（大容量的相同操作，做最小记录）备份还原策略策略说明完整备份和还原小型数据库，数据库很少改变或只读完整+差异备份与还原数据库频繁更改，想要最少的备份时间完整+事务日志备份与还原数据库和事务日志备份相结合，数据库经常更改，完整备份时间过长示例1完整备份和还原只能选择任意的一个完整数据库备份进行还原还原到10:00还原到11:00还原到12:00示例2完整备份+差异备份与还原如果需要还原到11:00时的数据库状态完整数据库备份1+差异数据库备份2如果需要还原到12:30时的数据库状态完整数据库备份2+差异数据库备份3示例3完整备份+日志备份与还原如果需要还原到11:00时的数据库状态完整备份1+日志备份1+日志备份2如果需要还原到12:30时的数据库状态完整备份2+日志备份3完整备份1+日志备份1+日志备份2+日志备份3如果需要恢复到10:45时的状态完整备份1+日志备份1+日志备份2指定到10:45的恢复即时点备份设备SQL Server支持的媒体磁盘磁带备份设备物理如：C:BackupsAccountingFull.bak逻辑存储在SQL Server内的系统表中如：Acconunting_Backup案例1：完整备份与还原完整数据库备份操作要点新建备份设备【任务】|【备份】指定数据库备份类型-完整备份及过期日期覆盖媒体完成后验证备份写入媒体前检验校验和完整数据库还原操作要点【任务】|【还原】|【数据库】目标数据库源数据库选择用于还原的备份集-完整覆盖现有的数据库：指定还原操作应覆盖所有数据库及相关文件，即使已存在同名的其他数据库或文件保留复制设置：将已发布的数据库还原到创建该数据库的服务器之外的服务器时，保留复制设置还原每个备份之前进行提示：还原初始备份之后，此选项会在还原每个附加备份集之前打开“继续还原“对话框，该对话框将要求你指示是否要继续进行还原限制范问还原的数据库：使还原的数据库仅供db_owner，dbcreator或sysadmin的成员使用还原选项RESTORE WITH RECOVERY默认的方式SQL Server将回滚所有未确认的事务，前滚所有确认的事务，在恢复过程完成后数据库可用RESTORE WITH NORECOVERYSQL Server将不回滚任何未确认的事务，也不前滚任何确认的事务，数据库不可用RESTORE WITH STANDBY使数据库处于备用状态，选择此选项需要指定一个备用文件恢复状态案例2：完整+差异备份与还原差异数据库备份操作要点新建备份设备【任务】|【备份】指定数据库备份类型-差异覆盖媒体完整+差异数据库还原操作要点【任务】|【还原】|【数据库】目标数据库源数据库选择用于还原的备份集-完整+差异还原选项恢复状态完整+日志备份与还原方案日志备份操作要点新建备份设备【任务】|【备份】指定数据库备份类型-事务日志覆盖媒体完整+日志还原操作要点【任务】|【还原】|【数据库】目标数据库源数据库选择用于还原的备份集-完整+日志还原选项恢复状态新建备份设备选择数据库进行备份选择备份目标背景：x公司建立1个数据库，名字叫x，存储了公司重要的数据，为了防止发生意外造成数据丢失，需要选择合适的备份策略对数据进行备份，最后通过还原数据验证策略正确性具体要求如下实施备份策略日志文件需要备份每天需要做一次完整备份中间还要做一次差异备份完成标准根据要求建立完整备份根据要求建立差异备份根据要求建立事务日志备份还原数据库背景：根据备份策略，已建立好x数据库备份，现在需要还原数据库当数据文件损坏时，还原数据库完成标准根据要求还原备份的数据库验证还原的结果数据转换的原因数据格式的转换OracleAcessExcel文本文件数据库对象的转移在不同服务器之间复制数据转换前需要考虑的问题更改数据格式如：1、0”TRUE”或“FLASE”数据的重构和映射如：从多个表中抽取数据，然后存储到一个单独的表数据的一致性如：Benet Corp、Benet或BenetCorporation验证数据有效性如：将顾客信息转换到目标数据之前，先验证顾客ID是否存在常用数据转换工具工具描述用途T-SQL语句Select into或Insert Select从现有表中选择数据并添加到表中备份和还原Backup和Restore将一个完整的SQL Server数据库拷贝到另外一个SQL Server中分离和附加将数据库分离或附加到SQL Server服务器上通过拷贝数据文件，将一个完整的SQL Server数据库移动或拷贝到另外一个SQL Server中复制将从源数据库中复制数据移动到目标数据库有间隔地将数据拷贝到多个数据库中SSIS导入导出向导允许用户交互式的创建用于导入，导出和数据转换的SSIS包在异种数据源之间转换数据，或是将某个SQL Server数据库中的对象转换到另一个SQL Server数据库中SSIS包允许有经验的数据库管理员导入，导出和转换数据，定义复杂数据的工作流从多个数据源中转换同种或异种数据，以及设置复杂的工作流，是更高级的转换和传输工具使用导入导出向导导入和导出向导概述案例将SQL Server数据导出到TXT文件中将Excel数据导入到SQL Server在不同的SQL Server之间转换数据案例1操作要点【任务】|【导出数据】选择数据源SQL Native Client选择目标平面文件目标指定表运行验证导出结果案例2操作要点【任务】|【导入数据】选择数据源Microsof Excel选择目标SQL Native Client指定表执行验证导入结果案例3操作要点【任务】|【导出数据】选择数据源SQL Native Client选择目标SQL Native Client指定表执行验证导入结果使用SSIS包SSIS概述包控制流元素任务数据流任务连接管理器案例将SQL Server数据导出到txt文件将Access数据导入到SQL Server案例1操作要点建立SSIS项目建立数据源SQL Native Client为数据源建立连接管理器建立数据流任务OLE DB源运行包并显示结果案例2操作要点建立SSIS项目建立数据源Microsoft Jet 4.0 OLE DB provider为数据源建立连接管理器建立数据流任务SQL Native Client运行包并显示结果复制什么是复制维护相同数据的多个拷贝不同地点的用户可以方便的访问数据在数据库间进行同步避免冲突复制模型复制组件说明发布服务器提供数据以便复制到其他服务器的服务器分发服务器它起着存储区的作用，用于复制域发布服务器相关联的特定数据订阅服务器接收复制数据的服务器项目用于识别发布中包含的数据库对象（如：表）发布是来自一个数据库的一个或多个项目的集合订阅是把发布副本传递到订阅服务器的请求有两种类型的订阅：推送与请求复制模型复制类型快照复制发送所有的数据根据计划事务性复制建立初始快照发送修改的数据连续合并复制建立初始快照修改在发布方和订阅方按需实施复制背景在x数据库中，有许多应用与销售信息记录表（Sales表）相关联，作为数据库管理员，为了避免冲突，需要将Sales表建立副本到一个不同的SQL Server实例上，原实例中的表用于数据更新，副本则用于查询分析，并且需要随时保存同步更新。步骤在默认实例Tahiti上新建发布在命名实例Tahitilns1上新建订阅验证复制创建发布操作要点【复制】|【本地发布】|【新建发布】分发服务器发布数据库发布类型事务性发布项目表快照代理代理安全性创建发布创建订阅操作要点【复制】|【本地订阅】|【新建订阅】发布分发代理位置订阅服务器分发代理安全性同步计划初始化订阅创建订阅验证复制操作要点查看复制的表修改发布表的数据，查看同步结果导出数据建立目标类型，和选择目标存放地址然后下一步下一步即可导入Excel数据选择数据源Excel，和选择数据源文件SSIS包创建发布创建订阅性能监视器的介绍对象如硬盘，进程，SQL Server实例计算器用于收集所要监视对象的某方面信息实例一些对象会有多个实例性能对象SQL Server性能对象Buffer Manager提供有关SQL Server使用的内存缓冲区的信息Databases提供有关SQL Server数据库信息Genneral Statistics提供有关服务器范围内的常规活动的信息Memory Manager提供有关SQL Server内存使用量的信息系统性能对象CPUProcessor - % Processor TimeProcess - % Processor Time内存Menory - Pages/sec硬盘%Disk TimeAvg. Disk Queue Length使用性能监视器SQL Server Profiler跟踪SQL Server和数据库的活动创建基于可重用模板的跟踪当跟踪运行时监视跟踪结果将跟踪结果保存在表中根据需要启动，停止，暂停和修改跟踪结果重播跟踪结果事件通过SQL Server Profiler来监视感兴趣的事件执行查询的性能单个用户或应用程序的活动登陆的尝试，失败，连接，断开逻辑磁盘的读/写在语句级别的CPU占用事件类事件类事件说明Security AuditAudit Login一个用户成功登陆到SQL ServerAudit logout一个用户退出SQL ServerSessionsExistingConnection当跟踪开始时已存在用户连接的属性Stored ProceduresRPC:completed一个远程过程调用完成TSQLSQL:BatchCompleted一个Transact-SQL批处理完成SQL:BatchStarting一个Transact-SQL批处理开始使用SQL Server ProfilerSQL Server代理什么是SQL Server代理SQL Server自动化组件存储在MSDB系统数据库中服务器必须启动运行作业触发警报联系操作员设置SQL Server代理自动启动SQL Server自动化管理操作员在完成作业或出现警报时接受电子通知的人员或组能够通过：邮件，寻呼机，Net Send作业作业是以由一系列SQL Server代理按顺序执行的一个指定操作包含一个或多个步骤，每个步骤都有自己的任务能够计划：一次运行，重复运行，手动运行警报对事件的自动响应警报名触发的事件或条件响应：通知操作员或启动一个作业建立作业操作要点配置【Messenger】服务新建操作员新建作业步骤通知Net Send查看作业活动修改作业开始作业建立警报操作要点新建警报类型对象条件响应通知操作员选项Net Send维护计划向导帮助管理员计划关键任务备份数据库和事务日志文件运行SQL Server代理作业收缩数据文件检查数据库完整性执行SQL Server代理作业检查错误日志排除与SQL Server相关的错误包含用户定义的事件和某些系统事情专用管理员连接（DAC）为DBA提供的一种特殊的诊断连接无法与服务器建立标准连接（比如CPU,内存满的时候）只有sysadmin角色的成员可以使用DAC连接客户端连接连接到SQL ServerTCP/IP命名管道VIA共享内存协议示例比如进行数据查询时做了一个死循环，导致CPU使用率达到百分百。进行专用管理员连接（DAC）本章目标工作组环境下账户和组的管理理解工作组的特点掌握本地用户账户的管理掌握本地组账户的管理掌握ALP规则工作组的特点每一台计算机都独立维护自己的资源，不能集中管理所有网络资源每一台计算机都在本地存储用户的账