Oracle笔记（二）

一、伪例（rownum rowid）

1.概念

表中不存在的，通过selcet*无法查询的列

select *,rownum,rowid from error(错误);

-- 解决 :

select employees., rownum,rowid from employees; --- 用表名修饰

select e.,rownum,rowid from employees e ; --- 用表的别名修饰

 2.rowid

在数据表里唯一标识一条记录，对记录所在空间的物理地址运算得到

3.rownum

数据库服务会为每次出现在查询结果里的，满足要求的记录编号，从1开始

-- 请打印表里的前 5 行数据

select * from employees where rownum<= 5 ;

-- 请打印表里的第 6 到第 10 行数据

select * from employees where rownum between 6 and 10 ;

-- 注意 : rownum 使用时必须从 1 开始用 , >=1 =1 < <= between 1 and …

 二、子查询

1.概念

在一条sq命令里嵌套了另一个查询语句，嵌套的查询语句称为“子查询”

-- 请查询公司里工资最高的员工信息

-- 思路：

--1) 查询公司里的最高工资 select max(salary) from employees; 【 result 】

--2) 根据最高工资查询员工信息 select * from employees where salary = result;

-- 合并：

select * from employees where salary=( select max(salary) from employees);

 2.子查询的结果是单行单列（一个值）--通常用在条件判断力【重点】

-- 请查询公司里工资高与平均工资的员工信息

-- 思路：

--1) 查询公司里的平均工资 select avg(salary) from employees; 【 result 】

--2) 根据平均工资查询员工信息 select * from employees where salary> result ;

-- 合并：

select * from employees where salary>( select avg(salary) from employees);

 3.子查询结果是多行一列（多个值）--（了解）

-- 请查询与 ‘King’ 在同一部门工作的员工信息

-- 思路：

--1) 查询 ‘King’ 所在部门编号

-- select department_id from employees where last_name='King'; 【 result(80,90) 】

--2) 查询 80 和 90 部门的员工信息 select * from employees where department_id in （ 80,90 ）；

-- 合并：

select * from employees

where department_id in ( select department_id from employees where last_name= 'King' );

 4.子查询的结果是多行多列（以表的形式体现-虚拟表）

针对虚拟表做二次查询（将虚拟表放在fromz子句）

-- 请查询公司里工资最高的五位员工信息

-- 分析： 1 ）排序 order by salary desc 2 ）按 rownum 提取前五个 rownum<=5

-- 总结：

-- select * from employees where rownum<=5 order by salary desc; --error

-- 错误原因：对表里的前五行数据按工资排序

-- 思路：

-- 1 ）先按工资对表里的所有数据排序 select * from employees order by salary desc; 【

tab1 】

-- 2 ）从 tab1 里查询前五行数据 select * from tab1 where rownum<=5;

-- 合并

select * from ( select * from employees order by salary desc ) where rownum<= 5 ;

 5.分页查询--对瞒足要求的结果数据分段显示

1）不含数据排序功能的数据分页

-- 请打印 first_name 是由四个字母构成的第 3 到第 5 名员工信息

-- 思路：

--1 ）查询表里的符合要求的前 5 行记录，并将 rownum 字段显示在结果中（注意：虚拟表有多少字段由 select 语句决定）

-- select e.*,rownum rn from employees where first_name like '____'; 【

tab1 】

--2 ）根据 rn 字段提取第 3 到第 5 行数据 select * from tab1 where rownum between 3 and 5;

-- 合并

select *

from ( select e.*,rownum rn from employees where first_name like '____' ) tab1

where rn between 3 and 5 ;

 命令编写步骤总结

--请查询瞒足***条件的第m到第n行数据

--1.根据***条件查询表里的前n行数据，同时将rownum字段添加打查询结果里（起别名m）

--2.根据rm字段提取第m到n行数据

 2）带排序g功能的数据分页

-- 请查询公司里工资最高的第六到第十个员工信息

-- 土办法：

--1 ）对表里数据按照工资降序排列 select * from employees order by desc; 【表 t1 】

--2 ）查询前十条记录，对这十条记录再次按照工资升序排列

-- select * from t1 where rownum<=10 order by salary; 【表 t2 】

--3 ）提取 t2 表里的前五个 select * from t2 where rownum<=5;

-- 合并

