PL/SQL 速成笔记

主要书籍：Ben Forta《Oracle pl/sql 必知必会 》

论坛贴参考：无法详实记载，仅为学习笔记，也希望对你有所帮助，如有错误欢迎指正

导入素材,俩sql脚本，先create后populate

---------------------------------------------------
-- Sams Teach Yourself Oracle PL/SQL in Ten Minutes
-- http://forta.com/books/0672328666
-- Example table population scripts
---------------------------------------------------


---------------------------------------------
-- Create customers table
---------------------------------------------
CREATE TABLE customers
(
  cust_id      int       NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);


---------------------------------------------
-- Create orderitems table
---------------------------------------------
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL
);


---------------------------------------------
-- Create orders table
---------------------------------------------
CREATE TABLE orders
(
  order_num  int  NOT NULL ,
  order_date date NOT NULL ,
  cust_id    int  NOT NULL
);

---------------------------------------------
-- Create products table
---------------------------------------------
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  varchar(1000) NULL
);


---------------------------------------------
-- Create vendors table
---------------------------------------------
CREATE TABLE vendors
(
  vend_id      int      NOT NULL,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL
);


---------------------------------------------
-- Create productnotes table
---------------------------------------------
CREATE TABLE productnotes
(
  note_id    int      NOT NULL ,
  prod_id    char(10) NOT NULL ,
  note_date  date     NOT NULL ,
  note_text  clob     NULL
);


----------------------
-- Define primary keys
----------------------
ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (cust_id);
ALTER TABLE orderitems ADD CONSTRAINT pk_orderitems PRIMARY KEY (order_num, order_item);
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_num);
ALTER TABLE products ADD CONSTRAINT pk_products PRIMARY KEY (prod_id);
ALTER TABLE vendors ADD CONSTRAINT pk_vendors PRIMARY KEY (vend_id);
ALTER TABLE productnotes ADD CONSTRAINT pk_productnotes PRIMARY KEY (note_id);


---------------------------------------------
-- Define foreign keys
---------------------------------------------
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
ALTER TABLE productnotes ADD CONSTRAINT fk_productnotes_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

---

---------------------------------------------------
-- Sams Teach Yourself Oracle PL/SQL in Ten Minutes
-- http://forta.com/books/0672328666
-- Example table population scripts
---------------------------------------------------


---------------------------------------------
-- Populate customers table
---------------------------------------------
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


---------------------------------------------
-- Populate vendors table
---------------------------------------------
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


---------------------------------------------
-- Populate products table
---------------------------------------------
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



---------------------------------------------
-- Populate orders table
---------------------------------------------
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, TO_DATE('2015-02-01', 'yyyy-mm-dd'), 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, TO_DATE('2015-02-12', 'yyyy-mm-dd'), 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, TO_DATE('2015-02-28', 'yyyy-mm-dd'), 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, TO_DATE('2015-03-03', 'yyyy-mm-dd'), 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, TO_DATE('2015-03-08', 'yyyy-mm-dd'), 10001);


---------------------------------------------
-- Populate orderitems table
---------------------------------------------
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);


---------------------------------------------
-- Populate productnotes table
---------------------------------------------
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', TO_DATE('2015-02-17', 'yyyy-mm-dd'),
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', TO_DATE('2015-02-18', 'yyyy-mm-dd'),
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', TO_DATE('2015-02-18', 'yyyy-mm-dd'),
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', TO_DATE('2015-02-19', 'yyyy-mm-dd'),
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', TO_DATE('2015-02-20', 'yyyy-mm-dd'),
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', TO_DATE('2015-02-22', 'yyyy-mm-dd'),
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', TO_DATE('2015-02-23', 'yyyy-mm-dd'),
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', TO_DATE('2015-02-25', 'yyyy-mm-dd'),
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', TO_DATE('2015-03-01', 'yyyy-mm-dd'),
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', TO_DATE('2015-03-01', 'yyyy-mm-dd'),
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', TO_DATE('2015-03-02', 'yyyy-mm-dd'),
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', TO_DATE('2015-03-02', 'yyyy-mm-dd'),
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', TO_DATE('2015-03-05', 'yyyy-mm-dd'),
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', TO_DATE('2015-03-07', 'yyyy-mm-dd'),
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

---

开始笔记部分

一、检索数据

--1.基本
--终止语句，sql语句最后使用分号
--检索所有列，* 指代所有列
--sql不区分大小写,select=SELECT=Select,但标识符（表名/列名等）可能区分
--处理sql语句时，利用关键字将长sql语句分解到多行上，执行时会忽略行内所有空白
--eg:
select  prod_id,prod_name,prod_price
from products;

SELECT  prod_id,prod_name,prod_price
from products;

--2.dual
--dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：

--2.1查看当前用户，可以在 SQL Plus中执行下面语句 
select user from dual;
	
--2.2用来调用系统函数
--获得当前系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--获得主机名
select SYS_CONTEXT('USERENV','TERMINAL') from dual;
--获得当前 locale
select SYS_CONTEXT('USERENV','language') from dual;
--获得一个随机数
select dbms_random.random from dual;


