Как создать таблицы в sql

MySQL: Создание таблицы (Create Table)

Таблицы создание команды требует:

• Имя таблицы
• Имена полей
• Определений для каждого поля

Вот универсальный синтаксис SQL для создания таблиц MySQL:

CREATE TABLE table_name (column_name column_type);

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

tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( tutorial_id ) );

Вот несколько пунктов, которые нуждаются в пояснении:

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

Создание таблиц из командной строки:

Это легко создать MySQL таблицу из MySQL> подсказка. Вы будете использовать команды SQL создать таблицу чтобы создать таблицу.

Пример:

Вот пример, который создает tutorials_tbl:

root@host# mysql -u root -p Enter password:******* mysql> use TUTORIALS; Database changed mysql> CREATE TABLE tutorials_tbl( -> tutorial_id INT NOT NULL AUTO_INCREMENT, -> tutorial_title VARCHAR(100) NOT NULL, -> tutorial_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( tutorial_id ) -> ); Query OK, 0 rows affected (0.16 sec) mysql>

Создание таблиц с помощью PHP скрипта:

Чтобы создать новую таблицу в любой существующей базы данных необходимо использовать функции PHP функции mysql_query(). Вы будете проходить свой второй аргумент при правильной команды SQL для создания таблицы.

Пример:

Вот пример создания таблицы с помощью PHP скрипта:

echo ‘Connected successfully
‘; $sql = «CREATE TABLE tutorials_tbl( «. «tutorial_id INT NOT NULL AUTO_INCREMENT, «. «tutorial_title VARCHAR(100) NOT NULL, «. «tutorial_author VARCHAR(40) NOT NULL, «. «submission_date DATE, «. «PRIMARY KEY ( tutorial_id )); «; mysql_select_db( ‘TUTORIALS’ ); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not create table: ' . mysql_error()); >echo «Table created successfullyn»; mysql_close($conn); ?>
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL auto_increment, `role_id` int(11) NOT NULL default ‘1’, `username` varchar(25) collate utf8_bin NOT NULL, `password` varchar(34) collate utf8_bin NOT NULL, `email` varchar(100) collate utf8_bin NOT NULL, `banned` tinyint(1) NOT NULL default ‘0’, `ban_reason` varchar(255) collate utf8_bin default NULL, `newpass` varchar(34) collate utf8_bin default NULL, `newpass_key` varchar(32) collate utf8_bin default NULL, `newpass_time` datetime default NULL, `last_ip` varchar(40) collate utf8_bin NOT NULL, `last_login` datetime NOT NULL default ‘0000-00-00 00:00:00’, `created` datetime NOT NULL default ‘0000-00-00 00:00:00’, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ; //С двумя ключами: CREATE TABLE IF NOT EXISTS `ci_sessions` ( session_id varchar(40) DEFAULT ‘0’ NOT NULL, ip_address varchar(16) DEFAULT ‘0’ NOT NULL, user_agent varchar(120) NOT NULL, last_activity int(10) unsigned DEFAULT 0 NOT NULL, user_data text NOT NULL, PRIMARY KEY (session_id), KEY `last_activity_idx` (`last_activity`) );

Шпаргалка

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.

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

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

Комментарии

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

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

Создание таблиц и работа с ними

17. Создание таблиц и работа с ними 17.1. Создание таблиц Язык SQL используется не только для манипулирования с данными, но и позволяет создавать таблицы и работать с ними. Существует два способа создания таблиц: · большинство СУБД имеют инструментарий администратора, который можно использовать для интерактивного создания таблиц базы данных и управления ими; · таблицы можно создавать и манипулировать ими посредством операторов языка SQL. Для создания таблиц программным способом используется оператор CREATE TABLE. При использовании интерактивного инструментария в действительности вся работа выполняется операторами SQL. Синтаксис оператора CREATE TABLE может различаться для разных реализаций SQL. Чтобы создать таблицу с помощью оператора CREATE TABLE нужно указать следующие данные:

Рекомендуемые материалы

Тест 1 — Основы программирования Си
Программирование и алгоритмизация
Лабораторная работа №2 — РК6
Информатика
299 90 руб.
Пак ответов итоговый тест
Программирование и алгоритмизация
Ответы на Аттестацию официального партнера amoCRM 2023
Информатика
Тест 2 верен на 95%
Программирование и алгоритмизация

Расчетно-графическая работа по курсу «Программирование». Семинар 1. Разработка алгоритмов и их кодирование на алгоритмическом языке СИ. Вариант 8

Программирование и алгоритмизация

