当前位置： 首页 > news >正文

【CS.DB】数据库-关系型数据库-MySQL-3.4.数据的插入_查询_更新和删除

news 来源：原创 2024/9/20 15:31:57

1000.04.CS.DB-Database-Relational-MySQL-3.4.数据的插入_查询_更新和删除-Created: 2024-08-08.Thursday10:11

在MySQL中，数据操作是日常数据库管理的核心部分。下面我们将详细讲解如何插入、查询、更新和删除数据，并通过经典例子帮助理解这些操作。

示例：

假设我们有一个 employees 表，结构如下：

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),position VARCHAR(50),salary DECIMAL(10, 2),hire_date DATE
);

1 数据插入

数据插入是将新记录添加到表中的过程。最常用的插入方法是 INSERT 语句。

插入一条记录：

INSERT INTO employees (name, position, salary, hire_date) VALUES ('Alice', 'Engineer', 70000.00, '2024-01-15');

插入多条记录：

INSERT INTO employees (name, position, salary, hire_date) VALUES
('Bob', 'Manager', 90000.00, '2023-05-01'),
('Charlie', 'Analyst', 60000.00, '2024-02-20');

使用隐式类型转换：

INSERT INTO employees (name, position, salary, hire_date) VALUES ('David', 'Clerk', 40000, 20240101);
-- 40000 将被转换为 40000.00, 20240101 将被转换为 '2024-01-01'mysql> select * from employees;
+----+-------+----------+----------+------------+
| id | name  | position | salary   | hire_date  |
+----+-------+----------+----------+------------+
|  1 | David | Clerk    | 40000.00 | 2024-01-01 |
+----+-------+----------+----------+------------+
1 row in set (0.01 sec)

2 数据查询

数据查询是从表中检索记录的过程。最常用的查询方法是 SELECT 语句。

示例：

查询所有记录：

SELECT * FROM employees;

查询特定字段：

SELECT name, position FROM employees;

使用条件查询：

SELECT * FROM employees WHERE position = 'Engineer';

使用排序：

SELECT * FROM employees ORDER BY salary DESC;

使用分页： ^20240808102635

-- 这将返回从第 21 条记录开始的 10 条记录。在这种查询中，`LIMIT` 指定返回的记录数量，`OFFSET` 指定跳过的记录数量。
SELECT * FROM employees LIMIT 10 OFFSET 20;

Ref. [[1000.04.CS.DB-Database-Relational-MySQL-4.6.深分页查询的优化]]
分页可以分为浅分页和深分页。上述提到的查询属于浅分页，适用于页数较少的情况。在记录数量不多的情况下，浅分页性能通常是可以接受的。但是，当页数变多时，特别是当 OFFSET 值变得很大时，浅分页的性能问题就会变得明显，因为数据库在处理深分页查询时，仍然需要扫描和跳过大量记录。

3 数据更新

数据更新是修改表中现有记录的过程。最常用的更新方法是 UPDATE 语句。

示例：

更新单条记录：

UPDATE employees SET salary = 75000.00 WHERE name = 'Alice';

更新多条记录：

UPDATE employees SET salary = salary * 1.10 WHERE position = 'Analyst';

带有条件的更新：

UPDATE employees SET position = 'Senior Engineer' WHERE name = 'Alice' AND salary < 80000;

4 数据删除

数据删除是从表中移除记录的过程。最常用的删除方法是 DELETE 语句。

示例：

删除单条记录：

DELETE FROM employees WHERE name = 'Charlie';

删除多条记录：

DELETE FROM employees WHERE position = 'Clerk';

DELETE FROM employees;
-- 或使用 TRUNCATE 清空表
TRUNCATE TABLE employees;

5 隐式类型转换 🔥

隐式类型转换是指数据库在必要时自动将一种数据类型转换为另一种数据类型。这在插入和查询操作中尤为常见。

隐式类型转换是MySQL的一项强大特性，但也需要谨慎使用，以避免数据不一致或意外错误。

示例：

字符串和整数之间的隐式转换：

INSERT INTO employees (name, position, salary, hire_date) VALUES ('Eve', 'Accountant', 50000, '20240101');
-- '20240101' 将被转换为 '2024-01-01'

查询中的隐式转换：

SELECT * FROM employees WHERE hire_date = 20240101;
-- 20240101 将被转换为 '2024-01-01'

**在某些情况下可能会引入潜在的错误或导致性能问题: **

5.1 字符串比较

你提到的错误示例：

