学生表、课程表、成绩表:
student
course
3score
- 查询1课程比2课程成绩高的所有学生的编号;
- 查询所有学生的学号、姓名、选课数、总成绩;
- 查询没有学完所有课程的学生学号、姓名;
建表
CREATE TABLE Student(
sno int PRIMARY KEY auto_increment,
sname VARCHAR(55) NOT NULL,
sage INT NOT NULL
);
CREATE TABLE Course(
cno INT PRIMARY KEY auto_increment,
cname VARCHAR(55) NOT NULL
);
CREATE TABLE Score(
sno int ,
cno int ,
score DOUBLE not null,
PRIMARY KEY(sno,cno)
);
- 查询1课程比2课程成绩高的所有学生的编号;
这里没有使用左连接,应该使用左连接进行查询,结果才符合实际,
no1
SELECT s1. sno
FROM score s1 ,score s2
where s1.sno = s2.sno and s1.cno <> s2.cno and s1.cno= 1 and s1.score>s2.score;
no2
SELECT sno,cno,score FROM score where cno = 1;
SELECT sno,cno,score FROM score where cno = 2;
SELECT s1.sno
FROM
(SELECT sno,cno,score FROM score where cno = 1) s1
INNER JOIN
(SELECT sno,cno,score FROM score where cno = 2) s2
on s1.sno = s2.sno
WHERE s1.score > s2.score;
- 查询所有学生的学号、姓名、选课数、总成绩;
SELECT stu.sno,stu.sname,count(s.sno) '选课数',sum(s.score) '总成绩'
from student stu left JOIN score s
ON stu.sno = s.sno
left JOIN course c
on s.cno = c.cno
GROUP BY stu.sno
;
- 查询没有学完所有课程的学生学号、姓名;
– 先查询所有课程数
SELECT count(*) 'AllNUM'
FROM course;
然后查询每个学生的选课数
SELECT stu.sno,stu.sname,count(sc.sno) 'num'
FROM student stu
LEFT JOIN
Score sc
on stu.sno = sc.sno
GROUP BY stu.sno
;
结果
SELECT stu.sno,stu.sname
FROM student stu
LEFT JOIN
Score sc
on stu.sno = sc.sno
GROUP BY stu.sno
HAVING count(sc.sno) <> (SELECT count(*) 'AllNUM'
FROM course)
;