Как вставлять данные в базы SQL

Операция вставки данных в реляционных БД — одна из наиболее востребованных, без которой невозможна нормальная работа с таблицами. В статье расскажем, как это делать средствами реляционных СУБД, работающих с языком SQL. Возьмем MySQL, наиболее популярную СУБД из тех, которые работают с SQL.

В качестве примера здесь и далее будем рассматривать две таблицы: ведущих европейских футбольных клубов (с указанием их национальной принадлежности, года основания, количества выигранных чемпионатов, кубков страны и европейских трофеев) и ряда ведущих стран мира (со столицами и населением в млн человек). Изначально таблицы (назовем их Clubs и Countries) будут пустыми: заполнены будут только названия столбцов. Наша задача — наполнить их при помощи различных инструкций, чтобы они выглядели примерно так:

Таблица «Countries»:

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

Теперь введите:

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

Наша база данных TestDB должна быть в списке. Далее нам нужно выдать доступы к ней пользователям. Допустим, у нас уже есть пользователь test_user, выдадим ему доступ:

Теперь можно приступать к созданию таблиц. Сделаем это сначала для клубов:

Это значит, что для первых двух столбцов мы задали строковые значения, причем длина данных в каждой ячейке не может превышать 64 и 32 символа соответственно, а ячейки в столбце Club при добавлении данных не могут быть пустыми (NOT NULL). Для оставшихся 4 столбцов мы предусмотрели целочисленные значения (INT). Теперь по тому же образцу создаем вторую таблицу:

Вот и всё, наши таблицы созданы, можно приступать к заполнению.

Инструкция INSERT INTO

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

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

Инструкция INSERT INTO со списком столбцов

Этот способ более надежный, так как исключает ошибки вставки в тех случаях, когда мы пропускаем какой-то столбец. Но взамен мы должны указывать названия столбцов:

Мы пропустили год основания клуба в первом случае (столбец Year) и населения во втором (столбец Population), но никакой ошибки не будет, эти поля просто останутся пустыми. И их несложно будет заполнить позже.

Инструкция INSERT INTO для одновременной вставки

Добавлять данные построчно не всегда удобно. Поэтому разберемся, как вставить данные сразу в несколько столбцов SQL. Это делает следующая инструкция:

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

Инструкция SET

В сочетании с INSERT INTO инструкция SET позволяет вставить в таблицу одну запись:

Но в этом и ее недостаток, так как таким образом невозможно вставить данные нескольких записей в таблицу SQL: для этого подойдет предыдущая инструкция.

Как вставить данные из другой таблицы SQL

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

Инструкция IGNORE для избегания ошибок

Чтобы MySQL не прекращал работу при попытке добавления ошибочных значений, используется инструкция IGNORE. Например, мы установили ограничение уникальности для столбца Club с тем, чтобы каждое название клуба было уникальным. Для нашей таблицы добавлять такое ограничение вручную не потребуется, так как в MySQL первое значение в таблице всегда должно быть уникальным. И если у нас уже есть такая строка:

Следующая инструкция вызовет ошибку:

У нас уже есть строка со значением Real Madrid в первом столбце. Но когда мы копируем данные из нескольких таблиц, без дублей скорее всего не обойдется. И чтобы программа игнорировала их и не выдавала при этом ошибки, мы добавляем инструкцию IGNORE:

То же самое со вторым примером. У нас уже есть строка:

Выполнение:

— выдаст нам ошибку. Поэтому и здесь воспользуемся инструкцией IGNORE:

Программа просто проигнорирует эту строку и продолжит выполнение без выдачи ошибки.

Инструкция LOAD DATA для загрузки из текстового файла

Допустим, у нас уже создана пустая таблица Clubs с соответствующими столбцами, и нам нужно ее наполнить информацией из текстового файла. Инструкция LOAD DATA позволит сделать это, однако предварительно понадобится подготовить текстовые данные в файле. Открываем наш файл (допустим, Clubs.txt) текстовым редактором и форматируем данные следующим образом, разделяя значения символом табуляции:

Такой SQL-запрос вставит данные в таблицу, расположив их по корректным столбцам. А что если у нас нет значений для конкретных столбцов? Допустим, мы не знаем годы основания клубов. В этом случае запись будет такой:

'\N' означает, что эта ячейка в таблице останется пустой. Теперь осталось только загрузить данные в SQL, но сначала включим работу с локальными файлами так:

Загружаем данные, что в Linux сделает команда:

А в Windows это делается так:

Но бывает так, что система не реагирует на эти инструкции: в этом случае вам потребуется включить работу с локальными файлами в MySQL, что подробно описано в официальном руководстве. Если кратко, то значение unsigned int в опции MYSQL_OPT_LOCAL_INFILE, относящейся к настройкам mysql_options(), должно быть ненулевым.

А у нас на этом всё: теперь вы знаете, как вставлять данные в базы SQL при помощи нескольких инструкций. Просто выбирайте наиболее подходящую инструкцию для конкретного случая, и вы избежите проблем при копировании.