· имя новой таблицы, которое вводится после слов CREATE TABLE; · имена и определения столбцов таблицы, разделенные запятыми; · в некоторых СУБД также требуется, чтобы было указано место размещения таблицы. Пример создания таблицы продуктов. CREATE TABLE Products1 ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price CURRENCY NOT NULL, prod_desc VARCHAR(255) NULL ); Когда создается новая таблица, то указанное имя не должно существовать в СУБД, иначе будет выдано сообщение об ошибке. SQLтребует, чтобы вначале вручную удалили таблицу, а затем вновь создали ее, а не просто переписали. Все столбцы заключаются в круглые скобки. За именем столбца размещается тип данных. При разработке таблиц необходимо обращать особое внимание на используемые типы данных. 17.2. Типы данных Типы данных и их название являются одним из основных источников несовместимости в SQL. К сожалению, в разных СУБД используются разные типы данных. Основные типы данных обычно поддерживаются всеми СУБД. Но даже если названия типа данных звучит одинаково, пониматься под одним и тем же типом данных в разных СУБД может не одно и то же. В Access 2007 предусмотрено 10 типов данных (раньше было 9)

Тип данных	Описание	Ограничение
Текстовый	Алфавитно-цифровые данные (текст и числа)	Может храниться до 255 знаков
Поле MEMO	Алфавитно-цифровые данные (текст и числа)	Может храниться до 2Гб данных (предельный размер для всех баз данных Access) при программном заполнении полей. При вводе данных вручную в поле можно ввести 65535 знаков.
Числовой	Числовые данные	В полях с типом данных «Числовой» используется параметр Список полей, управляющий размером значения, которое может содержать поле. Размер поля можно задавать равным 1, 2, 4, 8 или 16 байтам.
Дата/время	Значение даты и времени	Приложение Access хранит все значения даты и времени в виде 8-байтовых целых чисел с двойной точностью.
Денежный	Денежные данные	Данные хранятся в виде 8-байтовых чисел с точностью до четырех знаков после запятой. Этот тип данных используется для хранения финансовых данных и в тех случаях, когда значения не должны округляться.
Счетчик	Уникальные значения, создаваемые приложением Access при введении новой записи	Данные хранятся в виде 4-байтовых значений, обычно используются в первичных ключах.
Логический	Логические данные – «истина» или «ложь»	Используется – 1 для всех значений «ДА» и 0 для всех значений «Нет».
Поле объекта OLE	Изображения, документы, диаграммы и другие объекты из приложений Office и других программ Windows	Может храниться до 2 ГБ данных. Поля с типом данных «Поле объекта OLE» создают растровые изображения исходных документов или других объектов, а затем отображают их в полях таблиц и элементах управления форм или отчетов в базе данных. Чтобы в Access выводились эти изображения, необходимо, чтобы на компьютере, использующем базу данных, был зарегистрирован OLE-сервер (программа, поддерживающая этот тип файлов). Если для данного типа файлов OLE-сервер не зарегистрирован, отображается значок поврежденного изображения. Такая проблема бывает связана с некоторыми типами изображений, чаще всего с форматом JPEG. Как правило, а ACCDB-файлах вместо типа данных «Поле объекта OLE» используется тип «Вложение». Поле с таким типом данных более рационально используют место для хранения и не имеют ограничений, связанных с отсутствием зарегистрированных OLE-серверов.
Гиперссылка	Веб-адреса	Может храниться до 1ГБ данных. Это могут быть ссылки на веб-узлы, на узлы или файлы интрасети или локальной сети, а также на узлы или файлы локального компьютера.
Вложение	Файлы любого поддерживаемого типа	Новая функциональная возможность ACCDB-файлов Access 2007. В записи базы данных можно вкладывать изображения,файлы электронных таблиц, документы, диаграммы и другие файлы поддерживаемых типов точно так же, как в сообщения электронной почты. Можно также просматривать и редактировать вложенные файлы в зависимости от параметров, заданных разработчиком базы данных для поля с типом данных «Вложение». Эти поля дают большую свободу действий, чем поля с типом данных «Поле объекта OLE». И более рационально используют место для хранения, поскольку не создают растровые изображения исходного файла

Рассмотрим применяемые типы данных в СУБД и их соответствие в Access. Строковые данные Часто используются данные типа строки, строковые данные. Строки могут быть двух типов – строки фиксированной длины и строки переменной длины. Для строк фиксированной длины отводится столько байт, сколько определено в описании таблицы. В поле столбца будет храниться число отведенных символов, при необходимости текст строки дополняется пробелами. В строках переменной длины можно хранить столько символов, сколько позволяет максимально хранить данная СУБД. В строках сохраняются только указанные данные и никаких дополнительных. Поля с фиксированной длиной обеспечивают: · повышение производительности при сортировке и манипулировании данными; · позволяют индексировать столбцы, многие СУБД не способны индексировать столбцы с данными переменной длины. Строковые данные

