Мартин Грубер - Понимание SQL (991940), страница 34
Текст из файла (страница 34)
Создайте таблицу с именем Cityorders. Она должна содержать такие же поля onum,amt, и snum что и таблица Порядков, и такие же поля cnum и city что и таблица Заказчиков, так что порядок каждого заказчика будет вводиться в эту таблицу вместес его городом. Поле оnum будет первичным ключом Cityorders. Все поля вCityorders должны иметь ограничения при сравнении с таблицами Заказчиков и Порядков. Допускается, что родительские ключи в этих таблицах уже имеют соответствующие ограничения.2. Усложним проблему. Переопределите таблицу Порядков следующим образом: добавьте новый столбец с именем prev, который будет идентифицирован для каждогопорядка, поле onum предыдущего порядка для этого текущего заказчика.
Выполните это с использованием внешнего ключа ссылающегося на саму таблицу Порядков. Внешний ключ должен ссылаться также на поле cnum заказчика,обеспечивающего определенную предписанную связь между текущим порядком иссылаемым.(См. Приложение A для ответов.)20ВВЕДЕНИЕ:ПРЕДСТАВЛЕНИЯПРЕДСТАВЛЕНИЕ (VIEW) — ОБЪЕКТ ДАННЫХ, КОТОРЫЙ не содержит никакихданных его владельца. Это — тип таблицы, чье содержание выбирается из другихтаблиц с помощью выполнения запроса. Поскольку значения в этих таблицах меняются, то автоматически, их значения могут быть показаны представлением.В этой главе вы узнаете, что такое представления, как они создаются и немногооб их возможностях и ограничениях. Использование представлений, основанных наулучшеных средствах запросов, таких как обьединение и подзапрос, разработаныхочень тщательно, в некоторых случаях даст больший выиигрыш по сравнениию с запросами.ЧТО ТАКОЕ ПРЕДСТАВЛЕНИЕ?Типы таблиц, с которыми вы имели дело до сих пор, назывались — базовымитаблицами.
Это — таблицы, которые содержат данные. Однако имеется другой видтаблиц — представления. Представления — это таблицы, чье содержание выбирается или получается из других таблиц. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных.Представления подобны окнам, через которые вы просматриваете информацию(как она есть, или в другой форме, как вы потом увидите), которая фактически хранится в базовой таблице. Представление — это фактически запрос, который выполняется всякий раз, когда представление становится темой команды. Вывод запроса приэтом в каждый момент становится содержанием представления.КОМАНДА CREATE VIEWВы создаете представление командой CREATE VIEW.
Она состоит из словCREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления, которое нужносоздать, слова AS (КАК), и далее запроса, как в следующем примере:CREATE VIEW LondonstaffAS SELECT *FROM SalespeopleWHERE city = 'London';Теперь Вы имеете представление, называемое Londonstaff. Вы можете использовать это представление точно так же, как и любую другую таблицу. Она может бытьзапрошена, модифицирована, вставлена в, удалена из, и соединена с, другими таблицами и представлениями.
Давайте сделаем запрос такого представления (выводпоказан в Рисунке 20.1):SELECT *FROM Londonstaff;=============== SQL Execution Log ============| SELECT *|| FROM Londonstaff;|| ==============================================||snumsnamecitycomm|| ------------------------------- ||1001PeelLondon0.1200 ||1004MotikaLondon0.1100 |===============================================Рисунок 20.1: Представление LondonstaffКогда вы приказываете SQL выбрать (SELECT) все строки (*) из представления,он выполняет запрос содержащий в определении — Loncfonstaff, и возвращает все изего вывода.Имея предикат в запросе представления, можно вывести только те строки изпредставления, которые будут удовлетворять этому предикату.
Вы могли бы вспомнить, что в Главе 15, вы имели таблицу, называемую Londonstaff, в которую вывставляли это же самое содержание (конечно, мы понимаем, что таблица — не слишком велика. Если это так, вы будете должны выбрать другое имя для вашего представления). Преимущество использования представления, по сравнению с основнойтаблицы, в том, что представление будет модифицировано автоматически всякий раз,когда таблица, лежащая в его основе изменяется.Содержание представления не фиксировано, и переназначается каждый раз когда вы ссылаетесь на представление в команде. Если вы добавите завтра другого,живущего в Лондоне продавца, он автоматически появится в представлении.Представления значительно расширяют управление вашими данными. Это —превосходный способ дать публичный доступ к некоторой, но не всей информации втаблице. Если вы хотите, чтобы ваш продавец был показан в таблице Продавцов, нопри этом не были показаны комиссии других продавцов, вы могли бы создать представление с использованием следующего оператора (вывод показан в Рисунке 20.2)CREATE VIEW SalesownAS SELECT snum, sname, cityFROM Salespeople:=============== SQL Execution Log ============| SELECT *|| FROM Salesown;|| ==============================================||snumsnamecity|| ------------------------||1001PeelLondon||1002SerresSan Jose||1004MotikaLondon||1007RifkinBarcelona||1003AxelrodNew York|===============================================Рисунок 20.2: Представление SalesownДругими словами, это представление — такое же, как для таблицы Продавцов,за исключением того, что поле comm, не упоминалось в запросе, и следовательно небыло включено в представление.МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЙПредставление может теперь изменяться командами модификации DML, но модификация не будет воздействовать на само представление.
Команды будут на самомделе перенаправлены к базовой таблице:UPDATE SalesownSET city = 'Palo Alto'WHERE snum = 1004;Его действие идентично выполнению той же команды в таблице Продавцов. Однако, если значение комиссионных продавца будет обработано командой UPDATEUPDATE SalesownSET comm = .20WHERE snum = 1004;она будет отвергнута, так как поле comm отсутствует в представлении Salesown. Этоважное замечание, показывающее что не все представления могут быть модифицированы.Мы будем исследовать проблемы модификации представлений в Главе 21.ИМЕНОВАНИЕ СТОЛБЦОВВ нашем примере, поля наших представлений имеют свои имена, полученыепрямо из имен полей основной таблицы.
Это удобно. Однако, иногда вам нужно снабжать ваши столбцы новыми именами:* когда некоторые столбцы являются выводимыми, и проэтому не имеющимиимен.* когда два или более столбцов в объединении имеют те же имена, что в их базовой таблице.Имена, которые могут стать именами полей, даются в круглых скобках (), послеимени таблиц. Они не будут запрошены, если совпадают с именами полей запрашиваемой таблицы. Тип данных и размер этих полей будут отличаются от запрашиваемых полей которые "передаются" в них. Обычно вы не указываете новых имен полей,но если вы все таки сделали это, вы должны делать это для каждого поля в представлении.КОМБИНИРОВАНИЕ ПРЕДИКАТОВ ПРЕДСТАВЛЕНИЙ ИОСНОВНЫХ ЗАПРОСОВ В ПРЕДСТАВЛЕНИЯХКогда вы делаете запрос представления, вы собственно, запрашиваете запрос.Основной способ для SQL обойти это, — объединить предикаты двух запросов в один.Давайте посмотрим еще раз на наше представление с именем Londonstaff:CREATE VIEW LondonstaffAS SELECT *FROM SalespeopleWHERE city = 'London';Если мы выполняем следующий запрос в этом представленииSELECT *FROM LondonstaffWHERE comm > .12;он такой же, как если бы мы выполнили следующее в таблице Продавцов:SELECT *FROM SalespeopleWHERE city = 'London' AND comm > .12;Это прекрасно, за исключением того, что появляется возможная проблема спредставлением.
Имеется возможность комбинации из двух полностью допустимыхпредикатов и получения предиката который не будет работать. Например, предположим, что мы создаем (CREATE) следующее представление:CREATE VIEW Ratingcount (rating, number)AS SELECT rating, COUNT (*)FROM CustomersGROUP BY rating;Это дает нам число заказчиков, которые мы имеем для каждого уровня оценки(rating). Вы можете затем сделать запрос этого представления чтобы выяснить, имеется ли какая-нибудь оценка, в настоящее время назначенная для трех заказчиков:SELECT *FROM RatingcountWHERE number = 3;Посмотрим, что случится, если мы скомбинируем два предиката:SELECT rating, COUNT (*)FROM CustomersWHERE COUNT (*) = 3GROUP BY rating;Это недопустимый запрос.
Агрегатные функции, такие как COUNT (СЧЕТ), не могут использоваться в предикате. Првильным способом при формировании вышеупомянутого запроса, конечно же будет следующий:SELECT rating, COUNT (*)FROM CustomersGROUP BY rating;HAVING COUNT (*) = 3;Но SQL может не выполнить превращения. Может ли равноценный запрос вместо запроса Ratingcount потерпеть неудачу? Да может! Это — неоднозначная областьSQL, где методика использования представлений может дать хорошие результаты.Самое лучшее что можно сделать в случае, когда об этом ничего не сказано ввашей системной документации, так это попытка в ней разобраться.
Если командадопустима, вы можете использовать представления чтобы установить некоторые ограничения SQL в синтаксисе запроса.ГРУППОВЫЕ ПРЕДСТАВЛЕНИЯГрупповные представления — это представления, наподобии запросаRatingcount в предыдущем примере, который содержит предложение GROUP BY, иликоторый основывается на других групповных представлениях.Групповые представления могут стать превосходным способом обрабатыватьполученную информацию непрерывно.
Предположим, что каждый день вы должныследить за порядком номеров заказчиков, номерами продавцов принимающих порядки, номерами порядков, средним от порядков, и общей суммой приобретений в порядках.Чем конструировать каждый раз сложный запрос, вы можете просто создатьследующее представление:CREATE VIEW TotalfordayAS SELECT odate, COUNT(DISTINCT cnum), COUNT(DISTINCT snum),COUNT(onum), AVG(amt), SUM(amt)FROM OrdersGROUP BY odate;Теперь вы сможете увидеть всю эту информацию с помощью простого запроса:SELECT *FROM Totalforday;Как мы видели, SQL запросы могут дать вам полный комплекс возможностей, такчто представления обеспечивают вас чрезвычайно гибким и мощным инструментомчтобы определить точно, как ваши данные могут быть использованы.Они могут также делать вашу работу более простой, переформатируя данныеудобным для вас способом и исключив двойную работу.ПРЕДСТАВЛЕНИЯ И ОБЬЕДИНЕНИЯПредставления не требуют, чтобы их вывод осуществлялся из одной базовойтаблицы.
Так как почти любой допустимый запрос SQL может быть использован впредставлении, он может выводить информацию из любого числа базовых таблиц,или из других представлений.Мы можем, например, создать представление, которое показывало бы порядкипродавца и заказчика по имени:CREATE VIEW NameordersAS SELECT onum, amt, a.snum, sname, cnameFROM Orders a, Customers b, Salespeople cWHERE a.cnum = b.cnum AND a.snum = c.snum;Теперь вы можете выбрать (SELECT) все порядки заказчика или продавца (*),или можете увидеть эту информацию для любого порядка.Например, чтобы увидеть все порядки продавца Rifkin, вы должны ввести следующий запрос (вывод показан в 20.3 Рисунке):SELECT *FROM NameordersWHERE sname = 'Rifkin';=============== SQL Execution Log ==============| SELECT *|| FROM Nameorders|| WHERE sname = 'Rifkin';|| =============================================== ||onumamtsnumsnamecname|| ----------------- ------------- ||300118.691007 RifkinCisneros ||30061098.161007 RifkinCisneros |================================================Рисунок 20.3: Порядки Rifkin показаные в NameordersВы можете также объединять представления с другими таблицами, или базовыми таблицами или представлениями, поэтому вы можете увидеть все порядкиAxelrodа и значения его комиссиионных в каждом порядке:SELECT a.sname, cname, amt commFROM Nameorders a, Salespeople bWHERE a.sname = 'Axelrod' AND b.snum = a.snum;Вывод для этого запроса показывается в Рисунке 20.4.В предикате, мы могли бы написать — "WHERE a.sname = ’Axelrod' AND b.sname= ’Axelrod’", но предикат, который мы использовали здесь, более общеупотребительный.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
