关于Oracle和PL/SQL的学习记录4

------高级查询------
1.使用group by子句实现分组
select max(sage) 最大年龄,sdept 所在班级 from stu group by sdept;
group by子句是select语句的可选子句，其功能是为了将表的输出划分为若干个组
group by子句在被定义的数据的基础上建立比较小的租，并且对每个组进行聚合函数计算，或者说其产生每一组总体信息；group by可以把一列当成组合列，其总结组合列中不重复值的信息；使用了group by子句的选择列表中只能包含以下项：
常量值、组合列、表达式(每个表达式为每组返回一个值)

2.按条件查询并分组
含有group by子句的select语句也可以包含where子句，并对满足条件的查询进行分组
select max(sage) 最大年龄,sdept 所在班级 from stu where sgender='男' group by sdept;

3.使用cube汇总数据
group by子句中可以使用cube关键字和rollup关键字，其中cube参数的作用是除了返回由group by子句指定的列外，还返回按组统计的行
select sdept 系部,sno 学号,max(sage) 最大年龄 from stu group by cube(sno,sdept);
cube运算符生成的结果集是多维数据集，其包含了各维度的所有可能组合的交叉表格，即包含维度列中各值的所有可能组合

4.使用rollup汇总数据
select sdept 系部,sno 学号,max(sage) 最大年龄 from stu group by rollup(sno,sdept);
对于group by子句中右边的列中的每个值，rollup操作并不报告左边一列（各列）中值的所有可能组合
cube和rollup之间的区别在于：
cube生成的结果集显示了所选列中值的所有组合的聚合；
rollup生成的结果集显示了所选列中值的某一层次结构的聚合

5.用grouping函数区分null值
select sno,sdept,avg(sage) grouping(sno) from stu group by cube(sno,dept);
使用grouping函数，以sno字段为目标列，可看到该列中含有数据0和1，其中1表示由cube运算符造成的null值，其余null值为事实数据的空值

6.使用having子句过滤分组数据
select max(sage),sdept from stu group by sdept having max(sage)>=25;

having和where的不同之处在于：
在where子句中，在分组进行以前，消除不满足条件的行，在having子句中，在分组之后条件被应用
having可在条件中包含聚合函数，但where不能
where子句作用与表和视图，having子句作用与分组

having子句允许用户为每一个分组指定条件，也即可以根据用户指定的条件来选择行

7.使用order by子句排序
select sno,sname,sage,sbirth,sdept from stu order by sage asc;
根据年龄从小到大排序；
asc表示升序排列
desc表示降序排列
默认排序方式是升序；

8.对字符串列排序
select sno,sname,sage,sbirth,sdept from stu order by sname;
对字符串类型的数据进行排序时，按照的时ascll码的大小顺序；如果指定排序的字段值是中文汉字，则以拼音字母的ascll码值比较而得出的顺序

9.多列排序
select sno,sname,sage,sbirth,sdept from stu order by sdept,sage;
先用第一个字段排序，再用第二个字段排序

10.对查询进行集合运算
select sno from stu
minus
select sno from grade;
在Oracle PL/SQL中的集合运算就是将两个或者多个集合组合成为一个结果集，集合运算包括一下4种：
intersect交集：返回两个查询共有的记录
union all并集：返回各个查询的所有记录，包括重复记录
union并集：返回各个查询的所有记录，不包括重复记录
minus补集：返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

当使用集合操作的时候，查询所返回的列名可以不同，但列数以及列的数据类型必须匹配，否则无法进行运算

返回多个表的共有数据:
select sno from stu
intersect
select sno from grade;
返回各个表的所有数据：
select sno from stu
union all
select sno from grade;

11.无条件多表查询
连接查询：
select stu.sno,stu.sname,stu.dept,grade.sno,grade.cname,grade.score from stu,grade;

12.有共同字段的表等值连接:
select stu.*,grade.* from stu,grade where stu.sno = grade.sno;
含条件的等值连接：
select stu.sno,stu.sname,grade.cname,grade.score from stu,grade where stu.sno=grade.sno and stu.sname='张清';
有共同字段的表非等值连接：
select stu.sno,stu.sname.grade.cname,grade.score from stu,grade where stu.sno<> grade.sno;

13.两个表进行内连接：
是指当两个表的相关字段满足连接条件时，则从这两个表中提取数据并组合成新的记录
select stu.*,grade.* from stu inner join grade onstu.sno = grade.sno;
等值连接也是内连接的一种

两个表进行自然连接：
select stu.*,grade.cname,grade.score from stu inner join grade on stu.sno = grade.sno where stu.sgender = '女';
自然连接子句是基于两个表存在相同名称的列
返回两个表相匹配列中具有相同值的记录
如果名称相同的列数据类型不同，会产生错误

一个表进行自连接：
select distinct s1.sno,s1.sname,s1.sage,s1.sbirth,s1.sdept from stu s1 inner join stu s2 on s1.sage = s2.sage where s1.sno <> s2.sno order by s1.sage desc;
自连接中一般需要使用关键字distinct消除重复记录

14.两个表进行左外连接：
select stu.sno,stu.sname,grade.cname,grade.score from stu left outer join grade on stu.sno = grade.sno;
左连接的特点是显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全；左外连接返回那些存在于坐标而右表中却没有的行，再加上内连接的行

两个表进行右外连接：
select stu.sno,stu.sname,grade.cname,grade.score from stu right outer join grade on stu.sno = grade.sno;

两个表进行全外连接：
select stu.sno,stu.sname,grade.cname,grade.score from stu full outer join grade on stu.sno = grade.sno;
select stu.sno,stu.sname,grade.cname,grade.score from stu left outer join grade on stu.sno = grade.sno(+);
select stu.sno,stu.sname,grade.cname,grade.score from stu right outer join grade on stu.sno(+) = grade.sno;

15.多表连接
select stu.sno,stu.sname,grade.cname,grade.score,course.cscore from stu join grade on stu.sno = grade.sno join course on grade.cname = course.cname where stu.sname = '张清';