Создание таблиц — Основы реляционных баз данных
В этом уроке мы поработаем с таблицами: будем создавать их, добавлять, модифицировать и удалять данные. Также разберем типы данных таблицы.
Создание базы данных
Прежде чем создать таблицу, создадим базу данных hexlet с помощью SQL (если вы еще этого не сделали). Для этого подключитесь к СУБД через psql . При этом не указывайте базу данных, чтобы подключиться к базе по умолчанию. Далее выполните следующие запросы:
DROP DATABASE hexlet; CREATE DATABASE hexlet; В примере выше два SQL запроса:
- DROP DATABASE hexlet — удаляет базу данных с именем hexlet
- CREATE DATABASE hexlet — создает базу данных с таким же именем
Базовые правила построения запросов:
- Каждый запрос должен заканчиваться точкой с запятой. Иначе psql будет думать, что вы продолжаете вводить команды
- Регистр не важен. Можно было написать drop database hexlet; . По традиции принято использовать верхний регистр для ключевых слов самого SQL. Это позволяет визуально разделять структуру запроса от данных внутри него. Последнее в примере — это имя базы данных, которое может быть произвольным
Если подключиться к той же базе данных, которую вы хотите удалить или пересоздать, то во время попытки удаления СУБД будет ругаться, что к базе есть активное соединение — ваше соединение. Поэтому важно подключиться к любой другой базе данных.
Команды createdb и createuser , которые мы разобрали в прошлых уроках, выполняют SQL-запросы внутри СУБД. Их сделали ради удобства первоначальной настройки, и чтобы использовать в скриптах автоматизации.
SQL поддерживает комментарии — строчка, которая начинается с двух дефисов. Комментарии игнорируются СУБД при построении запросов:
hexlet=> -- i am comment hexlet=> Нам удалось создать базу данных hexlet , поэтому можно переходить к созданию таблицы.
Создание таблиц
Таблица создается с помощью запроса CREATE TABLE :
-- Это один запрос, хоть и многострочный. -- Описание запроса заканчивается символом ; CREATE TABLE courses ( name varchar(255), slug varchar(255), lessons_count integer, body text ); Чтобы создать таблицу, необходимо указать ее имя, набор полей и их типы. В примере выше названия полей — это name , slug , lessons_count и body , а varchar(255) , integer и text — их типы.
Типы данных
У каждого поля в PostgreSQL определенный тип, который задается на этапе создания таблицы. Это значит, что значением этого поля могут быть только определенные данные. Если поле имеет числовой тип, то в него невозможно вставить строку, и наоборот. База данных выдаст ошибку при попытке выполнить подобный запрос.
-- Выполняем запрос на вставку передавая в lessons_count строку вместо числа ERROR: invalid input syntax for type integer: "wrong value" В PostgreSQL встроено много различных типов данных, но на практике используются не все. Ниже мы разбираем только самые популярные типы.
Строки
Для строк в базах данных в основном используются два типа:
- varchar — для строк с ограничением максимальной длины
- text — для строк без ограничения. Как правило, это полноценные тексты
В базах данных нельзя оставить первый тип без указания длины. Это связано с производительностью и эффективностью. Данные в базах данных физически хранятся на дисках в файлах. Быстрый доступ к этим данным возможен только тогда, когда у данных фиксированный размер. Это позволяет быстро перемещаться по ним и считать смещения.
Если размер данных не известен, то придется просматривать весь файл в поисках нужного значения. Чтобы избежать подобной ситуации, тип text хранится отдельно. Это тоже негативно влияет на скорость, но уже не так сильно. Если размер строки известен или он меньше какого-то значения, то предпочтительнее использовать varchar.
| Имя | Описание |
|---|---|
| character varying(n), varchar(n) | строка ограниченной переменной длины |
| text | строка неограниченной переменной длины |
- varchar. Полное название типа character varying (varchar может использоваться как псевдоним). Размер строки с таким типом указывается в скобках после названия типа, например, varchar(10). Это значит, что в поле с таким типом можно записать строку длиной до 10 символов.
- text. Не требует указания размера и может содержать текст произвольной длины
Пример создания таблицы с такими типами:
CREATE TABLE blog_posts ( name varchar(80), body text ); Числа
Для чисел в основном используются два типа данных: integer и bigint. Какой конкретно указывать тип, зависит от потенциального потолка значения. Ниже указаны диапазоны, допустимые в рамках этих типов:
| Имя | Описание | Диапазон |
|---|---|---|
| integer | типичный выбор для целых чисел | -2147483648 .. +2147483647 |
| bigint | целое в большом диапазоне | -9223372036854775808 .. 9223372036854775807 |
Пример создания таблицы с такими типами:
CREATE TABLE users ( id bigint, age integer ); Даты
Типы для хранения дат отличаются друг от друга очень сильно, в первую очередь по решаемой задаче. Нам надо хранить день без конкретного времени? Это тип date. Нужно конкретный момент времени, тогда timestamp. Просто время без даты? Тогда time.
| Имя | Описание | Наименьшее значение | Наибольшее значение | Точность |
|---|---|---|---|---|
| timestamp | дата и время (без часового пояса) | 4713 до н. э. | 294276 н. э. | 1 микросекунда |
| date | дата (без времени суток) | 4713 до н. э. | 5874897 н. э. | 1 день |
| time | время суток (без даты) | 00:00:00 | 24:00:00 | 1 микросекунда |
Пример создания таблицы с такими типами:
CREATE TABLE events ( start_date date, -- имя поля может называться как тип данных time time, updated_at timestamp, created_at timestamp ); Хорошей практикой считается добавление и заполнение полей created_at и updated_at в каждую таблицу базы данных. С их помощью всегда можно узнать, когда запись создалась и обновилась.
Значения даты и времени принимаются практически в любом известном формате. Вот несколько примеров того, как можно задавать дату:
| Пример | Описание |
|---|---|
| 1999-01-08 | ISO 8601 (рекомендуемый формат) |
| January 8, 1999 |
Логический тип
Содержит всего два значения: true и false . Этот тип используется для флагов:
| Имя | Описание |
|---|---|
| boolean | true или false (истина или ложь) |
Пример создания таблицы с такими типами:
CREATE TABLE blog_posts ( -- флаг: опубликован? published boolean ); Состояние «true» может задаваться следующими значениями:
Для состояния «false» можно использовать следующие варианты:
Помимо типов данных для реальных значений, в базе существует специальное значение NULL , чтобы обозначать пустоту. Оно используется, когда у конкретного поля нет значения. Тип поля при этом не важен. Подробнее с NULL мы разберемся в следующих уроках.
Анализ структуры базы данных
Чтобы исследовать структуру таблиц в визуальном режиме, используется PgAdmin:
SQL для анализа структуры базы данных не существует. Если вы хотите посмотреть список таблиц и их структуру в базе данных, то придется использовать команды самого psql :
Просмотр списка таблиц базы данных hexlet
hexlet=> \d List of relations Schema | Name | Type | Owner --------+------------+-------+--------- public | courses | table | vagrant public | events | table | vagrant public | blog_posts | table | vagrant Здесь мы видим список таблиц в базе данных hexlet. Все что здесь отображается, было создано в этом уроке выше.
В первом столбце видим новое для нас понятие — schema. Это пространство имен, которое позволяет группировать таблицы, в различных ситуациях. На практике эта возможность используется редко, поэтому мы не обращаем на нее внимание. По умолчанию все таблицы публикуются в общей схеме public, которую можно не указывать.
Просмотр структуры таблицы courses
hexlet=> \d courses # public - обозначает схему по умолчанию Table "public.courses" Column | Type | Modifiers ---------------+------------------------+----------- name | character varying(255) | slug | character varying(255) | lessons_count | integer | body | text | В этом выводе показана структура таблицы courses. Здесь мы видим все имена полей и их типы.
Кроме перечисленных полезными могут оказаться следующие команды:
- \l — список всех баз данных
- \dt — список всех таблиц
- \? — вывод справки
Удаление таблиц
Чтобы удалить таблицу, выполняется запрос DROP :
DROP TABLE courses; Будьте внимательны, так как удаление таблицы приводит к безвозвратной потере данных.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
SQL-Урок 14. Создание таблиц (CREATE TABLE)
Язык SQL используется не только для обработки информации, но и предназначен для выполнения всех операций с базами данных и таблицами, включая создание таблиц и работу с ними.
Существует два способа создания таблиц, используя:
Следует отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, то есть интерфейс сам создает эти команды незаметно для пользователя (это подобно записи макроса в Excel, когда макрорекодер записывает ваши действия и превращает их в команды VBA).
1. Создание таблиц
Для создания таблиц программным способом используют оператор CREATETABLE. Для этого нужно указать следующие данные:
Давайте создадим новую таблицу и назовем ее Customers:
CREATE TABLE Customers ( ID CHAR(10) NOT NULL Primary key, Custom_name CHAR(25) NOT NULL, Custom_address CHAR(25) NULL, Custom_city CHAR(25) NULL, Custom_Country CHAR(25) NULL, ArcDate CHAR(25) NOT NULL, DEFAULT NOWO) 
Да, мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которые будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR(10)), затем указываем может поле содержать пустые значения (NULL или NOT NULL), а также нужно указать поле, которое будет первичным ключом (Primary keytbl.
Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть если пользователь не укажет значение для определенного поля — оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE.
2. Обновление таблиц
Для того чтобы изменить таблицу в SQL используется оператор ALTER TABLE. При использовании данного оператора следует ввести следующую информацию:
Например, давайте добавим новую колонку в таблицу Sellers, в которой будем указывать телефон реализатора:
ALTER TABLE Sellers ADD Phone CHAR (20) 
Помимо добавления столбцов мы также можем их удалять. Давайте теперь удалим поле Phone. Для этого пропишем следующий запрос:
ALTER TABLE Sellers DROP COLUMN Phone 
3. Удаление таблиц
Удаление таблиц осуществляется с помощью оператора DROP TABLE. Чтобы удалить таблицу Sellers_new, мы можем прописать следующий запрос:
DROP TABLE Sellers_new Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.
- Изменение регистра букв в тексте
- Сумма прописью на украинском языке
- Поиск латиницы в кириллице и наоборот
- Транслитерация с украинского на английский
Создание таблиц в MS SQL Server. CREATE TABLE
В статье пойдёт речь о том, как создать таблицу в СУБД SQL Server от Microsoft. Для работы будет использована графическая среда SQL Server Management Studio (SSMS). Также рассмотрим, как создавать таблицу с помощью инструкции CREATE TABLE.
Предусловия
Перед началом работы у вас должен быть установлен как сам MS SQL Server, так и SSMS. Версию сервера следует выбирать, исходя из технических характеристик своего компьютера и установленной операционной системы. В статье используется MS SQL Server 2017 Express.
Также таблицы (tables) не могут существовать без базы данных. Поэтому, прежде чем приступить к созданию таблиц, следует создать пустую БД. Это делается очень просто – достаточно запустить среду SSMS и выполнить несколько простых действий: 1) кликаем правой кнопкой мыши по контейнеру «Базы данных» и выбираем «Создать базу данных»; 2) в появившемся поле прописываем имя БД; 3) нажимаем «ОК».
Исходные данные
База создана, поэтому можно подумать о её структуре. Планируется создание двух таблиц: 1. Goods – table с информацией о товарах. Будет содержать несколько столбцов: — ProductId. Представляет собой идентификатор товара. Значение не должно быть NULL. Также здесь будет первичный ключ; — Category. Это ссылка на категорию товара. Не NULL. Если товар не распределён в нужную категорию, ему присваивается категория по умолчанию («Не указана», «Не определена»); — ProductName. В этом столбце будет наименование товара. Не NULL; — Price. Речь идёт о стоимости. Если цена ещё не определена, возможен NULL. 2. Categories — вторая table. В ней будет описание категорий реализуемых товаров, представленное двумя столбцами: — CategoryId. Представляет собой идентификатор категории. Не NULL, первичный ключ; — CategoryName. Название категории, не NULL.
Для того чтобы нельзя было внести товар с несуществующей категорией, будет добавлено ограничение внешнего ключа.
Создание table в Management Studio
Когда структура ясна, можно приступать к созданию: 1) откройте контейнер «Базы данных»; 2) откройте только что созданную тестовую БД; 3) щёлкните правой кнопкой мыши по категории «Таблицы» и выберите пункт «Таблица».
В результате будет открыт специальный конструктор таблиц с тремя колонками: • имя столбца; • тип данных (подробнее о типах данных читайте здесь); • значения NULL (если да – ставим галочку).
Колонки заполняются с учётом спроектированной ранее структуры для table Categories.
Далее определяем первичный ключ (primary key), щелкая по требуемому столбцу правой кнопкой мыши (у нас это CategoryId). Также выбираем пункт «Задать первичный ключ».
Теперь надо обеспечить, чтобы в столбце автоматически генерировался уникальный ID-записи. Для этого идентификатору надо задать свойство IDENTITY. Делается путём включения пункта «Спецификация идентификатора».
Теперь пользователь может сохранить table (правая кнопка мыши -> «Сохранить» либо известное сочетание «Ctrl+S»). Останется ввести название таблицы и нажать «OK».
На очереди таблица Goods. Здесь осуществляются те же действия с той лишь разницей, что для столбца Category дополнительно задаётся дефолтное значение и создаётся ограничение внешнего ключа. Для этого в свойствах соответствующего столбца в значении по умолчанию пишем 1.
А для создания FOREIGN KEY щёлкните правой кнопкой мыши в любой области конструктора и выберите пункт «Отношения…».
Далее жмём кнопку «Добавить».
Теперь зададим спецификацию таблиц и столбцов:
В открывшемся окне указываем: • таблицу первичного ключа – Categories; • таблицу внешнего ключа – это текущая таблица, которая пока не создана, поэтому отображается в качестве Table_1. Тут следует выбирать столбец Category из текущей таблицы — именно он и станет выполнять роль внешнего ключа (сопоставление будет CategoryId = Category); • имя связи — название ограничения. Можно написать FK_Category.
Останется определить правила обновления и удаления. Правило обновления оставляем как есть (изменять идентификатор — не лучшая идея). А чтобы при удалении категории всем товарам присваивалось дефолтное значение, определим правило удаления как «Присвоить значение по умолчанию».
Останется сохранить созданную (created) таблицу, назвав её Goods. Может появиться предупреждающее сообщение — оно не должно вас смутить.
Созданные таблицы станут видны в обозревателе объектов.
Теперь с ними можно работать и добавлять данные, используя инструкцию INSERT.
Инструкция CREATE TABLE
Создать новую таблицу можно и с помощью инструкции CREATE TABLE. CREATE TABLE является ключевым словом, которое сообщает СУБД, что именно вы хотите выполнить. Синтаксис создания в языке (language) SQL следующий:
Как видите, после оператора CREATE TABLE надо указать имя либо идентификатор таблицы, потом список столбцов и тип данных для каждого столбца, а также первичный ключ.
Для примера создадим table CUSTOMERS. В качестве первичного ключа укажем столбец ID. Также установим ограничения для полей, которые не могут иметь нулевое значение при создании записей (NOT NULL):
Увидеть структуру созданной table можно, если сделать соответствующий запрос (query) командой DESC:
Как создать таблицу в sql
Перед созданием таблицы необходимо выбрать базу данных, в которую таблица будет записана. Это делается с помощью оператора USE :
MySQLUSE имя_базы_данных;Для создания таблицы используется оператор CREATE TABLE . Его базовый синтаксис имеет следующий вид:
MySQLCREATE TABLE [IF NOT EXIST] имя_таблицы ( столбец_1 тип_данных, [столбец_2 тип_данных,] ... [столбец_n тип_данных,] );Например, создадим таблицу пользователей.
MySQLCREATE TABLE Users ( id INT, name VARCHAR(255), age INT );INT , VARCHAR(255) - типы данных: числовой и строковый соответственно. Более подробно о них можно будет узнать в следующих статьях.
Вышеприведённое определение столбцов в таблице является упрощённым. Помимо названия столбца и его типа в определение иногда необходимо добавлять следующие необязательные параметры:
- PRIMARY KEY Указывает колонку или множество колонок как первичный ключ.
- AUTO_INCREMENT Указывает, что значение данной колонки будет автоматически увеличиваться при добавление новых записей в таблицу. Каждая таблица имеет максимум одну AUTO_INCREMENT колонку. Стоит отметить, что данный параметр можно применять только к целочисленным типам и к типам с плавающей запятой.
- UNIQUE Указывает, что значения в данной колонке для всех записей должны быть отличными друг от друга.
- NOT NULL Указывает, что значения в данной колонке должны быть отличными от NULL .
- DEFAULT Указывает значение по умолчанию. Данный параметр не применяется к типам BLOB , TEXT , GEOMETRY и JSON .
Для нашей таблицы пользователей можно указать следующие параметры:
MySQLCREATE TABLE Users ( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18 );Так, в данном примере:
- id - поле числового типа, являющееся первичным ключом;
- name - поле строкового типа с максимальной длиной в 255 символов, являющееся обязательным к заполнению;
- age - поле числового типа со значением по умолчанию равным 18.
Для того, чтобы посмотреть описание созданной таблицы можно воспользоваться оператором DESCRIBE .
MySQLDESCRIBE Users;
Field Type Null Key Default Extra id int(11) NO PRI name varchar(255) NO age int(11) NO 18 Помимо описания столбцов, при создании таблицы можно дополнительно указать следующие параметры:
Первичный ключ. Если вы не определили первичный ключ с помощью параметров столбца, то это можно сделать с помощью дополнительных параметров таблицы, добавив запись PRIMARY KEY (, ) после перечисления столбцов:
MySQLCREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, PRIMARY KEY (id) );MySQLCREATE TABLE Companies ( id INT, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) );Дальше нужно добавить в таблицу Users поле company – место работы нашего пользователя, которое будет ссылаться на запись в таблице Companies . Полный запрос для создания таблицы будет выглядеть так:
MySQLCREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, company INT, PRIMARY KEY (id) );Для того, чтобы при добавлении новых записей в таблицу Users гарантировать, что в колонке company находится идентификатор, существующий в таблице Companies , используется внешний ключ. Он имеет следующий синтаксис:
MySQLFOREIGN KEY (столбец_1>, столбец_n>) REFERENCES внешняя_таблица> (столбец_во_внешней_таблице_1>, столбец_во_внешней_таблице_n>) [ON DELETE действие] [ON UPDATE действие]Полный запрос для создания таблицы с внешним ключом будет таким:
MySQLCREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, company INT, PRIMARY KEY (id), FOREIGN KEY (company) REFERENCES Companies (id) );При наличии внешних ключей можно определить поведение текущей записи, при изменении или удалении записи, на которую она ссылается.
MySQLCREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, company INT, PRIMARY KEY (id), FOREIGN KEY (company) REFERENCES Companies (id) ON DELETE RESTRICT ON UPDATE CASCADE );ON DELETE RESTRICT означает, что если попробовать удалить компанию, у которой в таблице Users есть данные, база данных не даст этого сделать:
MySQLCannot delete or update a parent row: a foreign key constraint failsУдаление таблицы производится при помощи оператора DROP TABLE .
MySQLDROP TABLE [IF EXIST] имя_таблицы;