Oracle常用操作【自己的练习】

  Oracle查询的时候条件要用单引号包裹,不能用双引号;Oracle的in子查询里面的值最多有1000个。。。。。。。。

连接orcl数据库

C:\Windows\system32>sqlplus sys/123456@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 14:32:34 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER 为 "SYS"
SQL> conn sbgl/sbgl@orcl
已连接。
SQL> show user
USER 为 "SBGL"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl

 

登录用户切换用户:

C:\Windows\system32>sqlplus /@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 17:31:52 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER 为 "SYS"
SQL> connect sbgl/sbgl@orcl as sysdba
已连接。
SQL> show user
USER 为 "SYS"
SQL> connect sbgl/sbgl@orcl
已连接。
SQL> show user
USER 为 "SBGL"
SQL>

注意:以sysdba登录实际上登录的sys用户,因此登录用户的时候不要以sysdba登录。

/**1创建表空间**/
create tablespace sbgl datafile 'F:\Oracle\sbgl.dbf'size 200M
autoextend on next 50M force logging;

/**2.创建用户***/
create user sbgl identified by sbgl default tablespace sbgl;

/**3.授予sbgl操作权限**/
grant connect,resource,dba to sbgl;


/***4.创建导出目录(之后手动创建目录)**/
create or replace directory pump_dir as 'F:\expdp';

/********5.向新创的用户授权******/
grant read,write on directory pump_dir to sbgl;

/*******6.查看管理理员目录****/
select * from dba_directories;

导入导出:

*导入数据*
1、将SBGL20180129.EXPDP文件拷贝到d:\expdp;
2、在CMD窗口下执行以下命名
impdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.EXPDP table_exists_action=replace  nologfile=y

*导出数据:*
expdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.expdp schemas=sbgl nologfile=y 

/****查看用户的角色***/
select * from USER_SYS_PRIVS where USERNAME='sbgl';
select * from dba_sys_privs;

 

/**查看当前登录用户信息(用户名、创建时间、表空间)*****/
select * from user_users;

 

/**查看所有用户信息****/

select * from dba_users;              /*查看数据库里面所有用户,前提是有dba权限的帐号,如sys,system*/
select username from dba_users;    //查看所有有dba权限用户名称

select * from all_users;                /*查看能管理的所有用户*/

 

/****查看用户与表空间的关系***/
select default_tablespace, temporary_tablespace, d.username from dba_users d where username='SBGL';

 

/***查看所有的表空间**/
select tablespace_name from dba_tablespaces;

 

/*******删除表空间*********/

drop tablespace 表空间名称 including contents and datafiles;  

 

/*****查看所有的表空间与其对应的数据文件的位置*********/ 

select * from dba_data_files;

 

/*查看所有用户**/
select * from dba_users;

 

/*查看一个表空间下面的所有表**/
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='SBGL';

 

/***查看一个用户有哪些表***/
select * from all_tables where owner='SBGL';

 

/***********查看当前用户有哪些表****/

 select table_name from user_tables;

 

/**********查看当前用户有哪些试图**********/

select * from user_views;

select VIEW_NAME from user_views;

 

/*****查看dba_tablespaces表结构*****/
desc dba_tablespaces;

 

/***查看当前用户***/
show user;

select username from user_users;

 

/*****查看当前用户所有的权限***/

select * from session_privs;


/***查看当前连接数据库***/
show parameter instance_name;

 

/****查看建表语句******/

 select dbms_metadata.get_ddl('TABLE','a') from dual   只需要将表名的小写改为大写即可;;   //其中a为表名;  如果有PLSQL的话可以直接在可视化界面查看表的建表语句;

 

/*****oracle查看版本**/

select * from v$version;

 

/*查看数据库名*/

select name from v$database; 

 

 /*查看sid*/

select instance_name from v$instance;

 

/**根据约束名称查询对应的表名(有一列是tablename)**/

select * from dba_constraints where constraint_name='xxx外键名' and constraint_type = 'R';

 

/********删除用户************/

drop user username;

