SchoolDB字段创建添加约束以及主键

-- SchoolDB字段创建添加约束以及主键
CREATE DATABASE SchoolDB;

USE SchoolDB;

CREATE TABLE Grade(
gradeId INT(4) NOT NULL,
gradeName VARCHAR(50) NOT NULL,
PRIMARY KEY (gradeId)
);

CREATE TABLE Subject(
subjectId INT(4) NOT NULL,
subjectName VARCHAR(20),
classHour INT(4),
gradeId INT(4),
PRIMARY KEY (subjectId),
CONSTRAINT CK_ch CHECK(classHour >= 0)
);

CREATE TABLE Student(
sudentNo VARCHAR(20) NOT NULL,
loginPwd VARCHAR(20) NOT NULL,
subjectName VARCHAR(50) NOT NULL, -- 注意：这里的 subjectName 字段可能不是必需的，因为学生和课程之间的关系应该在 Result 表中表示
sex CHAR(20) NOT NULL DEFAULT '男',
gradeId INT(4),
phone VARCHAR(20),
address VARCHAR(255) DEFAULT '地址不详',
bomDate DATE,
email VARCHAR(50),
identityCard VARCHAR(18) DEFAULT '全国唯一',
PRIMARY KEY (sudentNo),
CONSTRAINT UN_ic UNIQUE KEY(identityCard)
);

CREATE TABLE Result(
id INT(4) NOT NULL,
sudentNo VARCHAR(20) NOT NULL,
subjectId INT(4) NOT NULL,
subjectResult FLOAT(6,2) NOT NULL,
examDate DATETIME,
PRIMARY KEY (id),
CONSTRAINT CK_ch CHECK(subjectResult >= 0 AND subjectResult <= 100)
);

-- 添加外键约束
ALTER TABLE Subject
ADD CONSTRAINT FK_Subject_Grade FOREIGN KEY (gradeId) REFERENCES Grade(gradeId);

ALTER TABLE Student
ADD CONSTRAINT FK_Student_Grade FOREIGN KEY (gradeId) REFERENCES Grade(gradeId);

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Student FOREIGN KEY (sudentNo) REFERENCES Student(sudentNo),
ADD CONSTRAINT FK_Result_Subject FOREIGN KEY (subjectId) REFERENCES Subject(subjectId);

-- 查看表结构（可选）
DESC Grade;
DESC Subject;
DESC Student;
DESC Result;
-- SchoolDB字段创建添加约束以及主键
CREATE DATABASE SchoolDB;

USE SchoolDB;

CREATE TABLE Grade(
gradeId INT(4) NOT NULL,
gradeName VARCHAR(50) NOT NULL,
PRIMARY KEY (gradeId)
);

CREATE TABLE Subject(
subjectId INT(4) NOT NULL,
subjectName VARCHAR(20),
classHour INT(4),
gradeId INT(4),
PRIMARY KEY (subjectId),
CONSTRAINT CK_ch CHECK(classHour >= 0)
);

CREATE TABLE Student(
sudentNo VARCHAR(20) NOT NULL,
loginPwd VARCHAR(20) NOT NULL,
subjectName VARCHAR(50) NOT NULL, -- 注意：这里的 subjectName 字段可能不是必需的，因为学生和课程之间的关系应该在 Result 表中表示
sex CHAR(20) NOT NULL DEFAULT '男',
gradeId INT(4),
phone VARCHAR(20),
address VARCHAR(255) DEFAULT '地址不详',
bomDate DATE,
email VARCHAR(50),
identityCard VARCHAR(18) DEFAULT '全国唯一',
PRIMARY KEY (sudentNo),
CONSTRAINT UN_ic UNIQUE KEY(identityCard)
);

CREATE TABLE Result(
id INT(4) NOT NULL,
sudentNo VARCHAR(20) NOT NULL,
subjectId INT(4) NOT NULL,
subjectResult FLOAT(6,2) NOT NULL,
examDate DATETIME,
PRIMARY KEY (id),
CONSTRAINT CK_ch CHECK(subjectResult >= 0 AND subjectResult <= 100)
);

-- 添加外键约束
ALTER TABLE Subject
ADD CONSTRAINT FK_Subject_Grade FOREIGN KEY (gradeId) REFERENCES Grade(gradeId);

ALTER TABLE Student
ADD CONSTRAINT FK_Student_Grade FOREIGN KEY (gradeId) REFERENCES Grade(gradeId);

ALTER TABLE Result
ADD CONSTRAINT FK_Result_Student FOREIGN KEY (sudentNo) REFERENCES Student(sudentNo),
ADD CONSTRAINT FK_Result_Subject FOREIGN KEY (subjectId) REFERENCES Subject(subjectId);

-- 查看表结构（可选）
DESC Grade;
DESC Subject;
DESC Student;
DESC Result;