spool命令、创建一个表,创建并且copy表,查看别的用户下的表,rowid行地址 索引的时候使用,表的增删改查,删除表,oracle的回收站




1、spool命令

spool "D:\test.txt"

spool off

SQL> host cls

2、创建一个表

SQL> --条件(1):有创建表的权限,(2):有表空间

SQL> desc t4;

 名称                                     
是否为空?
类型

 ----------------------------------------- -------- ----------------------------

 TID                                               
  NUMBER

 TNAME                                             
VARCHAR2(20)

 TNAME2                                           
 CHAR(6)

 

SQL> select * from tab;

 

TNAME                         
TABTYPE  CLUSTERID                              

------------------------------ ------- ----------                              

BONUS                         
TABLE                                            

DEPT                          
TABLE                                           

EMP                           
TABLE                                           

EMP10                         
TABLE                                            

SALGRADE                      
TABLE                                           

STUDENT                       
TABLE                                           

T1                            
TABLE                                           

T4 
                           TABLE                                           

 

已选择8行。

 

SQL> drop table t1;

 

表已删除。

 

SQL> drop table t4;

 

表已删除。

 

SQL> --创建表

SQL> create table t4(tid number,tname varchar2(20),tname2 char(6));

 

表已创建。

 

SQL> --向表里面插入数据

SQL> --Oracle的数据类型

SQL> --A:varchar2(size)可变和定长区别

SQL> --B:varchar2()不能超过4096字节

SQL> --C:oracle的数据类型支持可见字符存储和不可字符的存储,存图片

 

3.创建并且copy表,使用create
table tablename + as的语法

SQL> --案例:

SQL> create table t5

 
2  as

 
3  select * from emp;

 

表已创建。

 

SQL> select * from tab;

 

TNAME                         
TABTYPE  CLUSTERID                              

------------------------------ ------- ----------                              

BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE                                     
      

BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE                                           

BONUS                         
TABLE                                           

DEPT                          
TABLE                                           

EMP                           
TABLE                                           

EMP10                         
TABLE                                           

SALGRADE                      
TABLE                                           

STUDENT     
                  TABLE                                           

T4                            
TABLE                                           

T5                            
TABLE                                           

 

已选择10行。

 

SQL> desc t5;

 名称                                     
是否为空?
类型

 ----------------------------------------- -------- ----------------------------

 EMPNO                                             
NUMBER(4)

 ENAME                                             
VARCHAR2(10)

 JOB                                               
VARCHAR2(9)

 MGR                                               
NUMBER(4)

 HIREDATE                                          
DATE

 SAL                                               
NUMBER(7,2)

 COMM   
                                           NUMBER(7,2)

 DEPTNO                                            
NUMBER(2)

 

SQL> set linesize 120

SQL> set pagesize 120

SQL> select * from t5;

 

    
EMPNO ENAME      JOB              MGR HIREDATE        
SAL       COMM     DEPTNO                             

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                             

     
7369 SMITH      CLERK          
7902 17-12月-80           
800                    20                             

     
7499 ALLEN      SALESMAN       
7698 20-2月 -81          
1600        300         30                             

     
7521 WARD       SALESMAN       
7698 22-2月 -81          
1250        500         30                             

     
7566 JONES      MANAGER         7839 02-4月 -81          
2975                    20                             

     
7654 MARTIN     SALESMAN       
7698 28-9月 -81          
1250       1400         30                             

     
7698 BLAKE      MANAGER        
7839 01-5月 -81          
2850                    30                             

     
7782 CLARK      MANAGER        
7839 09-6月 -81          
2450                    10                             

     
7788 SCOTT      ANALYST        
7566 19-4月 -87          
3000                    20                             

     
7839 KING       PRESIDENT           
17-11月-81          
5000                    10                             

     
7844 TURNER     SALESMAN       
7698 08-9月 -81          
1500          0         30                             

     
7876 ADAMS      CLERK          
7788 23-5月 -87          
1100                    20                             

     
7900 JAMES      CLERK          
7698 03-12月-81           
950                    30     
                        

     
7902 FORD       ANALYST        
7566 03-12月-81          
3000                    20                             

     
7934 MILLER     CLERK          
7782 23-1月 -82          
1300                    10                     
        

       

已选择18行。

 

4.查看别的用户下的表

SQL> select * from scott.dept;

 

   
DEPTNO DNAME          LOC                                                                                          

---------- -------------- -------------                                                       
                         

       
10 ACCOUNTING     NEW YORK                                                                                     

       
20 RESEARCH       DALLAS                                                                                       

       
30 SALES          CHICAGO                                                                                      

       
40 OPERATIONS     BOSTON                                                                                       

 

5.rowid行地址
索引的时候使用

SQL> select rowid,empno,ename from emp;

 

ROWID                  
EMPNO ENAME                                             
                                       

------------------ ---------- ----------                                                                               

AAAR3sAAEAAAACXAAA      
7369 SMITH                                                           
                         

AAAR3sAAEAAAACXAAB      
7499 ALLEN                                                                                    

AAAR3sAAEAAAACXAAC      
7521 WARD                                                                          
           

