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

　　

　　Oracle查询的时候条件要用单引号包裹，不能用双引号。。。。。。。。

 

连接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;

 

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

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;

 

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

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;

结果:

 

 补充: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;