基于web开发的图书管理系统(还有数据库源码)

5.1.1 数据库模式的创建 5.1.1.1 用户表 CREATE TABLE users ( uno INT PRIMARY KEY AUTO_INCREMENT, # ID为主键, 自动增长 upwd VARCHAR(50) NOT NULL, # 密码 uname VARCHAR (14) NOT NULL, # 用户姓名 usex VARCHAR (1) NOT NULL, # 性别 uage INT NOT NULL, # 年龄 uif VARCHAR (1) DEFAULT "否", # 是否是管理员, 默认值为 "否" lno INT, # 借阅id FOREIGN KEY (lno) REFERENCES lend (lno) # 外码参照 ); 5.1.1.2 图书表 CREATE TABLE books ( bno INT PRIMARY KEY AUTO_INCREMENT, # ID为主键, 自动增长 bname VARCHAR(50) NOT NULL, # 图书名称 bwriter VARCHAR(50) NOT NULL, # 图书作者 bpress VARCHAR(50) NOT NULL, # 图书出版社 bstock INT NOT NULL # 图书库存 ); 5.1.1.3 借阅表 CREATE TABLE lend ( lno INT PRIMARY KEY AUTO_INCREMENT, # ID为主键, 自动增长 lfre INT NOT NULL, # 借阅次数, 规定每人最多借书4本 lbno VARCHAR(100) # 借阅图书的id ); 5.1.1.4 信息表 CREATE TABLE message ( mno INT PRIMARY KEY AUTO_INCREMENT, # 公告id mtitle VARCHAR(50), # 公告标题 mcontent VARCHAR(500), # 公告内容 mtime datetime, # 公告发布时间 uno INT, # 公告发布者id FOREIGN KEY (uno) REFERENCES users (uno) # 外码参照 ); 5.1.2 数据库初始化 5.1.2.1 用户表初始化 INSERT INTO users (upwd, uname, usex, uage, lno, uif)VALUES("123", "王龙", "男", 21, 1, "是"); INSERT INTO users (upwd, uname, usex, uage, lno, uif)VALUES("456", "王凤", "女", 21, 2, "是"); INSERT INTO users (upwd, uname, usex, uage, lno)VALUES("123", "王敏", "女", 19, 3); INSERT INTO users (upwd, uname, usex, uage, lno)VALUES("456", "张立", "男", 20, 4); INSERT INTO users (upwd, uname, usex, uage, lno)VALUES("852", "吴宾", "女", 20, 5); INSERT INTO users (upwd, uname, usex, uage, lno)VALUES("963", "张海", "男", 21, 6); INSERT INTO users (upwd, uname, usex, uage, lno)VALUES("147", "钱小平", "女", 19, 7); 5.1.2.2 图书表初始化 insert into books(bname,bwriter,bpress,bstock) values("java学习指导", "李勇", "黄河出版社" ,2); insert into books(bname,bwriter,bpress,bstock) values("java教程", "张三", "长江出版社" ,1); insert into books(bname,bwriter,bpress,bstock) values("java web开发", "李四", "富家包出版社" ,3); insert into books(bname,bwriter,bpress,bstock) values("C和指针", " Kenneth A. Reek", "人民邮电出版社" ,7); insert into books(bname,bwriter,bpress,bstock) values("龙族", "江南", "长江出版社" ,4); insert into books(bname,bwriter,bpress,bstock) values("长安十二时辰", "马伯庸", "湖南文艺出版社" ,2); insert into books(bname,bwriter,bpress,bstock) values("世界上有趣的事太多", "张佳玮", "译林出版社" ,1); insert into books(bname,bwriter,bpress,bstock) values("java 虚拟机讲解", "王二", "长城出版社" ,3); insert into books(bname,bwriter,bpress,bstock) values("java ee开发", "成天", "汽院出版社" ,5); insert into books(bname,bwriter,bpress,bstock) values("java 之路", "于武提", "汽院出版社" ,5); insert into books(bname,bwriter,bpress,bstock) values("龙族", "江南", "长江出版社" ,4); insert into books(bname,bwriter,bpress,bstock) values("长安十二时辰", "马伯庸", "湖南文艺出版社" ,2); insert into books(bname,bwriter,bpress,bstock) values("世界上有趣的事太多", "张佳玮", "译林出版社" ,1); 5.1.2.3 借阅表初始化 INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); INSERT INTO lend (lfre,lbno)VALUES(0, ""); 5.1.2.4 公告表初始化 INSERT INTO message (mtitle, mcontent, mtime, uno)VALUES("图书管理系统正式上线", "欢迎各位同学使用图书管理系统！", "2017-06-01 10:49:34",1);