测试
-- 1. 用户注册
INSERT INTO t_member(nickname,`name`,email,address,tel,zip,`password`)
VALUES ('板砖者','tom','tom@kaishengit.com','河南郑州','13598123465','465000','123456');
-- 2. 用户登录
SELECT t.nickname,t.name,t.email,t.address,t.tel,t.zip FROM t_member t WHERE t.nickname = '板砖者' AND PASSWORD = '123456';
-- 3. 查看计算机类所有书籍
SELECT b.bookname,b.price FROM t_book b WHERE b.typeid IN (SELECT t.id FROM t_type t WHERE pid = '3');
-- 4． 根据一本书籍的主键，获取书籍的名称、价格、出版社名称、作家名称
-- 4.1 等值查询
SELECT b.bookname,b.price,p.publishname,a.name
FROM t_book b,t_publish p,t_author a
WHERE b.publish_id = p.id AND b.author_id = a.id
AND b.id = 1;
-- 4.2 内连接
SELECT b.bookname,b.price ,p.publishname AS '出版社名称', a.name AS '作家名称' FROM t_book b INNER JOIN
t_publish p ON b.publish_id = p.id INNER JOIN t_author a ON b.author_id = a.id and b.id=1;
-- 5． 获取一个作家所著的所有书籍
SELECT a.name , b.bookname,b.price FROM t_book b
LEFT JOIN t_author a ON b.author_id = a.id
WHERE a.name = "周志明";
-- 6． 查看一个会员收藏的书籍
SELECT b.bookname,b.price,m.nickname FROM t_fav f
LEFT JOIN t_book b ON f.bid=b.id
LEFT JOIN t_member m ON f.mid = m.id
WHERE m.nickname='java007'
-- 7. 写出书籍表的分页 SQL（每页 3 条书籍），写出输出第一页和第三页的
SELECT b.id,b.bookname,b.price FROM t_book b LIMIT 0,3;
SELECT b.id,b.bookname,b.price FROM t_book b LIMIT 6,3;
-- 8． 修改一个会员的密码
UPDATE t_member SET `password` ='123123'
WHERE nickname = 'java007' AND `password`='654321';
-- 9. 写出一本书籍对应的评论及评论的会员姓名
SELECT c.content, m.nickname,b.bookname FROM t_commentary c
LEFT JOIN t_book b ON c.bid = b.id
LEFT JOIN t_member m ON c.mid = m.id
WHERE b.bookname = "西游记"
-- 10． 查询一个订单对应的书籍
SELECT b.id,b.bookname , b.price ,m.nickname ,o.state
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_order o ON ob.oid = o.id
LEFT JOIN t_member m ON o.mid = m.id
WHERE o.id = 7;
-- 11． 统计一个月内计算机类书籍的销售总额
-- count(*) sum(b.price) book
-- DATEDIFF(NOW(),o.otime)<=60 order
-- pid=3 type order_book
SELECT ROUND(SUM(b.price),2),COUNT(*) FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_order o ON ob.oid = o.id
LEFT JOIN t_type t ON b.typeid = t.id
WHERE DATEDIFF(NOW(),o.otime)<=30
AND t.pid = 3
-- 12． 统计两个月内各类书籍的销售额
-- count(*) sum(b.price) book
-- DATEDIFF(NOW(),o.otime)<=60 order
-- pid=3 type order_book
SELECT ROUND(SUM(b.price),2) '销售额',
(SELECT `name` FROM t_type WHERE id = t.pid)'类别'
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_order o ON ob.oid = o.id
LEFT JOIN t_type t ON b.typeid = t.id
WHERE DATEDIFF(NOW(),o.otime)<=60
GROUP BY(t.pid)
-- 13． 统计两个月内各类书籍的销售数量
SELECT COUNT(*) '销售数量',
(SELECT `name` FROM t_type WHERE id = t.pid)'类别'
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_order o ON ob.oid = o.id
LEFT JOIN t_type t ON b.typeid = t.id
WHERE DATEDIFF(NOW(),o.otime)<=60
GROUP BY(t.pid)
-- 14． 删除一个用户对应的所有评论
DELETE FROM t_commentary WHERE `mid` = 3;
-- 15． 列出总销量前五名的书籍
SELECT COUNT(*),(SELECT bookname
FROM t_book WHERE id = b.id) '书名'
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
GROUP BY (b.id) ORDER BY(COUNT(*)) DESC
LIMIT 0,5
-- 16． 列出总销量前三名的书籍分类
SELECT COUNT(*),(SELECT `name`
FROM t_type WHERE id= t.pid) '分类'
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_type t ON b.typeid = t.id
GROUP BY (t.pid) ORDER BY(COUNT(*)) DESC
LIMIT 0,3
-- 17． 列出计算机类别中销售前三名的书籍
SELECT COUNT(*),(SELECT bookname
FROM t_book WHERE id = b.id) '书名'
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_type t ON b.typeid = t.id
WHERE t.pid = 3 GROUP BY(b.id)
ORDER BY(COUNT(*)) DESC LIMIT 0,3
SELECT COUNT(*),b.bookname
FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_type t ON b.typeid = t.id
WHERE t.pid = 3 GROUP BY(b.id)
ORDER BY(COUNT(*)) DESC LIMIT 0,3
-- 18． 列出《一本书》（随意名字）的销售量
SELECT count(*),b.bookname fROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
where b.bookname = "西游记";
-- 19． 列出书籍总销量前三名的作者
SELECT COUNT(*),(SELECT `name` FROM t_author WHERE
id = a.id) FROM t_order_book ob
LEFT JOIN t_book b ON ob.bid = b.id
LEFT JOIN t_author a ON b.author_id = a.id
GROUP BY(a.id) ORDER BY (COUNT(*)) DESC ,(a.id) DESC
LIMIT 0,3
-- 20． 搜索“张三”（随意名字）所著的书籍
SELECT b.bookname,b.price FROM t_book b LEFT
JOIN t_author a ON b.author_id = a.id
WHERE a.name ="周志明";
-- 21． 搜索书籍名称中含有“java”的书籍
SELECT b.bookname,b.price
FROM t_book b WHERE b.bookname LIKE '%java%';
SELECT COUNT(*)
FROM t_book b WHERE b.bookname LIKE '%java%';
-- 23 获取一本书的评论数量
SELECT COUNT(*), b.bookname
FROM t_commentary c LEFT JOIN t_book b
ON c.bid = b.id
WHERE b.bookname = "Spring3.x企业应用开发实战"
sql
create database e_business_platform_db_test;
use e_business_platform_db_test;
########################
# Create t_book table
########################
create table t_book(
id int auto_increment not null COMMENT '标识列',
bookname varchar(50) not null COMMENT '书籍名称',
price float not null COMMENT '书籍价格',
pagesize int COMMENT '书籍页数',
puttime datetime COMMENT '出版时间',
isbn varchar(20) unique COMMENT 'ISBN编码',
format varchar(10) COMMENT '开本',
publish_id int not null COMMENT '出版社id',
author_id int not null COMMENT '作者id',
synopsis text COMMENT '内容简介',
xu text COMMENT '书序',
typeid int not null COMMENT '分类id',
primary key(id)
) ENGINE = InnoDB;
########################
# Create t_publish table
########################
create table t_publish(
id int auto_increment not null COMMENT '标识列',
publishname varchar(50) not null COMMENT '出版社名称',
address varchar(100) COMMENT '出版社地址',
tel varchar(13) COMMENT '联系电话',
pname varchar(20) COMMENT '联系人姓名',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_author table
########################
create table t_author(
id int auto_increment not null COMMENT '标识列',
name varchar(20) not null COMMENT '作者姓名',
synopsis text COMMENT '作者简介',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_type table
########################
create table t_type(
id int auto_increment not null COMMENT '标识列',
name varchar(20) not null COMMENT '分类名称',
pid int COMMENT '父类id',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_commentary table
########################
create table t_commentary(
id int auto_increment not null COMMENT '标识列',
content text not null COMMENT '评论内容',
ctime datetime COMMENT '评论时间',
bid int COMMENT '书籍id',
mid int COMMENT '会员id',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_member table
########################
create table t_member(
id int auto_increment not null COMMENT '标识列',
nickname varchar(20) not null COMMENT '昵称',
name varchar(20) not null COMMENT '真实姓名',
email varchar(20) COMMENT '电子邮件',
address varchar(100) COMMENT '地址',
tel varchar(20) COMMENT '电话',
zip varchar(6) COMMENT '邮编',
password varchar(20) COMMENT '密码',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_order table
########################
create table t_order(
id int auto_increment not null COMMENT '标识列',
mid int COMMENT '会员id',
otime datetime not null COMMENT '下单时间',
address varchar(50) not null COMMENT '配送地址',
zip varchar(6) not null COMMENT '邮编',
name varchar(20) not null COMMENT '收货人姓名',
state varchar(10) not null COMMENT '订单状态',
price double not null COMMENT '订单状态',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_order_book table
########################
create table t_order_book(
id int auto_increment not null COMMENT '标识列',
oid int not null COMMENT '订单编号',
bid int not null COMMENT '书籍编号',
primary key(id)
)ENGINE = InnoDB;
########################
# Create t_fav table
########################
create table t_fav(
id int auto_increment not null COMMENT '标识列',
mid int COMMENT '会员id',
bid int COMMENT '书籍id',
primary key(id)
)ENGINE = InnoDB;
表
insert into `t_author` (`id`, `name`, `synopsis`) values('1','张玉红','张玉红，硕士研究生，自幼喜爱绘画');
insert into `t_author` (`id`, `name`, `synopsis`) values('2','sheff','Donald Alexander Sheff，出生于美国纽约，毕业于耶鲁大学.');
insert into `t_author` (`id`, `name`, `synopsis`) values('3','历史通','历史通毕业于北京大学汉语言博士后，精通古今历史.');
insert into `t_author` (`id`, `name`, `synopsis`) values('4','Tomas','Tomas毕业于耶鲁大学，哲学家.');
insert into `t_author` (`id`, `name`, `synopsis`) values('5','Ben Forta','Ben Forta数据库大师。');
insert into `t_author` (`id`, `name`, `synopsis`) values('6','吴承恩','明代文学家，小说家。');
insert into `t_author` (`id`, `name`, `synopsis`) values('7','David A. Patterson','加州大学伯克利分校计算机科学系教授');
insert into `t_author` (`id`, `name`, `synopsis`) values('8','Sierra K.','sun公司设计领导人');
insert into `t_author` (`id`, `name`, `synopsis`) values('9','周志明','资深Java技术专家');
insert into `t_author` (`id`, `name`, `synopsis`) values('10','陈华雄','spring专家');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('1','素描基础教程','33.8','264','2015-09-01 00:00:00','9787115399922','16','1','1','这是一本素描技法初级读物，内容\n简明易学，它以素描的基本理论知识和具体实践练习为主线，重点加强素描基本技法训练。','','6');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('2','美国纽约摄影学院摄影教材','156.8','1234','2010-08-01 00:00:00','10829742','16','2','2','《美国纽约摄影学院摄影教材（新修订版2）\n（套装上下册）》是20世纪80年代中期以来经久不衰的摄影教材。','','7');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('3','中华上下五千年','126.8','1694','2014-04-01 00:00:00','9787552800043','16','1','3','《中华上下五千年》通读中国历史必读书籍。','','8');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('4','西方哲学史','61','612','2015-05-01 00:00:00','97215528000121','16','3','4','《西方哲学史》了解西方哲学的发展始末。','','9');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('5','西游记','76.8','456','2010-05-01 00:00:00','2321528000121','16','3','6','《西游记》四大名著之一，神话小说之首。','','10');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('6','计算机组成与设计','84.2','536','2015-07-01 00:00:00','9787111504825','16','4','7','《计算机组成与设计》是计算机组成与设计的经典畅销教材。','','11');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('7','mysql必知必会','39','235','2009-05-01 00:00:00','87545528432','32','1','5','《mysql必知必会》学习mysql的经典书籍。','','14');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('8','oracle从精通到入门','96.8','932','2008-11-01 00:00:00','2312415350121','32','4','5','《oracle从精通到入门》学习orcle的经典书籍。','','13');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('9','深入理解Java虚拟机：JVM高级特性与最佳实践','67.2','452','2013-05-01 00:00:00','9787111421900','16','4','9','整个Java图书领域公认的经典著作和超级畅销书。','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('10','Spring3.x企业应用开发实战','76.5','710','2012-02-01 00:00:00','9787121152139','16','4','9','Spring 3.0是Spring在积蓄了3年之久后，隆重推出的一个重大升级版本，进一步加强了Spring作为Java领域开源平台的翘楚地位。','Spring为Java世界带来了一场震撼性的技术革新，它颠覆了传统Java应用开发\n笨重的方式，影响和正在影响着Java开发者思考问题的方法。从2004年发布第一个版本以来，Spring逐渐占据了Java开发人员的视线，\n博得了开源社区一片赞誉之声，开源社区里“春”城无处不飞花。','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('11','Head First Java','47.3','689','2015-05-01 00:00:00','9787508344980','16','5','8','《Head First Java》第14届Jolt大奖的参赛图书。','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('12','Head First 设计模式','47.3','637','2007-00-01 00:00:00','9787508353937','16','5','8','《Head First 设计模式》趋近完美，\n因为它在提供专业知识的同时，仍然具有相当高的可读性。','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('13','Head First Ajax','61.5','497','2007-02-01 00:00:00','9787508387918','20','5','8','《《Head First Ajax（中文版）》是了解如何构建动态、交互式Web\n应用的一个完美学习体验','谁适合看这本书?\n 如果对下面的所有问题都能肯定地回答“是”：\n ① 你了解HTML吗?懂得一些css和JavaScript吗(不过不要求是一个专家)?\n ② 你想真正学会、理解并记住Ajax吗?你是不是有一个目标，想要开发快速响应的实用Web应用?\n ③ 你是不是更喜欢一种轻松的氛围，就像在晚餐餐桌上交谈一样，而不愿意被动地听枯燥乏味的技术报告?\n 那么，这本书正是为你而作。','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('14','java 核心技术 卷一','101.2','720','2013-11-01 00:00:00','9787111445142','16','4','9','Java领域最有影响力和价值的著作之一，\n与《Java编程思想》齐名，10余年全球畅销不衰，广受好评','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('15','java 核心技术 卷二','118.2','856','2013-11-01 00:00:00','9787111442509','16','4','9','Java领域最有影响力和价值的著作之一，\n与《Java编程思想》齐名，10余年全球畅销不衰，广受好评','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('16','java编程思想','71.3','880','2007-06-01 00:00:00','9787111213826','16','4','10','《计算机科学丛书：Java编程思想（第4版）》赢得了全\n球程序员的广泛赞誉，即使是晦涩的概念，在BruceEckel的文字亲和力和小而直接的编程示例面前也会化解于无形。','','12');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('17','21世纪资本论','61.9','740','2014-09-01 00:00:00','9787508647258','32','1','4','《21世纪资本论》的伟大之处就在于，它告诉世人：我们不仅已经踏上了收入水平回归19世纪的道路，而且还正在向“世袭资本主义”回归。在“世袭资本主义”时代，经济制高点不是掌握在有才华的个人手中，而是被家族王朝所主宰。','决定财富积累和分配的因素有哪些？长期来看，我们的贫富差距是扩大了还是缩小了？财富是否越来越集中到少数人手中？未来的经济和社会将走向何方？','15');
insert into `t_book` (`id`, `bookname`, `price`, `pagesize`, `puttime`, `isbn`, `format`, `publish_id`, `author_id`, `synopsis`, `xu`, `typeid`) values('18','卓有成效的管理者','18.3','167','2009-09-01 00:00:00','9787111280712','16','4','4','《卓有成效的管理者（珍藏版）》中，德鲁克集中论述了一个管理者如何做到卓有成效。','在所有的管理学书籍中，德鲁克的著作对我影响很深。','16');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('1','这本书太有用了，强烈推荐','2016-10-22 00:00:00','1','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('2','封面不错，内容还没看，先给好评吧~','2016-10-22 00:00:00','2','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('6','学习java入门书籍，通俗易懂','2016-10-02 00:00:00','11','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('7','oracle经典书籍，赞一个！','2016-10-01 00:00:00','8','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('8','这两天学习数据库，好好学习','2016-10-01 00:00:00','7','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('9','太深奥了，看不懂，蓝瘦，香菇~','2016-07-23 00:00:00','9','1');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('10','数据库数据库。。。','2016-10-22 00:00:00','7','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('11','征服oracle，oracle','2016-10-22 00:00:00','8','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('12','春天在哪里啊春天在哪里','2016-10-22 00:00:00','10','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('13','java面向对象编程，可是没对象怎么办？','2016-10-22 00:00:00','11','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('14','太难了，能退么','2016-10-22 00:00:00','12','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('15','朋友推荐的书，希望能够看一遍','2016-10-22 00:00:00','13','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('16','这本书太有用了，强烈推荐','2016-10-06 00:00:00','6','2');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('17','快递太慢了，竟然用了三天才到，让用一天半读完这本书的我情何以堪，差评！！！！！','2016-10-16 00:00:00','10','3');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('18','谁写的书，怎么这么幼稚！！退钱~~','2016-10-16 00:00:00','15','3');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('19','太失望了太失望了太失望了，重要的事情说三遍！！！','2016-10-20 00:00:00','14','3');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('20','赞赞赞，十五字十五字十五字十五字','2016-10-14 00:00:00','5','4');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('21','还可以，需要精读','2016-10-14 00:00:00','10','4');
insert into `t_commentary` (`id`, `content`, `ctime`, `bid`, `mid`) values('22','学习摄影必备书籍','2016-07-20 00:00:00','14','5');
insert into `t_fav` (`id`, `mid`, `bid`) values('1','1','1');
insert into `t_fav` (`id`, `mid`, `bid`) values('2','1','5');
insert into `t_fav` (`id`, `mid`, `bid`) values('3','1','6');
insert into `t_fav` (`id`, `mid`, `bid`) values('4','1','9');
insert into `t_fav` (`id`, `mid`, `bid`) values('5','1','11');
insert into `t_fav` (`id`, `mid`, `bid`) values('6','1','7');
insert into `t_fav` (`id`, `mid`, `bid`) values('7','2','9');
insert into `t_fav` (`id`, `mid`, `bid`) values('8','2','12');
insert into `t_fav` (`id`, `mid`, `bid`) values('9','2','11');
insert into `t_fav` (`id`, `mid`, `bid`) values('10','1','12');
insert into `t_fav` (`id`, `mid`, `bid`) values('11','1','13');
insert into `t_fav` (`id`, `mid`, `bid`) values('12','1','14');
insert into `t_fav` (`id`, `mid`, `bid`) values('13','2','12');
insert into `t_fav` (`id`, `mid`, `bid`) values('14','2','13');
insert into `t_fav` (`id`, `mid`, `bid`) values('15','2','14');
insert into `t_fav` (`id`, `mid`, `bid`) values('16','3','1');
insert into `t_fav` (`id`, `mid`, `bid`) values('17','3','12');
insert into `t_fav` (`id`, `mid`, `bid`) values('18','3','14');
insert into `t_fav` (`id`, `mid`, `bid`) values('19','4','5');
insert into `t_fav` (`id`, `mid`, `bid`) values('20','4','13');
insert into `t_fav` (`id`, `mid`, `bid`) values('21','4','11');
insert into `t_fav` (`id`, `mid`, `bid`) values('22','5','7');
insert into `t_fav` (`id`, `mid`, `bid`) values('23','5','9');
insert into `t_fav` (`id`, `mid`, `bid`) values('24','5','12');
insert into `t_member` (`id`, `nickname`, `name`, `email`, `address`, `tel`, `zip`, `password`) values('1','java007','jack','jack@kaishengit.com','河南焦作','13598651235','455000','654321');
insert into `t_member` (`id`, `nickname`, `name`, `email`, `address`, `tel`, `zip`, `password`) values('2','板砖者','tom','tom@kaishengit.com','河南郑州','13598123465','465000','123456');
insert into `t_member` (`id`, `nickname`, `name`, `email`, `address`, `tel`, `zip`, `password`) values('3','学神无敌','louis','louis@kaishengit.com','河南开封','13519456789','415000','123456');
insert into `t_member` (`id`, `nickname`, `name`, `email`, `address`, `tel`, `zip`, `password`) values('4','kobe_lover','张顺','zhangshun@kaishengit','河南焦作','15936987412','425000','123456');
insert into `t_member` (`id`, `nickname`, `name`, `email`, `address`, `tel`, `zip`, `password`) values('5','孤独患者','陈奕迅','chenyixun@kaishengit','中国香港','18566666666','400000','123456');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('1','1','2016-10-20 00:00:00','河南焦作理工大学','455500','jack','已完成','123.8');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('2','1','2016-10-10 00:00:00','河南焦作理工大学','455500','jack','已完成','190.6');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('3','1','2016-10-01 00:00:00','河南焦作理工大学','455500','jack','已完成','47.3');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('4','1','2016-09-15 00:00:00','河南焦作理工大学','455500','jack','已完成','135.8');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('5','1','2016-07-19 00:00:00','河南焦作理工大学','455500','jack','已完成','67.2');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('6','2','2016-10-16 00:00:00','河南焦作理工大学','455500','奥特曼','未完成','420.4');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('7','2','2016-09-19 00:00:00','河南焦作理工大学','455500','奥特曼','已完成','84.2');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('8','3','2016-10-15 00:00:00','河南焦作理工大学','455500','学神无敌','已完成','101.2');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('9','3','2016-10-10 00:00:00','河南焦作理工大学','455500','学神无敌','已完成','194.7');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('10','3','2016-09-10 00:00:00','河南焦作理工大学','455500','学神无敌','已完成','118.6');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('11','4','2016-10-11 23:40:24','河南焦作理工大学','455500','科比','已完成','153.3');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('12','4','2016-09-06 23:41:24','河南焦作理工大学','455500','科比','已完成','47.3');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('13','5','2016-10-22 23:42:08','河南焦作理工大学','455500','Eason','已完成','290.7');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('14','5','2016-09-14 23:42:17','河南焦作理工大学','455500','Eason','已完成','221.5');
insert into `t_order` (`id`, `mid`, `otime`, `address`, `zip`, `name`, `state`, `price`) values('15','5','2016-07-13 23:42:22','河南焦作理工大学','455500','Eason','已完成','231');
insert into `t_order_book` (`id`, `oid`, `bid`) values('1','1','10');
insert into `t_order_book` (`id`, `oid`, `bid`) values('2','1','12');
insert into `t_order_book` (`id`, `oid`, `bid`) values('3','2','2');
insert into `t_order_book` (`id`, `oid`, `bid`) values('4','2','1');
insert into `t_order_book` (`id`, `oid`, `bid`) values('5','3','11');
insert into `t_order_book` (`id`, `oid`, `bid`) values('6','4','14');
insert into `t_order_book` (`id`, `oid`, `bid`) values('7','4','15');
insert into `t_order_book` (`id`, `oid`, `bid`) values('8','5','8');
insert into `t_order_book` (`id`, `oid`, `bid`) values('9','6','3');
insert into `t_order_book` (`id`, `oid`, `bid`) values('10','6','4');
insert into `t_order_book` (`id`, `oid`, `bid`) values('11','6','5');
insert into `t_order_book` (`id`, `oid`, `bid`) values('12','6','11');
insert into `t_order_book` (`id`, `oid`, `bid`) values('13','6','12');
insert into `t_order_book` (`id`, `oid`, `bid`) values('14','6','10');
insert into `t_order_book` (`id`, `oid`, `bid`) values('15','7','6');
insert into `t_order_book` (`id`, `oid`, `bid`) values('16','8','14');
insert into `t_order_book` (`id`, `oid`, `bid`) values('17','9','10');
insert into `t_order_book` (`id`, `oid`, `bid`) values('18','9','5');
insert into `t_order_book` (`id`, `oid`, `bid`) values('19','7','5');
insert into `t_order_book` (`id`, `oid`, `bid`) values('20','10','16');
insert into `t_order_book` (`id`, `oid`, `bid`) values('21','10','11');
insert into `t_order_book` (`id`, `oid`, `bid`) values('22','11','5');
insert into `t_order_book` (`id`, `oid`, `bid`) values('23','11','10');
insert into `t_order_book` (`id`, `oid`, `bid`) values('24','12','11');
insert into `t_order_book` (`id`, `oid`, `bid`) values('25','13','16');
insert into `t_order_book` (`id`, `oid`, `bid`) values('26','13','15');
insert into `t_order_book` (`id`, `oid`, `bid`) values('27','13','14');
insert into `t_order_book` (`id`, `oid`, `bid`) values('28','14','1');
insert into `t_order_book` (`id`, `oid`, `bid`) values('29','14','3');
insert into `t_order_book` (`id`, `oid`, `bid`) values('30','15','2');
insert into `t_order_book` (`id`, `oid`, `bid`) values('31','15','17');
insert into `t_order_book` (`id`, `oid`, `bid`) values('32','15','18');
insert into `t_order_book` (`id`, `oid`, `bid`) values('33','14','17');
insert into `t_publish` (`id`, `publishname`, `address`, `tel`, `pname`) values('1','人民邮电出版社','北京','010-81055075','魏人民');
insert into `t_publish` (`id`, `publishname`, `address`, `tel`, `pname`) values('2','中国摄影出版社','上海','020-23245075','艾摄影');
insert into `t_publish` (`id`, `publishname`, `address`, `tel`, `pname`) values('3','中国文学出版社','上海','020-13222372','莫言');
insert into `t_publish` (`id`, `publishname`, `address`, `tel`, `pname`) values('4','中国工业出版社','哈尔滨','0842-6543531','王科技');
insert into `t_publish` (`id`, `publishname`, `address`, `tel`, `pname`) values('5','中国电力出版社','北京','010-6102013','张电力');
insert into `t_type` (`id`, `name`, `pid`) values('1','艺术题材','0');
insert into `t_type` (`id`, `name`, `pid`) values('2','文学名著','0');
insert into `t_type` (`id`, `name`, `pid`) values('3','计算机系列','0');
insert into `t_type` (`id`, `name`, `pid`) values('4','金融管理','0');
insert into `t_type` (`id`, `name`, `pid`) values('5','少儿读物','0');
insert into `t_type` (`id`, `name`, `pid`) values('6','绘画书法','1');
insert into `t_type` (`id`, `name`, `pid`) values('7','摄影','1');
insert into `t_type` (`id`, `name`, `pid`) values('8','中国历史','2');
insert into `t_type` (`id`, `name`, `pid`) values('9','宗教哲学','2');
insert into `t_type` (`id`, `name`, `pid`) values('10','中外小说','2');
insert into `t_type` (`id`, `name`, `pid`) values('11','计算机硬件','3');
insert into `t_type` (`id`, `name`, `pid`) values('12','软件编程','3');
insert into `t_type` (`id`, `name`, `pid`) values('13','数据库','3');
insert into `t_type` (`id`, `name`, `pid`) values('14','办公软件','3');
insert into `t_type` (`id`, `name`, `pid`) values('15','经济金融','4');
insert into `t_type` (`id`, `name`, `pid`) values('16','人文管理','4');
insert into `t_type` (`id`, `name`, `pid`) values('17','儿童文学','5');
insert into `t_type` (`id`, `name`, `pid`) values('18','动漫','5');
主键
alter table t_book add constraint fk_t_book_publish foreign key
(publish_id) references t_publish(id);
alter table t_book add constraint fk_t_book_author foreign key
(author_id) references t_author(id);
alter table t_commentary add constraint fk_t_commentary_book foreign key
(bid) references t_book(id);
alter table t_commentary add constraint fk_t_commentary_member foreign key
(mid) references t_member(id);
alter table t_order add constraint fk_t_order foreign key
(mid) references t_member(id);
alter table t_order_book add constraint fk_t_order_book_order foreign key
(oid) references t_order(id);
alter table t_order_book add constraint fk_t_order_book_order foreign key
(bid) references t_book (id);
alter table t_fav add constraint fk_t_fav_book foreign key
(bid) references t_book(id);
alter table t_fav add constraint fk_t_fav_member foreign key
(mid) references t_member(id);
网友评论