第2章SQLServer操作基础.docx

第2章SQLServer操作基础.docx

《第2章SQLServer操作基础.docx》由会员分享，可在线阅读，更多相关《第2章SQLServer操作基础.docx（19页珍藏版）》请在冰点文库上搜索。

第2章SQLServer操作基础

第2章SQLServer操作基础

●教学目的：

本章主要介绍的是SQLServer2005的发展、安装和启动，数据库的创建与管理、数据表的创建和操作、索引管理。

使学生掌握SQLServer的操作

●教学重点：

1、数据库的创建与管理

2、数据表的创建和操作

3、索引管理

●教学难点：

索引管理

2.1SQLServer的发展、安装和启动

●教学目的：

从学生的实际应用出发，是学生了解SQLServer的发展，掌握SQLServer2005的安装和启动。

●教学重点：

1、SQLServer2005的安装和启动

●教学难点：

1、SQLServer2005的安装

●教学内容：

2.1.1SQLServer的发展

SQLServer是由Microsoft开发和推广的关系数据库管理系统（DBMS）,它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，并于1988年推出了第一个OS/2版本。

后来Aston-Tate公司退出了该产品的开发，而Microsoft公司、Sybase公司则签署了一项共同开发协议，这两家公司的共同开发结果是发布了用于WindowsNT操作系统的SQLServer，1992年，将SQLServer移植到了WindowsNT平台上。

在SQLServer4版本发行以后，Microsoft公司和Sybase公司在SQLServer的开发方面分道扬镳，取消了合同，各自开发自己的SQLServer。

Microsoft公司专注于WindowsNT平台上的SQLServer开发，而Sybase公司则致力于UNIX平台上的SQLServer的开发。

我们常用的是MicrosoftSQLServer以后简称为SQLServer或MSSQLServer。

SQLServer6.0版是第一个完全由Microsoft公司开发的版本。

1996年，Microsoft公司推出了SQLServer6.5版本，接着在1998年又推出了具有巨大变化的7.0版，这一版本在数据存储和数据库引擎方面发生了根本性的变化。

又经过两年的努力开发，Microsoft公司于2000年9月布了SQLServer2000，其中包括企业版、标准版、开发版、个人版四个版本。

2005年，SQLServer2005发布，由于引入了.NETFramework，允许构建.NETSQLServer专有对象，从而使SQLServer具有灵活的功能。

2008年，SQLServer2008发布，SQLServer2008推出了许多新特性和关键改进。

……

1．SQLServer特点：

1．真正的客户机/服务器体系结构。

2．图形化用户界面，使系统管理和数据库管理更加直观、简单。

3．丰富的编程接口工具，为用户进行程序设计提供了更大的选择余地。

4．SQLServer与WindowsNT完全集成，利用了NT的许多功能，如发送和接受消息，管理登录安全性等。

5．具有很好的伸缩性，可跨越从运行Windows95/98的膝上型电脑到运行Windows2000的大型多处理器等多种平台使用。

6．对Web技术的支持，使用户能够很容易地将数据库中的数据发布到Web页面上。

7．SQLServer提供数据仓库功能，这个功能只在Oracle和其他更昂贵的DBMS中才有。

2.1.2关SQLServer2005的安装和配置

1．SQLServer2005版本介绍

表1.1　不同的操作系统支持SQLServer2005各版本运行情况对照表

SQLServer2005版本

操作系统要求

企业版

Windows2000Server和WindowsServer2003

标准版

Windows2000Server、WindowsServer2003、Windows2000Professional和WindowsXPProfessional

工作组版

评估版

Windows2000Server、WindowsServer2003、Windows2000Professional和WindowsXPProfessional

开发版

精装版

Windows2000Server、WindowsServer2003、Windows2000Professional、WindowsXPProfessional和WindowsXPHome

2．软硬件要求

表1.2　安装SQLServer2005或SQLServer客户端组件的硬件要求

硬件

最低要求

计算机处理器

PentiumIII兼容处理器或更高速度的处理器。

处理器速度最低要求为600MHz

内存（RAM）

至少512MB

硬盘空间

完全安装需要800MB，SQLServer联机丛书和示例数据库需要400MB，安装客户端组件需要12MB。

AnalysisServices和数据文件需要35MB，ReportingServices和报表管理器需要40MB，IntegrationServices需要9MB，NotificationServices引擎组件需要5MB。

显示器

分辨率需要设置成1024x768模式

3．SQLServer2005安装（自己安装）

单击【安装SQL2005】按钮开始安装

2.1.3SQLServer2005组件和工具

SQLServer2005的主要组件:

上机学生自己熟悉练习

1、管理工具SQLServerManagementStudio。

