Операции с данными
Для добавления данных применяется команда INSERT , которая имеет следующий формальный синтаксис:
INSERT INTO имя_таблицы (столбец1, столбец2, . столбецN) VALUES (значение1, значение2, . значениеN)
После INSERT INTO идет имя таблицы, затем в скобках указываются все столбцы через запятую, в которые надо добавлять данные. И в конце после слова VALUES в скобках перечисляются добавляемые значения.
Допустим, у нас в базе данных есть следующая таблица:
CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC );
Добавим в нее одну строку с помощью команды INSERT:
INSERT INTO Products VALUES (1, 'Galaxy S9', 'Samsung', 4, 63000)
После удачного выполнения в pgAdmin в поле сообщений должно появиться сообщение «INSERT 0 1»:
Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым столбцом идет Id, поэтому этому столбцу передаетсячисло 1. Второй столбец называется ProductName, поэтому второе значение — строка «Galaxy S9» будет передано именно этому столбцу и так далее. То есть значения передаются столбцам следующим образом:
- Id: 1
- ProductName: ‘Galaxy S9’
- Manufacturer: ‘Samsung’
- ProductCount: 4
- Price: 63000
Также при вводе значений можно указать непосредственные столбцы, в которые будут добавляться значения:
INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ('iPhone X', 71000, 'Apple');
Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:
- ProductName: ‘iPhone X’
- Manufacturer: ‘Apple’
- Price: 71000
Для столбца Id значение будет генерироваться автоматически базой данных, так как он представляет тип Serial. То есть к значению из последней строки будет добавляться единица.
Для остальных столбцов будет добавляться значение по умолчанию, если задан атрибут DEFAULT (например, для столбца ProductCount), значение NULL. При этом неуказанные столбцы (за исключением тех, которые имеют тип Serial) должны допускать значение NULL или иметь атрибут DEFAULT.
Если конкретные столбцы не указываются, как в первом примере, тогда мы должны передать значения для всех столбцов в таблице.
Также мы можем добавить сразу несколько строк:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone 6', 'Apple', 3, 36000), ('Galaxy S8', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000)
В данном случае в таблицу будут добавлены три строки.
Возвращение значений
Если мы добавляем значения только для части столбцов, то мы можем не знать, какие значения будут у других столбцов. Например, какое значени получит столбец Id у товара. С помощью оператора RETURNING мы можем получить это значение:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES('Desire 12', 'HTC', 8, 21000) RETURNING id;
Как добавить данные в таблицу postgresql
Сразу после создания таблицы она не содержит никаких данных. Поэтому, чтобы она была полезна, в неё прежде всего нужно добавить данные. По сути данные добавляются в таблицу по одной строке. И хотя вы конечно можете добавить в таблицу несколько строк, добавить в неё меньше, чем строку, невозможно. Даже если вы указываете значения только некоторых столбцов, создаётся полная строка.
Чтобы создать строку, вы будете использовать команду INSERT . В этой команде необходимо указать имя таблицы и значения столбцов. Например, рассмотрим таблицу товаров из Главы 5:
CREATE TABLE products ( product_no integer, name text, price numeric );
Добавить в неё строку можно было бы так:
INSERT INTO products VALUES (1, 'Cheese', 9.99);
Значения данных перечисляются в порядке столбцов в таблице и разделяются запятыми. Обычно в качестве значений указываются константы, но это могут быть и скалярные выражения.
Показанная выше запись имеет один недостаток — вам необходимо знать порядок столбцов в таблице. Чтобы избежать этого, можно перечислить столбцы явно. Например, следующие две команды дадут тот же результат, что и показанная выше:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99); INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
Многие считают, что лучше всегда явно указывать имена столбцов.
Если значения определяются не для всех столбцов, лишние столбцы можно опустить. В таком случае эти столбцы получат значения по умолчанию. Например:
INSERT INTO products (product_no, name) VALUES (1, 'Cheese'); INSERT INTO products VALUES (1, 'Cheese');
Вторая форма является расширением PostgreSQL . Она заполняет столбцы слева по числу переданных значений, а все остальные столбцы принимают значения по умолчанию.
Для ясности можно также явно указать значения по умолчанию для отдельных столбцов или всей строки:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT); INSERT INTO products DEFAULT VALUES;
Одна команда может вставить сразу несколько строк:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);
Также возможно вставить результат запроса (который может не содержать строк либо содержать одну или несколько):
INSERT INTO products (product_no, name, price) SELECT product_no, name, price FROM new_products WHERE release_date = 'today';
Это позволяет использовать все возможности механизма запросов SQL (см. Главу 7) для вычисления вставляемых строк.
Подсказка
Когда нужно добавить сразу множество строк, возможно будет лучше использовать команду COPY . Она не такая гибкая, как INSERT , но гораздо эффективнее. Дополнительно об ускорении массовой загрузки данных можно узнать в Разделе 14.4.
| Пред. | Наверх | След. |
| Глава 6. Модификация данных | Начало | 6.2. Изменение данных |
6.1. Добавление данных
Сразу после создания таблицы она не содержит никаких данных. Поэтому, чтобы она была полезна, в неё прежде всего нужно добавить данные. По сути данные добавляются в таблицу по одной строке. И хотя вы конечно можете добавить в таблицу несколько строк, добавить в неё меньше, чем строку, невозможно. Даже если вы указываете значения только некоторых колонок, создаётся полная строка.
Чтобы создать строку, вы будете использовать команду INSERT. В этой команде необходимо указать имя таблицы и значения колонок. Например, рассмотрим таблицу товаров из Главы 5:
CREATE TABLE products ( product_no integer, name text, price numeric );
Добавить в неё строку можно было бы так:
INSERT INTO products VALUES (1, 'Cheese', 9.99);
Значения данных перечисляются в порядке колонок в таблице и разделяются запятыми. Обычно в качестве значений указываются константы, но это могут быть и скалярные выражения.
Показанная выше запись имеет один недостаток — вам необходимо знать порядок колонок в таблице. Чтобы избежать этого, можно перечислить колонки явно. Например, следующие две команды дадут тот же результат, что и показанная выше:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99); INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
Многие считают, что лучше всегда явно указывать имена колонок.
Если значения определяются не для всех колонок, лишние колонки можно опустить. В таком случае эти колонки получат значения по умолчанию. Например:
INSERT INTO products (product_no, name) VALUES (1, 'Cheese'); INSERT INTO products VALUES (1, 'Cheese');
Вторая форма является расширением PostgreSQL . Она заполняет колонки слева по числу переданных значений, а все остальные колонки принимают значения по умолчанию.
Для ясности можно также явно указать значения по умолчанию для отдельных колонок или всей строки:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT); INSERT INTO products DEFAULT VALUES;
Одна команда может вставить сразу несколько строк:
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);
Подсказка: Когда нужно добавить сразу множество строк, возможно будет лучше использовать команду COPY. Она не такая гибкая, как INSERT, но гораздо эффективнее. Дополнительно об ускорении массовой загрузки данных можно узнать в Разделе 14.4.
| Пред. | Начало | След. |
| Модификация данных | Уровень выше | Изменение данных |
Создание таблиц — Основы реляционных баз данных
В этом уроке мы поработаем с таблицами: будем создавать их, добавлять, модифицировать и удалять данные. Также разберем типы данных таблицы.
Создание базы данных
Прежде чем создать таблицу, создадим базу данных 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 студентов
Наши выпускники работают в компаниях: