Oracle中创建和管理表详解
程序员文章站
2023-11-25 16:45:52
sql> /*sql> 对于表的操作: 创建表，修改表(添加新的列，改变当前某些列，删除列),删除表sql> 创建表: create table(需要cr...
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
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