Работа с представлениями в MySQL
Пересказ статьи Robert Sheldon. Working with MySQL Views
Подобно другим системам управления базами данных, MySQL позволяет вам создавать представления, которые дают возможность пользователям и приложениям получать данные без предоставления им непосредственного доступа к лежащим в основе таблицам. Вы можете думать о представлении как о предварительно определенном запросе, который MySQL выполняет при обращении к представлению. MySQL хранит определение представления как объект базы данных, подобно табличному объекту.
Представление обладает несколькими преимуществами. Оно абстрагирует схему лежащих в основе таблиц и ограничивает доступ только теми данными, которые возвращает представление. Приложения, обращающиеся к представлению, не могут увидеть структуру таблиц и другие данные, которые содержат таблицы. В этом случае представление играет роль виртуальной таблицы, добавляя еще один слой безопасности, который скрывает структуру физических таблиц.
Представление также помогает упростить запросы, поскольку оно предлагает менее сложную версию схемы. Например, разработчику приложений не потребуется создавать детализированные многотабличные соединения, вместо этого он может обратиться к представлению в базовом операторе SELECT. Кроме того, возможность представлять абстрактную схему позволяет модифицировать лежащие в основе представления определения таблиц без нарушения работы приложений.
Несмотря на преимущества, которыми обладает представление, имеется и ряд ограничений. Например, MySQL не позволяет вам создавать индекс на представлении, определять на представлении триггер или ссылаться на системную или определяемую пользователем переменную в запросе представления.
Кроме того, допускается удаление таблицы, на которую ссылается представление, без возникновения ошибки. Это происходит только тогда, когда пользователь или приложение попытается обратиться к представлению; MySQL подаст сигнал, который может серьезно повлиять на рабочую нагрузку. (Полное изложение ограничений для представлений и другую информацию о представлениях можно найти в документации MySQL.)
Представление является сохраненным запросом, который MySQL выполняет, когда выполняется обращение к представлению. Обычно запрос представляет оператор SELECT, который запрашивает данные из одной или более таблиц. Начиная с MySQL 8.0.19, запросом может служить оператор VALUES или TABLE, но в большинстве случаев используется оператор SELECT, поэтому этот вариант мы и будем рассматривать в данной статье.
Любые таблицы, к которым обращается оператор SELECT, должны существовать к моменту, когда вы создаете представление. Помимо этого, мало что требуется, чтобы добавить представление в базу данных, разве что убедиться, что у вас есть необходимые разрешения на создание представления и запрос к таблицам, лежащим в его основе (эту тему я буду обсуждать подробно далее в данной серии).
Для демонстрации примеров в этой статье я создал базу данных travel и добавил таблицы manufacturers и airplanes. Те же самые таблицы я использовал и обновлял в предыдущей статье в этой серии. Чтобы добавить базу данных и таблицы в ваш экземпляр MySQL, вы можете выполнить следующий код SQL:
Чтобы выполнить эти операторы, скопируйте код и вставьте его на вкладку запросов в Workbench. Затем вы можете выполнить операторы все сразу или по одному в представленном порядке. Вы должны создать таблицу manufacturers до создания таблицы airplanes, поскольку у таблицы airplanes имеет внешний ключ, который ссылается на таблицу manufacturers.
Когда вы создаете представление, хорошей идеей является протестировать оператор SELECT в представлении, а затем выполнить представление после создания запроса. Для этого вам потребуются некоторые тестовые данные. Два следующих оператора INSERT добавят небольшое количество данных в две таблицы, достаточное, чтобы начать создавать представление:
Я буду обсуждать оператор INSERT более подробно позже в этой серии, поэтому не буду тратить много времени на это сейчас. На этом этапе вам нужно знать, что первый оператор добавляет три строки в таблицу manufacturers, а второй - 10 строк в таблицу airplanes.
Вы можете выполнить оба оператора одновременно или по очереди. Вы должны их выполнять в указанном здесь порядке , чтобы не нарушить ограничение внешнего ключа, определенного в таблице airplanes. Поскольку внешний ключ создан на столбце manufacturer_id, значения в этом столбце должны уже иметься в таблице manufacturers. Опять таки, я собираюсь глубже обсудить это в последующих статьях.
Это все, что вам необходимо для подготовки среды MySQL, поэтому вы сможете выполнять последующие примеры из этой статьи. Как и для первых двух статей этой серии, я использовал редакцию MySQL Community на компьютере с Windows для построения примеров. Я создавал примеры в Workbench, который устанавливается вместе с редакцией Community.
Если вы знакомились с предыдущей статьей в этой серии, то знаете, что Workbench GUI предоставляет вкладку Table - удобное средство для построения и редактирования определения таблицы. Однако другая вкладка для создания представлений - вкладка View - не так полезна. Одно из главных достоинств вкладки Table, особенно для новичков, состоит в том, что на ней представлены различные опции, доступные для определения таблицы. Вкладка View не имеет такого преимущества. В основном она позволяет вам построить оператор CREATE VIEW, как вы могли бы просто это сделать на вкладке запросов.
В этой статье я использовал вкладку запросов для всех примеров. Однако полезно знать, как попасть на вкладку View в случае, если вы захотите использовать ее. Чтобы открыть вкладку, выберите базу данных в навигаторе (Navigator), а затем щелкните на кнопке создания представления (create view) на панели инструментов Workbench. (Эта кнопка находится справа от иконки создания таблицы и имеет всплывающую подсказку Create a new view in the active schema in the connected server.) При щелчке на этой кнопке Workbench откроет вкладку View, как показано на рис.1.
Рис.1 Добавление представления с помощью Workbench GUI
На этой вкладке вы можете сделать только одно - написать оператор CREATE VIEW. Эта вкладка предоставляет несколько заглушек для начала работы с оператором, только и всего. Вы сами пишете фактический оператор (или копируете его из другого источника). Отсюда вы должны пройти еще пару экранов, подобных тем, которые сопровождали вкладку Table, но без преимущества автоматической генерации оператора.
Использовать вкладку View ли нет - вам решать. В любом случае вы должны еще придумать оператор CREATE VIEW. Имея это в виду, рассмотрим следующий пример, который создает представление на базе двух таблиц в базе данных travel.
В основном оператор CREATE VIEW требует, чтобы вы указали имя представления, затем ключевое слово AS с последующим оператором SELECT. В нашем случае представление называется airbus_info.
Сам оператор SELECT относительно прост. Он делает внутреннее соединение таблиц airplanes и manufacturers по столбцу manufacturer_id. Предложение WHERE ограничивает результаты только теми строками, для которых значением производителя является airbus, а предложение ORDER BY сортирует результаты по столбцу plane.
При создании представления полезно выполнить отдельно оператор SELECT, чтобы убедиться в том, что возвращаются требуемые результаты. Я придерживался относительно простых операторов, поскольку более подробное обсуждение операторов SELECT я планирую позже в этой серии. Однако используемый оператор выполняет необходимое - он демонстрирует как создавать представление. Оператор SELECT в представлении может быть как простым, так и сложным, таким, какой вам нужен.
Когда вы выполняете оператор CREATE VIEW, MySQL добавляет определение представления в активную базу данных. Вы можете проверить, что представление было создано, в навигаторе. Вам может потребоваться обновить навигатор, чтобы увидеть список. Ваше представление должно появиться в узле Views, как показано на рис.2.
Рис.2 Просмотр нового представления в навигаторе
Отсюда вы можете открыть определение представления на вкладке View. Если навести курсор на имя представления в навигаторе, вы увидите несколько небольших иконок для доступа к дополнительным функциям. Одна из их выглядит как гаечный ключ. Если щелкнуть по ней, Workbench откроет вкладку View и отобразит код представления, как показано на рис.3.
Рис.3 Доступ к определению представления в Workbench GUI
MySQL добавил несколько опций для определения представления, которые не включаются в исходный оператор CREATE VIEW. Все эти опции имеют значения по умолчанию. Мы скоро обсудим их.
Вы можете отредактировать оператор CREATE VIEW непосредственно на вкладке View. После внесения необходимых изменений щелкните Apply, проверьте код, снова щелкните Apply и затем Finish. Теперь закройте вкладку View. Мы не будем использовать этот метод в настоящей статье, но знайте, что есть такой вариант, если вы решите этим воспользоваться.
Вы можете также открыть оператор CREATE VIEW на вкладке запросов. Выполните щелчок правой кнопкой в навигаторе, укажите Send to SQL Editor (послать в редактор SQL), а затем щелкните Create Statement (оператор создания). Оператор вытягивается в одну строку, что довольно неудобно. Однако вы можете исправить это, щелкнув кнопку переформатирования на панели инструментов вкладки. (Эта кнопка выглядит как метелка и имеет всплывающую подсказку Beautify/reformat the SQL script.)
Подобно другим реляционным системам баз данных, MySQL придерживается во многом стандарта SQL, поддерживаемого американским национальным институтом стандартизации (ANSI). Один из этих стандартов включает создание базы данных INFORMATION_SCHEMA, которая обеспечивает доступ только на чтение к подробной информации о системе баз данных и её базах данных.
Информация, доступная посредством базы данных INFORMATION_SCHEMA, представлена как набор представлений, одно из который называется Views. Через него вы можете получить доступ к информации о представлениях, которые вы создаете в базе данных MySQL. Например, следующий запрос возвращает подробную информацию о представлении, которое мы только что создали в базе данных travel:
Оператор использует символ звездочки (*), указывающий, что должны возвращаться все столбцы. Он также задает имя целевого представления в предложении FROM, включающее имя базы данных (information_schema) с последующей разделительной точкой и именем представления (view). Кроме того, оператор содержит предложение WHERE, которое отфильтровывает все представления за исключением тех, которые содержатся в базе данных travel.
Если вы выполните этот оператор, результаты должны включать строку для представления airbus_info, которое вы создали выше. Информация включает детали о том, как было определено представление. Смысл многих столбцов в результирующем наборе должен быть понятен, а остальные будут обсуждаться ниже. Но я бы хотел особо выделить два столбца, а именно: CHECK_OPTION и IS_UPDATEABLE.
Оба столбца относятся к обновляемым представлениям, о которых я расскажу позже в этой серии. Пока нужно иметь в виду, что MySQL поддерживает обновляемые представления, и что представление считается обновляемым, если оно удовлетворяет определенному набору критериев. MySQL автоматически определяет, является ли представление обновляемым, на основе этих критериев. Если оно является обновляемым, MySQL устанавливает столбец IS_UPDATEABLE в значение YES (истина). В противном случае - в NO (ложь).
Следует отметить еще один столбец - VIEW_DEFINITION, который содержит запрос в представлении. Если вы хотите увидеть только этот запрос и ничего более, вы можете ограничить оператор SELECT только этими результатами:
Теперь предложение SELECT задает только возврат столбца view_definition, а не всех столбцов. Однако даже при ограничении результатов они все равно читаются с трудом. К счастью, Workbench предоставляет полезную функцию для просмотра значения столбца в полном объеме. Для этого выполните щелчок правой кнопкой непосредственно на значении в результатах и щелкните Open Value in Viewer (открыть значение в просмотрщике). MySQL откроет отдельное окно, в котором отобразит значение, как показано на рис.4.
Рис.4 Проверка оператора SELECT представления в просмотрщике
Здесь вы можете просматрвать как бинарные, так и текстовые значения. Кроме того, вы можете сохранить оператор в текстовом файле, щелкнув на кнопке Save. Вы не можете сохранить бинарное значение в файл.
Как отмечалось ранее, я подробно рассмотрю оператор SELECT позже в этой серии, но я хочу дать вам краткий обзор, как можно написать запрос к представлению, после его создания. По большей части он работает так же, как и запрос к таблице. Следующий пример показывает самый базовый оператор SELECT c представлением airbus_info, указанным в предложении FROM:
При выполнении этого оператора SELECT MySQL выполняет запрос в определении представления и возвращает результаты, подобные тем, как если бы запрос выполнялся непосредственно. На рис.5 показаны результаты, которые должен вернуть ваш оператор SELECT.
Рис.5 Просмотр результатов после вызова представления airbus_info
Вы можете также уточнить ваш оператор SELECT, как вы могли бы это сделать в представлении. Например, следующий оператор SELECT включает предложение WHERE, которое ограничивает результаты теми, для которых значение parking_area больше 20000:
Этот оператор включает также предложение ORDER BY, которое сортирует результаты по столбцу parking_area в убывающем порядке. Когда вы включаете предложение ORDER BY при вызове представления, оно отменяет предложение ORDER BY в самом определении представления (если оно там имеется). На рис.6 показаны данные, которые теперь должен вернуть ваш оператор SELECT:
Рис.6 Уточнение результатов запроса при обращении к представлению
Как можно увидеть, результаты теперь включают только две строки, и эти строки отсортированы по значениям столбца parking_area, при этом сначала идет наибольшее значение. Предложение ORDER BY в определении представления сортирует данные по значениям plane.
MySQL предоставляет несколько методов модификации определения представления. Одним и них является использование оператора CREATE VIEW, который включает предложение OR REPLACE, что продемонстрировано следующим примером:
Добавив предложение OR REPLACE после ключевого слова CREATE, вы можете уточнить существующее определение представления, а затем выполнить оператор без генерации ошибки, что было бы в случае отсутствия этого предложения. Это отличный инструмент при активной разработке базы данных и постоянном изменении схемы.
Помимо предложения OR REPLACE определение представления включает несколько других элементов, которых не было в исходном операторе CREATE VIEW, который вы создали. Первым является предложение ALGORITHM, которое сообщает MySQL использовать алгоритм MERGE при обработке представления. Алгоритм выполняет слияние вызывающего оператора и определения представления таким образом, чтобы сделать обработку представления более эффективной. Этот алгоритм также требуется для того, чтобы представление было обновляемым. Более подробно об алгоритмах смотрите в документации MySQL.
Две других новых опции - DEFINER и SQL SECURITY управляют тем, какой аккаунт пользователя имеет привилегии на использование при обработке представления. Опция DEFINER указывает, какой аккаунт назначается создателем приложения. В нашем случае опция устанавливается в CURRENT_USER, поэтому "определителем" является пользователь, который фактически выполнил оператор CREATE VIEW.
Опция SQL SECURITY может принимать аргументом либо DEFINER, либо INVOKER. Если задан DEFINER, то представление будет обрабатываться под аккаунтом указанного DEFINER. Если указан INVOKER, то представление будет обрабатываться под аккаунтом пользователя, который обращается к представлению.
После выполнения предыдущего оператора CREATE VIEW, вы можете проверить, что опции были обновлены, выполнив запрос к представлению INFORMATION_SCHEMA.VIEWS:
На рис.7 показаны результаты, которые я получил при выполнении оператора SELECT в моей системе. Поскольку я выполнял оператор CREATE VIEW как пользователь root, столбец DEFINER показывает мое имя пользователя и localhost в качестве экземпляра сервера. Результаты также показывают значение INVOKER в столбце SECURITY_TYPE, который соответствует опции SQL SECURITY.
Рис.7 Просмотр результатов из базы данных INFORMATION_SCHEMA
Вы могли заметить, что INFORMATION_SCHEMA.VIEWS не возвращает подробной информации об указанном алгоритме. Однако в этом случае столбец IS_UPDATABLE установлен в значение YES, которое указывает, что представление является обновляемым, и работает только с алгоритмом MERGE. Тогда, если столбец установлен в NO, вы не можете быть уверены, какой алгоритм был использован, поскольку другие факторы могут повлиять на то, почему представление не является обновляемым.
Другим подходом, который вы можете использовать для обновления определения представления, является выполнене оператора ALTER VIEW. Синтаксис оператора ALTER VIEW почти тот же, что синтаксис CREATE VIEW. Например, следующий оператор ALTER VIEW подобен предыдущему оператору CREATE VIEW за исключением того, что он еще указывает имена столбцов, используемые для возвращаемого результирующего набора:
В данном случае оператор включает список имен столбцов после имени представления. Имена столбцов заключаются в круглые скобки и разделяются запятыми. Это имена столбов, используемые для результатов представления вместо использования имен лежащих в определении таблиц.
После выполнения оператора ALTER VIEW вы можете затем написать запрос к представлению, как вы делали это раньше, только теперь вы должны использовать указанные имена столбцов. Например, следующий оператор SELECT ограничвает и упорядочивает результаты, как вы видели в предыдущем примере:
Обратите внимание, что оператор SELECT использует новые имена столбцов, которые также отображаются в результатах, как показано на Рис.8.
Рис.8 Запрос к обновленному представлению airbus_info
Задавая имена возвращаемых столбцов, вы можете абстрагировать базовую схему еще больше, что обеспечивает еще один уровень безопасности, облегчая изменение схемы базовых таблиц. Например, если вы измените имя столбца базовой таблицы, вам может потребоваться обновить запрос в представлении, но не имена возвращаемых столбцов, избегая каких-либо нарушений в работе вызывающих приложений.
Удаление представления - относительно простой процесс. Вы можете использовать Workbench GUI или выполнить оператор DROP VIEW. Чтобы использовать GUI, выполните щелчок правой кнопкой на представлении в навигаторе и щелкните Drop View. Когда появится диалоговое окошко, щелкните Drop Now.
Для использования оператора DROP VIEW вам нужно указать только имя представления и, опционально, предложение IF EXISTS, как показано в следующем примере:
Вы можете убедиться, что представление было удалено, выполнив следующий оператор SELECT к INFORMATION_SCHEMA.VIEWS:
Результаты больше не будут содержать строку для представления airbus_info.
Представления обеспечивают эффективный инструмент для предоставления данных приложениям способом, который абстрагируется от базовой структуры таблиц. В то же время они могут помочь упростить операторы SQL, которые разработчикам приложений потребуется написать для получения данных из базы данных MySQL. Они также добавляют дополнительный слой защиты, ограничвая доступ к базовым таблицам.
Я возвращусь к теме представлений позже в этой серии, когда буду обсуждать выборку и модификацию данных. А пока информация, представленная в этой статье, должна дать вам хороший старт для работы с представлениям. По мере того, как вы будете становиться более искусными в написании операторов SELECT, вы сможете создавать более эффективные представления, которые будут возвращать разнообразную информацию.
Представление также помогает упростить запросы, поскольку оно предлагает менее сложную версию схемы. Например, разработчику приложений не потребуется создавать детализированные многотабличные соединения, вместо этого он может обратиться к представлению в базовом операторе SELECT. Кроме того, возможность представлять абстрактную схему позволяет модифицировать лежащие в основе представления определения таблиц без нарушения работы приложений.
Несмотря на преимущества, которыми обладает представление, имеется и ряд ограничений. Например, MySQL не позволяет вам создавать индекс на представлении, определять на представлении триггер или ссылаться на системную или определяемую пользователем переменную в запросе представления.
Кроме того, допускается удаление таблицы, на которую ссылается представление, без возникновения ошибки. Это происходит только тогда, когда пользователь или приложение попытается обратиться к представлению; MySQL подаст сигнал, который может серьезно повлиять на рабочую нагрузку. (Полное изложение ограничений для представлений и другую информацию о представлениях можно найти в документации MySQL.)
Подготовка вашей среды MySQL
Представление является сохраненным запросом, который MySQL выполняет, когда выполняется обращение к представлению. Обычно запрос представляет оператор SELECT, который запрашивает данные из одной или более таблиц. Начиная с MySQL 8.0.19, запросом может служить оператор VALUES или TABLE, но в большинстве случаев используется оператор SELECT, поэтому этот вариант мы и будем рассматривать в данной статье.
Любые таблицы, к которым обращается оператор SELECT, должны существовать к моменту, когда вы создаете представление. Помимо этого, мало что требуется, чтобы добавить представление в базу данных, разве что убедиться, что у вас есть необходимые разрешения на создание представления и запрос к таблицам, лежащим в его основе (эту тему я буду обсуждать подробно далее в данной серии).
Для демонстрации примеров в этой статье я создал базу данных travel и добавил таблицы manufacturers и airplanes. Те же самые таблицы я использовал и обновлял в предыдущей статье в этой серии. Чтобы добавить базу данных и таблицы в ваш экземпляр MySQL, вы можете выполнить следующий код SQL:
DROP DATABASE IF EXISTS travel;
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=1001;
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) )
ENGINE=InnoDB AUTO_INCREMENT=101;Чтобы выполнить эти операторы, скопируйте код и вставьте его на вкладку запросов в Workbench. Затем вы можете выполнить операторы все сразу или по одному в представленном порядке. Вы должны создать таблицу manufacturers до создания таблицы airplanes, поскольку у таблицы airplanes имеет внешний ключ, который ссылается на таблицу manufacturers.
Когда вы создаете представление, хорошей идеей является протестировать оператор SELECT в представлении, а затем выполнить представление после создания запроса. Для этого вам потребуются некоторые тестовые данные. Два следующих оператора INSERT добавят небольшое количество данных в две таблицы, достаточное, чтобы начать создавать представление:
INSERT INTO manufacturers (manufacturer)
VALUES ('Airbus'), ('Beechcraft'), ('Piper');
INSERT INTO airplanes
(plane, manufacturer_id, engine_type, engine_count,
max_weight, wingspan, plane_length, icao_code)
VALUES
('A380-800', 1001, 'jet', 4, 1267658, 261.65, 238.62, 'A388'),
('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111.02, 'A319'),
('ACJ320neo (Corporate Jet version)', 1001, 'jet', 2, 174165, 117.45, 123.27, 'A320'),
('A300-200 (A300-C4-200, F4-200)', 1001, 'jet', 2, 363760, 147.08, 175.50, 'A30B'),
('Beech 390 Premier I, IA, II (Raytheon Premier I)', 1002, 'jet', 2, 12500, 44.50, 46.00, 'PRM1'),
('Beechjet 400 (from/same as MU-300-10 Diamond II)', 1002, 'jet', 2, 15780, 43.50, 48.42, 'BE40'),
('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57.67, 'B190'),
('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24.79, 'PA24'),
('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29.60, 'P46T'),
('J-3 Cub', 1003, 'piston', 1, 1220, 38.00, 22.42, 'J3');Я буду обсуждать оператор INSERT более подробно позже в этой серии, поэтому не буду тратить много времени на это сейчас. На этом этапе вам нужно знать, что первый оператор добавляет три строки в таблицу manufacturers, а второй - 10 строк в таблицу airplanes.
Вы можете выполнить оба оператора одновременно или по очереди. Вы должны их выполнять в указанном здесь порядке , чтобы не нарушить ограничение внешнего ключа, определенного в таблице airplanes. Поскольку внешний ключ создан на столбце manufacturer_id, значения в этом столбце должны уже иметься в таблице manufacturers. Опять таки, я собираюсь глубже обсудить это в последующих статьях.
Это все, что вам необходимо для подготовки среды MySQL, поэтому вы сможете выполнять последующие примеры из этой статьи. Как и для первых двух статей этой серии, я использовал редакцию MySQL Community на компьютере с Windows для построения примеров. Я создавал примеры в Workbench, который устанавливается вместе с редакцией Community.
Создание представления в MySQL
Если вы знакомились с предыдущей статьей в этой серии, то знаете, что Workbench GUI предоставляет вкладку Table - удобное средство для построения и редактирования определения таблицы. Однако другая вкладка для создания представлений - вкладка View - не так полезна. Одно из главных достоинств вкладки Table, особенно для новичков, состоит в том, что на ней представлены различные опции, доступные для определения таблицы. Вкладка View не имеет такого преимущества. В основном она позволяет вам построить оператор CREATE VIEW, как вы могли бы просто это сделать на вкладке запросов.
В этой статье я использовал вкладку запросов для всех примеров. Однако полезно знать, как попасть на вкладку View в случае, если вы захотите использовать ее. Чтобы открыть вкладку, выберите базу данных в навигаторе (Navigator), а затем щелкните на кнопке создания представления (create view) на панели инструментов Workbench. (Эта кнопка находится справа от иконки создания таблицы и имеет всплывающую подсказку Create a new view in the active schema in the connected server.) При щелчке на этой кнопке Workbench откроет вкладку View, как показано на рис.1.
Рис.1 Добавление представления с помощью Workbench GUI
На этой вкладке вы можете сделать только одно - написать оператор CREATE VIEW. Эта вкладка предоставляет несколько заглушек для начала работы с оператором, только и всего. Вы сами пишете фактический оператор (или копируете его из другого источника). Отсюда вы должны пройти еще пару экранов, подобных тем, которые сопровождали вкладку Table, но без преимущества автоматической генерации оператора.
Использовать вкладку View ли нет - вам решать. В любом случае вы должны еще придумать оператор CREATE VIEW. Имея это в виду, рассмотрим следующий пример, который создает представление на базе двух таблиц в базе данных travel.
CREATE VIEW airbus_info
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;В основном оператор CREATE VIEW требует, чтобы вы указали имя представления, затем ключевое слово AS с последующим оператором SELECT. В нашем случае представление называется airbus_info.
Сам оператор SELECT относительно прост. Он делает внутреннее соединение таблиц airplanes и manufacturers по столбцу manufacturer_id. Предложение WHERE ограничивает результаты только теми строками, для которых значением производителя является airbus, а предложение ORDER BY сортирует результаты по столбцу plane.
При создании представления полезно выполнить отдельно оператор SELECT, чтобы убедиться в том, что возвращаются требуемые результаты. Я придерживался относительно простых операторов, поскольку более подробное обсуждение операторов SELECT я планирую позже в этой серии. Однако используемый оператор выполняет необходимое - он демонстрирует как создавать представление. Оператор SELECT в представлении может быть как простым, так и сложным, таким, какой вам нужен.
Когда вы выполняете оператор CREATE VIEW, MySQL добавляет определение представления в активную базу данных. Вы можете проверить, что представление было создано, в навигаторе. Вам может потребоваться обновить навигатор, чтобы увидеть список. Ваше представление должно появиться в узле Views, как показано на рис.2.
Рис.2 Просмотр нового представления в навигаторе
Отсюда вы можете открыть определение представления на вкладке View. Если навести курсор на имя представления в навигаторе, вы увидите несколько небольших иконок для доступа к дополнительным функциям. Одна из их выглядит как гаечный ключ. Если щелкнуть по ней, Workbench откроет вкладку View и отобразит код представления, как показано на рис.3.
Рис.3 Доступ к определению представления в Workbench GUI
MySQL добавил несколько опций для определения представления, которые не включаются в исходный оператор CREATE VIEW. Все эти опции имеют значения по умолчанию. Мы скоро обсудим их.
Вы можете отредактировать оператор CREATE VIEW непосредственно на вкладке View. После внесения необходимых изменений щелкните Apply, проверьте код, снова щелкните Apply и затем Finish. Теперь закройте вкладку View. Мы не будем использовать этот метод в настоящей статье, но знайте, что есть такой вариант, если вы решите этим воспользоваться.
Вы можете также открыть оператор CREATE VIEW на вкладке запросов. Выполните щелчок правой кнопкой в навигаторе, укажите Send to SQL Editor (послать в редактор SQL), а затем щелкните Create Statement (оператор создания). Оператор вытягивается в одну строку, что довольно неудобно. Однако вы можете исправить это, щелкнув кнопку переформатирования на панели инструментов вкладки. (Эта кнопка выглядит как метелка и имеет всплывающую подсказку Beautify/reformat the SQL script.)
Доступ к информации о представлении в помощью базы данных INFORMATION_SCHEMA
Подобно другим реляционным системам баз данных, MySQL придерживается во многом стандарта SQL, поддерживаемого американским национальным институтом стандартизации (ANSI). Один из этих стандартов включает создание базы данных INFORMATION_SCHEMA, которая обеспечивает доступ только на чтение к подробной информации о системе баз данных и её базах данных.
Информация, доступная посредством базы данных INFORMATION_SCHEMA, представлена как набор представлений, одно из который называется Views. Через него вы можете получить доступ к информации о представлениях, которые вы создаете в базе данных MySQL. Например, следующий запрос возвращает подробную информацию о представлении, которое мы только что создали в базе данных travel:
SELECT * FROM information_schema.views
WHERE table_schema = 'travel';
Оператор использует символ звездочки (*), указывающий, что должны возвращаться все столбцы. Он также задает имя целевого представления в предложении FROM, включающее имя базы данных (information_schema) с последующей разделительной точкой и именем представления (view). Кроме того, оператор содержит предложение WHERE, которое отфильтровывает все представления за исключением тех, которые содержатся в базе данных travel.
Если вы выполните этот оператор, результаты должны включать строку для представления airbus_info, которое вы создали выше. Информация включает детали о том, как было определено представление. Смысл многих столбцов в результирующем наборе должен быть понятен, а остальные будут обсуждаться ниже. Но я бы хотел особо выделить два столбца, а именно: CHECK_OPTION и IS_UPDATEABLE.
Оба столбца относятся к обновляемым представлениям, о которых я расскажу позже в этой серии. Пока нужно иметь в виду, что MySQL поддерживает обновляемые представления, и что представление считается обновляемым, если оно удовлетворяет определенному набору критериев. MySQL автоматически определяет, является ли представление обновляемым, на основе этих критериев. Если оно является обновляемым, MySQL устанавливает столбец IS_UPDATEABLE в значение YES (истина). В противном случае - в NO (ложь).
Следует отметить еще один столбец - VIEW_DEFINITION, который содержит запрос в представлении. Если вы хотите увидеть только этот запрос и ничего более, вы можете ограничить оператор SELECT только этими результатами:
SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'travel'
AND table_name = 'airbus_info';Теперь предложение SELECT задает только возврат столбца view_definition, а не всех столбцов. Однако даже при ограничении результатов они все равно читаются с трудом. К счастью, Workbench предоставляет полезную функцию для просмотра значения столбца в полном объеме. Для этого выполните щелчок правой кнопкой непосредственно на значении в результатах и щелкните Open Value in Viewer (открыть значение в просмотрщике). MySQL откроет отдельное окно, в котором отобразит значение, как показано на рис.4.
Рис.4 Проверка оператора SELECT представления в просмотрщике
Здесь вы можете просматрвать как бинарные, так и текстовые значения. Кроме того, вы можете сохранить оператор в текстовом файле, щелкнув на кнопке Save. Вы не можете сохранить бинарное значение в файл.
Запрос к представлению в MySQL
Как отмечалось ранее, я подробно рассмотрю оператор SELECT позже в этой серии, но я хочу дать вам краткий обзор, как можно написать запрос к представлению, после его создания. По большей части он работает так же, как и запрос к таблице. Следующий пример показывает самый базовый оператор SELECT c представлением airbus_info, указанным в предложении FROM:
SELECT * FROM airbus_info;При выполнении этого оператора SELECT MySQL выполняет запрос в определении представления и возвращает результаты, подобные тем, как если бы запрос выполнялся непосредственно. На рис.5 показаны результаты, которые должен вернуть ваш оператор SELECT.
Рис.5 Просмотр результатов после вызова представления airbus_info
Вы можете также уточнить ваш оператор SELECT, как вы могли бы это сделать в представлении. Например, следующий оператор SELECT включает предложение WHERE, которое ограничивает результаты теми, для которых значение parking_area больше 20000:
SELECT * FROM airbus_info
WHERE parking_area > 20000
ORDER BY parking_area DESC;Этот оператор включает также предложение ORDER BY, которое сортирует результаты по столбцу parking_area в убывающем порядке. Когда вы включаете предложение ORDER BY при вызове представления, оно отменяет предложение ORDER BY в самом определении представления (если оно там имеется). На рис.6 показаны данные, которые теперь должен вернуть ваш оператор SELECT:
Рис.6 Уточнение результатов запроса при обращении к представлению
Как можно увидеть, результаты теперь включают только две строки, и эти строки отсортированы по значениям столбца parking_area, при этом сначала идет наибольшее значение. Предложение ORDER BY в определении представления сортирует данные по значениям plane.
Обновление определения представления в MySQL
MySQL предоставляет несколько методов модификации определения представления. Одним и них является использование оператора CREATE VIEW, который включает предложение OR REPLACE, что продемонстрировано следующим примером:
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = CURRENT_USER
SQL SECURITY INVOKER
VIEW airbus_info
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;Добавив предложение OR REPLACE после ключевого слова CREATE, вы можете уточнить существующее определение представления, а затем выполнить оператор без генерации ошибки, что было бы в случае отсутствия этого предложения. Это отличный инструмент при активной разработке базы данных и постоянном изменении схемы.
Помимо предложения OR REPLACE определение представления включает несколько других элементов, которых не было в исходном операторе CREATE VIEW, который вы создали. Первым является предложение ALGORITHM, которое сообщает MySQL использовать алгоритм MERGE при обработке представления. Алгоритм выполняет слияние вызывающего оператора и определения представления таким образом, чтобы сделать обработку представления более эффективной. Этот алгоритм также требуется для того, чтобы представление было обновляемым. Более подробно об алгоритмах смотрите в документации MySQL.
Две других новых опции - DEFINER и SQL SECURITY управляют тем, какой аккаунт пользователя имеет привилегии на использование при обработке представления. Опция DEFINER указывает, какой аккаунт назначается создателем приложения. В нашем случае опция устанавливается в CURRENT_USER, поэтому "определителем" является пользователь, который фактически выполнил оператор CREATE VIEW.
Опция SQL SECURITY может принимать аргументом либо DEFINER, либо INVOKER. Если задан DEFINER, то представление будет обрабатываться под аккаунтом указанного DEFINER. Если указан INVOKER, то представление будет обрабатываться под аккаунтом пользователя, который обращается к представлению.
После выполнения предыдущего оператора CREATE VIEW, вы можете проверить, что опции были обновлены, выполнив запрос к представлению INFORMATION_SCHEMA.VIEWS:
SELECT table_name AS view_name,
is_updatable, definer, security_type
FROM information_schema.views
WHERE table_schema = 'travel'
AND table_name = 'airbus_info';На рис.7 показаны результаты, которые я получил при выполнении оператора SELECT в моей системе. Поскольку я выполнял оператор CREATE VIEW как пользователь root, столбец DEFINER показывает мое имя пользователя и localhost в качестве экземпляра сервера. Результаты также показывают значение INVOKER в столбце SECURITY_TYPE, который соответствует опции SQL SECURITY.
Рис.7 Просмотр результатов из базы данных INFORMATION_SCHEMA
Вы могли заметить, что INFORMATION_SCHEMA.VIEWS не возвращает подробной информации об указанном алгоритме. Однако в этом случае столбец IS_UPDATABLE установлен в значение YES, которое указывает, что представление является обновляемым, и работает только с алгоритмом MERGE. Тогда, если столбец установлен в NO, вы не можете быть уверены, какой алгоритм был использован, поскольку другие факторы могут повлиять на то, почему представление не является обновляемым.
Другим подходом, который вы можете использовать для обновления определения представления, является выполнене оператора ALTER VIEW. Синтаксис оператора ALTER VIEW почти тот же, что синтаксис CREATE VIEW. Например, следующий оператор ALTER VIEW подобен предыдущему оператору CREATE VIEW за исключением того, что он еще указывает имена столбцов, используемые для возвращаемого результирующего набора:
ALTER
ALGORITHM = MERGE
DEFINER = CURRENT_USER
SQL SECURITY INVOKER
VIEW airbus_info
(plane, engine, count, wingspan, length, area)
AS
SELECT a.plane, a.engine_type, a.engine_count,
a.wingspan, a.plane_length, a.parking_area
FROM airplanes a INNER JOIN manufacturers m
ON a.manufacturer_id = m.manufacturer_id
WHERE m.manufacturer = 'airbus'
ORDER BY a.plane;В данном случае оператор включает список имен столбцов после имени представления. Имена столбцов заключаются в круглые скобки и разделяются запятыми. Это имена столбов, используемые для результатов представления вместо использования имен лежащих в определении таблиц.
После выполнения оператора ALTER VIEW вы можете затем написать запрос к представлению, как вы делали это раньше, только теперь вы должны использовать указанные имена столбцов. Например, следующий оператор SELECT ограничвает и упорядочивает результаты, как вы видели в предыдущем примере:
SELECT plane, wingspan, length, area
FROM airbus_info
WHERE area > 20000
ORDER BY area DESC;Обратите внимание, что оператор SELECT использует новые имена столбцов, которые также отображаются в результатах, как показано на Рис.8.
Рис.8 Запрос к обновленному представлению airbus_info
Задавая имена возвращаемых столбцов, вы можете абстрагировать базовую схему еще больше, что обеспечивает еще один уровень безопасности, облегчая изменение схемы базовых таблиц. Например, если вы измените имя столбца базовой таблицы, вам может потребоваться обновить запрос в представлении, но не имена возвращаемых столбцов, избегая каких-либо нарушений в работе вызывающих приложений.
Удаление представления в MySQL
Удаление представления - относительно простой процесс. Вы можете использовать Workbench GUI или выполнить оператор DROP VIEW. Чтобы использовать GUI, выполните щелчок правой кнопкой на представлении в навигаторе и щелкните Drop View. Когда появится диалоговое окошко, щелкните Drop Now.
Для использования оператора DROP VIEW вам нужно указать только имя представления и, опционально, предложение IF EXISTS, как показано в следующем примере:
DROP VIEW IF EXISTS airbus_info;Вы можете убедиться, что представление было удалено, выполнив следующий оператор SELECT к INFORMATION_SCHEMA.VIEWS:
SELECT * FROM information_schema.views
WHERE table_schema = 'travel';Результаты больше не будут содержать строку для представления airbus_info.
Работа с представлениями в базу данных MySQL
Представления обеспечивают эффективный инструмент для предоставления данных приложениям способом, который абстрагируется от базовой структуры таблиц. В то же время они могут помочь упростить операторы SQL, которые разработчикам приложений потребуется написать для получения данных из базы данных MySQL. Они также добавляют дополнительный слой защиты, ограничвая доступ к базовым таблицам.
Я возвращусь к теме представлений позже в этой серии, когда буду обсуждать выборку и модификацию данных. А пока информация, представленная в этой статье, должна дать вам хороший старт для работы с представлениям. По мере того, как вы будете становиться более искусными в написании операторов SELECT, вы сможете создавать более эффективные представления, которые будут возвращать разнообразную информацию.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой