SQL-Ex blog

Это вторая статья в серии, посвященной 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.

Для добавления столбца выполните двойной щелчок на первой ячейке столбца Column Name и напечатайте manufacturer_id. В столбце Datatype для этой строки напечатайте INT или выберите INT из выпадающего списка. Затем отметьте следующие флажки:

• PK. Конфигурирует столбец как первичный ключ.


• NN. Конфигурирует столбец как NOT NULL.


• UN. Конфигурирует INT базы данных как UNSIGNED.


• AI. Конфигурирует столбец с опцией AUTO_INCREMENT.

Что касается опции UNSIGNED, то MySQL позволяет указать, является ли целочисленный тип данных знаковым или беззнаковым. Если знаковый, то значения столбца могут включать отрицательные числа, если беззнаковый, значения не могут включать отрицательные числа. Целочисленные типы данных являются знаковыми по умолчанию. Более старые версии MySQL позволяли конфигурировать типы данных DECIMAL, DOUBLE и FLOAT беззнаковыми, однако эта функция устарела.

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

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

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


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

Вы можете повторить подобный процесс для каждого добавляемого столбца, выбирая тип данных и конфигурационные опции. При задании типа данных вы можете напечатать его или выбрать из выпадающего списка. Однако некоторые типы данных в выпадающем списке, включая TIMESTAMP, сопровождаются скобками, которых быть не должно. Это похоже на баг Workbench. Вы должны будете вручную удалить скобки для типа данных, чтобы это исправить. Вы можете также указать значение по умолчанию для любых определяемых столбцов. Здесь я добавил еще три столбца:

• Столбец manufacturer сконфигурирован имеющим тип VARCHAR(50) и NOT NULL.


• Столбец create_date сконфигурирован имеющим тип TIMESTAMP и NOT NULL. Для него установлено значение по умолчанию CURRENT_TIMESTAMP - системная функция, которая возвращает текущую дату и время.


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

На рис.7 показана вкладка Table после добавления трех столбцов. Сетка содержит строку для каждого столбца, при этом каждая строка показывает конфигурацию столбца.


Рис.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.