SQL实训题解析Word格式.docx
《SQL实训题解析Word格式.docx》由会员分享，可在线阅读，更多相关《SQL实训题解析Word格式.docx（20页珍藏版）》请在冰豆网上搜索。
出版社'
4、使用函数从字符串“中国机械工业出版社”中返回字符串“机械”，并使用REVERSE函数将字符串“机械”逆序返回。
selectsubstring（'
中国机械工业出版社'
3,2）
selectreverse（'
机械'
5、将字符串“abcdabcd”中的所有字符“a”换成字符“％”。
selectreplace（'
abcdabcd'
'
a'
%'
6、使用函数返回系统当前日期及当前日期的年分，月份及日期子部分。
selectyear_now=datename（year,getdate（））,
month_now=datename（month,getdate（））,
weekday_now=datename（weekday,getdate（））,date_now=getdate（）
三．实验作业
1、用WHILE语句求1—100之间的累加和
declare@sumint,@countint
select@sum=0,@count=1
label:
select@sum=@sum+@count
select@count=@count+1
while@count<
=100
gotolabel
select@sum,@count
2、使用PRINT语句在屏幕上输出2的10次方的值，输出的形式为“2的10次方为：
”
print'
2的次方为'
+rtrim（power（2,10））
3、用IF语句，求分段方程的值，方程如下：
X2（X<
=0）
Y=X+10（0<
X<
5）
X（X>
declare@xint,@yint
set@x=7
if@x<
=0set@y=power（@x,2）
else
if@x>
5set@y=@x
elseset@y=@x+10
select@y,@x
4、设置在20秒钟以后进行一次返回当前系统日期时间的操作。
begin
waitfordelay'
00:
20'
selectgetdate（）
end
5、创建一个数据库NEW，并在数据库中创建一个表student，该表中有四个字段，表如下：
学号
姓名
性别
年龄
95001
李萍
女
22
95002
黄宏
男
20
95003
刘玲
21
95004
王丽
用case语句判断，如果表中有“黄宏”这个人，则显示“有这个人”，否则显示“查无此人！
usenew
select*,'
显示的信息'
=case姓名
when'
黄宏'
then'
有这个人'
else'
查无此人'
fromstudent
三、实训作业：
本次实验的1，2，3，4四个题。
1、数据库的创建
1）使用SSMS创建一个只含一个数据文件和一个事务日志文件的数据库，数据库名为new，主数据库文件逻辑名称为new_data，数据文件初始大小为5MB，最大值为200MB，数据文件大小以3MB的增量增加。
日志逻辑文件名称new_log.ldf，日志文件初始大小为2MB，最大值100MB，日志文件以15％增量增加。
createdatabasenew2
on
primary（name=new2_date,
filename='
C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\new2.mdf'
size=5MB,
maxsize=200MB,
filegrowth=3MB）
logon
（name=new2_log,
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\new2.ldf'
size=2MB,
maxsize=100MB,
filegrowth=15%）
go
2）创建一个具有多个数据文件和日志文件的数据库。
该数据库名称为old，有3个5MB的数据文件,名字自定，最大空间均为20MB，且均按10％的增量增长,其中第三个数据文件在old_group文件组里,主文件是列表中的第一个文件；
还有1个10MB的事务日志文件，最大到30MB,按1MB增量增长，名字自定。
createdatabaseold
primary（name=old1_date,
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old1.mdf'
maxsize=20MB,
filegrowth=10%）,
（name=old2_date,
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old2.mdf'
filegroupold_group
（name=old3_date,
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old3.mdf'
filegrowth=10%）
（name=old4_log,
\ProgramFiles\MicrosoftSQLServer\MSSQL.3\MSSQL\Data\old4.ldf'
size=10MB,
maxsize=30MB,
filegrowth=1MB）
2、查看并修改数据库的属性
1）用SQL语句将NEW数据库中数据文件的最大值改为180MB,数据增长改为15%,将日志文件的初始大小改为3MB。
usenew2
alterdatabasenew2
modifyfile（name=new2_date,
maxsize=180MB,
modifyfile（name=new2_log,
size=3MB）
2）执行系统存储过程sp_helpdb查看old数据库的信息。
execsp_helpdbnew2
3、数据库的缩小、更名及删除
dropdatabaseold
1）使用命令缩小数据库NEW的大小，收缩成原来大小的80％。
dbccshrinkdatabase（new2,80）
2）使用两种方法实验：
将NEW更名为NEWDATA，并删除该数据库。
createdatabasenew
go
sp_renamedbnew,newdata
2、自定义数据类型的创建
使用对象资源管理器创建一个名为a0、数据长度为16，可变长字符，允许为空的自定义的数据类型
useSC
execsp_addtypea0,'
varchar（16）'
null'
3、表的创建与管理
1）用SQL语句在SC数据库中创建表student,有五个属性，分别为（学号nchar（5）notnull，姓名nvarchar（8）notnull,性别bit,年龄int）
usesc
createtablestudent
（学号nchar（5）notnull,
姓名nvarchar（8）notnull,
性别char
（2）,
年龄int）
2）使用SQL语句设置表student中学号为主键
altertablestudent5
addconstraintpk1_student_id
primarykeyclustered（student_id）
3）使用SQL语句为表student设置检查约束，要求所输入的年龄必须在15—35岁（含15及35）之间
altertablestudent2
addconstraintcheck_student_年龄
check（student_年龄>
=15andstudent_年龄<
=35）。
4）使用SQL语句为student表再添加一个属性，名为“系部”，数据类型为a0，允许空
usesc
altertablestudent
addstudent_xibua0notnull
5）使用SQL语句为student表添加记录（95001，李咏，false，20，计算机）
insertintostudent5
（student_id,student_name,student_sex）
values
（'
95001'
李咏'
男'
6）使用SQL语句将学号为95001这个学生的年龄改为19岁。
updatestudent2
setstudent_年龄='
19'
wherestudent_id='
第二大题：
约束有哪6种类型，各是什么含义？
（1）非空约束：
表中的某些列必须存在的有效值，不允许有空值出现。
（2）缺省约束：
当向数据库中的表插入数据时，如果用户没有明确给出某列的值，SQLSever自动为该列输入指定值。
（3）检查约束：
限制插入列中的值的范围。
（4）主键约束：
要求主键的列上没有两行具有相同值，也没有空值。
（5）唯一约束：
要求表中所有行在指定的列上没有完全相同的列值。
（6）外键约束：
要求正被插入或更新的列（外键）的新值，必须在被参照表（主表）的相应列（主键）中已经存在
1、默认值的创建及管理
使用SQL语句创建一个默认值abc1，该默认值为真值TRUE，将该默认值分别绑定到student表的性别列上，然后解除该默认值的绑定，并删除该默认值。
createdefaultabc1AS'
TRUE'
GO
sp_bindfaultabc1,'
student.性别'
sp_unbindfault'
dropdefaultabc1
2、规则的创建与管理
使用SQL语句创建一个规则abc2，该规则用于限定所取值在8至55之间，将该规则绑定到student表的年龄列上，以及xskc表的成绩列上，然后解除xskc表的成绩列上规则的绑定。
createruleabc2as@abc2>
=8and@abc2<
=55
sp_bindruleabc2,'
student.年龄'
xskc.成绩'
sp_unbindrule'
3、标识列的定义
使用SQL语句为xskc表添加一个标识列，列名为“标识列”，类型为int，初始值为1，步长也为1，注意观察xskc表的数据变化情况。
altertablexskc
add标识列intidentity（1,1）
（1）查询全体学生的学号及姓名
select*fromstudent
select学号,姓名fromstudent
（2）查询全体学生的姓名及出生年月（经过计算所得到的列），并将计算所得列的列名定义为出生年月。
select姓名,2011-年龄fromstudent
出生年月'
=2011-年龄fromstudent
（3）查询一下都有哪些系。
（4）查询所有20岁以下学生的姓名和年龄。
select姓名fromstudentwhere年龄<
（5）查询至少有一科的成绩在85分以上的学生的学号。
select学号fromxskcwhere成绩>
85
（6）查询年龄不在19到21之间的学生的姓名和他们的年龄
select姓名,年龄fromstudentwhere年龄notbetween19and21
（7）查询除了计算机系和机电系学生的学号和姓名
where系部notbetween'
计算机'
and'
机电'
（8）查询所有不姓刘的学生姓名
select姓名fromstudentwhere姓名notlike'
刘%'
（9）查询姓林的全名为两个字的学生的姓名及其所在系部
select*fromstudent
where姓名like'
林_'
（10）查询前两个字是李红，第三个字符是“[”的学生姓名及其所在系
select姓名,系部fromstudent
where姓名like'
李红b[%'
escape'
b'
（11）查询选修了课程但没有参加考试的学生学号。
select学号fromxskc
where课程号isnotnulland成绩isnull
（12）查询计算机系年龄小于21岁的学生姓名
select姓名fromstudent
where年龄<
21and系部='
（13）查询全体学生信息，查询结果按所在系降序排序。
select*fromstudent
orderby系部desc
1）从student和xskc表中查询成绩在80分以上的学生的学号、姓名、课程号和成绩
selectstudent.学号,student.姓名,xskc.课程号,xskc.成绩
fromstudent,xskc
where成绩>
80
2）查询成绩在80分以上的学生的学号，姓名，课程号，课程名，成绩。
selectstudent.学号,student.姓名,xskc.课程号,xskc.成绩,course.课程名
fromstudent,xskc,course
3）查询所有20岁以下学生的学号
selectstudent.学号
4）查询所有20岁以下学生选修课程的课程号
select课程号fromxskc
where学号in（select学号fromstudent
20）
groupby课程号
5）查询所有20岁以下学生选修课程的课程名及学分
select课程名,学分fromcourse
where课程号in（
20））
6）查询所有选过课学生的学号；
fromstudent,course
where课程号isnotnull
7）查询所有选过课学生的姓名及所在系；
selectstudent.学号,student.姓名,student.系部
8）查询被选修课程中每门课程的最高分；
selectxskc.学号,max（成绩）fromxskc
groupby学号
9）查询每个系有多少个学生；
USESC
SELECT系部,count（姓名）
GROUPBY系部
10）用连接查询的方法查询所有20岁以下学生选修课程的课程号。
selectdistinctcourse.课程号
11）创建一个新的永久表computer，用来存放student表中所有计算机系学生有关信息。
select*intocomputer1fromstudent
where系部='
（一）创建视图
1、用两种方法创建view1视图，显示所有女同学的有关资料
createviewview2
as
where性别='
false'
2、用T-SQL语句在SC数据库中创建一个名为view2，能输出成绩大于80分的学生选修成绩资料的加密的视图。
withencryption
select*fromxskc
80
3、用T-SQL语句创建view3视图，显示选修2学分这样的课程的学生的姓名，课程名，成绩
createviewview3
select姓名,成绩,课程名
fromxskc,student,course
where学分='
2'
andstudent.学号=xskc.学号
andxskc.课程号=course.课程号
4、用T-SQL语句创建view4视图，显示选修了C语言的学生的学号，姓名及成绩。
createviewview4
select姓名,成绩,student.学号
where课程名='
C语言'
（二）查看视图的创建信息
5、查看一下view3视图的定义脚本
sp_HELPTEXTVIEW3_1
思考：
能否使用该方法能否查看到view2的脚本
不能，因为已加密。
（三）修改视图的定义
6、用T-SQL语句将view3视图改为显示选修4学分课程的学生的姓名，课程名，成绩。
alterviewview3_1
4'
7、查询view1中年龄小于20岁的学生的有关信息
select*
fromview1_2
8、查询view3中成绩>
75分的学生的姓名
select姓名
fromview3_1
75
9、向view1中插入一个人，其信息为（95009，赵春梅，女，21，计算机）
insertintoview1
（学号,姓名,性别,年龄,系部）
values（'
95009'
赵春梅'
21'
10、利用view1将赵春梅的系部改为外语。
updateview1
set系部='
外语'
where姓名='
11、利用view1删除赵春梅这个人的信息。
deletefromview1
（一）创建索引
在做下题之前请先想办法删除course表中已经存在的聚簇索引。
1、用T-SQL为course表创建一个惟一的聚簇索引aa，索引列为课程名，降序，填充因子为60％。
use
sc
createuniqueclusteredindexaa
oncourse（课程名desc）
with
fillfactor=60
2、用T-SQL为student表创建一个唯一的非聚簇索引bb，索引列为姓名，升序，填充因子为40％
CREATENONCLUSTEREDINDEXbb
ONstudent（姓名ASC）
WITH
FILLFACTOR=40
3、用T-SQL为xskc表创建一个非唯一的非聚簇索引cc，索引列为课程号，学号，降序，填充因子为50％
createuniqueclusteredindexcc
onxskc（课程号desc,学号desc）
fillfactor=50
（二）重建索引
4、用T-SQL语句重建bb,填充因子改为60％。
createuniqueclusteredindexbb
onstudent（姓名asc）
（三）重命名索引
5、用T-SQL语句将索引aa的名字改为aaa。
升级会员 