Програмування на T- SQL
Синтаксис та угоди T-SQL
Правила формування ідентифікаторів
Усі об'єкти SQL Server мають імена (ідентифікатори). Прикладами об'єктів є таблиці, уявлення, процедури, що зберігаються і т.д. Ідентифікатори можуть включати до 128 символів, зокрема літери, символи _ @ $ # та цифри.
Перший символ завжди має бути буквеним. Для змінних та часових таблиць використовуються спеціальні схеми іменування. Ім'я об'єкта не може містити пробілів і збігатися із зарезервованим ключовим словом SQL Server, незалежно від регістру символів. Шляхом укладання ідентифікаторів у квадратні дужки, в іменах об'єктів можна використовувати заборонені символи.
Завершення інструкції
Стандарт ANSI SQL вимагає приміщення в кінці кожної інструкції точки з комою. У той же час, при програмуванні на мові T-SQL точка з комою не обов'язкова.
Коментарі
Мова T-SQL допускає використання коментарів двох стилів: ANCI та мови С. Перший з них починається з двох дефісів і закінчується в кінці рядка:
Це однорядковий коментар стилю ANSI
Також коментарі стилю ANSI можуть вставлятися наприкінці рядка інструкції:
SELECT CityName - Вилучені стовпці
FROM City – вихідна таблиця
WHERE IdCity = 1; -- обмеження на рядки
Редактор SQL може застосовувати та видаляти коментарі у всіх виділених рядках. Для цього потрібно вибрати відповідні команди у меню Виправленняабо на панелі інструментів.
Коментарі стилю мови С починаються з косої риси та зірочки (/*) і закінчуються тими самими символами у зворотній послідовності. Цей тип коментарів найкраще використовуватиме коментування блоків рядків, таких як заголовки або великі тестові запити.
багаторядкового
коментаря
Однією з головних переваг коментарів стилю С є те, що багаторядкові запити в них можна виконувати, навіть не розкоментуючи.
Пакети T-SQL
Запитом називають одну інструкцію T-SQL, а пакетом – їх набір. Вся послідовність інструкцій пакета надсилається серверу з клієнтських програм як одна цілісна одиниця.
SQL Server розглядає весь пакет як робочу одиницю. Наявність помилки хоча б в одній інструкції призведе до неможливості виконання пакета. У той самий час граматичний розбір не перевіряє імена об'єктів і схем, оскільки сама схема може змінитися процесі виконання інструкції.
Файл сценарію SQL та вікно аналізатора запитів (Query Analyzer) може містити кілька пакетів. У разі всі пакети поділяють ключові слова термінаторів. За замовчуванням цим ключовим словом є GO, і воно має бути єдиним у рядку. Усі інші символи (навіть коментарі) нейтралізують роздільник пакету.
Налагодження T-SQL
Коли редактор SQL виявляє помилку, він відображає характер і номер рядка в пакеті. Двічі клацнувши на помилці, можна одразу ж переміститися до відповідного рядка.
В утиліту Management Studio версії SQL Server 2005 не включено налагодження мови T-SQL, - він присутній у пакеті Visual Studio.
SQL Server пропонує кілька команд, які полегшують налагодження пакетів. Зокрема, команда PRINT надсилає повідомлення без створення результуючого набору даних. Команду PRINT можна використовувати для відстеження виконання пакета. Коли аналізатор запитів знаходиться в режимі сітки, виконайте наступний пакет:
SELECT CityName
FROM City
WHERE IdCity = 1;
PRINT "Контрольна точка" ;
Результуючий набір даних з'явиться у сітці і складатиметься з одного рядка. У вкладці «Повідомлення» з'явиться наступний результат:
(Рядок оброблено: 1)
Контрольна точка
Змінні
Змінні T-SQL створюються за допомогою команди DECLARE, що має наступний синтаксис:
DECLARE @Ім'я_Змінної Тип_Даних [,
@Ім'я_Змінної Тип_Даних, …]
Усі імена локальних змінних повинні починатися символом @. Наприклад, для оголошення локальної змінної UStr, яка зберігає до 16 символів Unicode, можна використати таку інструкцію:
DECLARE @UStr varchar (16)
Використовувані для змінних типиданих точно збігаються з існуючими в таблицях. В одній команді DECLARE через кому може бути перераховано кілька змінних. Зокрема, у наступному прикладі створюються дві цілочисленні змінні a і b:
DECLARE
@a int ,
@b int
Область визначення змінних (тобто. термін їхнього життя) поширюється лише з поточний пакет. За замовчуванням щойно створені змінні містять порожні значення NULL і до включення у вирази мають бути ініціалізовані.
Завдання значень змінних
В даний час у мові SQL передбачені два способи завдання значення змінної – для цієї мети можна використовувати оператор SELECT або SET. З точки зору виконуваних функцій ці оператори діють майже однаково, крім того, що оператор SELECT дозволяє отримати вихідне значення з таблиці, зазначеної в операторі SELECT.
Оператор SET зазвичай використовується для завдання значень змінних у такій формі, яка частіше зустрічається в процедурних мовах. Як типові приклади застосування цього оператора можна вказати такі:
SET @a = 1;
SET @ b = @ a * 1.5
Зверніть увагу на те, що у всіх цих операторах безпосередньо здійснюються операції присвоєння, в яких використовуються явно задані значення, або інші змінні. За допомогою оператора SET неможливо привласнити змінне значення, отримане за допомогою запиту; запит повинен бути виконаний окремо і тільки після цього отриманий результат може бути наданий за допомогою оператора SET. Наприклад, спроба виконання такого оператора викликає помилку:
DECLARE @c int
SET @ c = COUNT (*) FROM City
SELECT @c
а наступний оператор виконується цілком успішно:
DECLARE @c int
SET @c = (SELECT COUNT (*) FROM City)
SELECT @c
Оператор SELECT зазвичай використовується для надання значень змінним, якщо джерелом інформації, яка має бути збережена в змінній, є запит. Наприклад, дії, які здійснюються у наведеному вище коді, набагато частіше реалізуються за допомогою оператора SELECT:
DECLARE @c int
SELECT @c = COUNT(*) FROM City
SELECT @c
Зверніть увагу на те, що даний кодтрохи зрозуміліше (зокрема, він лаконічніший, хоч і виконує ті ж дії).
Таким чином, можна сформулювати наступну загальноприйняту угоду щодо використання того й іншого оператора.
Оператор SET використовується, якщо має бути виконана проста операція надання значення змінної, тобто. якщо значення, що присвоюється, вже задано явно у формі певного значення або у вигляді якоїсь іншої змінної.
- Оператор SELECT застосовується, якщо надання значення змінної має бути засноване на запиті.
Використання змінних у запитах SQL
Однією з корисних властивостей мови T-SQL є те, що змінні можуть використовуватися в запитах без необхідності створення складних динамічних рядків, що вбудовують змінні програмного коду. Динамічний SQL продовжує своє існування, але одиночне значення можна змінити простіше – за допомогою змінної.
Скрізь, де у запиті може використовуватися вираз, може використовуватись і змінна. У наступному прикладі продемонстровано використання змінної у пропозиції WHERE:
DECLARE @IdProd int;
SET @ IdProd = 1;
SELECT
FROM Product
WHERE IdProd = @IdProd;
Глобальні системні змінні
SQL Server має більше тридцяти глобальних змінних, що не мають параметрів, які визначаються і підтримуються системою. Усі глобальні змінні мають префікс як двох символів @. Ви можете отримати значення будь-якої з них за допомогою простого запиту SELECT, як у наведеному нижче прикладі:
SELECT @@CONNECTIONS
Тут використовується глобальна змінна @@CONNECTIONS для отримання кількості підключень до SQL Server з часу запуску програми.
Серед системних змінних, що найчастіше застосовуються, можна відзначити такі:
- @@ERROR - Містить номер помилки, що виникла під час виконання останнього оператора T-SQL у поточному з'єднанні. Якщо помилка не виявлена, містить 0. Значення цієї системної змінної встановлюється після виконання кожного чергового оператора. Якщо потрібно зберегти значення, що міститься в ній, то це значення слід переносити в локальну змінну відразу ж після виконання оператора, для якого повинен бути збережений код помилки.
- @@IDENTITY - Містить останнє ідентифікаційне значення, вставлене в базу даних в результаті останнього оператора INSERT. Якщо в останньому операторі INSERT не відбулося вироблення ідентифікаційного значення, системна змінна @@IDENTITY містить NULL. Це твердження залишається справедливим, навіть якщо відсутність ідентифікаційного значення викликано аварійним завершенням під час виконання оператора. А якщо за допомогою одного оператора здійснюється кілька операцій вставки, цій системній змінній надається лише останнє ідентифікаційне значення.
- @@ROWCOUNT - Одна з системних змінних, що найбільш широко використовуються. Повертає інформацію про кількість рядків, які торкнулися останнім оператором. Зазвичай застосовується контролю помилок, відмінних від тих, які відносяться до категорії помилок етапу прогону програми. Наприклад, якщо у програмі виявляється, що після виклику на виконання оператора DELETE з конструкцією WHERE кількість порушених рядків дорівнює нулю, можна зробити висновок, що сталося щось непередбачене. Після цього повідомлення про помилку можна активувати вручну.
! Слід зазначити, що з SQL Server 2000 глобальні змінні прийнято називати функціями. Назва глобальні збивала користувачів з пантелику, дозволяючи думати, що область дії таких змінних ширша, ніж у локальних. Глобальним змінним часто помилково приписувалася можливість зберігати інформацію, незалежно від цього, включена вона у пакет чи ні, що, звісно, відповідало реальності.
Засоби керування потоком команд. Програмні конструкції
У мові T-SQL передбачена більшість класичних процедурних засобів управління ходом виконання програми, зокрема. умовна конструкція та цикли.
ОператорIF. . . ELSE
Оператори IF. . .ELSE діють у мові T-SQL переважно так само, як і в будь-яких інших мовах програмування. Загальний синтаксис цього оператора має такий вигляд:
IF Логічне вираження
SQL інструкція I BEGIN Блок SQL інструкцій END
SQL інструкція | BEGIN Блок SQL інструкцій END]
Як логічний вираз може бути заданий практично будь-який вираз, результат обчислення якого призводить до повернення булева значення.
Слід враховувати, що виконуваним за умовою вважається тільки оператор, який безпосередньо слідує за оператором IF (найближчим до нього). Замість одного оператора можна передбачити виконання за умовою кількох операторів, поєднавши їх у блок коду за допомогою конструкції BEGIN…END.
У наведеному нижче прикладі умова IF не виконується, що запобігає виконання наступного за ним оператора.
IF 1 = 0
PRINT "Перший рядок"
PRINT "Другий рядок"
Необов'язкова команда ELSE дозволяє встановити інструкцію, яка буде виконана у випадку, якщо умова IF не буде виконана. Подібно до IF, оператор ELSE управляє лише безпосередньо наступною за ним командою або блоком коду укладеним між BEGIN…END.
Незважаючи на те, що оператор IF виглядає обмеженим, його пропозиція умови може включати в себе потужні функції, подібно до пропозиції WHERE. Зокрема, це вирази IF EXISTS().
Вираз IF EXISTS() використовує як умову наявність будь-якого рядка, повернутий інструкцією SELECT. Оскільки шукаються будь-які рядки, список стовпців в інструкції SELECT можна замінити зірочкою. Цей метод працює швидше, ніж перевірка умови @@ROWCOUNT>0, тому що не потрібно підрахувати загальну кількість рядків. Як тільки один рядок задовольняє умові IF EXISTS(), запит може продовжувати виконання.
У наступному прикладі вираз IF EXISTS використовується для перевірки наявності у клієнта з кодом 1 будь-яких замовлень перед видаленням його з бази. Якщо за даним клієнтом є інформація хоча б за одним замовленням, видалення не проводиться.
IF EXISTS (SELECT * FROM WHERE IdCust = 1)
PRINT "Неможливо видалити клієнта, оскільки в базі є пов'язані з ним записи"
ELSE
WHERE IdCust = 1
PRINT "Видалення зроблено успішно"
ОператориWHILE, BREAK таCONTINUE
Оператор WHILE у мові SQL діє багато в чому так само, як і в інших мовах, з якими зазвичай доводиться працювати програмісту. По суті, у цьому операторі до початку кожного проходу циклом перевіряється деяка умова. Якщо перед черговим проходом циклу перевірка умови призводить до отримання значення TRUE, здійснюється прохід циклу, інакше виконання оператора завершується.
Оператор WHILE має наступний синтаксис:
WHILE Логічне вираження
SQL інструкція I
Блок SQL інструкцій
Безумовно, за допомогою оператора WHILE можна забезпечити виконання у циклі лише одного оператора (за аналогією з тим, як зазвичай використовується оператор IF), але на практиці конструкції WHILE, за якими не слідує блок BEGIN. . .END, що відповідає повному формату оператора, зустрічаються рідко.
Оператор BREAK дозволяє негайно вийти з циклу, не чекаючи того, як буде виконано прохід до кінця циклу і повторна перевірка умовного виразу.
Оператор CONTINUE дозволяє перервати окрему ітерацію циклу. Коротко можна описати дію оператора CONTINUE так, що він забезпечує перехід на початок циклу WHILE. Відразу після виявлення оператора CONTINUE у циклі, незалежно від того, де він знаходиться, відбувається перехід у початок циклу та повторне обчислення умовного виразу (а якщо значення цього виразу більше не дорівнює TRUE, здійснюється вихід із циклу).
Наступний короткий сценарій демонструє використання оператора WHILE для створення циклу:
DECLARE @ Temp int ;
SET @ Temp = 0;
WHILE @Temp< 3
BEGIN
PRINT @ Temp;
SET @ Temp = @ Temp + 1;
Тут у циклі ціла змінна @Temp збільшується з 0 до 3 і на кожній ітерації її значення виводиться на екран.
ОператорRETURN
Оператор RETURN використовується для зупинки виконання пакету, а отже, процедури, що зберігається, і тригера (розглядаються в наступних лабораторних заняттях).
У цьому розділі ви дізнаєтеся як sql використовується для розширення програм написаних іншими мовами. Хоча непроцедурність мови SQL робить його дуже потужним, в той же час це накладає на нього велику кількість обмежень. Щоб подолати ці обмеження, ви можете включати SQL у програми написані тією чи іншою процедурною мовою (що має певний алгоритм). Для наших прикладів, ми вибрали Паскаль, вважаючи, що ця мова найбільш проста у розумінні для початківців, і ще тому, що Паскаль - одна з мов для яких ANSI має напівофіційний стандарт.ЩО ТАКЕ - ВЛОЖЕННЯ SQL
Щоб вкласти SQL в іншу мову, ви повинні використовувати пакет програм, який би забезпечував підтримку вкладення SQL в цю мову і, звичайно ж, підтримку самої мови. Звичайно, ви повинні бути знайомі з мовою, яку ви використовуєте. Головним чином, ви будете використовувати команди SQL для роботи в таблицях бази даних, передачі результатів виведення в програму та отримання введення з програми, в яку вони вкладаються, узагальнено посилаючись до головної програми (яка може або не може приймати їх з діалогу або надсилати назад у діалог користувача та програми).НАВІЩО ВКЛАДЖУВАТИ SQL?
Хоча і ми витратили деякий час на те щоб показати що вміє робити SQL, але якщо ви - досвідчений програміст, ви ймовірно відзначили, що сам по собі, він не дуже корисний при написанні програм. Найочевидніше обмеження - це те, що коли SQL може відразу виконати пакет команди, інтерактивний SQL в основному виконує по одній команді в кожен момент часу. Типи логічних конструкцій типу if ... then ("якщо... то"), for ... do ("для... виконати") і while ... repeat("поки що... повторювати") - використовуваних для структур більшості комп'ютерних програм, тут відсутні, так що ви не зможете прийняти рішення - чи виконувати, як виконувати, або як довго виконувати одну дію внаслідок іншої дії. Крім того, інтерактивний SQL не може робити багато з значеннями, крім введення їх в таблицю, розміщення або розподілу їх за допомогою запитів, і звичайно виведення їх на якийсь пристрій.Традиційніші мови, однак, сильні саме в цих областях. Вони розроблені так щоб програміст міг починати обробку даних, і ґрунтуючись на її результатах, вирішувати, чи робити це чи інше, або ж повторювати дію до тих пір, поки не зустрінеться деяка умова, створюючи логічні маршрути і цикли. Значення зберігаються в змінних, які можуть використовуватися та змінюватись за допомогою будь-якого числа команд. Це дає можливість вказувати користувачам на введення або виведення цих команд з файлу, і можливість форматувати виведення складними способами (наприклад, перетворювати числових даних в діаграми). Мета вкладеного SQL полягає в тому, щоб об'єднати ці можливості, що дозволяють вам створювати складні процедурні програми, які адресують базу даних за допомогою SQL - дозволяючи вам усунути складні дії в таблицях процедурною мовою, яка не орієнтована на таку структуру даних, в той же час підтримуючи структурну строгість процедурного. мови.
ЯК РОБИТЬСЯ ВЛОГИ SQL.
Команди SQL містяться у вихідний текст головної програми, якій передує фраза - EXEC SQL (EXECute SQL). Далі встановлюються деякі команди, які є спеціальними для вкладеної форми SQL і які будуть представлені в цьому розділі.Строго кажучи, стандарт ANSI не підтримує вкладений SQL як такий. Він підтримує поняття, зване - модуль, який більш точно, є набором процедур SQL, що викликається, а не вкладенням в іншу мову. Офіційне визначення синтаксису вкладення SQL буде включати розширення офіційного синтаксису кожної мови в який може вкладатися SQL, що дуже довга і невдячна задача, яку ANSI уникає. Однак, ANSI забезпечує чотири додатки (які не є частиною стандарту), які визначають синтаксис вкладення SQL для чотирьох мов: КОБОЛ, ПАСКАЛЬ, ФОРТРАН, та ПЛ/1. Мова C - також широко підтримується як інші мови. Коли ви вставляєте команди SQL у текст програми написаної іншою мовою, ви повинні виконати передкомпіляцію перед тим, як остаточно її скомпілюєте. Програма, яка називається прекомпілятором (або препроцесором), буде переглядати текст вашої програми та перетворювати команди SQL, у форму зручну для використання базовою мовою.В певному ANSI, основна програма викликає процедури SQL. Процедури вибирають параметри з головної програми, і повертають уже оброблені значення назад у основну програму. Модуль може містити будь-яку кількість процедур, кожна з яких складається з одиночної команди SQL. Йдеться в тому, щоб процедури могли працювати тим же способом, що й процедури мовою, в яку вони були вкладені (хоча модуль ще повинен ідентифікувати базову мову через відмінності в типах даних різних мов). Реалізації можуть задовольнити стандарту, виконавши вкладення SQL у такий спосіб, якби модулі вже були точно визначені. Для цієї мети прекомпілятор створюватиме модуль, званий модулем доступу. Тільки один модуль, що містить будь-яку кількість процедур SQL, може існувати для цієї програми. Розміщення операторів SQL безпосередньо у головному коді, відбувається більш просто і більш практично, ніж безпосередньо створення самих модулів.
Кожна з програм, що використовують вкладення SQL, пов'язана з ID доступу під час її виконання. ID доступу, пов'язаний з програмою, повинен мати всі привілеї для виконання операцій SQL, що виконуються в програмі. Взагалі те, вкладена програма SQL реєструється у базі даних, як і користувач, виконує програму. Більш детально, це визначає проектувальник, але ймовірно було б непогано включити у вашу програму команду CONNECT або їй подібну.
ВИКОРИСТАННЯ ЗМІННИХ ОСНОВНОЇ МОВИ В SQL Основний спосіб, яким SQL і частини базової мови ваших програм будуть зв'язуватися один з одним - це за допомогою значень змінних. Природно, що різні мови розпізнаютьрізні типи* бути оголошеними в SQL DECLARE SESSION (РОЗДІЛ ОГОЛОШЕНЬ) який буде описано далі.
* мати сумісний тип даних з їх функціями в команді SQL (наприклад, числовий тип якщо він вставляється в числове поле)
* бути призначеними значенню під час їх використання у команді SQL, якщо команда SQL самостійно неспроможна зробити призначення.
* передувати двокрапці (:) коли вони згадуються в команді SQL
Так як головні змінні відрізняються від імен стовпців SQL наявністю у них двокрапки, ви можете використовувати змінні з тими ж іменами що і ваші стовпці, якщо це звичайно потрібно. Припустимо, що ви маєте чотири змінних у вашій програмі, з іменами: id_num, salesperson, loc, і comm. Вони містять значення, які ви хочете вставити в таблицю Продавців. Ви можете вкласти наступну команду SQL у вашу програму: EXEC SQL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm) Поточні значення цих змінних будуть поміщені в таблицю. Як ви можете бачити, змінна comm має те саме їм що і стовпець в який це значення вкладається. Зверніть увагу, що в кінці команди немає крапки з комою. Це тому, що відповідне завершення для вкладеної команди SQL залежить від мови, для якої робиться вкладення.
Для Паскаля та PL/1, це буде точка з комою; для КОБОЛА, слово END-EXEC; і для ФОРТРАНА не буде завершення. В інших мовах це залежить від реалізації, і тому ми домовимося, що будемо використовувати точку з комою (у цій книзі) завжди, щоб не суперечити інтерактивному SQL та Паскалю. Паскаль завершує вкладений SQL та власні команди однаково – точкою з комою. Спосіб зробити команду повністю такий як описана вище, полягає в тому, щоб включати її в цикл і повторювати її, з різними значеннями змінних, як показано в наступному прикладі: while not end-ot-file (input) do begin readln ( id_num, salesperson, loc, comm); EXEC SOL INSERT INTO Salespeople VALUES (:id_num, :salesperson, :loc, :comm); end;
Фрагмент програми на ПАСКАЛІ, визначає цикл, який зчитуватиме значення з файлу, зберігатиме їх у чотирьох пройменованих змінних, зберігати значення цих змінних у таблиці Продавців, а потім зчитуватиме наступні чотири значення, повторюючи цей процес до тих пір, поки весь вхідний файл не прочитається. Вважається, що кожен набір значень завершується поверненням каретки (для незнайомих з Паскалем, функція readln зчитує інформацію, що вводиться, і переходить на наступний рядок джерела цієї інформації). Це дає вам простий спосіб передати дані з
Усі перемінні на які є посилання в пропозиціях SQL, повинні спочатку бути оголошені в SQL DECLARE SECTION (РОЗДІЛ ОГОЛОШЕНЬ), що використовує звичайний синтаксис головної мови. Ви можете мати будь-яке число таких розділів у програмі, і вони можуть розміщуватися десь у коді перед змінною, що використовується, підпорядкованою обмеженням визначеним відповідно до головної мови. Розділ оголошень повинен починати і закінчуватися вкладеними командами SQL - BEGIN DECLARE SECTION (Початок Розділу Оголошень) та END DECLARE SECTION (Кінець Розділу Оголошень), яким передує, як зазвичай EXEC SQL (Виконати). Щоб оголосити змінні, що використовуються в попередньому прикладі, ви можете ввести наступне: EXEC SQL BEGIN DECLARE SECTION; Var id-num: integer;Salesperson: packed array (1 . .10) ot char;
loc: packed array (1. .10) ot char;* коли ви використовуєте предикат перевіряючий значення, яке, як ви знаєте, можуть бути унікальним, як у цьому прикладі. Значення, які, як ви знаєте, можуть бути унікальними - це ті значення, які мають примусове обмеження унікальності або унікальний індекс, як це говорилося в Розділах 17 і .
* коли ви використовуєте одну або більше агрегатних функцій і не використовуєте GROUP BY.
* коли ви використовуєте SELECT DISTINCT у зовнішньому ключі з предикатом посилається на єдине значення батьківського ключа (забезпечуючи вашій системі припис довідкової цілісність), як у наступному прикладі: sname = "Motika"); Передбачалося, що Salespeople.sname і Salespeople.snum - це відповідно, унікальний і первинний ключі цієї таблиці, а Customers.snum - це зовнішній ключ, що посилається на Salespeople.snum, і ви припускали, що цей запит зробить єдиний рядок. Є інші випадки, коли ви можете знати, що запит повинен зробити єдиний рядок виводу, але вони мало відомі і, в більшості випадків, ви ґрунтуєтеся на тому, що ваші дані мають цілісність, яка не може бути наказана за допомогою обмежень. Не покладайтеся на це! Ви створюєте програму яка, ймовірно, буде використовуватися протягом деякого часу, і найкраще програти її щоб бути гарантованим у майбутньому від можливих відмов. У всякому разі, немає необхідності групувати запити, які виробляють одиночні рядки, оскільки SELECT INTO - використовується тільки для зручності.
Як ви побачите, ви можете використовувати запити, що виводять численні рядки, використовуючи курсор.
Одна з сильних якостей SQL - це здатність функціонувати на всіх рядках таблиці, щоб зустріти певну умову як блок запису, не знаючи скільки таких рядків там може бути. Якщо десять рядків задовольняють предикату, запит може вивести всі десять рядків. Якщо десять мільйонів рядків визначено, то всі десять мільйонів рядків будуть виведені. Це трохи складно, коли ви спробуєте пов'язати це з іншими мовами. Як ви зможете призначати висновок запиту для змінних, коли ви не знаєте як великий буде висновок? Рішення полягає в тому, щоб використовувати те, що називається – курсором. Ви ймовірно знайомі з курсором, як з миготливою рисою, яка відзначає вашу позицію на екрані комп'ютера. Ви можете розглядати SQL курсор як пристрій, який аналогічний до цього, відзначає ваше місце у виведенні запиту, хоча аналоги не повна. Наприклад, можливо вам потрібно, щоб висновок видавався по одному рядку, запитуючи кожен раз у користувача, чи хоче він продовжити, щоб побачити наступний рядок Look_at_more:= True;КУРСОР
SQL КОДИА) Для SELECT, жодного рядка не вибрано запитом.
Б) Для FETCH, останній рядок вже був вибраний, або жодного рядка не вибрано запитом у курсорі.
В) Для INSERT, жодного рядка не було вставлено (мається на увазі, що запит використовувався щоб згенерувати значення для вставки, і був відкинутий при спробі вилучення будь-якого рядка.
Г) Для UPDATE і DELETE, жодний рядок не відповів умові предикату, і отже жодних змін зроблено в таблиці не буде.
У будь-якому випадку буде встановлено код SQLCODE = 100.
2. Команда виконалася нормально, не задовольнивши жодної з зазначених умов. У цьому випадку буде встановлено код SQLCOD = 0.
3. Команда згенерувала помилку. Якщо це сталося, зміни, зроблені до бази даних поточною транзакцією, будуть відновлені (див. розділ 23).
У цьому випадку буде встановлено код SQLCODE = деякому негативному числу, що визначається проектувальником. Завдання цього числа, ідентифікувати проблему, наскільки це можливо. У принципі, ваша система має бути забезпечена підпрограмою, яка в цьому випадку повинна виконатися, щоб видати для вас інформацію розшифровувальне значення негативного числа, визначеного вашим проектувальником. У цьому випадку деяке повідомлення про помилку буде виведено на екран або записано у файл протоколу, а програма в цей час виконає відновлення змін для поточної транзакції, відключиться від бази даних і вийде з неї. Тепер ми можемо вдосконалити
ВИКОРИСТАННЯ SQLCODE ДЛЯ УПРАВЛІННЯ ЦИКЛАМИнаш попередній приклад для виходу з циклу автоматично, за умови, що курсор порожній, всі рядки обрані, або сталася помилка: Look_at_more: = lhe;
Це зручно для виходу за умов - всі рядки обрані. Але якщо ви отримали помилку, ви повинні зробити щось таке, що описано для третього випадку вище. Для цієї мети SQL надає пропозицію GOTO. Фактично, SQL дозволяє вам застосовувати його досить широко, тому програма може виконати команду GOTO автоматично, якщо буде зроблено певне значення SQLCODE. Ви можете зробити це разом із пропозицією WHENEVER. Є шматок прикладу для цього випадку: EXEC SQL WHENEVER SQLERROR GOTO Error_handler;< 0; а NOT FOUND - это другой способ сообщить что SQLCODE = 100. (Некоторые реализации называют последний случай еще как - SQLWARNING.) Error_handler - это им того места в программе в которое будет пере- несено выполнение программы если произошла ошибка (GOTO может состоять из одного или двух слов). Такое место определяется любым способом соответствующим для главного языка, например, с помощью метки в Паскале, или имени раздела или имени параграфа в КОБОЛЕ (в дальнейшем мы будем использовать термин - метка). Метка более удач- но идентифицирует стандартную процедуру распространяемую проектировщиком для включения во все программы.EXEC SQL WHENEVER NOT FOUND CONTINUE; SQLERROR - це інший спосіб повідомити, що SQLCODE
CONTINUE не робить чогось спеціального для значення SQLCODE. Це також значення за замовчуванням. якщо ви не використовуєте команду WHENEVER, яка визначає значення SQLCODE. Однак ці неактивні визначення дають можливість перемикатися вперед і назад, виконуючи і не виконуючи дії, в різних точках (мітках) вашої програми. Наприклад, якщо ваша програма включає декілька команд INSERT, які використовують запити, які реально повинні робити значення, ви могли б надрукувати спеціальне повідомлення або зробити щось таке, що пояснювало б, що запити повертаються порожніми і ніякі значення не були вставлені . У цьому випадку ви можете ввести наступне: EXEC SQL WHENEVER NOT FOUND GOTO No_rows; No_rows - це мітка в деякому коді, що містить певну дію. З іншого боку, якщо вам потрібно зробити вибірку у програмі пізніше, ви можете ввести таке в цій точці, EXEC SQL WHENEVER NOT FOUND CONTINUE; щоб виконання вибірки повторювалося до тих пір поки всі рядки не будуть витягнуті, що є нормальною процедурою, що не вимагає спеціальної обробки.
МОДИФІКУВАННЯ КУРСОРІВ< (SELECT AVG (rating) FROM Customers); Однако, вы можете получить тот же эффект, используя запрос для выбора соответствующих строк, запомнив их в курсоре, и выполнив DELETE с использованием курсора. Сначала вы должны объявить курсор: EXEC SQL DECLARE Belowavg CURSOR FOR SELECT * FROM Customers WHERE rating < (SELECT AVG (rating) FROM Customers); Затем вы должны создать цикл, чтобы удалить всех заказчиков выбранных курсором: EXEC SQL WHENEVER SQLERROR GOTO Error_handler; EXEC SQL OPEN CURSOR Belowavg; while not SOLCODE = 100 do begin EXEC SOL FETCH Belowavg INTO:a, :b, :c, :d, :e; EXEC SOL DELETE FROM Customers WHERE CURRENT OF Belowavg; end; EXEC SOL CLOSE CURSOR Belowavg; Предложение WHERE CURRENT OF означает что DELETE применяется к строке которая в настоящее время выбрана курсором. Здесь подразумевается, что и курсор и команда DELETE, ссылаются на одну и ту же таблицу, и следовательно, что запрос в курсоре - это не объединение. Курсор должен также быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять тем же условиям что и представления (см. Главу 21). Кроме того, ORDER BY и UNION, которые не разрешены в представлениях, в курсорах - разрешаются, но предохраняют курсор от модифицируемости. Обратите внимание в вышеупомянутом примере, что мы должны выбирать строки из курсора в набор переменных, даже если мы не собирались использовать эти переменные. Этого требует синтаксис команды FETCH. UPDATE работает так же. Вы можете увеличить значение комиссионных всем продавцам, которые имеют заказчиков с оценкой=300, следующим способом. Сначала вы объявляете курсор: EXEC SOL DECLARE CURSOR High_Cust AS SELECT * FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE rating = 300); Затем вы выполняете модификации в цикле: EXEC SQL OPEN CURSOR High_cust; while SQLCODE = 0 do begin EXEC SOL FETCH High_cust INTO:id_num, :salesperson, :loc, :comm; EXEC SQL UPDATE Salespeople SET comm = comm + .01 WHERE CURRENT OF High_cust; end; EXEC SQL CLOSE CURSOR High_cust; Обратите внимание: что некоторые реализации требуют, чтобы вы указы- вали в определении курсора, что курсор будет использоваться для выполнения команды UPDATE на определенных столбцах. Это делается с помощью заключительной фразы определения курсора - FOR UPDATE . Чтобы объявить курсор High_cust таким способом, так чтобы вы мог- ли модифицировать командой UPDATE столбец comm, вы должны ввести следующее предложение: EXEC SQL DECLARE CURSOR High_Cust AS SELECT * FROM Salespeople WHERE snum IN (SELECT snum FROM Customers WHERE rating = 300) FOR UPDATE OF comm; Это обеспечит вас определенной защитой от случайных модификаций, которые могут разрушить весь порядок в базе данных.Курсори можуть також бути використані, щоб вибирати групу рядків з таблиці, які можуть бути модифіковані або видалені одна за одною. Це дає вам можливість обходити деякі обмеження предикатів, що використовуються в командах UPDATE і DELETE. Ви можете посилатися на таблицю, задіяну в предикаті запиту курсору або будь-якому з його підзапитів, які ви не можете виконати в предикатах самих цих команд. Як підкреслено в Розділі 16 , стандарт SQL відхиляє спробу видалити всіх користувачів з рейтингом нижче середнього, у такій формі: EXEC SQL DELETE FROM Customers
Порожні (NULLS) значення - це спеціальні маркери, що визначаються самим SQL. Вони можуть поміщатися в основні змінні. Спроба вставити значення NULL у головну змінну буде некоректною, оскільки головні мови не підтримують NULL значень у SQL, за визначенням. Хоч результат при спробі вставити NULL значення в головну змінну визначає проектувальник, цей результат не повинен суперечити теорії бази даних, і тому має зробити помилку: код SQLCODE у вигляді негативного числа, і викликати підпрограму управління помилкою. Звичайно вам потрібно цього уникнути. Тому, ви можете вибрати значення NULL з допустимими значеннями, що не призводять до руйнування вашої програми. Навіть якщо програма і не зруйнується, значення головних змінних стануть неправильними, тому що вони не можуть мати NULL значень. Альтернативним методом, що надається для цієї ситуацією є - функція змінної indicator (покажчик). Змінна indicator – оголошена у розділі оголошень SQL нагадує інші змінні. Вона може мати тип головної мови який відповідає числовому типу SQL. Щоразу, коли ви виконуєте операцію, яка повинна помістити NULL значення в змінну головної мови, ви повинні використовувати змінну indicator для надійності. Ви поміщаєте змінну indicator в команду SQL безпосередньо після змінної головної мови, яку ви хочете захистити, без будь-яких прогалин або ком, хоча ви можете, за бажання, вставити слово - INDICATOR. Змінною indicator у команді, спочатку присвоюється значення 0. Однак, якщо виробляється значення NULL , змінна indicator становить рівною негативному числу. Ви можете перевірити значення змінної indicator, щоб дізнатися, чи було знайдено значення NULL. Давайте припустимо, що стать city і comm, таблиці Продавців, не мають обмеження NOT NULL, і що ми оголошували в розділі SQL, дві ПАСКАЛЬєвські змінні цілого типу, i_a і i_b. (Немає нічого такого в розділі оголошень, що могло б уявити їх як змінні indicator. Вони стануть змінними indicator, коли будуть використовуватися як змінні indicator.) Є одна можливість: EXEC SQL OPEN CURSOR High_cust;< O then writeln ("salesperson ", id_num, " has no city"); If i_b < O then writeln ("salesperson ", id_num, " has no commission"); end; {else} end; {while} EXEC SQL CLOSE CURSOR High_cust; Как вы видите, мы включили, ключевое слово INDICATOR в одном случае, и исключили его в другом случае, чтобы показать, что эффект будет одинаковым в любом случае. Каждая строка будет выбрана, но команда UPDATE выполнится только если NULL значения не будут обнаружены. Если будут обнаружены NULL значения, выполнится еще одна часть программы, которая распечатает предупреждающее сообщение, где было найдено каждое NULL значение. Обратите внимание: переменные indicator должны проверяться в главном языке, как указывалось выше, а не в предложении WHERE команды SQL. Последнее в принципе не запрещено, но результат часто бывает непредвиденным.ВИКОРИСТАННЯ ЗМІННОЇ INDICATOR ДЛЯ ЕМУЛЯЦІЇ NULL ЗНАЧЕНЬ SQL
Інша можливість полягає в тому, щоб обробляти змінну indicator, пов'язуючи її з кожною змінною головної мови, спеціальним способом, що емулює поведінку NULL значень SQL. Щоразу, коли ви використовуєте одне з цих значень у вашій програмі, наприклад, у пропозиції if ... then, ви можете спочатку перевірити пов'язану змінну indicator, чи є її значення = NULL. Якщо це так, то ви обробляєте змінну по-іншому. Наприклад, якщо значення NULL було вилучено з підлога city для головної змінної city, яка пов'язана зі змінною indicator - i_city, ви повинні встановити значення city, що дорівнює послідовності пробілів. Це буде необхідно, тільки якщо ви друкуватимете його на принтері; його значення не повинно відрізнятись від логіки вашої програми. Звичайно, i_city автоматично встановлюється ввід'ємне значення< >. Припустимо, що ви мали наступну конструкцію у вашій програмі: If sity = "London" then comm: = comm + .01 else comm: = comm - .01 Будь-яке значення, що вводиться в змінну city, або буде дорівнює "London" або не буде одно. Отже, у кожному випадку значення комісійних буде збільшено або зменшено. Однак, еквівалентні команди SQL виконуються по різному: EXEC SQL UPDATE Salespeople SET comm = comm + .01 WHERE sity = "London";та EXEC SQL UPDATE Salespeople SET comm = comm .01;
WHERE sity"London"; (Варіант на ПАСКАЛ працює тільки з єдиним значенням, у той час як варіант на SQL працює з усіма таблицями.) Якщо значення city у варіанті на SQL дорівнюватиме значенням NULL , обидва предикати будуть невідомі, і значення comm, отже, не буде змінено в будь-якому випадку. Ви можете використовувати змінну indicator щоб зробити поведінку вашої головної мови несуперечливою цьому, за допомогою створення умови, яке виключає значення NULL: If i_city > = O then begin - .01;
Змінна indicator також може використовуватись для призначення значення NULL. Просто додайте її до імені головної змінної в команді UPDATE або INSERT тим же способом, що і в команді SELECT. Якщо змінна indicator має негативне значення, значення NULL буде розміщено у полі. Наприклад, наступна команда поміщає значення NULL в стать city і comm, таблиці Продавців, щоразу, коли змінні indicator - i_a або i_b будуть негативними; інакше вона поміщає туди значення основних змінних: EXEC SQL INSERT INTO Salespeople VALUES (:Id_num, :salesperson, :loc:i_a, :comm:i_b); Змінна indicator використовується також, щоб показувати рядок, що відкидається. Це станеться якщо ви вставляєте значення символів SQL в головну змінну, яка не досить довга, щоб вмістити всі символи. Це особа проблема з нестандартним типами даних - VARCHAR та LONG (дивись Додатку C). У цьому випадку змінна буде заповнена першими символами рядка, а останні символи будуть втрачені. Якщо використовується змінна indicator, вона буде встановлена в позитивне значення, що вказує на довжину частини рядка, що відкидається, дозволяючи таким чином вам дізнатися, скільки символів було втрачено. У цьому випадку Ви можете перевірити за допомогою перегляду -значення змінної indicator > 0, або< 0.РЕЗЮМЕ
Команди SQL вкладаються в процедурні мови, щоб об'єднати сили двох підходів. Деякі додаткові засоби SQL необхідні для виконання цієї роботи. Вкладені команди SQL транслюються програмою, яка називається прекомпілятором, у форму придатну для використання транслятором головної мови, і використовуються в цій головній мові, як виклики процедури до підпрограм, які створює прекомпілятор, називаються - модулями доступу. ANSI підтримує вкладення SQL мови: ПАСКАЛЬ, ФОРТРАН, КОБОЛ, і PL/I. Інші мови також використовуються, особливо Сі. У спробі коротко описати вкладений SQL, є найважливіші місця у цьому розділі:* Всі вкладені команди SQL починаються словами EXEC SQL і закінчуються способом, який залежить від використовуваної головної мови.
* Усі головні змінні, доступні в командах SQL, повинні бути оголошені в розділі оголошень SQL, перш ніж вони будуть використані.
* Всім головним змінним має передувати двокрапка, коли вони використовуються в команді SQL.
* Запити можуть зберігати свій висновок безпосередньо в головних змінних, використовуючи пропозицію INTO, якщо і тільки якщо вони вибирають єдиний рядок.
* Курсори можуть використовуватися для збереження виводу запиту, та доступу до одного рядка у кожний момент часу. Курсори бувають оголошеними (якщо визначають запит, у якому будуть утримуватися), відкритими (якщо виконують запит), і закритими (якщо видаляють виведення запиту з курсора). Якщо курсор відкритий, команда FETCH використовується, щоб переміщати його по черзі до кожного рядка виводу запиту.
* Курсори є модифікованими або тільки читання. Щоб стати модифікованим, курсор повинен задовольняти всім критеріям, яким задовольняє перегляд; крім того, він не повинен використовувати пропозиції ORDER BY або UNION, які в жодному разі не можуть використовуватись переглядами. Чи не модифікований курсор є курсором тільки-читання.
* Якщо курсор модифікований, він може використовуватися для визначення, які рядки задіяні вкладеними командами UPDATE та DELETE через пропозицію WHERE CURRENT OF. DELETE або UPDATE повинні бути поза тією таблицею, до якої курсор звертається в запиті.
* SQLCODE повинен бути оголошений як змінна числового типу для кожної програми, яка буде використовувати вкладений SQL. Його значення встановлюється автоматично після виконання кожної команди SQL.
* Якщо команда SQL виконана як завжди, але не зробила виводу чи очікуваного зміни у базі даних, SQLCODE = 100. Якщо команда зробила помилку, SQLCODE дорівнюватиме деякому апаратно- певному негативному числу яке описує помилку. Інакше SQLCODE = 0.
* Пропозиція WHENEVER може використовуватися для визначення дії, яку потрібно зробити, коли SQLCODE = 100 (не знайдено) або коли SQLCODE дорівнює негативному числу (SQLERROR). Дія може бути або перехід до певної мітки в програмі (GOTO
Для отримання даних із бази даних використовується мова SQL. SQL - це мова програмування, яка дуже нагадує англійську, але призначена для програм управління базами даних. SQL використовується у кожному запиті в Access.
Розуміння принципів роботи SQL допомагає створювати точніші запити та спрощує виправлення запитів, які повертають неправильні результати.
Це стаття з циклу статей про мову SQL для Access. У ній описані основи використання SQL для вибірки даних та наведено приклади синтаксису SQL.
В цій статті
Що таке SQL?
SQL - це мова програмування, призначений для роботи з наборами фактів та відносин між ними. У програмах керування реляційними базами даних, такими як Microsoft Office Access, мова SQL використовується для роботи з даними. На відміну від багатьох мов програмування, SQL легко читаємо і зрозумілий навіть новачкам. Як і багато мов програмування, SQL є міжнародним стандартом, визнаним такими комітетами зі стандартизації, як ISO та ANSI.
На мові SQL описуються набори даних, які допомагають отримувати відповіді питання. При використанні SQL необхідно використовувати правильний синтаксис. Синтаксис – це набір правил, що дозволяють правильно поєднувати елементи мови. Синтаксис SQL заснований на синтаксисі англійської мовиі має багато спільних елементів із синтаксисом мови Visual Basicдля програм (VBA).
Наприклад, проста інструкція SQL, що отримує список прізвищ контактів з ім'ям Mary, може виглядати так:
SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";
Примітка:Мова SQL використовується не тільки для виконання операцій над даними, але ще й для створення та зміни структури об'єктів бази даних, наприклад, таблиць. Частина SQL, яка використовується для створення та зміни об'єктів бази даних, називається мовою опису даних DDL. Мова DDL не розглядається у цій статті. Щоб отримати додаткові відомості, див. Створення та змінення таблиць або індексів за допомогою запиту визначення даних.
Інструкції SELECT
Інструкція SELECT використовується для опису набору даних на мові SQL. Вона містить повний описнабору даних, які необхідно отримати з бази даних, включаючи:
таблиці, у яких містяться дані;
зв'язки між даними із різних джерел;
поля чи обчислення, на основі яких відбираються дані;
умови відбору, яким мають відповідати дані, що включаються до результату запиту;
необхідність та спосіб сортування.
Пропозиції SQL
Інструкція SQL складається з кількох частин, які називаються пропозиціями. Кожна пропозиція в інструкції SQL має призначення. Деякі пропозиції є обов'язковими. У наведеній нижче таблиці вказані пропозиції SQL, які найчастіше використовуються.
Пропозиція SQL | Опис | Обов'язкове |
|---|---|---|
|
Визначає поля, які містять необхідні дані. |
||
|
Визначає таблиці, які містять поля, зазначені у реченні SELECT. |
||
|
Визначає умови відбору полів, яким мають відповідати всі записи, що включаються до результатів. |
||
|
Визначає порядок сортування результатів. |
||
|
В інструкції SQL, яка містить статистичні функції, визначає поля, для яких у пропозиції SELECT не обчислюється зведене значення. |
Тільки за наявності таких полів |
|
|
В інструкції SQL, яка містить статистичні функції, визначає умови, що застосовуються до полів, для яких у пропозиції SELECT обчислюється зведене значення. |
Терміни SQL
Кожна пропозиція SQL складається з термінів, які можна порівняти з частинами мови. У наведеній нижче таблиці наведено типи термінів SQL.
Термін SQL | Порівняна частина мови | Визначення | приклад |
|---|---|---|---|
|
ідентифікатор |
іменник |
Ім'я, яке використовується для ідентифікації об'єкта бази даних, наприклад, ім'я поля. |
Клієнти.[НомерТелефону] |
|
оператор |
дієслово чи прислівник |
Ключове слово, яке репрезентує дію або змінює його. |
|
|
константа |
іменник |
Значення, яке не змінюється, наприклад, число або NULL. |
|
|
вираз |
прикметник |
Поєднання ідентифікаторів, операторів, констант та функцій, призначене для обчислення одного значення. |
>= Товари.[Ціна] |
Основні пропозиції SQL: SELECT, FROM та WHERE
Загальний формат інструкцій SQL:
SELECT field_1
FROM table_1
WHERE criterion_1
;
Примітки:
Access не враховує розрив рядків в інструкції SQL. Незважаючи на це, кожна пропозиція рекомендується починати з нового рядка, щоб інструкцію SQL було зручно читати як тому, хто її написав, так і всім іншим.
Кожна інструкція SELECT закінчується крапкою з комою (;). Крапка з комою може стояти як наприкінці останньої пропозиції, так і на окремому рядку наприкінці інструкції SQL.
Приклад у Access
У наведеному нижче прикладі показано, як Access може виглядати інструкція SQL для простого запиту на вибірку.
1. Пропозиція SELECT
2. Пропозиція FROM
3. Пропозиція WHERE
Розберемо приклад за пропозиціями, щоб зрозуміти, як працює синтаксис SQL.
Пропозиція SELECT
SELECT, Company
Це пропозиція SELECT. Воно містить оператор (SELECT), за яким слідують два ідентифікатори ("[Адреса електронної пошти]" та "Компанія").
Якщо ідентифікатор містить пробіли або спеціальні знаки (наприклад, "Адреса електронної пошти"), він повинен бути укладений у прямокутні дужки.
У пропозиції SELECT не потрібно вказувати таблиці, в яких містяться поля, і не можна задати умови відбору, яким повинні відповідати дані, що включаються до результатів.
В інструкції SELECT пропозиція SELECT завжди стоїть перед пропозицією FROM.
Пропозиція FROM
FROM Contacts
Це пропозиція FROM. Воно містить оператор (FROM), за яким слідує ідентифікатор (Контакти).
Пропозиція FROM не вказує поля для вибірки.
Пропозиція WHERE
WHERE City="Seattle"
Ця пропозиція WHERE. Воно містить оператор (WHERE), за яким слідує вираз (Місто = "Ростов").
За допомогою пропозицій SELECT, FROM та WHERE можна виконувати безліч дій. Щоб отримати додаткові відомості про використання цих пропозицій, див.
Сортування результатів: ORDER BY
Як і Microsoft Excel, в Access можна сортувати результати запиту в таблиці. Використовуючи пропозицію ORDER BY, ви також можете вказати спосіб сортування результатів під час виконання запиту. Якщо використовується пропозиція ORDER BY, вона повинна знаходитись наприкінці інструкції SQL.
Пропозиція ORDER BY містить список полів, для яких потрібно виконати сортування, у тому самому порядку, в якому будуть застосовані сортування.
Припустимо, наприклад, що результати спочатку потрібно відсортувати по полю "Компанія" в порядку зменшення, а потім, якщо присутні записи з однаковим значенням поля "Компанія", - відсортувати їх по полю "Адреса електронної пошти" в порядку зростання. Пропозиція ORDER BY буде виглядати так:
ORDER BY Company DESC,
Примітка:За умовчанням Access сортує значення за зростанням (від А до Я, від найменшого до найбільшого). Щоб замість цього виконати сортування значень за спаданням, необхідно вказати ключове слово DESC.
Додаткові відомості про пропозицію ORDER BY див. у статті Пропозиція ORDER BY.
Робота зі зведеними даними: пропозиції GROUP BY та HAVING
Іноді виникає необхідність роботи зі зведеними даними, такими як підсумковий продаж за місяць або найдорожчі товари на складі. Для цього у пропозиції SELECT до поля застосовується агрегатна функція. Наприклад, якщо в результаті виконання запиту потрібно отримати кількість адрес електронної пошти кожної компанії, пропозиція SELECT може виглядати так:
Можливість використання тієї чи іншої агрегатної функції залежить від типу даних у полі та потрібного виразу. Для отримання додаткових відомостей про доступні агрегатні функції див. Статистичні функції SQL .
Завдання полів, які не використовуються в агрегатній функції: пропозиція GROUP BY
При використанні агрегатних функцій зазвичай потрібно створити пропозицію GROUP BY. У пропозиції GROUP BY вказуються всі поля, до яких не застосовується агрегатна функція. Якщо агрегатні функції застосовуються до всіх полів у запиті, GROUP BY пропозицію створювати не потрібно.
Пропозиція GROUP BY повинна йти відразу ж за пропозицією WHERE або FROM, якщо пропозиція WHERE відсутня. У пропозиції GROUP BY поля вказуються в тому самому порядку, що й у пропозиції SELECT.
Продовжимо попередній приклад. Нехай у пропозиції SELECT агрегатна функція застосовується тільки до поля [Адреса електронної пошти], тоді пропозиція GROUP BY буде виглядати наступним чином:
GROUP BY Company
Додаткові відомості про пропозицію GROUP BY див. у статті Пропозиція GROUP BY.
Обмеження агрегованих значень за умов групування: пропозиція HAVING
Якщо потрібно вказати умови для обмеження результатів, але поле, до якого їх потрібно застосувати, використовується в агрегованій функції, пропозицію WHERE використовувати не можна. Замість нього слід використовувати пропозицію HAVING. Пропозиція HAVING працює так само, як і WHERE, але використовується для агрегованих даних.
Припустимо, наприклад, що до першого поля у реченні SELECT застосовується функція AVG (яка обчислює середнє значення):
SELECT COUNT(), Company
Якщо ви хочете обмежити результати запиту на основі значення функції COUNT, до цього поля не можна застосувати умову відбору у пропозиції WHERE. Замість умови слід помістити в пропозицію HAVING. Наприклад, якщо потрібно, щоб запит повертав рядки тільки в тому випадку, якщо компанія має кілька адрес електронної пошти, можна використовувати таку пропозицію HAVING:
HAVING COUNT()>1
Примітка:Запит може включати і пропозицію WHERE, і пропозицію HAVING, при цьому умови відбору для полів, які не використовуються у статистичних функціях, зазначаються у пропозиції WHERE, а умови для полів, що використовуються у статистичних функціях, - у пропозиції HAVING.
Додаткові відомості про пропозицію HAVING див. у статті Пропозиція HAVING.
Об'єднання результатів запиту: оператор UNION
Оператор UNION використовується для одночасного перегляду всіх даних, що повертаються кількома запитами на вибірку, у вигляді об'єднаного набору.
Оператор UNION дозволяє об'єднати дві вказівки SELECT в одну. Інструкції SELECT, що об'єднуються, повинні мати однакове число і порядок вихідних полів з такими ж або сумісними типами даних. При виконанні запиту дані кожного набору відповідних полів об'єднуються в одне вихідне поле, тому вихідні дані запиту мають стільки ж полів, скільки і кожна інструкція SELECT окремо.
Примітка:У запитах на об'єднання числовий та текстовий типи даних сумісні.
Використовуючи оператор UNION, можна вказати, чи повинні до результатів запиту включатися рядки, що повторюються, якщо такі є. Для цього слід використати ключове слово ALL.
Запит на об'єднання двох інструкцій SELECT має наступний базовий синтаксис:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
Припустимо, наприклад, що є дві таблиці, які називаються "Товари" та "Послуги". Обидві таблиці містять поля з назвою товару або послуги, ціною та відомостями про гарантію, а також поле, в якому вказується ексклюзивність товару або послуги, що пропонується. Незважаючи на те, що в таблицях "Продукти" та "Послуги" передбачено різні типигарантій, основна інформація та сама (надається на окремі продукти або послуги гарантія якості). Для об'єднання чотирьох полів із двох таблиць можна використовувати наступний запит на об'єднання:
SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;
Щоб отримати додаткові відомості про поєднання інструкцій SELECT за допомогою оператора UNION, див.
Основні команди SQL, які має знати кожен програміст
Мова SQL або Structured Query Language (мова структурованих запитів) призначена для управління даними в системі реляційних баз даних (RDBMS). У цій статті буде розказано про часто використовувані команди SQL, з якими повинен бути знайомий кожен програміст. Цей матеріал ідеально підійде для тих, хто хоче освіжити знання про SQL перед співбесідою на роботу. Для цього розберіть наведені в статті приклади та згадайте, що проходили на парах баз даних.
Зверніть увагу, що в деяких системах баз даних потрібно вказувати точку з комою наприкінці кожного оператора. Точка з комою є стандартним покажчиком на кінець кожного оператора SQL. У прикладах використовується MySQL, тому точка з комою потрібна.
Налаштування бази даних для прикладів
Створіть базу для демонстрації роботи команд. Для роботи вам знадобиться завантажити два файли: DLL.sql і InsertStatements.sql. Після цього відкрийте термінал і увійдіть в консоль MySQL за допомогою наступної команди (стаття передбачає, що MySQL вже встановлений у системі):
Mysql -u root -p
Потім введіть пароль.
Виконайте наведену нижче команду. Назвемо базу даних «university»:
CREATE DATABASE university; USE university; SOURCE Може знадобитися створити обмеження для певних стовпців у таблиці. Під час створення таблиці можна задати такі обмеження: Можна встановити більше одного первинного ключа. В цьому випадку вийде складовий первинний ключ. Створіть таблицю "instructor": CREATE TABLE instructor (ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name)); Можна переглянути різні відомості (тип значень, є ключем чи ні) про стовпці таблиці наступною командою: DESCRIBE При додаванні даних до кожного стовпця таблиці не потрібно вказувати назви стовпців. INSERT INTO SELECT використовується для отримання даних із певної таблиці: SELECT Наступною командою можна вивести всі дані з таблиці: SELECT * FROM У стовпцях таблиці можуть міститися дані, що повторюються. Використовуйте SELECT DISTINCT для отримання неповторних даних. SELECT DISTINCT Можна використовувати ключове слово WHERE у SELECT для вказівки умов у запиті: SELECT У запиті можна задавати такі умови: Спробуйте виконати такі команди. Зверніть увагу на умови, задані у WHERE: SELECT * FROM course WHERE dept_name='Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3; Оператор GROUP BY часто використовується з агрегатними функціями, такими як COUNT , MAX , MIN , SUM та AVG для групування вихідних значень. SELECT Виведемо кількість курсів для кожного факультету: SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name; Ключове слово HAVING було додано до SQL тому, що WHERE не може бути використане для роботи з агрегатними функціями. SELECT Виведемо список факультетів, які мають більше одного курсу: SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1; ORDER BY використовується для сортування результатів запиту зі спадання або зростання. ORDER BY відсортує за зростанням, якщо не буде вказано спосіб сортування ASC або DESC. SELECT Виведемо список курсів щодо зростання та зменшення кількості кредитів: SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC; BETWEEN використовується для вибору значень даних із певного проміжку. Можуть бути використані числові та текстові значення, а також дати. SELECT Виведемо список інструкторів, чия зарплата більша за 50 000, але менша за 100 000: SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000; Оператор LIKE використовується в WHERE , щоб встановити шаблон пошуку схожого значення. Є два вільні оператори, які використовуються в LIKE: Виведемо список курсів, в імені яких міститься «to», та список курсів, назва яких починається з «CS-»: SELECT * FROM course WHERE title LIKE '%to%'; SELECT * FROM course WHERE course_id LIKE "CS-___"; За допомогою IN можна вказати кілька значень для оператора WHERE: SELECT Виведемо список студентів із напрямків Comp. Sci., Physics та Elec. Eng.: SELECT * FROM student WHERE dept_name IN ( 'Comp. Sci.', 'Physics', 'Elec. Eng.'); JOIN використовується для зв'язку двох або більше таблиць за допомогою загальних атрибутів усередині них. На зображенні нижче показано різні способи об'єднання SQL. Зверніть увагу на різницю між лівим зовнішнім об'єднанням та правим зовнішнім об'єднанням: SELECT Виведемо список усіх курсів та відповідну інформацію про факультети: SELECT * FROM course JOIN department ON course.dept_name=department.dept_name; Виведемо список усіх обов'язкових курсів та деталі про них: SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id; Виведемо список усіх курсів незалежно від того, обов'язкові вони чи ні: SELECT course.course_id, title, dept_name, credits, prereq_id FROM prereq RIGHT OUTER JOIN course ON prereq.course_id=course.course_id; View - це віртуальна таблиця SQL, створена результаті виконання висловлювання. Вона містить рядки та стовпці і дуже схожа на звичайну SQL-таблицю. View завжди показує найсвіжішу інформацію з бази даних. Створимо view, що складається з курсів із 3 кредитами: Ці функції використовуються для отримання сукупного результату, що відноситься до даних, що розглядаються. Нижче наведено загальновживані агреговані функції: Вкладені підзапити - це SQL-запити, які включають вирази SELECT , FROM і WHERE , що вкладені в інший запит. Знайдемо курси, які викладалися восени 2009 та навесні 2010 років: SELECT DISTINCT course_id FROM section WHERE semester = 'Fall' AND year= 2009 AND course_id IN (SELECT course_id FROM section WHERE semester = 'Spring' AND year= 2010); Мова запитів до баз даних SQLнародився 70-ті роки. Його прототип був розроблений фірмою IBM та відомий під назвою SEQUEL (Structured English QUEry Language). SQL увібрав у собі переваги реляційної моделі, зокрема те, що в її основі лежить математичний апарат реляційної алгебри та реляційного обчислення, використовуючи при цьому порівняно невелику кількість операторів та простий синтаксис. Завдяки своїм якостям мова SQL стала спочатку «де-факто», а потім і офіційно затвердженою як стандарт мови для роботи з реляційними базами даних, що підтримується всіма провідними світовими фірмами, що діють у галузі технології баз даних. Використання виразної та ефективної стандартної мови дозволило в даний час забезпечити високий ступінь незалежності прикладних програмних систем, що розробляються, від конкретного типу використовуваної СУБД, істотно підняти рівень та уніфікацію інструментальних засобів розробки додатків, що працюють з реляційними базами даних. Говорячи про стандарті мови SQL, слід зазначити, більшість його комерційних реалізацій мають більші чи менші відступи від стандарту. Це, звичайно, погіршує сумісність систем, які використовують різні діалекти SQL. З іншого боку, корисні розширення реалізацій мови щодо стандарту є засобом розвитку мови та згодом включаються до нових редакцій стандарту. Мові SQL присвячено велику кількість книг, у тому числі й навчального призначення, деякі з них наведені у списку літератури цього посібника, зокрема, навчальний посібник спеціально присвячений питанням практичного вивчення мови SQL. У зв'язку з цим у даному посібникуми розглянемо лише важливі загальні особливості цієї мови, що мають значення для подальшого викладу матеріалу. Мова SQL - відноситься до класу непроцедурних мов програмування. На відміну від універсальних процедурних мов, які також можуть бути використані для роботи з базами даних, мова SQL орієнтована не на записи, а на множини. Це означає таке. Як вхідна інформація для формульованого на мові SQL запиту до бази даних використовується безліч кортежів-записіводнієї чи кількох таблицьвідносин. В результаті виконання запиту також утворюється безліч кортежіврезультуючої таблиці-відносини. Тобто в SQL результатом будь-якої операції над відносинами також є ставлення. Запит SQL ставить не процедуру, тобто. послідовність дій, необхідні отримання результату, а умови, яким мають задовольняти кортежі результуючого відносини, сформульовані термінах вхідного (чи вхідних) відносин. Існують і використовуються дві форми мови SQL: інтерактивний SQL та вбудований SQL. Інтерактивний SQLвикористовується для безпосереднього введення та отримання результату SQL запитів користувачем в інтерактивному режимі. Вбудований SQL складається з команд SQL, вбудованих всередину програм, які зазвичай написані деякою іншою мовою (Паскаль, С, С++ та ін). Це робить програми, написані такими мовами, більш потужними та ефективними, забезпечуючи можливість роботи за допомогою них з даними, що зберігаються в реляційних базахданих, вимагаючи, проте, запровадження додаткових коштів, які забезпечують інтерфейс SQL з мовою, куди він вбудовується. І інтерактивний, і вбудований SQL зазвичай поділяють такі складові. Мова Визначення Даних– DDL (Data Definition Language), дає можливість створення, зміни та видалення різних об'єктів бази даних (таблиць, індексів, користувачів, привілеїв тощо). До числа додаткових функціймови визначення даних DDL можуть бути включені також засоби визначення обмежень цілісності даних, визначення порядку у структурах зберігання даних, опис елементів фізичного рівня зберігання даних. Мова обробки даних- DML (Data Manipulation Language), надає можливість вибірки інформації з бази даних і перетворення даних, що зберігається в ній. Тим не менш, це не дві різні мови, а компоненти єдиного SQL. Ключові слова – це слова, що використовуються у виразах SQL, які мають спеціальне призначення. Наприклад, вони можуть означати конкретні команди SQL. Ключові слова не можна використовувати для інших цілей, наприклад як імена об'єктів бази даних. Оператори SQL є інструкціями, за допомогою яких здійснюється звернення SQL до бази даних. Оператори складаються з однієї чи більше окремих логічних частин, які називаються пропозиціями. Пропозиції починаються відповідним ключовим словом і складаються з ключових слів та аргументів. Слід звернути увагу, що терміни, які у мові SQL, дещо відрізняються від термінів, прийнятих в описі реляційної моделі. Зокрема, замість терміну відношення в ньому використовується термін таблиця , замість термінів кортеж і атрибут відповідно рядок і стовпець . 8.4.
Вибір даних. Оператор SELECT Оператор SELECT (ВИБРАТИ ) мови SQL є найважливішим і найчастіше використовуваним оператором. Він призначений вибірки інформації з таблиць бази даних. Спрощений синтаксис оператора SELECT має такий вигляд. SELECT<
список атрибутів>
FROM<
список таблиц>
У квадратних дужках вказані елементи, які можуть бути відсутніми. Ключове слово SELECT повідомляє СУБД, що ця пропозиція є запитом на отримання інформації. Після слова SELECT через кому перераховуються найменування полів (список атрибутів), вміст яких запитується. Обов'язковим ключовим словом у реченні-запиті SELECT є слово FROM (з). За ключовим словом FROM вказується список розділених ком імен таблиць, з яких витягується інформація. Наприклад, SELECT NAME, SURNAME FROM STUDENT; SQL-запит повинен закінчуватися символом "точка з комою". Наведений запит здійснює вибірку всіх значень полів NAME та SURNAME з таблиці STUDENT. Його результатом є таблиця наступного виду Порядок прямування стовпців у цій таблиці відповідає порядку полів NAME та SURNAME , вказаному у запиті, а не їх порядку у вхідній таблиці STUDENT. Звернімо увагу, що отримані в результаті SQL-запиту таблиці не повною мірою відповідають визначенню реляційного відношення. У зокрема в них можуть виявитися дублікати кортежів з однаковими значеннями атрибутів. Наприклад, запит: “Отримати список назв міст, в яких живуть студенти, відомості про які перебувають у таблиці STUDENT ”, можна записати у такому вигляді SELECT CITY FROM STUDENT; Його результатом буде таблиця Білгород Можна побачити, що у цій таблиці можуть зустрічатися однакові рядки. Вони виділені жирним шрифтом. Для виключення з результату SELECT -запиту записів, що повторюються використовується ключове слово DISTINCT (відмінний). Якщо запит SELECT витягує безліч полів, DISTINCT виключає дублікати рядків, у яких значення всіх вибраних полів ідентичні. Введення у вираз оператора SELECT , речення, яке визначається ключовим словом WHERE (де), дозволяє вводити вираз умови (предикат), що приймає значення істина або брехня для значень полів рядків таблиць, до яких звертається оператор SELECT . Пропозиція WHERE визначає, які рядки вказаних таблиць мають бути обрані. До таблиці, що є результатом запиту, включаються лише ті рядки, для яких умова (предикат), зазначена у пропозиції WHERE , набуває значення істина. Написати запит, який виконує вибірку імен (NAME ) всіх студентів з прізвищем (SURNAME ) Петров, відомості про які знаходяться в таблиці SELECT SURNAME, NAME FROM STUDENT WHERE SURNAME = 'Петров'; У умовах, що задаються в пропозиції WHERE, можуть використовуватися операції порівняння, що задаються наступними операторами: = (рівно), > (більше),< (меньше), >= (Більше або одно),<= (меньше или равно), <>(Не дорівнює), а також логічні оператори AND, OR та NOT. Наприклад, запит для отримання імен та прізвищ студентів, які навчаються на третьому курсі та отримують стипендію (розмір стипендії більший за нуль) буде виглядати таким чином SELECT NAME, SURNAME FROM STUDENT WHERE KURS = 3 AND STIPEND > 0; 8.5.
Реалізація операцій реляційної алгебри засобами мови SQL. Реляційна повнота SQL У попередніх розділах, присвячених розгляду реляційної алгебри, говорилося, що однією з важливих сторін наявності в реляційній моделі такого математичного апарату є можливість оцінки та доказиреляційної повнотиПрактично використовуються мови запитів до баз даних, зокрема мови SQL. Для того, щоб показати, що мова SQL є реляційно повною, потрібно показати, що будь-який оператор реляційної алгебри може бути виражений засобами SQL. Насправді досить показати, що засобами SQL можна висловити будь-який із примітивних реляційних операторів. Нижче наведено приклади реалізації реляційних операторів за допомогою мови SQL. Оператор об'єднання Реляційна алгебра: A UNION B Оператор SQL: SELECT * FROM A SELECT * FROM B; Оператор перетину Реляційна алгебра: A INTERSECT B Оператор SQL: SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, FROM A, B WHERE A. ПОЛЕ1=B. ПОЛЕ1 AND A. ПОЛЕ2=B. ПОЛЕ2 AND …; SELECT A.* FROM A, B WHERE A.pk = B.pk; Оператор віднімання Реляційна алгебра: A MINUS B Оператор SQL: SELECT * FROM A WHERE A.pk NOT IN (SELECT pk FROM B); де A.pk та B.pk первинні ключі таблиць A та B Оператор декартового твору Реляційна алгебра: A TIMES B Оператор SQL: FROM A, B; SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, … FROM A CROSS JOIN B; Оператор проекції Реляційна алгебра: A Оператор SQL: SELECT DISTINCT X, Y, …, Z FROM A; Оператор вибірки Реляційна алгебра: A WHERE θ Оператор SQL: SELECT * FROM A WHERE θ; Оператор θ-з'єднання Реляційна алгебра: (A TIMES B) WHERE θ Оператор SQL: SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, … FROM A, B WHERE θ; SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, … FROM A CROSS JOIN B WHERE θ; Оператор поділу Реляційна алгебра: A(X,Y) DEVIDE BY B(Y) Оператор SQL: SELECT DISTINCT A. X FROM A (SELECT * (SELECT * FROM A A1 A1. X = A. X AND A1. Y=B. Y)); Таким чином, наведені вирази доводять, що мова SQL, як реляційна алгебра, є реляційно повним. Слід звернути увагу, якщо у наведених запитах у таблицях будуть присутні NULL -значення (див. нижче розділ 9.1), всі вищеперелічені запити можуть відпрацювати неправильно, т.к. NULL< >NULL та NULL = NULL – є брехня. Це, проте, не спростовує зробленого висновку про реляційну повноту SQL, оскільки NULL -значення реляційною моделлюне підтримуються.Команди для роботи з базами даних
1. Перегляд доступних баз даних
SHOW DATABASES; 2. Створення нової бази даних
CREATE DATABASE; 3. Вибір бази даних для використання
USE 4. Імпорт SQL-команд із файла.sql
SOURCE 5. Видалення бази даних
DROP DATABASE Робота з таблицями
6. Перегляд таблиць, доступних у базі даних
SHOW TABLES; 
7. Створення нової таблиці
CREATE TABLE Обмеження цілісності під час використання CREATE TABLE
приклад
8. Відомості про таблицю
9. Додавання даних до таблиці
INSERT INTO 10. Оновлення даних таблиці
UPDATE 11. Видалення всіх даних із таблиці
DELETE FROM 12. Видалення таблиці
DROP TABLE Команди для створення запитів
13. SELECT
14. SELECT DISTINCT
15. WHERE
приклад
16. GROUP BY
приклад
17. HAVING
приклад
18. ORDER BY
приклад
19. BETWEEN
приклад
20. LIKE
SELECT приклад
21. IN
приклад
22. JOIN
Приклад 1
Приклад 2
Приклад 3
23. View
створення
CREATE VIEW Вилучення
DROP VIEW приклад
24. Агрегатні функції
25. Вкладені підзапити
приклад
8.1.
Відмінність SQL від процедурних мов програмування
8.2.
Форми та складові частини SQL
8.3.
Умови та термінологія
Найпростіші SELECT- запити