SQL建表

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex VARCHAR(10) NOT NULL,
    introduce TEXT
);
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(255) NOT NULL,
    result DECIMAL(5, 2) NOT NULL,
    studentId INT,
    FOREIGN KEY (studentId) REFERENCES student(id)
);

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, '男', '热爱学习，乐于助人。'),
('2023-01-02', '李四', '13900139000', 21, '女', '勤奋好学，善于沟通。'),
('2023-01-03', '王五', '13700137000', 22, '男', '热爱运动，成绩优异。'),
('2023-01-04', '赵六', '13600136000', 23, '女', '责任心强，乐于助人。'),
('2023-01-05', '孙七', '13500135000', 24, '男', '性格开朗，善于沟通。');
\
-- 假设学生ID 1 的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.50, 1),
('英语', 85.75, 1);


-- 假设学生ID 2 的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 88.00, 2),
('物理', 92.25, 2);


-- 假设学生ID 3 的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('语文', 85.00, 3),
('化学', 95.50, 3);


-- 假设学生ID 4 的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 92.00, 4),
('生物', 88.75, 4);


-- 假设学生ID 5 的成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('英语', 89.00, 5),
('历史', 91.25, 5);