MySQL必知必会04：操作表中的数据

最新推荐文章于 2023-08-06 23:51:32 发布
最新推荐文章于 2023-08-06 23:51:32 发布
阅读量650 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_31362767/article/details/123162846
版权

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录 极客时间 官网购买专栏。

文章目录

添加数据

添加数据的语法结构：

insert into 表名 [(字段名 [,字段名] ...)] 
values (值的列表);

添加数据分为两种情况：插入数据记录和插入查询结果。

测试数据

mysql> describe goodsmaster;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| barcode       | text         | YES  |     | NULL    |                |
| goodname      | text         | YES  |     | NULL    |                |
| price         | decimal(5,2) | YES  |     | NULL    |                |
| itemnumber    | int          | NO   | PRI | NULL    | auto_increment |
| specification | text         | YES  |     | NULL    |                |
| unit          | text         | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

插入数据记录

这里只给3个字段赋值，itemnumber、specification、unit不赋值：

insert into demo.goodsmaster (barcode, goodsname, price) values ('0004', '测试', 10);

mysql> insert into demo.goodsmaster (itemnumber, barcode, goodname, specification, unit, price) values (4, '004', 'ruler', 'tangle', 'a', 0.5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo.goodsmaster;
+---------+-----------------+-------+------------+---------------+------+
| barcode | goodname        | price | itemnumber | specification | unit |
+---------+-----------------+-------+------------+---------------+------+
| 004     | ruler           |  0.50 |          4 | tangle        | a    |
| 0004    | 测试            | 10.00 |        102 | NULL          | NULL |
+---------+-----------------+-------+------------+---------------+------+
7 rows in set (0.00 sec)

在插入一条数据记录的时候，必须要考虑字段约束的 3 种情况：

  • 如果字段允许为空，而我们没有给它赋值，那么 MySQL 会自动给它们赋予空值。

    “specification”, "unit"字段都允许为空，因此如果数据插入语句没有指定这几个字段的值，MySQL 会自动插入空值

  • 如果字段是主键，就不能为空，这个时候，MySQL 会按照添加的约束进行处理。比如字段"itemnumber"是主键，不能为空，而我们定义了自增约束，所以 MySQL 自动在之前的最大值基础上加了1。因此，itemnumber 也有了自己该有的值

  • 如果有一个字段定义不能为空，又不是主键，当插入一条数据记录的时候，就必须给这个记录赋值

如果操作违反了字段的约束限制，会出现报错。

比如说，尝试把表 demo.goodsmaster 的字段 “speicification” 改为不能为空：

mysql> alter table goodsmaster modify specification text not null;
ERROR 1138 (22004): Invalid use of NULL value

表里已经有一条没有给字段"specification"赋值的数据记录，这跟给字段“specification”添加非空约束的操作冲突了,

要把字段“speicification”的值为空的数据记录删除，然后再修改字段约束：

mysql> delete from goodsmaster where specification is NULL;
Query OK, 1 row affected (0.01 sec)

删除数据记录之后，再运行上面的语句，给字段"specification"添加非空约束，就成功了：

mysql> alter table goodsmaster modify specification text not null;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe goodsmaster;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| barcode       | text         | YES  |     | NULL    |                |
| goodname      | text         | YES  |     | NULL    |                |
| price         | decimal(5,2) | YES  |     | NULL    |                |
| itemnumber    | int          | NO   | PRI | NULL    | auto_increment |
| specification | text         | NO   |     | NULL    |                |
| unit          | text         | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

验证一下非空约束，尝试部分插入一条数据记录，不给字段 specification 赋值：

mysql> insert into demo.goodsmaster (barcode, goodname, price) values ('0004', '测试', 10);
ERROR 1364 (HY000): Field 'specification' doesn't have a default value

报错提示字段"specification"没有默认值，也就是说，这个字段不能为空，如果插入数据时不给它赋值，就必须给它一个默认值。

部分插入一条数据记录是可以的，但前提是，没有赋值的字段，一定要让 MySQL 知道如何处理，比如可以为空、有默认值，或者是自增约束字段，等等，否则，MySQL 会提示错误的。

插入查询结果

MySQL 支持把查询的结果插入到数据表中，可以指定字段，甚至是数值，插入到数据表中。语法结构如下：

insert into 表名 （字段名）
select 字段名或值
from 表名
where 条件

把一个查询结果插入数据表中的方式，可以提高添加数据的效率。

比如，历史流水表设计与流水表非常类似。不同的是，历史流水表增加了一些字段来标识历史流水的状态，比如日结时间字段，是用来记录日结操作是什么时候进行的。

insert into 历史流水表 （日结时间字段，其他字段）
select 获取当前时间函数，其他字段
from 流水表

删除数据

数据删除的语法很简单：

delete from 表名 
where 条件

删除全部数据：

mysql> select * from demo.test1;
+------+-----------+-------+------------+
| bar  | goodsname | price | itemnumber |
+------+-----------+-------+------------+
| 001  | book      | 29.51 |          1 |
| 002  | pen       |  5.17 |          2 |
| 003  | rubber    |  2.42 |          3 |
+------+-----------+-------+------------+
3 rows in set (0.00 sec)

mysql> delete from demo.test1;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from demo.test1;
Empty set (0.00 sec)

如果在 Workbench 中运行上面的 SQL 语句，会提示错误。因为 Workbench 自动处于安全模式，它要求对数据的删除或修改操作中必须包含 WHERE 条件。而且，这个 WHERE 条件中，必须用到主键约束或者唯一性约束的字段。MySQL 的这种安全性设置，主要就是为了防止删除或者修改数据时出现误操作，导致删除或修改了不相关的数据。

mysql> select * from demo.goodsmaster;
+---------+----------+-------+------------+---------------+------+
| barcode | goodname | price | itemnumber | specification | unit |
+---------+----------+-------+------------+---------------+------+
| 001     | book     |  1.50 |          1 | book1         | a    |
| 004     | ruler    |  0.50 |          4 | tangle        | a    |
+---------+----------+-------+------------+---------------+------+
2 rows in set (0.00 sec)

mysql> delete from demo.goodsmaster where itemnumber>=1;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from demo.goodsmaster;
Empty set (0.00 sec)

修改数据

数据修改语法：

update 表名 
set 字段名=值 
where 条件

不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

mysql> update demo.goodsmaster set price=2.5 where itemnumber=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from demo.goodsmaster;
+---------+----------+-------+------------+---------------+------+
| barcode | goodname | price | itemnumber | specification | unit |
+---------+----------+-------+------------+---------------+------+
| 001     | book     |  2.50 |          1 | book1         | a    |
+---------+----------+-------+------------+---------------+------+
1 row in set (0.00 sec)

查询数据

查询语句的语法结构：

select *|字段列表
from 数据源
where 条件
group by 字段
having 条件
order by 字段
limit 起始点，行数
  • SELECT：是查询关键字，表示要做一个查询。*是一个通配符，表示要查询表中所有的字段。也可以把要查询的字段罗列出来，这样，查询的结果可以只显示想要查询的字段内容
  • WHERE：表示查询条件。可以把要查询的数据所要满足的条件，放在 WHERE 关键字之后
  • GROUP BY：表示查询结果要分组，经常与 MySQL 的聚合函数一起使用
  • HAVING：用于筛选查询结果，跟 WHERE 类似

FROM

FROM 关键字表示查询的数据源。可以把要查询的数据表名，直接写在 FROM 关键字之后。在关联表中 FROM 关键字后面，还可以跟着更复杂的数据表联接。数据源也不一定是表，也可以是一个查询的结果。比如下面的查询：

mysql> select a.goodname, a.price from
    -> (select * from demo.goodsmaster) as a;
+----------+-------+
| goodname | price |
+----------+-------+
| book     |  2.50 |
+----------+-------+
1 row in set (0.00 sec)

select * from demo.goodsmaster 部分叫做派生表（derived table），或者子查询（subquery），意思是我们把一个查询结果数据集当做一个虚拟的数据表来看待。MySQL 规定，必须要用 AS 关键字给这个派生表起一个别名。在这张图中，给这个派生表起了个名字，叫做 a 。

ORDER BY

ORDER BY 的作用，是告诉 MySQL，查询结果如何排序。ASC 表示升序，DESC 表示降序。

mysql> select * from demo.goodsmaster;
+---------+----------+-------+------------+---------------+------+
| barcode | goodname | price | itemnumber | specification | unit |
+---------+----------+-------+------------+---------------+------+
| 001     | book     |  2.50 |          1 | book1         | a    |
| 002     | ruler    |  1.00 |        104 | NULL          | NULL |
| 003     | pen      |  2.00 |        105 | NULL          | NULL |
| 003     | pencil   |  1.60 |        106 | NULL          | NULL |
| 001     | notebook |  3.00 |        107 | NULL          | NULL |
+---------+----------+-------+------------+---------------+------+
5 rows in set (0.00 sec)

mysql> select * from demo.goodsmaster order by barcode asc, price desc;
+---------+----------+-------+------------+---------------+------+
| barcode | goodname | price | itemnumber | specification | unit |
+---------+----------+-------+------------+---------------+------+
| 001     | notebook |  3.00 |        107 | NULL          | NULL |
| 001     | book     |  2.50 |          1 | book1         | a    |
| 002     | ruler    |  1.00 |        104 | NULL          | NULL |
| 003     | pen      |  2.00 |        105 | NULL          | NULL |
| 003     | pencil   |  1.60 |        106 | NULL          | NULL |
+---------+----------+-------+------------+---------------+------+
5 rows in set (0.00 sec)

LIMIT

LIMIT 的作用是告诉 MySQL 只显示部分查询的结果。MySQL中，起始位置的起点是0。

mysql> select * from demo.goodsmaster limit 1,2;
+---------+----------+-------+------------+---------------+------+
| barcode | goodname | price | itemnumber | specification | unit |
+---------+----------+-------+------------+---------------+------+
| 002     | ruler    |  1.00 |        104 | NULL          | NULL |
| 003     | pen      |  2.00 |        105 | NULL          | NULL |
+---------+----------+-------+------------+---------------+------+
2 rows in set (0.00 sec)

这里的“LIMIT 1,2”中，“1”表示起始位置，1 表示从第 2 条记录开始；“2”表示 2 条数据。因此，“LIMIT 1,2”就表示从第 2 条数据开始，显示 2 条数据，也就是显示了第 2、3 条数据。

小结

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 个店用一套系统统一管理。那么首先遇到的问题就是，需要进行数据整合。

门店 A 的商品信息表是“demo.goods1”，代码如下：

mysql> select * from demo.goods1;
+------------+---------+-----------+---------------+---------+------------+
| itemnumber | barcode | goodsname | specification | unit    | salesprice |
+------------+---------+-----------+---------------+---------+------------+
|          1 | 001     | book      | 16k           | a       |      88.00 |
|          2 | 002     | pen       | 10in          | package |      10.00 |
|          3 | 003     | rubber    | NULL          | a       |       2.00 |
+------------+---------+-----------+---------------+---------+------------+
3 rows in set (0.00 sec)

门店 B 的商品信息表是“demo.goods2”：

ysql> select * from demo.goods2;
+------------+---------+-----------+---------------+------+------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice |
+------------+---------+-----------+---------------+------+------------+
|          1 | 001     | tech book | NULL          | NULL |      66.00 |
|          4 | 004     | bread     | NULL          | NULL |       1.50 |
+------------+---------+-----------+---------------+------+------------+
2 rows in set (0.00 sec)

假设要把门店 B 的商品数据，插入到门店 A 的商品表中去，如果有重复的商品编号，就用门店 B 的条码，替换门店 A 的条码，用门店 B 的商品名称，替换门店 A 的商品名称；如果没有重复的编号，就直接把门店 B 的商品数据插入到门店 A 的商品表中。

mysql> insert into demo.goods1
    -> select * from demo.goods2 as a
    -> on duplicate key update barcode=a.barcode, goodsname=a.goodsname;
Query OK, 3 rows affected (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from demo.goods1;
+------------+---------+-----------+---------------+---------+------------+
| itemnumber | barcode | goodsname | specification | unit    | salesprice |
+------------+---------+-----------+---------------+---------+------------+
|          1 | 001     | tech book | 16k           | a       |      88.00 |
|          2 | 002     | pen       | 10in          | package |      10.00 |
|          3 | 003     | rubber    | NULL          | a       |       2.00 |
|          4 | 004     | bread     | NULL          | NULL    |       1.50 |
+------------+---------+-----------+---------------+---------+------------+
4 rows in set (0.00 sec)

MySQL 数据插入:https://dev.mysql.com/doc/refman/8.0/en/insert.html

MySQL 数据更新：https://dev.mysql.com/doc/refman/8.0/en/update.html

MySQL 数据查询：https://dev.mysql.com/doc/refman/8.0/en/select.html

<花开花落>
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
对表的操作命令
m0_51779342的博客
03-14 2114
一、创建表格 Create table 表名（ 字段名（就是列名） 数据类型（长度） 约束条件（可省略） comment “注释”， 字段名（就是列名） 数据类型（长度） 约束条件（可省略） ）； 注意： 创建表时在小括号后面要加上分号 字段与字段中间用逗号隔开，最好一个字段不要加逗号（参考java参数格式） 如果定义为字符串类型的字段，后面要规定字符串长度 如果不指定长度，则使用默认值，如int类型默认长度11 二、数据类型 一、数值类型 类型 大小 描述 tinyint
MySQL操作表中数据
weixin_55509293的博客
06-07 134
操作表中数据 数据表是数据的载体，在使用的过程中我们需要频繁的对数据表中的数据进行增减、删除和修改操作。MYSQL中提供了对数据进行添加、删除和修改的完整功能，提高了数据库的管理效率 SQL中的表达式和逻辑运算符和逻辑运算符 SQL命令中的表达式和开发语言中的表达式定义是一样的，都是指用 运算符将同类型的数据（如：变量、常量、函数等） 按一定的规则连接起来的具有特定意义的语句，表达式可以分为条件表达式和逻辑表达式 表达式 在数据库中的表达式一般是指由常量、变量、函数和运算符组成。需要特别注意的是单个常量、变
表格数据的操作
fmechina的博客
06-12 5608
原文地址https://knowledge.safe.com/articles/19645/tutorial-manipulation-of-tabular-data.html    这篇文章介绍了一些用FME操作表格数据的不同工具。它包括：转置表格(transposing)，汇总统计和总量(summary statistics and totals)，数据透视表(pivot tables)和交叉...
(3)操作表中的数据
不积跬步，无以千里
08-08 286
1. Oracle 操作表中的数据本节我们会学习如何添加数据，修改数据以及删除数据。 添加数据，向表中所有字段添加值insert into userinfo(column_name,... )values(value1,value2)； insert into userinfo values(1,'xxx','123','xxx@126.com',sysdate)；sysdate–》获取当前系统时
表数据常见操作
weixin_30367873的博客
09-01 161
插入数据 1、普通插入 insert into user values('01','fei','table-ball','23'); 2、指定插入 insert into user(id,name,hobby) values('02','fei','football'); 3、多条插入 insert into user(id,name,hobby) values('03','fei','...
Mysql必知必会表数据.ZIP
10-21
理解并掌握这些MySQL必知必会的表数据知识，将使你在数据库管理、开发和维护中更加得心应手。通过不断实践和学习，你可以更深入地探索MySQL的高级特性，如复制、集群和云服务等，进一步提升你的数据库技能。
mysql必知必会_MYSQL_MySQL必知必会_
09-29
以上只是《MySQL必知必会》中的一部分关键知识点，实际教程还会涵盖更多细节，如性能调优、复制与集群等高级主题。通过学习这些基础知识，新手可以快速上手MySQL，为今后的数据库管理工作打下坚实基础。
【MySQL】对表中数据的操作
最新发布
m0_70811813的博客
08-06 4411
本期讲述了MySQL下对表中数据的增删查改操作：insert/update/select/as/where/order by/limit/group by/having/delete/truncate和各种聚合函数，各种语句应有尽有
数据表的操作
YJiaLiang的博客
05-16 591
数据表一. 数据表的操作1. 创建表结构2. 删除表3. 更改表4. 查二. 表中字段的操作1. 增2. 删3. 改4. 查三. 表中数据的操作1. 增2. 删3. 改4. 查 一. 数据表的操作 1. 创建表结构 --新建表 create table 表名( age int, name varchar(11) ); --带字符集和校验规则的表 create table 表明( age int, name varchar(11) )character set utf8 collate ut
MySQL入门第二天——数据库、表的基本操作
PrototypeOne的博客
09-09 353
MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库后查看该数据库基本信息MySQL命令删除数据库MySQL命令切换数据库 MySQL命令use db1;查看当前使用的数据库 MySQL命令。
数据表的基本操作
qq_41081716的博客
12-20 147
Clickhouse DML
四、数据表的基本操作
技术小白
04-14 2417
4.1 创建数据表 在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据 表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数 据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整 性和域完整性等）约束的过程。 数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库 名>”指定操作是在哪个数据库中进行，如果没有选择数据库，就会抛出“No database selected”的错误。 创建数据表的语句为CREATE TABLE，语法规则如下：
三、数据表的基本操作
苏迪的博客
04-23 2370
在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。数据表被定义为列的集合，数据在表中是按照行和列的格式来存储的，每一行代表一条唯一的记录，每一列代表记录中的一个域。 一、创建数据表 在创建完数据库之后，接下来的工作就是创建数据表。所谓创建数据表，指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。 1、创建表的语法形式 在创建数据表之前，应该使用语句“USE <数据库名&gt
MySQL中sql复制一条数据并做修改
热门推荐
我真的睡不够
06-29 1万+
一、复制同表数据，不做修改 INSERT INTO 表名 （字段）SELECT 字段 FROM 表名 WHERE 条件； 二、复制同表数据同时作出修改 如果要修改其中某一个字段，在查询语句中：x（要改变的值） as 字段名。 举例: INSERT INTO billruleregister (billnum,action,ruleId,iorder,overrule,tenant_id,isSystem,isAsyn) SELECT'st_purblueinvoicegr...
将查询结果插入到另一个表的三种情况！insert into 目地表 select *from 表 where 条件
水亦流人必上的博客
05-24 5235
一：如果要插入目标表不存在： select * into 目标表 from 表 where ... 二：如果要插入目标表已经存在： insert into 目的表 select * from 表 where 条件 三：如果是跨数据库操作的话： 怎么把A数据库的atable表所查询的东西，全部插入到B 数据库的btable表中 select * into B.btable from A.atable where ... 同样，如果是跨服务器的，也是可以的。 ......
mysql入门 ，及详细步骤
qq_41992378的博客
09-18 304
数据更新 插入单条数据：insert   into tb_dept values('D4','公关部','Liming','010-82953306'); 插入多条数据：insert   into tb_dept values('D4','公关部','Liming','010-82953306'),('D5','财务部','Lina','010-82953306'),('D5','财务部','Li...
MySQL数据库表记录的操作
weixin_44775434的博客
11-23 1209
MySQL数据库表记录的操作
MySQL 必知必会 三 （如何操作表中的数据）
车联网开发的博客
06-10 366
1.插入查询的结果 SQL: inset into 表名 （字段名）select 字段名或值 from 表名 where 条件；
mybatis 批量复制同表数据并做修改字段，后插入。
u010797364的博客
08-19 1544
sql复制一条或多条记录并修改。
MySQL 必知必会 21-30.pdf
10-04
MySQL 必知必会 21-30章总结摘要

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值