SQLSERVER教程数据库应用部分.docx

SQLSERVER教程数据库应用部分.docx

《SQLSERVER教程数据库应用部分.docx》由会员分享，可在线阅读，更多相关《SQLSERVER教程数据库应用部分.docx（93页珍藏版）》请在冰点文库上搜索。

SQLSERVER教程数据库应用部分

第1章SQLServer初步

§1.1SQLServer的简介

1.1.1什么是SQLServer

SQL：

StructuredQueryLanguage

SQLSERVER是一个以客户／服务器（c／s）模式访问、使用Transact-SQL语言的关系型数据库管理子统（RDBMS）。

SQLServer是一种基于网络的大型数据库软件。

主要是用SQL语言来管理、修改、和修改数据库。

主要运算在WINNT、WIN2000、也可运行在WIN9X上的一种DBMS。

1.1.2SQLServer2000的主要版本与发布时间

1.1.2.1主要版本

有个人（Personal）版、企业版（EnterPrise）、标准版和开发版等。

个人版：

最多连接5个用户，可安装在WINDOWS98、XP、WIN2000。

企业版：

可连接1000个用户，需安装在WIN2000服务版上。

1.1.2.2SQLServer2000的发布时间

1988年MICROSOFT＆SYBASE合作开发

1993年NT3.1平台上的SQLSERVER4．2

1994年中止合作独自开发

1995年SQLSERVER6．0

1996年SQLSERVER6．5

1998年SQLSERVER7．0

2000年SQLSERVER2000

2005年SQLSERVER2005

SQLSERVER2008将于2008年初上市

1.1.3SQLServer的安装

运行安装盘上的AUTORUN.EXE。

1.1.4SQL语言的特点

1、SQL具有自含式与嵌入式两种形式

v交互式SQL：

一般DBMS都提供联机交互工具，用户可直接键入SQL命令对数据库进行操作由DBMS来进行解释

v嵌入式SQL：

能将SQL语句嵌入到高级语言（宿主语言），使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力,一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句

vSQL的语法结构基本一致

2、SQL具有语言简洁、易学易用的特点

3、SQL支持三级模式结构

一个SQL数据库的总体逻辑结构是基本表（Table）的集合，对应于概念模式

SQL数据库的底层存储结构采用文件，一个或几个表对应一个存储文件,以与索引文件，对应模式

用户所见的数据结构是视图（View），用户可直接操作的表，可为视图或部分基本表。

对应外模式

§1.2主要组件

由企业管理器、查询分析器、服务管理器等组成。

1.2.1服务管理器

SQLServer服务管理器（ServiceManager）是在服务器端实际工作时最有用的实用程序。

SQLServer服务管理器的主要作用是用来启动数据库服务器的实时服务、暂停和停止正在运行的服务，或在暂停服务后继续服务。

1.2.2企业管理器

企业管理器（EnterpriseManager：

EM）是SQLServer中一个很重要的插件，它通过GUI（图形用户界面）环境管理SQLServer。

层次：

根目录、服务器组、服务器。

每一个服务器的名称由以下几项组成：

名称、数据库、数据转换服务、管理、复制、安全性、支持服务、MetaDataService等。

1.2.2.1建立服务器组

在企业管理器中，可以连接与管理多个服务器。

服务器组是用来把比较上相似的SQL　SERVER2000服务器组织在一起的一种方式，如市场部中有4个服务器，生产部中有6个服务器，可以将这些服务器都组织在“SQLSERVER组”中，也可以创建两个服务器组，一个叫作“市场”，一个叫做“生产”，这样比较容易区分服务器的作用。

服务器组的建立：

右键单击MicrosoftSQLServers或现有服务器组，从弹出的菜单中选择“新建服务器组”。

服务器组的更名：

右键单击要更名的服务器组。

1.2.2.2建立服务器

首次访问EM时，第一个任务是将服务器注册。

如果在服务器上运行，则本地服务器自动在SQLServer组中注册。

通过EM可以添加新服务器。

1.2.3查询分析器

