包含关系函数
1. in 等于括号内任一成员 如 select * from dept where deptno in ('10','20','30','40');
2. not in 不等于括号内任一成员 如 select * from dept where deptno not in ('10','20','30','40');
3. exists 和in函数类似；如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
4. between A and b 大于等于A与小于等于B 如 select * from emp where sal beteen 1000 and 1500;
5. not between A and b 不大于等于A与小于等于B 如 select * from emp where sal beteen 1000 and 1500;
6. like '%A%' 包含给定子串A 如 select * from emp where ename like '%A%';
7. >= 大于或等于 <= 小于或等于
nvl函数
1. nvl(n,r) n为空时返回r
替换函数
1. DECODE函数
decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值
2. CASE函数
case when 表达式1 then 表达式2 else 表达式3 end
当表达式1为真返回表达式2的值，否则返回表达式3的值
数值型常用函数
函数 返回值 样例 显示
ceil(n) 大于或等于数值n的最小整数 select ceil(10.6) from dual; 11
floor(n) 小于等于数值n的最大整数 select floor(10.6) from dual; 10
mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual;
power(m,n) m的n次方 select power(3,2) from dual; 9
round(n,m) 将n四舍五入,保留小数点后m位 select round(1234.5678,2) from dual; 1234.57
sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1
sqrt(n) n的平方根 select sqrt(25) from dual ; 5
常用字符函数
initcap(char) 把每个字符串的第一个字符换成大写 select initicap('mr.ecop') from dual; Mr.Ecop
lower(char) 整个字符串换成小写 select lower('MR.ecop') from dual; mr.ecop
replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','S','Boy') from dual; Boycott
substr(char,m,n) 取出从m字符开始的n个字符的子串 select substr('ABCDEF',2,2) from dual; BC
length(char) 求字符串的长度 select length('ACD') from dual; 3
|| 并置运算符 select 'ABCD'||'EFGH' from dual; ABCDEFGH
Instr(char, search_string, startpos, occurrence)
从字符串中，从位置startpos 开始,寻找search_string 第occurrence次的位置
select instr('cvmn','v',1,1) from dual;
日期型函数
常用日期型函数
sysdate 当前日期和时间 select sysdate from dual;
last_day 本月最后一天 select last_day(sysdate) from dual;
add_months(d,n) 当前日期d后推n个月 select add_months(sysdate,2) from dual;
months_between(d,n) 日期d和n相差月数 select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;
next_day(d,day) d后第一周指定day的日期 select next_day(sysdate,'Monday') from dual;
day 格式 有 'Monday' 星期一
'Tuesday' 星期二
'wednesday' 星期三
'Thursday' 星期四
'Friday' 星期五
'Saturday' 星期六
'Sunday' 星期日
特殊格式的日期型函数
Y或YY或YYY 年的最后一位，两位，三位 select to_char(sysdate,'YYY') from dual;
Q 季度,1-3月为第一季度 select to_char(sysdate,'Q') from dual;
MM 月份数 select to_char(sysdate,'MM') from dual;
RM 月份的罗马表示 select to_char(sysdate,'RM') from dual; IV
month 用9个字符表示的月份名 select to_char(sysdate,'month') from dual;
ww 当年第几周 select to_char(sysdate,'ww') from dual;
w 本月第几周 select to_char(sysdate,'w') from dual;
DDD 当年第几天,一月一日为001 ,二月一日032 select to_char(sysdate,'DDD') from dual;
DD 当月第几天 select to_char(sysdate,'DD') from dual;
D 周内第几天 select to_char(sysdate,'D') from dual; 如 sunday
DY 周内第几天缩写 select to_char(sysdate,'DY') from dual; 如 sun
hh12 12小时制小时数 select to_char(sysdate,'hh12') from dual;
hh24 24小时制小时数 select to_char(sysdate,'hh24') from dual;
Mi 分钟数 select to_char(sysdate,'Mi') from dual;
ss 秒数 select to_char(sysdate,'ss') from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;
转换函数
to_number() 将合法的数字字符串 select to_number('88877') from dual; 88877
to_char() 将数字转换为字符串 select to_char(88877) from dual; '88877'
set serveroupt on;
dbms_output.put_line('hello world')
---输出hello world
特殊函数
set heading off 由于正在创建数据文件，不需要表头
set pagesize 0 不需要分页
set linesize 80 设置行的最大尺寸
set echo off 告诉sql plus 在执行语句时，不要回显语句
set feedback off 禁止sql plus 显示有多少满足查询的行被检索到
col sales format 999,999,999
append 添加文本到当前行尾
change/old/new/ 在当前行用新的文本代替旧的文本
change/text 从当前行删除ｗｅｎｂ
del 删除当前行
input text 在当前行之后添加一行
list 显示缓冲区中的所有行
list n 显示缓冲区中的第n行
list m n 显示m到n
rpad(c_start,n,c_repeat) 用户重复的字符串
BitAnd(n,m)按位与
BitOr(n,m)按位或
STDDEV 计算标准偏差
查询表的大小：
select segment_name, bytes
from user_segments
where segment_type = 'TABLE'
and segment_name='T_QL_REPORT';
查询表空间的大小：
Select Tablespace_Name, Sum(bytes) / 1024 / 1024
From Dba_Segments
Where Dba_Segments.tablespace_name='GXMES' --表空间名为‘GXMES’
Group By Tablespace_Name;
查询表的列名：
select column_name from user_tab_columns where table_name ='MT_RT_USERINFO';
select cname from col where tname ='MT_RT_USERINFO';
删除函数
oracle 删除一个表中全部的数据，速度很快 TRUNCATE
关键字: oracle 删除 表 全部 数据 速度 快
TRUNCATE TABLE tableName
tableName是要删除全部行的表的表名
TRUNCATE TABLE 虽然与不带 WHERE 的 DELETE 语句相同
但 TRUNCATE TABLE 速度更快而且占资源少速度快
TRUNCATE TABLE 不记录日志所以不能激活触发器
TRUNCATE TABLE 不能用于参与了索引视图的表
判断是否为数字型
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
表解锁
select a.sid, b.owner, object_name, object_type, a.LMODE,a.REQUEST from v$lock a, all_objects b where type='TM' and a.ID1 = b.object_id; alter system kill session ' sid, s.serial#,';select * from v$session t where t.SID = '119'
数据库闪滚
flashback table MT_RT_SYSTEMMENU to timestamp to_timestamp('2010-05-17 18:00:00’,'yyyy-MM-dd hh24:mi:ss');
拼接聚合函数
strcat() 值拼接 注：公司自己写的
查询表关联
select *
from user_constraints t
where t.r_constraint_name in
(select q.constraint_name
from user_constraints q
where q.table_name = 'T_QL_DATA_COLLECTION_CFG')
C#在后台报无效字符。
ORA-00911: invalid character
1.可能是你的 SQl最后面加了分号
2.可能是你安装Oracle问题
客户端 服务端都看下
打开注册表编辑下
Regedit.exe ---》 HKEY_LOCAL_MACHINE ---》SOFTWARE ---》 ORACLE-HOME
将这个节点NLS_LANG设置为
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
逐行累加函数
select item,sum(qty)over(partition by item order by order_no) as h from b
select Item,
? ?? ?? ?sum(Qty) over(order by Item) as "累计"
from
(
??select Item, Item from 表名
??order by Item
)
