DDL(建表,建视图等)Data Definition Language
对于Oracle,一条transaction起始于DML语句,结束于 rollback,commit,DDL(事务自动提交),正常断开链接也自动提交;
create table
char:比varchar2效率高,但浪费空间
long:变长字符串,最高可到2G
varchar2:4k,4096
1.字段级约束
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;