2024年3月6日发(作者：)
实验五 查询数据库
1） 启动SQL Server查询分析器，打开“SQL查询分析器”窗口。
2） 在“SQL查询分析器”窗口中选择要操作的数据库，如“XSCJ”数据库。
3） 在KC表中查询学分低于3的课程信息，并按课程号升序排列。
select *
From KC
Where 学分<=3
4） 在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。
Select 学号,avg(成绩)
From XS_KC
Group by 学号
Order by 学号
5） 在XS_KC表中查询选修了3门以上课程的学生学号。
Select 学号
From XS_KC
Group by 学号
Having count(*)>=3
6） 按学号对不及格的成绩记录进行明细汇总。
Select 学号,成绩
From XS_KC
Where 成绩<60
7） 分别用子查询和连接查询，求107号课程不及格的学生信息。
Select *
From XSQK
Where 学号in
(select 学号
From XS_KC
Where 课程号= '107' and 成绩<60 )
8） 用连接查询在XSQK表中查询与杨颖住在同一寝室的学生，即其联系电话相同
Select *
From XSQK
Where 联系电话 in
(Select 联系电话
From XSQK
Where 姓名='杨颖')
9） 查询XSQK表中所有的系名。
10）查询有多少同学选修了课程。
select count(distinct 学号)
From
XS_KC
11） 查询有多少同学没有选课。
select count(*)
from xsQK left join xs_KC on xs_KC.学号=xsQK.学号
where 成绩is null
12）查询与杨颖同一个系的同学姓名。
select 姓名
From
xsQK
Where
13） 查询选修了课程的学生的姓名、课程名与成绩。
14） 统计每门课程的选课人数和最高分。
select 课程号,count(学号),max(成绩)
from xs_kc
group by 课程号
15） 统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。
select 学号,count(课程号),max(成绩)
from xs_kc
group by 学号
order by count(课程号)
实验七 创建和使用索引
1) 给KC表的课程号属性上创建聚集索引K1，在XS_KC表的[学号，课程号]上创建复合索引IX_XS_KC。
create clustered index k1 on [KC](课程号)
create clustered index IX_XS_KC on [XS_KC](学号,课程号)
2)强制使用索引进行查询
select *
from kc with(index (k1))
实验八 创建并使用约束和默认值对象
1）建立一个默认值对象“DFO_出生日期”，其值为1980-1-1
Create default DFO_出生日期
As '1980-1-1'
2）将默认值对象绑定到XSQK表的出生日期列。
exec sp_bindefault DFO_出生日期,'xsqk.出生日期'
实验九 实现数据完整性
1)在XSQK表中创建一个规则X＿R，只能接收8位的数字，并邦定到电话号码列中。
create rule x_R
as
@a like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
exec sp_bindrule x_r,'xsqk.电话号码 '
2）XSQK表再插入数据，电话号码为“7768971”，“34ar”,检查完整性
参照完整性
1）给XSQK，KC，XS＿KC建立参照完整性，确定主码，外码。
alter table KC
add constraint skc primary key(课程号)
alter table xsqk
add constraint skc1 primary key(学号)
alter table xs_kc
add constraint skc2 primary key(学号,课程号)
alter table xs_kc
add constraint xs foreign key(学号) references xsqk(学号)
on
delete cascade
on update cascade
alter table xs_kc
add constraint xs1 foreign key(课程号) references kc(课程号)
on delete no action
on update noaction
2）设置SXQK表对于删除和更新操作是级连，KC表对于删除更新操作是拒绝。
3）在XS＿Kc表中分别插入两条记录，学号分别为“020208”和“020201”检查完整性。
4）更新XS＿KC，更改学号“020201”为“020206”，检查完整性。
5）删除XSQK表中的学号为“020201”的记录，更改学号为“020201”的记录为“020208”
实验十 用户自定义函数
1）创建一个函数，输入学生学号，要求出此学生所选课程的门数
create function n1
(@id int ) -- 参数
returns int -- 返回值类型
begin
declare @nu int
select @nu=count(*)
from xs_kc
where 学号=@id
return @nu -- 返回值
end
select dbo.n1('020101')
2）创建一个函数，输入教师姓名，要求输入此教师所授课程被选修的情况
create function table1
(@tname varchar(50))
returns table
return
(select *
from kc
where 教师=@tname and 课程号 in
(select 课程号
from xs_kc
))select *
from t('陈红')
3）创建一个函数，输入学号，输出这个学生选修的情况以及成绩的等级。
小于60 不及格 60－70 及格 70－80 中等 80－90 良好
90以上 优秀
create function m6
(@sno nchar(10))
returns @my_table table
(
sno nchar(10),
cno nchar(10),
grade nchar(10),
l nchar(10)
)
as
begin
insert @my_table select 学号,课程号, 成绩,l=
case
when 成绩 <60 then '不及格'
when 成绩>=60 and 成绩<70 then '及格'
when 成绩>=70 and 成绩<80 then '中等'
when 成绩>80 and 成绩<=90 then '良好'
else '优秀'
end
from XS_kc
where 学号=@sno
return
end
select *
from m6('020101')
实验十一 实现存储过程
1）建立一个存储过程，要求输入一个课程号，查询该课成绩不及格的学生的基本信息，包括学号、姓名、性别和联系电话信息，最后输出。
create proc mp4
@cno varchar(10)
as
select xsqk.学号,姓名,性别,联系电话
from xsqk,xs_kc
where xsqk.学号=xs_kc.学号 and 成绩<60 and 课程号=@cno
exec mp4 ‘001’
2)创建一个存储过程，要求输入教师号，输出该教师所教课程的课程的被选修的人数
create proc mp5
@tno varchar(10)
as
select count(*)
from kc,xs_kc
where kc.课程号=xs_kc.课程号 and 教师=@tno
exec mp5 '陈红'
3)执行所创建的二个存储过程
4) 删除新建的存储过程
Drop proc mp5
发布评论