dada第二版本.sql.txt

刚用java做的网上购物车 做毕业论文用的 有需要的朋友可以参考下

#删除DB_DADA数据库
drop database DB_DADA;
#创建DB_DADA数据库
create database DB_DADA character set utf8;
#使用DB_DADA数据库
use DB_DADA;


#创建目录表
create table TAB_CATELOG(
    catelog_id  bigint primary key  auto_increment,
    catelog_name varchar(20)
);

#创建用户信息表
create table TAB_USERINFO(
    userinfo_id bigint primary key auto_increment,
    userinfo_username varchar(20),
    userinfo_password varchar(20),
    userinfo_realname varchar(20),
    userinfo_tel varchar(25),
    userinfo_address varchar(50),
    userinfo_email varchar(30) 
);

#创建订单状态表
create table TAB_STATUS(
    status_id bigint primary key auto_increment,
    status_name varchar(20)
);

#创建订单付款方式表
create table TAB_PAYWAY(
    payway_id bigint primary key auto_increment,
    payway_name varchar(20)
);


#创建订单发送方式表


create table TAB_SENDTIME(

    sendtime_id bigint primary key auto_increment,

    sendtime_name varchar(50)

);

#创建订单表
create table TAB_ORDER(
    order_id bigint primary key auto_increment,
    order_totalPrice double,
    
order_sendway varchar(50),
    order_time date,
    order_userinfo_id bigint 
	references TAB_USERINFO(userinfo_id),
    constraint    foreign key(order_id)
        references   TAB_PAYWAY(payway_id),
    constraint    foreign key(order_id)
        references   TAB_STATUS(status_id),
    constraint    foreign key(order_id)
        references   TAB_SENDTIME(sendtime_id)   
);



#创建图书信息表
create table TAB_BOOKINFO(
    bookinfo_id bigint primary key auto_increment,
    bookinfo_name varchar(30),
    bookinfo_author varchar(20),
    bookinfo_publisher varchar(50),
    bookinfo_price double,
    bookinfo_catelog_id bigint 
	references TAB_CATELOG(catelog_id)
);

#创建订单图书的连接表
create table TAB_ORDER_BOOK(
   order_id bigint 
	references TAB_ORDER(order_id),
   bookinfo_id bigint 
	references TAB_BOOKINFO(bookinfo_id),
   count int, 
   primary key(order_id, bookinfo_id)
);


###########################################################


insert into TAB_CATELOG (catelog_name) values ('计算机');
insert into TAB_CATELOG(catelog_name) values ('文学');
insert into TAB_CATELOG(catelog_name) values ('小说');


insert into TAB_STATUS(status_name) values ('未发货');
insert into TAB_STATUS(status_name) values ('送货中');
insert into TAB_STATUS(status_name) values ('已收货');





insert into TAB_PAYWAY(payway_name) values ('邮局汇款');
insert into TAB_PAYWAY(payway_name) values ('银行转账');
insert into TAB_PAYWAY(payway_name) values ('手机钱包付款');




insert into TAB_SENDTIME(sendtime_name) values ('周一至周五');
insert into TAB_SENDTIME(sendtime_name) values ('周六、周日');



insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通Struts','孙卫琴','人民邮电',39,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通Spring','孙卫琴','人民邮电',53,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通Hibernate','孙卫琴','机械工业',45,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通AJAX','梁明','机械工业',29,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通AspectJ','赵强','机械工业',39,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通JDO','梁明','电子工业',45,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('精通SQL','何肃','电子工业',29,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)value('Hibernate应用开发完全手册','李钟','电子工业',59,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('Spring应用开发完全手册','李钟','电子工业',59,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('Eclipse应用开发完全手册','李钟','清华大学',59,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('Struts应用开发完全手册','李钟','清华大学',59,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('VB数据库系统开发完全手册','王明','清华大学',59,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('Delphi数据库系统开发完全手册','王明','清华大学',48,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('JSP数据库系统开发完全手册','王明','北京大学',79,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('C#数据库系统开发完全手册','刘斌','北京大学',39,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('C++数据库系统开发完全手册','刘斌','北京大学',49,1);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('故宫','林一达','世界华语',45,2);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('学习','李四','中国青年',49,3);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('娱乐','王五','中国青年',19,3);

insert into TAB_BOOKINFO(bookinfo_name,bookinfo_author,bookinfo_publisher,
bookinfo_price,bookinfo_catelog_id)values('休闲','钱二','中国青年',30,3);