Oracle学习笔记1-查询

一、简单查询：
1.查询emp表中所有数据
select * from emp;

2.查询指定的列（例如emp表中，雇员编号，雇员姓名，雇员薪水）
select  empno ,ename,sal from emp;

3.查询中使用四则运算（+-*/）.
select  empno ,ename,sal*12 from emp;

4.对显示列进行别名设置。
select empno as 雇员编号，ename as 雇员姓名，sal*12 as 年薪 from emp;

5.通过||进行数据连接，出现的字符串要用 '' 括起来
select '雇员编号'||empno||'雇员姓名：'||ename||'年薪：'||sal*12 as 雇员信息 from emp;
查询结果如下：

6.去重查询用distinct关键字
select distinct job from emp;

二、限定查询
where 关键字
select * from emp where sal>1500;

select * from emp where ename='SMITH';
!=关键字
SELECT * from emp where job!='clerk';

select * from emp where sal>=1500 and job='SALESMAN';

select * from emp where job!='CLERK' and sal>2000;

select * from emp where not（job='CLERK' or SAL<=2000）;

范围查询  between ..and
select * from emp where sal between 1500 and 3000;

select * from emp where hiredate between '01-1月-81'and '31-12月-1981';

null 不能用=号判断，只能用is null 和 is not null
select * from emp where comm is not null;


列表范围查找：in，not in 
select *from emp;
select * from emp where empno  in('7369','7788','7566');
但是在使用not in 操作时请注意，不能判断null的情况，如果判断null则，强制显示结果都为空。

like not like模糊查询
使用通配符%
select * from emp where ename like 'S%';
"_"表示一个字符
select * from emp where ename like '______%';

select * from emp where ((deptno=10 and job='MANAGER') OR (deptno=20 and job='CLERK')
OR (JOB NOT IN ('MANAGER','CLERK')AND SAL>2000)) AND (ENAME LIKE'%s%'OR ENAME LIKE '%K%');

order by子句控制
降序desc,升序
select * from emp order by sal desc;

select * from emp order by sal ;

select * from emp order by sal desc ,hiredate asc;