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

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


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница4 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
Стоит отметить, что в данном случае регистр не имеет значение, и мы могли бы использовать для поиска и строку "Samsung", и "SAMSUNG", и "samsung". Все эти варианты давали бы эквивалентный результат выборки.
Другой пример - найдем все товары, у которых цена больше 45000:
MS SQL Server и T-SQL | Фильтрация. WHERE
SELECT * FROM Products
WHERE Price > 45000
В качестве условия могут использоваться и более сложные выражения. Например, найдем все товары, у которых совокупная стоимость больше 200 000:
SELECT * FROM Products
WHERE Price * ProductCount > 200000
Логические операторы
Для объединения нескольких условий в одно могут использоваться логические операторы. В T-SQL
имеются следующие логические операторы:
AND: операция логического И. Она объединяет два выражения:
выражение1 AND выражение2
Только если оба этих выражения одновременно истинны, то и общее условие оператора AND
также будет истинно. То есть если и первое условие истинно, и второе.
OR: операция логического ИЛИ. Она также объединяет два выражения:
выражение1 OR выражение2
Если хотя бы одно из этих выражений истинно, то общее условие оператора OR также будет истинно. То есть если или первое условие истинно, или второе.
NOT: операция логического отрицания. Если выражение в этой операции ложно, то общее условие истинно.
NOT выражение
Если эти операторы встречаются в одном выражении, то сначала выполняется NOT, потом AND и в конце OR.
Например, выберем все товары, у которых производитель Samsung и одновременно цена больше
50000:
SELECT * FROM Products
WHERE Manufacturer = 'Samsung' AND Price > 50000
MS SQL Server и T-SQL | Фильтрация. WHERE
Теперь изменим оператор на OR. То есть выберем все товары, у которых либо производитель
Samsung, либо цена больше 50000:
SELECT * FROM Products
WHERE Manufacturer = 'Samsung' OR Price > 50000
Применение оператора NOT - выберем все товары, у которых производитель не Samsung:
SELECT * FROM Products
WHERE NOT Manufacturer = 'Samsung'
MS SQL Server и T-SQL | Фильтрация. WHERE
Но в большинстве случае вполне можно обойтись без оператора NOT. Так, в предыдущий пример мы можем переписать следующим образом:
SELECT * FROM Products
WHERE Manufacturer <> 'Samsung'
Также в одной команде SELECT можно использовать сразу несколько операторов:
SELECT * FROM Products
WHERE Manufacturer = 'Samsung' OR Price > 30000 AND ProductCount > 2
Так как оператор AND имеет более высокий приоритет, то сначала будет выполняться подвыражение Price > 30000 AND ProductCount > 2, и только потом оператор OR. То есть здесь выбираются товары, которыех на складе больше 2 и у которых одновременно цена больше 30000,
либо те товары, производителем которых является Samsung.
С помощью скобок мы также можем переопределить порядок операций:
SELECT * FROM Products
WHERE (Manufacturer = 'Samsung' OR Price > 30000) AND ProductCount > 2
MS SQL Server и T-SQL | Фильтрация. WHERE
IS NULL
Ряд столбцов может допускать значение NULL. Это значение не эквивалентно пустой строке ''. NULL
представляет полное отсутствие какого-либо значения. И для проверки на наличие подобного значения применяется оператор IS NULL.
Например, выберем все товары, у которых не установлено поле ProductCount:
SELECT * FROM Products
WHERE ProductCount IS NULL
Если, наоборот, необходимо получить строки, у которых поле ProductCount не равно NULL, то можно использовать оператор NOT:
SELECT * FROM Products
WHERE ProductCount IS NOT NULL
MS SQL Server и T-SQL | Операторы фильтрации
Операторы фильтрации
Оператор IN
Оператор IN позволяет определить набор значений, которые должны иметь столбцы:
WHERE выражение [NOT] IN (выражение)
Выражение в скобках после IN определяет набор значений. Этот набор может вычисляться динамически на основании, например, еще одного запроса, либо это могут быть константные значения.
Например, выберем товары, у которых производитель либо Samsung, либо Xiaomi, либо Huawei:
SELECT * FROM Products
WHERE Manufacturer IN ('Samsung', 'Xiaomi', 'Huawei')
Мы могли бы все эти значения проверить и через оператор OR:
SELECT * FROM Products
WHERE Manufacturer = 'Samsung' OR Manufacturer = 'Xiaomi' OR Manufacturer = 'Huawei'
Но использование оператора IN гораздо удобнее, особенно если подобных значений очень много.
С помощью оператора NOT можно найти все строки, которые, наоборот, не соответствуют набору значений:
SELECT * FROM Products
WHERE Manufacturer NOT IN ('Samsung', 'Xiaomi', 'Huawei')
Оператор BETWEEN
Оператор BETWEEN определяет диапазон значений с помощью начального и конечного значения,
которому должно соответствовать выражение:
MS SQL Server и T-SQL | Операторы фильтрации
WHERE ProductName LIKE 'Galaxy%'
WHERE выражение [NOT] BETWEEN начальное_значение AND конечное_значение
Например, получим все товары, у которых цена от 20 000 до 40 000 (начальное и конечное значения также включаются в диапазон):
SELECT * FROM Products
WHERE Price BETWEEN 20000 AND 40000
Если надо, наоборот, выбрать те строки, которые не попадают в данный диапазон, то применяется оператор NOT:
SELECT * FROM Products
WHERE Price NOT BETWEEN 20000 AND 40000
Также можно использовать более сложные выражения. Например, получим товары, запасы которых на определенную сумму (цена * количество):
SELECT * FROM Products
WHERE Price * ProductCount BETWEEN 100000 AND 200000
Оператор LIKE
Оператор LIKE принимает шаблон строки, которому должно соответствовать выражение.
WHERE выражение [NOT] LIKE шаблон_строки
Для определения шаблона могут применяться ряд специальных символов подстановки:
%: соответствует любой подстроке, которая может иметь любое количество символов, при этом подстрока может и не содержать ни одного символа
_: соответствует любому одиночному символу
[ ]: соответствует одному символу, который указан в квадратных скобках
[ - ]: соответствует одному символу из определенного диапазона
[ ^ ]: соответствует одному символу, который не указан после символа ^
Некоторые примеры использования подстановок:
MS SQL Server и T-SQL | Операторы фильтрации
Соответствует таким значениям как "Galaxy Ace 2" или "Galaxy S7"
WHERE ProductName LIKE 'Galaxy S_'
Соответствует таким значениям как "Galaxy S7" или "Galaxy S8"
WHERE ProductName LIKE 'iPhone [78]'
Соответствует таким значениям как "iPhone 7" или "iPhone8"
WHERE ProductName LIKE 'iPhone [6-8]'
Соответствует таким значениям как "iPhone 6", "iPhone 7" или "iPhone8"
WHERE ProductName LIKE 'iPhone [^7]%'
Соответствует таким значениям как "iPhone 6", "iPhone 6S" или "iPhone8". Но не соответствует значениям "iPhone 7" и "iPhone 7S"
WHERE ProductName LIKE 'iPhone [^1-6]%'
Соответствует таким значениям как "iPhone 7", "iPhone 7S" и "iPhone 8". Но не соответствует значениям "iPhone 5", "iPhone 6" и "iPhone 6S"
Применим оператор LIKE:
SELECT * FROM Products
WHERE ProductName LIKE 'iPhone [6-8]%'
MS SQL Server и T-SQL | Обновление данных. Команда UPDATE
Обновление данных. Команда UPDATE
Для изменения уже имеющихся строк в таблице применяется команда UPDATE. Она имеет следующий формальный синтаксис:
UPDATE имя_таблицы
SET столбец1 = значение1, столбец2 = значение2, ... столбецN = значениеN
[FROM выборка AS псевдоним_выборки]
[WHERE условие_обновления]
Например, увеличим у всех товаров цену на 5000:
UPDATE Products
SET Price = Price + 5000
Используем критерий, и изменим название производителя с "Samsung" на "Samsung Inc.":
UPDATE Products
SET Manufacturer = 'Samsung Inc.'
WHERE Manufacturer = 'Samsung'
MS SQL Server и T-SQL | Обновление данных. Команда UPDATE
Более сложный запрос - заменим у поля Manufacturer значение "Apple" на "Apple Inc." в первых 2
строках:
UPDATE Products
SET Manufacturer = 'Apple Inc.'
FROM
(SELECT TOP 2 * FROM Products WHERE Manufacturer='Apple') AS Selected
WHERE Products.Id = Selected.Id
С помощью подзапроса после ключевого слова FROM производится выборка первых двух строк, в которых Manufacturer='Apple'. Для этой выборки будет определен псевдоним Selected. Псевдоним указывается после оператора AS.
Далее идет условие обновления Products.Id = Selected.Id. То есть фактически мы имеем дело с двумя таблицами - Products и Selected (которая является производной от Products). В Selected находится две первых строки, в которых Manufacturer='Apple'. В Products - вообще все строки. И
обновление производится только для тех строк, которые есть в выборке Selected. То есть если в таблице Products десятки товаров с производителем Apple, то обновление коснется только двух первых из них.
MS SQL Server и T-SQL | Удаление данных. Команда DELETE
Удаление данных. Команда DELETE
Для удаления применяется команда DELETE:
DELETE [FROM] имя_таблицы
WHERE условие_удаления
Например, удалим строки, у которых id равен 9:
DELETE Products
WHERE Id=9
Или удалим все товары, производителем которых является Xiaomi и которые имеют цену меньше
15000:
DELETE Products
WHERE Manufacturer='Xiaomi' AND Price < 15000
Более сложный пример - удалим первые два товара, у которых производитель - Apple:
DELETE Products FROM
(SELECT TOP 2 * FROM Products
WHERE Manufacturer='Apple]') AS Selected
WHERE Products.Id = Selected.Id
После первого оператора FROM идет выборка двух строк из таблицы Products. Этой выборке назначается псевдоним Selected с помощью оператора AS. Далее устанавливаем условие, что если
Id в таблице Products имеет то же значение, что и Id в выборке Selected, то строка удаляется.
MS SQL Server и T-SQL | Удаление данных. Команда DELETE
Если необходимо вовсе удалить все строки вне зависимости от условия, то условие можно не указывать:
DELETE Products
MS SQL Server и T-SQL | Агрегатные функции
Группировка
Агрегатные функции
Агрегатные функции выполняют вычисления над значениями в наборе строк. В T-SQL имеются следующие агрегатные функции:
AVG: находит среднее значение
SUM: находит сумму значений
MIN: находит наименьшее значение
MAX: находит наибольшее значение
COUNT: находит количество строк в запросе
В качестве аргумента все агрегатные функции принимают выражение, которое представляет критерий дя определения значений. Зачастую, в качестве выражения выступает название столбца,
над значениями которого надо проводить вычисления.
Выражения в функциях AVG и SUM должно представлять числовое значение. Выражение в функциях MIN, MAX и COUNT может представлять числовое или строковое значение или дату.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Avg
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:
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
);
INSERT INTO Products
VALUES
('iPhone 6', 'Apple', 3, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
MS SQL Server и T-SQL | Агрегатные функции
('Mi6', 'Xiaomi', 5, 28000),
('OnePlus 5', 'OnePlus', 6, 38000)
Найдем среднюю цену товаров из базы данных:
SELECT AVG(Price) AS Average_Price FROM Products
Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя можно его и не устанавливать.
Также мы можем применить фильтрацию. Например, найти среднюю цену для товаров какого-то определенного производителя:
SELECT AVG(Price) FROM Products
WHERE Manufacturer='Apple'
И, кроме того, мы можем находить среднее значение для более сложных выражений. Например,
найдем среднюю сумму всех товаров, учитывая их количество:
SELECT AVG(Price * ProductCount) FROM Products
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
SELECT COUNT(*) FROM Products
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
SELECT COUNT(Manufacturer) FROM Products
MS SQL Server и T-SQL | Агрегатные функции
Min и Max
Функции Min и Max возвращают соответственно минимальное и максимальное значение по столбцу. Например, найдем минимальную цену среди товаров:
SELECT MIN(Price) FROM Products
Поиск максимальной цены:
SELECT MAX(Price) FROM Products
Данные функции также игнорируют значения NULL и не учитывают их при подсчете.
Sum
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
SELECT SUM(ProductCount) FROM Products
Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
SELECT SUM(ProductCount * Price) FROM Products
All и Distinct
По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT.
SELECT AVG(DISTINCT ProductCount) AS Average_Price FROM Products
По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки:
SELECT AVG(ALL ProductCount) AS Average_Price FROM Products
Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
Комбинирование функций
Объединим применение нескольких функций:
MS SQL Server и T-SQL | Агрегатные функции
SELECT COUNT(*) AS ProdCount,
SUM(ProductCount) AS TotalCount,
MIN(Price) AS MinPrice,
MAX(Price) AS MaxPrice,
AVG(Price) AS AvgPrice
FROM Products
MS SQL Server и T-SQL | Операторы GROUP BY и HAVING
Операторы GROUP BY и HAVING
Для группировки данных в T-SQL применяются операторы GROUP BY и HAVING, для использования которых применяется следующий формальный синтаксис:
SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]
GROUP BY
Оператор GROUP BY определяет, как строки будут группироваться.
Например, сгруппируем товары по производителю
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer
Первый столбец в выражении SELECT - Manufacturer представляет название группы, а второй столбец - ModelsCount представляет результат функции Count, которая вычисляет количество строк в группе.
Стоит учитывать, что любой столбец, который используется в выражении SELECT (не считая столбцов, которые хранят результат агрегатных функций), должны быть указаны после оператора
GROUP BY. Так, например, в случае выше столбец Manufacturer указан и в выражении SELECT, и в выражении GROUP BY.
И если в выражении SELECT производится выборка по одному или нескольким столбцам и также используются агрегатные функции, то необходимо использовать выражение GROUP BY. Так,
следующий пример работать не будет, так как он не содержит выражение группировки:
MS SQL Server и T-SQL | Операторы GROUP BY и HAVING
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
Другой пример, добавим группировку по количеству товаров:
SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer, ProductCount
Оператор GROUP BY может выполнять группировку по множеству столбцов.
Если столбец, по которому производится группировка, содержит значение NULL, то строки со значением NULL составят отдельную группу.
Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но до выражения ORDER BY:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price > 30000
GROUP BY Manufacturer
ORDER BY ModelsCount DESC
Фильтрация групп. HAVING
Оператор HAVING определяет, какие группы будут включены в выходной результат, то есть выполняет фильтрацию групп.
Применение HAVING во многом аналогично применению WHERE. Только есть WHERE применяется к фильтрации строк, то HAVING используется для фильтрации групп.
Например, найдем все группы товаров по производителям, для которых определено более 1 модели:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
GROUP BY Manufacturer
HAVING COUNT(*) > 1
MS SQL Server и T-SQL | Операторы GROUP BY и HAVING
При этом в одной команде мы можем использовать выражения WHERE и HAVING:
SELECT Manufacturer, COUNT(*) AS ModelsCount
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING COUNT(*) > 1
То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.
Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения
HAVING:
SELECT Manufacturer, COUNT(*) AS Models, SUM(ProductCount) AS Units
FROM Products
WHERE Price * ProductCount > 80000
GROUP BY Manufacturer
HAVING SUM(ProductCount) > 2
ORDER BY Units DESC
В данном случае группировка идет по производителям, и также выбирается количество моделей для каждого производителя (Models) и общее количество всех товаров по всем этим моделям
(Units). В конце группы сортируются по количеству товаров по убыванию.
MS SQL Server и T-SQL | Расширения для группировки
Расширения SQL Server для группировки
Дополнительно к стандартным операторам GROUP BY и HAVING SQL Server поддерживает еще четыре специальных расширения для группировки данных: ROLLUP, CUBE, GROUPING
1   2   3   4   5   6   7   8   9   10

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


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