Основы SQL

Строковые функции
Краткий обзор строковых функций представлен в таблице.

|ASCII | возвращает код ASCII левого символа строки |

|CHAR | по коду ASCII возвращает символ |

|CHARINDEX | определяет порядковый номер символа, с которого начинается вхождение подстроки в строку |

|DIFFERENCE | возвращает показатель совпадения строк |

|LEFT | возвращает указанное число символов с начала строки |

|LEN | возвращает длину строки |

|LOWER | переводит все символы строки в нижний регистр |

|LTRIM | удаляет пробелы в начале строки |

|NCHAR | возвращает по коду символ Unicode |

|PATINDEX | выполняет поиск подстроки в строке по указанному шаблону |

|REPLACE | заменяет вхождения подстроки на указанное значение |

|QUOTENAME | конвертирует строку в формат Unicode |

|REPLICATE | выполняет тиражирование строки определенное число раз |

|REVERSE | возвращает строку, символы которой записаны в обратном порядке |

|RIGHT | возвращает указанное число символов с конца строки |

|RTRIM | удаляет пробелы в конце строки |

|SOUNDEX | возвращает код звучания строки |

|SPACE | возвращает указанное число пробелов |

|STR | выполняет конвертирование значения числового типа в символьный формат |

|STUFF | удаляет указанное число символов, заменяя новой подстрокой |

|SUBSTRING | возвращает для строки подстроку указанной длины с заданного символа |

|UNICODE | возвращает Unicode-код левого символа строки |

|UPPER | переводит все символы строки в верхний регистр |

Таблица 11.2.

SELECT Фирма, [Фамилия]+""

+Left([Имя],1)+"."

+Left([Отчество],1)

+"." AS ФИО

FROM Клиент

Пример 11.5. Использование функции LEFT для получения инициалов клиентов.

|SIN | вычисляет синус угла |

|SQUARE | выполняет возведение числа в квадрат |

|SQRT | извлекает квадратный корень |

|TAN | возвращает тангенс угла |

Таблица 11.1.

SELECT Товар.Название, Сделка.Количество,

Round(Товар.Цена*Сделка.Количество

*0.05,1)

AS Налог

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=

Сделка.КодТовара

Пример 11.4. Использование функции округления до одного знака после запятой для расчета налога.

Пример 11.2. Создать и применить функцию табличного типа для определения двух наименований товара с наибольшим остатком.

CREATE FUNCTION user1.itog()

RETURNS TABLE

AS

RETURN (SELECT TOP 2 Товар.Название

FROM Товар INNER JOIN Склад

ON Товар.КодТовара=Склад.КодТовара

ORDER BY Склад.Остаток DESC)

Пример 11.2. Создание функции табличного типа для определения двух наименований товара с наибольшим остатком.

Пример 9.11. Добавить ограничение первичного ключа.

ALTER TABLE Товар ADD CONSTRAINT pk1

PRIMARY KEY(КодТовара)

Пример 9.11. Добавление ограничений первичного ключа.

Пример 9.12. Изменить столбец, добавив ограничение NOT NULL.

ALTER TABLE Товар ALTER COLUMN

Название VARCHAR(40) NOT NULL

Пример 9.12. Добавление ограничения NOT NULL.

Пример 9.13. Добавить ограничение уникальности значения.

ALTER TABLE Товар ADD CONSTRAINT

u1 UNIQUE(Название)

Пример 9.13. Добавление ограничения уникальности значения.

CREATE TABLE Товар

(КодТовара INT IDENTITY(1,1) PRIMARY KEY,

Название VARCHAR(50) NOT NULL UNIQUE,

Цена MONEY NOT NULL,

Тип VARCHAR(50) NOT NULL,

Сорт VARCHAR(50) NOT NULL

CHECK(сорт in('первый','второй','третий')),

Город VARCHAR(50) NOT NULL,

Остаток INT

CHECK(остаток>=0))

Пример 9.1. Создание родительской таблицы Товар с ограничениями.

CREATE TABLE Клиент

(КодКлиента INT IDENTITY(1,1) PRIMARY KEY,

Фирма VARCHAR(50) NOT NULL,

Фамилия VARCHAR(50) NOT NULL,

Город VARCHAR(50) NOT NULL,

Телефон CHAR(10) NOT NULL

CHECK(Телефон LIKE

'[1-9][0-9]-[0-9][0-9]-[0-9][0-9]'))

Пример 9.2. Создание родительской таблицы Клиент с ограничениями.

