SQL数据库管理教材.docx

1、SQL数据库管理教材第1单元 SQL Server 概述2什么是SQL Server （Structure）数据库分网状，层次（注册表），关系型处理关系数据库的语言有很多种，其中最常用的是 SQL。美国国家标准化组织 (ANSI) 和国际标准化组织 (ISO) 定义软件标准，包括 SQL 语言的标准。SQL Server 2000 支持 SQL-92 入门级 (Entry Level)，即由 ANSI 和 ISO 在 1992 年公布的 SQL 标准。Microsoft SQL Server 支持的 SQL 方言称为 Transact-SQL (T-SQL)。T-SQL 是 Microsoft

2、 SQL Server 应用程序使用的主要语言。利用SQL Server 能够管理两种类型的数据库，联机事务处理（OLTP）数据库和联机分析（OLAP）数据库SQL Server 入门知识 SQL的功能管理事务和分析中的数据存储过程 相应客户应用程序的请求 能够利用Transact-SQL XML 多维表达式 (MDX) SQL分布管理对象（SQL-DMO） 发送客户端与SQL之间的请求 关系型数据库理系统维护数据库数据之间的关系保证数据存储的正确性，保证定义数据之间的关系的种种规则不会违背当出现系统故障的时候，将所有数据恢复到能够保证一致性的某种状态 数据存储模型OLTP降低数据的冗余信息，

3、加快数据库的更新速度。SQL Server支持大量用户执行事务处理。OLAP对大量数据进行组织和汇总，分析程序能够对数据进行快速评价。 客户应用程序1. Transact-SQL SQL Server使用的数据可查询语言2. XML 通过URL或HTTP的模板，XML能返回查询和存储过程的结果，也能更新，删除，插入数据库中的值。3. MDX 语法定义了多维对象和多维查询，能操作OLAP数据库中的多维数据。4. OLE DB 可访问关系型和非关系性数据库，ODBC只能访问关系型数据库，ADO是微软将OLE DB封装成控件而成。ODBC API 通过使用OLE DB 和 开放式数据互连（ODBC）

4、和应用程序编程接口。客户端应用程序能够向数据库发送命令。通过API发送的命令是Transact-SQL语言。5. Active X数据对象和Active X数据对象多维 客户端/服务器组件客户端负责业务逻辑，将数据呈现给用户，客户端运行在一台或多台计算机中。SQL Server管理数据库，并负责在多个请求之间分配服务器资源，内存 带宽和磁盘操作。1. 客户端应用程序发出Transact-SQL请求语句，收集结果，应用程序不必知道与服务器通信的底层协议2. 数据库API （OLE DB和ODBC（只能访问关系型的数据库）是应用程序向数据库提交语句，并收集相应的结果集。3. 客户端Net-Libr

5、ary 管理客户端的路由和网络连接，将数据请求和结果集进行一定的封装。4. Net-Library 接收数据包5. 开放式数据服务（ODS）自动侦听安装在服务器中的全部Net-Library并将数据包重新整理成命令，检查错误。6. 关系引擎，负责Transact-SQL语句的解析，对产生的执行计划进行优化，并执行相应的计划。7. 存储引擎，管理数据库文件和文件空间的利用情况，从物理页面读取数据控制并发访问，执行日志纪录和恢复操作，数据一致性检查，备份和恢复。 客户端/服务器通信 SQL Server服务1. MSSQL Server服务 核心服务，负责处理所有的Transact-SQL语句,并

6、管理服务器上所有构成数据库的文件，验证用户，在多个并发用户之间分配计算机上的资源，避免出现逻辑上的故障，如不同用户同时对相同数据进行更新。确保数据的一致性，和完整性。2. SQL ServerAgent 服务依赖于MSSQL Server 完成自动化管理，来创建和管理警报，本地或多服务企业，操作员，默认停止。3. MS DTC 分布式事务处理协调器允许客户在一个事务中包括几个不同的数据源，再出现错误的情况下也能保证数据的一致性。4. MS Search 全文搜索引擎，作为2000的服务进行，支持全文本功能，能够对字符数据进行查询 （相当于模糊查找select * from northwind.

7、products where contains(productname,sdhfjs)5. SQL Server 的多实例多实例复制，可实现容错。可修改实例使用的端口号。各实例独立运行，具有自己的系统设置和用户数据库。命名实例ctec00SQLname，默认实例ctec0013 SQL Server 集成化 集成SQL Server 和操作系统SQL Server 客户端能运行在2000，NT，95，98，其他操作平台上的IE浏览器。服务器组件SQL Server企业版只能运行在2000 Server，NT安装需Service pack 5 集成SQL Server 和Windows 2000

8、活动目录 通过AD定位SQL安全性 集成身份验证，域用户账号多处理器支持（SMP）SQL 2000支持2000的对 x 称多处理，默认使用标号高的CPUMicrosoft 事件查看器 通过应用程序日志触发SQL Server警报Windows 2000系统监视器 能够监视SQL Server性能Microsoft IIS服务 SQL Server利用IIS，IE浏览器能够访问http协议访问SQL Server数据库Windows 群集 Advanced Server 集成SQL Server 和其他Microsoft服务应用程序 ISA Exchange 活动目录1. 4SQL Server

9、数据库 数据库类型Master 从整体上控制用户数据库和SQL Server 操作。环境变量，系统错误消息。Model 模板经修改将影响新建的数据库Tempdb 存放临时表和其他临时存储需求提供存储区域。相当于操作系统的虚拟内存，关机清空。Msdb 支持管理任务自动化，存放警报和调度信息Disribution 为复制过程中所用的历史和事务数据提供存储区，数据导入导出产生 数据库对象表、数据类型、约束值、默认值、规则、索引（建立在列上提高查询速度，集聚索引和非聚集索引）、视图、用户自定义函数、存储过程（经过预编译的Transact-SQL语句的集合有自己的名称exec sp_help）、触发器（

10、特殊的存储过程，通过条件激发） 引用SQL Server 对象完全限定名 服务器实例.数据库.所有者.对象 所有者属于自己的对象可northwind.orders所有者可省。但ctec00.northwind.dbo.orders不可省对象可重名但不可是相同的所有者。create table northwind.dbo.OrderHistory1(orderID int,ProductID int,UnitPrice money,Quantity int) sp_helpindex northwind.dbo.orders 系统表1. 系统表，包含所有数据库和数据库对象的定义及配置信息。用户不

11、能直接修改系统表2. 数据库目录，每个数据库都包括一组存储这个特定数据库元数据的系统表，该组系统表就是该数据的目录，包含数据库中的对象的定义和权限3. 系统目录，只有master 数据库中才有系统目录sysloginssysmessages 每个SQL Server所返回的系统错误或警告都占相应的行sysdatabasessysusers 所有的数据库都有sysobjects 所有的数据库都有 元数据检索系统存储过程sp_help sp_help 过程仅在当前数据库中查找对象。sp_helpdb sp_helpdb northwindsp_helpindex northwind.dbo.ord

12、ers 查索引必须扩展存储过程EXEC sp_grantlogin onest00BobJ EXEC master.xp_cmdshell dir *.exeEXEC master.xp_cmdshell ping 192.168.10.1 系统和元数据函-提供了一种在Transact-SQL语句中查寻系统表的方法。 select db_id(northwind) select col_length(orders,orderid) as varchar select user_name() select stats_date （） select datalength (fgsdf) 信息模式视

13、图 （内部的，与系统表无关的SQL Server元数据视图）select * from information_schema.tables 数据库中表的清单select * from information_schema.columns 数据库中定义的列的清单select * from information_schema.table_privileges 给出数据库中表的安全信息15 SQL Server 安全性两个级别的身份验证，登陆身份验证和对数据库用户账户和角色的许可权限有权进入SQL 有权访问数据库 有权访问对象 有权访问语句 登陆身份验证（有权连接），Windows身份验证（只查帐

