Синтаксис sql инструкций для создания запросов на изменение данных

Изменение значений полей в таблице

Чтобы изменить значение в поле в таблице БД необходимо выполнить запрос UPDATE.

Общий синтаксис запроса UPDATE

UPDATE [LOW_PRIORITY] имя_таблицы
    SET название_поля1=выражение1,название_поля2=выражение2,...
    [WHERE условное_выражение] [LIMIT количество_записей]

UPDATE заменит значения текущих полей таблицы на новые значения.
SET устанавливает какие поля изменять и новые значения, которые нужно присвоить этим полям.
WHERE (если необходимо) — условие на изменение определенных записей. Если WHERE не указан, изменены будут все записи.
При указании параметра LOW_PRIORITY, выполнение UPDATE задержится пока другие клиенты читают таблицу.

Пример запроса на изменение всех записей поля «apple» таблицы «fruit» на определенное значение

UPDATE fruit SET apple=5;

Пример запроса с WHERE который изменяет определенную запись:

UPDATE tovar SET price=500 WHERE id=5

При изменении значения поля можно использовать его текущее значение.
Пример запроса увеличивающего значение поля price в 2 раза:

UPDATE tovar SET price=price*2;

SET в UPDATE вычисляет выражения слева направо.
Пример запроса который удваивает цену (поле price), а потом уменьшает его на 10:

UPDATE tovar SET price=price*2, price=price-10;

Запрос UPDATE возвратит количество полей, которые были изменены в этом запросе.
Использование LIMIT позволит изменять заданное количество записей.

Под обновлением данных в БД подразумевается изменение значений в существующих записях таблицы. При этом возможно как изменение значений полей в группе строк (даже всех строк таблицы), так и правка значения поля отдельной строки.

В SQL, изменить запись в таблице БД можно с помощью команды UPDATE. В самом минимальном виде команда обновления данных выглядит следующим образом:

UPDATE таблица SET поле = значение

Здесь, UPDATE – команда указывающая, что запрос на обновление данных;

таблица – название таблицы, в которой будет проводиться изменения;

SET – команда, после которой через запятую указываются поля с назначаемыми им значениями;

поле – поле таблицы, в которое будет внесено изменение;

значение – новое значение, которое будет внесено в поле.

Например, если необходимо задать полю во всех строках таблицы значение равное нулю, можно выполнить такой запрос:

UPDATE goods SET price = 0

В этом случае, поле price абсолютно во всех имеющиеся строках таблицы примет значение 0.

Изменение одного значения

Изменение значения всех полей в таблице необходимо крайне редко. Чаще всего необходимо поменять значение какой-то конкретной записи. Для этого в завершении строки с командой UPDATE будет добавлена директива WHERE, в которой указывается условие, определяющее с какой именно строкой нужно выполнить операцию обновления.

Имеется таблица:

Для примера, нужно обновить стоимость товара с известным нам его значением num. Для этого, выполним следующий запрос:

UPDATE goods SET price = 150 WHERE num = 2

Теперь, перед операцией изменения полей, будет выбрана строка, удовлетворяющая условию num = 2. Такая строка в таблице одна. В этой стоке цена и будет изменена на значение 150. В результате получим таблицу с измененной ценой товара.

Внесение изменений в несколько строк с условием отбора

Если вспомнить все многообразие условий в запросе, можно представить себе насколько разнообразными могут быть выборки. Поэтому и запросы на обновления могут выполняться либо с одной строкой, либо с группой строк, либо со всеми строками таблицы. Все зависит от стоящей перед вами задачи, а так же с какими строками таблицы нужно выполнять операции обновления.

Например, мы хотим уменьшить в два раза цену всех товаров, которые сейчас стоят от 100 и более. Запрос:

UPDATE goods SET price = price / 2 WHERE price >= 100

Условие WHERE здесь содержит правило, по которому будут выбраны только товары с ценой равной или более 100, а те товары, цена у которых ниже 100, затронуты запросом не будут.

price = price / 2 – формула, по которой будет вычисляться новая цена товаров. Новая цена будет записана равной старой цене поделенной на два.

В результате выполнения такого запроса получим таблицу с измененными записями:

Обновление значений в нескольких полях строки

При необходимости обновлять сразу несколько полей, все поля с их значениями указываются после директивы SET через запятую. Например, нужно изменить название и цену товара с кодом 2 на «утюг», стоимостью 300:

UPDATE goods SET title = "утюг", price = 300 WHERE num = 2

Такой запрос каждому соответствующему полю в строке назначит его значение. А условие укажет, в какой именно строке будут выполнены изменения.

