Рівень 18. Базові принципи роботи з SQL

Візьми аркуш паперу й випиши всі категорії, за якими можна структурувати дані про друзів Грега. Усього буде 9 категорій.

Категорії, які ми визначимо, використовуватимуться для впорядкування даних.

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

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

Щоразу, коли ви шукаєте щось в Інтернеті, звертаєтеся до довідки, користуєтеся TiVo, замовляєте квитки, отримуєте штраф за перевищення швидкості або купуєте щось у магазині, потрібна інформація запитується з бази даних.

База даних складається з таблиць.

Таблиця — це структура, у якій зберігаються дані, впорядковані за стовпцями та рядками. Пам'ятаєте категорії з попереднього прикладу? Кожна категорія відповідає
стовпцю таблиці.

Наприклад, стовпець може містити одне зі значень: Неодружений, Одружений, Розлучений.

Рядок таблиці містить усю інформацію про один об'єкт таблиці. У новій таблиці Грега рядок містить повний опис однієї людини. Наприклад, в одному рядку можуть зберігатися такі дані: Джон, Джексон, неодружений, письменник, jj@boards-r-us.com.

Інформація в базі даних поділяється на таблиці.

Нижче ви знайдете кілька карток і таблицю. Ваше завдання — уявити себе на місці частково заповненої таблиці. Звісно, вам захочеться заповнити порожні місця й досягти рівноваги та внутрішньої наповненості. Також дайте стовпцям таблиці змістовні назви. Коли ви

впораєтеся з вправою, перевірте, чи вдалося вам досягти духовної єдності з таблицею.

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

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

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

Є, але ці типи найважливіші. Конкретний набір підтримуваних типів даних також залежить від СКБД, тому за додатковою інформацією слід звертатися до документації. Також рекомендуємо книгу «SQL in a Nutshell» — це чудовий довідник, у якому описано основні відмінності між різними СКБД.

Парадоксально, але факт: нижче перелічено основні команди для керування таблицями — створення, видалення та читання таблиць. Але, як ви вже зрозуміли із заголовка, тестувальники зазвичай не користуються цими командами, це більше прерогатива програмістів. Завдання тестувальника зводиться до вміння витягти з таблиць потрібний перелік даних для тестів. Команди керування:

Для виведення опису структури таблиці використовується команда DESC.

Команда DROP TABLE знищує таблицю з усім вмістом. Будьте уважні, ніколи не використовуйте її просто так!

Для збереження даних у таблиці використовується команда INSERT, яка існує в кількох варіантах.

NULL — невизначене значення, яке не дорівнює нулю або порожньому рядку. Для стовпця, що містить null, виконується умова IS NULL, але при цьому він не дорівнює NULL.

Стовпці, значення яких не вказано в команді INSERT, за замовчуванням ініціалізуються значенням NULL.
Щоб заборонити зберігання null у стовпці, використовуйте ключові слова NOT NULL під час створення таблиці.

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

CREATE TABLE

Команда створює таблицю, але для її виконання необхідно знати ІМЕНА та ТИПИ ДАНИХ стовпців. Вони визначаються на основі аналізу інформації, яка зберігатиметься в таблиці.

DROP TABLE

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

CREATE DATABASE

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

USE DATABASE

Команда відкриває базу даних для створення таблиць.

NULL і NOT NULL

Під час створення бази даних слід знати, які стовпці не повинні набувати значення NULL — це спростить сортування та пошук даних. Умова NOT NULL задається для стовпців під час створення таблиці.

DEFAULT

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

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

CREATE TABLE doughnut_list

(

doughnut_name VARCHAR(10) NOT NULL,

doughnut_type VARCHAR(6) NOT NULL,

doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00

);

DEFAULT

1) Стовпець ЗАВЖДИ має містити значення. Для цього ми не лише оголошуємо його з ключовими словами NOT NULL, а й призначаємо значення за замовчуванням 1.

2) Це значення зберігається в стовпці doughnut_cost, якщо в команді INSERT не вказано інше значення.

DEC(3,2)

Значення може містити до 3 цифр: одну до коми і дві після неї

А ось як виглядатиме таблиця my_contacts, якщо оголосити всі стовпці з ключовими словами NOT NULL:

Під час роботи з базами даних операція вибірки зазвичай виконується частіше, ніж операція вставлення даних у базу. У цьому розділі ви познайомитеся з могутньою командою SELECT
і дізнаєтеся, як отримати доступ до важливої інформації, яку ви зберегли у своїх таблицях. Також ви навчитеся використовувати умови WHERE, AND та OR для вибіркового відбору даних і запобігання виведенню непотрібних даних.

Грег нарешті переніс усі дані зі своєї картотеки в таблицю my_contacts. Тепер йому хочеться відпочити. Він роздобув два квитки на концерт і хоче запросити одну зі своїх знайомих — дівчину Енн із Сан-Франциско. Щоб знайти її адресу електронної пошти, Грег переглядає вміст таблиці my_contacts командою SELECT з розділу 1.

SELECT * FROM my_contacts;

Шукаємо контакт

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

Наступна команда SELECT допоможе Грегу відшукати дані Енн набагато швидше, ніж за дотошного перегляду всієї величезної таблиці. У цій команді ми використовуємо умову WHERE, яка уточнює для СКБД критерій відбору записів. Умова звужує результати пошуку, а команда повертає лише ті записи, для яких ця умова виконується.

Знак = в умові WHERE означає, що кожне значення стовпця first_name перевіряється на рівність із текстом 'Енн'. Якщо два значення рівні, весь запис включається до результату вибірки. Якщо ні — запис пропускається.

У цьому вікні консолі показано результат запиту — підмножину записів, у яких стовпець first_name містить значення 'Енн'.

Зірочка (*) наказує СКБД повернути значення всіх стовпців таблиці.

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

Зірочка (*) наказує СКБД повернути значення всіх стовпців таблиці

Ви, напевно, подумали, що зараз будуть якісь нудні завдання, але нічого такого! Зараз ми з вами замутимо фруктові коктейлі в барі Galaxy QA Academy. Перегляньте таблицю всього меню, а потім Магістр дасть вам SQL-запит, і за його допомогою ви дізнаєтеся, який коктейль вам дістався!

Який напій видасть запит, сказати мені ти маєш.

Якщо ви вставляєте в таблицю дані (INSERT) або робите запит на пошук даних (SELECT) зі значенням VARCHAR, CHAR або BLOB, що містить внутрішній апостроф, необхідно повідомити СКБД, що цей апостроф не завершує текст,
а є його частиною і його потрібно включити до рядка. Для цього можна поставити
перед апострофом зворотну похилу риску.

Ви маєте повідомити СКБД, що апостроф не позначає початок або кінець рядка, а є частиною тексту

Екранування зворотною похилою рискою

Щоб розв'язати цю проблему (і водночас виправити команду INSERT), поставте перед апострофом у тексті зворотну похилу риску:

INSERT INTO my_contacts

VALUES

('Фаніон', 'Стів', 'steve@onionflavoredring.com', 'Ч', '1970-01-04', 'Панк', 'Гровер\'с Мілл, NJ', 'Неодружений', 'Бунтарство', 'Однодумці, гітаристи');

Коли ви ставите перед апострофом префікс \, що вказує, що апостроф є частиною тексту, це називається «екрануванням»

Екранування подвоєнням апострофа

Апостроф також можна «екранувати» іншим способом — поставивши перед ним додатковий апостроф:

INSERT INTO my_contacts
VALUES

('Фаніон', 'Стів', 'steve@onionflavoredring.com', 'Ч', '1970-01-04', 'Панк', 'Гровер''с Мілл, NJ', 'Неодружений', 'Бунтарство', 'Однодумці', 'гітаристи');

Апострофи також можна «екранувати» подвоєнням, тобто заміною одного апострофа двома.

Перепишіть наступну команду, використовуючи два різні способи екранування внутрішнього апострофа:

SELECT * FROM my_contacts
WHERE
location = 'Гровер'с Мілл, NJ';

Отже, ви вмієте писати команду SELECT для вибірки будь-яких типів даних — зокрема й таких, що містять апострофи.

Команда SELECT може включити у вибірку лише ті стовпці, які вам потрібні.

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

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

Погляньте лишень, яку гарну й лаконічну таблицю повертає наш новий запит. Це успіх!

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

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

Відбір стовпців корисний і зручний, але має й інші переваги.

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

Пам'ятаєте нашу таблицю easy_drinks? Наступна команда SELECT поверне коктейль «Поцілунок»:

SELECT drink_name FROM easy_drinks
WHERE
main = 'вишневий сік';

Пам'ятаєте нашу таблицю easy_drinks? Наступна команда SELECT поверне коктейль «Поцілунок»:

SELECT drink_name FROM easy_drinks
WHERE
main = 'вишневий сік';

Команда SELECT — найуживаніша для тестувальника. Давай якомога більше практикуватися — допишіть чотири команди SELECT, щоб вони також повертали «Поцілунок». А щоб закріпити, запишіть ще три команди SELECT, які повертають коктейль «Жабка».

Круто, ми впевнені, що тобі дуже сподобалося писати запити. Давай ще щось виберемо за допомогою SQL. Використовуючи таблицю my_contacts, напиши кілька запитів для Грега. Включіть у вибірку лише ті стовпці, які необхідні для отримання відповіді. Зверніть особливу увагу на апострофи.

Дві умови пошуку — тип «з глазур'ю» та оцінка «10» — можна об'єднати в один запит за допомогою ключового слова AND. Результати такого запиту задовольнятимуть обидві умови.

Результат запиту AND. Навіть якщо запит поверне кілька записів, ми знатимемо, що в усіх цих закладах є глазуровані пончики з оцінкою 10, тож піти можна в будь-який із них. Або в усі по черзі.

Проте використовувати два запити замість одного неефективно; до того ж ви ризикуєте пропустити напої, до складу яких входить 1.75 або 3 унції содової. Краще скористатися оператором порівняння «більше»:

Раніше в наших умовах WHERE використовувався лише оператор =. Ви щойно побачили приклад використання оператора >, який порівнює одне значення з іншим. Нижче наведено повний перелік операторів порівняння.

Завдання цього рівня до неподобства просте. Хай мене розберуть 1000 дроїдів, якщо ти не виконаєш його одним махом після всіх завдань, що ми перепаяли на цьому рівні! Тобі належить працювати з реальною БД типу MySQL. У практичному завданні тобі треба буде написати 10 запитів. Коли будеш готовий, надішли їх нам через форму.