总结:
1 逻辑比较3结果:true,false,未知;包含null的比较,结果为未知;比较条件为空值时,作为false对待。
2 group by:rollup用于产生部分求和;cube求得交换分类值。
3 标量子查询:在结果集中的每一行结果产生时都执行一遍;可通过减少标量子查询的重复执行来优化sql。每个标量子查询都课用left join来代替。select a.name,(select b.salary from work b where a.id=b.person_id )from person a;等同于select a.name,b.salary from person a, work b where a.id=b.person_id
4 order by 开销较大 需要排序的数据量为每行输出字节数*行数,较小的数据量排序在内存中完成,较大的排序在临时磁盘空间中完成。
5 merge:把多个操作结合在一起,从而避免使用多个insert/update/delete语句,尽量避免做不是必须做的事情。
MERGE INTO PHYSICAL_EXAM_RECORD per USING BI_PERSON_INFO bpi ON ( per.idcard = bpi.idcard )WHEN MATCHED THEN UPDATE SET per.VERIFY_ORGAN_CODE = bpi.INPUT_ORGAN_CODE, per.VERIFY_SUPER_ORGAN_CODE = bpi.INPUT_CENTER_ORGAN_CODE, per.VERIFY_GBCODE = bpi.INPUT_GBCODE ;
1
建表
create table a_temp_try
(
ID NUMBER(7,0 )not null ,
name varchar2(30 char),
gender number(1,0) default 1,
age number(3,0),
description varchar2(100 char),
create_date date
);
create table a_temp_try_copy as select * from a_temp_try;
2
单表插入数据:
单行:INSERT INTO a_temp_try values (1,'huahua',2,12,'ceshi1',TO_DATE('2019/06/01','yyyy/MM/dd'));
多行:INSERT INTO a_temp_try_copy (ID,name,gender,age,description,create_date) select * from a_temp_try;
INSERT INTO a_temp_try_copy select * from a_temp_try;
多表插入数据:
insert all
when sum_orders< 10000 then
into small_customers
when sum_orders< 100000 and sum_orders> 10000 then
into medium_customers
Else
into large_customers
select customer_id, sum(order_total )sum_orders from orders group by customer_id order by sum_orders