Вибірка даних з бази даних sql server icon

Вибірка даних з бази даних sql server




Скачати 208.31 Kb.
НазваВибірка даних з бази даних sql server
Дата конвертації31.03.2013
Розмір208.31 Kb.
ТипДокументи
1. /Конспект лекций/Использование SQL Server Management Studio.doc
2. /Конспект лекций/Лекция 0. Знайомство з SQL Server 2005.doc
3. /Конспект лекций/Лекция 1. Створення й керування базою даних SQL Server (short).doc
4. /Конспект лекций/Лекция 2. Синтаксичн_ елементи мови.doc
5. /Конспект лекций/Лекция 3. Виб_рка даних з бази даних.doc
6. /Конспект лекций/Лекция 4. Модиф_кац_я даних у базах даних SQL Server.doc
7. /Конспект лекций/Лекция 5. Збережен_ процедури.doc
8. /Конспект лекций/Лекция 5. Створення тригер_в _ керування ними ними.doc
9. /Конспект лекций/Мова SQL.Doc
10. /Конспект лекций/Создание триггеров и управление ими.doc
11. /Конспект лекций/Ц_л_сн_сть даних (short).doc
12. /Лабораторна робота малий формат.doc
Что такое sql server
Лекція Основи роботи з Microsoft sql| Server| 2005 Питання лекції Основні
Лекція Створення І керування базою даних sql server
Синтаксичні елементи мови Transact-sql
Вибірка даних з бази даних sql server
Модифікація даних у базах даних sql server
Збережені процедури
Створення|створіння| тригерів|трігерів| і керування ними (Тут додати що таке тригер і як він використовується)
Курс лекцій ( частина 1 ) для студентів спеціальності
Создание триггеров и управление ими
Цілісність даних
Методичні рекомендації для виконання лабораторних робіт для студентів спеціальності

Вибірка даних з бази даних SQL Server


Оператор SELECT дозволяє витягати дані із БД SQL Server і представляти їх користувачеві у вигляді одного або декількох результуючих наборів. Результуючий набір - це дані в табличній формі, які отримані в результаті виконання оператора SELECT. Подібно таблиці, результуючий набір складається з рядків і стовпців.

Основи оператора SELECT

Оператор SELECT мови Transact-SQL дозволяє одержувати існуючі дані з бази даних SQL Server. Більшість операторів SELECT описують чотири головних властивості результуючого набору:

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

  • таблицю, з якої витягаються дані для формування результуючого набору;

  • умови, яким повинні відповідати рядки вихідної таблиці, щоб потрапити в результуючий набір;

  • послідовність упорядкування рядків у результуючому наборі.

У наступному прикладі оператор SELECT одержує з таблиці Products ідентифікатор, ім'я й ціну за одиницю будь-якого продукту вартістю вище $40.

SELECT ProductID, ProductNaine, UnitPrice

FROM Products

WHERE UnitPrice > 40

ORDER BY UnitPrice ASC


Конструкція SELECT у цьому прикладі визначає стовпці, з яких необхідно витягти значення, а конструкція FROM - таблицю, у якій перебувають ці стовпці. Конструкція WHERE обмежує результуючий набір тими продуктами, значення UnitPrice яким більше $40. Конструкція ORDER BY задає сортування результуючого набору по зростанню на основі значення стовпця UnitPrice. Повний синтаксис оператора SELECT складний, однак в загальному вигляді головні конструкції можна записати в такий спосіб:

SELECT список_вибору

[INTO ім'я_нової_таблиці]

FROM список_таблиць

[WHERE умови_пошуку]

[GROUP BY угруповання_по_списку]

[HAVING умови_пошуку]

[ORDER BY поле_для_сортування [ASC DESC]]


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


Конструкція SELECT

Конструкція SELECT складається із ключового слова SELECT і списку вибору. Список вибору - це набір виразів, розділених комами. Кожний вираз визначає стовпець результуючого набору. Порядок стовпців результуючого набору визначається послідовністю виразів списку вибору.


Використання ключових слів у списку вибору

У списку вибору також можна вказати ключові слова, які визначають кінцевий формат результуючого набору.


Ключове слово DISTINCT

Ключове слово DISTINCT забороняє вивід у результуючому наборі повторюваних рядків. Наприклад, у таблиці Orders бази даних Northwind зберігаються повторювані значення у стовпці ShipCity. Наступний код дозволяє одержати список значень ShipCity без дублікатів:

SELECT DISTINCT ShipCity, ShipRegion

FROM Orders

ORDER BY ShipCity


Ключове слово TOP n

Ключове слово ТОР п задає перші п рядків результуючого набору, які необхідно повернути. Якщо задано параметр ORDER BY, вибір рядків виконується після впорядкування результуючого набору. Значення п указує число рядків, які повертаються, (якщо не визначене ключове слово PERCENT). Якщо задано ключове слово PERCENT, то п - це відсоток рядків, що повертаються , від загального числа рядків у результуючому наборі.

Наприклад, у наступному прикладі оператор SELECT повертає перші 10 упорядкованих за алфавітом міст із таблиці Orders.

SELECT DISTINCT TOP 10 ShipCity, ShipRegion

FROM Orders

ORDER BY ShipCity


Ключове слово AS

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

ім'я_таблиці AS псевдонім_таблиці

ім'я_таблиці псевдонім_таблиці

У наступному прикладі таблиці Publishers привласнюється псевдонім р:

USE pubs

SELECT p.pub_id, p.pub_name

FFOM publishers AS p


Якщо таблиці привласнений псевдонім, то у всіх явних посиланнях на таблицю в операторах Transact-SQL необхідно використовувати псевдонім, а не ім'я таблиці.


Типи інформації в списку вибору

У списку вибору дозволяється задавати різні типи інформації, наприклад прості вирази або скалярні підзапити. У наступному прикладі показано кілька елементів, які можна включити у список вибору:

SELECT FirstName + ' ' + LastNane AS "Employee Name",

IDENTITYCOL AS "Employee ID",

HomePhone, Region

FROM Northwind.dbo.Employees

ORDER BY LastName, FirstNarne ASC


Цей оператор поміщає в один стовпець імена й прізвища працівників, розділені пробілом. Ім'я стовпця, у якому зберігаються імена працівників - Employee Name. У результуючий набір також увійде стовпець із ідентифікатором, який буде названий Employee ID, а також стовпці HomePhone і Region. Результуючий набір упорядкований спочатку за прізвищем, а потім за іменем.


Конструкція INTO

Конструкція INTO дозволяє вказати, що для результуючого набору буде створена нова таблиця, ім'я якої задане цією конструкцією. За допомогою оператора SELECT...INTO вдається об'єднати дані з декількох таблиць або подань в одну таблицю. Його можна використовувати для створення нової таблиці з даними, обраними на зв'язаному сервері. У наступному прикладі оператор SELECT витягає значення зі стовпців FirstName і LastName таблиці Employees:

SELECT FirstName, LastName

INTO EmployeeNames

FROM Employees


Згенерирований результуючий набір створює таблицю EmployeeNames. У новій таблиці стовпці FirstName і LastName будуть містити значення з таблиці Employees. Результуючий набір не виводиться на вкладці Results, якщо не виконати явний запит до нової таблиці.


Конструкція FROM

Конструкцію FROM необхідно поміщати в кожному операторі SELECT, що витягає дані з таблиць або відображень. Ця конструкція дозволяє задати список таблиць і відображень, на стовпці яких посилаються список вибору і конструкція WHERE. Цим таблицям і відображенням можуть бути привласнені псевдоніми в конструкції AS. Конструкція FROM, крім того, дозволяє з'єднувати таблиці, задаючи умови з'єднання в конструкції JOIN. Конструкція FROM являє собою список імен таблиць, відображень і конструкцій JOIN, розділених комами. У наступному прикладі в операторі SELECT конструкція FROM задає таблицю Shippers:

SELECT * FROM Shippers


Конструкцію FROM також використовують і для визначення з'єднань між двома таблицями або відображеннями.


Конструкції WHERE, GROUP BY і HAVING

В операторі SELECT конструкції WHERE і HAVING визначають рядки вихідної таблиці, які необхідні для побудови результуючого набору. Конструкції WHERE і HAVING виконують роль фільтрів. Вони задають набір умов пошуку - для побудови результуючого набору вибираються лише ті рядочки, які відповідають умовам пошуку. Наприклад, в операторі SELECT конструкція WHERE повертає лише ті рядки, де район зазначений як штат Вашингтон (WA):

SELECT Customerl, CompanyName

FROM Northwind.dbo.Customers

WHERE Region = 'WA'


Конструкція HAVING, як правило (але не обов'язково), використовується разом з конструкцією GROUP BY. Конструкція HAVING задає додаткові фільтри, які застосовуються після завершення фільтрації, обумовленою конструкцією WHERE. У наступному прикладі в операторі SELECT використані конструкції WHERE, GROUP BY і HAVING:

SELECT OrdDLOrderID AS OrderID.

SUM(Ord1.Quantity) AS "Units Sold",

SUM(Ord1.UnitPrice * Ord1.Quantity) AS Revenue

FROM [Order Details] AS Ord1

WHERE OrdDLOrderID IN

(SELECT DISTINCT Ord2. OrderID

FROM [Order Details] AS Ord2

WHERE Ord2.UnitPrice > $100)

GROUP BY GrdDLOrderID

HAVING SUM(Ord1.Quantity) > 100


Тут конструкція WHERE повертає замовлення, вартість яких більше $100, а далі конструкція HAVING обмежує результат, відбираючи замовлення на більш ніж 100 одиниць товару. Конструкція GROUP BY обмежує рядки для кожного конкретного значення поля Ord1.


Конструкція GROUP BY

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

Після ключових слів GROUP BY вказують список стовпців, за яким виконується групування. Конструкція GROUP BY обмежує рядки результуючого набору. Для кожного конкретного значення стовпця або декількох стовпців, яки групуються, можливий тільки один рядок. У кожному рядку результуючого набору втримуються підсумкові дані, які пов'язані з деяким значенням стовпців, які групуються. Якщо в операторі SELECT є конструкція GROUP BY, SQL Server накладає обмеження на елементи списку вибору. У списку вибору можуть бути лише ті стовпці, що групуються, і вирази, які повертають тільки одне значення для кожного значення стовпців, що групуються, наприклад агрегатні функції (векторні агрегати), одним з параметрів яких є ім'я стовпця.

Як правило, конструкція HAVING використовується з конструкцією GROUP BY, хоча її дозволяється застосовувати й окремо. Будь-який вираз може стати таким, який групує, якщо він не містить агрегатних функцій. У конструкції GROUP BY необхідно задавати ім'я стовпця таблиці або відображення, а не ім'я стовпця результуючого набору, привласнене за допомогою конструкції AS. У конструкції GROUP BY припустимо вказати кілька стовпців у вигляді вкладених груп, тобто згрупувати таблицю за допомогою будь-якої комбінації стовпців.


Обробка конструкцій WHERE, GROUP BY і HAVING

Розуміння вірної послідовності, у якій застосовуються конструкції WHERE, GROUP BY і HAVING, допомагає програмувати ефективні запити:

  • конструкція WHERE фільтрує рядки, які є результатом операцій, заданих у конструкції FROM;

  • вихідна інформація конструкції WHERE групується за допомогою конструкції GROUP BY;

  • рядки згрупованого результату фільтруються засобами конструкції HAVING.


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


Конструкція ORDER BY

Конструкція ORDER BY сортує результат запиту по одному або декількох полях. Сортування може бути як по зростанню (ASC), так і по убуванню (DESC). Якщо не заданий жоден з видів сортування, за замовчуванням передбачається ASC. Якщо в конструкції ORDER BY названо кілька стовпців, виконується вкладене сортування. Наступний оператор сортує рядки таблиці Titles спочатку за назвою видавництва (по убуванню), потім по типі (по зростанню, для кожного видавництва) і, нарешті, за ціною (також по зростанню, оскільки не заданий параметр DESC).

USE Pubs

SELECT Pub_id, Type, Title_id, Price

FROM Titles

ORDER BY Pub_id DESC, Type, Price


Приклад використання операторів 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, дозволяють одержувати зведені дані.


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

З'єднання дозволяють витягати дані із двох або більшого числа таблиць на основі логічних зв'язків між таблицями. З'єднання вказує 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 результуючого набору також повертається порожнє значення. При відповідності умові з'єднання заповнюються всі стовпці результуючого набору.


Визначення підзапитів усередині операторів 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, виконується, коли підзапит не повертає жодного рядка.


Узагальнення даних

В Transact-SQL входять кілька елементів, що дозволяють генерувати прості зведені звіти. Для цього застосовують оператори CUBE або ROLLUP, які є частиною конструкції GROUP BY оператора SELECT. Крім того, можна використати оператори COMPUTE і COMPUTE BY, які також пов'язані з конструкцією GROUP BY. Оператори COMPUTE і COMPUTE BY підтримуються заради зворотної сумісності.


Узагальнення даних за допомогою оператора CUBE

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

USE Pubs

SELECT SUBSTRING(Title, 1, 65) AS Title,

SUM(qty) AS 'Quantity'

FROM Sales INNER JOIN Titles

ON Sales.Title_id = Titles.Title_id

GROUP BY Title

WITH CUBE

ORDER BY Title


Оператор SELECT відповідає відношенню один до багатьох між книгами й кількістю проданих екземплярів кожної книги. За допомогою оператора CUBE він повертає додатковий рядок. Вона містить порожнє значення в стовпці Title результуючого набору й представляє всі значення стовпця Title таблиці Titles. Результуючий набір повертає значення кількості проданих екземплярів кожної книги й сумарну кількість проданих екземплярів всіх книг. У цьому випадку оператори CUBE і ROLLUP повертають той самий результат.


Узагальнення даних за допомогою оператора ROLLUP

Оператор ROLLUP генерує звіти з підсумками і проміжними підсумками. Створюваний його засобами результуючий набір схожий на результуючий набір оператора CUBE. Однак є й розходження:

  • результуючий набір, згенерований оператором CUBE, показує підсумкові значення для всіх комбінацій значень обраних стовпців;

  • результуючий набір, згенерований оператором ROLLUP, показує підсумкові значення для ієрархії значень в обраних стовпцях.

Наступний оператор SELECT містить оператор ROLLUP у конструкції GROUP BY:

USE Pubs

SELECT Pub_name, Au.lname, Title, SUM(qty) AS 'Sum'

FROM Authors a INNER JOIN TitleAuthor ta

ON a.Au_id = ta.Au_id INNER JOIN Titles t

ON t.Title_id = ta.Title_id INNER JOIN Publishers p

ON p.Pub_id = t.Pub_id INNER JOIN Sales s

ON s.Title_id - t.Title.id

GROUP BY Pub_name, Au_lname, Title

WITH ROLLUP


Оператор ROLLUP дозволяє створювати угруповання в результуючому наборі. Для подання всіх значень поля в згрупованих рядках використовується порожнє значення (за винятком поля Sum). При використанні без оператора ROLLUP оператор SELECT генерує тільки одну групу. Оператор CUBE повертає набагато більше груп. При вказівці в конструкції GROUP BY наступних стовпців: Pub_name, Aujname, і Title оператор ROLLUP повертає наступні дані:

  • кількість екземплярів кожної книги кожного автора, проданих кожним видавництвом;

  • сумарна кількість екземплярів книг кожного автора, проданих кожним видавництвом;

  • сумарна кількість всіх книг, проданих кожним видавництвом;

  • сумарна кількість всіх книг всіх авторів, проданих всіма видавництвами.


На завершення наведемо декілька практичних прикладів для закріплення матеріалу лекції. Наберемо наведені нижче приклади у редакторі запитів та виконаємо їх. Після цього пропонується самостійно проаналізувати результат роботи кожної команди SELECT.


Одержання даних за допомогою внутрішнього з'єднання

USE Northwind

SELECT o.Customerl. o.Qrderl, s.CompanyName

FROM Orders про JOIN Shippers s

ON o.ShipVia = s.Shipperl

WHERE ShipCountry = 'USA'

ORDER BY o.Customerl, s.CompanyName


Одержання даних за допомогою лівого зовнішнього з'єднання

USE Northwind

SELECT o.OrderID, o.CustomerID, c.ContactNane. c.City

FROM Orders про LEFT JOIN Customers з

ON o.CustomerID - c.CustomerID

AND o.ShipCity = c.City

ORDER BY o.OrderID


Одержання даних за допомогою правого зовнішнього з'єднання

USE Northwind

SELECT o.QrderID, o.CustomerID, c.ContactName, c.City

FROM Orders про RIGHT JOIN Customers з

ON o.CustomerID = c.CustomerID

AND o.ShipCity = c . C i t y

ORDER BY o.OrderID


Одержання даних за допомогою повного зовнішнього з'єднання

Скористайтеся тим же оператором SELECT, що й у двох попередніх прикладах, тільки змініть у операторі відповідну частину команди для утворення повного зовнішнього з’єднання.


Використання в підзапиті ключового слова IN

USE Northwind

SELECT OrderID, EmployeeID AS EmpID

FROM Orders

WHERE EmployeeID IN (

SELECT EmployeeID

FROM Employees

WHERE City = 'Seattle'

ORDER BY OrderID )


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

USE Northwind

SELECT Orderl, UnitPrice

FROM [Order Details]

WHERE UnitPrice > ALL

SELECT UnitPrice

FROM [Order Details] JOIN Orders

ON [Order Details].OrderID = Orders.OrderID

AND Orders.EmployeeID = 5

ORDER BY UnitPrice, OrderID


Застосування підзапита із ключовим словом EXISTS

USE Northwind

SELECT OrderID, CustornerID

FROM Orders

WHERE EXISTS

(

SELECT * FROM Customers

WHERE Customers. CustomerID - Orders. CustornerID

AND City = 'London'

)

ORDER BY OrderID


Створення зведених даних за допомогою оператора ROLLUP

USE Northwind

SELECT ProductID. UnitPrice, SUM(Quantity) AS 'Sum'

FROM [Order Details]

GROUP BY ProductID, UnitPrice

WITH ROLLUP

ORDER BY ProductID






Схожі:

Вибірка даних з бази даних sql server iconТема. Робота з об'єктами бази даних у середовищі субд
Завдання. Здійснити пошук та впорядкування даних бази даних Бібліотека за визначеними умовами
Вибірка даних з бази даних sql server iconТема. Поняття моделі даних, бази даних
Обладнання та наочність: дошка, комп'ютер, презентація «Бази даних. Субд», інструкції з тб в комп'ютерному кабінеті
Вибірка даних з бази даних sql server iconЛабораторна робота №9 Використання мови sql для роботи з базою даних у форматі InterBase Порядок виконання роботи 1
Відкрити у Delphi проект для роботи з базою даних у форматі InterBase з лр №7. Перевірити правильність виконання запиту до таблиці...
Вибірка даних з бази даних sql server iconЛабораторна робота №10 Використання мови sql для роботи з базою даних у форматі Mysql порядок виконання роботи 1
Відкрити у Delphi проект для роботи з базою даних у форматі Mysql з лр №8. Перевірити правильність виконання запиту до таблиці бази...
Вибірка даних з бази даних sql server iconЗакон україни про захист персональних даних (Відомості Верховної Ради України (ввр), 2010, №34, ст. 481) {Із змінами, внесеними згідно із Законами №4452-vi
У тексті Закону слова "володілець бази персональних даних" І "розпорядник бази персональних даних" у всіх відмінках І числах замінено...
Вибірка даних з бази даних sql server iconПідготовка до контрольної роботи «Бази даних Access»
База даних – це структурована сукупність взаємопов’язаних даних певної предметної області (реальних об’єктів, процесів)
Вибірка даних з бази даних sql server iconІнформації про клас Видалення класу Створення та редагування бази даних учасників проекту Імпорт даних учасників
Цей посібник містить детальний опис послідовності введення основної інформації для створення бази даних навчального закладу у проекті...
Вибірка даних з бази даних sql server iconІнформація щодо заповнення форми
Лист даних" служить формою внесення моніторингових даних; лист "Примітки" служить для внесення приміток по даних. Також, файл має...
Вибірка даних з бази даних sql server iconНаказ №95 від 05 березня 2012 р. Про підсумки проведення міського конкурсу учнівської молоді з інформаційних технологій
Розробка бази даних в Місrosoft Access та отримання із бази даних заданої інформації
Вибірка даних з бази даних sql server iconЗгода на збір та обробку персональних даних
Головному управлінні освіти і науки уточнену інформацію та подавати оригінали відповідних документів для внесення моїх та моєї дитини...
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©te.zavantag.com 2000-2017
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи