Oracle学习笔记-----Oracle数据库了解+常用命令学习（函数+分组统计+多表查询+子查询+分页查询）

快来打我* 2023-03-03 13:26 25阅读 0赞

### Oracle数据库 ###

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说**Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案。**

Oracle与MySQL不同的是，MySQL是以数据库为使用单位，所有的表都是基于数据库创建的，而oracle是以用户为使用单位，并且用户角色可分为三种，如下

<table> 
 <thead> 
  <tr> 
   <th>角色</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>connect</td> 
   <td>数据库最终使用的角色，可以使用Oracle最基本的一些功能</td> 
  </tr> 
  <tr> 
   <td>resource</td> 
   <td>数据库开发角色，一般是普通程序员</td> 
  </tr> 
  <tr> 
   <td>dba</td> 
   <td>一般给专业的数据库管理员使用，可使用数据库的全部权限</td> 
  </tr> 
 </tbody> 
</table>

### Oracle涉及的专业术语 ###

**数据库**

Oracle 数据库是数据的物理存储。 这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库

**实例**

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。 一个数据库可以有 n 个实例。

**用户**

用户是在实例下建立的。不同实例可以建相同名字的用户。

**表空间**

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件） 的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。  
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

**数据文件**

数据文件是数据库的物理存储单位。 数据库的数据是存储在表空间中的， 真正是在某一个或者多个数据文件中。 而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

**Oracle体系结构**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70]

### Oracle常用命令 ###

**1.创建表空间**

create tablespace gzgs
    datafile 'D:\gzgs.dbf'
    size 10m
    autoextend on
    next 1m
    --------------------------------------------------------------------------------
    gzgs 为表空间名称
    datafile 指定表空间对应的数据文件
    size 后定义的是表空间的初始大小
    autoextend on 自动增长 ，当表空间存储都占满时，自动增长
    next 后指定的是一次自动增长的大小。

**2.创建用户**

create user sdl
    identified by sdl
    default tablespace gzgs
    --------------------------------------------------------------------------------
    identified by 后边是用户的密码
    default tablespace 后边是表空间名称
    oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户
    下的。

**3.赋予用户权限（分配角色）**

grant dba to sdl
    grant resource to sdl
    grant connect to sdl
    --------------------------------------------------------------------------------
    新创建的用户必须赋予用户角色，否则无法登陆，显示如下

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 1]

**4.建表**

--创建表
    create table person(
      pid number(10),
      pname varchar2(10),
      gender number(1) default 1,
      brithday date
    );

**5.修改表**

--修改表
    --修改表名
    alter table person rename to people; 
    
    --添加列
    alter table people add(phone number(1));
    
    --修改列的属性(phone的长度修改为11)
    alter table people modify(phone number(11));
    
    --修改表的某一列的名称
    alter table people rename column gender  to sex ;

**6.删除表**

drop table prople

**7.向表中插入数据**

--向people表插入一条记录
    insert into people(pid,pname,sex) values(1,'孙',1);
    insert into people(pid,pname,sex) values(2,'陈',2);

**8.修改数据**

--修改表中的数据信息
    --全局更新（修改表中的全部数据）
    update people set pid=1,pname='哈哈' , sex=1;
    
    --局部更新
    update people set pid=2,pname='嘿嘿',sex=2 where pid=1;

**9.删除数据**

--删除表中的某条数据
    delete from people where pid=2;
    
    --删除表中的全部数据
    delete from people

**10.查询数据**

--查询全部的数据
    select * from people
    
    --查询指定数据
    select * from people where pid=1

##### 序列 #####

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。

**1.创建序列**

create sequence seq;

**2.查看数列**

--查询当前序列
    select seq.currval from dual;
    
    --查询下一个序列
    select seq.nextval from dual

**3.使用序列插入记录**

insert into people(pid,pname,sex)values(seq.currval,'孙',1);

### 函数 ###

--给Scott用户解锁
    alter user scott account unlock;
    --激活密码
    alter user scott identified by tiger;

#### 单行函数 ####

**1. 字符函数**

