Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:

  • создание и редактирование таблиц;
  • сохранение и обновление записей;
  • выборка и фильтрация данных;
  • индексирование полей.

Этими азами мы и займемся: разберем синтаксис SQL-запросов в теории и на реальных примерах. К счастью, язык баз данных очень похож на простые английские предложения, так что вы легко с ним справитесь.

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

* В примерах используется SQL-синтаксис для MySQL 5. Запросы, предназначенные для разных СУБД, могут различаться.

  • Терминология
  • Уровень: Новичок
  • Уровень: уверенный пользователь
  • Уровень: SQL-мастер

Создание и удаление таблиц в ms sql server

Все данные в БД sql server хранятся в таблицах. Таблицы состоят из колонок, объединяющих значения одного типа, и строк — записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице — 1024 колонки, в одной строке (записи) — 8060 байтов.

sql server поддерживает следующие типы данных:

Таблицы можно создавать с помощью оператора create table языка transact-sql, а также с помощью enterprise manager. Рассмотрим сначала как это делается с помощью transact-sql.

Содание таблиц с помощью create table Для создания таблиц применяется оператор create table. Вот как выглядит упрощенный синтаксис этого оператора:

create table member
( member_no int not null,
lastname char(50) not null,
firstname char(50) not null,
photo image null
)

Этим оператором создается таблица member, состоящая из четырех колонок:

member_no — имеет тип int, значения null не допускаются lastname — имеет тип char(50) — 50 символов, значения null не допускаются firstname — аналогично lastname photo — имеет тип image (изображение), допускается значение null Примечание null — специальное обозначение того, что элемент данных не имеет значения. В описании типа колонки указывается, что элементы данных могут быть неинициализированы. При указании not null — «пустые» значения не допускаются. Если при вставке записи пропустить значение для такой колонки, вставка не произойдет, и sql server сгенерирует ошибку.

Попробуйте выполнить эту команду. Запустите query analyzer. Соединитесь с Вашим сервером. Из списка БД выберите sqlstep. Скопируйте в окно команд команду создания таблицы и выполните ее. (Если не забыли, надо нажать f5 или ctrl-e).

Чтобы точно удостовериться, в том, что таблица была создана, наберите команду:

sp_help member Выделите ее (как в обычном редакторе) и снова нажмите f5. В окно результатов будет выведена информация о таблице member.

