MYSQL½¨¿â½¨±í¼üÔ¼Êø

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,'±±¾©º£µí'),
('ÀîÎIJÅ','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);