Основы SQL – DML – INSERT и другие

Ранее в этой серии статей мы поговорили об SQL вообще и нормальных формах, в частности. Затем продолжили разговор о DDL и нескольких вариантах рефакторинга, с ним связанных. В этой статье речь пойдет о DML (Data Manipulation Language), то есть о тех базовых возможностях, которые предоставляет SQL (строго говоря, Transact-SQL) для обработки данных.

Когда-то давно, на университетском курсе по “Базам данных”, на котором нам немного рассказывали про FoxPro (я же говорил, что давно :)), преподаватель рассказывал про способы получения данных и их обработки. Как сейчас помню, он сказал фразу “а еще есть мощный язык для обработки данных, он называется SQL” и, после эффектной паузы, добавил – “там есть четыре инструкции – SELECT, INSERT, UPDATE и DELETE, но рассказать что-то еще у меня не хватит времени”.

Честно говоря, только через пару лет, на первой работе, я понял две вещи:

1. На этом курсе я практически ничего полезного так и не узнал.
2. Программирование БД может быть не менее увлекательным, чем программирование на высокоуровневых языках.

Зато сейчас у меня немного больше времени, чем у моего преподавателя, и я постараюсь рассказать про DML более подробно и, в то же время, доступно.


Как правило, начиная разговор о DML, в первую очередь рассказывают про SELECT… Но когда я делал как все? :) Если серьезно, мне кажется более логичным создать сначала структуру таблиц, потом наполнить ее данными, после этого что-нибудь обновить/удалить, а уже потом получать данные различными способами. В реальных приложениях обычно так и происходит, не так ли? Есть, правда, одна оговорка – довольно часто используется конструкция “INSERT … SELECT…”, но об этом позже.

Поскольку ранее я говорил о важности повторно запускаемых скриптов, то и сейчас буду приводить примеры DML, который можно будет запускать повторно. Для этого, потребуется использовать конструкции EXISTS(SELECT…) и LEFT JOIN, на случай, если они кому-то незнакомы, объясню их позднее.

Импорт данных

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

1. BULK INSERT – вставка записей из внешнего файла (с разделителями, например - CSV).
2. BCP – аналог BULK INSERT в виде утилиты командной строки.
3. SqlBulkCopy – класс Microsoft .NET Framework, позволяющий импортировать данные (по тому же протоколу, что и BULK INSERT) не из файла, а, в частности, из IDataReader.
4. Table-Valued Parameters – начиная с MS SQL Server 2008 появилась возможность использовать передачу таблиц в качестве параметров. Работает достаточно быстро, некоторые рекомендации по использованию есть по ссылке выше.
5. Есть еще несколько вариантов для импорта данных (XQuery, OPENXML, строки с разделителями), но на этих решениях я останавливаться не буду, потому что они имеют слабое отношение к SQL.

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

Добавление одной строки

А теперь, собственно об INSERT. Базовый синтаксис довольно прост, так выглядит вставка строк по одной (для создания соответствующей таблицы можно использовать скрипт из предыдущей статьи):

IF NOT EXISTS(SELECT * FROM dbo.Persons WHERE LastName = 'Иванов')
BEGIN
    INSERT dbo.Persons(FirstName, MiddleName, LastName)
        VALUES('Иван', 'Иванович', 'Иванов')
    INSERT dbo.Persons(FirstName, MiddleName, LastName)
        VALUES('Петр', 'Петрович', 'Иванов')
END

Как видно из примера, после фразы “INSERT Название_таблицы” сначала перечисляется список столбцов этой таблицы, а затем соответствующий (по порядковым номерам) список значений. Если существует и должно использоваться значение по умолчанию (DEFAULT CONSTRAINT), то можно либо пропустить столбец в списке, либо явно указать значением ключевое слово DEFAULT. Если (что крайне редко случается) для всех столбцов должны использоваться значения по умолчанию, то вместо фразы “VALUES(…)” можно написать “DEFAULT VALUES”.

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

1. Увеличивается вероятность ошибок. Во-первых, потому что порядок следования столбцов может измениться. Во-вторых, потому что можно банально забыть порядок и поставить, скажем, в нашем случае на первое место значение для фамилии, которое запишется в имя.
2. Чаще всего, в таблице рано или поздно появляются новые столбцы, которые не будут учитываться в написанном ранее INSERT (получим ошибку, что количество столбцов не совпадает с количеством значений). Помимо этого, часто есть столбцы, которые должны заполняться не в этом INSERT.
3. Код без списка столбцов менее понятен тому, кто еще не очень близко знаком со структурой подопытной таблицы.

Также отмечу, что в примере проверка для повторного запуска скрипта (IF NOT EXISTS) искусственная. Идентификация человека в БД – отдельная серьезная тема, а идентификатор строки у нас автоинкрементный. Поэтому сделал проверку только по фамилии – проверка по ФИО все равно не будет корректной.

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

Добавить несколько строк можно с помощью использования “SELECT” вместо “VALUES”. Поскольку запросы на чтение я оставил на потом, здесь рассмотрим только простейшие варианты.
Давайте добавим каждому человеку в нашей БД по одному тестовому телефонному номеру:

INSERT dbo.Phones (PersonID, Phone)
    SELECT p.PersonID, '123-'+CAST(p.PersonID AS VARCHAR(10))
    FROM dbo.Persons p
    LEFT JOIN dbo.Phones f ON f.PersonID = p.PersonID
        AND f.Phone = '123-'+CAST(p.PersonID AS VARCHAR(10))
    WHERE f.PersonID IS NULL

На всякий случай, поясню, что LEFT JOIN и фильтр “f.PersonID IS NULL”  дает, в результате, вставку только тех пар идентификаторов и телефонов, которые еще не присутствуют в таблице “Phones”. А “p” и “f” используются в качестве алиасов (псевдонимов), чтобы запрос был компактнее. Для более сложных запросов, когда одна и та же таблица используется несколько раз, без алиасов просто не обойтись.

Делать проверки на существование можно по-разному, навскидку, перечислю еще 3 способа, помимо LEFT JOIN:

• IF NOT EXISTS – сначала проверяем, если записей нет – добавляем. Минус в том, что этот метод вида “все или ничего”, тогда как остальные позволяют вставить только часть новых записей, когда некоторые уже есть в таблице.
• WHERE NOT EXISTS – похож на предыдущий способ, но проверка идет в WHERE от INSERT, поэтому нет вышеупомянутого минуса.
• WHERE Идентификатор NOT IN – почти аналогичен предыдущему, но не очень приспособлен к ситуации, когда первичный ключ составной.

Исторически сложилось, что я пользуюсь способом с “LEFT JOIN” – мне так удобнее, хотя готов согласиться, что способ “WHERE NOT EXISTS” может быть нагляднее. С другой стороны, когда коллеги тоже пользуются “LEFT JOIN”, проблем обычно не возникает. Что касается производительности – обычно встроенный оптимизатор запросов уравнивает эти варианты.
И, конечно, вы уже наверняка догадались, что “CAST” в Transact-SQL является аналогом приведения типа в других языках программирования.

Добавление тестовых данных

Вставкой тестовых данных мы занялись еще в прошлом разделе, теперь хочу остановиться на этом подробнее.

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

• Можно активно использовать числовые первичные ключи для заполнения тестовых полей (например, для названий вида Name1,.. NameN, или, как в нашем случае, для генерации разных телефонных номеров).
• Там, где нужны случайные значения для многострочных запросов нельзя использовать функцию RAND – она вернет одно и то же значение. Если корректность “случайности” не очень важна, можно использовать код на базе функции NEWID (генерирует GUID).
• Если хочется сделать различные по длине списки для разных родительских записей, удобно пользоваться теми же первичными ключами, но, например, с остатком от деления на некоторое число или с другими арифметическими операциями. Например, можно присоединить таблицу натуральных чисел (см. далее) по условию, что число меньше или равно остатку от деления на 5 плюс 1.
• Весьма полезная вещь (и не только для тестовых данных) – таблица натуральных чисел (например, от 1 до 100 или от 1 до 1000 – когда нужно будет больше чисел, с ней можно соединиться дважды). С ее помощью очень просто получать некоторые списки значений, что вы увидите на примере далее.

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

IF OBJECT_ID('dbo._Numbers') IS NULL
    CREATE TABLE dbo._Numbers (Number INT NOT NULL PRIMARY KEY)
GO
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM _Numbers)
BEGIN
    DECLARE @i INT
    SET @i = 0
    WHILE @i <= 1000
    BEGIN
        INSERT _Numbers VALUES(@i)
        SET @i = @i + 1
    END
END

Здесь была использована инструкция “SET NOCOUNT”, чтобы не захламлять результаты тысячей сообщений о вставке строки. Теоретически, этот код можно улучшить, но, учитывая его разовый запуск, это не имеет смысла.
 
А теперь, добавим избыточных телефонных номеров (немного модифицировав пример из предыдущего раздела):

INSERT dbo.Phones (PersonID, Phone) 
    SELECT p.PersonID, CAST(n.Number AS VARCHAR(3))+'-'+CAST(p.PersonID
            AS VARCHAR(10)) FROM dbo.Persons p
        JOIN dbo._Numbers n ON n.Number
            BETWEEN 100 AND 100 + 50 * p.PersonID
        LEFT JOIN dbo.Phones f ON f.PersonID = p.PersonID 
            AND f.Phone = CAST(n.Number AS VARCHAR(3))
                + '-' + CAST(p.PersonID AS VARCHAR(10))
        WHERE f.PersonID IS NULL

Обратите внимание, что не возникло проблем с телефонами, добавленными на предыдущем шаге, за счет связки LEFT JOIN + WHERE.

Добавление результата хранимой процедуры

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

Когда хранимая процедура возвращает результаты запроса, можно вставить эти результаты в таблицу (разумеется, при условии совпадения количества и совместимости типов полей). Синтаксис выглядит как “INSERT Название_таблицы EXEC Название_процедуры Параметры”. Вот простой пример, без особой смысловой нагрузки:

IF OBJECT_ID('dbo.TestSelect') IS NOT NULL
    DROP PROC dbo.TestSelect
GO
CREATE PROC dbo.TestSelect AS
SELECT FirstName, MiddleName, LastName FROM dbo.Persons
GO
DECLARE @p TABLE(FirstName VARCHAR(100), MiddleName VARCHAR(100),
    LastName VARCHAR(100))
INSERT @p EXEC dbo.TestSelect
SELECT * FROM @p

За кадром

В этой статье не рассматривались такие расширения оператора INSERT как WITH, OUTPUT и TOP (присутствуют в сравнительно свежих версиях MS SQL Server). За подробностями можно обратиться к статье про INSERT в MSDN.

Также за кадром осталась такая важная тема, как блокировки. Но это относится уже не к основам SQL, а к более глубокому его изучению. Надеюсь, после этой серии смогу перейти к следующей, в которой и расскажу как про блокировки, так и про “взаимоблокировки” (deadlocks).

Анонс

В следующих сериях:

• Продолжение разговора о DML - UPDATE, DELETE, SELECT
• Рефакторинг с учетом данных
• Подробнее о запросах
• Управляем доступом и транзакциями

Кстати говоря, если у кого-нибудь есть пожелания или даже новые темы к статьям из этой серии – пишите в комментариях. Постараюсь принять к сведению.