14、号）和SQL身份验证（确认用户和密码）syslogins 中存放登陆账号 身份验证模式 Windows 身份验证模式，不能指定SQL登陆账户混合模式两种方式均可 数据库用户账户和角色登陆到SQL Server的用户，还必须是某个数据库的用户才能访问数据库的资源。依赖于所属的角色。Guest可删除创建，guest拥有public角色权限。 角色类型固定服务器角色dbcreator diskadmin processadmin securityadmin serveradmin setupadmin sysadmin bulkadmin 固定数据库角色public db_owner db_acce

15、ssadmin db_ddladmin db_securityadmin db_backupoperatordb_datareader db_datawrite db_denydatareader db_denydatawriter用户定义的数据库角色 权限累加 拒绝优先 许可权限验证16应用SQL Server 常用的管理任务安装配置SQL Server，保证安全。建立数据库，为数据库和日志分配磁盘空间，传送数据进出数据库，定义并实施数据库的安全性，创建可重复性任务的自动作业，将数据库发不到多个站点而进行数据的复制工作。管理正在进行的活动，比如导入/导出，数据库和日志的备份，监控与调整数据库

16、。 管理SQL 图形界面 企业管理器，查询分析器，服务管理器，事件探查器。 命令提示符管理工具 Osql E 以windows身份运行Select * from northwind.productsGo Exit or quitOsql U sa P 以SQL身份运行，参数区分大小写。 帮助 查询分析器 shift + F1 实现SQL Server 设计数据库（创建数据库对象和应用逻辑，预留发展空间）创建数据库和数据库对象（创建表、数据完整性机制、创建索引和安全机制）测试和调整数据库和应用程序（为提高性能，正确的使用索引，RAID和文件组）规划部署 （分析工作负荷，以及SQL推荐优化的索引配

17、置） 选择SQL Server 的应用架构表示层 （把数据和应用呈现给用户）业务层（应用逻辑和业务规则,也可包括SQL Server）数据层（数据库定义，数据完整性逻辑，存储过程SQL Server主要属于该层）1. 智能服务器，数据库中实现大多数业务服务逻辑，客户端资源要求不高。业务层修改容易。2. 智能客户端，大多数处理在客户端进行，服务器处理数据服务。业务层修改困难。3. N层结构，处理工作分配在数据库和应用程序之间，非常容易的地增加应用程序服务器和数据库服务器。4. Internet 表示层和业务层驻留在web服务器上，客户端使用Internet浏览器。 利用数据库API设计应用程序应

18、用程序利用接口把点击动作转化成T-SQL语句ODBC只能访问关系型的数据库。OLE DB可访问关系的和部分非关系的数据库，可调用ODBC。微软开发的可访问关系型和非关系型数据库。微软将OLE DB封装成控件 ADO作为API。第2单元 安装和配置SQL Server22 SQL Server 的硬件安装要求 安装SQL Server 的最低要求 处理器 Windows 2000 or NT 要求主频166M以上，支持多处理器。内存 Windows 2000 Server 最低256 ， NT 4.0各种服务器 64。数据库规模大，适当增加。硬盘空间 典型安装需250M 完全安装270M ,客户

19、端工具100M。 优化SQL Server 的硬件配置处理器，增加数量或更换高速处理器，指定SQL使用使用那几个处理器对称多处理（SMP）。最多2、4、8、32个。磁盘子系统，用户应考虑使用硬件或操作系统RAID，能够增加SQL 的性能、可靠性。内存， 数据库的虚拟内存存放在Tempdb数据库中，内存不足将会从磁盘读书据，磁盘性能会影响查询性能。网络，客户端通过网络访问会影响到SQL Server 的吞吐量。网卡，带宽，并发连接。23 SQL Server 的版本信息 标准板 服务器扩展，大型数据库支持，数据仓库，web站点支持欠缺。10几万元。适用于小部门数据库服务器。 企业版 支持最大的W

20、eb站点和企业联机事务处理（OLTP）。32万。24SQL Server的软件安装要求 授权模式 每处理器，允许任意数目的用户访问SQL Server。适合用户较多的情况。每客户机，SQL给每个连接用户或应用程序，租给其一个许可证。 确定服务的安全上下文默认SQL Server 和 agent 服务将当前用户作为服务启动的账户。使用域用户账号1、 SQL Server 必须访问域中其他计算机上的文件。2、 多服务器作业3、 利用SQL Server 以电子邮件方式发送通知4、 单台计算机上安装exchange 和 SQL Server 服务器产品之间进行通信5、 密码用不过期，登陆任何时间6、

21、 将SQL Server 安装在目录林中所有用户都能访问的域中7、 不推荐将SQL Server 安装在控制器上。使用本地系统账户自动启动服务，可以选择默认实例，和命名实例自动启动。Windows 98 和 Windows me上和 agent 服务，以应用程序运行。 使用SQL Server 的命名实例默认实例以计算机的名称来标识，只允许一个。可以是SQL Server的任意版本。命名实例计算机名实例名，彼此独立运行，最多同时运行16个，必须使用SQL Server 2000的客户端组件连接到命名实例，SQL Server 早期版本中的数据库引擎不能作为命名实例运行， 实例主要应用于数据库引

22、擎及其支持组件，而不应用于客户端工具。仅有一个 MSSearchService 复本管理针对计算机上所有 SQL Server 实例的全文检索。 选择安全性机制选择Windows 身份验证 和 混合式身份验证 身份验证模式可以更改注册表，sa账号最好14位以上，账户默认映射到本地管理员组。2混合1为Windows 选择SQL排序的规则SQL支持的字符集依赖于区域设置中的系统语言设置。安装SQL是就决定了其支持的字符集。标准的用于基于文本的计算机数据的编码构架。Unicode 使用 2 个字节（16 位）表示每个字符，这种方法允许分派 65,536 个可能的唯一的字符。这样数量的可能的字符数值允

23、许用单个字符集表示几乎世界上所有的可书写语言。可在微软网站上下载8sys*.sql 在查询分析器中运行。可支持多种语言。 选择网络库的类型服务器上的协议应包括客户端的协议。TCP/IP套接字，默认实例用1433端口，命名实例065535SQL 7.0之间的通信需启用多协议加密，SQL Server 2000 可用SSL Kerberos 加密。 安装SQL Server 的方法1. 标准安装 最小安装（无升级工具，客户端管理工具，联机丛书）典型安装，自定义安装2. 无人职守安装 在不同的计算机上执行相同配置的安装 3. 远程安装 为远程计算机的管理员 拥有安装源文件的访问权限26升级SQL S

24、erver 从SQL Server 7.0升级到2000 安装命名实例，将保留7.0为默认实例。 从6.5升级到2000 从6.0升级到2000 不能直接升级27验证安装的正确性 检阅安装结果SQL Server服务 管理工具 数据库 文件和文件夹 启动、停止、暂停SQL Server 服务 （如果服务账号修改密码，则可能启动失败）企业管理器 SQL Server 的服务管理器 服务net start mssqlserver 允许用户连接net pause mssqlserver 事务继续进行不能开始新的进程，不能建立新的连接，数据库维护。 net stop mssqlserver 事务前滚或

25、会滚，结束事务。 建立到SQL Server 的连接查询分析器 企业管理器 osql Osql E 以windows身份运行Select * from northwind.productsGo Exit or quitOsql U sa P 以SQL身份运行，参数区分大小写。28配置SQL Server 企业管理器 注册服务器 注册信息 win2000的注册表维护每个用户的SQL的注册信息。 注册多个命名实例和服务器，可同时管理多个命名实例。 创建服务器组 可将注册SQL Server 分组管理 访问注册信息，可访问共享注册信息（多用户用相同的身份进行连接和访问SQL Server）和专有注册

26、信息（默认为专有注册信息）。29排除故障 确定故障源查阅安装过程中错误消息查阅Sqlstp.log日志文件 记录SQL Server 安装过程的活动，c:winntwin2000应用程序日志 事件查看器SQL Server 错误日志 SQL Server Agent 错误日志记录启动SQL Server 各阶段的信息，c:programe fileMicrosoft SQL servermssqllog 企业管理器或文本编辑器 测试网络连接本地用户能够建立连接，网络客户不能。验证客户端的网络库与服务器网络库相匹配。能否与服务器建立网络连接。 查找常见故障的解决方案SQL Server 服务启动

27、失败，不能访问DC来进行身份验证。登陆失败造成SQL Server启动失败，登陆账号过期，密码更改。SQL Server Agent服务尚未启动 SQL Server Agent服务启动的域用户账号，不具备访问SQL Server的许可权，将于用户加入到本地管理员组，或SQL sysadmin角色，sysadmin被映射到本地管理员组。不能建立与SQL Serve的连接，服务没有启动 or 网络库 or 用户没有登陆权。210推荐做法 只使用专门为数据库使用的些缓存控制器 使用与组织相一致的排序规则，以便于其他的SQL Server数据库相互兼容。 使用专用的域用户账号登陆，账号用不过期，任何

28、时间登陆。 使用混合模式身份验证，为sa指定密码。 注册所有利用企业管理器进行管理的SQL Server第3单元 管理数据库文件32数据结构简介 如何存储数据1. 数据库文件 所有数据库包括一个主数据文件.mdf，包含该数据库的系统信息和用户数据，扩展数据库，还可能包括多个辅助数据文件.ndf。逻辑名和物理名2. 日志文件 一个或多个日志文件.ldf。事务日志保存对数据库的更改，可恢复数据库到任意时刻的状态。日志文件默认为总数据文件大小的1/4。3. model数据库复制产成新的数据库。4. 数据库中存储数据以页的形式8KB，8192个字节，128页/1MB,便于添加删除数据。5. 行不能跨页

29、 以行为单位读取数据。每行8060字节。页头占用96个字节。页尾用来定位一页中的行。每行5000个字节1000行的数据库占多大空间。8192X1000/1000/1000=8.192M6. 扩展由8个相邻的页，小表或新表放在混合盘区，统一扩展盘区有一个对象独占。索引独占扩展。 事务1. 事务是一组Transact-SQL语句的集合，批处理，不可分，原子性。2. 隐性事务 Alter table insert open create delete revoke select grant update set implicit_transactions on | off 将隐式事务作为显试事务3.

30、 显式事务 用户定义明确定义事务的开始和结束，begin transaction 和 end transaction4. 解释事务日志，为了维护数据的一致性，便于进行数据库恢复，数据库更改语句发挥作用的同时，各条数据更改语句被记录到事务日志中。 事务日志工作流程1. 应用程序请求2. 受影响的页面被调入内存（高速缓存）3. 数据库更改语句发挥作用的同时，各条数据更改语句被记录到事务日志中（预写日志）。4. 检查点进程将完成的事务的结果写回磁盘。保证内存的空间。5. 如果出现故障，将会从最后一次检查点，到出现故障的时间，在此期间那些事物已经提交的事物前滚，未提交的事物回滚。33创建数据库 创建数

31、据库过程中的现象1. 权限，对master数据库的写权限，和数据库文件所在的文件系统的NTFS权限，Sa or sysadmin映射到本机管理员组的成员。在创建好数据库后，向数据库中写数据不再受NTFS限制（可去除NTFS权限实现安全性，因为SQL不启动，数据库文件能够删除，不受保护。如果SQL启动后找不到日志文件，数据库将处于质疑状态，不可用磁盘限额，日志写不进去，也会出现质疑状态）。2. 创建数据文件和事务日志，指定大小用空白页填充。 数据库创建过程中的选项设定主文件组包括数据库系统表，主数据文件代表数据库的起点。指向数据库中的其他文件。.mdf辅助文件 用来扩展数据库。可属于主文件组或用户自定