Для чего в sql применяется инструкция create

SQL CREATE TABLE

Инструкция CREATE TABLE используется для создания новой таблицы в базе данных.

Синтаксис CREATE TABLE

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
.
);

Параметры столбцов задают имена столбцов таблицы.

Параметр datatype указывает тип данных, которые может содержать столбец (например, varchar, integer, date и т.д.).

Совет: Для получения обзора доступных типов данных перейдите Справочник Типы данных.

Пример CREATE TABLE

В следующем примере создается таблица «Persons», содержащая пять столбцов: PersonID, LastName, FirstName, Address, и City:

Пример

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Столбец PersonID имеет тип int и будет содержать целое число.

Столбцы LastName, FirstName, Address, and City имеют тип varchar и будут содержать символы, а максимальная длина этих полей составляет 255 символов.

Пустая таблица «Persons» теперь будет выглядеть так:

PersonID	LastName	FirstName	Address	City

Совет: Пустая таблица «Persons» теперь может быть заполнена данными с помощью инструкции SQL INSERT INTO.

Создать таблицу, используя другую таблицу

Копия существующей таблицы также может быть создана с помощью команды CREATE TABLE.

Новая таблица получает те же определения столбцов. Можно выбрать все столбцы или отдельные столбцы.

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

Синтаксис CREATE TABLE

CREATE TABLE new_table_name AS
SELECT column1, column2.
FROM existing_table_name
WHERE . ;

Следующий SQL создает новую таблицу под названием «TestTables» (которая является копией таблицы «Customers»):

Пример

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Мы только что запустили
SchoolsW3 видео

курс сегодня!

Сообщить об ошибке

Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:

Ваше предложение:

Спасибо Вам за то, что помогаете!

Ваше сообщение было отправлено в SchoolsW3.

Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.

Transact-SQL — создание таблиц

Инструкция CREATE TABLE создает новую таблицу базы данных со всеми соответствующими столбцами требуемого типа данных. Далее приводится базовая форма инструкции CREATE TABLE:

Параметр table_name — имя создаваемой базовой таблицы. Максимальное количество таблиц, которое может содержать одна база данных, ограничивается количеством объектов базы данных, число которых не может быть более 2 миллиардов, включая таблицы, представления, хранимые процедуры, триггеры и ограничения. В параметрах col_name1, col_name2, . указываются имена столбцов таблицы, а в параметрах type1, type2, . — типы данных соответствующих столбцов.

Имя объекта базы данных может обычно состоять из четырех частей, в форме:

[server_name.[db_name.[schema_name.]]]object_name

Здесь object_name — это имя объекта базы данных, schema_name — имя схемы, к которой принадлежит объект, а server_name и db_name — имена сервера и базы данных, к которым принадлежит объект. Имена таблиц, сгруппированные с именем схемы, должны быть однозначными в рамках базы данных. Подобным образом имена столбцов должны быть однозначными в рамках таблицы.

Рассмотрим теперь ограничение, связанное с присутствием или отсутствием значений NULL в столбце. Если для столбца не указано, что значения NULL разрешены (NOT NULL), то данный столбец не может содержать значения NULL, и при попытке вставить такое значение система возвратит сообщение об ошибке.

Как уже упоминалось, объект базы данных (в данном случае таблица) всегда создается в схеме базы данных. Пользователь может создавать таблицы только в такой схеме, для которой у него есть полномочия на выполнение инструкции ALTER. Любой пользователь с ролью sysadmin, db_ddladmin или db_owner может создавать таблицы в любой схеме.

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

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

В примере ниже показано создание всех таблиц базы данных SampleDb. (База данных SampleDb должна быть установлена в качестве текущей базы данных.)

USE SampleDb; CREATE TABLE Department ( Number CHAR (4) NOT NULL, DepartmentName NCHAR (40) NOT NULL, Location NCHAR (40) NULL ); CREATE TABLE [dbo].[Project] ( [Number] CHAR (4) NOT NULL, [ProjectName] NCHAR (15) NOT NULL, [Budget] FLOAT (53) NULL ); CREATE TABLE dbo.Employee ( Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL ); CREATE TABLE dbo.Works_on ( EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL );

Кроме типа данных и наличия значения NULL, в спецификации столбца можно указать следующие параметры:

• предложение DEFAULT;
• свойство IDENTITY.

