首页 > 数据库 >Mysql开发过程复杂操作

Mysql开发过程复杂操作

时间：2025-01-07 13:58:45浏览次数：3  
标签：INSERT name 复杂 INTO 记录 插入 开发 Mysql table

Mysql开发过程复杂操作

文章目录


Mysql开发过程复杂操作

1-复杂操作总结

1-【添加时】更新相同KEY

1-INSERT INTO XXX ON DUPLICATE KEY UPDATE 【更灵活更通用】
2-REPLACE INTO 【只能更新主键】
3-SaveOrUpdate 【Mysql不直接支持，需要结合OAM框架】

  • 1-INSERT INTO XXX ON DUPLICATE KEY UPDATE
INSERT INTO my_table (id, name, value)
VALUES (2, 'example', 100)
ON DUPLICATE KEY UPDATE 
    id = VALUES(id),
    name = VALUES(name),
    value = VALUES(value);
  • 2-REPLACE INTO

1）使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作
2）执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录
3）【注意】如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入
4）可以使用SET进行赋值：REPLACE INTO users SET id = 123, name = ‘赵本山’, age = 50;
5）可能会删除【多条唯一索引】数据列表

REPLACE INTO my_table (name, value)
VALUES ('example', 100);

# 如果有多个唯一索引，这些
假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
假设table1中已经有了3条记录
a b c
1 1 1
2 2 2
3 3 3

下面我们使用REPLACE语句向table1中插入一条记录。
REPLACE INTO table1(a, b, c) VALUES
(1,2,3);

返回的结果如下
Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下
a b c
1 2 3

我们可以看到，REPLACE将原先的3条记录都删除了，然后将（1, 2, 3）插入。
  • 3-SaveOrUpdate

参考MyBatis实现SaveOrUpdate：http://blog.csdn.net/isea533/article/details/45578415

<insert id="saveOrUpdate" >
  <selectKey keyProperty="count" resultType="int" order="BEFORE">
    select count(*) from country where id = #{id}
  </selectKey>
  <if test="count > 0">
    update country 
    set countryname = #{countryname},countrycode = #{countrycode} 
    where id = #{id}
  </if>
  <if test="count==0">
    insert into country values(#{id},#{countryname},#{countrycode})
  </if>
</insert>

2-【查询后】添加到库中

INSERT INTO XXX SELECT * FROM XXX 【查询后添加到数据库中】

INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

3-【关联表】更新

UPDATE XXX JOIN XXX ON XXX SET XX=XX 【关联表更新】

UPDATE target_table
JOIN source_table ON target_table.column = source_table.column
SET target_table.column = source_table.column
WHERE condition;

4-【批量】数据插入

常见3种：①直接insert/②insert select/③LOAD DATA INFILE

  • ①直接insert
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
...
(value1, value2, value3, ...);
  • ②insert select
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
  • ③LOAD DATA INFILE
LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, column3, ...);

5-【批量】数据更新

常见3种：①update case when/②update join/③update子查询

  • ①update case when
UPDATE table_name
SET column1 = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE column1
END,
column2 = CASE
    WHEN condition1 THEN value3
    WHEN condition2 THEN value4
    ...
    ELSE column2
END
WHERE condition;
  • ②update join
UPDATE students
JOIN bonuses ON students.age = bonuses.age
SET students.grade = students.grade + bonuses.bonus;
  • ③update子查询
UPDATE students
SET grade = grade + (SELECT bonus FROM bonuses WHERE bonuses.age = students.age)
WHERE age IN (20, 21, 22);

2-复杂操作案例

1-【添加时】更新相同KEY

在 MySQL 中，你可以使用 INSERT ... ON DUPLICATE KEY UPDATE 语句来实现插入数据时，如果指定的键（可能是组合键）已经存在，则进行数据更新。

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

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    value INT,
    UNIQUE KEY unique_name_value (name, value)
);

