Oracle笔记 三、function 、select.doc

Scott表下有这么几个常用的表，而且还带有数据。分别是emp、dept、salgrade； 1、查看表结构用desc desc emp; 2、空表dual，最常用的空表，如： select 2 * 4 from dual; select sysdate from dual; 3、双引号能保持格式 如：select sysdate “toDay 日 期” from dual; 4、|| 字符串连接 如：select 2*3 || 8 from dual; select ename || sal from scott.emp; select ename || ‘ORACLE’ from scott.emp; 5、单引号，如：select 2 * 2 || abcefg from dual; 用两个单引号表示一个单引号 6、去掉重复数据distinct select distinct deptno from scott.emp; 去掉重复组合：select distinct deptno,job from scott.emp; 7、where查询 A、=查询，select * from scott.emp where sal = 1500; B、比较、、=、= select * from scott.emp where sal 1500; C、and or select * from scott.emp where sal 1500 and sal = 5000 or deptno = 10; D、in、not in select * from scott.emp where sal in (1500, 800) and deptno not in (10, 20) E、like模糊 escape 转义 Select * from scott.emp where ename like ‘%in%’; Select * from scott.emp where ename like ‘%in\%k%’; Select * from scott.emp where ename like ‘%in#%k%’ escape ‘#’; 表示like中的#号是转义字符，相当于\ F、is null、is not null K、 order by select sal, ename from scott.emp order by sal; select sal, ename from scott.emp order by sal asc; select sal, ename from scott.emp order by sal desc; select sal, ename from scott.emp where sal 2000 order by sal desc; select sal, deptno, ename from scott.emp order by sal,deptno desc; 8、function A、lower、upper、substr select lower(‘abcABC’) from dual; select upper(‘abcABC’) from dual; substr(target, startIndex, length) select substr(‘abcABC’, 1, 3) from dual; B、chr、ascii 将数字安装ascii值转换成字符：select char(65) from dual; 将字符转换成ascii值：select ascii(‘Z’) from dual; C、round、to_char 精确小数 select round(22.456) from dual; 保留2位小数：select round(22.456, 2) from dual; 精确到个位：select round(22.456, -1) from dual; 货币 设置货币格式，00