首页 > 代码库 > 函数练习小结
函数练习小结
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
函数练习小结