Как заполнить таблицу в mysql
Перейти к содержимому

Как заполнить таблицу в mysql

  • автор:

Повышение производительности баз данных MySQL с помощью Amazon ElastiCache for Redis

Вы можете добавить в базу данных файл seed.sql, доступный в репозитории учебных пособий. Войдите в инстанс EC2 и выполните следующую команду:

syntax: shell

$ mysql -h endpoint -P 3306 -u admin -p < seed.sql

Зависание команды означает, что выполнение блокируется параметрами группы безопасности. Убедитесь, что у инстанса EC2 есть доступ к группе безопасности, назначенной инстансу MySQL. Допустим, ваш инстанс EC2 был назначен стандартной группе безопасности. Теперь вы можете изменить группу безопасности инстанса MySQL, изменить правила входящих подключений и добавить правило MYSQL/Aurora, которое разрешает подключения через порт 3306 от любого инстанса из стандартной группы безопасности:

Шаг 3

В поле Source (Источник) можно начать вводить имя группы безопасности, а затем выбрать ее идентификатор из списка. Дополнительные сведения о группах безопасности можно найти в документации или справочнике по правилам групп безопасности.

Ниже представлен разбор команд, используемых в файле seed.sql. Если вам удастся заполнить базу данных, вы можете пропустить приведенные ниже инструкции и перейти сразу к шагу 4.

3.1. Подключение к базе данных:

syntax: shell

$ mysql -h endpoint -P 3306 -u admin -p

Введите пароль, сохраненный на шаге 2.19, при появлении соответствующего запроса.

3.2. Создание базы данных.

mysql> CREATE database tutorial; Query OK, 1 row affected (0.01 sec)

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

mysql> USE tutorial; Database changed mysql> CREATE TABLE planet ( -> id INT UNSIGNED AUTO_INCREMENT, -> name VARCHAR(30), -> PRIMARY KEY(id)); Query OK, 0 rows affected (0.057 sec) mysql> INSERT INTO planet (name) VALUES ("Mercury"); Query OK, 1 row affected (0.008 sec) mysql> INSERT INTO planet (name) VALUES ("Venus"); Query OK, 1 row affected (0.011 sec) mysql> INSERT INTO planet (name) VALUES ("Earth"); Query OK, 1 row affected (0.009 sec) mysql> INSERT INTO planet (name) VALUES ("Mars"); Query OK, 1 row affected (0.009 sec) mysql> INSERT INTO planet (name) VALUES ("Jupiter"); Query OK, 1 row affected (0.008 sec) mysql> INSERT INTO planet (name) VALUES ("Saturn"); Query OK, 1 row affected (0.010 sec) mysql> INSERT INTO planet (name) VALUES ("Uranus"); Query OK, 1 row affected (0.009 sec) mysql> INSERT INTO planet (name) VALUES ("Neptune"); Query OK, 1 row affected (0.009 sec)

На следующих этапах мы будем использовать таблицу планет из базы данных учебных пособий.

SQL-Ex blog

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

Это вторая статья в серии, посвященной MySQL. Я рекомендую вам предварительно познакомиться с первой статьей, если вы не сделали этого ранее. Здесь же я сосредоточусь, главный образом, на создании, изменении и удалении таблиц, демонстрируя для этого как использование операторов SQL, так и возможности GUI в MySQL Workbench. Как и в первой статье, я использую выпуск MySQL Community на компьютере с Windows для создания примеров для настоящей статьи. Все примеры выполнены в Workbench, которая ставится вместе с Community Edition.

Использование MySQL Workbench GUI для создания базы данных

Прежде чем создавать таблицы, необходимо иметь базу данных для этих таблиц, поэтому я сначала потрачу немного времени на создание базы данных. Создание базы данных в MySQL относительно простой процесс. Вы можете выполнить простой оператор CREATE DATABASE для экземпляра, в котором вы хотите добавить базу данных. Это особенно просто, если вы планируете использовать коллацию и набор символов по умолчанию. Например, для создания базы данных travel вам нужно всего лишь выполнить следующий оператор:

CREATE DATABASE travel;

Оператор CREATE DATABASE делает ровно то, что написано. Он создает базу данных в экземпляре MySQL, к которому вы подключены. Если вы хотите убедиться, что такой базы данных еще нет до выполнения оператора, вы можете добавить предложение IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS travel;

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

