Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【SQL】一些语法

12 Apr 2019 » SQL

一些语法

0.minus用法

minus返回的结果,是以左边表为基表,去重。

举例:样例数据

create table test01

id number(10),
name varchar(20)
;
insert into test01 values(1,'test01');
insert into test01 values(2,'test02');
insert into test01 values(3,'test03');
commit;

create table test02

id number(10),
name varchar(20)
;

insert into test02 values(1,'test01');
insert into test02 values(2,'test011');
insert into test02 values(3,'test022');
insert into test02 values(4,'test033');
commit;

测试

SQL> select * from test01 minus select * from test02;

        ID NAME
---------- ------------------------------------------------------------
         2 test02
         3 test03
 
SQL> select * from test02 minus select * from test01;

        ID NAME
---------- ------------------------------------------------------------
         2 test011
         3 test022
         4 test033

1.ROUND和TRUNC的用法

格式如下:ROUND(number[,decimals])

其中:number 待做截取处理的数值。decimals 指明需保留小数点后面的位数、并四舍五入。如果为负数则表示从小数点开始左边的位数,也做四舍五入。举例如下:

SQL> select round(156.06,1) from dual;

ROUND(156.06,1)
---------------
          156.1

SQL> select round(156.06,2) from dual;

ROUND(156.06,2)
---------------
         156.06

SQL> select round(156.06,-1) from dual;

ROUND(156.06,-1)
----------------
             160

SQL> select round(156.06,-2) from dual;

ROUND(156.06,-2)
----------------
             200

语法格式:TRUNC(number[,decimals])

其中: number 待做截取处理的数值;decimals 指明需保留小数点后面的位数。截取时并不对数据进行四舍五入。

SQL> select trunc(156.16,2) from dual;

TRUNC(156.16,2)
---------------
         156.16

SQL> select trunc(156.16,1) from dual;

TRUNC(156.16,1)
---------------
          156.1

SQL> select trunc(156.16,-1) from dual;

TRUNC(156.16,-1)
----------------
             150

SQL> select trunc(156.16,-2) from dual;

TRUNC(156.16,-2)
----------------
             100

2.nvl和nvl2的用法

  • 1.nvl(exp1,exp2)

    NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returnsexpr1.

​ 该函数是处理表达式中的空值:

​ 1.假设表达式exp1是空值,则该函数返回表达式exp2的值。

​ 2.假设表达式exp1不是空值,则该函数返回表达式exp1的值。

  • 2.nvl2(exp1,exp2,exp3)

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

​ 该函数处理方式:

​ 1.假设exp1为空值,则返回exp3。

​ 2.假设exp1非空,返回exp2。

举例论证:

create table nvltest(
   id  number(10),
   name varchar2(20),
   join_date DATE 
);

insert into nvltest values(1,'zhangshengdong',sysdate);
insert into nvltest values(2,'liuzhonghao',sysdate);
insert into nvltest values(3,'huke',null);
commit;

SQL> col name format A30;
SQL> select id,name,nvl2(join_date,'joined','fired') from nvltest;

        ID NAME                           NVL2(JOIN_DATE,'JO
---------- ------------------------------ ------------------
         1 zhangshengdong                 joined
         2 liuzhonghao                    joined
         3 huke                           fired
                                               
SQL> select id,name,nvl(join_date,sysdate+2) from nvltest;

        ID NAME                           NVL(JOIN_DATE,S
---------- ------------------------------ ---------------
         1 zhangshengdong                 11-APR-19
         2 liuzhonghao                    11-APR-19
         3 huke                           13-APR-19

同时,nvl中的两个参数必须同类型,否则报错,如下:

SQL> select id,name,nvl(join_date,'nice') from nvltest;
select id,name,nvl(join_date,'nice') from nvltest
                             *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

nvl2中的后两个参数,必须同类型,否则报错,如下:

SQL> select id,name,nvl2(join_date,sysdate,'fired') from nvltest;
select id,name,nvl2(join_date,sysdate,'fired') from nvltest
                                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

3.alter table xxx set unused用法

现场有一张大数据量的分区表,数据量在10G以上。因某种原因需要删除其中的某些字段。如果直接用

alter table1 drop (column1,column2); 或者alter table1 drop column column1的话,需要执行很长时间,这期间该表被锁,会影响到其它应用。

该语法等同于drop column,但是目的是提高性能,可以在生产高峰期时使用unused,之后在服务器空闲时再操作

SQL> desc nvltest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(20)
 JOIN_DATE                                          DATE

SQL> alter table nvltest set unused(JOIN_DATE); 

Table altered.

SQL> desc nvltest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NAME  

可以查到被修改的列

[oracle@db ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Thu Apr 11 16:51:47 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

SQL> conn zsd/zsd;
Connected.
SQL> SELECT OBJECT_ID,OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_NAME='NVLTEST';

 OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
     78806
NVLTEST


SQL> col name format A30
SQL> select col#,intcol#,name from col$ where obj#=78806;

      COL#    INTCOL# NAME
---------- ---------- ------------------------------
         1          1 ID
         2          2 NAME
         0          3 SYS_C00003_19041115:48:16$

最后删除unused语句:

SQL> alter table nvltest1 drop unused columns;

Table altered.

数据字典里面,显示的列已经没有SYS_C00003_19041115:48:16$:

SQL> col name format A10;
SQL> select col#,intcol#,name from col$ where obj#=78807;

      COL#    INTCOL# NAME
---------- ---------- ----------
         1          1 ID
         2          2 NAME
         3          3 JOIN_DATE