oracle数据库(二)

最新推荐文章于 2024-01-11 02:08:09 发布

原创最新推荐文章于 2024-01-11 02:08:09 发布 · 493 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

JavaWeb 同时被 2 个专栏收录

20 篇文章

订阅专栏

oracle

5 篇文章

订阅专栏

一、伪列

rowid：当前数据在数据库中的物理位置。
特点：不存在任何表中，能查出来。

代码案例：
- select employee_id,first_name,salary, rowid from employees
rownum：对满足条件的查询结果进行编号，从 1 开始。
案例1：查询员工表中员工信息，要求带有编号，从1开始。
- select employee_id,first_name,salary, rownum from employees
案例2：查询薪资大于10000的员工信息：工号、名字、薪资、序号(rownum)
- select employee_id,first_name,salary, rownum from employees
  where salary > 10000;
案例3：查询员工表中，前10个员工的信息(工号，姓名，薪资，序号)
- select employee_id,first_name,salary, rownum from employees
  where rownum <=10;
案例4：查询员工表中，6~10编号的员工信息。
- select employee_id,first_name,salary, rownum from employees
  where ronum>=6 and rownum <=10; // 没有数据
注意：rownum只能进行 < <= =1 >=1 <=1的运算，不能进行 > >= = 运算
补充：表别名
SQL：select *,employee_id,rownum from employees； —>错误，不明确的 *的含义

解决方法1：使用表名.*
- select employees.*,employee_id,rownum from employees
解决方法2：使用表别名.*
- select es.*,employee_id,rownum from employees es
总结：表别名语法
- select … from 表名别名
- 注意：中间没有 as 关键字

二、子查询

概念：一个查询语句执行过程中，用到另一个查询语句的执行结果，即一个select语句中

嵌套另一一个select语句，外面的select称为主查询，嵌套的称为子查询。
子查询结果是单个值(一行一列)

特点：将子查询的结果作为主查询的条件判断。
案例1：查询具有最高薪资的员工信息(工号、姓、工资)

思路：
- 查询员工的最高薪资：maxSalary
  - select max(salary) from employees —>maxSalary
- 查询salary = maxSalary 的员工信息
  - select employee_id,last_name,salary from employees
    where salary = maxSalary
- 合成SQL
  - select employee_id,last_name,salary from employees
    where salary = (select max(salary) from employees)
案例2：查询工资大于所有员工平均工资的员工信息。

思路：
- 查询平均工资：avgSalary
  - select avg(salary) from employees;
- 查询 salary > avgSalary 的员工
  - select employee_id,last_name,salary
    
    from employees where salary > avgSalary
- 合成Sql
  - select employee_id,last_name,salary from employees
    where salary > (select avg(salary) from employees)
子查询的结果是多个值(多行一列)
案例1：查询和姓 King的员工同部门的员工信息。

思路：
- 查询姓King的员工所在部门：det_id
  - select department_id from employees
    
    where last_name = ‘King’
- 查询部门为 det_id 的员工信息；
  - select employee_id,last_name,salary from employees
    where department_id in det_id
- 合成sql：
  - select employee_id,last_name,salary from employees
    where department_id in
    (select department_id from employees
    
    where last_name = ‘King’)
子查询的结果是多行多列

特点：直接将子查询当成表进行再次查询。
案例：查询工资最高的前5名员工信息。

错误的写法：
- select employees.*,rownum from employees
  
  where rownum<=5 order by salary;
思路分析：
- 先按照工资降序排列：tab1
  - select * from employees order by salary desc;
- 在tab1中查前5名员工：
  - select * from tab1 where rownum <= 5;
- 合成SQL：
  - select * from (select * from employees order by salary desc)
    where rownum<=5;
可以给表起别名：
- select * from (select * from employees order by salary desc)tab1
  where rownum<=5;
分页查询
关键点：
- 分页时，有排序，排序必须先行，构成虚表后进行2次查询
- 查询符合条件的前n行数据，并给rownum起别名 rn
- 查 rn >= m 的数据
案例：查询最高工资的第6~10名员工。

分析：
- 对所有的员工按照工资进行降序排列：tab1
  - select * from employees order by salary desc; --tab1
- 查询符合条件的前 10行数数据，同时对 rownum起别名rn : tab2
  - select tab1.*,rownum rn from tab1
    
    where rownum<=10 --tab2
- 在 tab2中查 rn>=6的数据
  - select * from tab2 where tab2.rn>=6
- 合成SQL
  - select * from
    (select tab1.*,rownum rn from
    (select * from
    
    (select * from employees order by salary desc)
    where rownum<=10)tab1 where rownum<=10)tab2
    where tab2.rn>=6

三、表连接

概念：将多张表的数据合并成一张表，然后进行查询处理。

注意：表连接后，有冲突的字段时，需要表名.字段名或是表别名.字段名加以区分

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uNrUTaph-1589016606126)(E:\02_第二阶段\备课\oracle\1574235536927.png)]$
内连接【重点】
**语法： select … from 表1 [inner] join 表2 on 表1.字段名 = 表2.字段名 **

where … group by … having … order by …

特点：
- 查询结果：左表和右表基于连接条件，都符合/匹配的上的数据
- 必须有连接条件
- 左表和右表没有顺序要求
案例1：查询工号、姓、工资、员工所在部门的编号、所在部门名称。

思路：
- 表连接员工表和部门表为一张表，确定连接依据：员工表.部门的id = 部门表.部门的id
  - employees e join departments d on e.department_id = d.department_id
- 对连接后的表进行查询即可：
  - select e.employee_id,e.last_name,e.salary,e.department_id,
    
    d.department_name
    from
    employees e join departments d on e.department_id = d.department_id
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fLrvjxmt-1589016606127)(E:\02_第二阶段\备课\oracle\1574239909245.png)]$
外连接

分类：左外连接、右外连接、全外连接
左外连：【重点】
- 语法：select … from 表1 left [outer] join 表2 on 连接规则
- 特点：
  - 以左表为主，左表和右表匹配上的数据+左表中没有匹配上的数据
  - 左表中没有匹配上的数据在显示时，右表字段补空
- 案例：查询工号、姓、工资、员工所在部门的编号、所在部门名称
```
select e.employee_id,e.last_name,e.salary,e.department_id,d.department_name 
from 
employees e left join departments d on e.department_id = d.department_id
```
- 思路结果图：
右外连：
- 语法：select … from 表1 right [outer] join 表2 on 连接规则
- 特点：
  - 以右表为主，左表和右表匹配上的数据+右表中没有匹配上的数据
  - 右表中没有匹配上的数据在显示时，左表字段补空
- 案例：查询工号、姓、工资、员工所在部门的编号、所在部门名称
  - select e.employee_id,e.last_name,e.salary,e.department_id,
    
    d.department_name
    from
    employees e right join departments d
    
    on e.department_id = d.department_id
- 思路结果图：
全外连：
- 语法：select … from 表1 full [outer] join 表2 on 连接规则
- 特点：
  - 左表数据和右表匹配上的数据+左表中没有匹配上的数据+右表中没有匹配上的数据
  - 没有对应的匹配数据在显示时，补空
- 案例：查询工号、姓、工资、员工所在部门的编号、所在部门名称
  - select e.employee_id,e.last_name,e.salary,e.department_id,d.department_name
    from
    employees e full join departments d on e.department_id = d.department_id
- 思路结果图：
多表连接
语法： select from 表1 join 表2 on 连接条件1 join 表3 on 连接条件2

案例：查询员工的工号、姓、工资、所在部门名称、所在街道地址。

思路：
- 先进行表连接：员工表(employees)+部门表(departments)+位置表(locations)
employees e join departments d on e.department_id=d.department_id

join locations l on d.location_id = l.location_id
- 对表连接之后进行查询
自连接
自连接：是特殊的表连接，通常采用内连接方式。

特殊在：参与表连接的2张或是多张表都是同一张表(自己)

案例1：查询员工的工号、名、工资、上级领导的工号、上级领导的名、上级领导的工资。
- ```
select 
e1.employee_id,e1.first_name,e1.salary,
e2.employee_id 领导id,e2.first_name 领导名,e2.salary 
from 
employees e1 join employees e2 on e1.manager_id = e2.employee_id
```
案例2：查询工资相同的员工的工号、名、工资。(两个人都查询这些信息)
- ```
select 
e1.employee_id,e1.first_name,e1.salary,
e2.employee_id,e2.first_name,e2.salary 
from 
employees e1 join employees e2 on e1.salary = e2.salary  
where e1.employee_id < e2.employee_id
```
分析思路结果图：

四、SQL的分类

SQL：structure querey language，结构化的查询语言，用来操作管理所有数据库数据的命令。
SQL的分类：

DQL: Data Query Language，数据查询语言(select)

DML: Data Manipulation Language，数据从操作语言(insert/delete/update)

DDL: Data Definition Language，数据定义语言 (create/alter/drop)

DCL: Data Controller Language，数据控制语言(grant/revoke)–>和DBA数据库管理员相关

TCL: Transaction Controller Language，事务控制语言(commit/rollback)