Додавання інформації в базу даних

Всі записи в SQL уводяться з використанням команди модифікації INSERT. У найпростішій формі ця команда ім'я наступний синтаксис:

INSERT INTO <table name> VALUES <value>, <value>…);

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

INSERT INTO TEACHERS

VALUES (4006, 'Федченко', 'Світлана', 'Геннадіївна', 01/09/1999);

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

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

INSERT INTO TEACHERS

VALUES (NULL, 'Федченко', 'Світлана', 'Геннадіївна', 01/09/1999);

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

Також допускається вказувати стовпці, куди необхідно здійснити вставку значення, що дозволяє робити це в будь-якому порядку. Наприклад, команда:

INSERT INTO TEACHERS (TDATE, TFAM, TIMA)

VALUES (01/09/1999, 'Федченко', 'Світлана');

дозволяє вставити значення в полючи таблиці в порядку TDATE, TFAM, TIMA, причому стовпці TNUM і ТОТСН відсутні. Це означає, що для цих полів автоматично встановлюється значення за замовчуванням. Значення за замовчуванням може бути введене чи заздалегідь, у противному випадку, це буде NULL значення. Якщо обмеження забороняє використання значення NULL у даному полі, то обов'язково треба подбати про забезпечення стовпця змістовним значенням для будь-якої команди INSERT.

Можна використовувати команду INSERT для того, щоб чи одержувати вибирати значення з однієї таблиці і поміщати їх в іншу разом із запитом. Для цього пропозиція VALUES заміняється на відповідний запит:

INSERT INTO EXCELLENT SELECT * FROM USP WHERE OCENKA = 5;

У результаті буде сформована таблиця з даними, приведеними в табл. 2.5.

Таблиця 2.5 Таблиця ЕХСЕLLENT

Отже, буде зробленеі наступне: Всі значення, дані запитом (інформація про студентів, що має тільки особисті оцінки), містяться в таблицю, названу EXCELLENT. Для того щоб не відбулося помилки, таблиця EXCELLENT повинна вже бути створена командою CREATE TABLE і мати п'ять стовпців, що збігаються з таблицею USP по типі даних.

Таким чином, буде отримана незалежна таблиця з деякими даними з таблиці успішності USP. При зміні значень у таблиці USP це ні в якому разі не відіб'ється на таблиці EXCELLENT.

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

Наприклад, за допомогою нижчеподаної команди можна вставити інформацію про середній бал кожного студента:

INSЕRT INTO AVGRAITING (SNUM, AVGOCENKA) SELECT SNUM, AVG

(OCENKA) FROM USP GROUP BY SNUM;

Зверніть увагу на те, що зазначено імена стовпців таблиці AVGRAITING, а виходить, послідовність даних списку, що вставляється, (тобто порядок проходження полів у пропозиції SELECT) повинна з цим порядком збігатися.

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

INSERT INTO STO (SFAM, SIMA, SOTCH) SELECT SFAM, SIMA, SOTCH FROM STUDENTS WHERE SNUM = ANY (SELECT SNUM FROM USP WHERE OCENKA = 5);

Обидва запити в цій команді функціонують так само, як якби вони не були частиною вираження INSERT. Подзапрос знаходить Всі рядки для студентів, що мають відмінні оцінки, і формує набір значень SNUM. Зовнішній запит вибирає рядка з таблиці STUDENTS, де ці значення SNUM знайдені, а INSERT уставляє знайдені дані в таблицю STO.

У команді INSERT допускається використовувати співвіднесені подзапросы. Припустимо, що мається таблиця МАХОCENKA, у якій зберігається інформація про студента, що має максимальну оцінку за визначену дату (скажемо, для нарахування іменної стипендії). Тоді, для відстеження зміни даних у таблиці успішності і модифікації відповідної інформації про претендента на іменну стипендію, необхідно скористатися наступною командою зі співвіднесеним подзапросом:

INSERT INTO MAXOCENKA (SNUM, OCENKA) SELECT SNUM, OCENKA

FROM USP FIRST WHERE OCENKA = (SELECT MAX (OCENKA)

FROM USP SECOND WHERE FIRST.UDATE=SECOND.UDATE);

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

Видалення даних

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

DELETE FROM STUDENTS;

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

DELETE FROM STUDENTS WHERE SNUM = 3416;

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

У команді DELETE допускається використовувати предикат, що вибирає целую групу рядків. Наприклад, що випливає команда видаляє з таблиці USP Всі дані, що відносяться до оцінок, лученным 10/06/1999:

DELETE FROM USP WHERE UDATE = 10/06/1999;

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

DELETE FROM STUDENTS WHERE SNUM=(SELECT SNUM FROM USP

WHERE OCENKA= 3);

У даному випадку подзапрос вибере всіх студентів, що мають трійки, з таблиці успішності, і в предикат основної команди поверне номера їхніх студентських квитків.

Допускається в предикаті команди DELETE використовувати і подзапросы, що дає можливість установити досить складні критерії того, які рядки будуть віддалятися. Крім того, дуже ефективно виконувати спочатку вторинні дії (перевірки і т.п.), після чого виконувати саме видалення. Хоча не можна посилатися на таблицю, з якої будуть віддалятися запису, у пропозиції FROM подзапроса, у предикаті допускається посилання на поточну рядок цієї таблиці, тобто можна використовувати співвіднесені подзапросы. Наприклад:

DELETE FROM STUDENTS WHERE EXISTS (SELECT * FROM USP

WHERE OCENKA = 3 AND STUDENTS.SNUM = USP.SNUM);

Частина предиката внутрішнього запиту посилається до таблиці STUDENTS. Це означає, що весь подзапрос буде виконуватися окремо для кожного рядка даної таблиці.

Зміна існуючих даних

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

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

UPDATE USP SET OCENKA = 5;

Звичайно, набагато частіше приходиться вказувати не всі, а тільки визначені рядки таблиці для зміни єдиного значення, і з цією метою разом з UPDATE можна використовувати предикати. Наприклад, змінити оцінки на 5 по предметі з кодом 2003, можна виконавши таку команду:

UPDATE USP SET OCENKA = 5 WHERE PNUM = 2003;

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

UPDATE TEACHERS SET TFAM = 'Федченко', TNAME = 'Світлана',

TOTCH = 'Геннадіївна' , TDATE =01/09/1999 WHERE TNUM = 4001;

Ця команда передасть новому викладачу Федченко всі поточні навчальні предмети з таблиці PREDMET - у нашому прикладі це буде фізика. Однак майте на увазі, що модифікувати відразу багато таблиць в одній команді UPDATE не можна, а отже, не можна і використовувати назва (префікс) таблиці з ім'ям полючи для цієї команди. Т.е., наприклад, SET TEACHERS. TFAM = 'Федченко' викликає помилку.

У пропозиції SET команди UPDATE можна використовувати вираження, розташовуючи їх у списку для того полючи, яких необхідно змінити (нагадаємо, що в пропозиції VALUES команди INSERT вираження використовувати не можна). Наприклад, для того, щоб збільшити стипендію в 2 рази, можна використовувати наступну конструкцію:

UPDATE STUDENTS SET STIP = STIP*2;

При цьому щораз, коли команда посилається до зазначеного значення полючи в пропозиції SET, дія виробляється, зрозуміло, над ще не модифікованими даними поточної запису.

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

UPDATE STUDENTS SET STIP = STIP*2 WHERE STIP = 25.50;

Команда UPDATE може працювати з NULL значеннями. Так що, якщо необхідно змінити всі оцінки студентів по навчальному предметі з кодом 2003 на NULL, можна скористатися наступною командою:

UPDATE USP SET OCENKA - NULL WHERE PNUM = 2003;

Потужним засобом модифікації даних є використання подзапросов у команді модифікації UPDATE. Важливий принцип, якому треба дотримувати при роботі з командами модифікації і підзапитами, полягає в тому, що не можна в пропозиції FROM будь-якого подзапроса модифікувати таблицю, до якої посилається основна команда.

