Oracle常用操作

只因为你温柔

已于 2022-12-12 18:24:11 修改

阅读量489

点赞数

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

于 2020-05-16 19:55:44 首次发布

本文链接：https://blog.csdn.net/zsx1314lovezyf/article/details/106164167

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Oracle常用操作

创建表空间/创建用户/给用户授权

--创建表空间
create tablespace 表名称 
datafile '数据文件' 
size 大小 
autoextend on 
next 大小; 

--创建表空间
create tablespace xiaoge --表空间名称
datafile 'd:\xiaoge.dbf' --数据文件
size 100m --表空间大小
autoextend on --可以自动扩展大小
next 10m; --每次扩展10兆

    
    
--删除表空间    
drop tablespace 表名称;    

--删除表空间
drop tablespace xiaoge;



--创建用户
create user 用户名  
identified by 密码 
default tablespace 用户名出生的表空间; 
    
--创建用户
create user xiaoge  --用户名
identified by xiaoge --密码
default tablespace xiaoge; --这个用户名出生的表空间

  
    
--给用户授权
--oracle数据库中常用角色
connect --连接角色, 基本角色
resource --开发者角色
dba --超级管理员角色

-- 给xiaoge用户授予dba角色
grant 角色 to 用户;
    
-- 给xiaoge用户授予dba角色
grant dba to xiaoge;

-- 切换到xiaoge用户下
-- 会话关掉, 登入, 选择你需要登入的用户就好了

数据类型/表的创建

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

--创建一个person表
create table 表名称(
       列名称 数据类型(大小),  
       列名称 数据类型(大小)
)

--创建一个person表
create table person(
       pid number(20),  
       pname varchar2(10)
)

修改表结构

--修改表结构
    
--添加一列
alter table 表名称 add 列名称 类型(大小);
    
--添加一列
alter table person add gender number(1);



--添加多列
alter table 表名称 add (列名称 类型(大小), 列名称 类型(大小));

--添加多列
alter table person add (gender number(1), birthday varchar2(10));



--修改列类型
alter table 表名称 modify 列名称 类型(大小);

--修改列类型
alter table person modify gender char(1);



--修改多列类型
alter table 表名称 modify (列名称 类型(大小), 列名称 类型(大小));

--修改多列类型
alter table person modify (gender char(1), birthday char(10));



--修改列名称
alter table 表名称 rename column 旧列名称 to 新列名称;

--修改列名称
alter table person rename column gender to sex;



--删除一列
alter table 表名称 drop column 列名称;

--删除一列
alter table person drop column sex;

数据的增删改查

--查询表中记录
select * from 表名称;

--查询表中记录
select * from person;



--添加一条记录
insert into 表名称 (列名称, 列名称) values (值, 值);
commit; --提交事务
    
--添加一条记录
insert into person (pid, pname) values (1, '小明');
commit; --提交事务
    
    
    
--修改一条记录
update 表名称 set 列名称 = 值 where 列名称 = 值;
commit; --提交事务
    
--修改一条记录
update person set pname = '小马' where pid = 1;
commit; --提交事务

    
    
    
----三个删除
--删除表中全部记录
delete from 表名称;    
    
--删除表中全部记录
delete from person;



--删除表结构
drop table 表名称; 
--删除表结构
drop table person; 



--先删除表, 再次创建表. 效果等同于删除表中全部记录.
--在数据量大的情况下, 尤其在表中带有索引的情况下, 该操作效率高.
--索引可以提高查询效率, 但是会影响增删改效率.
truncate table 表名称; 

--先删除表, 再次创建表. 效果等同于删除表中全部记录.
--在数据量大的情况下, 尤其在表中带有索引的情况下, 该操作效率高.
--索引可以提高查询效率, 但是会影响增删改效率.
truncate table person;

序列的使用

---序列不真的属于任何一张表, 但是可以逻辑和表做绑定.
---序列: 默认从1开始, 依次递增, 主要用来给主键赋值使用.
---dual: 虚表, 只是为了补全语法, 没有任何意义.
create sequence s_person; --创建序列s_person, 序列默认从1开始
-- 该表中最后一条的数据id+1, 就是该值(s_person.nextval: 取得序列的下一个内容)
select s_person.nextval from dual; 

--添加一条记录
insert into person (pid, pname) values(s_person.nextval, '小明');
commit --提交事务

scott用户, 密码tiger(默认的)

----scott用户, 密码tiger
--解锁scott用户
alter user 用户 account unlock;

