Изменение значений полей в таблице
UPDATE заменит значения текущих полей таблицы на новые значения.
SET устанавливает какие поля изменять и новые значения, которые нужно присвоить этим полям.
WHERE (если необходимо) — условие на изменение определенных записей. Если WHERE не указан, изменены будут все записи.
При указании параметра LOW_PRIORITY, выполнение UPDATE задержится пока другие клиенты читают таблицу.
Пример запроса на изменение всех записей поля «apple» таблицы «fruit» на определенное значение
UPDATE fruit SET apple=5;
Пример запроса с WHERE который изменяет определенную запись:
UPDATE tovar SET price=500 WHERE >При изменении значения поля можно использовать его текущее значение.
Пример запроса увеличивающего значение поля price в 2 раза:
UPDATE tovar SET price=price*2;
SET в UPDATE вычисляет выражения слева направо.
Пример запроса который удваивает цену (поле price), а потом уменьшает его на 10:
UPDATE tovar SET price=price*2, price=price-10;
Запрос UPDATE возвратит количество полей, которые были изменены в этом запросе.
Использование LIMIT позволит изменять заданное количество записей.
Руководство по SQL. Изменение данных.
Для изменения уже существующих данных мы используем SQL запрос UPDATE.
Для того, чтобы указать, какие именно данные необходимо изменить, мы должны использовать условный оператор WHERE.
Данный запрос имеет следующий вид:
UPDATE имя_таблицы SET колонка1 = значение1, колонка2 = значение2, колонкаN = значениеN WHERE [условие1];
Мы также можем использовать операторы AND/OR для уточнения условий.
Предположим, что у нас есть таблица developers, которая содержит следующие данные:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2000 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 2 | 2100 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+
Допустим, что нам необходимо изменить зарплату разработчика с именем Eugene Suleimanov. Для этого мы должны использовать следующую команду:
mysql> UPDATE developers SET SALARY = 2500 WHERE NAME LIKE 'Eugene Suleimanov';
В результате наша запись будет изменена и наша таблица будет содержать уже такие данные:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 2 | 2100 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+
Если мы хотим изменить несколько полей, например, опыт и зарплату, то наш запрос будет иметь следующий вид:
mysql> UPDATE developers SET EXPERIENCE = 3, SALARY = 2500 WHERE NAME LIKE 'Andrei Komarov';
В результате выполнения данного запроса, наша таблица developers будет иметь следующий вид:
+----+-------------------+------------+------------+--------+ | ID | NAME | SPECIALTY | EXPERIENCE | SALARY | +----+-------------------+------------+------------+--------+ | 1 | Eugene Suleimanov | Java | 2 | 2500 | | 2 | Peter Romanenko | Java | 3 | 3500 | | 3 | Andrei Komarov | JavaScript | 3 | 2500 | | 4 | Konstantin Geiko | C# | 2 | 2000 | | 5 | Asya Suleimanova | UI/UX | 2 | 1800 | +----+-------------------+------------+------------+--------+
На этом мы заканчиваем изучение способов изменения существующих данных.
В следующей статье мы рассмотрим процесс удаления записей.
ALTER TABLE — изменение таблицы в SQL
Рассказываем о команде ALTER TABLE и учим вносить с ее помощью изменения в таблицы и столбцы.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
ALTER TABLE — один из самых незаменимых инструментов в работе с базами данных SQL. В этой статье мы рассмотрим SQL оператор ALTER TABLE и его применение. Узнаем, как добавить или удалить поля с помощью этого инструмента, и рассмотрим различные примеры его использования. В данной статье мы не будем рассматривать MS SQL и остановимся на синтаксисе наиболее популярной версии — MySQL.
Синтаксис оператора ALTER TABLE в SQL
Синтаксис оператора ALTER TABLE выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
Из этой записи мы видим, что сценариев применения данной команды достаточно много. Давайте рассмотрим их. В качестве примера мы будем использовать базу данных slcbookshelf, которую мы создавали в статье о первичных и внешних SQL ключах.
use slcbookshelf; Добавление столбца в таблицу (ADD COLUMN)
Сейчас наша таблица выглядит следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Давайте добавим в нашу таблицу новый столбец, в котором будет отображаться автор каждой книги:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL; Данным запросом мы создали в нашей таблице новый столбец authors с типом NVARCHAR и длиной в 50 символов, который не может принимать пустое значение. Если мы не знаем автора произведения, тогда наша команда будет иметь такой вид:
ALTER TABLE books ADD author NVARCHAR(50) NOT NULL DEFAULT 'Неизвестен'; Теперь для существующих данных, для которых не заполнен столбец author, значение по умолчанию будет «Неизвестен».
Переименование столбца и таблицы
Переименование столбца (RENAME)
С помощью ALTER TABLE можно переименовать существующий столбец. Для этого выполните команду:
ALTER TABLE books RENAME COLUMN author TO authors; Переименование таблицы (RENAME)
При помощи ALTER TABLE можно переименовать таблицу. Выполняем запрос:
ALTER TABLE books RENAME TO books_selectel; Удаление столбца (DROP)
Чтобы удалить столбец из таблицы с помощью ALTER TABLE, требуется выполнить следующий запрос:
ALTER TABLE books DROP COLUMN authors; Изменение столбца (ALTER COLUMN)
Иногда бывают случаи, когда необходимо изменить уже созданный ранее столбец. Это действие можно выполнить с помощью команды ALTER TABLE. Для изменения существующего столбца необходимо выполнить такой запрос:
ALTER TABLE books ALTER COLUMN book_category VARCHAR(200); В данном примере мы изменили максимальное количество символов, которое может использоваться в полях столбца book_category с 255 до 200.
Также с помощью ALTER TABLE можно сделать действие сразу с несколькими столбцами. Чтобы изменить сразу несколько столбцов, вам потребуется использовать эту команду:
ALTER TABLE books MODIFY book_category VARCHAR(200), MODIFY book_name VARCHAR(200), . ; Таким запросом мы изменили сразу два столбца: book_category и book_name.
Изменение типа столбца
При помощи ALTER TABLE можно изменить тип столбца в таблице SQL. Изменение типа существующего столбца осуществляется при помощи команды:
ALTER TABLE books ALTER COLUMN book_category NVARCHAR(200); Выполнив эту команду, мы изменили тип book_category на NVARCHAR(200).
Добавление первичного и внешнего ключей при помощи ALTER TABLE
Вы можете определить существующий столбец в таблице в качестве первичного ключа с помощью команды ALTER TABLE. Запрос, добавляющий в таблицу первичный ключ, будет выглядеть следующим образом:
ALTER TABLE books ADD PRIMARY KEY (book_id); Аналогично при помощи ALTER TABLE можно добавить внешний ключ таблицы. Чтобы создать внешний ключ для таблицы MySQL выполните команду:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); В результате выполнения этой команды поле author_id в таблице books будет внешним ключом для аналогичного поля в таблице authors.
Работа с ограничениями
Ограничения — специальные правила, которые применяются к таблице, чтобы ограничить типы данных в таблице. Ограничения очень важны, так как их правильное применение помогает обеспечить целостность данных в таблицах и наладить стабильную работу базы. Давайте рассмотрим одно из таких ограничений — ограничение CHECK. Применяя ограничения CHECK к столбцу таблицы, мы создаем правило, по которому при добавлении данных СУБД будет автоматически проверять их на соответствии заданным правилам.
Создание ограничения
Например, если нам необходимо, чтобы все клиенты в базе данных Customers имели возраст больше 21 года, мы можем установить следующее ограничение:
ALTER TABLE Customers ADD CHECK (Age > 21); При применении такого ограничения стоит учитывать, что если в столбце уже существуют данные, не соответствующие ограничению, то команда будет выполнена с ошибкой. Чтобы избежать подобного поведения, вы можете добавить ограничение со значением WITH NOCHECK. Таким образом, текущие значения столбца не вызовут ошибок при выполнении запроса при несоответствии ограничению:
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21); Добавление ограничений с именами
Так как ограничений в таблицах может быть много, добавление имен к ограничениям может в значительной мере упростить будущую работу с таблицей. Создать имя для ограничения можно при помощи оператора CONSTRAINT:
ALTER TABLE Customers ADD CONSTRAINT Check_Age_Greater_Than_Twenty_One CHECK (Age > 21); Удаление ограничений
ALTER TABLE можно пользоваться и для удаления ограничений. Для удаления существующих ограничений необходимо выполнить следующую команду:
ALTER TABLE Customers DROP Check_Age_Greater_Than_Twenty_One; После выполнения этой команды ограничение перестанет применяться при добавлении новых данных в столбец.
Заключение
В данной статье мы с вами узнали что такое ALTER TABLE, рассмотрели работу с этой командой и научились вносить с ее помощью изменения в таблицы и столбцы, а также рассмотрели несколько примеров использования данной команды, которые сильно упростят будущую работу с базами данных.
Сброс пароля root в MySQL
2.17. Изменение данных
Очень редко бывают таблицы и даже целые базы данных, в которых данные не меняются, а только добавляются. Поэтому теперь мы переходим к рассмотрению процесса изменения данных в таблице.
Для изменения данных в записях таблицы используется оператор UPDATE. Общий вид этого оператора можно увидеть в листинге 2.6.
Листинг 2.6. Общий вид команды UPDATE
UPDATE < table_name WITH ( < table_hint_limited >[ . n ] ) | view_name | rowset_function_limited > SET < column_name = < expression | DEFAULT | NULL >| @variable = expression | @variable = column = expression > [ . n ] < < [ FROM < < table_source >> [ . n ] ] [ WHERE < search_condition >] > | [ WHERE CURRENT OF < < [ GLOBAL ] cursor_name >| cursor_variable_name > ] > [ OPTION ( < query_hint >[ . n ] ) ]
Самый простейший вариант команды выглядит следующим образом:
UPDATE Таблица SET Поле=Значение
В первой строке после оператора UPDATE указывается имя таблицы, которую необходимо обновить. Далее идет оператор SET, после которого идет имя поля и новое значение. При этом изменяются абсолютно все строки в таблице.
Давайте изменим дату рождения у всех записей в таблице tbPeoples:
UPDATE tbPeoples SET dDateBirthDay='10.10.2004'
Можете запустить этот пример и обновить поле «dDateBirthDay» во всех строках, а можете поверить мне на слово, что он работает и не портить красоту данных. Просто сейчас незачем делать значение поля даты одинаковым для всех работников.
Давайте лучше посмотрим другой пример, как можно изменить значение поля. Попробуем увеличить значение даты у всех строк на единицу. Я не зря выбрал дату. Если бы это было число, то оно увеличилось бы на 1, и это было бы вполне логично и понятно. А что произойдет с датой? Выполните запрос SELECT к таблице tbPeoples, и запомните несколько значений. Теперь выполним следующий запрос на обновление:
UPDATE tbPeoples SET dDateBirthDay = dDateBirthDay + 1
В данном примере мы устанавливаем полю «dDateBirthDay» значение «dDateBirthDay» плюс единица. Снова посмотрим содержимое таблицы tbPeoples. Обратите внимание, что все даты увеличились на один день.
Да, даты можно складывать с числами, потому что реально дата – это число. Более подробно о датах мы поговорим в главе 2.22.
Из этого примера видно, что при использовании оператора UPDATE можно пользоваться математическими вычислениями. Прекрасно, потому что это очень удобно.
Чтобы изменить только определенную строку или набор строк, нужно ограничить выборку с помощью секции WHERE, как мы это делали с оператором SELECT. Например, следующий запрос изменяет дату рождения только для работника Иванов Иван, для чего в секции WHERE заданы необходимые параметры поиска:
UPDATE tbPeoples SET dDateBirthDay='1.1.2004' WHERE vcFamil='ИВАНОВ' AND vcName='ИВАН'
Если вы выполните этот запрос в программе Query Analyzer, то увидите, что обновилось две записи. Дело в том, что в нашей таблице два работника с таким именем. Вы должны учитывать эту особенность, когда используете оператор UPDATE, ведь он может обновить любое количество записей в таблице, если они удовлетворяют указанным условиям. Старайтесь четко указать, что и как вы хотите обновлять, поэтому при обновлении лучше использовать ключевое поле, которое может четко идентифицировать в таблице нужную строку.
Чтобы проще было писать запросы на обновление и не ошибиться, я всегда начинаю писать сначала запрос SELECT. Например, для обновления даты рождения Иванова Ивана нужно сначала написать запрос на выборку:
SELECT * FROM tbPeoples WHERE vcFamil='ИВАНОВ' AND vcName='ИВАН'
Выполняем запрос и смотрим результат. Если все устраивает, и мы увидели только те строки, которые должны измениться, то заменяем первые две строчки запроса SELECT на операторы UPDATE и SET.
Если вы заранее знаете, что нужно обновить только одну запись (например, конкретного работника), то необходимо убедиться, что будет обновляться только одна строка. Как мы уже знаем, по фамилии искать бесполезно, бывают работники с одинаковыми фамилиями. Но у нас же есть в поле первичный ключ. Именно он может однозначно идентифицировать строку, и две записи таблицы не могут содержать в первичном ключе одно и то же значение:
UPDATE tbPeoples SET dDateBirthDay='1.1.2004' WHERE idPeoples=10
Тут очень важно создать механизм определения первичного ключа, но это уже ложиться на программиста клиентских программ для конечного пользователя. Наша задача – SQL запросы.
Обновлять можно и на основе двух таблиц сразу. Например, допустим, что необходимо установить новый номер телефона для работника по фамилии ‘КОНОНОВ’, и при этом тип телефона должен быть равен 1. Как написать такой запрос обновления? Чтобы вам проще было создавать такой запрос в будущем, давайте рассмотрим логику создания, которая опять же основывается на запросе выборки данных SELECT.
Для начала пишем запрос SELECT на выборку данных необходимого работника:
SELECT * FROM tbPeoples pl, tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples AND vcFamil='КОНОНОВ' AND idPhoneType=1
В этом запросе мы связываем таблицу работников с таблицей телефонов, и указываем необходимые условия выбора нужной строки по фамилии и типу телефона. Выполните этот запрос и в результате у вас должна появиться только одна строка, которую как раз и надо обновить.
Как теперь из запроса SELECT делаем UPDATE. Для этого первую строку (оператор SELECT) заменяем на оператор UPDATE, и указываем изменяемое поле:
UPDATE tbPhoneNumbers SET vcPhoneNumber='(905) 100-10-10' FROM tbPeoples pl, tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples AND vcFamil='КОНОНОВ' AND idPhoneType=1
Первой строкой стоит оператор UPDATE, в котором указывается таблица, поле которой нужно обновить. Во второй строке я указал оператор SET, и далее идет присвоение нужному полю нового значения. Остальные строки идентичны запросу SELECT. Точно также указывается секция FROM, наводиться связь, и указаны дополнительные условия отбора нужной строки.
Если запрос SELECT написан верно, и в его результате есть изменяемое поле, то изменив первую строку на UPDATE и оператор SET, вы верно напишете SQL запрос на изменение нужной строки и нужного поля.
Не смотря на то, что выборка данных происходит из нескольких таблиц, изменяться может только одна – та, которая указана после ключевого слова UPDATE.
Данные для изменения можно брать из другой таблицы. Например, в следующем запросе изменяется поле «dDateBirthDay» у работника, который имеет идентификатор 1. При этом поле «dDateBirthDay» увеличивается на значение первичного ключа из таблицы номеров телефонов:
UPDATE tbPeoples SET dDateBirthDay=dDateBirthDay+pn.idPhoneNumbers FROM tbPeoples pl, tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples AND pl.idPeoples=1 AND idPhoneType=1
А что, если нужно обновить сразу несколько полей? В этом случае в секции SET нужно перечислить операции изменения через запятую:
UPDATE tbPeoples SET dDateBirthDay=dDateBirthDay+1, vcFamil='mr.'+vcFamil
В этом примере, после ключевого слова SET мы изменяем поле «dDateBithDay», увеличивая его на 1 и поле «vcName», добавляя в начало фамилии префикс mr.
Следующий пример изменяет содержимое двух полей «vcPhoneNumbers» и «vcName» в связанных таблицах:
UPDATE tbPhoneNumbers SET vcPhoneNumber='(905) 100-10-10', vcName='Иван' FROM tbPeoples pl, tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples AND vcFamil='КОНОНОВ' AND idPhoneType=1
Количество изменяемых полей в одном запросе не ограничено, можно изменить хоть все поля. Но при этом вы должны учитывать, что нельзя изменять автоматически увеличиваемые поля. Мы не могли записать в такие поля значения при добавлении записей и не можем изменить их. Конечно же, мы можем насильно разрешить изменение поля, установив свойство IDENTITY_INSERT в ON:
SET IDENTITY_INSERT tbPeoples ON
Но это не рекомендовалось при добавлении записей, а при редактировании я бы сказал что запрещается. Нет, вы можете изменить, но при изменении возрастает вероятность нарушения целостности. Если данный ключ используется для связи с записями другой таблицы, то связь просто нарушиться.