12.25笔记及作业

—日期查询（大于，小于，大于等于，小于等于）
select from emp where hiredate > TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate < TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate >= TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate <= TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
—null用法
select from emp where MGR is null;
select from emp where mgr is not null;
—连接操作符||
select ename || ‘ job is ‘ || job from emp;
—去重 distinct
select distinct deptno from emp;
—数字函数，round按照四舍五入运算结果
—dual伪表
select round(5) from dual;
select round(5.23) from dual;
select round(5.6) from dual;
—round(,1)其中1为指定小数点后位数
select round(5.635, 1) from dual;
select round(5.655, 2) from dual;
—trunc()函数的用法
select sysdate from dual;
select trunc(sysdate) from dual; —日期年月日
select trunc(sysdate, ‘yy’) from dual; —返回当年第一天
select trunc(sysdate, ‘mm’) from dual; —返回当月第一天
select trunc(sysdate, ‘dd’) from dual; —返回当前年月日
select trunc(sysdate, ‘d’) from dual; —（星期天为第一天）返回当前星期第一天
select trunc(sysdate, ‘hh’) from dual; —当前时间
select trunc(sysdate, ‘mi’) from dual; —没有秒的概念
—trunc(number,num_digits)取整，直接舍去，不存在四舍五入
select trunc(123.658) from dual; —取整
select trunc(123.456, 1) from dual; —取小数点后一位
select trunc(123.456, -1) from dual; —取小数点前一位，并将小数点前一位值设为0
select trunc(123.456, 0) from dual; —取整
—initcap(str)首字母大写，其余小写
select initcap(‘huaMi’) from dual;
—获取字符串长度length(str)
select length(‘huamin’) from dual;
—nvl2（str,value1,value2）如果str为非空，返回value1，否则，返回value2
select nvl2(‘2’, 0, 1) from dual;
select nvl2(‘’, 0, 1) from dual;
—替换replace(str,y,z),在字符串str中找到y，并将y替换为z
select replace(‘huamin’, ‘ua’, ‘i’) from dual;
—substr(str,start,length]),截取str从start开始，指定长度length的字符
select substr(‘huamin’, 2, 3) from dual;
—lpad(str1,x,str2),左侧添加，x为两个字符串的总和长度
select lpad(‘huamin’, 10, ‘chen’) from dual;
—rpad(star1,x,star2)右侧添加，x为两个字符串的总和长度
select rpad(‘huamin’, 10, ‘chen’) from dual;
—ltrim()去左侧空格；去掉左侧从左开始的字符串
select ltrim(‘ huahuaminh’) from dual;
select ltrim(‘huahuaminh’, ‘hua’) from dual;
—rtrim()去右侧侧空格；去掉右侧从右开始的字符串
select rtrim(‘huahuaminh ‘) from dual;
select rtrim(‘huamin’, ‘in’) from dual;
—加减乘除
select sum(sal) from emp;
select avg(sal) from emp;
select sum(sal) / count(1) from emp;
—not and or,between and
— <,>,<=,>=,
—like,in,not in
select from emp where deptno in (20, 10);
select
from emp
where deptno = 20
or deptno = 10;
select * from emp where deptno not in (20, 10);
—日期
select ename, to_char(hiredate, ‘fmDD MM YYYY’) hiredate from emp;
select ename, to_char(hiredate, ‘fmDD Month YYYY’) hiredate from emp;

select to_char(sal, ‘$9’) salary from emp;

select to_number(‘1872.34’) from dual;

select to_date(‘20201224’,’yyyy-mm-dd’) from dual;
select to_date(‘20201224’,’fmDD-MM-YYYY’) from dual;
—十进制和十六进制转换(互换)
select to_char(2463,’xxxx’) from dual;
select to_number(‘99f’,’xxxx’) from dual;