oracle基础

创建表:
create table vendor_master
(
   vencode varchar2(5),
   venname varchar2(20),
   venadd1 varchar2(20),
   venadd2 varchar2(20),
   venadd3 varchar2(20)
);

更改表：
alter table vendor_master modify vencode varchar2(10);

查看表结构：
desc tablename

在表结构中添加新列：
alter table vendor_master add venadd4 varchar2(20);

在表中添加多个新列
alter table vendor_master add (venadd4 varchar2(20),venadd5 varchar(20));

在表中删除新列
alter table vendor_master drop column venadd4

在表中删除多个列
alter table vendor_master drop (venadd4,venadd5);

设置vendor_master表中venname字段不能为空
alter table vendor_master modify venname not null;

怎样给vendor_master表中vencode字段加上主键？
alter table vendor_master add constraint pk_vendor_master_vencode primary key(vencode);
alter table vendor_master drop constraint pk_vendor_master_vencode;

查看用户自定义的约束
select * from user_constraints;

select * from user_constraints where constraint_name=upper('pk_vendor_master_vencode');

select * from user_constraints where owner=upper('scott');

select * from user_constraints where table_name=upper('vendor_master');

alter table vendor_master add constraint uq_vendor_master_venname unique(venname);

alter table vendor_master add age int;

alter table vendor_master add constraint ck_vendor_master_age check(age>10 and age<20);

alter table vendor_master modify age default(15);

--创建外键
create table student
(
stuid int primary key,
stuname varchar2(20) unique,
stuage int check(stuage>=10 and stuage<=30)
);

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid);

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid) on delete cascade;

alter table borrow drop constraint fk_borrow_stuid;

--利用现有表创建新表
create table emp2 as select * from emp;

从其他表中选择数据插入到存在的emp3表中去
insert into emp3 select * from emp

--左连接
select * from emp e,dept d where e.deptno=d.deptno(+)
select * from emp e left join dept d on e.deptno=d.deptno;
--右连接
select * from emp e,dept d where e.deptno(+)=d.deptno
select * from emp e right join dept d on e.deptno=d.deptno;

select * from emp e full join dept d on d.deptno=e.deptno;

创建用户
conn sys/quan as sysdba;
create user AAA
identified by AAA;

--更改用户的密码
conn sys/quan as sysdba;
alter user AAA
identified by BBB;

--解除用户锁定状态
alter user AAA account unlock
--恢复用户锁定状态
alter user AAA account lock