Создание таблиц — Основы реляционных баз данных
В этом уроке мы поработаем с таблицами: будем создавать их, добавлять, модифицировать и удалять данные. Также разберем типы данных таблицы.
Создание базы данных
Прежде чем создать таблицу, создадим базу данных 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 — добавление данных в таблицу
Существуют два основных синтаксиса инструкции INSERT INTO:
INSERT INTO ИМЯ _ ТАБЛИЦЫ ( столбец 1 , столбец 2 , столбец 3 , . . . столбец N )
VALUES ( значение 1 , значение 2 , значение 3 , . . . значение N ) ;
Здесь столбец1, столбец2, столбец3,… столбец N — это названия столбцов в таблице, в которые вы хотите вставить данные. Вы можете не указывать имя столбца в SQL-запросе, если добавляете значения для всех столбцов таблицы. Но убедитесь, что порядок значений соответствует порядку столбцов в таблице.
В таком случае синтаксис инструкции SQL INSERT INTO будет следующим:
Заполнить SQL таблицу данными из другой
Есть 2 таблицы: «PS» 
«S» 
Как заполнить колонку «UserId» в таблице «S» на основании данных из таблицы «PS»? Другими словами, для каждого «PS».»subscriptionId» нужно посмотреть «PS».»UserId» и записать его значение в «S».»userID» так чтобы «PS».»subscriptionId» = «S».»id». Необходимо фактически заполнить таблицу «S» Результат который хочу получить: 
Отслеживать
задан 6 дек 2020 в 15:05
27 7 7 бронзовых знаков
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Начните с новой таблицы:
truncate table "S"; Затем заполните ее данными:
insert into "S" ("id", "userId") select t.subs_id, coalesce("PS"."userId", 0) from generate_series(1, (select max("subscriptionId") from "PS"), 1) t(subs_id) left outer join "PS" on t.subs_id = "PS"."subscriptionId"; На самом деле таблица «S» является избыточным, поскольку она не содержит никакой новой информации, является лишь представлением существующих данных.
Поэтому подумайте о создании представления (возможно, материализованного) вместо таблицы.
create view "SV" as select t.subs_id as "id", coalesce("PS"."userId", 0) as "userId" from generate_series(1, (select max("subscriptionId") from "PS"), 1) t(subs_id) left outer join "PS" on t.subs_id = "PS"."subscriptionId"; Урок 1. Создание объектов базы данных и отправка запросов к ним
Схема обучения Начало работы с запросами с помощью Transact-SQL содержит более подробные материалы, а также практические примеры.
На этом занятии вы узнаете, как создать базу данных, создать таблицу в базе данных и получить доступ к данным таблицы и изменить их. Поскольку это занятие представляет собой введение в использование Transact-SQL, в нем не используются и не описываются многие параметры, доступные для этих инструкций.
Инструкции Transact-SQL могут быть написаны и пересланы ядру СУБД следующими способами:
- При помощи среды SQL Server Management Studio. Предполагается, что вы используете среду Среда Management Studio, но можно также использовать среду Среда Management Studio Express, которая может быть загружена бесплатно с веб-узла центра загрузки Майкрософт.
- С помощью служебной программы sqlcmd .
- Соединившись из создаваемого приложения.
Исходный код исполняется в компоненте Компонент Database Engine таким же образом и с теми же разрешениями, независимо от того, как был передан исходный код инструкций.
Чтобы выполнить инструкцию языка Transact-SQL в Management Studio, откройте Management Studio и подключитесь к экземпляру ядра СУБД SQL Server.
Предварительные требования
Для работы с этим руководством необходима среда SQL Server Management Studio и доступ к экземпляру SQL Server.
- Установите SQL Server Management Studio.
Если у вас нет экземпляра SQL Server, создайте его. Чтобы создать экземпляр, выберите свою платформу по следующим ссылкам. При выборе проверки подлинности SQL используйте учетные данные SQL Server.
- Windows: скачайте SQL Server 2022 Developer Edition.
- Linux: скачайте SQL Server 2022 в контейнер.
Создание базы данных
Как и многие инструкции Transact-SQL, инструкция CREATE DATABASE имеет обязательный параметр: имя базы данных. Кроме этого, у инструкции CREATE DATABASE имеется ряд необязательных параметров, таких как расположение на диске, где требуется хранить файлы базы данных. При выполнении инструкции CREATE DATABASE без дополнительных параметров для многих из них SQL Server использует значения по умолчанию.
-
В окне Редактор запросов введите, но не выполняйте следующий код:
CREATE DATABASE TestData GO При создании базы данных SQL Server создает копию model базы данных и переименовывает ее в имя базы данных. Эта операция обычно занимает несколько секунд, если только с помощью дополнительного параметра не указан большой исходный размер базы данных.
Когда в одном пакете представлено несколько инструкций, они разделяются с помощью ключевого слова GO. Ключевое слово GO является необязательным, если в пакете содержится только одна инструкция.
Создание таблицы
Чтобы создать таблицу, нужно указать имя таблицы, имена и типы данных для каждого столбца таблицы. Также рекомендуется указывать, допускаются ли значения NULL для каждого из столбцов. Для создания таблицы необходимо иметь разрешение CREATE TABLE и разрешение ALTER SCHEMA для схемы, которая будет содержать таблицу. Предопределинная роль базы данных db_ddladmin имеет эти разрешения.
Большинство таблиц имеют первичный ключ, состоящий из одной или нескольких столбцов таблицы. Первичный ключ всегда уникален. Компонент Компонент Database Engine применяет ограничение на то, что любое значение первичного ключа не может повторяться в таблице.
Список типов данных и ссылки на их описание см. в разделе Типы данных (Transact-SQL).
Компонент Компонент Database Engine может быть установлен с учетом регистра и без учета регистра. Если компонент Компонент Database Engine установлен с учетом регистра, имена объектов должны иметь одно и тоже имя. Например, таблица с именем OrderData будет отличаться от таблицы ORDERDATA. Если компонент Компонент Database Engine установлен без учета регистра, эти два имени таблицы будут рассматриваться как одна таблица, то есть имя может быть использовано только один раз.
Переключение соединения редактора запросов на базу данных TestData
В окне редактора запросов введите и выполните следующий код, чтобы изменить соединение на базу данных TestData .
USE TestData GO Создание таблицы
В окне редактора запросов введите и выполните следующий код, чтобы создать таблицу Products . Столбцы таблицы имеют имена ProductID , ProductName , Price и ProductDescription . Столбец ProductID является первичным ключом таблицы. int , varchar(25) , money и varchar(max) . Только столбцы Price и ProductionDescription могут быть пустыми при вставке или изменении строки. Данная инструкция содержит необязательный элемент ( dbo. ), называемый схемой. Схема — это объект базы данных, к которому принадлежит таблица. Если вы являетесь администратором, схемой по умолчанию будет схема dbo . dbo означает владельца базы данных.
CREATE TABLE dbo.Products (ProductID int PRIMARY KEY NOT NULL, ProductName varchar(25) NOT NULL, Price money NULL, ProductDescription varchar(max) NULL) GO Вставка данных в таблицу и их обновление
Теперь, когда таблица создана Products , можно приступать к вставке данных в нее с помощью инструкции INSERT. После вставки данных содержимое строки изменяется с помощью инструкции UPDATE. Используйте предложение WHERE инструкции UPDATE, чтобы ограничить обновление одной строкой. Четыре оператора вводит следующие данные.
| ProductID | ProductName | Цена | ProductDescription |
|---|---|---|---|
| 1 | Clamp | 12,48 | Workbench clamp |
| 50 | Screwdriver | 3,17 | Flat head |
| 75 | Tire Bar | Tool for changing tires. | |
| 3000 | 3 mm Bracket | 0,52 |
Базовый синтаксис: INSERT, имя таблицы, список столбцов, VALUES, а затем список добавляемых значений. Два дефиса перед строкой указывают, что строка является комментарием, а текст игнорируется компилятором. В этом случае примечание описывает возможные варианты синтаксиса.
Вставка данных в таблицу
- Выполните следующую инструкцию, чтобы добавить строку в таблицу Products , которая была создана в предыдущей задаче.
-- Standard syntax INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription) VALUES (1, 'Clamp', 12.48, 'Workbench clamp') GO Если вставка выполнена, перейдите к следующему шагу. Если вставка завершается сбоем, это может быть вызвано тем, что в таблице Product уже есть строка с таким ИД продукта. Чтобы продолжить, удалите все строки в таблице и повторите предыдущий шаг. TRUNCATE TABLE удаляет все строки в таблице. Выполните следующую команду, чтобы удалить все строки в таблице:
TRUNCATE TABLE TestData.dbo.Products; GO -- Changing the order of the columns INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription) VALUES ('Screwdriver', 50, 3.17, 'Flat head') GO -- Skipping the column list, but keeping the values in order INSERT dbo.Products VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.') GO -- Dropping the optional dbo and dropping the ProductDescription column INSERT Products (ProductID, ProductName, Price) VALUES (3000, '3 mm Bracket', 0.52) GO Обновление таблицы продуктов
Введите и выполните следующую инструкцию UPDATE , чтобы изменить значение ProductName второго продукта со значения Screwdriver на значение Flat Head Screwdriver .
UPDATE dbo.Products SET ProductName = 'Flat Head Screwdriver' WHERE ProductID = 50 GO Чтение данных из таблицы
Для чтения данных в таблице используется инструкция SELECT. Инструкция SELECT является одной из наиболее важных инструкций Transact-SQL, и для нее существует много разновидностей синтаксиса. В этом руководстве вы будете работать с пятью базовыми версиями.
Чтение данных в таблице
- Чтобы прочитать данные из таблицы Products , введите и выполните следующие инструкции.
-- The basic syntax for reading data from a single table SELECT ProductID, ProductName, Price, ProductDescription FROM dbo.Products GO -- Returns all columns in the table -- Does not use the optional schema, dbo SELECT * FROM Products GO -- Returns only two of the columns from the table SELECT ProductName, Price FROM dbo.Products GO -- Returns only two of the records in the table SELECT ProductID, ProductName, Price, ProductDescription FROM dbo.Products WHERE ProductID < 60 GO -- Returns ProductName and the Price including a 7% tax -- Provides the name CustomerPays for the calculated column SELECT ProductName, Price * 1.07 AS CustomerPays FROM dbo.Products GO Полезные функции в инструкции SELECT
Сведения о некоторых функциях, которые можно использовать для работы с данными в инструкциях SELECT, см. в следующих статьях:
Создание представлений и хранимых процедур
Представление является хранимой инструкцией SELECT, а хранимая процедура представляет собой одну или более инструкций Transact-SQL, выполняемых в виде пакета.
Представления запрашиваются как таблицы и не принимают параметры. Хранимые процедуры сложнее, чем представления. Хранимые процедуры содержат как входные, так и выходные параметры и могут содержать инструкции, которые управляют потоком кода, например IF и WHILE. Использование хранимых процедур для всех повторяющихся действий в базе данных является хорошим стилем программирования.
В этом примере используется инструкция CREATE VIEW для создания представления, которое выбирает только два столбца в Products таблице. Затем с помощью инструкции CREATE PROCEDURE создайте хранимую процедуру, которая принимает параметр price и возвращает только те продукты, стоимость которых меньше указанного значения параметра.
Создание представления
Выполните следующую инструкцию, создающую представление, которое выполняет инструкцию select и возвращает названия и цены продуктов пользователю.
CREATE VIEW vw_Names AS SELECT ProductName, Price FROM Products; GO Тестирование представления
С представлениями обращаются так же, как с таблицами. Используйте инструкцию SELECT , чтобы получить доступ к представлению.
SELECT * FROM vw_Names; GO Создание хранимой процедуры
В следующем примере создается хранимая процедура pr_Names с входным параметром @VarPrice типа money . Эта хранимая процедура печатает инструкцию Products less than , соединенную операцией сцепления с входным параметром, тип которого преобразуется из money в varchar(10) . Затем процедура выполняет инструкцию SELECT на представлении, передавая входной параметр в предложение WHERE . Возвращаются все продукты, цена которых меньше значения входного параметра.
CREATE PROCEDURE pr_Names @VarPrice money AS BEGIN -- The print statement returns text to the user PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10)); -- A second statement starts here SELECT ProductName, Price FROM vw_Names WHERE Price < @VarPrice; END GO Тестирование хранимой процедуры
Чтобы выполнить хранимую процедуру, введите и выполните следующую инструкцию. Эта процедура должна возвратить названия двух продуктов, введенных в таблицу Products на занятии 1, цена которых меньше 10.00 .
EXECUTE pr_Names 10.00; GO Дальнейшие действия
В следующей статье вы узнаете, как настроить разрешения в объектах базы данных. Объекты, созданные в уроке 1, также будут использоваться в уроке 2.
Дополнительные сведения см. в следующей статье: