SQL数据库管理教材.docx

SQL数据库管理教材.docx

《SQL数据库管理教材.docx》由会员分享，可在线阅读，更多相关《SQL数据库管理教材.docx（63页珍藏版）》请在冰豆网上搜索。

SQL数据库管理教材

第1单元SQLServer概述

2什么是SQLServer（Structure）

数据库分网状，层次（注册表），关系型

处理关系数据库的语言有很多种，其中最常用的是SQL。

美国国家标准化组织（ANSI）和国际标准化组织（ISO）定义软件标准，包括SQL语言的标准。

SQLServer2000支持SQL-92入门级（EntryLevel），即由ANSI和ISO在1992年公布的SQL标准。

MicrosoftSQLServer支持的SQL方言称为Transact-SQL（T-SQL）。

T-SQL是MicrosoftSQLServer应用程序使用的主要语言。

利用SQLServer能够管理两种类型的数据库，联机事务处理（OLTP）数据库和联机分析（OLAP）数据库

SQLServer入门知识

ØSQL的功能

管理事务和分析中的数据存储过程

相应客户应用程序的请求

能够利用Transact-SQLXML多维表达式（MDX）SQL分布管理对象（SQL-DMO）

发送客户端与SQL之间的请求

Ø关系型数据库理系统

维护数据库数据之间的关系

保证数据存储的正确性，保证定义数据之间的关系的种种规则不会违背

当出现系统故障的时候，将所有数据恢复到能够保证一致性的某种状态

Ø数据存储模型

OLTP降低数据的冗余信息，加快数据库的更新速度。

SQLServer支持大量用户执行事务处理。

OLAP对大量数据进行组织和汇总，分析程序能够对数据进行快速评价。

Ø客户应用程序

1.Transact-SQLSQLServer使用的数据可查询语言

2.XML通过URL或HTTP的模板，XML能返回查询和存储过程的结果，也能更新，删除，插入数据库中的值。

3.MDX语法定义了多维对象和多维查询，能操作OLAP数据库中的多维数据。

4.OLEDB可访问关系型和非关系性数据库，ODBC只能访问关系型数据库，ADO是微软将OLEDB封装成控件而成。

ODBCAPI通过使用OLEDB和开放式数据互连（ODBC）和应用程序编程接口。

客户端应用程序能够向数据库发送命令。

通过API发送的命令是Transact-SQL语言。

5.ActiveX数据对象和ActiveX数据对象多维

Ø客户端/服务器组件

客户端负责业务逻辑，将数据呈现给用户，客户端运行在一台或多台计算机中。

SQLServer管理数据库，并负责在多个请求之间分配服务器资源，内存带宽和磁盘操作。

1.客户端应用程序发出Transact-SQL请求语句，收集结果，应用程序不必知道与服务器通信的底层协议

2.数据库API（OLEDB和ODBC（只能访问关系型的数据库）是应用程序向数据库提交语句，并收集相应的结果集。

3.客户端Net-Library管理客户端的路由和网络连接，将数据请求和结果集进行一定的封装。

4.Net-Library接收数据包

5.开放式数据服务（ODS）自动侦听安装在服务器中的全部Net-Library并将数据包重新整理成命令，检查错误。

6.关系引擎，负责Transact-SQL语句的解析，对产生的执行计划进行优化，并执行相应的计划。

7.存储引擎，管理数据库文件和文件空间的利用情况，从物理页面读取数据控制并发访问，执行日志纪录和恢复操作，数据一致性检查，备份和恢复。

Ø客户端/服务器通信

ØSQLServer服务

1.MSSQLServer服务

核心服务，负责处理所有的Transact-SQL语句,并管理服务器上所有构成数据库的文件，验证用户，在多个并发用户之间分配计算机上的资源，避免出现逻辑上的故障，如不同用户同时对相同数据进行更新。

确保数据的一致性，和完整性。

2.SQLServerAgent服务

依赖于MSSQLServer完成自动化管理，来创建和管理警报，本地或多服务企业，操作员，默认停止。

3.MSDTC分布式事务处理协调器

允许客户在一个事务中包括几个不同的数据源，再出现错误的情况下也能保证数据的一致性。

4.MSSearch

全文搜索引擎，作为2000的服务进行，支持全文本功能，能够对字符数据进行查询（相当于模糊查找select*fromnorthwind..productswherecontains（productname,’sdhfjs’）

5.SQLServer的多实例

多实例复制，可实现容错。

可修改实例使用的端口号。

各实例独立运行，具有自己的系统设置和用户数据库。

命名实例ctec00\SQLname，默认实例ctec00

1．3SQLServer集成化

Ø集成SQLServer和操作系统

SQLServer客户端能运行在2000，NT，95，98，其他操作平台上的IE浏览器。

服务器组件SQLServer企业版只能运行在2000Server，NT安装需Servicepack5

Ø集成SQLServer和Windows2000

活动目录通过AD定位SQL

安全性集成身份验证，域用户账号

多处理器支持（SMP）SQL2000支持2000的对x称多处理，默认使用标号高的CPU

Microsoft事件查看器通过应用程序日志触发SQLServer警报

Windows2000系统监视器能够监视SQLServer性能

MicrosoftIIS服务SQLServer利用IIS，IE浏览器能够访问http协议访问SQLServer数据库

Windows群集AdvancedServer

Ø集成SQLServer和其他Microsoft服务应用程序ISAExchange活动目录

1.4SQLServer数据库

Ø数据库类型

Master从整体上控制用户数据库和SQLServer操作。

环境变量，系统错误消息。

Model模板经修改将影响新建的数据库

Tempdb存放临时表和其他临时存储需求提供存储区域。

相当于操作系统的虚拟内存，关机清空。

Msdb支持管理任务自动化，存放警报和调度信息

Disribution为复制过程中所用的历史和事务数据提供存储区，数据导入导出产生

Ø数据库对象

表、数据类型、约束值、默认值、规则、索引（建立在列上提高查询速度，集聚索引和非聚集索引）、视图、用户自定义函数、存储过程（经过预编译的Transact-SQL语句的集合有自己的名称execsp_help）、触发器（特殊的存储过程，通过条件激发）

●引用SQLServer对象

完全限定名服务器\实例.数据库.所有者.对象

所有者属于自己的对象可northwind..orders所有者可省。

但ctec00.northwind.dbo.orders不可省

对象可重名但不可是相同的所有者。

createtablenorthwind.dbo.OrderHistory1

（

orderIDint,

ProductIDint,

UnitPricemoney,

Quantityint

）

sp_helpindex[northwind.dbo.orders]

●系统表

1.系统表，包含所有数据库和数据库对象的定义及配置信息。

用户不能直接修改系统表

2.数据库目录，每个数据库都包括一组存储这个特定数据库元数据的系统表，该组系统表就是该数据的目录，包含数据库中的对象的定义和权限

3.系统目录，只有master数据库中才有系统目录

syslogins

sysmessages每个SQLServer所返回的系统错误或警告都占相应的行

sysdatabases

sysusers所有的数据库都有

sysobjects所有的数据库都有

●元数据检索

系统存储过程

sp_helpsp_help过程仅在当前数据库中查找对象。

sp_helpdb

sp_helpdbnorthwind

sp_helpindex[northwind.dbo.orders]查索引必须[]

扩展存储过程

EXECsp_grantlogin'onest00\BobJ'

EXECmaster..xp_cmdshell'dir*.exe'

EXECmaster..xp_cmdshell'ping192.168.10.1'

系统和元数据函--------提供了一种在Transact-SQL语句中查寻系统表的方法。

selectdb_id（'northwind'）selectcol_length（'orders','orderid'）as'varchar'

selectuser_name（）selectstats_date（）selectdatalength（'fgsdf'）

信息模式视图（内部的，与系统表无关的SQLServer元数据视图）

select*frominformation_schema.tables数据库中表的清单

select*frominformation_schema.columns数据库中定义的列的清单

select*frominformation_schema.table_privileges给出数据库中表的安全信息

1．5SQLServer安全性

两个级别的身份验证，登陆身份验证和对数据库用户账户和角色的许可权限

有权进入SQL有权访问数据库有权访问对象有权访问语句

●登陆身份验证（有权连接），

Windows身份验证（只查帐号）和SQL身份验证（确认用户和密码）syslogins中存放登陆账号

●身份验证模式

Windows身份验证模式，不能指定SQL登陆账户

混合模式两种方式均可

●数据库用户账户和角色

登陆到SQLServer的用户，还必须是某个数据库的用户才能访问数据库的资源。

依赖于所属的角色。

Guest可删除创建，guest拥有public角色权限。

●角色类型

固定服务器角色

dbcreatordiskadminprocessadminsecurityadminserveradminsetupadminsysadminbulkadmin

固定数据库角色

publicdb_ownerdb_accessadmindb_ddladmindb_securityadmindb_backupoperator

db_datareaderdb_datawritedb_denydatareaderdb_denydatawriter

用户定义的数据库角色权限累加拒绝优先

●许可权限验证

1．6应用SQLServer

●常用的管理任务

安装配置SQLServer，保证安全。

建立数据库，为数据库和日志分配磁盘空间，传送数据进出数据库，定义并实施数据库的安全性，创建可重复性任务的自动作业，将数据库发不到多个站点而进行数据的复制工作。

管理正在进行的活动，比如导入/导出，数据库和日志的备份，监控与调整数据库。

●管理SQL图形界面企业管理器，查询分析器，服务管理器，事件探查器。

●命令提示符管理工具

Osql–E以windows身份运行

Select*fromnorthwind..products

Go

Exitorquit

Osql–Usa–P以SQL身份运行，参数区分大小写。

●帮助查询分析器shift+F1

●实现SQLServer

设计数据库（创建数据库对象和应用逻辑，预留发展空间）

创建数据库和数据库对象（创建表、数据完整性机制、创建索引和安全机制）

测试和调整数据库和应用程序（为提高性能，正确的使用索引，RAID和文件组）

规划部署（分析工作负荷，以及SQL推荐优化的索引配置）

●选择SQLServer的应用架构

表示层（把数据和应用呈现给用户）

业务层（应用逻辑和业务规则,也可包括SQLServer）

数据层（数据库定义，数据完整性逻辑，存储过程SQLServer主要属于该层）

1.智能服务器，数据库中实现大多数业务服务逻辑，客户端资源要求不高。

业务层修改容易。

2.智能客户端，大多数处理在客户端进行，服务器处理数据服务。

业务层修改困难。

3.N层结构，处理工作分配在数据库和应用程序之间，非常容易的地增加应用程序服务器和数据库服务器。

4.Internet表示层和业务层驻留在web服务器上，客户端使用Internet浏览器。

●利用数据库API设计应用程序

应用程序利用接口把点击动作转化成T-SQL语句

ODBC只能访问关系型的数据库。

OLEDB可访问关系的和部分非关系的数据库，可调用ODBC。

微软开发的可访问关系型和非关系型数据库。

微软将OLEDB封装成控件ADO作为API。

第2单元安装和配置SQLServer

2．2SQLServer的硬件安装要求

●安装SQLServer的最低要求

处理器Windows2000orNT要求主频166M以上，支持多处理器。

内存Windows2000Server最低256，NT4.0各种服务器64。

数据库规模大，适当增加。

硬盘空间典型安装需250M完全安装270M,客户端工具100M。

●优化SQLServer的硬件配置

处理器，增加数量或更换高速处理器，指定SQL使用使用那几个处理器对称多处理（SMP）。

最多2、4、8、32个。

磁盘子系统，用户应考虑使用硬件或操作系统RAID，能够增加SQL的性能、可靠性。

内存，数据库的虚拟内存存放在Tempdb数据库中，内存不足将会从磁盘读书据，磁盘性能会影响查询性能。

网络，客户端通过网络访问会影响到SQLServer的吞吐量。

网卡，带宽，并发连接。

2．3SQLServer的版本信息

●标准板服务器扩展，大型数据库支持，数据仓库，web站点支持欠缺。

10几万元。

适用于小部门数据库服务器。

●企业版支持最大的Web站点和企业联机事务处理（OLTP）。

32万。

2．4SQLServer的软件安装要求

●授权模式每处理器，允许任意数目的用户访问SQLServer。

适合用户较多的情况。

每客户机，SQL给每个连接用户或应用程序，租给其一个许可证。

●确定服务的安全上下文

默认SQLServer和agent服务将当前用户作为服务启动的账户。

使用域用户账号

1、SQLServer必须访问域中其他计算机上的文件。

2、多服务器作业

3、利用SQLServer以电子邮件方式发送通知

4、单台计算机上安装exchange和SQLServer服务器产品之间进行通信

5、密码用不过期，登陆任何时间

6、将SQLServer安装在目录林中所有用户都能访问的域中

7、不推荐将SQLServer安装在控制器上。

使用本地系统账户

自动启动服务，可以选择默认实例，和命名实例自动启动。

Windows98和Windowsme上和agent服务，以应用程序运行。

●使用SQLServer的命名实例

默认实例以计算机的名称来标识，只允许一个。

可以是SQLServer的任意版本。

命名实例计算机名\实例名，彼此独立运行，最多同时运行16个，必须使用SQLServer2000的客户端组件连接到命名实例，SQLServer早期版本中的数据库引擎不能作为命名实例运行，

实例主要应用于数据库引擎及其支持组件，而不应用于客户端工具。

仅有一个MSSearchService复本管理针对计算机上所有SQLServer实例的全文检索。

●选择安全性机制

选择Windows身份验证和混合式身份验证

身份验证模式可以更改注册表，sa账号最好14位以上，账户默认映射到本地管理员组。

2混合1为Windows

●选择SQL排序的规则

SQL支持的字符集依赖于区域设置中的系统语言设置。

安装SQL是就决定了其支持的字符集。

标准的用于基于文本的计算机数据的编码构架。

Unicode使用2个字节（16位）表示每个字符，这种方法允许分派65,536个可能的唯一的字符。

这样数量的可能的字符数值允许用单个字符集表示几乎世界上所有的可书写语言。

可在微软网站上下载8sys*.sql在查询分析器中运行。

可支持多种语言。

●选择网络库的类型

服务器上的协议应包括客户端的协议。

TCP/IP套接字，默认实例用1433端口，命名实例0—65535

SQL7.0之间的通信需启用多协议加密，SQLServer2000可用SSLKerberos加密。

●安装SQLServer的方法

1.标准安装最小安装（无升级工具，客户端管理工具，联机丛书）典型安装，自定义安装

2.无人职守安装在不同的计算机上执行相同配置的安装

3.远程安装为远程计算机的管理员拥有安装源文件的访问权限

2．6升级SQLServer

●从SQLServer7.0升级到2000

安装命名实例，将保留7.0为默认实例。

●从6.5升级到2000

●从6.0升级到2000不能直接升级

2．7验证安装的正确性

●检阅安装结果

SQLServer服务管理工具数据库文件和文件夹

●启动、停止、暂停SQLServer服务（如果服务账号修改密码，则可能启动失败）

企业管理器SQLServer的服务管理器服务

netstartmssqlserver允许用户连接

netpausemssqlserver事务继续进行不能开始新的进程，不能建立新的连接，数据库维护。

netstopmssqlserver事务前滚或会滚，结束事务。

●建立到SQLServer的连接

查询分析器企业管理器osqlOsql–E以windows身份运行

Select*fromnorthwind..products

Go

Exitorquit

Osql–Usa–P以SQL身份运行，参数区分大小写。

2．8配置SQLServer企业管理器

●注册服务器

●注册信息win2000的注册表维护每个用户的SQL的注册信息。

●注册多个命名实例和服务器，可同时管理多个命名实例。

●创建服务器组可将注册SQLServer分组管理

●访问注册信息，可访问共享注册信息（多用户用相同的身份进行连接和访问SQLServer）和专有注册信息（默认为专有注册信息）。

2．9排除故障

●确定故障源

查阅安装过程中错误消息

查阅Sqlstp.log日志文件记录SQLServer安装过程的活动，c:

\winnt

win2000应用程序日志事件查看器

SQLServer错误日志

SQLServerAgent错误日志

记录启动SQLServer各阶段的信息，c:

\programefile\MicrosoftSQLserver\mssql\log企业管理器或文本编辑器

●测试网络连接

本地用户能够建立连接，网络客户不能。

验证客户端的网络库与服务器网络库相匹配。

能否与服务器建立网络连接。

●查找常见故障的解决方案

SQLServer服务启动失败，不能访问DC来进行身份验证。

登陆失败造成SQLServer启动失败，登陆账号过期，密码更改。

SQLServerAgent服务尚未启动SQLServerAgent服务启动的域用户账号，不具备访问SQLServer的许可权，将于用户加入到本地管理员组，或SQLsysadmin角色，sysadmin被映射到本地管理员组。

不能建立与SQLServe的连接，服务没有启动or网络库or用户没有登陆权。

2．10推荐做法

●只使用专门为数据库使用的些缓存控制器

●使用与组织相一致的排序规则，以便于其他的SQLServer数据库相互兼容。

●使用专用的域用户账号登陆，账号用不过期，任何时间登陆。

●使用混合模式身份验证，为sa指定密码。

●注册所有利用企业管理器进行管理的SQLServer

第3单元管理数据库文件

3．2数据结构简介

●如何存储数据

1.数据库文件所有数据库包括一个主数据文件.mdf，包含该数据库的系统信息和用户数据，扩展数据库，还可能包括多个辅助数据文件.ndf。

逻辑名和物理名

2.日志文件一个或多个日志文件.ldf。

事务日志保存对数据库的更改，可恢复数据库到任意时刻的状态。

日志文件默认为总数据文件大小的1/4。

3.model数据库复制产成新的数据库。

4.数据库中存储数据以页的形式8KB，8192个字节，128页/1MB,便于添加删除数据。

5.行不能跨页以行为单位读取数据。

每行8060字节。

页头占用96个字节。

页尾用来定位一页中的行。

每行5000个字节1000行的数据库占多大空间。

8192X1000/1000/1000=8.192M

6.扩展由8个相邻的页，小表或新表放在混合盘区，统一扩展盘区有一个对象独占。

索引独占扩展。

●事务

1.事务是一组Transact-SQL语句的集合，批处理，不可分，原子性。

2.隐性事务Altertableinsertopencreatedeleterevokeselectgrantupdate

setimplicit_transactionson|off将隐式事务作为显试事务

3.显式事务用户定义明确定义事务的开始和结束，begintransaction和endtransaction

4.解释事务日志，为了维护数据的一致性，便于进行数据库恢复，数据库更改语句发挥作用的同时，各条数据更改语句被记录到事务日志中。

●事务日志工作流程

1.应用程序请求

2.受影响的页面被调入内存（高速缓存）

3.数据库更改语句发挥作用的同时，各条数据更改语句被记录到事务日志中（预写日志）。

4.检查点进程将完成的事务的结果写回磁盘。

保证内存的空间。

5.如果出现故障，将会从最后一次检查点，到出现故障的时间，在此期间那些事物已经提交的事物前滚，未提交的事物回滚。

3．3创建数据库

Ø创建数据库过程中的现象

1.权限，对master数据库的写权限，和数据库文件所在的文件系统的NTFS权限，Saorsysadmin映射到本机管理员组的成员。

在创建好数据库后，向数据库中写数据不再受NTFS限制（可去除NTFS权限实现安全性，因为SQL不启动，数据库文件能够删除，不受保护。

如果SQL启动后找不到日志文件，数据库将处于质疑状态，不可用磁盘限额，日志写不进去，也会出现质疑状态）。

2.创建数据文件和事务日志，指定大小用空白页填充。

Ø数据库创建过程中的选项设定

主文件组包括数据库系统表，主数据文件代表数据库的起点。

指向数据库中的其他文件。

.mdf

辅助文件用来扩展数据库。

可属于主文件组或用户自定