在这个表中，id 是主键，name 和 value 组成一个唯一键（即组合键）。

如果你想插入一条新记录，但如果 name 和 value 的组合已经存在，则更新该记录的其他字段，可以使用以下 SQL 语句：

INSERT INTO my_table (id, name, value)
VALUES (1, 'example', 100)
ON DUPLICATE KEY UPDATE 
    id = VALUES(id),
    name = VALUES(name),
    value = VALUES(value);

在这个例子中：

  • 如果 name = 'example' 且 value = 100 的记录不存在，则插入一条新记录。
  • 如果 name = 'example' 且 value = 100 的记录已经存在，则更新该记录的 id, name, 和 value 字段。
示例解释：

假设表 my_table 中已经有以下数据：

idnamevalue
1example100

执行上述 INSERT ... ON DUPLICATE KEY UPDATE 语句后，表中的数据不会改变，因为 name 和 value 的组合已经存在。

但是，如果你执行以下语句：

INSERT INTO my_table (id, name, value)
VALUES (2, 'example', 100)
ON DUPLICATE KEY UPDATE 
    id = VALUES(id),
    name = VALUES(name),
    value = VALUES(value);

由于 name = 'example' 且 value = 100 的记录已经存在，id 将被更新为 2，表中的数据将变成：

idnamevalue
2example100

这就是如何使用 INSERT ... ON DUPLICATE KEY UPDATE 在 MySQL 中实现插入时如果存在指定键则进行更新的操作。

2-【添加时更新】DUPLICATE KEY 对比REPLACE

在 MySQL 中，INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE INTO 都是用于处理插入数据时遇到唯一键冲突的情况，但它们的行为有所不同。

INSERT … ON DUPLICATE KEY UPDATE

  • 行为：

    • 如果插入的记录不存在，则插入新记录。
    • 如果插入的记录存在（基于唯一键或主键冲突），则更新指定的字段。

  • 语法：

    INSERT INTO table (column1, column2, column3)
VALUES (value1, value2, value3)
ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2);

  • 特点：

    • 可以选择性地更新某些字段。
    • 更灵活，可以只更新部分字段，而不是全部字段。
REPLACE INTO

  • 行为：

    • 如果插入的记录不存在，则插入新记录。
    • 如果插入的记录存在（基于唯一键或主键冲突），则删除原有的记录，并插入新的记录。

  • 语法：

    REPLACE INTO table (column1, column2, column3)
VALUES (value1, value2, value3);

  • 特点：

    • 会删除原有的记录并插入新的记录，相当于先执行 DELETE 再执行 INSERT。
    • 无法选择性地更新部分字段，而是完全替换原有的记录。
3-对比

  1. 数据更新方式：

    • INSERT ... ON DUPLICATE KEY UPDATE：更新指定的字段。
    • REPLACE INTO：删除原有记录并插入新记录。

  2. 性能：

    • INSERT ... ON DUPLICATE KEY UPDATE：通常比 REPLACE INTO 更高效，因为更新操作比删除和插入操作更轻量。

  3. 自增ID：

    • INSERT ... ON DUPLICATE KEY UPDATE：如果更新现有记录，自增ID不会变化。
    • REPLACE INTO：如果原有记录被删除并插入新记录，自增ID会变化。

  4. 适用场景：

    • 当你需要保留原有记录的某些字段，只更新部分字段时，使用 INSERT ... ON DUPLICATE KEY UPDATE。
    • 当你需要完全替换原有记录时，使用 REPLACE INTO。
4-示例

假设有表 my_table：

CREATE TABLE my_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    value INT,
    UNIQUE KEY unique_name_value (name, value)
);

插入数据：

INSERT INTO my_table (name, value)
VALUES ('example', 100)
ON DUPLICATE KEY UPDATE value = VALUES(value);

如果 name='example' 和 value=100 已经存在，则更新 value 字段。

使用 REPLACE INTO：

