最近在某鱼上有小伙伴让我帮他设计一个图书管理系统的数据库,从建库到简单的数据库,现在写完了,分享给大家哦!
我们先来看看他的要求,如下图:
根据以上需求我们来编写我们的SQL语句:
1. 创建数据库用户
--创建TEST用户
CREATE USER TEST PROFILE "DEFAULT" IDENTIFIED BY "TEST"
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
--给TEST权限赋予用户
GRANT "CONNECT" TO TEST WITH ADMIN OPTION;
GRANT "RESOURCE" TO TEST WITH ADMIN OPTION;
GRANT "DBA" TO TEST WITH ADMIN OPTION;
2. 创建用户表
--创建用户表
create table customer
(
userid integer,
xm varchar2(500),
xb varchar2(500),
tel varchar2(500)
);
--给用户表添加字段注释
comment on column customer.userid is '用户编号';
comment on column customer.xm is '姓名';
comment on column customer.xb is '性别';
comment on column customer.tel is '电话';
3. 创建图书分类表
--创建图书分类表
create table bookcategory
(
category_id integer,
category_name varchar2(500)
);
--给图书分类表的字段加上注释
comment on column bookcategory.category_id is '类别编号';
comment on column bookcategory.category_name is '类别名称';
4. 创建图书表
--创建图书表
create table book
(
bookid integer,
bookname varchar2(500),
bookauthor varchar2(500),
bookpublisher varchar2(500),
bookallnum integer,
bookfreenum integer,
bookcategoryid integer
);
--给图书表加注释
comment on column book.bookid is '图书编号';
comment on column book.bookname is '书名';
comment on column book.bookauthor is '作者';
comment on column book.bookpublisher is '出版社';
comment on column book.bookallnum is '总量';
comment on column book.bookfreenum is '剩余量';
comment on column book.bookcategoryid is '分类';
5. 创建借阅信息表
create table borrow
(
id integer,
book_id integer,
user_id integer,
borrow_time varchar2(500),
borrow_limit varchar2(500),
back_time varchar2(500)
);
--给借阅信息表加上注释
comment on column borrow.id is '编号';
comment on column borrow.book_id is '图书编号';
comment on column borrow.user_id is '用户编号';
comment on column borrow.borrow_time is '借阅时间';
comment on column borrow.borrow_limit is '借阅期限';
comment on column borrow.back_time is '归还时间';
6. 以下是图书分类的维护,对图书分类的增删改查
1.查询所有的分类
select * from bookcategory;
2.添加图书分类
insert into bookcategory(category_id,category_name) values('1','小说类');
insert into bookcategory(category_id,category_name) values('2','文学类');
3.删除某一图书分类
delete from bookcategory where category_name='小说类';
4.修改图书分类,将小说类改为科学类
update bookcategory set category_name='科学类' where category_name='小说类';
5.查询指定名称的图书分类
select * from bookcategory where category_name='文学类';
7. 图书信息的维护
1.查询所有的图书信息
select * from book;
2.添加图书信息
insert into book(bookid,bookname,bookauthor,bookpublisher,bookallnum,bookfreenum,bookcategoryid) values('1','钢铁是怎样练成的','老文学家','新华出版社',50,20,1);
3.删除指定名称的图书信息
delete from book where bookname='钢铁是怎样练成的';
4.修改指定名称图书的作家名称<备注:需要修改哪个字段就改哪个字段>
update book set bookauthor='吴青峰' where booknaem='钢铁是怎样练成的';
5.查询指定名称的图书信息
select * from book where bookname='钢铁是怎样练成的';
8. 用户信息的维护
1.查询所有的用户信息
select * from customer;
2.添加用户
insert into customer(userid,xm,xb,tel) values (1,'test','password1','155554747933');
insert into customer(userid,xm,xb,tel) values (2,'test3','password2','1535554747933');
3.删除指定用户
delete from customer where xm='test';
4.修改用户信息
update customer set xb='123' where xm='test';
至此,一个简单的小型图书管理系统的数据库就搭建完成了