Предложение DEFAULT в спецификации столбца указывает значение столбца по умолчанию, т.е. когда в таблицу вставляется новая строка, ячейка этого столбца будет содержать указанное значение, которое останется в ячейке, если в нее не будет введено другое значение. В качестве значения по умолчанию можно использовать константу, например одну из системных функций, таких как, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP и NULL.

Столбец идентификаторов, создаваемый указанием свойства IDENTITY, может иметь только целочисленные значения, которые системой присваиваются обычно неявно. Каждое следующее значение, вставляемое в такой столбец, вычисляется, увеличивая последнее, вставленное в этот столбец, значение. Поэтому определение столбца со свойством IDENTITY содержит (явно или неявно) начальное значение и шаг инкремента (такой столбец еще называют столбцом с автоинкрементом).

Ниже показан пример использования этих инструкций:

USE SampleDb; CREATE TABLE UserInfo ( -- Для столбца Id будет использоваться автоинкремент IDENTITY(10,5), -- т.е. при вставке данных первому элементу будет присвоено -- значение 10, второму 15, третьему 20 и т.д. Id INT NOT NULL PRIMARY KEY IDENTITY (10,5), Login VARCHAR(40) NOT NULL, -- Для поля BirthDate будет указана дата по умолчанию -- (если это поле не задано явно при вставке данных) BirthDate DATETIME DEFAULT ( -- По умолчанию -30 лет от текущей даты DATEADD(year, -30, GETDATE()) ) )

Инструкция CREATE TABLE и ограничения декларативной целостности

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

• повышается надежность данных;
• сокращается время на программирование;
• упрощается техническое обслуживание.

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

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

Если ограничения для обеспечения целостности предоставляются СУБД, то в случае изменений ограничений, соответствующие изменения в коде необходимо реализовать только один раз — в системе управления базами данных. А если ограничения предоставляются приложениями, то модификацию для отражения изменений в ограничениях необходимо выполнить в каждом из этих приложений.

Системами управления базами данных предоставляются два типа ограничений для обеспечения целостности:

• декларативные ограничения для обеспечения целостности;
• процедурные ограничения для обеспечения целостности, реализуемые посредством триггеров.

Декларативные ограничения определяются с помощью инструкций языка DDL CREATE TABLE и ALTER TABLE. Эти ограничения могут быть уровня столбцов или уровня таблицы. Ограничения уровня столбцов определяются наряду с типом данных и другими свойствами столбца в объявлении столбца, тогда как ограничения уровня таблицы всегда определяются в конце инструкции CREATE TABLE или ALTER TABLE после определения всех столбцов.

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

Каждому декларативному ограничению присваивается имя. Это имя может быть присвоено явно посредством использования опции CONSTRAINT в инструкции CREATE TABLE или ALTER TABLE. Если опция CONSTRAINT не указывается, то имя ограничению присваивается неявно компонентом Database Engine. Настоятельно рекомендуется использовать явные имена ограничений, поскольку это может значительно улучшить поиск этих ограничений.

Декларативные ограничения можно сгруппировать в следующие категории:

• предложение DEFAULT;
• предложение UNIQUE;
• предложение PRIMARY KEY;
• предложение CHECK;
• ссылочная целостность и предложение FOREIGN KEY.

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

Предложение UNIQUE

Иногда несколько столбцов или группа столбцов таблицы имеет уникальные значения, что позволяет использовать их в качестве первичного ключа. Столбцы или группы столбцов, которые можно использовать в качестве первичного ключа, называются потенциальными ключами (candidate key). Каждый потенциальный ключ определяется, используя предложение UNIQUE в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения UNIQUE следующий:

Опция CONSTRAINT в предложении UNIQUE присваивает явное имя потенциальному ключу. Опция CLUSTERED или NONCLUSTERED связана с тем обстоятельством, что компонент Database Engine создает индекс для каждого потенциального ключа таблицы. Этот индекс может быть кластеризованным, когда физический порядок строк определяется посредством индексированного порядка значений столбца. Если порядок строк не указывается, индекс является некластеризованным. По умолчанию применяется опция NONCLUSTERED. Параметр col_name1 обозначает имя столбца, который создает потенциальный ключ. (Потенциальный ключ может иметь до 16 столбцов.)

Применение предложения UNIQUE показано в примере ниже. (Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Projects, используя для этого инструкцию DROP TABLE Projects.)

USE SampleDb; CREATE TABLE Projects ( Number CHAR(4) DEFAULT 'p1', ProjectName NCHAR (15) NOT NULL, Budget FLOAT (53) NULL, CONSTRAINT unique_number UNIQUE (Number) );

