Oracle数据库基本概念理解（1）

--函数 数字转换为字符--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 asselect * from scott.emp;--当前用户表行数大于10行的表select table_name from user_all_tables awhere 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.empselect * from scott.empunionselect * from scott.emp where job='CLERK'--UNIONALL 联合所有select * from scott.empunion ALLselect * from scott.emp where job='CLERK'--INTERSECT 交集select * from scott.empINTERSECT select * from scott.emp where job='CLERK'--MINUS  减集select * from scott.empMINUSselect * 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;