Зверніть увагу на наступний важливий момент - у команді модифікації UPDATE (до речі, так само, як і в команді INSERT) може виникнути проблематична ситуація, зв'язана з можливими дублікатами рядків, одержуваними в результаті вкладеного запиту. У цьому випадку, якщо в таблиці, що модифікується, є обмеження, що змушують її значення бути унікальними, команда чи модифікації вставки зазнає невдачі. Тому рекомендується яким-небудь образом з'ясувати те, що ці значення могли минулого вже бути використані в таблиці, перш ніж намагатися чи вставити модифікувати запис. Це можна реалізувати за допомогою додавання вкладеного подзапроса, що використовує в предикаті оператори EXISTS, IN, < > чи аналогічні.

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

Наприклад, що випливає запит збільшує розмір стипендії в 2 рази студентам, у яких маються оцінки, принаймні, по двох навчальних предметах:

UPDATE STUDENTS SET STIP=STIP*2

WHERE 2<=(SELECT COUNT (SNUM) FROM USP

WHERE STUDENTS.SNUM=USP.SNUM);

Тут внутрішній запит підраховує кількість записів у таблиці успішності для кожного студента, і, якщо воно 2 і більше, предикат основної функції стає щирим, а розмір стипендії модифікується.

Розглянемо ще один, досить складний, приклад зі співвіднесеним подзапросом. Тут будемо модифікувати розмір стипендії для студентів, що мають мінімальний бал у той інший день:

UPDATE STUDENTS SET STIP=STRIP-1WHERE SNUM IN

(SELECT SNUM FROM USP FIRST WHERE OCENKA =(SELECT MIN (OCENKA)

FROM USP SECOND WHERE FIRST.UDATE =SECOND. UDATE));

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

SELECT AVG (OCENKA) FROM USP;

а потім результат цього запиту використовувати для модифікації

UPDATE USP SET OCENKA = OCENKA - 1 WHERE OCENKA < 4.2

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

Об'єднання таблиць

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

Використання команди для об'єднання таблиці із собою аналогічно тому прийому, що використовується для об'єднання декількох таблиць. Коли поєднується таблиця із собою, Всі повторювані імена стовпця заповнюються префіксами імені таблиці. Щоб посилатися до цих стовпців усередині запиту, необхідно мати два різних імена для цієї таблиці. Це можна зробити за допомогою визначення тимчасових імен, називаних псевдонімами, що визначаються в пропозиції FROM запиту. Синтаксис у цьому випадку наступний: після імені таблиці залишають пробіл, а потім повинний випливати псевдонім для неї.

Наприклад, для пошуку студентів, що мають однаковий розмір стипендії, можна скористатися наступним запитом:

SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP

FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = SECOND.STIP;

Результат такого запиту буде наступний:

У даному прикладі SQL поводиться так, ніби він з'єднував дві різні таблиці, називані FIRST і SECOND, тобто псевдоніми дозволяють однієї і тій же таблиці бути обробленої незалежно. Зверніть увагу на те, що псевдоніми можуть використовуватися в пропозиції SELECT до їхнього оголошення в пропозиції FROM, однак SQL буде спочатку допускати будь-як псевдоніми і може відхилити команду, якщо вони не будуть визначені далі в запиті. Крім того, необхідно пам'ятати, псевдонім існує тільки тоді, коли команда виконується, а після завершення запиту псевдоніми, використовувані в ньому, більше не мають ніякого значення.

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

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

SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIPFROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP=SECOND.STIP AND FIRST.SFAM < SECOND.SFAM;

Результат цього запиту буде такою:

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

Таким чином, можна використовувати цю особливість SQL для перевірки визначених видів помилок. Наприклад, якщо вважати, що навчальний предмет може вести тільки один викладач, те всякий раз у таблиці PREDMET необхідна перевірка на цю умову. При цьому щораз, коли код предмета з'являється в таблиці PREDMET, він повинний збігатися з відповідним номером викладача. Наступна команда буде визначати будь-як непогодженості в цій області:

SECOND.PNUM, SECOND.TNUM FROM PREDMET FIRST, PREDMET SECOND WHERE FIRST.PNUM = SECOND.PNUM AND FIRST.TNUM <> SECOND.TNUM;

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

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

Більш того, допускається використовувати будь-як число псевдонімів для однієї таблиці в запиті, хоча використання більш двох в одній пропозиції SELECT часто буде надмірністю. Наприклад, для призначення стипендії на наступний семестр необхідно переглянути Всі варіанти комбінацій студентів з різними розмірами стипендії: 25.50, 17.00 і 0.00 у.е. Тоді такий запит буде виглядати в такий спосіб:

SELECT FIRST.SFAM, SECOND.SFAM, THIRD.SFAM FROM STUDENTS FIRST, STUDENTS SECOND,STUDENTS THIRD WHERE FIRST.STIP = 25.50 AND SECOND.STIP = 17.00 AND THIRD.STIP = 0.00;

Висновок для цього запиту випливає нижче:

Індекси

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

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

Індекси - це корисний інструмент, що широко застосовується у всіх сучасних СУБД. Коли створюється індекс у поле, БД запам'ятовує відповідний порядок Всіх значень цього полючи в області пам'яті. Про переваги індексів може говорити наступне: припустимо, що таблиця STUDENTS має кілька тисяч записів, і необхідно знайти студента з конкретним номером студентського квитка. Т.к. запису в таблиці не упорядковані, те СУБД буде змушена переглядати всю таблицю, рядок за рядком, перевіряючи щораз значення полючи SNUM на рівність шуканому значенню. При наявності індексу в поле SNUM, система могла б знайти шуканий номер прямо в цьому упорядкованому індексі, і дати інформацію про те, як знайти правильний рядок таблиці.

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

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

Синтаксис команди для створення індексу наступний:

CREATE INDEX <INDEX NAME> ON <TABLE NAME>

(<COLUMN NAME> [,<COLUMN NAME>]...);

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

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

CREATE INDEX SFAMIDX ON STUDENTS (SFAM);

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

Для створення унікальних (не утримуючих повторюваних значень) індексів використовують ключове слово UNIQUE у команді CREATE INDEX. Фактично такий індекс буде первинним ключем таблиці. Наприклад, для таблиці STUDENTS поле SNUM підходить у якості первинного ключа, і він стане першим кандидатом для унікального індексу. Створити його можна командою:

CREATE UNIQUE INDEX SNUMIDX ON STUDENTS (SNUM);

Команда не буде виконана, якщо в поле SNUM маються неунікальні значення. Тому рекомендується створювати індекси відразу після того, як створена таблиця і до введення в неї яких-небудь значень. Цікава і така особливість унікального індексу: якщо в ньому використовується більш одного полючи (тобто він є комбінацією значень), те, узагалі говорячи, кожне з цих полів може і не бути унікальним.

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

Однак, для видалення індексу, необхідно знати його ім'я. З обліком цього команда для видалення має наступний синтаксис:

DROP INDEX <INDEX NAME>;

Наприклад, для видалення створеного індексу на прізвище студента, можна скористатися наступною командою:

DROP INDEX SFAMIDX;

Видалення індексу ні в якому разі не впливає на дані, що містяться в полях.

Порядок роботи:

1. Створити базу даних відповідно до варіанту завдання (кількість таблиць не менше 3).

Варіанти завдання (тип бази даних) згідно номеру бригади:

I. Бібліотека

II. Автосалон

III. Музичний магазин

IV. Чемпіонат країни по футболу

V. Комп’ютерні магазини міста

VI. Склад оргтехніки.

2. Перевірити створену базу даних на працездатність.

3. Провести основні операції з базою даних

1. добавлення запису;

2. перегляд даних;

3. видалення запису;

4. сортування даних.

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

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

1.

Лабораторна робота № 7

Тема: Імітаційне моделювання системи

• ⇐ Назад
• 1
• 2
• 3
• 4
• 5
• 6
• 789
• Далее ⇒