Робота з SQL-сервером

1. SQL — наше все

Як ти вже, напевно, здогадуєшся, всі команди SQL-серверу можна давати за допомогою SQL-запитів. Абсолютно всі.

Ці команди офіційно розбиті на 4 групи:

• оператори визначення даних (Data Definition Language, DDL):

  • CREATE створює об'єкт бази даних (саму базу, таблицю, представлення, користувача тощо)
  • ALTER змінює об'єкт
  • DROP видаляє об'єкт

• оператори маніпуляції даними (Data Manipulation Language, DML):

  • SELECT обирає дані, які відповідають зазначеним умовам
  • INSERT додає нові дані
  • UPDATE змінює наявні дані
  • DELETE видаляє дані

• оператори визначення доступу до даних (Data Control Language, DCL):

  • GRANT надає користувачеві (групі) дозволи на певні операції з об'єктом
  • REVOKE відкликає раніше видані дозволи
  • DENY встановлює заборону, яка має пріоритет над дозволом

• оператори управління транзакціями (Transaction Control Language, TCL):

  • COMMIT застосовує транзакцію
  • ROLLBACK відкочує всі зміни, зроблені в контексті поточної транзакції
  • SAVEPOINT ділить транзакцію на дрібніші ділянки

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

Але ми тут готуємо насамперед Java-програмістів, тому вивчатимемо ті сценарії, з якими ти точно зіткнешся на роботі.

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

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

Давай ще раз пройдемося по списку речей, яких ми торкнулися у попередніх лекціях.

2. Створення схеми в базі даних

Щоб створити в СУБД нову схему, потрібно виконати команду:

CREATE SCHEMA ім'я;

Це найпростіший варіант. Також під час створення нової схеми можна вказати формат кодування даних та інші параметри.

Якщо ти хочеш видалити схему, але не впевнений, що вона існує, потрібно виконати команду:

DROP SCHEMA IF EXIST ім'я;

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

3. Вибір поточної схеми

Якщо у тебе в СУБД багато схем, легко може виникнути ситуація, коли у різних схемах є однакові таблиці. Щоб не було плутанини, можна зробити дві речі:

• Завжди вказувати ім'я схеми перед ім'ям таблиці
• Вказати стандартну схему

Давай напишемо запит, який вибере дані з таблиці user, яка знаходиться в схемі test. Він виглядатиме приблизно так:

SELECT * FROM test.user;

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

До речі, у мові Java ми часто робимо щось схоже: якщо в коді нам потрібно використовувати класи з однаковими іменами з різних пакетів, ми додаємо ім'я пакета перед ім'ям класу.

Другий спосіб — це встановлення стандартної схеми. Якщо в запиті вказано ім'я таблиці, але не вказано схему, то використовується схема за замовчуванням. Для цього потрібен оператор USE:

USE ім'я-схеми;

Давай перепишемо попередній запит за допомогою оператора USE:

     USE test;
SELECT * FROM user;

4. Створення View

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

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

Ти можеш створити VIEW з будь-якого SELECT запиту за допомогою команди виду:

             CREATE VIEW ім'я AS
SELECT-запит;

 CREATE VIEW public_employee AS
 SELECT id, name FROM employee

У наведеному прикладі наша таблиця (VIEW) public_employee міститиме лише ID співробітників та їхні імена, але не інформацію про їхню зарплату. Використовувати такі View можна там же, де й реальні таблиці.

Навіщо потрібні View? Вони мають низку переваг:

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

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

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

Лише читання. View можна вказати лише запитом виду SELECT, тому робота з View ніяк не може змінити дані в реальних таблицях. До речі, це ще один плюс на користь запитів кешування. Але про це поговоримо якось наступного разу.