SQL练习

--以系统管理员身份登录到SQL Server服务器，并使用T-SQL语句实现以下操作；
--1. 将stu数据库中student表的sno定义为主键；
alter table [student] add constraint PK_student primary key (sno)
--2. 将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;
alter table [course] add constraint cno_pk primary key (cno)
--3. 为表course中的字段cname添加唯一值约束；
alter table [course] add constraint UQ_course_name unique (cname)
--alter table[course] drop constraint UQ_canme
--4. 将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
alter table[sc] drop constraint PK_sc
alter table[sc] add constraint PK_sc primary key(sno,cno)
--5. 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：
alter table[sc]
add constraint FK_sc_sno foreign key(sno) references student(sno)
on delete cascade
alter table[sc]
add constraint FK_sc_cno foreign key(cno) references course(cno)
on delete cascade
on update cascade
alter table [sc]
 drop constraint FK_sc_cno
--1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；
--2) 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；
--3) 修改course表cno字段值时，该字段在sc表中的对应值也应修改；
----4) 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；
--5) 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
--6. 定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
alter table [student] add constraint CK_sno1 check(sno like'[1-9][0][0][0-9][0-9][0-9][0-9][0-9][0-9]')
--7. 定义stu数据库中student表中学生年龄值在16-25范围内；
alter table student add constraint CK_age check(sage between 16 and 25)
--8. 定义stu数据库中student表中学生姓名长度在2-8之间；
alter table student add constraint CK_name_length check(sname like'__'|'___'|'____'|'_____'|'______'|'_______'|'________')
--9. 定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
alter table student add constraint CK_sex check(ssex like'男'|'女')
--10. 定义stu数据库student表中学生年龄值默认值为20；
alter table student add constraint DF_age default(20) for Sage
--11. 修改student表学生的年龄值约束可以为15-30范围内；
alter table student drop constraint CK_age
alter table student add constraint CK_age check(sage between 15 and 30)
--12. 删除上述唯一值约束、外键约束及check约束；
alter table [course] drop constraint UQ_course_name
alter table [student] drop constraint CK_sno1
alter table student drop constraint CK_age
alter table student drop constraint CK_name_length
--13.向sc表中插入或修改一条记录时，通过触发器检查记录学号字段的值在student表中是否存在，
--同时还要检查课程号的值是否存在若不存在，则取消插入或修改，否则插入成功；执行对sc的插入、修改操作，验证触发器的执行。

create trigger logic1
on sc after insert,update
as 
 if not exists(
    select sno,cno from inserted
    where sno=any(
        select sno from student
    )
    and cno=any(
        select cno from course
    )
 )
 begin
 rollback
 print'没有该记录'
 end;
insert into sc
values(201515001,12,67)
insert into sc
values(201515878,10,77)
--消息 3609，级别 16，状态 1，第 63 行
--事务在触发器中结束。批处理已中止。
--14.设计一更新触发器，当course表中的cno列修改时，激活该触发器同时更新sc表中的记录。
create trigger logic2 on course
after update
as
begin
declare @cno char(4)    
select @cno=cno from deleted
update sc
set sc.cno=(select cno from inserted)
where sc.cno=@cno
end;
update course
set cno=2
where cno=12
--执行结果：(6 行受影响)
--
--(1 行受影响)
--15.设计一触发器，约束数据库系统课程的课容量为80。
create trigger num_limit on sc
after insert
as if exists(
    select cno from sc
    where cno=(
        select cno from course
        where cname='数据库系统'
    )
    group by cno
    having count(sno)>80
)
begin
rollback
print '数据库系统即将超出80课容量，禁止插入！'
end;
--16.选做题：设计实例，验证after触发器与instead of触发器的异同。
create trigger test_1 on sc
after insert
as print '这是after触发器'
create trigger  test_2 on sc
instead of insert
as print '这是instead of触发器'
insert into sc
values(200515001,2,50);
drop trigger test_2
insert into sc
values (200515001,3,47);

 

--以系统管理员身份登录到SQL Server服务器，并使用T-SQL语句实现以下操作；--1. 将stu数据库中student表的sno定义为主键；alter table [student] add constraint PK_student primary key (sno)--2. 将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;alter table [course] add constraint cno_pk primary key (cno)--3. 为表course中的字段cname添加唯一值约束；alter table [course] add constraint UQ_course_name unique (cname)--alter table[course] drop constraint UQ_canme--4. 将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;alter table[sc] drop constraint PK_scalter table[sc] add constraint PK_sc primary key(sno,cno)--5. 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：alter table[sc]add constraint FK_sc_sno foreign key(sno) references student(sno)on delete cascadealter table[sc]add constraint FK_sc_cno foreign key(cno) references course(cno)on delete cascadeon update cascadealter table [sc] drop constraint FK_sc_cno--1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；--2) 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；--3) 修改course表cno字段值时，该字段在sc表中的对应值也应修改；----4) 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；--5) 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；--6. 定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；alter table [student] add constraint CK_sno1 check(sno like'[1-9][0][0][0-9][0-9][0-9][0-9][0-9][0-9]')--7. 定义stu数据库中student表中学生年龄值在16-25范围内；alter table student add constraint CK_age check(sage between 16 and 25)--8. 定义stu数据库中student表中学生姓名长度在2-8之间；alter table student add constraint CK_name_length check(sname like'__'|'___'|'____'|'_____'|'______'|'_______'|'________')--9. 定义stu数据库中student表中学生性别列中只能输入“男”或“女”；alter table student add constraint CK_sex check(ssex like'男'|'女')--10. 定义stu数据库student表中学生年龄值默认值为20；alter table student add constraint DF_age default(20) for Sage--11. 修改student表学生的年龄值约束可以为15-30范围内；alter table student drop constraint CK_agealter table student add constraint CK_age check(sage between 15 and 30)--12. 删除上述唯一值约束、外键约束及check约束；alter table [course] drop constraint UQ_course_namealter table [student] drop constraint CK_sno1alter table student drop constraint CK_agealter table student drop constraint CK_name_length--13.向sc表中插入或修改一条记录时，通过触发器检查记录学号字段的值在student表中是否存在，--同时还要检查课程号的值是否存在若不存在，则取消插入或修改，否则插入成功；执行对sc的插入、修改操作，验证触发器的执行。
create trigger logic1on sc after insert,updateas  if not exists(select sno,cno from insertedwhere sno=any(select sno from student)and cno=any(select cno from course) ) begin rollback print'没有该记录' end;insert into scvalues(201515001,12,67)insert into scvalues(201515878,10,77)--消息 3609，级别 16，状态 1，第 63 行--事务在触发器中结束。批处理已中止。--14.设计一更新触发器，当course表中的cno列修改时，激活该触发器同时更新sc表中的记录。create trigger logic2 on courseafter updateasbegindeclare @cno char(4)select @cno=cno from deletedupdate scset sc.cno=(select cno from inserted)where sc.cno=@cnoend;update courseset cno=2where cno=12--执行结果：(6 行受影响)----(1 行受影响)--15.设计一触发器，约束数据库系统课程的课容量为80。create trigger num_limit on scafter insertas if exists(select cno from scwhere cno=(select cno from coursewhere cname='数据库系统')group by cnohaving count(sno)>80)beginrollbackprint '数据库系统即将超出80课容量，禁止插入！'end;--16.选做题：设计实例，验证after触发器与instead of触发器的异同。create trigger test_1 on scafter insertas print '这是after触发器'create trigger  test_2 on scinstead of insertas print '这是instead of触发器'insert into scvalues(200515001,2,50);drop trigger test_2insert into scvalues (200515001,3,47);