Order BY Pub_id DESC, Type, Price

FROM Titles

SELECT Pub_id, Type, Title_id, Price

USE Pubs

Приклад використання операторів SELECT для вибірки даних

У цій вправі ми скористаємося операторами SELECT для витягу інформації з бази даних Pubs. Кожний наступний оператор SELECT необхідно будувати на основі попереднього, при цьому інформація, що входить у результуючий набір, усе більше конкретизується.

Витяг всіх даних з таблиці Titles

Уведемо наступні команди Transact-SQL вікні редактору запитів:

USE Pubs

SELECT * FROM Titles

Цей оператор визначає базу даних, де втримується потрібна для перегляду таблиця. Оператор SELECT витягає всі дані з таблиці Titles у базі даних Pubs. Зірочка (*) у списку вибору вказує, що треба вибрати дані із всіх стовпців таблиці. Виконаємо оператор Transact-SQL. Результуючий набір виводиться на вкладці Results.

Одержання даних з певних стовпців таблиці Titles

У вікні редактору запитів уведемо наступний код Transact-SQL:

USE Pubs

SELECT Title_id, Title, Price, Ytd_sales

FROM Titles

Тут оператор SELECT витягає дані зі стовпців Title_id, Title, Price і Ytd_sales бази даних Pub. Виконаємо оператор Transact-SQL. Результуючий набір виводиться на вкладці Results.

Завдання умови, якій повинен відповідати результуючий набір

Уведемо наступні команди Transact-SQL вікні редактору запитів:

USE Pubs

SELECT Titie_id, Title, Price, Ytd_sales

FROM Titles

WHERE Price > 10

Тепер оператор SELECT витягне лише ті рядки, значення поля Price яких перевищує $10. Виконаємо оператор Transact-SQL. Результуючий набір виводиться на вкладці Results.

Завдання порядку, у якому виводиться результуючий набір

Уведемо наступні команди Transact-SQL вікні редактору запитів:

USE Pubs

SELECT Title_id, Title, Price. Ytd_sales

FROM Titles

WHERE Price > 10

ORDER BY Price DESC, Title

Результуючий набір, що поверне цей оператор SELECT, упорядковується спочатку за ціною (по убуванню), а потім по заголовку (по зростанню). Виконаємо оператор Transact-SQL. Результуючий набір виводиться на вкладці Results.

Угруповання даних у результуючому наборі

Уведемо наступні команди Transact-SQL вікні редактору запитів:

USE Pubs

SELECT Type, AVG(Price) AS AvgPrice

FROM Titles

WHERE Price > 10

GROUP BY Type

ORDER BY AvgPrice DESC

У результуючому наборі, що поверне цей оператор SELECT, групуються рядки з однаковими значеннями поля Турe. Рядки, що не відповідають умовам конструкції WHERE, виключаються до початку будь-яких операцій по угрупованню. При угрупованні виконується усереднення значень стовпця Price, а отримане середнє значення вставляється в результуючий набір у вигляді стовпця AvgPrice. Значення стовпця AvgPrice упорядковуються по убуванню. Виконаємо оператор Transact-SQL. Результуючий набір виводиться на вкладці Results.

Створення таблиці для розміщення результуючого набору

Уведемо наступні команди Transact-SQL вікні редактору запитів:

USE Pubs

SELECT Type, AVG(Price) AS AvgPrice

INTO TypeAvgPrice

FROM Titles

WHERE Price > 10

GROUP BY Type

ORDER BY AvgPrice DESC

Оператор SELECT створить нову таблицю за назвою TypeAvgPrice. У стовпцях Турe і AvgPrice розміщаються значення результуючого набору. Виконаємо оператор Transact-SQL. На вкладці Results виводиться повідомлення, з зазначеним числом рядків, на яке вплинуло виконання оператора.

Уведемо і виконаємо наступний оператор Transact-SQL:

SELECT * FROM TypeAvgPrice

Уміст таблиці TypeAvgPrice виводиться на вкладці Results.

Уведемо і виконаємо наступний оператор Transact-SQL:

DROP TABLE TypeAvgPrice

На вкладці Results виводиться повідомлення про успішне завершення команди.

Тепер, коли ви познайомилися з основами роботи оператора SELECT і його різними конструкціями, ми розглянемо більш складні методики роботи із запитами. Одна з них дозволяє об'єднати кілька таблиць із метою одержання результуючого набору, що містить рядки й стовпці із всіх цих таблиць. Інша методика заснована на використанні підзапитів, які являють собою оператори SELECT, вкладені в інші оператори (SELECT, INSERT, UPDATE або DELETE). Усередині підзапитів дозволяються вкладені підзапити. Такі елементи мови Transact-SQL, як CUBE і ROLLUP, дозволяють одержувати зведені дані.

2.Витяг даних за допомогою з'єднань

З'єднання дозволяють витягати дані із двох або більшого числа таблиць на основі логічних зв'язків між таблицями. З'єднання вказує SQL Server, як варто використати дані однієї таблиці для вибору рядків з іншої таблиці.

З'єднання задають у конструкціях FROM або WHERE. Умови з'єднання разом з умовами конструкцій WHERE і HAVING визначають рядки, обрані з основної, зазначеної в конструкції FROM таблиці. Однак визначення умов з'єднання в конструкції FROM дозволяє відокремити їх від інших умов пошуку, наприклад, заданих у конструкції WHERE. Тому рекомендується задавати з'єднання саме цим методом.

Коли єдиний запит посилається на кілька таблиць, всі посилання на стовпці варто визначати точно, Будь-яке ім'я стовпця, що повторюється у двох або більше таблицях у складі одного запиту, необхідно конкретизувати, указавши ім'я таблиці.

Список вибору з'єднання може посилатися на всі стовпці таблиць, що з'єднують, або на деяку їхню підмножину. Не обов'язково, щоб у список вибору потрапили стовпці із всіх таблиць з'єднання. Наприклад, іноді в з'єднанні, що складається із трьох таблиць, одну таблицю використовують у якості 'мосту' між двома іншими; тому в список вибору стовпці з 'серединної' таблиці можуть не потрапити. Хоча в умові з'єднання звичайно використається знак рівності (=), дозволяється застосовувати й інші реляційні оператори або оператори порівняння (або інші предикати). Коли SQL Server обробляє з'єднання, механізм обробки запитів вибирає для цього найбільш ефективний метод (із декількох можливих). Хоча при фізичному виконанні різних з'єднань оптимізація виробляється no-різному, логічна послідовність операцій така:

· застосовуються умови з'єднання з конструкції FROM;

· застосовуються умови з'єднання й умови пошуку з конструкції WHERE;

· застосовуються умови пошуку з конструкції HAVING.

Іноді ця послідовність може впливати на результат запиту, якщо переміщати умови між конструкціями FROM і WHERE.

Не обов'язково привласнювати стовпцям, які використовуються в умові з'єднання, однакове ім'я або тип даних. Однак, якщо типи даних не ідентичні, необхідно, щоб вони були сумісні або SQL Server міг виконати їхнє неявне перетворення. Якщо неявне перетворення типів неможливо, умова з'єднання повинна явно перетворювати типи даних за допомогою функції CAST.

Більшість з'єднань можна переписати у вигляді підзапитів (запитів, вкладених усередині інших запитів), а більшість підзапитів вдається переписати у вигляді з'єднань.

Більшість з'єднань відносяться до внутрішніх або зовнішніх. Внутрішні з'єднання повертають рядки, тільки коли в обох таблицях існує хоча б по одному рядку, що відповідає умові з'єднання. При цьому рядки, для яких немає відповідних рядків з іншої таблиці, виключаються. Зовнішні з'єднання повертають всі рядки, принаймні по одній із зазначених у конструкції FROM таблиць або відображень, які відповідають кожній з умов пошуку, заданих конструкціями WHERE або HAVING. Також припустимі перехресні з'єднання і само-з'єднання.

Внутрішні з'єднання

У внутрішніх з'єднаннях значення стовпців, що з'єднуються, визначаються за допомогою оператора порівняння. У стандарті SQL-92 внутрішні з'єднання задаються в конструкціях FROM або WHERE. Внутрішні - єдиний тип з'єднань, які є у SQL.-92. У наступному операторі SELECT внутрішнє з'єднання використовується для одержання даних з таблиць Publishers і Titles бази даних Pubs:

SELECT t.Title, p.Pub_name

FROM Publishers AS p INNER JOIN Titles AS t

ON p.Pub_id = t.Pub_id

ORDER BY Title ASC

Цей оператор SELECT одержує дані зі стовпця Title таблиці Titles (t) і стовпця Pub_name таблиці Publishers (p). Оскільки в цьому операторі застосовується внутрішнє з'єднання, він повертає тільки ті рядки, для яких у стовпцях з'єднання (p.Pub_id і t.Pub_id) є значення, які відповідають одне одному.

Зовнішні з'єднання

SQL Server підтримує три типи зовнішніх з'єднань: ліві, праві і повні. Ліве зовнішнє з'єднання посилається на всі рядки лівої таблиці, а праве зовнішнє з'єднання - на всі рядки правої таблиці. Повне зовнішнє з'єднання повертає всі рядки з обох таблиць.

Використання лівих зовнішніх з'єднань

Результуючий набір, згенерований оператором SELECT, у який входить ліве зовнішнє з'єднання, складається із всіх рядків таблиці, на яку посилається конструкція LEFT OUTER JOIN, розташований ліворуч від цієї конструкції. З таблиці, що розташована праворуч, витягаються тільки ті рядки, які відповідають умові з'єднання. У наступному прикладі в операторі SELECT для одержання імен, прізвищ авторів і (де можливо) назв всіх видавництв, розташованих у тих же місцях, де проживають автори, застосовується ліве зовнішнє з'єднання:

USE Pubs

SELECT a.Au_fname. a.Au,lname, p.Pub_name

FROM Authors a LEFT OUTER JOIN Publishers p

ON a.City = p.City

ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC

У результуючому наборі цього запиту наведено список імен всіх авторів з таблиці Authors. Однак у нього ввійдуть тільки ті видавництва, які розташовані в тих же містах, де живуть автори. Якщо видавництво розташоване в якомусь іншім місті, у стовпець Pub_name результуючого набору повертається порожнє значення.

Використання правих зовнішніх з'єднань

Результуючий набір, згенерований оператором SELECT, у який входить праве зовнішнє з'єднання, складається із всіх рядків таблиці, що розташована праворуч від конструкції RIGHT OUTER JOIN. З таблиці, що розташована ліворуч, витягаються тільки ті рядки, які відповідають умові з'єднання.

У наступному прикладі в операторі SELECT праве зовнішнє з'єднання застосовується для одержання списку назв видавництв і імен із прізвищами авторів, якщо проживають у тому ж місті, де розташоване видавництво:

USE Pubs

SELECT a.Au_fname, a.Au_lname, p.Pub_name

FROM Authors a RIGHT OUTER JOIN Publishers p

ON a.City = p.City

ORDER BY p.Pub_name ASC, a.Au_lname ASC, a.Au_fname ASC

У результуючий набір цього запиту ввійдуть назви всіх видавництв із таблиці Publishers, а також імена тих авторів, які живуть у тих же містах, де розташовані видавництва. Якщо автор живе не в тому місті, де розташоване видавництво, то в стовпці результуючого набору Au_fname і Au_lname повертаються порожні значення.

Використання повних зовнішніх з'єднань

Результуючий набір, згенерований оператором SELECT, у якому є повне зовнішнє з'єднання, складається із всіх рядків обох таблиць незалежно від того, чи зазначені в таблицях відповідні значення (задані умовами з'єднаннями). У наступному операторі SELECT повне зовнішнє з'єднання використовується для одержання списку назв видавництв, імен і прізвищ авторів:

USE Pubs

SELECT a.Au_fname, a.Au_lname, p.Pub_name

FROM Authors a FULL OUTER JOIN Publishers p

ON a.City = p.City

ORDER BY p.Pub_name ASC, a.Au.lname ASC, a.Au_fname ASC

У результуючому наборі цього запиту перераховані назви всіх видавництв із таблиці Publishers і всіх авторів з таблиці Authors. Якщо автор живе не в тому місті, де розташоване видавництво, у стовпці результуючого набору Au_fname і Au_lname повертається порожнє значення. Якщо видавництво розташоване не в ттому же місті, де живе автор, у стовпець Pub_name результуючого набору також повертається порожнє значення. При відповідності умові з'єднання заповнюються всі стовпці результуючого набору.

3.Визначення підзапитів усередині операторів SELECT

Підзапитом називається оператор, що повертає єдине значення SELECT, вкладений в інший оператор SELECT, INSERT, UPDATE, DELETE або в інший підзапит. Підзапит дозволяється застосовувати в будь-якому місці, де дозволене використання виразу. Підзапит також називається внутрішнім запитом або внутрішньою вибіркою, а утримуючий підзапит оператор - зовнішнім запитом або зовнішньою вибіркою. У наступному прикладі підзапит вкладений у конструкцію WHERE зовнішнього оператора

SELECT:

USE Northwind

SELECT ProductName

FROM Products

WHERE UnitPrice =

(

SELECT UnitPrice

FROM Products

WHERE ProductName = 'Sir Rodney"s Scones'

)

Убудований оператор SELECT спочатку визначає значення UnitPrice для товару за назвою ’ Sir Rodney's Scones ’, який дорівнює $10, Після цього значення $10 використовується в зовнішньому операторі SELECT для добування імен всіх продуктів, ціна яких $10. Якщо таблиця фігурує тільки в підзапиті, але не в зовнішньому запиті, то стовпці цієї таблиці не вдається включити у вихідну інформацію (у список вибору зовнішнього запиту). У деяких операторах Transact-SQL підзапит обробляється так, ніби це був незалежний запит і його результати підставляються в зовнішній запит.

Типи підзапитів

Підзапити можна задавати у всіх місцях оператора SELECT. Однак оператори, у складі яких є підзапит, як правило складені в одному з наступних форматів:

· WHERE <вираз> [NOT] IN (<підзапит>)

· WHERE <вираз> <оператор_порівняння> [ANY | ALL] (<підзапит>)

· WHERE [NOT] EXISTS (<підзапит>)

Підзапити із ключовими словами IN або NOT IN

Результатом підзапиту із ключовим словом IN (або NOT IN) є список, що складається з нуля або більшої кількості значень. Результат, що повертає підзапит, використовується зовнішнім запитом.

У наступному прикладі підзапит вкладений у конструкцію WHERE і використається ключове слово IN:

USE Pubs

SELECT Pub_name

FROM Publishers

WHERE Pub_id IN

(

SELECT Pub_id

FROM Titles

WHERE Type = 'business'

)

Спочатку внутрішній запит повертає номери ідентифікаторів видавництв, що випустили літературу, що визначена типом business (1389 і 0736). Потім ці значення підставляються в зовнішній запит, що знаходить назви, що відповідають номерам ідентифікаторів з таблиці Publishers. Підзапити із ключовими словами NOT IN також повертають список, що складається з нуля або більшої кількості значень. Ці запити абсолютно аналогічні підзапитам із ключовим словом IN і відрізняються тільки тим, що ключове слово IN замінене на NOT IN.

Підзапити з операторами порівняння

В операторах порівняння, які використовуються з підзапитами, дозволяється застосовувати ключові слова ALL або ANY. Ключове слово SOME у стандарті SQL-92 уважається еквівалентом ANY. Підзапити в операторах порівняння повертають нуль або більше значень і можуть містити в собі конструкції GROUP BY або HAVING. Ці підзапити можна перевизначити за допомогою ключового слова EXISTS. Ключові слова ALL і ANY порівнюють скалярне значення з набором значень одного стовпця. Ключове слово ALL застосовується до всіх значень, а ключове слово ANY - як мінімум до одному. У наступному прикладі ключове слово ANY використовується з оператором порівняння ’більше’ (>):

USE Pubs

SELECT Title

FROM Titles

WHERE Advance > ANY

(

SELECT Advance

FROM Publishers INNER JOIN Titles

ON Titles. Pub_id = Publisher. Pub_id

AND Pub_name = ' Algodata Infosystems '

)

Цей оператор знаходить видання, які авансовані сумою, що перевищує мінімальну суму авансу, сплаченого Algodata Infosystems (у цьому випадку він становить $5 000). Конструкція WHERE у зовнішньому операторі SELECT містить підзапит, за допомогою з'єднання який витягає суми авансу для Algodata Infosystems. Після цього мінімальна сума авансу використовується для визначення видань, які необхідно витягти з таблиці Titles.

Підзапити із ключовими словами EXISTS і NOT EXISTS

Коли підзапит містить ключове слово EXISTS, він функціонує як перевірка наявності тієї або іншої сутності. Конструкція WHERE зовнішнього запиту перевіряє, є чи рядки, які повертає підзапит. Цей підзапит насправді не видає ніяких даних, замість цього він повертає значення TRUE або FALSE. У наступному прикладі конструкція WHERE із зовнішнього оператора SELECT містить підзапит і використовує ключове слово EXISTS:

USE Pubs

SELECT Pub_name

FROM Publishers

WHERE EXISTS

(

SELECT * FROM Titles

WHERE Titles. Pub_id = Publishers. Pub_id

AND Type = 'business'

)

Щоб визначити результат цього запиту, необхідно по черзі розглянути назву кожного видавництва. У цьому випадку першим буде видавництво Algodata Infosystems з ідентифікаційним номером 1389. Чи є в таблиці Titles рядки, значення поля Pub_id яким дорівнює 1 389, а тип - business? Якщо так, то Algodata Infosystems повинне бути серед обраних значень. Цей процес повторюється для кожної назви видавництва. Ключові слова NOT EXISTS працюють аналогічно EXISTS за винятком того, що умова конструкції WHERE, де використовується NOT EXISTS, виконується, коли підзапит не повертає жодного рядка.

Дата добавления: 2015-06-29