Sqlоперации выборки из представлений

Содержание

ВЫБОРКА ДАННЫХ ИЗ ПРЕДСТАВЛЕНИЙ

В предыдущих разделах кратко описывалась процедура преобразования операций выборки из представлений в эквивалентные операции выборки из одной или нескольких базовых переменных отношения. В данном разделе приводится более формальное описание этого преобразования.

Прежде всего, следует отметить (как указано в конце раздела 6.4 главы 6), что любое заданное реляционное выражение можно рассматривать как функцию на множестве отношений. Иными словами, текущие значения различных переменных отношения, упоминаемых в выражении, представляют собой фактические параметры данного вызова этой функции, а результатом ее вычисления является другая переменная отношения. Пусть D — это база данных (которая будет представлена в данном случае как множество переменных отношения), а V— это представление, определенное на множестве D, т.е. представление, определение которого является функцией х на множестве D, как показано

А теперь предположим, что RO — операция выборки из представления V. Тогда очевидно, что RO также является функцией на множестве отношений, а результат выборки будет иметь следующий вид.

RO ( V ) = RO ( X ( D ) )

Таким образом, результат операции выборки по определению совпадает с результатом вычисления функции X на множестве D, т.е. с результатом материализации копии отношения, являющегося текущим значением представления V, с последующим применением операции RO к этой материализованной копии. Но на практике обычно эффективнее вместо этой операции использовать описанную выше процедуру подстановки (см. раздел 10.1).

Эта процедура равносильна формированию функции с (…), являющейся композицией RO ( х ( …) ) функций х и RO (именно в этом порядке), и вычислению результата применения функции с непосредственно к множеству D. Но как бы то ни было, все-таки удобнее, по крайней мере, концептуально, дать определение семантики операции выборки из представлений в терминах материализации, а не подстановки. Другими словами, подстановка допустима лишь постольку, поскольку она гарантирует получение того же результата, который мог быть получен при использовании материализации (и это, безусловно, гарантируется).

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

■ Во-первых, они создают основу для аналогичного (но более глубокого) обсужде ния операций обновления в следующем разделе.

■ Во-вторых, становится очевидным, что материализация представляет собой без условно допустимый способ реализации представлений, по крайней мере, в слу чае операций выборки (хотя, возможно, довольно неэффективный). Однако этот способ, конечно же, не может быть использован при выполнении операции обновления, поскольку смысл обновления представления заключается в приме нении указанной операции обновления именно к лежащим в основе представ ления базовым переменным отношения, а не просто к некоторой материализо ванной копии их данных (об этом также более подробно будет сказано в следую щем разделе).

■ В-третьих, хотя, в принципе, процедура замены представления его определением вполне понятна и теоретически полностью обоснована, весьма огорчает тот факт, что в некоторых программных продуктах SQL (ко времени написания данной книги) этот процесс практически не действует успешно! Иначе говоря, в подобных продуктах выборка данных из представлений может совершенно неожиданно за вершаться неудачей. Процедура подстановки определения не реализована в верси ях стандарта SQL, предшествующих версии SQL: 1992. А причиной, по которой в приложениях и предыдущих версиях стандарта SQL операции выборки из пред ставлений не действуют должным образом, является неполная поддержка ими ре ляционного свойства замкнутости (см. упр. 10.15, часть а).

Источник: Дейт К. Дж., Введение в системы баз данных, 8-е издание.: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 1328 с.: ил. — Парал. тит. англ.

Выборка данных — оператор SELECT.

Тема: Создание запросов на выборку.

Введение в SQL

SQL является инструментом для выборки и обработки информации, которая содержится в компьютерной базе данных. Уникальность этого языка состоит в том, что он является единственным стандартным языком баз данных. Язык SQL поддерживают свыше 100 СУБД (Системы Управления Базами Данных). И самое главное: несмотря на то, что каждая СУБД по-своему извращается над стандартом (вносит какие-то свои дополнения), стандартный SQL поддерживают все базы данных.

Когда пользователю необходимо получить информацию из базы данных, он запрашивает эту информацию у СУБД с помощью SQL. СУБД обрабатывает запрос, находит (или не находит) требуемые данные и возвращает результат пользователю.

SQL используется для реализации всех функциональных возможностей, предоставляемых СУБД. К ним относятся:

  • Организация данных. SQL предоставляет пользователю возможность изменять структуру представления данных, а также устанавливать отношения между элементами базы данных.
  • Выборка данных. SQL дает пользователю или приложению возможность извлекать из базы данных содержащуюся в ней информацию и пользоваться ею.
  • Обработка данных. SQL дает пользователю или приложению возможность изменять базу данных, то есть добавлять в нее новые данные, а также удалять или модифицировать имеющиеся данные.
  • Управление доступом. С помощью SQL можно ограничить возможности пользователя по выборке и изменению данных и защитить их от несанкционированного доступа.
  • Совместное использование данных. SQL позволяет координировать совместное использование данных пользователями, которые работают параллельно, чтобы они не мешали друг другу.
  • Целостность данных. SQL позволяет обеспечить целостность базы данных, защищая ее от разрушения из-за несогласованных изменений или отказа системы.

Операторы языка SQL.

Команды языка SQL можно поделить на три категории:

  • DDL — Data Definition Language (Язык Определения Данных) — состоит из команд, которые создают объекты (таблицы, индексы, представления, и так далее) в базе данных.
  • DML — Data Manipulation Language (Язык Манипулирования Данными) — это набор команд, которые определяют какие значения представлены в таблицах в любой момент времени.
  • DCL — Data Control Language (Язык Управления Данными) — состоит из средств, которые определяют, разрешить ли пользователю выполнять определенные действия или нет.

Рассмотрим основные операторы DDL.

Выборка данных — оператор SELECT.

Замечание: SQL является регистронезависимым языком, то есть различия между большими и малыми буквами не существует.

Предположим у нас есть таблица, которая хранит информацию об имеющихся книгах и их атрибутах

Запрос, позволяющий отобразить всю таблицу (она называется Books) целиком, выглядит таким образом: Select * From Books

Здесь: Select — оператор выборки данных; * — указывает, что должны выбираться все столбцы в том же порядке, как они определены в базе данных; ключевое слово From указывает откуда должны выбираться данные (имя таблицы).

Если же пользователю не нужна полная информация, он может указать какие столбцы необходимо выбирать и в каком порядке, расположив имена столбцов после инструкции Select, и разделив их запятыми.

Select Name, Author, Press, Pages

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

Пользователь также имеет возможность форматировать вывод результата запроса на экран. Например, результат запроса

Select ‘Book = ‘, Name, ‘Press = ‘, Press

дает следующие результаты (см. рисунок).

Как можно заметить, у текстовых столбцов не очень понятное название (Expr2000 и т. п.). Так происходит потому, что таких столбцов в базе не существует и Access дает им собственные названия. Чтобы этого избежать, можно дать этим столбцам псевдонимы.

Select ‘Book = ‘ as BookName, Name, ‘Press = ‘as PressName, Press

Результат представлен на рисунке.

Кстати, такой псевдоним можно назначить даже существующему в таблице столбцу.

SQL также позволяет создавать «на лету» столбцы, значения в которых строятся на основе какого-либо выражения, в котором участвуют значения «реальных» столбцов, в каждой строке соответственно. Такой столбец называется вычислимым.

Например, вычислим общее количество страниц по имеющимся в наличии книгам.

Select Name, Quantity * Pages as TotalPages

Результат выполнения запроса представлен на рисунке.

Бывают случаи, когда в результирующем наборе строк появляются дубликаты (идентичные строки). Например, рассмотрим следующий запрос:

Select Author From Books

В данном случае мы получаем список всех авторов в таблице, но так как некоторые из них написали более одной книги, то в результирующем наборе встречаются одинаковые строки. Если нам это не нужно, то существует возможность исключить из результирующего набора строк дубликаты с помощью ключевого слова Distinct. Перепишем запрос:

Select Distinct Author From Books

Результат выполнения запроса представлен на рисунке.

Как видите, строки-дубликаты больше не появляются.

Однако, далеко не всегда нужно выбирать все строки таблицы. Если пользователю нужна какая-то конкретная информация, то он может ограничить результирующий набор строк, используя дополнительные условия (уточняя запрос).

Для установки условия используется ключевое слово Where и набор логических операторов:

  • > — больше
  • >= — больше либо равно
  • — не равно (в некоторых базах используется знак !=)
  • and — логическое «И» (умножение)
  • or — логическое «или» (сложение)
  • not — логическое «не» (отрицание)
  • between — принадлежность диапазону
  • in — проверка на членство в множестве
  • like — проверка на соответствие шаблону
  • is null — проверка на равенство значению NULL

Под значением NULL понимают неопределенное значение. Результатом логического выражения в SQL может быть либо истина, либо ложь, либо NULL (когда любая из частей выражения равна NULL). Строка будет включаться в результирующий набор только в том случае, если результат проверки условий отбора равен истине.

Приведем несколько примеров:

Запрос: необходимо вывести книги по программированию издательств «Питер» и «BHV»

Select Name as Название, Themes as Тематика, Press as Издательство

Where Themes = ‘Программирование’ and (Press = ‘BHV’ or Press = ‘Питер’)

Результат выполнения запроса представлен на рисунке.

Замечание: строки в SQL берутся в одинарные кавычки

Запрос: отобразить все книги, у которых количество страниц лежит в пределах от 200 до 600

Select Name as Название, Pages as Страницы

Where Pages Between 200 And 600

Результат выполнения запроса представлен на рисунке.

Запрос: отобразить все книги, имена авторов которых лежат в диапазоне от буквы ‘В’ до ‘О’

Select Name as Название, Author as Автор

Where Name Between ‘В’ And ‘О’

Результат выполнения запроса представлен на рисунке.

Замечание: при сравнении с текстом, отбор осуществляется согласно ASCII-кодам символов

Запрос: выбрать книги, относящиеся к программированию или к базам данных, и издательства которых не ‘Питер’и не ‘Бином’

Select Name as Название, Themes as Тематика, Press as Издательство

Where Press not in (‘Питер’,’Бином’)

Themes in (‘Программирование’,’Базы данных’)

Результат выполнения запроса представлен на рисунке.

Запрос: выбрать из таблицы тех авторов, у которых в имени и фамилии не менее трех букв ‘а’

Select Name as Автор

Where Name Like ‘*а*а*а*’

Результат выполнения запроса представлен на рисунке.

Оператор Like реализует поиск по шаблону:

  • * — означает, что данной позиции может присутствовать 0 или более любых символов (в других СУБД применяется символ %);
  • ? — означает, что в данной позиции обязан присутствовать 1 любой символ (в других СУБД применяется символ _);
  • # — означает, что в данной позиции обязана присутствовать 1 любая цифра;
  • [a-z] — означает, что в данной позиции обязан присутствовать 1 символ из указанного диапазона;
  • [dfaf] — означает, что в данной позиции обязан присутствовать 1 символ из указанного множества;
  • [!safgwe] — означает, что в данной позиции обязан присутствовать 1 символ, не входящий в указанный диапазон.

Для сортировки результирующих строк используется оператор Order By с необязательным параметром Asc (стоит по умолчанию) — сортировка по возрастанию (по алфавиту для строк), или Desc — сортировка по убыванию.

Запрос: отобразить всех авторов и их книги, авторов отсортировать по возрастанию, а названия книг (по авторам) по убыванию (вторичная сортировка)

Select Author as Автор, Name as Название

Order By Author, Name Desc

Результат выполнения запроса представлен на рисунке.

Итог:

Запрос на выборку данных подчиняется строгой структуре:

  1. Сначала указывается что выбрать и как отобразить (Select)
  2. Затем откуда выбрать (From)
  3. Как выбрать (Where)
  4. Как сортировать (Order By)

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Как то на паре, один преподаватель сказал, когда лекция заканчивалась — это был конец пары: «Что-то тут концом пахнет». 8378 — | 8008 — или читать все.

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Выборка данных: оператор SELECT

Для формирования запросов на выборку данных в SQL используется оператор SELECT. Его формат представлен ниже:

SELECT [ ALL | DISTINCT ] select_item_cominalist FROM table_reference_commalist [ WHERE conditional_expression ]

[ GROUP BY column_name_commalist ]

[ ORDER BY order_item_commalist ]

SELECT является достаточно сложным оператором, позволяющим выбирать данные из одной или нескольких таблиц, выполнять группировку, обработку данных с помощью агрегатных функций, формировать вложенные запросы и т.д. Выражение SELECT обрабатывается целиком, а не «построчно», как обычно бывает в языках программирования. В несколько обобщенном виде схема выполнения оператора SELECT выглядит следующим образом:

  • 1) выполняется раздел FROM;
  • 2) выполняется раздел WHERE (если есть);
  • 3) выполняется GROUP BY (если есть);
  • 4) выполняется HAVING (если есть);
  • 5) выполняются определения в разделе SELECT;
  • 6) выполняется ORDER BY (если есть).

Начнем с рассмотрения обязательного раздела SELECT. В нем указывается список элементов выборки select-item-commalist, который не должен быть пустым. Также может использоваться ключевое слово ALL или DISTINCT. Первое из них указывает, что в результате запроса могут быть повторяющиеся строки, второе – что повторения отбрасываются. Например, используется ключевое слово DISTINCT и есть три совпадающих строки, тогда в результате из них останется только одна. Когда явно ничего не указано, то подразумевается ALL.

Вернемся к списку элементов выборки. В нем через запятую могут указываться имена столбцов, константы, операции над столбцами или функции, возвращающие скалярное значение. Кроме того, может использоваться символ «*», обозначающий все столбцы, или .*, что указывает на все столбцы данной таблицы.

Перед тем как перейти к рассмотрению примеров, необходимо отметить, что в разделе FROM указываются таблицы, из которых делается выборка. Если через запятую перечислены несколько таблиц, это соответствует их декартову произведению. Следующий запрос позволит получить содержимое всей таблицы Т1:

SELECT * FROM T1 Аналогичный результат даст запрос SELECT Tl.* FROM Т1

Пусть имеется таблица Students, аналогичная представленной в табл. 7.2. Получить перечень номеров студенческих групп без повторения позволит следующий запрос:

SELECT DISTINCT [Group] FROM Students

По поводу этого запроса надо отметить следующее. Во-первых, предполагается, что номер группы может упоминаться в таблице несколько раз. Поэтому для отбрасывания повторений явно указывается ключевое слово DISTINCT. Если бы в перечне столбцов был первичный или альтернативный ключ, это обеспечило бы уникальность строк в результате запроса и DISTINCT можно было бы опустить. Во-вторых, название столбца Group совпадает с названием инструкции SQL. Поэтому в большинстве случаев потребуется явно указать в СУБД, что речь идет о названии столбца. В частности, для MS SQL Server надо будет использовать двойные кавычки или квадратные скобки: [Group].

