Oracle数据库的常用操作总结

Oracle数据库的常用操作总结

先简单介绍一下oracle的体系结构

1、数据库database:指的是oracle数据库系统的服务端。

2、实例SID：每一个数据库在oracle里称为一个实例，这个实例有个唯一的名称叫sid.在实际的羡慕中一台服务器一般只有一个实例，多实例需要进行数据库的集群配置。

3、表空间: 一个表空间相当于mysql的一个数据库的概念，oracle是分配表空间，而mysql是新建数据库。

4、数据文件:数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或多个数据文件中。

5、用户：sys为系统管理员；system为一般管理员；还有一类是普通管理员，数据库自带的用户scott,hr，项目中使用的用户。

总结:一个oracle数据库可以创建多个数据库，每个数据库称为实例，在实例里可以分配多个表空间，每个表空间里可以创建多个数据表，而每个表空间里的表数据都存储在一个数据文件里，要查询表空间里的表数据就需要在实例下给表空间指定个用户，由用户来访问表空间里的表。

下面来介绍常用的操作:

1、创建表空间

创建表空间只有系统管理员才有权限，其他用户没有权限。

create  tablespace  haha          --创建表空间的名称
datafile 'c:\20171124.dbf'      --数据文件存储的路径
size  10m                                     ---文件大小
autoextend on                            --自动扩展
next 10;                                        --扩展大小

2、创建用户

create  user   xixi                    --用户名
identified  by  xixi                    --密码
default  tablespace  haha;    --默认表空间的名称

3、给用户授权

语法:  grant  权限/角色   to  角色
         Oracle中已存在三个重要的角色:connect角色、resource角色,dba角色
         connect角色: --是授予最终用户的典型权利，最基本的
                    alter  session ---修改会话
                    create  cluster --建立聚簇
                    create  database link --建立数据库连接
                    create  sequence  --建立序列
                    create  session --建立会话
                    create symonym  --建立同义词
                    create view  ---建立视图
         resource角色:---是授予开发人员的
                     create  cluster --建立聚簇
                     create  procedure  --建立过程
                     create  sequence  --建立序列
                     create  table ---建表
                     create   trigger ---建立触发器
                     create  type ---建立类型
         dba   最高级权限，相当于管理员的角色

grant   dba  to xixi;    
grant   create  session to xixi;

4、建表

  Oracle中存储的字段类型:
                数值:
                        int
                        bigint
                        double
                        number(v1,v2)--v1表示数值总长度，v2表示小数位数(推荐使用)
                 字符:
                        char-------定长
                        varchar---可变长度
                        varchar2----可变长度，中文和字符都是占2位(推荐使用)
                 日期:
                        date----yyyy/mm/dd  hh:mi:ss
                        datetime ---date+9 可以精确到秒后9秒，3位毫秒，6位微妙，9位纳秒
                 大字段:
                        clob
                                 4g----文本数据---文章
                        blod
                                 4g----二进制---小电影
                        long
                                 2g-----文本数据---文章
                         大字段我们一般不使用，但在存储大数据都输存储在公有云上。     

create   table  person(
         pid  number(5),
         name  varchar2(10),
         gender  char(1),
         birthday date
)

5、维护表

--增加一列
alter table person add(address varchar2(20));
--修改列类型
alter table person modify(address varchar2(50));
--修改列名
alter table person rename column name to pname;
--删除列
alter table person drop column address;
--删除表
drop table person;
--修改表名
rename person to person1;

6、添加约束

--非空约束
alter table person modify (pname varchar2(20) not null);
--唯一约束
alter table person add constraint person_qu_pname unique  (pname);
--主键约束=非空+唯一
alter  table person add constraint person_pk_pid primary key(pid);
--检查约束
alter  table person add constraint person_ck_gender  check  (gender in (1,2,3));
--外键约束
/*
1、外键存在于子表中
2、外键的值一定是主表的主键
加入外键后，删除表时一定要先删除子表再删除主表，如果直接删除
主表会出现约束存在无法删除。但是可以加级联完成
*/
create table orders(
       oid number(10) primary key,
       oname varchar2(20) not null,
       oprice number(6,2)
);
create table order_detail(
       detail_id number(10) primary key,
       detail_name varchar2(20) not null,
       detail_price number(6,2),
       oid number(10)
);
/* 加入外键与级联删除*/
alter  table order_detail  add constraint detail_fk_oid
foreign key  (oid)  references orders(oid) on delete cascade;

