二维码的网站如何做,wordpress 如何发布文章,建零售网站还是,电销系统开发参考b站#xff1a;视频连接 目录 1、数据库设计2、数据库增删改查2.1 book表操作2.2 user表操作2.3 record表的操作#xff08;重点#xff09; 3、数据表导出 1、数据库设计
使用sqlite3#xff0c;新建一个book的表#xff0c;并且都让主键自增
NmaeValuebookid书本…参考b站视频连接 目录 1、数据库设计2、数据库增删改查2.1 book表操作2.2 user表操作2.3 record表的操作重点 3、数据表导出 1、数据库设计
使用sqlite3新建一个book的表并且都让主键自增
NmaeValuebookid书本标签name书名count数量press价格type类别pic书本封面图片
书的借阅记录表
NmaeValuebookid书本标签id序号userid用户idstart开始时间end结束时间record借阅记录
用户表 NmaeValueuserid用户idusername用户名auth结束时间department借阅记录password密码nickname姓名
将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 bookid2
#修改书籍价格
update book set press 300where 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 TransferSource Server : bookSource Server Type : SQLiteSource Server Version : 3035005 (3.35.5)Source Schema : mainTarget Server Type : SQLiteTarget Server Version : 3035005 (3.35.5)File Encoding : 65001Date: 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;