--3.彩色编码标识：蓝色=sql语句，标识符（表名/列名等）=黑色，注释=浅色/浅灰色
---------------------------------------------
-- Create customers table
---------------------------------------------
CREATE TABLE customers
(
  cust_id      int       NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);


--4.注释
--4.1内联注释：句内

select prod_name --this is a comment
from products;

--4.2内联注释：行首
--this is a comment
select prod_name 
from products;

--4.3内联注释：多行
/*select prod_name --this is a comment
from products;*/
select prod_name --this is a comment
from products;


--5.关键字：distinct，指定唯一，去重，放在需要指定唯一的列名前
select distinct vend_id
from products;


--6.检索多列，使用逗号分隔列名
select  prod_id,prod_name,prod_price
from products;


--7.使用完全限定表名/列名来引用列名
select products.prod_name
from ORCL.products;

select products.prod_name
from products;

二、对检索的数据进行排序

--单列排序，order by,以prod_name字母顺序由小到大排序
select prod_id,prod_price,prod_name
from products
order by prod_name；

--多列排序
--按列名排序，讲究优先级，只有当prod_name数值相同时，才会继续使用prod_price进行排序
select prod_id,prod_price,prod_name
from products
order by prod_name,prod_price;

--按列位置（序号）排序，建议不要用，容易出错
select prod_id,prod_price,prod_name
from products
order by 2,3;

--按列名降序，desc放在需要降序的列名后，其他照旧
select prod_id,prod_price,prod_name
from products
order by 2 desc,prod_name;


--默认是升序，asc，故一般不添加

三、过滤数据

--where子句
--where条件判断要放在from子句之后，如果需要order by 子句，需要放在where子句之后
select prod_name,prod_price
from products
where prod_price = 2.50;

--不等于有两种写法：！=或<>
--使用where子句过滤时，对应的值一般默认是区分大小写的
--一般给值与数据类型是字符串的列进行匹配时，值需要使用引号做定界符号引起来
select prod_name,prod_price
from products
where prod_name='Fuses';

--空值null是不占位置的，不参与排序/计算，再过滤时如果不确定是否存在空值而直接过滤空值的话，很有可能无法直接达到目的，因为还有一种叫做未知Unknown的特殊含义，当直接过滤null而又有Unknown存在时，数据库什么都不会返回,因此需要先验证再过滤
select prod_name
from products
where prod_price is null;

四、高级过滤数据

--where子句结合运算符来帮助where追加判断条件
--and,满足所有条件才有数据库的返回
--or,满足任意条件均有返回值
--显示组合运算符，圆括号（），sql在执行时的优先级是：() > and > or
--()常常与in结合使用，()内的有效列表作用等同or的多选,in的理论效率要高于or
select prod_name,prod_price
from products
where vend_id in (1002,1003)
order by prod_name;

select prod_name,prod_price
from products
where vend_id = 1002 or vend_id = 1003
order by prod_name;

--not会否定其后出现的任何条件
select prod_name,prod_price
from products
where vend_id not in (1002,1003)
order by prod_name;

五、使用通配符过滤

--结合通配符，创建搜索模式，用来匹配值的某些特殊部分
--使用like运算符，%指代0或1或多个，_指代1个，与like相匹配的值可能会区分大小写，空值null无法借%过滤处理，@符号若作为文本被检索，需要再加@转义才能生效，即@@
select prod_name,prod_price
from products
where  prod_name like '%anvil%';

select prod_id,prod_name
from products
where prod_name like '_ ton anvil%'

--不要尝试在一开始就使用通配符搜索，以通配符开头会减慢检索的速度

六、使用正则进行检索过滤

--pl/sql提供4种函数方便我们使用正则匹配过滤文本字符串，重点掌握regexp_like()函数，模糊匹配
--regexp_replace()替换字符串中的字串，regexp_instr()和regexp_substr()搜索字符串中的子串
--regexp_like类似like,但不同的是like是全匹配，即匹配所有列，而前者是需要指定特定列及对应特定值，如果被检索的列的列值包含特定值便会返回对应行
--正则匹配时是区分大小写的
select prod_name
from products
where  regexp_like(prod_name,'1000')
order by prod_name;

--正则匹配：
--or，|
--多个，[]
--范围，[-]
--特殊字符，加转义符号，\
--所有，.
where  regexp_like(prod_name,'1000|1002')
where  regexp_like(prod_name,'[1000,1002,1003]')
where  regexp_like(prod_name,'[1000-1002]')
where  regexp_like(vend_name,'\.')

--正则匹配：
--字符类别:
--任意数字，[0-9]=\d,
--任意字母或数字，[a-Z0-9]=\w,
--任意空白字符，\s,
--任意非数字，[^0-9]=\D,
--任意非字母或数字，[^a-Z0-9]=\W,
--任意非空白字符，\S


--正则匹配：
--多个实列/匹配多次/重复元字符
--*,匹配次数>=0
--+，匹配次数>=1
--？，匹配次数=0/1
--{n}，匹配次数=n
--{n,}，匹配次数>=n
--{n,m}，n<=匹配次数<=m

