Oracle 修改表结构命令:
1.添加新字段:
alter table table_name add (name varchar(20) default '默认值');
eg:
alter table userinfo add(address varchar(50))
2.修改表字段:
alter table table_name modify (name varchar2(30) default '默认值')
eg:
alter table userinfo modify (address varchar(20) default '中软')
3.删除表字段:
alter table table_name drop column column_name;
a): alter table userinfo set unused column address
b): alter table userinfo drop unused column
4.查看表结构
desc table_name
5.清空表中的数据
truncate table table_name
truncate table userinfo
注意区分: delete from table_name
-----------------------------------------------------------
1、创建约束
CREATE TABLE students
(student_id VARCHAR2(10) NOT NULL,
student_name VARCHAR2(30) NOT NULL,
college_major VARCHAR2(15) NOT NULL,
status VARCHAR2(20) NOT NULL,
state VARCHAR2(2),
license_no VARCHAR2(30)) TABLESPACE student_data;
2、创建主键
ALTER TABLE students
ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
3、创建Unique约束
ALTER TABLE students
ADD CONSTRAINT uk_students_license
UNIQUE (state, license_no)
4、创建Check约束
ALTER TABLE students
ADD CONSTRAINT ck_students_st_lic
CHECK ((state IS NULL AND license_no IS NULL) OR
(state IS NOT NULL AND license_no is NOT NULL));
添加check约束
alter table emp add constraint con check(dept_salary>0);
con 为约束名, dept_salary为字段名
5、创建外键约束
ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);
6. 约束..
Alter table table_name add constrants BID primary key (bookno);
ALERT TABLE table_name MODIFY( column1 PRIMARY KEY);
1、创建表的同时创建主键约束
(1)无命名 create table student (studentid int primary key not null,
studentname varchar(8),age int);
truncatedelete和drop的区别(2)有命名 create table students (studentid int ,studentname varchar(8),
age int,constraint yy primary key(studentid));
2、删除表中已有的主键约束
(1)有命名 alter table students drop constraint yy;
(2)无命名 可用 SELECT * from user_cons_columns where ..;
查表中主键名称得student表中的主键名为SYS_C002715
alter table student drop constraint SYS_C002715;
(3) 使约束失效:
alter table tbl_employee disable constraint fk_emp;
删除约束:
alter table tbl_department drop constraint pk_dept;
查询约束:
select CONSTRAINT_NAME from user_constraints where table_name='TBL_EMPLOYEE';
select CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns where table_name='TBL_EMPLOYEE';
3. 删除表.
Drop table table_name;
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论