数据库---oracle学习笔记1

select distinct dempno ,job from emp;

select * from emp;
select * from emp where deptno=10;
select * from emp wehre ename='clack';
select * from emp where sal>1500;
selece ename,sal from emp where deptno<>10;

select * from emp where sal between 800 and 1500;
select * from emp where comm = null;--为选定行
select * from emp where comm is null;
select * from emp where sal in(800,1500);
select * from emp where ename in ('smith','king');
select * from emp where hiredate>'20-2月-81'
select sysdate from dual;
select sysdate from emp;--查询本表的日期，格式特殊
or and not --连接符号
select ename rofm emp where ename like '%all%';
select ename from emp where ename like '_al%';
select ename from emp where eanme like '%$%%' escape '$'; -- 这里指定转义字符为￥
select * from demp order by dempno desc;
select * from emp order by asc;
select ename from emp where deptno <>10 order by deptno asc ename desc;
lower
select lower(ename) from emp;
select ename from emp where lower(ename) like '_a';
select substr(ename,2,3) from emp;
select char(65) from dual;--a ascii转换成字符
select ascii('a') from dual ;--65
select round(23.653) from dual;
select round(23.653,-1) from dual;
selct to_char(sal,'$99,999.9999') from emp;--转换的格式必须加‘’
select to_char(sal,'L00000.0000') from emp;
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
select to_char(sysdate ,'YYYY-MM-DD HH24:MI:SS') from dual;
select ename ,hiredate from emp hiredate >'1981-2-20 12:34:56';--格式不匹配
select ename ,hiredate from emp hiredate >todate('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select dal from emp where sal > to_number('$1,250','99,999.99');
nvl--空值
select ename sal*12+nvl(comm,0) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(*) from emp;
select count(*) from deptno=10;
select count(comm) from emp;
select count(distinct deptno) from emp;
select avg(sal),deptmo from emp group by deptno order by sal;
select max(sal) from emp group br deptno,job;
select ename from emp where sal=(select max(sal) from emp);
select avg(sal) from emp group by deptno;
select avg(sal) from emp group by deptno hanving avg(sal) >2000;
select avg(sal) from emp where sal>1200 group by deptno
having avg(sal)>1500
order by avg(sal) desc;