Простая работа с MySQL в простых примерах #2 – наполнение таблиц
Это вторая часть, в которой будут описаны способы вставки данных в таблицы.
Первая часть доступна тут>>> – в ней описаны общие возможности при работе с MySQL, полезные команды и некоторые функции. Так же, в первой части описан процесс создания таблиц и типы используемых в ней столбцов.
Методы вставки данных
Для добавления данных в таблицы используются несколько методов:
с помощью оператора INSERT и значения VALUES ;
с помощью оператора INSERT и значения SET ;
с помощью оператора LOAD DATA ;
с помощью утилиты mysqlimport из файла, в консоли сервера.
Добавление с помощью оператора с помощью утилиты INSERT VALUES
Оператор INSERT имеет следующий синтаксис:
Порядок указания параметров VALUES должен соответствовать порядку столбцов в таблице, проверить которые можно командой:
Выделять значения можно как двойными кавычками ” “ , так и одинарными – ‘ ‘ . Столбцы, имеющие атрибут AUTO_INCREMENT заполняются значением NULL . Можно использовать множественный ввод для разных строк одной таблицы, указав значения через запятую:
Для первого примера – используем созданную в первой части статьи таблицу main_list и добавим в неё такие данные:
И посмотрим – что получилось:
Добавим вторую запись для Sunko в таблицу main_socials , в которой укажем ссылку на его профайл Вконтакте:
Добавление с помощью оператора SET
При использовании оператора SET в запросе перечисляются все имена столбцов и значения, которые в них требуется установить:
Однако, с помощью оператора SET нельзя вставлять несколько строк, в отличии от схемы INSERT VALUES .
Больше про оператор INSERT можно прочитать тут>>>.
Добавление из файла с помощью LOAD DATA
Столбцы в файле должны быть разделены табуляцией (не пробелом!). Использовать кавычки не нужно. Значения VALUES располагаются как и при обычном методе INSERT VALUES – по очереди имеющихся столбцов в таблице.
Например – содержимое файла main_list.txt :
Если сервер выдал ошибку такого плана:
Добавьте в конфигурационный файл сервера MySQL my.cnf в блок [client] строку:
и перезапустите сервер.
Больше информации о LOAD DATA INFILE можно найти тут>>>.
Добавление из файла с помощью mysqlimport
Фактически, mysqlimport просто выполняет оператор LOAD DATA на сервере.
Требования к файлу такие же, как и при использовании LOAD DATA и были описаны выше.
–debug-info использовать не обязательно, тут он просто для примера.
Посмотрим, что получилось:
Больше про mysqlimport можно прочитать тут>>>.
Для удаления ошибчной или лишней записи – используйте такой запрос:
В следующей части будут рассмотрены другие возможности при работе с MySQL – выборка и сортировка значений из таблиц.
Как быстро заполнить таблицу в sql
SQL Server. Вставляем данные в таблицу оптимальным способом
08.04.2021 Сергей Геворкьян, г. Санкт-Петербург
Время прочтения: 7 мин.
Накопление данных для дальнейшего анализа и исследования — это процесс создания (очистки, преобразования) данных, загрузки в базу данных, хранения, поддержания в актуальном состоянии этих данных с возможностью выполнения определенных функциональных задач СУБД SQL Server. И все нужные данные должны извлекаться быстро и в достаточном объеме по запросу пользователя СУБД.
То, как выполняется генерация данных и затем вставка их в таблицы SQL Server, может отразиться не только на рациональности обслуживания БД, но и на ее производительности. Важность данного вопроса нередко упускается из виду пользователями СУБД. Если не принимать это во внимание, то в дальнейшем могут возникнуть трудности для администраторов и разработчиков, участвующих в сопровождении и обслуживании базы данных.
В данной статье предлагаю обратить внимание на некоторые способы создания и добавления данных в БД, которые могут помочь оптимизировать SQL-скрипты, дизайн БД, уменьшить вероятность ошибок при создании и обслуживании объектов БД для ваших задач.
Создадим небольшую тестовую таблицу, в которую в дальнейшем будут добавляться некоторые регистрационные данные клиента:
Вставка данных с помощью явного указания столбцов.
Первый простой пример вставки – используем оператор insert с явным указанием всех столбцов таблицы client:
Вставка данных с исключением столбцов из списка.
Теперь вставим еще данные в таблицу, исключив некоторые столбцы из списка. Для этого примера можно использовать столбцы, допускающие значение NULL, в нашем случае это client_comment, или столбцы, имеющие значения по умолчанию.
Проверим работу этих скриптов:
select * from client;
Видно, что для второй строки (client_id = 2) в скрипте при вставке не указан столбец client_comment и в таблице было назначено значение NULL для него.
Далее, для демонстрации примера вставки данных, добавим в столбец client_comment ограничение по умолчанию:
Теперь, имея это ограничение, продемонстрируем еще одну вставку данных insert, где опускается столбец client_comment
Преимущество вставки данных с явным указанием списка столбцов в том, что точно прописывается, какие из этих столбцов заполняются и какие данные помещаются в определенный столбец. Явно указанный список столбцов при вставке затрудняет случайное исключение столбцов. Причем, если столбец исключен из списка вставки – будет назначено значение NULL.. Если столбец NOT NULL и без ограничения по умолчанию будет исключен из списка, то при вставке данных будет выдана ошибка:
Недостаток этого способа — это неудобство сопровождения в сценариях, где схема таблицы может часто меняться.
Вставка данных без явного списка столбцов.
Другой способ вставки данных в созданную таблицу без списка столбцов можно выполнить так:
Плюс этого способа — это то, что он быстрый и требует меньшего обслуживания, т.к. здесь нет необходимости указывать и поддерживать список столбцов при вставке. Недостаток — если неверно сопоставить вставляемые данные столбцам, получаем сообщение об ошибке (например, не указываем данные для столбца client_comment):
Используйте этот способ, если удобство более простого синтаксиса в вашем уникальном проекте преобладает перед недостатком, который может возникнуть от нарушения кода в будущем, например, при изменении схемы таблицы. Можно улучшить этот код, добавив псевдонимы ко всем столбцам, в которые вставляются данные:
или указать комментарий:
Вставка данных с помощью select into.
Другой пример кода — создание временной таблицы и выбор данных в ней как часть одного оператора.
Данный способ может быть удобным и в случае использования постоянных таблиц, если сценарий проекта предполагает это.
Временная таблица создается «на лету» с указанными в коде именами столбцов. Причем SQL Server автоматически определяет тип данных для столбцов.
Чтобы посмотреть тип данных и размер столбцов этой временной таблицы выполним код:
Размеры столбцов соответствуют минимально необходимым размерам вставляемых данных. Это может быть удобно, если в дальнейшем не потребуется добавлять дополнительно новые данные в эту временную таблицу. В противном случае необходимо быть уверенным, что новые добавляемые данные не превысят уже определенные размеры столбцов. Однако, можно заранее определить тип данных и размер столбцов самостоятельно:
Select into удобен, когда вставляются в таблицу данные, отличающиеся раз от раза или часто меняется схема данных, и это приводит к некоторым сложностям обслуживания списка столбцов. К недостатку можно отнести то, что результирующая таблица не имеет индексов и ограничений, их необходимо создать в дальнейшем дополнительно. Но, если для вашего сценария в этом нет необходимости, то использование select into в таком случае — это способ сократить код и применять его при возможном изменении определения данных или базовой схемы вашего проекта.
Вставка данных, используя хранимую процедуру.
Следующий код использует хранимую процедуру sys.sp_who2 для возврата данных в созданную временную таблицу:
Таким образом в приведенном примере выполнение хранимой процедуры sys.sp_who2 возвращает список текущих соединений SQL Server. Создав заранее временную таблицу #table_process и вставив эти данные в нее, получим возможность фильтрации набора результатов по необходимым критериям. Так же это можно применить и для постоянной таблицы.
В ряде существующих методов вставки данных выбирайте тот, который больше отвечает требованиям вашего проекта:
insert с явным списком столбцов для приложений, в которых списки столбцов, входные и выходные данные меняются не часто;
insert без указания списка столбцов – для сценариев, в которых столбцы могут быть неизвестны заранее или меняются довольно часто;
select into для создания временной структуры данных, и далее для вставки временных данных в постоянную таблицу с использованием формального списка столбцов.
Изменение, заполнение и обновление таблиц
После создания пустых таблиц следующим логическим шагом является заполнение их данными и обновление этих данных. Для этого в Transact-SQL предназначена пара инструкций INSERT – UPDATE.
Однако, часто на позднем этапе проектирования возникает необходимость изменить саму схему таблиц. Например, если изменился первичный ключ или тип данных столбца. Чтобы не удалять старые таблицы и не создавать их заново с помочью CREATE TABLE c правильными параметрами, применяется инструкция ALTER TABLE. Применение этих трех конструкций рассматривается ниже.
Заполнение таблиц
Заполнить таблицу данными можно через конструкцию CREATE TABLE, однако более эффективным подходом является разделять создание таблицы и ее заполнение, особенно новичкам в SQL, потому что:
- визуально понятнее;
- удобнее, если наполнение таблиц поэтапное.
Чтобы получить следующий вид таблицы:
Потребуется создать ее с помощью CREATE TABLE и заполнить, применив инструкцию INSERT. Следующая инструкция добавляет одну строку в уже созданную нами таблицу housemates:
В примере выше следует различать два блока конструкции INSERT:
INTO – указывающий на таблицу в которую добавляются данные
VALUES – инициализирующий построчный ввод.
В параметрах (в скобках) блока INTO перечисляются столбцы в порядке их следования. Допустима запись без параметров, если порядок следования столбцов при создании таблицы и заполнении ее совпадают. Следующий код идентичен предыдушему:
Если необходимо поменять порядок заполнения, то это нужно явно указать:
В блоке VALUES производится построчная инициализация в порядке следования столбцов блока INTO. Заполнение строки – это перечисление значений ячеек в скобках. Значения перечисляются через запятую, строки между собой тоже.
Применить конструкцию INSERT можно только к тем столбцам в которых еще нет данных. Иначе возникнет ошибка выполнения запроса.
Обновление таблицы
Потребность в обновлении таблиц возникает если необходимо изменить уже введенные в таблицу данные. Выполняется с помощью инструкции UPDATE. Если INSERT работает только с пустыми таблицами, то UPDATE только с заполненными. Если запрос будет адресован к незаполненным ячейкам, возникнет ошибка.
Следующий код присвоит новый почтовый ящик жителю дома с идентификационным номером 103.
Блок SET – это блок изменений. Если нужно обновить значение нескольких ячеек, то они перечисляются через запятую.
Изменение таблицы
На поздних этапах проектирования или уже после разработки базы часто возникает необходимость:
- добавить или удалить столбец;
- изменить тип столбца;
- назначить столбец или группу столбцов первичным или внешним ключом, или снять эти ограничения.
Для всех этих операций предназначена инструкция переопределения ATER TABLE.
Чтобы добавить столбец инструкция ALTER TABLE применяется с предложением ADD. Добавим новый столбец к таблице housemates из прошлого раздела:
Нужно применить к нему предложение ALTER COLUMN внутри ALTER TABLE:
Удаляется столбец применением DROP COLUMN внутри ALTER TABLE:
Первичный или внешний ключ удаляется и добавляется конструкциями ALTER TABLE ADD CONSTRAINT/DROP CONSTRAINT, соответственно:
Если попытаться присвоить таблице первичный ключ, который уже там есть транслятор запросов вернет ошибку. Поэтому если в таблице уже есть первичный ключ, следует снять его и назначить группу столбцов первичным ключом. Например, так:
INSERT INTO. Ввод данных в таблицу базы данных в MySQL
Для ввода данных в БД понадобится команда INSERT INTO . Также важно знать название и тип данных полей (колонок) таблицы, которые вы будете заполнять.
Синтаксис ввода данных в таблицу.
Для примера заполним таблицу books для хранения книг из базы данных Bookstore, которую мы создали в предыдущем посте.
Сначала выберем базу данных, для которой будем выполнять запросы.
Создадим в таблице две записи с некоторыми данными.
Все строковые значения, а также даты и время, необходимо обрамлять кавычками.
Создание записи таблицы в терминале.
]# mysql -u root -p
Enter password:
mysql> USE Bookstore;
Database changed
mysql> INSERT INTO books
-> (title, author, publish_year, genre, price)
-> VALUES
-> ( ‘Дубровский’ , ‘Александр Пушкин’ , 1855, ‘Драма,Повесть’ , 125.50);
Query OK, 1 rows affected (0.00 sec)
mysql> INSERT INTO books
-> (title, author, publish_year, genre, price)
-> VALUES
-> ( ‘Нос’ , ‘Николай Гоголь’ , 1836, ‘Повесть’ , 150);
Query OK, 1 rows affected (0.00 sec)
Как ввести данные в БД с помощью HTML формы и PHP (PDO)
Для ввода большого количества запросов гораздо удобнее использовать HTML форму, поля которой будут соотнесены с полями таблицы в базе данных.