课程 四 组函数
本课重点：
1、了解可用的组函数
2、说明每个组函数的使用方法
3、使用GROUP BY
4、通过HAVING来限制返回组
注意：以下实例中标点均为英文半角
一、概念：
组函数是指按每组返回结果的函数。
组函数可以出现在SELECT和HAVING 字段中。
GROUP BY把SELECT 的结果集分成几个小组。
HAVING 来限制返回组，对RESULT SET而言。
二、组函数：(#号的函数不做重点)
1、AVG
2、COUNT
3、MAX
4、MIN
5、STDDEV #
6、SUM
7、VARIANCE #
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
实例1：一个混合实例，说明所有问题：
SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
2 SUM(salary)
3 FROM s_emp
4 WHERE UPPER(title) LIKE ’SALES%’;
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
1476 1525 1400 7380
说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。
* MIN MAX函数可以接任何数据类型。
如果是MIN(last_name), MAX(last_name)，返回的是什么呢？
千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt > cssingkdkdk > adopt > acccc
实例2：
SQL> SELECT COUNT(commission_pct)
2 FROM s_emp
3 WHERE dept_id = 31;
返回所有非空行个数
三、GROUP BY的应用：
先看一个简单实例：
SQL> SELECT credit_rating, COUNT(*) ”# Cust”
2 FROM s_customer
3 GROUP BY credit_rating;
注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#＄_，什么对象的名字都如此。当然空格也是可以的。
复杂实例：
SQL> SELECT title, SUM(salary) PAYROLL
2 FROM s_emp
3 WHERE title NOT LIKE ’VP%’
4 GROUP BY title
5 ORDER BY SUM(salary);
这里要注意一下几个CLAUSE的先后次序。
WHERE在这里主要是做参与分组的记录的限制。
**另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUP BY !否则要出错的!信息为：ERROR at line 1:
ORA-00937: not a single-group group function
理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。
结论：不加分组函数修饰的列必定要出现在GROUP BY 里。
错误实例：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 WHERE AVG(salary) > 2000
4 GROUP BY dept_id;
WHERE AVG(salary) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
应在GROUP BY 后面加上HAVING AVG(salary) > 2000;
因为是用来限制组的返回。
多级分组实例：
SQL> SELECT dept_id, title, COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id, title;
就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。
顺序对结果有决定性的影响。
总结：本课我们主要学习了分组函数的使用及如何进行分组查询，我们可以想像一下，SQL SERVER中有COMPUTE BY，来进行分组总数的计算，但在ORACLE中是没有的。大家可以建立一个有多个列，多个重复值的表，然后进行各种分组的演示，用得多了，自然明了。
课程 五 子查询
本课重点：
1、在条件未知的情况下采用嵌套子查询
2、用子查询做数据处理
3、子查询排序
注意：以下实例中标点均为英文半角
一、概述：
子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。
子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。
注意：1、子查询必须在一对圆括号里。
2、比较符号：>, =, 或者 IN.
3、子查询必须出现在操作符的右边
4、子查询不能出现在ORDER BY里 (试题中有时出现找哪行出错)
二、子查询的执行过程：
NESTED QUERY MAIN QUERY
SQL> SELECT dept_id SQL> SELECT last_name, title
2 FROM s_emp 2 FROM s_emp
3 WHERE UPPER(last_name)=’BIRI’; 3 WHERE dept_id =
这里 ，每个查询只运行一次。当然，子查询要首先被执行，大家设想一下，如果子查询中有一个以上的人的LASTNAME为BIRI，会如何？-----会出错，因为不能用=来连接。
ORA-1427: single-row subquery returns more than
one row
以上的查询也被称之为 单行子查询。
DELECT子查询实例：
delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
select pro_name from new_product where pro_addr in ('bj','sh'))
三、子查询中的GROUP 函数的应用
实例 1：
SQL> SELECT last_name, title, salary
2 FROM s_emp
3 WHERE salary <
4 (SELECT AVG(salary)
5 FROM s_emp);
实例2：
选择出工资最高的员工的家庭住址：
select emp_addr from employees where salary =
(select max(salary) from employees);
这是一个简单实用的例子，可以衍生出很多情况，在实际应用经常出现，请大家多多思考。
实例3：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING AVG(salary) >
5 (SELECT AVG(salary)
6 FROM s_emp
7 WHERE dept_id = 32);
子查询被多次执行，因为它出现在HAVING 子句中。
SQL> SELECT title, AVG(salary)
2 FROM s_emp
3 GROUP BY title
4 HAVING AVG(salary) =
5 (SELECT MIN(AVG(salary))
6 FROM s_emp
7 GROUP BY title);
对子查询，我们了解这么多在理论上已经覆盖了所有的知识点，对于UPDATE 和DELETE的子查询，不作为重点，但也要练习掌握。今天到这，谢谢大家。
课程 六 运行时应用变量
本课重点：
1、创建一个SELECT语句，提示USER在运行时先对变量赋值。
2、自动定义一系列变量，在SELECT运行时进行提取。
3、在SQL PLUS中用ACCEPT定义变量
注意：以下实例中标点均为英文半角
一、概述：
变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。
1、我们这里的运行时，指的是在SQL PLUS中运行。
2、ACCEPT ：读取用户输入的值并赋值给变量
3、DEFINE：创建并赋值给一个变量
4、在做REPORT时经常使用，比如对某个部门的销售信息进行统计，部门名称可以以变量代替。
SQL PLUS不支持对输入数据的有效性检查，因此提示要简单且不模棱两可。
二、应用实例：
1、SQL> SELECT id, last_name, salary
2 FROM s_emp
3 WHERE dept_id = &department_number;
2、可以在赋值前后进行比较：
SET VERIFY ON
.....
1* select * from emp where lastname='&last_name'
输入 last_name 的值: adopt
原值 1: select * from emp where lastname='&last_name'
新值 1: select * from emp where lastname='adopt'
----如果在原语句中没有单引号，那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。
SET VERIFY OFF 之后，原值和新值这两句消失。这在ORACLE8I中是默认为ON。
3、子句为变量：WHERE &condition; 要注意引号
三、DEFINE和ACCEPT的应用：
1、SET ECHO OFF //使内容不 显示在用户界面
ACCEPT p_dname PROMPT ’Provide the department name: ’
SELECT d.name, r.id, r.name ”REGION NAME”
FROM s_dept d, s_region r
WHERE d.region_id = r.id
AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
/
SET ECHO ON
存为文件：l7prompt.SQL
SQL> START l7prompt
Provide the department name: sales
2、SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = ”sales” (CHAR)
SQL> SELECT name
2 FROM s_dept
3 WHERE lower(name) = ’&dname’;
可以正常执行了。
SQL> DEFINE dname 主要是显示当前的变量是否赋值，值是什么。当然，我们可以用UNDEFINEGO 来使变量恢复初始，不然它会一直保持下去。
3、如果变量在SQL SCRIPT文件中确定 ：可以SQL> START l7param President 来赋值。
总结：本课主要针对较古老的SQLPLUS方法，在REPORT和结果集生成方面使用变量，达到方便操作，动态修改的目的。
本课重点：
1、了解可用的组函数
2、说明每个组函数的使用方法
3、使用GROUP BY
4、通过HAVING来限制返回组
注意：以下实例中标点均为英文半角
一、概念：
组函数是指按每组返回结果的函数。
组函数可以出现在SELECT和HAVING 字段中。
GROUP BY把SELECT 的结果集分成几个小组。
HAVING 来限制返回组，对RESULT SET而言。
二、组函数：(#号的函数不做重点)
1、AVG
2、COUNT
3、MAX
4、MIN
5、STDDEV #
6、SUM
7、VARIANCE #
语法：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
实例1：一个混合实例，说明所有问题：
SQL> SELECT AVG(salary), MAX(salary), MIN(salary),
2 SUM(salary)
3 FROM s_emp
4 WHERE UPPER(title) LIKE ’SALES%’;
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
1476 1525 1400 7380
说明：很多函数，我们在讲函数的已经向大家介绍过，但在此为何叫分组函数呢，主要是因为它们可以与GROUP BY来形成对不同组的计算，相当于在很多值中进行挑选。
* MIN MAX函数可以接任何数据类型。
如果是MIN(last_name), MAX(last_name)，返回的是什么呢？
千万记住，不是指LAST_NAME的长度，而是指在FIRST字母的前后顺序，第一个相同，然后比较第二个，如：xdopt > cssingkdkdk > adopt > acccc
实例2：
SQL> SELECT COUNT(commission_pct)
2 FROM s_emp
3 WHERE dept_id = 31;
返回所有非空行个数
三、GROUP BY的应用：
先看一个简单实例：
SQL> SELECT credit_rating, COUNT(*) ”# Cust”
2 FROM s_customer
3 GROUP BY credit_rating;
注意这里别名的应用，复习一下从前的课程，加了引号后，就可以用特殊字符，但也仅有三个：#＄_，什么对象的名字都如此。当然空格也是可以的。
复杂实例：
SQL> SELECT title, SUM(salary) PAYROLL
2 FROM s_emp
3 WHERE title NOT LIKE ’VP%’
4 GROUP BY title
5 ORDER BY SUM(salary);
这里要注意一下几个CLAUSE的先后次序。
WHERE在这里主要是做参与分组的记录的限制。
**另外，如果要选取出来一个不加组函数的列，如上面的TITLE，就要把这个列GROUP BY !否则要出错的!信息为：ERROR at line 1:
ORA-00937: not a single-group group function
理论很简单，如果不GROUP BY TITLE，显示哪一个呢？这个在试题中经常出现。
结论：不加分组函数修饰的列必定要出现在GROUP BY 里。
错误实例：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 WHERE AVG(salary) > 2000
4 GROUP BY dept_id;
WHERE AVG(salary) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
应在GROUP BY 后面加上HAVING AVG(salary) > 2000;
因为是用来限制组的返回。
多级分组实例：
SQL> SELECT dept_id, title, COUNT(*)
2 FROM s_emp
3 GROUP BY dept_id, title;
就是先按照DEPT_ID分组，当DEPT_ID相同的时候，再按TITLE分组，而COUNT(*)以合成的组计数。
顺序对结果有决定性的影响。
总结：本课我们主要学习了分组函数的使用及如何进行分组查询，我们可以想像一下，SQL SERVER中有COMPUTE BY，来进行分组总数的计算，但在ORACLE中是没有的。大家可以建立一个有多个列，多个重复值的表，然后进行各种分组的演示，用得多了，自然明了。
课程 五 子查询
本课重点：
1、在条件未知的情况下采用嵌套子查询
2、用子查询做数据处理
3、子查询排序
注意：以下实例中标点均为英文半角
一、概述：
子查询是一种SELECT句式中的高级特性，就是一个SELECT语句作为另一个语句的一个段。我们可以利用子查询来在WHERE字段中引用另一个查询来攻取值以补充其无法事先预知的子结果。
子查询可以用在WHERE子句，HAING子句，SELECT或DELETE语句中的FROM 子句。
注意：1、子查询必须在一对圆括号里。
2、比较符号：>, =, 或者 IN.
3、子查询必须出现在操作符的右边
4、子查询不能出现在ORDER BY里 (试题中有时出现找哪行出错)
二、子查询的执行过程：
NESTED QUERY MAIN QUERY
SQL> SELECT dept_id SQL> SELECT last_name, title
2 FROM s_emp 2 FROM s_emp
3 WHERE UPPER(last_name)=’BIRI’; 3 WHERE dept_id =
这里 ，每个查询只运行一次。当然，子查询要首先被执行，大家设想一下，如果子查询中有一个以上的人的LASTNAME为BIRI，会如何？-----会出错，因为不能用=来连接。
ORA-1427: single-row subquery returns more than
one row
以上的查询也被称之为 单行子查询。
DELECT子查询实例：
delete from new_table where cata_time > to_date('19990901','yyyymmdd') and pro_name=(
select pro_name from new_product where pro_addr in ('bj','sh'))
三、子查询中的GROUP 函数的应用
实例 1：
SQL> SELECT last_name, title, salary
2 FROM s_emp
3 WHERE salary <
4 (SELECT AVG(salary)
5 FROM s_emp);
实例2：
选择出工资最高的员工的家庭住址：
select emp_addr from employees where salary =
(select max(salary) from employees);
这是一个简单实用的例子，可以衍生出很多情况，在实际应用经常出现，请大家多多思考。
实例3：
SQL> SELECT dept_id, AVG(salary)
2 FROM s_emp
3 GROUP BY dept_id
4 HAVING AVG(salary) >
5 (SELECT AVG(salary)
6 FROM s_emp
7 WHERE dept_id = 32);
子查询被多次执行，因为它出现在HAVING 子句中。
SQL> SELECT title, AVG(salary)
2 FROM s_emp
3 GROUP BY title
4 HAVING AVG(salary) =
5 (SELECT MIN(AVG(salary))
6 FROM s_emp
7 GROUP BY title);
对子查询，我们了解这么多在理论上已经覆盖了所有的知识点，对于UPDATE 和DELETE的子查询，不作为重点，但也要练习掌握。今天到这，谢谢大家。
课程 六 运行时应用变量
本课重点：
1、创建一个SELECT语句，提示USER在运行时先对变量赋值。
2、自动定义一系列变量，在SELECT运行时进行提取。
3、在SQL PLUS中用ACCEPT定义变量
注意：以下实例中标点均为英文半角
一、概述：
变量可以在运行时应用，变量可以出现在WHERE 字段，文本串，列名，表名等。
1、我们这里的运行时，指的是在SQL PLUS中运行。
2、ACCEPT ：读取用户输入的值并赋值给变量
3、DEFINE：创建并赋值给一个变量
4、在做REPORT时经常使用，比如对某个部门的销售信息进行统计，部门名称可以以变量代替。
SQL PLUS不支持对输入数据的有效性检查，因此提示要简单且不模棱两可。
二、应用实例：
1、SQL> SELECT id, last_name, salary
2 FROM s_emp
3 WHERE dept_id = &department_number;
2、可以在赋值前后进行比较：
SET VERIFY ON
.....
1* select * from emp where lastname='&last_name'
输入 last_name 的值: adopt
原值 1: select * from emp where lastname='&last_name'
新值 1: select * from emp where lastname='adopt'
----如果在原语句中没有单引号，那么在输入值的时候要手工加上单引号。一般字符和日期型要在语句中加上单引号。
SET VERIFY OFF 之后，原值和新值这两句消失。这在ORACLE8I中是默认为ON。
3、子句为变量：WHERE &condition; 要注意引号
三、DEFINE和ACCEPT的应用：
1、SET ECHO OFF //使内容不 显示在用户界面
ACCEPT p_dname PROMPT ’Provide the department name: ’
SELECT d.name, r.id, r.name ”REGION NAME”
FROM s_dept d, s_region r
WHERE d.region_id = r.id
AND UPPER(d.name) LIKE UPPER(’%&p_dname%’)
/
SET ECHO ON
存为文件：l7prompt.SQL
SQL> START l7prompt
Provide the department name: sales
2、SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = ”sales” (CHAR)
SQL> SELECT name
2 FROM s_dept
3 WHERE lower(name) = ’&dname’;
可以正常执行了。
SQL> DEFINE dname 主要是显示当前的变量是否赋值，值是什么。当然，我们可以用UNDEFINEGO 来使变量恢复初始，不然它会一直保持下去。
3、如果变量在SQL SCRIPT文件中确定 ：可以SQL> START l7param President 来赋值。
总结：本课主要针对较古老的SQLPLUS方法，在REPORT和结果集生成方面使用变量，达到方便操作，动态修改的目的。
