sql语句的写法
1.条件一满足则条件二就不要加了。条件一不满足则按照条件二查询
where 条件1 or (not 条件1 and 条件2)
假设
条件一: a.id=b.id
条件二: a.NAME=b.NAME
WHERE Decode(a.id,b.id,1,Decode(a.NAME,b.NAME,1))=1
2.oracle中查询表和表的字段名的方法
select t.TABLE_NAME,to_char(wmsys.wm_concat(t.COLUMN_NAME)) as column_name from user_tab_columns t,user_tables t2 where t.table_name =t2.table_name group by t.table_name;
3.oracle查当前时间
select to_char(sysdate,'yyyy-mm-dd') as sys_time from dual; //获得2017-06-06
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as sys_time from dual; //获得2017-06-06 14:19:50
select to_char(to_date('2016-07-07','yyyy-mm-dd'),'day') as chinese_day from dual; // 获得星期四
select casethkssj from ywcl_case where substr(casethkssj,1,10) = to_char(sysdate,'yyyy-mm-dd');
select * from ywcl_case where to_char(‘casethkssj’,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd');
mysql
select top 1 * from tablename order by 时间 desc 不行的×
select * from tms_trans_notice where order_id='XLY411820' order by create_time desc LIMIT 1 可以
oracle:
select * from (select *,rownum as sn from tablename order by 时间 desc) as t where sn=1
还有：
SELECT * from tablename where 时间=(select max(时间) from tablename)
5.oracle中查询：两个字段的连接 成一个新的字段 或 条件的拼接
select KSMC || '|' || KSDM as ksmc_dm from GY_KSDM where KSMC like '%' || trim('xxx') || '%'
结果：ksmc_dm : 荷叶塘门卫|70042
6.oracle数据库报121541错误：
配置
ORACLE_HOME=c:\oracle\product\10.2.0\client_1
7.oracle双主键设置：
constraint PK_SC primary key (sno, cno)
8.相同的表t_xt_xzqh关联：
select * from t_xt_xzqh t1, t_xt_xzqh t2 where t1.xzqhsjbm = t2.xzqhbh and nvl(t1.xzqhyxbj, 1) = '1' and t2.xzqhbm = '22020000'
order by t1.xzqhbm
9.数据表的编码语言查询：
select userenv('language') from dual;
10.oracle数据库job每分钟运行一次（一天1440分钟）,存储过程(pro_ivrlog_del)每分钟按时运行设置job：
declare
job number;
begin
sys.dbms_job.submit(job,'pro_ivrlog_del;',sysdate,'sysdate+1/1440');
end;
11.oracle执行太多的会话时会锁住，找出所有照成锁的会话：
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.OBJECT_ID=o.object_id
kill 所有占用资源的会话：：
命令形式：alter system kill session 'sid,serial#';
占用test_1的资源的会话：
alter system kill session '158,15184';
alter system kill session '146 ,8229';
12.oracle创建用户
-- Create the user
create user gznsrxt identified by gznsrxt account unlock
default tablespace FRAME
temporary tablespace TEMP
ACCOUNT UNLOCK (可以忽略)
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to gznsrxt with admin option;
grant dba to gznsrxt with admin option;
grant resource to gznsrxt with admin option;
-- Grant/Revoke system privileges
grant create session to gznsrxt;
grant create view to gznsrxt;
grant unlimited tablespace to gznsrxt with admin option;
解释：：
create user "gznsrxt" 创建一个用户，名称为 gznsrxt
identified by "gznsrxt" 密码为gznsrxt
DEFAULT TABLESPACE "FRAME" 默认表空间是 FRAME
TEMPORARY TABLESPACE "TEMP" 临时表空间是 TEMP
ACCOUNT UNLOCK; 帐号正常未锁定
grant connect to gznsrxt with admin option;
授予用户gznsrxt 创建连接的角色，并可再授权
grant dba to gznsrxt with admin option;
授予用户gznsrxt 数据库管理员的橘色，并可再授权
oracle的默认设定是登陆时密码错几次之后,系统自动锁定该用户,此时可以用identified by gznsrxt account unlock这个命令对该用户解锁。
13导入导出表：
开始->运行->输入cmd->exp PECARD_HN/PECARD_HN@（127.0.0.1:1521/）orcl file=E:\work\dmp\PECARD_HN.dmp full=y 括号里的内容可能要忽略
开始->运行->输入cmd->imp PECARD_HN/PECARD_HN@127.0.0.1:1521/orcl file=E:\work\dmp\PECARD_HN.dmp full=y
将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
14.nvl/nvl2用法：
nvl(a,'n')<> 'y' 如果字段a不为null则为n，不然就是a之后与字符串y比较是否不等于这是个判断表达式
1、NVL是Oracle PL/SQL中的一个函数。
NVL( string1, replace_with)。如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL
2、NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，否则返回E2
例：
select * from t_xt_yh where nvl(yx_bj,'Y') = 'Y' and nvl(sc_bj,'N') = 'N'
15. 获得添加序列的id
select tx_fhnr_sequence.nextval id from dual;
16. 用count求每个id在表中的个数
select id,count(*) as counts from test group by id;
select flow_result, count(*) from asr_flow_record t where
t.indate>to_date('2016-09-21','yyyy-mm-dd') and t.indate<to_date('2016-09-22','yyyy-mm-dd')
group by flow_result
17.左连接和右连接
select * from a,b where a.id=b.id(+) AND a.name(+)='a';
a.name(+)='a' 相于 a.a.name = 'a' OR a.a.name IS NULL
a.id=b.id(+) 相于 a.id=b.id OR a.id有值，b.id="",
这里的意思就是a，b表连接，a表先全部展示，然后b表id和a表一致的就连接上，没有的就补些空格在后边。不是类似，就是左外连接。
例：
通过连接增加一行 “请选择”
select ' ' code, ' ' jg_dm, '请选择' caption from dual
union all
select * from (select jg_id code, jg_dm, jg_mc caption from t_xt_jg)
18.mysql语句：更改数字类型的默认值为0
select concat('alter table ',a.TABLE_NAME,' alter column ',a.COLUMN_NAME,' set default ''0'';'),a.*,b.*
from information_schema.`COLUMNS` a
INNER JOIN information_schema.`TABLES` b on (a.TABLE_NAME=b.TABLE_NAME)
where a.TABLE_SCHEMA='tms' and b.TABLE_SCHEMA='tms' and b.TABLE_TYPE='BASE TABLE' and b.TABLE_NAME like 'tms_%'
and a.data_type in ('bigint','int','decimal');
数字类型的字段 注意刷一下 默认值
18.mysql语句：更改数字类型的默认值为0
select trunc(sysdate, 'dd')+ rownum as R from user_objects where rownum <= 30
select trunc(sysdate, 'dd')+ rownum as R from dual where rownum <= 30
select trunc(sysdate, 'dd') as R from dual
select to_char(sysdate, 'dd') as R from dual
select trunc(sysdate, 'dd')+ rownum as R from user_objects where rownum <= 30 （30天后的日期）
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd')+ rownum from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