mysql> select * from employees where name = David;
ERROR 1054 (42S22): Unknown column 'David' in 'where clause'

这表明 MySQL 试图将 David 解释为一个列名，而不是一个字符串值。在 SQL 中，字符串值需要用单引号 ' 括起来：

mysql> select * from employees where name = 'David';

正确的查询使用了单引号，并且成功返回了结果。

5.2 隐式类型转换的潜在问题

隐式类型转换虽然方便，但可能会引入一些 bug，以下是一些例子和说明：

5.2.1 数值和字符串比较 🔥

考虑下面的表和数据：

CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(50),price VARCHAR(10)
);INSERT INTO products (id, name, price) VALUES
(1, 'Product A', '10'),
(2, 'Product B', '20'),
(3, 'Product C', '30');

假设你想要比较价格，并执行以下查询：

mysql> SELECT * FROM products WHERE price = 20;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  2 | Product B | 20    |
+----+-----------+-------+
1 row in set, 1 warning (0.01 sec)mysql>  SHOW WARNINGS\G
*************************** 1. row ***************************Level: WarningCode: 1292
Message: Truncated incorrect DOUBLE value: 'twenty'
1 row in set (0.00 sec)

因为 price 列是字符串类型，MySQL 会将 20 转换为字符串 '20'，这个查询会返回正确的结果。但是，如果 price 列包含非数值字符串，查询会产生不可预期的结果：

INSERT INTO products (id, name, price) VALUES (4, 'Product D', 'twenty');

再执行查询：

mysql> SELECT * FROM products WHERE price = 20;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  2 | Product B | 20    |
+----+-----------+-------+
1 row in set, 1 warning (0.00 sec)-- MySQL 尝试将 `price` 列的值与数值 `20` 进行比较。这时，MySQL 进行隐式类型转换，将 `price` 列的值转换为数值。对于包含非数值字符串（如 'twenty'）的行，MySQL 会发出警告并将这些值视为 `0`，从而导致不准确的结果。
mysql> SELECT * FROM products WHERE price < 20;
+----+-----------+--------+
| id | name      | price  |
+----+-----------+--------+
|  1 | Product A | 10     |
|  4 | Product D | twenty |
+----+-----------+--------+
2 rows in set, 1 warning (0.01 sec)

这时，MySQL 会进行隐式转换，可能会导致无法预期的结果或错误。这种情况下，显式转换能更明确地表达意图：

mysql> SELECT * FROM products WHERE CAST(price AS DECIMAL(10, 2)) = 20.00;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  2 | Product B | 20    |
+----+-----------+-------+
1 row in set, 2 warnings (0.00 sec)mysql>  SHOW WARNINGS\G
*************************** 1. row ***************************Level: WarningCode: 1366
Message: Incorrect DECIMAL value: '0' for column '' at row -1
*************************** 2. row ***************************Level: WarningCode: 1292
Message: Truncated incorrect DECIMAL value: 'twenty'
2 rows in set (0.01 sec)

总结:

  • 在设计数据库时，尽量选择合适的数据类型。例如，如果 price 应该始终是数值类型，应该将其定义为 DECIMAL 或 FLOAT 类型：CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(50),price DECIMAL(10, 2));
  • 在插入数据时，进行数据验证以确保数据类型和格式正确。例如，在应用程序层进行数据验证，确保插入到 price 列的数据是有效的数值。
  • 在查询时，显式地转换数据类型并处理异常。例如，使用 TRY_CAST 函数（在支持的数据库中）来处理可能的转换错误：SELECT * FROM products WHERE TRY_CAST(price AS DECIMAL(10, 2)) = 20.00;
5.2.2 字符串和日期比较

考虑下面的表和数据：

CREATE TABLE events (id INT PRIMARY KEY,name VARCHAR(50),event_date VARCHAR(10)
);INSERT INTO events (id, name, event_date) VALUES
(1, 'Event A', '2024-01-01'),
(2, 'Event B', '2024-02-01');

假设你想要比较日期，并执行以下查询：

SELECT * FROM events WHERE event_date = '2024-01-01';

这个查询会正确返回结果。但是如果 event_date 列包含非日期字符串：

INSERT INTO events (id, name, event_date) VALUES (3, 'Event C', 'January 1, 2024');-- 再执行查询：
SELECT * FROM events WHERE event_date = '2024-01-01';

MySQL 会进行隐式转换，可能会导致意外结果或错误。显式转换可以避免这种问题：