--将字母转换为大写
    select upper('nana') from dual;
    
    --将字母转换为小写
    select lower('HHFDWw') from dual;

**2.数字函数**

--四舍五入(数字1表示保留的小数位数）
    select round(15.33,1) from dual;
    
    --截取数字(数字1表示保留的小数位数）
    select trunc(12.55,1) from dual;
    
    --取余数
    select mod(10,3) from dual;

**日期函数**

--查询雇员的进入公司的周数
    select ename,round(sysdate-hiredate) from emp;
    
    --查询雇员的进入公司的周数
    select ename,round((sysdate-hiredate)/7) from emp;
    
    --查询雇员进入公司的月数
    select ename,round(months_between(sysdate,hiredate))from emp;

**转换函数**

--将日期转换为字符串
    
    select empno,ename,to_char(hiredate,'yyyy-mm-dd') 日期 from emp;
    
    --将字符串转换为日期
    select to_date('2020-07-31','yyyy-mm-dd') from dual;

![在这里插入图片描述][20200731214408966.png]

**通用函数**

**1.nvl处理空值**

--查询所有的雇员的年薪,此时如果奖金为null，年薪查询结果就会为null，因为null和任何值计算结果都为null
    select ename,sal*12+comm from emp;
    
    --用空值处理nvl
    select ename,sal*12+nvl(comm,0) 年薪 from emp;

**2.decode**

--给查询结果赋予中文名称（使用decode）
    select ename,
      decode(job ,
          'CLERK','业务员',
          'SALESMAN','销售员',
          'MANAGER','经理',
          'ANALYST','分析员',
          'PRESIDENT','总裁',
          '无') 职业 from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 2]

**3.case-when**

--使用case-when方式取中文名
    
    select ename,
           case
             when t.job='CLERK' then '业务员'
             when t.job='SALESMAN' then '销售员'
             when t.job='MANAGER' then '经理'
             when t.job='ANALIST' then '分析员'
             when t.job='PRESIDENT' then '总裁'
             else '无' end 职业
    from emp t;

#### 多行函数（聚合函数） ####

--查询记录总条数
    select count(*) from emp;
    
    --查询最低员工工资
    select min(sal)最低工资 from emp;
    
    --查询最高员工工资
    select max(sal)from emp;
    
    --查询平均员工工资
    select avg(sal) from emp;
    
    --查询20号部门员工总工资
    select sum(sal) from emp t where t.deptno=20;

#### 分组统计 ####

分组统计需要使用 GROUP BY 来分组  
语法： select\* |列名 from表名 \{where查询条件\} \{group by 分组字段\} order by 列名 1 aasc|desc，列名 2…asc|desc

**使用示例**

--查询每个部门的人数
    select emp.deptno,count(*) 人数 from emp group by emp.deptno;
    --查询每个部门的平均工资
    select emp.deptno,avg(sal) from emp group by emp.deptno;
    
    --查询部门民称和部门人数
    select dept.dname,count(emp.deptno) 人数
    from dept,emp
    where dept.deptno=emp.deptno
    group by dept.dname,emp.deptno
    
    --查询部门人数大于5的部门
    select dept.deptno,dept.dname from dept,emp where dept.deptno=emp.deptno group by dept.deptno,dept.dname having count(emp.ename)>5;
    
    --查询部门平均工资大于2000的部门
    select dept.deptno,dept.dname,avg(emp.sal) 平均工资 from dept,emp where dept.deptno=emp.deptno group by dept.deptno,dept.dname having avg(emp.sal)>2000;
    
    --查询每个部门的部门编号，部门名称（中文显示）,平均工资且按照平均工资降序排序
    select d.deptno 部门编号, 
    case
      when d.dname='ACCOUNTING' then '财务部'
      when d.dname='RESEARCH' then '调研部'
      when d.dname='SALES' then '销售部'
      else '无' end 部门名称
    ,avg(e.sal) 平均工资
    from dept d,emp e 
    where d.deptno=e.deptno
    group by d.deptno,d.dname
    order by avg(e.sal) desc

#### 多表查询 ####

**使用一张以上的表做查询就是多表查询**

语法： select \{distinct\} \*|列名… from 表名 别名，表名 1 别名\{where 限制条件 order by 排序字段 asc|desc…\}

**多表连接基本查询**

--查询部门表 和员工表
    select distinct * from emp,dept;
    
    --查询所有员工信息一级每个员工对应的部门信息
    select * from emp,dept where emp.deptno=dept.deptno;
    
    --查询出雇员的编号，姓名，部门的编号和名称，地址
    select emp.empno,emp.ename,dept.deptno,dept.dname,dept.loc from dept,emp;
    
    --查询每个员工的编号和姓名以及对应领导的名字和编号
    select e1.empno,e1.ename,e2. empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
    
    --查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
    select e1.empno  ,e1.ename  ,
    case
      when s1.grade=1 then '一'
      when s1.grade=2 then '二'
      when s1.grade=3 then '三'
      when s1.grade=4 then '四'
      when s1.grade=5 then '五'
      else '无' end 工资等级,
    d.dname  ,e2.ename  ,
    case
      when s2.grade=1 then '一'
      when s2.grade=2 then '二'
      when s2.grade=3 then '三'
      when s2.grade=4 then '四'
      when s2.grade=5 then '五'
      else '无' end 领导工资等级
    from emp e1,dept d,salgrade s1,emp e2, salgrade s2
    where e1.mgr=e2.empno and e1.deptno=d.deptno
    and e1.sal between s1.losal and s1.hisal
    and e2.sal between s2.losal and s2.hisal

**外连接（左右连接）**

1.  右连接  
    当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的
    
        --查询员工信息以及部门信息
         select emp.empno,emp.ename,dept.deptno,dept.dname from emp,dept where emp.deptno(+)=dept.deptno

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 3]