Тип данных	Описание	Access
CHAR	Строка фиксированной длины, состоящая из 1-255 символов. Размер должен быть определен во время создания	CHAR — текстовый
NCHAR	Особая форма типа CHAR, разработанная с целью поддержки многобайтовых символов или символов Unicode.	—
NVARCHAR	Специальная форма типа данных TEXT, разработанная с целью поддержки многобайтовых символов.	—
TEXT (другие названия LONG, MEMO, VARCHAR)	Текст переменной длины	VARCHAR – до 255 символов MEMO – неограниченно

Примечание. Если число используется для вычислений, то его лучше хранить в столбце, предназначенном для числовых данных. Если оно используется как строковый литерал, то лучше хранить в столбце с данными строкового типа. Например, код 01234 в числовом поле будет сохранено как 1234. Числовой тип данных Числовые типы данных предназначены для хранения чисел. В большинстве СУБД поддерживаются многие числовые типы данных, каждый из которых предназначен для хранения чисел определенного диапазона. Числовые типы данных

Тип данных	Описание	Access
BIT	Одноразрядное значение, 0 или 1	BINARY — логический
DECIMAL (NUMERIC)	Значения с фиксированной или плавающей запятой различной степени точности	NUMERIC – двойное с плавающей точкой, Авто
FLOAT (NUMBER)	Значения с плавающей запятой	FLOAT (NUMBER) – двойное с плавающей точкой, Авто
INT (INTEGER)	4-разрядные целые значения, поддерживаются числа от -2147483648 до 2147483647	INT (INTEGER, LONG) – длинное целое, Авто
REAL	4-разрядные значения с плавающей запятой	REAL –одинарное с плавающей точкой, Авто
SMALLINT	2-разрядные целые значения, поддерживаются числа от -32768 до 32767	SMALLINT – целое, Авто
TINYINT	1-байтовые целые значения, поддерживаются числа от 0 до 255	—

Денежный тип данных Денежный тип данных

Тип данных	Описание	Access
MONEY (CURRENCY)	Относится к типу DECIMAL, но со специфическими диапазонами, делающими их удобными для хранения денежных значений	MONEY CURRENCY – денежный, Авто

Типы данных даты и времени Типы данных даты и времени

Тип данных	Описание	Access
DATE	Значение даты	DATE – дата/время
DATETIME (TIMESTAMP)	Значения даты и времени	DATETIME (TIMESTAMP) — дата/время
SMALLDATETIME	Значения даты и времени с точностью до минуты (без значений секунд или миллисекунд)	—
TIME	Значение времени	TIME – дата/время

Не существует стандартного способа указания даты, который подходил бы к любой СУБД. В большинстве реализаций приемлем формат типа 2009-03-20 или MAR 20th 2009. Поскольку в каждый СУБД используется свой формат представления даты, ODBC (Open DataBase Connectivity — программный интерфейс (API) доступа к базам данных) создал свой собственный формат , который способен работать с любой СУБД при использовании ODBC. Формат ODBC выглядит так: для значений дат, для значений времени и для значений даты и времени. Двоичные типы данных Двоичные типы данных относятся к числу наименее совместимых и реже всего используются. В отличие от других типов данных двоичные типы данных могут содержать любые данные, даже информацию в двоичном виде, такую как графические изображения, мультимедиа и документы текстового процессора. Двоичные типы данных

Тип данных	Описание	Access
BINARY	Двоичные данные фиксированной длины (максимальная длина может быть от 255 байт до 8000 байт)	BINARY – двоичный, 510 разрядов
LONG RAW	Двоичные данные переменной длины объемом до 2 Гбайт	—
RAW (BINARY)	Двоичные данные фиксированной длины объемом до 255 байт	BINARY – двоичный, 510 разрядов
VARBINARY	Двоичные данные переменной длины, максимальный объем от 255 байт до 8000 байт	VARBINARY — двоичный, 510 разрядов

