Oracle编写小型商课数据库，以keep为例

最新推荐文章于 2024-07-19 10:25:53 发布

稚名miko

最新推荐文章于 2024-07-19 10:25:53 发布

阅读量382

点赞数 4

分类专栏： Oracle数据库文章标签： oracle 数据库

本文链接：https://blog.csdn.net/keadou/article/details/138902494

版权

Oracle数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

设计数据库，其流程一般如下所示：

明确设计理念——>明确需求——>绘制E-R图——>创建表格/添加约束——>检查表结构是否遗漏，并增删改表格——>添加数据——>进行DML和DQL语句的检查

E-R图设计

数据库设计的E-R图如下所示：

表的设计

需要添加的表主要为用户表、课程表、商品表，中间表为成绩表，商品推荐表，购物车表，并为其中部分字段田间主键约束，其建立过程如下所示。

--------------------- 用户表-----------------------
-- 创建用户表
create table kuser(
  id number(5) primary key, -- 给用户编号id添加主键约束
  name varchar2(32),-- 用户名
  password varchar2(32),-- 密码
  rank number(5)-- 会员等级
);


-------------------- 课程表-------------------------
-- 创建课程表
create table course(
  cid number(5) primary key,-- 给课程编号cid添加主键约束
  cname varchar2(32),-- 课程名
  crank varchar2(64),-- 课程等级
  tid number(5),-- 商品编号
  tname varchar2(32)-- 商品名
);

---------------------- 商品表--------------------------
-- 创建商品表
create table trade(
  tid number(5) primary key,-- 给商品表tid添加主键约束
  tname varchar2(32),-- 商品名
  price number(6,2),-- 商品价格，两位小数，共六位
  inv number(5),-- 库存
  sal number(5)-- 销售额
);

---------------------- 成绩表-----------------------
-- 创建成绩表
create table score(
  id number(5),-- 用户编号
  cid number(5),-- 课程编号
  score number(5,2)-- 成绩
);

-------------------- 商品推荐表---------------------
-- 创建课程内商品推荐表
create table recom(
  cid number(5),-- 课程编号
  tid number(5)-- 商品编号
);

----------------------- 创建购物车表--------------------
create table cort(
  id number(5),-- 用户编号
  tid number(5),-- 商品编号
  price number(6,2)-- 价格
);

添加约束条件

正规方法

根据需求分析，可以给各表添加外键约束，防止各表中出现未出现的数据。

-------------------- 课程表-------------------------
-- 建立商品编号关联到商品表的外键
ALTER TABLE COURSE
ADD CONSTRAINT COURSE_FK1 FOREIGN KEY
(
  TID 
)
REFERENCES trade
(
  TID 
)
ENABLE;

---------------------- 成绩表-----------------------
-- 建立用户编号关联到用户表的外键
ALTER TABLE SCORE
ADD CONSTRAINT SCORE_FK1 FOREIGN KEY
(
  ID 
)
REFERENCES KUSER
(
  ID 
)
ENABLE;
-- 建立课程编号关联到课程表的外键
ALTER TABLE SCORE
ADD CONSTRAINT SCORE_FK2 FOREIGN KEY
(
  CID 
)
REFERENCES COURSE
(
  CID 
)
ENABLE;

-------------------- 商品推荐表---------------------
-- 建立课程编号关联到课程表的外键
ALTER TABLE recom
ADD CONSTRAINT recom_fk1 FOREIGN KEY
(
  CID 
)
REFERENCES COURSE
(
  CID 
)
ENABLE;
-- 建立商品编号关联到商品表的外键
ALTER TABLE recom
ADD CONSTRAINT recom_fk2 FOREIGN KEY
(
  tid
)
REFERENCES trade
(
  tid
)
ENABLE;

----------------------- 创建购物车表--------------------
-- 建立用户编号关联到用户表的外键
ALTER TABLE cort
ADD CONSTRAINT cort_fk1 FOREIGN KEY
(
  id
)
REFERENCES kuser
(
  id
)
ENABLE;
-- 建立商品编号关联到商品表的外键
ALTER TABLE cort
ADD CONSTRAINT cort_fk2 FOREIGN KEY
(
  tid
)
REFERENCES trade
(
  tid
)
ENABLE;

偷懒方法

如果实在不想写（不会写）代码，可以在左侧数据库列表中，右键选中想要修改的表，在窗体内添加完修改后，在窗体左下角的DDL语句中查看具体的修改语句。

添加数据

简单多条数据

可以使用 insert 语句一条一条慢慢填写，例如，向用户表中添加一条数据。

insert into kuser(id,name,password,rank) values(1,'张三'，'123456'，3);

循环添加多条数据

假如需要测试大量数据，但对数据的内容要求不高的话，我更推荐使用这个方法，其中，需要理解dbms_random.value(c1,c2)语句。此语句是指在c1到c2中取随机值，但此随机值与c1和c2的格式有关，如果使用此语句，最好不要应用与varchar2类字段中，否则会经常出现数值过大超出容量的问题。

此外，如果需要保证用户自己的课程是不重复的，可能需要手动删除部分重复语句，我用distinct和限制条件都没实现相关要求。如果有大佬的话，希望能指点一二。

--------------------- 用户表-----------------------
-- 插入数据
begin
  -- 设置1-20的循环
  for x in 1..20
  loop
    -- 向用户表中插入（编号，用户名，密码，课程名）的数据
    insert into kuser values(x,'user'||x,123456,dbms_random.value(1,6));
  end loop;
  -- 提交数据
  commit;
end;

-------------------- 课程表-------------------------
-- 插入数据
-- 声明变量
declare
  m number(5);
  n number(5);
begin
  for x in 1..20
  loop
    -- 给变量赋随机值
    m :=dbms_random.value(1,6);
    n :=dbms_random.value(1,5);
    insert into course values(x,'course'||x,n,
    m,(select tname from trade t where t.tid=m));
  end loop;
  commit;
end;

---------------------- 商品表--------------------------
-- 插入数据
declare
  m number;
  n number;
  p number(6,2);
begin
  for x in 7..50
  loop
    p :=dbms_random.value(50,1500);
    m :=dbms_random.value(50,1500);
    n :=dbms_random.value(50,1500);
    insert into trade values(x,'trade'||x,p,m,n);
  end loop;
  commit;
end;

---------------------- 成绩表-----------------------
-- 插入数据
declare
  m number(5);
  n number(5);
  k number(5,2);
begin
  for x in 1..60
  loop
    -- ceil向上取整
    select k.id into m from kuser k where k.id=ceil(x/3);
    n :=dbms_random.value(1,20);
    k :=dbms_random.value(40,95);
    insert into score values(m,n,k);
  end loop;
  commit;
end;


-------------------- 商品推荐表---------------------
-- 添加数据
declare
  m number(5);
  n number(5);
begin
  for x in 1..20
  loop
    -- 多表联查，隐式内连接
    select co.tid into m from course co where co.cid=x;
    -- 子查询
    select t.inv into n from trade t where t.tid=(select co.tid from course co where co.cid=x);
    if
      n <> 0
    then
        insert into recom values(x,m);
    else
        insert into recom values(x,0);
    end if;
  end loop;
  commit;
end;

----------------------- 创建购物车表-----------------------
-- 添加数据
declare
  n number(5);
  p number(6,2);
begin
  for x in 1..20
  loop
    n :=dbms_random.value(1,50);
    -- into p 用于把查询出的price值赋给p，方便后续输出
    select t.price into p from trade t where t.tid=n;
    insert into cort values(x,n,p);
  end loop;
  commit;
end;

查询练习

根据这个数据库，自己稍微写了点查询的练习题，希望对各位有点帮助。

-- 1.查询购买trade1商品的用户数量是多少
select count(co.id) from cort co where co.tid=(select t.tid from trade t where t.tname='trade1');

-- 2.查询trade36商品的总销售额是多少
select sum(co.price) from cort co where co.tid=(select t.tid from trade t where t.tname='trade36');

-- 3.统计列印每个人的最高分和最低分,并按照学号的升序排序
select id,max(sc.score),min(score) from score sc group by id order by id;

-- 4.查询至少有一门课与编号为1的用户所学课程相同的用户的编号和用户名；
      -- 方法1：
select k.id,k.name from kuser k where k.id in
(select sc.id from score sc where sc.cid in(
(select sc.cid from score sc where sc.id=1)
)and sc.id <> 1
) order by k.id;
      -- 方法2：
select k.id,k.name from kuser k where k.id in
(select sc.id from score sc where sc.cid=any(
(select sc.cid from score sc where sc.id=1)
)and sc.id <> 1
) order by k.id;

-- 5.删除学习course1课的score 表记录；
delete from score sc where sc.cid=
(select cid from course where cname='course1');

-- 6.查询有课程成绩小于60 分的用户的编号和用户名；
select k.id,k.name from kuser k where k.id=any
(select sc.id from score sc where sc.score < 60);

-- 7.查询平均分小于60分的用户的编号和用户名；
select sc.id,k.name from score sc join kuser k on k.id=sc.id 
group by sc.id,k.name having avg(sc.score)<60
order by sc.id;

-- 8.查询每个用户最高和最低的分：以如下形式显示：用户id，用户名，最高分，最低分
select sc.id,k.name,max(sc.score),min(sc.score) from score sc 
join kuser k on k.id=sc.id group by sc.id,k.name order by sc.id;