AAAR3sAAEAAAACXAAD      
7566 JONES                                                                                    

AAAR3sAAEAAAACXAAE      
7654 MARTIN                                                                                   

AAAR3sAAEAAAACXAAF      
7698 BLAKE                                                                                    

AAAR3sAAEAAAACXAAG      
7782 CLARK                                                                                    

AAAR3sAAEAAAACXAAH      
7788 SCOTT                                                                                    

AAAR3sAAEAAAACXAAI      
7839 KING                                                                                     

AAAR3sAAEAAAACXAAJ      
7844 TURNER                                                                                   

AAAR3sAAEAAAACXAAK      
7876 ADAMS                                                                                    

AAAR3sAAEAAAACXAAL      
7900 JAMES                                                                                     

AAAR3sAAEAAAACXAAM      
7902 FORD                                                                                     

AAAR3sAAEAAAACXAAN      
7934 MILLER                                                                                    

AAAR3sAAEAAAACXAAO         
1 aaa                                                                                      

AAAR3sAAEAAAACXAAP         
2 bb                                                                                        

AAAR3sAAEAAAACXAAQ         
3 ccc                                                                                      

AAAR3sAAEAAAACXAAR         
4 cccc4                                                  
                                  

 

已选择18行。

 

SQL> desc t5;

 名称                                                             
是否为空?
类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                     
NUMBER(4)

 ENAME                                          
                           VARCHAR2(10)

 JOB                                                                       
VARCHAR2(9)

 MGR                                                                       
NUMBER(4)

 HIREDATE                                
                                  DATE

 SAL                                                                       
NUMBER(7,2)

 COMM                                                                      
NUMBER(7,2)

 DEPTNO                                 
                                   NUMBER(2)

 

6.表的增删改查

SQL> alter table t5 drop column ename;

 

表已更改。

 

SQL> desc t5;

 名称                                                             
是否为空?
类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                     
NUMBER(4)

 JOB                                                                       
VARCHAR2(9)

 MGR                                                                       
NUMBER(4)

 HIREDATE                                                                  
DATE

 SAL                                                                       
NUMBER(7,2)

 COMM                                                                      
NUMBER(7,2)

 DEPTNO                                                                    
NUMBER(2)

 

SQL> --向表中添加一列

SQL> alter table t5 add ename varchar2(10);

 

表已更改。

 

SQL> desc t5;

 名称                                                             
是否为空?
类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                      
               NUMBER(4)

 JOB                                                                       
VARCHAR2(9)

 MGR                                                                       
NUMBER(4)

 HIREDATE                                               
                   DATE

 SAL                                                                       
NUMBER(7,2)

 COMM                                                                      
NUMBER(7,2)

 DEPTNO                                                
                    NUMBER(2)

 ENAME                                                                     
VARCHAR2(10)

 

SQL> --修改表中的字段

SQL> alter table t5 modify varchar2(20);

SQL> alter table t5 modify ename varchar2(20);

 

表已更改。

 

SQL> desc t5;

 名称                                                             
是否为空?
类型

 ----------------------------------------------------------------- -------- --------------------------------------------

 EMPNO                                                                     
NUMBER(4)

 JOB                                            
                           VARCHAR2(9)

 MGR                                                                       
NUMBER(4)

 HIREDATE                                                                  
DATE

 SAL                                             
                          NUMBER(7,2)

 COMM                                                                      
NUMBER(7,2)

 DEPTNO                                                                    
NUMBER(2)

 ENAME                                     
                                VARCHAR2(20)

 

SQL> select * from tab;

 

TNAME                         
TABTYPE  CLUSTERID                                                                      

------------------------------ ------- ----------              
                                                        

BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE                                                                                   

BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE                                         
                                          

BONUS                         
TABLE                                                                                   

DEPT                          
TABLE                                                       
                            

EMP                           
TABLE                                                                                   

EMP10                         
TABLE                                                                     
              

SALGRADE                      
TABLE                                                                                   

STUDENT                       
TABLE                                                                                   

T4                            
TABLE                                                                                   

T5           
                 TABLE                                                                                   

 

已选择10行。

 

7.删除表

SQL> drop table t4;

 

表已删除。

 

8.oracle的回收站

SQL> --A:查看回收站

SQL> show recyclebin;

ORIGINAL NAME   
RECYCLEBIN NAME                OBJECT TYPE 
DROP TIME                                                 

---------------- ------------------------------ ------------ -------------------                                       

T1              
BIN$rIQD16aER0KVHp0zrF1+qA==$0 TABLE        2014-10-12:21:13:59                                       

T4              
BIN$yrN6U2dUQj+gtVqcgRFodw==$0 TABLE        2014-10-12:21:28:46                                       

T4              
BIN$5k3GcwZfRjiWlxGs/u26nA==$0 TABLE        2014-10-12:21:14:05                                       

SQL> --B:清空回收站

SQL> purge recyclebin;

 

回收站已清空。

 

SQL> --C:彻底删除一张表 drop table test1 purge;--加上purge,直接删除一张表,不通过回收站

