MySQL数据库表记录的操作(第六章)

本章开始,所有的SQL操作都在SQLyog中进行。

1 单表数据记录的插入

插入数据
插入数据的方式
为所有列插入数据
向bookcategory表中插入数据:

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

为指定列插入数据
向readerinfo表中插入指定数据:

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

同时插入多条记录
向bookcategory表中同时插入多条记录:

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

将查询结果插入到表中
将test表中符合查询条件的结果插入到bookcategory表中(test表和bookcategory表具有相同的结构):

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

2 设置自动编号(1)

在这里插入图片描述
设置表的属性值自动增加
自动编号举例
举例:创建表category_tmp:

CREATE TABLE bookcategory_tmp(
category_id INT PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(20) NOT NULL UNIQUE,
parent_id INT NOT NULL
)

往表中添加2条数据:

INSERT INTO bookcategory_tmp(category, parent_id)VALUES('计算机', 0);
INSERT INTO bookcategory_tmp(category, parent_id)VALUES('医学', 0);

结果:
category_tmp表id自动增长
在建表时指定从某个数字开始自动进行编号:

-- 如果存在先删除
DROP TABLE bookcategory_tmp;

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;

往表中插入2条数据:

INSERT INTO bookcategory_tmp(category, parent_id)VALUES('计算机', 0);
INSERT INTO bookcategory_tmp(category, parent_id)VALUES('医学', 0);

结果:
创建表id自动增长,且id从指定数字开始增长

3 设置自动编号(2)

为已有的表添加自增列
修改自增列的起始值
去掉自增列
删除表bookcategory_tmp的自增列:

ALTER TABLE bookcategory_tmp MODIFY category_id INT;

然后添加一条数据:

INSERT INTO bookcategory_tmp(category, parent_id)VALUES('小说', 1);

结果:
删除自动增长后插入数据
category_id没有设置自动增长,此时插入数据应写如下代码:

INSERT INTO bookcategory_tmp(category_id, category, parent_id)VALUES(1, '小说', 1);

再给表bookcategory_tmp添加自动增长:

ALTER TABLE bookcategory_tmp MODIFY category_id INT AUTO_INCREMENT;

往表中插入一条数据:

INSERT INTO bookcategory_tmp(category, parent_id)VALUES('历史', 2);

结果:
添加自动增长
给已经添加了自动增长的表添加自动增长的初值:

ALTER TABLE bookcategory_tmp AUTO_INCREMENT = 15;

往表中插入一条数据:

INSERT INTO bookcategory_tmp(category, parent_id)VALUES('英语', 2);

结果:
给表设置自动增长的初值
拓展操作:
我们在上一章中建立了4个表,其中bookinfo和bookcategory有外键关联关系,如下图:
外键关联的表
现在需要给表bookcategory的列category_id添加自动增长,sql如下:

-- 1.删除外键约束
ALTER TABLE bookinfo DROP FOREIGN KEY fk_bcid;
-- 2.添加自动增长
ALTER TABLE bookcategory MODIFY category_id INT AUTO_INCREMENT;
-- 3.恢复外键约束
ALTER TABLE bookinfo ADD CONSTRAINT fk_bcid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);

4 单表数据记录的更新

单表记录更新
单表记录更新语法
单表记录更新案例
首先提供一些测试数据:

USE book;
/*图书类别表*/
INSERT INTO bookcategory(category,parent_id)VALUES('计算机',0),('医学',0),('编程语言',1),('数据库',1),('儿科学',2);
bookcategory
/*图书信息表*/
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。

具体sql语句如下:

-- 向借阅信息表插入一条借阅信息
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;

5 单表数据记录的删除

单表记录的删除
举例删除表readerinfo中card_id = '210210199901011111’的记录:

DELETE FROM readerinfo WHERE card_id = '210210199901011111';

结果:
删除表中的一条记录
如果删除表中的所有记录,可以使用以下sql:

DELETE FROM readerinfo;

使用truncate删除单表记录
注意: 使用关键字DELETE只能删除表中的内容,不能删除表的结构;使用关键字TRUNCATE不但可以清空表的内容,还可以将整个表结构删除,并重新创建一个新表。有人会问:delete删除表内容,truncate删除表内容和表结构并重新创建一个新表有什么区别?
这里的主要区别在于,如果表中的键是自动增长策略,那么使用delete删除表内容后,如果再往表中插入数据,则自动增长的那列的值是按照删除之前的值继续增长而不是从1开始;使用truncate删除表内容和表结构,重新创建一个新表,插入数据自动增长列会从1开始增长。使用truncate删除表的速度比delete要快,因为它直接删除表。
业务需求:
案例
sql如下:

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

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

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

6 单表数据记录的查询

基本查询语句
查询所有列
例如:查询表bookcategory中的所有记录:

-- 查询表中的所有记录(方式1)
SELECT * FROM bookcategory;
-- 查询表中的所有记录(方式2)
SELECT category_id,category,parent_id FROM bookcategory;

结果:
查询表bookcategory中的所有记录
查询指定列
查询图书类别字段:

-- 查询图书类别字段
SELECT category FROM bookcategory;

结果:
查询图书类别字段
查询图书编号和图书类别字段:

-- 查询图书编号和图书类别字段
SELECT category_id,category FROM bookcategory;

结果:
查询图书编号和图书类别字段
查询指定记录
查询表bookinfo中出版社为“机械工业出版社”的记录:

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

结果:
查询表bookinfo中出版社为“机械工业出版社”的记录
查询结果不重复
查询表bookinfo中的出版社字段:

-- 查询出版社字段
SELECT press FROM bookinfo;

结果:
查询出版社字段
查询表bookinfo中的出版社字段并去重:

-- 查询出版社字段并去重
SELECT DISTINCT press FROM bookinfo;

结果:
查询出版社字段并去重
查询空值
查询表readerinfo中年龄字段为空的记录:

-- 查询年龄为空的记录
SELECT * FROM readerinfo WHERE age IS NULL;

结果:
查询表readerinfo中年龄字段为空的记录

7 对查询结果进行分组

分组查询
创建分组
注意: count(*)会查询所有的记录的行数,即使改行没有数据;count(列值)会查询所有列值不为空的记录的行数。
查询读者信息表的记录数:

-- 查询性别为男的记录数
SELECT COUNT(*) FROM readerinfo WHERE sex='男';

查询读者信息表的记录数
分组查询读者信息表中的性别:

-- 分组查询性别
SELECT sex FROM readerinfo GROUP BY sex;

结果:
分组查询性别

使用having过滤分组
分组查询性别并统计记录数:

-- 分组查询性别并统计记录数
SELECT sex AS '性别',COUNT(*) AS '记录数' FROM readerinfo GROUP BY sex;

结果:
分组查询性别并统计记录数
分组查询性别的记录数大于2的性别:

-- 分组查询性别记录数大于2的性别
SELECT sex AS '性别',COUNT(*) AS '记录数' FROM readerinfo GROUP BY sex HAVING COUNT(sex) > 2;

结果:
分组查询性别的记录数大于2的性别

8 对查询结果进行排序

对查询结果排序
单列排序
查找表bookinfo中所有记录按价格排序:

-- 按价格排序(默认升序排列)
SELECT * FROM bookinfo ORDER BY price;

结果:
查找表bookinfo中所有记录按价格,库存排序
多列排序
查找表bookinfo中所有记录按价格,库存排序:

-- 按价格排序(价格一样时按库存排序)
SELECT * FROM bookinfo ORDER BY price,store;

结果:
查找表bookinfo中所有记录按价格,库存排序
指定排序方向
查找表bookinfo中所有记录按价格,库存(库存降序)排序:

-- 按价格排序(价格一样时按库存降序排序)
SELECT * FROM bookinfo ORDER BY price,store DESC;

结果:
查找表bookinfo中所有记录按价格,库存(库存降序)排序

9 通过Limit语句限制查询记录的数量

用limit限制查询结果的数量
限制查询例子
现在看看表bookinfo中都有哪些数据:

SELECT * FROM bookinfo

结果:
bookinfo表中所有记录
综合扩展:将图书信息按照库存进行分组,统计每组库存的个数,然后按库存进行降序排列,并查看结果中的前4条记录:

SELECT store AS '库存',COUNT(*) AS '数量' FROM bookinfo GROUP BY store ORDER BY store DESC LIMIT 4;

结果:
在这里插入图片描述

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值