SELECT * FROM events WHERE STR_TO_DATE(event_date, '%Y-%m-%d') = '2024-01-01';
5.2.3 字符串与时间戳比较

考虑一个包含时间戳的表：

CREATE TABLE logs (id INT PRIMARY KEY,event_time TIMESTAMP
);INSERT INTO logs (id, event_time) VALUES
(1, '2024-01-01 10:00:00'),
(2, '2024-01-01 12:00:00');

假设你要查找特定时间之后的所有记录：

mysql> SELECT * FROM logs WHERE event_time > '2024-01-01 11:00:00';
+----+---------------------+
| id | event_time          |
+----+---------------------+
|  2 | 2024-01-01 12:00:00 |
+----+---------------------+
1 row in set (0.02 sec)

这条查询可以正常工作，因为字符串 '2024-01-01 11:00:00' 可以隐式转换为时间戳。但是，如果输入格式不一致，可能会引发问题：

mysql> SELECT * FROM logs WHERE event_time > '01-01-2024 11:00:00';
+----+---------------------+
| id | event_time          |
+----+---------------------+
|  1 | 2024-01-01 10:00:00 |
|  2 | 2024-01-01 12:00:00 |
+----+---------------------+
2 rows in set, 1 warning (0.01 sec)-- 虽然查询没有抛出错误，但返回的结果和警告表明，MySQL 在处理日期时间比较时进行了隐式转换。我们可以进一步探讨这个隐式转换的行为及其可能引发的问题。

隐式转换行为: MySQL 在处理不符合标准格式的日期时间字符串时，会尝试将其转换为合适的格式。如果转换失败，MySQL 可能会使用默认值 '0000-00-00 00:00:00' 进行比较。这就是为什么查询没有抛出错误但仍返回了结果，并且产生了一个警告。

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************Level: WarningCode: 1292
Message: Incorrect datetime value: '01-01-2024 11:00:00' for column 'event_time' at row 1
1 row in set (0.00 sec)

为了避免隐式转换问题并确保查询结果的准确性，建议使用标准的日期时间格式或显式转换函数。

使用标准日期时间格式: 确保使用符合 MySQL 标准的日期时间格式 'YYYY-MM-DD HH:MM:SS'：

SELECT * FROM logs WHERE event_time > '2024-01-01 11:00:00';

使用 STR_TO_DATE 函数: 如果输入的日期时间格式不标准，可以使用 STR_TO_DATE 函数显式地转换：

mysql> SELECT * FROM logs WHERE event_time > STR_TO_DATE('01-01-2024 11:00:00', '%d-%m-%Y %H:%i:%s');
+----+---------------------+
| id | event_time          |
+----+---------------------+
|  2 | 2024-01-01 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)
5.2.4 如何验证非数值字符串（如 ‘twenty’）在 MySQL 中被转换为 0 ?
-- 首先，创建一个名为 `products` 的表，并插入一些数据，包括非数值字符串。
CREATE TABLE products (id INT PRIMARY KEY,name VARCHAR(50),price VARCHAR(10)
);INSERT INTO products (id, name, price) VALUES
(1, 'Product A', '10'),
(2, 'Product B', '20'),
(3, 'Product C', '30'),
(4, 'Product D', 'twenty');-- 执行查询，将 `price` 列中的值转换为 `DECIMAL` 类型，并观察结果。
mysql> SELECT id, name, price, CAST(price AS DECIMAL(10, 2)) AS price_as_decimal FROM products;
+----+-----------+--------+------------------+
| id | name      | price  | price_as_decimal |
+----+-----------+--------+------------------+
|  1 | Product A | 10     |            10.00 |
|  2 | Product B | 20     |            20.00 |
|  3 | Product C | 30     |            30.00 |
|  4 | Product D | twenty |             0.00 |
+----+-----------+--------+------------------+
4 rows in set, 2 warnings (0.01 sec)-- 为了更加明确地验证转换过程中是否产生了警告信息，可以使用 `SHOW WARNINGS` 命令查看详细的警告信息。
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect DECIMAL value: '0' for column '' at row -1 |
| Warning | 1292 | Truncated incorrect DECIMAL value: 'twenty'          |
+---------+------+------------------------------------------------------+
2 rows in set (0.01 sec)

在这个查询结果中，我们可以看到 price 列的原始值和转换后的 price_as_decimal 列的值。对于 Product D，price 列中的非数值字符串 'twenty' 被转换为了 0.00。

  • Warning 1366: 这个警告信息说明在将某个值转换为 DECIMAL 类型时，出现了不正确的 DECIMAL 值 '0'。这通常是因为在转换过程中，MySQL 遇到了无法转换的字符串，并将其截断为 0。
  • Warning 1292: 这个警告信息明确指出 'twenty' 这个值被截断为不正确的 DECIMAL 值。这是由于 'twenty' 不是一个有效的数值字符串。

5.3 避免隐式转换的优势

  1. 明确性：显式转换可以使查询更明确和易读，减少误解和错误。
  2. 性能：显式转换可以减少不必要的计算和转换，提高查询性能。
  3. 一致性：通过显式转换，可以确保数据类型的一致性，避免数据类型混淆带来的问题。

References

相关文章：

  • 北京网站建设多少钱？
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • 安防监控/视频汇聚平台EasyCVR如何配置，实现默认获取设备的子码流？
  • 探索NSAppleScript的魔法：Objective-C与AppleScript的无缝对接
  • centos7系统更新阿里镜像源地址
  • Java流程控制01：用户交互Scanner
  • 如何将列数据转换为行数据——SQL和EF Core（C#）两种实现方式
  • 【Linux】
  • Java开发代码规范文档
  • windows 安装TVM
  • NCL的安装和运行；气象数据可视化；散点图、直方图、等值线图、箭头图、任意折线和任意图形、非规则网格、图形叠加、组图的绘制
  • 深入探讨安全验证：OAuth2.0、Cookie与Session、JWT令牌、SSO与开放授权平台设计
  • go 调用C语言函数或者库
  • 力扣题解（增减字符串匹配）
  • 【笔记】Swin-Transformer 的计算量与Transformer的计算量的对比：前者通过使用新颖的窗口技巧，将后者的高阶项变为低阶，大大降低了计算量
  • Vulkan入门系列9 - Uniform Buffer 资源描述符
  • Eval绕过限制参数限制
  • CentOS7 安装JDK
  • Java 内存分配及垃圾回收机制初探
  • JavaScript 基础知识 - 入门篇(一)
  • mongo索引构建
  • spring cloud gateway 源码解析（4）跨域问题处理
  • SpringCloud集成分布式事务LCN (一)
  • vagrant 添加本地 box 安装 laravel homestead
  • VirtualBox 安装过程中出现 Running VMs found 错误的解决过程
  • Vue ES6 Jade Scss Webpack Gulp
  • Vue UI框架库开发介绍
  • vue学习系列（二）vue-cli
  • 互联网大裁员：Java程序员失工作，焉知不能进ali？
  • 浅谈Kotlin实战篇之自定义View图片圆角简单应用(一)
  • 算法之不定期更新（一）（2018-04-12）
  • 微信小程序：实现悬浮返回和分享按钮
  • 硬币翻转问题，区间操作
  • HanLP分词命名实体提取详解
  • Spark2.4.0源码分析之WorldCount 默认shuffling并行度为200(九) ...
  • 阿里云API、SDK和CLI应用实践方案
  • # Redis 入门到精通（八）-- 服务器配置-redis.conf配置与高级数据类型
  • #pragma once
  • (70min)字节暑假实习二面（已挂）
  • （附源码）ssm户外用品商城 毕业设计 112346
  • （实测可用）（3）Git的使用——RT Thread Stdio添加的软件包，github与gitee冲突造成无法上传文件到gitee
  • (未解决)jmeter报错之“请在微信客户端打开链接”
  • (转贴)用VML开发工作流设计器 UCML.NET工作流管理系统
  • ***检测工具之RKHunter AIDE
  • .net 8 发布了，试下微软最近强推的MAUI
  • .net core 微服务_.NET Core 3.0中用 Code-First 方式创建 gRPC 服务与客户端
  • .NET/C# 编译期间能确定的相同字符串，在运行期间是相同的实例
  • .NET/C# 使用 SpanT 为字符串处理提升性能
  • .net实现头像缩放截取功能 -----转载自accp教程网
  • .NET下ASPX编程的几个小问题
  • .net中我喜欢的两种验证码
  • [\u4e00-\u9fa5] //匹配中文字符
  • [android] 手机卫士黑名单功能（ListView优化）
  • [Angular 基础] - 自定义指令，深入学习 directive
  • [bzoj2957]楼房重建
  • [C#]C# winform实现imagecaption图像生成描述图文描述生成
  • [C/C++]数据结构----顺序表的实现(增删查改)