MySQL新手锻炼

约束的用法：

创建班级表
CREATE TABLE class(
cid INT PRIMARY KEY AUTO_INCREMENT,
caption VARCHAR(20) NOT NULL);

INSERT INTO class(caption) VALUES('三年二班'),
('一年三班'),
('三年一班');

创建学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
gender ENUM('男', '女'),
class_id INT,
FOREIGN KEY (class_id) REFERENCES class(cid)
);

INSERT INTO student(sname,gender,class_id) VALUES('钢蛋',2,1),
('铁锤',2,1),
('山炮',1,2);

建老师表

CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20) NOT NULL
);

INSERT INTO teacher(tname) VALUES('波多'),
('苍井'),
('饭岛');
建课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(tid)
);
INSERT INTO course(cname,teacher_id,) VALUES('生物',1),
('体育',1),
('物理',2);
建成绩表
CREATE TABLE score(
sid INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
number DECIMAL(5,2),
FOREIGN KEY (student_id) REFERENCES student(sid)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (course_id) REFERENCES course(cid)
ON UPDATE CASCADE
ON DELETE RESTRICT
);

INSERT INTO score(student_id,course_id,number) VALUE(1,1,60),
(1,2,59),
(2,2,100);