Пример 7.15. Найти фирму, купившую товаров на сумму, превышающую 10000 руб.

SELECT Клиент.Фирма,

Sum(Товар.Цена*Сделка.Количество)

AS Общ_стоимость

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Клиент.Фирма

HAVING Sum(Товар.Цена*Сделка.Количество)>10000

Пример 7.15. Определение фирмы, купившей товаров на сумму, превышающую 10000 руб.

Добавим в запрос подзапрос.

Пример 7.16. Найти фирму, которая приобрела товаров на самую большую сумму.

SELECT Клиент.Фирма,

Sum(Товар.Цена*Сделка.Количество)

AS Общ_стоимость

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Клиент.Фирма

HAVING Sum(Товар.Цена*Сделка.Количество)>=

ALL(SELECT Sum(Товар.Цена*Сделка.Количество)

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Сделка.КодКлиента)

Пример 7.16. Определение фирмы, которая приобрела товаров на самую большую сумму.

Вложенный подзапрос подсчитывает общую стоимость покупок каждого клиента. Внешний подзапрос также подсчитывает общую стоимость покупок каждого клиента и определяет тех, для кого эта сумма, по сравнению с другими покупателями, оказалась больше или точно такой же.

Пример 7.10. Определить товары, покупку которых осуществляют только клиенты из Москвы, и никто другой.

SELECT DISTINCT Товар.Название,

Клиент.ГородКлиента

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

WHERE Товар.Название NOT IN

(SELECT Товар.Название

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

WHERE Клиент.ГородКлиента#60;>'Москва')

Пример 7.10. Определение товаров, покупку которых осуществляют только клиенты из Москвы, и никто другой.

Пример 7.11. Какие товары ни разу не купили московские клиенты?

SELECT DISTINCT Товар.Название,

Клиент.ГородКлиента

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

WHERE Товар.Название NOT IN

(SELECT Товар.Название

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

WHERE Клиент.ГородКлиента='Москва')

Пример 7.11. Определение товаров, которые ни разу не купили московские клиенты?

Во вложенном запросе определяется список товаров, приобретаемых клиентами из Москвы. Во внешнем запросе выбираются только те товары, которые не входят в этот список.

Пример 7.12. Определить фирмы, покупающие товары местного производства.

SELECT DISTINCT Клиент.Фирма, Клиент.ГородКлиента,

Товар.ГородТовара

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

WHERE Клиент.ГородКлиента=Товар.ГородТовара

Пример 7.12. Определение фирм, покупающих товары местного производства.

Пример 7.4. Определить клиентов, в сделках которых количество товара отличается от максимального не более чем на 10%.

SELECT Клиент.Фамилия,

Сделка.Количество

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=

Сделка.КодКлиента

WHERE Сделка.Количество>=0.9*

(SELECT Max(Сделка.Количество)

FROM Сделка)

Пример 7.4. Определение клиентов, в сделках которых количество товара отличается от максимального не более чем на 10%.

Покажем, как применяются подзапросы в предложении HAVING.

Пример 7.5. Определить даты, когда среднее количество проданного за день товара оказалось больше 20 единиц.

SELECT Сделка.Дата, Avg(Сделка.Количество) AS

Среднее_за_день

FROM Сделка

GROUP BY Сделка.Дата

HAVING Avg(Сделка.Количество)>20

Пример 7.5. Определение даты, когда среднее количество проданного за день товара оказалось больше 20 единиц.

За каждый день определяется среднее количество товара, которое сравнивается с числом 20. Добавим в запрос подзапрос.

Пример 7.6. Определить даты, когда среднее количество проданного за день товара оказалось больше среднего показателя по всем сделкам вообще.

SELECT Сделка.Дата,

Avg(Сделка.Количество)

AS Среднее_за_день

FROM Сделка

GROUP BY Сделка.Дата

HAVING Avg(Сделка.Количество)>

(SELECT Avg(Сделка.Количество)

FROM Сделка)

Пример 7.1. Определить дату продажи максимальной партии товара.

SELECT Дата, Количество

FROM Сделка

WHERE Количество=(SELECT Max(Количество) FROM Сделка)

Пример 7.1. Определение даты продажи максимальной партии товара.

При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор.

Условия в HAVING отличаются от условий в WHERE:

* HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;

* WHERE исключает из расчета агрегатных значений по группировке записи, не удовлетворяющие условию;

* в условии поиска WHERE нельзя задавать агрегатные функции.