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

需积分: 9 62 浏览量更新于2024-07-29 收藏 1.07MB PDF 举报

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脚本。 - `edit`命令用于打开和编辑SQL脚本文件。 - `spool`用于将查询结果或屏幕输出保存到指定文件，`spool off`则停止记录。 3. **文件操作和交互式命令**: - `&`符号在SQL查询中代表可变参数，如`select * from emp where job = '&job';`，执行时需要用户输入实际值。 - `edit`命令再次强调了脚本文件的编辑功能。 - `spool`除了记录屏幕输出，还可以用于自动化报告和日志管理。 4. **环境变量设置**: - SQL*Plus允许设置和控制输出，这在处理大量数据或调试脚本时非常有用。这些笔记为学习者提供了系统性的Oracle基础入门指南，涵盖了安装、权限管理、SQL*Plus交互以及常用命令，是理解和掌握Oracle数据库操作的重要参考资料。对于希望通过韩顺平老师的课程深入学习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页未读，继续阅读

jiansongchen

粉丝: 2

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

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

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

韩顺平最新Oracle笔记及PPT

韩顺平老师oracle教程听课笔记

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

韩顺平Oracle教程笔记：数据库安装与基本使用

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

韩顺平Oracle教程笔记详解：安装与基础操作指南

韩顺平Oracle教程笔记：超级用户与基本命令解析

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

最新资源