oracle(1)基础总结

以scott用户下的表emp为例

--------------------------------------------------------------------------------------------------------where

查询emp表的不重复的工作

select distinct job from emp

查询工资不等于1500的员工【!=或<>】

select * from emp where sal <> 1500

查询薪水在1300到1600之间的员工，包括1300和1600

select * from emp where sal between 1300 and 1600;

查询薪水不在1300到1600之间的员工，不包括1300和1600

select * from emp where sal NOT between 1300 and 1600;

查询入职时间在指定的时间段的员工

select * from emp where hiredate between '1981/6/9' and '1987/4/19';

或者

select * from emp where hiredate   between  to_date('1981/2/20','yyyy/mm/dd') and to_date('1987-4-19','yyyy/mm/dd');

查询20号或30号部门的员工，例如：根据ID号，选中的员工，批量删除 select * from emp where (deptno=20) or (deptno=30); 或 select * from emp where deptno in (30,20);

查询不是20号或30号部门的员工 select * from emp where deptno NOT in (30,20);

查询员工姓名中含有'_'的员工，使用\转义符，让其后的字符回归本来意思【like '%\_%' escape '\'】

select * from emp where ename like '%\_%' escape '\';

查询佣金为null的员工 select * from emp where comm is null; 注意：null不能参数=运算       null能参数number/date/varchar2类型运算

--------------------------------------------------------------------------------------------------------order by

查询员工信息，按入职日期降序排序，使用列名 select empno,ename,sal,hiredate,sal*12 "年薪" from emp order by hiredate desc;

--------------------------------------------------------------------------------------------------------单行函数

测试lower/upper/initcap函数，使用dual哑表 select lower('www.BAIdu.COM') from dual; select upper('www.BAIdu.COM') from dual; select initcap('www.BAIdu.COM') from dual;

测试concat/substr函数，从1开始，表示字符，不论中英文 select concat('hello','你好') from dual;正确 select concat('hello','你好','世界') from dual;错误 select 'hello' || '你好' || '世界' from dual;正确 select concat('hello',concat('你好','世界')) from dual;正确 select substr('hello你好',5,3) from dual; 5表示从第几个字符开始算，第一个字符为1，中英文统一处理 3表示连续取几个字符

测试length/lengthb函数，编码方式为UTF8/GBK(赵君)，一个中文占3/2个字节长度，一个英文一个字节 select length('hello你好') from dual; select lengthb('hello你好') from dual;

测试instr/lpad/rpad函数，从左向右找第一次出现的位置，从1开始 select instr('helloworld','o') from dual; 注意：找不到返回0       大小写敏感 select LPAD('hello',10,'#') from dual; --左加‘#’长度10 select RPAD('hello',10,'#') from dual;--右加‘#’长度10

测试trim/replace函数 select trim(' ' from '  he  ll                ') from dual; select replace('hello','l','L') from dual;

测试round/trunc/mod函数作用于数值型 select round(3.1415,3) from dual; select trunc(3.1415,3) from dual; select mod(10,3) from dual;

当前日期：sysdate = 2016/5/24

测试round作用于日期型（month） select round(sysdate,'month') from dual;    --2016/6/1

测试round作用于日期型（year） select round(sysdate,'year') from dual; --2016/1/1

测试trunc作用于日期型（month） select trunc(sysdate,'month') from dual; --2016/5/1

测试trunc作用于日期型（year） select trunc(sysdate,'year') from dual; --2016/1/1

显示昨天，今天，明天的日期，日期类型 +- 数值 = 日期类型 select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;

以年和月形式显示员工近似工龄，日期-日期=数值，假设：一年以365天计算，一月以30天计算 select ename "姓名",round(sysdate-hiredate,0)/365 "天数" from emp;

使用months_between函数，精确计算到年底还有多少个月 select months_between('31-12月-15',sysdate) from dual;

使用months_between函数，以精确月形式显示员工工龄 select ename "姓名",months_between(sysdate,hiredate) "精确月工龄" from emp;

测试add_months函数，下个月今天是多少号 select add_months(sysdate,1) from dual;

测试add_months函数，上个月今天是多少号 select add_months(sysdate,-1) from dual;

测试next_day函数，从今天开始算，下一个星期三是多少号【中文平台】 select next_day(sysdate,'星期三') from dual;

测试next_day函数，从今天开始算，下下一个星期三是多少号【中文平台】 select next_day(next_day(sysdate,'星期三'),'星期三') from dual;

测试next_day函数，从今天开始算，下一个星期三的下一个星期日是多少号【中文平台】 select next_day(next_day(sysdate,'星期三'),'星期日') from dual;

测试last_day函数，本月最后一天是多少号 select last_day(sysdate) from dual;

测试last_day函数，本月倒数第二天是多少号 select last_day(sysdate)-1 from dual;

测试last_day函数，下一个月最后一天是多少号 select last_day(add_months(sysdate,1)) from dual;

测试last_day函数，上一个月最后一天是多少号 select last_day(add_months(sysdate,-1)) from dual;

注意： 1）日期-日期=天数 2）日期+-天数=日期

--------------------------------------------------------------------------------------------------------三大类型转换

oracle中三大类型与隐式数据类型转换 （1）varchar2变长/char定长-->number，例如：'123'->123 （2）varchar2/char-->date，例如：'25-4月-15'->'25-4月-15' （3）number---->varchar2/char，例如：123->'123' （4）date------>varchar2/char，例如：'25-4月-15'->'25-4月-15'

查询1980年12月17日入职的员工（方式一：日期隐示式转换） select * from emp where hiredate = '17-12月-80';

使用to_char(日期，'格"常量"式')函数将日期转成字符串，显示如下格式：2015 年 04 月 25 日 星期六 select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;

使用to_char(日期，'格式')函数将日期转成字符串，显示如格式：2015-04-25今天是星期六 15:15:15 select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual; 或 select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：$1,234 select to_char(1234,'$9,999') from dual;

使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：￥1,234

select to_char(1234,'$9,999') from dual; select to_char(1234,'L9,999') from dual;

使用to_date('字符串','格式')函数，查询1980年12月17日入职的员工（方式二：日期显式转换） select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"'); 或 select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd'); 或 select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

使用to_number('字符串')函数将字符串‘123’转成数字123 select to_number('123') from dual;

注意： select '123' + 123 from dual;246 select '123' || 123 from dual;123123