Sql Server上机实验答案

--以默认方式创建stu001数据库

create database stu001

--打开stu001数据库

use stu001

--创建学生表XS

create table xs

( 学号char(7) not null primary key,

姓名char(8) not null,

性别bit not null default 1,

出生日期smalldatetime not null,

班级char(5) default '41021' ,

总学分tinyint default 0,

备注text null

)

--创建课程表KC

create table kc

( 课程号char(3) not null primary key,

课程名char(20) not null,

开课学期tinyint not null default 1 check(开课学期>=1 and 开课学期<9 ),

学分tinyint not null default 0,

学时tinyint not null

)

--创建学生与课程表xs_kc

create table xs_kc

( 学号char(7) not null references xs(学号),

课程号char(3) not null references kc(课程号),

成绩 tinyint null default 0 check(成绩>=0 and 成绩<=100) ,

学分tinyint null default 0

primary key(学号,课程号)

)

--修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空

use stu001

alter table xs

alter column 出生日期datetime null

--删除学生与课程表（XS_KC）中的“学分”字段

alter table xs_kc drop constraint DF__xs_kc__学分__0AD2A005

alter table XS_kc drop column 学分

--修改学生情况表（XS），删除“班级”字段，增加一“专业”字段（varchar(20), 默认值为“计算机”）

alter table xs drop constraint DF__xs__班级__7E6CC920

alter table xs drop column 班级

alter table xs add 专业varchar(20) default '计算机'

--向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成数据

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4102101','王林',1,'1983-1-23','计算机',40,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4102102','吴荣华',0,'1984-3-28','计算机',44,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4202103','张强',1,'1981-11-19','电子',null,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4202110','王向荣',1,'1980-12-9','电子',50,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4202221','李丽',0,'1982-7-30','电子',null,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4302101','刘明',1,'1982-10-18','自控',38,NULL)

insert into xs(学号,姓名,性别,出生日期,专业,总学分,备注) values('4402130','叶凡',1,'1983-11-18','数学',46,'转专业学习')

insert into kc(课程号,课程名,开课学期,学时,学分) values('101','计算机基础',1,48,3)

insert into kc(课程号,课程名,开课学期,学时,学分) values('102','C语言',2,80,5)

insert into kc(课程号,课程名,开课学期,学时,学分) values('103','数据库',4,,4)

insert into kc(课程号,课程名,开课学期,学时,学分) values('201','离散数学',6,,4)

insert into kc(课程号,课程名,开课学期,学时,学分) values('202','计算机网络',7,,4)

insert into kc(课程号,课程名,开课学期,学时,学分) values('301','软件工程',6,,4)

insert into xs_kc(学号,课程号,成绩) values('4102101','101',80)

insert into xs_kc(学号,课程号,成绩) values('4102101','102',)

insert into xs_kc(学号,课程号,成绩) values('4102101','103',78)

insert into xs_kc(学号,课程号,成绩) values('4102102','101',57)

insert into xs_kc(学号,课程号,成绩) values('4102102','102',67)

insert into xs_kc(学号,课程号,成绩) values('4202103','103',90)

insert into xs_kc(学号,课程号,成绩) values('4202110','101',85)

insert into xs_kc(学号,课程号,成绩) values('4202110','102',91)

insert into xs_kc(学号,课程号,成绩) values('4202221','101',69)

insert into xs_kc(学号,课程号,成绩) values('4402130','101',78)

insert into xs_kc(学号,课程号,成绩) values('4402130','103',74)

--（一）简单查询

--1.查询XS表中各个同学的所有信息。

use stu001

select * from xs

--2.查询XS表中各个同学的姓名、专业名和总学分。

select 姓名,专业,总学分from xs

--3.查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num，name和mark。

select 学号num,姓名name,总学分mark from xs

--4.查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。

select DISTINCT 专业from xs

--5.查询XS表中各个同学的姓名、专业名和总学分，只返回结果集的前行。

select top 5 姓名,专业,总学分from xs

--6.查询XS表中每个学生的学号、姓名和年龄信息。

select 学号,姓名,year(getdate())-year(出生日期) 年龄from xs

--7.查询XS表中专业为“计算机”的同学的情况。

select * from xs where 专业='计算机'

--8.查询XS表中年出生的学生姓名和专业情况。

select 姓名,专业from xs where year(出生日期)=1983

--9.查询XS表中专业名为“计算机”或“电子”或“数学”的学生的情况。

select * from xs where 专业in('计算机','数学','电子')

--10.查询XS表中姓“张”或“王”或“李”且单名的学生的情况。

select * from xs where 姓名like '张_' or 姓名like '王_' or 姓名like '李_'

--11.查询XS表中总学分尚未确定的学生情况。

select * from xs where 总学分is null

--12.查询XS表中专业为“电子”且总学分尚未确定的学生情况。

select * from xs where 专业='电子'and 总学分is null

--13.从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。

select * from xs

order by 总学分desc,学号asc

--14.查询年龄最小的前三个同学的姓名、专业和年龄。

select top 3 姓名,专业,year(getdate())-year(出生日期) 年龄

from xs

order by year(getdate())-year(出生日期) asc

--（二）数据汇总

--1.求选修了“”课程的学生的平均成绩。

select avg(成绩) 平均成绩

from xs_kc

where 课程号='101'

--2.求选修了“”课程的学生的最高分和最低分。

select max(成绩) 最高分,min(成绩) 最低分

from xs_kc

where 课程号='102'

--3.求学号为“”学生的总成绩。

select sum(成绩) 总成绩

from xs_kc

where 学号='4102101'

--4.求专业为“计算机”的学生的总人数。

select count(学号) 总人数

from xs

where 专业='计算机'

--5.求选修了“”课程的学生的人数。

select count(学号) 人数

from xs_kc

where 课程号='101'

--6.求选修了任意一门课程的学生的人数。

select count(distinct 学号) 人数

from xs_kc

--7.统计各个专业的学生数。（按专业分组）

select 专业,count(专业) 学生人数from xs

group by 专业

--8.统计各个专业的男女生人数。格式如下：

--专业 性别 人数

--…… …… ……

select 专业,性别,count(学号) 人数

from xs

group by 专业,性别

--9.查找平均成绩在分以上的学生的学号和平均成绩。

select 学号,avg(成绩) 平均成绩

from xs_kc

group by 学号

having avg(成绩)>80

--10.查找选修的课程中超过门成绩在分以上的学生的学号和成绩高于分的门数。格式如下：

--学号 成绩高于分的门数

--……

select 学号,count(课程号) 成绩高于分的门数

from xs_kc

where 成绩>=80

group by 学号

having count(课程号)>=2

--（三）连接查询

--1.查询每个学生的基本情况及选修的课程情况，格式如下：

--学号 姓名 专业 课程号 成绩

--…… …… …… …… ……

--等值连接

select xs.学号,xs.姓名,xs.专业,xs_kc.课程号,xs_kc.成绩

from xs join xs_kc on xs.学号=xs_kc.学号

--左外连接

select xs.学号,xs.姓名,xs.专业,xs_kc.课程号,xs_kc.成绩

from xs left outer join xs_kc on xs.学号=xs_kc.学号

--右外连接

select xs.学号,xs.姓名,xs.专业,xs_kc.课程号,xs_kc.成绩

from xs right outer join xs_kc on xs.学号=xs_kc.学号

--完全连接

select xs.学号,xs.姓名,xs.专业,xs_kc.课程号,xs_kc.成绩

from xs full outer join xs_kc on xs.学号=xs_kc.学号

--2.查询选修了课程号为的每个学生的姓名及成绩。

select xs.姓名,xs_kc.成绩

from xs join xs_kc on (xs.学号=xs_kc.学号)and (xs_kc.课程号='101')

--3.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。

select xs.学号,xs.姓名,xs_kc.成绩

from xs join xs_kc on xs.学号=xs_kc.学号join kc on xs_kc.课程号=kc.课程号and xs.专业='计算机' and kc.课程名='计算机基础'

--4.查询选修了课程号为的每个学生的基本情况及成绩，若学生未选修号课程，也包括其情况。

select xs.*, xs_kc.课程号,xs_kc.成绩

from xs left outer join xs_kc on xs.学号=xs_kc.学号 and xs_kc.课程号='101'

