数据库原理及应用总结.docx
《数据库原理及应用总结.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用总结.docx（24页珍藏版）》请在冰点文库上搜索。
数据库原理及应用总结
第一部分数据库—SQLServer2005的基本知识
一、SQLServer2005的版本（了解）
1）企业版
它是最全面的SQLServer版本，能够满足最复杂的要求。
2）标准版
适合于中小型企业数据管理和分析平台。
3）工作组版
它可以用做前端WEB服务器，也可以用于部门或分支机构的运营。
4）简易版
它是免费的版本，适用于低端用户、非专业开发人员以及编辑爱好者使用。
5）开发版
开发版功能和企业版完全一样，只是许可方式不同，只能用于开发和测试，不能用于生产服务器。
二、SQLServer两种身份验证模式（掌握）
如果用户要访问SQLServer时，需要经过两个认证过程：
一是身份验证，只验证用户是否有连接到SQLServer数据库服务器的资格；二是权限验证，检验用户是否有对指定数据库的访问权，并且当用户操作数据库中的数据或对象时验证用户是否有相应的操作权限。
1）Windows身份验证模式
在Windows身份验证模式下，用户必须首先登录到Windows中，然后再登录到SQLServer。
而且用户登录到SQLServer时，只需选择Windows身份验证模式，而无需再提供登录帐号和密码，系统会从用户登录到Windows时提供的用户名和密码中查找当前用户的登录信息，以判断该用户是否是SQLServer的合法用户。
注意：
◆如果用户在登录SQLServer时未给出用户登录名，则SQLServer将使用Windows验证模式。
◆如果SQLServer被设置为Windows验证模式，则用户在登录时即使输入一个具体的登录名时，SQLServer也将忽略该登录名。
2）Windows与SQLServer的混合验证模式
◆混合验证模式表示SQLServer接受Windows授权用户和SQLServer授权用户。
在该认证模式下，用户在连接SQLServer时必须提供登录名和密码，然后系统确定用户帐号在Windows操作系统下是否可信，对于可信连接用户，系统直接采用Windows身份验证机制，否则采用SQLServer验证机制。
◆SQLServer验证机制是系统自己执行验证处理，它通过与系统表中信息比较，检查输入的登录帐号是否已存在且密码是否正确。
如果匹配，则表明登录成功，否则身份验证失败，用户将收到错误信息。
注意：
◆Windows操作系统的用户既可以使用Windows认证，也可以使用SQLServer验证。
◆若不是Windows操作系统的用户只能使用SQLServer验证。
三、SQLServer2005登录帐号和用户帐号（了解）
1）登录服务器的登录帐号，也称登录名；登录帐号是指能登录到SQLServer服务器的帐号，属于服务器的层面，本身并不能让用户访问服务器中的数据库。
2）操作数据库的用户帐号。
如果登录服务器的用户要访问数据库时必须拥有用户帐号。
四、SQLServer2005的服务器管理（掌握）
五、SQLServer2005的数据结构（了解）
1）目录结构
2）存储结构
六、SQLServer2005系统数据库功能（了解）
1）master系统数据库
记录SQLServer2005系统的所有系统级的信息
注意：
如果master数据库被破坏或出现故障不可用，则SQLServer无法启动。
2）model模板数据库
向用户提供各种模板
3）msdb系统数据库
是SQLServer代理用来安排警报和作业、记录SQLServer代理程序服务项目和操作员信息等的数据库，有关数据库备份和还原的记录也会写在该数据库中。
4）tempdb系统数据库
保存临时或中间结果的工作空间，包含了所有的暂存数据表与暂存的预存程序。
服务器实例关闭时，将永久删除tempdb数据库中的数据。
每次SQLServer2005启动时都会重建tempdb数据库。
5）resource系统数据库
SQLServer2005中新增加的只读数据库,包含了SQLServer2005中所有系统对象。
七、数据库的附加与分离（了解）
1）图形界面下的
2）命令方式下的
◆调用系统过程sp_detach_db来分离数据库
◆调用系统过程sp_attach_db附加数据库
◆使用CREATEDATABASE附加数据库
八、基本术语（掌握）
1）数据库文件
每个SQLServer2005的数据库至少包含两个文件：
一个数据文件（.mdf）和一个日志文件（.ldf）。
2）数据文件
数据文件包括数据库的数据和对象，如表、视图、索引等。
每个数据库应该包含1个主要数据文件（.mdf），1个事务日志文件（.ldf）以及n个（n>=0）次要数据文件（.ndf）。
3）使用指令创建修改和删除数据库
◆只有数据库管到员或具有CREATEDATABASE权限的数据库所有者才有权执行ATERDATABASE语句。
◆在实际操作中，ALTERDATABASE一次只能修改一种参数。
◆DROPDATABASE{database_name}[,...n]},其中database_name为数据库名。
九、SQLServer2005的数据类型（了解）
1）系统数据类型
◆二进制数据：
Binary、Varbinary和Image
◆Unicode数据：
Nchar，Nvarchar和Ntext
◆日期和时间数据：
Datetime和Smalldatetime
数字数据：
◆精确数字
整数：
bigint、int、smallint、tinyint、bit
小数：
decimal和numeric
货币：
money和smallmoney
◆近似数字
float、real
2）用户自定义的数据类型
十、数据库编程基础（了解）
1）T-SQL概述
（1）特点
◆综合统一
◆高度非过程化，且面向集合
◆不同使用方式的语法结构相同
◆提供了两种使用方式：
交互式和嵌入式
◆容易理解和掌握
（2）分类
T-SQL语言类型
包含的T-SQL语句
数据定义语言
CREATE、ALTER、DROP
数据操作语言
SELECT、INSERT、UPDATE、DELETE
数据控制语言
GRANT、REVOKE
附加语言元素
事务管理语句、流程控制语句、变量、表达式等语言元素
2）Transact-SQL基础
（1）标识符
◆常规标识符
在Transact-SQL语句中使用时不用将其分隔的标识符称为常规标识符。
命名规则：
①标识符长度可以为1～128个字符。
②标识符的首字符必须为Unicode3.2标准所定义的字母、下划线（_）、at符号（＠）、和数字符号（＃）。
③后续字母可以为Unicode3.2标准所定义的字符、0-9数字或下划线（_）、at符号（＠）、数字符号（＃）、美元符号（$）。
④标识符内不能嵌入空格或其他特殊字符。
⑤标识符不能与SQLServer中的保留关键字同名。
例1：
下面SELECT语句中的表标识符StuInfo和列标识符SID均为常规标识符。
SELECT*FROMStuInfo
WHERESID='05000004'
◆分隔标识符
允许在标识符中使用SQLServer2005保留关键字或常规标识符中不允许使用的一些特殊字符，这时该标识符应包含在双引号（“”）或者方括号（[]）内。
命名规则：
符合标识符命名规则的标识符可以分隔，也可以不分隔
例1：
下列语句由于所创建的表名MyTable中包含空格，列名order与T-SQL保留字相同，因此均要用方括号来分隔。
SELECT*
FROM[MyTable]
WHERE[order]=10
（2）变量
◆局部变量
①局部变量的声明
局部变量用DECLARE语句声明，其语法格式如下：
DECLARE@variable_namedatatype[，…n]
②局部变量的赋值
用SET语句给变量赋值
格式：
SET@variable_name=expression
用SELECT语句给变量赋值
格式：
SELECT@variable_name=expression[，…n]
[FROMtable_name
Wherecondition]
③局部变量的输出
用PRINT语句输出变量的语法格式如下：
PRINT@variable_name
用SELECT语句输出变量的语法格式如下：
SELECT@variable_name[,…n]
◆全局变量
全局变量的名字以“@@”开头。
在UPDATA语句中使用@@rowcount变量来检测是否存在发生更改的记录。
USETeachingData
GO
例1：
将选修课程“00000001”的每个学生的成绩增加5分
UPDATEScoreInfo
SETScore=Score+5
WHERECID='00100001'
例2：
如果没有发生记录更新，则发生警告信息
IF@@rowcount=0
Print'警告：
没有发生记录更新！
'/*Print语句将字符串返回给客户端*/
（3）运算符
◆算术运算符
Transact-SQL支持的算术运算符有：
加（+）、减（-）、乘（*）、除（/）、取模（%）。
◆赋值运算符
赋值运算符（=）是将表达式的值赋给一个变量。
它通常用于SET和SELECT语句中。
◆字符串连接运算符
字符串连接运算符（+）用于将字符串或字符型变量串接起来。
◆比较运算符
Transact-SQL支持的比较运算符有：
大于（>）、等于（=）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（<>）、不等于（!
=或<>）、不大于（!
>）、不小于（!
<）。
比较的结果是布尔数据类型，包括TRUE、FALSE和UNKNOWN。
◆逻辑运算符
◆位运算符
位运算符包括：
与（&）、或（|）、异或（^）、求反（～）等逻辑运算。
◆在SQLServer2005中运算符的优先级
（1）（）
（2）+（正）、-（负）、~（按位NOT）
（3）*（乘）、/（除）、%（模）
（4）+（加）、+（连接）、-（减）
（5）=、>、<、>=、<=、<>、!
=、!
>和!
<（比较运算符）
（6）^（位异或）、&（位与）、|（位或）
（7）NOT
（8）AND
（9）OR、ALL、ANY、BETWEEN、IN、LIKE、SOME
（10）=（赋值）
（4）函数
◆内置系统函数
数学函数
字符串函数
数据类型转换函数
日期函数
例1：
在StuInfo表中，查询所有年满19岁的学生学号、姓名、性别、年龄和所在系部。
SELECTSID,Sname,Sex,DATEDIFF（yy,Birthday,GETDATE（））
asSge,Dept
FROMStuInfo
WhereDATEDIFF（yy,Birthday,GETDATE（））>19
◆用户自定义函数
用户自定义函数包括表值函数和标量值函数两类，其中表值函数又包括内联表值函数和多语句表值函数。
①标量函数
例1：
创建一个标量函数，该函数返回两个参数的最大值。
CREATEFUNCTIONMy_Max（@XREAL,@YREAL）
RETURNSREAL）
AS
BEGIN
DECLARE@ZREAL
IF@X>@Y
SET@Z=@X
ELSE
SET@Z=@Y
RETURN（@Z）
END
②内联表值函数
例2：
在TeachingData数据库中创建一个内联表值函数，该函数返回高于指定成绩的查询信息。
CREATEFUNCTIONgreaterScore（@cjFLOAT）
RETURNSTABLE
AS
RETURNSELECT*
FROMScoreInfo
WHEREScore>=@cj
③多语句表值函数
第二部分数据表的管理
一、索引的创建和删除（了解）
1）SQLServer2005中的七种索引
◆聚集索引
◆非聚集索引
◆唯一索引
◆包含性列索引
◆索引视图
◆全文索引
◆XML索引
2）使用对象资源管理器
3）使用命令方式创建
◆建立索引的常用命令格式为：
CREATE[UNIQUE][CLUSTERED]INDEX<索引名>
ON<表名>（<列名>[<次序>][,<列名>[<次序>]]…）；
◆删除索引的常用命令格式为：
DROPINDEX<索引名>ON<表名>
二、表结构的创建（熟练掌握）
1）使用对象资源管理器创建
2）使用命令方式创建
约束：
◆PrimaryKey约束
◆Unique约束
◆Check约束
◆ForeignKey约束
◆Default定义
◆NotNull约束
1、PrimaryKey约束
◆PRIMARYKEY约束是通过定义表的主键来实现实体完整性约束的。
◆为了能唯一的表示表中的数据行，通常将某一列或多列的组合定义为主键。
◆一个表只能有一个主键，而且主键约束中的列不能为空值，且唯一地标识表中的每一行。
◆如果主键不止一列，则一列中的值可以重复，但主键定义的所有列的组合值必须唯一。
2、Unique约束
◆UNIQUE约束确保表中一列或多列的组合值具有唯一性，防止输入重复值，主要用于保证非主键列的实体完整性。
3、Check约束
◆CHECK约束用于限制输入到一个属性或多个属性的值的范围。
◆使用一个逻辑表达式来检查要输入数据的有效性，如果输入内容满足CHECK约束的条件，将数据写入到表中，否则，数据无法输入，从而保证SQLServer数据库中数据的域完整性。
◆一个数据表可以定义多个CHECK约束。
4、ForeignKey约束
◆FOREIGNKEY约束为表中一列或多列的组合定义为外键。
其主要目的建立和加强表与表之间的数据联系，确保数据的参照完整性。
◆在创建和修改表时可通过定义FOREIGNKEY约束来建立外键。
◆外键的取值只能是被参照表中对应字段已经存在的值，或者是Null值。
◆FOREIGNKEY约束只能参照本身所在数据库中的某个表，包括参照自身表，但不能参照其他数据库中的表
5、NotNull约束
◆列的NOTNULL约束定义了表中的数据行的特定列是否可以指定为Null值。
Null值不同于零（0）或长度为零的字符串（‘’）。
◆在一般情况下，如果在插入数据时不输入该属性的值，则表示为Null值。
因此，出现NULL通常表示为未知或未定义。
6、Default约束
◆DEFAULT约束是为属性定义默认值。
若表中的某属性定义了DEFAULT约束，在插入新记录时，如果未指定在该属性的值，则系统将默认值置为该属性的内容。
◆默认值可以包括常量、函数或者NULL值等。
例1：
在teachingDataB中建立StuInfo表（学生信息表）
CREATETABLEStuInfo
（SIDchar（8）primarykey,--将SID设置为主键
Snamechar（10）NOTNULL,--Sname不允许取空值
Sexchar
（2）default'男'check（sex='男'orsex='女'）,
BirthDaysmalldatetimeNULL,
Deptchar（20）NULL,
Majorchar（20）NULL,
Classchar（10）NULL,
Gradechar（10）NULL,
Sageint）
三、表结构的修改（熟练掌握）
1）使用对象资源管理器修改
2）使用命令方式修改
例1：
将表StuInfo中的年龄字段Sage的数据类型由int改为tinyint，设置其默认值为20，并规定年龄必须在10-60之间。
ALTERTABLEStuInfo
ALTERCOLUMNSagetinyint
ALTERTABLEStuInfo
ADDCONSTRAINTDefa_ageDEFAULT20FORSage
ALTERTABLEStuInfo
ADDCONSTRAINTCK_ageCHECK（Sage>10ANDSage<60）
注意：
该命令语句一次只能修改一个参数。
四、表结构的删除（熟练掌握）
1）使用对象资源管理器删除
2）使用命令方式删除
DROPTABLEtable_name
◆注意：
删除字段：
ALTERTABLEtable_name
DROPCOLUMNcolumn_name
例1：
删除表StuInfo中的字段grade：
ALTERTABLEStuInfo
DROPCOLUMNgrade
五、表结构的数据修改（熟练掌握）
1）插入数据
INSERTINTO[（column_list）]VALUES（data_values）
例1：
将一个课程信息元组（课程号：
00000001；课程名：
高等数学；学分：
3；课程类别：
基础课）插入到CourseInfo表中。
INSERTINTOCourseInfo（CID,CName,CCredit,CProperty）
VALUES（'00000001','高等数学',3,'基础课'）;
2）修改数据
UPDATE
SET=[=,…]…
[WHERE]；
例1：
将高等数学的学分改为5分。
UPDATECourseInfo
SETCCredit=5
WHERECName='高等数学'；
3）删除数据
DELETEFROM[WHERE]；
例1：
删除表TchInfo中工号为10040002的教师的记录。
DELETEFROMTchInfo
WHERETID='10040002'；
第三部分数据查询
一、基本的查询语句的使用方法（熟练掌握）
1）基本语句说明：
◆SELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号*，表达式、列表、变量等。
其中*表示全部列。
◆TOPn（percent）：
表示返回最前面的n行（或百分之n行）数据。
◆INTO子句用于指定所要生成的新表的名称。
◆GROUPBY子句是分组查询子句。
◆HAVING子句用于指定分级子句的条件。
◆ASC表示升序排列，DESC表示降序排列。
◆根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。
◆如果有GROUPBY子句，则将查询结果按照<列名>相同的值进行分组。
◆如果GROUPBY子句后有HAVING短语，则只输出满足HAVING条件的元组。
2）基本的SELECT语句举例
例1：
查询数据库teachingdata中stuInfo表所有的学生信息
Useteachingdata
Select*fromstuInfo
例2：
查询表StuInfo中的前5条记录中的学生姓名SNAME和专业Major，要求在显示列标题的时候将SNAME和Major分别显示为姓名和专业。
Useteachingdata
Selecttop5snameas姓名,majoras专业
fromStuInfo
例3：
查询表StuInfo中的前50%条信息。
Useteachingdata
Selecttop50percent*fromStuInfo
例4：
从scoreInfo中查询有哪些课程被选择。
Useteachingdata
Selectdistinctcidfromscoreinfo
二、条件查询的基本操作（熟练掌握）
1）比较查询
常用的比较运算符有：
运算符
=
>
<
>=
<=
<>,!
=
LIKE
含义
等于
大于
小于
大于等于
小于等于
不等于
字符匹配
2）范围查询
◆使用between…and…语句制定内涵范围条件
例1：
查询成绩在70分到80分之间的学生的学号SID和成绩Score。
SELECTSID,ScoreFROMScoreInfo
WHEREScorebetween70AND80
例2：
如果要查询成绩除成绩70分到80分以外的学生的学号SID和成绩Score。
SELECTSID,ScoreFROMScoreInfo
WHEREScorenotbetween70AND80
◆使用in语句指定列表查询条件
例1：
查询tchinfo表中职称为教授、副教授的教师姓名，职称，并显示为姓名，职称。
Useteachingdata
SelectTnameas‘姓名’，titleas‘职称’fromtchinfo
Wheretitlein（‘教授’，‘副教授’）
3）模糊查询
LIKE运算符的一般格式：
<属性名>LIKE<字符串常量>
以上格式中属性名必须为字符型，字符串的字符可以包含如下所示的通配符：
_（下划线）
表示任意单个字符
%
表示任意长度的字符串
[]
与特定范围或特定集中的任意单字符的匹配
[^]
与特定范围或特定集之外的任意单字符的匹配
如：
◆Wherenamelike‘smith_’
可以找到所有6个字母的、以smith开头的名字
◆Wherenamelike‘%smith’
可以找到以smith结尾的名字
◆Wherenamelike‘[JT]im’
可以找到3个字母的、以im结尾并以J或T开始的名字
◆Wherenamelike‘m[^i]%’
可以找到以m开始，第二个字符不为‘i’的所有人的名字
4）空值判断查询条件
例1：
查询所有已经选课但没有考试成绩的学生的学号sid和相应的课程号cid
selectsid,cidfromscoreinfo
wherescoreisnull
注意：
这里的空值条件为ISNULL,不能写成SCORE=NULL
5）使用逻辑运算符查询
三种逻辑运算符的优先级从高到低分别为：
NOTANDOR
例1：
在表ScoreInfo中查询课程号CID为‘00000001’，且成绩低于60分的学生的学号SI,和成绩Score。
SELECTSIDAS学号,ScoreAS成绩
升级会员 