一.嵌套子查询
子 查 询 特 点:
- ①使用灵活,可以成为SQL语句的多个部分
- ②降低SQL的语句的复杂度,提高SQL语句的可读性
- 1.子查询作为条件
- --查询学号在王五前边的同学
- select * from StuInfo where stuid < (select stuid from StuInfo where stuname='王五')
- 2.子查询作为临时表
- --查询李四同学成绩大于80分的记录
- select stuname,subject,score from
- (select * from stuinfo where stuname = '李四') s1,
- (select * from stumarks where score > 80) s2
- where s1.stuid = s2.stuid
- 3.子查询作为列使用
- --查询所有学员html成绩,没有成绩以null显示
- select s.*,
- (select score from StuMarks where subject='html' and s.stuid=StuMarks.stuid) as '成绩'
- from StuInfo s
二.聚合函数
常见的五种聚合函数是:
求个数:count 求总和:sum 求最大值:max 求最小值:min 求平均值:avg
三.排序函数
- ——排序函数 over([分组子句] 排序语句[排序方式])
- ——row_number() 行号
- select row_number() over(order by score desc) as '排名',
- StuInfo.stuid,stuname,score from StuInfo,StuMarks
- where StuInfo.stuid=StuMarks.stuid and StuMarks.subject='java'
- ——rank() 存在并列时跳空
- select rank() over(order by score desc) as '排名',
- StuInfo.stuid,stuname,stusex,score from StuInfo,StuMarks
- where StuInfo.stuid=StuMarks.stuid and StuMarks.subject='java'
- ——dense_rank() 存在并列时不跳空
- select dense_rank() over(order by score desc) as '排名',
- StuInfo.stuid,stuname,stusex,score from StuInfo,StuMarks
- where StuInfo.stuid = StuMarks.stuid and StuMarks.subject='java'
- ——dense_rank() 存在并列时不跳空
- select dense_rank() over(order by sum(score) desc) as '排名',
- StuInfo.stuid,stuname,stusex,sum(score) '总分' from StuInfo,StuMarks
- where StuInfo.stuid = StuMarks.stuid
- group by StuInfo.stuid,stuname,stusex
- ——partition by 分组子句
- select dense_rank() over(partition by subject order by score desc)as '排名',
- StuInfo.stuid,stuname,subject,score from StuInfo,StuMarks
- where StuInfo.stuid=StuMarks.stuid