Каждое значение столбца Number таблицы Projects является уникальным, включая значение NULL. (Точно так же, как и для любого другого значения с ограничением UNIQUE, если значения NULL разрешены для соответствующего столбца, этот столбец может содержать не более одной строки со значением NULL.) Попытка вставить в столбец Number уже имеющееся в нем значение будет неуспешной, т.к. система не примет его. Явное имя ограничения, определяемого в примере — unique_number.

Предложение PRIMARY KEY

является столбец или группа столбцов, значения которого разные в каждой строке. Каждый первичный ключ определяется, используя предложение PRIMARY KEY в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения PRIMARY KEY следующий:

Все параметры предложения PRIMARY KEY имеют такие же значения, как и соответствующие одноименные параметры предложения UNIQUE. Но в отличие от столбца UNIQUE, столбец PRIMARY KEY не разрешает значений NULL и имеет значение по умолчанию CLUSTERED.

В примере ниже показано объявление первичного ключа для таблицы Employee базы данных SampleDb. Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Employee, используя для этого инструкцию DROP TABLE Employee.

USE SampleDb; CREATE TABLE Employee ( Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL, CONSTRAINT primary_id PRIMARY KEY (Id) );

В результате выполнения этого кода снова создается таблица Employee, в которой определен первичный ключ. Первичный ключ таблицы определяется посредством декларативного ограничения для обеспечения целостности с именем primary_id. Это ограничение для обеспечения целостности является ограничением уровня таблицы, поскольку оно указывается после определения всех столбцов таблицы Employee.

Следующий пример эквивалентен предыдущему, за исключением того, что первичный ключ таблицы Employee определяется как ограничение уровня столбца.

USE SampleDb; DROP TABLE Employee; CREATE TABLE Employee ( Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL );

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

Предложение CHECK

определяет условия для вставляемых в столбец данных. Каждая вставляемая в таблицу строка или каждое значение, которым обновляется значение столбца, должно отвечать этим условиям. Проверочные ограничения устанавливаются посредством предложения CHECK, определяемого в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения CHECK следующий:

Параметр expression должен иметь логическое значение (true или false) и может ссылаться на любые столбцы в текущей таблице (или только на текущий столбец, если определен как ограничение уровня столбца), но не на другие таблицы. Предложение CHECK не применяется принудительно при репликации данных, если присутствует параметр NOT FOR REPLICATION. (При репликации база данных, или ее часть, хранится в нескольких местах. С помощью репликации можно повысить уровень доступности данных.)

В примере ниже показано применение предложения CHECK:

USE SampleDb; CREATE TABLE Customer ( CustomerId INTEGER NOT NULL, CustomerRole VARCHAR(100) NULL, CHECK (CustomerRole IN ('admin', 'moderator', 'user')) );

Создаваемая в примере таблица Customer включает столбец CustomerRole, содержащий соответствующее проверочное ограничение. При вставке нового значения, отличающегося от значений в наборе (‘admin’, ‘moderator’, ‘user’), или при попытке изменения существующего значения на значение, отличающегося от этих значений, система управления базой данных возвращает сообщение об ошибке.

Предложение FOREIGN KEY

— это столбец (или группа столбцов таблицы), содержащий значения, совпадающие со значениями первичного ключа в этой же или другой таблице. Внешний ключ определяется с помощью предложения FOREIGN KEY в комбинации с предложением REFERENCES. Синтаксис предложения FOREIGN KEY следующий:

Предложение FOREIGN KEY явно определяет все столбцы, входящие во внешний ключ. В предложении REFERENCES указывается имя таблицы, содержащей столбцы, создающие соответствующий первичный ключ. Количество столбцов и их тип данных в предложении FOREIGN KEY должны совпадать с количеством соответствующих столбцов и их типом данных в предложении REFERENCES (и, конечно же, они должны совпадать с количеством столбцов и типами данных в первичном ключе таблицы, на которую они ссылаются).

Таблица, содержащая внешний ключ, называется ссылающейся (или дочерней) таблицей (referencing table), а таблица, содержащая соответствующий первичный ключ, называется ссылочной (referenced table) или родительской (parent table) таблицей. В примере ниже показано объявление внешнего ключа для таблицы Works_on базы данных SampleDb:

USE SampleDb; CREATE TABLE Works_on ( EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id), CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) );