REPLACE INTO my_table (name, value)
VALUES ('example', 100);

如果 name='example' 和 value=100 已经存在，则删除原有记录并插入新的记录。

总结
  • 使用 INSERT ... ON DUPLICATE KEY UPDATE 当你需要更新现有记录的部分字段。
  • 使用 REPLACE INTO 当你需要完全替换现有记录。

选择哪种方法取决于你的具体需求，以及你希望如何处理冲突的记录。

3-【查询后】添加到库中

在 MySQL 中，INSERT INTO ... SELECT 语句用于将一个表的数据插入到另一个表中。这是一种非常高效的数据复制方法，尤其是在需要批量插入数据时。

1-语法
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
2-示例

假设你有两个表 my_table 和 my_table2，它们的结构相同或相似，你可以使用 INSERT INTO ... SELECT 语句将 my_table 中的数据插入到 my_table2 中。

示例 1: 插入所有数据
INSERT INTO my_table2
SELECT * FROM my_table;

在这个例子中，my_table 中的所有数据都将被插入到 my_table2 中。

示例 2: 有条件地插入数据

如果你只想插入满足某些条件的数据，可以在 SELECT 语句中添加 WHERE 子句。

INSERT INTO my_table2
SELECT * FROM my_table
WHERE value > 50;

在这个例子中，只有 value 大于 50 的记录才会被插入到 my_table2 中。

示例 3: 插入指定列的数据

如果 my_table2 和 my_table 的列不完全相同，你可以指定要插入的列。

INSERT INTO my_table2 (column1, column2)
SELECT column1, column2
FROM my_table;

在这个例子中，只有 my_table 中的 column1 和 column2 的数据会被插入到 my_table2 中，并且只插入到 my_table2 的 column1 和 column2 列。

3-注意事项

  1. 表结构匹配：

    • 如果 my_table2 和 my_table 的列不完全相同，你需要确保 SELECT 语句中的列和 INSERT INTO 语句中的列匹配。

  2. 主键和唯一键：

    • 如果 my_table2 中有主键或唯一键约束，插入的数据不能违反这些约束。

  3. 数据类型：

    • 确保 my_table 中的数据类型与 my_table2 中的数据类型兼容。

  4. INSERT IGNORE：

    • 如果你希望在插入过程中忽略违反唯一键约束的记录，可以使用 INSERT IGNORE。

    示例：

    INSERT IGNORE INTO my_table2
SELECT * FROM my_table;

    这将在插入过程中忽略由于唯一键冲突而导致错误的记录。

4-示例表结构

假设 my_table 和 my_table2 的结构如下：

CREATE TABLE my_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    value INT
);

CREATE TABLE my_table2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    value INT
);
5-示例数据

假设 my_table 中有以下数据：

idnamevalue
1example100
2test200

执行以下 SQL 语句：

INSERT INTO my_table2
SELECT * FROM my_table;

结果 my_table2 中的数据将变为：

idnamevalue
1example100
2test200
6-总结
  • INSERT INTO ... SELECT 是一种高效的数据复制方法。
  • 你可以选择性地插入数据，或者指定插入的列。
  • 确保目标表和源表的结构兼容，并注意主键和唯一键约束。

如果你有更多具体需求或问题，欢迎继续提问！

4-【关联表】更新

在 MySQL 中，UPDATE 语句支持使用 JOIN 来连接两张表，从而可以根据另一张表的数据来更新目标表。这种操作在需要根据关联表的数据进行更新时非常有用。

1-语法
UPDATE target_table
JOIN source_table ON target_table.column = source_table.column
SET target_table.column = source_table.column
WHERE condition;
2-示例1

假设我们有两张表 employees 和 departments，我们希望根据 departments 表中的数据来更新 employees 表中的某些字段。

1-表结构
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department_id INT,
    salary INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    bonus_percentage INT
);
2-示例数据

employees 表数据：