--5.查询生日相同的学生的姓名和出生日期。格式如下

--姓名 姓名 出生日期

--…… …… ……

--其中：“姓名”和“姓名”中的姓名不能相同。

select a.姓名姓名,b.姓名姓名,a.出生日期

from xs a join xs b on a.出生日期=b.出生日期and a.姓名<>b.姓名

--6.显示每门课程的平均成绩并显示总平均成绩。格式如下：

--课程名 平均成绩

--…… ……

--总平均 ……

select CASE WHEN (GROUPING(a.课程名) = 1) THEN '总平均'

ELSE a.课程名

END

AS 课程名,avg(b.成绩) as '平均成绩'

from kc a join xs_kc b on a.课程号=b.课程号

group by a.课程名 WITH rollup

--7.查询各个专业各门课程的平均成绩。格式如下

--专业 课程名 平均成绩

--…… …… ……

select a.专业,c.课程名,b.成绩

from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号

select a.专业,c.课程名,avg(b.成绩) as '平均成绩'

from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号

group by a.专业,c.课程名

--8.查询“计算机”专业平均成绩在分以上的学生的学号、姓名和平均成绩。

select b.学号,a.姓名,avg(成绩) as '平均成绩'

from xs a join xs_kc b on ( a.学号=b.学号) and (a.专业='计算机')

group by b.学号,a.姓名having avg(成绩)>82

--（四）子查询的使用

--1.查询平均成绩高于号课程平均分的课程号及平均成绩。

select 课程号,avg(成绩) '平均成绩'

from xs_kc

group by 课程号

having avg(成绩)>(select avg(成绩) from xs_kc

group by 课程号

having 课程号='101' )

--2.查询“计算机基础”成绩的及格率。

select (select count(a.学号)*100 from xs_kc a join kc b on a.课程号=b.课程号and a.成绩>=60 and b.课程名='计算机基础')

/(select count(a.学号) from xs_kc a join kc b on a.课程号=b.课程号and b.课程名='计算机基础') as 计算机基础成绩的及格率

--或

select (select count(学号)*100 from xs_kc where 成绩>=60 and 课程号in (select 课程号from kc where 课程名='计算机基础') )

/(select count(学号) from xs_kc where 成绩>=60 and 课程号in (select 课程号from kc where 课程名='计算机基础')) as 计算机基础成绩的及格率

--3.查询号课程及格的学生的基本情况。

select * from xs

where 学号in (select 学号from xs_kc where 课程号='101' and 成绩>=60)

--4.查找比所有计算机专业的学生年龄都大的学生的情况。

select * from xs

where 专业<>'计算机' and 出生日期<(select min(出生日期) from xs where 专业='计算机')

--5.查找未选修“C语言”课程的学生的情况。(嵌套子查询)

select * from xs

where 学号not in( select 学号from xs_kc where 课程号in (select 课程号from kc

where 课程名='C语言' ))

--6.查询每个专业年龄最小的学生的基本信息。（相关子查询）

select *

from xs a

where 出生日期in

( select max(b.出生日期)

from xs b where a.专业=b.专业

group by 专业

)

--7.查找选修了“计算机基础”课程的学生的情况。

select *

from xs

where 学号in (select 学号from xs_kc where 课程号=(select 课程号from kc where 课程名='计算机基础'))

--8.查询“计算机基础”成绩高于平均分的学生的学号，姓名和成绩，并按“计算机基础”成绩从高到低排序。

select a.学号,b.姓名,a.成绩

from xs_kc a join xs b on a.学号=b.学号

where a.课程号=(select 课程号from kc where 课程名='计算机基础')and 成绩>

(select avg(成绩)

from xs_kc

where 课程号in(select 课程号from kc where 课程名='计算机基础'))

order by a.成绩asc

--9.查询“计算机基础”成绩及格的学生的学号、姓名、性别和专业情况。

select 学号,姓名,性别,专业

from xs

where 学号in (select 学号from xs_kc where 成绩>=60 and 课程号=(select 课程号from kc where 课程名='计算机基础'))

--10.在XS_KC表中查询其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息。

select *

from xs_kc

