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中创建和管理表详解

相关推荐