idnamedepartment_idsalary
1Alice15000
2Bob26000
3Charlie15500

departments 表数据：

idnamebonus_percentage
1Sales10
2Marketing15
3-更新示例

我们希望根据 departments 表中的 bonus_percentage 来更新 employees 表中的 salary 字段，即将员工薪资加上相应的奖金。

UPDATE employees
JOIN departments ON employees.department_id = departments.id
SET employees.salary = employees.salary + (employees.salary * departments.bonus_percentage / 100);
4-解释
  • UPDATE employees：表示我们要更新 employees 表。
  • JOIN departments ON employees.department_id = departments.id：通过 department_id 连接 employees 和 departments 表。
  • SET employees.salary = employees.salary + (employees.salary * departments.bonus_percentage / 100)：根据 departments 表中的 bonus_percentage，计算新的薪资并更新到 employees 表中。
5-更新后的数据

执行上述 SQL 语句后，employees 表中的数据将变为：

idnamedepartment_idsalary
1Alice15500
2Bob26900
3Charlie16050
6-注意事项

  1. 连接条件：

    • 确保连接条件正确，通常是基于外键或关联字段。

  2. SET 子句：

    • 在 SET 子句中，可以更新多个字段，只需用逗号分隔。

  3. WHERE 子句：

    • 如果有需要，可以在 WHERE 子句中添加条件，限制更新的记录。
3-示例 2: 更新多个字段

假设我们希望在更新薪资的同时，更新员工的部门名称。

UPDATE employees
JOIN departments ON employees.department_id = departments.id
SET employees.salary = employees.salary + (employees.salary * departments.bonus_percentage / 100),
    employees.department_name = departments.name;
更新后的数据
idnamedepartment_idsalarydepartment_name
1Alice15500Sales
2Bob26900Marketing
3Charlie16050Sales
4-总结
  • 使用 UPDATE ... JOIN 可以根据另一张表的数据来更新目标表中的字段。
  • 连接条件和 SET 子句是关键，确保连接条件正确，并且 SET 子句中指定要更新的字段和更新逻辑。

如果你有更多具体需求或问题，欢迎继续提问！

5-【批量】数据插入

在 MySQL 中，批量插入数据可以显著提高数据插入的效率，因为相比于逐条插入，批量插入可以减少与数据库的交互次数，从而降低网络开销和提高性能。

1-常见的批量插入方法

  1. 使用 VALUES 子句批量插入：

    这是最常见的批量插入方法，通过在一个 INSERT 语句中插入多条记录。

    INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...),
...
(value1, value2, value3, ...);

  2. 使用 SELECT 语句批量插入：

    通过从另一个表或查询结果中选择数据并插入到目标表中。

    INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

  3. 使用 LOAD DATA INFILE：

    从文件中批量导入数据，这是 MySQL 中最快的导入方法之一。

    LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, column3, ...);
2-示例

假设我们有一个名为 students 的表，结构如下：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade FLOAT
);
方法一: 使用 VALUES 子句批量插入
INSERT INTO students (name, age, grade)
VALUES
('Alice', 20, 85.5),
('Bob', 22, 90.0),
('Charlie', 21, 78.5);
方法二: 使用 SELECT 语句批量插入

假设有另一个表 temp_students，包含相同结构的数据：

INSERT INTO students (name, age, grade)
SELECT name, age, grade
FROM temp_students
WHERE age >= 20;
方法三: 使用 LOAD DATA INFILE

假设有一个 CSV 文件 students.csv，内容如下：

Alice,20,85.5
Bob,22,90.0
Charlie,21,78.5

插入语句：

LOAD DATA INFILE '/path/to/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, age, grade);
3-注意事项

  1. 性能优化：

    • 在批量插入大量数据时，可以考虑临时禁用索引，插入完成后重新启用，以提高插入速度。

      ALTER TABLE table_name DISABLE KEYS;
