数据库部分：
mysql> show tables
-> ;
+---------------------+
| Tables_in_book_shop |
+---------------------+
| t_book |
| t_bookrecord |
| t_user |
+---------------------+
3 rows in set (0.08 sec)
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> desc t_user
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| memberid | int(11) | NO | PRI | 0 | |
| username | varchar(20) | YES | | NULL | |
| password | varchar(20) | YES | | NULL | |
| type | varchar(1) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.08 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)
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| book_shop |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 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;