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

需积分: 10 21 浏览量更新于2024-07-25 收藏 773KB PDF 举报

韩顺平老师的Oracle视频教程笔记主要围绕Oracle数据库系统及其管理展开，强调了系统中两个关键用户——sys和system的角色及权限差异。在学习过程中，学员们重点掌握了以下几个要点： 1. **Oracle认证与安装**： - Oracle安装过程会自动生成两个核心用户：sys和system。 - sys用户是超级用户，拥有最高权限，包括sysdba角色，允许创建数据库，初始密码通常为change_on_install。 - system用户是管理操作员，权限较大，具备sysoper角色但不具备创建数据库权限，初始密码为manager。 - 在日常数据库维护中，system用户通常是首选登录用户。 2. **SQL*Plus基本命令**： - `conn`和`disconnect`命令用于连接和断开数据库，特权用户（如sysdba/sysoper）需要在连接时明确指定。 - `password`命令用于修改用户密码，包括自身和他人的密码。 - `showuser`显示当前登录用户的名称。 - `exit`不仅断开连接，还会退出SQL*Plus。 - `start`和`@`命令用于执行SQL脚本，可以直接引用文件路径。 - `edit`命令用于打开或编辑SQL脚本文件。 - `spool`命令用于将查询结果或屏幕输出保存到指定文件，`spooloff`则是关闭输出。 3. **交互式命令**： - `&`符号用于在SQL语句中引入用户交互的变量。 - `edit`除了编辑文件外，还有其他用途，具体操作取决于上下文。 - `spool`继续用于屏幕内容的输出控制。 4. **文件操作与环境变量**： - 学习了如何通过SQL*Plus执行文件操作，包括启动、编辑和查看SQL脚本，并掌握如何使用`spool`功能来记录操作和查询结果。通过这些笔记，学生能够熟练地操作Oracle数据库，进行基础的系统管理和SQL查询，以及理解特权用户的作用和安全注意事项。在实际工作中，掌握这些知识对于数据库管理员和开发人员来说至关重要，有助于提高工作效率和数据库安全性。

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页未读，继续阅读

nizhengjia888

粉丝: 8
资源: 51

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

oracle9i甲骨文公司教程和韩顺平老师oracle视频教程听课笔记

韩顺平老师oracle视频教程听课笔记.pdf

韩顺平老师oracle 听课笔记.pdf

韩顺平老师Oracle教程笔记：安装与基础命令详解

韩顺平老师java视频课堂笔记

oracle视频教程听课笔记.docx

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

韩顺平oracle课堂笔记

Oracle教程：韩顺平老师的数据库管理笔记

Oracle教程笔记：韩顺平老师的讲解

最新资源