-- 批量插入数据
ALTER TABLE table_name ENABLE KEYS;

    • 调整 MySQL 的缓冲区大小，如 innodb_buffer_pool_size 和 sort_buffer_size，以适应批量插入的操作。

  2. 事务处理：

    • 对于批量插入操作，建议使用事务来确保数据的一致性和完整性。

      START TRANSACTION;
INSERT INTO table_name ...;
COMMIT;

  3. 文件导入权限：

    • 使用 LOAD DATA INFILE 需要对应的文件读取权限，确保 MySQL 用户有文件导入的权限。

  4. 数据一致性：

    • 在批量插入过程中，注意数据的一致性，特别是在有外键约束或唯一性约束的情况下。
4-总结
  • 批量插入数据可以显著提高数据插入的效率。
  • 可以通过 VALUES 子句、SELECT 语句或 LOAD DATA INFILE 进行批量插入。
  • 在处理大量数据时，注意性能优化和事务处理，确保数据的一致性和完整性。

希望这些信息对你有帮助！如果你有任何其他问题，请随时提问。

6-【批量】数据更新

在 MySQL 中，批量更新数据可以通过多种方法实现，以提高数据更新的效率。以下是几种常见的批量更新方法：

1. 使用 UPDATE 语句批量更新

你可以在一个 UPDATE 语句中使用 CASE 语句或 JOIN 语句来批量更新多条记录。

使用 CASE 语句
UPDATE table_name
SET column1 = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ...
    ELSE column1
END,
column2 = CASE
    WHEN condition1 THEN value3
    WHEN condition2 THEN value4
    ...
    ELSE column2
END
WHERE condition;
示例

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

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade FLOAT
);

插入一些示例数据：

INSERT INTO students (name, age, grade)
VALUES
('Alice', 20, 85.5),
('Bob', 22, 90.0),
('Charlie', 21, 78.5);

现在我们希望根据学生的年龄更新他们的成绩：

UPDATE students
SET grade = CASE
    WHEN age = 20 THEN grade + 5
    WHEN age = 21 THEN grade + 3
    WHEN age = 22 THEN grade + 2
    ELSE grade
END;
2. 使用 JOIN 语句批量更新

你可以在 UPDATE 语句中使用 JOIN 语句来根据另一张表的数据更新目标表。

示例

假设我们有两张表 students 和 bonuses，我们希望根据 bonuses 表中的数据来更新 students 表中的成绩。

students 表：

idnameagegrade
1Alice2085.5
2Bob2290.0
3Charlie2178.5

bonuses 表：

agebonus
205
213
222

批量更新语句：

UPDATE students
JOIN bonuses ON students.age = bonuses.age
SET students.grade = students.grade + bonuses.bonus;
3. 使用子查询批量更新

你也可以使用子查询来批量更新数据。

示例

假设我们希望将所有年龄为 20 的学生的成绩增加 5 分，将所有年龄为 21 的学生的成绩增加 3 分，将所有年龄为 22 的学生的成绩增加 2 分。

UPDATE students
SET grade = grade + (SELECT bonus FROM bonuses WHERE bonuses.age = students.age)
WHERE age IN (20, 21, 22);
4-注意事项

  1. 性能优化：

    • 对于大量数据的更新，建议使用事务来确保数据的一致性和完整性。

    • 临时禁用索引，更新完成后重新启用，以提高更新速度。

      ALTER TABLE table_name DISABLE KEYS;
-- 执行批量更新
ALTER TABLE table_name ENABLE KEYS;

  2. 事务处理：

    • 在进行批量更新时，建议使用事务来确保数据的一致性和完整性。

      START TRANSACTION;
-- 批量更新语句
COMMIT;

  3. 数据一致性：

    • 在更新过程中，注意数据的一致性，特别是在有外键约束或唯一性约束的情况下。
