oracle学习笔记5

oracle中的transaction
在sqlplus中执行update insert delete等 只要不进行commit，事务都不会结束 除非遇到DDL DCL语句 事务会自动提交
当正常断开连接的时候 事务自动提交
当非正常断开的时候 事务自动回滚


创建表

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)
)

约束条件也是对象 例如下面的 stu_name_nn

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)
)

唯一约束

unique

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) unique
)


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)
)

加约束 加在字段后面 成为字段约束
加在表后面 称为表级约束
约束可以起名字 不起名字 oracle自动帮忙取

主键

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


外键
create table class
(
id number(4) primary key.
name varchar2(20) not null
)

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_id_pk primary key (id),
constraint stu_name_email_uni unique(email,name)
)

表级外键
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_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key (id),
constraint stu_name_email_uni unique(email,name)
)


修改表

增加字段 alter table stu add (addr varchar2(100))
删除字段
alter table stu drop (addr);
修改字段
alter table stu modify (addr varchar2(10))

修改表的约束条件
alter table stu drop constraint stu_class_fk
alter table stu add constraint stu_class_fk foreign key (class) references class(id)


oracle 数据字典表 desc dictionary

users_tables 查询当前用户拥有的表
查询用户表
select table_name from user_tables
查询用户名
select view_name from user_views
查询约束名
select constraint_name from user_constraints