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 ENUM('男', '女') NOT NULL,
introduce TEXT
);
CREATE TABLE score (
id INT PRIMARY KEY AUTO_INCREMENT,
scoreName VARCHAR(255) NOT NULL,
result FLOAT NOT NULL,
studentId INT,
FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
DML
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '学习刻苦'),
('2023-04-02', '李四', '13800138001', 21, '女', '成绩优异'),
('2023-04-03', '王五', '13800138002', 22, '男', '热爱运动'),
('2023-04-04', '赵六', '13800138003', 20, '女', '文静内向'),
('2023-04-05', '孙七', '13800138004', 21, '男', '乐于助人');
-- 张三的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 90, 1);
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 85, 1);
-- 李四的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('数学', 88, 2);
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 92, 2);
-- 王五的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 80, 3);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 85, 3);
-- 赵六的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('物理', 95, 4);
INSERT INTO score (scoreName, result, studentId) VALUES ('化学', 89, 4);
-- 孙七的成绩
INSERT INTO score (scoreName, result, studentId) VALUES ('英语', 78, 5);
INSERT INTO score (scoreName, result, studentId) VALUES ('生物', 87, 5);
应用
SELECT
s.userName,
SUM(sc.result) AS totalScore
FROM
student s
JOIN
score sc ON s.id = sc.studentId
WHERE
sc.result IS NOT NULL
GROUP BY
s.id, s.userName;