MySOL多表联合查询

ddl+dml

-- 创建student表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    sex VARCHAR(10) NOT NULL,
    introduce TEXT
);
 
 
-- 创建score表
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    scoreName VARCHAR(50) 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-04-01 10:00:00', '张三', '13800138000', 20, '男', '张三是一名优秀的学生，热爱学习。');
 
 
-- 插入第二条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-02 12:30:00', '李四', '13900139000', 21, '女', '李四是个开朗的学生，擅长数学。');
 
 
-- 插入第三条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-03 09:15:00', '王五', '13700137000', 19, '男', '王五喜欢阅读和写作，经常参加文学活动。');
 
 
-- 插入第四条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-04 15:45:00', '赵六', '13600136000', 22, '女', '赵六是计算机科学专业的学生，对编程非常感兴趣。');
 
 
-- 插入第五条学生信息
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-05 08:00:00', '孙七', '13500135000', 20, '男', '孙七热衷于体育运动，尤其是篮球。');

 
-- 假设学生ID分别为1, 2, 3, 4, 5（实际中需要查询得到）
 
 
-- 为学生ID为1的学生插入成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('数学', 90.50, 1),
('英语', 85.00, 1);
 
 
-- 为学生ID为2的学生插入成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('物理', 88.75, 2),
('化学', 92.00, 2);
 
 
-- 为学生ID为3的学生插入成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('语文', 86.25, 3),
('历史', 91.50, 3);
 
 
-- 为学生ID为4的学生插入成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('生物', 89.00, 4),
('地理', 94.00, 4);
 
 
-- 为学生ID为5的学生插入成绩
INSERT INTO score (scoreName, result, studentId) VALUES
('政治', 82.75, 5),
('体育', 87.50, 5);

dql

SELECT 
    s.id AS student_id,
    s.userName AS student_name,
    SUM(sc.result) '总分'
FROM 
    student s
JOIN 
    score sc 
ON s.id = sc.studentId
where sc.result>70
GROUP BY 
    s.id, s.userName
having SUM(sc.result)>900
ORDER BY SUM(sc.result) desc;