SQL server课程总结讲解及习题.docx
《SQL server课程总结讲解及习题.docx》由会员分享，可在线阅读，更多相关《SQL server课程总结讲解及习题.docx（37页珍藏版）》请在冰豆网上搜索。
SQLserver课程总结讲解及习题
数据库原理课程总结及复习题
第十五小组
组长：
张星宇
组员荆典尹歌
2011年6月6日
目录
SQLSever2005概述3
总结大纲4
知识点总结5
1基础知识5
1.1SQLServer2005的特点5
1.2SQLServer2005的工具和使用程序5
1.3SQLServer2005的服务器管理5
1.4SQLServerManagementStudio界面6
1.5SQLServer2005数据库的结构6
1.6SQLServer2005系统数据库6
1.7数据库的分离与附加6
1.8数据的导入与导出6
2数据库的创建与修改7
2.1预备知识7
2.2数据库的安全性机制7
2.3SQLSever登陆账号的管理7
2.4角色管理（具有相同权限的一组用户称为角色）8
2.5用户数据库的创建和修改9
3数据查询（重点）9
3.1基本的SELECT查询9
3.2条件查询9
3.3排序查询10
3.4分组筛选查询10
3.5联结查询11
3.6嵌套查询11
4数据表与视图11
4.1预备知识：
系统数据类型11
4.2视图的基本概念12
4.3表结构的创建与修改12
4.4视图的创建与修改与使用15
4.5数据库的权限管理16
3-7章复习题18
答案21
会议记录25
任务分工26
SQLSever2005概述
数据库技术是当今世界高新技术潮流中的重要技术之一，也是计算机科学和信息管理应用领域的主要研究对象之一。
从它的纵深知识内容来看，它蕴含了较深的数学概念和较多的模型定义及现代技术，从它的横向发展来看，它涉及了各个领域，诸如军事、商业、通信、娱乐等各个领域。
数据库（Database）是长期存储在计算机内、有组织的、可共享的数据集合。
数据库管理系统（DatabaseManagementSystem,DBMS）是位于用户与操作系统之间的数据管理软件，为用户或应用程序提供访问数据库的方法，可以让用户很方便地对数据库进行维护、排序、检索和统计等操作。
数据库系统（DatabaseSystem,DBS）是实现有组织地、动态地存储大量关联数据，方便多用户访问的计算机软件、硬件和数据资源组成的系统，即采用了数据库技术的计算机系统。
从狭义上来讲，数据库系统主要是指数据库、数据库管理系统和用户。
从广义上来讲，它不仅包括数据库、数据库管理系统和用户，还包括计算机硬件、操作系统和维护人员。
其中，数据库管理系统是数据库系统的核心和主体，它保证了数据库的独立性和共享性。
数据库中的数据不是杂乱无章地堆积在一起的，而是按照一定的数据模型组织、描述和存储的。
数据库中的数据相互关联，它可以为多个用户、多个程序所共享，具有较小冗余度，数据间联系密切，而又有较高的数据独立性。
数据库技术要解决的主要问题就是如何科学的组织和存储数据，如何高效地获取、更新和加工处理数据，并保证数据的安全性、可靠性和共享性。
总结大纲
基础知识
SQLServer2005的基础知识包括SQLServer2005的版本区分以及两种身份验证模式、软件的新特性、界面组成、服务器管理、工具和实用程序等内容。
此外，在基础知识中，还从宏观上介绍了系统数据库的组成与作用，细致介绍了数据库的存储结构、了解数据库的分离与附加原理、数据的导入导出上。
数据库的创建与修改
本章宏观介绍了数据的安全性机制、管理权限与角色管理，从原理上理解数据的安全性管理。
使我们对数据有更深入的了解。
数据查询
SQLServer2005数据查询，需要我们了解查询的含义，熟练掌握各种查询语句（如何查询数据表中的数据、如何按自己设置的条件来查询数据表中的数据、如何将查询到的数据表中的数据按升序或者降序排列、如何查询多个表中的数据）、逻辑运算符并会灵活运用。
还包括了安全性的登录账号管理内容。
数据表与视图
在本部分中，我们涉及到了对于软件的基本操作，
首先，在预备知识部门介绍了系统数据类型，而后具体介绍了表结构的创建与修改与表数据的操作，以便于制作出理想的表格。
然后介绍了一个新的概念——视图，并对其概念与作用，创建、使用、修改进行了讲解，并配有相应例题。
接下来，重点介绍了索引，了解索引的定义、认识索引的原理、掌握索引的实现。
最后学习数据的完整性的四个方面表现及如何实现数据的完整性。
其中将约束这一途径作为具体讲解，介绍了约束的分类及各种约束的实现方法。
知识点总结
1基础知识
1.1SQLServer2005的特点
1、SQLServer2005的版本
（1）企业版（最全面）
（2）标准版
（3）工作组版
（4）简易版（免费）
（5）开发版
2、SQLServer2005的新特性
（1）更多组件
（2）统一的用户界面（managementstudio）
（3）与VisualS语言相结合
（4）商业智能
（5）安全性增强
（6）XML技术
（7）WebServices
1.2SQLServer2005的工具和使用程序
（1）分析服务（AnalysisServices）
（2）配置工具
（3）文档和教程
（4）性能工具
（5）BusinessIntelligenceDevelopmentStudio
（6）SQLServerManagementStudio
1.3SQLServer2005的服务器管理
（1）数据库引擎
（2）分析服务
（3）全文搜索
（4）代理服务
（5）集成服务
1.4SQLServerManagementStudio界面
（1）“已注册服务器”窗口
（2）“对象资源管理器”窗口
（3）“文档”窗口
（4）“工具栏”主要按钮
1.5SQLServer2005数据库的结构
1目录结构（在安装后于默认路径C:
\ProgramFiles\MicrosoftSQLServer可以找到）
（1）80文件夹
（2）90文件夹
（3）MSSQL.n文件夹
2存储结构
（1）逻辑存储结构：
表、视图、存储过程等
（2）物理存储结构：
文件由称为“页”的单元组成
1.6SQLServer2005系统数据库
（1）master系统数据库：
含有系统级信息
（2）model系统数据库：
向用户提供各种模板
（3）msdb系统数据库：
安排警报和作业、记录SQLServer代理程序服务项目和操作员信息
（4）tempdb系统数据库：
保存临时或中间结果的工作空间
（5）resource系统数据库：
包含了SQLServer2005中所有系统对象
1.7数据库的分离与附加
1数据库的分离
（1）用【对象资源管理器】分离数据库
（2）用命令语句分离数据库：
sp_deteac_db语句
2数据库的附加
（1）用【对象资源管理器】附加数据库
（2）用命令语句附加数据库：
FORATTACH语句
1.8数据的导入与导出
使用对象资源管理器导入导出
●同版本或旧版本的SQLServer
●Excel、Access、纯文本文件
●通过OLEDB或ODBC来访问的数据源等
2数据库的创建与修改
2.1预备知识
（1）数据库的命名规则
（2）数据库的上限量：
32767
（3）数据库的文件
Ø主数据文件（.mdf）
Ø次数据文件（.ndf）
Ø事务日志文件（.ldf）
（4）数据库的文件组
（5）数据库的状态
2.2数据库的安全性机制
（1）保护数据库防止非法使用造成数据泄露、更改或破坏。
（2）数据库的安全性与计算机系统的安全性是紧密相连、相互支持的。
（3）①用户身份鉴定只有合法的用户才准许进入系统；
②DBMS要控制合法用户执行合法的操作；
③操作系统的保护措施；
④数据以密码形式存储到数据库中。
注：
SQLServer2005身份验证模式
◆Windows身份验证模式（默认模式，高权限，常供系统管理员使用）
◆SQL Server 身份验证（链接时需验证身份）
（4）数据安全性的核心问题是DBMS的存取控制机制。
2.3
SQLSever登陆账号的管理
（1）在SQLServer2005中有两种账号：
◆登录名：
登录服务器的账号，可登录服务器；
分为Windows域登录名和SQLServer登录名。
◆用户帐号：
可操作数据库的授权。
每一个用户帐号必隶属于某指定登录名。
（2）创建登陆账号
CREATELOGIN语句实现
eg1:
创建SQLServer登录名stu_admin1,密码111111
createloginstu_admin1
withpassword='111111',default_database=master
eg2:
创建windows域登录名
先利用控制面板创建一个用户账户TAN.
createlogin[318128E5963D486\TAN]fromwindows/*318128E5963D486为域/
（3）创建用户账号
CREATEUSER语句实现
Eg:
创建登录名stu_admin1下的用户
createuserU1_stu_admin1forloginstu_admin1
（4）管理登陆账号和用户账号
Ⅰ、关于登陆账号
①查看服务器的登陆账号
Sp_helplogins[‘login_name’]
②修改服务器的登陆账号
ALTERLOGINlogin_name
③删除服务器的登陆账号
DROPLOGINlogin_name
Ⅱ、关于用户账号
①查看数据库的用户账户
Sp_helpuser[security_account]
②修改数据库的用户账号
ALTERUSERuser_name
③删除数据库的用户账号
DROPUSERuser_name
2.4角色管理（具有相同权限的一组用户称为角色）
1、系统预定义角色
①固定服务器角色
Ⅰ固定服务器角色及权限
负责管理和维护SQLServer的组，在安装时自动创建。
Ⅱ利用T-SQL语句为登陆账号指定和删除服务器角色
登陆：
Sp_addsrvrolemember{login_name},role_name
删除：
Sp_dropsrvrolemember{‘login_name’},’role_name’
②固定数据库角色
Ⅰ固定数据库角色及权限
一些预定义的数据库用户组，在创建数据库时自动添加。
其中，public数据库角色特点：
◇每个数据库用户都自动归属为该角色成员。
◇用户可以对该角色进行授权，而其他固定数据库角色的权限是不可改变的。
◇将某权限授予public，则数据库所有用户便可具有该权限。
Ⅱ利用T-SQL语句为数据库角色添加用户账号
Sp_addrolemember‘role’,‘security_account’
2、用户自定义角色
①创建用户自定义角色
Sp_addrole’role’,[‘owner’]
②为用户定义的角色授权
例如：
为数据库角色Teacher授予查询TchInfo的权限
USETeachingData
GO
GRANTSELECTONTchInfo
TOTeacher
③为用户定义角色添加成员
例如：
为数据库角色Teacher加入用户U2_stu_admin1
USETeachingData
GO
Sp_addrolememberTeacher,U2_stu_admin1
2.5用户数据库的创建和修改
1．创建
（1）用“对象资源管理器”创建数据库
（2）用T-SQL命令语句创建数据库：
CREATEDATABASENAME
2.修改
（1）ALTERDATABASE：
修改数据库结构
（2）SP_rename：
修改数据库名称
例：
将数据库名sales修改为mysales。
SP_rename‘sales’,‘mysales’,’DATABASE’
（3）DROPDATABASE：
删除数据库
3数据查询（重点）
3.1基本的SELECT查询
1基本语法
（1）SELECTFROM
（2）ALL（默认）与DISTINCT（去掉重复行）
（3）Topn
（4）*（显示所有列）
例：
查询表TchInfo前三行的指定列
useyoman
selecttop3TIDas工号,Tnameas姓名,Titleas职称fromTchInfo
3.2条件查询
1基本语句
SELECTFROMWHERE（search_condition）
2比较查询
（1）比较运算符：
=<>!
=等
（2）例：
查询stuinfo中dept为管理系的学生信息
Select*fromstuinfo
Wheredept=’管理系’
3范围查询
（1）BETWEENAND
（2）NOTBETWEENAND
（3）（NOT）IN
例：
查询生日在1984-9-1和1985-8-31之间的学生信息
useyoman
select*fromStuInfo
whereBirthDaybetween'1984-9-1'and'1985-8-31'
注：
用户定义完整性
由用户针对具体数据环境与应用环境设置的一组规则或约束。
4模糊查询
（1）（NOT）LIKE%
（2）（NOT）LIKE_
（3）（NOT）LIKE[]
（4）（NOT）LIKE[^]
5使用逻辑运算符查询
（1）NOT
（2）AND
（3）OR
3.3排序查询
1基本语法
（1）ORDERBY
（2）ASC
（3）DESC
例：
查询ScoreInfo中CID=’001’的全部信息
useyoman
select*fromScoreInfo
whereCID='001'
ORDERBYScoredesc/*缺省视为升序（asc）*/
3.4分组筛选查询
1聚合函数
（1）SUM（）
（2）AVG（）
（3）MIN（）
（4）MAX（）
（5）CUONT（）
2基本语法
（1）GROUPBY
（2）HAVING
（3）CUBE
例：
在SCOREINFO表中查询、最低成绩
useyoman
selectCID,AVG（SCORE）AS平均成绩,MIN（SCORE）AS最低成绩FROMSCOREINFO
groupbyCID
3.5联结查询
用到逻辑运算副与比较运算符
1等值于非等值联结查询：
Ø自然联结：
DISTINCT
2复合条件联结：
WHERE子句中有两个联结条件
3自身联结：
需要为表指定两个别名
4外联结
（1）左外联结
（2）右外连接
3.6嵌套查询
（1）使用IN和NOTIN
（2）使用比较运算符
（3）使用EXISTS和NOTEXISTS
4数据表与视图
实体完整性
数据完整性域完整性
参照完整性
用户自定义完整性
定义和检验完整性的机制：
约束规则触发器存储过程标识列数据类型索引
4.1预备知识：
系统数据类型
1数值型
（1）整数
（2）位数据类型
（3）小数
（4）货币数据
2字符型
（1）字符串型
（2）Unicode字符串型
（3）二进制字符串型
3日期和时间类型
4特殊数据类型
这部分可放到1基础概述中，和4关系不大。
4.2视图的基本概念
1定义：
视图是一个虚拟的表，表中记录由一个查询语句（称为子查询）执行后所得到的查询结果构成。
2特点
（1）视图并不是表，而是一个SELECT语句构成的“虚表”
（2）视图看起来像一个表，操作方法也与基表相似
（3）视图的数据是n个（n>=1）表或视图的子集
3视图的优点
（1）简化用户操作：
分散在多个表中的数据，通过视图定义集中在一起；
（2）简化用户权限管理：
通过授予用户使用视图的权限，即指定了用户使用表的特定列的权限，增加了安全性。
4关于视图的注意事项
（1）只有在当前数据库中才能创建视图
（2）命名是不能与表同名，视图名必须是唯一的
（3）不能把规则、默认值或触发器与视图相关联
（4）不能在视图上建立任何索引，包括全文索引
4.3表结构的创建与修改
一、预备知识
1、实体完整性（EntityIntegrity）
实体：
是数据库中表示的一个客观存在并可相互区别的事物。
实体完整性的特征：
基本表中的主码不能取空值，保证实体有意义。
基本表中的主码必须唯一，保护实体可区分。
2、域完整性（DomainIntegrity）
域完整性要求存入数据库基本表中的数据必须满足某种特定的数据类型、格式和取值范围，以及该列是否接受null值等规定。
例：
StuInfo表中，Birthday列必须是日期型数据；性别（Sex）必须是字符型的，且只能取“男”或“女”。
3、参照完整性（ReferentialIntegrity）
通过主码和外码约束来维护表之间数据一致性的手段。
其规则是：
参照表中的外码取值只能取null值或是被参照表中某记录的主码值。
注：
参照完整性在两个表的插入、删除和修改操作上，体现为联动关系：
当参照表插入新纪录时，系统检查新记录的外码是否在被参照表中存在，如果存在则允许插入，否则拒绝输入；
被参照表删除某纪录a时，可以根据用户设定将参照表中外码值与a的主码值相同的记录同时删除（称为级联删除），也可以拒绝删除记录a；
被参照表修改某纪录a主码值时，根据需要系统可以将参照表中外码值与a的主码值相同的记录同时修改（称为级联修改），也可以拒绝CID修改。
二、表结构的创建
（1）用【对象资源管理器】创建表结构
（2）用T-SQL命令语句创建表结构：
例：
建立scoreInfo（成绩表）
USEyoman
CREATETABLEScoreInfo
（CIDchar（8）NOTNULL,/*notnull是实体完整性约束非空约束/
SIDchar（8）NOTNULL,/*char（8）是域的完整性约束约束了数据类型与长度/
TIDchar（8）NULL,
Scorenumeric（3,0）NULL,
Schyearchar（9）NULL,
Termchar
（1）NULL,
FOREIGNKEY（CID）REFERENCESCourseInfo（CID）,/*REFERENCES指定参照关系参照/
FOREIGNKEY（SID）REFERENCESStuInfo（SID）,
FOREIGNKEY（TID）REFERENCESTchInfo（TID）,
PRIMARYKEY（CID,SID）
）
注：
在数据完整性方面，此表为参照表，其余三个表为被参照表，CIDSIDTID为此表外码的同时又是另外三个表的主码，外码的取值只能是空或是主码的值，从而保证了表与表之间的数据的一致性。
此为参照完整性。
三、表结构的修改
（1）用【对象资源管理器】修改表结构
（2）用T-SQL命令语句修改表结构：
⏹ADD：
添加
⏹DROPCOLUMN:
删除列
⏹ALTERCOLUMN:
修改列值
⏹PRIMARYKEY指定主键
⏹FOREIGNKEY指定外键
⏹REFRENCES指定参照关系
⏹CONSTRAIN指定约束
例：
为StuInfo增加yoman列
useyoman
AltertableStuInfo
addyoumanvarchar（20）
例：
为ScoreInfo添加外键与主键
useyoman
altertableScoreInfo
ADDFOREIGNKEY（[CID]）REFERENCESCourseInfo（CID）,
FOREIGNKEY（[SID]）REFERENCESStuInfo（SID）,
FOREIGNKEY（[TID]）REFERENCESTchInfo（TID）,
PRIMARYKEY（CID,SID）
附注：
约束
通过强制用户执行在字段、记录或者表（表之间）等不同范围的数据规则，来保证数据的完整性。
列级约束：
声明作为列定义的一部分，仅适用于对应的列。
分类：
表级约束：
当一个约束中包含同一个表的多个列时，必须使用表级约束。
●主键（PRIMARYKEY）约束
主键唯一地表示表中的数据行，通常将某一列或多列组合定义为主键。
设置主键是实现实体完整性的方法。
一个表只能有一个主键，主键约束中的列不能为空值和重复值（唯一地标识表中的每一行）。
如果主键为复合键，则其中某列中的记录值可以重复，但主键所在的列组合值必须唯一。
定义主键时，系统自动为其生成一个同名的聚集索引。
●唯一性（UNIQUE）约束
UNIQUE约束确保表中一列或多列的组合值具有唯一性，防止输入重复值，主要用于保证非主键列的实体完整性。
SQLServer将为每个UNIQUE约束创建一个唯一索引。
一个表可创建多个UNIQUE约束。
●CHECK约束
通过限制列值的范围保证数据的域完整性。
一个数据表可以定义多个CHECK约束。
CHECK约束用一个逻辑表达式来检查输入数据的有效性，如果满足CHECK约束的条件，则将数据写入到表中，否则数据无法输入。
●外键（FOREIGHKEY）约束
把表中一列或多列组合定义为外键，主要目的是建立和加强表与表之间的数据联系，确保数据的参照完整性。
在创建和修改表时，可通过定义FOREIGHKEY约束
建立外键：
外键的取值只能是被参照表中对应字段已经存在的值，或者Null值。
外键约束只能参照本身所在数据库中的某个表，包括参照自身表，但不能参照其他数据库中的表。
●非空约束
●默认值（DEFALUT）约束
为属性定义默认值，有助于维护数据的完整性。
在插入新记录时，如果未指定在该属性的值，则系统将默认值置为该属性的内容。
默认值可以包括常量、函数或者NULL值等。
对于不允许接受null值的属性，默认值更显示出其重要性。
如当用户在添加数据记录时，在某属性上无法确定应该输入什么数据，而该属性又存在notnull约束，这时可以由系统以默认值的方式指定一个值。
4.4视图的创建与修改与使用
1.创建
使用T-SQL命令创建视图
常见的视图形式
（1）“行列子集”视图
（2）带有WithCheck
升级会员 