Добавление sql. Удаление записей из базы данных SQL

Кроме инструкции SELECT, которая была рассмотрена ранее, язык манипуляции данными DML (Data Manipulation Language) содержит три другие инструкции: INSERT, UPDATE и DELETE. Подобно инструкции SELECT эти три инструкции оперируют либо таблицами, либо представлениями. В этой статье рассматривается инструкция INSERT, а две другие инструкции рассматриваются в следующей статье.

Инструкция INSERT вставляет строки (или части строк) в таблицу. Существует две разные формы этой инструкции:

INSERT tab_name [(col_list)] DEFAULT VALUES | VALUES ({ DEFAULT | NULL | expression } [ ,...n]) INSERT INTO tab_name | view_name [(col_list)] {select_statement | execute_statement} Соглашения по синтаксису

Первая форма инструкции позволяет вставить в таблицу одну строку (или часть ее). А вторая форма инструкции INSERT позволяет вставить в таблицу результирующий набор инструкции SELECT или хранимой процедуры, выполняемой посредством инструкции EXECUTE. Хранимая процедура должна возвращать данные для вставки в таблицу. Применяемая с инструкцией INSERT инструкция SELECT может выбирать значения из другой или той же самой таблицы, в которую вставляются данные, при условии совместимости типов данных соответствующих столбцов.

Для обеих форм тип данных каждого вставляемого значения должен быть совместимым с типом данных соответствующего столбца таблицы. Все строковые и временные данные должны быть заключены в кавычки; численные значения заключать в кавычки не требуется.

Вставка одной строки

Для обеих форм инструкции INSERT явное указание списка столбцов не является обязательным. Отсутствие списка столбцов равнозначно указанию всех столбцов таблицы.

Параметр DEFAULT VALUES вставляет значения по умолчанию для всех столбцов. В столбцы с типом данных TIMESTAMP или свойством IDENTITY по умолчанию вставляются значения, автоматически создаваемые системой. Для столбцов других типов данных вставляется соответствующее ненулевое значение по умолчанию, если таково имеется, или NULL в противном случае. Если для столбца значения NULL не разрешены и для него не определено значение по умолчанию, выполнение инструкции INSERT завершается ошибкой и выводится соответствующее сообщение.

В примере ниже показана вставка строк в таблицу Employee базы данных SampleDb, демонстрируя использование инструкции INSERT для вставки небольшого объема данных в базу данных:

USE SampleDb; INSERT INTO Employee VALUES (34990, "Андрей", "Батонов", "d1"); INSERT INTO Employee VALUES (38640, "Алексей", "Васин", "d3");

Существует два разных способа вставки значений в новую строку. Инструкция INSERT в примере ниже явно использует ключевое слово NULL и вставляет значение NULL в соответствующий столбец:

USE SampleDb; INSERT INTO Employee VALUES (34991, "Андрей", "Батонов", NULL);

Чтобы вставить значения в некоторые (но не во все) столбцы таблицы, обычно необходимо явно указать эти столбцы. Не указанные столбцы должны или разрешать значения NULL, или для них должно быть определено значение по умолчанию.

USE SampleDb; INSERT INTO Employee(Id, FirstName, LastName) VALUES (34992, "Андрей", "Батонов");

Предыдущие два примера равнозначны. В таблице Employee единственным столбцом, разрешающим значения NULL, является столбец DepartmentNumber, а для всех прочих столбцов это значение было запрещено предложением NOT NULL в инструкции CREATE TABLE.

Порядок значений в предложении VALUES инструкции INSERT может отличаться от порядка, указанного в инструкции CREATE TABLE. В таком случае их порядок должен совпадать с порядком, в котором соответствующие столбцы перечислены в списке столбцов. Ниже показан пример вставки данных в порядке, отличающемся от исходного:

USE SampleDb; INSERT INTO Employee(DepartamentNumber, LastName, Id, FirstName) VALUES ("d1", "Батонов", 34993, "Андрей");

Вставка нескольких строк

Вторая форма инструкции INSERT вставляет в таблицу одну или несколько строк, выбранных подзапросом. В примере ниже показана вставка строк в таблицу, используя вторую форму инструкции INSERT. В данном случае выполняется запрос по выборке номеров и имен отделов, расположенных в Москве, и загрузка полученного результирующего набора в новую таблицу, созданную ранее.