where (学号NOT in (select 学号from xs) ) or(课程号NOT in (select 课程号from kc) )

--11.查询选修全部课程的学生的基本信息。

select *

from xs

where 学号in( select 学号

from xs_kc

group by 学号

having count(distinct 课程号)=(select count(课程号) from kc ))

--12.查询至少选修了“”同学选修课程的学生的基本信息。

select *

from xs

where 学号in( select 学号from xs_kc where 课程号='102')

--（五）数据的插入、删除和修改

--1.创建表XS1（包括学号、姓名、性别和出生日期字段，各个字段属性自定），然后使用SELECT子句向表XS1中插入多行数据（专业为“电子”的学生数据）。

select 学号,姓名,性别,出生日期into xs1 from xs where 专业='电子'

--2.修改XS1表的结构，增加个“平均成绩”字段，并根据XS_KC表修改每个学生的“平均成绩”。

use stu001

alter table xs1 add 平均成绩tinyint

update xs1

set 平均成绩=(select avg(成绩) from xs_kc group by 学号having xs1.学号=xs_kc.学号)

--3.将XS表中计算机专业的学生的总学分增加分。

update xs

set 总学分=总学分+10 where 专业='计算机'

--4.将XS表中学号为“”的同学的总学分增加分,备注改为“提前修完一门课程”。

update xs

set 总学分=总学分+4 ,备注='提前修完一门课程'

where 学号='4102101'

--5.将XS_KC表中学号为“”的同学的“计算机基础”课程的成绩增加分。

update xs_kc

set 成绩=成绩+10

where 学号='4102101' and 课程号=(select 课程号from kc where 课程名='计算机基础')

--6.修改XS1表的结构，增加个“总学分”字段，并使其值为该学生所学各门功课的学分之和。

alter table xs1 add 总学分tinyint

update xs1

set 总学分=(select sum(学分)

from xs_kc join kc on xs_kc.课程号=kc.课程号group by 学号having xs1.学号=xs_kc.学号)

--7.将XS1表中总学分小于分的学生数据删除。

delete from xs1 where 总学分<10

--8.将XS_KC表中“数据库”课程的所有成绩信息删除。

delete from xs_kc where 课程号in(select 课程号from kc where 课程名='数据库')

--9.将XS_KC表中其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息删除。

delete from xs_kc where ( 课程号not in(select 课程号from kc)) or (学号not in(select 学号from xs))

--10.删除XS1表中前两个同学的基本信息。

delete top(2) xs1

--11.删除XS1表中的所有数据。

delete from xs1

--（六）视图操作

--1.使用SQL语句创建视图V_SCORE1，使其显示计算机专业学生的所有基本信息，并保证以后对该视图的修改都要满足专业为“计算机”这个条件。

use stu001

go

create view V_SCORE1

as

select *

from xs

where 专业='计算机' WITH CHECK OPTION

--2.使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课程名称及其成绩。

use stu001

go

create view V_SCORE2

as

select a.学号,a.姓名,c.课程名,b.成绩

from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号

--3.使用T-SQL语句修改视图V_SCORE2，使其显示每个专业、每门课程的平均成绩，

结果格式为：

--专业 课程名 平均成绩

--…… …… ……

alter view V_SCORE2

as

select a.专业,c.课程名,avg(成绩) as 平均成绩

from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号

group by a.专业,c.课程名

SELECT * FROM V_SCORE2

--4.通过V_SCORE2查看计算机专业每门课程的平均成绩。

select 专业,课程名,平均成绩

from V_SCORE2

where 专业='计算机'

--5.通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：

--专业 平均成绩

--…… ……

select 专业,avg(平均成绩) as 平均成绩

from V_SCORE2

group by 专业

--6.使用视图V_SCORE1向其基表XS表中输入数据（数据自定），并观察什么样的数据可以插入，什么样的数据不可以插入，并解释为什么。

插入的专业必须是计算机

--7.使用SQL语句删除视图V_SCORE1。

Drop View V_SCORE1

--（七）数据完整性

--1.用户要求：XS表的学号列、KC表的课程号列、XS_KC表的学号和课程号列必须惟一且不能为空，请问如何解决该问题？请写出一种具体的解决方法。

设置主键约束