Рассмотрим пример с заданием имени столбца и использованием текстовой константы в столбце. Если необходимо явно указать, как столбец будет называться в выводимых результатах запроса, это можно сделать в списке элементов выборки в разделе SELECT. Новое имя указывается после исходного названия столбца через пробел или после необязательного ключевого слова «as». Ниже приведен пример, в котором список фамилий и инициалов студентов сопровождается подписью «Фамилия и инициалы»:

SELECT DISTINCT ‘Фамилия и инициалы’ as Labell, FIO FROM Students

Подпись задается с помощью строковой константы, которые в SQL берутся в одинарные кавычки. Называться столбец с подписью будет Labell. Результат выполнения этого запроса для набора данных из табл. 7.2 представлен в табл. 7.6. Как отмечалось выше, ключевое слово «as» в SELECT можно пропустить, но иногда оно позволяет сделать текст на SQL более понятным.

Илон Маск рекомендует:  Веб-студии студии создания сайтов и веб дизайна

SQL SELECT и запросы на выборку данных

Оператор языка SQL SELECT предназначен для запросов на выборку данных из базы данных. Он может быть использован как без условий (выбор всех строк во всех столбцах или всех строк в определённых столбцах), так и с многочисленными условиями (выбор определённых строк), которые заданы в секции WHERE. Ознакомимся со средствами SQL, которыми можно задавать эти условия на выборку данных, а также узнаем, как использовать оператор SELECT в подзапросах.

SELECT для выбора столбцов таблицы

Запрос с оператором SELECT для выбора всех столбцов таблицы имеет следующий синтаксис:

То есть для выбора всех столбцов таблицы после слова SELECT нужно ставить звёздочку.

Пример 1. Есть база данных фирмы — Company. В ней есть таблица Org (Структура фирмы) и Staff (Сотрудники). Требуется выбрать из таблиц все столбцы. Соответствующий запрос для выбора всех столбцов из таблицы Org выглядит следующим образом:

Этот запрос вернёт следующее (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Запрос для выбора всех столбцов из таблицы Staff выглядит следующим образом:

Этот запрос вернёт следующее:

Для выбора определённых столбцов таблицы нам потребуется вместо звёздочки перечислить через запятую названия всех столбцов, которые требуется выбрать:

Пример 2. Пусть требуется из таблицы Org выбрать столбцы Depnumb и Deptname, в которых содержатся данные соответственно о номерах отделов фирмы и об их названиях. Запрос для получения такой выборки будет следующим:

А из таблицы Staff нужно выбрать столбцы DEPT, NAME, JOB, в которых содержатся соответственно данные о номере отдела, в котором трудится сотрудник, его имени и должности:

SELECT и WHERE для выбора строк таблицы

Для выбора определённых строк таблицы вместе с оператором SELECT уже потребуется ключевое слово WHERE, указывающее на некоторое значение или несколько значений, содержащиеся в интересующих нас строках. Наиболее простые условия задаются при помощи операторов сравнения и равенства ( , =), а также ключевого слова IS. Условий может быть несколько, тогда они перечисляются с использованием ключевого слова AND. Запросы для выбора строк имеют следующий синтаксис:

Пример 3. Выберем из таблицы Staff строки, в которых содержатся данные только о сотрудниках, которые работают в 38-м отделе:

Этот запрос вернёт следующие данные:

Пример 4. В предыдущем примере мы выбирали строки из таблицы только по значению одного столбца — DEPT. Пусть теперь нужно выбрать данные о сотрудниках, которые работают в 38-м отделе и должность которых — служащий (Clerk). Для этого в секции WHERE соответствующие значения нужно перечислить с использованием слова AND:

Этот запрос вернёт следующие данные:

Пример 5. Пусть нужно выбрать из таблицы Staff идентификаторы и имена тех сотрудников, размер комиссии которых — неопределённый. Для этого в секции WHERE перед указанием значения столбца COMM — NULL нужно ставить не знак равенства, а слово IS:

Этот запрос вернёт следующие данные:

Для указания значений в строках, которые требуется выбрать, используются и знаки сравнения.

Пример 6. Выберем из таблицы имена, размеры заработные платы и число лет, проработанных в фирме, сотрудников, которые работают в фирме более девяти лет:

Запрос вернёт следующие строки:

Использование SELECT и предикатов IN, OR, BETWEEN, LIKE

Предикаты — слова IN, OR, BETWEEN, LIKE в секции WHERE — также позволяют выбрать определённые диапазоны значений (IN, OR, BETWEEN) или значения в строках (LIKE), которые требуется выбрать из таблицы. Запросы с предикатами IN, OR, BETWEEN имеют следующий синтаксис:

Запросы с предикатом LIKE имеют следующий синтаксис:

Пример 7. Пусть требуется выбрать из таблицы Staff имена, должности и число отработанных лет сотрудников, работающих в отделах с номерами 20 или 84. Это можно сделать следующим запросом:

Результат выполнения запроса:

На сайте есть подробный урок об использовании предиката IN.

Пример 8. Пусть теперь требуется выбрать из таблицы Staff те же данные, что и в предыдущем примере. Запрос со словом OR аналогичен запросу со словом IN и перечислением интересующих значений в скобках. Запрос будет следующим:

Пример 9. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, зарплата которых между 15000 и 17000 включительно:

Результат выполнения запроса:

На сайте есть подробный урок об использовании предиката BETWEEN.

Предикат LIKE используется для выборки тех строк, в значениях которых встречаются символы, указанные после предиката между апострофами (‘).

Пример 10. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и состоят из 7 символов:

Символ подчёркивания (_) означает любой символ. Результат выполнения запроса:

Пример 11. Выберем из той же таблицы имена, должности и число отработанных лет сотрудников, имена которых начинаются с буквы S и содержат любые другие буквы в любом количестве:

Символ процентов (%) означает любое количество символов. Результат выполнения запроса:

На сайте есть подробный урок об использовании предиката LIKE.

Значения, указанные с использованием предикатов IN, OR, BETWEEN, LIKE можно инвертировать при помощи слова NOT. Тогда запрашиваемые данные будут иметь противоположный смысл. Если мы используем NOT IN (20, 84), то будут выведены данные сотрудников, которые работают во всех отделах, кроме имеющих номера 20 и 84. С использованием NOT BETWEEN 15000 AND 17000 можно получить данные сотрудников, зарплата которых не входит в интервал от 15000 до 17000. Запрос с NOT LIKE выведет данные сотрудников, чьи имена не начинаются или не содержат символов, указанных с NOT LIKE.

Написать SQL запросы с SELECT и предикатами IN, NOT IN, BETWEEN самостоятельно, а затем посмотреть решения

Есть база данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 12. Вывести список актёров, которые не разу не были утверждены на главную роль. В таблице team данные о главных ролях содержатся в столбце mainteam. Если роль — главная, то в соответствующей строке отмечено ‘Y’.

Пример 13. Вывести список актеров, которые играли во всех спектаклях WilliamShakespeare. Данные об авторах содержается в таблице play в столбце author.

Пример 14. Вывести спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, Group by, Having, AVG, перекрестное соединение таблиц (CROSS JOIN), удобнее без слова JOIN, а с перечислением таблиц через запятую).

SELECT и ORDER BY — сортировка (упорядочение) строк