--解锁scott用户
alter user scott account unlock;



--解锁scott用户的密码[此句也可以用来重置密码]
alter user 用户 identified by 密码;

--解锁scott用户的密码[此句也可以用来重置密码]
alter user scott identified by tiger;

单行函数

--单行函数: 作用于一行, 返回一个值.

---字符函数
select upper('yes') from dual;  --YES upper小写变大写
select lower('YES') from dual; --yes lower大写变小写


---数值函数
select round(26.18, 1) from dual; --四舍五入, 后面的参数表示保留的位数(整数往后保留, 负数往前保留)
select trunc(56.16, 1) from dual; --直接截取, 不在看后面数字是否大于5
select mod(10, 3) from dual; --求余数


--日期函数
--查询出emp表中所有员工入职距离现在几天.
select sysdate-e.hiredate from emp e; --sysdate当前系统时间
--算出明天此刻
select sysdate+1 from dual;
--查询出emp表中所有员工入职距离现在几月.
select months_between(sysdate,e.hiredate) from emp e; -- months_between函数(只能用在算月)
--查询出emp表中所有员工入职距离现在几年.
select months_between(sysdate,e.hiredate) / 12 from emp e; -- months_between函数
--查询出emp表中所有员工入职距离现在几周.
select round((sysdate-e.hiredate) / 7) from emp e; 


--转换函数
--日期转字符串
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual; --2020-04-27 10:24:06
select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual; --2020-4-27 10:25:17加上fm前边是0直接去掉
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2020-4-27 22:26:12加上24就是一天24小时计时法
--字符串转日期
select to_date('2020-4-27 22:26:12', 'fm yyyy-mm-dd hh24:mi:ss') from dual;


--通用函数
--算出emp表中所有员工的年薪
--奖金里面有null值, 如果null值和任意数字做算数运算, 结果都是null.
select e.sal * 12 + nvl(e.comm,0) from emp e; --nvl如果第一个参数是null, 用第二个参数的值

条件表达式

--条件表达式
--条件表达式的通用写法, mysql和oracle通用
--给emp表中员工起中文名
select e.ename,
       case e.ename
       when 'SMITH' then '曹贼'
       when 'ALLEN' then '大耳贼'
       when 'WARD' then '诸葛小儿'
       else '无名'
       end
from emp e;

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

--oracle中除了起别名, 都用单引号.
--oracle专用条件表达式
select e.ename,
        decode(e.ename,
        'SMITH',  '曹贼',
        'ALLEN',  '大耳贼',
        'WARD',   '诸葛小儿',
        '无名') 中文名
from emp e;

多行函数

--多行函数[聚合函数]: 作用于多行, 返回一个值.
select count(1) from emp; --查询总数量
select sum(sal) from emp; --工资总和
select max(sal) from emp; --最大工资
select min(sal) from emp; --最低工资
select avg(sal) from emp; --平均工资

分组查询

--分组查询
--查询出每个部门的平均工资(这里写每个什么就是用谁来分组)
--分组查询中, 出现在group by后面的原始列, 才能出现在select后面.
--没有出现在group by后面的列, 想在select后面, 必须加上聚合函数.
--聚合函数有一个特性, 可以把多行记录变成一个值.
select
   e.deptno, avg(e.sal)
from emp e
group by
e.deptno;



--查询出平均工资高于2000的部门信息
select
   e.deptno, avg(e.sal)
from 
   emp e
group by
      e.deptno
having
      avg(e.sal) > 2000;
--所有条件都不能使用别名来判断, 因为条件优先

    
    
--查询出每个部门工资高于800的员工的平均工资
select
   e.deptno, avg(e.sal)
from 
   emp e
where
   e.sal > 800
group by
   e.deptno;
--where是过滤分组前的数据, having是过滤分组后的数据.
--表现形式: where必须在group by之前, having是在group by之后.

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

多表查询

--多表查询中的一些概念
--笛卡尔积
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;

--查询出所有部分以及部门下的员工信息.[外连接]
select * from emp e right join dept d on e.deptno = d.deptno;

--查询出所有员工信息, 以及员工所属部门.[外连接]
select * from emp e left join dept d on e.deptno = d.deptno;

--oracle中专用外连接
select * from emp e, dept d where e.deptno(+) = d.deptno; --(+)放在那边, 那么我们就显示另外一边的全部数据

自连接

