Как сделать псевдоним sql

Обновлено: 08.07.2024

Open with Desktop
View raw
Copy raw contents Copy raw contents

Copy raw contents

Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.

С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.

DDL – Data Definition Language

Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.

В эту группу входят следующие операторы:

CREATE – используется для создания объектов базы данных;
ALTER – используется для изменения объектов базы данных;
DROP – используется для удаления объектов базы данных.

DML – Data Manipulation Language

Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.

В эту группу входят самые распространённые операторы языка SQL:

SELECT – осуществляет выборку данных;
INSERT – добавляет новые данные;
UPDATE – изменяет существующие данные;
DELETE – удаляет данные.

DCL – Data Control Language

Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.

TCL – Transaction Control Language

Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.

Базовый синтаксис SQL команды SELECT

Одна из основных функций SQL — получение данных из СУБД. Для построения всевозможных запросов к базе данных используется оператор SELECT. Он позволяет выполнять сложные проверки и обработку данных.

Общая структура запроса

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

DISTINCT используется для исключения повторяющихся строк из результата
ALL (по умолчанию) используется для получения всех данных, в том числе и повторений
FROM перечисляет используемые в запросе таблицы из базы данных
WHERE — это условный оператор, который используется для ограничения строк по какому-либо условию
GROUP BY используется для группировки строк
HAVING применяется после группировки строк для фильтрации по значениям агрегатных функций
ORDER BY используется для сортировки. У него есть два параметра:
ASC (по умолчанию) используется для сортировки по возрастанию
DESC — по убыванию
LIMIT используется для ограничения количества строк для вывода

Псевдонимы используются для представления столбцов или таблиц с именем отличным от оригинального. Это может быть полезно для улучшения читабельности имён и создания более короткого наименования столбца или таблицы.

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

Для создания псевдонимов используется оператор AS:

Вы можете выводить любые строки и числа вместо столбцов:

Вы можете вывести любой столбец, определённый в таблице, например, town_to из таблицы Trip:

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

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

Эта конструкция используется для формирования словарей, примеры рассмотрим в главе про команду INSERT

Условный оператор WHERE

Ситуация, когда требуется сделать выборку по определенному условию, встречается очень часто. Для этого в операторе SELECT существует параметр WHERE, после которого следует условие для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.

Общая структура запроса с оператором WHERE

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

В условном операторе применяются операторы сравнения, специальные и логические операторы.

Операторы сравнения служат для сравнения 2 выражений, их результатом может являться ИСТИНА (1), ЛОЖЬ (0) и NULL.

Результат сравнения с NULL является NULL. Исключением является оператор эквивалентности.

Оператор	Описание
=	Оператор равенство
Оператор эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL NULL
<> или !=	Оператор неравенство
Оператор больше
>=	Оператор больше или равно

IS [NOT] NULL — позволяет узнать равно ли проверяемое значение NULL.

Для примера выведем всех членов семьи, у которых статус в семье не равен NULL:

[NOT] BETWEEN min AND max — позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max.

Выведем все данные о покупках с ценой от 100 до 500 рублей из таблицы Payments:

[NOT] IN — позволяет узнать входит ли проверяемое значение столбца в список определённых значений.

[NOT] LIKE шаблон [ESCAPE символ] — позволяет узнать соответствует ли строка определённому шаблону.

В шаблоне разрешается использовать два трафаретных символа:

символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении
символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении.

ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.

Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%:

Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.

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

Оператор NOT — меняет значение специального оператора на противоположный
Оператор OR — общее значение выражения истинно, если хотя бы одно из них истинно
Оператор AND — общее значение выражения истинно, если они оба истинны
Оператор XOR — общее значение выражения истинно, если один и только один аргумент является истинным

Выборка сводных данных (из двух и более таблиц)

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

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

id	Name
1	Иванов
2	Петров

id	Name	Phone
1	Иванов	322223
2	Петров	111111

То при простом запросе без условий

Получим примерно следующее:

id	Name	id2	Name2	Phone
1	Иванов	1	Иванов	322223
1	Иванов	2	Петров	111111
2	Петров	1	Иванов	322223
2	Петров	2	Петров	111111

Чтобы выбрать уникальные значения, нам нужно использовать оператор WHERE для связи этих таблиц

Сводные выборки нужны при импорте данных в базу. Сначала вы выделяете из таблиц импорта словари. А потом из таблиц импорта и словарей формируете запрос INSERT . SELECT для записи данных в основную таблицу.

Вложенные SQL запросы\

Вложенный запрос — это запрос на выборку, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса. Подзапрос может быть использован везде, где разрешены выражения.

Пример структуры вложенного запроса

Здесь, SELECT поля_таблиц FROM список_таблиц WHERE конкретное_поле IN (. ) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.

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

Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном SQL-запросе: DISTINCT, GROUP BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.

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

Подзапрос как скалярный операнд

Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).

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

Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.

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

Подзапросы с ANY, IN, ALL

ANY — ключевое слово, которое должно следовать за операцией сравнения (>, , = и т.д.), возвращающее TRUE, если хотя бы одно из значений столбца подзапроса удовлетворяет обозначенному условию.

ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.

IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <> ALL для NOT IN. Например, следующие запросы равнозначны:

Строковый подзапрос — это подзапрос, возвращающий единственную строку с более чем одной колонкой. Например, следующий запрос получает в подзапросе единственную строку, после чего по порядку попарно сравнивает полученные значения со значениями во внешнем запросе.

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

Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу "таблица_1":

Подзапросы как производные таблицы

Производная таблица — выражение, которое генерирует временную таблицу в предложении FROM, которая работает так же, как и обычные таблицы, которые вы указываете через запятую. Так выглядит общий синтаксис запроса с использованием производных таблиц:

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

Обработка вложенных запросов

Добавление данных, оператор INSERT

Для добавления новых записей в таблицу предназначен оператор INSERT.

Общая структура запроса с оператором INSERT

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

Значения можно вставлять перечислением с помощью слова values, перечислив их в круглых скобках через запятую или c помощью оператора select. Таким образом, добавить новые записей можно следующими способами:

Первичный ключ при добавлении новой записи

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

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

В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.

Теперь, зная синткасис команд INSERT и SELECT, можем разобраться как создать из исходного набора данных словари и загрузить данные в БД с учетом внешних ключей

Допустим есть список агентов (данные полученные от заказчика в виде CSV-файла), у которых есть поля название, тип и т.д. (далее по тексту я её называю таблица импорта)

В структуре БД поле "тип агента" создано как внешний ключ на таблицу типов

Для добавления "типов агентов" в таблицу AgentType мы будем использовать альтернативный синтаксис INSERT . SELECT

Пишем инструкцию SELECT, которая выбирает уникальные записи из таблицы импорта:

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

Этот запрос можно выполнить отдельно, чтобы проверить что получится

После отладки запроса SELECT перед ним допишем запрос INSERT:

Поле ID можно пропустить, оно автоинкрементное и создастся само (по крайней мере в MsSQL)
Количество вставляемых полей (Title) должно быть равным количеству выбираемых полей (Тип_агента)

Если в таблице есть обязательные поля, а нем неоткуда взять для них данные, то мы можем в SELECT вставить фиксированные значения (в примере пустая строка):

Заполнение основной таблицы

Тоже сначала пишем SELECT запрос, чтобы проверить те ли данные получаются

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

в поле AgentTypeID мы должны вставить ID соответсвующей записи из таблицы AgentType, поэтому выборка у нас из двух таблиц и чтобы не писать перед каждым полем полные названия таблиц мы присваиваем им алиасы

Т.е. мы выбираем перечисленные поля из таблицы agents_import и добавляем к ним ID агента у которого совпадает название.

При выборке из нескольких таблиц исходные данные перемножаются. Т.е. если мы не заполним перед этой выборкой словарь, то 100 * 0 = пустая выборка .

Если же мы не укажем условие WHERE, то выберутся, к примеру, 100 * 10 = 1000 записей (каждый агент будет в каждой категории). Поэтому важно, чтобы условие WHERE выбирало уникальные значения.

Естественно, количество внешних ключей в таблице может быть больше одного, в таком случае в секции FROM перечисляем все используемые словари и в секции WHERE перечисляем условия для всех таблиц объединив их логическим выражением AND

где алиасы b, c, d - словарные таблицы, а алиас "а" - таблица импорта

Написав и проверив работу выборки (она должна возвращать чтолько же записей, сколько в таблице импорта) дописываем команду вставки данных:

В каталоге data этого репозитория находится структура БД ( ms.sql ) и файлы для импорта: products_k_import.csv , materials_short_k_import.txt , productmaterial_k_import.xlsx .

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

Для создания псевдонимов используется оператор AS .

Практический пример

В вашей таблице Users есть столбец first_middle_last_name , который содержит в себе полное имя пользователя. У данного столбца очень длинное имя, и есть использовать не очень удобно. Поэтому его можно просто переименовать в более короткое и понятное имя: например, в name_user

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

Представление на основе одной или нескольких существующих таблиц баз данных (или представлений) создается с помощью инструкции CREATE VIEW и инструкции SELECT, которая является неотъемлемой частью инструкции CREATE VIEW. Поскольку создание представления всегда содержит запрос, инструкция CREATE VIEW принадлежит к языку манипуляции данными (DML), а не к языку описания данных (DDL). По этой причине создание и удаление представлений рассматривается в одной из следующих статей, после представления инструкций Transact-SQL для модификации данных.

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

Еще одним дополнительным объектом базы являются хранимые процедуры (stored procedure), которые создаются посредством инструкции CREATE PROCEDURE. Хранимая процедура - это последовательность инструкций Transact-SQL, созданная посредством языка SQL и процедурных расширений.

Схема (schema) - это объект базы данных, содержащий инструкции для создания таблиц, представлений и пользовательских разрешений. Схему можно рассматривать, как конструкцию, в которой собраны вместе несколько таблиц, соответствующие представления и пользовательские разрешения.

В Database Engine применяется такое же понятие схемы, как и в стандарте ANSI SQL. В стандарте SQL схема определяется как коллекция объектов базы данных, имеющая одного владельца и формирующая одно пространство имен. Пространство имен (namespace) - это набор объектов с однозначными именами. Например, две таблицы могут иметь одно и то же имя только в том случае, если они находятся в разных схемах. Схема является очень важным концептом в модели безопасности компонента Database Engine.

Триггером (trigger) называется объект базы данных, который задает определенное действие в ответ на определенное событие. Это означает, что когда для предопределенной таблицы происходит определенное событие (модификации, вставка или удаление данных), компонент Database Engine автоматически запускает одно или несколько дополнительных действий. Работу с триггерами, схемами, хранимыми процедурами и индексами мы рассмотрим позже.

Синоним (synonym) - это локальный объект базы данных, который предоставляет связь между самим собой и другим объектом, управляемым одним и тем же или связанным сервером баз данных. Синонимы объектов создаются посредством инструкции CREATE SYNONYM, применение которой показано в примере ниже:

В этом примере создается синоним таблицы CustomerSet в схеме Production базы данных TestCustomer. Этот синоним можно потом использовать в инструкциях языка DML, таких как SELECT, INSERT, UPDATE и DELETE.

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

Ограничения для обеспечения целостности и домены

- это набор всех возможных разрешенных значений, которые могут содержать столбцы таблицы. Почти во всех системах управления базами данных для определения таких возможных значений столбца используются такие типы данных, как INT, CHAR и DATE. Такого метода принудительного обеспечения "целостности домена" недостаточно, как можно увидеть в следующем примере.

Допустим в таблице person есть столбец zip, в котором указывается индекс города, в котором проживает данное лицо. Тип данных этого столбца можно определить как SMALLINT или CHAR(5). Определение типа данных столбца как SMALLINT будет неточным, потому что этот тип данных содержит все положительные и отрицательные целые числа в диапазоне от -2 15 до 2 15 -1. Объявление с использованием типа данных CHAR(5) будет еще менее точным, поскольку в таком случае можно будет использовать все буквенно-цифровые и специальные символы. Поэтому для точного определения данных столбца индексов требуется диапазон положительных значений от 00601 и 99950.

