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

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

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

Таблица «Countries»:

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

mysql -u root -h xxx.xxx.xxx.xxx -p

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

CREATE DATABASE TestDB;

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

show databases;

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

GRANT ALL PRIVILEGES ON TestDB.* TO 'test_user'@'%' WITH GRANT OPTION;

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

CREATE TABLE Clubs 
(
    Club VARCHAR(64) NOT NULL,
    Country VARCHAR(32),
    Year INT,
    Champs INT,
    Cups INT,
    Eurocups INT
);

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

CREATE TABLE Countries 
(
    Country VARCHAR(32) NOT NULL,
    Capital VARCHAR(32),
    Population INT
);

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

dbaas

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

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

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

INSERT INTO Countries VALUES("Russia", "Moscow", 147);

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

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 35, 19, 21);

INSERT INTO Countries VALUES("Russia", 147);

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

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

INSERT INTO Clubs(Club, Country, Champs, Cups, Eurocups) VALUES("Barcelona", "Spain", 26, 31, 18);

INSERT INTO Countries(Country, Capital) VALUES("USA", "Washington");

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

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

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

INSERT INTO Clubs(Club, Country, Eurocups)
VALUES
("Real Madrid", "Spain", 21),
("Barcelona", "Spain", 18),
("Milan", "Italy", 14),
("Juventus", "Italy", 8),
("Bavaria", "Germany", 10);

INSERT INTO Countries(Country, Population)
VALUES
("Russia", 147),
("USA", 336),
("China", 1427),
("India", 1435),
("Brazil", 218);

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

Инструкция SET

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

INSERT INTO Clubs SET
Club="Milan",
Country="Italy",
Year=1899,
Champs=19,
Cups=5,
Eurocups=14;

INSERT INTO Countries SET
Country="China",
Capital="Beijing",
Population=1427;

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

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

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

INSERT INTO Clubs(Club, Country, Year, Champs, Cups, Eurocups)
SELECT Club, Country, Year, Champs, Cups, Eurocups
FROM Clubs2;

INSERT INTO Countries(Country, Capital, Population)
SELECT Country, Capital, Population
FROM Countries2;

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

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

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

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

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

INSERT IGNORE Clubs(Club, Country, Year, Champs, Cups, Eurocups)
VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

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

Выполнение:

INSERT INTO Countries VALUES("Russia", "Moscow", 147);

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

INSERT IGNORE Countries(Country, Capital, Population)
VALUES("Russia", "Moscow", 147);

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

Подготовили для вас выгодные тарифы на DBaaS

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

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

'Real Madrid'    'Spain'    '1902'    '35'    '19'    '21'
'Barcelona'    'Spain'    '1899'    '26'    '31'    '18' 
'Milan'    'Italy'    '1899'    '19'    '5'    '14'
'Juventus'    'Italy'    '1897'    '36'    '14'    '8'
'Bavaria'    'Germany'    '1900'    '32'    '20'    '10'

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

'Real Madrid'    'Spain'    '\N'    '35'    '19'    '21'
'Barcelona'    'Spain'    '\N'    '26'    '31'    '18' 
'Milan'    'Italy'    '\N'    '19'    '5'    '14'
'Juventus'    'Italy'    '\N'    '36'    '14'    '8'
'Bavaria'    'Germany'    '\N'    '32'    '20'    '10'

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

set global local_infile=true;
exit
mysql --local_infile=1 -u test_user -h xxx.xxx.xxx.xxx -p

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

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt' INTO TABLE Clubs;

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

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt'' INTO TABLE Clubs LINES TERMINATED BY '\r\n';

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

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