数据库学习笔记二

一、分组函数

（一）基础语法与应用

分组函数用于对数据进行分组统计，关键词为group by。基本语法是select * from 表名称 [ where 条件 ] group by 字段 [ having 条件 ]。

• 计算每个班学生的平均年龄和班级人数：

-- 对student表按sclass（班级）字段分组，统计每个班级学生的平均年龄（avg(sage)）和人数（count(*)）
select sclass,avg(sage),count(*) from student group by sclass;

• 计算每个班、不同性别的学生平均年龄和人数：

-- 按sclass（班级）和ssex（性别）字段分组，统计相关数据
select sclass,ssex,avg(sage),count(*) from student group by sclass,ssex;

• 计算每个人的平均分和总分，返回学号：

-- 对mark表按sid（学号）分组，计算每个学生的平均分（trunc(avg(cmark),1)，保留一位小数）和总分（sum(cmark)）
select sid,trunc(avg(cmark),1),sum(cmark) from mark group by sid;

（二）having语句的使用

having语句用于在分组后对结果进行筛选，分组前的条件用where，分组后的条件只能用having，且where后不能用聚合函数，having后可以。

• 查询平均分超过80分的课程的课程号和均分：

-- 对mark表按cid（课程号）分组，筛选出平均成绩（avg(cmark)）大于等于80分的课程
select cid,avg(cmark) from mark group by cid having avg(cmark) >= 80;

• 查询平均分在80分以上的学生的学号和均分：

-- 按sid（学号）分组，筛选出平均成绩大于等于80分的学生
select sid,avg(cmark) from mark group by sid having avg(cmark)>=80;

（三）分组函数总结

• where后不能跟聚合函数，要用聚合函数筛选条件需在having后编写。
• select后查询的字段只能是聚合函数或者group by的字段。
• group by后的字段可以部分出现在select后。

二、多表查询

（一）概念与笛卡尔积问题

多表查询涉及两张及以上的表，但表之间要有关联关系。若直接使用select * from A表 , B表，会产生笛卡尔积，即表中行数相乘的结果，这通常不是预期结果，需要消除。
例如，dept表和emp表直接连接会产生笛卡尔积：

select * from dept; -- 假设查询出4条数据
select * from emp; -- 假设查询出14条数据
select * from dept , emp; -- 会查询出56条（4*14）数据，结果错误

消除笛卡尔积的语法是select * from A表 , B表 where 消除笛卡尔积的条件，条件是表之间关联的依据。如dept和emp表关联依据是deptno字段：

-- 方式一
select * from dept , emp where dept.deptno = emp.deptno;
-- 方式二，使用表别名，使代码更简洁易读
select * from dept d , emp e where d.deptno = e.deptno;

（二）多表查询示例

• 查询学生的姓名、课程名和分数：

-- 从student、course、mark三张表中查询，通过sid和cid关联三张表
select s.sname,c.cname,m.cmark from student s , course c , mark m where m.sid = s.sid and m.cid = c.cid;

• 查询每个学生的总学分（分数超过60分才计分），返回姓名和总学分：

-- 关联course、student、mark三张表，筛选出分数大于60分的记录，按sid和sname分组统计总学分（sum(cval)）
select s.sname,sum(cval) from course c , student s , mark m where m.sid = s.sid and m.cid = c.cid and m.cmark >60 group by m.sid,s.sname;

三、内连接与外连接

（一）内连接

• 隐式内连接：select * from A表 , B表 where 条件。
• 显示内连接：select * from A表 inner join B表 on 条件。
  例如，查询每个班的每门课的均分，显示班级名、课程名、均分：

-- 隐式内连接
select s.sclass,c.cname,avg(m.cmark) from student s ,course c ,mark m where m.sid = s.sid and m.cid = c.cid group by s.sclass,c.cname;
-- 显示内连接
select s.sclass,c.cname,avg(m.cmark) from student s inner join mark m on m.sid = s.sid inner join course c on m.cid = c.cid group by s.sclass,c.cname;

（二）外连接

外连接用于设置基准表，保证基准表的所有内容都能被查询出来。有左外连接select * from A表 left join B表 on 条件和右外连接select * from A表 right join B表 on 条件，左外连接左边表是基准表，右外连接右边表是基准表。
例如，dept表有四个部门，emp表没有40号部门的员工，使用外连接查询：

-- 左外连接，以dept表为基准表
select * from dept d left join emp e on d.deptno = e.deptno;
-- 右外连接，以dept表为基准表
select * from emp e right join dept d on d.deptno = e.deptno;

四、子查询

（一）概念与应用场景

子查询是将一条SQL的执行结果带入到另一条SQL语句中，结果可用于条件判断或作为新表继续操作。

（二）子查询示例

• 查询张三的数学课程分数：

-- 先查询张三的学号和数学课程的cid，再将其作为条件查询分数
select * from mark where sid = (select sid from student where sname = '张三') and cid = (select cid from course where cname = '数学');

• 查询平均成绩超过80分的学生姓名和班级：

-- 先查询出平均分超过80分的学生sid，再通过sid在student表中查询姓名和班级
select * from student where sid in(select sid from mark group by sid having avg(cmark)>=80);

五、Oracle数据库中的伪列

（一）rowid

rowid是表中每一行唯一的物理地址，可用于条件查询，每张表中每一行的rowid都不同。

select * from 表名称 where rowid =?
-- 例如
select * from dept where rowid = 'AAAR3qAAEAAAACHAAC';

（二）rownum

rownum是表的自增操作，从1开始依次自增1，不能作为唯一标识。它不适合做等值判断查询，更适合范围查询。

• 查询前10条数据：

select * from 表名称 where rownum <= 10;
-- 例如查询emp表前5条数据
select rowid ,rownum,emp.* from emp where rownum <= 5;

• 若要查询[6,10]条数据，不能直接使用rownum > 6 and rownum <= 10，需通过子查询解决：

-- 先查询前10条数据并显示rownum
select rownum r , emp.* from emp where rownum <= 10;
-- 再从结果中筛选出r大于6且小于等于10的数据
select * from (select rownum r , emp.* from emp where rownum <= 10) where r>6 and r<=10;

• 通用性分页查询：

-- 假设每页展示pageSize条数据，currentPage表示当前页
-- start为当前页起始行号，end为当前页结束行号
select * from (select rownum r, student.* from student where rownum <= (select count(*) from student)) where r >= ((currentPage - 1)*pageSize + 1) and r <= (currentPage * pageSize);