create table stu ( id number(6) primary key, 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) references class(id), email varchar2(50), constraint stu_name_email_nui unique(email, name) )
create table class ( id number(4) primary key, name varchar2(20) not null );
--五个约束条件 -- 为空 name varchar2(20) constraint stu_name_nn not null, --stu_name_nn 也可以不起名字
-- 修改现有表的结构 alter table stu add(addr varchar2(100)); alter table stu drop (addr); alter table stu modify(addr varchar2(150)); alter table stu drop constraint stu_class_fk
-- 删除一张表 drop table table_name;
-- 数据字典表 desc user_tables; select table_name from user_tables; select view_name from user_views; select constraint_name from user_constraints; select dictionary; select constraint_name, table_name from user_constraints; select * from dictionary where table_name like 'USER_TABLE%';
-- 索引 -- 加主键和唯一约束时,自动建立索引 -- 查询速度快了,插入速度慢了 create index idx_stu_email on stu(email); drop index idx_stu_email;
-- 序列 create sequence seq; select seq.nextval from dual; drop seq;
--插入数据前先保存 create table emp2 as select * from emp;
sqlplus sys/password as sysdba
alter user scott account unlock;
grand create table, create view to scott;
--建立删除用户 导出表 SQL> conn sys/orcl as sysdba; drop user liuchao cascade;