建表语句
1、货物信息表
create table goods (
gid int not null primary key,
gname varchar2(20) not null unique,
gprice varchar2(10) not null,
count number not null,
prodate varchar2(10) not null,
intime varchar2(10),
outtime varchar2(10));
2、管理员表
create table management (
mid int not null primary key,
mname varchar2(20) not null,
mtel varchar2(11) not null,
msex varchar2(10) not null check(msex='male' or msex='female'),
gid int,
foreign key(gid) references goods(gid));
3、入库信息表
create table instorage (
inid int not null primary key,
gname varchar2(20) not null,
intime varchar2(10),
incount number not null,
gid int,
mid int,
foreign key(gid) references goods(gid),
foreign key(mid) references management(mid));
4、出库信息表
create table outstorage (
outid int not null primary key,
gname varchar2(20) not null,
outtime varchar2(10),
outcount number not null,
gid int,
mid int,
foreign key(gid) references goods(gid),
foreign key(mid) references management(mid));
表结构
1、货物信息表
desc goods
名称
是否为空? 类型
------------------------- -------- ---------------
GID
GNAME
GPRICE
COUNT
PRODATE
INTIME
OUTTIME
NOT NULL NUMBER(38)
NOT NULL VARCHAR2(20)
NOT NULL VARCHAR2(10)
NOT NULL NUMBER
NOT NULL VARCHAR2(10)
VARCHAR2(10)
VARCHAR2(10)
2、管理员表
desc management;
名称
------------------ -------- ---------------------
是否为空? 类型
NOT NULL NUMBER(38)
NOT NULL VARCHAR2(20)
NOT NULL VARCHAR2(11)
NOT NULL VARCHAR2(10)
NUMBER(38)
MID
MNAME
MTEL
MSEX
GID
3、入库信息表
desc instorage;
名称
-------------------- -------- -----------------
是否为空? 类型
INID
GNAME
INTIME
INCOUNT
GID
MID
4、出库信息表
desc outstorage;
NOT NULL NUMBER(38)
NOT NULL VARCHAR2(20)
VARCHAR2(10)
NOT NULL NUMBER
NUMBER(38)
NUMBER(38)
名称
---------------- -------- ---------------
是否为空? 类型
OUTID
GNAME
OUTTIME
OUTCOUNT
GID
MID
NOT NULL NUMBER(38)
NOT NULL VARCHAR2(20)
VARCHAR2(10)
NOT NULL NUMBER
NUMBER(38)
NUMBER(38)
添加数据
values(1001,'
values(1002,'
values(1003,'
into
into
into
goods
goods
goods
1、货物信息表
insert
',10,100,'20140321','20140401','20140410');
insert
',4,500,'20140708','20140910','20140930');
insert
',1,1000,'20140724','20140911','20140930');
insert
',30,50,'20141003','20141010','20141017');
insert
',4,300,'20140101','20140301','20140320');
insert
',11,70,'20140531','20140616','20140701');
insert
',45,20,'20140217','20140430','20140519');
goods
goods
goods
goods
into
into
into
into
values(1004,'
values(1005,'
values(1006,'
花
薯
values(1007,'
康
师
傅
杏
葡
仁
萄
旺
旺
QQ
蛋
光
饼
糖
糖
酥
生
片
面
2、管理员信息表
insert into management values(101,'张三','18822234567','male',1001);
insert into management values(102,'张三','18822234567','male',1002);
insert into management values(103,'李四','18723456789','female',1003);
insert into management values(104,'李四','18723456789','female',1004);
insert into management values(105,'王五','13484251700','female',1005);
insert into management values(106,'王五','13484251700','female',1006);
insert into management values(107,'赵六','15067892301','male',1007);
3、入库信息表
insert into instorage values(1002001,'杏仁饼','20140501',50,1001,101);
insert into instorage values(1002002,'QQ 糖','20141022',100,1003,101);
insert into instorage values(1002003,'花生','20140418',100,1005,104);
insert into instorage values(1002004,'薯片','20140819',30,1006,103);
insert
','20140530',20,1007,103);
values(1002005,' 康 师 傅 面
instorage
into
outstorage
into
into
4、出库信息表
insert
','20141010',200,1002,102);
insert
','20141001',500,1003,103);
insert
','20141022',20,1004,102);
insert
','20140401',200,1005,101);
into
into
outstorage
outstorage
outstorage
values(80020311,' 葡 萄 糖
values(80020312,'QQ
糖
values(80020313,' 蛋 光 酥
values(80020314,'
花
生
insert
','20140612',20,1007,104);
outstorage
into
values(80020315,' 康 师 傅 面
数据
1、货物信息表
select * from goods;
GID GNAME
GPRICE
COUNT PRODATE
INTIME
OUTTIME
---------- ---------- ---------- ----- ---------- ---------- ----------
20140410
20140930
20140930
20141017
20140320
20140701
20140519
10
1001 杏仁饼
1002 葡萄糖
4
1003 旺旺 QQ 糖 1
1004 蛋光酥
30
4
1005 花生
1006 薯片
11
1007 康师傅面 45
100 20140321
500 20140708
1000 20140724
50 20141003
300 20140101
70 20140531
20 20140217
20140401
20140910
20140911
20141010
20140301
20140616
20140430
2、管理员信息表
select * from management;
MSEX
MTEL
MID MNAME
GID
---------- -------------------- ----------- ---------- ----------
1001
1002
1003
1004
1005
1006
1007
18822234567 male
18822234567 male
18723456789 female
18723456789 female
13484251700 female
13484251700 female
15067892301 male
101 张三
102 张三
103 李四
104 李四
105 王五
106 王五
107 赵六
3、入库信息表
select * from instorage;
INTIME
INCOUNT
INID GNAME
MID
---------- ------------- ---------- ---------- ---------- ----------
101
101
104
103
103
1002001 杏仁饼
1002002 QQ 糖
1002003 花生
1002004 薯片
1002005 康师傅面
20140501
20141022
20140418
20140819
20140530
1001
1003
1005
1006
1007
50
100
100
30
20
GID
4、出库信息表
select * from outstorage;
OUTTIME
OUTCOUNT
OUTID GNAME
MID
---------- ------------- ---------- ---------- ---------- ----------
102
103
101
104
102
80020311 葡萄糖
80020312 QQ 糖
80020314 花生
80020315 康师傅面
80020313 蛋光酥
20141010
20141001
20140401
20140612
20141022
1002
1003
1005
1007
1004
200
500
200
20
20
GID
表与表的连接
goods 表与 instorage 表
select g.gid,i.gname,i.incount,g.count from goods g,instorage i where
g.gid=i.gid;
INCOUNT
GID GNAME
COUNT
---------- -------------------- ---------- ----------
100
1000
300
70
20
1001 杏仁饼
1003 QQ 糖
1005 花生
1006 薯片
1007 康师傅面
50
100
100
30
20
goods 表与 management 表
select g.gid,m.mname,m.mtel,g.gname,g.gprice from goods g,management m
where g.gid=m.gid;
GID MNAME
MTEL
GNAME
GPRICE
---------- -------- ----------- -------------------- -------
10
4
3
30
4
11
45
18822234567 杏仁饼
18822234567 葡萄糖
18723456789 旺旺 QQ 糖
18723456789 蛋光酥
13484251700 花生
13484251700 薯片
15067892301 康师傅面
1001 张三
1002 张三
1003 李四
1004 李四
1005 王五
1006 王五
1007 赵六
goods 表与 outstorage 表
select
g,outstorage o where
g.gid=o.gid;
g.gid,g.gname,g.count,o.outtime,o.outcount
from
goods
GID GNAME
OUTCOUNT
---------- ---------- ---------- ---------- ----------
200
COUNT OUTTIME
1002 葡萄糖
1003 旺旺 QQ 糖
1004 蛋光酥
1005 花生
500 20141010
1000 20141001
50 20141022
300 20140401
500
20
200
1007 康师傅面
20 20140612
20
四张表的连接
select g.gid"货物编号",g.gname"货物名称",g.gprice"货物单价",g.count"
数 量 ",i.incount" 入 库 数 量 ",o.outcount" 出 库 数 量 ",m.mname" 管 理 员 姓 名
",m.mtel"管理员电话" from goods g,management m,instorage i,outstorage o
where g.gid=m.gid and g.gid=i.gid and g.gid=o.gid;
货物编号 货物名称 货物单价
数量 入库数量 出库数量 管
理员姓名 管理员电话
---------- ---------- ---------- ---------- ---------- ----------
---------- -----------
1003 旺旺 QQ 糖
18723456789
1005 花生
13484251700
四
五
1
4
1000
100
500 李
300
100
200 王
1007 康师傅面
15067892301
45
20
20
20 赵
六
create table storage as select g.gid" 货 物 编 号 ",g.gname" 货 物 名 称
",g.gprice"货物单价",g.count"数量",i.incount"入库数量",o.outcount"出
库数量",m.mname"管理员姓名",m.mtel"管理员电话" from goods g,management
m,instorage i,outstorage o where g.gid=m.gid and g.gid=i.gid and
g.gid=o.gid;
select * from storage;
货物编号 货物名称 货物单价
数量 入库数量 出库数量 管
理员姓名
---------- ---------- ---------- ---------- ---------- ----------
----------
管理员电话
-----------
1
4
45
1000
100
500 李
300
100
200 王
20
20
20 赵
1003 旺旺 QQ 糖
四
18723456789
1005 花生
五
13484251700
1007 康师傅面
六
15067892301
建立索引
create index goods_gname_idx on goods(gname);//有约束 primary key,unique
时自动建索引
create index goods_count_idx on goods(count);
create index management_mname_idx on management(mname);
create index instorage_incount_idx on instorage(incount);
create index outstorage_outcount_idx on outstorage(outcount);
原因:
在 select 操作占大部分的表上创建索引;
在 where 子句中出现最频繁的列上创建索引;
在选择性高的列上创建索引 ;
建立视图
授权
conn sys_sys as sysdba;
输入口令:sys
已连接。
grant create view to scott;
授权成功。
create view a as select 货物编号,货物名称,货物单价 from storage;
select * from a;
货物编号 货物名称
货物单价
---------- ---------- ----------
1003 旺旺 QQ 糖
1005 花生
1007 康师傅面
1
4
45
create view b as select g.gid,g.gname,g.count,o.outtime,o.outcount from
goods g,outstorage o where g.gid=o.gid;
select * from b;
GID GNAME
OUTCOUNT
---------- ---------- ---------- ---------- ----------
200
COUNT OUTTIME
1002 葡萄糖
1003 旺旺 QQ 糖
1004 蛋光酥
1005 花生
1007 康师傅面
500 20141010
1000 20141001
50 20141022
300 20140401
20 20140612
500
20
200
20
create view c as select gname from goods where gprice=(select gprice from
goods where gname='葡萄糖');
select * from c;
GNAME
----------
葡萄糖
花生
查询与价格与葡萄糖相等的货物名称
select gname from goods where gprice=(select gprice from goods where
gname='葡萄糖');
GNAME
----------
葡萄糖
花生
利用替代变量进行数据的插入:
into
values(1008,' 伊 利 优 酸 乳
打开控制台:
set serveroutput on;
代码:
create or replace procedure pro is
begin
insert
',30,30,'20141112','20141116','20141120');
end;
/
过程已创建。
exec pro;
PL/SQL 过程已成功完成。
select * from goods;
goods
GID GNAME
GPRICE
COUNT PRODATE
INTIME
OUTTIME
---------- ---------- ---------- ---------- ---------- ----------
----------
1001 杏 仁饼
20140410
1002 葡萄 糖
20140930
1003 旺旺 QQ 糖
20140930
1004 蛋光 酥
20141017
1005 花生
20140320
1006 薯片
20140701
1007 康 师傅 面
20140519
1008 伊 利 优 酸 乳
20141120
10
4
1
30
4
11
45
30
100 20140321
20140401
500 20140708
20140910
1000 20140724
20140911
50 20141003
20141010
300 20140101
20140301
70 20140531
20140616
20 20140217
20140430
30 20141112
20141116