CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL,
age INT NOT NULL,
sex ENUM('男', '女') NOT NULL,
introduce TEXT
);
CREATE TABLE score (
id INT AUTO_INCREMENT PRIMARY KEY,
scoreName VARCHAR(255) NOT NULL,
result DECIMAL(5, 2) NOT NULL,
studentId INT,
FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, '男', '张三是一个勤奋的学生。'),
('2023-02-01', '李四', '13900139000', 21, '男', '李四喜欢编程和数学。'),
('2023-03-01', '王五', '13700137000', 22, '女', '王五对艺术有浓厚的兴趣。'),
('2023-04-01', '赵六', '13600136000', 20, '女', '赵六是一个优秀的音乐生。'),
('2023-05-01', '孙七', '13500135000', 21, '男', '孙七热爱运动,尤其是篮球。');
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.50, (SELECT id FROM student WHERE userName = '张三')),
('语文', 85.00, (SELECT id FROM student WHERE userName = '张三')),
('英语', 88.00, (SELECT id FROM student WHERE userName = '李四')),
('物理', 92.00, (SELECT id FROM student WHERE userName = '李四')),
('化学', 89.50, (SELECT id FROM student WHERE userName = '王五')),
('历史', 93.00, (SELECT id FROM student WHERE userName = '王五')),
('生物', 87.00, (SELECT id FROM student WHERE userName = '赵六')),
('地理', 91.00, (SELECT id FROM student WHERE userName = '赵六')),
('政治', 86.50, (SELECT id FROM student WHERE userName = '孙七')),
('体育', 94.00, (SELECT id FROM student WHERE userName = '孙七'));
sql数据库
最新推荐文章于 2024-09-10 10:21:38 发布