Выше приведены основные виды операций обновления. На их основе формируется запросы для решения большинства задач изменения данных в разработке с применением SQL.

Обновление данных в базах — один из ключевых элементов при работе в MySQL. Под обновлением подразумевается изменение значений в существующих записях таблицы. Обновление допускает различные варианты: как изменение значений полей в группе строк (в том числе и всех строк таблицы), так и корректировку значения поля отдельной строки.

Для полноценной работы как с локальными базами данных, так и с облачными базами данных timeweb.cloud, важно понимать синтаксис команд для обновления данных.

Ключевой командой для изменения записей в таблице базы данных в MySQL является UPDATE. Обновление происходит последовательно, от первой до последней строки, по порядку. В зависимости от типа обновления в MySQL выделяется два синтаксиса для оператора UPDATE. 

В случае обновления одной таблицы синтаксис выглядит следующим образом:

UPDATE LOW_PRIORITY IGNORE table_reference
    SET assignment_list
    WHERE where_condition
    ORDER BY ...
    LIMIT row_count

Обязательный параметр:

SET assignment_list — указывает, какие столбцы нужно изменить и каким образом (assignment_list — список столбцов и новых значений).

Необязательные параметры:

• LOW_PRIORITY — если указан этот параметр, выполнение UPDATE откладывается до тех пор, пока другой пользователь не решит просмотреть данные таблицы.
• IGNORE — в этом сценарии выполнение UPDATE не прерывается, даже если в процессе возникают какие-либо ошибки. Если возникают дубликаты в столбце с уникальным ключом, то такие строки не обновляются. 
• WHERE where_condition — широко известный оператор задает условия, по которым отбираются строки для обновления (where_condition — список условий). Если параметры WHERE не указаны, обновятся все строки таблицы.
• ORDER BY — если указан этот параметр, строки обновляются в заданном порядке.
• LIMIT row_count — ограничение на количество обновляемых строк (row_count — некое число строк). Считаются строки, которые попадают под условие WHERE, независимо от того, изменились они или нет. 

В случае обновления нескольких таблиц одновременно синтаксис будет следующим:

UPDATE LOW_PRIORITY IGNORE table_references
    SET assignment_list
    WHERE where_condition

table_references — список таблиц, которые мы хотим изменить. Они изменятся таким образом, как указано в assignment_list. 

При обновлении нескольких таблиц нельзя использовать параметр ORDER BY или LIMIT. Помимо этого, при обновлении нескольких таблиц нет гарантий, что они будут обновлены в указанном порядке.

Остальные параметры запроса (необязательные) — LOW_PRIORITY, IGNORE и WHERE — работают так же, как в сценарии обновления одной таблицы.

Итак, давайте рассмотрим примеры использования оператора UPDATE в MySQL.

Для начала создадим базу данных для книжного магазина. В ней будет 4 таблицы: книги, жанры, писатели и покупки. 

Воспользуемся операторами CREATE TABLE и INSERT INTO, чтобы создать таблицы author, genre, book и sales. 

CREATE TABLE author (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name_author VARCHAR(50) NOT NULL
    );
INSERT INTO author (name_author) 
VALUES ('Leo Tolstoy'), 
('Franz Kafka'), 
('Nikolai Gogol'),
('William Shakespeare'),
('Homer');
CREATE TABLE genre (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name_genre VARCHAR(30) NOT NULL
);
INSERT INTO genre (name_genre) 
VALUES ('Realist novel'),
('Dystopian novel'),
('Picaresque novel'),
('Epic poetry');
CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50), 
    author_id INT NOT NULL, 
    genre_id INT,
    price DECIMAL(8,2) NOT NULL, 
    amount INT DEFAULT 0, 
    FOREIGN KEY (author_id) REFERENCES author (id),
    FOREIGN KEY (genre_id) REFERENCES genre (id)
);
INSERT INTO book (title, author_id, genre_id, price, amount) 
VALUES ('Anna Karenina', 1,1, 650.00, 15),
('The Castle', 2,2, 570.20, 6),
('Dead Souls', 3,3, 480, 2),
('Iliad', 5,4, 518.99, 4),
('Odyssey', 5,4, 518.99, 7);
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    count INT NOT NULL,
    cost DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (book_id) REFERENCES book (book_id)
    );

 Получаем следующие таблицы. Таблица book:

Столбцы:

• book_id – уникальный идентификатор книги;
• title – название книги;
• author_id – идентификатор автора (внешний ключ);
• genre_id – идентификатор жанра (внешний ключ);
• price – цена книги за один экземпляр; 
• amount – количество книг на складе.

