--创建一个新的chulai表
create table chulai
(xingming varchar2(14), xingbie varchar(2), ninaling number(2));
select * from chulai;
alter table chulai
add (gongzuo varchar(14))
insert into chulai
values('阿晨','男',18,'掏粪',01);
insert into chulai
values('船长','男',19,'开掏粪车',02);
insert into chulai
values('阿帅','男',20,'卖粪人',03);
--增加一个新的列
alter table chulai
add (bianhao number(2)unique)--把编号设置为唯一属性
--删除编号这一列
alter table chulai
drop (bianhao)
--删除行
delete chulai
where rownum = 1
--创建另一个chuqu表
create table chuqu
(bianhao number(2),bumen varchar(14)not null,mengxiang varchar(14))
select * from chuqu
insert into chuqu
values(01,'掏粪工','掏光所有粪');
insert into chuqu
values(02,'开车','掏粪车不漏');
insert into chuqu
values(03,'销售','卖光所有粪');
--用where条件将两个表进行连接
select a.bianhao, a.xingming,a.xingbie,a.ninaling,a.gongzuo,b.bumen,b.mengxiang
from chulai a, chuqu b
where a.bianhao = b.bianhao
--用Join on连接两个表
select a.bianhao, a.xingming,a.xingbie,a.ninaling,a.gongzuo,b.bumen,b.mengxiang
from chulai a join chuqu b
on a.bianhao = b.bianhao
--创建第3个表
create table suanle
(bianhao number(2)unique,sal number(8)not null,jiangjin number(8),ruzhi date)
select * from suanle
insert into suanle
values(01,100000,200000,to_date('2012-08-08','yyyy-mm-dd'));
insert into suanle
values(02,10000,20000,to_date('2014-07-07','yyyy-mm-dd'));
insert into suanle
values(03,1000,100000,to_date('2016-06-06','yyyy-mm-dd'));
delete suanle
where rownum = 1
--用left out join 连接3个表
select a.bianhao, a.xingming,c.ruzhi,c.sal,c.jiangjin,a.xingbie,a.ninaling,a.gongzuo,b.bumen,b.mengxiang
from chulai a full join chuqu b
on a.bianhao = b.bianhao
join suanle c
on a.bianhao = c.bianhao
--创建一个jiaoyi表
create table jiaoyi
(商品编号 number(4)PRIMARY KEY, 商品名称 varchar2(14), 商品数量 number(4)，商品种类 varchar2(14),销售窗口 varchar2(2));
select * from jiaoyi --验证表是否创建成功
--添加商品信息
insert into jiaoyi
values(001,'空调',118,'电器类','01');
insert into jiaoyi
values(002,'短袖',999,'服装类','02');
insert into jiaoyi
values(003,'巧克力',888,'食品类','02');
insert into jiaoyi
values(004,'水杯',777,'生活用品类','02');
insert into jiaoyi
values(005,'三体',888,'图书类','02');
--创建一个jiaoyi1表
create table jiaoyi1
(销售窗口 varchar2(2),收银员 varchar2(14),性别 varchar2(2),入职时间 date)
select * from jiaoyi1
--添加收银员信息
insert into jiaoyi1
values('01','小猫','女',to_date('2016-2-14','yyyy-mm-dd'));
insert into jiaoyi1
values('02','小狗','男',to_date('2015-8-14','yyyy-mm-dd'));
drop table jiaoyi1
--创建一个jiaoyi2表
create table jiaoyi2
(商品编号 number(4),进库量 number(4),出库量 number(4),售价 varchar2(20),进价 varchar2(20))
select * from jiaoyi2
--添加商品库存及价格
insert into jiaoyi2
values(001,500,200,to_char(8888,'L99999'),to_char(5000,'L99999'))--把价钱转换成¥格式
insert into jiaoyi2
values(002,1200,1000,to_char(120,'L99999'),to_char(100,'L99999'));
insert into jiaoyi2
values(003,1200,1000,to_char(244,'L99999'),to_char(150,'L99999'));
insert into jiaoyi2
values(004,1000,800,to_char(58,'L99999'),to_char(35,'L99999'));
insert into jiaoyi2
values(005,1200,1000,to_char(88,'L99999'),to_char(50,'L99999'));
--表之间的关联
select a.销售窗口,a.商品编号, a.商品名称,a.商品数量,a.商品种类,b.收银员,b.性别,b.入职时间
from jiaoyi a join jiaoyi1 b
on a.销售窗口 = b.销售窗口
--3表之间的关联
select a.销售窗口,a.商品编号, a.商品名称,a.商品数量,c.进库量,c.出库量,c.售价,c.进价,a.商品种类,b.收银员,b.性别,b.入职时间
from jiaoyi a join jiaoyi1 b
on a.销售窗口 = b.销售窗口
join jiaoyi2 c
on a.商品编号 = c.商品编号
