图书管理系统sql语句

mybatis写sql

查询书库列表

SELECT * FROM `stackroom`;

新增书库

INSERT INTO stackroom(type) VALUE(#{type});

修改书库类型

UPDATE stackroom SET type=#{type} where stack_id=#{id};

删除书库

DELETE FROM stackroom WHERE stack_id=#{id};

查询书库数量

SELECt count(1) FROM stackroom;

---

查询书本类型数量

SELECT count(0) FROM books;

查询全部书本类型 （分页）(分类)（动态SQL实现）

--从index(从0开始)开始向后lenth个
SELECT b_id,`name`,publisher,author,price,number,type FROM books JOIN stackroom ON books.stackid=stackroom.stack_id 
<if test="stackid!=null">	
 WHERE stackid = #{stackid}
</if>
<if test="index != null and length!=null">
  limit #{index},#{length};
</if>

查询某一本书通过书名

SELECT b_id,`name`,publisher,author,price,number,type FROM books JOIN stackroom ON books.stackid=stackroom.stack_id WHERE `name` like %#{bookname}% limit #{index},#{length}

新增一本书类型

INSERT INTO books(`name`,publisher,author,price,number,stackid)
VALUES(#{bookname},#{publisher},#{author},#{price},#{number},#{stackid});

修改一本书类型

UPDATE books set 
`name` = #{bookname},
publisher = #{publisher},
author = #{author},
price = #{price}
WHERE
b_id = #{bookid};

删除书本类型

Delete From books where b_id=#{bookid}

---

实体书与书类别对应 新增书本

INSERT INTO san(san,bid)VALUES(#{uuid},#{bid});

UPdate books Set number=number+1 where bid=#{bid}

---

查询一本未借出的实体书

SELECT san FROM san WHERE isborrow=1 AND bid=#{bookid} limit  1;

---

借书

borrow表的更新

INSERT INTO borrow(userid,sanid,borrow_time)
VALUES(#{userid},#{sanid},#{borrow_time});

san表的更新

UPDATE san SET
isborrow = 0;
WHERE san =#{sanid};

books表的更新

UPDATE books SET number=number-1 WHERE b_id=#{bookid};

还书

查询用户要还的书

SELECT b_id,`name`,publisher,author,price,isborrow FROM books
JOIN san ON books.b_id=san.bid
WHERE san.san IN
(
SELECT san FROM borrow 
WHERE borrow.userid=#{userid}
)

更新books表

UPDATE books SET number=number+1 WHERE b_id=#{bookid};

更新borrow表

UPDATE borrow SET return_time=#{time} where sanid=#{sanid}

更新san表

UPDATE san SET isborrow=1 where san.san=#{sanid}

查询书库

查询