oracle11g教程从入门到精通 下载本文

ename = 'SMITH');

在from子句中使用子查询

请思考:如何显示高于自己部门平均工资的员工的信息 思路:

1. 查出各个部门的平均工资和部门号

SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把上面的查询结果看做是一张子表

SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

如何衡量一个程序员的水平?

网络处理能力, 数据库, 程序代码的优化程序的效率要很高

小总结:

在这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。

注意:别名不能用as,如:SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

在ds前不能加as,否则会报错 (给表取别名的时候,不能加as;但是给列取别名,是可以加as的)

分页查询

按雇员的id号升序取出

oracle的分页一共有三种方式

1.根据rowid来分

select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 执行时间0.03秒 2.按分析函数来分

select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 执行时间1.01秒 3.按rownum来分

select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 执行时间0.1秒

其中t_xiaoxi为表名称,cid为表的关键字段,取按cid降序排序后的第9981-9999条记录,t_xiaoxi表有70000多条记录。

个人感觉1的效率最好,3次之,2最差。

//测试通过的分页查询okokok

select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5;

下面最主要介绍第三种:按rownum来分 1. rownum 分页

SELECT * FROM emp; 2. 显示rownum[oracle分配的]

SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; rn相当于Oracle分配的行的ID号 3.挑选出6—10条记录 先查出1-10条记录

SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 如果后面加上rownum>=6是不行的, 4. 然后查出6-10条记录

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 5. 几个查询变化

a. 指定查询列,只需要修改最里层的子查询 只查询雇员的编号和工资

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查询,只需要修改最里层的子查询 工资排序后查询6-10条数据

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;

用查询结果创建新表

这个命令是一种快捷的建表方式

CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

创建好之后,desc mytable;和select * from mytable;看看结果如何?

合并查询 合并查询

有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号union,union all,intersect,minus

多用于数据量比较大的数据局库,运行速度快。 1). union

该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 2).union all

该操作符与union相似,但是它不会取消重复行,而且不会排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。 3). intersect

使用该操作符用于取得两个结果集的交集。

SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus

使用改操作符用于取得两个结果集的差集,他只会显示存在第一个集合中,而不存在第二个集合中的数据。

SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS

SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; (MINUS就是减法的意思)

创建数据库有两种方法:

1). 通过oracle提供的向导工具。√

database Configuration Assistant 【数据库配置助手】 2).我们可以用手工步骤直接创建。

七:java操作oracle

java连接oracle

介绍:前面我们一直在plsql中操作oracle,那么如何在java 程序中操作数据库呢? 下面我们举例说明,写一个java,分页显示emp表的用户信息。 Java代码

1. package com.sp; 2.

3. import java.sql.Connection; 4. import java.sql.DriverManager; 5. import java.sql.ResultSet; 6. import java.sql.Statement; 7.

8. //演示 如何使用 jdbc_odbc桥连接方式 9. public class TestOracle { 10.

11. public static void main(String[] args) { 12. try { 13.

14. // 1.加载驱动

15. Class.forName(\iver\ 16.

17. // 2.得到连接

18. Connection ct = DriverManager.getConnection(

19. \racle\\ 20.

21.\ 22.

23. // 从下面开始,和SQL Server一模一样

24. Statement sm = ct.createStatement();

25. ResultSet rs = sm.executeQuery(\ct * from emp\

26. while (rs.next()) { 27. //用户名

28. System.out.println(\用户名: \2));

29. //默认是从1开始编号的 30. }

31. } catch (Exception e) { 32. e.printStackTrace(); 33. } 34. } 35.}

在得到连接那里,要去配置数据源,点击控制面板-->系统和安全-->管理工具-->数据源(ODBC),打开后点添加,如图: 可以看到,有个Oracle in OraDb10g_home1的驱动,它是Oracle安装完后自动加上去的。 选中后,点完成,再填如下信息,如图:

这样配好后基本就可以了,但为了安全起见,建议大家测试一下,点击 Test Connection按钮, 测试通过后点ok,然后数据源就生成了,如图:

然后把数据源名称写进jdbc.odbc:里。

这里要注意:jdbcodbc能不能远程连接呢?不能远程连接,也就是你这样写的话就意味着java程序和oracle数据库应该是在同一台机器上,因为这里没有指定IP地址,肯定默认就是本地。如果要远程连,就用jdbc,jdbc是可以远程连的。

运行TestOracle.java,控制台输出.......................