добавить новое поле sql
Как добавить новый столбец в таблицу на SQL?
Продолжаем изучать SQL и сегодня мы поговорим о том, как можно добавить новую колонку в уже существующую таблицу с данными. И как обычно рассмотрим примеры реализации этого в разных СУБД.
Очень часто бывает, что нужно добавлять колонки в существующие таблицы, которые уже находятся в работе и в них уже есть данные. Причины такой необходимости могут быть разные, например, добавилась еще одна характеристика и ее нужно хранить именно в этой таблице. Ну, в общем если Вы читаете эту статью, то у Вас возникла такая необходимость и сейчас мы с Вами научимся добавлять новые столбцы в таблицу.
Добавляем колонку в таблицу в PostgreSQL
Сначала начнем разбирать пример на СУБД PostgreSQL. Допустим, у нас есть таблица «users» и в ней уже есть данные:
| id | Name | Fam |
| 1 | User1 | Fam1 |
| 2 | User2 | Fam2 |
| 3 | User3 | Fam3 |
Другими словами у нас в таблице имеется:
У нас возникла необходимость добавить в нашу таблицу еще и отчество пользователя. Обращаю Ваше внимание на то, что перед добавлением новой колонке Вы должны определить, какой тип данных будет в этой колонке. В нашем примере подойдет тип varchar.
Переходим к добавлению столбца:
Теперь после того, как Вы добавили колонку, можно обновить данные, например, добавим в строку со значением user1 отчество Otch1:
После этого можете проверить данные, выполнив запрос на выборку с помощью нам уже известного оператора SELECT:
Вы получите следующий результат:
| id | Name | Fam | Otch |
| 1 | User1 | Fam1 | Otch1 |
Добавляем новую колонку в таблицу в MS SQL Server 2000
Думаю, что с PostgreSQL мы разобрались, а точнее научились добавлять колонки в существующие таблицы.
Теперь попробуем проделать то же самое в MS SQL Server 2000. Все исходные данные допустим, будут такими же.
Здесь запрос практически такой же, но все равно он немного отличается:
Здесь уже название схемы указывать необязательно (по умолчанию таблица будет определена на основе контекста подключения), а также ключевое слово column тоже нет необходимости писать. Давайте также обновим и проверим наши данные:
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.
Вот и все! Надеюсь, Вам помог этот небольшой урок по добавлению колонок в существующие таблицы с использованием языка SQL. Удачи!
SQL-Урок 13. Добавление данных (INSERT INTO)
1. Добавление целых строк
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES (‘6’, ‘1st Street’, ‘Los Angeles’, ‘Harry Monroe’, ‘USA’)
Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES.
2. Добавление части строк
В предыдущем примере при использовании оператора INSERT мы явно отмечали имена столбцов таблицы. Используя данный синтаксис, мы можем пропустить некоторые столбцы. Это значит, что вы вводите значение для одних столбцов но не предлагаете их для других. Например:
INSERT INTO Sellers (ID, City, Seller_name) VALUES (‘6’, ‘Los Angeles’, ‘Harry Monroe’)
3. Добавление отобранных данных
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU
Нужно обратить внимание, чтобы значение внутренних ключей не повторялись (поле ID), в противном случае произойдет ошибка. Оператор SELECT также может включать предложения WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе SELECT, для нее важно только порядок их расположения. Поэтому данные в первом указанном столбце, что были выбраны из-за SELECT, будут в любом случае заполнены в первый столбец таблицы Sellers, указанной после оператора INSERT INTO, независимо от названия поля.
4. Копирование данных из одной таблицы в другую
Часто при работе с базами данных возникает необходимость в создании копий любых таблиц, с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL предусмотрен отдельный оператор SELECT INTO. Например, нам нужно создать копию таблицы Sellers, нужно будет прописать запрос следующим образом:
SELECT * INTO Sellers_new FROM Sellers
Создание таблицы в SQL
Для создания таблиц используется оператор CREATE TABLE.
Для примера используем ранее созданные таблицы.
Итак, в результате в базе данных Institute мы собираемся получить следующие таблицы: 
Используем следующие правила:
Название таблицы может быть написано и без кавычек, так как состоит из одного слова.
Поле name будет строковым, ограничим его в 25 символов ( VARCHAR(25) ), поле тоже обязательно для заполнения, поэтому добавим NOT NULL :
`name` VARCHAR(25) NOT NULL,
`zarplata` INT(11), `premia` INT(11),
После перечисления всех полей указываем ключевое поле:
Получаем код создания таблицы teachers :
CREATE TABLE `teachers` ( `id` INT(11) NOT NULL, `name` VARCHAR(25) NOT NULL, `zarplata` INT(11), `premia` INT(11), PRIMARY KEY (`id`) );
Проверить результат можно в сервисе онлайн визуализации схемы базы данных https://dbdesigner.net/, указания по работе с сервисом можно прочитать здесь.
Для заполнения таблицы данными используется оператор INSERT языка SQL.
Обновление таблиц: удаление и добавление полей
ALTER TABLE teachers ADD phone CHAR (20);
Протестируем в сервисе http://sqlfiddle.com/ (инструкция по использованию сервиса здесь).
Добавим код в левое окно:
select * from teachers;
ALTER TABLE teachers DROP COLUMN phone
CREATE TABLE `teachers` ( `id` INT(11) NOT NULL, `name` VARCHAR(25) NOT NULL, `zarplata` INT(11), `premia` INT(11), `phone` char(20), PRIMARY KEY (`id`) ); ALTER TABLE teachers DROP COLUMN phone; INSERT INTO teachers VALUES (1, ‘Иванов’,10000,500), (2, ‘Петров’,15000,1000), (3, ‘Сидоров’,14000,800), (4,’Боброва’,11000,800);
ALTER TABLE — изменение таблицы в SQL
Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.
SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.
При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.
Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.
Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).
При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).
Синтаксис ALTER TABLE на примере MS SQL Server
Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:
Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.
Добавляем новый столбец
Для примера добавим новый column Address в таблицу Customers:
В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:
Тогда, если в таблице существуют данные, для них для column Address добавится значение «Неизвестно».
Удаляем столбец
Теперь можно удалить column Address:
Меняем тип
Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).
Добавляем ограничения CHECK
Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.
При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:
По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.
Добавляем внешний ключ
Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:
Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):
Добавляем первичный ключ
Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:
Добавляем ограничения с именами
Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):
Удаляем ограничения
Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:
Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.
Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название «FK_Orders_To_Customers». Здесь для удаления внешнего подойдёт такое выражение:
Хотите знать про SQL Server больше? Добро пожаловать на курс «MS SQL Server Developer» в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
Вставка столбца со значением по умолчанию в таблицу SQL Server
Что такое ограничение DEFAULT
Ограничение DEFAULT задает значение по умолчанию для столбца.
Когда выполняется оператор INSERT, но не указывается конкретное значение для столбца с созданным ограничением DEFAULT, SQL Server вставляет значение по умолчанию, указанное в определении ограничения DEFAULT.
Чтобы создать ограничение по умолчанию, вам необходимо иметь разрешение на выполнение ALTER TABLE и CREATE TABLE.
Добавление ограничения DEFAULT при создании новой таблицы
Это будет таблица с именем SalesDetails. Когда мы вставляем данные в эту таблицу без указания значения для столбца Sale_Qty, запрос должен вставить нуль. Чтобы добиться этого, я создаю ограничение по умолчанию с именем DF_SalesDetails_SaleQty на столбце Sale_Qty.
Давайте теперь протестируем поведение ограничения, вставив несколько фиктивных записей в таблицу. Выполните следующий запрос:
Теперь посмотрим, что находится в таблице:
Как можно увидеть, в столбец Sale_Qty был вставлен нуль.
Если при создании таблицы, мы не указываем имя ограничения DEFAULT, SQL Server создает ограничение с уникальным именем, которое генерируется системой.
Создайте таблицу с помощью следующего запроса:
Выполните следующий скрипт, чтобы увидеть имя ограничения:
SQL Server создал ограничение со сгенерированным системой именем.
Добавление ограничение DEFAULT в существующую таблицу
Чтобы добавить ограничение для существующего столбца таблицы, используется оператор ALTER TABLE ADD CONSTRAINT:
Давайте сначала добавим столбец Product_name в SalesDetails:
Вставляем данные в таблицу без указания значения для столбца Product_name. Запрос должен вставить N/A.
Для этого я создам ограничение по умолчанию с именем DF_SalesDetails_ProductName на столбце Product_name. Следующий запрос создает это ограничение:
Теперь давайте проверим действие ограничения. Вставим запись, не указывая имя товара:
После вставки записей выполним оператор SELECT, чтобы просмотреть данные:
Как видно на рисунке, значением столбца Product_name для PROD0003 является N/A.
Изменение ограничения DEFAULT
Мы можем изменить определение ограничения по умолчанию: сначала удалить существующее ограничение, а затем создать ограничение с другим определением.
Предположим, что вместо вставки N/A мы хотим вставлять Not Applicable. Сначала мы должны удалить ограничение DF_SalesDetails_ProductName. Выполните следующий запрос:
После удаления ограничения выполните запрос для создания ограничения:
Теперь давайте вставим запись без указания имени товара:
Выполните оператор SELECT для просмотра данных в таблице SalesDetails:
Видно, что значением столбца Product_name является Not Applicable.
Просмотр ограничения DEFAULT
Мы можем увидеть список ограничений DEFAULT с помощью Server Management Studio и выполнив запрос к динамическим административным представлениям.
Откройте SSMS и разверните Databases > DemoDatabase > SalesDetails > Constraint:
Видно, что созданы два ограничения с именами DF_SalesDetails_SaleQty и DF_SalesDetails_ProductName.
Мы можем использовать хранимую процедуру sp_helpconstraint для просмотра списка ограничений, созданных в таблице:
В столбце constraint_keys выводится определение ограничения по умолчанию.
Удаление ограничения
Проверим, что ограничение было удалено:
Рассмотрим теперь оператор DROP DEFAULT. Он имеет следующий синтаксис:
где constraint_name задает имя ограничения, которое требуется удалить.
Чтобы удалить ограничение с помощью оператора DROP DEFAULT, выполните следующий запрос:
Надеюсь, что эта информация и практические примеры поможет в вашей работе.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись