一、实验目的

 熟练掌握数据表的连接查询、嵌套查询和集合查询以及数据更新为后继学习作准备。

二、实验属性

    1、了解并掌握SQL企业控制管理器的使用;

    2、掌握基本表的数据查询。

三、实验仪器设备及器材

    1、计算机。

     2、SQL SERVER 2008数据库。

四、 实验要求

    1、预习教材第三章，熟悉SQL语句。

    2、掌握多表查询语句的一般格式。

    3、掌握多表无条件、有条件查询及查询结果排序与分组。

    4、掌握多表连接、嵌套和集合查询语句的一般格式及各种使用方法。

五、实验原理

    SQL语言应用。

六、实验步骤

    1、启动SQL查询分析器；

    2、选择SQL　SERVER后，按确认；

    3、选择数据库；

    4、验证如下例子：

      1、 聚集函数的使用

         1.1  查询学生总人数。

         1.2  查询选修了课程的学生人数。

         1.3  查询选修C01号课程的学生平均成绩。

         1.4  查询选修C01号课程的学生最高分数。

      2、 查询结果分组

               2.1  求每门课的课程号(Cno)及相应的选课人数。

         2.2  查询选修了3门或3门以上课程的学生学号(Sno)。

      3 、不同表之间的连接查询

         3.1  查询每个学生及其选修课程的情况。

         3.2 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

         3.3 查询考试成绩在90分以上的学生学号(Sno)、姓名(Sname)、 选修的课程名(Cname)。

      4 带谓词IN的嵌套查询

               4.1 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。

         4.2 查询与“李勇”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

七、 操作步骤

    1、 聚集函数的使用

      1.1  查询学生总人数。

           I 、在创建查询框中输入SQL语句，如下图所示：

                 

          II、执行SQL语句，其查询结果如下图所示：

                  

     1.2  查询选修了课程的学生人数。

        I 、在创建查询框中输入SQL语句，如下图所示：

                

       II 、执行SQL语句，其查询结果如下图所示：

           

   1.3  查询选修C01号课程的学生平均成绩。

        I 、在创建查询框中输入SQL语句，如下图所示：

           

    II 、执行SQL语句，其查询结果如下图所示：

              

  1.4  查询选修C01号课程的学生最高分数。

      I 、在创建查询框中输入SQL语句，如下图所示：

            

    II 、执行SQL语句，其查询结果如下图所示：

      

  2、 查询结果分组

    2.1  求每门课的课程号(Cno)及相应的选课人数。

       I 、在创建查询框中输入SQL语句，如下图所示：

            

  II 、执行SQL语句，其查询结果如下图所示：

            

   2.2  查询选修了3门或3门以上课程的学生学号(Sno)。

      I 、在创建查询框中输入SQL语句，如下图所示：

           

    II 、执行SQL语句，其查询结果如下图所示：

       

  3 、不同表之间的连接查询

   3.1 查询每个学生及其选修课程的情况。

      I 、在创建查询框中输入SQL语句，如下图所示：

           

    II 、执行SQL语句，其查询结果如下图所示：

                

  3.2查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名 (Cname)及成绩(Grade)。

     I 、在创建查询框中输入SQL语句，如下图所示：

           

      II、执行SQL语句，其查询结果如下图所示：

           

  3.3查询考试成绩在90分以上的学生学号(Sno)、姓名(Sname)、选修的课程名(Cname)。

     I 、在创建查询框中输入SQL语句，如下图所示：

           

      II、执行SQL语句，其查询结果如下图所示：

               

  4 带谓词IN的嵌套查询

   4.1 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在 系(Sdept)。

      I 、在创建查询框中输入SQL语句，如下图所示：

            

      II、执行SQL语句，其查询结果如下图所示：

           

  4.2 查询与“李勇”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

     I 、在创建查询框中输入SQL语句，如下图所示：

            

      II、执行SQL语句，其查询结果如下图所示：

                      