drop user username cascade;//级联删除用户的数据 

 

/************添加一列 *******/
alter table A add( columnname varchar2(20));

 

/*********修改列名称    ******/
alter table A rename column coluName  to newName;

 

/************修改列的类型:*******/
alter table A modify coluName varchar(200);


/****删除一列 ***********/
alter table A drop column coluName

 

/******字符串拼接 (||)***/

 select 1||'23' from dual;

结果:

/**************序列简单使用*********/

1. 创建
CREATE SEQUENCE SEQ_TEST_1 INCREMENT BY 1 NOCYCLE CACHE 10;
2.获取序列的下一值
select "SEQ_TEST_1".nextval from dual
3.删除序列
DROP SEQUENCE SEQ_TEST_1

有时候序列会发生错误,导致数据插入报错,解决办法:重新设置自增的步长,然后自增一下到指定的值,然后重置自增的步长为1。比如下面想在目前错误的基础上+400变为正确的:

alter sequence SEQ_TEST_1 increment BY 400;
select "SEQ_TEST_1".nextval from dual
alter sequence SEQ_TEST_1 increment BY 1

插入的时候直接使用序列:

insert into table (id) values (seq_name.nextval)

 

 补充:oracle中多个用户共用一个表空间也是不同的表,表空间、表、用户的关系如下:

用户=商家
表=商品
表空间=仓库
1. 1个商家能有很多商品,1个商品只能属于一个商家
2. 1个商品可以放到仓库A,也可以放到仓库B,但不能同时放入A和B
3. 仓库不属于任何商家
4. 商家都有一个默认的仓库,如果不指定具体仓库,商品则放到默认的仓库中

oracle中用户的所有数据都是存放在表空间中的,很多个用户可以共用一个表空间,也可以指定一个用户只用某一个表空间。
表空间:创建表空间会在物理磁盘上建立一个数据文件,作为数据库对象(用户、表、存储过程等等)的物理存储空间;
用户:创建用户必须为其指定表空间,如果没有显性指定默认表空间,则指定为users表空间;创建用户后,可以在用户上,创建表、存储过程等等其他数据库对象;
表:是数据记录的集合;
创建过程: 表空间--->用户--->表;
所属关系: 表空间 包含 用户 包含 表;

 

分页查询相关:(对原始的查询进行两层封装)

/*****普通的查询(30数据)****/
select * from ENVIRONMENT_APPLY_AUDIT;


/*******查询带行号的数据**/
select ROWNUM ru,AAA.* from (      select * from ENVIRONMENT_APPLY_AUDIT   ) AAA;

/*******查询第一页   每页5条数据***/
select * from (select AAA.*,ROWNUM ru from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6);
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6) where ru>0;


/**********查询第二页数据********************/
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<11) where ru>5;


/**********查询第三页数据********************/
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<16) where ru>10;

 

/********************oracle表分区相关*************/

1.概念

表空间:是一个或多个数据文件的集合,所有的数据对象都存在指定的表空间中。
分区表:逻辑上分区表仍然是一个完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),可以提高查询效率,不至于每次都扫描整张表。

 

查询oracle版本是否支持分区

select * from v$option where parameter = 'Partitioning';

 结果为true 的时候表示当前oracle版本支持分区。如果不支持需要更换oracle版本为支持分区的版本。XE不支持,ORCL支持。

 

  对现有表table_8进行分区有两种方式,第一种是创建与现有表一样结构的分区表table_9,然后将原来table_8表的数据插入到table_9,然后删掉表table_8,将table_9命名为table_8;第二种是使用oracle自带的线上重定义功能进行分区,重定义会将基础表table_8和table_9进行交换,包括索引、触发器、外键等也会交换,重定义之后table_8变为分区表,table_9变为普通表,table_9会保留table_8的原有的数据。(两种方式都需要确保基础表table_8和中间表table_9的表结构一致,包括索引、触发器等)

2.练习

====================从无到有创建表分区(散列分区)========================
    在列值上使用散列分区。当列的值没有指定条件时,建议使用散列分区。散列分区为同通过指定分区编号来均匀分布数据的一种分区类型。