SQL> --D:还原表(从回收站中返复原表)

SQL> --闪回的内容

SQL> --并不是所有的人都有回收站 
管理员没有回收站

SQL> --E:结论:通过回收站的名字,查看原来表的内容,需要双引号

SQL> spool off

 

时间: 2014-10-12

spool命令、创建一个表,创建并且copy表,查看别的用户下的表,rowid行地址 索引的时候使用,表的增删改查,删除表,oracle的回收站的相关文章

laravel路由创建RESTFul风格控制器实现文章增删改查

基本控制器及控制器路由.控制器中间件都比较简单,这里不再赘述,相关文档参考HTTP 控制器文档一节. 1.创建RESTFul风格控制器 注:关于什么是RESTFul风格及其规范可参考这篇文章:理解RESTful架构. 本文我们主要讨论创建一个RESTFul风格的控制器用于对博客文章进行增删改查,创建这样的控制器很简单,在应用根目录运行如下Artisan命令即可: php artisan make:controller PostController 该命令会在app/Http/Controller

【黑马Android】(04)数据库的创建和sql语句增删改查/LinearLayout展示列表数据/ListView的使用和BaseAdater/内容提供者创建

数据库的创建和sql语句增删改查 1. 加载驱动. 2. 连接数据库. 3. 操作数据库.   创建表: create table person( _id integer primary key, name varchar(20), age integer );   添加: insert into person(name, age) values('lisi', 19);   删除: delete from person where _id = 1;   修改: update person se

利用Hibernate怎么增删改查一个表!

问题描述 小弟新手别喷!!!我现在配置了hibernate.cfg.xml写了一个User的实体类建了个Tableuser_register我想通过hibernate来访问建好的table表,显示在浏览器上!我就想要个例子,最好有增删改查,谢谢各位大神了!! 解决方案 解决方案二:数据层:/*一定要有工具给你自动生成的session工厂(如果你用的IDE是MyEclipse那么在添Hibernate*将会给你自动生成).*/Sessionsess=HibernateSessionFactory.

【自然框架】之鼠标点功能现(一):单表的增删改查(即上次5月23日活动的一个主题)【Demo、源码下载】

  简单的需求,点点鼠标就可以了,那么复杂的需求呢?还是要写代码,哈哈. 不要被我误导了哦,关于什么时候写代码的问题,请看这里:http://www.cnblogs.com/jyk/archive/2009/06/21/1507594.html    单表的增删改查         我有一个梦想,那就是不用敲代码,只需要点点鼠标,就可以实现客户的需求.       可能您会说这是不可能的,但是有个梦想总没有错吧.我就是想实现我的这个梦想,虽然可能一辈子都达不到,但是我还想努力一下子,不想让自己后

C# 用Linq的方式实现对Xml文件的基本操作(创建xml文件、增删改查xml文件节点信息)

C# 用Linq的方式实现对Xml文件的基本操作(创建xml文件.增删改查xml文件节点信息)     http://www.cnblogs.com/mingmingruyuedlut/archive/2011/01/27/1946239.html    修改了一下里面一部分             try            {                //定义并从xml文件中加载节点(根节点)                XElement rootNode = XElement.L

将xml文件作为一个小的数据库,进行学生的增删改查的简单实例_AJAX相关

1.xml文件: <?xml version="1.0" encoding="UTF-8"?><Students> <student id="2"> <name>ttt</name> <age>44</age> </student> <student id="3"> <name>linda2</name

什么是Pro*C/C++,嵌入式SQL,第一个pro*c程序,pro*c++,Makefile,Proc增删改查

 1 什么是Pro*C/C++ 1.通过在过程编程语言C/C++中嵌入SQL语句而开发出的应用程序 2.什么是嵌入式SQL 1.在通用编程语言中使用的SQL称为嵌入式SQL 2.在SQL标准中定义了很多中语言的嵌入式SQL 3.各个厂商对嵌入式SQL的具体实现不同 3.什么是Pro*C/C++ 1.在C/C++语言中嵌入SQL语句而开发出的应用程序. 2.目的:使c/c++这种效率语言称为访问数据库的工具. 4.嵌入式SQL的载体是宿主语言 宿主语言          Pro程序 C/C++

【框架】[Hibernate]利用Hibernate进行单表的增删改查-Web实例

转载请注明出处:http://blog.csdn.net/qq_26525215 本文源自[大学之旅_谙忆的博客] 前面两篇博客已经将Hibernate的基础知识讲解得差不多了,差不多到写实例的时候了. 本篇只用hibernate进行单表的增删改查. 应用Hibernate,对students表进行增删改查. service层和DAO层,我都是直接写实现类了(因为这里主要是演示一下Hibernate的使用),如果是开发项目,注意一定要写接口! 准备数据库: 首先准备一个students表: cr

AngularJS中如何使用$http对MongoLab数据表进行增删改查_AngularJS

主页面: <button ng-click="loadCourse()">Load Course</button> <button ng-click="toggleAddCourse(true)">Add New Course</button> <ng-includce src="'course_list.html'"></ng-include> <ng-include