--查询出员工姓名, 员工领导姓名(这里把e1当成员工表, 把e2当成领导表)
--自连接: 自连接其实就是站在不同的角度吧一张表看成多张表
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;



--查询出员工姓名, 员工部门名称, 员工领导姓名, 员工领导部门名称.
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;

子查询

--子查询
--子查询返回一个值
--查询出工资和scott一样的员工信息
select * from emp where sal in (select sal from emp where ename = 'SCOTT');



--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in (select sal from emp where deptno = 10);




--子查询返回一张表
--查询出每个部门最低工资, 和最低工资员工姓名, 和该员工所在部门名称
--1.选查询出每个部门最低工资
select 
   min(e.sal) 
from
   emp e
group by
   e.deptno
--2. 三表联查, 得到最终结果
select
  t.deptno, t.msal, e.ename, d.dname
from 
  emp e, 
  dept d, 
  (select 
     deptno, 
     min(e.sal) msal
  from
     emp e
  group by
     e.deptno) t
where 
     t.deptno = e.deptno
and
     t.msal = e.sal
and
     e.deptno = d.deptno;

分页查询

--oracle中的分页
--rownum行号: 当我们做select操作的时候,
--每查询出一行记录, 就会在该行上加上一个行号,
--行号从1开始, 一次递增, 不能跳着走.

    
    
--排序操作会影响rownum的顺序
select rownum, e.* from emp e order by e.sal desc;

    
    
--如果普及到排序, 但是还要使用rounum的话, 我们可以再次嵌套查询.
select 
       * 
from 
       (select 
               rownum rn, 
               d.* 
        from (select 
                      * 
              from 
                      emp e 
              order by 
                      e.sal 
              desc) d) b 
where 
       b.rn < 4; 
   


--emp表工资倒叙排列后, 每页五条记录, 查询第二页.
--rownum行号不能写上大于一个整数.
--第一种方式
select * from (
       select rownum rn, tt.* from (
           select * from emp order by sal desc
       ) tt
) where rn > 5 and rn < 11;



--第二种方式
select * from (
       select rownum rn, tt.* from (
           select * from emp order by sal desc
       ) tt where rownum < 11
) where rn > 5;

视图

--视图
--视图的概念: 视图就是提供一个查询的窗口, 所有数据来自于原表.

--查询语句创建表
create table 表名称 as select * from 用户.表名称; 
select * from 表名称;

--查询语句创建表
create table emp as select * from scott.emp; --把scott用户下的表, 赋值一份到该用户下
select * from emp;



--创建视图[必须有dba权限]
create view 视图名称 as select 列名称, 列名称 from 表名称; 

--创建视图[必须有dba权限]
create view v_emp as select ename, job from emp; --创建个名为v_emp的视图表, 该表中只有2个字段(ename, job)


    
--查询视图
select * from 视图名称;

--查询视图
select * from v_emp;



--修改视图[不推荐->因为你修改视图, 原表数据也会跟着修改]
update 视图名称 set 列名称 = 值 where 列名称 = 值;
commit; --提交事务
    
--修改视图[不推荐->因为你修改视图, 原表数据也会跟着修改]
update v_emp set job = 'CLERK' where ename = 'ALLEN';
commit; --提交事务

    

--创建只读视图
create view 视图名称 as select 列名称, 列名称 from 表名称 with read only;

--创建只读视图
create view v_emp1 as select ename, job from emp with read only;




--视图的作用?
--第一: 视图可以屏蔽掉一些敏感字段.
--第二: 保证总部和分部数据及时统一.

索引

--索引
--索引的概念: 索引就是在表的裂伤构建一个二叉树
--达到大幅度提高查询效率的目的, 但是索引会影响增删改的效率.
--单列索引
--创建单列索引
create index 索引名称 on 表名称(字段名);
create index idx_ename on emp(ename);

--单列索引触发规则, 条件必须是索引列中的原始值.
--单行函数, 模糊查询, 都会影响索引的触发.
select * from emp where ename = 'SCOTT';



--复合索引
-- 创建复合索引
create index 索引名称 on 表名称(字段名, 字段名);
create index idx_enamejon on emp(ename, job);



--复合索引中第一列为优先检索列
--如果要触发复合索引, 必须包含有优先检索列中的原始值
select * from emp where ename = 'SCOTT' and job = 'xxx'; --触发复合索引
select * from emp where ename = 'SCOTT' or job = 'xxx'; --不触发索引
select * from emp where ename = 'SCOTT'; --触发单列索引.