SQLServer2000的查询分析器（QueryAnalyzer）可以使用户交互式地输入和执行各种Transact-SQL语句，并且迅速地查看这些语句的执行结果，来完成对数据库中的数据的分析和处理。

1.2.3.1启动查询分析器

执行企业管理器的“工具”菜单下的“SQL查询分析器”命令或执行“MicroSoftSQLServer”下的“查询分析器”。

1.2.3.2查询分析器的使用

新建查询文件

保存查询文件

1.2.4使用帮助

运行“联机丛书”组件。

第2章Transact-SQL语言基础

Transact-SQL语言又称T-SQL是MicroSoft开发的一种SQL语言。

§2.1数据类型

2.1.1二进制数据

二进制数据以十六进制形式存储。

二进制数据：

固定长度（binary）的或可变长度（varbinary）的二进制数据类型。

格式：

binary[（n）]固定长度的n个字节二进制数据。

varbinary[（n）]n个字节变长二进制数据

注释：

如果在数据定义或变量声明语句中没有指定n，默认长度为1。

declareb1varbinary

（1）

setb1=0x3456

selectb1

结果为0x34

而：

declareb1varbinary

setb1=0x3456

selectb1

结果也为0x34

2.1.2字符数据

用来表示一串字符。

字符数据型分为char、varchar等类型。

Ø长度不同时可用varchar类型，但任何项的长度都不能超过8KB。

Ø定长使用char类型（最多8KB）。

格式:

char[（n）]定长为n的字符型数据

varchar[（n）]最大长度为n的变长字符型数据

例：

declarechar1char（5）

setchar1='fff'

selectchar1

注意：

如果没有在数据定义或变量声明语句中指定n，则默认长度为1。

2.1.3日期时间数据

日期时间数据类型：

用来表示日期与时间，依时间围与精确程度可分为DATETIME与SMALLDATETIME两种。

例:

’4/01/9812:

15:

00:

00:

00PM’，’1:

28:

29:

15:

01AM8/17/98’。

datetime围为

smalldatetime围为.6.6

该数据类型长度固定。

2.1.4数值型数据

2.1.4.1整型数据

bigint（-2^63-2^63-1）（8B）。

int（-2,147,483,648至2,147,483,647）（4B）。

smallint（-32,768至32,767）（2B）。

tinyint（0–255）（1B）。

定义数据时要注意数据的围。

2.1.4.2实型数据

定点数数据：

使用decimal（numeric）数据类型存储：

-1038+1～1038-1

格式：

decimal[（p[,s]）]其中P为总位数，即精度，S为小数位数

numeric[（p[,s]）]若S缺省，则无小数部分

精度

存储字节数

1-9

5

10-19

9

20-28

13

29-38

17

浮点数数据：

用float型和real型存储，浮点数据只是近似数据，不一定能准确表示数据。

其中：

Float：

-1.79E+308～1.79E+308

Real：

-3.4E+38～3,4E+38

格式：

float（real）[（n）]n为用于存储科学记数法float数尾数的位数

2.1.5货币数据

货币数据类型用于货币值。

分为money和smallmoney类型。

精确度为四位小数。

money：

-922,337,203,685,477.5808～922,337,203,685,477.5807）（8B）。

Smallmoney：

-214,748.3648～214,748.3647（4B）。

2.1.6特殊数据类型

bit数据类型只能包括0或1。

可以用bit数据类型代表TRUE或FALSE、YES或NO。

2.1.7文本和图像数据类型

文本（text）：

用来处理可变长的大文本数据，其中text可存储2GB。

图像（image）：

用来处理图像数据，可存储2GB。

例如，必须将一个有关用户信息的大文本文件（.txt）导入到SQLServer数据库中。

应将这些数据作为一个数据块存储起来，而不是集成到数据表的多个列中。

为了达到这个目的，可以创建一个text数据类型的列。

然而，如果必须存储当前作为标记图象文件格式（TIFF）图像（.tif）存储的、每个大小为10KB的公司徽标，则需创建一个image数据类型的列。

§2.2变量

