oracle_sql_1

--创建表空间
create tablespace myspace
datafile ‘D:/or/myfile.dbf‘
size 50M;


--创建用户
--create  user 用户名 identified 密码;
create user t95 identified by t95;

--为用户授权
--connect:允许用户登录的权限
--resource:允许用户操作表的权限
--dba:管理员权限
grant connect,resource to t95;

--删除权限
revoke resource from t95;

--删除用户
drop user t95 purge;

--创建表
--主键：primary key
--非空约束：not null
create table cls(
cid number(8) primary key,
cname varchar2(50) not null
);

--唯一约束：unique
--检查约束:check (sex in(0,1))
create table student(
stuid number(8) primary key,
stuname varchar2(50) unique,
sex number(2) check (sex in(0,1)),
birthday timestamp default sysdate,
cid number(8),
constraint student_cls_fk foreign key(cid) references cls(cid)
);

alter table student add constraint student_cls_fk foreign key(cid) references cls(cid);


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

--修改列
alter table student modify stupwd varchar2(20);

--删除列
alter table student drop column stupwd;

--创建序列
create sequence stuseq;

--查看序列
select stuseq.nextval,stuseq.currval from dual;
select stuseq.currval from dual;

insert into cls(cid,cname) values(stuseq.nextval,‘T95‘);

--提交事务
commit;

--回滚事务
rollback;

oracle_sql_1

标签：