--操作符
--算术运算+、-、*、/
select ename,sal,(sal*12+ 2000) from emp where sal>2000
--字符串连接操作符(||)
select(ename|| 'is a' ||job) as "employee details" from emp where sal>2000--字符创可以用单引号也可以用双引号,在别名中存在空格时,必须用双引号。在表名、列名时用双引号
--高级查询
--消除重复行
select distinct deptno from emp
--null值查询
select ename,job,comm from emp where sal<2000 and comm is null
--in 、not in
select ename,job,comm from emp where job in('SALESMAN','PRESIDENT','ANALYST')
--like模糊查询
select ename,job,sal from emp where ename like 'J%S'---查找名字为J开头S结尾的
select ename,job ,sal from emp where ename like '_O%'--查找名字第二个字为O的
---集合运算
--1.补集minus
select deptno from dept minus select deptno from emp--查询哪个部门下没有员工
--2.并集
insert into dept select 50,'公关部','台湾'from dual union select 60,'研发部','西安' from dual--使用union将多条记录一次性插入一张表中
--连接查询
--1.内连接((inner join)inner可省略)
select e.ename,e.job,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno
--2.外链接(outer join)
select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno(+)=d.deptno
--3.外链接(right outer join) outer可以省略
select e.ename,e.job,e.sal,d.dname from emp e right outer join dept d on e.deptno=d.deptno
--子查询
--1.单行子查询
select ename,job,sal from emp where deptno=(select deptno from dept where dname='SALES')
--2.any子查询
select ename,sal from emp where sal<any(select sal from emp where job='SALESMAN')--比子查询最大值小
--3.all子查询
select ename,sal from emp where sal<all(select sal from emp where job='SALESMAN')--比子查询所有结果都小,即小于最小值
--伪列
--1.rowid(物理地址)
select rowid ,ename from emp where sal>2000
---2.rownum
select rownum,ename,job,sal from emp where rownum<=5
--分页:oracle不支持top分页
--1.between
select * from(select rownum r,ename from emp) where r between 6 and 10
--2.rownum
select * from(select rownum r,ename from emp where rownum <=10) where r>5