Таблица Works_on в этом примере задается с тремя декларативными ограничениями для обеспечения целостности: primary_works, foreign_employee и foreign_project. Эти ограничения являются ограничением уровня таблицы, где первое указывает первичный ключ, а второе и третье — внешний ключ таблицы Works_on. Кроме этого, внешние ключи определяют таблицы Employee и Projects, как ссылочные таблицы, а их столбцы Id и Number, как соответствующий первичный ключ столбца с таким же именем в таблице Works_on.

Предложение FOREIGN KEY можно пропустить, если внешний ключ определяется, как ограничение уровня таблицы, поскольку столбец, к которому применяется ограничение, является неявным «списком» столбцов внешнего ключа, и ключевого слова REFERENCES достаточно для указания того, какого типа является это ограничение. Таблица может содержать самое большее 63 ограничения FOREIGN KEY.

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

Ссылочная целостность (referential integrity) обеспечивает выполнение правил для вставок и обновлений таблиц, содержащих внешний ключ и соответствующее ограничение первичного ключа. Пример выше имеет два таких ограничения: foreign_employe и foreign_project. Предложение REFERENCES в примере определяет таблицы Employee и Projects в качестве ссылочных (родительских) таблиц.

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

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

Возможные проблемы со ссылочной целостностью — случай 1

Вставка новой строки в таблицу Works_on с номером сотрудника 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:

USE SampleDb; INSERT INTO Works_on VALUES (11111, 'p1', 'qwe', GETDATE())

При вставке новой строки в дочернюю таблицу Works_on используется новый номер сотрудника EmpId, для которого нет совпадающего сотрудника (и номера) в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, как это сделано ранее, то компонент Database Engine не допустит вставки новой строки с таким номером EmpId.

Возможные проблемы со ссылочной целостностью — случай 2

Изменение номера сотрудника 9502 во всех строка таблицы Works_on на номер 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:

USE SampleDb; UPDATE Works_on SET EmpId = 11111 WHERE EmpId = 9502;

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

Возможные проблемы со ссылочной целостностью — случай 3

Замена значения 9502 номера сотрудника Id на значение 22222 в таблице Employee. Соответствующая инструкция Transact-SQL будет выглядеть таким образом:

USE SampleDb; UPDATE Employee SET WHERE >В данном случае предпринимается попытка заменить существующее значение 9502 номера сотрудника Id значением 22222 только в родительской таблице Employee, не меняя соответствующие значения Id в ссылающейся таблице Works_on. Система не разрешает выполнения этой операции. Ссылочная целостность не допускает существования в ссылающейся таблице (таблице, для которой предложением FOREIGN KEY определен внешний ключ) таких значений, для которых в родительской таблице (таблице, для которой предложением PRIMARY KEY определен первичный ключ) не существует соответствующего значения. В противном случае такие строки в ссылающейся таблице были бы "сиротами". Если бы описанная выше модификация таблицы Employee была разрешена, тогда строки в таблице Works_on со значением Id равным 9502 были бы сиротами. Поэтому система и не разрешает выполнения такой модификации.

Возможные проблемы со ссылочной целостностью — случай 4

Удаление строки в таблице Employee со значением Id равным 9502.

Этот случай похожий на случай 3. В случае выполнения этой операции, из таблицы Employee была бы удалена строка со значением Id, для которого существуют совпадающие значения в ссылающейся (дочерней) таблице Works_on.

Опции ON DELETE и ON UPDATE

Компонент Database Engine на попытку удаления и модифицирования первичного ключа может реагировать по-разному. Если попытаться обновить значения внешнего ключа, то все эти обновления будут несогласованы с соответствующим первичным ключом, база данных откажется выполнять эти обновления и выведет сообщение об ошибке.

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

NO ACTION

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

CASCADE

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

SET NULL

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

SET DEFAULT

Аналогично опции SET NULL, но с одним исключением: всем внешним ключам, соответствующим модифицируемому первичному ключу, присваивается значение по умолчанию. Само собой разумеется, что после модификации первичный ключ родительской таблицы все равно должен содержать значение по умолчанию.

В языке Transact-SQL поддерживаются первые две из этих опций. Использование опций ON DELETE и ON UPDATE показано в примере ниже:

USE SampleDb; CREATE TABLE Works_on ( EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) ON DELETE CASCADE, CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) ON UPDATE CASCADE );

В этом примере создается таблица Works_on с использованием опций ON DELETE CASCADE и ON UPDATE CASCADE. Если таблицу Works_on загрузить значениями, каждое удаление строки в таблице Employee будет вызывать каскадное удаление всех строк в таблице Works_on, которые имеют значения внешнего ключа, соответствующие значениям первичного ключа строк, удаляемых в таблице Employee. Подобным образом каждое обновление значения столбца Number таблицы Project будет вызывать такое же обновление всех соответствующих значений столбца ProjectNumber таблицы Works_on.

Как создавать таблицы в MySQL (Create Table)

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

Эта инструкция — часть курса «MySQL для новичков».

Смотреть весь курс

Введение

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

• Названия таблиц и столбцов.
• Типы данных столбцов.
• Атрибуты и ограничения.

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

Синтаксис Create table в MySQL и создание таблиц

Поскольку наш путь в базы данных только начинается, стоит вспомнить основы. Реляционные базы данных хранят данные в таблицах, и каждая таблица содержит набор столбцов. У столбца есть название и тип данных. Команда создания таблицы должна содержать все вышеупомянутое:

CREATE TABLE table_name ( column_name_1 column_type_1, column_name_2 column_type_2, . column_name_N column_type_N, ); 

table_name — имя таблицы;

column_name — имя столбца;

column_type — тип данных столбца.

Теперь разберем процесс создания таблицы детально.

Названия таблиц и столбцов

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

Имена могут содержать символы подчеркивания для большей наглядности. Классический пример непонятных названий — table1, table2 и т. п. Использование транслита, неясных сокращений и, разумеется, наличие орфографических ошибок тоже не приветствуется. Хороший пример коротких информативных названий: Customers, Users, Orders, так как по названию таблицы должно быть очевидно, какие данные таблица будет содержать. Эта же логика применима и к названию столбцов.

Максимальная длина названия и для таблицы, и для столбцов — 64 символа.

Типы данных столбцов

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

Числовые типы

• INT — целочисленные значения от −2147483648 до 2147483647, 4 байта.
• DECIMAL — хранит числа с заданной точностью. Использует два параметра — максимальное количество цифр всего числа (precision) и количество цифр дробной части (scale). Рекомендуемый тип данных для работы с валютами и координатами. Можно использовать синонимы NUMERIC, DEC, FIXED.
• TINYINT — целые числа от −127 до 128, занимает 1 байт хранимой памяти.
• BOOL — 0 или 1. Однозначный ответ на однозначный вопрос — false или true. Название столбцов типа boolean часто начинается с is, has, can, allow. По факту это даже не отдельный тип данных, а псевдоним для типа TINYINT (1). Тип настолько востребован на практике, что для него в MySQL создали встроенные константы FALSE (0) или TRUE (1). Можно использовать синоним BOOLEAN.
• FLOAT — дробные числа с плавающей запятой (точкой).

Символьные

• VARCHAR(N) — N определяет максимально возможную длину строки. Создан для хранения текстовых данных переменной длины, поэтому память хранения зависит от длины строки. Наиболее часто используемый тип строковых данных.
• CHAR(N) — как и с varchar, N указывает максимальную длину строки. Char создан хранить данные строго фиксированной длины, и каждая запись будет занимать ровно столько памяти, сколько требуется для хранения строки длиной N.
• TEXT — подходит для хранения большого объема текста до 65 KB, например, целой статьи.

Дата и время

• DATE — только дата. Диапазон от 1000-01-01 по 9999-12-31. Подходит для хранения дат рождения, исторических дат, начиная с 11 века. Память хранения — 3 байта.
• TIME — только время — часы, минуты, секунды — «hh:mm:ss». Память хранения — 3 байта.
• DATETIME — соединяет оба предыдущих типа — дату и время. Использует 8 байтов памяти.
• TIMESTAMP — хранит дату и время начиная с 1970 года. Подходит для большинства бизнес-задач. Потребляет 4 байта памяти, что в два раза меньше, чем DATETIME, поскольку использует более скромный диапазон дат.

Бинарные

Используются для хранения файлов, фото, документов, аудио и видеоконтента. Все это хранится в бинарном виде.

Подробный разбор типов данных, включая более специализированные типы, например, ENUM, SET или BIGINT UNSIGNED, будет в отдельной тематической статье.

Практика с примерами

Для лучшего понимания приведем пример, создав простую таблицу для хранения данных сотрудников, где

• id — уникальный номер,
• name — ФИО,
• position — должность
• birthday — дата рождения

Синтаксис create table с основными параметрами:

CREATE TABLE Staff ( id INT, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday Date ); 

Тут могут появиться вопросы. Откуда MySQL знает, что номер уникален? Если еще нет должности для этого сотрудника, что будет, если оставить поле пустым?
Все это (как и многое другое) придtтся указать с помощью дополнительных параметров — атрибутов.

Часто таблицы создаются и заполняются скриптами. Если мы вызовем команду CREATE TABLE Staff, а таблица Staff уже есть в базе, команда выдаст ошибку. Поэтому перед созданием разумно проверить, содержит ли уже база таблицу Staff. Достаточно добавить IF NOT EXISTS, чтобы выполнить эту проверку в MySQL, то есть вместо

CREATE TABLE Staff 

CREATE TABLE IF NOT EXISTS Staff 

Повторный запуск команды выведет предупреждение:

1050 Table 'Staff' already exists 

Если таблица уже создана и нужно создать таблицу с тем же именем с «чистого листа», старую таблицу можно удалить командой:

DROP TABLE table_name; 

Возможности SQL в «Облачных базах данных»

Атрибуты (ATTRIBUTES) и ограничения (CONSTRAINTS)

PRIMARY KEY

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

Пользы индексов на примерах: для поиска уникального значения среди 10000 строк придется проверить, в худшем случае, все 10000 без индекса, с индексом — всего 14. Поиск по миллиону записей займет не больше в 20 проверок — это реализация идеи бинарного поиска.

Создадим таблицу Staff с номером сотрудника в качестве первичного ключа. Первичный ключ гарантирует нам, что номер точно будет уникальным, а поиск по нему — быстрым.

CREATE TABLE Staff ( id INT PRIMARY KEY, name VARCHAR(255), position VARCHAR(30), birthday Date, has_children BOOLEAN ); 

NOT NULL

При заполнении таблицы мы утверждаем, что значение этого столбца должно быть установлено. Если нет явного указания NOT NULL, и этот столбец не PRIMARY KEY, то столбец позволяет хранить NULL, то есть хранение NULL — поведение по умолчанию. Для первичного ключа это ограничение можно не указывать, так как первичный ключ всегда гарантирует NOT NULL.

Изменим команду CREATE TABLE, добавив NOT NULL ограничения: таким образом, мы обозначим обязательные для заполнения столбцы (т.е. столбцы, поля в которых не могут оставаться пустыми при наличии записи в таблице):

CREATE TABLE Staff ( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday DATE NOT NULL, has_children BOOLEAN NOT NULL ); 

DEFAULT

Можно указать значение по умолчанию, т.е. текст или число, которые будут сохранены, если не указано другое значение. Применяется не ко всем типам: BLOB, TEXT, GEOMETRY и JSON не поддерживают это ограничение.
Эта величина должна быть константой, функция или выражение не допустимы.

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

CREATE TABLE Staff ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday DATE NOT NULL, has_children BOOLEAN DEFAULT(FALSE) NOT NULL ); 

Для типа данных BOOLEAN можно использовать встроенные константы FALSE и TRUE. Вместо DEFAULT(FALSE) можно указать DEFAULT(0) — эти записи эквивалентны.

AUTO_INCREMENT

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

CREATE TABLE Staff ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday DATE NOT NULL, has_children BOOLEAN DEFAULT(FALSE) NOT NULL ); 

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

Интересно, что при CREATE TABLE MySQL не позволяет установить стартовое значение для AUTO_INCREMENT. Можно назначить стартовое значение для счетчика AUTO_INCREMENT уже созданной таблицы.

ALTER TABLE Staff AUTO_INCREMENT=10001; 

Первая запись после такой модификации получит >

UNIQUE

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

CREATE TABLE Staff ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday DATE NOT NULL, has_child BOOLEAN DEFAULT(0) NOT NULL, phone VARCHAR(20) UNIQUE NOT NULL ); 

CHECK

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

Синтаксис позволяет устанавливать CHECK как в описании столбца при CREATE TABLE:

birthday DATE NOT NULL CHECK (birthday > ‘1900-01-01’), 

так отдельно от описания столбцов:

CHECK (birthday > ‘1900-01-01’), 

В этих случаях название проверки будет определено автоматически. При вставке данных, не прошедших проверку, будет сообщение об ошибке Check constraint ‘staff_chk_1’ is violated. Ситуация усложняется, когда установлено несколько CHECK, поэтому рекомендуется давать понятное имя.

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

