--创建表空间
create tablespace itcast297
datafile 'c:/itcast297.dba'
size 100m
autoextend on
next 10m;
--创建用户
create user itcast297 identified by itcast297
default tablespace itcast297;
--赋予用户dba权限
grant dba to itcast297;
/*
oracle数据库数据类型
字符类型:
char:固定长度类型
varchar2:可变长度类型,可保存1333个汉字
数值类型:
number(3):999
number(3,2):9.99
日期类型:
date:相当于MySQL的datetime精确到秒
timestamp:精确到秒的后九位
大数据类型:
long:大数据字符类型2G
clob:存放数据类型,4G
Blob:存放二进制数据,4G
*/
--创建表空间
create table person(
pid number,
pname varchar2(50)
)
--增加一条数据
insert into person values(1,'abcv');
select * from person;
--修改一条数据
update person set pname='aaa' where pid=1;
commit;
--删除一条数据
delete from person where pid=1;
commit;
--============约束
drop table person;
--创建person表,pid为主键,
create table person (
pid number primary key,
pname varchar2(20),
gender number
)
--constraint 主键名 primary key(字段) 方式
create table person (
pid number,
pname varchar2(50),
gender number,
constraint PK_PID primary key (pid)
)
--非空约束
create table person(
pid number,
pname varchar2(50) not null,
gender number
)
--唯一约束
create table person(
pid number,
pname varchar2(50) unique,
gender number
)
insert into person(pid,pname,gender) values(1,'asd',0);
commit;
insert into person(pid,pname,gender) vaules(2,'asd',1);
commit;--不允许插入,因为pname为唯一值
--check约束,check(列名 in (值))
create table person(
pid number,
pname varchar2(50),
gender number check(gender in (0,1))
)
insert into person(pid,pname,gender) values(1,'asd',2);
commit;--报错check,只有gender等于1,0才能插入
--===外键约束
--1.创建orders表,字段为order_id(主键),total_price
create table orders (
order_id number primary key,
total_price number(8,2)
)
--2.创建order_detail表,字段为detal_id(主键),order_id(外键),item_name
create table order_detail(
detal_id number primary key,
order_id number,
item_name varchar2(50),
constraint FK_ORDER_ORDER_ID foreign key(order_id) references orders(order_id)
)
--3.为orders和order_detail表插入数据
insert into orders(order_id,total_price) values(1,100);
commit;
insert into order_detail(detal_id,order_id,item_name)
values(1,1,'phone');
commit;
--删除orders和order_detail表中的数据
--先删除子表数据后删除主表
delete from order_detail where detal_id=1;
delete from orders where order_id=1;
commit;
select * from order_detail;
select * from orders;
--==oracle数据库,回滚点
--设置savepoint 回滚点,再次修改数据后用rollbeack回滚数据
select * from person;
insert into person(pid,pname,gender) values(1,'abc',1);
savepoint p1;
update person set pname='cba';
rollback to p1;
commit;--最后提交的是abc