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
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