17.3. Работа со значениями NULL Использование NULL подразумевает, что в столбце не должно содержаться никакое значение или неизвестно значение, которое должно быть в столбце. Столбец, в котором разрешается присутствие значения NULL, позволяет также добавлять в таблицу строки, в которых не предусмотрено значение для данного столбца. Столбец, в котором не разрешается присутствие значения NULL, не принимает строки с отсутствующим значением. Для этого столбца всегда потребуется вводить какое-то значение при добавлении или обновлении строк. Каждый столбец таблицы может быть или пустым (NULL), или не пустым (NOT NULL), и это его состояние оговаривается в определении таблицы во время ее создания. Рассмотрим пример. CREATE TABLE Orders1 ( order_num INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL ); В этом примере все три столбца являются необходимыми, каждый содержит ключевое слово NOT NULL, которые будут препятствовать добавлению в таблицу столбцов с отсутствующим значением. В следующем примере создадим таблицу, в которой могут быть столбцы обеих разновидностей. CREATE TABLE Vendors1 ( vend_id CHAR(10) NOT NULL, vend_name CHAR(50) NOT NULL, vend_address CHAR(50), vend_city CHAR(50), vend_state CHAR(5), vend_ZIP CHAR(10), vend_country CHAR(50) ); В случае допуска в столбце NULL описатель NULL можно не указывать. Значение NULL является значением по умолчанию. Во многих СУБД отсутствие ключевых слов NOT NULL трактуется как NULL. С другой стороны, например, в СУБД DB2 наличие ключевого слова NULL является обязательным. Первичные ключи представляют собой столбцы, значения которых уникально идентифицирует каждую строку таблицы. Поэтому столбцы, которые допускают отсутствие значений, не могут использоваться в качестве уникальных идентификаторов. 17.4. Определение значений по умолчанию Язык SQL позволяет определять значения по умолчанию, которые будут использованы в том случае, если при добавлении строки какое-то ее значение не указано. Значения по умолчанию определяются с помощью ключевого слова DEFAULT в определениях столбца оператора CREATE TABLE. Рассмотрим пример. CREATE TABLE OrderItems1 ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL(8,2) NOT NULL ); Столбец quantity содержит количество каждого предмета в заказе. DEFAULT 1 в описании столбца предписывает СУБД указывать количество, равное 1, если не указано иное. Значение по умолчанию часто используется для хранения в столбцах даты и денежных единиц. Например, системная дата может быть использована как дата по умолчанию путем указания функции или переменной, используемой для ссылки на системную дату. Например, в SQL Server – DEFAULT GETDATE(), в MySQL – DEFAULT CURRENT_DATE(). В Access DEFAULT не работает. 17.5. Обновление таблиц Для обновления определения таблицы, следует воспользоваться оператором ALTER TABLE. Этот оператор позволяет изменить таблицу, при этом необходимо учитывать. · В идеальном случае структура таблицы вообще не должна меняться после того, как в таблицу введены данные. Требуется немало времени, чтобы предугадать будущие потребности в процессе разработки таблиц, чтобы позже не потребовалось вносить в их структуру изменения. · Все СУБД позволяют добавлять в уже существующие таблицы столбцы, но некоторые ограничивают типы данных, которые могут быть добавлены. · Многие СУБД не позволяют удалять или изменять столбцы в таблице. · Большинство СУБД разрешабт переименовывать столбцы. · Многие СУБД налагают серьезные ограничения на изменения, которые могут быть сделаны по отношению к заполненным столбцам, и несколько меньшие – по отношению к незаполненным. Чтобы изменить таблицу посредством оператора ALTER TABLE, нужно ввести следующую информацию: · Имя таблицы, подлежащей изменению, после ключевых слов ALTER TABLE, таблица с таким именем должна существовать. · Список изменений, которые должны быть сделаны. Рассмотрим пример добавления столбцов в таблицу. ALTER TABLE Vendors ADD vend_phone CHAR(20); В таблицу Vendors будет добавлен столбец vend_phone. Другие операции изменения, например, изменение или удаление столбцов, введение ограничений или ключей, требуют похожего синтаксиса. Например, удаление столбца, будет работать не во всех СУБД. В Access работает. ALTER TABLE Vendors DROP COLUMN vend_phone; Сложные изменения структуры таблицы обычно выполняются вручную м включают следующие шаги. · Создание новой таблицы с новым расположением столбцов. · Использование оператора INSERT SELECT для копирования данных из старой таблицы в новую. При необходимости используются функции преобразования и вычисляемые поля. · Проверка того факта, что новая таблица содержит нужные данные. · Переименование старой таблицы или удаление ее. · Присвоение новой таблице имени, которое ранее принадлежало старой таблицы. · Восстановление триггеров, хранимых процедур, индексов и внешних ключей, если это необходимо. 17.6. Удаление таблиц Удаление самих таблиц, а не только их содержимого выполняется с помощью оператора DROP TABLE. Пример, удалим таблицу CustCopy. Вместе с этой лекцией читают «2. Автоматизация добычных участков». DROP TABLE CustCopy; Надо быть аккуратным, так как невозможно возвратиться к прежнему состоянию – в результате применения этого оператора таблица будет безвозвратно удалена. Во многих СУБД применяются правила, препятствующие удалению таблиц, связанных с другими таблицами. Если эти правила действуют, то при применении оператора DROP TABLE по отношению к таблице, которая связана с другой таблицей, СУБД блокирует проведение этой операции до тех пор, пока не будет удалена данная связь 17.7. Переименование таблиц В разных СУБД переименование таблиц осуществляется по-разному. Не существует жестких, устоявшихся стандартов на выполнение этой операции. В СУБД MySQL, Oracle применяется оператор RENAME. В СУБД SQL Server можно использовать хранимую процедуру sp_rename. Основной синтаксис для всех операций переименования требует указания старого и нового имен.

Создаем таблицу в SQL

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

Для создания таблицы используется SQL-оператор CREATE TABLE .

Синтаксис

Базовый синтаксис для создания таблицы выглядит так:

CREATE TABLE имя_таблицы ( имя_столбца1 тип_данных ограничения, имя_столбца2 тип_данных ограничения, 
. );

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

-- Синтаксис для БД MySQL 
CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE ); -- Синтаксис для БД SQL Server CREATE TABLE persons ( id INT NOT NULL PRIMARY KEY IDENTITY(1,1), name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE );

Тип данных

Выше мы показали, как создать таблицу persons с четырьмя столбцами id , name , birth_date и phone . Обратите внимание, что за именем каждого столбца указан его тип данных. Так мы указываем, какой тип данных будет храниться в столбце: целое число, строка, дата и т. д.

Некоторые типы данных могут быть объявлены с параметром длины, который указывает, сколько символов может храниться в столбце. Например, VARCHAR(50) будет хранить до 50 символов.

Примечание. Тип данных столбцов может отличаться в зависимости от системы базы данных. Например, целочисленный тип в MySQL и SQL Server называется INT , а в Oracle — NUMBER .

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

Тип данных	Хранит
INT	Числовые значения в диапазоне от -2147483648 до 2147483647
DECIMAL	Десятичные значения.
CHAR	Строки фиксированной длины с максимальным размером 255 символов.
VARCHAR	Строки переменной длины с максимальным размером 65 535 символов.
TEXT	Строки с максимальным размером 65 535 символов.
DATE	Значения даты в формате ГГГГ-ММ-ДД.
DATETIME	Значения даты и времени в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС.
TIMESTAMP	значения временных меток — количество секунд, прошедших с эпохи Unix (‘1970-01-01 00:00:01’ UTC).

Ограничения

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

Некоторые из ограничений мы уже использовали в предыдущем примере:

• Ограничение NOT NULL гарантирует, что поле не может иметь значение NULL .
• Ограничение PRIMARY KEY отмечает соответствующее поле как первичный ключ таблицы.
• Атрибут AUTO_INCREMENT — расширение MySQL, которое предписывает MySQL автоматически присваивать значение этому полю, если оно не определено, путем увеличения предыдущего значения на 1. Доступно только для числовых полей.
• Ограничение UNIQUE гарантирует, что каждая строка для столбца должна иметь уникальное значение.

Более подробно об ограничениях в SQL мы поговорим в следующей статье.

Примечание. Microsoft SQL Server использует свойство IDENTITY для выполнения функции автоинкремента. Значение по умолчанию — IDENTITY(1,1) — означает, что начальное значение и инкремент равны 1.

Совет. Вы можете выполнить инструкцию DESC имя_таблицы; , чтобы посмотреть информацию о столбцах или структуру любой таблицы в базах данных MySQL и Oracle. Аналогичная инструкция в SQL Server — EXEC sp_columns имя_таблицы; .

Используем IF NOT EXISTS

Если вы попытаетесь создать таблицу, которая уже существует в базе данных, вы получите сообщение об ошибке. Чтобы избежать этого в MySQL, можно использовать IF NOT EXISTS , как показано ниже:

CREATE TABLE IF NOT EXISTS persons ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, birth_date DATE, phone VARCHAR(15) NOT NULL UNIQUE );

Совет. Если вы хотите увидеть список таблиц внутри выбранной базы данных, используйте команду SHOW TABLES; в командной строке MySQL.

СodeСhick.io — простой и эффективный способ изучения программирования.

2023 © ООО «Алгоритмы и практика»