ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
(5 оценок, среднее: 5,00 из 5)
Загрузка...
Одной из наиболее распространенных операций в MySQL является изменение существующего значения записи в таблице.
В этой статье мы расскажем, как использовать команду UPDATE в MySQL вместе с некоторыми полезными примерами.
Рассматриваются в данном руководстве следующие примеры:
Для этого урока мы будем использовать следующую таблицу worker в качестве примера. Это структура таблицы для примера.
mysql> DESC worker; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | dept | varchar(10) | YES | | Sales | | | salary | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
В настоящее время таблица worker имеет следующие записи.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | Sales | 5000 | | 200 | Anton | IT | 5500 | | 300 | Maxim | IT | 7000 | | 400 | Dimon | Marketing | 9500 | | 500 | Anton | IT | 6000 | | 501 | Anna | Accounting | NULL | +-----+--------+------------+--------+
Если вы новичок в MySQL, вероятно, вы должны сначала понять основы MySQL , включая , как создать базу данных MySQL.
В следующем простом примере, команда UPDATE будет установлено значение столбца DEPT к IT для всех строк в таблице worker.
mysql> UPDATE worker SET dept='IT '; Query OK, 3 rows affected (0.02 sec) Rows matched: 6 Changed: 3 Warnings: 0
Вывод команды UPDATE будет иметь следующие две строки:
Вот обновленные записи после вышеуказанной команды UPDATE.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | IT | 5000 | | 200 | Anton | IT | 5500 | | 300 | Maxim | IT | 7000 | | 400 | Dimon | IT | 9500 | | 500 | Anton | IT | 6000 | | 501 | Anna | IT | NULL | +-----+--------+------------+--------+
Вместо того, чтобы обновить все записи, вы можете выборочно обновить определенные записи на основе условия WHERE.
В следующем примере будет обновлять таблицу сотрудников и назначить сотрудника, который имеют зарплату больше или равно 7000 для отдела маркетинга.
mysql> UPDATE worker SET dept='Marketing' WHERE salary >=7000; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0
Были только две записи, которые соответствовали выше, таблица обновилась, как показано ниже.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | IT | 5000 | | 200 | Anton | IT | 5500 | | 300 | Maxim | Marketing | 7000 | | 400 | Dimon | Marketing | 9500 | | 500 | Anton | IT | 6000 | | 501 | Anna | IT | NULL | +-----+--------+------------+--------+
Мы обсуждали много различных практических условий WHERE в нашем учебнике MySQL по команде SELECT. Это очень полезно, чтобы понять, как использовать предложение WHERE эффективно во время UPDATE: 25 основных примеров в MySQL для команды SELECT.
При присвоении значения в столбце после SET, вы не всегда должны указывать статические значения. Вы также можете использовать выражения, как показано ниже.
Ниже приводится очень простое выражение, где оно увеличивает значение заработной платы на 500 для всех сотрудников отдела IT.
mysql> UPDATE worker SET salary=salary+500 WHERE dept='IT'; Query OK, 3 rows affected (0.01 sec) Rows matched: 4 Changed: 3 Warnings: 0
Были только 4 записи, которые соответствовали условию выше. Но только три записи были обновлены, как показано ниже, в качестве одного из записей сотрудников, который принадлежит к техническому отделу, имел нулевое значение в поле заработной платы. Таким образом, зарплата выше + 500 выражение по прежнему NULL, и не обновляется эта конкретную запись.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | IT | 5500 | | 200 | Anton | IT | 6000 | | 300 | Maxim | Marketing | 7000 | | 400 | Dimon | Marketing | 9500 | | 500 | Anton | IT | 6500 | | 501 | Anna | IT | NULL | +-----+--------+------------+--------+
Вместо того, чтобы указать статическое значение или выражение, вы также можете использовать ключевое слово “DEFAULT”, когда вы присваиваете значение столбца после SET.
Если вы посмотрите на вывод “DESC worker”, показанного ниже, вы увидите, что столбец с именем по умолчанию. Как вы видите там, зарплата имеет значение по умолчанию NULL. Отдел имеет значение по умолчанию продаж.
mysql> DESC worker; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | dept | varchar(10) | YES | | Sales | | | salary | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
Во-первых, давайте обновим столбец зарплаты по умолчанию, используя ключевое слово DEFAULT, как показано ниже.
mysql> UPDATE worker SET salary=DEFAULT; Query OK, 5 rows affected (0.03 sec) Rows matched: 6 Changed: 5 Warnings: 0
Затем обновите столбец отдела до значения по умолчанию, используя ключевое слово DEFAULT, как показано ниже.
mysql> UPDATE worker SET dept=DEFAULT; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0
Как видно из следующих выходных данных, мы можем видеть, что ключевое слово DEFAULT взял соответствующие значения по умолчанию из определения таблицы сотрудников, и использовали их, чтобы обновить его. Колонка Департамент получил обновление до продаж и зарплата обновлена до NULL, как показано ниже.
mysql> SELECT * FROM worker; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Andrey | Sales | NULL | | 200 | Anton | Sales | NULL | | 300 | Maxim | Sales | NULL | | 400 | Dimon | Sales | NULL | | 500 | Anton | Sales | NULL | | 501 | Anna | Sales | NULL | +-----+--------+-------+--------+
В одном операторе обновления, вы также можете обновить значения для более чем одного столбца, как показано ниже.
В следующем примере, мы присваивая значения как зарплаты и столбца DEPT для всех записей, где идентификатор сотрудника больше 300.
mysql> UPDATE worker SET salary=5000, dept='Marketing' WHERE id > 300; Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0
Как видно из следующего вывода, вышеприведенная команда UPDATE обновляет два значения столбца в течение последних трех записей, которые соответствовали выше, когда выполняется условие.
mysql> SELECT * FROM worker; +-----+--------+-----------+--------+ | id | name | dept | salary | +-----+--------+-----------+--------+ | 100 | Andrey | Sales | NULL | | 200 | Anton | Sales | NULL | | 300 | Maxim | Sales | NULL | | 400 | Dimon | Marketing | 5000 | | 500 | Anton | Marketing | 5000 | | 501 | Anna | Marketing | 5000 | +-----+--------+-----------+--------+
Кроме того, помимо получения хорошую статьи на команде UPDATE в MySQL, очень полезно, чтобы понять все доступные операции MySQL INSERT: 12 основных примеров в MySQL для команды INSERT.
Мы также можем использовать опцию LIMIT, чтобы ограничить количество записей, которые должны быть обновлены.
Даже если условие, где соответствует несколько записей, оператор обновления будет обновлять только 1-е X количество записей, указанных в значении LIMIT.
В следующем примере мы присваиваем зарплату всех записей до 6500, так как мы не имеем WHERE. Но, мы используем LIMIT 3. Это означает, что она будет обновлять зарплату только первым трем записям для условия согласования.
mysql> UPDATE worker SET salary=6500 LIMIT 3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0
Как видно из следующего выхода, только 1-е три записи обновляются предыдущей командой.
mysql> SELECT * FROM worker; +-----+--------+-----------+--------+ | id | name | dept | salary | +-----+--------+-----------+--------+ | 100 | Andrey | Sales | 6500 | | 200 | Anton | Sales | 6500 | | 300 | Maxim | Sales | 6500 | | 400 | Dimon | Marketing | 5000 | | 500 | Anton | Marketing | 5000 | | 501 | Anna | Marketing | 5000 | +-----+--------+-----------+--------+
Одна вещь, чтобы иметь в виду, что LIMIT 3 на самом деле не означает, что обновление будет продолжать идти, пока он не обновит 3 записей. Вместо этого оператор обновления прекратит выполнение, как только он обработал первые 3 строки, которые соответствовали условию выполнения, независимо от того, были ли действительно обновлены эти записи или нет.
Можно также объединить две таблицы во время обновления. Можно также обновить значения из двух таблиц одновременно с помощью одного оператора обновления.
В этом примере мы будем использовать в таблице следующие преимущества наряду с нашей существующей таблицей сотрудников.
mysql> SELECT * from benefits; +------------+-------+ | dept | bonus | +------------+-------+ | Sales | 1000 | | IT | NULL | | Marketing | 800 | +------------+-------+
Следующее утверждение обновление будет увеличивать значение столбца заработной платы из таблицы сотрудников к значению, указанному в таблице льгот для соответствующего столбца DEPT.
Это означает, что мы должны объединить оба работника и таблицу преимущества во время обновления, как показано ниже. Используйте общее поле между этими двумя таблицами в предложении WHERE. В этом примере общее поле dept.
Кроме того, сразу после ключевого слова UPDATE укажите имя обоих таблиц, как показано ниже. После ключевого слова SET можно указать либо имя одного или нескольких столбцов, которые должны быть обновлены, либо из одной таблицы, или как в таблице.
mysql> UPDATE worker,benefits
-> SET worker.salary=worker.salary+benefits.bonus
-> WHERE worker.dept=benefits.dept and benefits.bonus is not null;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
Обратите внимание, что в приведенном выше обновлении, мы используем внутреннее соединение.
Ниже приведен вывод после того, как приведенное выше утверждение обновление было выполнено. Как вы видите ниже, зарплата работника получила увеличивается на основе значений из таблицы выгоды.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | Sales | 7500 | | 200 | Anton | Sales | 7500 | | 300 | Maxim | IT | 6500 | | 400 | Dimon | IT | 5000 | | 500 | Anton | Marketing | 5800 | | 501 | Anna | Marketing | 5800 | +-----+--------+------------+--------+
Аналогично предыдущему примеру внутреннего соединения, мы можем также использовать левое соединение.
Когда мы используем внутреннее соединение, мы не указываем ключевое слово “inner join”, так как это по умолчанию при объединении нескольких таблиц.
Однако при использовании соединения слева мы должны явно указать “left join”, как показано ниже.
mysql> UPDATE worker LEFT JOIN benefits on worker.dept = benefits.dept
-> SET worker.salary = worker.salary+500
-> WHERE benefits.bonus is null;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Вот выход после вышеуказанного обновления.
mysql> SELECT * FROM worker; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Andrey | Sales | 7500 | | 200 | Anton | Sales | 7500 | | 300 | Maxim | IT | 7000 | | 400 | Dimon | IT | 5500 | | 500 | Anton | Marketing | 5800 | | 501 | Anna | Marketing | 5800 | +-----+--------+------------+--------+
В MySQL в команде обновления, нет никакого прямого способа получения нового значения обновлений.
Например, в PostgreSQL, мы можем использовать что-то вроде этого: “UPDATE table_name SET column_name = expression WHERE condition RETURNING column_name”. В MySQL, мы не имеем возвращения как часть команды обновления MySQL.
Но, вы можете сделать следующее, чтобы получить возвращаемое значение внутри вашей процедуры, пакета, функции или из командной строки.
mysql> UPDATE worker SET salary = salary+500 WHERE id=400; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT salary AS tmp_salary FROM worker WHERE id=400; +------------+ | tmp_salary | +------------+ | 5500 | +------------+
В приведенном выше примере, после того, как обновление будет сделано, переменная tmp_salary имеет обновленную зарплату от идентификатора сотрудника
Если вы хотите получить значение зарплаты, прежде чем обновление было сделано, очевидно, вы просто должны переключить последовательность из двух вышеупомянутых утверждений. В этом случае будет сначала SELECT, а затем UPDATE.
Или, вы можете использовать “@” в качестве части заявления UPDATE и получить предварительно обновленное значение, как показано ниже.
UPDATE worker SET salary = salary+500 WHERE id=400 AND @tmp_salary := salary
В приведенной выше команде обновления, после того, как обновление будет сделано, переменная tmp_salary имеет предварительно обновляемую зарплату для сотрудников ID 400. Как вы видите здесь, хотя значение заработной платы уже обновлено до 6000. Переменная tmp_salary, которая использовалась в приведенной выше команде UPDATE по-прежнему имеет значение 5500.
mysql> SELECT * FROM worker WHERE id = 400; +-----+-------+------------+--------+ | id | name | dept | salary | +-----+-------+------------+--------+ | 400 | Dimon | IT | 6000 | +-----+-------+------------+--------+ mysql> SELECT @tmp_salary; +-------------+ | @tmp_salary | +-------------+ | 5500 | +-------------+
Вы также можете использовать условные обновления MySQL с помощью условных команд, как CASE, IF и т.д. Это полезно для упрощения обновления.
Вместо того чтобы использовать несколько обновлений, вы можете просто обойтись с помощью одной команды UPDATE, которая сочетает в себе все ваши операции.
Например, скажем, у нас есть следующие три оператора обновления, которые обновляют поле заработной платы на основе значения из поля отдела.
UPDATE worker SET salary = salary+1000 WHERE dept = 'Sales'; UPDATE worker SET salary = salary+500 WHERE dept = 'IT '; UPDATE worker SET salary = salary+800 WHERE dept = 'Marketing';
Вы можете объединить все перечисленные выше заявления трех UPDATE в одном заявлении UPDATE используя условие CASE, как показано ниже.
UPDATE worker SET salary = CASE dept WHEN 'Sales' THEN salary+1000 WHEN 'IT ' THEN salary+500 WHEN 'Marketing' THEN salary+500 ELSE salary END;
Так же, как CASE, вы можете также использовать IF условие для обновления значения столбца соответственно.
Вы можете использовать значение ORDER BY во время обновления. Предложения ORDER BY, безусловно, имеет смысл во время SELECT. Но, зачем нам нужен ORDER BY во время обновления.
Скажем, у вас есть уникальный идентификатор и поле идентификатор сотрудника таблицы.
Когда вы выполните следующую команду, чтобы увеличить идентификатор сотрудника на 100, вы можете получить сообщение об ошибке дубликатом.
mysql> UPDATE contractor set id=id+100; ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'
Это происходит потому, что, когда он пытается обновить значение идентификатора от 100 до 200, то уже есть существующая запись с идентификатором, как 200. Поле ID также имеет уникальный UNIQUE, в данном случае это является PRIMARY ключом. Таким образом, мы получаем вышеуказанную ошибку.
Для этого мы должны выполнить следующую команду с ORDER BY с идентификатором Desc.
mysql> UPDATE worker SET id=id+100 order by id desc; Query OK, 6 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0
В приведенном выше случае, сначала начните с максимального идентификатора сотрудника, обновлять эту запись, а затем переходить к следующему. Таким образом, он никогда не будет иметь сценарий повторяющегося значения в процессе обновления.
