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

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


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница6 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле CustomerId из Orders соответствует полю Id из Customers:
SELECT * FROM Orders, Customers
WHERE Orders.CustomerId = Customers.Id
Теперь объединим данные по трем таблицам Orders, Customers и Proucts. То есть получим все заказы и добавим информацию по клиенту и связанному товару:
SELECT Customers.FirstName, Products.ProductName, Orders.CreatedAt
FROM Orders, Customers, Products
WHERE Orders.CustomerId = Customers.Id AND Orders.ProductId=Products.Id
Поскольку надо соединить три таблицы, то применяются как минимум два условия. Ключевой таблицей остается Orders, из которой извлекаются все заказы, а затем к ней подсоединяется данные по клиенту по условию Orders.CustomerId = Customers.Id и данные по товару по условию Orders.ProductId=Products.Id
Поскольку в данном случае названия таблиц сильно увеличивают код, то мы его можем сократить за счет использования псевдонимов таблиц:
SELECT C.FirstName, P.ProductName, O.CreatedAt
FROM Orders AS O, Customers AS C, Products AS P
WHERE O.CustomerId = C.Id AND O.ProductId=P.Id
05.04.2018
MS SQL Server и T-SQL | Неявное соединение таблиц
Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:
SELECT C.FirstName, P.ProductName, O.*
FROM Orders AS O, Customers AS C, Products AS P
WHERE O.CustomerId = C.Id AND O.ProductId=P.Id
MS SQL Server и T-SQL | Inner Join
Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:
INNER JOIN
В прошлой теме было рассмотрено неявное соединение таблиц. Оно производилось на основе простой выборки неявно путем сведения данных. Для явного соединения данных из двух таблиц применяется оператор JOIN. Общий формальный синтаксис применения оператора INNER JOIN:
SELECT столбцы
FROM таблица1
[INNER] JOIN таблица2
ON условие1
[[INNER] JOIN таблица3
ON условие2]
После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку.
Перед JOIN может использоваться необязательное ключевое слово INNER. Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.
Возьмем таблицы с данными из прошлой темы:
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
);
MS SQL Server и T-SQL | Inner Join
SELECT Orders.CreatedAt, Orders.ProductCount, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например,
"Orders.ProductCount".
Также используя псевдонимы, мы можем сократить код:
SELECT O.CreatedAt, O.ProductCount, P.ProductName
FROM Orders AS O
JOIN Products AS P
ON P.Id = O.ProductId
Подобным образом мы можем присоединять и другие таблицы. Например, добавим к заказу информацию о покупателе из таблицы Customers:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId
Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:
MS SQL Server и T-SQL | Inner Join
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId
WHERE Products.Price < 45000
ORDER BY Customers.FirstName
Условия после ключевого слова ON могут быть более сложными по составу:
SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId AND Products.Manufacturer='Apple'
JOIN Customers ON Customers.Id=Orders.CustomerId
ORDER BY Customers.FirstName
В данном случае выбираем все заказы на товары, производителем которых является Apple.
При использовании оператора JOIN следует учитывать, что процесс соединения таблиц может быть ресурсоемким, поэтому следует соединять только те таблицы, данные из которых действительно необходимы. Чем больше таблиц соединяется, тем больше снижается производительность.
MS SQL Server и T-SQL | Outer Join
OUTER JOIN
В предыдущей теме было рассмотрено внутреннее соединение таблиц. Но MS SQL Server также поддерживает внешнее соединение или outer join. В отличие от inner join внешнее соединение возвращает все строки одной или двух таблиц, которые участвуют в соединении.
Outer Join имеет следующий формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]...
Перед оператором JOIN указывается одно из ключевых слов LEFT,RIGHT или FULL, которые определяют тип соединения:
LEFT: выборка будет содержать все строки из первой или левой таблицы
RIGHT: выборка будет содержать все строки из второй или правой таблицы
FULL: выборка будет содержать все строки из обоих таблиц
Также перед оператором JOIN может указываться ключевое слово OUTER, но его применение необязательно. Далее после JOIN указывается присоединяемая таблица, а затем идет условие соединения.
Например, соединим таблицы Orders и Customers:
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id
Таблица Orders является первой или левой таблицей, а таблица Customers - правой таблицей.
Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из Orders, а затем к ним по условию Orders.CustomerId = Customers.Idбудут добавляться связанные строки из Customers.
MS SQL Server и T-SQL | Outer Join
По вышеприведенному результату может показаться, что левостороннее соединение аналогично
INNER Join, но это не так. Inner Join объединяет строки из дух таблиц при соответствии условию.
Если одна из таблиц содержит строки, которые не соответствуют этому условию, то данные строки не включаются в выходную выборку. Left Join выбирает все строки первой таблицы и затем присоединяет к ним строки правой таблицы. К примеру, возьмем таблицу Customers и добавим к покупателям информацию об их заказах:
-- INNER JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id
--LEFT JOIN
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
MS SQL Server и T-SQL | Outer Join
Изменим в примере выше тип соединения на правостороннее:
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders RIGHT JOIN Customers
ON Orders.CustomerId = Customers.Id
Теперь будут выбираться все строки из Customers, а к ним уже будет присоединяться связанные по условию строки из таблицы Orders:
Поскольку один из покупателей из таблицы Customers не имеет связанных заказов из Orders, то соответствующие столбцы, которые берутся из Orders, будут иметь значение NULL.
Используем левостороннее соединение для добавления к заказам информации о пользователях и товарах:
MS SQL Server и T-SQL | Outer Join
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id
И также можно применять более комплексные условия с фильтрацией и сортировкой. Например,
выберем все заказы с информацией о клиентах и товарах по тем товарам, у которых цена меньше
45000, и отсортируем по дате заказа:
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
LEFT JOIN Products ON Orders.ProductId = Products.Id
WHERE Products.Price < 45000
ORDER BY Orders.CreatedAt
Или выберем всех пользователей из Customers, у которых нет заказов в таблице Orders:
MS SQL Server и T-SQL | Outer Join
SELECT FirstName FROM Customers
LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS NULL
Также можно комбинировать Inner Join и Outer Join:
SELECT Customers.FirstName, Orders.CreatedAt,
Products.ProductName, Products.Manufacturer
FROM Orders
JOIN Products ON Orders.ProductId = Products.Id AND Products.Price < 45000
LEFT JOIN Customers ON Orders.CustomerId = Customers.Id
ORDER BY Orders.CreatedAt
Вначале по условию к таблице Orders через Inner Join присоединяется связанная информация из
Products, затем через Outer Join добавляется информация из таблицы Customers.
Cross Join
Cross Join или перекрестное соединение создает набор строк, где каждая строка из одной таблицы соединяется с каждой строкой из второй таблицы. Например, соединим таблицу заказов Orders и таблицу покупателей Customers:
SELECT * FROM Orders CROSS JOIN Customers
Если в таблице Orders 3 строки, а в таблице Customers то же три строки, то в результате перекрестного соединения создается 3 * 3 = 9 строк вне зависимости, связаны ли данные строки или нет.
При неявном перекрестном соединении можно опустить оператор CROSS JOIN и просто перечислить все получаемые таблицы:
SELECT * FROM Orders, Customers
MS SQL Server и T-SQL | Группировка в соединениях
Группировка в соединениях
В выражениях INNER/OUTER JOIN также можно использовать группировку. Например, выведем для каждого пользователя количество заказов, которые он сделал:
SELECT FirstName, COUNT(Orders.Id)
FROM Customers JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;
Критерием группировки выступают Id и имя покупателя. Выражение SELECT выбирает имя покупателя и количество заказов, используя столбец Id из таблицы Orders.
Так как это INNER JOIN, то в группах будут только те покупатели, у которых есть заказы.
Если необходимо вывести даже тех покупателей, у которых нет заказов, то применяется OUTER
JOIN:
SELECT FirstName, COUNT(Orders.Id)
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
GROUP BY Customers.Id, Customers.FirstName;
MS SQL Server и T-SQL | Группировка в соединениях
Или выведем товары с общей суммой сделанных заказов:
SELECT Products.ProductName, Products.Manufacturer,
SUM(Orders.ProductCount * Orders.Price) AS Units
FROM Products LEFT JOIN Orders
ON Orders.ProductId = Products.Id
GROUP BY Products.Id, Products.ProductName, Products.Manufacturer
MS SQL Server и T-SQL | UNION
UNION
Оператор UNION подобно inner join или outer join позволяет соединить две таблицы. Но в отличие от inner/outer join объединения соединяют не столбцы разных таблиц, а два однотипных набора в один.
Формальный синтаксис объединения:
SELECT_выражение1
UNION [ALL] SELECT_выражение2
[UNION [ALL] SELECT_выражениеN]
Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица
Customers) и для сотрудников банка (таблица Employees):
USE usersdb;
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
AccountSum MONEY
);
CREATE TABLE Employees
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
);
INSERT INTO Customers VALUES
('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)
INSERT INTO Employees VALUES
('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')
Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами - именем (FirstName) и фамилией (LastName).
Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:
MS SQL Server и T-SQL | UNION
SELECT FirstName, LastName
FROM Customers
UNION SELECT FirstName, LastName FROM Employees
В данном случае из первой таблицы выбираются два значения - имя и фамилия клиента. Из второй таблицы Employees также выбираются два значения - имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип совпадают для обеих выборок.
При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку, то в выражениях ORDER
BY необходимо ориентироваться именно на названия столбцов первой выборки:
SELECT FirstName + ' ' +LastName AS FullName
FROM Customers
UNION SELECT FirstName + ' ' + LastName AS EmployeeName
FROM Employees
ORDER BY FullName DESC
В данном случае каждая выборка имеет по одному столбцу, который представляет объединение имени и фамилии клиента или сотрудника. Но в случае с клиентами столбец будет называться
FullName, а в случае с сотрудниками - EmployeeName. Тем не менее для сортировки применяется название столбца из первой выборки и он же будет в результирующей выборке:
MS SQL Server и T-SQL | UNION
Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены.
Например, в следующем случае объединение завершится с ошибкой:
SELECT FirstName, LastName, AccountSum
FROM Customers
UNION SELECT FirstName, LastName
FROM Employees
Также соответствующие столбцы должны соответствовать по типу. Так, следующий пример завершится с ошибкой из-за не соответствия по типу данных:
SELECT FirstName, LastName
FROM Customers
UNION SELECT Id, LastName
FROM Employees
В данном случае первый столбец первой выборки имеет тип NVARCHAR, то есть хранит строку.
Первый столбец второй выборки - Id имеет тип INT, то есть хранит число.
Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются. Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах. При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL:
SELECT FirstName, LastName
FROM Customers
UNION ALL SELECT FirstName, LastName
FROM Employees
MS SQL Server и T-SQL | UNION
Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:
SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum
FROM Customers WHERE AccountSum < 3000
UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum
FROM Customers WHERE AccountSum >= 3000
В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.
MS SQL Server и T-SQL | EXCEPT
EXCEPT
Оператор EXCEPT позволяет найти разность двух выборок, то есть те строки которые есть в первой выборке, но которых нет во второй. Для его использования применяется следующий формальный синтаксис:
SELECT_выражение1
EXCEPT SELECT_выражение2
Для примера возьмем таблицы из прошлой темы:
USE usersdb;
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
AccountSum MONEY
);
CREATE TABLE Employees
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
);
INSERT INTO Customers VALUES
('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800)
INSERT INTO Employees VALUES
('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson')
Таблица Employees содержит данные обо всех сотрудниках банка, а таблица Customers - обо всех клиентах. Но сотрудники банка могут также быть его клиентами. И допустим, нам надо найти всех клиентов банка, которые не являются его сотрудниками:
SELECT FirstName, LastName
FROM Customers
MS SQL Server и T-SQL | EXCEPT
EXCEPT SELECT FirstName, LastName
FROM Employees
Подобным образом можно получить всех сотрудников банка, которые не являются его клиентами:
SELECT FirstName, LastName
FROM Employees
EXCEPT SELECT FirstName, LastName
FROM Customers
MS SQL Server и T-SQL | INTERSECT
INTERSECT
Оператор INTERSECT позволяет найти общие строки для двух выборок, то есть данный оператор выполняет операцию пересечения множеств. Для его использования применяется следующий формальный синтаксис:
SELECT_выражение1
INTERSECT SELECT_выражение2
Для примера возьмем таблицы из прошлой темы:
USE usersdb;
CREATE TABLE Customers
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
1   2   3   4   5   6   7   8   9   10

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


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