Catalog
数据库实验六
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
--以系统管理员身份登录到SQL Server服务器,并使用T-SQL语句实现以下操作:
use student
--1.将stu数据库中student表的sno定义为主键;
alter table student
add 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 cname_uq
unique(cname)

--4.将数据库stu的表sc的sno及cno字段组合定义为主键,约束名称为sc_pk;
alter table sc
add constraint sc_pk
primary key(sno,cno)

--5.对于数据表sc的sno、cno字段定义为外码,使之与表student的主码sno及表course的主码cno对应,实现如下参照完整性:
--1)删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录;
--2)修改student表某记录的sno时,若sc表中与该字段值对应的有若干条记录,则拒绝修改;
--3)修改course表cno字段值时,该字段在sc表中的对应值也应修改;
--4)删除course表一条记录时,若该字段在在sc表中存在,则删除该字段对应的记录;
--5)向sc表添加记录时,如果该记录的sno字段的值在student中不存在,则拒绝插入;
alter table sc
add constraint sc_fk foreign key (sno) references student(sno) on delete cascade on update no action,
foreign key (cno) references course(cno) on delete cascade on update cascade

--6.定义check约束,要求学生学号sno必须为9位数字字符,且不能以0开头,第二三位皆为0;
alter table student
add constraint sno_ck
check(sno like '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9]')

--7.定义stu数据库中student表中学生年龄值在16-25范围内;
alter table student
add constraint sage_ck
check(sage between 16 and 25)

--8.定义stu数据库中student表中学生姓名长度在2-8之间;
alter table student
add constraint sname_ck
check(sname like '__'|'___'|'____'|'_____'|'______'|'_______'|'________')

--9.定义stu数据库中student表中学生性别列中只能输入“男”或“女”;
alter table student
add constraint ssex_ck
check(ssex in ('男','女'))

--10.定义stu数据库student表中学生年龄值默认值为20;
alter table student
add constraint sage_df
default 20 for sage

--11.修改student表学生的年龄值约束可以为15-30范围内;
alter table student
drop constraint sage_ck
alter table student
add constraint sage_ck
check(sage between 15 and 30)

--12.删除上述唯一值约束、外键约束及check约束;
alter table course
drop constraint cname_uq
alter table sc
drop constraint sc_fk
alter table student
drop constraint sno_ck,sname_ck,ssex_ck,sage_ck

use xsgl
--13.设计触发器实现如果一个学生转专业了,那么输出一条信息显示该学生各门课程的平均分。
create trigger dept_ch
on xs
for update
as
if update(专业)
begin
select cj.学号,avg(成绩)
from cj,inserted
where cj.学号 = inserted.学号
group by cj.学号
end

update xs
set xs.专业 = '信息管理'
where xs.学号 = '2006030101'

--14.设计触发器实现如果成绩被修改了20分以上,则输出提示信息“修改成绩超过20分,请慎重”。
create trigger grade_ck_tg
on cj
after update
as
begin
declare @pregrade numeric(18,0),@aftgrade numeric(18,0)
select @pregrade = 成绩 from inserted
select @aftgrade = 成绩 from deleted
if((@pregrade-@aftgrade)>20 or (@aftgrade-@pregrade)>20)
begin
print'修改成绩超过20分,请慎重'
end
end

update cj
set cj.成绩 = 89
where cj.学号 = '2006030101' and cj.课程号 = 'A001'

--15.在student表中增加一列total,表示学生选课总门数,初始值为0。
alter table xs
add total int default 0
--定义一个触发器,实现如下完整性约束:当向SC表插入选课记录时,自动更新student表对应学号的total值,考虑成批插入数据的情况。
create trigger sc_insert
on cj
after insert
as
declare @total int
select @total = total from xs
update xs
set total = @total+1

--16.设计一触发器,约束数据库系统课程的课容量为120。
create trigger contain
on cj
for insert,update
as
declare @num int
select @num = count(*) from deleted,kc where deleted.课程号 = kc.课程号 and kc.课程名='数据库系统'
if @num > 120
begin
rollback transaction
print'数据库课程人数已满'
end

--17.设有两个表:商品库存表(商品编号,商品名称,库存数量,库存单价,库存金额);商品销售表(商品编号,商品名称,购货商号,销售数量,销售单价,销售金额);设计一触发器实现如下业务规则:
--(1)保证在商品库存表中插入的数据,库存金额 = 库存数量 * 库存单价。
create trigger insert_商品库存表
on 商品库存表
for insert
as
update 商品库存表
set 库存金额 = 库存数量 * 库存单价
where 商品编号 in (select 商品编号 from inserted)

--(2)如果销售的商品不存在库存或者库存为零,则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
--建表并验证触发器的执行。
create trigger insert_商品销售表
on 商品销售表
for insert
as
begin transaction
if not exists (
select 库存数量
from 商品库存表
where 商品编号 in (select 商品编号 from inserted) )
begin
raiseerror('错误!该商品不存在库存,不能销售!',16,1)
rollback
return
end

if exists (
select 库存数量
from 商品库存表
where 商品编号 in (select 商品编号 from inserted) and 库存数量 <= 0 )
begin
raiseerror('错误!该商品库存小于等于0,不能销售!',16,1)
rollback
return
end

--18.建立教师表(教工编号,姓名,专业,职称,工资)和工资变化表(教工编号,原工资,新工资)
create table teacher(
tno char(10) primary key,
tname nchar(10),
tdept char(16),
tpro char(10),
tsala money
)
create table sala_change(
tno char(10),
tpresala money,
taftsala money
)

--设计触发器实现教授的工资不得低于4000元,如果低于4000元则自动改为4000元。
create trigger sala_tg
on teacher
for update,insert
as
declare @sala money
select @sala = tsala from teacher
if @sala < 4000
begin
update teacher
set tsala = 4000
update sala_change
set taftsala = 4000,tpresala = @sala
end

--19.使用第18题的两个表设计触发器实现如果教工的工资发生变化则向工资变化表插入一条记录,包含教工编号,原工资,新工资。
create trigger insert_if_change
on teacher
for update
as
if update(tsala)
begin
declare @tno char(10),@tpresala money,@taftsala money
select @tno = tno from teacher
select @tpresala = tsala from deleted
select @taftsala = tsala from inserted
insert into sala_change
values(@tno,@tpresala,@taftsala)
end
Author: Christopher Shen
Link: https://www.pasxsenger.com/2019/11/06/数据库实验六/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Donate
  • 微信
  • 支付寶