Oracle 常用文法

標籤：ar   for   sp   on   ad   ef   size   res   ui   

--建立資料表空間
create tablespace myschool
datafile ‘D:/ms.dbf‘
size 50M

--建立使用者
create user t87 identified by t87;

--授予許可權(dba,connect,resource)
grant connect,resource to t87;

--撤銷許可權
revoke connect,resource from t87;

--修改使用者密碼
alter user t87 identified by a;

--鎖定使用者
alter user t87 account lock;

--解鎖
alter user t87 account unlock;

--刪除使用者
drop user t87;

--建表
--主鍵約束：primary key
--非空約束:not null
--檢查約束check (sex in(‘男‘,‘女‘))
--唯一約束:unique
--預設約束：default 20
--外鍵約束：constraint student_sgroup_fk foreign key(gid) references sgroup(gid)
create table student(
stuid number(8) primary key,
stuname varchar2(50) not null,
sex varchar2(10) check (sex in(‘男‘,‘女‘)),
seat number(8) unique,
age number(8) default 20,
gid number(8) not null,
constraint student_sgroup_fk foreign key(gid) references sgroup(gid)
);

alter table student add constraint student_sgroup_fk foreign key(gid) references sgroup(gid);
create table sgroup(
gid number(8) primary key,
gname varchar2(50) not null
);

--添加列
alter table student add email varchar2(50);

--修改列
alter table student modify email varchar2(100);

--刪除列
alter table student drop column email;

--刪除表
drop table student purge;

select * from tab;

select * from student for update;
select * from sgroup for update;

-- 建立序列
create sequence stuseq;

select stuseq.nextval from dual;
select stuseq.currval from dual;

select
from biao1 ,biao2
where biao1.ziduan=biao2.ziduan

insert into student
(stuid, stuname, sex, seat, age, gid)
values
(stuseq.nextval, v_stuname, v_sex, v_seat, v_age, v_gid);

Oracle 常用文法