sp_help — системная процедура, которая возвращает информацию об объектах БД (таблицах, хранимых процедурах и пр. Формат вызова таков:

sp_help <имя таблицы>

Удалить таблицу проще простого. Там же, в запросчике (так у нас называют query analyzer), наберите:

Выделите эту строку и нажмите f5. Таблица будет удалена, о чем Вас и уведомят. В нашем случае эта процедура проста. На самом деле в большой БД просто так удалить таблицу не получится, поскольку она будет связана с другими таблицами, и для удаления потребуется эти связи оборвать. Как это сделать см. следующие шаги.

Введите названия колонок, их тип и длину также как в примере выше. Нажмите на иконку с дискетой для сохранения таблицы и можете закрыть окно. Раскройте вашу БД, щелкните на категории «tables» и в списке таблиц увидите только что введенную таблицу. Для ее удаления выделите ее в списке, нажмите правую кнопку мыши и в контекстном меню выберите «delete». Таблица будет удалена.

Мы изучили как создаются и удалются таблицы. Следующий наш шаг — создание полноценной БД, на примере которой мы будем изучать:

что такое реляционная целостность БД и как она обеспечивается в sql server как модифицировать данные в таблицах (операторы insert, update, delete, select) как использовать хранимые процедуры и триггеры

Если у Вас возникала необходимость сохранить результирующий набор данных, который вернул SQL запрос, то данная статья будет Вам интересна, так как в ней мы рассмотрим инструкцию SELECT INTO, с помощью которой в Microsoft SQL Server можно создать новую таблицу и заполнить ее результатом SQL запроса. Начнем мы, конечно же, с описания самой инструкции SELECT INTO, а затем перейдем к примерам. Инструкция SELECT INTO в Transact-SQLSELECT INTO – инструкция в языке в T-SQL, которая создает новую таблицу и вставляет в нее результирующие строки из SQL запроса. Структура таблицы, т. количество и имена столбцов, а также типы данных и свойства допустимости значений NULL, будут на основе столбцов (выражений), указанных в списке выбора из источника в инструкции SELECT. Обычно инструкция SELECT INTO используется для объединения в одной таблице данных из нескольких таблиц, представлений, включая какие-то расчетные данные. Для того чтобы использовать инструкцию SELECT INTO требуется разрешение CREATE TABLE в базе данных, в которой будет создана новая таблица. Инструкция SELECT INTO имеет два аргумента:

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Важные моменты про инструкцию SELECT INTO

  • Инструкцию можно использовать для создания таблицы на текущем сервере, на удаленном сервере создание таблицы не поддерживается;
  • Заполнить данными новую таблицу можно как с текущей базы данных и текущего сервера, так и с другой базы данных или с удаленного сервера. Например, указывать полное имя базы данных в виде база_данных.схема.имя_таблицы или в случае с удаленным сервером, связанный_сервер.база_данных.схема.имя_таблицы;
  • Столбец идентификаторов в новой таблице не наследует свойство IDENTITY, если: инструкция содержит объединение (JOIN, UNION), предложение GROUP BY, агрегатную функцию, также, если столбец идентификаторов является частью выражения, получен из удаленного источника данных или встречается более чем один раз в списке выбора. Во всех подобных случаях столбец идентификаторов не наследует свойство IDENTITY и создается как NOT NULL;
  • С помощью инструкции SELECT INTO нельзя создать секционированную таблицу, даже если исходная таблица является секционированной;
  • В качестве новой таблицы можно указать обычную таблицу, а также временную таблицу, однако нельзя указать табличную переменную или возвращающий табличное значение параметр;
  • Вычисляемый столбец, если такой есть в списке выбора инструкции SELECT INTO, в новой таблице он становится обычным, т.е. не вычисляемым;
  • SELECT INTO нельзя использовать вместе с предложением COMPUTE;
  • С помощью SELECT INTO в новую таблицу не переносятся индексы, ограничения и триггеры, их нужно создавать дополнительно, после выполнения инструкции, если они нужны;
  • Предложение ORDER BY не гарантирует, что строки в новой таблице будут вставлены в указанном порядке.
  • В новую таблицу не переносится атрибут FILESTREAM. Объекты BLOB FILESTREAM в новой таблице будут как объекты BLOB типа varbinary(max) и имеют ограничение в 2 ГБ;
  • Объем данных, записываемый в журнал транзакций во время выполнения операций SELECT INTO, зависит от модели восстановления. В базах данных, в которых используется модель восстановления с неполным протоколированием, и простая модель, массовые операции, к которым относится SELECT INTO, минимально протоколируются. За счет этого инструкция SELECT INTO может оказаться более эффективней, чем отдельные инструкции по созданию таблицы и инструкции INSERT по заполнение ее данными.

Все примеры я буду выполнять в СУБД Microsoft SQL Server 2016 Express. Исходные данныеДля начала давайте создадим две таблицы и заполним их данными, эти таблицы мы и будем объединять в примерах.

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Пример 1 – Создание таблицы с помощью инструкции SELECT INTO с объединением данныхДавайте представим, что нам необходимо объединить две таблицы и сохранить полученный результат в новую таблицу (например, нам нужно получить товары с названием категории, к которой они относятся). —Операция SELECT INTO
SELECT T1. ProductId, T2. CategoryName, T1. ProductName, T1. Price
INTO TestTable3
FROM TestTable T1
LEFT JOIN TestTable2 T2 ON T1. CategoryId = T2. CategoryId

—Выборка данных из новой таблицы
SELECT * FROM TestTable3

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

В итоге мы создали таблицу с названием TestTable3 и заполнили ее объединёнными данными. Пример 2 – Создание временной таблицы с помощью инструкции SELECT INTO с группировкой данныхСейчас давайте, допустим, что нам нужны сгруппированные данные, например, информация о количестве товаров в определенной категории, при этом эти данные нам нужно сохранить во временную таблицу, например, эту информацию мы будем использовать только в SQL инструкции, поэтому нам нет необходимости создавать полноценную таблицу. —Создаем временную таблицу (#TestTable) с помощью инструкции SELECT INTO
SELECT T2. CategoryName, COUNT(T1. ProductId) AS CntProduct
INTO #TestTable
FROM TestTable T1
LEFT JOIN TestTable2 T2 ON T1. CategoryId = T2. CategoryId
GROUP BY T2. CategoryName

—Выборка данных из временной таблицы
SELECT * FROM #TestTable

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

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

Новая база данных создается с помощью оператора SQL CREATE DATABASE, за которым следует имя создаваемой базы данных. Для этой цели также используется оператор CREATE SCHEMA. Например, для создания новой базы данных под названием MySampleDB в командной строке mysql нужно ввести следующий запрос:

CREATE DATABASE MySampleDB;

Если все прошло нормально, команда сгенерирует следующий вывод:

Query OK, 1 row affected (0. 00 sec)

Если указанное имя базы данных конфликтует с существующей базой данных MySQL, будет выведено сообщение об ошибке:

ERROR 1007 (HY000): Can’t create database ‘MySampleDB’; database exists

В этой ситуации следует выбрать другое имя базы данных или использовать опцию IF NOT EXISTS. Она создает базу данных только в том случае, если она еще не существует:

CREATE DATABASE IF NOT EXISTS MySampleDB;

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

В определении столбца ​​задается тип данных, может ли столбец быть NULL, AUTO_INCREMENT. Оператор CREATE TABLE также позволяет указать столбец (или группу столбцов) в качестве первичного ключа. Прежде чем будет создавать таблицу, нужно выбрать базу данных. Это делается с помощью оператора SQL USE:

Создадим таблицу, состоящую из трех столбцов: customer_id, customer_name и customer_address. Столбцы customer_id и customer_name не должны быть пустыми (то есть NOT NULL). customer_id содержит целочисленное значение, которое будет автоматически увеличиваться при добавлении новых строк. Остальные столбцы будут содержать строки длиной до 20 символов. Первичный ключ определяется как customer_id.

CREATE TABLE customer
(
customer_id int NOT NULL AUTO_INCREMENT,
customer_name char(20) NOT NULL,
customer_address char(20) NULL,
PRIMARY KEY (customer_id)
) ENGINE=InnoDB;

Если для столбца указано значение NULL, тогда пустые строки будут добавляться в таблицу. И наоборот, если столбец определяется как NOT NULL, тогда пустые строки не будут добавлены​​.

Первичный ключ — это столбец, используемый для идентификации записей в таблице. Значение столбца первичного ключа должно быть уникальным. Если несколько столбцов объединены в первичный ключ, то комбинация значений ключей должна быть уникальной для каждой строки.

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

PRIMARY KEY (имя_столбца, имя_столбца

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

CREATE TABLE product
(
prod_code INT NOT NULL AUTO_INCREMENT,
prod_name char(30) NOT NULL,
prod_desc char(60) NULL,
PRIMARY KEY (prod_code, prod_name)
) ENGINE=InnoDB;

Когда столбец определяется с помощью AUTO_INCREMENT, его значение автоматически увеличивается каждый раз, когда в таблицу добавляется новая запись. Это удобно при использовании столбца в качестве первичного ключа. Благодаря AUTO_INCREMENTне нужно писать инструкции SQL для вычисления уникального идентификатора для каждой строки.

AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. И он должен быть проиндексирован (например, объявлен в качестве первичного ключа).

Значение AUTO_INCREMENT для столбца можно переопределить, указав новое при выполнении инструкции INSERT.

Можно запросить у MySQL самое последнее значение AUTO_INCREMENT, используя функцию last_insert_id() следующим образом:

Значения по умолчанию используются, когда значение не определено при вставке в базу данных. Значения по умолчанию задаются с помощью ключевого слова DEFAULT в операторе CREATE TABLE. Например, приведенный ниже запрос SQL задает значение по умолчанию для столбца sales_quantity:

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

  • — был представлен вMySQL версии 4.0 и классифицирован как безопасная среда для транзакций.Ее механизм гарантирует, что все транзакции будут завершены на 100%. При этом частично завершенные транзакции (например, в результате отказа сервера или сбоя питания) не будут записаны. Недостатком InnoDB является отсутствие поддержки полнотекстового поиска.
  • — высокопроизводительный движок с поддержкой полнотекстового поиска. Эта производительность и функциональность обеспечивается за счет отсутствия безопасности транзакций.
  • — с точки зрения функционала эквивалентен MyISAM, за исключением того, что все данные хранятся в оперативной памяти, а не на жестком диске. Это обеспечивает высокую скорость обработки. Временный характер данных, сохраняемых в оперативной памяти, делает движок MEMORY более подходящим для временного хранения таблиц.

Движки различных типов могут сочетаться в одной базе данных. Например, некоторые таблицы могут использовать движок InnoDB, а другие — MyISAM. Если во время создания таблицы движок не указывается, то по умолчанию MySQL будет использовать MyISAM.

Чтобы указать тип движка, который будет использоваться для таблицы, о поместите соответствующее определение ENGINE= после определения столбцов таблицы:

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

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

  • goods – таблица будет содержать информацию о товарах:
    product_id – идентификатор товара, данное значение должно автоматически генерироваться. Столбец не может содержать значения NULL и является первичным ключом;product_name – наименование товара, столбец не может содержать значения NULL;category – ссылка на категорию товара, столбец не может содержать значения NULL, но имеет значение по умолчанию, например, для случаев, когда товар еще не распределили в необходимую категорию, в этом случае товару будет присвоена категория по умолчанию («Не определена» или «Не указана»);price – цена товара, столбец может содержать значения NULL, например, с ценой еще не определились.
  • product_id – идентификатор товара, данное значение должно автоматически генерироваться. Столбец не может содержать значения NULL и является первичным ключом;
  • product_name – наименование товара, столбец не может содержать значения NULL;
  • category – ссылка на категорию товара, столбец не может содержать значения NULL, но имеет значение по умолчанию, например, для случаев, когда товар еще не распределили в необходимую категорию, в этом случае товару будет присвоена категория по умолчанию («Не определена» или «Не указана»);
  • price – цена товара, столбец может содержать значения NULL, например, с ценой еще не определились.
  • categories — таблица будет содержать описание категорий товаров:
    category_id – идентификатор категории, данное значение должно автоматически генерироваться. Столбец не может содержать значения NULL и является первичным ключом;category_name – наименование категории, столбец не может содержать значения NULL.
  • category_id – идентификатор категории, данное значение должно автоматически генерироваться. Столбец не может содержать значения NULL и является первичным ключом;
  • category_name – наименование категории, столбец не может содержать значения NULL.

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

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Схема – это пространство имен в базе данных, своего рода контейнер объектов внутри базы данных, который позволяет логически разделять данные на схемы.

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

  • Включить параметр «Не NULL», чтобы параметр не мог хранить значения NULL;
  • Включить параметр «Первичный ключ», чтобы столбец выполнял роль первичного ключа;
  • Включить идентификацию, чтобы в столбце автоматически генерировались значения.

В качестве типа данных выберем целочисленный тип integer.

Заметка! Как создать составной тип данных в PostgreSQL.

После ввода всех данных мы можем сохранить все изменения, нажав на кнопку «Сохранить», тем самым создав таблицу.

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

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Заметка! ТОП 5 популярных систем управления базами данных (СУБД).

Осталось определить ограничение внешнего ключа, иными словами, чтобы столбец category таблицы goods ссылался на столбец category_id таблицы categories, таким образом, мы определим связь между этими таблицами. Чтобы это сделать, переходим на вкладку «Ограничения» (основной формы создания таблиц), затем переходим на вкладку «Внешний ключ» и с помощью кнопки плюс «+» добавляем новый внешний ключ. Вводим название ограничения, и в детализированных свойствах на вкладке «Столбцы» задаем связь между таблицами, т. указываем столбцы и нажимаем на плюс «+» для добавления связи. После этого все требования, указанные в нашей задаче, будут выполнены и мы можем нажать кнопку «Сохранить» для создания таблицы.

Созданные таблицы отобразятся в обозревателе.

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Заметка! Установка и настройка PostgreSQL 12 на Debian 10.

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

Чтобы выполнить инструкции, открываем редактор запросов (Запросник), вводим инструкции и нажимаем «Execute». — Удаление таблиц
DROP TABLE IF EXISTS goods, categories;

— Создание таблицы categories
CREATE TABLE categories (
category_id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
);

— Создание таблицы goods
CREATE TABLE goods (
product_id INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category INT NOT NULL DEFAULT 1,
price NUMERIC(18,2) NULL,
CONSTRAINT fk_category_goods FOREIGN KEY (category) REFERENCES categories (category_id)
);

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Создание таблиц в PostgreSQL с помощью pgAdmin 4 (видеоматериал)https://www. youtube. com/embed/h5wgbJiSy7Q?feature=oembedНа сегодня это все, надеюсь, материал был Вам интересен и полезен, пока!

Установка

Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
teacher VARCHAR(100)
);

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(100),
classroom_id INT,
CONSTRAINT fk_classrooms
FOREIGN KEY(classroom_id)
REFERENCES classrooms(id)
);

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

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

Но что же случится, если у нас появится новый ученик, которому ещё не назначили класс? Неужели нам придется ждать, пока станет известно в каком он классе, и только после этого добавить его запись в базу данных?

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
category VARCHAR(20),
name VARCHAR(200),
due_date DATE,
weight FLOAT
);