--2.用户要求：XS表中的学生的专业只能是“计算机”，“数学”，“电子”中的任意一个，请问如何解决该问题？请写出一种具体的解决方法。

--检查约束

alter table xs with nocheck add constraint ys1 check (专业in('计算机' ,'数学' ,'电子'))

--3.用户要求：XS表中的学生的年龄必须在岁到岁之间，请问如何解决该问题？请写出一种具体的解决方法。

--检查约束

alter table xs with nocheck add constraint ys2 check (year(getdate())-year(出生日期) between 18 and 30)

--4.用户要求：KC表中的课程号代表课程的编号，编号的每一位只能由—的数字组成，请问如何解决该问题？请写出一种具体的解决方法。

alter table kc with nocheck add constraint ys3 check(课程号like '[0-9][0-9][0-9]')

--5.用户要求：在输入XS表中的数据时，若“专业”列没有提供数据，系统应自动取值“计算机”，请问如何解决该问题？请写出一种具体的解决方法。

--专业默认为\"计算机\"

alter table xs add constraint df_专业 default '计算机' for 专业

--6.用户要求：XS_KC表中的成绩列的取值只能为—之间的整数，且在用户没有输入数据时自动取值，请问如何解决该问题？请写出一种具体的解决方法。

alter table xs_kc add constraint ys3 default 0 for 成绩

alter table xs_kc with nocheck add constraint ys4 check(成绩between 0 and 100)

--7.编写语句为XS_KC表的“学号”列添加外键约束，该约束参照XS表的主键“学号”列。该约束用于实现何种完整性？向具有该约束的表中添加数据或修改数据时，应注意什么？

alter table xs_KC add constraint ys5 foreign key(学号) references xs(学号)

--8.用户要求：XS_KC表中的“课程号”列的取值应依赖于KC表中“课程号”的取值，且当KC表中某个“课程号”的取值发生改变时，XS_KC表中的对应的“课程号”也应级联更新，当KC表中某个“课程号”被删除时，XS_KC表中对应该“课程号”的数据也应级联删除，请问如何解决该问题？请写出具体的解决方法。

alter table xs_KC add constraint ys7 foreign key(课程号) references kc(课程号) ON DELETE CASCADE ON UPDATE CASCADE

--9.创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表中是否存在，检查该记录的课程号在KC表中是否存在，若有一项为否，则不允许插入。

CREATE TRIGGER t_XS_KC1

ON XS_KC

AFTER INSERT

AS

DECLARE @xh CHAR(7)

DECLARE @cch CHAR(3)

SELECT @xh=学号,@cch=课程号FROM INSERTED

IF(NOT EXISTS(SELECT * FROM xs WHERE 学号=@xh)) OR

(NOT EXISTS(SELECT * FROM KC WHERE 课程号=@cch))

BEGIN

ROLLBACK TRANSACTION

RAISERROR('学号或课程号不存在',16,1)

END

insert into xs_kc values('4102101','222',40)

insert into xs_kc values('4102111','101',40)

--10.创建一触发器，当在XS表中删除一个学生的基本信息时，级联删除该学生的学习信息（即该学生在XS_KC表中的数据）。

CREATE TRIGGER t_XS1

ON XS

AFTER delete

AS

DECLARE @xh CHAR(7)

SELECT @xh=学号FROM DELETED

DELETE FROM XS_KC WHERE 学号=@xh

delete from xs where 学号='4102101'

--11.在XS_KC表上创建一触发器，若对学号列和课程号列修改,且修改后的学号或课程号在XS表和KC表中不存在,则给出提示信息,并取消修改操作。

CREATE TRIGGER t_XS_KC2

ON XS_KC

AFTER UPDATE

AS

BEGIN

IF UPDATE(学号) or UPDATE(课程号)

BEGIN

IF not EXISTS(SELECT xs.学号FROM xs,inserted WHERE xs.学号=inserted.学号) or not EXISTS(SELECT kc.课程号FROM kc,inserted WHERE kc.课程号=inserted.课程号)

BEGIN

ROLLBACK TRANSACTION

RAISERROR('学号或课程号不存在，不能更改',16,1)

END

END

END

--12.在XS_KC表上创建一触发器，当对学生的学习成绩进行插入或修改时，根据学习成绩级联更新该学生在XS表中的总学分

--（对于插入操作，如果成绩及格，该学生的总学分应为原来的总学分加上该门课程的规定学分，否则总学分不变；

--对于修改操作，若原成绩不及格而修改后的成绩及格，则该学生的总学分应为原来的总学分加上该门课程的规定学分，

--若原成绩及格而修改后的成绩不及格，则该学生的总学分应为原来的总学分减去该门课程的规定学分）。

CREATE TRIGGER t_XS_KC3

ON XS_KC

AFTER insert,update

AS

IF UPDATE(成绩)

BEGIN

declare @cj tinyint,@cj1 tinyint,@cj2 tinyint,@xf tinyint

IF EXISTS(SELECT * from inserted) and not EXISTS(SELECT * from deleted)

BEGIN

select @cj=成绩from inserted

if (@cj>60)

begin

select @xf=学分from kc where 课程号=(select 课程号from inserted)

update xs set 总学分=总学分+@xf

end

END

if EXISTS(SELECT * from inserted) and EXISTS(SELECT * from deleted)

BEGIN

select @cj1=成绩from inserted

select @cj2=成绩from deleted

select @xf=学分from kc where 课程号=(select 课程号from inserted)

if @cj2<60 and @cj1>=60

begin

update xs set 总学分=总学分+@xf

end

if @cj2>=60 and @cj1<60

begin

update xs set 总学分=总学分-@xf

end

end

END

--13.创建一视图，该视图包括学生的学号、姓名、性别、出生日期、专业、课程号和成绩，现在该视图上创建INSTEAD OF触发器，使通过该视图可以对XS表和XS_KC表中的数据进行输入。

create view V_SCORE3

as

select xs.学号,姓名,性别,出生日期,专业,xs_kc.课程号,xs_kc.成绩

from xs join xs_kc on xs.学号=xs_kc.学号

CREATE TRIGGER t_xs_kc

ON V_SCORE3

instead of insert

AS

begin

insert into xs(学号,姓名,性别,出生日期,专业) select 学号,姓名,性别,出生日期,专业from inserted

insert into xs_kc(学号,课程号,成绩) select 学号,课程号,成绩from inserted

end

--（八）数据库编程

--1．使用游标实现如下功能：显示每个学生的学号、姓名、各门功课的成绩与平均成绩。格式如下：

--学号 姓名 课程名称 成绩

--…… …… …… ……

DECLARE cursor1 CURSOR

FOR

select a.学号,a.姓名,c.课程名,b.成绩 from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号

OPEN cursor1

FETCH NEXT FROM cursor1

declare @xh char(7),@xm char(8),@ccm char(20),@cj char(5)

FETCH NEXT FROM cursor1 into @xh ,@xm,@ccm,@cj

print '学号 姓名 课程名称 成绩'

WHILE @@FETCH_STATUS=0

begin

print @xh+' '+@xm+' '+@ccm+' '+@cj

FETCH NEXT FROM cursor1 into @xh ,@xm,@ccm,@cj

end

CLOSE cursor1

DEALLOCATE cursor1

--2．使用游标实现如下功能：修改XS表中“总学分”字段的值，使其为该学生所学各门功课的学分之和。

DECLARE cursor2 CURSOR

FOR

SELECT 学号,sum(学分) as 总学分from xs_kc,kc where xs_kc.课程号=kc.课程

号 group by 学号

DECLARE @xh CHAR(7)

DECLARE @xf tinyint

OPEN cursor2

FETCH NEXT FROM cursor2 INTO @xh, @xf

WHILE @@FETCH_STATUS=0

BEGIN

UPDATE xs SET 总学分=@xf WHERE 学号=@xh

FETCH NEXT FROM cursor2 INTO @xh, @xf

END

CLOSE cursor2

DEALLOCATE cursor2

--3．创建一存储过程proc1，显示指定学生指定课程的成绩，然后执行该存储过程（用两种方法执行）。

CREATE PROCEDURE proc1

@xh CHAR(7),

@cch CHAR(3)

AS

SELECT * from xs_kc where 学号= @xh and 课程号=@cch