5-总结
  • 批量更新数据可以显著提高数据更新的效率。
  • 可以通过 CASE 语句、JOIN 语句或子查询来进行批量更新。
  • 在处理大量数据时，注意性能优化和事务处理，确保数据的一致性和完整性。

3-INSERT、UPDATE、REPLACE、DELETE对比

一、INSERT和REPLACE

INSERT和REPLACE语句的功能都是向表中插入新的数据。这两条语句的语法类似。它们的主要区别是如何处理重复的数据。

1. INSERT的一般用法

MySQL中的INSERT语句和标准的INSERT不太一样，在标准的SQL语句中，一次插入一条记录的INSERT语句只有一种形式。

INSERT INTO tablename(列名…) VALUES(列值);

而在MySQL中还有另外一种形式。

INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;

第一种方法将列名和列值分开了，在使用时，列名必须和列值的数一致。如下面的语句向users表中插入了一条记录：

INSERT INTO users(id, name, age) VALUES(123, '姚明', 25);

第二种方法允许列名和列值成对出现和使用，如下面的语句将产生同样的效果。

INSERT INTO users SET id = 123, name = '姚明', age = 25;

如果使用了SET方式，必须至少为一列赋值。如果某一个字段使用了默认值（如默认或自增值），这两种方法都可以省略这些字段。如id字段上使用了自增值，上面两条语句可以写成如下形式：

INSERT INTO users (name, age) VALUES('姚明',25);
INSERT INTO users SET name = '姚明', age = 25;

MySQL在VALUES上也做了些变化。如果VALUES中什么都不写，那MySQL将使用表中每一列的默认值来插入新记录。

INSERT INTO users () VALUES();

如果表名后什么都不写，就表示向表中所有的字段赋值。使用这种方式，不仅在VALUES中的值要和列数一致，而且顺序不能颠倒。

INSERT INTO users VALUES(123, '姚明', 25);

如果将INSERT语句写成如下形式MySQL将会报错。

INSERT INTO users VALUES('姚明',25);
2. 使用INSERT插入多条记录

看到这个标题也许大家会问，这有什么好说的，调用多次INSERT 语句不就可以插入多条记录了吗！但使用这种方法要增加服务器的负荷，因为，执行每一次 SQL服务器都要同样对SQL进行分析、优化等操作。幸好MySQL提供了另一种解决方案，就是使用一条INSERT语句来插入多条记录。这并不是标准的 SQL语法，因此只能在MySQL中使用。

INSERT INTO users(name, age)
VALUES('姚明', 25), ('比尔.盖茨', 50), ('火星人', 600);

上面的INSERT 语句向users表中连续插入了3条记录。值得注意的是，上面的INSERT语句中的VALUES后必须每一条记录的值放到一对(…)中，中间使用","分割。假设有一个表table1

CREATE TABLE table1(n INT);

如果要向table1中插入5条记录，下面写法是错误的：

INSERT INTO table1 (i) VALUES(1,2,3,4,5);

MySQL将会抛出下面的错误

ERROR 1136: Column count doesn't match value count at row 1

而正确的写法应该是这样：

INSERT INTO table1(i) VALUES(1),(2),(3),(4),(5);

当然，这种写法也可以省略列名，这样每一对括号里的值的数目必须一致，而且这个数目必须和列数一致。如：

INSERT INTO table1 VALUES(1),(2),(3),(4),(5);
3. REPLACE语句

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引，当我们再向这个表中使用已经存在的键值插入一条记录，那将会抛出一个主键冲突的错误。当然，我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法，必须先使用DELETE语句删除原先的记录，然后再使用 INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案，这就是REPLACE语句。使用REPLACE插入一条记录时，如果不重复，REPLACE就和INSERT的功能一样，如果有重复记录，REPLACE就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。

在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了 DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。

REPLACE的语法和INSERT非常的相似，如下面的REPLACE语句是插入或更新一条记录。

REPLACE INTO users (id,name,age) VALUES(123, '赵本山', 50);