Таблица genres:

Столбцы: 

• id – уникальный идентификатор;
• name_genre – обозначение жанра.

Таблица author:

Столбцы:

• id – уникальный идентификатор;
• name_author – имя автора книги.

Таблица sales:

Столбцы:

• id – уникальный идентификатор операции;
• book_id – уникальный идентификатор книги из таблицы book (внешний ключ);
• count – количество купленных книг;
• cost – общая стоимость товаров.

Операции по обновлению данных

Теперь, создав образец базы данных, мы покажем выполнение различных операций по обновлению данных с использованием оператора UPDATE и других команд в MySQL. 

1. Обновление всех строк

Если при использовании UPDATE вы не используете задающий условия параметр WHERE, то будут обновлены все строки в таблице. Предположим, в книжном магазине проходит акция «Всё по 500» — изменим цену всех книг на фиксированную 500 рублей:

UPDATE book 
SET price=500;

В результате выполнения запроса мы получим такую табличку:

Если мы попробуем присвоить значение, которое уже находится в столбце, то MySQL заметит это и не обновит его.

Если мы захотим присвоить значение NULL столбцу, при создании которого было указано NOT NULL, то запрос вернет ошибку: 

Column 'name_column' cannot be null

В то же время, если указать параметр IGNORE, то значение будет изменено на значение по умолчанию для конкретного типа: 0 для числовых, “” для символьных и «нулевое» для дат. Например, 0000 для типа данных YEAR или 0000-00-00 00:00:00 для типа DATETIME.

2. Обновление строк с условием

Изменение всех строк таблицы требуется довольно редко. Гораздо чаще нам необходимо обновить значения для какой-то конкретной записи или для нескольких. Допустим, мы хотим изменить строки по какому-то условию. Например, устроим распродажу книг, которых осталось в наличии меньше 5 штук. Для этого в MySQL с командой UPDATE используем оператор WHERE:

UPDATE book 
SET price=300
WHERE amount < 5;

Результат выполнения запроса:

Хорошо видно, что изменились только строки с книгами Dead Souls и Iliad, так как только они удовлетворяли прописанному в запросе условию.

3. Обновление значений с выражением

При обновлении мы можем задавать столбцу не только статичное значение, но и выражения. Предположим, в магазине проходит акция, и на книги русских писателей объявлена скидка в 15%:

UPDATE book 
SET price=price * 0.85
WHERE author_id in (1,3);

В таблице author имеется только два русских писателя – Leo Tolstoy и Nikolai Gogol с author_id 1 и 3 соответственно.

Результат выполнения запроса:

Обновление значений происходит в определенном порядке: слева направо. Например, следующий запрос сначала увеличит значение amount на 1, а потом удвоит его:

UPDATE book 
SET amount = amount + 1, amount =amount*2;

Результат выполнения запроса:

4. Обновление с DEFAULT

Также мы можем изменить значение строк на значения «по умолчанию» DEFAULT, которые задаются при создании или изменении таблицы. Для того чтобы узнать, какие значения в нашей таблице используются по умолчанию, выполним запрос:

DESC book

В результате получим следующую структуру нашей таблицы в MySQL:

Заменим значения столбца amount на значение DEFAULT. Так как по умолчанию значение для amount было 0, мы должны получить все 0:

UPDATE book 
SET amount=DEFAULT;

Результат выполнения запроса соответствует ожиданиям:

5. Обновление нескольких столбцов

Используя один запрос, мы можем обновить сразу несколько столбцов. Например, изменим значения цены и количества у строк со значением book_id < 4:

UPDATE book 
SET price=price*0.9,
       amount = amount - 1
WHERE book_id < 4;

Результат выполнения запроса:

6. Использование LIMIT

Используя параметр LIMIT, мы можем ограничить количество записей, которые должны быть обновлены. Например, обновим только первую строку:

UPDATE book 
SET price=100
WHERE genre_id = 4
LIMIT 1;

В таблице имеется две строки с genre_id равным 4, но, так как мы указали LIMIT 1, обновится только одна:

Также следует отметить, что LIMIT N вовсе не означает, что мы обновим N строк. В результате запроса произойдёт обработка первых N строк, подходящих под условие WHERE, независимо от того, обновились эти строки в итоге или нет.

7. Обновление нескольких таблиц

В MySQL мы можем обновить сразу несколько таблиц:

UPDATE book, author 
SET amount=amount + 3, 
author.name_author = '-'
WHERE book.book_id = 4 and author.id = 4;

Результат запроса в таблице book:

Результат запроса в таблице authors:

