Изменения представлений
Как мы упоминали ранее, текущая версия DB Browser (на момент написания этой книги) не поддерживает изменение существующих представлений. В качестве альтернативы в SQLite необходимо создать новое представление и присвоить ему новое имя или удалить существующее представление. Чтобы изменить представление в SQLite, необходимо перейти на вкладку Execute SQL (Выполнить SQL-запрос), щелкнуть правой кнопкой мыши на представлении и скопировать оператор CREATEVIEW. Затем можно вставить результаты на вкладку Execute SQL (Выполнить SQL-запрос), внести изменения и снова запустить оператор на выполнение.
Примечание
При повторном запуске представления, если исходное представление все еще сохранено в разделе Views (Представления), необходимо переименовать новое представление (или удалить существующее). Иначе появится сообщение об ошибке, указывающее, что представление уже существует. Имя представления должно быть уникальным.
Практические задания
• Измените представление V_AvgTotal, чтобы в нем не было функции ROUND().
• Из главы 8 выберите другой пример подзапроса, преобразуйте часть подзапроса в представление и запустите его снова.
Соединенные представления
Представления очень полезны для хранения длинных или сложных запросов. В главе 6 мы рассказывали о соединениях. Соединения помогают создавать связи между таблицами, обычно это довольно длинные запросы, поэтому их можно сохранить в качестве представлений, чтобы не вводить заново. В последней главе, посвященной подзапросам, мы использовали вместе таблицы invoice_items и tracks, чтобы выяснить, какие песни из таблицы tracks никогда не были заказаны. Нам бы пригодилось представление, связывающее эти две таблицы. На него можно было бы ссылаться в подзапросе либо просто сохранить как представление, чтобы использовать при необходимости.
Чтобы создать представление для этих двух таблиц, сначала необходимо решить, какое соединение мы хотим использовать. Поскольку мы ищем коррелирующие поля, то будем использовать условие INNERJOIN для таблиц invoice_items и tracks.
SELECT
ii.InvoiceId,
ii.UnitPrice,
ii.Quantity,
t. Name,
t. Composer,
t. Milliseconds
FROM
invoice_items ii
INNER JOIN
tracks t
ON ii.TrackId = t.TrackId
Напоминание
При создании соединения для каждой таблицы мы используем короткие псевдонимы, а затем, выявив общее поле, связываем таблицы друг с другом. В нашем случае зададим t для таблицы tracks и ii для таблицы invoice_items, так как i уже используется для таблицы invoices.
Теперь, когда соединение создано, мы можем добавить еще одну строку в начало запроса, чтобы сохранить его как представление.
CREATE VIEW V_Tracks_InvoiceItems AS
SELECT
ii.InvoiceId,
ii.UnitPrice,
ii.Quantity,
t. Name,
t. Composer,
t. Milliseconds
FROM
invoice_items ii
INNER JOIN
tracks t
ON ii.TrackId = t.TrackId
Теперь возьмем из главы 6 соединение, объединяющее таблицы invoices, customers и employees, и сохраним как представление.
CREATE VIEW V_inv_cus_emp AS
SELECT
i. InvoiceId,
i. InvoiceDate,
i. Total,
i. CustomerId,
c. FirstName,
c. LastName,
c. SupportRepId,
e. EmployeeId,
e. LastName,
e. FirstName,
e. Title
FROM
invoices AS i
INNER JOIN
customers AS c
ON
i. CustomerId = c.CustomerId
INNER JOIN
employees AS e
ON
e. EmployeeId = c.SupportRepId
ORDER BY
InvoiceDate
Примечание
В главе 6 мы немного изменили это соединение. Из условия SELECT мы удалили символ *. Смысл представлений — показать именно то, что необходимо. Таким образом, мы включили только необходимые поля из таблиц invoices, customers и employees.
Мы видим, что оба этих соединения сохранены как представления на вкладке Database Structure (Структура базы данных) (рис. 126).
Рис. 126
Теперь, когда у нас имеются эти два представления, мы используем их во внутреннем соединении:
SELECT *
FROM
V_Tracks_InvoiceItems ii
INNER JOIN
V_inv_cus_emp ice
ON
ii.InvoiceId = ice.InvoiceId
С помощью приведенного выше запроса, объединяющего пять таблиц, мы можем узнать, какие треки были проданы каждым сотрудником и какому клиенту. Агрегируя данные, мы также определим, какой трек продавался лучше всего, какой общий доход был получен от продажи трека, а также информацию о сотрудниках, осуществивших продажи. Теперь мы можем при желании сохранить это объединение как представление.
Примечание
Соединенные представления будут работать только в том случае, если ключевые поля, общие для всех этих таблиц, были включены в первоначальное соединение.
Удаление представлений с помощью оператора DROP
Ранее мы уже описали один способ удаления представления — щелчок правой кнопкой мыши по имени представления на вкладке Database Structure (Структура базы данных). Представление также можно удалить с помощью команды DROP. Это выглядит следующим образом:
DROP VIEW
V_AvgTotal
Она удалит представление V_AvgTotal. Из базы данных удаляется только представление — синтаксис DROPVIEW не влияет на данные.
Примечание
Если вы удалите представление, на которое ссылаются другие представления, эти представления больше не будут работать.
Внимание
DROP — это команда для удаления любых элементов. Она может также удалить из базы данных таблицу. В следующей главе вы изучите инструменты, с помощью которых можно редактировать или удалять данные без возможности восстановления. Как вы уже знаете, представление также можно удалить из базы данных, щелкнув по нему правой кнопкой мыши и выбрав опцию Remove View (Удалить представление).
Контрольные вопросы
Для этого задания используем запрос из главы 8:
SELECT
BillingCity,
AVG(Total) AS [City Average],
(SELECT
avg(total)
from
invoices) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните представление с именем V_GlobalAverage.
2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.
3. Сохраните этот новый запрос как представление с именем V_CityAvgVsGlobalAvg.
4. Удалите представление V_GlobalAverage. Как будет работать V_CityAvgVsGlobalAvg?
Резюме
• Представления — это виртуальные запросы, созданные с помощью SQL, на которые можно ссылаться в других запросах.
• Представления создаются посредством добавления в начало запроса следующего условия: CREATEVIEWV_VIEWNAMEAS.
• Представления особенно полезны при написании длинных запросов.
• Представления можно изменять и удалять с помощью функций из вашей версии СУБД или с помощью команд SQL.
Глава 10. DML — язык управления данными
Все операторы SQL, которые вы уже изучили, использовались для извлечения информации из базы данных или создания производных данных на основе имеющихся значений. Эта глава познакомит вас с языком управления данными (DML) и с операторами SQL, которые используются для изменения данных, хранящихся в таблицах базы данных.
Внимание
С этими командами лучше попрактиковаться в «песочнице», например на нашей учебной базе данных. Использование DML в рабочей базе с реальными данными может привести к фатальным последствиям.
Чем различаются анализ данных и управление базами данных
До сих пор мы говорили о том, что основная цель SQL-запросов — извлечение существующих данных из базы данных и преобразование их в полезную информацию (на примере компании sTunes). Но, как мы упоминали во введении, SQL выполняет гораздо более сложные задачи, чем просто превращение данных в значимую информацию. Существуют такие специалисты, как разработчики баз данных и администраторы баз данных, контролирующие рост, улучшение и управление базой данных компании. Функции этих специалистов различаются в зависимости от компании и реализации базы данных. Даже опытные пользователи SQL расходятся во мнениях о том, является ли язык DML (Data Manipulation Language — язык управления данными) отдельной областью изучения или его следует изучать совместно с операторами SQL, предназначенными только для извлечения информации.
На мой взгляд,
новичкам могут быть непонятны различия в ролях тех, кто работает с базами данных. Язык DML отлично подходит для администрирования и разработки баз данных. Если компания небольшая и в ней используется только одна база данных, роли аналитика, разработчика и администратора могут быть назначены одному человеку, и этим человеком можете быть вы! Так что даже если ваша основная цель — просто научиться писать необходимые запросы, важно понять работу DML.
Данными в РСУБД можно управлять с помощью следующих DML-команд: INSERT, UPDATE и DELETE. Как следует из названий, эти команды**** могут использоваться для добавления, изменения и удаления данных из таблиц в базе данных. Для нашей базы sTunes мы продемонстрируем, как выполнить задание от руководства компании на добавление новых исполнителей к нашей музыкальной базе, добавление новых записей, а затем их удаление.
Примечание
В DB Browser при внесении любых изменений в базу данных sTunes с использованием DML отображается сообщение с предложением сохранить изменения или закрыть файл базы данных без сохранения изменений. Вы можете сделать копию исходного файла базы данных, чтобы попрактиковаться в работе с базой данных, не опасаясь потерять оригинал.