MySQL的实操

于 2023-10-29 22:53:40 发布
阅读量530 收藏 3
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_47428520/article/details/133678391
版权

学完了MySQL的基础知识，于是在网上找了三个对表操作的需求，通过实操让自己更加熟练掌握MySQL;

1. 图书管理系统：

设计一个图书管理系统，其中需要包括的表有：图书表（图书ID，书名，作者，出版社，出版日期，价格等），借阅者表（借阅者ID，姓名，联系方式等），借阅记录表（借阅记录ID，借阅者ID，图书ID，借阅日期，归还日期等）。

第一版

图书表

create table books(
	id int unsigned AUTO_INCREMENT,				#图书ID
	name varchar(128) NOT NULL,					#书名
	author varchar(64) NOT NULL,				#作者
	press varchar(256) NOT NULL,				#出版社
	pulishing_time date NOT NULL,				#出版时间
	price decimal(5, 2) unsigned NOT NULL,		#价格 
	state bool DEFAULT false,					#false为未借出
	PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#其实这里可以不用写出来的，因为默认的存储引擎就是InnoDB，默认的字节设置就utf8mb4；我这里设置为utf8；

本来我想这个图书表使用MyISAM存储引擎的，但是后面发现借阅记录表需要使用这个表的id作为外键，因为MyISAM不支持外键，于是就改为使用InnoDB存储引擎了。

借阅者表

create table borrowers(
	id varchar(18) PRIMARY KEY,					#借阅者ID
	name varchar(64) NOT NULL,					#姓名
	contact_info varchar(11) NOT NULL,			#联系方式
	borrow_books tinyint unsigned default 0,	#借阅图书的数量
	CONSTRAINT check_max_borrow CHECK(borrow_books <= 10)
)DEFAULT CHARSET=utf8;

#本来我是这里设置存储引擎是MyISAM
#但是我要求每个人最多借阅10本图书，所以需要改回来为InnoDB
#注意：CONSTRAINT check_max_borrow CHECK(borrow_books <= 10)这个需要MySQL是8.0.16或更高版本

借阅记录表

create table borrower_records(
	id bigint unsigned AUTO_INCREMENT,			#借阅记录ID
	borrower_id varchar(18) NOT NULL,			#借阅者ID
	book_id int unsigned NOT NULL,				#图书ID
	borrow_date datetime NOT NULL,				#借阅日期
	return_data datetime default NULL,			#归还日期
	PRIMARY KEY(id),							#主键
	FOREIGN KEY(book_id) REFERENCES books(id)	#外键，关联图书表的id
)DEFAULT CHARSET=utf8;

触发器

#借阅书籍时触发的定时器(借阅者表)
DELIMITER $$
create trigger tri_insert_book after insert on borrower_records
for each row 
BEGIN
update borrowers set borrow_books =borrow_books +1 
where borrowers.id=NEW.borrower_id ;

update books set state=true 
where books.id  = NEW.book_id ;
END;
$$
DELIMITER ;

#归还书籍时候触发的定时器(借阅者表)
DELIMITER $$
create trigger tri_delete_book after update on borrower_records for each row 
BEGIN
IF NEW.return_data IS NOT NULL AND OLD.return_data IS NULL THEN
update borrowers set borrow_books=borrow_books-1 where borrowers.id = OLD.borrower_id; 

update books set state=false where books.id  = OLD.book_id ;
END IF;
END;
$$
DELIMITER ;

删除表

drop table borrower_records;
drop table borrowers;
drop table books;

插入数据

#插入图书数据
insert into books values(0, '小狗钱钱','舍费尔', '北京出版社', '1998-01-01', 18.5, default);
insert into books values(0, '怪诞行为学','丹艾瑞里', '北京出版社', '1998-01-02', 31.2, default);
#插入借阅者数据
insert into borrowers values('441224199801016666', '李二狗', '13166624067', default);

查看数据(尽量少点使用select *)

select * from books;
select * from borrowers;

借阅图书

#借阅图书
insert into borrower_records(id, borrower_id, book_id, borrow_date) values(0, '441224199801016666', 2,  NOW());


insert into borrower_records(id, borrower_id, book_id, borrow_date) values(0, '441224199801016666', 1,  NOW());

插看数据(发生回表，能使用覆盖索引就使用覆盖索引)

#插看数据
select * from books where id = 2;
select * from borrowers where id='441224199801016666';
select * from borrower_records where id=1;

归还图书

#归还图书
update borrower_records  set return_data =NOW() where book_id=2;

------------------------------------第二版----------------------------------------------

在这个上面的系统中执行以下操作：

  • 根据书名查询图书信息。
  • 根据借阅者姓名查询其借阅的所有图书。
  • 添加新的图书和借阅者信息。
  • 更新借阅记录，例如添加新的借阅记录，或者更新归还日期。
根据书名查询图书信息。
create table books(
	id int unsigned AUTO_INCREMENT,				#图书ID
	name varchar(128) NOT NULL,					#书名
	author varchar(64) NOT NULL,				#作者
	press varchar(256) NOT NULL,				#出版社
	pulishing_time date NOT NULL,				#出版时间
	price decimal(5, 2) unsigned NOT NULL,		#价格 
	state bool DEFAULT false,					#false为未借出
	PRIMARY KEY(id)
	#INDEX index_col(id, name, state) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

create index books_name on books(name);
insert into books values(0, '小狗钱钱','舍费尔', '北京出版社', '1998-01-01', 18.5, default);

select * from books where name='小狗钱钱';
这样依旧会是全表查询；




mysql> explain select * from books where name='小狗钱钱';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | books | ALL  | books_name    | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

drop table books;

######################################
create table books(
	id int unsigned AUTO_INCREMENT,				#图书ID
	name varchar(128) NOT NULL,					#书名
	author varchar(64) NOT NULL,				#作者
	press varchar(256) NOT NULL,				#出版社
	pulishing_time date NOT NULL,				#出版时间
	price decimal(5, 2) unsigned NOT NULL,		#价格 
	state bool DEFAULT false,					#false为未借出
	#PRIMARY KEY(id),
	INDEX index_col(id, name, state) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into books values(0, '小狗钱钱','舍费尔', '北京出版社', '1998-01-01', 18.5, default);


#如果是查询所有字段，那么是全表查询
mysql> explain select * from books where name='小狗钱钱';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | books | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

#使用联合索引
mysql> explain select id, name, state from books where id > 0 AND name='小狗钱钱';
+----+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | books | index | index_col     | index_col | 392     | NULL |    6 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+--------------------------+

1.因为有需求是根据书名查询图书信息，如果直接使用上面的表，不添加索引的话，那么通过书名查询就会是全表查询，我们要避免全表查询，所以需要给书名加一个索引，这样就可以避免全表查询。
create index books_name on books(name);

insert into books values(0, ‘小狗钱钱’,‘舍费尔’, ‘北京出版社’, ‘1998-01-01’, 18.5, default);
insert into books values(0, ‘怪诞行为学’,‘丹艾瑞里’, ‘北京出版社’, ‘1998-01-02’, 31.2, default);

explain select * from books where name=‘小狗钱钱’;

使用联合索引好还是索引的联合好？？？

explain select id, name, state from books where id > 0 AND name=‘小狗钱钱’;

根据借阅者姓名查询其借阅的所有图书。
create table borrower_records(
	id bigint unsigned AUTO_INCREMENT,			#借阅记录ID
	borrower_id varchar(18) NOT NULL,			#借阅者ID
	book_id int unsigned NOT NULL,				#图书ID
	borrow_date datetime NOT NULL,				#借阅日期
	return_data datetime default NULL,			#归还日期
	PRIMARY KEY(id),							#主键
	FOREIGN KEY(book_id) REFERENCES books(id)	#外键，关联图书表的id
)DEFAULT CHARSET=utf8;

create index borrower_id on borrower_records(borrower_id);

insert into books values(0, ‘小狗钱钱’,‘舍费尔’, ‘北京出版社’, ‘1998-01-01’, 18.5, default);
insert into books values(0, ‘怪诞行为学’,‘丹艾瑞里’, ‘北京出版社’, ‘1998-01-02’, 31.2, default);
#插入借阅者数据
insert into borrowers values(‘441224199801016666’, ‘李二狗’, ‘13166624067’, default);

insert into borrower_records(id, borrower_id, book_id, borrow_date) values(0, ‘441224199801016666’, 2, NOW());

select * from borrower_records where borrower_id=‘441224199801016666’;

mysql> explain select * from borrower_records where borrower_id='441224199801016666';
+----+-------------+------------------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | borrower_records | ref  | borrower_id   | borrower_id | 56      | const |    1 | Using where |
+----+-------------+------------------+------+---------------+-------------+---------+-------+------+-------------+

-----------------------------到此----------------------------

---------------------------------end--------------------------------

#一共借阅的时间
SELECT return_data - borrow_date AS time_difference
FROM borrower_records;

#假设图书最多借30天，29天时候还没还的话就通知借阅者，怎么知道到了29天了？
来一个线程去找29天的和未还图书的，这样就知道都有谁借了29天还没有还图书的了。

1.自增怎么insert数据？
自增的字段设置为0

2.如果数据超出了怎么办？报错误吗？报什么错误？
不会报错，但是如果是主键或者唯一索引的话，并且一样的话就会报错；
比如：insert into borrowers values('441224199801016666', '李二狗', '13166624067', default);与
insert into borrowers values('4412241998010166667777', '李二狗', '13166624067', default);就会报错，因为borrowers的id的类型是varchar(18)，这两个数据的id前面18个字节是一样的，所以会报错；

但是，insert into borrowers values('4412241998010166677777', '李二狗', '13166624067', default);这个和第一个就不会报错，因为有一个是id是441224199801016666，另一个id是441224199801016667。

3.怎么获得现在日期
NOW() CURDATE() TIME() 
需要吧设置借阅者ID和图书ID索引啊，不然where borrower时候就全文检索了，速度会很慢
或者外键，他们二者那个更好？

我需要知道同一个书名的书到底还有多少本是未借出的或者是借出的。
create index name on books(name);

 explain select * from books where name='小狗钱钱';是使用全文索引，应该建立怎样的索引才合适？
 explain的每个参数代表什么？
 借阅者应该有借阅图书的数量，一次同时只能借10本
 需要使用触发器或者存储过程才能触发

3.一般图书还有abc等分组的，所以最好不要使用unsigned int

思路：

1.图书表的图书ID作为自增主键，

2.一个图书馆允许有多本相同的书，相同的书在图书表上仅图书ID不同，其他都是相同的。

3.借阅者表的借阅者ID用身份证表示，主键

4.借阅记录表的借阅记录ID作为主键

我想要知道某本书的借阅者的联系方式
1通过在借阅记录表查询图书的id得到所有比图书id的记录
2然后得到最新的借阅记录id，得到借阅者id
3通过借阅者id在借阅表查询联系方式

2. 学生选课系统：

设计一个学生选课系统，需要包括的表有：学生表（学号，姓名，专业等），课程表（课程号，课程名，教师，学分等），选课记录表（记录编号，学号，课程号，选课时间等）。在这个系统中执行以下操作：

  • 根据学生姓名查询其所选的所有课程。
  • 查询某一课程的所有学生。
  • 添加新的学生，课程和选课记录。
  • 更新选课记录，例如学生退选某一门课程。

学生表

create table students(
	id int unsigned,				#学号
	name varchar(128) NOT NULL,		#姓名
	major varchar(64) NOT NULL,		#专业
	PRIMARY KEY(id)
)DEFAULT CHARSET=utf8;

课程表

create table course(
	id tinyint unsigned AUTO_INCREMENT,		#课程号
	name varchar(64) NOT NULL,				#课程名
	teacher varchar(8) NOT NULL,			#教师
	credit float unsigned NOT NULL,			#学分
	PRIMARY KEY(id)	
)DEFAULT CHARSET=utf8;

-----------------------分割线-------------------

3. 电商订单管理系统：

设计一个电商订单管理系统，其中需要包括的表有：商品表（商品ID，商品名，价格，库存等），客户表（客户ID，姓名，联系方式等），订单表（订单ID，客户ID，下单时间，总金额等），订单明细表（明细ID，订单ID，商品ID，购买数量，小计等）。在这个系统中执行以下操作：

  • 根据客户查询其所有订单及订单包含的商品。
  • 根据商品查询所有购买过它的客户。
  • 添加新的商品，客户和订单。
  • 更新订单明细，例如修改购买数量。
Q之路
关注
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值