SQL Server 创建学生管理系统

创建一个学生管理系统，完成题目要求

1、新建数据库：PXSCY

create database PXSCJ;

2、建表确定属性：XSB、KCB、CJB

3、设计每个表的实体完整性：键、索引

4、设计每个表的域完整性：CHECK语句

5、建立表之间的参照完整性：XSB与CJB，KCB与CJB

-- 学生表
create table xsb(
    学号   char(6)   primary key   check(学号 like '[0][8][1][12][0-9][0-9]')   not null,
    姓名   char(8)   not null,
    性别   bit   default 1   not null,
    出生日期   date   check(出生日期 >'1980-01-01'),
    专业   char(12)   check(专业 in('计算机','通信工程')),
    总学分   int   default 0   check(总学分<=160 and 总学分>=0),
    备注   varchar(500)
);

-- 课程表
create table kcb(
    课程号   char(3)   primary key   check(课程号>='100'and 课程号<='299')   not null,
    课程名   char(16)   check(课程名 in('计算机基础', '程序设计与语言', '离散数学'))  not null,
    开课学期   tinyint   default '1'   check(开课学期>=1 and 开课学期<=8),
    学时   tinyint   check(学时>=1 and 学时<=80),
    学分   tinyint   check(学分>=1 and 学分 <=10)   not null
);

-- 成绩表
create table cjb(
    学号   char(6)   check(学号>=0 and 学号<='100')   not null,
    课程号   char(3)   not null,
    成绩   int   default 0,
    primary key (学号, 课程号),
    foreign key(学号) references xsb(学号),
    foreign key(课程号) references  kcb(课程号)
);

6、输入表数据：增加、删除、修改记录

-- 学生表添加记录
insert into xsb (学号, 姓名, 性别, 出生日期, 专业, 总学分, 备注)
values ('081101', '王林', 1, '1990-02-10', '计算机', 50, null),
       ('081103', '王燕', 0, '1989-10-06', '计算机', 50, null),
       ('081108', '林一帆', 1, '1989-08-05', '计算机', 52, '已提前修完一门课'),
       ('081202', '王林', 1, '1989-01-29', '通信工程', 40, '有一门课不及格，待补考'),
       ('081204', '马琳琳', 0, '1989-02-10', '通信工程', 42, null);

-- 课程表添加记录
insert into kcb (课程号, 课程名, 开课学期, 学时, 学分)
values ('101', '计算机基础', 1, 80, 5),
       ('102', '程序设计与语言', 2, 68, 4),
       ('206', '离散数学', 4, 68, 4);

-- 成绩表添加记录
insert into cjb (学号, 课程号, 成绩)
values ('081101', '101', 80),
       ('081101', '102', 78),
       ('081101', '206', 76),
       ('081103', '101', 62),
       ('081103', '102', 70),
       ('081108', '101', 85),
       ('081108', '102', 64),
       ('081108', '206', 87),
       ('081202', '101', 65),
       ('081204', '101', 1);

-- 修改 xsb 数据：学号081202的姓名改王琳
update xsb set 姓名='王琳' where 学号='081202';

-- 修改 cjb 数据：学号081204成绩+90
update cjb set 成绩=成绩+90 where 学号='081204';

-- 删除 cjb 数据：删除学号081204的成绩
delete from cjb where 学号='081204';

-- 数据恢复
insert into cjb values ('081204', '101', 91);

7、进行查询操作

7.1、查询计算机专业的学生信息

select * from xsb where 专业='计算机';

7.2、查询CJB表中的学号，课程号，成绩，其中成绩空显示“尚未考试”，<60不及格， =<60---<70及格，=<70---<80中，=<80---<90良，=<90---<100优秀。列标题别名为“成绩等级”

select 学号, 课程号, 成绩等级=
                case
                    when 成绩 is null then '尚未考试'
                    when 成绩<60 then '不及格'
                    when 成绩>=60 and 成绩<70 then '及格'
                    when 成绩>=70 and 成绩<80 then '中'
                    when 成绩>=80 and 成绩<90 then '良'
                    when 成绩>=90 and 成绩<100 then '优'
                    else '成绩无效'
                end
from cjb;

7.3、对XSB表选择姓名，专业和总学分，返回结果集的前6行

select top 6 姓名, 专业, 总学分 from xsb;

7.4、输出成绩的max，min，sum，avg，count五个函数，写出合适的列名

select count(学号) 成绩总数, max(成绩) 最大成绩, min(成绩) 最小成绩, sum(成绩) 总成绩, avg(成绩) 平均成绩 
    from cjb;

7.5、查询XSB表中姓“王”且双名的学生情况

select * from xsb where 姓名 like '王_';

7.6、查询未选修离散数学的学生情况

select * from xsb where 学号 not in (
    select 学号 from cjb where 课程号=(
        select 课程号 from kcb where 课程名='离散数学'
        )
    );

7.7、查询206号课程成绩不低于101号课程最低成绩的学生学号

SELECT 学号 FROM CJB WHERE 课程号='206' 
AND 成绩 !<(SELECT MIN(成绩) FROM CJB WHERE 课程号='101')

7.8、查找选修了206号课程且成绩在80分以上的学生姓名及成绩

select xsb.学号, xsb.姓名, cjb.成绩 from xsb, cjb
    where xsb.学号=cjb.学号 and cjb.课程号='206' and cjb.成绩>80;

7.9、查询每个同学选了几门课，并输出选修大于2的记录

select 学号, count(课程号) as '课程数' from cjb group by 学号;
select 学号, count(课程号) as '课程数' from cjb group by 学号 having count(课程号)>2;

7.10将计算机专业的学生的“计算机基础”课程成绩按照降序排列，成绩相同的的再按姓名升序排列

select xsb.学号, xsb.姓名, xsb.专业, cjb.成绩 from xsb, kcb, cjb
    where xsb.学号=cjb.学号 and kcb.课程号=cjb.课程号
        and xsb.专业='计算机' and kcb.课程名='计算机基础'
            order by cjb.成绩 desc, xsb.姓名 asc;

8、表上建立视图

8.1、在XSB上创建计算机专业的视图

create view cs_xs as
    select * from xsb where 专业='计算机';

8.2、使用XSB和CJB创建计算机专业的成绩视图

create view cs_kc as
    select 成绩 from xsb, cjb
    where xsb.学号=cjb.学号 and xsb.专业='计算机'
    with check option;

9、游标

9.1、建立游标指向选修计算机基础课程的课程号、课程名、成绩信息

declare xs_cur1 cursor
    local scroll scroll_locks
    for select kcb.课程号, kcb.课程名, cjb.成绩
        from cjb, kcb
        where kcb.课程号=cjb.课程号 and 课程名='计算机基础';

9.2、使用游标输出所有行

declare xs_cur1 cursor
    local scroll scroll_locks
    for select kcb.课程号, kcb.课程名, cjb.成绩
        from cjb, kcb
        where kcb.课程号=cjb.课程号 and 课程名='计算机基础'
open xs_cur1
    fetch first from xs_cur1
    while(@@fetch_status=0)
        begin
            fetch next from xs_cur1
        end

9.3、使用游标把数据集的成绩加10

declare xs_cur2 cursor
    local scroll scroll_locks
    for select 学号, 姓名, 总学分
        from xsb
    for update of 总学分
open xs_cur2
    declare  @xh  char(6)
    declare  @xm  char(6)
    declare  @zxf  int
    fetch first from xs_cur2 into @xh, @xm, @zxf
    while(@@FETCH_STATUS=0)
        begin
            print '学号:' + @xh
            print '姓名:' + @xm
            print '总学分:' + CAST(@zxf+10 AS CHAR(2))
            print '--------------'
            fetch next from xs_cur2 into @xh, @xm, @zxf
        end

9.4、使用游标删除数据集的所有行

declare xs_cur3 cursor
    local scroll scroll_locks
    for select 学号, 姓名, 总学分
        from xsb
    for update of 总学分
open xs_cur3
    fetch first from xs_cur3
    while(@@fetch_status=0)
        begin
            delete from xsb where current of xs_cur3
            fetch next from xs_cur3
        end

10、将课程号为101、学号为081101的成绩循环修改到大于等于94，每次加5，并输出循环次数

use PXSCY
declare @one int = 0;
while (select 成绩 from cjb where 课程号='101' and 学号='081101') < 94
    begin
        update cjb set 成绩=成绩+5 where 课程号='101' and 学号='081101'
        set @one += 1
    end
select @one as '循环次数';

记录

** 2022 - 06 - 09**