Oracle创建和管理表

简介: SQL> /* SQL> 对于表的操作: 创建表,修改表(添加新的列,改变当前某些列,删除列),删除表 SQL> 创建表: create table(需要create table的权限) SQL> 修改表: alter table tablename add/modify/drop SQL> 删除表:drop table tablename SQL&

SQL> /*
SQL> 对于表的操作: 创建表,修改表(添加新的列,改变当前某些列,删除列),删除表
SQL> 创建表: create table(需要create table的权限)
SQL> 修改表: alter table tablename add/modify/drop
SQL> 删除表:drop table tablename
SQL> */
SQL> show user;
USER 为 "SCOTT"
SQL> --访问hr用户下的表
SQL> select * from hr.employees;
select * from hr.employees
                 *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> --测试defaul值
SQL> create table test1
  2  (tid number,
  3   tname varchar(20),
  4   hiredate date default sysdate);

表已创建。

SQL> insert into test1(tid,tname) values(1,'Mary');

已创建 1 行。

SQL> select * from test1;

       TID TNAME                HIREDATE                                                                               
---------- -------------------- --------------                                                                         
         1 Mary                 12-6月 -11                                                                             

SQL> --rowid rownum都是伪列
SQL> select rowid,rownum,empno from emp;

ROWID                  ROWNUM      EMPNO                                                                               
------------------ ---------- ----------                                                                               
AAANA2AAEAAAAAsAAT          1       1122                                                                               
AAANA2AAEAAAAAsAAO          2       1234                                                                               
AAANA2AAEAAAAAsAAP          3       1235                                                                               
AAANA2AAEAAAAAsAAQ          4       2222                                                                               
AAANA2AAEAAAAAsAAR          5       2345                                                                               
AAANA2AAEAAAAAsAAS          6       2346                                                                               
AAANA2AAEAAAAAsAAA          7       7369                                                                               
AAANA2AAEAAAAAsAAB          8       7499                                                                               
AAANA2AAEAAAAAsAAC          9       7521                                                                               
AAANA2AAEAAAAAsAAD         10       7566                                                                               
AAANA2AAEAAAAAsAAE         11       7654                                                                               

ROWID                  ROWNUM      EMPNO                                                                               
------------------ ---------- ----------                                                                               
AAANA2AAEAAAAAsAAF         12       7698                                                                               
AAANA2AAEAAAAAsAAG         13       7782                                                                               
AAANA2AAEAAAAAsAAH         14       7788                                                                               
AAANA2AAEAAAAAsAAI         15       7839                                                                               
AAANA2AAEAAAAAsAAJ         16       7844                                                                               
AAANA2AAEAAAAAsAAK         17       7876                                                                               
AAANA2AAEAAAAAsAAL         18       7900                                                                               
AAANA2AAEAAAAAsAAM         19       7902                                                                               
AAANA2AAEAAAAAsAAN         20       7934                                                                               

已选择20行。

SQL> --rowid:oracle维护一个地址,该地址指向了该行在硬盘上实际存储的位置
SQL> --关于varchar2和char
SQL> create table testchar
  2  ( c char(5),
  3    v varchar(5));

表已创建。

SQL> insert into testchar values('a','b');

已创建 1 行。

SQL> select * from testchar;

C     V                                                                                                                
----- -----                                                                                                            
a     b                                                                                                                

SQL> select concat(c,'#'),concat(v,'#') from testchar;

CONCAT CONCAT                                                                                                          
------ ------                                                                                                          
a    # b#                                                                                                              

SQL> --添加新列
SQL> alter table testchar
  2  add  hiredate date;

表已更改。

SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(5)
 V                                                                          VARCHAR2(5)
 HIREDATE                                                                   DATE

SQL> --修改表
SQL> alter table testchar
  2  modify c char(10);

表已更改。

SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(10)
 V                                                                          VARCHAR2(5)
 HIREDATE                                                                   DATE

SQL> --删除列
SQL> alter table testchar
  2  drop hiredate;
drop hiredate
     *
第 2 行出现错误:
ORA-00905: 缺失关键字


SQL> ed
已写入 file afiedt.buf

  1  alter table testchar
  2* drop column hiredate
SQL> /

表已更改。

SQL> desc testchar;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 C                                                                          CHAR(10)
 V                                                                          VARCHAR2(5)

SQL> host cls

SQL> --删除表
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                                                                      
------------------------------ ------- ----------                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP10                          TABLE                                                                                   
EMP101                         TABLE                                                                                   
TEST1                          TABLE                                                                                   
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE                                                                                   
TESTDELETE                     TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   

已选择10行。

SQL> drop table testdelete;

表已删除。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                                                                      
------------------------------ ------- ----------                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP10                          TABLE                                                                                   
EMP101                         TABLE                                                                                   
TEST1                          TABLE                                                                                   
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   
BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE                                                                                   

已选择10行。

SQL> --使用purge参数彻底删除表
SQL> drop table test1 purge;

表已删除。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                                                                      
------------------------------ ------- ----------                                                                      
DEPT                           TABLE                                                                                   
EMP                            TABLE                                                                                   
BONUS                          TABLE                                                                                   
SALGRADE                       TABLE                                                                                   
EMP10                          TABLE                                                                                   
EMP101                         TABLE                                                                                   
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE                                                                                   
TESTCHAR                       TABLE                                                                                   
BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE                                                                                   

已选择9行。

SQL> --oracle的回收站
SQL> --查看回收站
SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME                                                 
---------------- ------------------------------ ------------ -------------------                                       
TESTDELETE       BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE        2011-06-12:15:43:34                                       
TESTDELETE       BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE        2011-06-12:14:51:43                                       
SQL> --清空回收站
SQL> purge recyclebin;

回收站已清空。

SQL> show recyclebin;
SQL> --关于约束:
SQL> --创建一个表,包含所有约束
SQL> create table myuser
  2  ( userID number constraint pk primary key,
  3    username varchar2(20) constraint c_name not null,
  4    gender   varchar2(2)  constraint c_gender check (gender in ('男','女')),
  5    email    varchar2(20) constraint c_email1 not null
  6                          constraint c_email2 unique
  7    deptno   number constraint fk refereneces dept(deptno)
  8  );
  deptno   number constraint fk refereneces dept(deptno)
  *
第 7 行出现错误:
ORA-00907: 缺失右括号


SQL>   create table myuser
  2  ( userID number constraint pk primary key,
  3    username varchar2(20) constraint c_name not null,
  4    gender   varchar2(2)  constraint c_gender check (gender in ('男','女')),
  5    email    varchar2(20) constraint c_email1 not null
  6                          constraint c_email2 unique,
  7    deptno   number constraint fk refereneces dept(deptno)
  8  );
  deptno   number constraint fk refereneces dept(deptno)
                  *
第 7 行出现错误:
ORA-02253: 此处不允许约束条件说明


SQL> ed
已写入 file afiedt.buf

  1    create table myuser
  2  ( userID number constraint pk primary key,
  3    username varchar2(20) constraint c_name not null,
  4    gender   varchar2(2)  constraint c_gender check (gender in ('男','女')),
  5    email    varchar2(20) constraint c_email1 not null
  6                          constraint c_email2 unique,
  7    deptno   number constraint fk references dept(deptno)
  8* )
SQL> /

表已创建。

SQL> desc myuser;
 名称                                                              是否为空? 类型
 ----------------------------------------------------------------- -------- --------------------------------------------
 USERID                                                            NOT NULL NUMBER
 USERNAME                                                          NOT NULL VARCHAR2(20)
 GENDER                                                                     VARCHAR2(2)
 EMAIL                                                             NOT NULL VARCHAR2(20)
 DEPTNO                                                                     NUMBER

SQL> insert into myuser values(1,'Tom','男','ddd@126.com',10);

已创建 1 行。

SQL> insert into myuser values(1,'Tom','男','ddd@126.com',10);
insert into myuser values(1,'Tom','男','ddd@126.com',10)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK)


SQL> insert into myuser values(2,'Tom','啊','ddd@126.coddm',10);
insert into myuser values(2,'Tom','啊','ddd@126.coddm',10)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.C_GENDER)


SQL> --触发器也可以检查数据的正确与否
SQL> spool off

目录
相关文章
|
2月前
|
Oracle 安全 关系型数据库
【Oracle】玩转Oracle数据库(六):模式对象管理与安全管理
【Oracle】玩转Oracle数据库(六):模式对象管理与安全管理
49 10
|
2月前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(三):数据库的创建和管理
【Oracle】玩转Oracle数据库(三):数据库的创建和管理
135 5
|
10月前
|
SQL 监控 Oracle
Oracle创建和管理表空间
Oracle创建和管理表空间
73 1
|
9月前
|
Oracle 关系型数据库 数据库
9-4 Oracle管理表空间和数据文件
9-4 Oracle管理表空间和数据文件
|
2月前
|
Oracle 关系型数据库 数据库
Oracle查询优化-复制表的定义及数据
【1月更文挑战第5天】【1月更文挑战第14篇】在Oracle数据库中,复制表定义和复制表数据是两个常见的操作。
72 1
|
10月前
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
89 0
|
2月前
|
消息中间件 关系型数据库 Kafka
实时计算 Flink版产品使用合集之oracle cdc 抽取新增一张表 可以从savepoint恢复吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之oracle无主键的表支持同步吗如何实现
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
运维 DataWorks Oracle
DataWorks产品使用合集之在标准模式下,当同步Oracle的表或视图时,是否需要在源端的测试和生产环境中都存在要同步的表或视图
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
19 3
|
2月前
|
资源调度 Oracle 关系型数据库
实时计算 Flink版产品使用合集之同步Oracle数据时,一张表产生了大量的连接数,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。

推荐镜像

更多