数据库系统概论05.ppt

文档介绍：Lorem Ipsum
Lorem Ipsum is simply dummy text of the printing. Lorem Ipsum is simply dummy text of the printing.
数据库系统概grouped by branch-name:
branch-name
balance
Perryridge
Brighton
Redwood
1300
1500
700
branch-name g sum(balance) (account)
一般的分组统计查询有哪些？
聚集查询:
查询学生人数
Select count(*) from student
Select count(Sno) from student
查询选修了课程的学生人数
Select count(distinct Sno) from SC
计算1号课程的学生总分、最高分和最低分
Select sum(grade) as total, max(grade) as max, min(grade) as min
From SC where Cno=‘1’
查询各门课程的课程号及其选课人数
Select Cno, count(Sno) from SC group by Cno
查询选修了3门以上课程的学生学号
Select Sno from SC group by Sno having count(Cno)>=3
聚集查询
五种统计函数
Having 和 where 的区别
有where又有分组时，谁先执行？
在聚集查询中, select 目标列 只能是group by中出现的字段，或者聚集函数。
课堂练习
查询各个分支点的储蓄总额
查询各分支点的储蓄均额
查询各分支点的开户总数
查询各分支点的最大最小帐户余额
branch-name
account-number
balance
Perryridge
Perryridge
Brighton
Brighton
Redwood
A-102
A-201
A-217
A-215
A-222
400
900
750
750
700
关系 account grouped by branch-name:
连接查询
查询每个学生及其选修课程的情况（笛卡尔积）
Select Student.*, SC.*
From Student,SC where =
查询每个学生姓名及其所选课程成绩（自然连接，内连接）
Select sname, cno, grade
From student inner join SC on =
customer-name
loan-number
Jones
Smith
Hayes
L-170
L-230
L-155
3000
4000
1700
loan-number
amount
L-170
L-230
L-260
branch-name
Downtown
Redwood
Perryridge
Relation loan
Relation borrower
loan-number
amount
L-170
L-230
3000
4000
customer-name
Jones
Smith
branch-name
Downtown
Redwood
Inner Join: loan Borrower
Jones
Smith
null
loan-number
amount
L-170
L-230
L-260
3000
4000
1700
customer-name
branch-name
Downtown
Redwood
Perryridge
Left Outer Join: loan Borrower
loan-number
amount
L-170
L-230
L-155
3000
4000
null
customer-name
Jones
Smith
Hayes
branch-name
Downtown
Redwood
null
Right Outer Join: loan borrower
loan-number
amount
L-170
L-230
L-260
L-155
3000
4000
1700
null
customer-name
Jones
Smith
null
Hayes
branch-name
Downtown
Redwood
Perryridge
null