mysql数据库知识6-数据库表记录的操作

-- 为表的所有列插入数据
insert into bookcategory(category_id,category,parent_id)values(1,'计算机',0);

insert into bookcategory values(2,'医学',0);

-- 为表的指定列插入数据
insert into readerinfo(card_id,name,tel)values('210210199901011111','张飞','13566661111');

-- 同时插入多条记录
insert into bookcategory(category_id,category,parent_id)values(3,'编程语言',1),(4,'数据库',1),(5,'儿科学',2);

-- 将查询结果插入的表中
insert into bookcategory select * from test where id>5;

-- 创建表时添加自增列
create table bookcategory_tmp(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
)auto_increment=5;

-- 测试自增列
insert into bookcategory_tmp(category,parent_id)values('医学',0);

 

-- 创建表时添加自增列
create table bookcategory_tmp(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
)auto_increment=5;

-- 测试自增列
insert into bookcategory_tmp(category,parent_id)values('医学',0);

-- 去掉自增列
alter table bookcategory_tmp modify category_id int;

-- 添加自增列
alter table bookcategory_tmp modify category_id int auto_increment;

-- 修改自增列的起始值
alter table bookcategory_tmp auto_increment = 15;

insert into bookcategory_tmp(category,parent_id)values('文学',0);

-- 删除图书信息表的外键
alter table bookinfo drop foreign key fk_bcid;

-- 为图书类别表添加自动编号的功能
alter table bookcategory modify category_id int auto_increment;

-- 恢复关联
alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);

 

/*图书类别表*/
insert into bookcategory(category,parent_id)values('计算机',0),('医学',0),('编程语言',1),('数据库',1),('儿科学',2);

/*图书信息表*/
insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(20150201,3, 'Java编程思想', '(美)埃克尔', 79.8, default, '2007-04-01', 5),
(20150202,4, 'PHP和MySQL Web开发', 'Luke Welling等',95, default, '2009-04-01', 2),
(20150301,3, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3),
(20160801,5, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1),
(20170401,5, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4);

/*读者信息表*/
insert into readerinfo(card_id,name,sex,age,tel,balance)
values
(210210199901011111,'张飞','女',18,'13566661111',300),
(210210199901012222,'李月','女',19,'13566662222',200),
(210210199901013333,'王鹏','男',20,'13566663333',300),
(210210199901014444,'刘鑫','男',21,'13566664444',400),
(210210199901015555,'杨磊','男',22,'13566665555',500);

/*
身份证号为210210199901011111的读者，2017-11-29借走了图书编号为20150201的这本书，
根据业务需求向借阅信息表插入一条借阅信息，更新读者信息表中的余额和图书信息表的库存，要求如下：
1、借书的期限为1个月。
2、借书的费用为：书的价格*5%
3、图书信息表中图书编号为20150201这本书的库存-1
*/

-- 向借阅信息表插入一条借阅信息
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20150201,210210199901011111,'2017-11-29','2017-12-29','否');

-- 更新读者信息表中的余额
-- 查看书的价格 79.80
select price from bookinfo where book_id = 20150201;

-- 更新余额
update readerinfo set balance = balance - 79.80*0.05 where card_id = '210210199901011111';
select * from readerinfo;

-- 更新图书信息表的库存
update bookinfo set store = store -1 where book_id = 20150201;
select * from bookinfo;

 

-- 删除指定条件的记录
delete from readerinfo where card_id = '210210199901011111';
-- 删除表中所有记录
delete from readerinfo;

/*读者信息表*/
insert into readerinfo(card_id,name,sex,age,tel,balance)
values
(210210199901011111,'张飞','女',18,'13566661111',300),
(210210199901012222,'李月','女',19,'13566662222',200),
(210210199901013333,'王鹏','男',20,'13566663333',300),
(210210199901014444,'刘鑫','男',21,'13566664444',400),
(210210199901015555,'杨磊','男',22,'13566665555',500);

truncate table readerinfo;

/*
图书管理系统要下架所有关于儿科学的书，实现的需求如下：
1、删除图书信息表中的所有关于儿科学的书。
2、删除图书类别表中儿科学这一类别
*/

-- 查询儿科学的类别编号 5
select category_id from bookcategory where category='儿科学';

-- 删除图书编号为5的图书信息
delete from bookinfo where book_category_id = 5;

-- 删除图书类别表中儿科学这个类别
delete from bookcategory where category = '儿科学';

 

-- 查询所有列
select * from bookcategory;

select category_id,category,parent_id from bookcategory;

-- 查询指定列
select category from bookcategory;

select category_id,category from bookcategory;

-- 查询指定条件的记录
select book_id,book_name,price from bookinfo where press='机械工业出版社';

-- 查询结果不重复的记录
select distinct press from bookinfo;

-- 查看空值
select * from readerinfo where age is null;

-- 统计读者信息表中男读者的人数
select count(*) from readerinfo where sex='男';

-- 将读者信息表中的记录按性别进行分组
select sex from readerinfo group by sex;

-- 将读者信息表中的记录按性别进行分组，并统计每种性别的人数
select sex,count(*) from readerinfo group by sex;

-- 将读者信息表中的记录按性别进行分组，分组后人数大于的性别
select sex from readerinfo group by sex having count(sex)>2;

-- 单列排序
select * from bookinfo order by price;

-- 多列排序
select * from bookinfo order by price,store;

-- 指定排序方向
select * from bookinfo order by price,store desc;

-- 前3行记录
select * from bookinfo limit 3;

-- 从第3条记录开始的后2条记录
select * from bookinfo limit 2,2;

select * from bookinfo limit 2 offset 2;

insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(20160011,4, '高性能MySQL', 'Baron Schwartz等',85.8, '电子工业出版社', '2013-04-01', 10),
(20160210,4, '数据库系统基础教程', '[美]厄尔曼等', 35.5, '机械工业出版社', '2009-08-01', 20),
(20170050,4, '数据库系统实现', '[美]加西亚·莫利纳 ', 46.6, '机械工业出版社', '2010-05-01',8);

-- 将图书信息按照库存进行分组，统计每组库存下的个数，然后按库存进行降序排序，并查看结果中的前四条记录。

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