01 Oracle_day2

Oracle笔记

一. 伪列 ( rownum rowid )

1. 概念

​ 表里不存在的,通过select * 无法查询到的列

select  *,rownum,rowid  from employees ;    -- 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. 概念

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

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

--思路：
--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. 子查询的结果是多行多列( 以表的形式体现 – 虚拟表 )

​ 针对虚拟表做二次查询 ( 将虚拟表放在from子句 )

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

-- 分析：  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;

​

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

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

--土办法：
--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； 

6. 关联子查询 【了解】

1）提示： 通过表的别名可以区分查询

三. 表连接

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;

3）案例2

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 definition language ) — 数据定义语言 create drop truncate alter

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

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

a manipulation language ) — 数据操纵语言 insert update delete

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

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

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