oracle 20130911

select t.*, t.rowid from t_table t where id between 2 and 5;
select t.*, t.rowid from t_table t where id>=2 and id<=5

select t.*, t.rowid from t_table t where id in(1,2,3);
select t.*, t.rowid from t_table t where id =1  or id=2 or id=3;

select t.*, t.rowid from t_table t where  t.name like '%1%'--%:通配0~n个字符
select t.*, t.rowid from t_table t where  t.name like '___'-- _:通配1个字符

select t.*, t.rowid from t_table t where  t.name is not null;--该列不为空
select t.*, t.rowid from t_table t where  t.name is null;--该列为空


select t.*, t.rowid from t_table t where id=1 and name='111'--与运算
select t.*, t.rowid from t_table t where id=1 or name='66'--或运算
select t.*, t.rowid from t_table t where not id=1; --非运算
select t.*, t.rowid from t_table t where id!=1 ;
--集合运算 集合的列数和类型必须匹配
--并集 去掉重复
select t.* from t_table t
union
select t1.id,t1.age,t1.name from t_user t1;
--并集 不去重复
select t.* from t_table t
union all
select t1.* from t_user t1;
--交集
select t.* from t_table t
INTERSECT
select t1.* from t_user t1;
-- 在A中A与B交集的补集
select t.* from t_table t --集合A
MINUS
select t1.* from t_user t1;-- 集合B

select t.id||t.age||t.name as afterstrs from t_table t --连接

select sysdate from dual;

select substr(t.name,2,1) as afterstrs from t_table t --取子串
select concat('str',t.name) as afterstrs from t_table t --字符串连接
select length(t.name) as afterstrs from t_table t --字符串长度
select upper(t.name) as afterstrs from t_table t --字符串中包含小写字母全转换到大写
select Lower(t.name) as afterstrs from t_table t --
--lTRIM()去左边空格  RTRIM()去右边空格

select trim('1' from '12314561') as afterstrs from dual -- 拆字符：结果231456

select mod(3,5) as afterstrs from dual -- 求模
select round(123.6555) as afterstrs from dual -- 取整124 四舍五入
select round(123.6555,2) as afterstrs from dual -- 取整123.66 四舍五入

select trunc(123.6555) as afterstrs from dual; -- 取整123
select trunc(123.6555,2) as afterstrs from dual; -- 取整123.65
select sqrt(9) as afterstrs from dual; --开方3

select to_date('2013/09/11','yyyy/MM/dd') as da from dual;--字符串转到日期
select sum(id) as ss  from t_table t  group by t.name;--分组函数  求和 sum
select count(*) as ss,t.name  from t_table t  group by t.name;--分组函数  求记录条数

select t.*  from t_table t  order by t.id desc,age asc;--排序 默认升序
select Translate('sasds', 's', 'x')   from dual;--结果：xaxdx

select nvl(t.name,1)  from t_table t --t.name为空时替换成1
select nvl2(t.name,1,2)  from t_table t --t.name为空时替换成2,不为空时替换成1
select nullif(t.name,'77')  from t_table t --将t.name的值为'77' 置为空

select max(t.age) as n,t.id  from t_table t  
group by t.id;
select max(t.age) as n,t.id from t_table t
group by t.id
having t.id!=3;
--分析函数
select t.*, RANK ()  OVER (ORDER BY t.age DESC)AS DENRANK  from t_user t;--不连续          1,1,3,4,4,4,4,8
select t.*, DENSE_RANK  ()  OVER (ORDER BY t.age DESC)AS DENRANK  from t_user t;-- 连续    1,1,2,3,3,3,3,4
select t.*, ROW_NUMBER  ()  OVER (ORDER BY t.age DESC)AS DENRANK  from t_user t;--根据行号 1,2,3,4,5,6,7,8

select t1.*,t2.*,RANK () OVER (PARTITION BY t1.age ORDER BY t2.age DESC)
 from t_user t1,t_table t2 where t1.id = t2.id;




select t1.*,RANK () OVER (PARTITION BY t1.age ORDER BY t1.id DESC) as 名次--PARTITION BY 列名：根据列名分区 根据t1.id DESC 排名
 from t_user t1