select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';
insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';
select * from readerfee;
身份证号为5461xxxxxxx的读者将超限的图书20201101归还,根据描述实现如下需求:
更新借阅信息表,将借阅状态(status)更新为‘是’。
更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
update borrowinfo set status = '是'where book_id = 20201101 and card_id = '5461xxxxxxx';
select * from borrowinfo;
update readerfee set actual_return_date=sysdate(), book_fee=datediff(sysdate(),return_date)*0.2 where book_id = 20201101 and card_id = '5461xxxxxxx';
ALTER TABLE bookinfo ADD FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);
删除外键约束
ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;
外键约束的参照操作
cascade,从父表删除或更新且自动删除或更新子表中匹配的行
create table bookinfo( book_id int primary key, book_category_id int, constraint fk_cid foreign key (book_category_id) references bookcategory(category_id) on delete cascade);
创建图书管理系统表
图书类别表
create table bookcategory( category_id int primary key, category varchar(20) not null unique, parent_id int not null );
图书信息表
create table bookinfo(
book_id int primary key, book_category_id int, book_name varchar(20) not null unique, author varchar(20) not null, price float(5,2) not null, press varchar(20) default '机械工业出版社', pubdate date not null, store int not null, constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id)
);
读者信息表
create table readerinfo(
card_id char(18) primary key, name varchar(20) not null, sex enum('男','女','保密') default '保密', age tinyint, tel char(11) not null, balance decimal(7,3) default 200
);
借阅信息表
create table borrowinfo(
book_id int, card_id char(18), borrow_date date not null, return_date date not null, status char(11) not null, primary key(book_id,card_id)
);
9.数据库表记录的操作
单表数据记录的插入
语法格式:
insert into table_name(column_list) values(value_list);
为表的所有列插入数据
insert into bookcategory (category_id,category,parent_id)values (1,'x',0);
insert into bookcategory values(2,'y',0);
为表的指定列插入数据
insert into readerinfo (card_id,name,tel)values('4562135465','张飞','4651354651');
同时插入多条记录
insert into bookcategory(category_id,category,parent_id)values(3,'x',1),(4,'y',1),(5,'z',2);
将查询结果插入的表中
insert into bookcategory select * from testwhere id>5;
自动增加
设置表的属性值自动增加:
列名 数据类型 auto_increment
创建表时添加自增列
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('dadaqianduan',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 borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20202010,46516874,'2020-11-29','2020-12-29','否');
更新读者信息表中的余额
查看书的价格 79.80 select price from bookinfo where book_id = 20202010;
更新余额
update readerinfo set balance = balance - 79.80*0.05 where card_id = '46516874'; select * from readerinfo;
更新图书信息表的库存
update bookinfo set store = store -1 where book_id = 20150201; select * from bookinfo;
单表数据记录的删除
删除指定条件的记录
delete from readerinfo where card_id = '46461265464565';
select borrowinfo.book_id,book_name,borrowinfo.card_id, name, tel, return_date, status from borrowinfo inner join bookinfo on borrowinfo.book_id = bookinfo.book_id inner join readerinfo on borrowinfo.card_id = readerinfo.card_id where borrowinfo.status = '否';
select t1.book_id,book_name,t1.card_id, name, tel, return_date, status from borrowinfo t1 join bookinfo t2 on t1.book_id = t2.book_id join readerinfo t3 on t1.card_id = t3.card_id where t1.status = '否';
外连接
外连接将查询多个表中相关联的行。
外连接分为:左外连接 left outer join;右外连接right outer join
根据业务需要,我们需要查看图书类别表中的所有类别下都有哪些图书。
select book_id, book_name, category from bookcategory left join bookinfo on bookcategory.category_id = bookinfo.book_category_id where parent_id<>0;
select book_id, book_name, category from bookinfo a right join bookcategory b on b.category_id = a.book_category_id;
select * from bookcategory;
左外连接:显示左表全部记录,右表满足连接条件的记录。
右外连接:显示右表全部记录,左表满足连接条件的记录。
语法结构:
select column_list from t1
left | right [outer] join t2 on join_condition1;
自连接
如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接
查询所有图书类别的图书类别编号,类别名称,上级分类名称。
select * from bookcategory;
select s.category_id as'图书类别编号', s.category as '图书类别名称', p.category as'图书的上级分类名称' from bookcategory s inner join bookcategory p on s.parent_id = p.category_id;
多表更新
update table1 {[inner] join | {left|right} [outer] join} table2 on conditional_expr set col1 = {expr1|default} [,col2 = {expr2|default}]... [where where_condition]
身份证号为432xxxxxx的读者将超时的图书86154归还,根据描述实现如下需求:
更新借阅信息表,将借阅状态(status)更新为‘是’。
更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
同时更新读者信息表的余额。(在余额中扣除罚款金额)
update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_id set actual_return_date = sysdate(),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance - book_fee where t1.book_id = 86154 and t1.card_id = '432xxxxxx';
select * from readerinfo;
多表删除
delete table1[.*], table2[.*] from table1 {[inner]join|{left|right}[outer]join} table2 on conditional_expr [where where_condition]
图书类别表,图书信息表:
由于业务需求,需要删除图书类别表中在图书信息表中没有图书记录的类别。
select book_id,book_name,category from bookcategory_bak t1 left join bookinfo_bak t2 on t1.category_id = t2.book_category_id where parent_id<>0;
delete t1 from bookcategory_bak t1 left join bookinfo_bak t2 on t1.category_id = t2.book_category_id where parent_id<>0 and book_id is null;
select * from bookcategory_bak;
需要删除图书类别表的编程语言的类别,以及图书信息表中关于编程语言的图书记录。
select book_id,book_name,category_id,category from bookcategory_bak t1 inner join bookinfo_bak t2 on t1.category_id = t2.book_category_id;
delete t1,t2 from bookcategory_bak t1 inner join bookinfo_bak t2 on t1.category_id = t2.book_category_id where t1.category_id = 3;
delimiter // create procedure selectproc1() begin select book_id, book_name, price, store from bookinfo; end // delimiter;
call selectproc();
删除存储过程:
drop procedure [if exists] proc_name;
创建一个查询图书的编号、书名、价格和库存的存储过程。 delimiter // create procedure selectproc1() begin select book_id,book_name,price,store from bookinfo; end// delimiter ;
调用存储过程 call selectproc1();
创建查询图书编号、书名、图书类别的存储过程 delimiter // create procedure proc1() begin select book_id,book_name,category from bookinfo t1 join bookcategory t2 on t1.book_category_id = t2.category_id; end// delimiter ;
call proc1();
设计一个存储过程,删除一个读者,并输出剩余读者的个数。 delimiter // create procedure proc2(in cid char(18), out num int) begin delete from readerinfo where card_id = cid; select count(card_id) into num from readerinfo; end// delimiter ;
select * from readerinfo; call proc2('6545xx', @num); select @num;
设计一个存储过程,实现交换两个数的处理。 delimiter // create procedure proc3(inout num1 int, inout num2 int) begin declare t int default 0; set t = num1; set num1 = num2; set num2 = t; end// delimiter ;