Разобранные до сих пор запросы SQL SELECT возвращали строки, которые могли быть расположены в любой последовательности. Однако часто требуется отсортировать строки по порядку номеров, алфавиту и другим признакам. Для этого служит ключевое словосочетание ORDER BY. Такие запросы имеют следующий синтаксис:

Пример 15. Пусть требуетя выбрать из таблицы Staff сотрудников, работающих в отделе с номером 84 и отсортировать (упорядочить) записи по числу отработанных лет в возрастающем порядке:

Слово ASC указывает, что порядок сортировки — возрастающий. Это слово не обязательно, так как возрастающий порядок сортировки применяется по умолчанию. Результат выполнения запроса:

Пример 16. Пусть требуетя выбрать те же данные, что и в предыдущем примере, но отсортировать (упорядочить) записи по числу отработанных лет в убывающем порядке:

Слово DESC указывает, что порядок сортировки — убывающий. Результат выполнения запроса:

SELECT и DISTINCT — удаление дубликатов строк

Когда для значений строк таблицы не задано условие уникальности, в результатах запроса могут встретиться одинаковые строки. Часто требуется вывести лишь уникальные строки. Это делается при помощи выражения DISTINCT после оператора SELECT.

Пример 17. Пусть требуетcя узнать, какие существуют отделы и какие должности среди отделов, номера которых меньше 30. Это можно сделать при помощи следующего запроса:

Результат выполнения запроса:

Оператор SELECT в подзапросах SQL

До сих пор мы разбирали конструкции SQL с оператором SELECT, в которых условия, по котором выбираются данные, и сами выбираемые данные содержатся в одной и той же таблице базы данных. На практике часто бывает, что данные, которые надо выбрать, содержатся в одной таблице, а условия — в другой. Здесь на помощь приходят подзапросы: значения условия отбора возвращаются из другого запроса (вложенного запроса), начинающегося также с SELECT. Запросы с подзапросами могут выдавать как одну, так и несколько строк.

Пример 18. Все те же таблицы ORG и STAFF. Пусть требуетcя узнать, в каком подразделении работает сотрудник с идентификационным номером 280, и где это подразделение расположено. Но информация о подразделениях хранится в таблице ORG, а информация о сотрудниках — в таблице STAFF. Это можно сделать при помощи следующего запроса с подзапросом, в котором внешний SELECT обращается к таблице ORG, а внутренний SELECT — к таблице STAFF:

Результат выполнения запроса:

Пример 19. Пусть теперь требуетcя узнать, в каких подразделениях (без дублирования) работают сотрудники с заработной платой менее 13000. Для этого в секции WHERE внешнего SELECT (запрос к таблице ORG) задаётся условие, принимающее диапазон значений (IN), а внутренний SELECT (к таблице STAFF) как раз возвращает требуемый диапазон значений:

Создание и удаление представлений

Для создания представлений используется следующий синтаксис:

CREATE [OR REPLACE] VIEW имя_представления AS

[WITH CHECK OPTION];

Примеры создания представлений:

— создание представления, содержащего все поля таблицы

— CONTRAGENTS за исключением поля Address (скрытие данных)

CREATE OR REPLACE VIEW Contragents_Lite AS

SELECT Contr_id, Name, Phone, Comments FROM Contragents;

Операции выборки из представлений

Для пользователя выборка данных из представления ничем не отличается от выборки данных из таблицы. Иногда пользователи и не догадываются, пользуются они реальной таблицей или представлением. Выборка данных из представлений осуществляется так же, как и из таблиц – при помощи оператора SELECT, например:

— выборка всей информации обо всех контрагентах

— за исключением адреса, поскольку поле не было включено

SELECT * FROM Contragents_Lite ORDER BY Name;

Обновляемые представления

Если к представлению можно применить операторы обновления (INSERT, UPDATE или DELETE), то представление является обновляемым (updateble), иначе оно является читаемым (read-only).

Ниже приведены критерии того, является ли представление обновляемым в SQL:

· оно базируется на одной таблице;

· оно должно включать первичный ключ таблицы;

· оно не должно включать полей, полученных в результате применения функций агрегирования;

· оно не может содержать спецификации DISTINCT;

· оно не должно использовать GROUP BY или HAVING;

· оно не должно использовать подзапросы;

· оно может быть определено на другом представлении, но это представление должно быть обновляемым;

· оно не может содержать константы, строки или выражения в списке выбираемых выходных полей;

· для INSERT оно должно включать поля из таблицы, которые имеют ограничения NOT NULL.

Например, приведенное представление Contragents_Lite является обновляемым, поскольку оно удовлетворяет всем перечисленным критериям, а представление WareHouse – нет.

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

Для предотвращения описанной ситуации применяется параметр WITH CHECK OPTION, который может указываться при создании обновляемых представлений после оператора SELECT. При наличии этого параметра СУБД предотвращает операции обновления данных, если они нарушают условия горизонтальной или вертикальной фильтрации, определенные в предложении в операторе SELECT:

— создание обновляемого представления с защитой от

— изменений скрытого столбца Address

CREATE OR REPLACE VIEW Contragents_Lite AS

SELECT Contr_id, Name, Phone, Comments FROM Contragents

WITH CHECK OPTION;

Компоненты языка Transact-SQL

Переменные

Локальные переменные обозначаются префиксом @, глобальные переменные – двойным символом @@. Например: @MyVariable, @@VERSION. Все глобальные переменные определяются SQL Server, вы не можете определить их самостоятельно. Большинство глобальных переменных предоставляют информацию о текущем статусе SQL Server.

Создание локальных переменных:

DECLARE @локальная_переменная тип_данных

Например: DECLARE @var1 int, @var2 int

Большинство типов данных являются скалярными, т.е. содержат одно значение, такое как число или строка. Возможно объявлять переменные с табличным типом данных:

Определение таблицы идентично обычному CREATE TABLE, за исключением некоторых ограничений.

После создания локальных переменных, которые первоначально имеют значение NULL, им можно присвоить другие значения следующими способами:

SET @myChar = ‘Hello’

  • Используя команду SELECT

SELECT @myChar = ‘Hello’

  • С вложенным оператором SELECT

SELECT @myChar = MAX (OilName) FROM Oils

Оператор = замещает ключевое слово SELECT.

  • Используя команду INSERT INTO с указанием переменной табличного типа

INSERT INTO @myTable SELECT * FROM Oils или

INSERT INTO @myTable VALUES (‘The value’)

Управление ходом выполнения

Transact-SQL обрабатывает операторы сценария от начала до конца, не пропуская ни один оператор, если не задано иное. Это не всегда то, что вам нужно. Иногда вам необходимо, чтобы оператор выполнялся, только если некоторое условие истинно, или повторялся до тех пор, пока некоторое условие является истинным, или выполнялся указанное количество раз.

Когда вы приступаете к использованию операторов управления ходом выполнения Transact-SQL, удобно интерпретировать определенную группу команд как блок. Transact-SQL позволяет вам сделать это с помощью пары команд BEGIN. END.

В блок вы можете включить любой оператор Transact-SQL, в том числе другие блоки BEGIN. END, но здесь есть несколько ограничений. Вы не можете сочетать операторы CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER и CREATE VIEW с какими-либо другими операторами. Также вы не можете изменять структуру таблицы и затем ссылаться на новый столбец в этом же блоке.

Условное выполнение IF. ELSE

