-- 处理日期
select order_num
from orders
where to_number(to_char(order_date, 'yyyy')) = 2004;

-- between
select order_num
from orders
where order_date between to_date('01-jan-2004')
  and to_date('31-dec-2004')

-- in 语法 等同于where...and...
select cust_id
from orders
where order_num in (20007, 20008);

-- 聚合函数
select count(*) as num_items,
     , min(prod_price) as price_min
     , max(prod_price) as price_max
     , avg(prod_price) as price_avg
from products;

-- group by 子句
select vend_id,count(*) as num_prods
   from products
   group by vend_id;

-- group by 和 having 联合使用
select vend_id,count(*) as num_prods
from products
where prod_price >= 4
group by vend_id
having count(*) >= 2;

-- group by and having and order by
select order_num,count(*) as items
from orderitems
group by order_num
having count(*) >= 3
order by items,order_num;

-- sql 子查询
select cust_name
     , cust_state
     , (
            select count(*)
            from orders
            where orders.cust_id = customers.cust_id
       ) as orders
from customers
order by cust_name;

-- 三张表查询两个字段，有一张表用来作为约束
select cust_name
     , cust_contact
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
  and orderitems.order_num = orders.order_num
  and prod_id = 'rgan01';

-- 单表自查寻  为jim jones所在公司工作的所有客户   要用别名
select c1.cust_id
     , c1.cust_name
     , c1.cust_contact
from customers c1, customers c2
where c1.cust_name = c2.cust_name
  and c2.cust_contact = 'jim jones';

-- 左外连接
select customers.cust_id
     , orders.order_num
from customers
left outer join orders
  on customers.cust_id = orders.cust_id;

-- 全外连接
select customers.cust_id
     , orders.order_num
from orders
full outer join customers
  on orders.cust_id = customers.cust_id;

-- 所有客户及每个客户的订单数
select customers.cust_id
     , count(orders.order_num) as num_ord
from customers
inner join orders
  on customers.cust_id = orders.cust_id
group by customers.cust_id ;

-- 组合查询
union      -- 删除重复的数据
union all  -- 不删除重复的数据

select cust_name
     , cust_contact
     , cust_email
from customers
where cust_state in('il','in','mi')
union
select cust_name
     , cust_contact
     , cust_email
from customers
where cust_name = 'fun4all'
order by cust_name, cust_contact;
--等同于
select cust_name
     , cust_contact
     , cust_email
from customers
where cust_state in ('il','in','mi')
 or cust_name = 'fun4all';

-- 插入数据
insert into table_name(columns_name) values(values...)
insert into customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip) values('1000006', null, null, 'toy land', '123 any street', 'new york', 'ny', '1111')

insert into table_name
select(some_columns)
from table_name
where condition
order by select_columns
insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip)
select cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip
from custnew;

-- 新建一张和源表表结构和数据同样的表
create table copytable1 as
select *
from customers;

-- 更新语句 切记不能省略where子句
update customers
set cust_email = '[email protected]'
where cust_id = '1000000005';

-- 删除数据 切记不能省略where子句
delete
from customers
where cust_id = '1000000006';

-- 修改表 alter table
alter table vendors add vend_phone char(20);

-- 删除表格
drop table table_name;

-- 视图
  -- 创建视图的时候尽量不要用order by
  -- 需要的时候在使用视图的时候加上
  create view view_name as
  select cust_name
       , cust_contact
       , prod_id
  from customers, orders, orderitems
  where customers.cust_id = orders.cust_id
    and orderitems.order_num = orders.order_num;
  -- 使用视图
  select cust_name
       , cust_contact
  from productcustomers
  where prod_id = 'rgan01';
  -- 视图使用where子句
  create view view_name as
  select cust_id
       , cust_name
       , cust_email
  from customers
  where cust_email is not null;--where 子句过滤掉不满足要求的数据
  -- use view
  select *
  from customeremaillist;
  -- 视图对于计算字段特别爽
  create view orderitemsexpanded as
  select order_num,prod_id
       , quantity
       , item_price
       , quantity * item_price as expanded_price
  from orderitems;

  -- 存储过程
  -- oracle
  create or replace procedure mailinglistcount(listcount out number)
  is
  begin
     select *
     from customers
     where not cust_email is null;
     listcount := sql%rowcount;
  end;

  -- 事务
  -- 方式： 将多条sql组成一个组，要么整个组执行成功，要么整个组执行失败
  -- 作用： 维护数据库的完整性
  -- 定义事务
  begin transaction
  <do your transaction here>
  commit transaction
  -- 回滚
  delete from orders;
  rollback;
  -- 提交事务
  commit
  begin transaction
  delete orderitems where order_num =12345
  delete orders where order_num = 12345 --两个delete同时成功才会执行commit
  commit transaction
  -- 保留点  savepoint
  -- 声明  savepoint delete1;
  -- 回滚到保留点 rollback to delete1(保留点)
  -- 理论上说保留点越多越好

  -- 游标
  --声明
  declare custcursor cursor
  is
  select * from customers
  where cust_email is null;
  --打开
  open cursor custcursor

  -- 约束
  --主键
  create table table_name
  (
    column_name primary key
  )
  --或者
  alter table table_name
  add constraint primary key (column_name);
  --外键 可以防止数据被意外删除
  create table orders
  (
    column_name references other_tbale(other_column);
  )
  --或者
  alter table table_name
  add constraint
  foreign key (column_name) references orther_table(column_name)
  --检查约束  用来保证数据满足必要的条件
  create table table_name
  (
    column_name check (column_name > 0)
  )
  --或者
  alter table table_name
  add constraint check (column_name like '[mf]')
  --索引 加快查询速度，减慢增删改速度
  --创建
  create index index_name
  on table_name (column_name);
  --触发器 遇到特地的语句时执行
  create trigger trigger_name
  after something or something
  for each row
  begin
    ...
  end;

  --数据库安全
  grant   -- 授权
  revoke  -- 收权