MySql图书馆数据库源码修改版sp2

fengrui99

昨天发了帖子
我测试了下 发现很多低级语法错误问题
修改了一下 分享下 源码 我只是学生分享下 期末作业的作品 如果有好的写法请大家多多指教

mysql图书数据库代码说明


1.严重修复 sql语法错误

2.调整外键sql语句位置

3.调整sql语句 关键字冲突问题

4.缺少type的表 取消type的外键

[SQL] 纯文本查看 复制代码

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'图书编号'
);

haoyundao123

第一次沙发

fengrui99

总有刁民想阴朕 发表于 2016-11-18 15:47
大神，我来瞅瞅

哈 我可不是大神 我的语言基础很差

小涩男

路过，瞅瞅。膜拜下。

zhangbo093

非常不错，只要设计好关联关系图，就不会错太远。重要的是思路，感谢楼主分享所学所得！

fengrui99

zhangbo093 发表于 2016-11-18 17:24
非常不错，只要设计好关联关系图，就不会错太远。重要的是思路，感谢楼主分享所学所得！

嘻嘻 谢谢 你给我支持和鼓励

qwe72355

感谢楼主分享所学所得！

Kaiter_Plus

感谢楼主的分享！

13080607110

试一下哈哈哈