CREATE TABLE Staff ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, position VARCHAR(30), birthday DATE NOT NULL, has_child BOOLEAN DEFAULT(0) NOT NULL, phone VARCHAR(20) UNIQUE NOT NULL, CONSTRAINT staff_chk_birthday CHECK (birthday > '1900-01-01'), CONSTRAINT staff_chk_phone CHECK (phone REGEXP '[+]?[0-9] ?\\(?[0-9]\\)? ?[0-9][0-9 -]+[0-9]') ); 

Для добавления ограничений используем оператор CONSTRAINT, при этом, все названия уникальны, как и имена таблиц. Учитывая, что по умолчанию названия включают в себя и имя таблицы, рекомендуем придерживаться этого правила. Если используется CONSTRAINT, мы обязаны дать имя ограничению, которое вводим.

FOREIGN KEY или внешний ключ

Внешний ключ — это ссылка на столбец или группу столбцов другой таблицы. Это тоже ограничение (CONSTRAINT), так как мы сможем использовать только значения, для которых есть соответствие по внешнему ключу. Создает индекс. Таблицу с внешним ключом называют зависимой.

FOREIGN KEY (column_name1, column_name2) REFERENCES external_table_name(external_column_name1, external_column_name2) 

Сначала указывается выражение FOREIGN KEY и набор столбцов таблицы, откуда строим FOREIGN KEY. Затем ключевое слово REFERENCES указывает на имя внешней таблицы и набор столбцов этой внешней таблицы. В конце можно добавить операторы ON DELETE и ON UPDATE, с помощью которых настраивается поведение при удалении или обновлении данных в главной таблице. Это делать не обязательно, так как предусмотрено поведение по умолчанию. Поведение по умолчанию запрещает удалять или изменять записи из внешней таблицы, если на эти записи есть ссылки по внешнему ключу.

Возможные опции для ON DELETE и ON UPDATE:

CASCADE: автоматическое удаление/изменение строк зависимой таблицы при удалении/изменении связанных строк главной таблицы.
SET NULL: при удалении/изменении связанных строк главной таблицы будет установлено значение NULL в строках зависимой таблицы. Столбец зависимой таблицы должен поддерживать установку NULL, т.е. параметр NOT NULL в этом случае устанавливать нельзя.
RESTRICT: не даёт удалить/изменить строку главной таблицы при наличии связанных строк в зависимой таблице. Если не указана иная опция, по умолчанию будет использовано NO ACTION, что, по сути, то же самое, что и RESTRICT.

Рассмотрим пример:
Для таблицы Staff было определено текстовое поле position для хранения должности.
Так как список сотрудников в компании обычно больше, чем список занимаемых должностей, есть смысл создать справочник должностей.

CREATE TABLE Positions ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL ); 

Поскольку из Staff мы будем ссылаться на Positions, таблица персонала Staff будет зависимой от Positions. Изменим синтаксис CREATE TABLE для таблицы Staff, чтобы должность была ссылкой на запись в таблице Positions.

CREATE TABLE Staff ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, position_id int, birthday DATE NOT NULL, has_child BOOLEAN DEFAULT(0) NOT NULL, phone VARCHAR(20) UNIQUE NOT NULL, FOREIGN KEY (position_id) REFERENCES Positions (id) ); 

При CREATE TABLE, чтобы не усложнять описание столбца, рекомендуется указывать внешний ключ и все его атрибуты после перечисления создаваемых столбцов.
Можно ли добавить внешний ключ, если таблица уже создана и в ней есть данные? Можно! Для внесения изменений в таблицу используем ALTER TABLE.

ALTER TABLE Staff ADD FOREIGN KEY (position_id) REFERENCES Positions(id); 

Или в развернутой форме, определяя имя ключа fk_position_id явным образом:

ALTER TABLE Staff ADD CONSTRAINT fk_position_id FOREIGN KEY (position_id) REFERENCES Positions(id); 

Главное условие в этом случае — согласованность данных. Это значит, что для всех записей внешнего ключа position_id должно найтись соответствие в целевой таблице Positions по столбцу id.

Создание таблиц на основе уже существующих, временные таблицы

Мы рассмотрели создание таблицы с «чистого листа», но есть два других способа:

LIKE

Создание таблицы на основе уже существующей таблицы. Копирует структуру — количество, названия и типы столбцов, индексы, все ограничения, кроме внешних ключей. Как мы помним, внешний ключ создает индекс. При создании через LIKE индексы в новой таблице будут построены также, как и в старой, но внешние ключи не скопируются. Таблица будет создана без записей и без счетчиков AUTO_INCREMENT.

CREATE TABLE new_table LIKE source_table; 

SELECT

Можно создать таблицу на основе SELECT-запроса — результат этой выборки будет записан в новую таблицу. Такая таблица не будет иметь индексов, ограничений и ключей. Все столбцы, с учетом порядка, типов данных и названий, будут взяты из запроса — поля из SELECT станут столбцами новой таблицы. При этом можно переопределить изначальные названия полей, что особенно актуально, когда в выборку попадают столбцы с одинаковыми названиями (на уровне таблицы названия столбцов всегда уникальны).

CREATE TABLE new_table [AS] SELECT * FROM source_table; 

Разберем пример создания новой таблицы через SELECT, используя две таблицы в выборке — Staff и Positions. В запросе определим три поля: id, staff, position — это будут столбцы новой таблицы StaffData211015 (срез сотрудников на определённую дату). Без присвоения псевдонимов (name as staff, name as position) в выборке получилось бы два одинаковых поля name, что не позволило бы создать таблицу из-за duplicate column name ошибки.

CREATE TABLE StaffData211015 SELECT s.Id, s.name as staff, p.name as position FROM Staff s JOIN Positions p ON s.position_id = p.id 

TEMPORARY

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

Чтобы обозначить таблицу как временную, нужно добавить TEMPORARY в CREATE TABLE:

CREATE TEMPORARY TABLE table_name; 

Работа с уже созданной таблицей

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

Переименование

Ключевая команда — RENAME.

• Изменить имя таблицы:

RENAME TABLE old_table_name TO new_table_name; 

• Изменить название столбца:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; 

Удаление данных

• DELETE FROM Staff; — удалит все записи из таблицы. Условие в WHERE позволит удалить только определенные строки, в примере ниже удалим только одну строку с DELETE FROM Staff WHERE TABLE Staff; — используется для полной очистки всей таблицы. При TRUNCATE счетчики AUTO_INCREMENT сбросятся. Если бы мы удалили все в строки командой DELETE, то новые строки учитывали бы накопленный за время жизни таблицы AUTO_INCREMENT.
• DROP TABLE Staff; — команда удаления таблицы.

Изменение структуры таблицы

Команда ALTER TABLE включает в себя множество опций, рассмотрим основные вместе с примерами на таблице Staff.

Добавление столбцов

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

ALTER TABLE Staff ADD email VARCHAR(50), ADD age INT, ADD has_auto BOOLEAN; 

Удаление столбцов

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

ALTER TABLE Staff DROP COLUMN age; 

Значение по умолчанию

Выставим значение по умолчанию для столбца has_auto:

ALTER TABLE Staff ALTER COLUMN has_auto SET DEFAULT(FALSE); 

Изменение типа данных столбца

Для столбца name изменим тип данных:

ALTER TABLE Staff MODIFY COLUMN name VARCHAR(500) NOT NULL; 

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

Установка CHECK

Добавим ограничение формата для email через регулярное выражение:

ALTER TABLE Staff ADD CONSTRAINT staff_chk_email CHECK (email REGEXP '^[^@]+@[^@]+\\.[^@]$'); 

Заключение

Любой путь начинается с первых шагов. В работе с базами данных этими шагами является создание структуры таблиц. Продуманная композиция сущностей (таблиц) и связей между ними — основа проектирования любого вашего приложения от интернет-магазинов до мощных систем управления предприятиями.

SQL — создание таблицы

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

Синтаксис

Основной синтаксис инструкции CREATE TABLE выглядит следующим образом:

CREATE TABLE имя _ таблицы (
столбец 1 типданных ,
столбец 2 типданных ,
столбец 3 типданных ,
столбец N типданных ,
PRIMARY KEY ( один или несколько столбцов )

CREATE TABLE — это ключевое слово, сообщающее системе баз данных, что вы хотите сделать. В данном случае мы хотим создать новую таблицу. После оператора CREATE TABLE указывается уникальное имя или идентификатор таблицы.

Затем в скобках указывается список, определяющий каждый столбец в таблице и тип данных для него. Чтобы вам было понятнее, давайте рассмотрим следующий пример.

Копию существующей таблицы можно создать с помощью комбинации инструкций CREATE TABLE и SELECT. По следующей ссылке вы можете найти полную информацию, как Создать таблицу используя другую таблицу

Пример

В следующем примере мы создаем таблицу CUSTOMERS с идентификатором в качестве первичного ключа, а NOT NULL — это ограничения, указывающие, что эти поля не могут содержать значение NULL при создании записей в этой таблице: