script_66.txt

orale培训教材包括了所有的sql说明和实例

--在以后的练习中，我们经常要用到以下两个表，所以先讲解一下这两个表的结构及含意


 名称  产品库存表  :plsql101_product用于存放产品信息                               
 ---------------------------------------------------------------
 PRODUCT_NAME                           产品名称
 PRODUCT_PRICE                          产品单价
 QUANTITY_ON_HAND                       库存数
 LAST_STOCK_DATE                        最后进货日期

CREATE TABLE plsql101_product ( 
     product_name     VARCHAR2(25), 
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;
 ---------------------------------------------------------------


名称      采购信息表plsql101_purchase                     
--------------------------------------------------
PRODUCT_NAME        商品名称           
SALESPERSON         经手人(编号)           
PURCHASE_DATE       采购日期           
QUANTITY            采购数量  

CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;
--------------------------------------------------



---------- sm_group.txt ----------
/*
 * 范例名称：分组函数
 * 文件名称：sm_group.txt
 */
drop table sm_group;
CREATE TABLE sm_group
(FIELD1  VARCHAR2(4),
FIELD2  NUMBER(2),
FIELD3  DATE);

--data insert
INSERT INTO sm_group VALUES('A',1,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('A',2,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',1,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',2,TO_DATE('1999/09/09','YYYY/MM/DD'));

--INSERT INTO sm_group VALUES('A',1,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('A',null,TO_DATE('1999/09/09','YYYY/MM/DD'));

--field1为空，按field1分组，null为一组
INSERT INTO sm_group VALUES(null,3,TO_DATE('1999/09/09','YYYY/MM/DD'));

--sum
select sum(FIELD2) from sm_group;
select sum(FIELD2) from sm_group
GROUP BY FIELD1;

select sum(FIELD2) from sm_group;
select sum(FIELD2) from sm_group
GROUP BY FIELD1;

select sum(FIELD2) from sm_group;
select sum(FIELD2) from sm_group
GROUP BY FIELD1,FIELD2;

--COUNT
SELECT COUNT(*) FROM sm_group;
SELECT COUNT(1) FROM sm_group;

SELECT COUNT(*) FROM sm_group GROUP BY FIELD1;
SELECT COUNT(FIELD1) FROM sm_group GROUP BY FIELD1;
SELECT COUNT(FIELD2) FROM sm_group GROUP BY FIELD1;
--COUNT AND NULL
INSERT INTO sm_group VALUES('A',NULL,TO_DATE('1999/09/09','YYYY/MM/DD'));

SELECT * FROM sm_group;
--count field2.NULL 不能被记入。
SELECT COUNT(FIELD2) FROM sm_group GROUP BY FIELD1;

DELETE FROM sm_group WHERE field2 is null;

--AVG
SELECT AVG(FIELD1) FROM sm_group ;
--ERR 无效数字

SELECT AVG(FIELD2) FROM sm_group;
SELECT AVG(FIELD2) FROM sm_group 
GROUP BY FIELD1;
--AVG AND NULL
INSERT INTO sm_group VALUES('A',NULL,TO_DATE('1999/09/09','YYYY/MM/DD'));
SELECT AVG(FIELD2) FROM sm_group GROUP BY FIELD1;
SELECT * FROM sm_group;


---------- group_count.txt ----------
/*
 * 范例名称：COUNT函数
 * 文件名称：group_count.txt
 */

SELECT COUNT(*) FROM plsql101_purchase;
SELECT COUNT(product_name) FROM plsql101_purchase;

--同样ok.与COUNT(*)类似
SELECT COUNT(1) FROM plsql101_purchase;

SELECT COUNT(1) FROM plsql101_product;

SELECT COUNT(product_name) FROM plsql101_product;

SELECT COUNT(last_stock_date) FROM plsql101_product;

--分组函数可以进行计算
SELECT COUNT(last_stock_date) / COUNT(product_name) 
FROM plsql101_product;



---------- sm_min.txt ----------
/*
 * 范例名称：MIN函数
 * 文件名称：sm_min.txt
 */

--MIN 
SELECT MIN(FIELD1) FROM sm_group ;
--文本字段
INSERT INTO sm_group VALUES('a',NULL,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('a',2,TO_DATE('1999/09/08','YYYY/MM/DD'));
SELECT MIN(FIELD1) FROM sm_group ;

--MIN AND NULL
SELECT MIN(FIELD2) FROM sm_group ;
SELECT MIN(FIELD2),FIELD1 FROM sm_group GROUP BY FIELD1;

SELECT * FROM sm_group;



---------- sm_max.txt ----------
/*
 * 范例名称：MAX函数
 * 文件名称：sm_max.txt
 */

--Max 
SELECT Max(FIELD1) FROM sm_group ;
--文本字段.
--INSERT INTO sm_group VALUES('b',NULL,TO_DATE('1999/09/09','YYYY/MM/DD'));
--INSERT INTO sm_group VALUES('b',5,TO_DATE('1999/09/08','YYYY/MM/DD'));
SELECT MAX(FIELD1) FROM sm_group ;

--MAX AND NULL
SELECT MAX(FIELD2) FROM sm_group ;
SELECT MAX(FIELD2),FIELD1 FROM sm_group GROUP BY FIELD1;
--SELECT MIN(FIELD2),FIELD1 FROM sm_group GROUP BY FIELD1;--结论：MAX,MIN全不计算NULL

SELECT * FROM sm_group;



---------- group_by.txt ----------
/*
 * 范例名称：练习
 * 文件名称：group_by.txt
 */
drop plsql101_purchase;
--重建表格
CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     salesperson   VARCHAR2(3),
     purchase_date DATE, 
     quantity      NUMBER(4,2)
     )
;
SELECT * FROM plsql101_purchase;

--如果没有如下数据，插入。
insert into plsql101_purchase values('豆腐','张',to_date('2002/01/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','张',to_date('2002/02/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','关',to_date('2002/03/01','yyyy/mm/dd'),10);
insert into plsql101_purchase values('钢笔','关',to_date('2002/04/01','yyyy/mm/dd'),10);



--求各商品销售总量
SELECT product_name, SUM(quantity) 
FROM   plsql101_purchase
GROUP BY product_name;

----

SQL> desc plsql101_purchase:（）
 名称                           
 ----------------------------
 PRODUCT_NAME                    
 SALESPERSON                    
 PURCHASE_DATE                  
 QUANTITY                       

--综合练习要求
求采购信息表（plsql101_purchase）
1各商品：
采购总量,每次采购的平均数量，
一共有多少次采购，
单次采购的最低数量，单次采购的最高数量

扩展：
2求采购信息表（plsql101_purchase）按年统计：
采购商品的总数量，总采购次数

3按年统计各产品的采购信息:
采购总量,每次采购的平均数量，
一共有多少次采购，
单次采购的最低数量，单次采购的最高数量



------------------------------ sm_having.txt ----------
/*
 * 范例名称：HAVING子句
 * 文件名称：sm_having.txt
 */

DROP TABLE sm_group;

CREATE TABLE sm_group
(FIELD1  VARCHAR2(4),
FIELD2  NUMBER(2),
FIELD3  DATE);

--data insert
INSERT INTO sm_group VALUES('A',2,TO_DATE('1999/09/09','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('A',2,TO_DATE('1999/09/08','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',1,TO_DATE('1999/09/07','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('B',2,TO_DATE('1999/09/06','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('C',3,TO_DATE('1999/09/05','YYYY/MM/DD'));
INSERT INTO sm_group VALUES('C',3,TO_DATE('1999/09/04','YYYY/MM/DD'));

--HAVING
--HAVING中使用分组函数
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING sum(FIELD2)>=2;

select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING sum(FIELD2)>=4;

select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING MAX(FIELD1)>'A';
--ok
--HAVING中直接使用字段，（不是分组函数）
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';
--ok
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING MAX(FIELD3)>'05_9月_99';
ok
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD3>'05_9月_99';
--err!FIELD3不在GROUP BY中。

--SELECT 
select sum(FIELD2),MAX(FIELD3) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';

select sum(FIELD2),MAX(FIELD3) from sm_group
GROUP BY FIELD1
HAVING FIELD3>'A';

select FIELD1,sum(FIELD2),MAX(FIELD3) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';

select sum(FIELD2),FIELD3 from sm_group
GROUP BY FIELD1;
--ERR.FIELD3不在GROUP BY中

  
---------- having.txt ----------
/*
 * 范例名称：HAVING子句
 * 文件名称：having.txt
 */

--HAVING 子句TEST
select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD1>'A';

select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING MAX(FIELD3)>'05_9月_99';

select sum(FIELD2) from sm_group
GROUP BY FIELD1
HAVING FIELD3>'05_9月_99';



---------- group_all.txt ----------
/*
 * 范例名称：HAVING综合练习
 * 文件名称：group_all.txt
 */



SELECT SUBSTR(product_name, 1, 15) "商品", 
       SUM(quantity) "采购总量",     --采购总量
       AVG(quantity) "平均数量",     --每次采购的平均数量
       COUNT(quantity) "采购总次数", --一共有采购多少次
       MIN(quantity) "最低数量",     --单次采购的最低数量
       MAX(quantity) "最高数量"      --单次采购的最高数量
FROM   plsql101_purchase
GROUP BY product_name
HAVING SUM(quantity) < 30; --总采购数量<30的商品



SELECT SUBSTR(product_name, 1, 15) , 
       SUM(quantity) ,
       AVG(quantity) ,
       COUNT(quantity) ,
       MIN(quantity) ,
       MAX(quantity) 
FROM   plsql101_purchase
GROUP BY product_name
HAVING SUM(quantity) >= 100;