1.基础查询框架

select t1.col1, t2.col2    -- 取啥，可多表多列
from table_name1 t1, table_name2 t2  -- 从哪儿取，可多表，t1和t2为定义的表缩写
where t1.col3 = t2.col4   -- 取数条件
group by t2.col3
order by t2.col3 

上述任何地方的列名，可以认为是向量，直接作为对象，运用函数。任何地方的列名连接都用逗号。

2.过滤数据

• distinct, 在select之后，过滤重复行，可多列

select distinct first_name from contacts order by first_name -- 去除重复项

select first_name, count(first_name) from contacts having count(first_name)>1 group by first_name -- 引申part：查找重复项

select state, count(state) from locations  group by state order by state nulls first; -- NULL 不计数

• and/ or/ in/ between/ like 可用在where 条件里进行过滤。其中like类似通配符模式，%匹配0个或多个字符的任何字符串，_匹配任何单个字符

select product_name, category_id from products where category_id not in (1, 4) order by product_name;  -- in 后面可为向量

select product_name, category_id from products where product_name like 'ASR%' order by product_name; -- % 任意个字符，-为单个字符

select product_name, category_id from products where product_name like '%99%' and product_name like 'AS%' order by product_name;

select order_id, customer_id, status, to_char(order_date, 'YYYY-MM-DD') as order_date 
from orders 
where extract(year from order_date) = '2017'
order by order_date2 desc;

select order_id from orders where order_date between date '2016-12-01' and date '2016-12-31' order by order_date;

• any/some/all, [not] exists：在子查询返回情况的判断，some同any。any可接数组或者子查询结果。

select name
from customers a
where exists (
      select 1 from orders where customer_id = a.customer_id   -- 注意这里一定是a在父查询里面，而不是子查询里面。a和b的省略是在各自的主体查询里的。
      )
order by name; 

select name, b.order_id
from customers a, orders b
where not exists (
      select null from orders where customer_id = a.customer_id -- 注意null的用法
      )
order by name;

3.排序数据

• order by，默认升序，如降序，需指定desc，可多列；null可指定在前或者后，nulls first 或者nulls last

4.连接查询结果

• inner join 类似R函数merge，进行列合并

select order_id, status, first_name, last_name
from orders left join employees on orders.salesman_id = employees.employee_id
order by order_date desc;

• left join或者right join类似merge中的，all.x=true或者all.y=true
• union 类似于rbind，union all就不合并重复行；没有all就删除重复；进行行合并

select first_name ||' '|| last_name name, email, 'contact'
from contacts
union --all
select first_name ||' '|| last_name name, email, 'employee'
from employees
order by name desc;

• intersect，行合并的交集；minus，行合并的差集，前者为基础

select last_name from contacts
intersect
select last_name from employees;

select last_name from contacts
minus
select last_name from employees;

5.分组查询结果

• group_by 演示如何将行分组为子组，并为每个分组应用聚合函数

select b.name, count(a.order_id), max(a.order_date)
from orders a inner join customers b using(customer_id)
where status = 'Canceled' and order_date > date '2016-10-10'
group by b.name;

select customer_id, status, sum(quantity * unit_price) sales
from orders inner join order_items using (order_id)
group by customer_id, status  -- rollup(customer_id, status)会计算分类汇总项
order by customer_id;

• having 演示如何过滤分组中的行记录。where是过滤行，having过滤分组的行。

select order_id, sum(unit_price * quantity) order_value
from order_items
group by order_id
having sum(unit_price * quantity) > 500000  -- having 不能使用新的变量名，挺bug的。
order by order_value desc;

6.子查询

SELECT product_name, category_id, list_price,
ROUND((SELECT AVG( list_price ) 
       FROM products p1 
       WHERE p1. category_id = p2.category_id),2) avg_list_price -- 注意这里p1和p2的用法
FROM
    products p2
ORDER BY
    category_id;

select name 
from customers
where
     customer_id not in (
     select customer_id 
     from orders 
     where extract(year from order_date) = '2017') 
order by name;

7.几个关键函数

• length/concat(x,y)/replace/substr 以及dual的虚拟表
• rank() over (order by xx desc) as xxx & dense_rank
• extract(year/month/day from ‘date’)
• to_char/to_date/to_number
• nvl(x,value):指定空值返回值；nvl2(x,value1, value2)