exec proc1 '4102101','102'

exec proc1 @xh='4102101',@cch=102

--4．创建一存储过程proc2，显示其指定课程的成绩在指定范围内的学生学号、姓名、课程名和成绩，并返回其人数，然后执行该存储过程。（用两种方法执行）。

CREATE PROCEDURE proc2

@cch CHAR(3)

AS

declare @zrs int

select a.学号,a.姓名,c.课程名,b.成绩 from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号and b.课程号=@cch

select @zrs=count(*) from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号and b.课程号=@cch

return @zrs

declare @zrs int

exec @zrs=proc2 101

select @zrs

--5．创建一存储过程xs_insert,用于实现往xs表中插入一条记录，并返回执行状态码信息：如果插入成功，则返回；否则，则返回。

CREATE PROCEDURE xs_insert

(@学号char(7), @姓名char(8) ,@性别bit ,@出生日期smalldatetime ,@总学分

tinyint ,@备注text,@专业varchar(20))

AS

insert into xs values(@学号,@姓名,@性别,@出生日期,@总学分,@备注,@专业)

if @@ROWCOUNT=0

return 1

else return 0

--6．创建一存储过程xs_update,用于对指定学号指定课程名称的学生成绩进行修改。

CREATE PROCEDURE xs_update

(@xh char(7),@ccm char(20),@cj tinyint)

AS

declare @cch char(3)

select @cch=课程号from kc where 课程名=@ccm

update cs_kc set 成绩=@cj where 学号=@xh and 课程号=@cch

--7．创建一存储过程，用于统计每个专业计算机基础成绩在各个分数段的学生人数。分数段划分为：低于分，～分，～分，～分，～分。

--专业 低于 60～ 70～ 80～ 90～

--计算机 0 3 7 5 4

--…… …… …… …… …… ……

-- CREATE PROCEDURE xs_update

-- as

-- select a.专业,b.成绩 from xs a join xs_kc b on a.学号=b.学号join kc c on b.课程号=c.课程号and c.课程名='计算机基础'

--（九）数据库的安全性管理

--1.查看所使用的SQL SERVER服务器的安全验证模式。

右击服务器实例名，选择“属性”，单击“安全性”查看

--2.创建一SQL SERVER认证模式的登录帐号，并使该帐号成为“Database Creators”服务器角色的成员。请问，此时该登录帐号具有什么权限？

CREATE LOGIN newlogin with password='123456'

EXEC Sp_addsrvrolemember 'newlogin','dbcreator'

能够创建数据

--3.创建一Windows NT认证模式的登录帐号，并使该帐号成为STUxxxx（学生管理）数据库的DB_Owner角色的成员。请问，此时该登录帐号具有什么权限？

CREATE LOGIN [liuzc\\winlogin] from windows--[liuzc\\winlogin]根据计算机用户名的实际名称进行修改。

create new for login [liuzc\\winlogin] --[liuzc\\winlogin]根据计算机用户名的实际名称进行修改

Exec sp_addrolemember 'DB_Owner','new'

能够在该库范围内任意操作

--4.用创建的新登录帐号登录服务器，对STUxxxx（学生管理）数据库进行访问，此时你能对数据库进行什么操作？为什么？

--5.用原来的登录帐号登录服务器，为STUxxxx（学生管理）数据库创建一GUEST用户，并使GUEST用户具有对STUxxxx（学生管理）数据库中XS表和KC表的SELECT权限。然后，用创建的新登录帐号登录服务器，对STUxxxx（学生管理）数据库进行访问，此时你能对数据库进行什么操作？为什么？

--6.用原来的登录帐号登录服务器，为STUxxxx（学生管理）数据库添加一新的用户（对应的登录帐号为创建的新登录帐号），授予该用户具有在STUxxxx（学生管理）数据库中创建视图的权限和对XS、KC和XS_KC表的SELECT权限，并拒绝其创建表的权限和对XS、KC和XS_KC表的修改、删除、插入的权限。然后，用新登录帐号登录服务器，验证所设置权限的正确性。

--7.用原来的登录帐号登录服务器，把以上的权限设置用数据库角色来实现，并验证设置权限的正确性。