函数练习小结

原创

duxiutemp 2016-11-21 15:56:51 ©著作权

文章标签 dd 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者duxiutemp的原创作品，请联系作者获取转载授权，否则将追究法律责任

NULL值与任何值进行四则运算后，其结果均为NULL
如果列别名包括空格或特殊字符，或者它是区分大小写的，那么需要双引号

把列与列，列与字符连接起来用||表示，用来合成列

输出显示关键字或者操作符的内容q'[]'表示
[ ] , ! ! , : : , { } , ( ) , < > , . . 等都是可以，但切记，要成对出现

distinct去重的结果是不排序的

字符和日期要包含在单引号中
字符大小写敏感，日期格式敏感
默认的日期格式是DD-MON-RR

sql语句中的日期类型要与当前会话中的nls_date_format的格式相同

使用escape标识符
select employee_id,last_name,job_id from employees where job_id like '%SA\_%' escape '\';
如果字符串中有两个连续的"_",那么是否需要两次转译？
SQL1：select * from t_like where y like 'a\__d%' escape '\';
SQL2：select * from t_like where y like 'a\_\_d%' escape '\';
经实验转译一次两次都可以

检索null时，无法用=或<>来进行判断，只允许用is null和is not null来进行判断。

运算优先级
1.算数运算符
2.连接符
3.比较符
4.IS [NOT] NULL, LIKE, [NOT] IN
5.[NOT] BETWEEN
6.不等于
7.NOT
8.AND
9.OR

排序结果中有null值，升序排序后，null值在最下面（反之在最上面），因为null值是未知的，也就是无穷大

如果需求是，非空值升序，空值显示在结果的最上面
select distinct department_id from employees order by 1 asc nulls first;
如果需求是，非空值降序，空值在显示结果的最下面
select distinct department_id from employees order by desc nulls last;

使用&和&&临时存储值，替代变量
使用替代变量，可以替代一下内容
where条件
order by子句
列表达式
表名
整个select语句
SQL> select employee_id,last_name,salary,department_id
2 from employees
3 where employee_id=&v_num;
Enter value for v_num: 101
old   3: where employee_id=&v_num
new   3: where employee_id=101

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        101 Kochhar                        17000            90
SQL> select last_name,department_id,salary*12
2 from employees
3 where job_id='&job_title';
Enter value for job_title: HR_REP
old   3: where job_id='&job_title'
new   3: where job_id='HR_REP'

LAST_NAME                 DEPARTMENT_ID SALARY*12
------------------------- ------------- ----------
Mavris                               40      78000

SQL> select last_name,department_id,salary from employees where hire_date=to_date('&hd','yyyy-mm-dd');
Enter value for hd: 2008/1/13
old   1: select last_name,department_id,salary from employees where hire_date=to_date('&hd','yyyy-mm-dd')
new   1: select last_name,department_id,salary from employees where hire_date=to_date('2008/1/13','yyyy-mm-dd')

LAST_NAME                 DEPARTMENT_ID     SALARY
------------------------- ------------- ----------
Grant                                50       2600
hire_date=to_date('2008-01-13','yyyy-mm-dd')是在告诉数据库我输入的格式是怎么样的
hire_date='2008-01-13'是直接输入，但是这种情况输入的格式必须跟nls_date_format相同

SQL> select employee_id,last_name,job_id,&column_name from employees
2 where &condition
3 order by &order_column;
Enter value for column_name: email
old   1: select employee_id,last_name,job_id,&column_name from employees
new   1: select employee_id,last_name,job_id,email from employees
Enter value for condition: manager_id=101
old   2: where &condition
new   2: where manager_id=101
Enter value for order_column: 1
old   3: order by &order_column
new   3: order by 1

EMPLOYEE_ID LAST_NAME                 JOB_ID     EMAIL
----------- ------------------------- ---------- -------------------------
        108 Greenberg                 FI_MGR     NGREENBE
        200 Whalen                    AD_ASST    JWHALEN
        203 Mavris                    HR_REP     SMAVRIS
        204 Baer                      PR_REP     HBAER
        205 Higgins                   AC_MGR     SHIGGINS

&和&&都表示需要输入参数，区别是&表示一次性参数 &&表示可多次引用

使用define命令创建和分配一个变量的值，使用undefine命令来删除一个变量
define employee_num=200
SQL> define employee_num=200
SQL> select employee_id,last_name,salary,department_id from employees
2 where employee_id=&employee_num;
old   2: where employee_id=&employee_num
new   2: where employee_id=200

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
        200 Whalen                          4400            10

SQL> undefine employee_num

使用 VERIFY 命令来显示替换变量之前和之后的值，默认值为ON，即显示替换变量之前之后的值

set verify off
show verify

SQL> select ascii('A') from dual;

ASCII('A')
----------
        65

