В. М. Дубовой Укладач д т. н., проф
| Вид материала | Документы |
- Р. А. Хальфин 08. 12. 2006 г. N 6530-рх методические рекомендации, 1062.91kb.
- Программа студенческой олимпиады мгмсу по стоматологии 30. 10., 33.94kb.
- М. А. Рыбалко (отв редактор), проф, 973.68kb.
- Реферат циклу підручників «Україна в світовій політиці», 148.74kb.
- Колективу авторів у складі: проф. Гаращенко Ф. Г., проф. Закусило О. К., проф. Зайченко, 259.94kb.
- Программа по курсу "Уголовное право" / Сост проф. Б. С. Волков, проф. И. Д. Козочкин,, 638.15kb.
- Квалификационные тесты по дерматовенерологии Москва, 2267.11kb.
- Інформаційні технології в журналістиці: вітчизняний І світовий досвід Київ 2002, 8272.38kb.
- И научные учреждения второе переработанное и дополненное издание, 8298.18kb.
- Программа учебного курса «Международное право» для студентов, 747.97kb.
Сортування результатів запиту
Більшість БД, що працюють з SQL, надають спеціальні кошти, що дозволяють удосконалювати висновок запитів.
SQL дозволяє поміщати вираження і константи серед обраних полів. Ці вираження можуть чи доповнювати заміщати полючи в пропозиціях SELECT, при цьому вони можуть містити в собі одне чи більш обраних полів. Наприклад, якщо необхідно переглянути проіндексовану стипендію, збільшивши її в два рази, то можна скористатися запитом:
SELECT SFAM, SIMA, SOTCH, STIP*2 FROM STUDENTS;
Висновок цього запиту буде такою:
| SFAM | SIMA | SOTCH | |
| Поляків | Анатолій | Олексійович | 51.00 |
| Старова | Любов | Михайлівна | 34.00 |
| Гриценко | Володимир | Миколайович | 0.00 |
| Котенко | Анатолій | Миколайович | 0.00 |
| Нагорний | Євгеній | Васильович | 51.00 |
Останній стовпець без найменування, тому що це - стовпець висновку, тобто це - стовпці даних, створені запитом способом, іншим, чим простий витяг їх з таблиці. Такі стовпці створюються щораз, коли використовуються функції, чи константи вираження в пропозиції SELECT запиту. Т.к. ім'я стовпця - один з атрибутів таблиці стовпці, що з'являються не з таблиць, не мають ніяких.
Досить часто виникає необхідність у розміщенні тексту у висновку запиту. Наприклад, для підвищення зручності роботи з результатами попереднього запиту, можна вставити скорочена назва одиниці виміру проіндексованої стипендії - умовних одиниць, що виконується наступним запитом:
SELECT SFAM, SIMA, SOTCH, 'y.e.', STIP*2 FROM. STUDENTS;
Висновок цього запиту буде наступний:
| SFAM | SIMA | SOTCH | | |
| Поляків | Анатолій | Олексійович | у.е. | 51.00 |
| Старова | Любов | Михайлівна | у.е. | 34.00 |
| Гриценко | Володимир | Миколайович | у.е. | 0.00 |
| Котенко | Анатолій | Миколайович | у.е. | 0.00 |
| Нагорний | Євгеній | Васильович | У.е. | 51.00 |
Коментар буде надрукований у кожнім рядку висновку, а не просто один раз для всієї таблиці. Припустимо, що необхідно звіт про кількість студентів, що одержують ту чи іншу стипендію, тоді можна запропонувати наступний запит:
SELECT COUNT (DISTINCT SNUM) , 'студ. одержують стипендію ', STIP, ' у.е.'
FROM STUDENTS GROUP BY STIP;
В результаті буде отримано:
| | STIP | |
| 2 студ. одержують стипендію | 0.00 | у.е. |
| 1 студ. одержують стипендію | 17.00 | у.е |
| 2 студ. одержують стипендію | 25.00 | у.е. |
Некоректність висновку тексту для стипендії 17.00 не можна уникнути, не створивши більш складної конструкції для висновку, чим запропонована. Іноді корисніше вивести один коментар для усього висновку в цілому чи робити свій власний коментар для кожного рядка, однак це забезпечують різні програми, що використовують SQL і мають засоби генератора звітів.
Для упорядкування висновку полів таблиць SQL використовує команду ORDER BY, дозволяючи сортувати висновок запиту відповідно до значень у тій чи іншій кількості обраних стовпців. Якщо вказується кілька полів, то стовпці висновку упорядковуються один усередині іншого, при цьому можна визначати зростання (ASC) чи убування (DESC) для кожного стовпця. За замовчуванням установлене зростання.
Як приклад використовуємо запит, що виводить таблицю з інформацією про студентів за абеткою прізвищ:
SELECT * FROM STUDENTS ORDER BY SFAM ASC;
Висновок цього запиту приведений нижче:
| SNUM | SFAM | SIMA | SOTCH | |
| 3414 | Гриценко | Володимир | Миколайович | 0.00 |
| 3415 | Котенко | Анатолій | Миколайович | 0.00 |
| 3416 | Нагорний | Євгеній | Васильович | 25.50 |
| 3412 | Поляків | Анатолій | Олексійович | 25.50 |
| 3413 | Старова | Любов | Михайлівна | 17.00 |
Приклад для упорядочивания інформації з декількох стовпців. Упорядкуємо по зменшенню розмір стипендії, а для студентів, що мають однаковий її розмір - за абеткою їхніх прізвищ. Для цього скористаємося запитом:
SELECT * FROM STUDENTS ORDER BY STIP DESC, SFAM ASC;
Результати запиту наступні:
| SNUM | SFAM | SIMA | SOTCH | |
| 3414 | Нагорний | Євгеній | Васильович | 25.50 |
| 3415 | Поляків | Анатолій | Олексійович | 25.50 |
| 3416 | Старова | Любов | Михайлівна | 17.00 |
| 3412 | Гриценко | Володимир | Миколайович | 0.00 |
| 3413 | Котенко | Анатолій | Миколайович | 0.00 |
Основна мета ключового слова ORDER BY - дати можливість використовувати цю команду зі стовпцями висновку так само, як і зі стовпцями таблиці - адже іноді потрібно зробити упорядочивание висновку по стовпцях, виробленим агрегатною функцією, чи константами вираженнями в пропозиції SELECT запиту.
Внесення змін у базу даних
При роботі з SQL винятково важливо не тільки уміти вибирати дані, але і користатися засобами, що керують значеннями в таблиці. Значення можуть бути поміщені і вилучені з полів трьома командами мови DML (Мова Маніпулювання Даними), а саме:
- INSERT - уставити;
- UPDATE - модифікувати;
- DELETE - видалити.
Додавання інформації в базу даних
Всі записи в SQL уводяться з використанням команди модифікації INSERT. У найпростішій формі ця команда ім'я наступний синтаксис:
INSERT INTO
| UNUM | OCENKA | UDATE | SNUM | PNUM |
| 1001 | 5 | 10/06/1999 | 3412 | 2001 |
| 1005 | 5 | 12/06/1999 | 3416 | 2004 |
Отже, буде зробленеі наступне: Всі значення, дані запитом (інформація про студентів, що має тільки особисті оцінки), містяться в таблицю, названу 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. Вона застосовна як до всіх рядків таблиці, якщо не використовується предикат, що визначає записи, що модифікуються, так і до конкретних рядків при наявності предиката, що, у свою чергу, може мати досить складну структуру.