SQL

SQL语句分为

1.DML（数据操作语言）：insert、update、delete

insert all/first when first_condition then into first_table_name values(XXX) ... when last_condition then into last_table_name values(XXX) select XXX

insert into table_name select XXX from XXX             table_name中的属性为XXX

merge into main_table using change_table on (main_table.primary_key = change_table.primary key) 

when matched then

     update set main_table.first_column = change_table.first_conlumn

     ...

    update set main_table.last_column = change_table.last_conlumn

when not matched then

    insert (first_column,...,last_column) values (change_table.first_column,...,change_table.last_column)

2.DQL（数据查询语言）：select

3.DDL（数据定义语言）：create、alter、drop

create table table_name (ename varchar2(10) [constraint primary key]、...)                            primary key、unique、not null、foreign key

create table table_name as select XXXX

create table table_name(ename varchar2(10)) insert into 

alter table table_name add new_column datetype [constraint]

alter table table_name drop column column_name

alter table table_name  rename column column_name to new_column_name

alter table table_name rename to new_table

alter table table_name modify column_name new_datatype

alter table table_name add constraint con_name constraint

alter table table_name drop constraint con_name

alter table table_name enable constraint con_name

alter table table_name disable constraint con_name 

4.DCL（数据控制语言）：权限授权或撤销、完整性约束和事物开始和结束等控制语句

除运算

not exists (select statement1 except/minus select statement2)

若statement1 中的元素全部出现在statement2 中，则为true。反之为false

行列转换

用with、decode、pivot或with、case

with TT as (select Deptno,job, avg(sal)as avgsal from SCOTT.emp group by deptno,job) 
select DName,sum(decode(job,'MANAGER',avgsal, null)) as MANAGER,
sum(decode(job,'CLERK',avgsal, null)) as CLERK,
sum(decode(job,'SALESMAN',avgsal, null)) as SALESMAN,
sum(decode(job,'PRESIDENT',avgsal, null)) as PRESIDENT,
sum(decode(job,'ANALYST',avgsal, null)) as ANALYST from TT natural join SCOTT.DEPT group by DName;

或

with TT as (
     select deptno,
     case job when 'MANAGER' then avg(sal) else null end as T1,
      case job  when 'CLERK' then avg(sal) else null end as T2,
      case job    when 'SALESMAN' then avg(sal) else null end as T3,
      case job      when 'PRESIDENT' then avg(sal) else null end as T4,
      case job        when 'ANALYST' then avg(sal) else null end as T5
                 from SCOTT.EMP group by job,deptno
)
select Dname, NVL(to_char(sum(T1)),'无') as MANAGER, NVL(to_char(sum(T2)),'无') as CLERK, NVL(to_char(sum(T3)),'无') as SALESMAN, NVL(to_char(sum(T4)),'无') as PRESIDENT,
NVL(to_char(sum(T5)),'无') as ANALYST from TT natural join SCOTT.dept group by DNAme;

原理均是先用判断语句decode 或case 判断原列属性job是否为某个值（新列属性），填充avgsal，若是，填充avgsal,否则填充null。最后用合并函数sum来将多个相同的行合并，对应的列属性相加。

这是sum合并之前，可以看出存在相同的deptno

合并之后可以看到相同行均被合并。

NVL(xxx,XXX)表示若xxx为空则输出XXX。但要求xxx与XXX的类型要一致。若xxx为空时输出无。则应该为NVL(to_char(xxx),'无')。

还有pivot函数可以用来行列转换

select * from (

(select avg(sal) as avgsal,job,deptno from emp group by job,deptno)

pivot sum(avgsal) for job in (

  when 'MANAGER' then avgsal

when 'CLERK' then avgsal,

                when 'SALESMAN' then avgsal,

                      when  'PRESIDENT' then avgsal,

                            when 'ANALYST' then avgsal

)

);

null值得运用：

1）属性值为null，会被分组函数视为一组。但不会被聚集函数识别，除了count(*);

2）null在in中等同于不存在，但是null在not in 中，永远视为false

3）oracle中null||'A' => 'A'   sqlserver中null+'A'=>null.

操作函数

nvl（input_value,result_if_is_null）

nvl2（input_value,result_if_is_not_null,result_if_is_null）

nullIF(input_value1,input_value2)   如果input_value1 = input_value2，返回null，否则返回input_value1

coalesce(input_value1,...,input_valuen)   返回第一个不为空的值