Oracle_基本语法

--用SYS账户登录
conn sys/scott as sysdba
--创建表空间
create tablespace epet_tablespace
datafile 'e:\oracleDB\epet_tablespace.dbf'
size 10m
autoextend on
--创建epet用户
create user epet identified by epet default tablespace epet_tablespace
--赋予权限
grant connect,resource to epet
--用epet登录
conn epet/epet
--创建表
create table master
(
       id number(10) primary key,
       loginid varchar2(20) not null,
       password varchar2(20) not null,
       status varchar2(10)
)
create table pet_type
(
       id number(10) primary key,
       name varchar2(20) not null,
       status varchar2(20)
)
create table pet
(
       id number(10) primary key,
       master_id number(10) not null,
       name varchar2(20) not null,
       type_id number(10) not null,
       health number(10) not null,
       love number(10) not null,
       prop1 varchar(20) not null,
       prop2 varchar(20) not null,
       prop3 varchar(20) not null,
       adopt_time timestamp not null,
       status varchar(20)
)
--创建表关系
alter table pet
add constraint master_fk foreign key (master_id) references master(id)
alter table pet
add constraint type_fk foreign key (type_id) references pet_type(id)
--创建序列
create sequence seq_epet
start with 1
increment by 1
nocache
--插入数据
--master表
insert into master values(seq_epet.nextval,'wangcheng','12345','1')
insert into master values(seq_epet.nextval,'xiaoshui','12345','1')
insert into master values(seq_epet.nextval,'xiaozhuzhu','12345','1')
--pet_type表
insert into pet_type values(seq_epet.nextval,'wangcheng','1')
insert into pet_type values(seq_epet.nextval,'xiaoshui','1')
insert into pet_type values(seq_epet.nextval,'xiaozhuzhu','1')
--pet表
insert into pet values(seq_epet.nextval,1,'xiaoxiao',4,'100','100','1','1','1',sysdate,'1')
insert into pet values(seq_epet.nextval,2,'xiaoxiao',5,'100','100','1','1','1',sysdate,'1')
insert into pet values(seq_epet.nextval,3,'xiaoxiao',6,'100','100','1','1','1',sysdate,'1')