select * from ( select * from ( select * from employees order by salary desc ) t1

where rownum<= 10 order by salary ) t2

where rownum<= 5 ;

-- 请查询公司里工资最高的第六到第十个员工信息

-- 正规方式：

--1 ）对表里数据按照工资降序排列 select * from employees order by desc; 【表 t1 】

--2 ）查询前十条记录，并将 rownum 字段（别名 rn ）添加到查询结果里

-- select t1.*,rownum rn from t1 where rownum<=10 ; 【表 t2 】

--3 ）根据 rn 字段提取 t2 表里第 6 到第 10 行数据 select * from t2 where rn between 6 and 10 ；

-- 合并

select *

from ( select t1.*,rownum rn

from ( select * from employees order by desc ) t1

where rownum<= 10 ) t2

where rn between 6 and 10 ；

 命令编写步骤总结

--请查询瞒足***条件的第n行到第m行数据

--1.先对表里数据安要求进行筛选以及排序

--2.查询t1表里面的前n行字段，同时将rownum字段添加到查询结果里（起别名rm）

--3.根据rm字段提取第m到第n行数据

三、表连接

1.概念

1）当结果数据来自于多张表时，需要通过一定的条件

-- 请打印员工的编号，姓名，工资，部门编号，以及所在部门名称

select e .employee_id ,e .last_name ,e .salary ,e .department_id ,d .department_name

from employees e , departments d

where e .department_id = d .department_id ;
 

2 ）分类：内连接 外链接 自连接 多表连接（语法）

2. 内连接【重点】

1 ）规则：使用关键字 inner join 连接， inner 可以省略，使用 on 指定连接条件，其他判定条件沿用 where

2 ）案例：

-- 请打印 60 部门员工信息，以及所在部门信息

select e.*,d.*

from employees e inner join departments d

on e .department_id = d .department_id

where e .department_id = 60 ;

 3）注意：内连接只会显示符合连接条件的记录，对于连接条件为null的记录直接舍弃，不在结果中显示

3. 外链接【重点】

1 ）作用：可以处理连接条件为 null 的记录

2 ）分类

① 左外链接【重点】：在表连接的过程中，以 “ 左表 ” 为主（左表记录全部出现），右表辅助（没有对应的

记录补齐空行），使用关键字 left outer join 定义（ outer 可以省略）

② 右外连接【了解】：表连接时以 “ 右表 ” 为主，

“ 左表 ” 辅助，关键字 -- right outer join

③ 全外连接【了解】：表连接时两张表的记录全部出现，谁缺谁补，关键字 full outer join

3 ）案例

-- 请打印所有员工信息，以及他们所在部门的信息

-- 左外连接实现 【重点】

select e.*,d.* from employees e left join departments d on e .department_id =d .department_id ;

-- 右外连接实现

select e.*,d.* from departments d right join employees e on e .department_id =d .department_id ;

-- 全外连接实现

select e.*,d.* from employees e full join departments d on e .department_id =d .department_id ;

 4.自连接

1）特点：通过为一张表定义，模拟表连接

2）案列1

-- 请打印员工的姓名，以及他的领导的姓名

select e .last_name ,m .last_name

from employees e left join employees m

on e .manager_id = m .employee_id ;

5. 多表连接（语法）【重点】

-- 请打印员工详细信息，所在部门信息，以及所在的城市（数据来源表： employees departments locations ）

select e.* , d.* , lo.*

from employees e left join departments s

on e .department_id = d .department_id

left join locations lo

on d .location_id = lo .location_id

where ...... ;

四. Sql命令的分类

1. 概念区分

1 ） Sql ( structure query language) : 结构化的查询语言 , 用来操作管理所有数据库数据的命令

2 ） PLSQL : oracle 公司对 sql 命令的增强和改进

3 ） SQLPlus : oracle 提供的工具 – 操作工具命令

2. Sql 命令分类

1 ） DQL ( data queyr language) --- 数据查询语言 select

2 ） DML( data manipulation language ) --- 数据操纵语言 insert update delete

3 ） DDL ( data defifinition language ) --- 数据定义语言 create drop truncate alter

4 ） DCL( data control language ) --- 数据控制语言 grant revoke

5 ） TCL ( transaction control language) --- 事务控制语言 commit rollback