基于MySQL语法
1. 四张表结构
- 学生表
student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 - 课程表
course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 - 教师表
teacher(t_id,t_name) –教师编号,教师姓名 - 成绩表
score(s_id,c_id,s_score) –学生编号,课程编号,分数
2. 建表语句
CREATE TABLE `student` (
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`)
);
CREATE TABLE `course` (
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY (`c_id`)
);
CREATE TABLE `teacher` (
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
);
CREATE TABLE `score` (
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY (`s_id`, `c_id`)
);
INSERT INTO student
VALUES ('01', '赵雷', '1990-01-01', '男'),
('02', '钱电', '1990-12-21', '男'),
('03', '孙风', '1990-05-20', '男'),
('04', '李云', '1990-08-06', '男'),
('05', '周梅', '1991-12-01', '女'),
('06', '吴兰', '1992-03-01', '女'),
('07', '郑竹', '1989-07-01', '女'),
('08', '王菊', '1990-01-20', '女');
INSERT INTO course
VALUES ('01', '语文', '02'),
('02', '数学', '01'),
('03', '英语', '03');
INSERT INTO teacher
VALUES ('01', '张三'),
('02', '李四'),
('03', '王五');
INSERT INTO score
VALUES ('01', '01', 80),
('01', '02', 90),
('01', '03', 99),
('02', '01', 70),
('02', '02', 60),
('02', '03', 80),
('03', '01', 80),
('03', '02', 80),
('03', '03', 80),
('04', '01', 50),
('04', '02', 30),
('04', '03', 20),
('05', '01', 76),
('05', '02', 87),
('06', '01', 31),
('06', '03', 34),
('07', '02', 89),
('07', '03', 98);
3. 答案(仅供参考)
SELECT stu.*, s1.s_score AS '语文', s2.s_score AS '数学'
FROM student stu
JOIN score s1 ON stu.s_id = s1.s_id AND s1.c_id = 01
JOIN score s2 ON stu.s_id = s2.s_id AND s2.c_id = 02
WHERE s1.s_score > s2.s_score;
SELECT stu.*, s1.s_score AS '语文', s2.s_score AS '数学'
FROM student stu
JOIN score s1 ON stu.s_id = s1.s_id AND s1.c_id = 01
JOIN score s2 ON stu.s_id = s2.s_id AND s2.c_id = 02
WHERE s1.s_score < s2.s_score;
SELECT stu.*, AVG(sc.s_score) AS '平均分'
FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING AVG(sc.s_score) >= 60;
SELECT stu.*, AVG(sc.s_score) AS '平均分'
FROM student stu
LEFT JOIN score sc ON stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING AVG(sc.s_score) < 60
OR AVG(sc.s_score) IS NULL;
SELECT stu.*, x.总成绩, x.选课总数
FROM student stu
LEFT JOIN (SELECT SUM(sc.s_score) AS 总成绩, COUNT(sc.c_id) AS 选课总数, sc.s_id
FROM course c
JOIN score sc ON c.c_id = sc.c_id
GROUP BY sc.s_id) x ON stu.s_id = x.s_id;
SELECT COUNT(*)
FROM teacher t
WHERE t.t_name LIKE '李%';
SELECT *
FROM student stu
WHERE stu.s_id IN (SELECT s_id
FROM teacher t
JOIN course c ON t.t_id = c.t_id
JOIN score s ON c.c_id = s.c_id
WHERE t.t_name = '张三');
SELECT *
FROM student stu
WHERE stu.s_id NOT IN (SELECT s_id
FROM teacher t
JOIN course c ON t.t_id = c.t_id
JOIN score s ON c.c_id = s.c_id
WHERE t.t_name = '张三');
SELECT *
FROM student stu
LEFT JOIN (SELECT * FROM score WHERE c_id = '01') sc1 ON stu.s_id = sc1.s_id
LEFT JOIN (SELECT * FROM score WHERE c_id = '02') sc2 ON stu.s_id = sc2.s_id
WHERE sc1.c_id = '01'
AND sc2.c_id = '02';
SELECT *
FROM student stu
LEFT JOIN (SELECT * FROM score WHERE c_id = '01') sc1 ON stu.s_id = sc1.s_id
LEFT JOIN (SELECT * FROM score WHERE c_id = '02') sc2 ON stu.s_id = sc2.s_id
WHERE sc1.c_id = '01'
AND sc2.c_id IS NULL;
SELECT *
FROM student st
WHERE st.s_id IN (SELECT sc.s_id FROM score sc GROUP BY sc.s_id HAVING COUNT(*) < (SELECT COUNT(*) FROM course));
SELECT *
FROM student stu
WHERE stu.s_id IN
(SELECT DISTINCT s_id FROM score WHERE c_id IN (SELECT c_id FROM score WHERE s_id = '01') AND s_id != '01');
SELECT *
FROM student
WHERE s_id IN (SELECT s_id
FROM score t1
GROUP BY s_id
HAVING GROUP_CONCAT(c_id) = (SELECT GROUP_CONCAT(c_id) FROM score WHERE s_id = '01')
AND s_id != '01');
SELECT s_name
FROM student
WHERE s_id NOT IN (SELECT DISTINCT s_id
FROM score
WHERE c_id IN (SELECT t2.c_id
FROM teacher t1,
course t2
WHERE t1.t_id = t2.t_id
AND t1.t_name = '张三'));
SELECT st.s_id, s_name, x.avg
FROM student st
JOIN (SELECT s_id, AVG(s_score) AS avg FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(*) >= 2) x
ON st.s_id = x.s_id;
SELECT st.s_id, st.s_name, x.s_score, x.c_id
FROM student st
RIGHT JOIN (SELECT * FROM score sc WHERE sc.c_id = '01' AND sc.s_score < 60) x ON st.s_id = x.s_id
ORDER BY s_score DESC;
SELECT st.s_id AS 编号,
st.s_name AS 姓名,
MAX(IF(c.c_name = '语文', sc.s_score, 0)) AS 语文,
MAX(IF(c.c_name = '数学', sc.s_score, 0)) AS 数学,
MAX(IF(c.c_name = '英语', sc.s_score, 0)) AS 英语,
AVG(sc.s_score) AS 平均分
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
LEFT JOIN course c ON sc.c_id = c.c_id
GROUP BY st.s_id
ORDER BY 平均分 DESC;
SELECT co.c_id, co.c_name,
MAX(sc.s_score) AS '最高分',
MIN(sc.s_score) AS '最低分',
ROUND(AVG(sc.s_score), 2) AS '平均分',
SUM(IF(sc.s_score >= 60, 1, 0)) / COUNT(sc.s_id) AS '及格率',
SUM(IF(sc.s_score >= 70 AND sc.s_score < 80, 1, 0)) / COUNT(sc.s_id) AS '中等率',
SUM(IF(sc.s_score >= 80 AND sc.s_score < 90, 1, 0)) / COUNT(sc.s_id) AS '优良率',
SUM(IF(sc.s_score >= 90, 1, 0)) / COUNT(sc.s_id) AS '优秀率'
FROM course co
LEFT JOIN score sc ON co.c_id = sc.c_id
GROUP BY co.c_id, co.c_name;
SELECT *, ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS rk
FROM score
ORDER BY c_id, rk;
SELECT st.s_id, st.s_name, (IF(SUM(sc.s_score) IS NULL, 0, SUM(sc.s_score)))
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY st.s_id
ORDER BY SUM(sc.s_score) DESC;
SELECT t1.t_name, t1.c_name, AVG(s_score)
FROM score sc
RIGHT JOIN (SELECT te.t_name, co.*
FROM teacher te
LEFT JOIN course co ON te.t_id = co.t_id) t1 ON sc.c_id = t1.c_id
GROUP BY t1.t_name, t1.c_name
ORDER BY AVG(s_score) DESC;
SELECT *
FROM (SELECT st.s_name, c.c_name, sc.s_score, ROW_NUMBER() OVER (PARTITION BY c.c_id ORDER BY sc.s_score DESC ) AS rk
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id) t1
WHERE rk BETWEEN 2 AND 3;
SELECT co.c_id, co.c_name,
SUM(IF(sc.s_score > 85 AND sc.s_score <= 100, 1, 0)) AS '[100-85]',
SUM(IF(sc.s_score > 70 AND sc.s_score <= 85, 1, 0)) AS '[85-70]',
SUM(IF(sc.s_score > 60 AND sc.s_score <= 70, 1, 0)) AS '[70-60]',
SUM(IF(sc.s_score > 0 AND sc.s_score <= 60, 1, 0)) AS '[60-0]'
FROM course co
LEFT JOIN score sc ON co.c_id = sc.c_id
GROUP BY co.c_id, co.c_name;
SELECT st.s_name, AVG(sc.s_score), ROW_NUMBER() OVER (ORDER BY AVG(sc.s_score) DESC)
FROM student st
JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_name;
SELECT *
FROM (SELECT c_name, s_name, s_score, ROW_NUMBER() OVER (PARTITION BY c_name ORDER BY s_score DESC ) AS rk
FROM student st
JOIN score sc ON st.s_id = sc.s_id
JOIN course c ON c.c_id = sc.c_id) t1
WHERE rk <= 3;
SELECT c_name, COUNT(*) AS 人数
FROM course co
JOIN score sc ON co.c_id = sc.c_id
GROUP BY c_name;
SELECT st.s_id, st.s_name
FROM student st
JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name
HAVING COUNT(sc.c_id) = 2;
SELECT s_sex, COUNT(*) AS 人数
FROM student
GROUP BY s_sex;
SELECT *
FROM student
WHERE s_name LIKE '%风%';
SELECT s_name, COUNT(*)
FROM student
GROUP BY s_name
HAVING COUNT(*) > 1;
SELECT *
FROM student
WHERE s_birth LIKE '1990%';
SELECT co.c_id, co.c_name, ROUND(AVG(sc.s_score), 2) AS avg
FROM course co
JOIN score sc ON sc.c_id = co.c_id
GROUP BY co.c_id, co.c_name
ORDER BY avg DESC, co.c_id ASC;
SELECT st.s_id, st.s_name, ROUND(AVG(s_score), 2) AS avg
FROM student st
JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_id, st.s_name
HAVING avg >= 85;
SELECT st.s_name, sc.s_score
FROM student st
JOIN score sc ON st.s_id = sc.s_id
JOIN course co ON sc.c_id = co.c_id
WHERE co.c_name = '数学'
AND sc.s_score < 60;
SELECT s_name, group_concat(s_score), group_concat(c_name)
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
LEFT JOIN course co ON sc.c_id = co.c_id
GROUP BY s_name;
SELECT st.s_name, group_concat(c_name), group_concat(s_score)
FROM student st
JOIN score sc ON st.s_id = sc.s_id
JOIN course co ON sc.c_id = co.c_id
WHERE sc.s_score > 70
GROUP BY st.s_name;
SELECT st.s_name, co.c_name, sc.s_score
FROM student st
JOIN score sc ON sc.s_id = st.s_id
JOIN course co ON co.c_id = sc.c_id
WHERE sc.s_score < 60;
SELECT st.s_id, st.s_name, sc.s_score
FROM student st
JOIN score sc ON st.s_id = sc.s_id
WHERE c_id = '01'
AND s_score > 80;
SELECT c_name, COUNT(*) AS 人数
FROM course co
JOIN score sc ON co.c_id = sc.c_id
GROUP BY co.c_id;
SELECT st.*, s_score
FROM student st
JOIN score sc ON st.s_id = sc.s_id
JOIN course co ON co.c_id = sc.c_id
JOIN teacher te ON te.t_id = co.t_id
WHERE t_name = '张三'
ORDER BY s_score DESC
LIMIT 1;
SELECT st1.s_id, st1.s_name, sc1.c_id, sc1.s_score
FROM student st1
JOIN score sc1 ON st1.s_id = sc1.s_id
JOIN student st2
JOIN score sc2 ON st2.s_id = sc2.s_id
WHERE sc1.s_score = sc2.s_score
AND sc1.c_id != sc2.c_id
GROUP BY sc1.c_id, sc1.s_id;
SELECT s_id, c_id, s_score
FROM (SELECT s_id, c_id, s_score, ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS rk FROM score) t1
WHERE rk <= 2;
SELECT sc.c_id, c_name, COUNT(*) AS sum
FROM course co
JOIN score sc ON co.c_id = sc.c_id
GROUP BY sc.c_id
ORDER BY sum DESC, sc.c_id;
SELECT st.s_id
FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY st.s_id
HAVING COUNT(*) >= 2;
SELECT st.*
FROM student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY st.s_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM course);