Oracle-查询

查询

简单查询

语法

SELECT [DISTINCT]*| 字段 [别名][,字段 [别名]]
FROM 表名称 [表别名]

查询 dept 表的全部记录

select * from dept;

按列名查询

select empno,ename,sal from emp;

去重复

注意：查询出来的 job 内容有重复数据，使用 distinct 删除重复

select distinct job from emp;

别名

列的名称不方便浏览，可以使用列别名

select ename,job,sal*12 income from emp;
select ename,job,sal*12 as income from emp;

简单查询中，可以使用四则运算符

查询每一个雇员的姓名，职位和基本年薪

select ename,job,sal*12 from emp;

每个月每个人有 200 的饭补和 100 的车补，计算年薪

select ename,job,(sal+300)*12 income from emp;

年底多发一个月的基本工资

select ename,job,(sal+300)*12+sal income from emp;

使用||连接符

select empno||','||ename from emp;

限定查询

语法

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]
FROM 表名称 [表别名]
[WHERE 条件(s)]

条件：

>,>=,<,<=,!=(<>),
BETWEEN...AND...,LIKE,IN,IS NULL,AND,OR,NOT

关系运算

工资高于1500 的所有雇员信息

select * from emp where sal>1500;

职位是办事员的雇员信息

select * from emp where job='CLERK';

在 oracle 数据库，数据区分大小写

多条件查询

select * from emp where job='CLERK' or job='SALESMAN';

select * from emp
where (job='CLERK' or job='SALESMAN') 
and sal>1200;

查询所有不是办事员的雇员信息

select * from emp where job<>'CLERK';
select * from emp where job!='CLERK';
select * from emp where NOT job='CLERK';

范围查询

工资在 1500-3000 之间的全部雇员信息

select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;

求反

select * from emp where not sal between 1500 and 3000;

判断是否为空

查询出所有领取奖金的雇员信息

select * from emp where comm is not null;
select * from emp where not comm is null;

指定范围的判断

查询雇员编号是 7369，7566，7799 的雇员信息

select * from emp where empno in (7369,7566,7799);

不在指定范围内
select * from emp where empno not in (7369,7566,7799,null);

注意：关于 NOT IN 的问题

• 如果使用 IN 操作符，查询的范围之中存在 null，不影响查询
• 如果使用 NOT IN 操作符，如果查询范围中有 null，则不会有任何查询结果

模糊查询

LIKE 子句

• _匹配单个字符

• %: 匹配任意多个字符
  雇员姓名中以字母 A 开头的全部雇员信息

  select * from emp where ename like ‘A%’;
  雇员姓名中第二个字母是 A 的全部雇员信息

  select * from emp where ename like ‘_A%’;
  求反

  select * from emp where ename not like ‘%A%’;
  %%表示查询全部信息

多表查询

语法：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]
FROM 表名称 [别名],[表名称 [别名],表名称 [别名]...]
[WHERE 条件(S)]
[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

多表查询之前，首先必须查询各个表中的数据量，这个操作可以通过 COUNT()函数来完成

确认数据量后，执行多表查询语句：

select * from emp,dept;

显示消除了笛卡尔积，实际没有

采用关联字段的形式

select * from emp e,dept d where e.deptno=d.deptno;

关联查询两张表中的信息

select e.empno,e.ename,e.job,d.dname,d.loc
 from emp e,dept d
 where d.deptno=e.deptno;

关联查询一张表中的信息，自身关联

select e.ename,e.job,m.ename 
from emp e,emp m
 where e.mgr=m.empno;

左、右连接

左右连接可以改变查询判断条件的参考方向

• (+)用于左右连接的更改，这种符号有以下两种使用情况：
  (+)=:表示右连接
  =(+):表示左连接
  该符号为 oracle 独有

表别名

select e.empno,e.ename,d.dname
 from emp e,dept d
 where e.deptno=d.deptno;

内连接

内连接必须满足连接条件，不满足的信息无法显示

A（inner） join B on 连接条件

select e.ename,e.empno,d.dname
 from dept d inner join emp e
on e.deptno=d.deptno;

左外连接

关键字 A left join B on 连接条件

select e.empno,e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;

和下面代码功能相同

select e.empno,e.ename,d.dname
 from myemp e,dept d
 where e.deptno=d.deptno(+);

右外连接

关键字 A right join B on 连接条件

select e.empno,e.ename,d.dname
 from emp e right join dept d
 on e.deptno=d.deptno;

select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

完全外连接 full join

select e.ename,d.dname
from emp e full join dept d
on e.deptno=d.deptno;

自然连接 natural join

不用区分关联条件，oracle 自动为我们区分

select ename,dname 
from emp natural join dept 
 where sal>2000;

缺点
如果表名不确定，不能查询

自连接

select e.ename,e.job,m.ename 
 from emp e left join emp m
 on e.mgr=m.empno;

条件查询

 select e.ename,e.job,m.ename
 from emp e,emp m
 where e.mgr=m.empno;

数据伪列 ROWNUM

ROWNUM 为每一个显示的记录都会自动的随着查询生成的行号。
查询前五条记录

select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;

查询 6-10 行记录

• 错误做法：

  select rownum,empno,ename,job,hiredate,sal from emp where rownum between 6 and 10;
  

• 执行完成后并没有返回任何数据，因为 rownum 不是真实列，正确的思路是先查询前 10 条记录，然后再查询后 5 条记录，需要使用子查询

  select * from (
    select rownum rn,empno,ename,job,hiredate,sal from emp
    where rownum<=10) temp
    where temp.rn>5;
  

ROWID

ROWID 表示的是每一行数据保存的物理地址的编号。
查看 ROWID

select rowid,deptno,dname,loc from dept;

每一条记录的 ROWID 都不会重复，所以即使所有列的内容重复，ROWID 也不会重复.

• 格式为：AAAL+XAAEAAAAANAAA
  其中： 数据对象号：AAAL+X
  相对文件号：AAE
  数据块号： AAAAAN
  数据行号： AAA