组函数和子查询训练任务
1.1. 创建测试表
1) 创建表
HR@ENMOEDU>create table t_group
(id number(2) primary key
,name varchar2(10)
,age number(2)
,classno number(2));
2) 插入测试数据
insert into t_group values(1,'zhangsan',25,3);
insert into t_group values(2,'lisi',26,3);
insert into t_group values(3,'wangwu',30,1);
insert into t_group values(4,null,15,1);
insert into t_group values(5,'zhangsan',25,2);
insert into t_group values(6,null,null,null);
commit;
3) 查看效果
HR@ENMOEDU>select * from t_group;
ID NAME AGE CLASSNO
-------- ---------- ---------- ----------
1 zhangsan 25 3
2 lisi 26 3
3 wangwu 30 1
4 15 1
5 zhangsan 25 2
6
6 rows selected.
HR@ENMOEDU>
1.2. 聚集
1.2.1. COUNT
语法:COUNT({ * | [ DISTINCT | ALL ] expr })
1) 统计表中的数据量
HR@ENMOEDU>select count(*) from t_group;
COUNT(*)
----------
6
HR@ENMOEDU>
2) 统计name字段不为空的记录数
--方法一:使用where子句过滤name为null的记录
HR@ENMOEDU>select count(*) from t_group where name is not null;
COUNT(*)
----------
4
HR@ENMOEDU>
--方法二:count函数中,写入name字段的名称进行统计,结果一样是正确的,原因是,count(name)这种方式统计记录数是不统计name为null的记录的
HR@ENMOEDU>select count(name) from t_group;
COUNT(NAME)
-----------
4
HR@ENMOEDU>
1.2.2. SUM
语法:SUM([ DISTINCT | ALL ] expr)
1) 统计age字段值的总和。注:null值是不参与运算的
HR@ENMOEDU>select sum(age) from t_group;
SUM(AGE)
----------
121
HR@ENMOEDU>
2) 统计age字段通过distinct去重后的总和(即:先去重,后求和),null同样不参与运算
HR@ENMOEDU>select sum(distinct age) from t_group;
SUM(DISTINCTAGE)
----------------
96
HR@ENMOEDU>
1.2.3. MIN、MAX
语法:MIN([ DISTINCT] expr)
MAX([ DISTINCT] expr)
expr为一个字符型、日期型或数值类型,如果expr为字符型,则根据ASCII来判断大小
注:测试以max为例,min与max相类似,请自行测试
1) 查看age最大值
HR@ENMOEDU>select max(age) from t_group;
MAX(AGE)
----------
30
HR@ENMOEDU>
2) 验证字符型是通过ASCII来判断大小的
通过目测我们就能发现ASCII最大的name为zhangsan,max(name)的结果,与我们目测分析的相同,也就是验证了字符型是根据ASCII来判断大小的
HR@ENMOEDU>select id,name,ascii(name) from t_group;
ID NAME ASCII(NAME)
-------- ---------- -----------
1 zhangsan 122
2 lisi 108
3 wangwu 119
4
5 zhangsan 122
6
6 rows selected.
HR@ENMOEDU>select max(name) from t_group;
MAX(NAME)
----------
zhangsan
HR@ENMOEDU>
3) 思考题
MAX组函数中使用了distinct是否有意义?无意义
4) 自测题:
ü 创建测试表
SYS@ENMOEDU>create table t_objects as select * from dba_objects;
Table created.
SYS@ENMOEDU>create index idx_t_objects on t_objects(object_id);
Index created.
SYS@ENMOEDU>
ü 请大家自行验证如下SQL:
select min(object_id) from t_objects;
select max(object_id) from t_objects;
select max(object_id),min(object_id) from t_objects;
ü 提示:通过执行时间和执行计划来观察他们在效率上的区别。
打开显示执行时间的方法:
SYS@ENMOEDU>set timing on
打开显示执行计划的方法:
SYS@ENMOEDU>set autot trace exp
1.2.4. AVG
语法:AVG([ DISTINCT] expr)
求平均值
1) 统计age字段值的平均值
HR@ENMOEDU>select avg(age) from t_group;
AVG(AGE)
----------
24.2
HR@ENMOEDU>
2) 统计去重以后的age字段值的平均值
HR@ENMOEDU>select avg(distinct age) from t_group;
AVG(DISTINCTAGE)
----------------
24
HR@ENMOEDU>
3) 小结
我们会发现,两个结果是不同的,为什么?因为distinct去重以后,做平均时分母的值就变了,导致结果的不一致。
注:avg的distinct是有意义的,在开发的过程中,要注意此细节
1.3. 分组
1.3.1. Group by 子句
在数据库查询中,分组是一个非常重要的应用。分组是指将数据表中所有记录中,以某个或者某些列为标准,划分为一组。
进行分组查询应该使用group by子句。group by子句指定分组标准,并将数据源按照该标准进行划分,然后循环处理每组数据。
HR@ENMOEDU>
SELECT classno, SUM(age) AS sum_age,
COUNT(DISTINCT NAME) AS cnt,
AVG(age) AS avg_age,
MIN(age) AS min_age,
MAX(age) AS max_age
FROM t_group
GROUP BY classno;
CLASSNO SUM_AGE CNT AVG_AGE MIN_AGE MAX_AGE
---------- ---------- ---------- ---------- ---------- ----------
0
1 45 1 22.5 15 30
2 25 1 25 25 25
3 51 2 25.5 25 26
问:CNT字段为什么有一个0?
答:是因为CNT字段统计的是name字段的数量,因为按照CLASSNO进行了分组,CLASSNO为null的那一组(行)的name也为null,由于null是不被count所统计的,所以该值为0.
另外,我们还要从这个结果中看出,无论是sum、avg、min还是max,当所统计的值都为null的时候,那么则直接返回null。
1.3.2. Having 子句
where子句可以过滤from子句所指定的数据源,但是对于group by子句所产生的分组无效。为了将分组按照一定条件进行过滤,应该使用having子句。
HR@ENMOEDU>
SELECT classno, SUM(age) AS sum_age,
COUNT(DISTINCT NAME) AS cnt,
AVG(age) AS avg_age,
MIN(age) AS min_age,
MAX(age) AS max_age
FROM t_group
GROUP BY classno
HAVING COUNT(DISTINCT NAME)>=1;
CLASSNO SUM_AGE CNT AVG_AGE MIN_AGE MAX_AGE
---------- ---------- ---------- ---------- ---------- ----------
1 45 1 22.5 15 30
2 25 1 25 25 25
3 51 2 25.5 25 26
HR@ENMOEDU>
第2章 子查询
子查询是指在查询语句的内部嵌入查询(也称嵌套查询),以获得临时的结果集。 Oracle总是自动优化带有子查询的查询语句。如果子查询中的数据源与父查询中的数据可以实现连接操作,那么将转化为连接操作;否则,将首先执行子查询,然后执行父查询
子查询指嵌入在其他SQL中的select语句,也称嵌套查询.
按照子查询返回结果,可将子查询分为:
单行单列
单行多列
多行单列
多行多列
特点:
1.优先执行子查询,主查询再使用子查询的结果
2.子查询返回的列数和类型要匹配
3.子查询要用括号括起来
4.子查询返回多行要用多行关系运算符
2.1. 查询条件中的子查询
HR@ENMOEDU>select last_name from employees where salary > (select salary from employees where last_name='Bloom');
LAST_NAME
-------------------------
Hartstein
Higgins
King
Kochhar
De Haan
Greenberg
Raphaely
Russell
Partners
Errazuriz
Cambrault
Zlotkey
Vishney
Ozer
Abel
15 rows selected.
HR@ENMOEDU>
2.2. 建表语句中的子查询
利用CTAS方式创建表tmp_user_objects(where 1=2 这种不等于的条件只创建的表结构),这种方式就是建表语句中的子查询
HR@ENMOEDU>create table tmp_user_objects as select * from user_objects where 1=2;
Table created.
HR@ENMOEDU>select count(*) from tmp_user_objects;
COUNT(*)
----------
0
2.3. 插入语句中的子查询
HR@ENMOEDU>insert into tmp_user_objects select * from user_objects where object_type='TABLE';
5 rows created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>