【sql】编写基本的SQL SELECT语句三

 组函数和子查询训练任务

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2140203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2140203/