您当前的位置: 首页 > 数据库教程 > Oracle教程 > Oracle中创建和管理表详解

Oracle中创建和管理表详解

作者:不详 来源:网络 发布时间: 2014-09-01 11:45 点击:
以下是对Oracle中的创建和管理表进行了详细的分析介绍,需要的朋友可以过来参考下 SQL /* SQL 对于表的操作: 创建表,修改表(添加新的列,改变当前某些列,删除列),删除表 SQL 创建表: create table(需要create table的权限) SQL 修改表: alter table tablename add/mo

Oracle中创建和管理表详解

  以下是对Oracle中的创建和管理表进行了详细的分析介绍,需要的朋友可以过来参考下

  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
分享到:
本文"Oracle中创建和管理表详解"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4