7、视图

虚拟表，数据来源是实体表
         意义:
               权限粒度控制
               复杂sql简单化

--创建只读视图
create view per1 as select pname ,pid from person with read only ;
select * from per1;

8、序列

--实现主键自增
create sequence persion_pid;
select persion_pid.nextval from dual;
select persion_pid.currval  from dual;

9、索引

索引(用于数据量很大时的查询)
       索引的原理:
                   表里数据按索引字段从小到大排序
                   取出两个字段:索引字段(sal)和rowid
                   把这个结果集保存在一张特殊的表里---索引表
                   根据索引表创建一棵树(Btree)---二叉树
                   在查找是通过字段在二叉树中进行折半查找。

--创建索引，是在查询条件下创建的。
select * from emp where sal=1500;
create  index   emp_index_sal on emp(sal);

10、操作数据

--插入数据
insert  into person values(2,'哈哈',2,sysdate);
insert into person(pid,pname) select empno,ename from scott.emp;
select * from person;
--修改数据
update person set pname='嘻嘻' where pid=2;
--删除
delete  from person;
truncate table person;--删除表，再重新创建表。

11、查询

/*内连接
        1、隐式内连接:
                from   A,B where A.关联字段=B.关联字段
          2、显示内连接
                from   A   inner join B  on A.关联字=B.关联字
*/
select *   from emp,dept  where emp.deptno=dept.deptno;
select  *  from emp  inner join dept  on  emp.deptno=dept.deptno;

/*
外连接
1、左外连接
        from  A  left join B on A.关联字=B.关联字
        以左表为基准，全部显示，以右表作为补充表。   
2、右外连接
        from  A  right  join B on A.关联字=B.关联字
        以右表为基准，全部显示，以左表作为补充表。
3、Oracle的外连接特殊写法:
        在补充显示表后添加(+)
         from   A,B where A.关联字段=B.关联字段(+)
*/
select *  from dept left join emp on dept.deptno=emp.deptno;
select *  from dept right join emp on dept.deptno=emp.deptno;
select *  from dept,emp where dept.deptno=emp.deptno(+);

/*
子查询(子查询通常用于，子查询里面的表是小表，外面是大表)
       在一个子查询语句中嵌套了另一个查询语句(多个)
       意义:
               1、优化sql查询---性能
               2、多条sql简单化---功能
        语法:
        from A,(查询语句) B where A.关联字段=B.关联字段
        from  A   where  a  表的字段   in   B 表的字段
*/
--1、查询是领导的所有员工信息
select  * from emp where empno in(select mgr from emp);
--2、.列出至少有三个员工的所有部门和部门信息。
select d. * 
from dept d,(select  deptno, count(*)  c from emp group by deptno ) e
where d.deptno=e.deptno and e.c>=3;4


/*
exists---存在
语法:
        from A  where exists(子查询)
                子查询有结果集，则exists表达式为真。
                子查询没有结果集，则exists为假
*/
--查询有员工的部门信息
select * from dept d where exists(select deptno from emp e where e.deptno=d.deptno)

--oracle中的分页(第二页，每页5条记录)
/*
rownum原理
1、执行查询语句，返回结果集
2、取出第一行数据，增加一列rownum，赋值为1
3、条件判断，如果不满足条件则抛弃该行，如果满足条件则返回该行
4、继续取下一行，生成rownum
5、重复执行第三步
*/
select  *  from (select rownum  rn,t.empno,t.ename,t.sal  from(select * from emp order by sal desc) t
where rownum<11)where  rn>5;

集合运算中的关键字

并集: union/union all

交集 :  intersect

差集: minus