-- 查询所有序列
select * from user_sequences;
-- 删除序列
DROP SEQUENCE 序列名;
DROP SEQUENCE NUM_SEQ;
-- 创建表
/*==============================================================*/
/* Table: sequence_test */
/*==============================================================*/
create table sequence_test
(
seq_num NUMBER(10) not null,
user_name VARCHAR2(100),
user_age NUMBER(3),
user_value VARCHAR2(10),
constraint PK_WAS_APP_JVM primary key (seq_num)
)
-- 添加备注(即使没有表也可添加备注,建表后自动生效)
comment on table sequence_test
is '序列测试表'
/
comment on column sequence_test.seq_num
is '流水号'
/
comment on column sequence_test.user_name
is '用户名'
/
comment on column sequence_test.user_age
is '用户年龄'
/
comment on column sequence_test.user_value
is '用户其他属性'
/
-- 删除备注
-- 创建序列
create sequence num_seq
increment by 1
start with 1000000000
maxvalue 9999999999
minvalue 1000000000
nocycle
order
*注 increment by 1 每次增长1
-- 插入数据
insert into sequence_test (seq_num,user_name,user_age,user_value) values (num_seq.nextval,'111',111,4);
-- 查询数据
select * from sequence_test;
-- 删除表
drop table sequence_test;
--重命名表
ALTER TABLE table_name RENAME TO new_table_name;
--修改列的名称
ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name;
--删除一个字段
alter table table_name drop column column_name;
1、创建表的同时创建主键约束
(1)无命名
create table student (
studentid int primary key not null,
studentname varchar(8),
age int);
(2)有命名
create table students (
studentid int ,
studentname varchar(8),
age int,
constraint yy primary key(studentid));
--删除表中已有的主键约束
(1)无命名
可用 SELECT * from user_cons_columns;
查找表中主键名称得student表中的主键名为SYS_C002715
alter table student drop constraint SYS_C002715;
--(2)有命名
alter table students drop constraint yy;
--向表中添加主键约束
alter table student add constraint pk_student primary key(studentid);