--函数 数字转换为字符
--0 强制位数，9位数不够不显示 $美元
SELECT TO_CHAR(124.3456,'0000.00') FROM dual ;
SELECT TO_CHAR(124.3456,'9999.99') FROM dual ;
SELECT TO_CHAR(124.3456,'$9999.99') FROM dual ;
--日期 日期转换为字符
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM dual ;
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH:MI:SS') FROM dual;
--字符转换为日期
SELECT TO_DATE('2005-12-06','yyyy-mm-dd') FROM dual;
--常用的伪列 rowid rownum
select rowid,emp.* from scott.emp;
select rownum,emp.* from scott.emp;
--查询 第三行数据
select from (select rownum rnum,s. from scott.emp s ) where rnum=3;
--转换空值的函数 NVL(EXP1, EXP2)select emp.* from scott.emp;
select nvl(comm,0) from scott.emp;
--去除重复行
select distinct job from scott.emp;
--根据现有表创建表
create table emp
as
select * from scott.emp;
--当前用户表行数大于10行的表
select table_name from user_all_tables a
where a.num_rows>10 ;
select * from sun.tuser;
--事务控制
insert into sun.tuser(userid,username,pwd)
values(18,'1777','1777');
savepoint aa; --保存事物点
insert into sun.tuser(userid,username,pwd)
values(19,'1777','1777');
rollback to aa; --回滚到保存的事物点
select * from sun.tuser;
commit--提交事务
--集合操作符
--1.union 联合
select * from scott.emp;
select count(*) from scott.emp
select * from scott.emp
union
select * from scott.emp
where job='CLERK'
--UNIONALL 联合所有
select * from scott.emp
union ALL
select * from scott.emp
where job='CLERK'
--INTERSECT 交集
select * from scott.emp
INTERSECT
select * from scott.emp
where job='CLERK'
--MINUS 减集
select * from scott.emp
MINUS
select * from scott.emp
where job='CLERK'
--\ 连接符号，类似 +;
--分析函数
--row_number 排名有相同数据时排名递增
--dense_rank 排名有相同数据时排名一样
--rank 排名有相同数据时排名一样，但在下一个不同数据空出排名
select ename, job,sal,
row_number()over(partition by job order by sal desc ) "number",
dense_rank()over(partition by job order by sal desc ) "dense_rank",
rank()over(partition by job order by sal desc ) "rank"
from emp;
select ename, job,sal,
row_number()over( order by sal desc ) "number",
dense_rank()over(order by sal desc ) "dense_rank",
rank()over( order by sal desc ) "rank"
from emp;
