Використання статистичних функцій

CREATE TABLE ім'я_таблиці

(ім'я реквізиту_1 тип_ланих(довжина) [not null], ім'я реквізиту_2 типланих(довжина) [not null], ім'я реквізиту_3 типланих(довжина) [not null],...);

Наприклад для створення таблиці PLATDOR, структура якої була визначена в розділі 2.3, необхідно виконати оператор:

CREATE TABLE PLATDOR

NMRB CHAR(14) NOT NULL,

SUM NUMBER(16) NOT NULL,

PRIZ CHAR(128) NOT NULL);

З допомогою опції PRIMARY KEY можна призначати ключові реквізити. В прикладі, що вище ключовий реквізит NOMPD - номер платіжного доручення. В таблиці може бути задано і декілька ключів. З цією метою опція PRIMARY KEY вставляється в кінці опису стовпців з переліком імен ключових стовпців, наприклад PRIMARY KEY (NOMPD, DATE). Якщо є необхідність задати контроль стовпців на унікальність, тобто всі значення даних в цьому стовпці мають бути неповторними, то вказується опція UNIQUE. З допомогою опції NULL значення реквізиту контролюється на можливість пустого значення чи нуля. Відповідно в противному вказується NOT NULL.

Після створення таблиці її можна модифікувати з допомогою оператора AL TER ТАBLE, що дозволяє добавляти та видаляти стовпці, міняти визначення стовпців тощо. В загальному вигляді синтаксис оператора має формат:

ALTER TABLE ім'я таблиці [MODIFY] [COLUMN

ім'я_стовпця] [тип_даних(довжина) | NULL NOT NULL]

[RESTRICT I CASCADE]

[DROP] [CONSTRAINT ім'я_обмеження]

[ADD] [COLUMN визначення_стовпця]

Наприклад, для зміни атрибутів стовпця MFOB таблиці PLATDOR, необхідно виконати оператор:

ALTER TABLE PLATDOR MODIFY (MFOB NUMBER(6));

Як бачимо після виконання оператора буде змінено тип даних стовпця MFOB з символьного CHAR(6) на числовий NUMBER(6). Аналогічно можна змінити довжину відповідних реквізитів.

З допомогою даного оператора можна добавляти нові стовпці в таблицю. Наприклад, щоб добавити в таблицю PLATDOR новий стовпець SLUGB - призвіще службовся, що провів операцію, необхідно виконати:

ALTER TABLEPLATDORADDCOLUMN(SLUGBVARCHAR2(20));

При видаленні таблиць необхідно перед іменем таблиці вказувати ім'я користувача. Видалятися може як вся таблиця разом з усіма посиланнями на інші таблиці так і окремі їїстовпці. Для видалення таблиці PLATDOR користувачем ADMIN необхідно виконати оператор:

DROP TABLE ADMIN.PLATDOR RESTRICT | CASCADE;

Опції RESTRICT - перевірка чи є на таблицю посилання (у випадку наявності оператором буде видана помилка) та CASCADE - видалення всіх посилань на таблицю не є обов'язковими.

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

Рис. 5.10. Материнсько-дочірні відношення між: таблицями.

В даному випадку стовпець NMRA може бути призначений зовнішнім ключом дочірньої таблиці KLIENT_TBL. Цей зовнішній ключ посилається на стовпець NMRA з таблиці PLATDOR. Наявність зовнішнього ключа гарантує, при даному відношенні, що для кожного значення NMRA в таблиці KLIENT_TBL знайдеться відповідне значення NMRA в таблиці PLATDOR.

В стандарті SQL для створення зовнішнього ключа необхідно ввести оператор:

CREATE TABLE KLIENTTBL

(IDKOD CHAR(12) NOT NULL,

NAZVA VARCHAR(20) NOT NULL,

NMRA CHAR(14) NOT NULL,

SECEK CHAR(5) NULL,

ADRES CHAR(50) NULL,

KERIV CHAR(36) NULL,

CONSTRAINT NMRA_FK FOREIGN KEY (NMRA)

REFERENCESPLATDOR(NMRA));

Можна теж саме зробити дещо простіше, якщо таблиця KLI-ENT_TBL була створена раніше, наприклад:

ALTER TABLE KLIENT_TBL

ADD CONSTRAINT NMRA_FK FOREIGN KEY (NMRA)

REFERENCES PLATDOR (NMRA);

З допомогою SQL-технології можна задавати умови для перевірки правильності даних, пошуку їх в базі даних тощо. Наприклад, якщо необхідно призначити умову перевірки значень стовпця MFOA, що дорівнює '315308', то необхідно виконати:

CREATE TABLE PLATDOR

CONSTRAINT CHK.MFOA CHECK (MFOA='315308');

Мова маніпулювання даними (DML) - це частина SQL, що надає користувачу БД можливість вводити реальні зміни в інформацію реляційної бази даних. З допомогою DML користувач може доповнювати таблиці новими даними, поновлювати існуючі дані та видаляти інформацію з БД. З цією метою в SQL є оператори INSER Т, UPDA ТЕ, DELETE.

Для введення нових даних в таблицю використовується оператор INSERT. В загальному вигляді оператор має формат:

INSERT INTO ім'я_схеми.ім'я_таблиці

УАШЕвСзначенняГ,'значенням,..., [NULL]);

Наприклад для введення нового запису в таблицю PLATDOR необхідно виконати оператор:

INSERT INTO PLATDOR

VALUES('25','30/ll/2004','315018','26001260254001',

'315405726004300145002735000.00',

'за послуги акт №025 від 29.11.2004');

Якщо інформацію потрібно ввести в певні стовпці, як наприлад в IDKOD, NMRA, NAZVA таблиці KLIENT_TBL, необхідно виконати оператор:

INSERT INTO KLIENT_TBL(IDKOD, NMRA, NAZVA)

УАШЕ8('1273201145726005802450001','ВАТУкртелеком');

Для оновлення даних в таблиці використовується оператор UPDA ТЕ, що не вводить та не видаляє записи, а лише змінює існуючі на нові. В загальному вигляді для зміни даних одного стовпця вико­нується оператор:

UPDATE ім'я_таблиці

SET ім'я_стовпця = 'значення'

[WHERE умова];

Наприклад для заміни значення MFOB на315816в таблиці PLATDOR, якщо значення NOMPD = 25, необхідно виконати оператор:

UPDATE PLATDOR

SET MFOB = '315816'

WHERE NOMPD = 25

Для видалення даних з таблиць використовується оператор DELETE, що в загальному вигляді має формат:

DELETE FROM ім'я_схеми.ім'я_таблиці

[WHERE умова];

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

Мова запитів даних - це складова частина SQL, що надає користувачу можливість вилучати інформацію з БД у зручному для нього вигляді. У стандарті SQL запити реалізовуються з допомогою оператора SELECT, що не використовується сам по собі, а потребує використання додаткових параметрів та ключових слів. Такими ключовими словами є обов'язкове слово FROM та необов'язкові WHERE і ORDERBY. В загальному випадку оператор SELECT має наступний формат:

SELECT [ * | ALL | DISTINCT стовпець 1,стовпець2,...]

FROM таблицяі [таблиця2,...];

В даному випадку '*' - означає, що в результаті запита будуть показані всі стовпці таблиці. Опція ALLвикористовується для того, щоб показати дані в стовпці всі, навіть ті які повторяються. На відміну від ALLопція DISTINCT дозволяє показувати в стовпці лише дані, які не повторяються. За ключовим словом SELECT слідує перелік стовпців, які користувач хоче бачити, а за словом FROM перелік таблиць з яких вказані дані будуть вилучатись.

Для прикладу розглянемо дані таблиці PLATDOR, що показані на рис.5.8. Оператором SELECT* FROMPLATDOR; з даної таблиці будуть вибрані та показані всі стовпці вказаної таблиці, тобто таблиця буде виведена повністю як на рис.5.8. Якщо виконати оператор SELECT MFOB FROM PLATDOR; то буде виведено лише один стовпець, що відповідає МФО банка отримувача. Опцію ALL в даному випадку вказувати немає сенсу, тому що вона діє по умовчуванню. Однак, якщо вказати в операторі опцію DISTINCT, як наприклад: SELECT DISTINCT MFOB FROM PLATDOR; то буде виведено стовпець 315018,300001,815013,315405, тобто 315018 лишеодин раз.

Оператор SELECT може виконуватись із заданою умовою, тобто частиною запиту, що несе інформацію про метод відбору даних із БД. В загальному випадку оператор має формат:

SELECT [ALL | * | DISTINCT стовпець 1,стовпець2,...]

FROM таблиця 1,[таблиця2]

WHERE [умоваї | виразі]

[AND умова2 | вираз2];

Для прикладу розглянемо ситуацію, коли потрібно з таблиці PLATDOR відібрати документи сума яких більша за 35000 грн. Для цього необхідно виконати оператор:

SELECT* FROM PLATDOR WHERE SUM > 35000;

Результатом його виконання буде два перших рядки з таблиці.

Впорядкування даних можна проводити з допомогою оператора SELECT з ключовим словом ORDER BY. В загальному випадку оператор має формат:

SELECT [ALL | * | DISTINCT стовпець 1,стовпець2,...]

FROM таблиця 1, [таблиця2 ]

WHERE [умоваї | виразі]

[AND умова2 | вираз2]

[AND умова2 | вираз2]

ORDER BY стовпецьі | ціле_значення [ASC|DESC];

Опція ASC визначає порядок впорядкування даних за зростанням і може бути опущена тому що діє по умовчуванню. Відповідно опція DESC визначає порядок впорядкування по спаданню даних. Наприклад для впорядкування таблиці PLATDOR по спаданню стовпця NOMPD необхідно виконати оператор:

SELECT * FROM PLATDOR ORDER BY NOMPD DESC;

Очевидно, що результатом виконання даного оператора буде таблиця на рис.5.8 представлена навпаки, на першому місці буде останій рядок, а відповідно перший на останньому. В ORDER BY може вживатися номер стовпця, наприклад ORDER BY I DESC, де 1-й стовпець-це NOMPD.

З допомогою оператора SELECT та функції COUNT можна підра­хувати кількість записів в таблиці. В загальному вигляді оператор має формат:

SELECT COUNT(*) FROM ім'я_таблиці;

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

SELECT СОШТ(ім'я_стовпця) FROM ім'я_таблиці;

Розглянемо деякі операції, що можуть задавати умови для вибору даних. До них відносяться:

операції порівняння;

логічні операції;

арифметичні операції.

До операцій порівняння належать рівність - ( = ), нерівність -(<>), більше - ( >) та менше - (<). В деяких реалізаціях SQL нерівність позначається (!=) замість (<> ).

Операція рівності в SQL призначена для визначення рівності одного значення іншому. Наприклад, якщо потрібно знайти в таблиці платіжних доручень документ сума за яким рівна 36000, то це можна реалізувати оператором:

SELECT * FROM PLATDOR WHERE SUM = 36000;

Як бачимо, результатом виконання даного запиту буде документ за № 326. Аналогічно у випадку нерівності, оператором

SELECT * FROM PLATDOR WHERE SUM <> 36000;

буде отримано всі документи окрім платіжного доручення за № 326.

Логічні операції Надаються ключовими словами:

IS NULL - використовується для перевірки рівності даного значення значенню NULL. Наприклад, якщо необхідно знайти документ в таблиці PLATDOR сума якого відповідає NULL, то необхідно виконати оператор: SELECT * FROM PLATDOR WHERE SUM = NULL; Результатом його роботи буде 0 документів, тому що немає в таблиці таких значень суми документа;

BETWEEN - використовується для пошуку значень, що попадають в діапазон від мінімального до максимального. Наприклад, якщо потрібно знайти всі платіжні доручення сума яких більша за 10000, але менша за 20000, то необхідно виконати оператор: SELECT * FROM PLATDOR WHERE SUM BETWEEN 10000 AND 20000;

IN - використовується для порівняння вказаного значення із заданим конкретним списком значень. Наприклад для пошуку документів за номерами 326,328,329 необхідно виконати оператор:

SELECT * FROM PLATDOR

WHERE NOMPD IN ('326','328','329');

LIKE - використовується для пошуку значень, що похожі на заданий контекст. Наприклад оператором: SELECT * FROM PLATDOR WHERE MFOB = '315%'; будуть виведені документи банк отримувача в яких починається з '315', тобто вибір банків Хмельницької області. Знак (%) використовується для представлення певної кількості символів від нуля до максимальної довжини реквізиту. Знак (_) означає один символ або число.

AND - логічне І (кон'юнкція) забезпечує зв'язок логічним І двох умов у ключовому слові WHERE.

OR - логічне АБО (диз'юнкція) забезпечує зв'язок логічним АБО двох умов у ключовому слові WHERE.

NOT - логічне заперечення НЕ надає всім атрибутам з якими вживається протилежного значення.

Арифметичні операції призначені для виконання елементарних арифметичних дій: додавання - (+), віднімання - (-), множення - (*) та ділення - (/). Арифметичні операції задаються оператором SELECT, що у загальному випадку має формат:

SELECT (стовпецьі + стовпець2 - стовпецьЗ) * стовпець5

FROM ім'я_таблиці;

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

COUNT - призначена для підрахунку кількості рядків в таблиці чи значень даних в стовпці відмінних від NULL. В загальному вигляді оператор функції має формат: COUNT [ (* ) | (DISTINCT | ALL) ] ( ім'я_стовпця)

SUM - використовується для підрахунку суми значень в стовпці.

Формат оператора функції наступний: SUM ([ DISTINCT ] ім'я_стовп-ця);

МАХ - призначена для визначення максимального значення вказаної групи рядків. В загальному випадку оператор має формат: МАХ ([ DISTINCT ] ім'я_стовпця);

MIN - призначена для визначення мінімального значення вказаної групи рядків. В загальному випадку оператор має формат: MIN ([ DISTINCT ] ім'я_стовпця);

AVG - використовується для підрахунку середнього арифметичного значення заданої сукупності рядків таблиці.В загальному випадку оператор має формат: AVG ([ DISTINCT ] ім'я_стовпця).

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

CONCATENATION - конкатенація, тобто об'єднання декількох строк в одну. В загальному випадку функція має формат: ім'я_строки1 П ім'я_строки2 абоім'я_строки1 + ім'я_строки2

TRANSLATE - заміняє символи в строці на вказані. Оператор має формат: TRANSLATE ( множина_строк, значення 1, значення2) де значення 1 вказує, що замінити, а значення2 на що замінити;

UPPER - призначена для переводу даних з регістра нижнього на верхній і має формат UPPER(строка_символів);

LOWER - призначена для переводу даних з регістра верхнього на нижній і має формат LOWER(строка_символів);

SUBSTR - використовується для виділення підстроки символів зі строки за вказаними позиціями. В загальному вигляді має формат: SUBSTR (ім'я_стовпця, початкова_позиція, довжина);

INSTR - призначена для пошуку заданого контексту символів в строці тексту і повертає позицію, починаючи з якої даний контекст зустрівся. Функція має формат: INSTR (ім'я_строки, 'контекст');

LENGTH - визначає довжину вказаної строки і має формат: LENGTH (ім'я_строки).

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

ABS - абсолютне значення числа

SQRT - корінь квадратний

POWER - піднесення до степеня

ЕХР - експонента

ROUND - округлення

SIGN - знак числа

FLOOR - ціла частина числа

SIN, COS, TAN - тригонометричні функції

Математичні функції призначені для визначення значень деяких мате­матичних величин, що зустрічаються в процесі алгоритмів обчислень і мають формат: ФУНКЦІЯ (ВИРАЗ).

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

4. Синтаксис SQL-інструкцій

В SQL можна проводити індексування за стовпцями. При створенні індекса таблиці в нього заноситься інформація про розміщення даних в стовпці по якому проводиться індексування. Якщо вказане значення в індексі буде знайдено, то індекс швидко надасть адресу, тобто точне місцезнаходження даних в таблиці рис. 5.11.

В SQL для пошуку даних в стовпці MFOB, а саме він приведений на рис.5.11, необхідно виконати запит:

SELECT * FROM PLATDOR WHERE MFOB='315308';

Зрозуміло, що якщо індекс створено не було, то пошук буде здійснюватись послідовно по всій таблиці. У випадку створення індекса це буде швидко як на рис.5.11, тобто бачимо, що індекс впорядковано за зростаннями МФО банків, тому знайдений він буде відразу і передано управління за адресою де знаходяться дані в таблиці.

Для створення індекса в загальному випадку необхідно виконати оператор:

CREATE INDEX ім'я_індекса

ON ім'я_таблиці (ім'я_стовпця);

Розрізняють ^прості, ^складні та ^унікальні індекси.

Простий індекс - це індекс, що створюється за даними одного стовпця таблиці.

Складний індекс - це індекс, що створюється за даними декількох стовпців таблиці.

Унікальний індекс - це індекс, що використовується як для швидкого пошуку даних так і для контролю їх цілісності.

Унікальний індекс не дозволяє вводити в таблицю дані, що повто­рюються. В загальному випадку формат оператора для створення унікального індекса наступний:

CREATE UNIQUE INDEX ім'я_індекса

ON ім'я_таблиці (ім'я_стовпця);

Слід зазначити, що створення індексів має сенс у випадках:

Р- використання зовнішніх ключів, як елемента зв язування двох таблиць;

Р для стовпців, що часто використовуються у виразах ключових слів ORDER BY та GROUP BY;

Р за стовпцями, що мають велику кількість унікальних даних.

Література:

1. Гончаров А. Ю. Access 2007: справочник с примерами / А. Ю. Гончаров. – М.: Кудиц-Пресс, 2008. – 293 с.

2. Кучерява Т. О. Інформатика та комп’ютерна техніка: активізація навчання: практикум для індивідуальної роботи / Т. О. Кучерява, М.

В. Сільченко, І. В. Шабаліна. – К.: КНЕУ, 2006. – 448 с.

3. Макарова М. В. Інформатика та комп’ютерна техніка: навч. посібник для студ. вищих навч. закл. / М. В. Макарова, Г. В. Карнаухова, С. В. Запара. – Суми: Університетська книга, 2008. – 665 c.

4. Редько М. М. Інформатика та комп’ютерна техніка: навч.-метод.посібник з контрольними завданнями для заоч. форми навч. / М. М. Редько. – Вінниця: Нова Книга, 2007. – 568 c.

Питання для самоконтролю

1. Що таке SQL-запит? Яка різниця між використанням DBQ-запиту і SQL-запиту?

2. Що таке простий SQL-запит на вибірку? Яке призначення режиму перегляду SQL-запитів?

3. Як створити нову таблицю за допомогою SQL-запиту запитів? Як побудувати умову зі сполучником "або" у SQL-запиту запиті?

4. Що таке SQL-запиту запит на доповнення? Які є способи створення SQL-запитів? Як додати таблицю у вікно конструктора SQL-запитів?

5. Як перемістити поля з таблиці у бланк запиту? Як упорядкувати записи, використовуючи SQL-запит? Як змінити тип запиту? Як побудувати умову зі сполучником "і" у SQL-запиті?

6. Що таке SQL-запит з параметром? Як модифікувати запит? Як скопіювати частину структури таблиці у нову таблицю?

7. Як виконати обчислення в SQL-запиті? Як відшукати і вилучити з таблиці записи, що повторюються?