Если булево выражение, следующее за командой IF, имеет значение TRUE, то будет выполнен оператор или блок операторов, следующий за этим оператором. Если булево выражение имеет значение FALSE, то оператор или блок операторов, следующий за FALSE, будет пропущен.

Операторы IF. ELSE могут быть вложены один в другой, образуя логическую структуру, схожую с конструкцией IF. ELSEIF. ELSE, принятой в других языках программирования. Однако следует остерегаться использовать этот прием в простых случаях.

Тот же эффект можно достичь путем использования оператора CASE. При этом оценить ход выполнения будет легче.

В большинстве языков программирования оператор CASE является расширенной формой оператора IF, которая позволяет вам определять множество булевых выражений в одном операторе. В SQL Server CASE является функцией, а не командой. Она используется не сама по себе, как IF, а как часть оператора SELECT или UPDATE.

Синтаксис простой структуры CASE представлен ниже:

WHEN выражение_один THEN результирующее_выражение_один

WHEN выражение_два ТНEN результирующее_выражение_два

WHEN выражение_п ТНEN результирующее_выражение_п

Вы можете использовать в выражении любое количество фраз WHEN. Фраза ELSE необязательна — она выполняется, только если все фразы WHEN оцениваются как FALSE.

Сравнение одного значения с некоторым количеством других значений является типичной задачей, но иногда вам требуется большая степень гибкости. В этом случае вы можете воспользоваться так называемым поисковым синтаксисом функции CASE Transact-SQL, который имеет следующий вид:

WHEN булево_выражение_один ТНEN результирующее_выражение_один

WHEN булево_выражение_два ТНEN результирующее_выражение_два

WHEN булево_выражение_п THEN результирующее_выражение_п

SELECT TOP 10 OilName, LatinName,

WHEN LEFT (OilName, 1) = ‘B’ THEN ‘Name B’

WHEN LEFT (LatinName, 1) = ‘C’ THEN ‘ LatinName C’

END AS TestResults

ORDER BY OilName

В этой форме CASE вы можете указать целое булево выражение в каждой фразе WHEN вместо неявного сравнения выражения в простой форме. Учтите, что при определении истинности можно оценивать несколько булевых_выражений. Transact-SQL возвратит только первое результирующее _выражение, а затем перейдет к оператору, следующему за END.

Структуры IF. ELSE и CASE управляют порядком выполнения операторов, основываясь на результатах вычисления булевого выражения. Команда GOTO является безусловной. Она передает выполнение непосредственно к оператору, следующему после метки, которая на него указывает. Сама команда GOTO имеет очень простой синтаксис:

GOTO является весьма непопулярной в кругу программистов командой. Применение команды GOTO, тем не менее, бывает вполне оправданным, особенно при обработке ошибок. Если вы будете аккуратно использовать эту команду, она даже сможет сделать код проще для понимания. Используйте команду GOTO для безусловного перехода.

Циклы

Последняя из команд управления ходом выполнения позволяет вам выполнять оператор или блок операторов до тех пор, пока выполняется (является истинным) указанное условие.

Простейшая форма цикла WHILE содержит булево выражение и оператор или блок операторов. Операторы будут повторяться, пока булево выражение не примет FALSE. Если при первой оценке булево выражение имеет значение FALSE, то оператор или блок операторов не будет выполняться вообще.

Илон Маск рекомендует:  Атрибут charset в HTML

DECLARE @counter int

GROUP BY CompanyName

ORDER BY CompanyName

Вот фрагмент выходных данных, генерируемых этой инструкцией:

Around the Horn 13806.8

Berglunds snabbkop 24927.6

Blauer See Delikatessen 3421.95

Blondesddsl pere et fils 18534.1

Bolido Comidas preparadas 4232.85

Bottom-Dollar Markets 20801.6

Если не пользоваться функцией CONVERT(), названия компаний будут дополняться пробелами до длины 40 символов, а в числах будет слишком много десятичных знаков:

Around the Horn 13806.800003051758

Berglunds snabbkop 24927.57746887207

Blauer See Delikatessen 3421.9500045776367

CAST (variable AS data_type)

Функция CAST() преобразует значение переменной или столбца к заданному типу данных. Эта функция делает то же самое, что и функция CONVERT() и включена в T-SQL для обеспечения совместимости со стандартом SQL-92.

Последнее изменение этой страницы: 2020-01-25; Нарушение авторского права страницы

SQL — Урок 4. Выборка данных — оператор SELECT

Вместо «что_выбрать» мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо «откуда_выбрать» следует указать имя таблицы.

Давайте сначала посмотрим все столбцы из таблицы users:

SELECT * FROM users;

Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:

SELECT id_user FROM users;

Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:

SELECT name, email FROM users;

Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:

SELECT * FROM topics;

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:

По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC

Теперь наши данные отсортированы в порядке по убыванию.

Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:

Сравните результат с результатом предыдущего запроса.

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta ( >WHERE, синтаксис у такого запроса следующий:

Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):

SELECT * FROM topics WHERE >

Или мы хотим узнать, кто создал тему «велосипеды»:

Конечно, было бы удобнее, чтобы вместо id автора, выводилось его имя, но имена хранятся в другой таблице. В последующих уроках мы узнаем, как выбирать данные из нескольких таблиц. А пока узнаем, какие условия можно задавать, используя ключевое слово WHERE.

Оператор Описание
= (равно) Отбираются значения равные указанному

SELECT * FROM topics WHERE >
Результат:

> (больше) Отбираются значения больше указанного

SELECT * FROM topics WHERE id_author>2;

Пример:

SELECT * FROM topics WHERE id_author Результат:

>= (больше или равно) Отбираются значения большие и равные указанному

SELECT * FROM topics WHERE id_author>=2;

Пример:

SELECT * FROM topics WHERE >
Результат:

!= (не равно) Отбираются значения не равные указанному

SELECT * FROM topics WHERE >
Результат:

IS NOT NULL Отбираются строки, имеющие значения в указанном поле

SELECT * FROM topics WHERE id_author IS NOT NULL;

IS NULL Отбираются строки, не имеющие значения в указанном поле

SELECT * FROM topics WHERE id_author IS NULL;

Empty set — нет таких строк.

BETWEEN (между) Отбираются значения, находящиеся между указанными

SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3;

IN (значение содержится) Отбираются значения, соответствующие указанным

SELECT * FROM topics WHERE id_author IN (1, 4);

NOT IN (значение не содержится) Отбираются значения, кроме указанных

SELECT * FROM topics WHERE id_author NOT IN (1, 4);

LIKE (соответствие) Отбираются значения, соответствующие образцу

SELECT * FROM topics WHERE topic_name LIKE ‘вел%’;

Возможные метасимволы оператора LIKE будут рассмотрены ниже.

NOT LIKE (не соответствие) Отбираются значения, не соответствующие образцу

SELECT * FROM topics WHERE topic_name NOT LIKE ‘вел%’;

Метасимволы оператора LIKE

Поиск с использованием метасимволов может осуществляться только в текстовых полях.

Самый распространенный метасимвол — %. Он означает любые символы. Например, если нам надо найти слова, начинающиеся с букв «вел», то мы напишем LIKE ‘вел%’, а если мы хотим найти слова, которые содержат символы «клуб», то мы напишем LIKE ‘%клуб%’. Например:

Еще один часто используемый метасимвол — _. В отличие от %, который обозначает несколько или ни одного символа, нижнее подчеркивание обозначает ровно один символ. Например:

Обратите внимание на пробел между метасимволом и «рыб», если его пропустить, то запрос не сработает, т.к. метасимвол _ обозначает ровно один символ, а пробел — это тоже символ.

