1 | 实验五:T-SQL(3) |
##1 给表student列Sno增加检查长度为8位的约束并测试。
###1.1 先建表1
2
3
4
5
6
7
8
9
10use Students;
create table Student
(
Sno char(8) not null primary key,
Sname char(8) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
###1.2 增加约束条件1
alter table Student add constraint ck_tudent_ssex check (len(Sno)=8)
###1.3 测试1
2//有效用例
insert into Student values('19130201','丁鹏','男',20,'Software')
1
2//无效用例
insert into Student values('191302021','王韵婷','女',20,'Software')
##2 给表student列ssex的输入限定为男、女两个值并测试。
###2.1 建表时,设定限制。1
2
3
4
5
6
7
8
9
10use Students;
create table Student
(
Sno char(8) not null primary key,
Sname char(8) null,
Ssex char(4) null check(ssex='男' or ssex='女'),
Sage int null,
Sdept char(10) null
)
###2.2 测试1
2//有效用例
insert into Student values('19130201','丁鹏','男',20,'Software');
1
2//无效用例
insert into Student values('19130202','王韵婷','1',20,'Software');
##3 给表sc列grade的输入限定为0到100并测试。
###3.1 建表时,设定限制。1
2
3
4
5
6
7
8
9use Students;
create table SC
(
Sno char(8) not null,
Cno int null,
Grade int null check(Grade >=0 and Grade<=100),
//或采用constraint ck_sc_grade check(Grade >=0 and Grade<=100)
)
###3.2 测试1
2//有效用例
insert into SC values('19130201',1,99);
1
2//无效用例
insert into SC values('19130202',1,199);
##4 给表sc的列增加外键约束并测试。
###4.1 建表1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22use Students;
create table Student
(
Sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
create table SC
(
Sno char(8) not null,
Cno int null,
Grade int null,
)
###4.2 增加外键1
alter table SC add constraint pk_sc_fk foreign key(Sno) references Student(Sno);
###4.3 测试1
2//有效用例
insert into SC values('19130202',1,99);
1
2//无效用例
insert into SC values('19130201',1,89);
##5 给表student增加列idcard表示身份证号并限定输入长度为18位,且最后一位奇数表示男,偶数表示女,这个值必须与ssex一致,并请测试。
###5.1 建表1
2
3
4
5
6
7
8
9
10use Students;
create table Student
(
Sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
###5.2 增加带约束条件的类1
2alter table Student add idcard char(18) null;
alter table Student add constraint pk_id_ck check(len(idcard)=18 and((CAST(right(idcard,1) AS INT )%2=1 and Ssex ='男') or (CAST(right(idcard,1) AS INT )%2=0 and Ssex ='女')));
###5.3 测试1
2//有效用例
insert into Student values('19130202','王韵婷','女',20,'Software','320682199509130002');
1
2//无效用例
insert into Student values('19130205','史逸凡','男',19,'Software','320682199509130004');