11111111111

DDL

-- 创建student表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    age INT NOT NULL,
    sex ENUM('男', '女') NOT NULL,
    introduce TEXT
);


-- 创建score表
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 ON UPDATE CASCADE
);

DML

-- 插入5条学生信息到student表
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 20, '男', '张三同学是计算机科学专业的学生，成绩优异。'),
('李四', '13900139000', 21, '女', '李四同学热爱数学，经常参加数学竞赛。'),
('王五', '13700137000', 19, '男', '王五同学热爱运动，同时也是班级的体育委员。'),
('赵六', '13600136000', 22, '女', '赵六同学擅长文艺表演，多次在校级晚会上演出。'),
('孙七', '13500135000', 20, '男', '孙七同学对编程有浓厚兴趣，自学了多种编程语言。');


-- 插入10条成绩信息到score表，确保每个学生ID对应两个科目的成绩

-- 假设有语文和数学两个科目
INSERT INTO score (scoreName, result, studentId) VALUES
('语文', 90.50, (SELECT id FROM student WHERE userName = '张三')),
('数学', 88.00, (SELECT id FROM student WHERE userName = '张三')),
('语文', 92.00, (SELECT id FROM student WHERE userName = '李四')),
('数学', 95.00, (SELECT id FROM student WHERE userName = '李四')),
('语文', 85.50, (SELECT id FROM student WHERE userName = '王五')),
('数学', 87.00, (SELECT id FROM student WHERE userName = '王五')),
('语文', 91.00, (SELECT id FROM student WHERE userName = '赵六')),
('数学', 93.00, (SELECT id FROM student WHERE userName = '赵六')),
('语文', 89.50, (SELECT id FROM student WHERE userName = '孙七')),
('数学', 92.00, (SELECT id FROM student WHERE userName = '孙七'));

DQL

SELECT 
    s.id AS student_id,
    s.userName,
    s.age,
    s.sex,
    COALESCE(SUM(sc.result), 0) AS total_score
FROM 
    student s
LEFT JOIN 
    score sc ON s.id = sc.studentId
GROUP BY 
    s.id, s.userName, s.age, s.sex
ORDER BY 
    s.id;