Создаваемая в примере выше новая таблица MoscowDepartment имеет те же столбцы, что и существующая таблица Department, за исключением отсутствующего столбца Location. Подзапрос в инструкции INSERT выбирает в таблице Department все строки, для которых значение столбца Location равно "Москва", которые затем вставляются в созданную в начале запроса новую таблицу.

В примере ниже показан еще один способ вставки строк в таблицу, используя вторую форму инструкции INSERT. В данном случае выполняется запрос на выборку табельных номеров, номеров проектов и дат начала работы над проектом для всех сотрудников с должностью "Менеджер", которые работают над проектом p2 с последующей загрузкой полученного результирующего набора в новую таблицу, создаваемую в начале запроса:

USE SampleDb; CREATE TABLE ManagerTeam (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, EnterDate DATE); INSERT INTO ManagerTeam (EmpId, ProjectNumber, EnterDate) SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE Job = "Менеджер";

Перед вставкой строк с помощью инструкции INSERT таблицы MoscowDepartment и ManagerTeam (в примерах выше) были пустыми. Если же таблица уже существовала и содержала строки с данными, то к ней были бы добавлены новые строки.

В этой статье мы разберём, пожалуй, одни из самых важных SQL-запросов . Это запросы на добавление и удаление записей из таблицы базы данных . Поскольку, ОЧЕНЬ часто приходится добавлять новые записи в таблицу , причём делать это в автоматическом режиме, то данный материал обязателен к изучению.

Для начала SQL-запрос на добавление новой записи в таблицу :

INSERT INTO users (login, pass) values("TestUser", "123456")

При добавлении записи вначале идёт команда "INSERT INTO ", затем название таблицы, в которую мы вставляем запись. Далее идёт в круглых скобках названия полей, которые мы хотим заполнить. А затем в круглых скобках после слова "values " начинаем перечислять значения тех полей, которые мы выбрали. После выполнения этого запроса в нашей таблице появится новая запись.

Иногда требуется обновить запись в таблице , для этого существует следующий SQL-запрос :

UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"

Данный запрос является более сложным, так как он имеет конструкцию "WHERE ", но о ней чуть ниже. Вначале идёт команда "UPDATE ", затем имя таблицы, а после "SET " мы описываем значения всех полей, которые мы хотим изменить. Было бы всё просто, но встаёт вопрос: "А какую именно запись следует обновлять? ". Для этого существует "WHERE ". В данном случае мы обновляем запись, поле "login " у которой имеет значение "TestUser ". Обратите внимание, что если таких записей будет несколько, то обновятся абсолютно все ! Это очень важно понимать, иначе Вы рискуете потерять свою таблицу.

Давайте немного ещё поговорим о "WHERE ". Помимо простых проверок на равенство существуют так же и неравенства, а также логические операции: AND и OR .

UPDATE users SET login = "TestUser2", pass="1234560" WHERE id < 15 AND login="TestUser"

Данный SQL-запрос обновит те записи, id которых меньше 15 И поле "login " имеет значение "TestUser ". Надеюсь, Вы разобрались с конструкцией "WHERE ", потому что это очень важно. Именно "WHERE " используется при выборке записей из таблиц , а это самая частоиспользуемая задача при работе с базами данных.

И, напоследок, простой SQL-запрос на удаление записей из таблицы :

DELETE FROM users WHERE login="TestUser2"

После команды "DELETE FROM " идёт имя таблицы, в которой требуется удалить записи. Дальше описываем конструкцию "WHERE". Если запись будет соответствовать описанным условиям, то она будет удалена. Опять же обратите внимание, в зависимости от количества записей, удовлетворяющих условию после "WHERE ", может удалиться любое их количество.

Удаление записей

Для удаления записей из таблицы применяется оператор DELETE:

DELETE FROM имяТаблицы WHERE условие;

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

Следующий запрос удаляет записи из таблицы Customer, в которой значение столбца LName равно "Иванов":

DELETE FROM Customer

WHERE LName = "Иванов"

