Создание баз данных и таблиц в SQL

Готовы ли вы начать создавать базы данных и таблицы с помощью языка структурированных запросов? В этой статье мы рассмотрим процесс создания таблиц вручную с помощью команд CREATE DATABASE и CREATE TABLE. Если вы новичок в SQL, вы можете сначала изучить некоторые основы SQL.

Бизнес-требования

Прежде чем мы сядем за клавиатуру, мы должны убедиться, что у нас есть четкое понимание требований заказчика. Каков наилучший способ получить это понимание? Говорить с клиентом, конечно! После встречи с менеджером по персоналу XYZ мы узнали, что они являются компанией по продаже виджета и в первую очередь заинтересованы в отслеживании информации об их персонале по продажам.

Корпорация XYZ делит свои торговые силы в восточные и западные регионы, каждая из которых разделена на многие территории, охваченные отдельными представителями продаж. Отдел кадров хотел бы отслеживать территорию, охватываемую каждым сотрудником, а также информацию о заработной плате каждого сотрудника и структуру надзора. Чтобы удовлетворить эти требования, мы разработали базу данных, состоящую из трех таблиц, показанных на диаграмме Entity-Relationship на этой странице.

Выбор платформы базы данных

Мы решили использовать систему управления базами данных (или СУБД), которая построена на языке структурированных запросов (SQL). Поэтому все наши команды создания базы данных и таблицы должны быть написаны с учетом стандартного ANSI SQL.

В качестве дополнительной выгоды использование ANSI-совместимого SQL гарантирует, что эти команды будут работать в любой СУБД, поддерживающей стандарт SQL, включая Oracle и Microsoft SQL Server. Если вы еще не выбрали платформу для своей базы данных, Database Software Options проведет вас через процесс выбора.

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

Нашим первым шагом является создание самой базы данных. Многие системы управления базами данных предлагают ряд параметров для настройки параметров базы данных на этом этапе, но наша база данных допускает простое создание базы данных. Как и во всех наших командах, вы можете обратиться к документации для своей СУБД, чтобы определить, соответствуют ли какие-либо дополнительные параметры вашей конкретной системе вашим потребностям. Давайте используем команду CREATE DATABASE для настройки нашей базы данных:

Персонал CREATE DATABASE

Особо обратите внимание на капитализацию, использованную в приведенном выше примере. Обычная практика среди программистов SQL - использовать все заглавные буквы для ключевых слов SQL, таких как «CREATE» и «DATABASE», при использовании всех строчных букв для пользовательских имен, таких как имя базы данных «персонал». Эти соглашения обеспечивают удобство чтения.

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

Создание нашей первой таблицы

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

Атрибут ReportsTo хранит идентификатор менеджера для каждого сотрудника. Из показанных примеров записей мы можем определить, что Сью Скампи является менеджером как Тома Кендалла, так и Джона Смита. Однако в базе данных диспетчера Сью нет информации, о чем свидетельствует запись NULL в ее строке.

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

Персонал ИСПОЛЬЗОВАНИЯ;

В качестве альтернативы, «персонал базы данных»; команда будет выполнять ту же функцию. Теперь мы можем взглянуть на команду SQL, используемую для создания таблицы наших сотрудников:

CREATE TABLE сотрудники (employeeid INTEGER NOT NULL, lastname VARCHAR (25) NOT NULL, firstname VARCHAR (25) NOT NULL, reportsto INTEGER NULL);

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

CREATE TABLE имя_таблицы (атрибуты типа атрибута_файла, …, атрибуты_файла атрибута_файла);

Атрибуты и типы данных

В предыдущем примере имя таблицы - это сотрудники, и мы включаем четыре атрибута: employeeid, lastname, firstname и reportsto. Тип данных указывает тип информации, которую мы хотим сохранить в каждом поле. Идентификатор сотрудника - это простой целочисленный номер, поэтому мы будем использовать тип данных INTEGER как для поля employeeid, так и для поля reportsto. Имена сотрудников будут символьными строками переменной длины, и мы не ожидаем, что какой-либо сотрудник будет иметь имя или фамилию длиной более 25 символов. Поэтому для этих полей мы будем использовать тип VARCHAR (25).

Значения NULL

Мы также можем указать либо NULL, либо NOT NULL в поле опций оператора CREATE. Это просто указывает базе данных, допустимы ли значения NULL (или пустые) для этого атрибута при добавлении строк в базу данных. В нашем примере отдел кадров требует, чтобы для каждого сотрудника хранился идентификатор сотрудника и полное имя. Однако не у каждого сотрудника есть менеджер (генеральный директор никому не сообщается!), Поэтому мы разрешаем записи NULL в этой области.Обратите внимание, что значение NULL является значением по умолчанию, и опускание этого параметра неявно допускает значения NULL для атрибута.

Построение оставшихся таблиц

Теперь давайте посмотрим на таблицу территорий. Из быстрого просмотра этих данных представляется, что нам нужно сохранить целые и две строки переменной длины. Как и в нашем предыдущем примере, мы не ожидаем, что ID региона будет потреблять более 25 символов. Однако некоторые из наших территорий имеют более длинные имена, поэтому мы расширим допустимую длину этого атрибута до 40 символов.

Давайте посмотрим на соответствующий SQL:

СОЗДАТЬ ТАБЛИЦЫ (internalid INTEGER NOT NULL, территория Описание VARCHAR (40) NOT NULL, regionid VARCHAR (25) NOT NULL);

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

Этот метод хранения данных уменьшает избыточность в нашей базе данных и обеспечивает оптимальное использование пространства на наших накопителях. Мы подробно рассмотрим команду JOIN в будущем учебнике. Вот код SQL для реализации нашей итоговой таблицы:

СОЗДАТЬ ТАБЛИЦЫ useeeterritories (employeeid INTEGER NOT NULL, areaid INTEGER NOT NULL);

Механизм SQL обеспечивает изменение структуры базы данных после создания

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

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

Сотрудники ALTER TABLE ДОБАВИТЬ зарплату INTEGER NULL;

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