На сегодня достаточно. В следующем уроке мы научимся составлять запросы к двум и более таблицам. А пока попробуйте самостоятельно составить запросы к таблице posts (сообщения).

Предыдущий урок Вернуться в раздел Следующий урок 
Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.

1. Операция выборки.

1. Операция выборки.

Операция выборки на языке SQL реализуется оператором Select следующего вида:

Select все атрибуты

From имя отношения

Where условие выборки;

Здесь вместо того, чтобы писать «все атрибуты», можно использовать значок «*». В теории языка структурированных запросов этот значок означает выбор всех атрибутов из схемы отношения.

Условие выборки здесь (и во всех остальных реализациях операций) записывается в виде логического выражения со стандартными связками not (не), and (и), or (или). На атрибуты отношения ссылаемся посредством их имен.

Рассмотрим пример. Определим следующую схему отношения:

Успеваемость (№ зачетной книжки, Семестр, Код предмета, Оценка, Дата);

Здесь, как уже упоминалось ранее, подчеркнутые атрибуты образуют ключ отношения.

Составим оператор Select следующего вида, реализующий унарную операцию выборки:

Where № зачетной книжки = 100 and Семестр = 6;

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

Представления (VIEW) в MySQL

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).

Преимущества использования представлений:

  1. Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
  2. Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
  3. Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения представлений в MySQL

Создание представлений

CREATE [ OR REPLACE]
[ALGORITHM = ]
VIEW view_name [(column_list)]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

* This source code was highlighted with Source Code Highlighter .

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

  1. OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
  2. ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
  3. column_list — задает имена полей представления.
  4. WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:

  1. Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:

    CREATE VIEW v AS SELECT a. >FROM a,b;

    * This source code was highlighted with Source Code Highlighter .

    CREATE VIEW v (a_ >AS SELECT a. >FROM a,b;

    * This source code was highlighted with Source Code Highlighter .

    CREATE VIEW v AS SELECT a. >FROM a,b;

    * This source code was highlighted with Source Code Highlighter .

    CREATE VIEW v AS SELECT group_concat( DISTINCT column_name oreder BY column_name separator ‘+’ ) FROM table_name;

    * This source code was highlighted with Source Code Highlighter .

    Алгоритмы представлений

    Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

    В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.

    В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
    Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

    При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
    UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

    Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

    Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

    CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

    * This source code was highlighted with Source Code Highlighter .

    SELECT subject, param FROM v WHERE param>1000;

    * This source code was highlighted with Source Code Highlighter .

    SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

    * This source code was highlighted with Source Code Highlighter .

    Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

    Пусть наше представление выбирает количество тем для каждого форума:

    CREATE VIEW v AS SELECT forum_ >count (*) AS num FROM topics GROUP BY forum_id;

    * This source code was highlighted with Source Code Highlighter .

    * This source code was highlighted with Source Code Highlighter .

    SELECT MAX ( count (*)) FROM topics GROUP BY forum_id;

    * This source code was highlighted with Source Code Highlighter .

    Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

    В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

    CREATE TEMPORARY TABLE tmp_table SELECT forum_ >count (*) AS num FROM topics GROUP BY forum_id;
    SELECT MAX (num) FROM tmp_table;
    DROP TABLE tpm_table;

    * This source code was highlighted with Source Code Highlighter .

    Обновляемость представлений

    Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:

    1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
    2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

    Обратите внимание: встречающиеся в русско-язычной литературе требования, чтобы обновляемое представление было основано на единственной таблице и присутствие в числе полей представления первичного ключа физичекой таблицы не являются необходимыми. Скорее всего требование единственной таблицы является ошибкой перевода. Дело в том, что через представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION.

    Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

    Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

    При использовании в определении представления конструкции WITH [CASCADED | LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

    • Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
    • Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

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

    Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

    • Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
    • Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

    Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

    punbb > CREATE OR REPLACE VIEW v AS
    -> SELECT forum_name, `subject`, num_views FROM topics,forums f
    -> WHERE forum_ >AND num_views>2000 WITH CHECK OPTION ;
    Query OK, 0 rows affected (0.03 sec)

    punbb > UPDATE v SET num_views=2003 WHERE subject= ‘test’ ;
    Query OK, 0 rows affected (0.03 sec)
    Rows matched: 1 Changed: 0 WARNINGS: 0

    punbb > SELECT subject, num_views FROM topics WHERE subject= ‘test’ ;
    +———+————+
    | subject | num_views |
    +———+————+
    | test | 2003 |
    +———+————+
    1 rows IN SET (0.01 sec)

    * This source code was highlighted with Source Code Highlighter .

    Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

    punbb > UPDATE v SET num_views=1999 WHERE subject= ‘test’ ;
    ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’

    * This source code was highlighted with Source Code Highlighter .

    Не все обновляемые представления позволяют добавление данных:

    punbb > INSERT INTO v (subject,num_views) VALUES ( ‘test1’ ,4000);
    ERROR 1369 (HY000): CHECK OPTION failed ‘punbb.v’

    * This source code was highlighted with Source Code Highlighter .

    Причина в том, что значением по умолчанию колонки forum_ >

    punbb > INSERT INTO v (forum_ >VALUES (1, ‘test1’ ,4000);
    ERROR 1054 (42S22): Unknown COLUMN ‘forum_id’ IN ‘field list’

    * This source code was highlighted with Source Code Highlighter .

    punbb > INSERT INTO v (forum_name) VALUES ( ‘TEST’ );
    Query OK, 1 row affected (0.00 sec)

    * This source code was highlighted with Source Code Highlighter .

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

    Содержание

    Глава 5. О предложениях определения данных и оптимизации запросов

    5.1. Системный каталог

    Системный каталог — это набор таблиц, в которых содержится информация, необходимая для правильного функционирования СУБД: о поддерживаемых базах данных и их базовых таблицах, представлениях, курсорах, индексах, пользователях и их правах доступа к информации, правилах модификации данных и т.д. В разных СУБД, поддерживающих SQL, существует от десятка до нескольких десятков системных таблиц, структура которых ничем не отличается от уже знакомой нам структуры пользовательских таблиц.

    Так, в каждой строке системной таблицы SYSTABLES хранится описание одной из таблиц пользовательских или системной баз данных. Для каждой из них указывается имя таблицы, имя пользователя, который создал эту таблицу, число столбцов в ней и ряд других элементов информации. В таблице SYSCOLUMNS содержится строка для каждого столбца каждой таблицы, в которой указано имя столбца, имя таблицы, частью которой является данный столбец, тип данных для этого столбца и много другой информации о столбце.

    С помощью предложения SELECT пользователь может получить информацию из любой системной таблицы. Например, он может дать запрос на получение имен таблиц, числа их столбцов и строк, владельца и краткого описания (если таковое вводилось в базу данных):

    и получить результат, показанный на рис. 5.1,а.

    Для получения же некоторых данных о столбцах таблицы Блюда можно дать запрос

    и получить результат, показанный на рис. 5.1,б.

    Tab_name N_col N_row Tab_owner Comments
    .
    SYS_TABLES 11 SYSTEM
    SYS_COLUMNS 14 SYSTEM
    .
    Блюда 6 33 KIRILLOW Перечень блюд, известных шеф-повару
    Поставки 4 37 GROMOW Данные о поставляемых продуктах
    Вид_блюд 2 5 KIRILLOW Перечень видов блюд
    Трапезы 2 3 GROMOW Перечень трапез в пансионате
    Состав 3 148 KIRILLOW Состав блюд
    Продукты 11 17 KIRILLOW Таблица продуктов
    .
    Col_name Type Length Comments
    БЛ INTEGER 4 Код блюда
    Блюдо TEXT 16 Название блюда
    В TEXT 1 Код вида блюда (З, С, . )
    Основа TEXT 6 Основной продукт в блюде
    Выход REAL 4 Масса порции готового блюда
    Труд INTEGER 4 Стоимость приготовления блюда (коп)

    Рис. 5.1. Результаты запросов по системным таблицам

    Пользователь, не знакомый со структурой базы данных, может с помощью подобного рода запросов получить информацию о такой структуре. Для этого ему надо владеть языком SQL и немного подумать.

    В заключение следует отметить, что СУБД не позволяет обновлять каталог с помощью предложений DELETE, INSERT и UPDATE. Обновление проводится только при создании, модификации или уничтожении таблиц, индексов, правил и т.п. с помощью предложений, рассматриваемых ниже.

    5.2. Создание и уничтожение базовых таблиц

    Базовые таблицы описываются в SQL с помощью предложения CREATE TABLE (создать таблицу), синтаксис которого имеет небольшие различия в различных СУБД. Однако все они поддерживают следующую минимальную форму:

    где тип_данных должен принадлежать к одному из типов данных, поддерживаемых СУБД (например, одному из типов данных, перечисленных в п.1.2).

    Так, описание таблицы Блюда может быть записано в виде

    В результате создается пустая базовая таблица Блюда, а в системный каталог помещается строка, описывающая эту таблицу. Отметим, что в профессиональных СУБД имя таблицы дополняется именем пользователя, который издал предложение CREATE TABLE. Если этот пользователь зарегистрирован в системе под именем Kirillov, то в каталоге будет зарегистрирована таблица Kirillov.Блюда и указанный пользователь может обращаться к ней по имени Kirillov.Блюда или по сокращенному имени Блюда, которое использовалось во всех предшествующих примерах и будет использоваться далее.

    Конструкция NOT NULL запрещает использование неопределенного значения, т.е. специального значения, которое вводится для представления «неизвестного значения» или «неприменимого значения». Например, строка поставки таблицы Поставки может содержать неопределенное значение в столбце Цена и (или) К_во (извесно, что поставщик поставляет указанный продукт, но на данный момент неизвестна цена этого продукта и (или) объем поставки).

    Существующую базовую таблицу можно в любой момент уничтожить с помощью предложения DROP TABLE (уничтожить таблицу):

    по которому удаляется описание таблицы, ее данные, связанные с ней представления и индексы, построенные для столбцов таблицы (см. п. 5.3).

    В SQL существует также предложение ALTER TABLE (изменить таблицу), которое позволяет добавить справа к таблице новый столбец, т.е. модифицировать описание табицы. Так как без него «можно жить», а объем книги ограничен, то мы не будем здесь описывать это предложение.

    5.3. О индексах и производительности

    Для ускорения поиска данных можно создавать индексы. Индекс — это системная таблица, построенная по значениям заданного столбца заданной таблицы. В нем размещается перечень уникальных значений указанного столбца таблицы со ссылками на те ее строки, где встречаются эти значения (структура, похожая на предметный указатель книги). Например, индекс, построенный для столбца Основа таблицы Блюда, будет содержать следующие сведения:

    Значения столбца Строки, в которых встречается такое значение
    Кофе 32 33
    Крупа 20 21
    Молоко 7 8 12 18 22 24 28 31
    Мясо 2 6 9 13 14
    Овощи 1 3 17 23 15
    Рыба 4 5 10 11
    Фрукты 25 26 27 29 30
    Яйца 16 19

    Отметим, что такой индекс уже существовал (в несколько иной форме) в базе данных, хотя это обстоятельство никак не повлияло на текст иллюстрационных предложений SELECT, DELETE, INSERT и UPDATE. SQL намеренно не включает в свои конструкции ссылки на индексы. Решение о том, использовать или не использовать какой-либо индекс при обработке некоторого конкретного запроса принимается не пользователем, а оптимизатором СУБД, который учитывает множество факторов — размер таблиц, тип используемых структур хранения данных, статистическое распределение данных в таблицах и индексах и т.д. Однако чтобы оптимизатор смог использовать индексы, их нужно построить (чтобы выиграть в лотерею нужно, по крайней мере, иметь лотерейный билет).

    Естественно, что поиск какого-либо значения путем последовательного перебора неупорядоченных данных будет во много раз медленнее, чем поиск с использованием упорядоченного списка (индекса). Ясно также, что таблицу можно упорядочить лишь по данным одного столбца, тогда как поиск часто приходится осуществлять по данным нескольких столбцов. По нескольким столб-цам производится и соединение таблиц. Поэтому, несмотря на то, что индексы увеличивают объем базы данных, их следует использовать как для отдельных столбцов таблицы, так и для комбинации нескольких ее столбцов (например, для комбинации: Фамилия, Имя, Отчество).

    Для построения индекса в SQL существует предложение CREATE INDEX (создать индекс), имеющее формат

    где UNIQUE (уникальный) указывает, что никаким двум строкам в индексируемой базовой таблице не позволяется принимать одно и то же значение для индексируемого столбца (или комбинации столбцов) в одно и то же время.

    Например, индексы для столбцов БЛ и Основа таблицы Блюда создаются с помощью предложений

    а индекс для первичного ключа (столбцы БЛ и ПР) таблицы Состав — с помощью предложения

    В больших (более 1000 строк) таблицах поиск индексированных значений выполняется на порядок быстрее, чем поиск неиндексированных, а в очень больших таблицах — на два-три порядка.

    Так может быть, если позволяет память, следует построить индексы для всех столбцов всех таблиц базы данных?

    Если база данных не должна модифицироваться, то на этот вопрос можно дать положительный ответ. Однако при удалении или добавлении строки таблицы должны быть перестроены все индексы, построенные для ее столбцов, а при изменении значения индексированного столбца — индекс этого столбца. Когда модифицируется много — несколько сотен (тысяч) строк — и после модификации каждой строки перестраиваются все ее индексы, время модификации может быть на порядок (несколько порядков) больше времени модификации строк с неиндексированными столб-цами. Поэтому перед модификацией множества строк таблицы целесообразно уничтожить индексы ее столбцов, что можно сделать с помощью предложения DROP INDEX (уничтожить индекс), имеющего следующий формат:

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

    5.4. Представления

    5.4.1. Создание и уничтожение представлений

    В п.1.3 уже обсуждалось это понятие, а также приводились примеры его создания и использования. Напомним, что представление — это виртуальная таблица, которая сама по себе не существует, но для пользователя выглядит таким образом, как будто она существует. Представление не поддерживаются его собственными физическими хранимыми данными. Вместо этого в каталоге таблиц хранится определение, оговаривающее, из каких столбцов и строк других таблиц оно должно быть сформировано при реализации SQL-предложения на получение данных из представления или на модификацию таких данных.

    Синтаксис предложения CREATE VIEW имеет вид

    где подзапрос, следующий за AS и являющийся определением данного представления, не исполняется, а просто сохраняется в каталоге;

    необязательная фраза «WITH CHECK OPTION» (с проверкой) указывает, что для операций INSERT и UPDATE над этим пред-ставлением должна осуществляться проверка, обеспечивающая удовлетворение WHERE фразы подзапроса;

    список имен столбцов должен быть обязательно определен лишь в тех случаях, когда:

    а) хотя бы один из столбцов подзапроса не имеет имени (создается с помощью выражения, SQL-функции или константы);

    б) два или более столбцов подзапроса имеют одно и то же имя;

    если же список отсутствует, то представление наследует имена столбцов из подзапроса.

    Например, создадим представление Мясные_блюда

    которое может рассматриваться пользователем как новая таблица в базе данных.

    Уничтожение ненужных представлений выполняется с помощью предложения DROP VIEW (уничтожить представление), имеющего следующий формат:

    5.4.2. Операции выборки из представлений

    Создав представление Мясные_блюда пользователь может считать, что в базе данных реально существует такая таблица и дать, например, запрос на получение из нее всех данных:

    результат которого имеет вид

    БЛ Блюдо В Выход
    2 Салат мясной З 200
    6 Мясо с гарниром З 250
    9 Суп харчо С 500
    13 Бастурма Г 300
    14 Бефстроганов Г 210

    Поскольку при определении представления может быть использован любой допустимый подзапрос, то выборка данных может осуществляться как из базовых таблиц, так и из представлений:

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

    Блюдо Продукт Вес
    Бастурма Говядина 180
    Бастурма Помидоры 100
    Бастурма Лук 40
    Бастурма Зелень 20
    Бастурма Масло 5

    Легко заметить, что данный запрос, осуществляющий выбор данных через два представления, выглядит для пользователя точно так же, как обычный SELECT, оперирующий обычной базовой таблицей. Однако СУБД преобразует его при выполнении в эквивалентную операцию над лежащими в основе базовыми таблицами (перед выполнением проводит слияние выданного пользователем SELECT с предложениями SELECT из описаний представлений, хранящихся в каталоге).

    5.4.3. Обновление представлений

    Рассмотренные в гл.4 операции DELETE, INSERT и UPDATE могут оперировать не только базовыми таблицами, но и представлениями. Однако, если из базовых таблиц можно удалять любые строки, обновлять значения любых их столбцов и вводить в такие таблицы новые строки, то этого нельзя сказать о представлениях, не все из которых являются обновляемыми.

    Безусловно обновляемыми являются представления, полученные из единственной базовой таблицы простым исключением некоторых ее строк и (или) столбцов, обычно называемые «представление-подмножество строк и столбцов». Таким является представление Мясные_блюда, полученное из базовой таблицы Блюда исключением из нее столбца Труд и строк, не содержащих значение ‘Мясо’ в столбце Основа. Работая с ним, можно:

      вставить (операция INSERT) новую строку, например, строку (34, ‘Шашлык’, ‘Г’, 150), фактически вставляя соответствующую строку (34, ‘Шашлык’, ‘Г’, 150, NULL) в лежащую в основе базовую таблицу Блюда;

    удалить (операция DELETE) существующую строку из представления, например строку (13, ‘Бастурма’, ‘Г’, 300), фактически удаляя соответствующую строку (13, ‘Бастурма’, ‘Г’, 300, 5) из таблицы Блюда;

  2. обновить (операция UPDATE) какое-либо поле в существующей строке, например увеличить массу порции Бефстроганова с 210 до 250 граммов, фактически осуществляя то же самое изменение в соответствующем поле таблицы Блюда.

Однако если бы представление Мясные_блюда имело вместо столбца Выход столбец Вых_труд, полученный путем суммирования значений столбцов Выход и Труд таблицы Блюда, то указанные выше операции INSERT и UPDATE были бы отвергнуты системой. Действительно, как распределить вводимое значение столбца Вых_труд (153 или 254) между значениями столбцов Выход и Труд базовой таблицы Блюда? Была бы отвергнута и операция удаления масла из состава бастурмы, если бы ее попытались выполнить путем удаления строки (‘Бастурма’, ‘Масло’, 5) в представлении Горячие_мясные_блюда. Встает множество вопросов: надо ли удалять из базовой таблицы Блюда строку, содержащую значение ‘Бастурма’ в столбце Блюдо?; надо ли удалять из базовой таблицы Продукты строку, содержащую значение ‘Масло’ в столбце Продукт?; надо ли удалять из базовой таблицы Состав все строки, содержащие значение 5 в столбце Вес?. Последний вопрос возник потому, что при конструировании представления Горячие_мяс-ные_блюда в него не была включена информация о связях между лежащими в его основе базовыми таблицами Блюда, Состав и Продукты, и у системы нет прямых путей для поиска той единственной строки таблицы Состав, которая должна быть удалена.

Таким образом, некоторые представления по своей природе обновляемы, в то время как другие таковыми не являются. Здесь следует обратить внимание на слова «по своей природе». Дело заключается не просто в том, что некоторая СУБД не способна поддерживать определенные обновления, в то время как другие СУБД могут это делать. Никакая СУБД не может непротиворечивым образом поддерживать без дополнительной помощи обновление такого представления как Горячие_мясные_блюда. «Без дополнительной помощи» означает здесь «без помощи какого-либо человека — пользователя».

Как было указано выше, к теоретически обновляемым представ-лениям относятся представления-подмножества строк и столбцов. Однако существуют некоторые представления, которые не являются представлениями-подмножествами строк и столбцов, но также теоретически обновляемы. Хотя известно, что такие есть и можно привести их примеры, но невозможно дать их формального определения. Неверным является такое формальное определение некоторых авторов — «нельзя обновлять соединение». Во-первых, в некоторых соединениях с успехом выполняется операция UPDATE, а, во-вторых, как было показано выше, не обновляемы и некоторые представления, не являющиеся соединениями. Кроме того, не все СУБД поддерживают обновление любых теоретически обновляемых представлений. Поэтому пользователь должен сам оценивать возможность использования операций DELETE, INSERT или UPDATE в созданном им представлении.

5.4.4. Для чего нужны представления

Одна из основных задач, которую позволяют решать представления, — обеспечение независимости пользовательских программ от изменения логической структуры базы данных при ее расширении и (или) изменении размещения столбцов, возникающего, например, при расщеплении таблиц. В последнем случае можно создать представление-соединение с именем и структурой расщепленной таблицы, позволяющее сохранить программы, существовавшие до изменения структуры базы данных.

Кроме того, представления дают возможность раличным пользователям по-разному видеть одни и те же данные, возможно, даже в одно и то же время. Это особенно ценно при работе различных категорий пользователей с единой интегрированной базой данных. Пользователям предоставляют только интересующие их данные в наиболее удобной для них форме (окно в таблицу или в любое соединение любых таблиц).

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

SQL — Использование представлений

Дата публикации: 2020-12-11

От автора: представление — это не что иное, как оператор SQL, который хранится в базе данных с соответствующим именем. На самом деле представление является определенным структурированным набором данных таблицы в виде предопределенного SQL-запроса.

Представления SQL могут содержать все строки таблицы или определенные выбранные строки. Представление может быть создано из одной или нескольких таблиц, это зависит от составленного для представления SQL-запроса.

Представления, которые являются типом виртуальных таблиц, позволяют пользователям делать следующее:

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

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

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Обобщать данные из разных таблиц, чтобы использовать их для различных отчетов.

Создание представлений

Представления базы данных создаются с помощью инструкции CREATE VIEW. Представления можно создать из одной таблицы, нескольких таблиц или другого представления.

Пользователь должен иметь соответствующие системные привилегии в зависимости от конкретной реализации.
Основной синтаксис CREATE VIEW следующий:

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL