--1 学校表
create table School(
ID int identity(1,1),-- 主键
SchName nvarchar(30) not null,
SchlloAddress nvarchar(30) null,
Sort int not null,
CreateID int not null,
CreateTime DateTime not null,
primary key(ID)
);
go
select * from School;
insert into school(SchName,sort,CreateID,CreateTime)values('清华大学',1,1,2022-01-01);
insert into school(SchName,sort,CreateID,CreateTime)values('复旦大学',2,1,2021-01-01);
insert into school(SchName,sort,CreateID,CreateTime)values('山东大学',3,1,2020-01-01);
insert into school(SchName,sort,CreateID,CreateTime)values('潍坊大学',4,1,2019-01-01);
insert into school(SchName,sort,CreateID,CreateTime)values('中国地质大学',5,1,2018-01-01);
-- 2 班级表 班级关联学校表
create table StuClass(
ID int identity(1,1),
SchoolID int not null,--fk 外键 关联学校表的主键ID
ClassCode int null, --班级编号
ClassName nvarchar(50) not null,
primary key(ID),
-- 外键
foreign key(SchoolID) references School(ID),
);
go
select * from StuClass;
--
insert into StuClass(SchoolID,ClassName,ClassCode)values(1,'高一1班',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(1,'高一2班',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(1,'高一3班',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(2,'高二1班',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(3,'高三3班',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(4,'大一',01);
insert into StuClass(SchoolID,ClassName,ClassCode)values(5,'大二',01);
-- 3 学生表 学生关联班级表
create table Student(
ID int identity(1,1), --主键
ClassID int not null,--FK 外键.关联班级表中的主键ID
Name nvarchar(50) not null,
Age int null,
Birthday datetime null,
ChineseScores int not null,--语文成绩
MathScores int not null,
primary key(ID),
-- 字段 设置为外键。主要作用是关联班级表，班级表中的主键。
foreign key(ClassID) references StuClass(ID) --外键1 关联班级表
);
select * from Student;
--
insert into student(name,ClassID,ChineseScores,MathScores)values('张三',1,88,99);
insert into student(name,ClassID,ChineseScores,MathScores)values('李四',2,88,99);
insert into student(name,ClassID,ChineseScores,MathScores)values('王五',3,100,100);
insert into student(name,ClassID,ChineseScores,MathScores)values('赵柳',4,100,99);
insert into student(name,ClassID,ChineseScores,MathScores)values('如花',5,85,99);
insert into student(name,ClassID,ChineseScores,MathScores)values('孙二娘',6,88,99);
