-- 创建student表
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
age INT NOT NULL,
sex ENUM('男', '女') NOT NULL,
introduce TEXT
);
-- 创建score表
CREATE TABLE score (
id INT PRIMARY KEY AUTO_INCREMENT,
scoreName VARCHAR(50) NOT NULL,
result INT NOT NULL,
studentId INT,
FOREIGN KEY (studentId) REFERENCES student(id) ON DELETE CASCADE
);
dml语句
-- 插入5条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, '男', '来自北方，喜欢运动'),
('2023-02-02', '李四', '13900139000', 21, '女', '来自南方，爱好音乐'),
('2023-03-03', '王五', '13700137000', 22, '男', '来自东部，擅长编程'),
('2023-04-04', '赵六', '13600136000', 23, '女', '来自西部，喜欢旅行'),
('2023-05-05', '孙七', '13500135000', 24, '男', '来自中部，热爱阅读');
-- 插入10条成绩信息
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90, (SELECT id FROM student WHERE userName = '张三')),
('语文', 85, (SELECT id FROM student WHERE userName = '张三')),
('数学', 88, (SELECT id FROM student WHERE userName = '李四')),
('英语', 92, (SELECT id FROM student WHERE userName = '李四')),
('物理', 89, (SELECT id FROM student WHERE userName = '王五')),
('化学', 93, (SELECT id FROM student WHERE userName = '王五')),
('数学', 86, (SELECT id FROM student WHERE userName = '赵六')),
('生物', 91, (SELECT id FROM student WHERE userName = '赵六')),
('历史', 87, (SELECT id FROM student WHERE userName = '孙七')),
('地理', 94, (SELECT id FROM student WHERE userName = '孙七'));
学生表和成绩表，俩表联结查询总分和平均分
SELECT
s.userName,
COALESCE(SUM(sc.result), 0) AS totalScore,
COALESCE(AVG(sc.result), 0) AS averageScore
FROM
student s
LEFT JOIN
score sc ON s.id = sc.studentId
GROUP BY
s.id, s.userName desc;