> 使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右边表的关联条件字段上就是右连接。

1.  使用我们以前的做法发现 KING 的上级领导没有被展示，我们需要使用左右连接把他查询出来
    
        --查询所有员工信息以及对应领导信息
         select  e1.empno,e1.ename,e2.empno,e2.ename
         from emp e1,emp e2
         where e1.mgr=e2.empno(+)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 4]

#### 子查询 ####

**子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询，Sql的任何位置都可以加入子查询。**

--查询比 7654 号员工工资高的雇员
    select * from emp e where e.sal >(select emp.sal from emp where emp.empno=7654)
    
    --查询出比雇员 7654 的工资高，同时从事和 7788 的工作一样的员工
    select * from emp e where e.sal >(select e1.sal from emp e1 where e1.empno=7654) and e.job=(select job from emp e2 where e2.empno=7788)
    
    --要求查询每个部门的最低工资和最低工资的雇员和部门名称
    select d1.dname 部门名称, a.最低工资,e1.ename 员工姓名
    from emp e1,(select deptno,min(sal) 最低工资 from emp group by deptno) a,dept d1
    where e1.deptno=d1.deptno and e1.sal=a.最低工资

#### Rownum与分页查询 ####

Rownum:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 5]**应用**

--查询emp表的前五列
    select rownum,emp.* from emp where rownum<6;
    
    --查询6-10列
    --第一种写法
    select *
    from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm >5
    
    --第二种写法
    select *
    from (select rownum r ,emp.* from emp) b
    where b.r >5 and b.r <11

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70]: /images/20230208/3370055b068d4f34a445c8801f954d0e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 1]: /images/20230208/146c4d5418e54b6181b3eed574bc67b2.png
[20200731214408966.png]: /images/20230208/08be49da066a4da1acbefdd3b162a0ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 2]: /images/20230208/bbc415c9a5854b8aa5410168425d49a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 3]: /images/20230208/9cf8e26acc9f40849e48a4ed28dc09d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 4]: /images/20230208/c02baf1ae3d84d119c6fc38db1142903.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY4MDk2Mg_size_16_color_FFFFFF_t_70 5]: /images/20230208/e756a738c18f4de893f0beb9c7c72f56.png