变量分为局部变量和全局变量，但使用前必须使用DECLARE声明

2.2.1局部变量

Transact-SQL局部变量是可以保存特定类型的单个数据值的对象，还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。

2.2.1.1输出变量的值：

格式:

select变量名

或Print变量名

2.2.1.2声明局部变量

格式：

DECLARE变量名类型[（长度）][,…]

作用：

声明变量并将变量初始化为NULL

2.2.1.3变量赋值

可通过SET语句和SELECT语句对局部变量赋值。

格式：

SET变量名=表达式

SELECT变量名=表达式

2.2.2全局变量

全局变量是由系统提供且预先声明的变量，通过在名称前保留两个（）符号区别于局部变量。

全局变量在整个SQLServer系统使用。

存储的通常是一些SQLServer的配置设定值和统计数据。

在使用全局变量时应该注意以下几点：

●全局变量是在服务器级定义的。

●用户只能使用预先定义的全局变量。

●引用全局变量时，必须以标记符“”开头。

●全局变量对用户来说是只读的。

●局部变量的名称不能与全局变量的名称相同。

如：

SERVERNAME给出服务器名

ROWCOUNT给出最近一个SELECT影响的行数

2.2.3命令的执行与结果显示

执行“查询”菜单下“执行”命令或F5键。

执行“查询”菜单下的“文本显示结果”或“以表格显示结果”命令

§2.3运算符和表达式

2.3.1运算符

2.3.1.1算术运算符

+-*/%（取模，求余）

2.3.1.2比较运算符

=，>，<，>=，<=，<>，!

=（不等于），!

>（不大于），!

<（不小于）

2.3.1.3位运算符

运算符

含义

&（按位AND）

按位AND（两个操作数）

|（按位OR）

按位OR（两个操作数）

^（按位异或XOR）

按位互斥OR（两个操作数

~（按位求反NOT）

返回数字的补数。

2.3.1.4逻辑运算符

运算符

含义

ALL

如果一系列的比较都为TRUE，那么就为TRUE。

AND

如果两个布尔表达式都为TRUE，那么就为TRUE。

ANY

如果一系列的比较中任何一个为TRUE，那么就为TRUE。

BETWEEN

如果操作数在某个围之，那么就为TRUE。

EXISTS

如果子查询包含一些行，那么就为TRUE。

IN

如果操作数等于表达式列表中的一个，那么就为TRUE。

LIKE

如果操作数与一种模式相匹配，那么就为TRUE。

NOT

对任何其它布尔运算符的值取反。

OR

如果两个布尔表达式中的一个为TRUE，那么就为TRUE。

SOME

如果在一系列比较中，有些为TRUE，那么就为TRUE。

2.3.1.5字符串串联运算符

字符串串联运算符允许通过加号“+”进行字符串串联

2.3.2表达式

SQLSERVER中表达式非常丰富，除了以上表达式外，还CASE表达式等，用于复杂情况。

§2.4函数

2.4.1字符串函数

2.4.1.1ASCII

功能：

返回字符表达式最左端字符的ASCII代码值。

格式：

ASCII（character_expression）

2.4.1.2CHAR

功能：

将ASCII值转换为字符函数。

格式：

CHAR（integer_expression）

参数：

integer_expression介于0和255之间的整数。

如果整数表达式不在此围，将返回NULL值。

例：

显示ASCII字符

declareiastinyint

seti=32

whilei<126

begin设置语句块，相当于循环体

printchar（i）

seti=i+1

end

2.4.1.3LEN

功能：

返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾部空格。

格式：

LEN（string_expression）

参数：

string_expression要计算的字符串表达式。

例:

SELECTLEN（'CompanyName'）

2.4.1.4LEFT和RIGHT

功能：

LEFT为左取子串函数

格式：

LEFT（character_expression,integer_expression）

功能：

RIGHT为右取子串函数：

格式：

RIGHT（character_expression,integer_expression）

例：

SELECTLEFT（'ABCDEFG',5）

2.4.1.5LOWER和UPPER

格式：

LOWER（character_expression）

2.4.2日期和时间函数。