8. Обновление таблиц с объединением (INNER JOIN)

Во время обновления можно также объединять таблицы при помощи команды INNER JOIN.

UPDATE book b INNER JOIN author a 
ON b.author_id = a.id
SET b.title = CONCAT(b.title, ' (', a.name_author,')');

Указывать INNER необязательно, так как этот тип объединения используется по умолчанию. Запрос можно переписать следующим образом, и мы получим такой же результат:

UPDATE book, author a 
SET b.title = CONCAT(b.title, ' (', a.name_author,')')
WHERE b.author_id = a.id;

9. Обновление таблиц с объединением (LEFT JOIN)

Также мы можем использовать LEFT JOIN. В этом случае обязательно указывать, что мы используем именно левое объединение. Например, можно изменить на складе количество книг после их покупки. Добавим в таблицу sales две строки:

INSERT INTO sales (book_id, count, cost) 
VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3), 
(3, 1, (SELECT price FROM book WHERE book_id = 3)*1);

Магазин продал 3 книги Anna Karenina и 1 книгу Dead Souls. Выполним запрос: 

UPDATE book LEFT JOIN sales on book.book_id = sales.book_id
SET amount = amount - count
WHERE sales.book_id is not NULL;

В итоге обновления видим, что количество книг на складе уменьшилось (для тех, которые мы продали):

Если мы попробуем не использовать LEFT JOIN, то получим ошибку «Out of range value for column ‘amount’ at row 3», т.к. amount не может быть отрицательным. Или, если добавить IGNORE, получим:

Как можно видеть в данном случае во всех строках количество уменьшилось на три книги, что нам не очень подходит.

10. Обновление с CASE, IF, IFNULL, COALESCE

При обновлении таблицы также возможно использовать условные операторы, такие как CASE, IF и т.д.

Функция CASE проверяет истинность набора условий и, в зависимости от результата, возвращает один из возможных результатов. Синтаксис при работе с UPDATE в MySQL для операторов CASE и WHEN будет следующий:

UPDATE book
SET price = 
  CASE genre_id
  WHEN 1 THEN 100
  WHEN 2 THEN 150
  ELSE price 
  END;

В данном случае, если книга имеет жанр 1 мы устанавливаем стоимость 100, если жанр 2 – стоимость 150.

Результат выполнения запроса:

Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Если книга имеет жанр 4, то мы уменьшаем ее стоимость на 200, иначе оставляем стоимость прежней:

UPDATE book
SET price = IF (genre_id = 4, price-200, price);

Результат выполнения запроса:

Функция IFNULL проверяет значение выражения – если оно имеет значение NULL, то возвращается определенное значение, в противном случае возвращается само выражение. Пусть одно из значений amount оказалось NULL:

Проверим все значения в столбце amount, и если встретится NULL, заменим его на 0: 

UPDATE book
SET amount = IFNULL(amount, 0);

Результат выполнения запроса:

Функция COALESCE довольна похожа на IFNULL. Основная особенность заключается в том, что данная функция может принимать сразу несколько значений (два и более). Как и IFNULL, возвращает первое не равное NULL.

Для того чтобы посмотреть, как работает этот вариант, создадим таблицу следующего вида:

И выполним запрос:

UPDATE test_table
SET col4 = COALESCE(col1, col2, col3, 'нет значения');

Результат выполнения запроса:

11. Обновление с сортировкой

Сортировка может помочь при обновлении поля с уникальным ключом. Если мы захотим сдвинуть наши id на 1, то, изменив первую строку, мы получим две строки, у которых id = 2, и возникнет ошибка. Но если добавить ORDER BY и начать обновлять с конца, то запрос успешно выполнится:

UPDATE book
SET book_id=book_id+1 

Результат выполнения запроса:

12. Обновление на основе данных из других таблиц

Также в MySQL при работе с UPDATE в условии WHERE возможно использовать вложенные команды SELECT и FROM. В рассматриваемом примере мы сначала получаем идентификатор жанра ‘Epic poetry’, а потом на основе полученного значения отбираем строки для обновления таблицы. 

UPDATE book
SET amount = 0
WHERE genre_id = (
   SELECT id
   FROM genre 
   Where name_genre = 'Epic poetry'
);

Как вариант, мы можем выбрать значения, которые нужно изменить, используя запрос: 

UPDATE book
SET price = (
SELECT MIN (cost)
FROM sales)
WHERE amount < 5;

Мы изменяем значения price всех книг, у которых количество на складе меньше 5, на минимальную сумму продажи. 

Минимальная сумма продаж у нас 480:

