drop database if exists StudentDB;
create database StudentDB;
use StudentDB;
create table StudentInfo
(
StuName varchar(8) not null,
StuNo char(6) not null primary key,
StuSex char(2) not null default '男',
StuAge tinyint not null,
StuSeat tinyint not null,
StuAddress varchar(50) not null default '地址不详',
-- constraint PK_StudentInfo_StuSeat primary key (StuNo),
constraint CK_StudentInfo_StuSex check(StuSex in('男','女')),
constraint CK_StudentInfo_StuNo check(StuNo like 's253[0-9][0-9]'),
constraint CK_StudentInfo_StuAge check(StuAge < 40 and StuAge > 15)
-- constraint CK_StudentInfo_StuSeat check(StuSeat < 30 and StuSeat > 0)
-- constraint DF_StudentInfo_StuAddress default '地址不详'
);
insert into StudentInfo values('张秋丽','s25301','大',18,1,'北京海淀'),
('李斯文','s25303','男',22,2,'北京海淀'),
('李文才','s25302','男',85,3,'地址不详'),
('欧阳俊雄','s25304','男',28,4,'新疆'),
('梅超风','s25318','女',23,5,'');
use StudentDB;
create table StudentMarks
(
ExamNo char(7) not null primary key,
StuNo char(6) not null,
WrittenExam tinyint not null,
LabExam tinyint not null,
-- constraint PK_StudentMarks_ExamNo primary key StuNo,
constraint FK_StudentInfo_StudentMarks_StuNo foreign key (StuNo) references StudentInfo(StuNo)
);
insert into StudentMarks values('s271811','s25303',90,58),('s271813','s25302',60,90),('s271816','s25301',87,82);