1.创建两个表空间
create tablespace part1 datafile 'D:\oraclexe\tablespace\part1.dbf'size 1024M autoextend on next 500m force logging;
create tablespace part2 datafile 'D:\oraclexe\tablespace\part2.dbf'size 1024M autoextend on next 500m force logging;

2.创建用户
create user sa identified by 111222 default tablespace part1;

3.授权
grant connect,resource,dba to sa;

4. 查看是否支持分区
select * from v$option where parameter = 'Partitioning';

5.创建一个测试表
create table table_1(ID NUMBER PRIMARY KEY, name varchar2(200));

插入数据
insert into table_1 values(1, 'name1');
insert into table_1 values(2, 'name1');

创建分区表(哈希分区)
CREATE TABLE table_2
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY hash
  (
    ID
  )
  (
    partition part1 TABLESPACE part1,
    partition part2 TABLESPACE part2
  );

查看表是否已经分区:
SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME = 'TABLE_2';

查看表table_2的分区以及分区所在的表空间:
select * from user_tab_partitions where table_name = 'TABLE_2'

测试将表1的数据插入表2:
insert into table_2 select * from table_1

查看分区1的数据:
select * from table_2 partition(part1)
结果:
2    name1
5    name1
6    name1
8    name1

查看分区2的数据:
select * from table_2 partition(part2)
1    name1
3    name1
4    name1
7    name1
9    name1
10    name1


==============在线重定义将普通表变为分区表=========================
测试能否对表进行在线重定义:
EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_1'); // 参数是用户, 表名

创建中间分区表table_3:
CREATE TABLE table_3
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY hash
  (
    ID
  )
  (
    partition part1 TABLESPACE part1,
    partition part2 TABLESPACE part2
  );

开始重建:
BEGIN
  SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_1', 'TABLE_3');
END;

把中间表的内容和数据源表进行同步
EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_1', 'TABLE_3')

开始重定义之后,完成重定义之前,需要在中间表上创建与源表对应的索引、外键、触发器等,而中间表与源表对应的主键,如果需要按照主键重定义,需要在开始redefine之前创建。

结束在线重定义
EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_1', 'TABLE_3')

如果执行在线重定义的过程中出错:
可以在start_redef_table之后,finish_redef_table之前执行:EXEC SYS.dbms_redefinition.abort_redef_table('sa', 'TABLE_1', 'TABLE_3')

测试表table_1是否已经分区
SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_1'

==================================分区的其他操作===================================================

分区创建:
1.再开始建表的时候就指定分区
2.有数据的表table_1变为分区表的思路:
(1)建一个分区中间表table_2,表结构与表table_1一样,然后将表table_1的数据插入table_2,删除表table_1之后将table_2重命名为table_1
(2)在线重定义,也是用中间表进行分区


===============================创建其他方式的分区================================
======范围分区====
1.基于ID进行划分
(1)小于10的一个分区,小于20的一个分区
CREATE TABLE table_4
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY range
  (
    ID
  )
  (
    partition part41 VALUES less than (10) TABLESPACE part1,
    partition part42 VALUES less than (20) TABLESPACE part2
  );
插入20的时候会报错,未映射到分区:SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。
解决办法:第二个分区建立的时候使用Maxvalue
CREATE TABLE table_4
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY range
  (
    ID
  )
  (
    partition part41 VALUES less than (10) TABLESPACE part1,
    partition part42 VALUES less than (maxvalue) TABLESPACE part2
  );

    按日期进行分区也是上面的语句:只不过值改为日期比较-> partition part42 VALUES less than (to_date('01-apr-1998','dd-mon-yyyy')) TABLESPACE part2

======列表分区(适用于一个列的值只有几个值)====
1.创建列表分区
CREATE TABLE table_5
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY list
  (
    name
  )
  (
    partition part51 VALUES
    (
      'zs'
    )
    TABLESPACE part1,
    partition part52 VALUES
    (
      'lisi',
      'ww',
      'zl',
      'tq'
    )
    TABLESPACE part2
  );
