1、数据库设计

使用sqlite3，新建一个book的表，并且都让主键自增

书的借阅记录表

用户表

将record表的bookid和userid和另外两个表关联起来，设置外键关联可以避免一些前置的错误

2、数据库增删改查

2.1 book表操作

#插入书籍
insert into book VALUES(NULL,'精品美','19.9','历史','人文','','100','');
insert into book VALUES(NULL,'三国演义','30','历史','人文','','50','');
insert into book VALUES(NULL,'西游记','20','神话','人文','','50','');
#查询所有书籍
select * from book
#删除书籍
DELETE from book WHERE bookid=2
#修改书籍价格
update book set press = '300'where bookid = 2;
#清空库，并且让主键从1自增
DELETE from book;
DELETE FROM sqlite_sequence WHERE name = 'book';

2.2 user表操作

#插入
insert into user VALUES(NULL,'小强','1年级1班','学生','计算机系','xiaoqiang','123456');
insert into user VALUES(NULL,'小张','1年级1班','学生','计算机系','xiaozhang','123456');
insert into user VALUES(NULL,'小明','1年级1班','学生','计算机系','xiaoming','123456');

2.3 record表的操作（重点）

#record增加,设置好的外键要存在才能添加进入
insert into record VALUES(NULL,1,2,'2024年2月21日10:00:14','2024年2月23日10:00:14');
#record删除
DELETE from record；
#删除用户，需要先把记录删了，因为外键的存在，要先归还
DELETE from user where userid = 2;
#删除书籍同理

#通过记录查询人和书籍，多表联查
SELECT * from record join book using(bookid);
SELECT * from record join user using(userid);

设置的外键阻止删除

多表联查

3、数据表导出

/*
` Navicat Premium Data Transfer
` Source Server         : book
` Source Server Type    : SQLite
` Source Server Version : 3035005 (3.35.5)
` Source Schema         : main
` Target Server Type    : SQLite
` Target Server Version : 3035005 (3.35.5)
` File Encoding         : 65001
` Date: 21/02/2024 10:42:57
*/
PRAGMA foreign_keys = false;
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS "book";
CREATE TABLE "book" (
 `  "bookid" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
 `  "name" text,
 `  "press" integer,
 `  "type1" TEXT,
 `  "type2" TEXT,
 `  "type3" TEXT,
 `  "count" integer,
 `  "  pic" TEXT
);
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO "book" VALUES (1, '精品美', 19.9, '历史', '人文', '', 100, '');
INSERT INTO "book" VALUES (2, '精品美', 300, '历史', '人文', '', 100, '');
INSERT INTO "book" VALUES (3, '三国演义', 30, '历史', '人文', '', 50, '');
INSERT INTO "book" VALUES (4, '西游记', 20, '神话', '人文', '', 50, '');
-- ----------------------------
-- Table structure for record
-- ----------------------------
DROP TABLE IF EXISTS "record";
CREATE TABLE "record" (
 `  "bookid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 `  "id" INTEGER,
 `  "userid" INTEGER,
 `  "start" TEXT,
 `  "end" TEXT,
 `  "record" TEXT,
 `  FOREIGN KEY ("bookid") REFERENCES "book" ("bookid") ON DELETE NO ACTION ON UPDATE NO ACTION,
 `  FOREIGN KEY ("userid") REFERENCES "user" ("userid") ON DELETE NO ACTION ON UPDATE NO ACTION
);
-- ----------------------------
-- Records of record
-- ----------------------------
-- ----------------------------
-- Table structure for sqlite_sequence
-- ----------------------------
DROP TABLE IF EXISTS "sqlite_sequence";
CREATE TABLE "sqlite_sequence" (
 `  "name",
 `  "seq"
);
-- ----------------------------
-- Records of sqlite_sequence
-- ----------------------------
INSERT INTO "sqlite_sequence" VALUES ('book', 4);
INSERT INTO "sqlite_sequence" VALUES ('record', 0);
INSERT INTO "sqlite_sequence" VALUES ('book', 4);
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
 `  "userid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 `  "username" TEXT,
 `  "auth" TEXT,
 `  "department" TEXT,
 `  "password" TEXT,
 `  "nickname" TEXT
);
-- ----------------------------
-- Records of user
-- ----------------------------
-- ----------------------------
-- Auto increment value for book
-- ----------------------------
UPDATE "sqlite_sequence" SET seq = 4 WHERE name = 'book';
-- ----------------------------
-- Auto increment value for record
-- ----------------------------
PRAGMA foreign_keys = true;