SQL复习资料文档格式.docx
《SQL复习资料文档格式.docx》由会员分享，可在线阅读，更多相关《SQL复习资料文档格式.docx（13页珍藏版）》请在冰豆网上搜索。
5确定索引的最佳存储位置
事务的属性
原子性一致性隔离持久性
简述各类数据完整性及其对应的约束实现
实体完整性将行定义为特定表的唯一实体
域完整性指特定列的项的有效性
输入或删除行时，引用完整性保留表之间定义的关系
用户定义完整性使您可以定义不属于其他任何完整性类别的特定业务规则
对应的约束实现是：
1实体完整性约束
2域完整性约束
3参照完整性约束
4用户自定义完整性约束
索引的作用
与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。
索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。
通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。
索引可以减少为返回查询结果集而必须读取的数据量。
索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。
DML触发器的用途
DML触发器全名：
DATAMANIPULATIONLANGUAGE（数据操纵语言）触发器
强制参照完整性创建审计跟踪执行与CHECK约束类似的功能，但是跨表、跨数据库甚至是跨服务器使用
用自己的语句代替用户的操作语句
更新汇总信息
为报表提供非规范化表
设置条件标志
SQL分为几类，每类常用的命令有哪些？
存储过程的优点
使得需要过程式动作的进程可调用
安全性
性能
四、计算题（4分）
一个数据表使用单一扩展盘区或混合扩展盘区存储时占用磁盘空间的估算。
五、操作题（共45分）
所有操作假设由sa账号用SQL语句完成。
1、创建一个数据库
2、在SQLServe中添加一个登录名
3、在“教学”数据库中添加数据库用户
4、在“教学”数据库中创建三个数据表：
考核内容重点：
数据类型、主键、外键、空/非空、数据校验、默认等约束。
5、修改表：
添加一列“性别”（只能取值“男”或“女”），并在“生日”上添加不对已有数据进行验证的数据校验约束“bircheck”，要求输入的数据需满足的条件是学生的年龄必须不小于14岁。
（7分）
添加列、添加约束条件（对已有数据是否校验）、开关约束条件
6、权限管理
7、创建一个视图，用以显示各班的SQL成绩统计（包括班级，最高分，最低分，平均分）。
创建一个显示所有XX级计算机系学生的《YY》课程的成绩的视图，其中包括学号，姓名，课程名，分数四列。
按班级、性别统计XX级计算机系各班的YY课程的平均成绩，结果按照班级的升序，性别的降序显示。
8创建建单/复合索引（升/降序）。
9、定义触发器
在“教学”数据库中添加一个角色。
5、将安全账户添加到角色中。
六、编程题（20分）
在“教学”数据库中创建一个存储过程cj_proc，实现按照输入的课程名称打印此门课程的成绩报表（如不给定课程名称，则输出“SQL”的成绩），输出结果按照分数降序排列。
创建数据库
1、创建一个数据库，数据库名为myfirstdb，包含一个数据文件和一个日志文件，名字自起，大小各为10MB，最大为20MB，增量分别为2MB和10%。
createdatabasemyfirstdb
on
（name=myfirstdb,
filename='
C:
\ProgramFiles\MicrosoftSQLServer\SQLserver\myfirstdb_data.mdf'
size=10mb,
maxsize=20mb,
filegrowth=2mb）
logon
（name=myfirstdblog,
\ProgramFiles\MicrosoftSQLServer\SQLserver\myfirstdblog_log.ldf'
filegrowth=10%）;
go
2、创建一个数据库，数据库名为pubs，包含一个主数据文件、两个各含一个数据文件的自定义文件组和一个日志文件。
createdatabasepubs
onprimary
（name=pubs_data,
\ProgramFiles\MicrosoftSQLServer\SQLserver\pubs_data.mdf'
）,
filegroupfg1
（name=pubs_data1,
\ProgramFiles\MicrosoftSQLServer\SQLserver\pubs_data1.mdf'
filegroupfg2
（name=pubs_data2,
\ProgramFiles\MicrosoftSQLServer\SQLserver\pubs_data2.mdf'
）
（name=pubs_log,
\ProgramFiles\MicrosoftSQLServer\SQLserver\pubs_log.ldf'
）;
3、修改myfirstdb，在数据库中添加一个数据文件（test）和一个文件组myfilegroup1（文件组中包含两个数据文件，自己定义）。
alterdatabasemyfirstdb
addfile
（name=test,
\ProgramFiles\MicrosoftSQLServer\SQLserver\test_data.mdf'
addfilegroupmyfilegroup1
（name=test1,
\ProgramFiles\MicrosoftSQLServer\SQLserver\test1_data.mdf'
（name=test2,
\ProgramFiles\MicrosoftSQLServer\SQLserver\test2_data.mdf'
tofilegroupmyfilegroup1;
添加登录名数据库用户
1、创建一个登录账号“teacher”，为账号‘teacher’在自己的数据库中添加一个安全账户‘sql_teacher’,在数据库中添加一个角色“visitor”。
将安全账户‘sql_teacher’添加到角色“visitor”中，授予“visitor”在“学生表”中’学号’,’姓名’,’生日’三列上的SELECT、INSERT、DELETE、UPDATE权限。
createloginteacher1
withpassword='
abc'
createusersql_teacher1
forloginteacher1
createrolevisitor1
execsp_addrolemember'
visitor1'
'
sql_teacher1'
Go
grantselecton学生（学号,姓名,生日）tovisitor1;
grantinserton学生（学号,姓名,生日）tovisitor1;
grantdeleteon学生（学号,姓名,生日）tovisitor1;
grantupdateon学生（学号,姓名,生日）tovisitor1;
2、允许teacher创建数据库。
Grantcreatedatabasetoteacher
3、收回sql_teacher在姓名列上的insert权限。
Revokeinserton学生（姓名）fromsql_teacher
4、禁止sql_teacher更新生日列中的数据。
Denyupdate学生（生日）tosql_teacher
数据表操作
1、在PUBS数据库中添加一个表“学生”，表中包含三列：
学号（CHAR（10）,主键约束,数据校验约束（输入的学号必须是在校生的学号,学号的前2位是入学年份），姓名（nvarchar（10）），性别（只能取值“男”或“女”）。
usepubs
createtable学生（学号char（10）primarykey
check（year（getdate（））%100-left（学号,2）<
4and
year（getdate（））%100-left（学号,2）>
0）,
姓名nvarchar（10）,
性别nchar
（1）check（性别like'
男女'
））
go
2、修改学生表，在表中添加一列：
出生年月（datetime）默认值为19年前的元旦。
altertable学生
add出生年月datetime
defaultstr（（year（getdate（）-19）））+'
-1-1'
3、将“姓名”列的宽度改为12，不允许空值；
性别的默认值设为“男”。
altertable学生
altercolumn姓名nvarchar（12）notnull
addconstraintXS_XB_def
default'
男'
for性别
4、创建一个课程表，表中包含四列
课程号主键
课程名称非空
先修课号自引用课程号
学分非空。
createtable课程
（课程号intidentityprimarykey,
课程名称nvarchar（20）notnull,
先修课程号intforeignkeyreferences课程（课程号）,
学分numeric（2,1）notnull）;
5、创建一个表“成绩”，表中有三列：
学号外键，
课程号外键，
成绩（小数类型（有效数字4位,小数1位）,数据校验要求在输入时只能输入0~100的数字），默认值为0
createtable成绩（学号char（10）FOREIGNKEYREFERENCES学生（学号）,
课程号INTFOREIGNKEYREFERENCES课程（课程号）,
分数numeric（4,1）default（0）
check（分数between0and100）primarykey（学号,课程号））;
6、让学生表中“学号”列上的数据校验不起作用。
编程题存储过程
2.创建三个表：
学生（学号，姓名）、课程（课程号，课程名）、成绩（学号、课程号、分数），然后在三个表中分别添加记录。
按照输入的课程名称打印此门课程的成绩报表（如不给定课程名称则打印SQL课程的成绩）,输出结果按照分数降序排列：
例如：
《SQL》成绩表
****************************************************
名次学号姓名成绩
10508044126李军95
20508044124李明85
30508044125王刚75
****************************************************
*/
IFEXISTS（SELECTNAMEFROMsysobjects
WHERENAME='
学生'
DROPtable学生
GO
课程'
DROPtable课程
成绩'
DROPtable成绩
createtable学生（学号char（10）primarykey
constraintxh_chkcheck（学号like'
0508044[1-4][0-3][0-9]'
）,姓名nvarchar（10）notnull）
createtable课程（课程号char（6）primarykey,课程名称nvarchar（40））
createtable成绩（学号char（10）notnull,课程号char（6）notnull,分数numeric（4,1））
insert学生values（'
0508044124'
李明'
0508044125'
王刚'
0508044126'
李军'
insert课程values（'
080101'
SQL'
080204'
DS'
insert成绩values（'
85）
95）
75）
86）
ifexists（select*fromsysobjectswherename='
cj_proc'
andxtype='
p'
dropproccj_proc
createproccj_proc@KCMnvarchar（40）=’SQL’
as
SETNOCOUNTON
DECLARE@XHchar（10）,@XMnvarchar（10）,@kchnvarchar（6）,
@CJnumeric（4,1）,@messagenvarchar（80）,@mcint
ifnotexists（select课程.课程号from课程innerjoin成绩on课程.课程号=成绩.课程号and课程.课程名称=@kcm）
print'
无此课程成绩'
else
begin
select@kch=课程.课程号from课程where课程.课程名称=@kcm
《'
+@kcm+'
》成绩表'
printREPLICATE（'
*'
48）
名次学号姓名成绩'
declarexhcj_cursorcursorscroll
forselect学生.学号,姓名,分数from学生,成绩
where成绩.学号=学生.学号and课程号=@kch
orderby分数desc
openxhcj_cursor
fetchnextfromxhcj_cursorinto@xh,@xm,@cj
set@mc=1
while@@fetch_status=0
set@message=ltrim（str（@mc））+space（10）+@xh+space（7）+@xm+space（12）+ltrim（str（@CJ））
PRINT@message
fetchnextfromxhcj_cursorinto@xh,@xm,@CJ
set@mc=@mc+1
end
CLOSExhcj_cursor
DEALLOCATExhcj_cursor
end
创建视图单/复合索引
1、创建一个显示所有学生的《SQL》课程的成绩的视图，其中包括学号，姓名，课程名，分数四列。
usestudent
createtablet8（学号char（10）primarykey,姓名nvarchar（5））
createtablet9（课程号char（5）,课程名nvarchar（5））
createtablet7（学号char（10）,课程号char（5）,课程名nvarchar（5）,分数decimal（4,1））
insertintot8values（1008014112,'
SAM'
insertintot8values（1008014134,'
KING'
insertintot8values（1008014145,'
JAN'
insertintot8values（1008014156,'
QIN'
insertintot9values（12344,'
insertintot9values（12345,'
C'
insertintot7values（1008014112,12344,'
99.0）
insertintot7values（1008014112,12345,'
89.0）
insertintot7values（1008014134,12344,'
88.0）
insertintot7values（1008014145,12344,'
76.0）
insertintot7values（1008014156,12344,'
65.0）
createviewSQLCJB_VW
asselectt8.学号,t8.姓名,t7.课程名,t7.分数
fromt8
innerjoint7
ont7.学号=t8.学号
wheret7.课程名='
2、在课程表的课程号列上建一个索引。
CreateuniqueclusteredindexKCB_IDX
ont9（课程号）
3、在成绩表的学号，课程号上建复合索引。
CreateuniqueclusteredindexCJB_IDX
Ont7（学号,课程号）
4、显示各班的成绩统计
createviewCJTJ_VW
as
selectsubstring（学号,6,1）as班级,
MAX（分数）as最高分,
MIN（分数）as最低分,
AVG（分数）as平均分
fromt7
where分数>
groupbysubstring（学号,6,1）
触发器练习
1、创建表t1,t2。
使用触发器实现引用完整性，当在t1表中执行了加工性操作，则在t2表中作相应操作。
触发器练习题:
2、当在"
学生"
表中删除记录，要求将成绩表中的相应记录删除。
createtriggerxsh_del_triggeron学生fordelete
asdelete成绩where成绩.学号in（select学号fromdeleted）
3、当在"
表中添加05级计算机专业学生的记录，则在成绩表中的添加此学生相应成绩记录。
createtriggerxsh_insert_triggeron学生forinsert
as
declare@xhchar（10）
select@xh=学号frominserted
insert成绩select@xh,课程.课程号,0.0from课程
COLUMNS_UPDATED练习
1、第1、3、5三列同时修改。
（表中有7列）
2、第1、3、5、9、15三列同时修改。
（表中有16列）
3、第1、3、5三列任意一列被修改。
（表中有8列）
4、第1、3两列未被同时修改。
升级会员 