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

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-программистов, поэтому будем изучать те сценарии, с которыми ты точно столкнешься на работе.

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

Более того, иногда твой код будет записывать в базу не все данные или записывать их не так, так что тебе придется частенько лазить в нее ручками и смотреть, а что же там все-таки хранится по факту.

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

Создание схемы в базе данных

Чтобы создать в СУБД новую схему, нужно выполнить команду:

CREATE SCHEMA имя;

Это самый простой вариант. Так же при создании новой схемы можно указать формат кодировки данных и другие параметры.

Если ты хочешь удалить схему, но не уверен, что она существует, то нужно выполнить команду:

DROP SCHEMA IF EXIST имя;

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

Выбор текущей схемы

Если у вас в СУБД много схем, то легко может возникнуть ситуация, когда в разных схемах есть одинаковые таблицы. Чтобы не возникло путаницы, можно сделать две вещи:

• Всегда указывать имя схемы перед именем таблицы
• Указать схему по умолчанию

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

SELECT * FROM test.user;

Без этого просто не обойтись, если нужно объединить (JOIN) несколько таблиц из разных схем в одном запросе.

Кстати, в языке Java мы часто делаем что-то похожее: если в коде нам нужно использовать классы с одинаковыми именами из разных пакетов, мы добавляем имя пакета перед именем класса.

Второй способ – это указание схемы по умолчанию. Если в запросе указано имя таблицы, но не указана схема, то используется схема по умолчанию. Для этого используется оператор USE:

USE имя-схемы;

Давай перепишем предыдущий запрос с использованием оператора USE:

USE test;
SELECT * FROM user;

Создание 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 никак не может поменять данные в реальных таблицах. Кстати, это еще один плюс в пользу кеширования запросов. Но об этом как-нибудь в следующий раз.