CREATE TABLE grades (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
assignment_id INT,
score INT,
student_id INT,
CONSTRAINT fk_assignments
FOREIGN KEY(assignment_id)
REFERENCES assignments(id),
CONSTRAINT fk_students
FOREIGN KEY(student_id)
REFERENCES students(id)
);

Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.

Отлично! Мы установили, настроили и наполнили базу данных.

Итак, в этой статье мы научились:

  • создавать базу данных;
  • наполнять таблицы данными;
  • устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

  • виды фильтраций в запросах;
  • запросы с условиями типа if-else;
  • новые виды соединений таблиц;
  • функции для работы с массивами;

Терминология

База данных состоит из таблиц, а таблица – из колонок и строк.

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

Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.

Новичок

Несложно догадаться, что оператор CREATE создает новую таблицу в базе. Ему нужно передать описания всех полей таблицы в формате:

Создадим таблицу с данными о собаках и их рационе питания:

# создать таблицу dogs с 5 полями разных типовCREATE TABLE rations ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, weight INT DEFAULT 0, color VARCHAR(20), portion INT);

ALTER

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

# переименовать таблицу rations в portionsALTER TABLE rations RENAME TO portions;# добавить в таблицу portions числовое поле ageALTER TABLE portions ADD age INT;# удалить из таблицы portions поле colorALTER TABLE portions DROP COLUMN color;# переименовать поле name в dog_nameALTER TABLE portions CHANGE name dog_name VARCHAR(20) NOT NULL;

DROP и TRUNCATE

Оператор DROP удаляет таблицу из базы целиком:

# удалить таблицу portions DROP TABLE portions;

Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:

# очистить таблицу portions TRUNCATE TABLE portions;

Атрибуты и ограничения

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

Самые распространенные в SQL ограничения целостности (CONSTRAINTS):

  • DEFAULT – устанавливает значение по умолчанию;
  • AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
  • NOT NULL – запрещает создавать запись с пустым значением поля;
  • UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
  • PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
  • CHECK – проверяет значение поля на соответствие некоторому условию.

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

Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.

Решим новую задачу – составление списка президентов:

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

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

Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.

Для примера возьмем базу данных организации с таблицами сотрудников и отделов:

CREATE TABLE departments (  id INT AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(20));# в поле departament будет храниться id одного из отделов, # перечисленных в таблице departmentsCREATE TABLE employees (  id INT AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(50),  department INT,  salary INT,  FOREIGN KEY (department) REFERENCES departments(id));

Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.

Сохранение и обновление записей

Добавить в таблицу новую запись (или даже сразу несколько) очень просто:

INSERT INTO portions (dog_name, weight, portion) VALUES («Jack», 25, 250);INSERT INTO portions (dog_name, weight, portion)  VALUES («Max», 15, 180), («Charlie», 37, 350);

Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:

CREATE TABLE dogs(  id INT AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  weight INT DEFAULT 0);INSERT INTO dogs (name, weight) SELECT dog_name, weight FROM portions;

UPDATE

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

Вот так легким движением руки мы обнулили зарплату сразу у всех сотрудников.

Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.

UPDATE employees SET salary = 0 WHERE name = «Ivan Ivanov»;

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

DELETE

Можно удалить из таблицы все записи сразу или только те, которые соответствуют некоторому условию:

DELETE FROM employees;# Ивана Иванова пора увольнятьDELETE FROM employees WHERE name = «Ivan Ivanov»;

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

Уверенный пользователь

Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.

Вот небольшая демо-база, на которой вы можете попрактиковаться:

SELECT

# получить все поля из всех записейSELECT * FROM employees;# получить только имена и зарплаты всех работниковSELECT name, salary FROM employees;

Можно переименовывать поля для вывода:

SELECT name as employee FROM employees;

# по уменьшению зарплатыSELECT name, salary FROM employees ORDER BY salary ASC;# по увеличению зарплатыSELECT name, salary FROM employees ORDER BY salary DESC;

Ограничение количества результатов:

# пять самых высокооплачиваемых работникаSELECT name FROM employees ORDER BY salary DESC LIMIT 5;# все работники кроме пяти самых высокооплачиваемыхSELECT name FROM employees ORDER BY salary DESC OFFSET 5;

