韩顺平Oracle实用教程：安装与基础命令详解

需积分: 10 172 浏览量更新于2024-07-27 收藏 993KB PDF 举报

Oracle实用教程由韩顺平老师编写，专为想要学习Oracle数据库的朋友提供指导。该教程深入浅出地介绍了Oracle的基础知识，特别关注于系统管理与日常操作。以下是部分核心知识点： 1. **Oracle账户管理**: - Oracle安装时会自动创建两个关键用户：sys和system。 - sys用户是超级用户，拥有最高权限，包括sysdba角色，可以创建数据库，初始密码是change_on_install。 - system用户则是管理操作员，具有sysoper角色，但无create database权限，初始密码为manager。大部分数据库维护操作通常使用system用户进行。 2. **sql*plus常用命令**: - `conn`：用于连接数据库，如`conn 用户名/密码@网络服务名 [assysdba/sysoper]`，在需要特权时添加assysdba或sysoper标志。 - `disconnect`：关闭当前数据库连接。 - `password`：更改用户密码，修改其他用户密码需sys/system权限。 - `showuser`：显示当前登录用户。 - `exit`：断开连接并退出sql*plus。 - `start` 和 `@`：执行SQL脚本，如`sql>@d:\a.sql` 或 `sql>start d:\a.sql`。 - `edit`：编辑指定的SQL脚本文件。 - `spool`：将输出内容保存到文件，如`sql>spool d:\b.sql` 并在完成时输入 `spool off`。 3. **交互式命令**： - `&`：可以替换变量，在执行时用户需要输入相应值，如`select * from emp where job = '&job';`。 - `edit`：再次强调编辑SQL脚本的功能。 - `spool`：持续提到的内容，用于捕获输出到文件。 4. **环境变量设置**： - `spool`命令除了用于输出，还支持显示和设置环境变量，以便调整输出格式和行为。通过韩顺平老师的Oracle实用教程，学习者可以掌握Oracle的基本使用技巧、用户权限管理以及sql*plus工具的高效操作，这对于进一步深入理解Oracle数据库管理和实际项目开发具有重要作用。

Oracle 笔记

问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？

规定：多表查询的条件是至少不能少于表的个数-1 才能排除笛卡尔集

（如果有 N 张表联合查询，必须得有 N-1 个条件，才能避免笛卡尔集合）

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

问题：显示部门号为 10 的部门名、员工名和工资？

SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.deptno = 10;

问题：显示各个员工的姓名，工资及工资的级别？

先看 salgrade 的表结构和记录

SQL>select * from salgrade;

GRADE LOSAL HISAL

------------- ------------- ------------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

扩展要求：

问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？

SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno;

（注意：如果用 group by，一定要把 e.deptno 放到查询列里面）

 自连接

自连接是指在同一张表的连接查询

问题：显示某个员工的上级领导的姓名？

比如显示员工‘FORD’的上级

SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD';

子查询

 什么是子查询

子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询。

 单行子查询

单行子查询是指只返回一行数据的子查询语句

请思考：显示与 SMITH 同部门的所有员工？

思路：

1 查询出 SMITH 的部门号

select deptno from emp WHERE ename = 'SMITH';

2 显示

SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');

数据库在执行 sql 是从左到右扫描的，如果有括号的话，括号里面的先被优先执行。

 多行子查询

多行子查询指返回多行数据的子查询

请思考：如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号

SELECT DISTINCT job FROM emp WHERE deptno = 10;

SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10);

（注意：不能用 job=..，因为等号=是一对一的）

 在多行子查询中使用 all 操作符

Oracle 笔记

问题：如何显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

执行效率上，函数高得多

 在多行子查询中使用 any 操作符

问题：如何显示工资比部门 30 的任意一个员工的工资高的员工姓名、工资和部门号？

SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

扩展要求：

大家想想还有没有别的查询方法。

SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);

 多列子查询

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指

查询返回多个列数据的子查询语句。

请思考如何查询与 SMITH 的部门和岗位完全相同的所有雇员。

SELECT deptno, job FROM emp WHERE ename = 'SMITH';

SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE 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 来分

Oracle 笔记

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';

剩余58页未读，继续阅读

ruanshiming1220

粉丝: 0
资源: 2

韩顺平Oracle实用教程：安装与基础命令详解

韩顺平.oracle实用教程(免费下载).

韩顺平.linux视频教程和PPT

韩顺平.oracle实用教程

韩顺平.oracle9i.表查询.笔记

韩顺平.linux视频教程迅雷下载地址

韩顺平.linux视频教程迅雷下载地址（全）

韩顺平.linux视频教程

传智播客PHP培训.韩顺平.PHP视频教程.Ajax技术.PPT.zip

韩顺平.linux视频教程学习小结

韩顺平.linux视频教程学习小结.v1.0.docx

最新资源