--创建表(非空、唯一、主键、外键、check)
create table backtable as select * from oldtable;--备份表
create table tablename(
colname1 varchar2(num),--varchar2不定长,省空间,最多4K,4096B
colname2 char(num),--char定长,效率高
colname3 number(p,s),
colname4 date,
colname5 long,--变长字符串
);
create table stu(
id number(6),
name varchar2(20) constraint stu_name_nn not null,
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar2(50) constraint stu_email_uni unique,--两个空值不认为重复
constraint stu_id_pk primary key (id),--非空且唯一,逻辑上唯一代表一条记录
constraint stu_class_fk foreign key (class) references class(id),--被参考的字段必须是主键
constraint stu_name_email_uni unique(name,email)
);
create table class(
id number(4) primary key,
name varchar2(20) not null
);
--修改表结构
alter table tablename add(cloname coltype);
alter table stu add(addr varcher2(100));
alter table tablename drop (cloname);
alter table stu drop (addr);
alter table tablename modify(cloname type);--有数据了再更改精度不能降低
alter table stu modify (addr varchar2(50));
alter table tablename drop constraint constraintname;
alter table stu drop constraint stu_class_fk;
alter table tablename add constraint constraintname foreign key (colname) references tablename (colname)
alter table stu add constraint stu_class_fk foreign key (class) references class (id);
drop table tablename;
mysql中show create table tablename可以查看对应的表创建语句,继而可以查看系统给无名约束指定的名字
--创建视图 view
就是一个子查询,简化查询,增加了维护成本,保护私有数据;视图可以用来更新数据,但是不常用
create view as(
···
)
create view v$_stu as select id,name,age from stu;