Вместо использования оператора CREATE DATABASE для создания базы данных вы можете использовать оператор CREATE SCHEMA. Оба оператора поддерживают одинаковый синтаксис и оба приводят к одному и тому же результату. Это происходит потому, что MySQL рассматривает базы данных и схемы как одно и то же. Фактически, MySQL рассматривает CREATE SCHEMA как синоним CREATE DATABASE. Когда вы создаете базу данных, то создаете схему. Когда вы создаете схему, вы создаете базу данных. Workbench использует оба термина, свободно переходя от одного к другому.

Вы также можете использовать функции GUI, встроенные в Workbench для создания базы данных. Хотя это может показаться избыточным, имея в виду простоту выполнения оператора CREATE DATABASE, GUI имеет преимущество в перечислении всех символьных наборов и коллаций, доступных для определения базы данных, если вы решили не ограничиваться значениями по умолчанию.

Чтобы использовать GUI для создания базы данных, сначала щелкните на кнопке создания схемы на панели инструментов Workbench. (Кнопка выглядит как стандартная иконка базы данных и имеет всплывающую подсказку Create a new schema in the connected server.) Когда откроется вкладка Schema, вам нужно только ввести имя базы данных, как показано на рис.1.

Рис.1 Добавление базы данных к экземпляру MySQL

Если вы хотите использовать отличные от значений по умолчанию набор символов или коллацию, вы можете выбрать их из выпадающих списков. Например, вы можете выбрать utf8 в качестве набора символов и коллацию utf8_unicode_ci.

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

Вкладка Schema содержит также опцию Rename References. Однако она заблокирована и применяется только в том случае, когда вы обновляете модель базы данных. Workbench иногда содержит опции интерфейса, которые не применяются в текущей ситуации, но которые могут смутить вас, когда вы впервые начинаете работать с MySQL или Workbench. Однако обычно вы можете принимать значения по умолчанию, и не беспокоитесь об опциях, по крайней мере, до тех пор, пока не узнаете как они работают и где применимы.

Для этой статьи (предполагая, что вы хотите следовать примерам) вы можете принять набор символов и коллацию по умолчанию и щелкнуть Apply. Это запустит мастера Apply SQL Script to Database, показанного на рис.2. На первом экране мастера показан оператор SQL, который сгенерировал Workbench, но еще не применил к экземпляру MySQL.

Рис.2 Проверка оператора CREATE SCHEMA

Экран содержит также опции Algorithm и Lock Type. Обе опции относятся к онлайновой функции DDL MySQL, которая обеспечивает поддержку изменений таблицы на месте и одновременных DML. Вам не потребуется понимание этих опций прямо сейчас, и вы можете спокойно принять значения по умолчанию. (Это еще один пример опций, когда Workbench может сбить с толку.) Однако если вы хотите больше узнать об этих возможностях, информацию можно найти в документации MySQL, относящейся к InnoDB и online DDL.

Чтобы создать базу данных, щелкните кнопку Apply, которая приведет вас на следующий экран, показанный на рис.3. Этот экран просто подтверждает, что база данных была создана. Вы можете теперь щелкнуть Finish, чтобы закрыть окно диалога. Не забудьте закрыть также исходную вкладку Schema.

Рис.3 Завершение создания новой схемы (базы данных)

База данных теперь должна появиться в списке на панели Schemas в навигаторе. Если этого не произошло, щелкните на кнопке refresh (обновить) в верхнем правом углу панели. База данных (схема) travel должна теперь появиться наряду с другими базами данных в экземпляре MySQL. В моей системе есть еще только одна база данных по умолчанию sys, как показано на рис.4.

Рис.4 Появление новой базы данных в навигаторе

На этот момент MySQL создала только структуру базы данных. Вы можете теперь добавить таблицы в базу данных, а также представления, хранимые процедуры и функции.

Использование MySQL Workbench GUI для создания таблицы

Вы также можете использовать MySQL Workbench GUI, чтобы добавить таблицу в базу данных. В этом случае начните с выбора узла базы данных travel в навигаторе. Вам может потребоваться выполнить двойной щелчок на узле, чтобы выбрать его. При выборе имя базы данных должно быть выделено жирным. Когда база данных выбрана, щелкните на кнопке создания таблицы на панели инструментов Workbench. (Кнопка выглядит как стандартная иконка таблицы и имеет всплывающую подсказку Create a new table in the active schema in connected server.) При щелчке на кнопку Workbench откроет вкладку Table, как показано на рис.5.

