Основи SQL-технології

В сучасних реляційних СУБД таких як Oracle, Informix, Sybase, DB2, dBASE, Paradox тощо використовується мова структурованих запитів SQL, що є досить потужною та популярною мовою маніпулювання даними реляційних СУБД. Основні можливості мови SQL захищені стандартами ANSIX.3.135 в 1986 році та ISO в 1987. Після внесення ряду доповнень в стандарт, мова SQL стала поєднувати в собі можливості мови визначення даних, мови маніпулювання даними та мови обробки запитів.

SQL-технологія реалізує наступні основні функції реляційних СУБД:

> визначення даних, тобто SQL дозволяє визначити структуру підтримуваних даних і організацію використовуваних реляційних відношень;

– доступ до даних - SQL забезпечує санкціонований доступ до даних клієнту;

– управління доступом - SQL забезпечує синхронізацію обробки бази даних різними прикладними програмами, захистом доступу від несанкційованого доступу;

– розділення даних - SQL координує роботу клієнтів, що одночасно виконують операції з БД;

– забезпечення цілосності даних, тобто використання SQL може забезпечити досить складні обмеження, задоволення яким буде перевірятися у випадках модифікації БД;

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

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

Сеанс SQL починається з вводу команди CONNECT, що виконує підключення користувача (клієнта) до БД. Формат команди наступний:

CONNECT <ім'я користувача>@<ім'я БД>

При підключенні користувача до БД буде автоматично надано запит на введення пароля користувача.

Завершення сеансу SQL здійснюється введенням команди DISCONNECT, введення якої відключить користувача від БД.

В SQL-технології підтримуються наступні типи даних:

– символьн і - дані, що складають відповідні найменування із слів чи словосполучень, тобто текст;

=> ч ислові - дані в числовому форматі, що придатні для проведення арифметичних операцій;

> значення дати та часу - дата та час, що встановлені на внутрішньому годиннику комп 'ютера в принятих системою форматах.

До символьних даних відносяться:

1 ) строки фіксованої довжини - це строки, довжина яких фіксована чи постійна. Для опису в БД строк фіксованої довжини використовується атрибут СНАRAСTER(п) або скорочено СНАR(n), den-фіксована довжина реквізиту;

2) строки змінної довжини - це строки, довжина яких є постійно різною, тобто плаваючою. Для визначення такої строки в стандарті SQL існує атрибут CHARACTER VARYING(n) абоскорочено VARCHAR(n), деп-максимальна довжина, що виділена для цього реквізиту. В СУБД Oracleвикористовується також; позначення VARCHAR2(n), що означає теж: саме.

До числових даних відносяться:

1) двоїчні числа - це числа, що можуть бути представлені з допомогою 0 та 1. Для їх опису в SQL існує атрибути ВІТ(п) ma BIT VAR YING(n), відповідно для фіксованої та змінної довжини;

2) десяткові числа - це числа в яких використовується десяткова розділова крапка. Для представлення десяткових чисел використовується атрибут DECIMAL(p,s), де р - точність, тобто загальна кількість знаків в числі, as-маштаб, тобто кількість знаків після десяткової крапки. Наприклад число 10251,025 може бути описане DECIMAL(8,3). Слід пам'ятати, що при введенні чисел, які перевищують формат опису, вони будуть округлятися, а також: формат задає максимально можливе число. Наприклад в останньому випадку не може бути введене число більше за 99999,999;

3) цілі числа - це додатні та від 'ємні числа, що не мають дробової частини. Для їх опису використовується атрибут INTEGER;

4) дійсні числа - це десяткові числа, що мають змінними точність та маштаб. Вони описуються атрибутами FLO А Т(п) або REA L (п).

В загальному випадку, згідно зі стандартом ANSI, всі числові значення можуть описуватись одним атрибутом NUMBER(n).

Значення дати та часу - можуть бути описан і атрибутами DATE і TIME. Вони як правило вказуються без довжини, тобто значення передається автоматично за системними форматами.

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

• мова визначення даних - Data Definition Language (DDL);

• мова маніпуляції даними - Data Manipulation Language (DML);

• мова запитів даних - Data Query Language (DQL);

• мова управління даними - Data Control Language (DCL);

• команди адміністрування даних;

• команди управління транзакціями.

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

Розглянемо основні оператори мови визначення даних.

Одним із таких операторів є CREA ТЕTABLE, що призначений для створення таблиць в базі даних. Перед створенням таблиці необхідно визначитися з структурою таблиці, які типи даних будуть вводитись, як назвати таблицю та стовпці в ній, яку довжину мають стовпці тощо. Якщо вище перерахована інформація визначена, то створення таблиці виконується:

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

(ім'я реквізиту_1 тип_ланих(довжина) [not null], ім'я реквізиту_2 типланих(довжина) [not null], ім'я реквізиту_3 типланих(довжина) [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

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

В даному випадку стовпець 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

(NOMPD

NUMBER(5)

NOT NULL PRIMARY KEY,

DATE

DATE

NOT NULL,

MFOA

CHAR(6)

NOT NULL,

NMRA

CHAR(14)

NOT NULL,

MFOB

CHAR(6)

NOT NULL,

NMRB

CHAR(14)

NOT NULL,

SUM

NUMBER(16)

NOT NULL,

PRIZ

CHARM 28)

NOT NULL),

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, що показані на рис.3. Оператором SELECT* FROMPLATDOR; з даної таблиці будуть вибрані та показані всі стовпці вказаної таблиці, тобто таблиця буде виведена повністю як на рис.3. Якщо виконати оператор 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;

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

Впорядкування даних можна проводити з допомогою оператора 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;

Очевидно, що результатом виконання даного оператора буде таблиця на рис.3 представлена навпаки, на першому місці буде останій рядок, а відповідно перший на останньому. В 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(строка_символів);

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

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

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

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

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

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

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

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

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

SIGN - знак числа

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

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

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

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

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

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

SELECT * FROM PLATDOR WHERE MFOB='315308';

Рис. 6. Схема доступу до даних з допомогою індекса.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дата добавления: 2014-01-04