javaweb开发之---在线图书管理系统

来源：互联网发布：美化你的声音知乎编辑：程序博客网时间：2024/05/24 07:34

数据库部分：

mysql> show create table t_user
-> ;
+--------+-------------------------------------------------------------------
-------------------------+
| Table | Create Table
|
+--------+-------------------------------------------------------------------
-------------------------+
| t_user | CREATE TABLE `t_user` (
`memberid` int(11) NOT NULL DEFAULT '0',
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`type` varchar(1) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`email` varchar(40) DEFAULT NULL,
PRIMARY KEY (`memberid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+-------------------------------------------------------------------
-------------------------+
1 row in set (0.03 sec)

mysql> show t_user
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual th
mysql> select * from t_user
    -> ;
+----------+----------+-----------+------+-------------+------------+
| memberid | username | password | type | phone       | email      |
+----------+----------+-----------+------+-------------+------------+
|        0 | wangjin | 520123412 | 1    | 15907189950 | 126@qq.com |
|        1 | wangjin | 520       | 1    | 1223456     | 126.com    |
|        2 | wangping | 123456    | 0    | 7882580     | 126.com    |
|        3 | jin      | 123       | 1    | 7882580     | 126.com    |
|        4 | fang     | 520       | 1    | 7882580     | 126.com    |
+----------+----------+-----------+------+-------------+------------+
5 rows in set (0.00 sec)

mysql> select * from t_book
    -> ;
+--------+-----------------+------------+-------+----------+
| bookid | bookName        | authorName | price | sortName |
+--------+-----------------+------------+-------+----------+
|      1 | web             | sunweiqing |    22 | computer |
|      3 | sql             | 丁忠俊     |    22 | 计算机   |
|      4 | structuretest   | 杨薇薇     |    25 | 计算机   |
|      5 | spring          | jin        |    25 | essay    |
|      6 | linuxorder      | linux      |    25 | computer |
|      7 | javaface        | java       |    25 | computer |
|      8 | informationsafe | 翁广安     |    25 | computer |
|      9 | html            | wangzhi    |    25 | computer |
|     10 | assembly        | jin        |    22 | computer |
|     11 | arithmetic      | 秦明       |    22 | 算法     |
+--------+-----------------+------------+-------+----------+
10 rows in set (0.00 sec)

mysql> select * from t_book;
+--------+-----------------+------------+-------+----------+
| bookid | bookName        | authorName | price | sortName |
+--------+-----------------+------------+-------+----------+
|      1 | web             | sunweiqing |    22 | computer |
|      3 | sql             | 丁忠俊     |    22 | 计算机   |
|      4 | structuretest   | 杨薇薇     |    25 | 计算机   |
|      5 | spring          | jin        |    25 | essay    |
|      6 | linuxorder      | linux      |    25 | computer |
|      7 | javaface        | java       |    25 | computer |
|      8 | informationsafe | 翁广安     |    25 | computer |
|      9 | html            | wangzhi    |    25 | computer |
|     10 | assembly        | jin        |    22 | computer |
|     11 | arithmetic      | 秦明       |    22 | 算法     |
+--------+-----------------+------------+-------+----------+
10 rows in set (0.00 sec)

mysql> select * from t_user
    -> ;
+----------+----------+-----------+------+-------------+------------+
| memberid | username | password | type | phone       | email      |
+----------+----------+-----------+------+-------------+------------+
|        0 | wangjin | 520123412 | 1    | 15907189950 | 126@qq.com |
|        1 | wangjin | 520       | 1    | 1223456     | 126.com    |
|        2 | wangping | 123456    | 0    | 7882580     | 126.com    |
|        3 | jin      | 123       | 1    | 7882580     | 126.com    |
|        4 | fang     | 520       | 1    | 7882580     | 126.com    |
+----------+----------+-----------+------+-------------+------------+
5 rows in set (0.00 sec)

mysql> select * from t_user
    -> ;
+----------+----------+----------+------+---------+---------+
| memberid | username | password | type | phone   | email   |
+----------+----------+----------+------+---------+---------+
|        2 | wangping | 123456   | 0    | 7882580 | 126.com |
|        3 | jin      | 123      | 1    | 7882580 | 126.com |
|        4 | fang     | 520      | 1    | 7882580 | 126.com |
+----------+----------+----------+------+---------+---------+
3 rows in set (0.00 sec)

mysql> select * from t_user
    -> ;
+----------+----------+----------+------+-------------+-------------------+
| memberid | username | password | type | phone       | email             |
+----------+----------+----------+------+-------------+-------------------+
|        0 | wangjin | 123456   | 1    | 13823782780 | 1017951385@qq.com |
|        2 | wangping | 123456   | 0    | 7882580     | 126.com           |
|        3 | jin      | 123      | 1    | 7882580     | 126.com           |
|        4 | fang     | 520      | 1    | 7882580     | 126.com           |
+----------+----------+----------+------+-------------+-------------------+
4 rows in set (0.00 sec)

create table t_bookrecord(
id int primary key auto_increment,
bookid int, //还可以加一些属性控制进去 not null等
memberid int,
starttime datetime,
endtime datetime)//创建某个表

rename table t_user to users;//更改表名
show create table t_bookrecord;//显示建立表时的信息
desc t_bookrecord;//查看表结构
alter table t_book change id bookid int;//更改某个字段的名字
alter table t_book drop bookname;//删除某一列
alter table t_book add sort varchar(20);//增加某一列

insert into t_bookrecord(bookid,memberid,starttime,endtime)
values(1,1,now(),now());//插入表数据
insert into t_bookrecord(bookid,memberid,starttime,endtime)
values(4,2,'2012-01-02 00:00:00 ','2012-01-02 00:10:30 ');

select count(*) from t_bookrecord where bookid=1;//统计某本书的阅读次数
select sum(endtime-starttime) from t_bookrecord where bookid=1;//
select bookid, sum(endtime-starttime) from t_bookrecord group by bookid order by sum(endtime-starttime)desc;//排序
select count(memberid) from (select distinct memberid from t_bookrecord) t_bookrecord;// 统计distinct踢出重复或者 select count(distinct memberid) from t_bookrecord;
select memberid,sum(endtime-starttime) from t_bookrecord where memberid=2;//统计个人阅读的时间
select memberid,count(id) from t_bookrecord where memberid=2;//统计总阅读次数某个人的
select memberid,count(distinct bookid) from t_bookrecord where memberid=2;//统计某个人总共阅读了多少次数。

select bookid,sum(endtime-starttime) from t_bookrecord
group by bookid
where starttime>='2012-01-01 00-00-00' and endtime<='2012-01-03 00-00-00'
order by sum(endtime-starttime) desc;

select bookid,sum(endtime-starttime) from t_bookrecord
where starttime>='2012-01-01 00-00-00' and endtime<='2012-01-02 00-10-30'
group by bookid;

select bookid,count(bookid) from t_bookrecord
where starttime>='2012-01-01 00-00-00' and endtime<='2012-01-03 00-00-00'
group by bookid;// 统计在某段时间内的某本书的阅读次数排名阅读时间排名。

--图书分类信息表
create table bookcat (
catid number(10) primary key,
catname varchar2(50) not null
);

--图书基本信息表
create table book (
   bookid       number(10) primary key ,
   catid        number(10) not null references bookcat(catid) on delete cascade,
   bookname     varchar2(50) not null ,
   price        number(10,2) not null ,
   saleprice    number(10,2) not null ,
   descriptions varchar2(600) not null ,
   author       varchar2(50) not null ,
   content      varchar2(2000) not null,
   image        varchar2(50)    null ,
   discount     number(10,2)      not null,
   remaincount number(10)      not null,
   registdate   date            not null,
   saledcounts number(10)      not null
);

--客户基本信息表
create table shopuser (
    username varchar2(20) primary key ,
    pwd      varchar2(20) not null ,
    realname varchar2(20) not null ,
    tel      varchar2(20) not null ,
    address varchar2(100) not null ,
    email    varchar2(20) not null
);

--客户订单表
create table orders (
    orderid   number(13) primary key ,
    username varchar2(20) not null references shopuser (username) on delete cascade ,
    ordertime date         not null ,
    delivery varchar2(20) not null ,
    payment   varchar2(20) not null ,
    status    varchar2(20) not null
);

--订单详细信息表
create table orderdetail (
   orderid    number(13) not null references orders(orderid) on delete cascade ,
   bookid     number(10) not null references book(bookid) on delete cascade ,
   counts     number(10) not null ,
   price      number(10,2) not null
);

--管理员基本信息表
create table manager (
managerName varchar2(20) primary key ,
managerPwd varchar2(20) not null
);

-- Create sequence
create sequence book_seq
minvalue 1
maxvalue 999999
start with 15
increment by 1
cache 20
order;