Если таблица содержатся сведения о нескольких клиентах с фамилией Иванов, то все они будут удалены.

В операторе WHERE может находиться подзапрос на выборку данных (оператор SELECT). Подзапросы в операторе DELETE работают точно так же, как и в операторе SELECT. Следующий запрос удаляет всех клиентов из города Москва, при этом уникальный идентификатор города возвращается с помощью подзапроса.

DELETE FROM Customer

WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName = "Москва" )

Transact-SQL расширяет стандартный SQL, позволяя использовать в инструкции DELETE еще одно предложение FROM. Это расширение, в котором задается соединение, может быть использовано вместо вложенного запроса в предложении WHERE для указания удаляемых строк. Оно позволяет задавать данные из второго FROM и удалять соответствующие строки из таблицы в первом предложении FROM. В частности предыдущий запрос может быть переписан следующим образом

DELETE FROM Customer

FROM Customer k INNER JOIN

Операция удаления записей из таблицы является опасной в том смысле, что связана с риском необратимых потерь данных в случае семантических (но не синтаксических) ошибок при формулировке SQL-выражения. Чтобы избежать неприятностей, перед удалением записей рекомендуется сначала выполнить соответствующий запрос на выборку, чтобы просмотреть, какие записи будут удалены. Так, например, перед выполнением рассмотренного ранее запроса на удаление не помешает выполнить соответствующий запрос на выборку.

SELECT *

FROM Customer k INNER JOIN

City c ON k.IdCity = c.IdCity AND c.CityName = "Москва"

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

DELETE FROM Product

Задание для самостоятельной работы: Сформулируйте на языке SQL запрос на удаление всех заказов, не имеющих в составе ни одного товара (т. е. все пустые заказы).

Запросы на изменение используются для добавления (INSERT INTO), удаления (DELETE) и обновления (UPDATE) записей в таблицах.

Команда INSERT INTO

Команда INSERT INTO может быть использована для добавления одной или нескольких записей в конец таблицы.

Синтаксис команды INSERT INTO имеет следующий вид:
INSERT INTO Название_Таблицы [(Название_Полей)] VALUES (Значения_данных);

Например, поместим в таблицу "Группы" запись, содержащую данные (1, БФ-16а, 1, 1) в соответствующие столбцы (КодГруппы, Название, Курс, Семестр).

Для этого запрос в формате:
INSERT INTO Группы (КодГруппы, Название, Курс, Семестр) VALUES (1, БФ-16а, 1, 1);
введем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.

Сохраняем запрос с именем "Добав_1_записи". В результате в «Области переходов» появится несвязанный объект - «Добав_1_записи» (Рис.1.).

Рис. 1.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Добав_1_записи» в пустой таблице появится первая запись (Рис. 2.)

Рис. 2.

Команда UPDATE

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

Синтаксис команды UPDATE имеет следующий вид:
UPDATE Название_Таблицы SET Название_Столбца = Новое_Значение WHERE Условие_Отбора;

В предложении SET указываются имена столбцов, данные в которых надо изменить. Оператор WHERE не является обязательным, но при его помощи можно указать только на те строки столбцов (таблиц), которые необходимо обновить.

Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.

Например, изменим уже существующую запись для первого ключевого поля id в столбце "Название" таблицы "Группы".

Запрос в формате:
UPDATE Группы SET Название ="БФ-16б" WHERE ID=1;

Сохраняем запрос с именем "Изменение_записи_1". В результате в «Области переходов» появится объект - «Изменение_записи_1» (Рис. 3.).

Рис. 3.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Изменение_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий в таблице "Группы" изменится запись в поле "Название" с БФ-16а на БФ-16б (Рис. 4.).

Рис. 4.

Команда DELETE

Команда DELETE предназначена для удаления записей из таблицы.

Формат запроса на удаление записей:
DELETE FROM Название_Таблицы WHERE Условие_Отбора;

Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.

Например, удалим существующую запись для первого ключевого поля id во всех столбцах (полях) таблицы "Группы".

Для этого запрос в формате:
DELETE FROM Группы WHERE ID=1;
создаем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.

Сохраняем запрос с именем "Удаление_записи_1". В результате в «Области переходов» появится объект - «Удаление_записи_1» (Рис. 5.).

Рис. 5.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Удаление_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий откроется окно диалога "Подтвердите удаление записей". После подтверждения в таблице "Группы" будет удалены данные во всех полях, т.е. удалена запись (Рис. 6.).

Рис. 6.

Следует отметить, что для удаления данных в конкретных полях таблицы целесообразно воспользоваться командой UPDATE и заменить значения в требуемых полях на Null. Если необходимо удалить таблицу в целом, то следует использовать оператор DROP TABLE (синтаксис оператора: DROP TABLE Название_Таблицы;).

Восстановим первую запись в таблице "Группы", выполнив команду «Добав_1_записи», в результате восстановим первую запись (Рис. 7.).

Рис. 7.

Данная же статья посвящена наполнению таблиц БД данными, то есть будем изучать SQL-команды для вставки новых записей. Следует сказать, что запись в таблицу можно сделать двумя способами:

Способ №1.
Попробуем внести запись о новой стране в таблицу countries. Синтаксис добавления будет следующим:
INSERT INTO имя_таблицы (поле_1, поле_2, …) VALUES (Значение_1, Значение_2, …); Исходя из нашей структуры таблицы, SQL-запрос будет таким:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); Вот так мы внесли в нашу таблицу запись о стране «Россия». Здесь должно быть все понятно и просто, главное внимательно смотреть какие поля указываете в первых скобках, и в таком же порядке прописывать значения во вторых.

Способ №2.
Второй способ, на мой взгляд, немного проще, поскольку вы видите что, и какому полю присваиваете. Поверьте, если в таблице огромное количество столбцов, то очень просто перепутать или недосмотреть порядок полей в первых, и порядок значений во вторых скобках. Синтаксис второго метода такой:
INSERT INTO имя_таблицы SET поле_1 = Значение_1, поле_2 = Значение_2, … ; Давайте на данном примере внесем какую-нибудь информацию в табличку persons , поскольку там побольше полей, и сразу будет чувствоваться преимущество второго способа:
INSERT INTO persons SET first_name="Ivan", last_name="Dulin", registration_date="2012-06-14", country="1"; Теперь в нашей табличке есть такие данные:

Вы, наверное, заметили, что мы не указали значение для age, а оно оказалось заполненным... Все правильно – для данного поля мы устанавливали значение по умолчанию как 25. Так что теперь наш Ivan Dulin числиться в базе с возрастом 25 лет. Возможно, не самый удачный пример делать значение по умолчанию для поля возраста, но можно такие свойства устанавливать к таким полям, как, к примеру, рейтинг пользователя сайта, или количество просмотров страницы. Для них изначально ставится значение 0.

Следует еще обратить внимание на формат даты в MySQL: YYYY-MM-DD. Если его не придерживаться, то ваши записи просто не будут внесены в таблицу.

Как видим, поле profession заполнено как NULL, это пустое значение. Для данного поля мы задавали свойство «не обязательное к заполнению (NULL)» .

Как дополнение, рассмотрим еще такую команду:
LOAD DATA LOCAL INFILE "D:\zapros.txt" INTO TABLE persons; Как вы думаете, что мы сейчас сделали?! А сделали мы следующее: добавили данные в таблицу persons из файла zapros.txt , который находится на диске D. Информация в файле должна быть следующей структуры:

Структура данных файла должна соответствовать таким требованиям:

Каждая новая запись должна описываться с новой строчки
Данные должны быть указаны для абсолютно всех полей. Как вы можете заметить, для поля id мы указали значение NULL, ведь оно у нас автоинкрементное, так что MySQL сам внесет нужное значение.
Поля разделяются друг от друга символом табуляции (клавиша Tab).
Введенная информация должна соответствовать типам данных конкретного поля. То есть, например, дата в формате ГГГГ-ММ-ДД, целое число для integer и т.д.

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

Таблица professions:

Таблица persons:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

Обязательно добавьте эти записи, поскольку они нам понадобятся для изучения оператора SELECT (выбора информации с БД), который мы рассмотрим в следующей (четвертой) статье по изучению SQL-запросов.