Более точно целостность домена можно принудительно обеспечить с помощью ограничений CHECK (определяемые в инструкции CREATE TABLE или ALTER TABLE), благодаря их гибкости и тому, что они всегда принудительно применяются при вставке или модифицировании данных столбца.

Язык Transact-SQL поддерживает домены посредством создания псевдонимов типов данных с помощью инструкции CREATE TYPE. Рассмотрению псевдонимов типов данных и типов данных среды CLR посвящены следующие два раздела.

Псевдонимы типов данных

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

Для создания псевдонимного типа данных обычно применяется инструкция CREATE TYPE. Далее показан синтаксис этой инструкции для определения псевдонима типа данных:

Использование инструкции CREATE TYPE для создания типа данных показано в примере ниже:

В этом коде создается псевдонимный тип данных Zip на основе стандартного типа данных SMALLINT. Теперь этот определенный пользователем тип данных можно присвоить столбцу таблицы, как показано в примере ниже:

Здесь тип данных столбца ZipCode таблицы Customer определяется псевдонимом типа данных Zip. Допустимые значения этого столбца требуется ограничить диапазоном целочисленных значений от 600 до 99950. Как можно видеть в примере, это ограничение можно наложить с помощью предложения CHECK.

Обычно компонент Database Engine неявно преобразовывает разные типы данных совместимых столбцов. Это также относится и к псевдонимным типам данных. Начиная с версии SQL Server 2008, стали поддерживаться определяемые пользователем табличные типы. В примере ниже показано создание такого типа с помощью инструкции CREATE TYPE.

Определяемый пользователем табличный тип данных User_table_t имеет два столбца: name и salary. Основное синтаксическое отличие табличных типов от псевдонимных состоит в наличии предложения AS TABLE, как это можно видеть в примере. Определяемые пользователем табличные типы обычно применяются с возвращающими табличные значения параметрами.

Типы данных CLR

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

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

Синтаксис псевдонима SQL для столбцов

Синтаксис псевдонима SQL для таблиц

Демо база данных

В этом руководстве мы будем использовать образец базы данных RUNOOB.

Ниже приведены данные журнала доступа к сайту "access_log":

Экземпляр псевдонима столбца

Следующий оператор SQL определяет два псевдонима: один для столбца имени, а другой - для столбца страны.

Подсказка:Если имя столбца содержит пробелы, необходимы двойные кавычки или квадратные скобки:

В следующем операторе SQL мы объединяем три столбца (url, alexa и country) вместе и создаем псевдоним с именем site_info:

Экземпляр псевдонима таблицы

Тот же оператор SQL без псевдонима:

Использование псевдонимов полезно в следующих ситуациях:

В запросе участвует более одной таблицы
Функция, используемая в запросе
Имена столбцов длинные или плохо читаемые
Необходимо объединить два или более столбца вместе

QL join используется для объединения строк из двух или более таблиц.

На следующем рисунке показаны 7 применений, связанных с LEFT JOIN, RIGHT JOIN, INNER JOIN и OUTER JOIN.

Наиболее распространенные типы JOIN:SQL INNER JOIN (простое JOIN). SQL INNER JOIN возвращает все строки, удовлетворяющие условиям JOIN из нескольких таблиц.

Обратите внимание, что "в таблице" Сайты "id"Столбец указывает на поле в таблице access_log"site_id". Две приведенные выше таблицы связаны столбцом site_id.

Затем, если мы запустим следующий оператор SQL (включая INNER JOIN):

Другой SQL JOIN

Прежде чем продолжить объяснение примеров, мы сначала перечислим различные типы SQL JOIN, которые вы можете использовать:

INNER JOIN: Если в таблице есть хотя бы одно совпадение, возвращается строка
LEFT JOIN: Даже если в правой таблице нет совпадений, все строки возвращаются из левой таблицы
RIGHT JOIN: Даже если в левой таблице нет совпадений, все строки возвращаются из правой таблицы
FULL JOIN: Пока есть совпадение в одной из таблиц, возвращать строки

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

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

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

Читайте также: