如何用SQL设计一个图书管理系统＜纯SQL＞

最近在某鱼上有小伙伴让我帮他设计一个图书管理系统的数据库，从建库到简单的数据库，现在写完了，分享给大家哦！

我们先来看看他的要求，如下图：

根据以上需求我们来编写我们的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';

至此，一个简单的小型图书管理系统的数据库就搭建完成了