11. Агрегатні функції
За допомогою запиту можна одержувати узагальнене групове значення полів так само як і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції повертають одне значення для всієї групи таблиці. Всього таких функцій, що підтримуються будь-яким діалектом мови SQL, є 5:
*COUNT (лічильник) виробляє кількість рядків або не-NULL значення полів, які вибрав запит.
*SUM (Сума) знаходить арифметичну суму всіх вибраних значень даного поля.
*AVG (Середнє) виконує усереднення всіх вибраних значень даного поля.
*MAX знаходить найбільше з усіх вибраних значень даного поля.
*MIN знаходить найменше з усіх вибраних значень даного поля.
Агрегатні функції використовуються подібно іменам полів у команді SELECT запиту. Їх аргументами є імена стовпців. З SUM і AVG можуть використовуватись лише числові поля. Функції COUNT, MAX, и MIN, допускають використання і числових і символьних полів. Коли вони використовуються з символьними полями, MAX і MIN будуть транслювати їх у еквівалент ASCII, за яким MIN буде означати перше, а MAX – останнє значення за алфавітом.
AVG() – повертає середнє арифметичне значень для заданого стовпця. Результатом буде таблиця з додатковим стовпцем, що міститиме середнє значення, причому йому можна давати окрему назву:
SELECT AVG(amt) as avamt
From Orders;
Функція COUNT дещо відрізняється від усіх. Вона повертає кількість значень у даному столпці, або кількість рядків у таблиці.
Select count(snum) from orders;
Коли вона проводить підрахунок наявних значень у тому чи іншому стовпчику, перед іменем аргументу можна використовувати необов¢язкове ключове слово DISTINCT, яке вказує, що перед застосуванням цієї функції дублюючі рядки потрібно ігнорувати при підрахунку. Тобто виконується підрахунок лише унікальних значень у деякому полі. Можна використовувати її, наприклад, для підрахунку числа продавців які включені до таблиці.
МАХ(), MIN() повертають, відповідно найбільше і найменше значення стовпчика. Причому пошук можна здійснювати не серед всіх значень стовпчика, а лише у тих рядках, які задовольняють критерію, вказаному в директиві WHERE:
SELECT MAX (amt) as maxamt, MIN(amt) as minamt
FROM Orders
Пропозиція GROUP BY дозволяє визначити підмножену значень у певному полі в термінах іншого поля, і застосувати функцію агрегату до підмножини. Це дає можливість об¢єднувати поля і агрегатні функції у єдиній команді SELECT.
Згідно строгій інтерпретації ANSI SQL, не можна використовувати агрегат агрегату.
12. Операції з’єднання відношень
Практична робота з БД показала, що для уникнення надлишковості інформації, що зберігається, і водночас для забезпечення її цілісності, цю інформацію доцільно розподілити між двома або більше таблицями. Коли ж потрібно переглянути загальні дані для деякої предметної області проводиться з’єднання таблиць, в результаті якого інформація однієї таблиці доповнюється даними з інших, зв’язаних з нею таблиць. Такі таблиці повинні мати спільні стовпці (найчастіше це поля первинного та зовнішнього ключів), що вказуються у директиві WHERE. З операцією з’єднання ми зустрічалися при розгляді операцій реляційної алгебри і познайомилися з операціями природного з’єднання, з’єднання за умовою та еквіз’єднанням. Саме про останній варіант піде мова сьогодні, а саме про його різновиди. Розрізняють внутрішнє (INNER JOIN) та зовнішнє з’єднання (OUTER JOIN). Останнє ділиться в свою чергу на ліве LEFT та праве RIGHT зовнішні з’єднання (OUTER JOIN).
Внутрішнє з’єднання дозволяє повернути з двох таблиць лише ті рядки, які відповідають значенням зі спільних стовпців. Всі решта рядків ігноруються при такому з’єднанні.
1) SELECT A.A#, Aname, B#, Bname
WHERE A.A#=B.A#;
Зовнішнє з’єднання використовується для того, щоб вибирати з таблиці рядки, що містять як відповідні так і не відповідні значення для двох таблиць. Невідповідними є поля тих рядків, які не знаходять у пов’язаній таблиці
Операція зовнішнього з’єднання є позиціонною, тобто залежить від того до якої таблиці приєднуються рядки і яка таблиця є тією, що приєднується. Тому саме розрізняють операції лівого та правого зовнішніх з’єднань. При лівому зовнішньому з’єднанні повертаються невідповідні рядки з таблиці, що знаходиться ліворуч , а при правому – з таблиці, що знаходиться праворуч.
Записується оператор лівого зовнішнього з’єднання наступним чином:
2) SELECT A.A#, A.name, B#, Bname
WHERE A.A# *= B.A#;
При правому зовнішньому з’єднанні за основу береться таблиця, що стоїть справа від оператора з’єднання і до неї приєднуються відповідні поля з лівої таблиці, які відповідають значенням загального стовпчика, а поля, які не мають еквівалентів, заповнюються нулями.
13. Створення базових відношень за допомогою оператора CREATE TABLE
На основі реляційних значень, присвоєних деякій множині змінних-відношень, реляційні вирази дозволяють одержувати безліч інших реляційних значень, наприклад в результаті з¢єднання двох змінних-відношень. Вихідні (задані) змінні-відношення називають базовими змінними-відношеннями (реальні), а присвоєнні їм значення – базовими відношеннями. Відношення, яке одержане з базового відношення в результаті виконання деяких реляційних виразів, називається похідним відношенням.
Реляційні системи надають засоби для створення, в першу чергу базових змінних-відношень. На мові SQL, наприклад, ця функція забезпечується оператором CREATE TABLE EMP…;
Якщо за допомогою засобів мови SQL визначають базу даних, то для кожної базової таблиці визначення містить один оператор CREATE TABLE ім¢я базової таблиці (список елементів таблиці);. Під елементом таблиці розуміють найчастіше визначення стовпчика, яке має наступний вигляд:
<ім¢я стовпчика>< тип/ім¢я домена>[<значення за замовчуванням>]
CREATE TABLE<table name>
(<column name><data type><column constraint>,
<column name><data type><column constraint>,);
CREATE TABLE S// suppliers
(S#CHAR(5),
SNAMECHAR(20),
STATUSNUMERIC(5),
CITYCHAR(15),
PRIMARY KEY (S#));
14. Обмеження відношень
Крім зазаначення безпосередньо типів та розміру полів таблиці можна задавати обмеження з метою забезпечення цілісності таблиці. При створенні таблиці (або, при її зміні), можна накладати обмеження на значення які можуть вводитися в поля. Якщо це зробити, SQL буде відхиляти будь-які значення які порушують критерії які визначені для того чи іншого поля.
Щодо інших потенціальних ключів, то вони є альтернативними ключами, і для позначення їх унікальності використовують унікальне обмеження (UNIQUE), яке гарантує відсутність дублікатів стовпця. Можна визначити групу полів як унікальну за допомогою команди обмеження таблиці — UNIQUE. Оголошення унікальності групи означає не унікальність значень складових цієї групи, а унікальність їх комбінації.
Обмеження цілісності за посиланням, або зовнішній ключ. Після створення в одній таблиці поля первинного ключа, значення цього поля можуть використовуватися в іншій таблиці (таблицях) БД для створення істинних тверджень при характеристиці різних сутностей. В інших таблицях це поле вже не є унікальним, має назву поля зовнішнього ключа. FOREIGN KEY пов’язує один або декілька стовпців таблиці з ідентичним номером стовпців іншої таблиці. Його значення не можуть вийти за межі діапазону значень, який у першопочатковій таблиці задається значеннями цього ж поля але вже первинного ключа. Таким чином обмеження FOREIGN KEY використовується лише після створення у відповідній таблиці обмеження PRIMARY KEY для цього є поля. Причому, ці поля не обов’язково повинні мати однакову назв (хоча це бажано), але повинні мати один тип і однакову довжину. Коли в таблиці, де визначено обмеження PRIMARY KEY обновлюються значення стовпців, автоматично обновлюються значення в таблиці, для якої визначено обмеження FOREIGN KEY.
Обмеження СНЕСК (перевірка). Можна запобігти небажаним або помилковим введенням значень. Для цього використовується обмеження СНЕСК. Воно накладає умову на значення, що вводиться, які можна ввести в один або декілька стовпців таблиці. Це область з якої стовпець може одержувати свої значення. СНЕСК виражається як нулевий вираз, який приймає значення true або false.
Обмеження DEFAULT (значення за замовчуванням). Коли ви вставляєте новий рядок у таблицю без вказання одного чи декількох значень чи при введені інформації залишаєте незаповненими поля, SQL повинен мати значення за замовчуванням для заповнення цих пропусків. Найбільш загальним значенням за замовчуванням є — NULL. Це значення за замовчуванням для будь-якого стовпця, якому не надано обмеження NOT NULL. Значення DEFAULT вказується в команді CREATE TABLE у той самий спосіб що і обмеження стовпця, хоча, з технічної точки зору, воно не має обмежуючих властивостей — воно не обмежує значення, які можна вводити, а просто визначає, що може статися, якщо не ввести будь-яке з них. В якості обмеження обирають значення, що найчастіше зустрічається у стопці.
15. Групування даних таблиці за допомогою директив ORDER BY і GROUP BY
Таблиці — це невпорядковані набори рядків і дані, які входять до них, не обов’язково з’являються в деякій визначеній послідовності. SQL використовує команду ORDER BY щоб дозволити користувачеві впорядкувати ваше виведення. Ця команда впорядковує виведення запиту згідно зі значеннями в тій чи іншій кількості обраних стовпців. Для кожного стовпця можна встановити як зростання (ASC) так і спадання (DESC). За замовчуванням встановлюється зростання. Для впорядкування даних таблиці за спаданням номеру груп запишемо таку команду: