MySql多表增删查改

#school 是否有school，有的话删除
drop database if EXISTS school;
#创建school数据库
create database school;
use school;

#图书信息表 设定1001开始
drop table if EXISTS books;
create table books(
bno int(4) not null primary key auto_increment,
bname VARCHAR(20) not null,
author VARCHAR(20),
price float not null,
quanitity int(4) not null
)auto_increment=1001;

#借书信息表 设定101开始
drop table if EXISTS card;
create table card(
cno int(4) not null primary key auto_increment,
name VARCHAR(20) not null unique,
class VARCHAR(20) not null
)auto_increment=101;
#图书借阅信息表
drop table if EXISTS info;

create table info(
id int(4) not null primary key auto_increment,
cno int(4) REFERENCES card(cno),
bno int(4) REFERENCES book(bno),
rdate datetime
);
show tables;
desc books;#表结构
#插入图书数据books
insert into books(bname,price,author,quanitity)values("红楼梦",50,"施耐庵",30),("三国演义",50,"xxx",20),("情商",50,"qqq",10);

select * from books;
#插入借书信息表card
insert into card(name,class) values("张三","表演1班"),("李四","模特2班"),("田七","歌手1班");

select * from card;

#插入借阅信息表info
insert into info values(1,101,1001,NOW());
insert into info values(2,102,1002,NOW());
insert into info values(3,103,1003,NOW());

select * from info;
--修改
update card set class="大数据1班" where cno=101;

--查询所有
select * from books b,card c,info i where b.bno=i.bno and c.cno=i.cno;
--删除
delete from info where id=3;