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_oidforeign 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 ) ewhere 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，赋值为13、条件判断，如果不满足条件则抛弃该行，如果满足条件则返回该行4、继续取下一行，生成rownum5、重复执行第三步*/select  *  from (select rownum  rn,t.empno,t.ename,t.sal  from(select * from emp order by sal desc) twhere rownum<11)where  rn>5;

集合运算中的关键字

并集: union/union all

交集 :  intersect

差集: minus