SQL> select ascii('''') from dual;

ASCII('''')
-----------
         39
求单引号的ASCII码写法很特殊两个单引带表一个单引

ascii只针对一个字符进行转换

SQL> select chr(39) from dual;

C
-
'
空格：chr(32)
回车：chr(13)
换行：chr(10)

instr在一个字符串中搜索指定的字符，返回发现指定的字符的位置
instr(c1,c2,I,J)
c1:被搜索的字符串
c2:希望搜索的字符串
I:搜索开始的位置，默认为1
J:出现的位置，默认为1

vsize(expr) vsize返回expr的内部表示的字节数，如果expr是空的，那么这个函数返回null，此功能不支持直接clob数据

soundex(char)返回一个与给定的字符串读音几乎相同的字符串

select trim('a' from 'aaaaaaabbbbbbbbbbbaaaaaa') from dual;

abs(n)返回指定值n的绝对值

to_multi_byte(char)半角转全角
to_single_byte(char)全角转半角

语法：ceil(n)
取大于等于数值n的最小整数
SCOTT@ORA11GR2> select ceil(9.9),ceil(9),ceil(9.1) from dual;

CEIL(9.9)     CEIL(9) CEIL(9.1)
---------- ---------- ----------
         10            9           10

语法：floor(n)
取小于等于数值n的最大整数
SCOTT@ORA11GR2> select floor(9.9),floor(9),floor(9.1) from dual;

FLOOR(9.9)    FLOOR(9) FLOOR(9.1)
---------- ---------- ----------
          9            9             9

show parameter nls_date_format
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile
nls_date_format 为静态参数，修改时必须加上scope=spfile子句，重启数据库参数生效

带有时区及毫秒的日期格式
select systimestamp from dual;

返回数据库的时区
select dbtimezone from dual;

scott用户如果不存在，利用脚本创建
select username from dba_users where username='SCOTT';
@?/rdbms/admin/utlsampl.sql

show linesize
show pagesize

清空sqlplus缓存区的内容
SQL>cl buff清空缓存区的内容

登录时读取的文件
vi /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql

power(x,y)返回x的y次幂。
SQL> select power(2,3) from dual;

POWER(2,3)
----------
         8
缺陷：y为小数会报错

sign(n)符号函数
当n>0时，sign(n)=1
当n=0时，sign(n)=0
当n<0时，sign(n)=-1

返回数据库的时区
select dbtimezone from dual;

alter database set time_zone='+08:00';
时区分东西，东时区都带+号标识，西时区用-号标识。
在一个特定的时间点，时区号越大（考虑正负号）的地方，时间越晚。

返回当前会话的时区
select sessiontimezone from dual;

SQL> select dbtimezone,sessiontimezone,current_date from dual;

DBTIME SESSIONTIMEZONE                                                             CURRENT_DATE
------ --------------------------------------------------------------------------- -------------------
+00:00 +08:00                                                                      2016-11-21 14:53:58
发现current_date时间为会话所在时区的时间

将当前会话改为西六区
alter session set time_zone='-6:00';

dbtimezone:是数据库服务器所在的时区
sessiontimezone：是你的会话的时区
比如数据库服务器是放在英国（+00：00时区），而你在中国（+08：00）访问数据库，则sessiontimezone就是+08：00，dbtimezone就是+00：00

返回为当前会话所在时区的timestamp with time zone时间
SYS@ORA11GR2> select dbtimezone,sessiontimezone,current_timestamp from dual;

DBTIME SESSIONTIMEZONE CURRENT_TIMESTAMP
------ --------------- ----------------------------------------
+08:00 -06:00            06-JUN-12 04.14.37.777012 AM -06:00

QL> select current_timestamp(3) from dual;

CURRENT_TIMESTAMP(3)
---------------------------------------------------------------------------
21-NOV-16 01.04.14.905 AM -06:00

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
21-NOV-16 01.09.44.191830 AM -06:00

巧妙显示为标准日期型
SQL> select current_timestamp,current_timestamp+0 from dual;

CURRENT_TIMESTAMP                                                           CURRENT_TIMESTAMP+0
--------------------------------------------------------------------------- -------------------
21-NOV-16 01.10.58.179676 AM -06:00                                         2016-11-21 01:10:58

scc或cc显示世纪

SQL> select to_char(sysdate,'scc yyyy-mm-dd') from dual;

TO_CHAR(SYSDAT
--------------
21 2016-11-21

q表示当前时间为一年的第几季度

SQL> select to_char(sysdate,'q') from dual;

T
-
4

ww：一年中的第几周，w一个月中的第几周

SQL> select to_char(sysdate,'ww'),to_char(sysdate,'w') from dual;

TO T
-- -
47 3

ddd：一年中的第几天，dd一个月中的第几天 d一周中的第几天
SQL> select to_char(sysdate,'ddd'),to_char(sysdate,'dd'),to_char(sysdate,'d') from dual;

TO_ TO T
--- -- -
326 21 2