计算机软件及应用数据库

4.4.1SQLServer2000数据库管理系统的组成4.4.2创建数据库4.4.3创建表4.4.4编辑表4.4.5数据库备份与恢复4.4.6SQL语言4.4.1SQLServer2000数据库管理系统的组成

SQLServer2000前端工具

查询分析器为数据库应用程序开发人员提供了一个简单易用的SQL交互窗口，用户通过它可以交互地设计、测试、运行TransactSQL语句。

SQLServer企业管理器是SQLServer的一个最重要的工具，它通过图形化的用户界面（GUI）来设置和管理服务器，易于使用。SQLServer支持系统数据库、示例数据库和用户数据库。系统和示例数据库是在安装SQLServer后自动创建的，用户数据库是由系统管理员或授权的用户创建的数据库。一、系统数据库

SQLServer的系统数据库包括以下几个数据库：master：控制用户数据库和SQLServer操作，储存用户 账户、可配置的环境变量、系统错误消息等信息model：新建用户数据库的模板原型tempdb：临时表以及其他临时的工作存储需要msdb：调度信息和作业历史存储区域SQLServer的数据库组成系统级信息

临时数据库，属于全局资源，没有权限限制。

复制、作业调度和管理报警等建立新数据库的模板二、数据库对象

数据库包含的对象主要包括：关系图、表、视图、存储过程、用户、角色等。关系图 强调的是表和表之间的关系表 由行和列构成的集合，用来存储数据。视图 是个虚拟表。常用于集中、简化和定制显示数据库中 的信息。对一个或多个基表中的数据进行筛选、引用。存储过程 存放于服务器的预先编译好的一组T-SQL语句。

创建数据库就是为数据库确定名称、大小、存放位置、文件名的过程。SQLServer2000中创建数据库的方法有：使用企业管理器创建数据库使用Transact-SQL语句创建数据库使用向导创建数据库使用企业管理器创建数据库例：以默认形式创建mydb数据库，步骤如下：

(1)启动企业管理器，连接服务器，展开其树形目录，用鼠标 右键单击【数据库】文件夹，在弹出的快捷菜单中，单 击【新建数据库…】打开图1所的对话框。4.4.2创建数据库(2)在【数据库属性】对话框的【名称】文本框内输入数据库名 (逻辑名)。例如MyDB，系统默认用该数据库名与“_data”串 的连接命名数据文件（见图2），该数据库名与“_log”串的 连接命名日志文件（见图3）。主数据文件(.mdf)：存储数据信息和数据库的启动信息。一个 数据库有且仅有一个主数据文件。日志文件(.ldf)：存储数据库的所有事务日志信息，包含用 于恢复数据库的日志信息，一个数据库的 日志文件至少有一个，也可以有多个。

(3)单击【确定】按钮，数据库就创建好了，在默认位置、采 用默认设置创建了一个名为MyDB的数据库。图1数据库属性对话框图2数据文件选项卡界面

图3事务日志文件选项卡界面4.4.3创建表SQLServer的一个数据库中可以存储20亿个表，一个表最多允许定义1024个列。表的行数和总大小仅受可使用空间的限制。表的每一列必须具有相同的数据类型。一、设计表

在为一个数据库设计表之前，应该完成了需求分析，确定了概念模型，将概念模型转换为关系模型。关系模型中的每一个关系对应数据库中的一个表。创建表的过程,就是将一种具体的关系DBMS（例如SQLServer2000）作为工具，实现关系模型（逻辑模型）到物理模型的转换，即关系模型的物理实现。因此，以下称关系为表，称元组为行（或记录），称属性为列（或字段）。

设计表时需要确定如下内容：1)表中需要的列以及每一列的类型(必要时还要有长度)。2)列是否可以为空。3)是否需要在列上使用约束、默认值和规则。4)需要使用什么样的索引。5)哪些列作为主键。表的设计要体现完整性约束的实现。实体完整性约束的体现是主键约束，即主键的各列不能为空，且主键作为行的惟一标识；外键约束是参照完整性约束的体现；默认值和规则等是用户定义的完整性约束的体现。数据类型描述imageImage用来存储超过8k的可变长度的二进制数据，如bmp、gif、jpeg等图象。char存储固定长度的字符类型的数据，(<=8k)，实际定义varcharvarchar存储可变长度的字符数据，(<=8k)textText用来存储大于8k的ASCII字符。Datetime日期类型，存储从1753年1月1日-9999年12月31日的日期二、字段的数据类型int4字节整型，表达范围是-2147483648-2147483647float浮点数，但在计算机里面的表达是一个近似值decimal浮点数，但表达是一个精确的值money货币类型，8字节bit占用一个二进制的位，经常作为布尔类型使用三、字段的属性字段长度 对于字符类型，如char和varchar，定义字段的同时可以指定字段存放的字符的个数，如定义字段为sNamevarchar(20)