В этой ситуации невозможно обновить таблицу, выбрав значения из той же таблицы в подзапросе. Но есть вариант использовать небольшую хитрость – мы можем объединить таблицу с собой:

UPDATE book AS book_1
    INNER JOIN(
        SELECT genre_id, MIN(amount) AS min_amount
        FROM book
        GROUP BY genre_id
    ) AS book_2 ON book_1.genre_id = book_2.genre_id
SET book_1.amount = book_2.min_amount;

В данном случае подзапрос создает временную таблицу для присоединения и закрывает ее до того, как начнется выполнение UPDATE.

Подзапрос находит минимальное количество книг для каждого жанра, после чего используется для обновления столбца amount. В нашей таблице только у жанра 4 имеется больше одной строки. Значения в обеих строках должно замениться на минимальное для этого жанра – 4.

Результат выполнения запроса:

Есть еще один вариант обновления – использование SELECT FROM SELECT:

UPDATE book AS book_1
SET book_1.price = (SELECT MIN(price) AS min_price FROM (
SELECT price 
FROM book) as book_2);

В данном случае также создается временная таблица. Однако в этом случае присваивается только одно значение всем строкам.

Заключение

Мы постарались максимально подробно раскрыть особенности применения оператора UPDATE в MySQL. Но, конечно, практическое применение может продемонстрировать немало других интересных вариантов.

SELECT (Name, Age) FROM Clients WHERE Age > 20

Выбрать Имя и Возраст из Таблицы с клиентами, где Возраст больше 20

SELECT (Name, Age) FROM Clients

SELECT (Name, Age) FROM Clients

SELECT (Name, Age) FROM Clients WHERE Age > 20

SELECT (Name, Age) FROM Clients WHERE Age > 20 GROUP BY City

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000
ORDER BY Age

SELECT (Name, PaymentAmount, Age) 
FROM Clients 
WHERE Age > 20 
GROUP BY City 
HAVING PaymentAmount > 1000
ORDER BY Age DESC

CREATE DATABASE CatsCatsCats;

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

CREATE TABLE CatsAndOwners (
CatID int(6) NOT NULL,
CatName varchar(255) NOT NULL,
CatAge int(6) NOT NULL,
CatColor varchar(255) NOT NULL,
CatOwnerName varchar(255) NOT NULL
);

ALTER TABLE CatsAndOwners
ADD City varchar(255);

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(1, 'Мурка', 3, 'Чёрная', 'Дмитрий', 'Москва');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(2, 'Белла', 7, 'Белая', 'Максим', 'Саратов');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(3, 'Симба', 5, 'Рыжий', 'Екатерина', 'Санкт-Петербург');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(4, 'Лео', 2, 'Полосатый', 'Александр', 'Екатеринбург');

INSERT INTO CatsAndOwners(CatID, CatName, CatAge, CatColor, CatOwnerName, City) 
VALUES
(5, 'Мася', 1, 'Серый', 'Анна', 'Москва');

SELECT CatName, CatOwnerName
FROM CatsAndOwners;

SELECT *
FROM CatsAndOwners;

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge > 5

SELECT CatName
FROM CatsAndOwners
WHERE CatColor = 'Чёрная'
    AND City = 'Москва'

SELECT CatName
FROM CatsAndOwners
WHERE CatAge > 5
    OR CatColor = 'Чёрная'

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge BETWEEN 2 AND 6

SELECT CatName, CatAge
FROM CatsAndOwners
WHERE CatAge BETWEEN 2 AND 8
    AND (City = 'Саратов' OR City = 'Санкт-Петербург')
    OR CatName = 'Мурка'

SELECT CatName, CatAge
FROM CatsAndOwners
ORDER BY CatAge DESC

SELECT CatName, CatAge
FROM CatsAndOwners
ORDER BY CatAge

SELECT City, COUNT(*) AS CatCount 
FROM CatsAndOwners
GROUP BY City;

SELECT *
FROM CatsAndOwners
LIMIT 2;

UPDATE CatsAndOwners 
SET CatColor = 'Пурпурный'
WHERE CatName = 'Симба';

DELETE FROM CatsAndOwners 
WHERE City = 'Саратов';

ALTER TABLE CatsAndOwners 
DROP COLUMN CatOwnerName;

DROP TABLE CatsAndOwners;

SELECT SUM(CatAge) AS TotalAge
FROM CatsAndOwners;

SELECT MIN(CatAge) AS MinAge
FROM CatsAndOwners;

SELECT City, AVG(CatAge) AS AverageAge
FROM CatsAndOwners
GROUP BY City;

Научитесь: Профессия Data Scientist
Узнать больше