04 增删改查:如何操作表中的数据?
你好,我是朱晓峰。今天,我们来聊一聊如何操作数据表里的数据。
在咱们的超市项目中,我们给用户设计好了一个数据表 demo.goodsmaster,定义好了里面的字段,以及各种约束,如下所示:
mysql> DESCRIBE demo.goodsmaster;
+---------------+--------------+------+-----+---------+--+
| Field | Type | Null | Key | Default |Extra |
+---------------+------------+------+-----+---------+------------+
| itemnumber | int | NO | PRI | NULL |auto_increment |
| barcode | text | NO | | NULL | |
| goodsname | text | NO | | NULL | |
| specification | text | YES | | NULL | |
| unit | text | YES | | NULL | |
| price | decimal(10,2)| NO | | NULL | |
+---------------+------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
现在,我们需要使用这个表来存取数据。那么,如何对表里面的数据进行操作呢?接下来,我就给你讲讲操作表中的数据的方法,也就是常说的“增删改查”。
添加数据
我们先来看一下添加数据的语法结构:
这里方括号“[]”表示里面的内容可选,也就是说,根据MySQL的语法要求,写不写都可以。
添加数据分为两种情况:插入数据记录和插入查询结果。下面我分别来介绍下。
插入数据记录
其实,MySQL支持的数据插入操作十分灵活。你既可以通过给表里面所有的字段赋值,完整地插入一条数据记录,也可以在插入记录的时候,只给部分字段赋值。
这是什么意思呢?我借助一个例子来给你讲解下。
比如,我们有一个叫demo.goodsmaster的表,包括了itemnumber、barcode、goodsname、specification、unit和price共6个字段。我想要插入一条数据记录,其中包含了所有字段的值,就可以这样操作:
INSERT INTO demo.goodsmaster
(
itemnumber,
barcode,
goodsname,
specification,
unit,
price
)
VALUES
(
4,
'0003',
'尺子',
'三角型',
'把',
5
);
运行这个SQL语句,然后对数据表进行查询,可以得到下面的结果:
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
+------------+---------+-----------+---------------+------+-------+
1 row in set (0.01 sec)
如果我想插入一条记录,但是只给部分字段赋值,可不可以呢?比如说,客户有个商品,需要马上上线销售,目前只知道条码、名称和价格,其它的信息先不录入,等之后再补,可以实现吗?
我们来尝试一下只给3个字段赋值,看看实际操作的结果:
INSERT INTO demo.goodsmaster
(
-- 这里只给3个字段赋值,itemnumber、specification、unit不赋值
barcode,
goodsname,
price
)
VALUES
(
'0004',
'测试',
10
);
运行这条SQL语句,我们来查询表的内容,就会发现,显然是可以的。
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
| 5 | 0004 | 测试 | NULL | NULL | 10.00 |
+------------+---------+-----------+---------------+------+-------+
2 rows in set (0.00 sec)
我们之所以能够在插入一条数据记录的时候,只给部分字段赋值,原因就在于我们对字段的定义方式。
那字段是怎么定义的呢?我们来查看一下表的结构,看看各个字段的定义:
mysql> DESCRIBE demo.goodsmaster;
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| itemnumber | int | NO | PRI | NULL | auto_increment |
| barcode | text | NO | | NULL | |
| goodsname | text | NO | | NULL | |
| specification | text | YES | | NULL | |
| unit | text | YES | | NULL | |
| price | decimal(10,2) | NO | | NULL | |
+---------------+---------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
可以看到,我们在插入数据时没有明确赋值的3个字段,都有着各自的特点。“specification”和“unit”都可以是空值,而itemnumber则定义了自增约束。
我们在插入一条数据记录的时候,必须要考虑字段约束的3种情况。
第一种情况是,如果字段允许为空,而我们没有给它赋值,那么MySQL会自动给它们赋予空值。在刚刚的代码中,“specification”“unit”字段都允许为空,因此,如果数据插入语句没有指定这几个字段的值,MySQL会自动插入空值。
第二种情况是,如果字段是主键,就不能为空,这个时候,MySQL会按照我们添加的约束进行处理。比如字段“itemnumber”是主键,不能为空,而我们定义了自增约束,所以MySQL自动在之前的最大值基础上加了1。因此,“itemnumber”也有了自己该有的值。
第三种情况是,如果有一个字段定义不能为空,又不是主键,当你插入一条数据记录的时候,就必须给这个记录赋值。
如果我们的操作违反了字段的约束限制,会出现什么情况呢?
比如说,我们尝试把表demo.goodsmaster的字段“speicification”改为不能为空:
运行这个SQL语句,系统会提示错误,原因就是我们刚才部分插入了一条数据记录,没有给字段“specification”赋值,这跟我们给字段“specification”添加非空约束的操作冲突了。
因此,我们要把字段“speicification”的值为空的数据记录删除,然后再修改字段约束:
删除数据记录之后,再运行上面的语句,给字段“specification”添加非空约束,就成功了。
现在我们来验证一下非空约束。我们尝试部分插入一条数据记录,不给字段“specification”赋值:
运行这个SQL语句,MySQL报告错误,提示字段“specification”没有默认值。也就是说,这个字段不能为空,如果插入数据时不给它赋值,就必须给它一个默认值。
现在,你一定清楚了,部分插入一条数据记录是可以的,但前提是,没有赋值的字段,一定要让MySQL知道如何处理,比如可以为空、有默认值,或者是自增约束字段,等等,否则,MySQL会提示错误的。
好了,到这里,我们就学会了给MySQL的数据表插入一条数据记录。但是,在实际工作中,一次只插入一条数据,有时候会不够用。
我们的项目就有这样的场景:门店每天的销售流水都很多,日积月累,流水表变得越来越大。如果就让它这么不断地增长,数据量甚至会达到数亿条,占据的存储空间达到几十个G。虽然MySQL可以处理这样比较大的数据表,但是每次操作的响应时间就会延长,这会导致系统的整体效率下降。
刚开始的时候,我们开发了一个日结处理,当天算清所有账目。其中一个步骤就是,把当天流水表的数据全部转到历史流水表当中去。现在,我们就可以用上数据插入语句了。具体有2步:
- 从流水表中取出一条数据;
- 把这条数据插入到历史流水表中。
然后不断重复这两个步骤,一直到把今天流水表的数据全部插入到历史流水表当中去。
你肯定会说,这种做法的效率也太低了吧?有没有更好的办法呢?
当然是有的。这个时候,我们就要用到MySQL的另一种数据插入操作了:把查询结果插入到数据表中。
插入查询结果
MySQL支持把查询的结果插入到数据表中,我们可以指定字段,甚至是数值,插入到数据表中。语法结构如下:
举个例子,在我们的超市信息系统的MySQL数据库中,历史流水表设计与流水表非常类似。不同的是,历史流水表增加了一些字段来标识历史流水的状态,比如日结时间字段,是用来记录日结操作是什么时候进行的。
用INSERT语句实现起来也很简单:
好了,添加数据的操作就讲完了,现在你知道了,我们给一张数据表插入一条数据记录的时候,可以给所有的字段赋值,也可以给部分字段赋值。这取决于字段的定义。如果字段不能为空并且没有默认值,就必须赋值。另外,我们还可以通过把一个查询结果插入数据表中的方式,提高添加数据的效率。
接下来,我们再来看看如何删除数据。
删除数据
数据删除的语法很简单,如下所示:
如果我们现在想把刚才用过的表demo.goodsmaster里的内容清理一下,删除全部数据,可以通过下面的SQL语句来实现:
因为我们的查询主要是在MySQL的图形化管理工具中进行,现在,我们在Workbench中运行上面的SQL语句。这个时候,Workbench会提示错误。
这是因为,Workbench自动处于安全模式,它要求对数据的删除或修改操作中必须包含WHERE条件。而且,这个WHERE条件中,必须用到主键约束或者唯一性约束的字段。MySQL的这种安全性设置,主要就是为了防止删除或者修改数据时出现误操作,导致删除或修改了不相关的数据。
因此,我们要习惯在删除数据的时候,添加条件语句WHERE,防止误操作。
假设我们的数据表demo.goodsmaster中有如下数据记录:
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
| 5 | 0004 | 测试 | NULL | NULL | 10.00 |
+------------+---------+-----------+---------------+------+-------+
2 rows in set (0.00 sec)
如果我们要删除所有数据,可以这样写:
好了,下面我们来学习一下修改数据。
修改数据
我们来看一下MySQL的数据修改语法:
语法很简单,需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
下面我们来看一个通过主键查询商品信息的例子,看看修改主键的值,会产生什么样的结果。
mysql> SELECT *
-> FROM demo.goodsmaster
-> WHERE itemnumber = 3;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 3 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
+------------+---------+-----------+---------------+------+-------+
1 row in set (0.00 sec)
我们能查询到一条商品编号是3的数据记录:条码是“0003”,名称是“尺子”,价格是5元。
如果我们修改了主键的值,就可能会改变刚才的查询结果:
mysql> UPDATE demo.goodsmaster
-> SET itemnumber=2
-> WHERE itemnumber = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT *
-> FROM demo.goodsmaster
-> WHERE itemnumber = 3;
Empty set (0.00 sec)
可以看到,查询结果是空,因为商品编号是3的数据记录,已经不存在了。
当然,如果你必须要修改主键的值,那有可能就是主键设置得不合理。关于主键的设置问题,我会在下节课给你详细介绍。这里你只需要知道,不要随便修改表的主键值,就可以了。
查询数据
经过了前面的操作之后,现在,我们知道了把数据存入数据表里,以及删除和修改表里数据的方法。那么如何知道数据操作的结果呢?这就要用到数据查询了。
我们先来看下查询语句的语法结构:
在这些字段中,SELECT、WHERE、GROUP BY和HAVING比较好理解,你只要知道它们的含义就可以了。
- SELECT:是查询关键字,表示我们要做一个查询。“*”是一个通配符,表示我们要查询表中所有的字段。你也可以把要查询的字段罗列出来,这样,查询的结果可以只显示你想要查询的字段内容。
- WHERE:表示查询条件。你可以把你要查询的数据所要满足的条件,放在WHERE关键字之后。
- GROUP BY:作用是告诉MySQL,查询结果要如何分组,经常与MySQL的聚合函数一起使用。
- HAVING:用于筛选查询结果,跟WHERE类似。
FROM、ORDER BY和LIMIT相对来说比较复杂,需要注意的地方比较多,我来具体给你解释一下。
FROM
FROM关键字表示查询的数据源。我们现在只学习了单个数据表,你可以把你要查询的数据表名,直接写在FROM关键字之后。以后我们在学到关联表的时候,你就会知道,FROM关键字后面,还可以跟着更复杂的数据表联接。
这里需要你要注意的是,数据源也不一定是表,也可以是一个查询的结果。比如下面的查询:
这里你要注意的是,红色框里的部分叫做派生表(derived table),或者子查询(subquery),意思是我们把一个查询结果数据集当做一个虚拟的数据表来看待。MySQL规定,必须要用AS关键字给这个派生表起一个别名。在这张图中,我给这个派生表起了个名字,叫做“a”。
ORDER BY
ORDER BY的作用,是告诉MySQL,查询结果如何排序。ASC表示升序,DESC表示降序。
我来举个简单的小例子,带你看看ORDER BY是怎么使用的(这里我们仍然假设字段“specification”和“unit”允许为空)。我们向表demo.goodsmaster中插入2条数据:
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0003',
'尺子1',
15
);
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0004',
'测试1',
20
);
如果我们不控制查询结果的顺序,就会得到这样的结果:
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 4 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
| 5 | 0004 | 测试 | NULL | NULL | 10.00 |
| 6 | 0003 | 尺子1 | NULL | NULL | 15.00 |
| 7 | 0004 | 测试1 | NULL | NULL | 20.00 |
+------------+---------+-----------+---------------+------+-------+
4 rows in set (0.00 sec)
如果我们使用ORDER BY对查询结果进行控制,结果就不一样了:
mysql> SELECT *
-> FROM demo.goodsmaster
-> ORDER BY barcode ASC,price DESC;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 6 | 0003 | 尺子1 | NULL | NULL | 15.00 |
| 4 | 0003 | 尺子 | 三角型 | 把 | 5.00 |
| 7 | 0004 | 测试1 | NULL | NULL | 20.00 |
| 5 | 0004 | 测试 | NULL | NULL | 10.00 |
+------------+---------+-----------+---------------+------+-------+
4 rows in set (0.00 sec)
可以看到,查询结果会先按照字段barcode的升序排序,相同barcode里面的字段,按照price的降序排序。
LIMIT
LIMIT的作用是告诉MySQL只显示部分查询的结果。比如,现在我们的数据表demo.goodsmaster中有4条数据,我们只想要显示第2、3条数据,就可以用LIMIT关键字来实现:
mysql> SELECT *
-> FROM demo.goodsmaster
-> LIMIT 1,2;
+------------+---------+-----------+---------------+------+-------+
| itemnumber | barcode | goodsname | specification | unit | price |
+------------+---------+-----------+---------------+------+-------+
| 5 | 0004 | 测试 | NULL | NULL | 10.00 |
| 6 | 0003 | 尺子1 | NULL | NULL | 15.00 |
+------------+---------+-----------+---------------+------+-------+
2 rows in set (0.00 sec)
这里的“LIMIT 1,2”中,“1”表示起始位置,MySQL中,起始位置的起点是0,1表示从第2条记录开始;“2”表示2条数据。因此,“LIMIT 1,2”就表示从第2条数据开始,显示2条数据,也就是显示了第2、3条数据。
总结
今天,我们学习了添加、删除、修改和查询数据的方法,这些都是我们经常遇到的操作,你一定要好好学习。下面的这些SQL语句,是我总结的数据操作语法,你一定要重点掌握。
INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
INSERT INTO 表名 (字段名)
SELECT 字段名或值
FROM 表名
WHERE 条件
DELETE FROM 表名
WHERE 条件
UPDATE 表名
SET 字段名=值
WHERE 条件
SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点,行数
最后,我再补充一点。如果我们把查询的结果插入到表中时,导致主键约束或者唯一性约束被破坏了,就可以用“ON DUPLICATE”关键字进行处理。这个关键字的作用是,告诉MySQL,如果遇到重复的数据,该如何处理。
我来给你举个例子。
假设用户有2个各自独立的门店,分别有自己的系统。现在需要引入连锁经营的模式,把2个店用一套系统统一管理。那么首先遇到的问题就是,需要进行数据整合。下面我们就以商品信息表为例,来说明如何通过使用“ON DUPLICATE”关键字,把两个门店的商品信息数据整合到一起。
假设门店A的商品信息表是“demo.goodsmaster”,代码如下:
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 |
| 2 | 0002 | 笔 | 10支装 | 包 | 5.00 |
| 3 | 0003 | 橡皮 | NULL | 个 | 3.00 |
+------------+---------+-----------+---------------+------+------------+
3 rows in set (0.00 sec)
门店B的商品信息表是“demo.goodsmaster1”:
mysql> SELECT *
-> FROM demo.goodsmaster1;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 | 0001 | 教科书 | NULL | NULL | 89.00 |
| 4 | 0004 | 馒头 | | | 1.50 |
+------------+---------+-----------+---------------+------+------------+
2 rows in set (0.00 sec)
假设我们要把门店B的商品数据,插入到门店A的商品表中去,如果有重复的商品编号,就用门店B的条码,替换门店A的条码,用门店B的商品名称,替换门店A的商品名称;如果没有重复的编号,就直接把门店B的商品数据插入到门店A的商品表中。这个操作,就可以用下面的SQL语句实现:
INSERT INTO demo.goodsmaster
SELECT *
FROM demo.goodsmaster1 as a
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;
-- 运行结果如下
mysql> SELECT *
-> FROM demo.goodsmaster;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
| 1 | 0001 | 教科书 | 16开 | 本 | 89.00 |
| 2 | 0002 | 笔 | 10支装 | 包 | 5.00 |
| 3 | 0003 | 橡皮 | NULL | 个 | 3.00 |
| 4 | 0004 | 馒头 | | | 1.50 |
+------------+---------+-----------+---------------+------+------------+
4 rows in set (0.00 sec)
最后,我再跟你分享3份资料,分别是MySQL数据插入、MySQL数据更新和MySQL数据查询,如果你在工作中遇到了更加复杂的操作需求,就可以参考一下。
思考题
我想请你思考一个问题:商品表demo.goodsmaster中,字段“itemnumber”是主键,而且满足自增约束,如果我删除了一条记录,再次插入数据的时候,就会出现字段“itemnumber”的值不连续的情况。请你想一想,如何插入数据,才能防止这种情况的发生呢?
欢迎在留言区写下你的思考和答案,我们一起交流讨论。如果你觉得今天的内容对你有所帮助,也欢迎你分享你的朋友或同事,我们下节课见。
- 朱晓峰 👍(3) 💬(2)
你好,我是朱晓峰,下面我就来公布一下上节课思考题的答案: 上节课,我们学习了创建和修改表的具体方法,下面是思考题的答案: ALTER TABLE demo.goodsmaster CHANGE COLUMN salesprice salesprice DECIMAL(10,2) NOT NULL UNIQUE;
2021-04-21 - 时光过客 👍(22) 💬(2)
朱老师,怎么实现联表更新。比如A表有商品唯一id,价格。B表也有关联的商品唯一id,价格为空,实现从A表相同的Id的价格更新到B表。
2021-05-28 - 洛奇 👍(5) 💬(4)
请问朱老师,INSERT...ON DUPLICATE 语句存在死锁的可能吗?我记得在一次携程面试的时候,面试官考我这个问题,我当时却以为它是原子操作。
2021-03-27 - 右耳朵猫咪 👍(4) 💬(2)
老师好 我想请教您一个问题 mysql分页有必要加order by 某个字段吗 听说如果不加的话 查询第二页数据的时候可能会查询到第一页的数据 如果加order by,可能会影响性能,这该如何取舍呢?
2021-03-16 - 小白 👍(3) 💬(2)
ALTER TABLE demo.goodsmaster AUTO_INCREMENT=断点数值
2021-03-24 - SharpBB 👍(2) 💬(1)
笔记如下: 1.添加数据 insert into 表名(字段1,字段2...) values('老王','小二',...); 添加数据前要看下是否有约束 对于主键自增 可以不加该字段 对于非空约束 不赋值会报错 对于唯一约束 注意不要重复了 批量添加可以先查询某个时间段的数据 批量插入 如 insert into test.user(name,age,school_id) select name,age,school_id from test.user; 2.删除数据 delete from demo.test where id>0; 习惯加where语句 防止误操作 3.修改数据 update <库名.表名> set 字段=值 where 字段=值 如 update test.user set name='bb' where id=2; 注意不要修改主键值 一般也不会修改除非你主键设置的有问题 4.查询数据 from 如果有嵌套的查询 后面可能会跟一个派生表 必须起别名 order by 跟在字段后面 asc升序 desc降序 limit 限制sql查询显示的条数 大数据量下必须要做limit限制了1.添加数据 insert into 表名(字段1,字段2...) values('老王','小二',...); 添加数据前要看下是否有约束 对于主键自增 可以不加该字段 对于非空约束 不赋值会报错 对于唯一约束 注意不要重复了 批量添加可以先查询某个时间段的数据 批量插入 如 insert into test.user(name,age,school_id) select name,age,school_id from test.user; 2.删除数据 delete from demo.test where id>0; 习惯加where语句 防止误操作 3.修改数据 update <库名.表名> set 字段=值 where 字段=值 如 update test.user set name='bb' where id=2; 注意不要修改主键值 一般也不会修改除非你主键设置的有问题 4.查询数据 from 如果有嵌套的查询 后面可能会跟一个派生表 必须起别名 order by 跟在字段后面 asc升序 desc降序 limit 限制sql查询显示的条数 大数据量下必须要做limit限制了
2022-02-08 - 洛奇 👍(2) 💬(1)
不用非得自增主键连续吧???
2021-03-27 - Darren 👍(1) 💬(1)
老师,为什么合并时,要用类似barcode=a.barcode
2021-07-17 - 哈喽 👍(1) 💬(2)
老师我是零基础,比如having,group_by不知道怎么用
2021-06-30 - 日落黄昏下 👍(1) 💬(1)
为什么要避免这个问题呢?重要数据删除采用逻辑删除就不会有这个问题了。
2021-05-19 - FengX 👍(1) 💬(1)
SELECT @itemnumber=MIN(a.itemnumber)+1 FROM demo.goodsmaster as a WHERE NOT EXISTS( SELECT b.itemnumber FROM demo.goodsmaster as b WHERE b. itemnumber=a.itemnumber+1 ) 然后INSERT INTO demo.goodsmaster(itemnumber,...) VALUES(@itemnumber,....)
2021-04-02 - 李鸣 👍(1) 💬(1)
set @zizen = 0; UPDATE demo.goodsmaster set itemnumber = (select @zizen :=@zizen+1 as nid) where demo.goodsmaster.itemnumber IS NULL;
2021-03-22 - 星空下 👍(1) 💬(3)
使用max获取当前最大的主键id,id+1作为新的主键插入数据库。但是高并发场景下可能会出现主键冲突问题。其他的暂时没有想到,希望老师给出权威答案。
2021-03-16 - qwfys200 👍(0) 💬(1)
ON DUPLICATE KEY,这个不错,很有用,我们经常在项目中使用这个。
2022-07-15 - 不管 👍(0) 💬(1)
where怎么写,有哪些常用法?也没提到。我相信有很多从0开始学的,我就是,还得自己去搜索相关的知识。
2022-07-12