创建班级表并使班级号为主键/唯一约束 create table dbo.BJ ( 班级号 char(6) not null primary key, 班级名 char(12) null, 辅导员 char(8) null, 专业名 char(12) null ) go 给学生表添加身份证号码属性,添加班级号属性并作为外键约束 alter table XS add 身份证号码 char(20) alter table XS add 班级号 char(6) constraint fk_bjh foreign key(班级号) references BJ
alter table XS add constraint sf_uk unique(身份证号码) 将姓名设置为唯一约束 alter table XS add constraint sf_xm unique(姓名)
性别是否为0/1 alter table XS add constraint ck_xb check ((性别='1') or (性别='0')) 成绩是否为[0,100] alter table CJ add constraint ck_cj check ((成绩>=0) and (成绩<=100)) 专业名是否为计算机,通信工程,测控之一 alter table KC add constraint ck_kcm check (课程名 in ('计算机','通信工程','测控'))
专业名默认值为计算机 alter table XS add constraint def_zym default '计算机' for 专业名 备注默认为当天日期 alter table xs add constraint aa default convert(char(30),getdate(),111) for 备注
create rule xs_rule as @range like'[0-3][0-3][0-9][0-9][0-9][0-9]' go exec sp_bindrule 'xs_rule','XS.学号' go
create view v_xs1 with encryption as select * from XS where 总学分>=50 and 专业名='计算机' with check option
create view v_xs with encryption as select XS.学号,姓名,专业名,课程名,成绩 from XS,KC,CJ where XS.学号=CJ.学号 and KC.课程号=CJ.课程号 and 总学分>=50 with check option
create procedure proc_info as select * from XS where 专业名='计算机' go execute proc_info
create procedure pro_student @学号 char(6) as select XS.学号,姓名,性别,出生时间,专业名,总学分,备注 from XS where XS.学号=@学号
drop view if exists v_xs
drop procedure if exists pro_student |
数据库实验三
最新推荐文章于 2024-10-08 17:39:57 发布