2.4.2.1DATEADD

功能：

在向指定日期加上一段时间的基础上，返回新的datetime值。

格式：

DATEADD（datepart,number,date）

日期部分

缩写

Year

yy,yyyy

quarter

q

Month

mm,m

dayofyear

dy,y

Day

dd,d

Week

wk,ww

Hour

hh

minute

mi,n

second

ss,s

millisecond

ms

例:

SELECT“出生日期”＝csrq，”周年”=DATEADD（year,1,csrq）fromstudent

2.4.2.2DATEDIFF

功能：

给出两个日期之间的差值。

格式：

:

DATEDIFF（datepart,startdate,enddate）

例：

检索所有学生的年龄：

SELECTDATEDIFF（YEAR,CSRQ,GETDATE（））ASAGE，CSRQFROMSTUDENT

2.4.2.3DAY、MONTH、YEAR

格式：

DAY（date）

功能：

取指定日期的日

格式：

MONTH（date）

功能：

取指定日期的月

格式：

YEAR（date）

功能：

取指定日期的年

例：

检索所有学生的年龄：

SELECTYEAR（GETDATE（））-YEAR（CSRQ）ASAGEFROMSTUDENT

2.4.2.4GETDATE

格式：

GETDATE（）

功能：

取当前系统日期和时间

2.4.3数值函数

2.4.3.1CEILING

功能：

返回大于或等于所给数字表达式的最小整数。

格式：

CEILING（numeric_expression）

例：

SELECTCEILING（123.45）,CEILING（-123.45）,CEILING（0.0）

2.4.3.2FLOOR

功能：

返回小于或等于所给数字表达式的最大整数。

格式：

FLOOR（numeric_expression）

例：

SELECTFLOOR（123.45）,FLOOR（-123.45）

2.4.3.3POWER

作用:

返回给定表达式乘指定次方的值。

语法:

POWER（numeric_expression,y）

2.4.3.4RAND

返回0到1之间的随机float值。

语法RAND（）

declareaasfloat

seta=rand（）

printa

2.4.3.5EXP

返回所给的float表达式的指数值。

语法：

EXP（float_expression）

2.4.3.6CONVERT函数

功能：

将某种数据类型的表达式显式转换为另一种数据类型。

CAST和CONVERT提供相似的功能。

格式：

CONVERT（data_type,expression）

CAST（expressionASdata_type）

例：

selectconvert（char（6）,567）

2.4.4系统函数

返回数据库和数据库对象的特性信息。

2.4.4.1COL_LENGTH

功能：

返回列的定义长度（以字节为单位）。

格式：

COL_LENGTH（'table','column'）

参数：

'table'：

表名称，Column：

列名称

2.4.4.2DB_NAME

在master数据库中，记录了SQLServer系统的所有系统级别信息。

它记录所有的登录和系统配置设置与所有其它的数据库的信息，其中包括数据库文件的位置。

功能：

返回数据库名。

语法：

DB_NAME（database_id）

参数：

database_id，是应返回数据库的标识号（ID）。

例：

USEmaster

SELECTdbid,DB_NAME（dbid）ASDB_NAME

FROMsysdatabases

2.4.4.3DB_ID

功能：

返回数据库标识（ID）号。

格式：

DB_ID（['database_name']）

参数：

'database_name'，数据库名，如果不填database_name，则返回当前数据库ID。

例：

此例检查在sysdatabases中的每个数据库，使用数据库名来确定数据库ID。

USEmaster

SELECTname,DB_ID（name）ASDB_ID

FROMsysdatabases

2.4.4.4HOST_NAME

功能：

返回工作站名称。

语法：

HOST_NAME（）

返回类型：

nchar

例：

selectHOST_NAME（）

第3章数据库的创建与管理

§3.1数据库概述

数据库是相关的多维数据集与其所共享的对象的容器。

这些对象包括数据源（表）、表、视图、规则、角色、存储过程、自定义函数等。

如果多个多维数据集要共享这些对象，那么这些对象和多维数据集必须在同一个数据库中。

在对象层次结构中，数据库直接从属于分析服务器。

因此，在安装分析服务器后，第一个要创建的对象就是数据库。

数据库可以在"数据库"对话框中进行创建

3.1.1数据库的文件组成

数据库可由三种文件组成，主文件（.MDF）、次要数据文件（.ndf）、日志文件（.LDF），在SQLServer中，数据库必须至少包含一个主文件和一个事务日志文件。

3.1.1.1主要数据文件

该文件包含数据库的启动信息，并用于存储数据。

每个数据库都有一个主文件。

主文件的扩展名为.mdf。

3.1.1.2次要文件

这些文件含有不能置于主要数据文件中的所有数据。

如果主文件可以包含数据库中的所有数据，那么数据库就不需要次要数据文件。

有些数据库可能足够大故需要多个次要数据文件，或使用位于不同磁盘驱动器上的辅助文件将数据扩展到多个磁盘。

Ø次要文件可有多个。

Ø次要文件的扩展名为.ndf

3.1.1.3日志文件

事务日志文件保存用来恢复数据库的日志信息。

每个数据库必须至少有一个事务日志文件（尽管可以有多个）。

事务日志文件最小为512KB。

数据和事务日志信息从不混合在同一文件中，并且每个文件只能由一个数据库使用。

注意：

每个数据库至少有两个文件，一个主文件和一个事务日志文件。

3.1.2文件组

文件组是SQLServer中一个或多个文件的命名集合，它构成或分配用于数据库管理的单个单元。

文件组允许对文件进行分组，以便于管理和数据的分配／放置。

例如，可以分别在三个硬盘驱动器上创建三个文件（Data1.mdf、Data2.ndf和Data3.ndf），并将这三个文件指派到文件组fgroup1中。

然后，可以明确地在文件组fgroup1上创建一个表。

对表中数据的查询将分散到三个磁盘上，在多处理器中，对同一文件组的不同文件，可以采用多线程并行操作，因而性能得以提高。

3.1.2.1文件组的分类

3.1.2.1.1主文件组

此文件组包含主数据文件以与任何其它没有放入其它文件组的文件。

系统表都在主文件组。

3.1.2.1.2用户定义文件组

该文件组是用CREATEDATABASE或ALTERDATABASE语句中的FILEGROUP关键字指定的任何文件组。

3.1.2.1.3默认文件组

默认文件组包含在创建时没有指定文件组的所有表和索引的页。

在每个数据库中，每次只能有一个文件组是默认文件组。

如果没有指定默认文件组，则默认文件组是主文件组。

3.1.2.2文件组说明

Ø最多可以为每个数据库创建256个文件组。

文件组只能包含数据文件。

事务日志文件不能是文件组的一部分。

Ø文件组不能独立于数据库文件创建。

文件组是在数据库中对文件进行分组的一种管理机制。

§3.2数据库的创建与管理

3.2.1数据库创建

3.2.1.1使用创建数据库向导

打开企业管理器后，选中“数据库”后，执行“工具”菜单下的“向导”命令。

3.2.1.2使用企业管理器

打开企业管理器后，右键单击“数据库”，选择“新建数据库”命令。

3.2.1.3使用create命令

格式:

CREATEDATABASEdatabase_name：

数据库文件名

ON [PRIMARY]：

指定数据文件名，包含主文件和次文件

（NAME=logical_file_name,：

指定操作系统文件对应的逻辑文件名

    FILENAME='os_file_name',：

指定操作系统文件名，必须给出路径

SIZE=size,：

指定文件的大小，默认为MB

    MAXSIZE=maxsize,：

指定文件的最大值

    FILEGROWTH=growth_increment）：

指定文件增长的增量

LOGON：

指定日志文件

（NAME=logical_file_name,

    FILENAME='os_file_name',

SIZE=size,

    MAXSIZE=maxsize,

    FILEGROWTH=growth_increment）

例如：

CREATEDATABASEOrders

ONPRIMARY

（NAME=Orders_dat,

FILENAME='c:

\programfiles\microsoftsqlser