MySql图书馆数据库源码修改版sp2
昨天发了帖子
我测试了下 发现很多低级语法错误问题
修改了一下 分享下 源码 我只是学生分享下 期末作业的作品 如果有好的写法请大家多多指教
mysql图书数据库代码说明
1.严重修复 sql语法错误
2.调整外键sql语句位置
3.调整sql语句 关键字冲突问题
4.缺少type的表 取消type的外键
create table customer
(
customer_id int(6) primary key comment'客户编号',
customer_name varchar(50) not null comment'客户名称',
password varchar(20) not null comment'密码',
true_name varchar(20)comment'真实姓名',
email_address varchar(50) unique not null comment'邮箱地址',
password_question varchar(50) not null comment'密码提示问题 ',
status char(1)default 1 comment'客户状态',
level char(1) default 1 comment'客户级别',
score int(6) comment'客户积分',
register_date date comment'注册时间',
register_time TIMESTAMP comment'最后登录时间',
login_count int(6) comment '登入次数',
login_ip char(16) comment '登入ip'
);
create table customer_detail
(
customer_id int(6) primary key comment'客户编号',
sex char(1) default 'f' comment'性别',
province varchar(50) comment'所在省份',
city varchar(50) comment'所在城市',
address varchar(50) comment'详细地址',
zip varchar(50) comment'邮政编码',
phone varchar(50) comment'固定电话',
mobile varchar(20) comment'移动电话',
pay_type char(1) default 1 comment'支付方式 ',
deliver_type char(1) default 1 comment'送货方式 '
);
create table order1
(
order_id int(6) primary key comment'订单编号',
customer_id int(6) not null comment'客户编号',
receiver_name char(12) not null comment'收货人姓名',
receiver_phone varchar(20) not null comment'收货人固定电话',
receiver_mobile varchar(20) not null comment'收货人移动电话',
receiver_province varchar(20) not null comment'收货人所在省份',
receiver_city varchar(20) not null comment'收货人所在城市',
receiver_address varchar(50) not null comment'收货人详细地址',
receiver_zip char(6) comment'收货人邮政编码',
pay_type char(1) not null default 1 comment'支付方式',
deliver_type char(1) not null default 1 comment'送货方式',
deliver_fee decimal(6.2) comment '送货费用',
statuss char(1) default 1 comment'订单状态 ',
total_price decimal(6.2) comment'订单总价',
order_time timestamp comment'订单时间',
expriation_date date comment'失效日期 '
);
alter table order1
add constraint fk_id foreign key(customer_id) references customer_detail(customer_id);
create table order_detail
(
order_detail_id int(6) primary key comment'订单明细编号',
order_id int(6) comment'订单编号',
book_id int(6) comment'图书编号',
unit_price decimal(6.2)not null comment'单价',
order_qty int(2) not null comment'数量'
);
alter table order_detail
add constraint fk_id_1 foreign key(order_id) references order1(order_id);
create table book
(
book_id int(6) primary key comment '图书编号',
book_name varchar(50) not null comment '图书名称',
book_frist_type_id int(3) not null comment '图书小类编号',
author_id int(3) not null comment '作者编号',
publisher_id int(3) not null comment'出版社编号',
isbn varchar(20) unique comment'ISBN',
publisher_date date comment'出版时间',
publisher_no varchar(20) comment'版次',
print_no varchar(20) comment'印次',
page_qty varchar(20) comment'页数',
word_qty varchar(20) comment'字数',
paper_qty int(3) comment'纸张',
wrap varchar(20) comment'包装',
format int(3) comment'开本',
book_desc varchar(250) comment'图书简介',
unit_price decimal(6.2) not null comment'零售价',
discount decimal(1.2) comment'折扣',
inventoty_qty int(6) not null comment'库存',
view_count int(6) comment'浏览次数',
buty_cunt int(6) comment'购买次数'
);
alter table order_detail
add constraint fk_id_2 foreign key(book_id) references book(book_id);
create table book_piture_catalog
(
book_id int(6) primary key comment'图书编号',
cover_small_picture blob comment'封面小图',
cover_big_picture blob comment'封面大图',
book_catolog blob comment'图书目录'
);
create table book_first_type
(
book_first_type_id int(2) primary key comment'分类编号',
type_name varchar(50) not null comment'分类名称',
type_desc varchar(250) comment'分类描述'
);
alter table book
add constraint fk_id_3 foreign key(book_frist_type_id) references book_first_type(book_first_type_id);
create table book_second_type
(
book_second_type_id int(3) primary key comment'分类编号',
type_name varchar(50) not null comment'分类名称',
book_first_type_id int(2) not null comment'大类编号',
type_desc varchar(250) comment'分类描述'
);
alter table book_second_type
add constraint fk_id_6 foreign key(book_first_type_id) references book_first_type(book_first_type_id);
create table publisher
(
publisher_id int(3) primary key comment'出版社编号',
publisher_name varchar(50) not null comment'出版社名称',
address varchar(50) comment'地址',
phone varchar(50) comment'电话',
site_url varchar(50) comment'网址'
);
alter table book
add constraint fk_id_5 foreign key(publisher_id) references publisher(publisher_id);
create table author
(
author_id int(3) primary key comment'作者编号',
author_name varchar(50) not null comment'作者名称',
author_desc varchar(250) comment'作者简介',
email varchar(50) comment'邮箱地址'
);
alter table book
add constraint fk_id_4 foreign key(author_id) references author(author_id);
create table publish_book
(
Publish_book_id int(6) primary key comment'出版图书编号',
publisher_id int(3) not null comment'出版社编号',
book_id int(6) not null comment'图书编号'
);
alter table publish_book
add constraint fk_id_7 foreign key(publisher_id) references publisher(publisher_id);
alter table publish_book
add constraint fk_id_8 foreign key(book_id) references book(book_id);
create table Write_book
(
Write_book_id int(6) primary key comment'编写图书编号',
Author_id int(3) not null comment'作者编号',
book_id int(6) not null comment'图书编号'
);
第一次沙发 总有刁民想阴朕 发表于 2016-11-18 15:47
大神，我来瞅瞅
哈 我可不是大神 我的语言基础很差 路过，瞅瞅。膜拜下。 非常不错，只要设计好关联关系图，就不会错太远。重要的是思路，感谢楼主分享所学所得！{:1_927:} zhangbo093 发表于 2016-11-18 17:24
非常不错，只要设计好关联关系图，就不会错太远。重要的是思路，感谢楼主分享所学所得！
嘻嘻 谢谢 你给我支持和鼓励 感谢楼主分享所学所得！ 感谢楼主的分享！ 试一下{:1_907:}哈哈哈
页:
[1]