对于数值型的字段，如int,float等，系统自动定义其占用的字节数。decimal由用户自己定义长度和小数位数。小数位数 用于decimal和货币类型的字段自动增量 用于数据库表的记录号的自动增加，常用于做表中记录的主键默认值 当表中新增加一个记录时，又不想具体指定某个字段的 值。可以给它一个默认的值非空限制 用于限制字段的值不能为空。 例如学生的姓名字段，每一个记录都必须有值。有效性规则 字段的有效性规则用于检查字段中的输入值是否符合要求索引 对字段进行索引，可以通过设定该字段为查询条件来加快 数据的检索速度和统计、分组的速度。关键字 关系模式中的主键，用于唯一标识数据库表中的记录。1、设计表结构（1）进入企业管理器（2）展开要操作的数据库（3）单击右键，选择创建表（4）输入字段名，确定每个字段的类型（5）保存，为表起名

注：用鼠标选择某列后，再用鼠标点击工具栏上的钥匙可 将该列设定为主键。2、输入记录（1）右击表，选择“打开表

返回所有行”（2）直接输入数据

四、创建表的过程将编号设为主键包括删除字段、插入字段、移动字段、修改字段名、设置主关键字、设置表间关联等操作。4.4.4编辑表在表设计器中创建关系的步骤:把作为关系外键方的表(引用表)打开表设计器。在表设计器中右击选择“关系”命令并单击"新建"按钮。从“主键表”和“外键表”的下拉列表中，分别选择“被引用表”和“引用表”的名字。在各自下面的网格中输入分配给各表主键的列和外键的列。表设计器会建议一个关系名。若要更改这个名称，请编辑"关系名"文本框的内容。选择"关闭"按钮以创建关系。

4.4.5数据库备份与恢复一、数据库的完整备份步骤：展开服务器组，然后展开服务器。展开“数据库”文件夹，右击要备份的数据库，指向“所有任务”子菜单，然后单击“备份数据库”命令。在“备份”选项下单击“数据库—

完全”。在"目的"选项下,单击"磁带"或"磁盘"，然后指定备份目的地。如果没出现备份目的地，则单击"添加"以添加现有的目的地或创建新目的地。实现将数据库

kfsl复制到磁盘文件D:\studentcourse二、数据库的完整恢复

步骤：展开服务器组，然后展开服务器。展开“数据库”文件夹，单击鼠标右键，指向“所有任务”子菜单，然后单击“还原数据库”命令。在“还原为数据库”后的文本框中，输入要还原的数据库的名字。在“还原”后的一组按钮中单击“从设备”，然后单击“选择设备”。在"还原自"下，单击"磁带"或"磁盘"，然后选择还原设备如果没有出现设备，则单击“添加”以添加现有的备份设备或创建新的备份设备(备份文件)。在"还原数据库"对话框内，单击"查看内容"并选择要还原的备份集。单击“数据库—

完全”还原数据库备份。

在“选项”选项卡中，选中“在现有数据库上强制还原”复选框输入要还原的数据库的名字(1)(2)(3)4.4.6SQL语言SQL语言是数据库的标准语言，是结构化查询语言（StructuredQueryLanguage）的缩写，它包括查询、定义、操纵、控制。使用的方法有两种：用户交互的方式联机使用，称为交互式SQL；另一种是作为子语言嵌入到其他程序设计语言中使用，称为嵌入式SQL。SQL语言支持关系数据库三级模式结构视图对应外模式，基本表对应模式，存储文件对应内模式。基本表是本身独立存在的表，一个关系对应一个表；一个基本表对应一个存储文件，一个表可带多个索引，索引也存放在存储文件中；视图是从一个或几个基本表导出的表，它本身不独立储存在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，是一个虚表。在查询分析器中执行SQL语句的步骤：从开始菜单进入SQLServer的查询分析器选择数据库

(默认的数据库为系统数据库master,可从 下拉列表框里选所需查询的数据库)输入SQL语句按执行按钮执行SQL语句.一、SQL中的数据查询SELECT[ALL|DISTINCT

]<列名1>[,<列名2>……]FROM<表名>[WHERE<条件表达式>][GROUPBY<列名>

[HAVING<条件表达式>

]][ORDERBY<列名>[ASC|DESC]]输出列数据来源

记录筛选条件记录分组的依据记录排序的依据单表查询选择表中的全部列或部分列，对应于投影运算。

a)查询指定列：

select<列名表>from<表名>

如：查询全体学生的学号和姓名

SELECTsNo,sNameFROMStudent;b)查询全部列：

select*from<表名>如：查询全体学生的详细记录

SELECT*FROMStudent;c)查询经过计算的值

select子句中的<列1[，列2]…>也可以是表达式，还可通过指定别名来改变查询结果的列标题如：查询全体学生的姓名和年龄

SELECTsNameNAME,2010-birthdayiAgeFROMStudent;d)消除取值重复的行：两个不同的元组投影到指定的某些列后相同

SELECTDISTINCT<列名1，[]…>FROM<表名>

如：查询选项了课程的学生的学号

SELECTDISTINCTsNo,sNameFROMselectCourse;

表Student表depart例1：查询student表中所有的记录

SELECT*FROMstudent例2：查询student表中所有学生的姓名和奖学金

SELECT姓名,奖学金FROMstudent例3.查询student表中所有专业号（要求重复值只选一个）

SELECT专业号FROMstudent不符合要求正确的命令为：

SELECTDISTINCT

专业号FROMstudent查询满足条件的元组：通过where子句实现

SELECT<列名表>FROM<表名>WHERE<条件表达式>

条件表达式是由常量、变量和函数通过运算符连接起来的式子，其结果为一个布尔型的数据，WHERE子句常用的查询条件有：关系表达式：=,>,<,>=,<=,!=,<>,!>,!<逻辑表达式：NOT,AND,OR,确定范围：字段[NOT]BETWEEN值1AND值2确定集合：字段[NOT]IN(值列表)空值：字段IS[NOT]NULL字符匹配：字段[NOT]LIKE"字符串通配式"关系表达式关系运算符：关系运算符两边值的类型必须一致。>表示大于>=表示大于等于<表示小于<=表示小于等于=表示等于<>表示不等于

例4.查询student表中奖学金大于等于350的学生姓名和奖学金金额SELECT姓名,奖学金FROMstudent

WHERE奖学金>=350

a)比较大小

b)确定范围

c)确定集合

d)字符匹配谓词LIKE可以用来进行字符串的匹配，其语法格式：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]

含义：查找指定的属性列值与<匹配串>相匹配的元组,匹配串可以含有通配符%和_。例5.查询student表中女生信息例6.查询student表中84年8月8日出生的学生信息SELECT*FROMstudentWHERE性别=‘女’

字符型常量要用’括起SELECT*FROMstudentWHERE出生日期=‘1984-8-8’日期型常量要用’括起

逻辑表达式

——多个条件用AND、OR、NOT组合成逻辑表达式逻辑运算符：ANDORNOT

例7.查询student表中01专业的男生和02专业的女生的学号和 姓名

SELECT学号,姓名FROMSTUDENT

WHERE专业号=’01’AND性别=‘男’

OR专业号=’02’AND性别=‘女’字段[NOT]LIKE"字符串通配式“%(百分号)

代表任意长度（长度可以为0）的字符串例：a%b 表示以a开头，以b结尾的任意长度的字符串。如 acb，addgb，ab等都满足该匹配串_(下横线)

代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意 字符串。如acb，afb等都满足该匹配串例8.查询student表中姓陈的学生信息

SELECT*FROMstudentWHERE姓名LIKE‘陈%’

字段[NOT]BETWEEN值1AND值2例9.例如查询student表中奖学金在300至400之间的学生信息

SELECT*FROMstudentWHERE奖学金BETWEEN300AND400等价于：奖学金>=300AND奖学金<=400字段[NOT]IN(值列表)例10.查询student表中专业号为01和03的学生信息

SELECT*FROMstudentWHERE专业号IN(’01’,’03’)等价于：专业号=’01’OR专业号=’03’

字段IS[NOT]NULL例11.查询student表中尚未分配专业的学生信息

SELECT*FROMstudentWHERE专业号ISNULL③使用统计函数查询输出列为：统计函数名(列名)[AS列标题]SQL统计函数：count(列名)：列值个数，不包含为空的值

注：count(*)：元组个数sum(列名)：列值总和avg(列名)：列值平均值max(列名)：列值中的最大值min(列名)：列值中的最小值

例12.统计student表中奖学金的总额、平均值及最高值

SELECTSUM(奖学金)as奖学金总额,AVG(奖学金)as平均奖学金,MAX(奖学金)as最高奖学金

FROMstudent对查询的结果进行排序用ORDERBY子句对查询结果按照一个或多个属性列的升序（）ASC）或降序（DESC）排列，缺省值为升序。

SELECT列名表FROM表名[WHERE条件]

ORDERBY排序依据[ASC/DESC]

例13.查询grade表的学号和成绩，要求按照成绩的升序排名

SELECT学号,成绩FROMgradeORDERBY成绩ASC对查询的结果分组

GROUPBY子句将查询结果表按某一列或多列值分组，值相等的为一组。例14.查询某个课程号相应的选课人数

SELECTcNo,COUNT(sNo)FROMscGROUPBYcNo

解释：该语句对查询结果按cNo的值分组，所有具有相同cNo值的元组为一组，然后对每一组用函数COUNT计算，求得该组学生人数。例：查询选修了3门以上课程的学生学号

SELECTsNoFROMscGROUPBYsNoHAVINGCOUNT(*)>3

解释：HAVING短语作用于组，可指定筛选条件，只输出满足条件的组。注意不同于where子句，where子句作用于基本表。２．连接查询（多表查询）若一个查询同时涉及两个以上的表，称之为连接查询。连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：

[<表名１>.]<列名１><比较运算符>[<表名２>.]<列名２>比较运算符为＝时，称为等值连接；其他运算符称非等值连接。连接谓词中的列名称为连接字段，连接字段必须是可比的。例15查找奖学金在300元以上的学生的姓名、学号、专业名。分析：直接观察表student和depart，可以发现，通过student可以得到学生的学号、姓名，但无法得到学生的专业名，但缺少的信息可以通过表depart得到，表Student和depart的专业号如果统一起来，就可以唯一得到所需要的信息

select学号,姓名,专业名fromstudent,departwherestudent.专业号=depart.专业号and奖学金>300自身连接连接操作是一个表与其自己进行连接，称为表的自身连接。外连接在前面的连接操作中，只有满足条件的元组才能作为结果输出；若以某表为主体，列出所有主键的信息，另一个表的信息可以为空，则使用外连接。

SELECTstudent.sNO,sName,cNoFROMstudent,scWHEREstudent.sNo=sc.sNo(*);

外连接的表示方法为：在连接谓词的某一边加符号*。外连接就好像是为符号*所在边的表增加一个万能的行，该行全部由空值组成。它可以和另一边的表中所有不满足连接条件的元组进行连接。嵌套查询

SQL中一个SELECT语句为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例如：查询与某某在同一个系的学生。

SELECTsNo,sName,sDeptFROMstudentWHEREsDeptIN(SELECTsDeptFROMstudentWHEREsName=‘某某’);二、数据操纵

SQL的数据操纵语句有INSERT、UPDATE、DELETE，完成数据的插入、更新和删除功能。

数据更新语句Update修改表中原有的数据记录与Select语句不同，Update语句不存在多表联合操作，每个语句一次只能针对一个表。其语法格式为：

update表set字段名=表达式[,字段名=表达式,….][where条件]例17：将student表中所有学生的奖学金加100元

例18：将student表中学号为“01001”的学生专业号改为“02”

1、数据更新语句--Updateupdatestudentset奖学金=奖学金+100updatestudent

SET专业号=‘02’

WHERE学号=‘01001’2、插入新记录语句Insert

语法格式为：insertinto表名[(字段名1，字段名2…)]values(值1[,值2,…值n])注：非空的字段及没有字段默认值的字段,必须出现在字段列表中.如果不写字段名列表,则必须按表名定义的字段顺序,在values后定义值的序列.例19：向Student表插入一条记录INSERTINTOStudentVALUES(‘01001’,‘李明’，‘男’，’1984-8-8’,19，‘01’，500）若出生日期和奖学金字段可以为空，则：INSERTINTOStudent(学号,姓名,性别,年龄,专业号)VALUES(‘01001’,‘李明’，‘男’,19，‘01’）3、删除记录delete

语法

deletefrom表名[where条件]例20：删除student表中学号为’01007’的学生记录

DELETEFROMstudentWHERE学号=‘01007’三、定义、修改、删除基本表

SQL语句有CREATETABLE、ALTERTABLE、DROPTABLE

其语法格式为：

CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]]…[,<表级完整性约束条件>]);例17：建立student表

CREATETABLEstudent(sNoCHAR(5)NOTNULLUNIQUE,sNameCHAR(20)UNIQUE,sSexCHAR(1),sAgeint,sDepartCHAR(15));1、定义基本表–

CREATETABLE其语法格式为：

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束条件][DROP[完整性约束名]]…[MODIFY<列名><数据类型>];例：向student表中增加入学时间列，数据类型为日期型

ALTERTABLEstudentADDcomeTimeDATE;2、修改基本表–

ALTERTABLE其语法格式为：

DROPTABLE<