#3 — Добавление и обновление записей в БД
В ходе урока вы познакомитесь с операторами Insert, а также Update. Эти операторы позволяют добавить данные в табличку, а также обновить значения для определенных записей в таблице.
Видеоурок
Добавление данных
Добавление в SQL происходит при помощи команды Insert Into . В команде можно указать поля, а также значения, которые необходимо встроить в табличку.
Пример использования:
INSERT INTO users (name, age) VALUES('Алексей', 38);Также можно добавлять несколько записей, просто записывая их через запятую.
Пример:
INSERT INTO users (name, age) VALUES('Алексей', 38), ('Мартин', 12);Обновление данных
Обновление данных происходит при помощи команды Update . В ней необходимо указать условие Where , дабы обратиться лишь к тем записям, что вам нужны.
UPDATE users SET name = 'Иван' WHERE AND age = 44;Задание к уроку
Необходимо оформить подписку на проект, чтобы получить доступ ко всем домашним заданиям
Большое задание по курсу
Вам необходимо оформить подписку на сайте, чтобы иметь доступ ко всем большим заданиям. В задание входит методика решения, а также готовый проект с ответом к заданию.
PS: подобные задания доступны при подписке от 1 месяца
SQL UPDATE — обновление данных
Оператор SQL UPDATE предназначен для обновления (редактирования) данных в таблице. Он применяется, когда в той или иной строке таблицы уже записаны некоторые данные и нужно внести в них изменения. Оператор UPDATE имеет следующий синтаксис:
UPDATE ИМЯ_ТАБЛИЦЫ SET ИМЯ_СТОЛБЦА_1=ЗНАЧЕНИЕ, . ИМЯ_СТОЛБЦА_N=ЗНАЧЕНИЕ [ WHERE УСЛОВИЕ]
Квадратные скобки [], в которые заключена часть запроса WHERE УСЛОВИЕ, означает, что эта часть является необязательной.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
А скрипт для создания базы данных «Портал объявлений 1», её таблицы и заполения таблицы данных — в файле по этой ссылке .
Использование оператора SQL UPDATE вместе с секцией WHERE
Хотя часть запроса на обновление данных WHERE УСЛОВИЕ является необязательной, в большинстве случаев она применяется, так как обновить чаще требуется значения столбцов в определённых строках.
Пример 1. Есть база портала объявлений. В ней есть таблица Ads, содержащая данные о объявлениях, поданных за неделю (более подробно — в уроке об агрегатных функциях SQL, пример 7). Таблица выглядит так:
| Id | Category | Part | Units | Money |
| 1 | Транспорт | Автомашины | 110 | 17600 |
| 2 | Недвижимость | Квартиры | 89 | 18690 |
| 3 | Недвижимость | Дачи | 57 | 11970 |
| 4 | Транспорт | Мотоциклы | 131 | 20960 |
| 5 | Стройматериалы | Доски | 68 | 7140 |
| 6 | Электротехника | Телевизоры | 127 | 8255 |
| 7 | Электротехника | Холодильники | 137 | 8905 |
| 8 | Стройматериалы | Регипс | 112 | 11760 |
| 9 | Досуг | Книги | 96 | 6240 |
| 10 | Недвижимость | Дома | 47 | 9870 |
| 11 | Досуг | Музыка | 117 | 7605 |
| 12 | Досуг | Игры | 41 | 2665 |
Требуется изменить значения столбцов Units и Money в строке с Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Units=148, Money=23680 WHERE align=»justify»>После выполнения этого запроса соответствующая строка будет содержать следующие данные:
| 4 | Транспорт | Мотоциклы | 148 | 23680 |
Запросом на обновление данных с использованием оператора SQL UPDATE и секции WHERE можно изменить значения столбцов и в нескольких строках, которые соответствуют условию, указанному в секции WHERE.
Пример 2. База данных и таблица — те же, что и в примере 1. Требуется поменять название категории «Недвижимость» на «Постройки». Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Category=’Постройки’ WHERE Category=’Недвижимость’
В результате действия этого запроса изменится значение столбца Category во второй, третьей и десятой строках таблицы.
Использование оператора SQL UPDATE и вычисляемые значения
В запросах на обновление данных с использованием оператора SQL UPDATE можно путём задания вычислений менять значения, имеющие числовой формат. Соответствующие запросы могут быть с или без секции WHERE.
Пример 3. База данных и таблица — те же, что и в предыдущих примерах.
Теперь предположим, что во время заполнения таблицы данными изменились расценки на объявления, публикуемые на портале. Требуется увеличить значения столбца Money в 2 раза во всех строках таблицы. Пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Money = Money*2
Использование оператора SQL UPDATE без секции WHERE
Пример 4. База данных и таблица — те же, что и в предыдущих примерах. Требуется сделать неопределёнными (NULL) значения столбцов Units и Money во всех строках таблицы. Запрос для такого обновления данных будет следующим (на MS SQL Server — с предваряющей конструкцией USE adportal1;):
UPDATE ADS SET Units= NULL , Money= NULL
В результате действия этого запроса столбцы Units и Money примут значение NULL во всех строках таблицы.
Примеры запросов к базе данных «Портал объявлений-1» есть также в уроках об операторах INSERT, DELETE, HAVING и UNION.
Обновление или исправление реплицируемых баз данных
SQL Server поддерживает обновление реплика баз данных из предыдущих версий SQL Server. При обновлении узла не требуется останавливать действия на других узлах. Соблюдайте следующие правила, определяющие допустимые версии объектов репликации.
- Версия распространителя не должна быть ниже версии издателя (во многих случаях распространителем и издателем является один и тот же экземпляр).
- Версия издателя не должна превышать версию распространителя.
- Версия подписчика зависит от типа публикации:
- Подписчик на публикацию транзакций может иметь любую версию, отличающуюся от версии издателя, но не более, чем на две версии. Например: издатель SQL Server 2012 (11.x) может иметь подписчиков SQL Server 2014 (12.x) и SQL Server 2016 (13.x), а издатель SQL Server 2016 (13.x) — подписчиков SQL Server 2014 (12.x) и SQL Server 2012 (11.x).
- Подписчик на публикацию слиянием может иметь любую версию, которая не превышает версию издателя и поддерживается в соответствии с циклом поддержки жизненного цикла версий.
Путь обновления SQL Server зависит от схемы развертывания. Для SQL Server, как правило, предлагается два пути обновления:
- Параллельное использование. Развертывание параллельной среды и перемещение баз данных вместе со связанными объектами уровня экземпляров, такими как учетные данные и задания, в новую среду.
- Обновление на месте. Установочный носитель SQL Server может обновить существующую установку SQL Server, заменив элементы SQL Server и обновив объекты баз данных. В средах с группами доступности Always On или экземплярами отказоустойчивых кластеров обновление на месте сочетается с последовательным обновлением для минимизации простоя.
Общий подход, который используется для обновления топологий репликации в параллельном режиме, подразумевает перемещение пар «издатель-подписчик» в новую параллельную среду по частям вместо перемещения всей топологии. Этот поэтапный подход помогает контролировать время простоя и до определенной степени минимизировать вмешательство в зависимую от репликации бизнес-деятельность.
Основная часть этой статьи относится к обновлению версии SQL Server. Тем не менее процесс обновления на месте также должен использоваться при применении исправлений SQL Server из пакета обновления или накопительного пакета обновления.
Обновление топологии репликации включает несколько этапов. Рекомендуем выполнить пробное обновление реплики топологии репликации в тестовой среде, прежде чем делать это в реальной рабочей среде. Это поможет подготовить рабочую документацию, которая позволит эффективно провести фактическое обновление, избежав длительных и затратных простоев. Нам известны примеры, когда клиентам удалось существенно сократить время простоев с использованием групп доступности Always On или экземпляров отказоустойчивого кластера SQL Server. Кроме того, мы рекомендуем создать резервные копии всех баз данных (включая MSDB, master, базы данных распространителя и пользовательские базы), участвующих в репликации перед попыткой обновления.
При наличии базы данных распространителя в экземпляре отказоустойчивого кластера все участвующие узлы должны использовать одну и ту же сборку. Мы не рекомендуем настроить, в котором один узел является версией SQL Server более ранней, чем SQL Server 2016 с пакетом обновления 2 (SP2)3 или SQL Server 2017 CU6, а другой — версией SQL Server более поздней, чем SQL Server 2016 с пакетом обновления 2 (SP2) или SQL Server 2017 CU6. В версиях SQL Server 2016 с пакетом обновления 2 (SP2) и накопительным пакетом обновления 3 (CU3), а также SQL Server 2017 c накопительным пакетом обновления 6 (CU6) добавлена поддержка использования базы данных распространителя в группе доступности Always On и поддержка новых объектов (таблиц, хранимых процедур) в базах данных распространителя. Если база данных распространителя находится в экземпляре отказоустойчивого кластера и выполняется поэтапная миграция (и вы не можете обновить все узлы до одной и той же версии SQL Server), то в течение короткого периода миграции рекомендуется выполнять действия с учетной записью, например добавление новых подписчиков, подписок, издателей или публикаций, на узле с более поздней версией SQL Server.
Матрица репликации
Матрица совместимости транзакций и моментальных снимков реплика
Издатель Распространитель Подписчик SQL Server 2022 SQL Server 2022 SQL Server 2022
SQL Server 2019
SQL Server 2017SQL Server 2019 SQL Server 2022
SQL Server 2019SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016SQL Server 2017 SQL Server 2022
SQL Server 2019
SQL Server 2017SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014SQL Server 2016 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012SQL Server 2014 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2012 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2008 R2
SQL Server 2008SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008Матрица совместимости реплика слиянием
Издатель Распространитель Подписчик SQL Server 2022 SQL Server 2022 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2019 SQL Server 2022
SQL Server 2019SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2017 SQL Server 2022
SQL Server 2019
SQL Server 2017SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2016 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2014 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2012 SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2008 R2
SQL Server 2008SQL Server 2022
SQL Server 2019
SQL Server 2017
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008SQL Server 2008 R2
SQL Server 2008Запуск агента чтения журнала для репликации транзакций перед обновлением
Перед обновлением SQL Server необходимо убедиться, что все зафиксированные транзакции из опубликованных таблиц были обработаны агентом чтения журналов. Чтобы это обеспечить, для каждой базы данных, содержащей публикации транзакций, необходимо выполнить следующие шаги.
- Убедитесь, что в базе данных запущен агент чтения журнала. По умолчанию агент работает постоянно.
- Остановите активность пользователей в опубликованных таблицах.
- Подождите, пока агент чтения журнала не скопирует транзакции в базу данных распространителя, затем остановите агент.
- Чтобы убедиться, что все транзакции были обработаны, выполните процедуру sp_replcmds . Результирующий набор от этой процедуры должен быть пустым.
- Чтобы закрыть соединение процедуры sp_replcmds, выполните процедуру sp_replflush .
- Обновите сервер до последней версии SQL Server.
- Перезапустите агент SQL Server и агент чтения журналов, если они не запускаются автоматически после обновления.
Выполнение агентов для репликации слиянием после обновления
После обновления запустите агент моментальных снимков для каждой публикации слиянием и агент слияния для каждой подписки, чтобы обновить метаданные репликации. Применять новый моментальный снимок не требуется, потому что заново инициализировать подписки не нужно. Метаданные подписки обновляются при первом запуске агента слияния после обновления. Это означает, что база данных подписки может оставаться в сети в активном режиме во время обновления издателя.
Механизм репликации слиянием хранит метаданные публикации и подписки в нескольких системных таблицах баз данных публикации и подписки. При запуске агента моментальных снимков обновляются метаданные публикации, а при запуске агента слияния — метаданные подписки. Пользователь должен только сформировать моментальный снимок публикации. Если при публикации слиянием используются параметризованные фильтры, каждой секции также соответствует моментальный снимок. Обновлять эти секционированные снимки не требуется.
Запустите агенты из СРЕДЫ SQL Server Management Studio, монитора репликации или из командной строки. Дополнительные сведения о выполнении агента моментальных снимков см. в следующих статьях:
- Создание и применение исходного моментального снимка
- Запуск и остановка агента репликации (среда SQL Server Management Studio)
- Создание и применение исходного моментального снимка
- Replication Agent Executables Concepts
Дополнительные сведения о выполнении агента слияния см. в следующих статьях:
- Синхронизация подписки по запросу
- Синхронизация принудительной подписки
После обновления SQL Server в топологии, которая использует слияние реплика tion, измените уровень совместимости публикации любых публикаций, если вы хотите использовать новые функции.
Обновление до выпусков Standard Edition, Workgroup Edition и Express Edition
Перед обновлением одного выпуска SQL Server до другого убедитесь, что в выпуске, в котором выполняется обновление, поддерживается ли функция. Дополнительные сведения см. в разделе о репликации в выпусках и поддерживаемых функциях SQL Server 2022.
Процедура обновления топологии репликации
Эта процедура представляет рекомендуемый порядок обновления серверов в топологии репликации. Она также применима к репликации транзакций или репликации слиянием. При этом она не относится к одноранговой репликации, а также к поочередному или немедленному обновлению подписок.
Обновление «на месте»
- Обновите распространителя.
- Обновите издателя и подписчика. Эти элементы можно обновлять в любом порядке.
Для SQL 2008 и 2008 R2 обновление издателя и подписчика должно выполняться одновременно для соответствия матрице топологии репликации. Издатель и подписчик SQL 2008 или 2008 R2 не могут иметь издателя или подписчика SQL 2016 (или более поздней версии). Если одновременное обновление невозможно, используйте промежуточное обновление, чтобы сначала перевести экземпляры SQL на SQL 2014, а затем обновить их еще раз до SQL 2016 (или более поздней версии).
Параллельное обновление
- Обновите распространителя.
- Перенастройте распространение на новом экземпляре SQL Server.
- Обновите издателя.
- Обновите подписчика.
- Перенастройте все пары «издатель-подписчик», включая повторную инициализацию подписчика.
Процедура параллельной миграции распространителя в Windows Server 2012 R2
Если вы планируете перевести экземпляр SQL Server на SQL Server 2016 (или более поздней версии) и текущей ОС является Windows 2008 (или 2008 R2), потребуется параллельное обновление операционной системы до Windows Server R2 или более поздней версии. Это необходимо, так как SQL Server 2016 нельзя установить в Windows Server 2008/2008 R2 и эти ОС не поддерживают обновление на месте непосредственно в Windows Server 2016. Хотя можно выполнить обновление на месте с Windows Server 2008/2008 R2 до Windows Server 2012, а затем до Windows Server 2016. Как правило, делать это не рекомендуется из-за простоя и дополнительной сложности, препятствующей простому пути отката. Параллельное обновление — это единственный доступный путь обновления для экземпляров SQL Server, участвующих в отказоустойчивом кластере. Следующие действия можно выполнить на автономном экземпляре SQL Server или внутри экземпляра отказоустойчивого кластера (FCI) Always On.
- Настройте новый экземпляр SQL Server (автономный или отказоустойчивый кластер Always On), выпуск и версию в качестве распространителя в Windows Server 2012 R2/2016, используя другое имя кластера Windows и FCI-экземпляра SQL Server либо имя автономного узла. Необходимо сохранить прежнюю структуру каталогов распространителя, чтобы исполняемые файлы агентов репликации, папки репликации и файлы базы данных находились по тому же пути в новой среде. Это упростит обновление и устранение проблем после миграции.
- Синхронизируйте репликацию, а затем завершите работу всех агентов репликации.
- Завершите работу текущего экземпляра распространителя SQL Server. Если это автономный экземпляр, завершите работу сервера. Если это экземпляр отказоустойчивого кластера SQL, отключите всю роль SQL Server в диспетчере кластеров, включая имя сети.
- Удалите записи объектов компьютеров DNS и AD для старой среды (текущего экземпляра распространителя).
- Измените имя узла нового сервера, чтобы оно соответствовало старому серверу.
- Если это экземпляр отказоустойчивого кластера SQL, задайте для нового экземпляра то же имя виртуального сервера, что и у старого.
Чтобы уменьшить простой, рекомендуем выполнять параллельную миграцию и обновление на месте до SQL Server 2016 раздельно. Это позволит реализовать поэтапный переход, сократить риски и минимизировать время простоя.
Веб-синхронизация для репликации слиянием
Параметр веб-синхронизации для слияния реплика tion требует, чтобы прослушиватель Репликация SQL Server (replisapi.dll) копировался в виртуальный каталог на сервере службы IIS (IIS), используемом для синхронизации. При настройке веб-синхронизации этот файл копируется в виртуальный каталог мастером настройки веб-синхронизации. При обновлении компонентов SQL Server, установленных на сервере IIS, необходимо вручную скопировать replisapi.dll из com-каталога в виртуальный каталог на сервере IIS. Дополнительные сведения о конфигурации см. в статье Настройка веб-синхронизации.
Восстановление из копии реплицированной базы данных из предыдущей версии
Чтобы обеспечить неизменность параметров репликации при восстановлении реплицированной базы данных, имеющей более раннюю версию, выполните восстановление на сервер и в базу данных, имеющих те же имена, что и у сервера или базы данных, для которых была сделана резервная копия.
Работа с командой UPDATE — как обновить данные в таблице MySQL
9 подробных примеров о том, как обновить данные в MySQL с помощью UPDATE.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
В этой статье мы расскажем, для чего нужна команда UPDATE в MySQL и покажем как ей пользоваться на нескольких примерах.
UPDATE: что это за команда и зачем она нужна
UPDATE — это команда, которая обновляет данные в таблице. Ее общий синтаксис такой:
UPDATE [table] table_name SET column1 = value1, column2 = value2, . [WHERE condition] [ORDER BY expression [ ASC | DESC ]] [LIMIT number_rows];Сначала мы указываем обязательные параметры: название таблицы, названия колонок и нужные значения для обновления. Обратите внимание, что в MySQL можно использовать ключевое слово table (update table), а можно его опустить и сразу указать название таблицы.
Затем идут необязательные блоки WHERE (условие обновления), ORDER BY (сортировка) и LIMIT (ограничение количества обновляемых записей).
Создание облачной базы данных MySQL
Для начала нам понадобится сервер MySQL. На примере платформы Selectel мы покажем, как создать базу данных и подключиться к ней. Мы создадим управляемую БД — это позволит быстро создать сервер, и не заниматься его настройкой.
Управляемая база данных в облаке
Если у вас уже есть развернутый сервер MySQL, можете сразу переходить к следующему разделу.
Итак, в панели управления заходим в раздел «Облачная платформа» — «Базы данных», нажимаем кнопку «Создать кластер».
На следующем экране выбираем параметры создаваемой БД. Выбираем «СУБД» — MySQL. Далее выбираем конфигурацию сервера, нам будет достаточно 2 vCPU, 4 ГБ оперативной памяти и 32 ГБ диска. Важно выбрать публичную сеть, чтобы к серверу можно было подключаться из интернета.
Ждем несколько минут, пока сервер создается. После того, как он перейдет в статус ACTIVE, можно продолжать настройку.
Нажимаем на имя кластера и переходим на вкладку «Пользователи». Тут нужно создать пользователя для доступа к БД. Запишите его имя и пароль, они понадобятся для подключения.
Затем переходим на вкладку «Базы данных», тут нужно создать БД (схему), в которой мы будем работать.
Когда БД создастся, нужно дать к ней доступ пользователю, которого мы создали на предыдущем шаге. Для этого нажмите на название БД и выберите пользователя.
Затем перейдите на вкладку «Настройки» и запишите DNS- или IP-адрес сервера, он нужен для подключения.
Все, сервер MySQL готов к работе, подключитесь к нему. В рамках этой статьи мы ограничимся обычным консольным клиентом mysql. Но вы можете использовать любые другие способы, которые позволяют писать SQL-команды. Например, в нашем блоге есть статья про инструмент MySQL Workbench.
Структура таблиц
Мы будем рассматривать работу с командой UPDATE на примере очень простой схемы БД. Представим, что мы — сеть магазинов бытовой техники. У нас есть 3 таблицы:
- categories: таблица категорий товаров. В ней хранятся только идентификаторы и названия категорий.
+----+------------------+ | id | name | +----+------------------+ | 1 | Ноутбуки | | 2 | Планшеты | | 3 | Телефоны | +----+------------------+- stores: таблица магазинов. В ней хранятся идентификаторы, названия магазинов, город и адрес.
+----+------------------+---------------------+----------------------+ | id | name | city | address | +----+------------------+---------------------+----------------------+ | 1 | Магазин 1 | Санкт-Петербург | Цветочная 63 | | 2 | Магазин 2 | Санкт-Петербург | Советская 20 | | 3 | Магазин 3 | Москва | Берзарина 42 | | 4 | Магазин 4 | Москва | Авиамоторная 96 | +----+------------------+---------------------+----------------------+- products: таблица товаров. В ней хранятся идентификаторы, названия товаров, ссылки на категорию товара и магазин, цена товара и его количество.
+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 50000 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 79000 | 2 | | 3 | Планшет 1 | 2 | 2 | 8000 | 5 | | 4 | Планшет 2 | 2 | 4 | 12000 | 3 | | 5 | Телефон 1 | 3 | 1 | 18000 | 5 | | 6 | Телефон 2 | 3 | 2 | 25000 | 1 | | 7 | Телефон 3 | 3 | 3 | 78000 | 1 | +----+------------------+-------------+----------+-------+----------+Мы не будем показывать, как создать таблицы, это выходит за рамки статьи. Мы писали отдельную статью про работу с таблицами — почитайте, если вы еще не умеете создавать их.
Пример 1: обновление одной строки
Допустим, мы хотим изменить стоимость одного конкретного товара. Для этого указываем имя MySQL таблицы (update products), название обновляемого поля и значение (set price = 50500), а также условие, какую именно стоку нужно обновить (where >
UPDATE products SET price = 50500 WHERE >Результат:
+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 50500 | 1 | +----+------------------+-------------+----------+-------+----------+Теперь покажем, как обновить сразу несколько полей в одной строке. Например, у товара одновременно изменилась стоимость и количество. Вместо того, чтобы писать два отдельных запроса, мы укажем сразу оба изменяемых поля и их значения:
UPDATE products SET price = 78500, quantity = 3 WHERE >Результат:
+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 2 | Ноутбук 2 | 1 | 3 | 78500 | 3 | +----+------------------+-------------+----------+-------+----------+Пример 2: обновление всех строк в таблице
Также можно обновить сразу несколько строк в одной таблице, или даже все. Например, мы хотим выровнять количество всех товаров во всех магазинах, и установить его равным трем. Для этого нам нужно просто опустить блок WHERE, и тогда оператор UPDATE применит все, что указано в блоке SET сразу ко всем строкам в таблице:
UPDATE products SET quantity = 3;+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 50500 | 3 | | 2 | Ноутбук 2 | 1 | 3 | 78500 | 3 | | 3 | Планшет 1 | 2 | 2 | 8000 | 3 | | 4 | Планшет 2 | 2 | 4 | 12000 | 3 | | 5 | Телефон 1 | 3 | 1 | 18000 | 3 | | 6 | Телефон 2 | 3 | 2 | 25000 | 3 | | 7 | Телефон 3 | 3 | 3 | 78000 | 3 | +----+------------------+-------------+----------+-------+----------+Пример 3: обновление с выражением
Вместо конкретного значения можно использовать выражение, на основании которого будет вычисляться значение. Например, в магазинах проходит акция, и нужно снизить цены всех ноутбуков на 10%. Рассчитывать значение вручную для каждого товара неудобно, поэтому мы используем выражение. Укажем, что цену нужно умножить на 0.9, то есть сделать ее равной 90% от начальной:
UPDATE products SET price = (price * 0.9);+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 45450 | 3 | | 2 | Ноутбук 2 | 1 | 3 | 70650 | 3 | | 3 | Планшет 1 | 2 | 2 | 7200 | 3 | | 4 | Планшет 2 | 2 | 4 | 10800 | 3 | | 5 | Телефон 1 | 3 | 1 | 16200 | 3 | | 6 | Телефон 2 | 3 | 2 | 22500 | 3 | | 7 | Телефон 3 | 3 | 3 | 70200 | 3 | +----+------------------+-------------+----------+-------+----------+Пример 4: обновление на основе данных из других таблиц
В условии WHERE можно использовать данные, полученные из других таблиц. Расширим предыдущий пример с выражением. В этот раз акция проходит только в магазине с названием «Магазин 2». Для этого нужно в условии WHERE указать идентификатор магазина, для которого нужно обновить цены. Но вместо того, чтобы искать в таблице идентификатор и потом подставлять его в SQL-запрос, мы можем указать название, а идентификатор подставится сам.
Мы используем вложенный оператор SELECT, который сначала вернет идентификатор нужного нам магазина, а затем подставит его в блок WHERE:
UPDATE products SET price = (price * 0.9) WHERE store_id = ( SELECT id FROM stores WHERE name = 'Магазин 2' );+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 45450 | 3 | | 2 | Ноутбук 2 | 1 | 3 | 70650 | 3 | | 3 | Планшет 1 | 2 | 2 | 6480 | 3 | | 4 | Планшет 2 | 2 | 4 | 10800 | 3 | | 5 | Телефон 1 | 3 | 1 | 16200 | 3 | | 6 | Телефон 2 | 3 | 2 | 20250 | 3 | | 7 | Телефон 3 | 3 | 3 | 70200 | 3 | +----+------------------+-------------+----------+-------+----------+То же самое можно сделать с помощью оператора JOIN:
UPDATE products JOIN stores ON stores.id = store_id SET price = (price * 0.9) where stores.name = 'Магазин 2';Пример 5: ключевое слово DEFAULT
В блоке SET вместо указания конкретного значения можно использовать ключевое слово DEFAULT, которое установит для этого столбца значение по умолчанию. Если для столбца не указано значение по умолчанию, тогда значение будет равно NULL.
В нашей таблице products у поля quantity есть значение по умолчанию, оно равно 1. Поэтому когда мы выполним следующую команду, количество всех товаров станет равным единице:
UPDATE products SET quantity = DEFAULT;+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 45450 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 70650 | 1 | | 3 | Планшет 1 | 2 | 2 | 6480 | 1 | | 4 | Планшет 2 | 2 | 4 | 10800 | 1 | | 5 | Телефон 1 | 3 | 1 | 16200 | 1 | | 6 | Телефон 2 | 3 | 2 | 20250 | 1 | | 7 | Телефон 3 | 3 | 3 | 70200 | 1 | +----+------------------+-------------+----------+-------+----------+Пример 6: обновление значений в нескольких таблицах
Команда UPDATE может обновить значения сразу в нескольких таблицах за один раз. Допустим, мы хотим обновить адрес одного из магазинов, и тут же обновить количество товара в нем:
UPDATE stores, products SET stores.address = 'Пятницкая 23', products.quantity = 3 WHERE stores.id = 4 and products.store_id = 4;Результат, таблица stores:
+----+------------------+---------------------+----------------------+ | id | name | city | address | +----+------------------+---------------------+----------------------+ | 1 | Магазин 1 | Санкт-Петербург | Цветочная 63 | | 2 | Магазин 2 | Санкт-Петербург | Советская 20 | | 3 | Магазин 3 | Москва | Берзарина 42 | | 4 | Магазин 4 | Москва | Пятницкая 23 | +----+------------------+---------------------+----------------------+Результат, таблица products:
+----+------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 | 1 | 1 | 45450 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 70650 | 1 | | 3 | Планшет 1 | 2 | 2 | 6480 | 1 | | 4 | Планшет 2 | 2 | 4 | 10800 | 3 | | 5 | Телефон 1 | 3 | 1 | 16200 | 1 | | 6 | Телефон 2 | 3 | 2 | 20250 | 1 | | 7 | Телефон 3 | 3 | 3 | 70200 | 1 | +----+------------------+-------------+----------+-------+----------+Пример 7: ограничение количества обновляемых строк
Ключевое слово LIMIT позволяет ограничить количество обновляемых строк, не смотря на то, сколько на самом деле строк подошло под условие WHERE. Например, этот запрос дописать к названиям всех товаров примечание «(2020 год)». Но так как мы указали ограничение LIMIT 1, то обновится только одна строка:
UPDATE products SET name = CONCAT(name, ' (2020 год)') LIMIT 1;+----+-----------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+-----------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 (2020 год) | 1 | 1 | 45450 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 70650 | 1 | | 3 | Планшет 1 | 2 | 2 | 6480 | 1 | | 4 | Планшет 2 | 2 | 4 | 10800 | 3 | | 5 | Телефон 1 | 3 | 1 | 16200 | 1 | | 6 | Телефон 2 | 3 | 2 | 20250 | 1 | | 7 | Телефон 3 | 3 | 3 | 70200 | 1 | +----+-----------------------+-------------+----------+-------+----------+Пример 8: условные операторы IF и CASE
Иногда значение в блоке SET может быть не явным, а зависеть от какого-либо условия. Например, мы хотим уменьшить цены на все ноутбуки на 100р. Мы уже знаем, как это можно сделать с помощью условия WHERE. А теперь покажем, как то же самое можно сделать с помощью условного оператора IF:
UPDATE products SET price = IF(category_id=1, price-100, price);+----+-----------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+-----------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 (2020 год) | 1 | 1 | 45350 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 70550 | 1 | | 3 | Планшет 1 | 2 | 2 | 6480 | 1 | | 4 | Планшет 2 | 2 | 4 | 10800 | 3 | | 5 | Телефон 1 | 3 | 1 | 16200 | 1 | | 6 | Телефон 2 | 3 | 2 | 20250 | 1 | | 7 | Телефон 3 | 3 | 3 | 70200 | 1 | +----+-----------------------+-------------+----------+-------+----------+Усложним пример. Теперь нам нужно уменьшить цены на ноутбуки на 100р, на планшеты поднять на 100р, а телефоны — уменьшить на 5%. Для этого лучше подойдет другой условный оператор — CASE. В нем мы можем перечислить сразу несколько условий:
UPDATE products SET price = CASE WHEN category_id = 1 THEN price-100 WHEN category_id = 2 THEN price+100 WHEN category_id = 3 THEN price*0.95 END;+----+-----------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+-----------------------+-------------+----------+-------+----------+ | 1 | Ноутбук 1 (2020 год) | 1 | 1 | 45250 | 1 | | 2 | Ноутбук 2 | 1 | 3 | 70450 | 1 | | 3 | Планшет 1 | 2 | 2 | 6580 | 1 | | 4 | Планшет 2 | 2 | 4 | 10900 | 3 | | 5 | Телефон 1 | 3 | 1 | 15390 | 1 | | 6 | Телефон 2 | 3 | 2 | 19238 | 1 | | 7 | Телефон 3 | 3 | 3 | 66690 | 1 | +----+-----------------------+-------------+----------+-------+----------+Пример 9: сортировка ORDER BY
В MySQL сортировка ORDER BY обычно используется в операторе SELECT, но также ее можно использовать и в UPDATE. В этом случае записи будут обновляться в указанном порядке. Вот одна из ситуаций, когда это может пригодиться.
Допустим, мы хотим обновить идентификаторы всех товаров — увеличить на единицу. Для этого мы напишем такую команду:
UPDATE products SET >Но СУБД вернет ошибку: Duplicate entry ’2′ for key ’products.PRIMARY’. Так происходит потому, что операция UPDATE начинает обрабатывать строки последовательно, начиная с первой. И получается, что она пытается обновить идентификатор в первой строке с 1 на 2. Но сейчас в БД уже есть запись с ключом = 2, поэтому появляется ошибка. Чтобы этого избежать, мы можем использовать ORDER BY и начать обновлять таблицу с конца:
UPDATE products SET BY id DESC;+----+-----------------------+-------------+----------+-------+----------+ | id | name | category_id | store_id | price | quantity | +----+-----------------------+-------------+----------+-------+----------+ | 2 | Ноутбук 1 (2020 год) | 1 | 1 | 45250 | 1 | | 3 | Ноутбук 2 | 1 | 3 | 70450 | 1 | | 4 | Планшет 1 | 2 | 2 | 6580 | 1 | | 5 | Планшет 2 | 2 | 4 | 10900 | 3 | | 6 | Телефон 1 | 3 | 1 | 15390 | 1 | | 7 | Телефон 2 | 3 | 2 | 19238 | 1 | | 8 | Телефон 3 | 3 | 3 | 66690 | 1 | +----+-----------------------+-------------+----------+-------+----------+Заключение
Мы познакомились с командой UPDATE, рассмотрели различные варианты ее использования и подкрепили знания на примерах. Вы узнали, как с помощью SQL запроса обновить данные в таблице. Теперь вы сможете на основе этих примеров писать команды для своей структуры таблиц.
Insert в MySQL — добавление данных в таблицу