Лабораторна робота № 3
Функції маніпулювання даними

Мета – глибше ознайомитися з синтаксисом SQL-запитів. Навчитися використовувати вбудовані функції у конструкціях SQL.

Засоби. Виділений сервер MySql, клієнт для адміністрування сервера (MySQL-Front або PHPMyAdmin), навчальна база даних.

Теоретичні відомості

Як і інші комп'ютерні формальні мови, SQL підтримує функції маніпулювання даними. Під такими функціями розуміють операції, що виконуються над даними, як правило, для зручнішого опрацювання та модифікації інформації до бажаного вигляду. Прикладом може послужити функція Year, яку ми використовували, щоб "витягнути" з повної дати рік. Точніше кажучи, ми перетворили дані, що мали тип DATETIME, у потрібне нам ціле число.

Функції мають меншу переносність, ніж код SQL, який, як відомо, є переносним і виконується у більшості системах. Більшість інструкцій SQL відносно переносна і, коли виявляються відмінності між реалізаціями SQL, вони, як правило, не настільки істотні, щоб викликати труднощі. На противагу цьому функції володіють набагато меншою переносністю, практично в кожній СУБД є свої специфічні функції, які інші СУБД не підтримують; іноді такі відмінності є дуже істотними. Зважаючи на це, багато програмістів так пишуть свій SQL-код, щоб не використовувати інструкцій, специфічних для даної реалізації мови SQL.

Якщо ви використовуєте в роботі специфічні функції, то не забувайте писати коментарі до свого коду, щоб пізніше інший програміст міг зрозуміти, для якої реалізації SQL призначений ваш код.

Більшість реалізацій SQL підтримують такі типи функцій:

•      функції для маніпулювання текстовими даними (для прикладу: виокремлення частини тексту, конвертація стрічки в верхній чи нижній регістр тощо);

•      числові функції використовуються в математичних операціях, для обробки числових даних (знаходження модуля, виконання різних арифметичних операцій);

•      функції дати і часу, використовуються для обробки даних, що мають тип date, time, datetime, як правило, для виділення окремих елементів з цілого значення;

•      системні функції видають специфічну інформацію, яка використовується в даній конкретній СУБД (наприклад, повертають ім'я поточного користувача СУБД, або інформацію про дану версію СУБД).

Функції для роботи з текстом

Розглянемо такий запит:

SELECT Name,   UPPER(Name) as Name_big  from students

ORDER BY Name_big

Результат виконання запиту:

Name	Name_big
Іванов Михайло Петрович	Іванов Михайло Петрович
Іванців Лариса Львівна	Іванців Лариса Львівна
...	...

У цьому прикладі бачимо, що функція Upper() конвертувала текст у верхній регістр, і в цьому прикладі ім'я студента виведено двічі, перший раз як поле збережене в таблиці Students, другий раз, перетворене функцією Upper, як поле Name_big.

Найчастіше використовують такі функції роботи з текстом:

Функції роботи з датою і часом

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

Найчастіше використовують такі функції роботи з датою і часом:

 

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

При роботі з датою, особливо при фільтрації даних оператором WHERE, найчастіше використовують специфічні функції, розглянуті вище. При роботі з датою найперше, що слід знати, — це формат дати, що використовує MySql. Кожного разу, коли необхідно вказувати конкретне значення дати при заповненні чи модифікації таблиць, дата повинна подаватися у форматі рррр-мм-дд. Так, перше вересня 2008 року виглядатиме 2008-09-01. Можливо інтерпретатор розпізнав би й інші варіанти, але варто віддати перевагу запропонованому способу, тому що це виключає двозначність (наприклад, 01/09/08 можна сприйняти і як 9-те січня 2008 р. і як 8-те вересня 2001 р. тощо).

Роки, вказані за допомогою двох цифр, в MYSQL теж підтримуються. Інтерпретатор сприймає 00- 69 як 2000-2069 і 70-99 як 1970-1999. Але щоб уникнути непорозумінь і конфліктів з новішими версіями, бажано вказувати роки в чотирицифровому вигляді.

Числові функції

Найчастіше використовують такі функції роботи з числами:

Функція CASE

Часто виникає необхідність при побудові вибірки аналізувати значення полів і виводити в деякому стовпці одне значення при певній умові та інше, коли ця умова не виконується. Ось, наприклад, потрібно зробити вибірку з таблиці студентів Students, яка має містити два стовпці: "ім'я студента" (Name) та "форма навчання" (Form), де має бути слово "платна" коли відповідне поле paid в таблиці Students дорівнює 1 або "державна" — коли 0. Потрібну вибірку зручно робити, використавши функцію CASE. Конструкція CASE обчислює та перевіряє вираз, а також порівнює результат з одним зі значень, вказаним у конструкції WHEN. Якщо знайдено збіг, то повертається відповідне значення конструкції THEN. У протилежному випадку повертається значення за замовчуванням.

SELECT Name,

(CASE (paid) WHEN 1 THEN 'платна' WHEN 0 THEN 'державна'

ELSE 'невідома' END) as form FROM students

Синтаксис функції CASE є таким:

CASE вираз WHEN значення виразу THEN результат, що повертається

[WHEN [значення виразу] THEN результат, що повер­тається ... ]

[ELSE результат що, повертається за замовчуванням при невиконанні жодної з умов]

END

Або альтернативний варіант:

CASE WHEN [умова1] THEN результат, що повертається1

[WHEN [умова2] THEN результат, що повертається2 ...]

[ELSE результат, що повертається при невиконанні жодної з умов]

END

Завдання

1. Написати запит, який повертає фільми з БД films, що були додані в каталог пізніш, ніж через 4 роки після дати їх створення.

2.      Вивести з БД univer список студентів, вказавши навпроти кожного поля Name, що містить ім'я студента, прізвище і по батькові, окремо ім'я студента.

3.      Вивести з БД films список фільмів, які були створені у високосні роки.

4.      Написати запит до БД films, результатом якого є таблиця з двома полями: назва фільму (Name) та кількість років (Years_old), що минули від створення кожного фільму до теперішнього часу.

5.      Вивести список усіх абонентів з БД phones, в яких номер телефону є повним квадратом.