八、实验总结

   通过此次上机实验，使我更加熟悉了对SQL语句及其应用，虽然是第一次上机调试但并不感到陌生。在SQL语句调试的过程中虽错误总是千奇百怪，但根据老师教授的知识很快的得以纠正，完成了本次实验，通过本次实验，深刻的意识到自身的不足，也明白了理论与实践的差距，同时自己学过的知识也得以加深理解与巩固，感谢老师给予我们机会，使我们的知识更加牢固！

1. GROUP BY

假如有如下一张表inoutinfo，

status	number	spend
正在车场	粤BS8120	10
已出车场	粤BS8120	15
已出车场	粤BS8120	20
正在车场	粤BS8121	25
已出车场	粤BS8121	30
已出车场	粤BS8121	35

查询同辆车的进出记录（GROUP BY + 单个字段）

select number as 车牌号, count(*) as 数量
from inoutinfo
Group by number

测试结果：

解读：1. 这条语句的执行顺序：查询inoutinfo中的记录-按照number分组-输出number和count(*)这两个字段；
2. 输出的字段，必须要在Group by子句或包含在聚合函数中，例如下面的语句将会报错，

--报错：选择列表中的列 'inoutinfo.spend' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中
select number as 车牌号, count(*), spend as 数量
from inoutinfo
Group by number

那么，为什么会这样呢？
其实，道理很简单，我们根据number分组，可以分成2组（“粤BS8120”“粤BS8121”），说明最终显示只有两笔记录。因此对于所有输出字段来说，要么有出现在Group by子句中，将记录汇总显示，要么通过聚合函数将记录处理成单值显示。
常见的聚合函数有：count，sum，avg，max，min；

查询同辆车的不同状态下的进出记录（GROUP BY + 多个字段）

select number 车牌号, status 状态, SUM(spend) 费用
from inoutinfo
group by number, status

测试结果：

解读：1. 分组顺序是按照group by后面的字段顺序依次分组，例如先按照number分成“粤BS8120”“粤BS8121”，然后按照status将之前的每一个组又分成两组，“已出车场”“正在车场”：所以最终有四组：“粤BS8120 + 已出车场”，“粤BS8120 + 正在车场”，“粤BS8121 + 已出车场”，“粤BS8121 + 正在车场”，然后根据分组，通过聚合函数sum统计费用；
2. 为了显示清楚，建议输出字段的顺序和GROUP BY子句中字段的顺序要保持一致；
3. GROUP BY后面字段的顺序，不影响最终的分组结果。

2. GROUP BY ALL

在GROUP BY的后面加上一个ALL，表示对通过WHERE子句查询得到的结果集进行分组查询后，依然显示被WHERE子句过滤掉的记录，但是其他通过聚合函数统计的数据都会显示0或NULL。

select number 车牌号, COUNT(*) 数量, SUM(spend) 费用
from inoutinfo
where number = '粤BS8120'
group by all number

测试结果：

解读：1. where子句在group by子句之前执行，因此粤BS8121的相关记录在分组前已经被过滤掉了，但是在结果集中还是显示出来了，只是对应的数量和费用显示为0和NULL。

3. GROUP BY WITH CUBE

GROUP BY 后面加上CUBE运算符后，结果集会在GROUP BY分组统计的结果集基础上，增加归纳各个单独字段和相互混合字段后统计得到的多维交叉结果集。下面我们用几个例子来说明，

单个字段的分组查询

select number 车牌号, COUNT(*) 数量
from inoutinfo
group by number with cube

测试结果：

解读：1. 与之间没有加CUBE的结果集对比，发现多了一笔记录（NULL，6），这就是通过归纳车牌号这个字段后，统计得到的记录， 这里的归纳称为“小计”，用NULL填充；
2. 其实上面的那个NULL易于统计数据中本身包含的NULL值混淆，所以我们可以采用grouping这个函数解决这个问题：grouping作用于归纳出来的NULL，返回1，作用于查询得到的NULL，返回0。修正后的代码如下，

select 
case
when(grouping(number)= 1) then '小计' 
else number
end 车牌号, COUNT(*) 数量
from inoutinfo
group by number with cube

测试结果：

两个字段的分组查询

select 
number 车牌号, status 状态, COUNT(*) 数量
from inoutinfo
group by number, status with cube

测试结果：

解读：1. 通过前面可知，GROUP BY分组会产生四笔记录，对应上图中的，1、2、4、5。通过归纳车牌号，得到3和6两笔记录；通过归纳状态得到8和9两笔记录；通过归纳车牌号和状态得到7这笔记录；
2. GROUP BY后面字段的顺序不影响分组结果。

4. GROUP BY WITH ROLLUP

ROLLUP与CUBE作用一样，都是在GROUP BY的基础上，进一步归纳统计数据，但是ROLLUP比CUBE的限制要多。它不会将所有的字段混合归纳，而是根据GROUP BY后面字段的顺序，从高等级字段向低等级字段归纳，因此GROUP BY后面字段的顺序会影响分组结果。

select 
number 车牌号, status 状态, COUNT(*) 数量
from inoutinfo
group by number, status with rollup

测试结果：

解读：1. 与上面的结果对比，发现结果集少了两笔，因为它没有归纳车牌号下不同状态的统计数据。按照字段等级，status在number字段的后面，所以status字段的等级比number的高，所以先归纳status字段，得到3和6两笔记录，然后根据3和6归纳车牌号字段，得到7这笔记录。

5. GROUP BY和HAVING、WHERE、ORDER BY一起使用

select 
number, status, COUNT(*) 数量, SUM(spend) total
from inoutinfo2
where status = '已出车场'
group by number, status
having SUM(spend) > 20
order by total asc

测试结果：

解读： 1.它们四者的执行顺序：where > group by > having > order by;
2. 因为where是在group by之前执行，所以where子句中不能包含聚合函数，而having就可以；
3. having用于过滤分组后的结果集，所以不能使用输出字段的别名进行判读。而执行order by时，结果集已经查询完成，所以在order by中能使用输出字段的别名进行排序。

Oracle复习（三）

SQL高级查询：

• 默认日期格式：日期数据的格式是dd-mon-yy

设置默认的日期格式 
ALTER SESSION SET NLS_DATE_FORMAT=‘date_format’; 
date_format参数既可以采用 month-dd-yyyy格式，也可以采用yyyy-month-dd格式， 还可以采用yyyy-dd-month格式

• 日期-字符的转换

可以把字符 数据转变成日期数据，也可以把日期数据转变 成字符数据
 TO_CHAR()函数 
 TO_CHAR(sysdate, 'yyyy month dd, HH24:MI:SS') 
 
 TO_DATE()函数 
 TO_DATE('2008-09-30', 'yyyy-mm-dd')

层次查询：

select [Level]列名 FROM 表名 [WHERE]
                                      [START WITH  列=值]
                                      [CONNECT BY 条件]


eg： 查emp表中 
select empno,ename,mgr from emp 
start with empno = 7839 
connect by prior empno = mgr


以树状形式显示：
COLUMN empno FORMAT A30
select lpad(' ',5*level-1)||empno EMPNO, lpad(' ',5*level-1)||ename ENAME FROM emp 
START WITH empno = 7839 
CONNECT BY PRIOR empno = mgr

情景查询：

 在执行select操作过程中，经常需要对不同的 数据显示不同的结果。也就是说，在不同的情 景下，需要显示不同的结果。
 在Oracle 10g系统中，可以使用 decode()函数和case表达式完成情景查询
 
 eg:
 decode(列，条件，结果, 
                条件，结果, 
                条件，结果, 
                条件，结果, 
                ，结果 )
 SELECT ename,sal,         
           decode(deptno, '10', '会计部',                           
                                '20', '研发部',                           
                                '30', '销售部',                           
                                '其他部门')     
 FROM emp;