插入多条记录：

REPLACE INTO users(id, name, age)
VALUES(123, '赵本山', 50), (134,'Mary',15);

REPLACE也可以使用SET语句

REPLACE INTO users SET id = 123, name = '赵本山', age = 50;

上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

假设table1中已经有了3条记录

abc
111
222
333

下面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

返回的结果如下

Query OK, 4 rows affected (0.00 sec)

在table1中的记录如下

abc
123

我们可以看到，REPLACE将原先的3条记录都删除了，然后将（1, 2, 3）插入。

二、UPDATE

UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式，在后面可以加WHERE以限制更新的记录范围。

UPDATE table_name SET column_name1 = value1, column_name2 = value2, ...
WHERE ... ;

如下面的语句将users表中id等于123的记录的age改为24

UPDATE users SET age = 24 WHERE id = 123;

同样，可以使用UPDATE更新多个字段的值

UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;

上面的UPDATE语句通过WHERE指定一个条件，否则，UPDATE将更新表中的所有记录的值。

在使用UPDATE更新记录时，如果被更新的字段的类型和所赋的值不匹配时，MySQL将这个值转换为相应类型的值。如果这个字段是数值类型，而且所赋值超过了这个数据类型的最大范围，那么MySQL就将这个值转换为这个范围最大或最小值。如果字符串太长，MySQL就将多余的字符串截去。如果设置非空字段为空，那么将这个字段设置为它们的默认值，数字的默认值是0，字符串的默认值是空串（不是null，是""）。

有两种情况UPDATE不会对影响表中的数据。

  1. 当WHERE中的条件在表中没有记录和它匹配时。
  2. 当我们将同样的值赋给某个字段时，如将字段abc赋为’123’，而abc的原值就是’123’。

和INSERT、REPLACE一样，UPDATE也返回所更新的记录数。但这些记录数并不包括满足WHERE条件的，但却未被更新的记录。如下同的UPDATE语句就未更新任何记录。

UPDATE users SET age = 30 WHERE id = 12;

Query OK, 0 rows affected (0.00 sec)

需要注意的是，如果一个字段的类型是TIMESTAMP，那么这个字段在其它字段更新时自动更新。

在有些时候我们需要得到UPDATE所选择的行数，而不是被更新的行数。我们可以通过一些API来达到这个目的。如MySQL提供的C API提供了一个选项可以得到你想要的记录数。而MySQL的JDBC驱动得到的默认记录数也是匹配的记录数。

UPDATE和REPLACE基本类似，但是它们之间有两点不同。

  1. UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。
  2. UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。

三、DELETE和TRUNCATE TABLE

在MySQL中有两种方法可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的所有记录。因此，DELETE语句更灵活。

如果要清空表中的所有记录，可以使用下面的两种方法：

DELETE FROM table1;
TRUNCATE TABLE table1;

其中第二条记录中的TABLE是可选的。

如果要删除表中的部分记录，只能使用DELETE语句。

DELETE FROM table1 WHERE ...;

如果DELETE不加WHERE子句，那么它和TRUNCATE TABLE是一样的，但它们有一点不同，那就是DELETE可以返回被删除的记录数，而TRUNCATE TABLE返回的是0。

如果一个表中有自增字段，使用TRUNCATE TABLE和没有WHERE子句的DELETE删除所有记录后，这个自增字段将起始值恢复成1.如果你不想这样做的话，可以在DELETE语句中加上永真的WHERE，如WHERE 1或WHERE true.

DELETE FROM table1 WHERE 1;

上面的语句在执行时将扫描每一条记录。但它并不比较，因为这个WHERE条件永远为true。这样做虽然可以保持自增的最大值，但由于它是扫描了所有的记录，因此，它的执行成本要比没有WHERE子句的DELETE大得多。

