Главная страница
qrcode

Руководство по SQLServer. Руководство по ms sql server


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница5 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
SETS и OVER.
ROLLUP
Оператор ROLLUP добавляет суммирующую строку в результирующий набор:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer WITH ROLLUP
Как видно из скриншота, в конце таблицы была добавлена дополнительная строка, которая суммирует значение столбцов.
Альтернативный синтаксис запроса, который можно использовать, начиная с версии MS SQL Server
2008:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY ROLLUP(Manufacturer)
При группировке по нескольким критериям ROLLUP будет создавать суммирующую строку для каждой из подгрупп:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer, ProductCount WITH ROLLUP
MS SQL Server и T-SQL | Расширения для группировки
При сортировке с помощью ORDER BY следует учитывать, что она применяется уже после добавления суммирующей строки.
CUBE
CUBE похож на ROLLUP за тем исключением, что CUBE добавляет суммирующие строки для каждой комбинации групп.
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
GROUP BY Manufacturer, ProductCount WITH CUBE
05.04.2018
MS SQL Server и T-SQL | Расширения для группировки
GROUPING SETS
Оператор GROUPING SETS аналогично ROLLUP и CUBE добавляет суммирующую строку для групп.
Но при этом он не включает сами группам:
SELECT Manufacturer, COUNT(*) AS Models, ProductCount
FROM Products
GROUP BY GROUPING SETS(Manufacturer, ProductCount)
MS SQL Server и T-SQL | Расширения для группировки
При этом его можно комбинировать с ROLLUP или CUBE. Например, кроме суммирующих строк по каждой из групп добавим суммирующую строку для всех групп:
SELECT Manufacturer, COUNT(*) AS Models,
ProductCount, SUM(ProductCount) AS Units
FROM Products
GROUP BY GROUPING SETS(ROLLUP(Manufacturer), ProductCount)
С помощью скобок можно определить более сложные сценарии группировки:
SELECT Manufacturer, COUNT(*) AS Models,
ProductCount, SUM(ProductCount) AS Units
FROM Products
GROUP BY GROUPING SETS((Manufacturer, ProductCount), ProductCount)
MS SQL Server и T-SQL | Расширения для группировки
OVER
Выражение OVER позволяет суммировать данные, при этому возвращая те строки, которые использовались для получения суммированных данных. Например, найдем количество моделей и общее количество товаров этих моделей по производителю:
SELECT ProductName, Manufacturer, ProductCount,
COUNT(*) OVER (PARTITION BY Manufacturer) AS Models,
SUM(ProductCount) OVER (PARTITION BY Manufacturer) AS Units
FROM Products
Выражение OVER ставится после агрегатной функции, затем в скобках идет выражение PARTITION
BY и столбец, по которому выполняется группировка.
То есть в данном случае мы выбираем название модели, производителя, количество единиц модели и добавляем к этому количество моделей для данного производителя и общее количество единиц всех моделей производителя:
MS SQL Server и T-SQL | Подзапросы
INSERT INTO Customers VALUES ('Tom'), ('Bob'),
('Sam')
Подзапросы
Выполнение подзапросов
T-SQL поддерживает функциональность подзапросов (subquery), то есть таких запросов, которые могут встроены в другие запросы.
Например, создадим таблицы для товаров, покупателей и заказов:
USE productsdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Таблица Orders содержит ссылки на две другие таблицы через поля ProductId и CustomerId.
Добавим в таблицы некоторые данные:
INSERT INTO Products
VALUES ('iPhone 6', 'Apple', 2, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi 5X', 'Xiaomi', 2, 26000),
('OnePlus 5', 'OnePlus', 6, 38000)
MS SQL Server и T-SQL | Подзапросы
INSERT INTO Orders
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-11',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-13',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
(SELECT Id FROM Customers WHERE FirstName='Bob'),
'2017-07-11',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
)
Здесь интерес представляет добавление элементов в таблицу Orders. Например, первый заказ был сделан покупателем Tom на товар Galaxy S8. Соответственно в таблицу Orders нам надо сохранить информацию о заказе, где поле ProductId указывает на Id товара Galaxy S8, поле Price - на его цену, а поле CustomerId - на Id покупателя Tom. Но на момент написания запроса нам может быть неизвестен ни Id покупателя, ни Id товара, ни цена товара. В этом случае можно выполнить подзапрос.
Подзапрос выполняет команду SELECT и заключается в скобки. В данном же случае при добавлении одного товара выполняется три подзапроса. Каждый подзапрос возвращает одного скалярное значение, например, числовой идентификатор.
В данном случае подзапросы выполнялись к другой таблице, но могут выполняться и к той же, к которой вызывается основной запрос. Например, найдем товары из таблицы Products, которые имеют минимальную цену:
SELECT *
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products)
Или найдем товары, цена которых выше средней:
SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
MS SQL Server и T-SQL | Подзапросы
Коррелирующие подзапросы
Подзапросы бывают коррелирующими и некоррелирующими. В примерах выше команды SELECT
выполняли фактически один подзапрос для всей команды, например, подзапрос возвращает минимальную или среднюю цену, которая не изменится, сколько бы мы строк не выбирали в основном запросе. То есть результат подзапроса не зависел от строк, которые выбираются в основном запросе. И такой подзапрос выполняется один раз для всего внешнего запроса.
Но также существуют коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе.
Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:
SELECT CreatedAt,
Price,
(SELECT ProductName FROM Products
WHERE Products.Id = Orders.ProductId) AS Product
FROM Orders
Здесь для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.
Коррелирующий подзапрос может выполняться и для той же таблицы, к которой выполняется основной запрос. Например, выберем из таблицы Products те товары, стоимость которых выше средней цены товаров для данного производителя:
MS SQL Server и T-SQL | Подзапросы
SELECT ProductName,
Manufacturer,
Price,
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer) AS AvgPrice
FROM Products AS Prods
WHERE Price >
(SELECT AVG(Price) FROM Products AS SubProds
WHERE SubProds.Manufacturer=Prods.Manufacturer)
В данном случае определено два коррелирующих подзапроса. Первый подзапрос определяет спецификацию столбца AvgPrice. Он будет выполняться для каждой строки, извлекаемой из таблицы Products. В подзапрос передается производитель товара и на его основе выбирается средняя цена для товаров именно этого производителя. И так как производитель у товаров может отличаться, то и результат подзапроса в каждом случае также может отличаться.
Второй подзапрос аналогичен, только он используется для фильтрации извлекаемых из таблицы
Products. И также он будет выполняться для каждой строки.
Чтобы избежать двойственности при фильтрации в подзапросе при сравнении производителей
(SubProds.Manufacturer=Prods.Manufacturer) для внешней выборки установлен псевдоним Prods, а для выборки из подзапросов определен псевдоним SubProds.
Следует учитывать, что коррелирующие подзапросы выполняются для каждой отдельной строки выборки, то выполнение таких подзапросов может замедлять выполнение всего запроса в целом.
MS SQL Server и T-SQL | Подзапросы в основных командах
Подзапросы в основных командах SQL
Подзапросы в SELECT
В выражении SELECT мы можем вводить подзапросы четырьмя способами:
1. Использовать в условии в выражении WHERE
2. Использовать в условии в выражении HAVING
3. Использовать в качестве таблицы для выборки в выражении FROM
4. Использовать в качестве спецификации столбца в выражении SELECT
Рассмотрим некоторые из этих случаев. Например, получим все товары, у которых цена выше средней:
SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
Чтобы получить нужные товары, нам вначале надо выполнить подзапрос на получение средней цены товара: SELECT AVG(Price) FROM Products.
Или выберем всех покупателей из таблицы Customers, у которых нет заказов в таблице Orders:
SELECT * FROM CUSTOMERS
WHERE Id NOT IN (SELECT CustomerId FROM Orders)
Хотя в данном случае подзапросы прекрасно справляются со своей задачей, стоит отметить, что это не самый эффективный способ для извлечения данных из других таблиц, так как в рамках TSQL для сведения данных из разных таблиц можно использовать оператор JOIN, который рассматривается в следующей теме.
Получение набора значений
При использовании в операторах сравнения подзапросы должны возвращать одно скалярное значение. Но иногда возникает необходимость получить набор значений. Чтобы при использовании в операторах сравнения подзапрос мог возвращать набор значений, перед ним необходимо использовать один из операторов: ALL, SOMEили ANY.
При использовании ключевого слова ALL условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом. Например, найдем все товары, цена которых меньше чем у любого товара фирмы Apple:
SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer='Apple')
Если бы мы в данном случае опустили бы ключевое слово ALL, то мы бы столкнулись с ошибкой.
MS SQL Server и T-SQL | Подзапросы в основных командах
(SELECT Id FROM Customers WHERE
FirstName='Tom'),
Допустим, если подзапрос возвращает значения vl1, val2 и val3, то условие фильтрации фактически было бы аналогично объединению этих значений через оператор AND:
WHERE Price < val1 AND Price < val2 AND Price < val3
В тоже время подобный запрос гораздо проще переписать другим образом:
SELECT * FROM Products
WHERE Price < (SELECT MIN(Price) FROM Products WHERE Manufacturer='Apple')
При применении ключевых слов ANY и SOME условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом. По действию оба этих оператора аналогичны, поэтому можно применять любое из них. Например, в следующем случае получим товары, которые стоят меньше самого дорого товара компании Apple:
SELECT * FROM Products
WHERE Price < ANY(SELECT Price FROM Products WHERE Manufacturer='Apple')
И также стоит отметить, что данный запрос можно сделать проще, переписав следующим образом:
SELECT * FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products WHERE Manufacturer='Apple')
Подзапрос как спецификация столбца
Результат подзапроса может представлять отдельный столбец в выборке. Например, выберем все заказы и добавим к ним информацию о названии товара:
SELECT *,
(SELECT ProductName FROM Products WHERE Id=Orders.ProductId) AS Product
FROM Orders
Подзапросы в команде INSERT
В команде INSERT подзапросы могут применяться для определения значения, которое вставляется в один из столбцов:
INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
MS SQL Server и T-SQL | Подзапросы в основных командах '2017-07-11',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
)
Подзапросы в команде UPDATE
В команде UPDATE подзапросы могут применяться:
1. В качестве устанавливаемого значения после оператора SET
2. Как часть условия в выражении WHERE
Так, увеличим количество купленных товаров на 2 в тех заказах, где покупатель Тоm:
UPDATE Orders
SET ProductCount = ProductCount + 2
WHERE CustomerId=(SELECT Id FROM Customers WHERE FirstName='Tom')
Или установим для заказа цену товара, полученную в результате подзапроса:
UPDATE Orders
SET Price = (SELECT Price FROM Products WHERE Id=Orders.ProductId) + 2000
WHERE Id=1
Подзапросы в команде DELETE
В команде DELETE подзапросы также применяются как часть условия. Так, удалим все заказы на
Galaxy S8, которые сделал Bob:
DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName='Galaxy S8')
AND CustomerId=(SELECT Id FROM Customers WHERE FirstName='Bob')
MS SQL Server и T-SQL | Оператор EXISTS
Оператор EXISTS
Оператор EXISTS позволяет проверить, возвращает ли подзапрос какое-либо значение. Как правило, этот оператор используется для индикации того, что какая-либо строка удовлетворяет условию. То есть фактически оператор EXISTS не возвращает строки, а лишь указывает, что в базе данных есть как минимум одна строка, которые соответствует данному запросу. Поскольку возвращения набора строк не происходит, то подзапросы с подобным оператором выполняются довольно быстро.
Применение оператора имеет следующий формальный синтаксис:
WHERE [NOT] EXISTS (подзапрос)
Например, найдем всех покупателей из таблицы Customer, которые делали заказы:
SELECT *
FROM Customers
WHERE EXISTS (SELECT * FROM Orders
WHERE Orders.CustomerId = Customers.Id)
Другой пример - найдем все товары из таблицы Products, на которые не было заказов в таблице
Orders:
SELECT *
FROM Products
WHERE NOT EXISTS (SELECT * FROM Orders WHERE Products.Id = Orders.ProductId)
05.04.2018
MS SQL Server и T-SQL | Оператор EXISTS
Стоит отметить, что для получения подобного результата ы могли бы использовать и опеатор IN:
SELECT *
FROM Products
WHERE Id NOT IN (SELECT ProductId FROM Orders)
Но поскольку при применении EXISTS не происходит выборка строк, то его использование более оптимально и эффективно, чем использование оператора IN.
MS SQL Server и T-SQL | Неявное соединение таблиц
Соединение таблиц
Неявное соединение таблиц
Для сведения данных из разных таблиц мы можем использовать стандартную команду SELECT.
Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
USE productsdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(30) NOT NULL
);
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
CreatedAt DATE NOT NULL,
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Здесь таблицы Products и Customers связаны с таблицей Orders связью один ко многим. Таблица
Orders в виде внешних ключей ProductId и CustomerId содержит ссылки на столбцы Id из соответственно таблиц Products и Customers. Также она хранит количество купленного товара
(ProductCount) и и по какой цене он был куплен (Price). И кроме того, таблицы также хранит в виде столбца CreatedAt дату покупки.
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO Products
VALUES ('iPhone 6', 'Apple', 2, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi 5X', 'Xiaomi', 2, 26000),
('OnePlus 5', 'OnePlus', 6, 38000)
MS SQL Server и T-SQL | Неявное соединение таблиц
INSERT INTO Customers VALUES ('Tom'), ('Bob'),('Sam')
INSERT INTO Orders
VALUES
(
(SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-11',
2,
(SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
(SELECT Id FROM Customers WHERE FirstName='Tom'),
'2017-07-13',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
),
(
(SELECT Id FROM Products WHERE ProductName='iPhone 6S'),
(SELECT Id FROM Customers WHERE FirstName='Bob'),
'2017-07-11',
1,
(SELECT Price FROM Products WHERE ProductName='iPhone 6S')
)
Теперь соединим две таблицы Orders и Customers:
SELECT * FROM Orders, Customers
При такой выборке для каждая строка из таблицы Orders будет совмещаться с каждой строкой из таблицы Customers. То есть, получится перекрестное соединение. Например, в Orders три строки, а в Customers то же три строки, значит мы получим 3 * 3 = 9 строк:
MS SQL Server и T-SQL | Неявное соединение таблиц
То есть в данном случае мы получаем прямое (декартово) произведение двух групп. Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из Orders связан с конкретным покупателем из Customers, а не со всеми возможными покупателями.
1   2   3   4   5   6   7   8   9   10

перейти в каталог файлов


связь с админом