Oracle表的基本查询

入门查询

1、查看表结构

Desc dept;

2、取消重复行

Select distinct depno from emp;

3、显示雇员年工资

select ename ,(sal+nvl(comm,0))*12 "年工资" ,comm from emp;

4、oracle 连接字符串(||)

Select ename || ' is a ' || job from emp;

5、入职时间在1982/1/1后

select * from emp where hiredate >'1-1月-1982'

6、like 操作符 % 零个或者多个 _ 表示一个字符

7、in 查询方式

8、order by 语句 asc desc

9、分页查询？？？(oracle的分页比其他的难)

复杂查询

1、 数据分组 max 、min、avg、sum、count

2、 group by ，having

group by 用于对查询的结果分组统计(分组字段必须在查询中)

having 用于限制分组的显示结果

实例：显示每个部门的每种岗位的平均工资和最低工资

select deptno,job ,max(sal),avg(sal) from emp group by deptno,job order by deptno;

实例2：部门平均工资大于2000

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000

多表查询

1、多表查询是指基于两个或者两个以上的表后者视图的查询。在实际应用单表不 能满足你的需求。

2、多表查询无条件结果是笛卡尔积表1个数*表2个数

3、如果有2表至少有一个条件，如果有3个表至少有2个条件才能消除笛卡尔积

自连接

1、 自连接是指同一张表的连接查询。

2、 EMP表特别形象。显示员工的上级 emp as worker ,emp as boss

实例 : 显示员工的上级领导

Select worker.ename,boss.ename from emp as worker ,emp as boss where worker.mrg=boss.empno

子查询

说明: 子查询是指在其他SQL语句中的select 语句，也叫嵌套查询

1、 单行子查询 (=) single-row subqueries

解释： 单行子查询是指返回一行数据的子查询语句

2、 多行子查询 (in) multiple-row subqueries

解释： 多行子查询是指返回多行数据的子查询

3、 多行子查询使用all操作符

Select ename,sal,deptno from emp where sal>all(select sal from emp where deptno =30);

4、 多行子查询中时用any操作符

Select ename,sal,deptno from emp where sal>any(select sal from emp

Where deptno=30);

5、 多列子查询 multiple-column subqueries

解释: 多列子查询是指返回多个列数据的子查询，而单行、多行子查询都是返回的其中一列

实例：

Select * from emp where (deptno,job) =(select deptno,job from emp where ename=’SMITH’)

6、 在from 字句中使用子查询

解释： 当在from字句中使用子查时，该子查询会被当一个视图对该，因此叫做内嵌视图，当在from 字句中使用子查询时，必须给子查询指定别名。

实例： 如何显示高于自己部门平均工资的员工信息下面是步骤

1、 查询各个部门的平均工资

Select deptno,avg(sal) mysal from emp group by deptno

2、 把上面的查询看做是一张子表

(Select deptno,avg(sal) mysal from emp group by deptno) a1

3、实例结果：

Select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2 , (Select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal

7、相关子查询 correlated subqueries

解释： 相关子查询是指引用了父查询中某些表或其某些列的子查询(但父查询不能引用子查询中的表或其列)。当最终结果集的内容需要子查询依赖父查询中每一行中的记录值时，需要使用相关子查询。

实例: 每个部门最高雇员的信息(无distinct)

select deptno ,

(select max(sal) from emp b where b.deptno=a.deptno) maxsal

From emp a order by deptno;

8、标量子查询 (scalar subqueries)

解释： 标量子查询只返回单行单列数据。

实例： 查询每个部门的最高工资信息(使用了distinct)

select deptno ,

(select max(sal) from emp b where b.deptno=a.deptno) maxsal

From emp a order by deptno;

9、在Create Table 中使用子查询

实例: create table dept1 (depton,dname,loc) as

Select depton,dname,loc from dept

10、在Create View 中使用子查询

实例: create or replace view dept_20 as

Select * from emp1 where deptno=20 order by empno;

11、在 update 中使用子查询

实例: update emp1 set (sal,comm)=

(Select sal,comm from emp1 where ename=’WARD’)

Where job=(select job from emp1 where ename-‘WARD’)

实例: update emp set (job,sal,comm)=(select job,sal,comm. From emp where ename=’SMITH’ ) where ename=’SCOTT’

12、在 insert 中使用子查询

13、在 delete中使用子查询

分页查询

1、 oracle分页总共有三种方式

(1)、rownum 分页 (rownum oracle分配的行号) 效率居中

①、select * from emp;

②、select a1.* ,rownum rm from (select * from emp) a1

③、select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>6

A、 如果要指定查询列，只需要修改最里层的子查询

B、 如果排序也只需要在最里层排序

C、 在子查询中只能使用rownum 不能使用rn

2、 按照分析函数分页(速度最慢)

3、 根据RowID 分页 (速度最快)

用查询结果创建新表

解释： 导入表结构以及表数据

实例: create table mytable (id,name,sal,job,deptno)

As select empno,ename,sal,job,deptno

合并查询

集合操作符 union ,union all, intersect,minus

1、 union该操作符取得两个结果的并集,当使用该操作符会自动去掉重复行

2、 union all 与union相似,不会去掉重复行,不会排序

3、 intersect 取得两个结果的交集

4、 minus 使用该操作符用于取得两个结果的差集,它只会显示在第一集合中而不存在第二个集合中的数据

实例：

Select ename,sal,job from emp where sal>2500

(集合操作符)

Select ename,sal,job from emp where job=’MANAGER’

创新新的数据库

1、通过oracle提供的工具向导 dbca数据库配置助手

2、手工步骤直接创建09

注意:

1、 运算符中如果有Null值则结果为null

2、 nvl(comm,0)如果comm为null 怎变成0处理

3、 查询时列名用”” 如果是字符串连接用’’

4、 Group by 、having、order by 顺序不能颠倒

5、 笛卡尔积规定：多表查询的查询条件不能少于表的个数-1 才能消除

6、 Sql 执行时先执行条件在最后面的(这个可以优化速度)

7、 Max(sal) 效率比 all (select sal from emp) 高

8、 多行子查询中 all =max() any =min()

9、 给列取别名可以加as 给表取别名不能加as

10、集合操作符速度比( and or 速度快的多)(自己测试快一倍)

11、在select、update、delete语句的子查询中不能使用order by 子句,他只能用在最外面一层的父查询中。但在Create 语句中的子查询可以使用order by

12、在pl/sql 中按上下行翻出3行信息，修改当前行的语句才起作用。

13、oracle默认日期格式为: 22-11月-1988

添加时采用to_date(‘1988-11-22’,yyyy-mm-dd) 等方式