MySQL Views
Просмотры
Представление - это объект данных, который не содержит никаких данных. Содержимое представления является результатом базовой таблицы. Они работают так же, как базовая таблица, но не содержат собственных данных. Разница между представлением и таблицей заключается в том, что представления - это определения, построенные поверх других таблиц (или представлений). Если данные изменяются в базовой таблице, то же самое изменение отражается в представлении. Представление может быть построено поверх одной или нескольких таблиц.
Версия: MySQL 5.6
Содержание:
- Почему Просмотров?
- Как создать MySQL View?
- Ограничения на определение View
- Инструменты для создания MySQL Views
- Изменить представление MySQL
- DROP представление MySQL
- MySQL CREATE VIEW с ГДЕ
- MySQL CREATE VIEW с помощью AND и OR
- MySQL CREATE VIEW с GROUP BY
- MySQL CREATE VIEW с ORDER BY
- MySQL CREATE VIEW с МЕЖДУ и IN
- MySQL CREATE VIEW с LIKE
- MySQL CREATE VIEW с использованием подзапросов
- MySQL CREATE VIEW с помощью JOIN
- MySQL CREATE VIEW с помощью UNION
Почему просмотров?
- Представления могут быть эффективными копиями базовых таблиц.
- Представления могут иметь имена столбцов и выражения.
- Вы можете использовать любые пункты в представлениях.
- Представления могут быть использованы в INSERT / UPDATE / DELETE.
- Представления могут содержать выражения в списке выбора.
- Представления могут быть представлениями представлений.
Для просмотра MySQL требуется версия 5.0 или выше
Чтобы заставить работать представления, вам нужно обновить MySQL до версии 5.0 (или выше). Вы можете проверить свою версию MySQL следующим образом:
mysql> SELECT VERSION (); + ----------- + | ВЕРСИЯ () | + ----------- + | 5.6.12 | + ----------- + 1 ряд в наборе (0,00 сек)
Проверьте привилегии текущего пользователя:
Оператору CREATE VIEW требуется привилегия CREATE VIEW для представления и некоторая привилегия для каждого столбца, выбранного оператором SELECT. Следующая команда показывает права пользователя.
mysql> ПОКАЗАТЬ ПРИВИЛЕГИИ;
+ ----------------- + ---------------------------- + - -------------------------------------------------- --- +
| Привилегия | Контекст | Комментарий |
+ ----------------- + ---------------------------- + - -------------------------------------------------- --- +
| Альтер | Столы | Чтобы изменить таблицу |
| Изменить рутину | Функции, процедуры | Чтобы изменить или удалить сохраненные функции / процедуры |
| Создать | Базы данных, таблицы, индексы | Для создания новых баз данных и таблиц |
| Создать рутину | Базы данных | Использовать CREATE FUNCTION / PROCEDURE |
| Создать временный | Базы данных | Использовать CREATE TEMPORARY TABLE |
| столы | | | | Создать вид | Столы | Для создания новых взглядов |
| Создать пользователя | Администратор сервера | Для создания новых пользователей |
| Удалить | Столы | Чтобы удалить существующие строки |
| Drop | Базы данных, таблицы | Чтобы удалить базы данных, таблицы и представления |
| Событие | Администратор сервера | Создавать, изменять, удалять и выполнять события |
| Выполнить | Функции, процедуры | Для выполнения хранимых процедур |
| Файл | Доступ к файлам на сервере | Для чтения и записи файлов на сервере |
| Грант вариант | Базы данных, таблицы, | Чтобы предоставить другим пользователям те привилегии, которыми вы обладаете |
| | Функции, процедуры | | | Индекс | Столы | Чтобы создать или удалить индексы |
| Вставить | Столы | Вставить данные в таблицы |
| Блокировка столов | Базы данных | Чтобы использовать LOCK TABLES (вместе с привилегией SELECT) |
| Процесс | Администратор сервера | Для просмотра простого текста выполняющихся в данный момент запросов |
| Прокси | Администратор сервера | Сделать возможным использование прокси пользователя |
| Отзывы | Базы данных, таблицы | Иметь ссылки на таблицы |
| Перезагрузить | Администратор сервера | Чтобы перезагрузить или обновить таблицы, журналы и привилегии |
| Репликация | Администратор сервера | Спросить, где находится подчиненный или главный сервер |
| клиент | | | | Репликация | Администратор сервера | Чтобы прочитать двоичный журнал событий от мастера |
| раб | | | | Выбрать | Столы | Извлечь строки из таблицы |
| Показать базы данных | Администратор сервера | Чтобы просмотреть все базы данных с SHOW DATABASES |
| Показать вид | Столы | Чтобы увидеть взгляды с SHOW CREATE VIEW |
| Выключение | Администратор сервера | Чтобы выключить сервер |
| Супер | Администратор сервера | Чтобы использовать KILL thread, SET GLOBAL, CHANGE MASTER и т. Д. |
| Триггер | Столы | Использовать триггеры |
| Создать | Администратор сервера | Для создания / изменения / удаления табличных пространств |
| табличное пространство | | | | Обновление | Столы | Чтобы обновить существующие строки |
| Использование | Администратор сервера | Нет привилегий - разрешить подключение только |
+ ------------------------- + -------------------- + - -------------------------------------------------- --- +
31 ряд в наборе (0,00 сек)
Выберите базу данных:
Перед созданием представления мы должны выбрать базу данных. Следующая команда показывает список баз данных.
mysql> ПОКАЗАТЬ БАЗЫ ДАННЫХ;
+ -------------------- +
| База данных |
+ -------------------- +
| информационная схема |
| ч |
| mysql |
| performance_schema |
| сакила |
| тест |
| мир |
+ -------------------- +
7 рядов в наборе (0,06 сек)
Теперь выберите базу данных «hr» и перечислите таблицы:
mysql> USE hr;
База данных изменена
mysql> ПОКАЗАТЬ СТОЛЫ;
+ -------------- +
| Tables_in_hr |
+ -------------- +
| alluser |
| отделы |
| emp_details |
| job_history |
| рабочие места |
| места |
| регионы |
| пользователь |
| user_details |
+ -------------- +
9 рядов в наборе (0,00 сек)
Создать вид
Следующие утверждения создают представление. По умолчанию представление связано с базой данных по умолчанию (в настоящее время используется база данных). Чтобы связать представление с заданной базой данных, укажите имя как имя_базы_данных . view_name при его создании. Вот полный синтаксис:
Синтаксис:
СОЗДАЙТЕ
[ИЛИ ЗАМЕНИТЬ]
[АЛГОРИТМ = {НЕ УКАЗАНО | Мердж | TempTable}]
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
[БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[С [КАСКАДИРОВАННЫМ | МЕСТНЫЙ] ПРОВЕРЬТЕ ВАРИАНТ]
Объяснение:
СОЗДАЙТЕ [ИЛИ ЗАМЕНИТЬ] [АЛГОРИТМ = {НЕ УКАЗАНО | Мердж | TempTable}] [DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }] [БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}] VIEW view_name [(column_list)] AS select_statement [С [КАСКАДИРОВАННЫМ | МЕСТНЫЙ] ПРОВЕРЬТЕ ВАРИАНТ]
Оператор CREATE VIEW создает новый вид.
view_name: view_name - это имя представления. Представление всегда принадлежит базе данных. По умолчанию в текущей используемой базе данных создается новое представление. Имя представления также может использоваться с именем базы данных, как database_name.view_name, но это необязательно, если database_name является базой данных по умолчанию.
select_statement: select_statement является оператором SELECT и предоставляет определение представления. select_statement может выбирать данные из базовых таблиц или других представлений.
Пример:
mysql> USE hr; База данных изменена mysql> CREATE VIEW my_v1 AS SELECT * FROM user_details; Запрос в порядке, затронуто 0 строк (0,13 с)
column_list: часть column_list является необязательной. Он предоставляет список имен для столбцов представления сразу после имени представления, где имена должны быть уникальными. Количество имен в column_list должно быть таким же, как количество столбцов, извлеченных оператором SELECT. Если вы хотите присвоить столбцам вида другое имя, вы можете сделать это, добавив предложение [AS name] в список выбора.
Пример: просмотр без column_list
mysql> SELECT * FROM user; + ---------- + ----------- + -------- + | идентификатор пользователя | пароль | имя | + ---------- + ----------- + -------- + | scott123 | [электронная почта защищена] | Скотт | | ferp6734 | [электронная почта защищена] & 3 | Палаш | | diana094 | [электронная почта защищена] | Диана | + ---------- + ----------- + -------- + 3 ряда в наборе (0,04 сек) mysql> CREATE VIEW my_v2 AS SELECT * FROM user; Запрос в порядке, затронуто 0 строк (0,05 с) mysql> SELECT * FROM my_v2; + ---------- + ----------- + -------- + | идентификатор пользователя | пароль | имя | + ---------- + ----------- + -------- + | scott123 | [электронная почта защищена] | Скотт | | ferp6734 | [электронная почта защищена] & 3 | Палаш | | diana094 | [электронная почта защищена] | Диана | + ---------- + ----------- + -------- + 3 ряда в наборе (0,05 сек)
Теперь укажите имя столбца в представлении выше:
mysql> CREATE VIEW my_v3 AS SELECT ID пользователя AS User_ID, пароль AS Пароль, имя AS Имя от пользователя; Запрос в порядке, затронуто 0 строк (0,04 с) mysql> SELECT * FROM my_v3; + ---------- + ----------- + -------- + | Идентификатор пользователя | Пароль | Имя | + ---------- + ----------- + -------- + | scott123 | [электронная почта защищена] | Скотт | | ferp6734 | [электронная почта защищена] & 3 | Палаш | | diana094 | [электронная почта защищена] | Диана | + ---------- + ----------- + -------- + 3 ряда в наборе (0,04 сек)
СОЗДАЙТЕ
[ИЛИ ЗАМЕНИТЬ]
[АЛГОРИТМ = {НЕ УКАЗАНО | Мердж | TempTable}]
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
[БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[С [КАСКАДИРОВАННЫМ | МЕСТНЫЙ] ПРОВЕРЬТЕ ВАРИАНТ]
ИЛИ ЗАМЕНА: Если необязательное предложение ИЛИ ЗАМЕНИТЬ добавлено с оператором CREATE VIEW, оператор CREATE VIEW заменяет существующее представление и создает новое. Если представление не существует, CREATE VIEW - то же самое, что CREATE OR REPLACE VIEW.
СОЗДАЙТЕ
[ИЛИ ЗАМЕНИТЬ]
[АЛГОРИТМ = {НЕ УКАЗАНО | Мердж | TempTable}]
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
[БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[С [КАСКАДИРОВАННЫМ | МЕСТНЫЙ] ПРОВЕРЬТЕ ВАРИАНТ]
- ALGORITHM: предложение ALGORITHM является необязательным, оно влияет на то, как MySQL обрабатывает представление. ALGORITHM принимает три значения: MERGE, TEMPTABLE или UNDEFINED. Алгоритм по умолчанию НЕ УКАЗАН.
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
[БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}]: предложения DEFINER и SQL SECURITY задают контекст безопасности, который будет использоваться при проверке прав доступа во время вызова представления.
Если вы укажете предложение DEFINER, следующие правила определяют допустимые пользовательские значения DEFINER:
- Если у вас нет привилегии SUPER, единственным допустимым значением пользователя является ваша собственная учетная запись, и вы не можете установить определитель для какой-либо другой учетной записи.
- Если у вас есть привилегия SUPER, вы можете указать любое синтаксически допустимое имя учетной записи.
В хранимой подпрограмме, определенной с помощью характеристики SQL SECURITY DEFINER, CURRENT_USER возвращает значение подпрограммы DEFINER. Это также влияет на представление, определенное в такой подпрограмме, если определение представления содержит значение DEFINER CURRENT_USER.
[С [КАСКАДИРОВАННЫМ | LOCAL] CHECK OPTION]: предложение WITH CHECK OPTION может быть задано для обновляемого представления, чтобы предотвратить вставки или обновления строк, кроме тех, для которых предложение WHERE в select_statement имеет значение true. В предложении WITH CHECK OPTION для обновляемого представления ключевые слова LOCAL и CASCADED определяют область проверочного тестирования, когда представление определяется в терминах другого представления. Ключевое слово LOCAL ограничивает опцию CHECK только определенным представлением. CASCADED заставляет также проверять проверки для базовых представлений. Когда не указано ни одно ключевое слово, по умолчанию используется CASCADED.
Ограничения на определение View
- Оператор SELECT не может содержать подзапрос в предложении FROM.
- Оператор SELECT не может ссылаться на системные или пользовательские переменные.
- Внутри хранимой программы определение не может ссылаться на параметры программы или локальные переменные.
- Оператор SELECT не может ссылаться на подготовленные параметры оператора.
- Любая таблица или представление, указанные в определении, должны существовать.
- Определение не может ссылаться на таблицу TEMPORARY, и вы не можете создать представление TEMPORARY.
- Любые таблицы, названные в определении представления, должны существовать во время определения.
- Вы не можете связать триггер с представлением.
- Псевдонимы для имен столбцов в операторе SELECT проверяются по максимальной длине столбца в 64 символа (а не по максимальной длине псевдонима в 256 символов).
Инструменты для создания MySQL Views
Вы можете написать процедуру в инструменте командной строки MySQL или использовать MySQL Workbench, который является отличным интерфейсным инструментом (здесь мы использовали версию 5.3 CE).
Инструмент командной строки MySQL:
Выберите команду MySQL Client из меню «Пуск»:
При выборе командной строки MySQL появится следующий экран:
После успешного входа в систему вы можете получить доступ к командной строке MySQL:
Теперь вы можете создать и запустить собственное представление, см. Следующий пример:
MySQL верстак (5.3 CE):
Выберите MySQL верстак из меню Пуск:
После выбора MySQL верстак появится следующий экран входа в систему:
Теперь введите данные для входа:
После успешного входа в систему появится новый экран и из панели браузера объектов выберите базу данных:
После выбора базы данных щелкните правой кнопкой мыши на Views, появится новое всплывающее окно:
После выбора «Создать вид» появится следующий экран, где вы можете написать свой собственный вид.
После написания представления нажмите кнопку «Применить», и появится следующий экран:
Следующим экраном будет просмотр сценария и его применение в базе данных.
Теперь нажмите кнопку Готово и запустите просмотр:
Изменить вид
Оператор ALTER VIEW изменяет определение существующего представления. Синтаксис оператора похож на CREATE VIEW.
Синтаксис:
ALTER
[ИЛИ ЗАМЕНИТЬ]
[АЛГОРИТМ = {НЕ УКАЗАНО | Мердж | TempTable}]
[DEFINER = {пользователь | ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ }]
[БЕЗОПАСНОСТЬ SQL {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[С [КАСКАДИРОВАННЫМ | МЕСТНЫЙ] ПРОВЕРЬТЕ ВАРИАНТ] Для этого оператора требуются привилегии CREATE VIEW и DROP для представления, а также некоторые привилегии для каждого столбца, указанного в операторе SELECT.
Оставить представление
Оператор DROP VIEW используется для удаления одного или нескольких представлений. Чтобы удалить представление, вы должны иметь привилегию DROP для каждого представления. Вот синтаксис:
Синтаксис:
DROP VIEW [ЕСЛИ СУЩЕСТВУЕТ]
view_name [, view_name] ...
[ОГРАНИЧЕНИЕ | CASCADE]
Предложение IF EXISTS предотвращает возникновение ошибки для несуществующих представлений.
MySQL CREATE VIEW с ГДЕ
Команда CREATE VIEW может использоваться с предложением WHERE.
Пример:
Пример таблицы: автор
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE country='USA'
Приведенный выше оператор MySQL создаст представление 'view_author', в котором будут приниматься записи (для всех столбцов) таблицы авторов, если эти записи содержат значение 'USA' для столбца страны.
MySQL CREATE VIEW с помощью AND и OR
Команда CREATE VIEW может использоваться с операторами AND и OR.
Пример:
Пример таблицы: издатель
CREATE VIEW view_publisher
AS SELECT pub_name,pub_city,country
FROM publisher
WHERE (country='USA' AND pub_city='New York')
OR (country='India' AND pub_city='Mumbai');
Приведенный выше оператор MySQL создаст представление 'view_publisher', принимающее записи для столбцов pub_name, pub_city и country таблицы издателя, если (A) (i) значение столбца страны равно США, а (ii) значение pub_city равно New Йорк; или (B) (i) значение столбца страны - ИНДИЯ, а (ii) значение pub_city - Мумбаи.
MySQL CREATE VIEW с GROUP BY
Команда CREATE VIEW может использоваться с предложением GROUP BY.
Пример:
Пример таблицы: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang, count(*)
FROM book_mast
GROUP BY pub_lang
Приведенный выше оператор создаст представление 'view_bookmast', в котором будут собраны все записи, сгруппированные по pub_lang, из pub_lang и количество книг для каждого языка (pub_lang).
MySQL CREATE VIEW с ORDER BY
Команда CREATE VIEW может использоваться с предложением ORDER BY.
Пример:
Пример таблицы: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang,count(*)
FROM book_mast
GROUP BY pub_lang ORDER BY pub_lang;
Приведенный выше оператор MySQL создаст представление 'view_bookmast', в котором будут собраны все записи, сгруппированные по pub_lang и отсортированные по pub_lang, из pub_lang и количество книг для каждого языка (pub_lang) таблицы book_mast.
MySQL CREATE VIEW с МЕЖДУ и IN
Команда CREATE VIEW может использоваться с оператором BETWEEN и IN.
Пример:
Пример таблицы: book_mast
CREATE VIEW view_bookmast
AS SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G'
AND no_page IN(165,250,350,400,510);
Приведенный выше оператор создаст представление 'view_bookmast', в котором будут собраны все записи таблицы book_mast, если (A) имя книги (book_name) начинается с любого из символов от 'A' до 'G' и (B) количества страниц (no_page) - любой из следующих 165, 250, 350, 400, 510.
MySQL CREATE VIEW с LIKE
Команда CREATE VIEW может использоваться с оператором LIKE.
Пример:
Пример таблицы: автор
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE aut_name
NOT LIKE 'T%' AND aut_name NOT LIKE 'W%';
Приведенный выше оператор MySQL создаст представление 'view_author', в котором будут приниматься все записи таблицы автора, если (A) имя автора (aut_name) не начинается с 'T' и (B) имя автора (aut_name) не начинается с буквой W
MySQL CREATE VIEW с использованием подзапросов
Команда CREATE VIEW может использоваться с подзапросами.
Пример:
Пример таблицы: покупка
Пример таблицы: book_mast
CREATE VIEW view_purchase
AS SELECT invoice_no,book_name,cate_id
FROM purchase
WHERE cate_id= (SELECT cate_id FROM book_mast WHERE no_page=201);
Приведенный выше оператор MySQL создаст представление 'view_purchase', в котором будут храниться все записи столбцов invoice_no, book_name и cate_id таблицы покупок, если идентификатор категории (cate_id) удовлетворяет условию, определенному в подзапросе (за которым следует cate_id =).
Подзапрос извлекает только cate_ids из таблицы book_mast, которая содержит книги с 201 страницей.
MySQL CREATE VIEW с помощью JOIN
Команда CREATE VIEW может использоваться вместе с оператором JOIN.
Пример:
Пример таблицы: категория
Пример таблицы: покупка
CREATE VIEW view_purchase
AS SELECT a.cate_id,a.cate_descrip, b.invoice_no,
b.invoice_dt,b.book_name
FROM category a,purchase b
WHERE a.cate_id=b.cate_id;
Приведенный выше оператор MySQL создаст представление view_purchase вместе с оператором JOIN.
Здесь оператор JOIN извлекает cate_id, cate_descrip из таблицы категорий и invoice_no, invoice_dt и book_name из таблицы покупок, если cate_id таблицы категорий и покупки совпадают.
MySQL CREATE VIEW с помощью UNION
Команда CREATE VIEW может использоваться с UNION.
Пример:
Пример таблицы: book_mast
CREATE VIEW view_bookmast AS
SELECT *
FROM book_mast
WHERE pub_id='P001' UNION
SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G' UNION
SELECT *
FROM book_mast
WHERE no_page IN(165,250,350,400,510);
Вышеупомянутая инструкция MySQL создаст представление 'view_bookmast', содержащее столбцы, как в 'book_mast'.
Записи будут вставлены с объединением трех подзапросов.
Первый запрос вставляет эти строки в представление «view_bookmast» из таблицы «book_mast», чей pub_id равен «P001».
Второй запрос вставляет эти строки в представление «view_bookmast» из таблицы «book_mast», строки которой имеют столбец «book_name», начинающийся с любой буквы от «A» до «G».
Третий запрос вставляет эти строки в представление view_bookmast из таблицы book_mast, строки которой имеют любое из следующих значений 165 250 350 400 500 в no_page.
Предыдущая: Транзакция MySQL
Далее: MySQL Security
Новый контент: Composer: менеджер зависимостей для PHP , R программирования