数据库的查询操作

三张表结构

查询学号为S001的学生的课程

SELECT cname from stumarks c, stucourse m WHERE c.cno=m.cno and stuno='S001'


select * from stuinfo ORDER BY stubirth


//倒序排列查出来的成绩 按照stuscore分组后面加条件 
SELECT * from stumarks GROUP BY stuscore having stuscore>86 ORDER BY  stuscore desc
//正序
SELECT * from stumarks GROUP BY stuscore having stuscore>86 ORDER BY  stuscore ASC

以下是方便自己的查看

SELECT *
FROM stuinfo join stumarks on stuinfo.stuno=stumarks.stuno;

SELECT *
FROM stuinfo,stumarks
WHERE stuinfo.stuno=stumarks.stuno

SELECT *
FROM stuinfo LEFT JOIN stumarks on stuinfo.stuno=stumarks.stuno

SELECT stumarks RIGHT  JOIN stuinfo on stuinfo.stuno=stumarks.stuno

SELECT stusex,AVG(stuscore)
from stuinfo,stumarks
WHERE stuinfo.stuno=stumarks.stuno
GROUP BY stusex

SELECT stuinfo.stuno,stuname,cname
FROM stuinfo,stumarks,stucourse
WHERE stuinfo.stuno=stumarks.stuno AND stumarks.cno=stucourse.cno

SELECT i.stuno,stuname,cname,stuscore
FROM stuinfo i JOIN stumarks m on i.stuno=m.stuno
JOIN stucourse c on c.cno=m.cno

SELECT i.stuno,stuname,cname
FROM stuinfo i,stucourse c,stumarks m
WHERE i.stuno=m.stuno and c.cno=m.cno and stuname like '刘%'

SELECT stuno,stuscore
FROM stucourse c,stumarks m
WHERE c.cno=m.cno and cname='英语' AND stuscore BETWEEN 80 and 90 

SELECT AVG(YEAR(SYSDATE())-YEAR(stubirth))
FROM stuinfo i,stumarks m
where i.stuno=m.stuno
AND cno='0004'

SELECT AVG(year(SYSDATE())-year(stubirth))
FROM stuinfo JOIN stumarks ON stuinfo.stuno=stumarks.stuno
WHERE cno='0004'