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

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


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница7 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
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')
В таблице Customers хранятся все клиенты банка, а в таблице Employees - все его сотрудники. Но сотрудники могут быть одновременно и клиентами банка, поэтому их данные могут храниться сразу в двух таблицах. Найдем всех сотрудников банка, которые одновременно являются его клиентами.
То есть нам надо найти общие элементы двух выборок:
SELECT FirstName, LastName
FROM Employees
MS SQL Server и T-SQL | INTERSECT
INTERSECT SELECT FirstName, LastName
FROM Customers
MS SQL Server и T-SQL | Функции для работы со строками
Встроенные функции
Функции для работы со строками
Для работы со строками в T-SQL можно применять следующие функции:
LEN: возвращает количество символов в строке. В качестве параметра в функцию передается строка, для которой надо найти длину:
SELECT LEN('Apple') -- 5
LTRIM: удаляет начальные пробелы из строки. В качестве параметра принимает строку:
SELECT LTRIM(' Apple')
RTRIM: удаляет конечные пробелы из строки. В качестве параметра принимает строку:
SELECT RTRIM(' Apple ')
CHARINDEX: возвращает индекс, по которому находится первое вхождение подстроки в строке. В качестве первого параметра передается подстрока, а в качестве второго - строка,
в которой надо вести поиск:
SELECT CHARINDEX('pl', 'Apple') -- 3
PATINDEX: возвращает индекс, по которому находится первое вхождение определенного шаблона в строке:
SELECT PATINDEX('%p_e%', 'Apple')
-- 3
LEFT: вырезает с начала строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:
SELECT LEFT('Apple', 3) -- App
RIGHT: вырезает с конца строки определенное количество символов. Первый параметр функции - строка, а второй - количество символов, которые надо вырезать сначала строки:
SELECT RIGHT('Apple', 3)
-- ple
SUBSTRING: вырезает из строки подстроку определенной длиной, начиная с определенного индекса. Певый параметр функции - строка, второй - начальный индекс для вырезки, и третий параметр - количество вырезаемых символов:
SELECT SUBSTRING('Galaxy S8 Plus', 8, 2)
-- S8
REPLACE: заменяет одну подстроку другой в рамках строки. Первый параметр функции строка, второй - подстрока, которую надо заменить, а третий - подстрока, на которую надо заменить:
SELECT REPLACE('Galaxy S8 Plus', 'S8 Plus', 'Note 8') -- Galaxy Note 8
MS SQL Server и T-SQL | Функции для работы со строками
REVERSE: переворачивает строку наоборот:
SELECT REVERSE('123456789')
-- 987654321
CONCAT: объединяет две строки в одну. В качестве параметра принимает от 2-х и более строк, которые надо соединить:
SELECT CONCAT('Tom', ' ', 'Smith')
-- Tom Smith
LOWER: переводит строку в нижний регистр:
SELECT LOWER('Apple') -- apple
UPPER: переводит строку в верхний регистр
SELECT UPPER('Apple') -- APPLE
SPACE: возвращает строку, которая содержит определенное количество пробелов
Например, возьмем таблицу:
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
);
И при извлечении данных применим строковые функции:
SELECT UPPER(LEFT(Manufacturer,2)) AS Abbreviation,
CONCAT(ProductName, ' - ', Manufacturer) AS FullProdName
FROM Products
ORDER BY Abbreviation
MS SQL Server и T-SQL | Функции для работы с числами
Функции для работы с числами
Для работы с числовыми данными T-SQL предоставляет ряд функций:
ROUND: округляет число. В качестве первого параметра передается число. Второй параметр указывает на длину. Если длина представляет положительное число, то оно указывает, до какой цифры после запятой идет округление. Если длина представляет отрицательное число,
то оно указывает, до какой цифры с конца числа до запятой идет округление
SELECT ROUND(1342.345, 2)
-- 1342.350
SELECT ROUND(1342.345, -2)
-- 1300.000
ISNUMERIC: определяет, является ли значение числом. В качестве параметра функция принимает выражение. Если выражение является числом, то функция возвращает 1. Если не является, то возвращается 0.
SELECT ISNUMERIC(1342.345)
-- 1
SELECT ISNUMERIC('1342.345')
-- 1
SELECT ISNUMERIC('SQL')
-- 0
SELECT ISNUMERIC('13-04-2017') -- 0
ABS: возвращает абсолютное значение числа.
SELECT ABS(-123)
-- 123
CEILING: возвращает наименьшее целое число, которое больше или равно текущему значению.
SELECT CEILING(-123.45)
-- -123
SELECT CEILING(123.45)
-- 124
FLOOR: возвращает наибольшее целое число, которое меньше или равно текущему значению.
SELECT FLOOR(-123.45)
-- -124
SELECT FLOOR(123.45)
-- 123
SQUARE: возводит число в квадрат.
SELECT SQUARE(5)
-- 25
SQRT: получает квадратный корень числа.
SELECT SQRT(225)
-- 15
RAND: генерирует случайное число с плавающей точкой в диапазоне от 0 до 1.
SELECT RAND()
-- 0.707365088352935
SELECT RAND()
-- 0.173808327956812
COS: возвращает косинус угла, выраженного в радианах
MS SQL Server и T-SQL | Функции для работы с числами
SELECT COS(1.0472)
-- 0.5 - 60 градусов
SIN: возвращает синус угла, выраженного в радианах
SELECT SIN(1.5708)
-- 1 - 90 градусов
TAN: возвращает тангенс угла, выраженного в радианах
SELECT TAN(0.7854)
-- 1 - 45 градусов
Например, возьмем таблицу:
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
);
Округлим произведение цены товара на количество этого товара:
SELECT ProductName, ROUND(Price * ProductCount, 2)
FROM Products
MS SQL Server и T-SQL | Функции по работе с датами и временем
Функции по работе с датами и временем
T-SQL предоставляет ряд функций для работы с датами и временем:
GETDATE: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime
SELECT GETDATE()
-- 2017-07-28 21:34:55.830
GETUTCDATE: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime
SELECT GETUTCDATE()
-- 2017-07-28 18:34:55.830
SYSDATETIME: возвращает текущую локальную дату и время на основе системных часов, но отличие от GETDATE состоит в том, что дата и время возвращаются в виде объекта datetime2
SELECT SYSDATETIME()
-- 2017-07-28 21:02:22.7446744
SYSUTCDATETIME: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime2
SELECT SYSUTCDATETIME()
-- 2017-07-28 18:20:27.5202777
SYSDATETIMEOFFSET: возвращает объект datetimeoffset(7), который содержит дату и время относительно GMT
SELECT SYSDATETIMEOFFSET()
-- 2017-07-28 21:02:22.7446744 +03:00
DAY: возвращает день даты, который передается в качестве параметра
SELECT DAY(GETDATE())
-- 28
MONTH: возвращает месяц даты
SELECT MONTH(GETDATE())
-- 7
YEAR: возвращает год из даты
SELECT YEAR(GETDATE())
-- 2017
DATENAME: возвращает часть даты в виде строки. Параметр выбора части даты передается в качестве первого параметра, а сама дата передается в качестве второго параметра:
SELECT DATENAME(month, GETDATE())
-- July
Для определения части даты можно использовать следующие параметры (в скобках указаны их сокращенные версии):
year (yy, yyyy): год quarter (qq, q): квартал
MS SQL Server и T-SQL | Функции по работе с датами и временем month (mm, m): месяц dayofyear (dy, y): день года day (dd, d): день месяца week (wk, ww): неделя weekday (dw): день недели hour (hh): час minute (mi, n): минута second (ss, s): секунда millisecond (ms): миллисекунда microsecond (mcs): микросекунда nanosecond (ns): наносекунда tzoffset (tz): смешение в минутах относительно гринвича (для объекта datetimeoffset)
DATEPART: возвращает часть даты в виде числа. Параметр выбора части даты передается в качестве первого параметра (используются те же параметры, что и для DATENAME), а сама дата передается в качестве второго параметра:
SELECT DATENAME(month, GETDATE())
-- 7
DATEADD: возвращает дату, которая является результатом сложения числа к определенному компоненту даты. Первый параметр представляет компонент даты, описанный выше для функции DATENAME. Второй параметр - добавляемое количество. Третий параметр - сама дата, к которой надо сделать прибавление:
SELECT DATEADD(month, 2, '2017-7-28')
-- 2017-09-28 00:00:00.000
SELECT DATEADD(day, 5, '2017-7-28')
-- 2017-08-02 00:00:00.000
SELECT DATEADD(day, -5, '2017-7-28')
-- 2017-07-23 00:00:00.000
Если добавляемое количество представляет отрицательное число, то фактически происходит уменьшение даты.
DATEDIFF: возвращает разницу между двумя датами. Первый параметр - компонент даты,
который указывает, в каких единицах стоит измерять разницу. Второй и третий параметры сравниваемые даты:
SELECT DATEDIFF(year, '2017-7-28', '2018-9-28')
-- разница 1 год
SELECT DATEDIFF(month, '2017-7-28', '2018-9-28')
-- разница 14 месяцев
SELECT DATEDIFF(day, '2017-7-28', '2018-9-28')
-- разница 427 дней
TODATETIMEOFFSET: возвращает значение datetimeoffset, которое является результатом сложения временного смещения с другим объектом datetimeoffset
SELECT TODATETIMEOFFSET('2017-7-28 01:10:22', '+03:00')
SWITCHOFFSET: возвращает значение datetimeoffset, которое является результатом сложения временного смещения с объектом datetime2
MS SQL Server и T-SQL | Функции по работе с датами и временем
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:30')
EOMONTH: возвращает дату последнего дня для месяца, который используется в переданной в качестве параметра дате.
SELECT EOMONTH('2017-02-05')
-- 2017-02-28
SELECT EOMONTH('2017-02-05', 3) -- 2017-05-31
В качестве необязательного второго параметра можно передавать количество месяцев,
которые необходимо прибавить к дате. Тогда последний день месяца будет вычисляться для новой даты.
DATEFROMPARTS: по году, месяцу и дню создает дату
SELECT DATEFROMPARTS(2017, 7, 28)
-- 2017-07-28
ISDATE: проверяет, является ли выражение датой. Если является, то возвращает 1, иначе возвращает 0.
SELECT ISDATE('2017-07-28')
-- 1
SELECT ISDATE('2017-28-07')
-- 0
SELECT ISDATE('28-07-2017')
-- 0
SELECT ISDATE('SQL')
-- 0
В качестве примера использования функций можно привести создание таблицы заказов, которая содержит дату заказа:
CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL DEFAULT GETDATE(),
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);
Выражение DEFAULT GETDATE() указывает, что если при добавлении данных не передается дата, то она автоматически вычисляется с помощью функции GETDATE().
Другой пример - найдем заказы, которые были сделаны 16 дней назад:
SELECT * FROM Orders
WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16
MS SQL Server и T-SQL | Преобразование данных
Id INT IDENTITY PRIMARY
KEY,
Преобразование данных
Когда мы присваиваем значение одного одного типа столбцу, который хранит данные другого типа,
либо выполняем операции, которые вовлекают данные разных типов, SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически. SQL Server может выполнять неявные преобразования от типа с меньшим приоритетом к типу с большим приоритетом. Таблица приоритетов (чем выше, тем больший приоритет):
datetime smalldatetime float real decimal money smallmoney int smallint tinyint bit nvarchar nchar varchar char
То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и время
(datetime).
В тех случаях, когда необходимо выполнить преобразования от типов с высшим приоритетом к типам с низшим приоритетом, то надо выполнять явное приведение типов. Для этого в T-SQL
определены две функции: CONVERT и CAST.
Функция CAST преобразует выражение одного типа к другому. Она имеет следующую форму:
CAST(выражение AS тип_данных)
Для примера возьмем следующие таблицы:
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
(
MS SQL Server и T-SQL | Преобразование данных
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
);
Например, при выводе информации о заказах преобразует числовое значение и дату в строку:
SELECT Id, CAST(CreatedAt AS nvarchar) + '; total: ' + CAST(Price * ProductCount AS nvarchar)
FROM Orders
Convert
Большую часть преобразований охватывает функция CAST. Если же необходимо какое-то дополнительное форматирование, то можно использовать функцию CONVERT. Она имеет следующую форму:
CONVERT(тип_данных, выражение [, стиль])
Третий необязательный параметр задает стиль форматирования данных. Этот параметр представляет числовое значение, которое для разных типов данных имеет разную интерпретацию.
Например, некоторые значения для форматирования дат и времени:
0 или 100 - формат даты "Mon dd yyyy hh:miAM/PM" (значение по умолчанию)
1 или 101 - формат даты "mm/dd/yyyy"
3 или 103 - формат даты "dd/mm/yyyy"
7 или 107 - формат даты "Mon dd, yyyy hh:miAM/PM"
8 или 108 - формат даты "hh:mi:ss"
10 или 110 - формат даты "mm-dd-yyyy"
14 или 114 - формат даты "hh:mi:ss:mmmm" (24-часовой формат времени)
MS SQL Server и T-SQL | Преобразование данных
Некоторые значения для форматирования данных типа money в строку:
0 - в дробной части числа остаются только две цифры (по умолчанию)
1 - в дробной части числа остаются только две цифры, а для разделения разрядов применяется запятая
2 - в дробной части числа остаются только четыре цифры
Например, выведем дату и стоимость заказов с форматированием:
SELECT CONVERT(nvarchar, CreatedAt, 3),
CONVERT(nvarchar, Price * ProductCount, 1)
FROM Orders
TRY_CONVERT
При использовании функций CAST и CONVERT SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:
SELECT CONVERT(int, 'sql')
Чтобы избежать генерации исключения можно использовать функцию TRY_CONVERT. Ее использование аналогично функции CONVERT за тем исключением, что если выражение не удается преобразовать к нужному типу, то функция возвращает NULL:
SELECT TRY_CONVERT(int, 'sql')
-- NULL
SELECT TRY_CONVERT(int, '22')
-- 22
Дополнительные функции
Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут использоваться для преобразования в ряд типов:
STR(float [, length [,decimal]]): преобразует число в строку. Второй параметр указывает на длину строки, а третий - сколько знаков в дробной части числа надо оставлять
CHAR(int): преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
MS SQL Server и T-SQL | Преобразование данных
ASCII(char): преобразует символ в числовой код ASCII
NCHAR(int): преобразует числовой код UNICODE в символ
UNICODE(char): преобразует символ в числовой код UNICODE
SELECT STR(123.4567, 6,2)
-- 123.46
SELECT CHAR(219)
-- Ы
SELECT ASCII('Ы')
-- 219
SELECT NCHAR(1067)
-- Ы
SELECT UNICODE('Ы')
-- 1067
MS SQL Server и T-SQL | Функции CASE и IIF
Функции CASE и IIF
CASE
Функция CASE проверяет значение некоторого выражение, и в зависимости от результата проверки может возвращать тот или иной результат.
CASE принимает следующую форму:
CASE выражение
WHEH значение_1 THEN результат_1
WHEH значение_2 THEN результат_2
WHEH значение_N THEN результат_N
[ELSE альтернативный_результат]
END
Возьмем для примера следующую таблицу 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
);
Выполним запрос к этой таблице и используем функцию CASE:
SELECT ProductName, Manufacturer,
CASE ProductCount
WHEN 1 THEN 'Товар заканчивается'
WHEN 2 THEN 'Мало товара'
WHEN 3 THEN 'Есть в наличии'
ELSE 'Много товара'
END AS EvaluateCount
FROM Products
Здесь значения столбца ProductCount последовательно сравнивается со значениями после операторов WHEN. В зависимости от значения столбца ProductCount функция CASE будет возвращать одну из строк, которая идет после соответствующего оператора THEN. Для возвращаемого результата определен столбец EvaluateCount:
MS SQL Server и T-SQL | Функции CASE и IIF
Также функция CASE может принимать еще одну форму:
CASE
WHEH выражение_1 THEN результат_1
WHEH выражение_2 THEN результат_2
WHEH выражение_N THEN результат_N
[ELSE альтернативный_результат]
END
Например, применительно к таблице Products:
SELECT ProductName, Manufacturer,
CASE
WHEN Price > 50000 THEN 'Категория A'
WHEN Price BETWEEN 40000 AND 50000 THEN 'Категория B'
WHEN Price BETWEEN 30000 AND 40000 THEN 'Категория C'
ELSE 'Категория D'
END AS Category
1   2   3   4   5   6   7   8   9   10

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


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