mysql语句-图书管理系统（增删改查）

一、相关表

图书管理系统 共涉及四张表：
1、图书类别表
2、图书信息表
3、读者信息表
4、借阅信息表

二、系统需求

1、要求一个读者借书时间为一个月
2、读者每借一本书，余额就扣除这本书价格的3%。

三、任务需求

1、用mysql语句创建四张表

-- 创建数据库
create database book;
use book;

-- 1 图书类别表
create table category(
id int primary key auto_increment,
name varchar(20) not null,
parent_id int not null
);

-- 2 图书信息表
create table bookinfo(
book_id int primary key auto_increment,
category_id int,
book_name varchar(20) not null,
auther varchar(20) not null,
price float(6,2) not null,
press varchar(20) not null,
public_date date not null,
store int not null,
constraint fk_cate foreign key(category_id) references category(id)
)auto_increment=10000;

-- 3读者信息表
create table reader(
card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default'保密',
age tinyint not null,
phone_number char(11) not null,
balance decimal(6,2) default 200
);

-- 4借阅信息表
create table borrowinfo(
book_id int,
card_id char(18),
borrow_date date not null,
return_date date,
is_return enum('是','否'),
constraint pk_bookid_cardid primary key(book_id,card_id),
constraint fk_bookid foreign key(book_id) references bookinfo(book_id),
constraint fk_cardid foreign key(card_id) references reader(card_id)
);

2、用mysql语句插入所有记录

-- 1 插入类别记录
insert into category(name,parent_id) values('计算机',0),('历史',0),('英语',0),('科学',0),('数学',0),('艺术',0);

-- 2 插入图书记录
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'Android权威指南','王贺',58.8,'人民邮电出版社','2016-02-01',3);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'linux私房菜','鸟哥',76.5,'人民邮电出版社','2015-09-21',10);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'Java编程思想','布鲁斯',110,'机械工业出版社','2008-05-30',12);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(1,'JavaScript DOM 编程艺术','吉米科',49.0,'人民邮电出版社','2016-02-01',4);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(3,'英语常用口语100句','李希',28,'南方工业出版社','2002-04-25',6);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(5,'高等数学一','王志胜',45,'吉林工业出版社','2010-04-25',6);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(5,'高等数学2','王志胜',48,'吉林工业出版社','2010-04-25',6);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(2,'三国鼎立','吴风',55,'人民出版社','2010-09-18',6);
insert into bookinfo(category_id,book_name,auther,price,press,public_date,store) values(2,'且看盛唐','文旭烟',32,'人民出版社','2013-11-29',6);

-- 3 插入读者记录
insert into reader values('330100199201231104','小明','男',29,'15787223423',300);
insert into reader values('330100199201231105','小红','女',21,'15787223424',220);
insert into reader values('330100199201231106','王迷','男',19,'15787223425',310);
insert into reader values('330100199201231107','李思','女',24,'15787223426',230);
insert into reader values('330100199201231108','小王','女',32,'15787223427',240);
insert into reader values('330100199201231109','王霞','女',26,'15787223428',300);

3、用mysql语句完成一次借阅

需求：

身份证号为‘330100199201231107’的读者，今天借了一本编号为’10002‘的图书，完成以下业务需求：

1、向借阅信息表中插入借书信息

insert into borrowinfo(book_id,card_id,borrow_date,return_date,is_return) values('10002','330100199201231107',curdate(),date_add(curdate(),interval 1 month),'否');

2、更新图书信息表的库存

update bookinfo set store=store-1 where book_id='10002';

3、更新读者信息表的余额

select price from bookinfo where book_id='10002';
update reader set balance=balance-0.03*110 where card_id='330100199201231107';

4、mysql查询语句

1、查看图书信息表中有哪些出版社？（distinct可以消除重复）

select distinct press from bookinfo;

2、查看图书信息表中出版社为人民出版社的图书有几本？

select count(*)from bookinfo where press='人民出版社';

3、查看读者信息表中女读者有几个？

select count(*) from reader where sex='女';

4、查看读者信息表中男女读者分别有几个？

select sex,count(*) from reader group by sex;

5、查看图书信息表中只有一种图书的出版社有哪些？

select press from bookinfo group by press having count(press)=1;

6、查看图书信息表的信息，要求出版日期以’201702’这样的形式显示

select book_id,book_name,date_format(public_date,'%Y%m') from bookinfo;

7、将图书信息表按库存降序，价格升序排列

select*from bookinfo order by store desc,price;

8、查询读者信息表中从第2条开始的3条读者记录。

select *from reader limit 1,3;

9、将图书信息表按照库存进行分组，统计每组库存下的个数，按照库存降序排序，并且查看结果中的前4条记录。

select store,count(*) from bookinfo group by store order by store limit 4;

5、mysql删除语句

1、从读者信息表中删除身份证号为‘330100199201231105’的读者记录

delete from reader where card_id='330100199201231105';

2、清除借阅信息表中的所有记录

delete from borrowinfo;

– 删除并重建一个表

truncate table borrowinfo;

3、删除图书信息表中所有计算机类的书

delete from bookinfo where category_id=(select id from category where name='计算机');