Агрегатные функции и группировка

SQL позволяет привести несколько записей таблицы к некоторому единому значению:

# общее количество работниковSELECT COUNT(*) FROM employees;# найти работника с максимальной зарплатойSELECT name, MAX(salary) FROM employees;# найти работника с минимальной зарплатойSELECT name, MIN(salary) FROM employees;# найти среднюю зарплату по предприятиюSELECT AVG(salary) FROM employees;# найти сумму всех зарплатSELECT SUM(salary) FROM employees;

Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:

# найти максимальную зарплату в каждом отделеSELECT department, MAX(salary) FROM employees GROUP BY department;

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

Объединение таблиц

Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.

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

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

Соединение бывает внутреннее (INNER) и внешнее (OUTER).

Внутреннее соединение

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

SELECT employees. name, employees. salary, departments. name as departmentFROM employees INNER JOIN departments ON employees. department = departments. id;

SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.

Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.

Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.

Внешнее соединение

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

Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:

SELECT employees. name, employees. salary, departments. name as departmentFROM employees LEFT JOIN departments ON employees. department = departments. id;

Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.

Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:

SELECT employees. name, employees. salary, employees. department, departments. name FROM employees RIGHT JOIN departments ON employees. department = departments. id;

И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.

SELECT employees. name, employees. salary, employees. department, departments. name FROM employees FULL JOIN departments ON employees. department = departments. id;

Декартово произведение

Оператор CROSS JOIN позволяет получить все возможные комбинации записей из двух таблиц:

SELECT * FROM employees CROSS JOIN departments;

Автосоединение

Кроме того, таблицу можно соединять с самой собой. Это пригодится, чтобы найти босса для каждого сотрудника. Сейчас в поле boss находится идентификатор другого сотрудника, необходимо вывести его имя:

SELECT e1. name, e1. department, e2. name as boss FROM employees e1 LEFT JOIN employees e2 ON e1. boss = e2

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

Объединение выборок

SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:

UNION

Объединить штатных и внештатных сотрудников

INTERSECT

Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе

SELECT name FROM employeesINTERSECTSELECT name FROM race_walking_team

MINUS

Найти всех сотрудников, которые не участвуют в сборной предприятия по спортивной ходьбе и заставить участвовать:

SELECT name FROM employeesMINUSSELECT name FROM race_walking_team

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?

SQL-мастер

Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.

У представлений есть еще одна важная миссия: обеспечение безопасности. Под view вы легко можете скрыть бизнес-логику и архитектуру базы и защитить свое приложение от нежелательных вторжений.

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

// простое представлениеCREATE VIEW view(name, salary)ASSELECT name, salaryFROM employees;

Если представление изменяемое, можно использовать при его создании CHECK OPTION для проверки изменений на соответствие некоторому предикату:

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

  • WITH CASCADED CHECK OPTION – проверяет запросы на всех уровнях вложенности;
  • WITH LOCAL CHECK OPTION – проверяет только «верхний» запрос.

Представление даже может ссылаться само на себя.

Чтобы удалить представление, используйте уже знакомый оператор DROP:

Индексы

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

// создание индекса для двух полейCREATE INDEX index_name ON table(column1, column2);

Наличие индексов в базе ускоряет выполнение операций SELECT и вычисление условий WHERE. Но есть и обратная сторона медали: замедляются операции вставки и удаления данных, так как при этих изменениях необходимо пересчитывать индексы.

Триггеры

Триггеры в SQL – это процедуры, которые автоматически запускаются при выполнении определенной операции (INSERT/UPDATE/DELETE) – до (BEFORE) или после (AFTER) нее.

// создание триггера// бонус к зарплате каждому новому сотрудникуDELIMITER $$CREATE OR MODIFY TRIGGER bonusesBEFORE INSERT ON employeesFOR EACH ROW BEGINSET NEW. salary = NEW. salary+3000;END$$

Удалить существующий триггер можно с помощью оператора DROP:

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса?