数据库.docx
《数据库.docx》由会员分享，可在线阅读，更多相关《数据库.docx（13页珍藏版）》请在冰豆网上搜索。
数据库
SQLServer实用教程
结课作业
姓名：
班级：
学号：
2013年7月8日
目录
一、数据库系统简介2
二、数据库三级模式结构2
三、在SQLServer2005中有两类数据库3
四、基本知识点总结4
1.SQL基础：
4
2.SQL语句5
3.查询5
4.视图、索引和事务6
5.T—SQL7
6.游标8
7.存储过程9
8.触发器9
9.数据库高级管理10
五、数据库学习心得体会12
一、数据库系统简介
数据库技术是应数据管理任务的需求而产生的。
随着计算机技术的发展，对数据管理技术也不断地提出了更高的要求，其先后经历了人工管理、文件系统、数据库系统3个阶段。
这3个阶段的特点分别如下所述。
数据库系统（DataBaseSystem，DBS）是采用数据库技术的计算机系统，是由数据库（数据）、数据库管理系统（软件）、数据库管理员（人员）、硬件平台（硬件）和软件平台（软件）5部分构成的运行实体。
其中，数据库管理员（DataBaseAdministrator，DBA）是对数据库进行规划、设计、维护和监视等的专业管理人员，在数据库系统中起着非常重要的作用。
数据库具有一个严谨的体系结构，这样可以有效地组织、管理数据，提高数据库的逻辑独立性和物理独立性。
二、数据库三级模式结构
1．模式也称逻辑模式或概念模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
模式实际上是数据库数据在逻辑级上的视图。
一个数据库只有一个模式。
定义模式时不仅要定义数据的逻辑结构，而且要定义数据之间的联系，定义与数据有关的安全性、完整性要求。
2．外模式也称用户模式，它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
外模式通常是模式的子集。
一个数据库可以有多个外模式。
应用程序都是和外模式打交道的。
外模式是保证数据库安全性的一个有力措施。
每个用户只能看见和访问所对应的外模式中的数据，数据库中的其余数据对他们是不可见的。
3．内模式也称存储模式，一个数据库只有一个内模式。
它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。
例如，记录的存储方式是顺序结构存储还是树结构存储；索引按什么方式组织；数据是否压缩，是否加密；数据的存储记录结构有何规定等。
三、在SQLServer2005中有两类数据库
系统数据库与用户数据库：
系统数据库存储有关SQLServer的系统信息，它们是SQLServer2005管理数据库的依据。
如果系统数据库遭到破坏，SQLServer将不能正常启动。
在安装SQLServer2005时，系统将创建4个可见的系统数据库：
master、model、msdb和tempdb。
（1）master数据库包含了SQLServer诸如登录账号、系统配置、数据库位置及数据库错误信息等，用于控制用户数据库和SQLServer的运行。
（2）model数据库为新创建的数据库提供模板。
Model数据库是作为在系统上创建数据库的模板。
当系统收到“CreateDATABASE”命令时，新创建的数据库的第一部分内容从Model数据库复制过来，剩余部分由空页填充，所以SQLServer数据中必须有Mode数据库。
（3）。
Msdb数据库供SQLServer代理程序调度警报和作业以及记录操作员时使用。
比如，我们备份了一个数据库，会在表backupfile中插入一条记录，以记录相关的备份信息。
（4）tempdb数据库为临时表和临时存储过程提供存储空间，所有与系统连接的用户的临时表和临时存储过程都存储于该数据库中。
•
•
四、基本知识点总结
1.SQL基础：
SQLServer2000安装、配置，服务器启动、停止，企业管理器、查询分析器
第一代数据库--网状数据库和层次数据库；第二代数据库--关系数据库
数据库（DB）；数据库管理系统（DBMS）；数据库系统（DBS）
SQLServer2000提供了不同版本：
企业版、标准版、个人版、开发版
SQLServer中的数据类型：
整数：
int,smallint,tinyint,bigint;浮点数：
real,float,decimal;二进制：
binary,varbinary;逻辑：
bit；字符：
char,nchar,varchar,nvarchar；文本和图形：
text,ntext,image；日期和时间：
datetime,smalldatetime；货币：
money,smallmoney
数据库的创建和删除；数据库表的创建、修改和删除
数据完整性：
实体完整性：
PrimaryKey,UniqueKey,UniqueIndex,IdentityColumn；域完整性：
Default,Check,ForeignKey,Datatype，Rule；参照完整性：
ForeignKey,Check,Triggers,Procedure；用户定义完整性：
Rule,Triggers,Procedure;CreateTable中得全部列级和表级约束
SQLServer中有5种约束：
主键约束（PrimaryKeyConstraint）、默认约束（DefaultConstraint）、检查约束（CheckConstraint）、唯一性约束（UniqueConstraint）、外键约束（ForeignKeyConstraint）.
关系图
数据库设计的步骤：
需求分析、概念结构设计、逻辑结构设计、数据库物理设计、数据库实施、数据库运行和维护
两个实体之间的联系：
一对一（1:
1）、一对多（1：
n）、多对多（m：
n）
实体关系模型--E-R图
数据库规范化：
将数据库的结构精简为最简单的形式；从表中删除冗余列；标识所有依赖于其他数据库的数据。
数据库三范式：
第一范式就是无重复的列；第二范式就是非主属性非部分依赖于主关键字；第三范式就是属性不依赖于其他非主属性
2.SQL语句
SQL全称是“结构化查询语言（StructuredQueryLanguage）”
SQL的4个部分：
数据定义语言DDL（DataDefinitionLanguage）用来定义数据的结构：
create、alter、drop。
数据控制语言DCL（DataControlLanguage）用来控制数据库组件的存取许可、存取权限等得命令：
grant、revoke。
数据操纵语言DML（DataManipulationLanguage）用来操纵数据库中得数据的命令：
insert、update、delete。
数据查询语言DQL（DataQueryLanguage）用来查询数据库中得数据的命令：
select。
SQL中得运算符：
算术运算符、位运算符、比较运算符、逻辑运算符、通配运算符、字符串连接符、赋值运算符
3.查询
简单查询,使用TOP子句
查询结果排序orderby
带条件的查询where,使用算术表达式，使用逻辑表达式，使用between关键字，使用in关键字，
模糊查询like
在查询中使用聚合函数：
sum（x）,avg（x）,min（x）,max（x）,count（x）,count（*）
使用分组查询groupby，having子句
distinct关键字
列别名
selecttop6*fromsalesorderbyqtydesc
selectau_id,au_fname,au_lnamefromauthorswherestatein（'ks','ca','mi'）
selectau_fname,au_lname,phonefromauthorswhereau_idlike'72[234]-%'
selecttype,sum（price）,avg（price）,count（*）fromtitlesgroupbytypehavingtypein（'business','psycheology'）
简单子查询：
嵌套子查询、相关子查询；子查询的select语句中不能使用orderby子句，roderby子句只能对最终查询结果排序。
嵌套子查询：
执行过程，先执行子查询，子查询得到的结果不被显示，而是传给外层查询，作为外层查询的条件，然后执行外层查询，并显示结果。
嵌套子查询的执行不依赖于外层查询，子查询只执行一次。
带有比较运算符的子查询，带有in和notin的子查询，带有any或all的子查询
相关子查询：
子查询为外层查询的每一行执行一次，外层查询将子查询引用的列的值传给了子查询。
相关子查询的执行依赖于外层查询，子查询需要重复的执行。
带有exists和notexists的相关子查询。
多表联接查询：
内联接（innerjoin）、外联接（（left、right、full）outerjoin）、自联接（selfjoin）和交叉联接（crossjoin）
在查询上创建新表：
selectinto语句首先创建一个新表，然后用查询的结果填充新表。
表别名
selectcoursenamefromcoursewherecourseidin（selectdistinctcourseidfromgradewheregrade>10）
selectstudnamefromstudentwheresudbirthday>any（selectstudbirthdayfromstudentwhereclass='信息系'）andclass<>'信息系'
selectstudnamefromstudentwhereexists（select*fromgradewherestudid=student.studidandcourseid='01'）
selectstud1.*fromstudentasstud1joinstudentasstud2onstud2.studname='mm'andstud1.studsex=stud2.studsex
select*intogirlsfromstudentwherestudsex='m'
4.视图、索引和事务
视图是由一个或多个数据表（基本表）导出的虚拟表或者查询表，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。
视图的好处：
能够简化用户的操作；视图能够对机密数据提供安全保护。
创建视图时，视图的名称存在sysobjects表中。
有关视图中所定义列的信息添加到syscolumns表中，而有关视图相关性的信息添加到snds表中。
另外，createview语句的文本添加到syscomments表中。
在通过视图向表中插入数据时，如果insert语句列表中包含有视图中没有选择的列和不允许为空值的列，这种操作是不允许的。
创建视图：
createviewview_employeeasselectemp_id,fname,lnamefromemployee
使用视图：
select*fromview_employee
修改视图：
alterviewview_employeeasselectemp_id,fname,job_idfromemployeewherejob_id>10
删除视图：
dropveiwview_employee
查看视图结构：
execsp_helpview_employee
查看视图定义信息：
execsp_helptext'view_employee'
索引提供了一种基于一列或多列的值对表的数据行进行快速访问的方法。
索引提供的是表中得逻辑顺序。
聚集索引基于数据行的键值在表内排序和存储这些数据行。
当数据表以某列为关键字建立聚集索引时，表中得数据行就以该列（聚集索引键）的排序次序进行存储。
每个表只能有一个聚集索引。
非聚集索引具有完全独立于数据行的结构，一个表可以建立多个非聚集索引。
创建聚集索引：
createclusteredindexstudid_indonstud（studid）
创建非聚集索引：
createuniqueindexstudfullname_indonstud（fnamedesc,lname）
删除索引：
dropindexstud.studid_ind
查看stud表上得索引：
execsp_helpindexstud
事务是一种机制，是一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤销操作请求。
事务的特性：
原子性（Atomicity）、一致性（Consistenty）、隔离性（Isolation）、永久性（Durability）。
事务分类：
显示事务、隐性事务、自动提交事务。
视图、索引和事务的创建、使用、修改和删除
5.T—SQL
全局变量：
由系统定义和维护，其名称以@@字符开头
局部变量：
由用户定义和赋值，其名称以@字符开头
输出语句：
print
逻辑控制语句：
begin...end;break;case;continue;goto;if...else;return;while
常用函数：
行集函数，聚合函数，标量函数
转换函数：
convert（dt,e,s）,cast（）
数学函数：
绝对值abs（n）,向上取整ceiling（n）,向下取整floor（n）,指定次幂power（n,y）,四舍五入round（n,length）,求符号sign（n）,平方根sqrt（n）
日期和时间函数：
dateadd（datepart,num,date）,datediff（datepart,date1,date2）,datename（datepart,date）,datepart（datepart,date）,getdate（）,year（date）,month（date）,day（date）
字符串函数：
lower（e）,upper（e）,left（e,i）,right（e,i）,replace（s1,s2,s3）用3替换1中的2，replicate（e,i）重复指定次数，stuff（s1,start,length,s2）用2替换1中指定位置，substring（expression,start,length）
元数据函数：
db_id（'database_name'）,db_name（datebase_id）,object_id（'obj_name'）,object_name（obj_id）,col_length（'table','column'）,col_name（table_id,col_id）
聚合函数：
avg（expr）,count（expr）,count（*）,max（expr）,min（expr）,sum（expr）
selectau_lname,au_fname,contory=
casestate
when'ut'then'utah'
when'ca'then'california'
else'world'
end,cityfromauthorsorderbystatedesc
while（selectavg（price）fromtitles）<30
begin
updatetitlessetprice=price*2
if（selectmax（price）fromtitles）>50break
elsecontinue
end
print'价格太高'
begin
insertintojobsvalues（'a',80,234）
if@@error<>0print'数据插入失败'
elsegotoM
end
M:
print'数据插入成功'
6.游标
游标是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。
将批操作变成行操作，对结果集中得某行进行操作。
declareauthor_csrcursorread_onlyfor--定义只读游标
selectau_fname,au_lnamefromauthorswherestate='ca'orderbyau_fname,au_lname
declare@lnamevarchar（20）,@fnamevarchar（20）--定义变量
openauthor_csr--打开游标
fetchnextfromauthor_csrinto@lname,@fname--执行一次数据读取操作
while@@fetch_status=0--循环游标读取数据
begin
print'authorname:
'+@lname+''+@fname
fetchnextfromauthor_csrinto@lname,@fname
end
closeauthor_csr--关闭游标
deallocateauthor_csr--释放游标
7.存储过程
存储过程（storedprocedure）类似c语言中的函数，是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。
用户通过指定存储过程的名字饼给出参数来执行它。
常用的系统存储过程：
sp_database,sp_helpdb,sp_renamedb,sp_tables,sp_column,sp_help,sp_helpconstraint,sp_helpindex,sp_stored_procedure,sp_password
创建存储过程：
createprocedurebook_num（@book_namevarchar（26）,@starttimedatetime,@endtimedatetime,@totalintoutput）
as
select@total=count（jy.askbookid）frombook,jylsjywherebooknamelike@book_nameandbook.isbn=jy.isbnandjy.starttime>=@starttimeandendtime<=@endtime
使用存储过程：
declare@book_namechar（26）,@totalint
set@book_name='面向对象分析和设计'
execbook_num@book_name,'2007-01-01','2007-11-01',@totaloutput
select@book_nameasbookname,@totalasnum
8.触发器
触发器是一种特殊类型的存储过程，主要是通过实践进行触发而被执行。
触发器的主要作用就是能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。
其他功能：
强化约束，跟踪变化，级联运行，存储过程调用。
SQLServer2000支持两种类型触发器：
after触发器：
要求只有执行某一操作之后，触发器才被执行，且只能在表上定义。
insteadof触发器：
表示并不执行其所定义的操作，而仅是执行触发器本身。
既可以在表上定义，也可以在视图上定义，但对同一操作只能定义一个insteadof触发器。
工作原理：
当触发insert触发器时，新的数据行就会被插入到触发器表和inserted表中。
触发器通过检查inserted表来确定是否执行触发器动作或如何执行。
当在定义有触发器的表上执行update语句时，原始行被移入到deleted表，更新行被移入inserted表。
触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。
当触发deleted触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。
createtriggerupdate_smoke_t_saleonsmoke_t_saleforupdate
as
declare@newsalenumint,@smokeproductnamevarchar（40）
select@newsalenum=salenumfrominserted
select@smokeproductname=smokeproductnamefrominserted
ifupdate（salenum）--判断是否更新
begin
updatesmoke_t_salesetsaletotalprice=@newsalenum*salepricewheresmokeproductname=@smokeproductname
insertintosmoke_log（logContent）values（'更新成功'）
end
else
print'未更新'
9.数据库高级管理
SQLServer安全体系结构，4个等级：
客户机操作系统的安全性，SQLServer的登录安全性，数据库的使用安全性，数据对象的使用安全性
SQLServer验证模式：
windows身份验证模式和混合模式（windows身份验证和SQLServer身份验证）
登录账户：
用户登录（连接）SQLServer服务器的账户和密码。
角色管理：
服务器角色（负责管理和维护SQLServer的组）；数据库角色（是对某个数据库具有相同访问权限的用户账户和组的集合）
数据库用户：
对于每个要求访问数据库的登录账户，必须在要访问的数据库中建立该数据库的访问账户，且与其登录账户链接关联，才可进入该数据库访问。
权限管理：
是指用户是否能进行访问数据库资源的相应操作。
权限包括：
语句权限、对象权限和暗示权限。
授予权限：
grant语句[...]to安全账户[...]
grant权限[...]on表或视图[（列[,...]）]|on存储过程|on用户自定义函数to安全账户[，...]
拒绝权限：
deny语句[...]to安全账户[...]
deny权限[...]on表或视图[（列[,...]）]|on存储过程|on用户自定义函数to安全账户[，...]
撤销权限：
revoke语句[...]from安全账户[...]
revoke权限[...]on表或视图[（列[,...]）]|on存储过程|on用户自定义函数from安全账户[，...]
备份和恢复：
数据库备份设备,在进行数据库备份之前，首先要创建备份设备。
包括：
磁盘、磁带和命名管道
SQLServer备份策略：
只备份数据库、备份数据库和事务日志、差异备份。
backupdatabasemedicaldbtodisk='medical_bk1'withname='medicaldbbackup'description='medicaldbfullbackup'init
restoredatabasemedicaldbfr
升级会员 