简单查询:
select * from emp;
select job from emp;
select empno as 员工编号 ,ename as 员工姓名 from emp;
select empno eno,ename eme from emp;--常用注释
select distinct job from emp;
select distinct ename nam, job jo ,deptno deno from emp; --只有所在行全部相同才能被认为相同,可以合并
select '员工编号是' | | empno | | '姓名是' | | ename | | '的工作是' | | job from emp ;
限定查询
select * from emp where sal>2000;
select * from emp;
select * from emp where comm is null; --查询奖金是空的 注意有0 不为空
select * from emp where comm is not null; --查询奖金不是空的 注意有0 不为空
select * from emp where comm is not null and sal>1500;--既有奖金并且工资大于1500
select * from emp where comm is not null or sal>1500;--有奖金或者工资大于1500
select * from emp where comm is null and sal <=1500; --查询没有奖金的和工资不大于1500的
select * from emp where not (comm is not null or sal>1500); --同上
select * from emp where sal >1500 and sal<3000; --不包含边界
select * from emp where sal >=1500 and sal<=3000; --包含边界
select * from emp where sal between 1500 and 3000; --同上
select * from emp where hiredate between to_date ('1981-1-1','yyyy-mm-dd') and to_date ('1981-12-12','yyyy-mm-dd');
--查询日期
select * from emp where ename ='WARD'; --查询内容时注意大小写
select * from emp where empno in('7369','7499','7654'); --查询在empno中函数in中的编号 精确查询
select *from emp where ename like '%M%'; --查询名字中含有M字符的
select * from emp where ename like '_M%'; -- 查询名字中第2个字符是M的
select * from emp where ename like '%M__'; --查询名字倒数第三位是M的
select * from emp where empno != 7369; --查询编号中不为7369的