select prod_name
from products
where regexp_like(prod_name,'\(\d sticks?\)')
order by prod_name;

--正则匹配：
--特定位置
--^,开头
--$,结尾

select prod_name
from products
where regexp_like(prod_name,'^[0-9\.]')
order by prod_name;

七、创建计算字段

--计算字段，顾名思义就是整合列
--数据库中的字段一般指的就是计算字段，实质上是等同列

--连接字段，||

select vend_name || ',('||vend_country||')'
from vendors
order by vend_name;

--删除右边空格，rtrim()函数；ltrim()删除左边空格，ttrim()删除左右两边的空格

select rtrim(vend_name) || ',('||rtrim(vend_country)||')'
from vendors
order by vend_name;

--使用别名，as；字段或列的替代名称=别名，未命名的列无法在客户端中使用

select rtrim(vend_name) || ',('||rtrim(vend_country)||')' as vend_titles
from vendors
order by vend_name;

--使用数学计算，+-*/

select prod_id,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num=20005;

八、使用函数操作数据

--函数类型：时间日期函数、系统函数、文本函数、数值函数

--文本函数
--length():返回字符串的长度
--lower():把字符串转换成小写形式
--lpad():在字符串左边填充空格
--ltrim():从字符串左边修剪掉空白
--rpad():在字符串右边填充空格
--rtrim():从字符串右边修剪掉空白
--soundex():返回字符串的SOUNDEX值
--substring():返回字符串内的字符
--upper():把字符串转换成大小写形式

select vend_name,upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;

--soundex是一种算法，用于把任何文本字符串转换成一种字母数字的模式，描述该字符串的语音表示，考虑了类似的字符发音和音节，允许字符串的发音（而不是按他们的输入）来比较他们
select cust_name,cust_contact
from customers
where soundex(cust_contact)=soundex('Y lie');


--日期和时间函数
--add_month():给日期添加月份（也可以减去月份）
--extract():从日期和时间中减去年、月、日、时、分或秒
--last_day():返回月份的最后一天
--nths_between():返回两个月份之间的月数
--xt_day():返回指定日期后面的那一天
--sdate():返回当前日期和时间
--to_date():把字符串转换成日期

--extract能灵活提取日期的特定部分
select cust_id,order_num
from orders
where extract(year from order_date)=2015
and extract(month from order_date)=2


--数值函数
--用于代数、三角或几何计算
--abs():返回数字的绝对值
--cos():返回指定角度的三角余弦值
--exp():返回指定数字的指数值
--mod():返回除法运算的余数
--sin():返回指定角度的三角正弦值
--sqrt():返回指定数字的平方根
--tan():返回指定角度的三角正切值

--附：
--SUBSTR函数
--使用SUBSTR函数可以得到字符串的一个子串。
--A.得到FIRST_NAME字段的前三个字符
select FIRST_NAME, substr(FIRST_NAME,1,3) substr from t;

--B.从FIRST_NAME字段的第三个字符开始连续取4个字符。
select FIRST_NAME, substr(FIRST_NAME,3,4) substr from t;

--C.从相反的方向获得子字符串
--下面的SQL中substr函数的第二个参数是“-6”，表示从字符串后面向前数第6个字符开始，再读取4个字符。这里构造的结构和上面的结果相同，可谓殊途同归是也。
--这里之所以使用到了rtrim函数，是为了消除字符串尾部空格对结果的影响。
select FIRST_NAME, substr(rtrim(FIRST_NAME),-6,4) substr from t;

--D.SUBSTR函数的第三个参数可以为空，表示从截取开始的位置一直到字符串尾部的意思。
select FIRST_NAME, substr(FIRST_NAME,3) substr from t;

九、汇总函数

---聚合函数：是指用于操作一组行以计算并返回单个值的函数
---sql聚合函数：
--avg(),返回列的平均值,行中空值null将被忽略
--count(),返回列中的行数，*做参数会包含空值,特定值做参数会排除空值null
--max()，返回列的最大值,直接忽略空值null
--min(),返回列的最小值,直接忽略空值null
--sum(),返回列的值汇总,直接忽略空值null

--avg_eg: 求所有产品的平均价格，avg_price 是均价的别名
select avg(prod_price) as avg_price
from products;

--avg_eg: 求特定供应商产品的平均价格
select avg(prod_price) as avg_price
from products
where  vend_id=1003;

--count_eg: 统计顾客表的所有行
select count(*) as num_custs
from customers;

--count_eg: 统计有电子邮箱顾客的行数
select count(cust_email) as num_custs
from customers;

--max_eg: 统计最贵的商品价格
select max(prod_price) as max_price
from products;

--min_eg: 统计最贵的商品价格
select min(prod_price) as min_price
from products;

--sum_eg: 统计特定订单中总订货量
select sum(quantity) as items_ordered
from orderitems
where order_num=20005;

--sum_eg: 统计特定订单的总订货量的价值
select sum(item_price*quantity) as items_ordered
from orderitems
where order_num=20005;

未完待续