Вибір груп за допомогою HAVING
Наступним виразом в операторі SELECT є вираз HAVING. GROUP BY з виразом HAVING подібний до SELECT з виразом WHERE. Наприклад:
select count(*), job
from employee
group by job
having count(*) = l;
Такий запит вибере всі наявні в компанії посади, на яких працює по одному службовцю. Результат виконання запиту буде приблизно наступним:
+--------------+------------------------------------+
| count(*) | job |
+--------------+------------------------------------+
| 1 | Адміністратор БД |
| 1 | Системний адміністратор |
+--------------+------------------------------------+
2 rows in set (0.05 sec)
Досвід показує, що починаючі користувачі SQL часто плутають вираз WHERE і HAVING. Вираз WHERE використовується в запиті для перевірки умов, що стосуються окремих стрічок, а умови HAVING застосовуються до груп.
Сортування результатів пошуку за допомогою ORDER BY
Наступним виразом в операторі SELECT є вираз ORDER BY. Він дає можливість відсортувати рядки результату по одному або декількох стовпцях. Сортувати при цьому можна як по зростанню (позначається ASC), так і по спаданню (позначається DESC). Наприклад:
select *
from employee
order by job asc, name desc;
У результаті будуть обрані усі рядки з таблиці employee і відсортовані за значенням job за абеткою. Якщо при цьому два або більше службовців будуть мати однакові посади, то відповідні рядки будуть відсортовані за іменем у зворотному порядку.
Упорядкування даних
Розглянемо питання про те, як можна змінити порядок виводу даних, витягнутих з таблиць MySQL, використовуючи вираз ORDER BY оператора SELECT.
|
|
|
Дані, що виймаються до цього, завжди виводилися в тому порядку, у якому вони були збережені в таблиці. У дійсності SQL дозволяє сортувати витягнуті дані за допомогою виразу ORDER BY. Ця пропозиція вимагає ім'я стовпця, на основі якого будуть сортуватися дані. Давайте подивимося, як можна вивести імена співробітників з упорядкованими за алфавітом прізвищ співробітників (у зростаючому порядку).
SELECT l_name, f_name
from employee_data
ORDER BY l_name;
А от так співробітників можна відсортувати за віком.
SELECT f_name, l_name, age
from employee_data
ORDER BY age;
Вираз ORDER BY може сортувати в зростаючому порядку (ASCENDING або ASC) або в спадному порядку (DESCENDING або DESC) залежно від зазначеного аргументу.
Щоб вивести список співробітників в спадному порядку, можна використовувати такий оператор.
SELECT f_name
from employee_data
ORDER by f_name DESC;
Примітка: Зростаючий порядок (ASC) використовується за замовчуванням.
Приклади завдань
1. Вивести список співробітників у порядку, обумовленому зарплатою, що вони одержують.
2. Виведіть список співробітників в спадному порядку їхнього стажу роботи в компанії.
3. Що робить наступний оператор?
SELECT emp_id, l_name, title, age
from employee_data
|
|
|
ORDER BY title DESC, age ASC;
4. Вивести список співробітників (прізвище та ім'я), які обіймають посаду "програміст" або "розроблювач Web" і відсортувати їхні прізвища за алфавітом.
Можливі вирішення завдань
mysql> SELECT f_name, l_name, salary
-> from employee_data
-> ORDER BY salary;
mysql> SELECT f_name, l_name, yos
-> from employee_data
-> ORDER by yos;
Оператор виводить список співробітників з ідентифікаційним номером, посадою, і віком, відсортований за посадою в спадному порядку й за віком у зростаючому порядку.
mysql> SELECT emp_id, l_name, title, age
-> from employee_data ORDER BY
-> title DESC, age ASC;
Примітка: Спочатку сортуються посади в спадному порядку. Потім для кожної посади сортується вік співробітників у зростаючому порядку.
mysql> SELECT l_name, f_name from employee_data
-> where title IN ('програміст',
-> 'розроблювач Web') ORDER BY l_name;
Підготовка до виконання роботи
1 Ознайомитись з інструкцією.
2 Опрацювати теоретичний матеріал.
3 Усно дати відповіді на контрольні запитання вхідного контролю.
4 Продумати методику виконання роботи.
5 Підготувати бланк звіту.
Питання вхідного контролю
1 Які ви знаєте засоби групування результатів?
2 Які ви знаєте засоби сортування результатів?
3 Які ви знаєте функції групування (агрегуючі функції)?
|
|
|
4 В чому полягає необхідність сортування результатів?
5 Яке призначення має вираз HAVING у SELECT запиті?
Порядок виконання роботи
1 Отримати допуск до виконання практичної роботи, давши відповіді на контрольні запитання вхідного контролю.
2 Увімкнути ПК, увійти в операційну систему під відповідним користувачем.
3 Запустити монітор mysql за допомогою ярлика на робочому столі.
4 Відновити базу даних використовуючи попередньо записаний текстовий файл.
5 Будуємо запити до бази даних з використанням засобів групування і сортування результатів.
6 Результати виконання інформаційних запитів записати у звіт по роботі і продемонструвати викладачеві.
7 Записати усі запити до бази даних в текстовий файл, а текстовий файл на носій інформації.
Питання вихідного контролю
1 Вкажіть формат запису виразу GROUP BY.
2 Вкажіть формат запису виразу HAVING.
3 Вкажіть формат запису виразу ORDER BY.
4 Які вимоги ставляться щодо параметрів виразу GROUP BY?
5 Чи можна використовувати HAVING незалежно від GROUP BY?
Лабораторна роботи № 13
Тема: Використання транзакцій з таблицями InnoDB.
Мета роботи: Вивчити поняття транзакцій. Вивчити режими автоматичної фіксації та блокування. Навчитися використовувати механізм застосування режимів ізольованості транзакцій таблиць InnoDB.
|
|
|
Теоретичні відомості
Використання транзакцій в MySQL.Щоб використовувати транзакції, необхідно використовувати тип таблиць, що гарантує безпеку транзакцій, тобто InnoDB або BDB. При цьому для одержання необхідного результату можна використовувати кілька варіантів синтаксису.
Оператор START TRANSACTION має синоніми BEGIN і BEGIN WORK. Можна віддати перевагу якійсь із зазначених форм, щоб код був сумісний з іншими використовуваними вами базами даних, але, оскільки START TRANSACTION пропонується стандартом SQL-99, рекомендується використовувати саме його.
Режим автоматичної фіксації.Зазвичай, MySQL працює в режимі автоматичної фіксації транзакцій (режим autocommit). Кожний виконуваний запит насправді ізолюється у вигляді транзакції. Ви можете інтерпретувати це, як автоматичне додавання команд START TRANSACTION і COMMTT до кожного із запитів. Пари наступних простих запитів
update account set balance = balance - 1000 where number = 2;
update account set balance = balance + 1000 where number = 1;
#виконується так, начебто вони записані у вигляді start transaction;
update account set balance = balance - 1000 where «number = 2;
commit;
start transaction;
update account set balance = balance + 1000 where number = 1;
commit;
Зверніть увагу на те, що якщо ви надрукуєте вручну
start transaction;
#нічого зафіксовано не буде доти, поки ви вручну не введете
commit;
'Автоматичну фіксацію можна скасувати за допомогою команди SET:
set autocommit=0;
А наступна команда поверне MySQL у режим автоматичної фіксації транзакцій:
SET AUTOCOMMIT=L;
Змінна autocommit є локальною для кожного сеансу, тому зміна зазначеного режиму буде впливати тільки на запити вашого сеансу до його завершення.
Якщо виключити режим autocommit, то для запуску транзакції не потрібно викликати START TRANSACTION. Дуже важливо при цьому не забувати час від часу викликати COMMIT, щоб фіксувати зміни, які ви вносите в базу даних.
Незалежно від того, включений або виключений режим autocommit, у деяких ситуаціях зміни будуть фіксуватися автоматично. Якщо ви використовуєте тип таблиць, що не забезпечує безпеку транзакцій (наприклад, MyISAM), всі ваші зміни будуть негайно зафіксовані, незалежно від установки autocommit. Ви можете групувати свої оператори за допомогою START TRANSACTION і COMMIT, але у випадку таблиць, що не забезпечують безпеку транзакцій, це не буде мати ніякого значення. Можна навіть викликати ROLLBACK, і це не породить помилки – тільки ніяк не вплине на зміни, які вже внесені у відповідну таблицю. Це може придатися при перевірці коду або завантаженню дамп-файлу на сервери з різними типами таблиць.
Для таблиць, що забезпечують безпеку транзакцій, деякі дії (крім безпосереднього уведення COMMIT) автоматично викликають запуск COMMIT. Наприклад, вимога блокування неявно викликає фіксацію всіх ще невиконаних запитів.
Використання блокування.Альтернативним способом одержання деяких можливостей транзакцій є ручне блокування й розблокування таблиць.
Якщо необхідно записати код для депозиту банківського рахунку у вигляді двох окремих транзакцій, це можна зробити так: lock tables account write;
SELECT BALANCE
FROM ACCOUNT
WHERE NUMBER = 2;
UPDATE ACCOUNT SET BALANCE = 1500
WHERE NUMBER = 2;
UNLOCK TABLES;
Виклик LOCK TABLES означає спробу блокувати всі таблиці, перераховані в даному операторі, щоб поточний потік міг працювати з ними, не побоюючись зовнішніх впливів. Виклик UNLOCK TABLES скасовує всі блокування, призначені даним потоком. Скасувати блокування просто. При цьому варто враховувати тільки те, що заблокувавши таблицю, ви повинні відкрити її якнайшвидше, щоб зменшити колізії для інших потоків. Блокування – більше складна проблема.
Необхідно запросити всі необхідні блокування відразу. У попередньому прикладі було потрібна одне блокування, але якщо потрібен доступ до множини таблиць або навіть до декількох псевдонімів однієї таблиці, доведеться додати їх у той же виклик. Наприклад:
LOCK TABLES ACCOUNT WRITE, ACCOUNT AS A READ,
OTHERTABLE LOW_PRIORITY WRITE;
Виклик LOCK TABLES знімає всі встановлені до цього блокування, так що спроби нагромадити потрібні блокування за допомогою декількох операторів не мають змісту - усі раніше визначені блокування будуть скасовані й ви одержите тільки ті, які будуть зазначені в заключному операторі.
Є два основних типи блокування: блокування для читання й блокування для запису. Якщо потрібен доступ до таблиці для запису в неї і ви не можете дозволити іншим потокам використовувати таблицю в той же час, блокування для запису заборонить будь-якому іншому потоку як читання таблиці, так і запис у неї, поки ви це блокування не знімете. Блокування для читання є менш строгим. Якщо необхідно тільки прочитати дані таблиці, немає нічого небезпечного в дозволі іншим потокам читати дані з таблиці одночасно. Блокування для читання забороняє іншим потокам тільки запис у таблицю, поки ваш потік здійснює блокування.
Блокування для запису може також позначатися як low_priority. У будь-якій системі, що використовує блокування, включаючи MySQL, є правила, що дозволяють вирішити, хто одержить блокування першим при конфлікті запитів. Як правило, MySQL надає пріоритет запитам блокування для запису перед запитами блокування для читання, щоб модифікація даних виконувалася якнайшвидше. Якщо таке поводження вас не влаштовує, можна встановити низький пріоритет блокування для запису, як це зроблено для таблиці othertable у попередньому прикладі. Тут, однак, є певна проблема. Щораз при запиті блокування вам, можливо, доведеться чекати, поки вона буде надана. Низько пріоритетне блокування буде представлене тільки тоді, коли не виявиться інших потоків, що запросили блокування для читання або для запису з тією ж таблицею. На дуже завантаженому сервері цього може не відбутися ніколи.
Швидше за все, ви не занадто часто будете вручну контролювати блокування так, як показано тут, але є певні ситуації, коли рекомендується саме ручний контроль. Якщо у вас є додаток, що вимагає дуже високої продуктивності, але рідко використовує транзакції, ви можете вибрати який-небудь швидкий тип таблиць, що не забезпечує безпеку транзакцій, і скористатися блокуванням для вирішення проблем із транзакціями.
Іншою типовою ситуацією, коли вам може знадобитися LOCK TABLES, є безпосередня робота з файлами даних MySQL. Наприклад, щоб бути впевненими, що файли на диску залишаються узгодженими і незміненими, поки виконується їхнє резервне копіювання, необхідно встановити для них блокування.
Найголовніше, що варто пам'ятати про блокування, - це те, що зняти блокування потрібно якнайшвидше, оскільки інші системи й користувачі будуть змушені чекати. Деякі завдання, що вимагають блокування під час їхнього виконання (наприклад, реіндексація або резервне копіювання великих файлів), можуть забирати дуже багато часу.
Модель транзакцій lnnoDB.Системи керування базами даних з підтримкою транзакцій служать, загалом, одним цілям, але розрізняються підходами до рішення відповідних завдань. Щоб ізолювати транзакції, InnoDB використовує дрібномасштабний механізм блокування на рівні рядків. Це значить, що різні транзакції можуть виконуватися для однієї й тої ж таблицыодновременно, якщо вони здійснюють тільки читання або використовують різні рядки для запису.
Незафіксовані зміни блокують для інших потоків тільки порушені рядки, а не всю таблицю. Це забезпечує високу продуктивність IrmoDB і ряд інших характеристик, які звичайно очікуються від сучасної системи керування реляційними базами даних (СУРБД).
Ізольованість транзакцій.Таблиці InnoDB пропонують чотири різних рівні ізольованості транзакцій. У порядку від найсильнішого до слабкого, рівні ізольованості можуть бути наступними:
упорядкування (serializable);
повторюване читання (repeatable read);
читання підтвердженого (read conrmitted);
читання непідтвердженого (read uncommitted).
Як і для багатьох інших опцій, тут теж доводиться вибирати між стійкістю й продуктивністю.
Впорядкування (serializable) – це ідеал з погляду чистоти й стійкості. З впорядкуванням читання й запис у базі даних повинні здаватися такими, що виконуються почергово, коли записувані зміни вносяться повністю до початку наступного читання. При цьому транзакції не завжди будуть виконуватися в неперемежованій послідовності – багато транзакцій не заважають одна інший, але у випадку колізії будуть заважати. Блокування й очікування разом з непродуктивними зусиллями на пророкування можливих колізій перетворюють упорядкування в найповільніший режим ізольованості. Якщо ви захочете використовувати цей режим, скористайтеся наступною командою:
set transaction isolation level serializable;
Для InnoDB рівнем ізольованості за замовчуванням є повторюване читання (repeatable read). У цьому режимі ізольованості кожна транзакція працює в ізольованій версії таблиці, де кожний рядок залишається в тому вигляді, у якому вона перебувала перед початком транзакції. При читанні будь-якого рядка гарантується повторюваність результату. Якщо викликати
SELECT * FROM ACCOUNT
WHERE NUMBER=L;
на початку транзакції й виконувати той же запит у рамках транзакції пізніше, ви одержите той самий результат. Проте існує небезпека так званого фантомного читання. Можливо, що інша транзакція, що була тільки що зафіксована, додала в таблицю нові рядки. Якщо виконати один і той же запит з деякою умовою двічі, наприклад
SELECT * FROM ACCOUNT
WHERE BALANCE>1000;
то цілком можливо, що в другий раз ви одержите нові рядки - фантомні.
На практиці фантомне читання в MySQL повинне спостерігатися винятково рідко. Для рішення цієї проблеми механізм InnoDB використовує алгоритм, називаний блокуванням наступного ключа, але стовпець, що використовується у відповідній умові, повинен бути індексований. Ви, імовірно, уже знаєте, що IrmoDB пропонує блокування на рівні рядків. Коли транзакція використовує рядок, вона блокує її, щоб транзакція могла ізолюватися від інших. Разом з таким блокуванням рядків блокування наступного ключа заблокує й порожнечі між рядками, знайдені в індексі. У результаті такого підходу до дозволу проблеми фантомного читання лише невеликому числу систем дійсно може знадобитися режим упорядкування для ізольованості.
Якщо встановити для сервера режим читання підтвердженого (read committed), транзакції вже не будуть занадто ізольованими. Якщо виконати запит і повторити його пізніше в рамках тої ж транзакції, у другий раз ви одержите інший результат, якщо за цей час інша транзакція змінить дані й буде зафіксована. Якщо вам буде потрібно встановити цей режим, відповідна команда повинна виглядати так:
set transaction isolation level read committed;
На найслабкішому рівні ізольованості, у режимі читання непідтвердженого (read uncommitted), очевидно вже не тільки те, що транзакції більше не ізольовані, не забезпечують цілісність і, таким чином, відповідність ACID, але й те, що транзакцій по суті взагалі мати неможливо. У цьому режимі транзакції можуть читати зміни, які вносять інші транзакції, до того, як ці зміни будуть підтверджені (тобто зафіксовані). Це називають "брудним " читанням. Ви можете допустити це тільки при винятково незвичайних умовах, наприклад, коли ви знаєте, що всі активні потоки будуть або тільки читати, або тільки записувати дані, але не те й інше одночасно. Щоб установити режим читання непідтвердженого, використовуйте наступну команду:
set transaction isolation level read uncommitted;
Транзакція – це зв'язаний набір запитів SQL, що інтерпретується як окрема атомарна одиниця. Вона повинна бути або повністю виконана, або відхилена.
Стандартним способом SQL представлення транзакцій є
start transaction;
# необхідні дії commit;
але існує безліч інших еквівалентних способів одержання того ж результату.
ACID розшифровується як Atomicity (атомарность), Consistency (цілісність), Isolation (ізольованість) і Durability (стійкість). Ви повинні розуміти значення цих термінів, інакше навколишні "розумники" відмовляться розмовляти з вами.
У порядку від найдужчого до слабкого, рівні ізольованості можуть бути наступними: упорядкування (serializable), повторюване читання (repeat-able read), читання підтвердженого (read committed) і читання непідтвердженого (read uncommitted). Для таблиць InnoDB за замовчуванням використовується режим повторюваного читання.
Дата добавления: 2018-05-13; просмотров: 407; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!