Рис.5 Добавление таблицы в Workbench GUI

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

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

MySQL также поддерживает другие движки хранилища, такие как MyISAM, MEMORY, CSV и ARCHIVE. Каждый из них имеет специфические характеристики и область использования. Сейчас я рекомендую вам принять по умолчанию InnoDB, пока вы лучше не поймете различие между ними. Я также рекомендую почитать документацию MySQL о различных типах движков.

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

Узнав основы, вы можете добавить первый столбец, который будет называться manufacturer_id. Он будет также первичным ключом и включать опцию AUTO_INCREMENT, которая скажет MySQL автоматически генерировать уникальное число для значения этого столбца, аналогично свойству IDENTITY в SQL Server.

  • PK. Конфигурирует столбец как первичный ключ.
  • NN. Конфигурирует столбец как NOT NULL.
  • UN. Конфигурирует INT базы данных как UNSIGNED.
  • AI. Конфигурирует столбец с опцией AUTO_INCREMENT.

Знаковость целого влияет на диапазон поддерживаемых значений. Рассмотрим тип данных INT. Если столбец определен как знаковый тип данных INT, значения в столбце должны быть в диапазоне между -2147483648 и 2147483647. Однако, если тип данных является беззнаковым, значения должны находиться между 0 и 4294967295. Если вы знаете, что столбец никогда не будет хранить отрицательных значений, вы можете определить его как беззнаковый, чтобы обеспечить больший диапазон для положительных целых чисел.

По мере конфигурирования столбца Workbench обновляет установки опций в разделе ниже сетки. Этот раздел внизу отражает установки столбца, выбранные в сетке, и может быть полезным при определении нескольких столбцов. Этот раздел также предоставляет несколько дополнительных необязательных опций. Например, вы можете добавить комментарий, относящийся к выбранному столбцу. Можно также установить набор символов и коллацию на уровне столбца (для символьных типов данных).

На рис.6 показан столбец manufacturer_id как он пока определен. Обратите внимание, что нижний раздел отражает все установки, определенные в сетке столбца.

Рис.6 Добавление столбца в определение таблицы

  • Столбец manufacturer сконфигурирован имеющим тип VARCHAR(50) и NOT NULL.
  • Столбец create_date сконфигурирован имеющим тип TIMESTAMP и NOT NULL. Для него установлено значение по умолчанию CURRENT_TIMESTAMP - системная функция, которая возвращает текущую дату и время.
  • Столбец last_update сконфигурирован имеющим тип TIMESTAMP и NOT NULL. Для него установлено значение по умолчанию, которое включает функцию CURRENT_TIMESTAMP наряду с опцией ON UPDATE CURRENT_TIMESTAMP, которая генерирует значение, когда происходит обновление таблицы.

Рис.7 Добавление нескольких столбцов в определение новой таблицы

Осталось сделать еще один шаг, чтобы завершить определение таблицы. Для этого вам нужно перейти на вкладку Options и установить начальное значение AUTO_INCREMENT. Я использовал 1001, как показано на рис.8. В результате первой добавленной в таблицу записи будет присвоено значение manufacturer_id, равное 1001, а каждая последующая строка будет иметь приращение 1.

Рис.8 Установка начального значения опции AUTO_INCREMENT

Как можно видеть, имеется множество других табличных опций, которые вы можете сконфигурировать, и есть другие вкладки, на которых конфигурируются дополнительные параметры. Но сейчас мы на этом остановимся и добавим таблицу в базу данных. Для этого щелкните кнопку Apply, которая запустит мастера Apply SQL Script to Database (применить скрипт в базе данных), показанного на рис.9.

Рис.9 Проверка оператора CREATE TABLE

На этом экране вы можете проверить сгенерированный оператор SQL и, если желаете, выбрать алгоритм и тип блокировки. Вы можете непосредственно тут отредактировать оператор SQL. (Только не допустите ошибок.)

Обратите внимание, что столбец manufacturer_id имеет тип INT (беззнаковый) и определен как первичный ключ. Он также включает опцию AUTO_INCREMENT. Начальное значение AUTO_INCREMENT, 1001, определено как опция таблицы, наряду с движком хранилища InnoDB. Отметим также, что столбцы create_date и last_update включают определенные предложения DEFAULT (значения по умолчанию).

Для завершения процесса создания таблицы просто щелкните Apply, а затем Finish на следующем экране. Затем вы сможете проверть в навигаторе, что таблица была создана, что показано на рис.10.

Рис.10 Просмотр новой таблицы в навигаторе

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

Использование SQL для создания таблицы в базе данных MySQL

Функционал Workbench GUI может быть удобен для создания объектов базы данных, особенно, если вы новичок в MySQL или разработке баз данных. Он также может быть полезен в понимании различных опций, доступных при создании объекта. Однако большинство разработчиков предпочитает самим писать код SQL и, если вы уже имеете опыт работы с SQL, вам, вероятно, не составит труда адаптироваться к MySQL.

Имея это в виду, на следующем шаге мы создадим вторую таблицу в базе данных travel. Для этого вы можете использовать следующий оператор CREATE TABLE:

CREATE TABLE IF NOT EXISTS airplanes ( 
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=101;

По большей части CREATE TABLE придерживается стандарта SQL. Эта таблица подобна той, которую я создавал в первой статье этой серии. Она также использует некоторые элементы, аналогичные элементам таблицы manufacturers, созданной выше. Таблица содержит девять столбцов с разными типами данных и опциями, хотя все столбцы сконфигурированы как NOT NULL.

Стоит отметить один момент, связанный с столбцом max-weight, который имеет тип данных MEDIUMINT (беззнаковый). Вы помните, что этот тип данных лежит между типами данных SMALLINT и INT с точки зрения поддерживаемого диапазона чисел. Таким образом, вы имеете больше возможностей градации при работе с целыми значениями. Ни SQL Server, ни Oracle не поддерживают тип данных MEDIUMINT.

То, что вы еще не видели (по крайней мере, в этой и предыдущей статьях), это ограничение внешнего ключа (foreign key), которое определено для столбца manufacturer_id. Внешний ключ ссылается на столбец manufacturer_id в таблице manufacturers. Ограничение гарантирует, что любое значение, добавленное в таблицу airplanes, должно существовать в таблице manufacturers. Если вы попытаетесь добавить другое значение, то получите ошибку.

Определение таблицы также включает две табличных опции. Опция ENGINE задает InnoDB в качестве движка хранилища, а опция AUTO_INCREMENT устанавливает начальное значение в 101.

На данный момент оператор CREATE TABLE можно считать достаточно полным, поэтому вы можете двинуться дальше и выполнить его в Workbench. Затем вы можете увидеть таблицу в навигаторе, как показано на рис.11.

Рис.11 Просмотр таблицы airplanes в навигаторе

Как видно, под узлом Foreign Keys в навигаторе присутствует внешний ключ. Обратите внимание, что MySQL также добавил индекс для внешнего ключа, и дал ему такое же имя - foreign key. Мы обсудим индексы позже в этой серии статей.

Изменение определения таблицы в базе данных MySQL

Вы можете также использовать SQL для модификации определения таблицы в MySQL. Например, следующий оператор ALTER TABLE добавляет два столбца в таблицу airplanes:

ALTER TABLE airplanes 
ADD COLUMN wingspan DECIMAL(5,2) NOT NULL AFTER max_weight,
ADD COLUMN plane_length DECIMAL(5,2) NOT NULL AFTER wingspan;

Оба столбца имеют тип DECIMAL(5,2). Это означает, что каждый столбец может хранить до 5 цифр с двумя десятичными знаками.

Каждое определение столбца также включает предложение AFTER, которое определяет, куда добавить столбец в определении таблицы. Например, предложение AFTER в определении столбца wingspan указывает, что столбец должен быть добавлен после столбца weight, а предложение AFTER в определении столбца plane_length говорит, что этот столбец должен быть добавлен после столбца wingspan.

Когда вы выполняете этот оператор ALTER TABLE, MySQL обновит соответствующим образом таблицу airplanes. Вы можете затем увидеть эти новые столбцы в навигаторе.

Вы можете использовать также Workbench GUI для изменения определения таблицы. Для этого выполните щелчок правой кнопкой на таблице, а затем щелкните Alter Table - откроется вкладка Table. Здесь вы можете изменять определения столбов или опции таблицы. Вы можете также добавлять или удалять столбцы. На рис.12 показана вкладка Table с выбранными столбцами wingspan и plane_length, это те столбцы, которые вы только что добавили выше.

Рис.12 Простмотр новых столбцов в редакторе таблиц

Следующим шагом будет добавление сгенерированного столбца в таблицу. Сгенерированный столбец - это столбец, значение которого вычисляется с помощью выражения, подобно вычисляемым столбцам в базах данных SQL Server и Oracle.

Для добавления столбца выполните двойной щелчок на первой ячейке в первой пустой строке сетки таблицы (ниже определения столбца last_update), а затем напечатайте имя столбца - parking_area. В той же строке напечатайте тип данных INT, выберите опцию G (что означает GENERATED) и напечатайте wingspan*plane_length в столбце Default/Expression. Выражение умножает значение размаха крыльев на значение длины самолета для получения общей площади.

Когда вы создаете сгенерированный столбец в GUI, Workbench автоматически выбирает опцию Virtual в области подробностей столбца (внизу вкладки). Это означает, что значения столбца будут генерироваться по требованию, а не при сохранении в базе данных. Опция Stored делает обратное. Значение вычисляется, когда строка вставляется в таблицу, при этом значение сохраняется пока строка не будет обновлена или удалена. Для этой статьи я использовал опцию Stored.

После создания столбца вы можете перенести его в новое местоположение в списке столбцов, перетягивая его в желаемую позицию. В этом случае я переместил столбец parking_area после столбца plane_length, как показано на рис.13.

Рис.13 Добавление сгенерированного столбца в таблицу airplanes

Это все, что вам требуется сделать для добавления сгенерированного столбца в таблицу. Для завершения процесса щелкните Apply, что приведет к запуску мастера Apply SQL Script to Database. Здесь вы можете проверить скрипт SQL, как показано на рис.14.

Рис.14 Проверка нового столбца, добавленного в таблицу airplanes

Когда Workbench генерирует оператор ALTER TABLE, он добавляет предложение GENERATED ALWAYS AS, чтобы показать, что это сгенерированный столбец. (Ключевые слова GENERATED ALWAYS не являются обязательными, и вы можете опустить их при создании своего собственного оператора SQL.) Кроме того, предложение включает вычисляемое выражение в скобках.

Workbench добавляет также ключевое слово STORED к определению столбца, показывающее, что вычисляемые значения должны быть сохранены, а не вычисляться по требованию. Еще определение включает предложение AFTER, указывающее, что столбец должен быть добавлен после столбца plane_length.

Если все нормально, щелкните еще раз Apply, а затем Finish, чтобы завершить работу мастера. затем вы можете подтвердить обновление таблицы в навигаторе.

Удаление таблицы из базы данных MySQL

Как и при других DDL действиях в Workbench, вы можете использовать SQL или GUI для удаления таблицы из базы данных. Например, вы можете удалить таблицу airplanes, выполнив следующий оператор DROP TABLE, который включает дополнительное предложение IF EXISTS:

DROP TABLE IF EXISTS airplanes;

Вы можете также удалить таблицу в навигаторе. Для этого щелкните правой кнопкой на таблице, а затем щелкните Drop Table. Это приведет к появлению диалогового окна Drop Table, показанного на рис.15. Щелкните Drop Now для удаления таблицы.

Рис.15 Удаление таблицы в Workbench

Обратите внимание, что диалог включает также опцию Review SQL. Щелкните её, если вы захотите вместо этого просмотреть оператор DROP TABLE, который сгнерировал Workbench. Вы сможете затем выполнить отсюда этот оператор.

Работа с таблицами в базе данных MySQL

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

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Как заполнить таблицу в mysql

Создав таблицу, нужно позаботиться об ее заполнении. Для этого предназначены команды LOAD DATA и INSERT .

Предположим, ваши записи соответствуют приведенным в этой таблице (обратите внимание: MySQL принимает даты в формате ГГГГ-ММ-ДД ; возможно, к такой записи вы не привыкли).

name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1998-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29

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

Создайте текстовый файл с именем `pet.txt', содержащий по одной записи в каждой строке (значения столбцов должны быть разделены символами табуляции и даны в том порядке, который был определен командой CREATE TABLE ). Незаполненным полям (например, неизвестный пол или даты смерти живых на сегодняшний день животных), можно присвоить значение NULL . В текстовом файле это значение представляется символами \N . Например, запись для птицы Whistler должна выглядеть примерно так (между значениями должны располагаться одиночные символы табуляции):

name owner species sex birth death
Whistler Gwen bird \N 1997-12-09 \N

Загрузить файл `pet.txt' в таблицу можно с помощью следующей команды:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Маркер конца строки и символ, разделяющий значения столбцов, можно специально задать в команде LOAD DATA , но по умолчанию используются символы табуляции и перевода строки. Воспринимая их, команда сможет корректно прочитать файл `pet.txt'.

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

mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Обратите внимание на то, что здесь строковые выражения и даты представлены в виде ограниченных кавычками строк. Кроме того, в команде INSERT отсутствующие данные можно прямо заменять на NULL . Пользоваться эвфемизмом \N , как в команде LOAD DATA , нужды нет.

Этот пример наглядно показывает, что если бы с самого начала все данные вносились в базу при помощи нескольких команд INSERT , а не одной команды LOAD DATA , то набирать пришлось бы гораздо больше текста.

Insert в MySQL — добавление данных в таблицу

В статье расскажем, для чего нужна команда INSERT в MySQL и покажем как ей пользоваться на десяти практических примерах.

Зачем нужна команда Insert

Команда INSERT используется для того, чтобы вставлять новые данные в таблицы. Общий и наиболее часто используемый синтаксис выглядит так:

INSERT into table_name [(column1, [, column2] . )] values (values_list) 

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

Создаем базу MySQL в облаке

Прежде чем начать работать с командой Insert, нам нужна база данных MySQL. Чтобы не заниматься долгой установкой и настройкой, мы создадим управляемую БД на платформе Selectel. Если сервер MySQL у вас уже установлен, можете сразу переходить к следующему разделу.

Заходим в личный кабинет, в разделе «Облачная платформа» переходим к «Базам данных». Нажимаем кнопку «Создать кластер».

Создание базы данных в панели Selectel

На следующем экране выбираем параметры новой базы. Выбираем «СУБД» — MySQL. Далее необходимо выбрать конфигурацию сервера: нам будет достаточно 2 vCPU, 4 ГБ оперативной памяти и 32 ГБ диска. Обратите внимание на раздел «Сеть» — у вас должна быть выбрана публичная сеть, чтобы к базе данных можно было подключиться из интернета. Остальные параметры можно оставить по умолчанию.

Конфигурация для нового кластера БД

Немного подождем, пока сервер создается. Когда он перейдет в статус ACTIVE, выберите его и зайдите в раздел настроек. Сначала перейдем на вкладку «Пользователи» и создадим нового пользователя, который сможет подключаться к базе данных. Не забудьте записать имя пользователя и пароль, они будут нужны для подключения.

Создание пользователя

Затем перейдем на следующую вкладку — «Базы данных». Нам нужно создать базу, в которой мы будем работать. Для этого нажмем кнопку «Создать базу данных». Запишите название созданной БД, оно нам будет нужно для подключения.

Создание базы данных MySQL

Когда база создастся, нужно дать нашему пользователю права на нее. На этой же вкладке нажмем кнопку «Добавить» и выберем недавно созданного пользователя.

Добавление доступа к базе

Все, БД настроена и к ней можно подключаться. Чтобы узнать параметры подключения, перейдите на вкладку «Настройки» и там в самом низу в разделе «Подключение» указаны нужные параметры. Вы можете пользоваться консольным клиентом или любой графической утилитой. Мы будем показывать на примере консольного клиента, поэтому строка для подключения будет выглядеть вот так:

mysql --host=109.71.10.30 \ --port=6033 \ --user=chrystal \ --password \ --database=db 

После ввода этой команды консоль попросит нас ввести пароль. Вот и все, мы подключились к БД.

Структура таблиц для примеров

Все дальнейшие команды мы будем показывать на простом примере из трех таблиц. Допустим, мы — сеть фруктовых магазинов. У нас есть таблица со списком магазинов, таблица со списком товаров (фруктов) и таблица, в которой мы записываем какой фрукт в каком магазине продается и по какой цене.

С помощью команды Insert мы будем вставлять в эти таблицы новые данные разными способами. Итак, вот описание наших таблиц и начальные данные в них:

+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | +----+------------------+--------------+ 
+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | +----+------------------+ 
  • sale, таблица наличия товаров и цены:
+----------+----------+-------+-------+ | store_id | fruit_id | price | count | +----------+----------+-------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | +----------+----------+-------+-------+ 

Пример 1: базовое использование команды INSERT

Начнем с самого простого использования команды INSERT в MySQL. Добавим новый фрукт в справочник фруктов. Команда будет выглядеть так:

INSERT INTO fruits VALUES (4, 'Апельсин'); 

Проверяем таблицу фруктов и видим, что появилась новая запись:

+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | +----+------------------+ 

Мы не указывали названия столбцов, в которые хотим вставить данные. Поэтому они вставлялись по порядку, как они расположены в таблице. Поэтому нам нужно было указывать значения для всех столбцов, которые есть в таблице, даже если мы не хотим их заполнять.

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

INSERT INTO stores (id, name) VALUES (4, 'Магазин 4'); 

Обратите внимание, что в таблице stores у нас еще есть столбец с адресом, но мы его не указали. Посмотрим, что вставилось в таблицу:

+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | | 4 | Магазин 4 | NULL | +----+------------------+--------------+ 

Мы видим указанные нами значения, а в поле address вставилось значение NULL.

Пример 2: вставка нескольких строк

За один запрос можно добавлять сразу несколько элементов. Например, мы хотим добавить три новых фрукта. Вместо того, чтобы писать три отдельных команды INSERT, мы воспользуемся вот таким способом записи:

INSERT INTO fruits VALUES (5, 'Киви'), (6, 'Виноград'), (7, 'Груша'); 

Посмотрим результат. Видим, что появилось сразу три новых записи:

+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | +----+------------------+ 

Пример 3: использование SET

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

INSERT INTO sale SET store_id=1, fruit_id=1, price=89, count=3; 

Проверим, что числа вставились именно в те столбцы, которые мы указали:

+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | +----------+----------+--------+-------+ 

Пример 4: вставка с выражением

Во всех предыдущих примерах мы указывали явное значение для столбца. Но в MySQL можно использовать арифметические выражения, например сумму, разность, умножение и так далее. Допустим, мы хотим добавить запись в таблицу sale о том что в магазине № 2 продаются апельсины по 137 рублей. Но потом мы решаем, что хотим сделать скидку 6%. Чтобы не высчитывать ее самим, мы можем использовать такой запрос:

INSERT INTO sale VALUES (2, 4, 137*0.94, 5); 

Мы умножаем 137 рублей на 0.94, это и есть скидка 6%. Проверим что у нас получилось:

+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | | 2 | 4 | 128.78 | 1 | +----------+----------+--------+-------+ 

Пример 5: вставка данных из другой таблицы

В качестве источника данных для вставки можно использовать другую таблицу. Предположим, у нас есть таблица fruits_new, где хранится список фруктов, которые мы заказали у поставщиков, но эти фрукты еще не продаются в наших магазинах. И вот наконец-то нам привезли два новых фрукта, которые мы так давно ждали. Но вместо того, чтобы переписывать эти названия в команду INSERT, мы можем просто использовать таблицу fruits_new и загружать данные из нее. Для этого нам понадобится оператор SELECT:

INSERT INTO fruits SELECT * FROM fruits_new WHERE >Обратите внимание, что мы не указываем названия столбцов: из какого взять и в какой вносить. Поэтому такой способ подойдет только если структура таблиц одинакова, или в таблице-источнике меньше столбцов. Чаще всего используется другой синтаксис, когда столбцы указаны явно:
INSERT INTO fruits (name, id) SELECT name, id FROM fruits_new WHERE >Проверим результат. После выполнения этих двух команд у нас должно появиться две новых строки:
+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | | 8 | Ананас | | 9 | Вишня | +----+------------------+ 

Пример 6: вставка значения по умолчанию

Обычно, если в MySQL для столбца не указано значение, то команда INSERT INTO подставляет NULL. Но если при создании таблицы у этого столбца было указано значение по умолчанию (default), тогда подставится именно оно. В нашей таблице sale у столбца count значение по-умолчанию равно единице. Проверим это, вставив новую запись без указания этого столбца:

INSERT INTO sale (store_id, fruit_id, price) VALUES(4, 7, 50); 

Проверяем результат. Мы не указывали значение для столбца count, но автоматически подставлилось значение 1.

+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | | 2 | 4 | 128.78 | 5 | | 4 | 7 | 50 | 1 | +----------+----------+--------+-------+ 

Пример 7: вставка или обновление при дубликате

В MySQL нельзя добавить строку в таблицу, у которой дублируется первичный ключ (primary key). В наших таблицах stores и fruits поле id — это первичный ключ. Поэтому если мы попробуем добавить в таблицу fruit значение с то MySQL выдаст ошибку.

Но существует похожая на INSERT команда — REPLACE, которая умеет перезаписывать значения. Она работает как INSERT и UPDATE одновременно: если в таблице еще нет записи с таким первичным ключом, команда создаст новую запись; а если уже есть — заменит ее.

REPLACE INTO fruits VALUES (1, 'Лимон'); 

Проверяем результат и видим, что вместо яблока у нас появился лимон с тем же идентификатором.

+----+------------------+ | id | name | +----+------------------+ | 1 | Лимон | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | | 8 | Ананас | | 9 | Вишня | +----+------------------+ 

Пример 8: игнорирование ошибки при вставке

Продолжим предыдущий пример. Допустим, записи в нашу таблицу вставляет бэкенд-сервис приложения. Мы хотим, чтобы сервис попробовал вставить новую запись, но если такой первичный ключ уже существует — ничего не обновлял и просто шел выполнять другой код. Но при совпадении ключей MySQL выдаст ошибку, а код на бэкенде прервется и ее нужно будет обрабатывать в сервисе. Гораздо проще добавить к команде INSERT ключевое слово IGNORE, чтобы при совпадении первичного ключа MySQL не генерировал ошибку:

INSERT IGNORE INTO stores VALUES (1, 'Магазин 10', 'Адрес 10'); 

Обратите внимание на сообщение, которое выдает MySQL, оно будет примерно таким: Query OK, 0 rows affected, 1 warning. Это значит, что запрос выполнился успешно, при этом он не затронул ни одну строки и есть одно предупреждение. Проверим результат: видим, что новых магазинов не появилось, а текущие не изменились.

+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | | 4 | Магазин 4 | NULL | +----+------------------+--------------+ 

Пример 9: вставка записи в определенные партиции

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

  • p0 для записей, у которых count от 0 до 100
  • p1 для записей, у которых count от 101 до 150.

Чтобы вставить данные в первую партицию, воспользуемся командой:

INSERT INTO sale PARTITION (p0) VALUES (1, 9, 130, 10); 

Кроме того, в одной команде мы можем вставлять данные сразу в несколько партиций. Это делается так:

INSERT INTO sale PARTITION (p0, p1) VALUES (4, 8, 90, 10), (3, 7, 50, 120); 

Пример 10: изменение приоритета

В некоторых движках таблиц MySQL (например, в MyISAM, MEMORY и MERGE) можно изменить приоритет вставки данных. Например, можно сделать так, что если таблицу считывает команда SELECT, то команда INSERT будет ждать ее завершения. Это делается с помощью ключевого слова LOW_PRIORITY:

INSERT LOW_PRIORITY INTO sale VALUES (1, 6, 40, 3); 

Если же нужно наоборот поднять приоритет, используется такая команда:

INSERT HIGH_PRIORITY INTO sale VALUES (1, 6, 40, 3); 

Но нужно иметь ввиду, что если к таблице активно идут SELECT-запросы, то команда INSERT с низким приоритетом может ждать своей очереди довольно долго.

Заключение

Мы рассмотрели команду INSERT и познакомились с десятью самыми основными способами ее использования. Вы узнали, как с помощью SQL команды можно добавить новую строку в таблицу или обновить существующую. Зная эти основы, вы можете начать писать INSERT-запросы для своей структуры БД.

ALTER TABLE — изменение таблицы в SQL

Работа с командой UPDATE — как обновить данные в таблице MySQL

Зарегистрируйтесь в панели управления

И уже через пару минут сможете арендовать сервер, развернуть базы данных или обеспечить быструю доставку контента.

Читайте также:

Инструкция

Как создать бота для анализа сообщений в Telegram

Инструкция

Как создать Telegram-бота с помощью библиотеки grammY

22 февраля 2024

Инструкция

Как быстро написать API на FastAPI с валидацией и базой данных

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *