Oracle入门级基础-表空间、用户、单行函数、聚合函数、Rownum与分页

最新推荐文章于 2023-08-13 20:44:07 发布

Chandler07

最新推荐文章于 2023-08-13 20:44:07 发布

阅读量210

点赞数 1

分类专栏： Oracle 文章标签： Oracle

本文链接：https://blog.csdn.net/le_exclusive/article/details/103330316

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Oracle体系结构

在这里插入图片描述

数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日

志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统

只有一个库。可以看作是 Oracle 就只有一个大数据库

实例

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

用户

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

表空间

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

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

数据文件

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

注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念， oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

创建表空间

表空间？ ORACLE 数据库的逻辑单元。数据库—表空间一个表空间可以与多个数据

文件（物理结构）关联

一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

  create tablespace dataspace    --创建表空间指定表空间名称   
  datafile 'c:\mydata.dbf'       --创建表空间指定路径和文件名   
  size 10M                       --指定表空间初始化大小  
  autoextend on                  --自动扩展   
  next 10M                       --超过初始化大小   每次增长10M   。

用户

创建用户

create user le identified by root default   tablespace dataspace;

identified by 后边是用户的密码

default tablespace 后边是表空间名称

oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

用户授权

   grant dba to le;

Oracle 中已存在三个重要的角色： connect 角色， resource 角色， dba 角色。

CONNECT 角色： --是授予最终用户的典型权利，最基本的

ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图

RESOURCE 角色： --是授予开发人员的

CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型

DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

数据类型

No	数据类型	描述
1	Varchar， varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是 n
		NUMBER(m,n):表示一个小数，总长度是 m，小数是 n，整数是 m-n
3	DATE	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存 4G
5	BLOB	大对象，表示二进制数据，可存 4G

表的管理

建表

Create table 表名（

字段 1 数据类型 [default 默认值],

字段 2 数据类型 [default 默认值],

…

字段 n 数据类型 [default 默认值]

）;

   create table person    ( pid number(10) primary key,--主键id  oracle中没有自增 （会使用序列代替自增） 
                           myname varchar2(30),--姓名           
                           mysex char(1),   --性别 0 1           
                           myage number(3)     --年龄  
                          );

删除表

语法： DROP TABLE 表名

表的修改

在 sql 中使用 alter 可以修改表

Ø 添加语法： ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]…)

Ø 修改语法： ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]…)

Ø 修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

   --增加字段   
   alter table person add myaddress varchar2(30);  
   --修改字段   
   alter table person modify myaddress varchar2(200);  
   --删除   
   alter table person drop column myaddress ;

数据库表数据的操作

--05数据的增删改   
--增   
insert into person values(1,'老王',1,30);   
commit;   
rollback;   
--改   
update person set myname = '隔壁老王' where pid = 1;  
commit;   
--删（谨慎使用）  
delete from person where pid = 1;  
--查  
select * from person;

序列

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

 --06序列 一般情况一个表使用一个序列  
 create sequence SEQ_PERSON   --序列名称   
 minvalue 1                   --最小值   
 maxvalue 999999999999999999999999999 --最大值  
 start with 1                         --开始值  
 increment by 1                       --每次加1   
 cache 20                             --缓存20           
 --使用序列    
 NEXTVAL --下一个值  
 Currval --当前序列的值   
 --dual伪表 主要为了拼接sql语句而产生的表       select 列 from 表;      
 select SEQ_PERSON.NEXTVAL  from dual;  
 select SEQ_PERSON.Currval  from dual;       
 --操作表使用序列     
 insert into person values(SEQ_PERSON.NEXTVAL,'老王2',1,30);   
 commit;

scott账号

   --07scott用户   
   --解锁账号    
   alter user scott account  unlock;   
   --修改密码（密码过期）       
   alter user scott identified by tiger ;

单行函数

--08单行函数
--字符函数 upper lower 
select upper(e.ename) from emp e;
select lower(e.ename) from emp e;

--数值函数 round trunc  mod 

select round(5555.6646,2),round(5545.6666,-2.6),round(5555.6666) from dual;

select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333)  from dual;

select mod(23,8),mod(24,8) from dual;


--日期函数 sysdate
select sysdate from dual;

--算出明天此刻
select sysdate+1 from dual;


--09查询出emp表中所有员工入职距离现在几月。
--(当前时间-入职时间)/30
select e.empno,e.ename,round((sysdate-e.hiredate)/30) from emp e ;--不够精准

select e.empno,e.ename,round(months_between(sysdate,e.hiredate)) from emp e;--精准查询


--10查询出emp表中所有员工入职距离现在几年。
select e.empno,e.ename,round(months_between(sysdate,e.hiredate)/12) from emp e;--精准查询


--11查询出emp表中所有员工入职距离现在几周。
select e.empno,e.ename,round((sysdate-e.hiredate)/7) from emp e;--精准查询


--12转换函数
--日期转字符串to_char
select 
to_char(sysdate,'d'),-- 每周第几天 
to_char(sysdate,'dd'),--  每月第几天 
to_char(sysdate,'ddd'),--  每年第几天 
to_char(sysdate,'ww'),--  每年第几周 
to_char(sysdate,'mm'),--  每年第几月 
to_char(sysdate,'q'),--  每年第几季 
to_char(sysdate,'yyyy'),-- 年 
to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')
from dual;
--字符串转日期 to_date
select to_date('2018/10/12 17:12:55','yyyy/mm/dd hh24:mi:ss') from dual;

--13通用函数
--算出emp表中所有员工的年薪
select e.sal*12+nvl(comm,0),e.empno,e.ename,e.sal,e.comm from emp e;

--14条件表达式
--给emp表中员工起中文名
条件取值方式一：
select 
e.empno,
e.ename,
case ename
when 'SMITH' then '老王' 
when 'ALLEN' then '隔壁老王' 
when 'WARD' then '隔壁小李' 
else '未知'
end
 from emp e;

条件取值方式二：
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
select 
e.empno,
e.ename,
decode(ename,'SMITH','老王','ALLEN','隔壁老王','未知') 
 from emp e;



--15判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
select 
e.empno,
e.ename,
case 
when sal > 3000 then '高收入' 
when sal > 1500 then '中等收入' 
else '低收入'
end
 from emp e;

多行函数（聚合函数）

--16多行函数
--查询总数量
select count(*) from emp;
--工资总和
select sum(sal) from emp;
--最大工资
select max(sal) from emp;
--最低工资
select min(sal) from emp;
--平均工资
select avg(sal) from emp;

分组统计

--分组查询
--17查询出每个部门的平均工资
思路：
1.从哪个表查询
2.查询条件是什么
3.查询哪些列

select e.deptno,avg(sal) from emp e group by e.deptno
 
--18查询出平均工资高于2000的部门信息
select e.deptno,avg(sal) from emp e group by e.deptno  having avg(sal) > 2000

 
--19查询出每个部门工资高于800的员工的平均工资,然后再查询出平均工资高于2000的部门
select e.deptno,avg(sal) from emp e  where e.sal > 800 group by e.deptno  having avg(sal) > 2000

sql执行顺序

--总结
select from where group by order by having 

from --从那个表查询数据
where --分组前过滤条件
group by --按照什么条件进行分组
having --在分组的基础上进行过滤
select --查询哪些列（查询的列要在groupby后或多行函数结果）
order by --在最终结果上进行排序

多表查询

--多表查询
--20查询员工表和部门表
select * from emp e,dept d;--产生笛卡尔积
--内连接添加过滤条件 来解决笛卡尔积问题
--隐式内连接
select * from emp e,dept d
where e.deptno = d.deptno;
--显式内连接
select * from emp e inner join dept d
on e.deptno = d.deptno;

--外连接
--左外连接
--右外连接


--21查询出所有部门，以及部门下的员工信息。
--以部门表作为主表（基准表）查询 关联员工表
select * from dept d left join emp e on d.deptno = e.deptno
 
--22查询所有员工信息，以及员工所属部门
select * from emp e  left join dept d on d.deptno = e.deptno

  
--23查询出员工姓名，员工领导姓名 
--通过别名的方式虚拟出两张表来进行查询

select 
 e1.ename 员工姓名,e2.ename 领导姓名
from 
emp e1,--员工表
emp e2 --领导表
where e1.mgr = e2.empno

--24查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select 
 e1.ename 员工姓名,d1.dname 员工部门名称 ,e2.ename 领导姓名,d2.dname 员工领导部门名称
from 
emp e1,--员工表
emp e2, --领导表
dept d1, --员工部门表
dept d2 --领导部门表
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno

--补充
oracle中独有的查询方式(+)
select * from dept d,emp e where d.deptno = e.deptno
--(+)方式来查询
select * from dept d,emp e where d.deptno = e.deptno(+)
--分析
全量表   部门表
非全量表 员工表  在非全量表条件上加(+)就可以查询出想要的数据

子查询

--25查询出工资和SCOTT一样的员工信息
查询出工资
select * from emp e where e.sal = (select e.sal from emp e where e.ename = 'SCOTT')
and e.ename <> 'SCOTT'
SCOTT一样的员工信息
select e.sal from emp e where e.ename = 'SCOTT'  --单行单列子查询


--26查询出工资和10号部门任意员工一样的员工信息
  查询出工资和
  select * from emp e where e.sal in (select e.sal from emp e where e.deptno =10 )
  10号部门任意员工一样的员工信息
  select e.sal from emp e where e.deptno =10   --多行单列子查询
--27查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称

查询出每个部门最低工资
select e.deptno,min(e.sal) from emp e group by e.deptno

和最低工资员工姓名
select e.sal,e.ename from 
emp e,--员工表
(select e.deptno,min(e.sal) minsal from emp e group by e.deptno )ee --每个部门最低工资
where e.deptno = ee.deptno
and e.sal = ee.minsal

和该员工所在部门名称
select e.sal,e.ename,d.dname from 
emp e,--员工表
(select e.deptno,min(e.sal) minsal from emp e group by e.deptno )ee, --每个部门最低工资
dept d
where e.deptno = ee.deptno
and e.sal = ee.minsal
and e.deptno = d.deptno

Rownum与分页查询

--28oracle中的分页 rownum关键字 伪列
rownum伪列：随着查询数据而产生序号

select rownum,e.* from emp e where rownum <= 3;--不带条件分页查询（第一页）


select rownum,ee.* from (                     --带条件分页查询（第一页）
select e.* from emp e order by sal desc 
)ee where rownum <= 3

 
--29emp表工资倒叙排列后，每页3条记录，查询第二页。
select * from (
select rownum rn,ee.* from (                     --带条件分页查询（第一页）
select e.* from emp e order by sal desc 
)ee 
)
where rn >= 4 and rn <= 6            --推荐使用此方法

select * from (
select rownum rn,ee.* from (                     --带条件分页查询（第一页）
select e.* from emp e order by sal desc 
)ee 
)
where rn between 4 and 6