如果插入的值不在上面分区允许的范围之内也是报错:SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。


======组合范围散列分区====
    基于范围分区和散列分区,表首先按照某列进行范围分区,然后按照某列进行列表分区,分区之中的分区称为子分区
CREATE TABLE table_6
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY range
  (
    ID
  )
  subpartition BY list
  (
    name
  )
  (
    partition part61 VALUES less than (10) TABLESPACE part1 ( subpartition part61sub1 VALUES
    (
      'zs'
    )
    TABLESPACE part1, subpartition part61sub2 VALUES
    (
      'lisi'
    )
    TABLESPACE part2 ) ,
    partition part62 VALUES less than (maxvalue) TABLESPACE part2 ( subpartition part62sub1 VALUES
    (
      'zs'
    )
    TABLESPACE part1, subpartition part62sub2 VALUES
    (
      'lisi'
    )
    TABLESPACE part2 )
  );

==================================分区查询===========================
1.查看当前用户可以访问的所有分区表的信息
select * from all_part_TABLES;

2.显示当前用户所有分区表的信息:
select * from user_part_tables;

3.查看所有的组合分区表的子分区信息:
select * from user_tab_subpartitions

============================带索引的表的重建(在线重定义分区)==================
1.创建带索引的表8
CREATE TABLE table_8
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  );

创建索引:
create index t8_name_index on table_8(name);

插入数据:
insert into table_8 select * from table_1;

2.查看是否支持在线重定义
EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_8');

3.创建与表8结构一样的分区表9
CREATE TABLE table_9
  (
    ID   NUMBER PRIMARY KEY,
    name VARCHAR2(200)
  )
  partition BY hash
  (
    ID
  )
  (
    partition part1 TABLESPACE part1,
    partition part2 TABLESPACE part2
  );

4.开始重定义
BEGIN
  SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_8', 'TABLE_9');
END;

5.进行数据同步
EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_8', 'TABLE_9')

6.结束在线重定义
EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_8', 'TABLE_9')

7.查看表8是否已经分区
SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_8'

8.查看表8中每个分区的数据:
select * from table_8 partition(part1)
select * from table_8 partition(part2)

 

/**************Oracle 事务相关********************/

DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交,无需用Commit提交。

DQL(数据查询语言)- Select查询语句不存在提交问题。

DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。

DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。

DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。

  执行完DML语句,若没有commit再执行DDL语句,也会自动commit未被commit的数据。

  如果打开自动提交,DML操作后也不需要手动提交:SET AUTOCOMMIT ON;

补充:Oracle如果字段有默认值,在插入或者修改的时候设为null的时候默认值不会生效。

补充:注意not in里包含的子查询是否包含null值。另外记住 null = 2 或者 null != 2 都是false,不满足匹配的条件。

1.使用in做条件时时始终查不到目标列包含NULL值的行,比如:

select * from t_in where id in (1,3,null); 等价于id = 1 or id = 3 or id = null,id = NULL的结果是UNKNOWN,也相当于FALSE。(判断为NULL只能用is)

2.如果not in条件中包含null值,则不会返回任何结果,包含in中含有子查询。比如:

(1)select * from t_in where id not in (1,3,null); 等价于id!=1 and id!=3 and id!=null。(NULL与其他值做=或!=比较结果都是UNKNOWN,所以整个条件就相当于FALSE的,最终没有查出数据。)

(2)select * from t_in where id not in (1,3); 上面查询的where条件等价于id != 1 and id !=3,即使t_in表中有一行为null,它虽然满足!=1和!=3但根据上面的规则,NULL与其他值做=或!=比较结果都是UNKNOWN,所以NULL也不会出现在结果。也就是NOT IN 不能查出为null的元素。

 参考: https://www.cnblogs.com/DreamDrive/p/7608815.html

 

posted @ 2018-02-02 15:49  QiaoZhi  阅读(1504)  评论(0编辑  收藏  举报