Oracle DDL SQL语句总结

DDL（建表，建视图等）Data Definition Language

create table t(a varchar2(10));
desc t;

drop table t;

事务transaction（一系列操作，要么同时完成，要么同时都不完成）
对于Oracle，一条transaction起始于DML语句，结束于 rollback,commit,DDL（事务自动提交）,正常断开链接也自动提交;

create table
char：比varchar2效率高,但浪费空间
long：变长字符串，最高可到2G
varchar2：4k，4096

create table stu
(
id number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1, --默认值
class number(4),
email varchar2(50)
);

drop table stu;

constraint
1.字段级约束

create table stu
(
id number(6),
name varchar2(20) constraint stu_name_nn not null, --（constraint）非空约束
sex number(1),
age number(3),
sdate date,
grade number(2) default 1, --默认值
class number(4),
email varchar2(50) unique --唯一约束,可插null
);

2.表级约束

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_name_email_uni unique(email,name)
);

3.主键约束（不能为空，唯一。逻辑意义：单独的代表一条记录）

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),
email varchar2(50),
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email,name)
);

4.外键约束（建立在两个字段上）

create table class
(
id number(4) primary key,--被参考字段（被参考字段必须是主键）
name varchar2(20) not null
);

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) ,--references class(id),--参考字段
email varchar2(50),
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email,name)
);

insert into class values(1000,'c1');
insert into stu(id,name,class,email) values (1,'a',1000,'a');
insert into stu(id,name,class,email) values (1,'a',1001,'a'); --failed

alter 修改表结构

alter table stu add(addr varchar2(100));
alter table stu drop (addr);
alter table stu modify(addr varchar2(150));

delete from class;
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fk foreign key(class) references class(id); 

数据字典表

desc user_tables;

select table_name from user_tables;--当前用户有哪些表
select view_name from user_views;--当前用户有哪些视图
select constraint_name from user_constraints;--当前用户有哪些约束
select constraint_name,table_name from user_constraints;

select table_name from dictionary;
select table_name from dictionary where table_name like 'USER%';

indexes 牢牢记住

create index idx_stu_email on stu (email);
drop index idx_stu_email;

select index_name from user_indexes;
读起来更快，修改起来更慢。

views
就是一个子查询

create view v$_stu as select id,name,age from stu;--查看权限

sequence 独一无二的数，自动递增

create table article
(
id number,
title varchar2(1024),
cont long
);

GRANT CREATE ANY sequence TO yincheng;

create sequence seq;
select seq.nextval from dual;

drop sequence seq;