1)
select Cno,COUNT(Sno)
from sc
group by Cno;
分析:该语句对查询结果按Cno的值分组,所有相同Cno值的元组为一组
然后对每一组作用集函数COUNT计算,以求得该组的学生人数。
如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING
短语指定筛选条件。select Cno,COUNT(Sno)
from sc
group by Cno
having COUNT(*)〉3;select *
from Student
where Sno = '9500';
2)
两个表的查询。
seclect Student.*,SC.*
from Student,SC
where Student.Sno = SC.Sno;
3)
外连接
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Student.Sno = SC.Sno(*)
4)
带有IN谓词的子查询
例:
查询与“刘晨”在同一个系学习的学生
select Sno,Sname,Sdept
from Student
where Sdept IN
(select Stept
from Student
Where Sname = '刘晨');
方法2:
select S1.Sno,S1.Sname,S1.Sdept
from Student S1,Student S2
where S1.Sdept = S2.Sdept and S2.Sname = '刘晨';5)
对查询结果排序
select Sno,Grade
from SC
where Cno = '3'
order by grade desc;
6)
查询学生总人数
select count(*)
from Student;
计算1号课程的最高成绩
select max(Grade)
from SC
where Cno = '1'; //avg(),sum()一列值的总和。count()统计元组个数。
7)
Statement stmt=con.createStatement();
stmt.executeUpdate("Update bookTable set Title='Java2' where Author='zhang'");
stmt.executeUpdate("Delete from bookTable where Author='zhang'");
stmt.executeUpdate("Insert into bookTable(BookID,Author,Title) values(1,'Li Ming','Java2')"); //未给出的列,其值为NULL