SQL 期末考试复习资料.docx
《SQL 期末考试复习资料.docx》由会员分享，可在线阅读，更多相关《SQL 期末考试复习资料.docx（23页珍藏版）》请在冰豆网上搜索。
SQL期末考试复习资料
第一章
数据库技术发展的三个阶段
人工管理阶段、文件系统阶段、数据库系统阶段
数据库的基本概念
DataBase数据库是为了实现一定的目的、按某种规则组织起来的“数据”的“集合”，简而言之，它是有结构的数据集合。
数据库系统的三级模式结构（考）
外模式、模式、内模式
外模式/模式映射保证逻辑独立性
模式/内模式映射保证物理独立性
数据模型的要素及其特点
层次模型、网状模型、关系模型
要素：
数据结构：
数据操作：
关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL）
完整性约束：
实体完整性、参照完整性和用户定义的完整性
关系的特点：
1、每列数据项不可再分解
2、同一个关系中，不能出现相同的属性名
3、同一个关系中，不能有完全相同的元组
4、所有元组格式相同，长度相同；属性类型相同
5、行列顺序无关紧要
关系的完整性（插入）：
实体完整性、参照完整性
关系的完整性（删除）：
参照完整性规则
关系的完整性（修改）=删除+插入
实体之间的关系：
（1）一对一关系
（2）一对多关系
（3）多对多关系
常见数据库：
foxpro、access、sqlserver、mybase、oracle
第二章
SQLServer2008新特性：
可信、高效率、智能
SQL（结构化查询语言）：
数据定义语言（DDL）CREATE、DROP、ALTER
数据操作语言（DML）SELECT、INSERT、UPDATE、DELETE
数据控制语言（DCL）GRANT、REVOKE
特点：
一体化、高度非过程化、以同一种语法格式提供两种使用方式、语言简捷，易学易用
SQL标识符
1.标识符的首字母必须是以下两种情况之一：
所有在统一码（Unicode）2.0标准规定的字符，包括26个英文字母a-z和A-Z，以及其他一些语言字符，如汉字。
下划线“_”、“@”或“#”。
2.后续字符：
除首字母约定外，还有数字字符及“$”。
3.不允许是T-SQL的保留字
4.不允许嵌入空格或其他特殊字符（@、#、##、@@）
常规标识符：
符合标识符的格式规则
分隔标识符：
使用了双引号（“”）或方括号（[]）等分隔符号，既可以遵守标识符命名规则，也可以不遵守标识符命名规则。
第3章创建和管理数据库
数据库：
是按照数据结构来组织、存储、和管理数据的仓库，是存储在一起的相关数据的集合。
SQLServer2008的数据库对象主要包括表（table）、视图（view）、索引（index）、存储过程（storedprocedure）、触发器（trigger）和约束（constraint）等。
SQLServer2008数据库主要由文件和文件组组成。
文件主要分为3类：
主要数据文件、次要数据文件和事务日志文件。
文件组主要分为两种类型：
主文件组和用户定义文件组。
数据库对象的全名应该由服务器名、数据库名、拥有者名和对象名
两种类型的实例：
默认实例和命名实例（<计算机名称>\<实例名称>）
数据库操作（使用SSMS和T-SQL创建、修改、删除数据库）
新创建的数据库，系统对数据文件的默认值为：
初始大小3MB，最大大小不限制，增量为1MB。
对日志文件的默认值为：
初始大小1MB，最大大小不限制，增长方式为按10%比例增长。
DROPDATABASE
数据表操作（使用SSMS和T-SQL创建、修改、删除数据表）
createdatabaseuserdb1
on
（
name=userdb4_data,
filename='E:
\sql_data\userdb4.mdf',
size=5,
maxsize=10,
filegrowth=1
）
logon
（
name=userdb4_log,
filename='E:
\sql_data\userdb4.ldf',
size=2,
maxsize=5,
filegrowth=1
）
创建、修改、删除约束（根据完整性规则应用SSMS和T-SQL设置表的主码、约束和外码等）
添加日志文件：
usebooksys--使用数据库
alterdatabasebooksys--修改数据库
addlogfile--添加日志文件
（
name=booksys_ldf,
filename='I:
\sql_data\test1\booksys.ldf',
size=2mb,
maxsize=10mb,
filegrowth=2mb
）
tofilegroup文件组名--将文件添加到文件组中
添加次要数据文件：
usebooksys
alterdatabasebooksys
addfile
（
name=booksys,
filename='I:
\sql_data\test1\booksys.ndf',
size=2mb,
maxsize=10mb,
filegrowth=2mb
）
添加文件组：
Alterdatabase数据库名
Addfilegroup文件组名
删除文件：
Alterdatabase数据库名
removefile文件名
删除数据库:
Drop数据库名
创建表：
usebooksys
createtablebook
（
bookidvarchar（20）primarykey,--主键
booknamevarchar（60）unique,--唯一约束
editorchar（8）notnull,--非空约束
pricenumeric（5,2）check（price>20）,--检查约束
publishvarchar（30）default（‘电子科大’）,--默认值
publishdatadatetime,
qtyint,
foreignkey（字段）references参照表名（字段[主键]）
）
修改列：
ALTERtable表名
（
ADD列名类型约束，
|ALTERCOLUMN列名数据类型约束，
|DROPCOLUMN列名
）
添加约束
altertableborrow
addconstraintpk_idprimarykey（bookid,cardid,bdate）—添加主键约束
添加外键约束
altertableborrow
addconstraintfk_cardforeignkey（cardid）referencesreader（cardid）
检查约束
ALTERTABLEstudent
ADDCONSTRAINTCHECK_sexCHECK（sex='男'orsex='女'）
默认值
ALTERTABLEstudent
ADDCONSTRAINTdf_spDEFAULT'三院'FOR列名
唯一约束
ALTERTABLEstudent
ADDCONSTRAINTuq_IDUNIQUE（ID）
删除约束：
ALTERTABLE表名
DROP约束名
常见的约束：
1、NULL/NOTNULL：
空值/非空值约束。
2、PRIMARYKEY：
主键约束，等价非空、单值。
3、CHECK（逻辑表达式）：
检查约束。
4、DEFAULT常量表达式：
默认值约束。
5、UNIQUE：
单值约束。
6、foreignkeyREFERENCES父表名（主键）：
外键约束
删除表
droptable表名
数据操作（使用SSMS和T-SQL添加、修改、删除数据）
插入数据
insertintobookvalues（'TP2001--001','数据结构','李国庆','22.00','清华大学出版社','2001-1-8',20）
更新数据
updatestudentsetclassno='05'wherespno='03'
删除数据
deletestudentwherespno='03'
删除列前必须先删除其上的约束
第四章T-SQL编程与应用
DECLARE（考）
DECLARE
{
@变量名数据类型，@变量名数据类型[,…n]
}
通配符（考）
上课例子：
SELECT*FROM数据表WHERE编号LIKE’00[^8]%[A-D]%’
可能会查出的编号值为：
A、006SABC
B、007SFFG
C、08ASX
D、005B
运算符优先级
当一个复杂表达式中包含有多个运算符时，运算符的优先级决定了表达式计算和比较操作的先后顺序。
运算符的优先级由高到低的顺序如下：
（1）+（正）−（负）～（位反）
（2）*（乘）/（除）%（取余）
（3）+（加）+（字符串串联运算符）-（减）
（4）=、>、<、>=、<=、<>、!
=、!
>、!
<（比较运算符）
（5）^（按位异或）&（按位与）|（按位或）
（6）NOT
（7）AND
（8）ALLANYBETWEENINLIKEORSOME（逻辑运算符）
（9）=（赋值）
第五章数据查询（设计题一定考）
查询语法格式（考）
SELECT列名
INTO新表名
FROM<表或视图>
WHERE逻辑表达式--列级条件
GROUPBY列名
HAVING逻辑表达式--组级条件
ORDERBY列名asc升序，desc降序
常用的聚合函数（考）
sum（）
Avg（）
Max（）
Min（）
Count（）
Count（distinct）
GROUPBY注意事项
（1）在SELECT子句的字段列表中，除了聚集函数外，其他所出现的字段一定要在GROUPBY子句中有定义才行
（2）SELECT子句的字段列表中不一定要有聚集函数，但至少要用到GROUPBY子句列表中的一个项目。
（3）在SQLServer中text、ntext和image数据类型的字段不能作为GROUPBY子句的分组依据。
（4）GROUPBY子句不能使用字段别名。
交叉连接（笛卡尔积）
SELECTfieldlistFROMtable1,table2
SELECTfieldlistFROMtable1crossJOINtable2
连接
SELECTfieldlist
FROMtable1[INNER|LEFT|RIGHT|FULL]JOINtable2
ONtable1.column=table2.column
INNERJOIN:
如果表中有至少一个匹配，则返回行
LEFTJOIN:
即使右表中没有匹配，也从左表返回所有的行
RIGHTJOIN:
即使左表中没有匹配，也从右表返回所有的行
FULLJOIN:
只要其中一个表中存在匹配，就返回行
子查询（IN比较运算符EXISTSUNION）（考选择、填空）
UNION操作符用于合并两个或多个SELECT语句的结果集。
UNION内部的SELECT语句必须拥有相同数量的列。
列也必须拥有相似的数据类型。
同时，每条SELECT语句中的列的顺序必须相同。
Between：
列名>=开始值and列名<=结束值（考）
DISTINCT消除重复记录（考）
第六章
（考）
视图（View）是一个虚拟表。
视图作用（考多选题）
视图能简化用户的操作、提高安全性、逻辑数据独立性
修改数据
创建视图：
（考）
CREATEVIEWview_name
ASselect_statement
修改视图：
ALTERVIEWview_name
ASselect_statement
删除视图:
DROPVIEW视图名
视图添加数据
Insertinto视图名values（列值，列值，…，列值n）
视图更新数据
update视图名set列=列值，列=列值
Where条件表达式
视图删除数据
DELETEFROM视图名WHERE逻辑表达式
第七章索引与数据完整性
索引优点（考）
提高数据库的查找速度、保证列的惟一性、加快表与表之间的连接速度、减少
查询中分组和排序的时间、检索数据的过程中使用优化隐藏器，提高系统性能
索引类型（考）
存储结构：
聚集索引（物理顺序与索引顺序相同、一个表一个）和非聚集索引（物理顺序与索引顺序不相同、一个表可有多个）
数据唯一性：
唯一索引（UNIQUEIndex）和非唯一索引（NonuniqueIndex）
唯一索引是指索引值必须是唯一的，不允许数据表中具有两行相同的索引值.
键列的个数：
单列索引和多列索引
索引分类（考）
聚集索引、非聚集索引、唯一索引、索引视图、全文索引
关键字
CREATE
[UNIQUE][CLUSTERED|NONCLUSTERED]--唯一，聚集，非聚集
INDEX索引名
ON表（视图）名（列名[ASC|DESC][,…n]）
删除索引
dropindex表名.索引名
数据完整性
（1）数据类型准确无误。
（2）数据取值符合规定的范围。
（3）多个数据表之间的数据不存在冲突。
数据完整性机制（考）
实体完整性（行）、域完整性（列）、引用完整性（表与表）、用户定义完整性
以下为了解即可，不知道考不考
存储过程优点（考选择题）
允许标准组件式编程，可以嵌套使用，增强重用性和共享性
可以接受并使用参数动态执行其中的SQL语句
比一般的SQL语句执行速度快
具有安全特性
允许模块化程序设计
减少网络通信流量
可以强制应用程序的安全性
存储过程类别（考）
系统提供的存储过程、用户自定义存储过程和扩展存储过程。
创建语法
CREATEPROCEDURE[proc]procedure_name[@参数]
with[recompile（重新编译）|encryption（加密）]（考）
Assql语句
调用方法
Exec存储过程名参数
变量传值的调用方法：
declare@变量名数据类型
set@变量名=value
Execprocedure_name@变量名
修改存储过程：
alterPROCEDURE[proc]procedure_name[@参数]
Assql语句
删除存储过程
dropproc存储过程名
获得存储过程信息：
sp_helptext
查看存储过程的相关性信息：
sp_depends
查看存储过程的一般信息：
sp_help
例子:
createprocp_stu_avg
@stu_idchar（10）,
@avg_cjintoutput
as
select@avg_cj=avg（chengji）
fromxuanke
wherexkid=@stu_id
declare@cjint
execp_stu_avg@stu_id='09002',@avg_cj=@cjoutput
select@cjas平均成绩
触发器：
trigger
触发器分为DML触发器和DDL触发器。
创建DML触发器语法：
CREATETRIGGER[schema_name.]trigger_name
ON{table|view}
{FOR|AFTER|INSTEADOF}
{[INSERT][,][UPDATE][,][DELETE]}
AS
sql_statement
DDL触发器只能在触发事件发生后才会调用执行，即它只能是AFTER触发器。
创建DDL触发器语句（无设计题）
CREATETRIGGERtrigger_name
ON{ALLSERVER|DATABASE}
Withencryption
{FOR|AFTER}触发的事件类型
ASsql_statement
删除触发器
DROPTRIGGER触发器名
管理触发器
修改触发器、删除触发器、禁用（启用）触发器
第九章事务
系统函数：
标量函数，聚合函数，行集函数。
（考）
三种用户自定义函数：
标量函数：
返回单个数据值。
（考设计题）
内连表值函数：
返回值是一个记录集合——表。
在此函数中，return语句包含一条单独的select语句。
多语句表值函数：
返回值是由选择的结果构成的记录集。
函数不能有输出参数，存储过程可以有输出参数。
创建函数语法：
（考设计题）
createfunction函数名（@形参名数据类型[,@形参名数据类型,…,n]）
returns返回值数据类型
as
begin
命令行或程序块（必须有Return子句）
End
调用函数：
Printdbo.函数（[实参]）或selectdbo.函数（[实参]）
删除自定义函数
dropfunction函数名
Pivot（必考）
selectsex,信息学院,计算机学院,机械学院
from（selecta.studentID,a.sex,a.specialityfrom
studenta）ast
pivot（count（studentID）forspecialityin（信息学院,
计算机学院,机械学院））asb
事务属性：
原子性（Atomicity）、一致性（Consistency）、隔离线（Isolation）、持久性（Durability）（考）
事务管理主要使用四条语句（考）
1.BEGINTRANSACTION（事务的起点）
2.COMMITTRANSACTION（事务的终点）
3.ROLLBACKTRANSACTION（事务回滚）
4.SAVETRANSACTION（事务保存点）
类型：
系统提供事务、用户定义事务（考）
模式：
自动提交事务、显式事务、隐性事务（考）
并发问题：
丢失更新、未确认的相关性（脏读）、不一致的分析（不可重复读）、幻象读
死锁条件：
互斥条件、请求与保持条件、不剥夺条件、循环等待条件
锁：
共享锁S、独占锁X、更新锁U（考）
第十章
SQLServer的安全性管理是建立在验证（authentication）和访问许可（permission）这两种机制上的。
SQLServer2008的验证方式包括Windows验证模式与混合验证模式.
SQLServer的安全模型中包括以下几部分：
•SQLServer身份验证；
•登录账户；
•数据库用户；
•角色；
•权限
在SQLServer中，账号有两种：
一种是登录服务器的登录账号，另外一种就是使用数据库的用户账号.
创建登陆名:
createlogin登录名withpassword=’密码’
修改登录名：
alterlogin登录名withpassword=’密码’
删除登录名：
droplogin登录名
课后作业
--作业二
--1、显示book表中所有书号、书名和出版社
selectbookid,bookname,publishfrombook
--2、显示所有的书目（DISTINCT）
selectdistinctbooknamefrombookorderbybookname
--3、显示年以后出版的书
select*frombookwhere（（year）publishdate）>2001
--4、显示读者信息表中所有计算机系的男同学的信息
select*fromreaderwheredept='计算机系'andsex='男'
--5、显示所有编者姓刘的图书情况
select*fromreaderwherenamelike'刘%'
--6、将book中的书籍信息按出版时间先后显示
select*frombookorderbypublishdateASC
--7、列出在book表中总共有多少种书
selectcount（bookid）frombook
--8、列出水利水电出版社出版的书的种数
SELECTbooknameFROMbook
wherepublish='水利水电出版社'
GROUPBYbookname
--9、查找最贵和最便宜的书的价格
SELECTmax（price）FROMbook
SELECTmin（price）FROMbook
--10、各类书的总库存
selectbookname,count（qty）as总库存frombookgroupbybookname
--11、统计各出版社的图书库存总量
selectpublish,count（qty）as总库存frombookgroupbypublish
--12、将进行读者信息表reader和借书情况表borrow两张表进行各种连接
Select*fromreaderJOINborrowONreader.cardid=borrow.cardid
select*fromreaderLeftJOINborrowONreader.cardid=borrow.cardid
select*fromreaderRightJOINborrowONreader.cardid=borrow.cardid
--创建查询某读者信息的存储过程proc_reader，（cardid在执行存储过程时给出）并调用。
ifexists（select*fromsysobjectswherename='proc_reader'andtype='P'）
begin
dropprocedureproc_reader
print'已删除！
'
end
else
print'不存在，可创建！
'
createprocedureproc_reader
@cardidvarchar（20）
as
select*
fromreader
wherecardid=@cardid
execproc_reader@cardid='s0111'
--创建DML（after）触发器tri_borrow，在图书借阅表borrow中添加借阅信息记录（bookid、cardid）后，
--自动添加借阅日期为当前，并按天的借阅期限得到该书的应还日期；并且对应的书籍库存量减。
createtriggertri_borrow
onborrow
AFTERINSERT
as
begin
print'更新触发开始执行'
declare@bookidvarchar（20）,@cardidvarchar（10）
SET@bookid=（SELECTbookidFR
升级会员 