1.表操作
创建表 CREATE TABLE test1 (column_name datetype,co_n dp);
[主键 CONSTRAINT id_pk PRIMARY KEY(id)
[外键 col2 data_type REFERENCES table1(col1)
[唯一约束 CONSTRAINT id_uk UNIQUE; 该列所有字段不可以重复
[表空间 TABLESPACE abc;
CREATE TABLE test TABLESPACE abc AS SELECT * FROM test2;
删除表 DROP TABLE test1 CASCADE CONSTRAINTS PURGE;
改表名 ALTER TABLE test6 RENAME TO test3;
2.列操作
增加列
ALTER TABLE test ADD hobby nvarchar2(20);
删除列 ALTER TABLE test DROP COLUMN hobby;
更新列 UPDATE test SET id = 2 WHERE ename=’smith’;
UPDATE test SET id =(SELECT id FROM test2 WHERE empno=7369) WHERE ename=’smith’;
清空列 UPDATE test SET id=null;
改列名 ALTER TABLE test RENAME COLUMN id TO id1;
改列数据类型 ALTER TABLE test MODIFY id integer;
设置not null ALTER TABLE test MODIFY ename NOT NULL;
删除not null ALTER TABLE test MODIFY ename NULL;
check约束 ALTER TABLE test ADD CONSTRAINT emp_chk CHECK(empno>7000);
删除约束 ALTER TABLE test DROP CONSTRAINT emp_chk ;
设置主键 ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY(ename);
删除主键 ALTER TABLE test DROP CONSTRAINT id_pk;
设置外键 alter table new add constraint new_test3 foreign key(eid) references test3(id);
//外键条件,一个表的主键和另一个表的非主键连接
删除外键 alter table new drop constraint new_test3;
唯一约束 ALTER TABLE test ADD UNIQUE(id);
删除唯一约束 ALTER TABLE test DROP UNIQUE(id);
改列默认值 ALTER TABLE test MODIFY(id DEFAULT 22);
3.行操作
添加行 INSERT INTO test VALUES('smith',7369,5);
INSERT INTO test(id,ename) VALUES(5,’smith’);
INSERT INTO test(id,ename) SELECT id,ename FROM emp;
删除行 DELETE FROM test WHERE id=3;
删除所有行 TRUNCATE TABLE test;
DELETE FROM test;
修改行 UPDATE test SET dpid=6,ename='direc' WHERE id=7369;
UPDATE test SET dpid=(SELECT dpid FROM test2 WHERE ename='kare') WHERE id=7369;
4.表空间
create tablespace abc
datafile ‘/u01/app/oracle/oradata/TestDB11/catalog.dbf’
size 100M
extent management local autoallocate //扩展自动管理
segment space management auto;
drop tablespace abc including contents cascade constraints;
1.同时删除表空间中的数据
2.同时删除完整性限制
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30077753/viewspace-1408879/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30077753/viewspace-1408879/