Как сложить два числа в sql запросе
Одна из основных функций SQL — это получение выборок данных из СУБД. Для этого в SQL используется оператор SELECT . Давайте рассмотрим несколько простых запросов с его участием.
Для начала важно понимать, что через оператор SELECT можно выводить данные не только из таблиц базы данных, но и произвольные строки, числа, даты и т.д. Например, так можно вывести произвольную строку:
MySQLSELECT "Hello world"
Для вывода всех полей из определённой таблицы используется символ * . Давайте взглянем на схему базы данных и выведем данные одной из таблиц.
MySQLSELECT * FROM FamilyMembers
member_id status member_name birthday 1 father Headley Quincey 1960-05-13T00:00:00.000Z 2 mother Flavia Quincey 1963-02-16T00:00:00.000Z 3 son Andie Quincey 1983-06-05T00:00:00.000Z 4 daughter Lela Quincey 1985-06-07T00:00:00.000Z 5 daughter Annie Quincey 1988-04-10T00:00:00.000Z 6 father Ernest Forrest 1961-09-11T00:00:00.000Z 7 mother Constance Forrest 1968-09-06T00:00:00.000Z Если необходимо вывести информацию только по определённым столбцам таблицы, а не всю сразу, то это можно сделать перечисляя названия столбцов через запятую:
MySQLSELECT member_id, member_name FROM FamilyMembers
member_id member_name 1 Headley Quincey 2 Flavia Quincey 3 Andie Quincey 4 Lela Quincey 5 Annie Quincey 6 Ernest Forrest 7 Constance Forrest В случае, если мы хотим вывести какие-то столбцы таблицы, но чтобы в итоговой выборке они были названы иначе, мы можем использовать псевдонимы (их также называют алиасами).
Их синтаксис достаточно простой, мы должны использовать оператор AS . Как в примере ниже:
MySQLSELECT member_id, member_name AS Name FROM FamilyMembers
member_id Name 1 Headley Quincey 2 Flavia Quincey 3 Andie Quincey 4 Lela Quincey 5 Annie Quincey 6 Ernest Forrest 7 Constance Forrest Или же можно обойтись и без него, просто написав желаемое наименование поля через пробел.
MySQLSELECT member_id, member_name Name FROM FamilyMembers
Псевдонимы могут содержать до 255 знаков (включая пробелы, цифры и специальные символы)
Это наш первый урок практического модуля. До этого были лишь теоретические, направленные на восполнение потенциальных пробелов в теории реляционных баз данных. После каждого практического урока мы предлагаем группу заданий для самостоятельной работы, чтобы сразу же закрепить полученную информацию.
Если вы пропустили модуль «Введение», а именно статью «Структура курса» , где описывался принцип работы и интерфейс блока «Самостоятельные упражнения», то рекомендуем вернуться к нему .
Как вывести несколько чисел используя только SQL запрос?
В MySQL для 5 чисел более элегантного нет. Если чисел больше, то имеет смысл делать join из пары-тройки подобных запросов, для перемножения количества чисел. Более красивые решения доступны только в других СУБД поддерживающих например CTE. Единственное усовершенствование - id можно писать только у первого числа в union
19 апр 2016 в 12:55
ну почему же, есть процедуры
19 апр 2016 в 12:56
@splash58 Ну процедура это не совсем "sql-запрос", а ТС просит "написать SQL-запрос"
19 апр 2016 в 12:57
ну он один раз функцию напишет, и будет ему запрос
19 апр 2016 в 12:583 ответа 3
Сортировка: Сброс на вариант по умолчанию
В MySQL единственный вариант (Алиас нужен только у первого числа):
select 1 ID union select 2 union select 3 union select 5
Самый красивый (для малого количества чисел) вариант можно написать в MS-SQL и Postrgess:
select * from (values (1),(2),(3),(5)) as t(id)
Oracle (С использованием системного типа в качестве коллекции):
select column_value ID from table(sys.odcinumberlist(1,2,3,5))
Если чисел значительно больше и они идут просто подряд, то почти универсальный (Из широко распространенных СУБД НЕ работает только в MySQL):
with Q as ( select 1 ID union all select ID+1 from Q where ID
Самый лаконичный (IMHO) вариант для большого количества чисел подряд в Oracle:
select rownum id from DUAL connect by rownum
Объединение, пересечение, вычитание запросов SQL
При запросах к базе данных часто необходимо делать объединение, пересечение и вычитание запросов.
1 янв. 2021 · 4 минуты на чтение
Следующий этап научиться объединять запросы, выполнять над ними операции пересечения и вычитания.
Спонсор поста
Оператор JOIN
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в раздел FROM операторов SELECT , UPDATE или DELETE .
Для выбора данных из нескольких таблиц необходимо перечислить имена требуемых таблиц в предложении FROM через запятую. В этом случае получим декартово произведение таблиц.
SELECT * FROM P, PD
База данных, используемая в примерах, находится в этом посте.
Существует два способа соединения таблиц:
1) Условие соединения указывается в предложении WHERE
Пример 1.
Пусть требуется вывести информацию о поставках в виде отношения R(pnum, pname, dnum, volume).SELECT P.pnum, pname, dnum, volume FROM P, PD WHERE P.pnum=PD.pnum
2) Условие соединения указывается в предложении FROM
Синтаксис такого соединения следующий:
FROM INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN ON .=
В соответствии с приведенным выше синтаксисом возможны следующие варианты соединения таблиц:
- Внутреннее соединение
- Внешнее соединение(левое, правое и полное)
Внутреннее соединение
С помощью внутреннего соединения выше рассмотренный пример можно записать следующим образом:
SELECT pname, dnum, volume FROM P INNER JOIN PD ON P.pnum=PD.pnum
Соединять можно и более двух таблиц.
Пример 2.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, volume) .SELECT pname, dname, volume FROM (P INNER JOIN PD ON P.pnum=PD.pnum) INNER JOIN D ON PD.dnum=D.dnum
Пример 3.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, dprice, volume, cost) , где значения поля стоимость поставки cost определяются как dprice*volume .
pname dname dprice volume cost Иванов Болт 10 руб. 100 1000 руб. SELECT pname, dname, str(dprice)+’руб.’ AS dprice, volume, str(dprice*volume)+’руб.’ AS cost FROM (P INNER JOIN PD ON P.pnum=PD.pnum) INNER JOIN D ON PD.dnum=D.dnum
При построении вычисляемых полей для строковых значений может быть использована операция конкатенации (соединения) строк, которая записывается с помощью символа & или + . Эта операция была использована для получения значений вычисляемых полей dprice и cost . Предварительно числовые значения были приведены к строке с помощью функции STR() .
Внешние соединение
Различают 3 вида внешних соединений:
- Левое внешнее соединение
- Правое внешнее соединение
- Полное внешнее соединение
Левое внешнее соединение
Левое внешнее соединение отношений А и В отличается от внутреннего тем, что в результирующее отношение добавляются все кортежи из отношения А (левой таблицы), при этом отсутствующие значения полей из отношения В (правой таблицы) будут заполняться NULL-значениями.
Полное внешние соединение
При выполнении полного внешнего соединения в результирующее отношение помещаются все кортежи из обоих отношений, неизвестные значения заполняются NULL-значениями.
Правое внешнее соединение
Правое соединение выполняется аналогично левому, но в результирующее отношение добавляются все кортежи из отношения B (правой таблицы).
Рандомный блок
Оператор UNION – Объединение
Для объединения запросов используется следующий синтаксис:
UNION [ALL]По умолчанию оператор UNION удаляет повторяющиеся строки из результирующего набора. Если указан параметр ALL, то результат будет содержать все строки, в том числе повторяющиеся.
Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:
- Таблицы должны иметь одинаковое количество столбцов.
- Типы данных в соответствующих столбцах должны быть совместимыми.
Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:
- Таблицы должны иметь одинаковое количество столбцов.
- Типы данных в соответствующих столбцах должны быть совместимыми.
При объединении данных из столбцов с разными именами результирующему столбцу присваивается имя столбца из первого запроса.
К результату объединения рекомендуется применять предложение ORDER BY , где можно ссылаться только на имена столбцов левого запроса в операторе UNION .
Пример 4.
Пусть задана таблица P1.
Номер Наименование 5 Орлов Определим результат следующего объединения:
SELECT * FROM P1 UNION SELECT * FROM P WHERE pnum=1 ORDER BY Номер
Номер Наименование 1 Иванов 5 Орлов Пример 5.
Получить номера деталей, цена которых более 20 рублей или суммарное поставляемое количество более 500 штук.Запрос разбивается на две части:
- Вывод номеров деталей, цена которых более 200 рублей.
- Вывод номеров деталей, которые поставляются в количестве более 500 штук.
Результирующая таблица получается при объединении двух частей запроса.
SELECT dnum FROM D WHERE dprice>20 UNION SELECT dnum FROM PD GROUP BY dnum HAVING sum(volume)>500 ORDER BY dnum
Пример 6.
Вывести информацию о деталях. В том случае если цена детали не указана вывести ‘цены нет’.Запрос разбивается на две части:
- Вывод информации о деталях, для которых указана цена.
- Вывод информации о деталях, для которых не указана цена. В этом случае в предложении SELECT вместо атрибута dprice нужно указывать строковую константу ‘цены нет’.
SELECT dnum, dname, dprice FROM D WHERE dprice IS NOT NULL UNION SELECT dnum, dname, 'цены нет' FROM PD WHERE dprice IS NULL ORDER BY dnum
Оператор INTERSECT – Пересечение
Пересечение запросов выполняется с помощью оператора INTERSECT , который выполняется аналогично оператору UNION .
Пример 7.
Вывести номера деталей, которые поставляет и поставщик с номером 1, и поставщик с номером 2.SELECT dnum FROM PD WHERE pnum=1 INTERSECT SELECT dnum FROM PD WHERE pnum=2
Оператор EXCEPT – Вычитание
Вычитание выполняется с помощью оператора EXCEPT , который выполняется аналогично оператору UNION .
Пример 8.
Вывести номера поставщиков, которые не поставляют детали в настоящее время.SELECT pnum FROM P EXCEPT SELECT pnum FROM PD
Пример 9.
Вывести номера поставщиков, которые не поставляют деталь № 2.SELECT pnum FROM P EXCEPT SELECT pnum FROM PD WHERE dnum=2
Пример 10.
Вывести номера поставщиков, которые поставляют только деталь № 1.SELECT pnum FROM PD WHERE dnum=1 EXCEPT SELECT pnum FROM PD WHERE dnum<>1
Заключение
В заключение, операторы JOIN , UNION , INTERSECT и EXCEPT являются важными инструментами при работе с реляционными базами данных.
Оператор JOIN используется для соединения двух таблиц, а операторы UNION , INTERSECT и EXCEPT используются для объединения, пересечения и вычитания данных из двух или более таблиц.
В целом, знание этих операторов и умение применять их в соответствии с требованиями конкретной задачи может значительно упростить работу с базами данных и обеспечить более эффективный анализ данных.
SQL запросы быстро. Часть 1
Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.
Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join'ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.
Практика
Введение в синтаксис будет рассмотрено на примере открытой базы данных, предназначенной специально для практики SQL. Чтобы твое обучение прошло максимально эффективно, открой ссылку ниже в новой вкладке и сразу запускай приведенные примеры, это позволит тебе лучше закрепить материал и самостоятельно поработать с синтаксисом.
Кликнуть здесь
После перехода по ссылке можно будет увидеть сам редактор запросов и вывод данных в центральной части экрана, список таблиц базы данных находится в правой части.
Структура sql-запросов
Общая структура запроса выглядит следующим образом:
SELECT ('столбцы или * для выбора всех столбцов; обязательно') FROM ('таблица; обязательно') WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно') GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно') HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно') ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')
Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS'ом.
SELECT, FROM
SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.
Выбрать все (обозначается как *) из таблицы Customers:
SELECT * FROM Customers
Выбрать столбцы CustomerID, CustomerName из таблицы Customers:
SELECT CustomerID, CustomerName FROM Customers
WHERE
WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.
Фильтрация по одному условию и одному значению:
select * from Customers WHERE City = 'London'
Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):
select * from Customers where City IN ('London', 'Berlin')
select * from Customers where City NOT IN ('Madrid', 'Berlin','Bern')
Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:
select * from Customers where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15
select * from Customers where City in ('London', 'Berlin') OR CustomerID > 4
GROUP BY
GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).
При использовании GROUP BY обязательно:
- перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
- агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.
select City, count(CustomerID) from Customers GROUP BY City
Группировка количества клиентов по стране и городу:
select Country, City, count(CustomerID) from Customers GROUP BY Country, City
Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:
select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails GROUP BY ProductID
Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:
select City, count(CustomerID) from Customers WHERE Country = 'Germany' GROUP BY City
Переименование столбца с агрегацией с помощью оператора AS. По умолчанию название столбца с агрегацией равно примененной агрегатной функции, что далее может быть не очень удобно для восприятия.
select City, count(CustomerID) AS Number_of_clients from Customers group by City
HAVING
HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).
Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:
select City, count(CustomerID) from Customers group by City HAVING count(CustomerID) >= 5
В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:
select City, count(CustomerID) as number_of_clients from Customers group by City HAVING number_of_clients >= 5
Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:
select City, count(CustomerID) as number_of_clients from Customers WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend') group by City HAVING number_of_clients >= 5
ORDER BY
ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.
Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:
select * from Customers ORDER BY City
Осуществлять сортировку можно и по нескольким столбцам, в этом случае сортировка происходит по порядку указанных столбцов:
select * from Customers ORDER BY Country, City
По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:
select * from Customers order by CustomerID DESC
Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:
select * from Customers order by Country DESC, City
JOIN
JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.
Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:
select * from Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID
Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,
select * from Orders join Customers on Orders.CustomerID = Customers.CustomerID where Customers.CustomerID >10
Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:
Другие типы JOIN'ов можно увидеть на замечательной картинке ниже:
В следующей части подробнее поговорим о типах JOIN'ов и вложенных запросах.
При возникновении вопросов/пожеланий, всегда прошу обращаться!