启动SQLServerManagementStudio的操作步骤如下：

1）依次选择“开始”→“程序”→“MicrosoftSQLServer2005”→“SQLServerManagementStudio”菜单命令；

2）SQLServerManagementStudio提供“数据库引擎”、“AnalysisServices”、“ReportingServices”、“SQLServerMobile”、“IntegrationServices”5种服务器类型，选择“数据库引擎”服务器类型，选择服务器名称和身份验证方式，单击【连接】按钮。

2、SQLServer配置工具：

3、SQLServer性能工具

1）事件探查器SQLServerProfiler

2）数据库引擎优化顾问

4、开发工具

BusinessIntelligenceDevelopmentStudio

BusinessIntelligenceDevelopmentStudio是一种基于MicrosoftVisualStudio2005的开发环境，使用分析服务（AnalysisServices）、报表服务（ReportingServices）、集成服务（IntegrationService）来开发商业智能解决方案，增强了商业智能应用程序的开发。

集成服务能更容易地集成、转换和分析来自多个异类信息源的数据，比如多个操作系统的数据。

可以对数据仓库提供提取、转换和加载（ETL）处理的包。

分析服务为商业智能应用程序提供了联机分析处理（OLAP）和数据挖掘功能。

可以设计、创建和管理数据的多维结构，设计、创建和可视化数据挖掘模型。

在BusinessIntelligenceDevelopmentStudio中，可以开发分析服务项目、报表服务通过Web服务进行管理，个性化报表

5、系统数据库

安装好SQLServer2005后，系统会自动创建5个系统数据库。

它们存放在MicrosoftSQLServer默认安装目录下的MSSQL\Data文件夹中，数据库文件的扩展名为.mdf，数据库日志文件的扩展名为.ldf。

其中4个在SQLServerManagementStudio环境中可见（master、model、tempdb、msdb）和1个逻辑上不单独存在，隐藏的系统数据库Resource。

（1）Master数据库

Master数据库记录了SQLServer系统的所有信息。

这些信息包括所有的登录信息、系统设置信息、SQLServer的初始化信息和其它系统数据库及用户数据库的相关信息。

Master数据库对应的主数据文件是Master.mdf，日志文件是Mastlog.ldf。

（2）Model数据库

Model数据库是所有用户数据库和Tempdb数据库的模板数据库。

当用户使用CREATEDATABASE命令建立新的数据库时，新数据库的第一部分总是通过复制Model数据库中的内容创建，剩余部分由空页填充。

Model数据库对应的主数据文件是Model.mdf，日志文件是Modellog.ldf。

（3）Msdb数据库

Msdb数据库是代理数据库，主要用于SQLServerAgent,为其警报、任务调度和记录操作员的操作提供存储空间。

Msdb数据库对应的主数据文件是Msdb.mdf，日志文件是Msdblog.ldf。

（4）Tempdb数据库

是一个临时数据库。

Tempdb数据库由整个系统的所有数据库使用。

它为所有的临时表、临时存储过程及其它临时操作提供存储空间。

SQLServer每次启动时，Tempdb数据库被重新建立。

当用户与SQLServer断开联系时，其临时表和存储过程被自动删除。

对应的主数据文件是Tempdb.mdf，日志文件是Templog.ldf。

作业：

课后习题1、2

2.2数据库的创建与管理

●教学目的：

使学生掌握创建、打开、修改及删除数据库的方法。

●

教学重点：

学生掌握创建、修改数据库

●教学难点：

修改数据库

●教学内容：

SQL-Server能够支持多个数据库，在一个服务器上可以创建32767个数据库。

创建数据库的用户拥有该数据库

数据库由表、视图、存储、安全等逻辑对象组成。

每一个对象又有不同的多个逻辑对象组成。

每一个逻辑对象都存储在系统文件或文件组中。

根据这些文件的作用不同，可以分为三类，具体如下。

1）主数据文件：

每个数据库有且仅有一个主数据文件，它包含数据及数据库的启动信息，是数据库和其他数据文件的起点。

主数据文件的扩展名为.mdf。

2）次数据文件：

这些数据文件用于存储不能存在主数据文件中的数据和数据库对象，默认扩展名为.ndf。

一个数据库中可以有多个次数据文件，如果主文件可以包含数据库中的所有数据，那么就可以没有次数据文件。

如果数据库很大，可以设置多个次数据文件，次数据文件可以位于不同磁盘驱动器上。

3）日志文件：

用于存储所有事务对数据库执行修改的记录，利用事务日志备份可以恢复数据库，一个数据库可以有一个或多个日志文件，扩展名为.ldf。

日志文件最小为512KB。

数据和事务日志信息不能存储在同一文件中。

SQL-Server2005文件可以定义初始容量，增长量，最大容量等参数。

每个数据库都有一个PRIMARY文件组。

可以为数据库创建多个数据文件，并将这些数据文件组织成文件组。

2.2.1数据库的创建

SQL-Server的操作有管理器方式和Transact-SQL语句方式，本章只介绍管理器方式，SQL的操作见后续内容。

1、准备创建数据库

1）确定数据库的名称、所有者（创建数据库的用户）数据库名称：

studentcourse

2）确定存储该数据库的数据文件的大小及文件空间增长方式，确定关系、索引，及系统存储参数的配置，确定数据库的存取方法.

主数据文件：

逻辑名称studentcourse；

物理文件名：

C:

\Data\studentcourse.mdf，初始大小：

3MB，最大空间：

不限制，空间增加量：

1MB，属于文件组primary。

次数据文件：

逻辑名称secondsc；物理文件名：

C:

\mydb\secondsc.ndf，初始大小：

3MB，最大空间：

50MB，空间增加量：

1MB，属于文件组group1。

日志文件：

逻辑名称studentcourse_log；

物理文件名：

C:

\Log\studentcourse_log.ldf，始初大小：

1MB，最大空间：

20MB，空间增加量：

10%

2、使用SQLServerManagementStudio创建数据库

2.2.2管理数据库

1、查看数据库信息

（1）使用SQLServerManagementStudio查看数据库信息

右击‘数据库’的属性选项，进行查看。

（2）命令格式:

EXECsp_helpdb[数据库名]

功能:

查看指定数据库的相关数据文件信息、数据库拥有者、创建时间等信息。

若缺省数据库名，则显示所有数据库信息。

（在查询设计器中使用命令）

【例】EXECsp_helpdbstudentcourse

EXECsp_helpdb

2、打开数据库

（1）使用SQLServerManagementStudio打开数据库

在对象资源管理器中单击相应的数据库

（2）命令格式

USE<数据库名>

功能：

使指定数据库成为当前数据库

【例】打开学生选课“studentcourse”数据库。

USEstudentcourse

3、修改数据库

•数据库文件建立后，在使用中会对原来的设置进行修改。

【例】向数据库Studentcourse中添加一个名为group2的文件组，并在该文件组中添加一个名为Studentcourse2、路径为默认的次数据文件，初始值大小为2MB，最大值为50MB，文件以1MB增长；再添加一个名为Studentcourse_Log2的日志文件，初始值大小为1MB，最大值为100MB，文件以10%增长。

然后对数据库中Studentcourse2文件重命名为Studentcourse_2，最后把该文件从数据库中移除。

使用SQLServerManagementStudio

右击Studentcourse的属性，在文件中修改。

4、删除数据库

（1）使用SQLServerManagementStudio

右击数据库进行删除。

（2）命令格式：

DROPDATABASE<数据库名>

5、压缩数据库

【例】压缩学生选课studentcourse数据库，使其最大可用空间为30%。

使用SQLServerManagementStudio压缩数据库

右击数据库-任务-收缩-数据库

【例】压缩学生选课数据库studentcourse中的一个secondsc次数据文件，将其压缩为2MB。

右击数据库-任务-收缩-文件

6、数据库的保存

（1）备份和还原数据库

右击目标数据库-任务-备份

目标添加到U盘路径文件。

还原时，右击数据库-还原数据库-在源设备中加入U盘路径文件。

（2）分离和附加数据库

右击目标数据库-任务-分离

拷贝到U盘路径

附加时右击数据库-附加-加入U盘路径文件。

作业：

1.实训一

2.实训三实验内容1.2

2.3创建数据库表

●教学目的：

要求学生掌握创建、修改、及删除数据表的方法。

●教学重点：

创建、修改、及删除数据表

●教学难点：

创建、修改数据表

●教学内容：

数据库中有一些系统表，如：

对每个数据库对象含有一行记录，对基本表或者视图的每个列和存储过程中的每个参数含有一行记录，它对整个数据库中的每个WindowsNT用户、WindowsNT用户组、SQLServer用户或者SQLServer角色含有一行记录，系统上的每个系统数据库和用户自定义的数据库含有一行记录，对表、视图和存储过程之间的每个依赖关系含有一行记录，对使用CREATETABLE或者ALTERTABLE语句为数据库对象定义的每个完整性约束含有一行记录等等。

每个数据库最多可创建20亿个数据表，最多可以定义1024个字段，每行8092个字节。

2.3.1创建基本表的主要工作

•确定基本表名；

•确定每个字段的名称、数据类型、字段宽度、是否可为空等；

•确定每个表的主键

•确定每个表的外键

•字段的完整性约束

•字段的默认值等

•字段名：

可以1~128个字符，由字母、汉字、数字、下划线组成

系统数据类型：

1.整型数据类型

•整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。

•int（integer）：

int（或integer）数据类型可以存储从-231（-2，147，483，648）到231-1（2，147，483，647）范围之间的所有正负整数。

•Smallint：

可以存储从-215（-32，768）到215-1范围之间的所有正负整数。

•Tinyint：

可以存储从0到255范围之间的所有正整数。

2.浮点数据类型

•浮点数据类型用于存储十进制小数。

浮点数值的数据在SQLServer中采用只入不舍的方式进行存储。

•Real：

可以存储正的或者负的十进制数值，最大可以有7位精确位数。

•Float：

可以精确到第15位小数，其范围从-1.79E-308到1.79E+308。

•Decimal和numeric：

Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。

3.字符数据类型

•字符数据类型可以用来存储各种字母、数字符号和特殊符号。

•Char：

其定义形式为char（n），汉字2个，字目1个存储空间。

•Varchar：

其定义形式为varchar（n）。

用char数据类型可以存储长达255个字符的可变长度字符串。

•Nchar：

其定义形式为nchar（n）,每个字符和汉字占用一个存储空间。

Unicode

•Nvarchar：

其定义形式为nvarchar（n）。

4.日期和时间数据类型

•Datetime：

用于存储日期和时间的结合体。

它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。

•Smalldatetime：

与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年6月6日内的日期。

5.文本和图形数据类型

•Text：

用于存储大量文本数据，其容量理论上为1到231-1（2，147，483，647）个字节，但实际应用时要根据硬盘的存储空间而定。

•Ntext：

与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。

•Image：

用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。

6.货币数据类型

•Money：

用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808到922337213685477.5808，精度为货币单位的万分之一。

•Smallmoney：

与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。

7.位数据类型

Bit：

称为位数据类型，其数据有两种取值：

0和1，长度为1字节。

8.二进制数据类型

•Binary：

其定义形式为binary（n），数据的存储长度是固定的，当输入的二进制数据长度小于n时，余下部分填充0。

•Varbinary：

其定义形式为varbinary（n），数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。

其它含义同binary。

9.特殊数据类型

•Timestamp：

亦称时间戳数据类型，它提供数据库范围内的唯一值，反应数据库中数据修改的相对顺序，相当于一个单调上升的计数器。

•Uniqueidentifier：

也称作唯一标识符数据类型。

Uniqueidentifier用于存储一个16字节长的二进制数据类型，它是SQLServer根据计算机网络适配器地址和CPU时钟产生的全局唯一标识符代码（GloballyUniqueIdentifier，简写为GUID）。

10.新增数据类型

Bigint：

用于存储从-263（-9，223，372，036，854，775，807）到263-1（9，223，372，036，854，775，807）之间的所有正负整数。

•sql_variant：

通配符数据类型，会自动转换为写入的数据的类型。

用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQLServer数据。

•table：

用于存储对表或者视图处理后的结果集。

这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

11.自定义数据类型

数据库-可编程性-类型-用户自定义类型-右击新建用户自定义数据类型。

也可以选中数据类型进行删除。

系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径，其命令形式如下：

EXECsp_addtype<新数据类型>,

[系统数据类型][,null/notnull]

例：

自定义一个地址数据类型

execsp_addtypeaddress,'varchar（80）','notnull'

例：

删除自定义的数据类型

execsp_droptypeaddress

2.4索引管理

●教学目的：

要求学生掌握创建及删除索引的方法。

●教学重点：

创建及删除索引。

●教学难点：

创建索引。

●教学内容：

2.4.1索引概述

数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。

1．索引的作用

1）加速数据检索

2）优化查询

3）强制数据完整性。

2．索引的分类

1）聚集索引

2）非聚集索引

3）唯一索引

聚集索引对表的物理数据页中的数据按列进行排序。

是一个物理顺序，一个表只能有一个聚簇索引。

非聚集索引具有完全独立于数据行的结构，是一个逻辑索引，一个表可以建立多个非聚集索引。

唯一索引要求该数据不能有任两行值完全相同。

2.3.6创建索引

Studentcourse索引情况表

列名

聚集索引

唯一索引

非聚集索引

unique

是否主键

索引名

S.sno

√

√

√

IN_sno

sname

√

IN_sname

C.cno

√

√

√

IN_cno

C.cname

√

√

IN_Cname

SC.sno

√

√

√

IN_sc

SC.cno

SC.grade

√

IN_grade

也可以右击数据表，新建索引。

作业：