Лекція 4. Створення запитів і фільтрів. Обчислення за допомогою оператора SELECT. Вбудовані функції.

Мета:

1.   Вивчити створення запитів і фільтрів

2.   Зрозуміти процес виконання обчислень за допомогою оператора SELECT. вбудовані функції

 

Створення запитів і фільтрів

Запити призначені для зв'язку однієї або декількох таблиць, також вони можуть здійснювати відбір окремих полів з таблиці і виробляти фільтрацію даних згідно з умовою, накладеному на одне або кілька полів, такі запити називають фільтрами.

Для реалізації запитів використовують спеціальний мова запитів SQL (Structured Query Language).

У ІВ Запити можуть знаходитися як на стороні клієнтського додатка, так і на стороні сервера. Якщо запит зберігається на стороні клієнта, то він прописується всередині об'єкта зв'язку. У цьому випадку клієнтський додаток не залежить від файлу даних. Файл даних містить тільки таблиці, тому, ми легко можемо модифікувати клієнтську програму, не зачіпаючи файл даних, але в цьому випадку запит передається серверу через мережу, що може викликати проблеми з безпекою.

Якщо запит зберігається або виконується на сервері, то сам запит виступає в якості компонента БД, вся передача інформації відбувається всередині файлу даних, тобто всередині самого сервера, клієнтського додатку тільки передаються результати виконання запиту. У цьому випадку забезпечується висока захист даних, але у випадку зміни запиту доведеться міняти сам файл даних.

Всі запити діляться на:

1.   статичні;

2.   динамічні

Структура статичних запитів незмінна в ході роботи з програмою, а динамічні запити можуть змінюватися залежно від ситуації.

Зауваження. Зазвичай динамічні запити можуть бути реалізовані тільки за допомогою запитів, що виконуються на стороні клієнта. Якщо необхідно реалізувати динамічні запити, які виконуються на стороні сервера, то в цьому випадку необхідно використовувати збережені процедури.

Збережені процедури - SQL запит, збережений на стороні сервера і цей запит має параметри, які підставляються всередину SQL коду. При виклику збереженої процедури необхідно передавати в неї значення параметра.

В основному запит або збережена процедура або реалізує зв'язок між таблицями, або здійснює фільтрацію даних, деякі SQL запити також можуть робити обчислення.

У разі зв'язків між таблицями одна таблиця завжди виступає первинною, а інша вторинної, зв'язок відбувається за допомогою полів зв'язку. При зв'язку порівнюються записи з однаковими значеннями полів зв'язку. Первинна таблиця завжди заповнюється першої, а її полі зв'язку заповнюється автоматично (тип даних - лічильник). Вторинна таблиця завжди заповнюють після заповнення первинної таблиці, значення її поля зв'язку підставляється з значень поля зв'язку первинної таблиці. Поля зв'язку повинні мати однаковий тип даних.

Існує чотири види зв'язку між таблицями:

1.   Одна до однієї - одному полю в первинній таблиці відповідає одне поле у ​​вторинній таблиці;

2.   Одна до багатьох - одному полю в первинній таблиці відповідає декілька полів у вторинній таблиці;

3.   Багато до однієї - декількох полях в первинній таблиці відповідає одне поле у ​​вторинній таблиці;

4.   Багато до багатьох - одному полю в первинній таблиці відповідає декілька полів у вторинній таблиці і навпаки.

Запити з першим видами зв'язку називаються простими, а з рештою видів зв'язку - складними. Якщо в БД є хоча би дві пов'язаних таблиці, то БД називається реляційної.

Щоб створити запит необхідно зробити активною БД для якої створюється запит, потім в робочій області редактора запитів створити запит за допомогою команди SELECT, що має наступний синтаксис:

SELECT [ALL | DISTINCT]

[TOP | PERCENT n]

<Список полів>

[INTO <Ім'я нової таблиці>]

[FROM <Ім'я таблиці>]

[WHERE <Умова>]

[GROUP BY <Поле>]

[ORDER BY <Поле> [ASC | DESC]]

[COMPUTE AVG | COUNT | MAX | MIN | SUM (<Вираз>)]

Тут параметри ALL | DISTINCT показують, які записи обробляються: ALL обробляє всі записи, DISTINCT тільки унікальні, видаляються повторення записів.

TOP n визначає яка кількість записів обробляють, якщо вказано PERCENT, то n вказує відсоток від загального числа записів. <Список полів> - тут вказуються відображувані поля з таблиць через кому.

Зауваження:

1.   Якщо імена полів, що відображаються в різних таблицях не повторюються, то ми можемо вказувати тільки імена стовпців або полів без вказівки самих таблиць (ПІБ, посада). Якщо відображаються поля з різних таблиць з однаковими іменами потрібно вказувати і ім'я таблиці <Ім'я поля>. <Ім'я таблиці>;

2.   Тут же можна привласнювати псевдоніми полям, наступним чином <Ім'я поля> AS <Псевдонім>

3.   Якщо необхідно вивести всі поля з таблиці, то їх можна замінити значком "*"

Розділ INTO. Якщо присутній цей розділ, то на основі результатів запиту створюється нова таблиця.

Розділ FROM. Тут вказуються таблиці і запити, через кому, які беруть участь в новому запиті.

Зауваження: У розділі FROM так само можна задавати складні зв'язки, зв'язок поля однієї таблиці, з декількома полями іншої таблиці. У цьому випадку розділ FROM буде мати наступний вигляд:

 

FROM <Таблиця1> INNER JOIN <Таблиця2> ON <Таблиця1>. <Поле1> оператор <Таблиця2>. <Поле2> ...

Тут встановлюється взаємозв'язок Таблиці 1 і Табліци2 по Полю1 і Полю2 залежно від оператора порівняння. Таких розділів INNER JOIN може бути скільки завгодно.

Розділ WHERE. Даний розділ використовують для створення простих запитів, в цьому випадку в якості умови вказуємо зв’язуванні поля, або цей розділ використовують для створення фільтрів, тут вказують умови відбору. В умовах відбору ми можемо використовувати стандартні логічні оператори NOT, OR, AND.

 

Зауваження: У своєму стандартному вигляді запити можуть реалізовувати тільки статичні фільтри, але не динамічні. Для реалізації динамічних фільтрів використовуються збережені процедури.

Розділ GROUP BY - визначає поле для групування записів у запиті.

Розділ ORDER BY - визначає поле для сортування записів у запиті. Якщо вказаний параметр ASC, то буде проводитися сортування за зростанням, якщо DESC - спаданням. За замовчуванням використовується сортування за зростанням.

Розділ COMPUTE дозволяє в кінці результатів виконання запиту вивести деякі підсумкові обчислення за запитом. Можливі такі види обчислень: AVG - середня параметра; COUNT - кількість значень параметра не дорівнює NULL; MAX і MIN - максимальні і мінімальні значення параметра; SUM - сума всіх значень параметра, де <Вираз> - сам параметр. Як параметр зазвичай виступають які-небудь поля таблиць, що беруть участь у запиті.

 

Приклад: Даний запит пов'язує дві таблиці Співробітники та Посади по полях Код. При своєму виконанні він відображає перші 20 відсотків співробітників з обох таблиць. З таблиці співробітники відображаються всі поля, а з таблиці Посади тільки поле посаду. Наприкінці результатів виводиться кількість відображених співробітників.

 

SELECT TOP 20 PERCENT *. Cпівробітники, Посада. Посади

FROM Співробітники, Посади

WHERE Код. Співробітники = Код. Посади

COMPUTE COUNT (ПІБ. Співробітники)

Приклад: Даний запит з таблиці Операції виводить всі записи, значення поля Місяць у яких дорівнює "Май". Дані в результаті групуються по полю операція і сортуються за сумою операції. Наприкінці результатів запиту відображається загальна сума відібраних операцій за травень. Результати даного запиту зберігаються в таблиці "Угоди за травень".

 

SELECT ALL Операція, Сума

INTO [Угоди за Травень]

FROM Операції

WHERE Місяць = травень

GROUP BY Операція

ORDER BY Сума

COMPUTE SUM (Сума)

Зауваження: У даному запиті при позначенні назви полів таблиці явно не вказуються, оскільки використовувалася тільки одна таблиця.

 

Виконання обчислень за допомогою оператора SELECT. Вбудовані функції

Крім зв'язування таблиць і відбору даних оператор SELECT може використовуватися для обчислень. У цьому випадку він має синтаксис:

 

SELECT <Вираз>

де <вираз> - якийсь математичний вираз чи функція. Вираз має стандартний вид (як в Visual Basic), воно може включати в себе вбудовані функції сервера.

Зауваження: Ми можемо використовувати вбудовані функції і вирази в обчислюваних полях при створенні таблиць.

У SQL Server існують наступні вбудовані функції, розбиті на групи.

 

Математичні функції

Зауваження: В якості параметрів функції будемо вказувати відповідний їм тип даних.

·        ABS (numeric) - модуль числа;

·        ACOS / ASIN / ATAN (Float) - арккосинус, арксинус, арктангенс в радіанах;

·        COS / SIN / TAN / COT (Float) - косинус, синус, тангенс, котангенс;

·        CEILING (Numeric) - найменше ціле, більше або рівне параметру в дужках;

·        DEGREES (Numeric) - перетворює радіани в градуси;

·        EXP (Float) - експонента, ех;

·        FLOOR (Numeric) - найбільше ціле менше або рівне висловом numeric;

·        LOG (Float) - натуральний логарифм ln;

·        LOG10 (Float) - десятковий логарифм log10;

·        PI () - число пі;

·        POWER (Numeric, y) - зводить вираз Numeric в ступінь у;

·        RADIANS (Numeric) - перетворює градуси в радіани;

·        RAND () - генерує випадкове число типу даних Float, розташоване між нулем і одиницею;

·        ROUND (Numeric, Довжина) - округлює вираз Numeric до заданої Довжини (кількість знаків після комою);

·        SIGN (Numeric) - виводить знак числа +/- або нуль;

·        SQUARE (Float) - обчислює квадрат числа Float;

·        SQRT (Float) - обчислює квадратний корінь числа Float.

Приклади використання математичних функцій:

·        SELECT ABS (-10) результат 10

·        SELECT SQRT (16) результат 4

·        SELECT ROUND (125.85,0) результат 126

·        SELECT POWER (2,4) результат 16

Рядкові функції

Рядкові функції дозволяють робити операції з однією або кількома рядками.

·        'Рядок1' + 'рядок2' приєднує Рядок1 до Рядок2;

·        ASCII (Char) - повертає ASCII код з самого лівого символу виразу Char;

·        CHAR (Int) - виводить символ відповідний ASCII кодом у виразі Int;

·        CHARINDEX (Зразок, Вираз) - виводить позицію зразка виразу, тобто де знаходиться Зразок в Вираженні;

·        DIFFERENCE (Вираз1, Вираз 2) - порівнює два вирази, виводить числа від 0 до 4: 0 - вирази абсолютно різні; 4 - вирази абсолютно ідентичні. Обидва висловлювання типу даних Char;

·        LEFT (Char, Int) - виводить з рядка Char Int символів зліва;

·        RIGHT (Char, Int) - виводить з рядка Char Int символів праворуч;

·        LTRIM (Char) - видаляє з рядка Char пробіл зліва;

·        RTRIM (Char) - видаляє з рядка Char пробіл праворуч;

·        WCHAR (Int) - виводить вираз Int у форматі Unicode;

·        REPLACE (рядок1, рядок2, рядок 3) - змінює в рядок1 всі елементи рядок2 на елементи рядок 3;

·        REPLICATE (Char, Int) - повторює рядок Char Int разів;

·        REVERSE (Сhar) - виробляє інверсію рядки Char, тобто розпорядженні символи у зворотному порядку;

·        SPACE (Int) - виводить Int пробіла;

·        STR (Float) - переводить число Float в рядок;

·        STUFF (Вираз1, Початок, Довжина, Вираз 2) - видаляє з Вираз 1 починаючи з позиції символу Початок кількість символів рівне параметру Довжина, натомість підставляє Вираз 2;

·        SUBSTRING (Вираз, Початок, Довжина) - з Вирази виводиться рядок заданої Довжини починаючи з позиції Початок;

·        UNICODE (Char) - виводить код у форматі Unicode першого символу в рядку Char;

·        LOWER (Char) - переводить рядок Char в маленькі букви;

·        UPPER (Char) - переводить рядок Char в заголовні букви.

Приклади застосування строкових функцій:

 

·        SELECT ASCII ('G') результат 71.

·        SELECT LOWER ('ABC') результат abc.

·        SELECT RIGHT ('ABCDE', 3) результат CDE

·        SELECT REVERSE ('МИР') результат РИМ.

Зауваження. У всіх строкових функціях значення виразу типу Char полягають в одинарні лапки.

Функції дат

Зауваження: в деяких функціях дат використовується так звана частина дат, яка кодується спеціальними символами:

dd - число дат (від 1 до 31);

dy - день року (число від 1 до 366);

hh - значення години (0-23)

ms - значення секунд (від 0 до 999)

mi - значення хвилин (0-59)

qq - значення (1-4)

mm - значення місяців (1-12)

ss - значення секунд (0-59)

wk - значення номерів тижнів у році

dw - значення днів тижня, тиждень починається з неділі (1-7).

yy - значення років (1753 -999)

Функції дат призначені для роботи з датами або часу. Існують декілька наступні функції дат:

·        DATEADD (частина, число, date) - додає до дати date частина дати збільшене на число;

·        DATEDIFF (частина, date1, date2) - виводить кількість частин дати між date1 і date2;

·        DATENAME (частина, date) - виводить символьне значення частин дати до заданої дати (назва днів тижня);

·        DATEPART (частина, date) - виводить числове значення частини дати з заданої дати (номер місяці);

·        DAY (date) - виводить кількість днів в заданій даті;

·        MONTH (date) - виводить кількість місяців в заданій даті;

·        YEAR (date) - виводить кількість років в заданій даті;

·        GETDATE () - виводить поточну дату встановлену за комп'ютером;

Зауваження: Дати виводяться в Американському форматі: місяць / день / рік.

Приклади функції робіт з датами:

·        SELECT DATEADD (dd, 5,11 / 20/07) результат Nov / 25/2007.

·        SELECT DATEDIFF (dd, 11/20/07, 11/25/07) результат 5 днів.

·        SELECT DATENAME (mm, 11/20/07) результат November.

·        SELECT DATEPART (mm, 11/20/07) результат 11.

Зауваження: У виразах оператора SELECT можна використовувати операції порівняння. У результаті буде або істина TRUE, або брехня FALSE. Можна використовувати наступні оператори: =, <,>,> =, <=, <>,! <(не менше),!> (не більше),! = (Не дорівнює). Пріоритет операції задається круглими дужками.

 

Системні функції

Системні функції призначені для отримання інформації про базу даних та її вмісті. У SQL сервері існують наступні системні функції:

·        COL_LENGTH (таблиця, поле) - виводить ширину поля;

·        DATALENGTH (вираз) - виводить довжину вирази;

·        GETANSINULL (ім'я БД) - виводить допустимо або неприпустимо використовувати в БД значення NULL;

·        IDENT_INCR (таблиця) - виводить крок збільшення поля лічильника в таблиці;

·        IDENT_SEED (таблиця) - виводить початкове значення лічильників в таблиці;

·        ISDATE (вираз) - виводить одиницю, якщо вираз є датою і нуль, якщо не є;

·        ISNUMERIC (вираз) - виводить одиницю, якщо вираз є числовим і нуль, якщо не числовим;

·        NULIFF (вираз1, вираз2) - виводить NULL якщо вираз1 рівний виразу2.

Агрегатні функції

Агрегатні функції - дозволяють обчислювати підсумкові значення по полях таблиці.

·        AVG (поле) - виводить середнє значення поля;

·        COUNT (*) - виводить кількість записів у таблиці;

·        COUNT (поле) - виводить кількість всіх значень поля;

·        MAX (поле) - виводить максимальне значення поля;

·        MIN (поле) - виводить мінімальне значення поля;

·        STDEV (поле) - виводить середньоквадратичне відхилення всіх значень поля;

·        STDEVP (полі) - виводить середньоквадратичне відхилення різноманітних значень поля;

·        SUM (поле) - підсумовує всі значення поля;

·        TOP n [Percent] - виводить n перших записів з таблиці, або n% записів з таблиці;

·        VAR (поле) - виводить дисперсію всіх значень поля;

·        VARP (поле) - виводить дисперсію всіх різних значень поля.

Приклади використання агрегатних функцій:

·        SELECT AVG (вік) FROM Студенти - виводить середній вік студента з таблиці "Студенти".

·        SELECT COUNT (ПІБ) FROM Студенти - виводить кількість різних ПІБ з таблиці "Студенти".

·        SELECT Top 100 * FROM Студенти - виводить перші 100 студентів з таблиці "Студенти".

 

Контрольні питання

1.   Для чого призначені запити?

2.   Яку мову запитів SQL використовують для реалізації запитів?

3.   Де можуть зберігатися запити?

4.   Які є запити?

5.   Які є види зв’язку між таблицями?

6.   Для чого призначений розділ FROM?

7.   Для чого використовується розділ WHERE?

8.   Для чого призначені системні функції?

9.   Які системні функції існують у SQL?

10.       Для чого призначені агрегатні функції?