DELETE和TRUNCATE TABLE的最大区别是DELETE可以通过WHERE语句选择要删除的记录。但执行得速度不快。而且还可以返回被删除的记录数。而TRUNCATE TABLE无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。

和标准的SQL语句不同，DELETE支持ORDER BY和LIMIT子句，通过这两个子句，我们可以更好地控制要删除的记录。如当我们只想删除WHERE子句过滤出来的记录的一部分，可以使用LIMIT，如果要删除后几条记录，可以通过ORDER BY和LIMIT配合使用。假设我们要删除users表中name等于"Mike"的前6条记录。可以使用如下的DELETE语句：

DELETE FROM users WHERE name = 'Mike' LIMIT 6;

一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用ORDER BY对记录进行排序。

DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

标签：INSERT,name,复杂,INTO,记录,插入,开发,Mysql,table
From： https://blog.csdn.net/2301_77717148/article/details/144981727

相关文章

  • GIS开发在构建实景三维中国系统中的关键作用
    随着信息技术的飞速发展，地理信息系统（GIS）开发在现代社会的应用越来越广泛。特别是在中国，GIS开发在构建实景三维中国系统中发挥着至关重要的作用。根据中国测绘学会发布的《中国测绘地理信息科技创新与产业发展研究报告（2024）》，到2025年，我国预计将初步建成实景三维中国系统，这一成就......
  • JSP剧本杀门店管理系统v03na（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景与意义随着剧本杀游戏的日益流行，剧本杀门店数量不断增加，门店管理问题逐渐凸显。传统的人工管理方式存在效率低下、信息不准确等问题，无......
  • JSP菊花茶销售网站x6v6e--（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景与意义随着人们健康意识的增强，天然、健康的饮品越来越受到消费者的青睐。菊花茶作为一种传统中草药茶饮，具有清热解毒、明目等功效，市场......
  • JSP居民小区安全巡检系统服务端设计65261程序+源码+数据库+调试部署+开发环境
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、研究背景随着城市化进程的加快和人口密度的增加，社区安全问题日益凸显。传统的社区管理方式已不能满足现代化的需求，因此需要一种更加高效、智能......
  • JSP居民睡眠质量监控与分析系统9d0fi--（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景与意义随着现代生活节奏的加快和工作压力的增大，越来越多的人开始关注自己的睡眠质量。然而，传统的睡眠监测方式存在一定的局限性，如需要......
  • ssm智能题库管理系统t1xri--（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、研究背景与意义在教育信息化的大背景下，传统题库管理方式逐渐暴露出效率低下、更新滞后和资源分配不均等问题。随着人工智能技术的飞速发展，开发......
  • ssm智慧家政系统36myw（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景随着社会经济的发展和人们生活水平的提高，家政服务需求日益增长。然而，传统的家政服务模式存在着信息不透明、服务质量不稳定等问题，已无......
  • 让您的工作效率提高数倍的8种开发人员工具
    在现代社会，产品领域正以前所未有的速度演变，这得益于持续的创新和大量新技术的涌现。每天都有无数新工具发布，找到那些能带来巨大价值并值得升级到你的技术栈中的工具可能会让人感到不知所措。在这篇文章中，我整理了我最近发现的8个强大工具，它们将显著提升你的开发工作流程，并为你......
  • Jetbrains fleet 配置 C++开发环境（基于CMAKE和MinGW）
    Jetbrainsfleet配置C++开发环境1.安装JetbrainsFleet到Fleet下载页面下载Toolbox并安装Jetbrains-Fleet下载页安装完成后在任务栏打开Toolbox，在列表中选择安装fleet。2.为Fleet准备Workspace在适当的地方建立文件夹作为fleet的工作空间，并在fleet中打开。3......
  • ssm自动算薪系统we32b（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景与意义随着企业规模的扩大和业务复杂性的增加，传统的工资计算方式逐渐暴露出效率低下、准确性差等问题。为了解决这些问题，开发一套自动......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99