Мова SQL. базовий курс

SQL (зазвичай вимовлена ​​як «СІКВЕЛ») символізує собою «Структурований Мова Запитів». Це – мова, яка дає вам можливість створювати та працювати в реляційних базах даних, які є наборами пов'язаної інформації, що зберігається в таблицях. Якщо Ви поставили питання з чого почати вивчення SQL, то Вам до нас. Наша фірма пропонує Вам курси SQL, а також уроки SQL для початківців, після вивчення яких створення бази даних SQL Ви зможете зробити самостійно. Вивчення SQL – це дуже перспективне заняття. Воно включає SQL лекції, курси бази даних SQL, а також SQL запити курси. Є можливість пройти курси мова SQL та провести навчання SQL з нуля, а також пройти курси SQL програмуванняза допомогою SQL навчання онлайн, отримавши ексклюзивну практику

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

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

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

Стандарт SQL визначається ANSI (Американським Національним Інститутом Стандартів) і зараз також приймається ISO (Міжнародною організацією зі стандартизації). Однак більшість комерційних програм баз даних розширюють SQL без повідомлення ANSI, додаючи різні інші особливості в цю мову, які, як вони вважають, будуть дуже корисними. Іноді вони дещо порушують стандарт мови, хоча гарні ідеїмають тенденцію розвиватися і незабаром ставати стандартами "ринку" власними силами через корисність своїх якостей. І Ви можете вивчити всі ці функції, пройшовши курси навчання SQL у нашому офісі або пройти вивчення SQL онлайн.

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

Як ми підкреслювали раніше, SQL символізує собою структуровану мову запитів. Вивчення SQL запитіві створення запитів у базі даних - ймовірно найчастіше використовуваний аспект SQL. Фактично для категорії SQL користувачів, малоймовірно щоб хтось використовував цю мову для чогось іншого. З цієї причини, ми почнемо нашу мову SQL вивчення з обговорення запиту і як він виконується цією мовою.

Запит - команда, яку ви даєте вашій програмі бази даних, і яка повідомляє їй, щоб вона вивела певну інформацію з таблиць у пам'ять. Ця інформація зазвичай надсилається безпосередньо на екран комп'ютера або терміналу, яким ви користуєтеся, хоча, в більшості випадків, її можна також надіслати принтеру, зберегти у файлі (як об'єкт у пам'яті комп'ютера), або уявити, як вступну інформацію для іншої команди або процесу.

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

Всі запити SQL складаються з одиночної команди. Структура цієї команди оманливо проста, тому що ви повинні розширювати її так, щоб виконати високо складні оцінки та обробки даних. Ця команда називається - SELECT (ВИБІР).

Мова SQL. Базовий курс.

У цьому навчальному посібнику, написаному Євгеном Моргуновим, викладаються основи SQL — це базовий курс. Причому мова розглядається стосовно конкретної системи управління базами даних (СУБД) — PostgreSQL. Реалізація мови SQL у кожному СУБД відповідає стандарту у тому чи іншою мірою, але крім стандартизованих функцій і можливостей, кожна СУБД пропонує свої додаткові розширення мови. PostgreSQL забезпечує дуже гарну підтримкустандарту мови SQL і також надає цікаві та практично корисні додаткові можливості. Однією з головних переваг PostgreSQL є розширюваність. Це означає, наприклад, що користувач (звичайно фахівець у галузі баз даних) може розробити свої власні типи даних. Ці типи даних будуть мати всі властивості вбудованих типів даних і можуть бути введені в роботу без зупинки сервера. Крім того, PostgreSQL є вільно розповсюджуваним продуктом з відкритим вихідним кодом, який доступний на великій кількості платформ.

У посібнику розглядаються не тільки всі основні команди мови SQL, але також інші питання, такі, як індекси і транзакції. Посібник написаний таким чином, щоб його можна було використовувати як під керівництвом викладача, так і самостійно. Передбачається, що студенти мають доступ до вже встановленої СУБД, тому процедура встановлення PostgreSQL детально не розглядається, а лише даються вказівки про те, де знайти інструкції зі встановлення. Цей посібник призначений для отримання практичних навичок використання мови SQL. Навчальний матеріал подається таким чином, що багато важливих знань читач повинен отримати в результаті виконання завдань, що знаходяться наприкінці кожного розділу.

Здебільшого тексті розділів ці знання може бути не представлені. Передбачається, що значна частина завдань виконуватиметься читачем самостійно за допомогою документації на СУБД PostgreSQL, але найчастіше даються вказівки до їх виконання.

Посібник призначений для студентів, які навчаються за напрямами 09.03.01 – «Інформатика та обчислювальна техніка», 09.03.02 – «Інформаційні системи та технології», 09.03.03 – «Прикладна інформатика», 09.03.04 – «Програмна інженерія. 03 - «Математичне забезпечення та адміністрування інформаційних систем». Воно може бути корисним широкому колу студентів та фахівців, які бажають ознайомитися з основами мови SQL у середовищі системи управління базами даних PostgreSQL.

Видавництво - Postgres Professional

Рік видання – 2017

Формат книги - PDF

Транскрипт

2 Компанія Postgres Professional Е. П. Моргунов МОВА SQL. БАЗОВИЙ КУРС НАВЧАЛЬНО-ПРАКТИЧНИЙ ПОСІБНИК Москва 2017

3 УДК ББК М79 М79 Моргунов, Є. П. Мова SQL. Базовий курс: навч.-практ. посібник / Є. П. Моргунов; за ред. Є. В. Рогова, П. В. Лузанова; Postgres Professional. М., с. Даний навчально-практичний посібник є першою, базовою, частиною навчального курсуз мови SQL, пропонованого російською компанією Postgres Professional. Навчальний матеріал викладається у розрахунку використання системи управління базами даних PostgreSQL. Посібник може використовуватися як під керівництвом викладача, так самостійного вивчення мови SQL. Посібник призначений для студентів, які навчаються за напрямами «Інформатика та обчислювальна техніка», «Інформаційні системи та технології», «Прикладна інформатика», «Програмна інженерія» та «Математичне забезпечення та адміністрування інформаційних систем». Воно може бути корисним широкому колу студентів та фахівців, які бажають ознайомитися з основами мови SQL у середовищі системи управління базами даних PostgreSQL. УДК ББК Postgres Professional, 2017 Є. П. Моргунов, 2017

4 Зміст Вступ 5 1 Введення в бази даних та SQL Що таке бази даних і навіщо вони потрібні Основні поняття реляційної моделіЩо таке мова SQL Опис предметної області та навчальної бази даних Контрольні питання та завдання Створення робочого середовища Установка СУБД Програма psql інтерактивний термінал PostgreSQL Розгортання навчальної бази даних Контрольні питання та завдання Основні операції з таблицями 22 Контрольні питання та завдання Типи даних СУБД PostgreSQL Числові типи рядкові) типи Типи «дата/час» Логічний тип Масиви Типи JSON Контрольні питання та завдання Основи мови визначення даних Значення за умовчанням та обмеження цілісності Створення та видалення таблиць Модифікація таблиць Подання Схеми бази даних Контрольні питання та завдання Запити Додаткові можливості команди SELECT угруповання Підзапити Контрольні питання та завдання Зміна даних Вставка рядків у таблиці Оновлення рядків у таблицях Видалення рядків із таблиць Контрольні питання та завдання

5 8 Індекси Загальна інформаціяІндекси за декількома стовпцями Унікальні індекси Індекси на основі виразів Часткові індекси Контрольні питання та завдання Транзакції Рівень ізоляції READ UNCOMMITTED Рівень ізоляції READ COMMITTED Рівень ізоляції REPEATABLE READ Рівень ізоляції Контроль а таблиць Методи формування з'єднань наборів рядків Управління планувальником Оптимізація запитів Контрольні питання та завдання Рекомендовані джерела 255 4

6 Вступ В даний час термін «база даних» відомий багатьом людям, навіть далеким від професійної розробки комп'ютерних програм. Бази даних стали дуже поширеною технологією, що зажадало, у свою чергу, більшого числафахівців, здатних проектувати їх та обслуговувати. У ході еволюції теорії та практики баз даних стандартом де-факто стала реляційна модель даних, а в рамках цієї моделі сформувалася і спеціалізована мова програмування, що дозволяє виконувати всі необхідні операції з даними Structured Query Language (SQL). Таким чином, важливим компонентомкваліфікації спеціаліста у галузі баз даних є володіння мовою SQL. У цьому навчальному посібнику викладаються основи мови SQL – це базовий курс. Причому, мова розглядається стосовно конкретної системи управління базами даних (СУБД) PostgreSQL. Реалізація мови SQL у кожному СУБД відповідає стандарту у тому чи іншою мірою, але крім стандартизованих функцій і можливостей, кожна СУБД пропонує свої додаткові розширення мови. PostgreSQL забезпечує дуже хорошу підтримку стандарту мови SQL і надає цікаві та практично корисні додаткові можливості. Однією з головних переваг PostgreSQL є розширюваність. Це означає, наприклад, що користувач (звичайно фахівець у галузі баз даних) може розробити свої власні типи даних. Ці типи даних будуть мати всі властивості вбудованих типів даних і можуть бути введені в роботу без зупинки сервера. Крім того, PostgreSQL є вільнорозповсюджуваним продуктом з відкритим вихідним кодом, який доступний на великій кількості платформ. У посібнику розглядаються не тільки всі основні команди мови SQL, але також інші питання, такі, як індекси і транзакції. Посібник написаний таким чином, щоб його можна було використовувати як під керівництвом викладача, так і самостійно. Передбачається, що студенти мають доступ до вже встановленої СУБД, тому процедура встановлення PostgreSQL детально не розглядається, а лише даються вказівки про те, де знайти інструкції зі встановлення. Цей посібник призначений для отримання практичних навичок використання мови SQL. Навчальний матеріал подається таким чином, що багато важливих знань читач повинен отримати в результаті виконання завдань, що знаходяться наприкінці кожного розділу. Здебільшого тексті розділів ці знання може бути не представлені. Передбачається, що значна частина завдань виконуватиметься читачем самостійно за допомогою документації на СУБД PostgreSQL, але найчастіше даються вказівки до їх виконання. Завдання, наведені у посібнику, розрізняються за рівнем складності. Найскладніші з них, а також ті, що вимагають багато часу для виконання, відзначені зірочкою. Завдання можна виконувати з вивчення навчального матеріалу конкретного розділу. Однак деякі з них мають комплексний характер, тому для їх виконання необхідно вивчити весь розділ або, як мінімум, кілька розділів. 5

7 Хоча посібник має практичну спрямованість і не є теоретичним курсом, все ж таки в першому розділі коротко, на елементарному рівні викладаються основні поняття теорії баз даних і реляційної моделі. Це зроблено для того, щоб студенти могли розпочати практичне освоєння мови SQL без затримки, з перших днів навчального семестру, ще до того моменту, коли ці поняття будуть ґрунтовно розглянуті в лекційному курсі. На факультетах інформаційних технологійу російських вузах бази даних традиційно вивчаються на другому чи третьому курсі. Причому цій дисципліні, як правило, приділяється один семестр. Однак кількість академічних навчальних годин може різнитися. Якщо на практичні заняття з цієї дисципліни навчальний план відводить 36 годин, тоді ми рекомендуємо наступне розподілення часу на вивчення матеріалу посібника. Глава 1. Введення бази даних і SQL Глава 2. Створення робочого середовища Глава 3. Основні операції з таблицями Глава 4. Типи даних СУБД PostgreSQL Глава 5. Основи мови визначення даних Глава 6. Запити Глава 7. Зміна даних Глава 8. Індекси Глава 9. Транзакції Глава 10. Підвищення продуктивності 1 година 1 година 4 години 4 години 4 години 8 годин 4 години 2 години 4 години 4 години Глави 1 та 2 можуть бути вивчені за одне двогодинне заняття, оскільки СУБД PostgreSQL вже має бути встановлена ​​у навчальній аудиторії заздалегідь. Глава 3 є коротким оглядом основних можливостей мови SQL, після її вивчення студенти повинні уявляти собі прості способивикористання всіх основних команд мови. Цей розділ не дуже складний, але об'ємний, тому на його вивчення відводиться чотири години. Глава 4 присвячена розгляду основних типів даних, які у PostgreSQL. Це великий розділ, однак у ній значну частину складають завдання та вправи. Передбачається, що студенти за чотири години мають засвоїти лише основні прийоми використання типів даних. А для того, щоб знання закріпилися, рекомендується звертатися до матеріалу цього розділу (у тому числі і до вправ) у процесі вивчення інших розділів посібника, у разі необхідності уточнення тих чи інших особливостей застосування конкретних типів. Розподілити час, виділений на вивчення цього розділу, ми рекомендуємо наступним чином: дві години на перші чотири параграфи числові та рядкові типи, типи «дата/час» та логічний тип, ще дві години на масиви та тип json/jsonb. Щоб виконувати запити до бази даних, необхідно добре розуміти її структуру, взаємозв'язок таблиць. Тому глава 5, в якій розглядаються основи мови визначення даних, дуже важлива з погляду детального вивчення таблиць бази даних «Авіаперевезення» та підготовки до освоєння глави 6. Оскільки матеріал глави заснований на тому, що база даних вже розгорнута на комп'ютері студента, то вводити команди для створення таблиць не потрібно. Це дозволяє скоротити час, що витрачається вивчення глави. У посібнику прийнято підхід, у якому спочатку розглядаються команди визначення даних, та був команди маніпулювання даними. Тому розділ 5 «Основи мови визначення даних» передує 6

8 главі 6 "Запити". Проте обраний підхід реалізується не надто жорстко: у оглядовій главі 3 розглядаються основні команди, зокрема, і нескладні запити. А запити це вже мова маніпулювання даними. На вивчення глави 5 відведено чотири години. Протягом першого двогодинного заняття потрібно вивчити два перших параграфи, у яких висвітлюються такі питання, як обмеження цілісності та створення та видалення таблиць. Друге двогодинне заняття потрібно присвятити вивченню трьох параграфів, що залишилися. Вони йдеться про способи модифікації таблиць, і навіть уявленнях (views) і схемах бази даних. Глава 6 є центральною главою посібника, тому її вивчення відводиться вісім годин, т. е. більше, ніж вивчення інших глав. Вона складається із чотирьох параграфів. Перший із них присвячений різноманітним додатковим можливостям команди SELECT. Йдеться, зокрема, про такі речі, як пропозиції LIMIT та OFFSET, оператор LIKE та регулярні висловлювання в умовах пропозиції WHERE та про інші можливості. Тим не менш, матеріал цього параграфа нескладний, для його вивчення достатньо виділити одну годину. У другому параграфі розповідається про способи з'єднання таблиць. Це складніша тема, на її вивчення необхідно виділити дві години. Третій параграф присвячений агрегуванню та угрупованню. У ньому розглядається і така важлива та цікава тема, як віконні функції. Цей параграф також потребує двогодинного заняття. Найскладніший розділ цього розділу четвертий. Він присвячений підзапиту. У ньому зокрема висвітлюється така важлива і цікава тема, як загальні табличні вирази (Common Table Expressions CTE). Для вивчення матеріалу даного параграфа необхідно виділити три години. У розділі 7 зібрані всі команди, призначені зміни даних: вставка рядків, їх оновлення та видалення. Оскільки попередніх розділах ці команди вже використовувалися на вирішення найпростіших завдань, то цьому розділі розглядаються складніші способи їх використання. У ній багато вправ, вони становлять половину її обсягу. Рекомендується приділити дві години вивченню способів вставки рядків у таблиці, а ще дві години розгляду операцій оновлення та видалення рядків. Розділ 8 присвячений індексам, він невеликий, тому з нею можна ознайомитися за одне двогодинне заняття. Оскільки індекси тісно пов'язані з питаннями продуктивності, тобто швидкості виконання запитів, було б доцільно після вивчення заключної глави повернутися до глави 8 і подивитися на представлені в ній команди та запити, вже знаючи про команду EXPLAIN. Транзакціям присвячено розділ 9. Механізми їх виконання мають багато тонкощів, тому при вивченні цього розділу необхідно експериментувати і намагатися пояснити отримані результати. У заключній главі 10 розглядаються питання підвищення продуктивності. Цей розділ може здатися занадто абстрактним і складним для початкового курсу мови SQL, проте він дуже важливий. Студенти повинні навчитися читати плани виконання запитів та розуміти призначення кожної операції, поданої у плані. А оволодіння мистецтвом оптимізації запитів вимагатиме багато часу та досвіду, воно прийде не одразу. У тому випадку, коли на практичні заняття з дисципліни «Бази даних» у навчальному плані відводиться 54 години, можна змінити пропонований розподіл навчального годинника. Зокрема, у розділі 4 можна більше часу присвятити типу даних 7

9 json/jsonb та масивам. У розділі 6 можна детальніше розглянути віконні функції та загальні табличні вирази. При вивченні глави 9, присвяченої транзакцій, було б доцільно розробити нескладне додаток, в якому використовувалися б транзакції, і провести експерименти з цим додатком, виконуючи паралельно кілька сеансів і змінюючи рівні ізоляції транзакцій. У рамках глави 10 є сенс повернутися до команд і запитів глави 8 та вивчити плани їх виконання за допомогою команди EXPLAIN. За рахунок додаткового часу можна розглянути всі завдання та вправи підвищеної складності (помічені зірочкою). Таким чином, розподіл часу може бути таким: Розділ 1. Введення в бази даних і SQL Розділ 2. Створення робочого середовища Розділ 3. Основні операції з таблицями Розділ 4. Типи даних СУБД PostgreSQL Розділ 5. Основи мови визначення даних Розділ 6. Запити Розділ 7. Зміна даних Глава 8. Індекси Глава 9. Транзакції Глава 10. Підвищення продуктивності 1 година 1 година 4 години 6 годин 6 годин 12 годин 6 годин 4 години 8 годин 6 годин У посібнику використовуються різні видишрифтів виділення фрагментів тексту залежно від призначення. Команди, які вводять користувач як у середовищі операційної системи, і у середовищі утиліти psql, виділяються напівжирним моноширинним шрифтом. Наприклад: psql -d demo -U postgres або SELECT avg(total_amount) FROM bookings; Результати роботи команд операційної системи та SQL-команд, виконуваних серед утиліти psql, надруковані моноширинним шрифтом. Наприклад, у відповідь команду EXPLAIN SELECT * FROM aircrafts; на екран буде виведено наступне: QUERY PLAN Seq Scan on aircrafts (cost= rows=9 width=52) (1 рядок) Ми сподіваємося, що вивчення матеріалу, викладеного в навчальному посібнику, сприятиме підвищенню рівня вашої кваліфікації та розширенню професійного кругозору. 8

10 1 Вступ до бази даних та SQL Цей розділ вступний. В ній ми розповімо про основи баз даних, про те, що таке реляційна модель і навіщо потрібна мова SQL. Дуже важливою темою цього розділу стане опис предметної області, на основі якої буде спроектована навчальна база даних, яка і служитиме як майданчик для вивчення мови SQL. Цей посібник призначений насамперед для практичного освоєння мови SQL, а чи не вивчення теорії баз даних, тому вивчення теорії необхідно звертатися до авторитетним джерелам, список яких наведено наприкінці навчального посібника. 1.1 Що таке бази даних і навіщо вони потрібні? Технології баз даних існували не завжди. Однак і до їх впровадження в практику люди також збирали та обробляли дані. Одним із способів зберігання даних були так звані плоскі файли (flat files), які мали дуже просту структуру: дані зберігалися як записів, розділених на поля фіксованої довжини. У реальному житті між елементами даних найчастіше виникають складні зв'язки, які необхідно перенести і до електронної бази даних. При використанні плоских файлів ці зв'язки організувати складно, а ще складніше підтримувати їх при змінах та видаленнях окремих елементівданих. Однією з основних понять теорії баз даних є модель даних. Можна сказати, що вона характеризує спосіб організації даних та основні методи доступу до них. Спочатку були запропоновані ієрархічна та мережева моделі даних. Проте в ході еволюції теорій та ідей було розроблено реляційну модель даних, яка зараз і є домінуючою. Тому нині переважають бази даних реляційного типу. Їхньою характерною рисою є той факт, що дані сприймаються користувачем як таблиці. У розпорядженні користувача є оператори для вибірки даних із таблиць, а також для вставки нових даних, оновлення та видалення наявних даних. Однією з переваг реляційної бази даних є її здатність підтримувати зв'язки між елементами даних, позбавляючи програміста необхідності займатися цією рутинною і дуже трудомісткою роботою. У ті часи, коли технології реляційних баз даних ще не набули широкого поширення, програмістам доводилося процедурними мовами вручну реалізовувати такі операції, які зараз називаються каскадним оновленням зовнішніх ключів або каскадним видаленням записів з підпорядкованих таблиць (файлів). Тут слово «вручну» означає, що для виконання цих операцій доводилося писати код, що складається з елементарних команд, що дозволяє дістатися до кожного запису, що оновлюється або видаляється. Той підхід до роботи з базами даних називався навігаційним програмістом вказував програмі конкретний алгоритм пошуку записів. Наведемо як приклад просту ситуацію: у базі даних, побудованої на основі файлів, зберігається інформація про студентів та їх екзаменаційні оцінки, причому, особисті дані студентів зберігаються в одному файлі, назвемо його умовно «Студенти», а екзаменаційні оцінки в іншому файлі, який умовно назвемо "Оцінки". Якщо потрібно видалити інформацію про конкретного студента та його екзаменаційних 9

11 оцінках, то доведеться не тільки виконати операцію видалення конкретного запису з файлу «Студенти», але додатково організувати цикл для пошуку та видалення тих записів з файлу «Оцінки», у яких ключове поле має те ж значення, що і поле у ​​видаленому записі з файлу "Студенти". Працюючи з реляційними базами даних, програміст позбавлений програмування на «атомарному» рівні, тому що сучасні мови для «спілкування» із цими базами даних є декларативними. Це означає, що для отримання результату достатньо лише вказати, що потрібно отримати, але не потрібно наказувати спосіб отримання результату, тобто як його отримати. Система баз даних це комп'ютеризована система, призначена для зберігання, переробки та видачі інформації на запит користувачів. Така система включає програмне і апаратне забезпечення, самі дані, а також користувачів. Сучасні системибаз даних є, як правило, розрахованими на багато користувачів. У таких системах одночасного доступу до бази даних можуть отримати відразу кілька користувачів. Основним програмним забезпеченням є система управління базами даних. Англійською вона називається database management system (DBMS). Крім СУБД у систему баз даних можуть входити утиліти, кошти на розробки додатків (програм), засоби проектування бази даних, генератори звітів та інших. Користувачі систем з базами даних поділяються на низку категорій. Перша категорія – це прикладні програмісти. Друга категорія – це кінцеві користувачі, заради яких і виконується вся робота. Вони можуть отримати доступ до бази даних, використовуючи прикладні програми або універсальні програми, що входять до програмного забезпечення самої СУБД. У більшості СУБД є так званий процесор мови запитів, який дозволяє користувачеві вводити команди мови високого рівня(наприклад, мови SQL). Третя категорія користувачів – це адміністратори бази даних. До їхніх обов'язків входять: створення бази даних, вибір оптимальних режимів доступу до неї, розмежування повноважень різних користувачів на доступ до тієї чи іншої інформації в базі даних, виконання резервного копіюваннябази даних і т. д. Систему баз даних можна поділити на два основні компоненти: сервер та набір клієнтів (або зовнішніх інтерфейсів). Сервер і є СУБД. Клієнтами є різні програми, написані прикладними програмістами, або вбудовані програми, що поставляються разом із СУБД. Один сервер може обслуговувати багато клієнтів. Сучасні СУБД включають словник даних. Це частина бази даних, яка описує самі дані, що зберігаються у ній. Словник даних допомагає СУБД виконувати свої функції. 1.2 Основні поняття реляційної моделі У кожній технологічній сфері є термінологія. Існують базові терміни, на яких ґрунтуються всі подальші міркування. Такі терміни при- 10

12 є і у сфері баз даних. Нині ми коротко про них поговоримо. У період, що передує народженню реляційної теорії, бази даних зазвичай розглядалися як набір файлів, які з записів, а записи, своєю чергою, підрозділялися деякі поля. Поле було елементарною одиницею даних. У реляційних базах даних користувач сприймає дані як таблиць. Тому терміну "файл" відповідає термін "таблиця", замість терміну "запис" використовується термін "рядок", а замість терміну "поле" термін "стовпець" (або "колонка"). Таким чином, таблиці складаються з рядків і стовпців, на перетині яких повинні бути «атомарні» значення, які не можна розбити на дрібніші елементи без втрати змісту. У формальної теорії реляційних баз даних ці таблиці називають відносинами (relations), тому і бази даних називаються реляційними. Ставлення це математичний термін. При визначенні властивостей таких відносин використовується теорія множини. У термінах цієї теорії рядки таблиці називатимуться кортежами (tuples), а колонки атрибутами. Ставлення має заголовок, що складається з атрибутів, і тіло, що складається з кортежів. Кількість атрибутів називається ступенем відношення, а кількість кортежів – кардинальним числом. Крім теорії множин, однією з основ реляційної теорії є такий розділ математичної логіки, як обчислення предикатів. Таким чином, у теорії та практиці баз даних існує три групи термінів. Іноді терміни з різних груп використовують як синонімів, наприклад, запис і рядок. Як ми вже сказали вище, у реляційних базах даних користувач сприймає дані у вигляді таблиць. Розглянемо просту систему, у якій лише дві таблиці. Перша «Студенти»: залікової книжки П. І. О. Серія документа Номер документа Іванов Іван Петрович Клімов Андрій Іванович Новіков Микола Юрійович І друга «Успішність»: Залікова книжка Предмет Навчальний рік Семестр Фізика 2016/ Математика 2016/ Фізика 2 / Працюючи з базами даних часто доводиться слідувати різним обмеженням, які можуть бути зумовлені специфікою конкретної предметної області. Спрощуючи реальну ситуацію, приймемо такі обмеження: 11

13 номер залікової книжки складається з п'яти цифр і не може бути негативним (у різних вузах використовуються різні схеми присвоювання номерів заліковим книжкам, ці схеми можуть бути набагато складнішими за прийняту нами і можуть враховувати, наприклад, рік вступу студента до вузу); серія документа, що посвідчує особу, є чотиризначним числом, а номер документа, що посвідчує особу, шестизначним числом; номер семестру може приймати лише два значення 1 (осінній семестр) та 2 (весняний семестр); оцінка може приймати лише три значення 3 (задовільно), 4 (добре) та 5 (відмінно): інші оцінки виставляти до залікових книжок не прийнято. Для ідентифікації рядків у таблицях і зв'язку таблиць між собою використовуються звані ключі. Потенційний ключ – це комбінація атрибутів таблиці, що дозволяє унікальним чином ідентифікувати рядки в ній. Ключ може складатися і лише з одного атрибуту таблиці. Наприклад, у таблиці "Студенти" таким ідентифікатором може бути атрибут "Номер залікової книжки". Як потенційний ключ даної таблиці можуть також служити два її атрибути, взяті разом: «Серія документа, що засвідчує особу» та «Номер документа, що засвідчує особу». Жоден з них окремо не може використовуватися як унікальний ідентифікатор. У такому разі ключ буде складним. При цьому важливим є те, що потенційний ключ повинен бути не надлишковим, тобто ніяка підмножина атрибутів, що входять до нього, не повинна мати властивість унікальності. Потенційний ключ, що включає два згадані атрибути, є не надлишковим. Ключі потрібні для адресації лише на рівні рядків (записів). За наявності в таблиці більше одного потенційного ключа один з них вибирається як так званий первинний ключ, а інші будуть альтернативними ключами. Розглянемо таблиці «Студенти» та «Успішність». Припустимо, що у таблиці «Студенти» немає рядка з номером залікової книжки 55900, тоді включати рядок із таким номером залікової книжки до таблиці «Успішність» немає сенсу. Таким чином, значення стовпця «Номер залікової книжки» у таблиці «Успішність» мають бути узгоджені зі значеннями такого ж стовпця у таблиці «Студенти». Атрибут "Номер залікової книжки" у таблиці "Успішність" є прикладом того, що називається зовнішнім ключем. Таблиця, що містить зовнішній ключ, називається таблицею, що посилається (referencing table). Таблиця, що містить відповідний потенційний ключ, називається посилальною (цільовою) таблицею (referenced table). У таких випадках говорять, що зовнішній ключ посилається на потенційний ключ у таблиці посилання. Зовнішній ключ може бути складеним, тобто може містити більше одного атрибуту. Зовнішній ключ повинен бути унікальним. Проблема забезпечення того, щоб база даних не містила неправильних значень зовнішніх ключів, відома як проблема цілісності посилання. Обмеження, згідно з яким значення зовнішніх ключів повинні відповідати значенням потенційних ключів, називається обмеженням цілісності посилань (посилальним обмеженням). Забезпеченням виконання обмежень цілісності посилається СУБД, а від розробника потрібно лише вказати атрибути, що служать як зовнішні 12

14 ключів. При проектуванні баз даних часто передбачається, що при видаленні рядка з таблиці посилань відповідні рядки з таблиці, що посилається, повинні бути також видалені, а при зміні значення стовпця, на який посилається зовнішній ключ, повинні бути змінені значення зовнішнього ключа в таблиці, що посилається. Цей підхід називається каскадним видаленням (оновленням). Іноді застосовуються інші підходи. Наприклад, замість видалення рядків з таблиці, що посилається, в цих рядках просто замінюють значення атрибутів, що входять у зовнішній ключ, так званими NULL-значеннями. Це спеціальні значення, що означають "ніщо" або відсутність значення, вони не збігаються зі значенням "нуль" або "порожній рядок". Значення NULL застосовується в базах даних і як значення за замовчуванням, коли користувач не ввів жодного конкретного значення. Первинні ключі не можуть містити значення NULL. Транзакція одна з найважливіших понятьтеорії баз даних Вона означає набір операцій над базою даних, що розглядаються як єдина і неподільна одиниця роботи, що виконується повністю або не виконується зовсім, якщо стався якийсь збій у процесі транзакції. Таким чином, транзакції є засобом забезпечення узгодженості даних. У нашій базі даних транзакцією можуть бути, наприклад, дві операції: видалення рядка з таблиці «Студенти» та видалення зв'язаних за зовнішнім ключем рядків із таблиці «Успішність». 1.3 Що таке мова SQL Мова SQL це непроцедурна мова, яка є стандартним засобом роботи з даними у всіх реляційних СУБД. Оператори (команди), написані цією мовою, лише вказують СУБД, який результат має бути отриманий, але не описують процедуру отримання цього результату. СУБД сама визначає спосіб виконання команди користувача. У мові SQL зазвичай виділяються група операторів визначення даних (Data Definition Language DDL), група операторів маніпулювання даними (Data Manipulation Language DML) і група операторів, управляючих привілеями доступу до об'єктів бази даних (Data Control Language DCL). До операторів мови визначення даних (DDL) відносяться команди для створення, зміни та видалення таблиць, уявлень та інших об'єктів бази даних. Детальному розгляду цих команд присвячено розділи 5 і 8. До операторів мови маніпулювання даними (DML) відносяться команди для вибірки рядків з таблиць, вставки рядків у таблиці, оновлення та видалення рядків. Ці команди докладно розглядаються в розділах 6 і 7. Оператори DCL у посібнику не розглядаються, тому що PostgreSQL дозволяє на початковому етапі вивчення мови SQL обійтися без їх використання. 13

15 1.4 Опис предметної області та навчальної бази даних Щоб показати всі основні можливості мови SQL, нам потрібна база даних. Ця база даних не повинна бути надто складною, щоб її вивчення не потребувало надто багато часу. Але, разом з тим, вона повинна бути досить різноманітною, щоб запити до неї виглядали правдоподібними, майже такими ж, як і в реальній роботі. Як предметну область виберемо пасажирські авіаперевезення. Її оригінальний опис та опис бази даних «Авіаперевезення» можна знайти за адресами та ru/docs/postgrespro/current/demodb-bookings.html. Сподіваємося, що ця галузь знайома багатьом читачам нашого навчального посібника. Звичайно, у навчальних цілях реальна ситуація навмисно спрощена, але всі важливі речі збережені. Отже, якась російська авіакомпанія виконує пасажирські авіаперевезення. Вона має свій парк літаків різних моделей. Кожна модель літака має певний код, який надає Міжнародна асоціація авіаперевізників (IATA). При цьому вважатимемо, що літаки однієї моделі мають однакові компонування салонів, тобто порядок розміщення крісел та нумерацію місць у салонах бізнес-класу та економічного класу. Наприклад, якщо це модель Sukhoi SuperJet-100, місце 2A відноситься до бізнес-класу, а місце 20D до економічного класу. Бізнес-клас та економічний клас це різновиди так званого класу обслуговування. Наша авіакомпанія виконує польоти між аеропортами Росії. Кожному аеропорту надано унікальний трилітерний код, при цьому використовуються тільки великі літери латинського алфавіту. Ці коди надає не сама авіакомпанія, а спеціальні організації, які керують пасажирськими авіаперевезеннями. Найчастіше назва аеропорту не збігається з назвою міста, якому цей аеропорт належить. Наприклад, у місті Новосибірську аеропорт називається Толмачево, у місті Єкатеринбурзі Кольцово, а у Санкт-Петербурзі Пулково. До того ж, деякі міста мають більше одного аеропорту. Відразу як приклад згадується Москва з її аеропортами Домодєдово, Шереметьєво та Внуково. Додамо ще одну важливу деталь: кожен аеропорт характеризується географічними координатами довготою та широтою, а також часовим поясом. Формуються маршрути перельотів між містами. Звичайно, кожен такий маршрут вимагає вказівки не лише міста, а й аеропорту, оскільки, як ми вже сказали, у місті може бути й більше одного аеропорту. Як спрощення реальності ми вирішимо, що маршрути не матимуть проміжних посадок, тобто у них буде лише аеропорт відправлення та аеропорт призначення. Кожен маршрут має шестизначний номер, що включає цифри та літери латинського алфавіту. За підсумками переліку маршрутів формується розклад польотів (чи рейсів). У розкладі вказується плановий час відправлення та плановий час прибуття та тип літака, що виконує цей рейс. При фактичному виконанні рейсу виникає необхідність обліку додаткових відомостей, а саме: фактичного часу відправлення та фактичного часу прибуття, а також статусу рейсу. Статус рейсу може набувати кількох значень: 14

16 Scheduled (за місяць відкривається можливість бронювання); On Time (за добу відкривається реєстрація); Delayed (рейс затримано); Departed (вилетів); Arrived (прибув); Cancelled (скасований). Тепер звернемося до пасажирів. Політ починається з бронювання авіаквитка. В даний час загальноприйнятою практикою є оформлення електронних квитків. Кожен такий квиток має унікальний номер, Що складається з 13 цифр. В рамках однієї процедури бронювання може бути оформлено декілька квитків, але кожна така процедура має унікальний шестизначний номер (шифр) бронювання, що складається з великих літер латинського алфавіту та цифр. Крім того, для кожної процедури бронювання записується дата бронювання та розраховується загальна вартість оформлених квитків. У кожен квиток, крім його тринадцятизначного номера, записується ідентифікатор пасажира, а також його ім'я та прізвище (латинська транскрипція) та контактні дані. Як ідентифікатор пасажира використовується номер документа, що засвідчує особу. Звичайно, пасажир може змінити свій документ, а іноді навіть прізвище та ім'я, за час, що минув між бронюванням квитків у різні дні, тому неможливо напевно сказати, що якісь конкретні квитки були оформлені на одного й того ж пасажира. Кожен електронний квиток може бути вписано більше одного перельоту. Фахівці називають ці записи про перельоти сегментами. Як приклад наявності кількох сегментів можна навести такий: Красноярськ Москва, Москва Анапа, Анапа Москва, Москва Красноярськ. При цьому можна в рамках одного бронювання оформити кілька квитків на різних пасажирів. Для кожного перельоту вказується номер рейсу, аеропорти відправлення та призначення, час вильоту та час прибуття, а також вартість перельоту. Крім того, вказується і так званий клас обслуговування: економічний, бізнес та ін. Коли пасажир прибуває до аеропорту відправлення та проходить реєстрацію квитка, оформляється так званий талон. Цей талон пов'язаний з авіаквитком: у талоні вказується такий самий номер, який має електронний авіаквиток даного пасажира. Крім того, в талоні вказується номер рейсу та номер місця у літаку. Вказується також і номер посадкового талону послідовний номер, який надається в процесі реєстрації квитків на цей рейс. Нагадаємо, що кожному крісло у салоні літака відповідає конкретний клас обслуговування. Дана інформаціявраховується при реєстрації квитків та оформленні посадкових талонів. Якщо, наприклад, пасажир придбав квиток з економічним класом обслуговування, то його посадковому талоні буде вказано номер місця в салоні економічного класу, але не в салоні бізнес-класу. Дотримуючись наведеного опису предметної області, можна спроектувати модельну базу даних, призначену вивчення мови SQL. Оскільки наш навчальний посібник у першу чергу призначений для вивчення мови SQL, а не для оволодіння мистецтвом проектування баз даних, ми наведемо лише діаграму 15

17 Bookings Бронювання Airports Аеропорти # book_ref * book_date * total_amount # airport_code * airport_name * city * longitude * latitude * timezone Tickets Квитки # ticket_no * book_ref * passenger_id * passenger_name tions * amount Flights Рейс # flight_id * flight_no * scheduled_departure * scheduled_arrival * departure_airport * arrival_airport * status * aircraft_code actual_departure actual_arrival Aircrafts Літаки # aircraft_code * model * range Boarding_passes Посадочні талони Seats Місця # ticket_no # f _no * fare_conditions схеми даних, де показані сутності, виділені у предметній галузі, а також їх зв'язки та атрибути. Конкретні ж типи даних, первинні і ключі, і навіть обмеження, накладені на атрибути і таблиці, ми покажемо вже у наступних розділах у процесі розгляду команд SQL, призначених для фізичного створення таблиць у базі даних. Наведену схему можна знайти на сайті Postgres Professional за адресами, вказаними на початку цього розділу. Контрольні питання та завдання 1. Які групи операторів виділяються у складі мови SQL? 2. Дайте неформальне визначення основних понять реляційної моделі даних: ставлення, кортеж, атрибут. 3. Навіщо потрібні зовнішні ключі в реляційних таблицях? 4. Що таке потенційний ключ? 5.* Запропонуйте приклад надлишкового потенційного ключа для однієї з таблиць бази даних «Авіаперевезення» та поясніть, чому він буде надлишковим. 16

18 6.* У поточній реалізації бази даних «Авіаперевезення» передбачається, що літаки однієї моделі можуть мати лише одну компонування салону. Уявимо, що керівництвом прийнято рішення про те, що потрібно враховувати можливість різних компоновок для кожної моделі. Які таблиці доведеться модифікувати у такому разі та яким чином? Чи потрібно створювати додаткові таблиці? 17

19 2 Створення робочого середовища Перш ніж розпочинати безпосереднє вивчення мови SQL, потрібно отримати доступ до сервера PostgreSQL. Це можна зробити, наприклад, у комп'ютерному класі або шляхом звернення до віддаленому серверучерез термінал. Проте можна створити робоче середовище для себе та на своєму локальному комп'ютері, встановивши повну версіюСУБД PostgreSQL, тобто сервер та клієнтські програми. У цьому випадку у вас буде набагато більше повноважень щодо налаштування та використання PostgreSQL. У заключній частині глави ми покажемо, як розгорнути навчальну базу даних «Авіаперевезення», наповнену спеціально підготовленими правдоподібними даними. 2.1 Встановлення СУБД Оскільки даний навчальний посібник призначений для вивчення мови SQL, а не основ адміністрування СУБД PostgreSQL, ми обмежимося лише короткими вказівками про те, де знайти інструкції з встановлення. Почати потрібно з вибору дистрибутива СУБД, який ви хотіли б встановити. Ви можете вибрати оригінальний варіант PostgreSQL або той, що пропонується компанією Postgres Professional. Він називається Postgres Pro і містить не тільки всі функції та модулі, що входять до складу стандартного дистрибутива, а й додаткові розробки, виконані у компанії Postgres Professional. Для вивчення основ мови SQL ці дистрибутиви підходять однаково. Проте документація російською включена лише до складу Postgres Pro. Після того, як ви визначитеся з конкретним дистрибутивом СУБД, необхідно вибрати операційну систему. PostgreSQL підтримує безліч систем, у тому числі різні версії Linux та Windows. Встановлювати рекомендується остання стабільна версія СУБД. Якщо ви вирішили скористатися оригінальним дистрибутивом PostgreSQL, то знайти інструкції щодо його встановлення в різних операційних системах можна за адресою Якщо ж ви зупинили свій вибір на дистрибутиві Postgres Pro, слід звернутися сюди: latest. Після встановлення як PostgreSQL, так і Postgres Pro середовищі Windowsдоведеться вжити додаткових заходів, щоб використання російського алфавіту в інтерактивному терміналі psql не викликало проблем. Утиліта psql розглядається у наступному розділі. У процесі встановлення буде створено обліковий запис користувача СУБД із ім'ям postgres. Для вивчення цього посібника створювати додаткові облікові записи не потрібно. 18

20 Встановивши той чи інший дистрибутив PostgreSQL, потрібно навчитися запускати сервер баз даних, тому що інакше неможливо працювати з даними. Як це зробити, докладно описано в документації розділу 18.3 «Запуск сервера баз даних». Знайти цей розділ можна за адресою current/server-start.html. Під час встановлення СУБД у середовищі Windows створюється служба (service) для автоматичного запускусервера PostgreSQL під час завантаження операційної системи. Завершивши роботу із сервером, потрібно коректно зупинити (вимкнути) його. Порядок дій у такій ситуації описаний у документації розділу 18.5 «Вимкнення сервера». Знайти цей розділ можна за адресою postgresql/9.6/server-shutdown.html. 2.2 Програма psql інтерактивний термінал PostgreSQL Для доступу до серверу баз даних у комплекті PostgreSQL входить інтерактивний термінал psql. Для його запуску потрібно ввести команду psql При запуску утиліти psql у середовищі Windows можливо некоректне відображеннялітери російського алфавіту. Для усунення цього потрібно властивості вікна, в якому виконується psql, змінити шрифт на Lucida Console і за допомогою команди chcp змінити поточну кодову сторінку на CP1251: chcp 1251 У середовищі утиліти psql можна вводити не тільки команди мови SQL, але і різні сервіси підтримувані самою утилітою. Для отримання короткої довідки з усіх сервісних команд потрібно ввести \? Багато таких команд починаються із символів \d. Наприклад, щоб переглянути список всіх таблиць і уявлень (views), створених у тій базі даних, до якої ви зараз підключені, введіть команду \dt Якщо ж вас цікавить визначення (просто кажучи, структура) якоїсь конкретної таблиці бази даних, наприклад, students, потрібно ввести команду \d students Для отримання списку всіх SQL-команд потрібно виконати команду \h Для виведення опису конкретної SQL-команди, наприклад, CREATE TABLE, потрібно зробити так: \h CREATE TABLE 19

21 Ця утиліта дозволяє скорочувати обсяг ручного введення за рахунок доповнення команди, що вводиться «силами» psql. Наприклад, при введенні команди SQL можна використовувати клавішу Tab для доповнення вводимого ключового слова команди або імені таблиці бази даних. Наприклад, при введенні команди CREATE TABLE... можна, ввівши символи cr, натиснути клавішу Tab psql доповнить це слово до create. Аналогічно можна зробити і зі словом TABLE. Для введення достатньо ввести лише літери «ta» і натиснути клавішу Tab. Якщо ви ввели надто мало букв для того, щоб утиліта psql могла однозначно ідентифікувати ключове слово, доповнення не відбудеться. Але в такому випадку ви можете натиснути клавішу Tab двічі і отримати список усіх ключових слів, що починаються з введеної вами комбінації літер. 2.3 Розгортання навчальної бази даних Завершивши встановлення сервера баз даних, ми можемо перейти безпосередньо до розгляду питання, як розгорнути у вашому кластері PostgreSQL навчальну базу даних «Авіаперевезення», підготовлену компанією Postgres Professional. На сайті компанії є розділ, присвячений цій базі даних, знайти його можна за посиланням. включає дані протягом цілого року. Всі дані були згенеровані за допомогою спеціальних алгоритмів, що забезпечують їхню «правдоподібність». Ми рекомендуємо почати з компактної версії бази даних «Авіаперевезення», а після отримання деякого досвіду написання SQL-запитів ви встановите повну версію і вже на ній зможете краще «відчути» різні тонкощі роботи з даними великих обсягів, наприклад, оціните вплив індексів на швидкість доступу до даних. Як перший крок до розгортання бази даних потрібно завантажити її заархівовану резервну копіюна посилання zip. Потім необхідно витягти файл із архіву: unzip demo_small.zip Вилучений файл називається demo_small.sql. Тепер ми створимо базу даних з ім'ям demo у вашому кластері PostgreSQL. Найкоротший варіант команди буде таким: psql -f demo_small.sql -U postgres Якщо ви хочете перенаправити виведення повідомлень, які генерує СУБД у процесі роботи, з екрана до файлів, то можна зробити так: psql -f demo_small.sql -U postgres > demo.log 2>demo.err Можна розділити стандартний пристрій виводу та стандартний пристрій виведення помилок. Звичайні повідомлення будуть перенаправлені у файл demo.log, а повідомлення про помилки у файл demo.err. Зверніть увагу, що між цифрою 2, що позначає дескриптор стандартного пристрою виведення повідомлень про помилки, і знаком «>», що позначає переадресацію виводу, не повинно бути пропуску. 20

22 Якщо вам зручніше зібрати всі повідомлення в один спільний файл, тоді потрібно зробити так: psql -f demo_small.sql -U postgres > demo.log 2>&1 Зверніть увагу, що вираз 2>&1 в кінці команди пишеться без пробілів. Воно вказує на операційну систему, що повідомлення про помилки потрібно направити туди ж, куди виводяться і звичайні повідомлення. Якби наш SQL-файл був дуже великим, тоді можна було б виконати команду фоновому режимі, поставивши наприкінці командного рядкасимвол &, а за ходом процесу в реальному часі спостерігати за допомогою команди tail. psql -f demo_small.sql -U postgres > demo.log 2>&1 & tail -f demo.log Виберіть один із запропонованих варіантів команди для розгортання бази даних і виконайте цю команду. Все готово! Можна підключатися до нової бази даних: psql -d demo -U postgres Контрольні питання та завдання 1. Виконайте процедуру встановлення СУБД PostgreSQL у середовищі вибраної операційної системи. 2. Ознайомтеся з утилітою psql за допомогою вбудованої довідки, а також за допомогою довідки, що викликається за командою psql --help 3. Крім утиліти psql існують інші універсальні програмидля роботи з сервером баз даних PostgreSQL, наприклад pgadmin. Це потужна утиліта із графічним інтерфейсом. Самостійно встановіть програму pgadmin та вивчіть основні прийоми роботи з нею. 4. Виконайте розгортання навчальної бази даних. Спробуйте підключитись до неї за допомогою утиліти psql. Для виходу з утиліти використовуйте \q. 21

23 3 Основні операції з таблицями Мова SQL дуже різноманітний, він включає у собі низку команд, які, своєю чергою, іноді мають безліч параметрів і ключових слів. Але почнемо ми з короткого оглядуОсновні можливості мови SQL. У цьому розділі ви навчитеся вводити дані в базу даних, освоїте основні способи отримання інформації з бази даних, тобто вибірки, а також дізнаєтеся, як можна внести зміни в інформацію, що зберігається в базі даних, і видалити ті дані, які більше не потрібні. У практиці вивчення іноземних мовє гарна традиція. Вже на першому занятті учень вивчає деякі базові граматичні конструкції та слова, що дозволяють йому сказати кілька найпростіших, але практично корисних фраз. Ми підемо цій традиції. У цьому розділі нашого посібника ви ознайомитеся з основними командами мови SQL, які дозволять вам виконувати базові операції. А складніші (і цікавіші) команди ви вивчите в наступних розділах. Скажімо, два слова про наш підхід до роботи. У принципі можливі два способи організації роботи студента. Перший спосіб такий: студент використовує базу даних, де вже містяться всі необхідні таблиці та інші об'єкти бази даних, підготовлені заздалегідь автором навчального посібника або іншим кваліфікованим фахівцем. При цьому деякий набір необхідних даних також уже введений у таблиці, тому можна відразу ж переходити до виконання запитів до цих таблиць. Описаний спосіб здається дуже привабливим, оскільки він вимагає менше зусиль на початковому етапі освоєння SQL. Однак, на наш погляд, правильнішим є інший спосіб. Напевно, він трудомісткіший, але при його використанні ви краще, як кажуть, відчуєте процес створення таблиць і введення записів у ці таблиці. А виконуючи різні запити до бази даних, вам буде легше оцінити правильність отриманого результату виконання запиту, оскільки ви запровадили всі дані самостійно і тому зможете обґрунтовано припустити, які результати очікуєте побачити на екрані. Звичайно, перший спосіб може бути дуже корисним при вивченні більш складних, просунутих можливостей мови SQL, які важко зрозуміти без використання великих масивів даних, а великі масиви даних вводити в базу даних вручну нераціонально. Набагато раціональнішим буде їхнє автоматичне формування програмним шляхом. У розділі 1 ми описали предметну область, тому тепер можемо розпочати безпосереднього створення таблиць у базі даних. Для виконання всіх наступних команд та операцій ми будемо використовувати утиліту psql, що входить до стандартного постачання СУБД PostgreSQL. На вашому комп'ютері вже має бути розгорнута база даних demo. Процес її створення описаний у розділі 2. Тепер запустіть утиліту psql і підключіться до цієї бази даних обліковим записомкористувача postgres: psql -d demo --U postgres Для створення таблиць у мові SQL служить команда CREATE TABLE. Її повний синтаксис представлений у документації на PostgreSQL, а спрощений синтаксис такий: 22

Компанія Postgres Professional Е. П. Моргунов PostgreSQL. Основи мови SQL Навчальний посібник Санкт-Петербург «БХВ-Петербург» 2018 УДК 004.655 ББК 32.973.26-018.2 М79 Моргунов, Є. П. М79 PostgreSQL. Основи

Мова SQL Лекція 6 Індекси Є. П. Моргунов Сибірський державний університет науки і технологій імені академіка М. Ф. Решетньова м. Красноярськ Інститут інформатики та телекомунікацій [email protected]

Братчиков І.Л. 41. Основи проектування баз даних. Методи побудови СУБД. SQL, приклади реалізації. Основи сучасних баз даних. 1. Основні визначення. Термінологія. У літературі можна зустріти

Тема 10. Основи технології баз даних Технологія баз даних є одним із ключових досягнень інформатики, що використовується для побудови комп'ютерних інформаційних систем та додатків, що реалізують інформаційні

ОСНОВНІ ПОНЯТТЯ БАЗ ДАНИХ 1. Виберіть правильний порядок дій під час проектування БД а) Вирішення проблеми передачі б) Аналіз предметної області, з урахуванням вимоги кінцевих користувачів в)

Ця книга призначена для розробників, які в якості системи управління базами даних (СУБД) використовують Oracle. Тому в цій книзі не відводиться багато місця для розгляду питань,

Варіант 1 Виберіть правильний варіант відповіді. Можливий лише один варіант правильної відповіді. 1. Інформаційна система-це а) Будь-яка система обробки інформації; б) Система обробки текстової інформації

Мова SQL Лекція 3 Основи мови визначення даних Є. П. Моргунов Сибірський державний університет науки та технологій імені академіка М. Ф. Решетньова м. Красноярськ Інститут інформатики та телекомунікацій

Мова SQL Лекція 5 Зміна даних Є. П. Моргунов Сибірський державний університет науки та технологій імені академіка М. Ф. Решетнева м. Красноярськ Інститут інформатики та телекомунікацій [email protected]

➀ Інформаційні системи та банки даних. Найважливішою умовою забезпечення ефективного функціонування будь-якої організації є розвинену інформаційну систему. Інформаційна система це система,

Глава 1. Початок роботи Як влаштовано базу даних Microsoft AccessПроектування структури даних Етапи роботи з базою даних Приступимо до роботи Інтерфейс користувача Access 2007 Налаштування елементів

Глава 3. Встановлення та запуск версії багато користувачів Вимоги та умови...1 Порядок встановлення СБіС++ в мережі...2 Встановлення та налаштування сервера та клієнта Pervasive.SQL...3 Встановлення програми...4 Підготовка

БАНК ТЕСТОВИХ ЗАВДАНЬ Навчальна дисципліна Напрямок Рівень підготовки Кафедра Б1.Б.12 Управління даними 09.03.02 Інформаційні системи та технології бакалаврат Методів та засобів вимірювання та автоматизації

Бази даних Розвитку обчислювальної технікиздійснювалося за двома основними напрямами: застосування обчислювальної техніки до виконання чисельних розрахунків; використання засобів обчислювальної техніки

Московський державний технічний університет імені Н. Е. Баумана Калузька філія Ю. Є. Гагарін, С. В. Пономарьов У MS ACCESS Навчально-методичний посібник

1. Інформація та дані 2. Основні поняття систем з базами даних Інформаційні комп'ютерні системи з базами даних це системи інформаційних, математичних, програмних, мовних, організаційних

PDM STEP SUITE версія 5.0 Інструкція зі встановлення PDM STEP Suite Інструкція для системного програміста. Частина 1 АТ НДЦ "Прикладна Логістика" 2019 PDM STEP Suite v.5.0. Інструкція для системного програміста

1 із 6 Лекція 1. Архітектура системи баз даних. Функції адміністратора системи баз даних у корпоративній інформаційній системі. Об'єкти бази даних 1.1. Архітектура системи баз даних ANSI/SPARC...1

ЗМІСТ ВСТУП... 3 1 ДОСЛІДЖЕННЯ ПРЕДМЕТНОЇ ОБЛАСТІ ТА ПОСТАНОВКА ЗАВДАННЯ... 4 2 ПОБУДУВАННЯ ІНФОЛОГІЧНОЇ МОДЕЛІ... 5 3 ДАТАЛОГІЧНЕ ПРОЕКТУВАННЯ3 БАЗЕКТИВАННЯ3.

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

Навчальна дисципліна «Бази даних та управління ними» для студентів спеціальності Прикладна інформатика (бакалавріат) 080800.62 Лекція 15 МОДЕЛІ ОРГАНІЗАЦІЇ ДОСТУПУ ДО БАЗІВ ДАНИХ Навчальні питання: 1. Архітектура

Мова SQL Лекція 7 Транзакції Є. П. Моргунов Сибірський державний університет науки і технологій імені академіка М. Ф. Решетньова м. Красноярськ Інститут інформатики та телекомунікацій [email protected]

Комп'ютерні інформаційні технології Модуль 13. Загальна характеристика СУБД Access 1 ЗАВДАННЯ МОДУЛЯ Після вивчення модуля ви зможете: розповісти про функціональні можливості СУБД Access; знати особливості

Система електронного документообігу А р х і в а р і у с Посібник зі встановлення Москва, 2008 2 АННОТАЦІЯ У цьому документі наводиться опис комплекту поставки програмного продукту «ЄВФРАТ-Документообіг

Бази даних (БД). СИСТЕМИ УПРАВЛІННЯ БД загальні положенняМета будь-якої інформаційної системи – обробка даних про об'єкти реального світу. У широкому значенні слова база даних - це сукупність відомостей про

Введення до баз даних. Введення у SQL. Sumy Educational Center Software Quality Assurance (QA) Netcracker 2016 1 Ви дізнаєтеся про наступне Що таке база даних Класифікація баз даних

1.1. Основи роботи з базами даних 1.1.1. Бази даних та системи управління базами даних. Моделі даних База даних (БД) це іменована сукупність структурованих даних, що належать до певної

Здається, це було зовсім недавно, але які відтоді відбулися суттєві зміни! У той час, коли автор займався підготовкою до друку книги Professional SQL Server 7.0 Programming на початку 1999 року

Введення в теорію баз даних Лекція 1 План лекції Основні поняття Організація даних у БД Види моделей даних Архітектура БД Класифікація БД Server 2008. Лекція 1 2 Основні поняття Server 2008. Лекція

Додаток 4 Підсистема управління процесами та обчисленнями ЗМІСТ 1. Загальні відомості... 2 2. Моделювання станів та робочих процесів... 2 2.1. Управління списком груп станів... 2 2.2. створення

Softacom CRM Посібник зі встановлення Версія 2.1. 1 «Посібник зі встановлення Softacom CRM». Містить опис процесу встановлення системи. Зміст 1 ВСТУП... 3 1.1 Загальне про систему... 3 1.2 Особливості

Лабораторна робота 4 «Створення зв'язків між таблицями» Мета роботи: вивчення технології створення структури реляційної бази даних Microsoft Office Access 2007 1. Короткі теоретичні відомості Слово

Логічне резервування Теми Логічне та фізичне резервування Копіювання окремих таблиць Резервування та відновлення баз даних та кластера 2 Види резервування Логічне резервування

MICROSOFT ACCESS: ЗВ'ЯЗКИ, ВИЧИСЛЮВАНІ ПОЛЯ, СТВОРЕННЯ КНОПОЧНОЇ ФОРМИ БД I. ЗВ'ЯЗКИ ТАБЛИЦЬ Сучасні бази даних зазвичай складаються з багатьох таблиць, пов'язаних між собою. Однією з цілей створення гарної структури

НАЦІОНАЛЬНИЙ АВІАЦІЙНИЙ УНІВЕРСИТЕТ Кафедра прикладної інформатики Курсова робота Тема: СУБД Microsoft Access Створення та обробка БД Мета роботи: теоретично та практично освоїти основні прийоми роботи

Транзакції та одночасний доступ: порівняння реалізацій у PostgreSQL та Oracle Єгор Рогов, Postgres Professional Навіщо все це? запит результат Навіщо все це? запит результат Транзакції Атомарність

@БД, CУБД 1. Структура даних, котрій характерна підпорядкованість об'єктів нижнього рівня об'єктам верхнього рівня, називається A. табличною B. реляційної *C. ієрархічної D. мережевий 2. Відмінна

VOGBIT Посібник зі встановлення 2010 Зміст Вступ... 4 Вимоги до програмного забезпечення апаратного забезпечення... 5 Послідовність встановлення... 6 Встановлення програми... 7 Запит та отримання ліцензії...

Етапи розробки бази даних З базами даних, зазвичай, працюють не професіонали, тому можна сформулювати такі вимоги до БД. Розробники, під час створення БД, повинні орієнтуватися на ці

Система управління базами даних Access Виконала Чибінова Назлигуль Ніязівна, студентка факультету іноземних мов Єлабузького Інституту Казанського (Привозького) федерального університету. Науковий

Інформатика Лекція 5 MS Access Інформаційні системи Програми для зберігання, поповнення, редагування та використання великих обсягів даних Під використанням мається на увазі в найпростішому випадку доступ

Зміст Передмова...3 Частина I. Бази даних, СУБД та моделі даних Глава 1. Призначення технології баз даних. Функції та основні компоненти систем управління базами даних............................................

Лабораторія моніторингу та автоматизації освітнього процесу. Посібник адміністратора сайту підтримки освітнього процесу Редакція 3 від 18.12.15. Коротков Д.С., Жучок І.О. Увімкнення/вимкнення

Розділ 6 Теорія баз даних 6.1. Загальні поняття 6.2. Моделі даних 6.3. Реляційні бази даних 6.4. Постріляційні моделі та бази даних 6.5. Проектування баз даних Сучасні інформаційні системи,

Лабораторна робота 9 Дослідження можливостей Microsoft Access 2007 щодо формування запитів із використанням SQL. Мета: 1. Вивчити порядок формування запитів із використанням SQL у Microsoft Access

Комплекс підготовки документів аеронавігаційної інформації Створення бази аеронавігаційних даних Загальні положення Основним джерелом аеронавігаційної інформації комплексу є реляційна базааеронавігаційних

1. Оціночні засоби поточного контролю. Питання, що виносяться на опитування (для дискусії) розділам навчальної дисципліни: Розділ 1. Введення до банків даних 1. Поняття банку даних (БнД). Компоненти БНД. 1.1. Інформаційний

Лекція 2 1 За словником: Архітектури інформаційних систем Інформаційна система організаційно впорядкована сукупність документів (масивів документів) та інформаційних технологій, у тому числі з використанням

Дж.Боуман, С.Емерсон, М.Дарновскі ПРАКТИЧНЕ КЕРІВНИЦТВО З SQL Ця книга допоможе навіть у тих випадках, коли марно найкраще керівництво користувача. Тут розкриваються теми, що часто пропускаються

Система електронного документообігу А р х і в а р і у с Посібник зі встановлення Москва, 2009 2 АННОТАЦІЯ У цьому документі наводиться опис комплекту постачання програмного продукту «ЄВФРАТ-Документообіг.

Сергій Воробйов
Провідний інженертестувальник
SQL. Базовий курс
www.aplana.ru

Зміст
● Частина 1. Введення в SQL
● Частина 2. Data Defenition Language
● Частина 3. Data Manipulation Language
● Частина 4. DRL. Прості запити.
● Частина 5. Вибір даних з кількох таблиць.
● Частина 6. Агрегатні функції. Групування даних.
● Частина 7. Підзапити.
● Частина 8. Функції для роботи з рядками, датами та числами.
23

SQL. Базовий курс
Частина 1. Введення в SQL
www.aplana.ru

Введення в SQL
SQL (англ. Structured Query Language – «мова структурованих
запитів») – універсальна комп'ютерна мова, що використовується для
створення, модифікації та управління даними в реляційних базах
даних.
43

Введення в SQL
База даних – список або безліч пов'язаних списків з
інформацією
Система управління базами даних (СУБД) – спеціальний
софт, який керує цими списками
53

Реляційні та нереляційні БД
Реляційна БД – база даних, що базується на реляційній моделі
даних:
Дані в основі є набір таблиць;
Дані задовольняють певним умовам цілісності;
Підтримує оператори маніпулювання таблицями
(наприклад, вибірка чи копіювання таблиці).
Нереляційні бази даних – ієрархічні, мережеві,
об'єктно-орієнтовані, NoSQL.
63

Чим БД відрізняються від електронних таблиць
1. Зберігання великої кількості рядків
В електронних таблицях кількість рядків обмежена.
У БД зберігаються мільйони рядків.
2. Одночасне обслуговування багатьох користувачів
3. Безпека.
Користувачам надаються привілеї лише на
певні таблиці та дії.
4. Реляційні характеристики.
Дані зберігаються у різних таблицях, між таблицями
існують зв'язки.
5. Обмеження, що гарантують якість даних.
73

Таблиця (table)
Рядок (row) – горизонтальний ряд осередків, відведений для кожного
об'єкт таблиці.
Запис (record) – дані у рядку.
Стовпець(column) містить інформацію одного типу.
Поле (field) - перетин стовпця та рядки.
83

Основи інтерфейсу SQL
СУБД
93
Назва
Розшифровка
InterBase/FireBird
PSQL
Procedural SQL
IBM DB2
SQL PL
SQL Procedural Language
Ms SQL Server/Sybase ASE
T-SQL
Transact-SQL
MySQL
SQL/PSM
SQL/Persistent Stored
Module
Oracle
PL/SQL
Procedural Language/SQL
(заснований мовою Ada)
PostgreSQL
PL/pgSQL
Procedural
Language/PostgreSQL
(дуже схожий на Oracle
PL/SQL)

10.

Відмінності синтаксису функцій СУБД
MSSQL
DB2
NUMERIC
NUMBER
NUMERIC
DATEADD
MONTH_ADD
(Date + 10 DAYS)
EOMONTH
LAST_DAY
LAST_DAY
DATEDIFF
MONTH_BETWEEN
TIMESTAMPDIFF
UPPER
UPPER
UCASE/UPPER
CONVERT
TO_CHAR
TO_CHAR
INITCAP
INITCAP
-
103
Oracle

11.

Синтаксис SQL
Функції та назви об'єктів нечутливі до регістру:
SELECT = sELeCt.
Однак при пошуку по текстовим полям регістр враховується
SQL не чутливий до перенесення рядків
Відсутні обов'язкові символи, що завершують рядки
Підтримуються - однорядкові коментарі та
/ * Багаторядкові * /
Кожну транзакцію прийнято завершувати крапкою з комою, але
при виконанні окремих команд їх вживання не
обов'язково
113

12.

Типи даних
CHAR(n) – рядки постійної довжини (до 256 байтів у MS SQL Server), тобто.
ввели менше даних у рядок – розмір не зміниться
VARCHAR(n) – рядки змінної довжини, тобто. вимагає пам'яті стільки,
скільки даних
INTEGER – число без десяткової точки
NUMERIC (m,n) – використовується для зберігання нуля та позитивних або
негативних чисел з фіксованою та плаваючою точкою. MТОЧНІСТЬ ( загальне числоцифр), n – МАСШТАБ (число цифр праворуч
десяткової точки). m/n –необов'язкові параметри
DATE – дата у форматі yyyy-mm-dd (ISO), dd/mm/yyyy (ANSI), dd-MON-yy.
BOOLEAN – логічний тип даних: true/false чи 1/0.
Також значення поля може бути NULL – означає відсутність значень –
порожній осередок.
123

13.

Тип DATE
За умовчанням можна подавати в базі даних дату у форматі DD-MONYYYY (наприклад, '01-FEB-1900'):
INSERT INTO table1 (id, date_work) values ​​(1, '01-FEB-1900').
Можна також використовувати ключове слово DATE. При цьому вже для
формату дати YYYY-MM-DD (наприклад, '1900-02-01'):
INSERT INTO table1 (id, date_work) values ​​(1, DATE '1900-02-01').
Також альтернативно можна використовувати тип дати + час TIMESTAMP
для завдання вже не лише дати, а й часу:
INSERT INTO table1 (id, date_work) values ​​(1, ‘01-FEB-1900-10.50.01’),
тобто.
1 лютого 1900 року 10 годин 50 хвилин і 1 секунда (формат dd-MON-yy
-hh.mm.ss.nnnnn)
133

14.

Перетворення типів даних у MSSQL
CONVERT (тип даних, рядок, стиль) - перетворення одного
формату даних в інший
У символи:
CONVERT(VARCHAR(20), GETDATE())
У дату
CONVERT(DATETIME, "14-11-2015", 105)
До числа
CONVERT(NUMERIC, ‘1234657890”)
143

15.

Перетворення типів даних на Oracle
TO_CHAR(вхідне значення, формат) – перетворення дати,
числа, часу в рядок.
Формат: 'MONTH DD', 'MONTH DD, YYYY', 'DD/MM/YYYY', 'DAY MON, YY AD'
YEAR
$9,999.00
І т.д.
TO_CHAR(SYSDATE, "MONTH DD")
TO_DATE(вхідне значення, формат) – перетворення рядка на
дату. Формат: DD-MON-YYYY
Month dd, YYYY, HH: MI p.m.
І т.д.
TO_DATE("02-JAN-2012", "DD-MON-YYYY")
TO_NUMBER(вхідне значення, формат) – перетворення рядка на число.
TO_NUMBER("123")
153

16.

Інші об'єкти бази даних
Подання (view) – це об'єкти БД, які не містять
власних таблиць, та їх вміст береться з інших таблиць
або уявлень за допомогою виконання запиту.
Схема (schema) - названа група пов'язаних об'єктів БД.
Індекс (index) – об'єкт, створюваний підвищення
продуктивності пошуку. Прихована таблиця, що містить один
або кілька важливих стовпців таблиці та покажчики на рядки
таблиці.
Обмеження (constraint) – умови, які мають задовольняти
введені користувачем записи.
163

17.

Інші об'єкти бази даних
Зберігається процедура (stored procedure) – об'єкт бази даних,
представляє собою набір SQL-інструкцій. Зберігається у БД.
Виклик процедури призводить до виконання містяться в ній
інструкції.
Функція (function) – схожа на процедуру, що зберігається, але повертає
значення, яке може бути використане у більшому
оператора.
Тригер (trigger) – процедура, яка виконується автоматично,
коли відбувається деяке задане подія.
Курсор (cursor) – посилання контекстну область пам'яті. Використовуючи
курсор, можна окремо обробляти кожен рядок пов'язаного з
ним SQL-оператора.
173

18.

Розділи мови SQL
1. DDL - Data Defenition Language (мова визначення об'єктів БД).
CREATE, ALTER, DROP тощо
2. DCL - Data Control Language (мова управління даними).
GRANT, REVOKE
3. DML - Data Manipulation Language (мова маніпулювання)
даними). INSERT, UPDATE, DELETE
4. Data Retrieval – вибірка даних SELECT
5. Transaction Control (мова підтримки процесу транзакцій).
COMMIT, ROLLBACK, SAVEPOINT.
183

19.

SQL. Базовий курс
Частина 2. Data Defenition Language
www.aplana.ru

20.

Data Defenition Language
1.
CREATE TABLE (створення таблиць)
Загальний синтаксис:
CREATE TABLE имя_таблицы (
поле1 Тип поля1,
поле2 Тип поля2,
…, полеN Тип поляN);
CREATE TABLE person_info (
person_id INTEGER NOT NULL,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(20) NOT NULL,
gender CHAR(1),
birthday DATE,
salary NUMERIC(7,2));
203

21.

Data Defenition Language
2. ALTER TABLE (зміна таблиць)
ALTER TABLE имя_таблицы (ADD<имя столбца> <определение столбца>}|
(MODIFY<имя столбца> <Определение столбца>)| (DROP COLUMN<имя
стовпця>)
3. DROP TABLE (видалення таблиць)
DROP TABLE имя_таблицы (CASCADE CONSTRAINTS);
4. TRUNCATE TABLE (очищення таблиць)
TRUNCATE TABLE имя_таблицы
213

22.

SQL. Базовий курс
Частина 3. Data Manipulation Language
www.aplana.ru

23.

Data Manipulation Language
1. INSERT – Вставка окремого запису.
INSERT INTO имя_таблицы VALUES (значення поля1, значення поля2,..,
значення поля N);
INSERT INTO имя_таблицы (поле1, поле3,…) VALUES (значення поля1, значення поля2,...,
значення поля N);
INSERT INTO person_info VALUES (1, "John", "Smith", "M", "15-OCT-1973",
45568.56);
INSERT INTO person_info (person_id, first_name, last_name) VALUES (5, Sarah", 'Connor");
Успішно.
INSERT INTO person_info VALUES (NULL, "Jane", "Smith", "F", "8-AUG-1987",
NULL);
Помилка, тому що person_id не може бути NULL.
233

24.

Data Manipulation Language
Вставка групи записів
INSERT INTO имя_таблицы
SELECT ...;
CREATE TABLE t2 (
first_1 VARCHAR(15),
last_1 VARCHAR(20),
birthday_1 DATE);
INSERT INTO t2
SELECT first_name, last_name, birthday
FROM person_info;
243

25.

Data Manipulation Language
INSERT INTO person_info VALUES (2, "Sara", "Doe", "F", "9-OCT-1986",
29789.56);

29789.56);
Успішно.
INSERT INTO person_info VALUES (3, "Sara", "Doe", "F", "9-OCT-1986",
29789.56);
Тепер видалимо другий запис з person_id=2
DELETE FROM person_info WHERE person_id = 2
253

26.

Data Manipulation Language
Цілісність даних
Цілісність сутностей - визначає рядок таблиці як унікальний екземпляр
деякої сутності.
Первинний ключ (primary key) – стовпець або група стовпців унікально
що ідентифікує кожен запис.
Зовнішній ключ (foreign key) – відображення зв'язків між таблицями. Підлегла
таблиця повинна мати ідентичний стовпець (або групу стовпців) для зберігання
значень, що унікально ідентифікують головні записи.
Посилальна цілісність – у підпорядкованих таблицях повинно бути записів,
посилаються на неіснуючі записи основних таблиць.
263

27.

Data Manipulation Language
273

28.

Data Manipulation Language
Первинний ключ
ALTER TABLE имя_таблицы
ADD PRIMARY KEY (ім'я_стовпця);
ALTER TABLE person_info
ADD PRIMARY KEY(person_id);
Значення первинного ключа мають на увазі унікальну ідентифікацію
записи, відповідно, значення що неспроможні повторюватися.
І знову спробуємо додати запис із person_id=2:
INSERT INTO person_info VALUES (2, "Rita", "Blow", "F", "9-OCT-1975",
29789.56);
283

29.

Зовнішній ключ
ALTER TABLE ім'я_підпорядкованої_таблиці
ADD CONSTRAINT ім'я_обмеження FOREIGN KEY (ім'я_стовпця
підлеглої
таблиці) REFERENCES ім'я_головної_таблиці;
CREATE TABLE person_address (
person_id INTEGER,
address VARCHAR(200));
ALTER TABLE person_address
ADD CONSTRAINT person_fk_address
FOREIGN KEY (person_id)
REFERENCES person_info;
293

30.

Data Manipulation Language
INSERT INTO person_address VALUES (1, "Moscow, Arbat street, 67-14");
INSERT INTO person_address VALUES (2, "Moscow, Arbat street, 67-14");
Успішно.
INSERT INTO person_address VALUES (4, "Zelenograd, Green street, 23");
Помилка. Спроба вставити підпорядкований запис за відсутності
відповідного головного запису.
INSERT INTO person_address VALUES (3, "Zelenograd, Green street, 23");
303

31.

Зв'язування таблиць під час створення
Як ми вже розглянули раніше, широко використовується створення
первинного (PRIMARY KEY) та зовнішнього (FOREIGN KEY) ключів
через команди зміни структури існуючих таблиць. Також
можна додавати ці конструкції і під час створення таблиці:
CREATE TABLE tab1(
id integer PRIMARY KEY,
…..
313

32.

Data Manipulation Language
2. UPDATE – Зміна значень стовпців таблиці
A) Зміна всіх значень стовпця таблиці
UPDATE
SET =
UPDATE person_address
SET address = "Volgograd, First street, 15-20"
Б) Зміна конкретних значень таблиці
UPDATE
SET =
WHERE =
UPDATE person_address SET address = "Volgograd, First street, 15-20"
WHERE person_id = 3;
UPDATE
SET =
WHERE = [оператор]
UPDATE person_info SET salary = salary * 2
WHERE person_id = 3;
323

33.

Data Manipulation Language
3. DELETE - Видалення рядків із таблиці
А) Видалення всіх значень стовпця таблиці
DELETE FROM
Б) Видалення конкретних значень таблиці
DELETE FROM
WHERE =
333

34.

Практичне завдання №1
1. Створити БД, зображену на рис.1 (створити таблиці та зовнішній ключ)
2. Внести до таблиць такі дані.
Dept: (1, "Marketing"), (2, "RD")
Emp: (1, 1, "James", 1000), (2, 2, "Smith", 2000)
3. Створити таблицю dept_arch з такою самою структурою, як і таблиці dept.
4. Вставити в таблицю dept_arch усі дані з таблиці dept.
343

35.

Практичне завдання №1 (продовження)
5. Збільште на 15% зарплату співробітнику Smith.
6. Переконайтеся, що в таблицю dept не можна вставити такий запис: (2, Sales). Чому?
7. Переконайтеся, що таблицю emp не можна вставити такий запис: (3, 4, " Black " , 3000,
"Active"). Чому?
8. Змініть назву відділу RD на RandD (таблиця dept).
9. Видаліть із таблиці emp запис із emp_id = 1.
12. Видаліть із таблиці emp всі записи.
13. Видаліть таблицю emp.
353

36.

SQL. Базовий курс
Частина 4. DRL. Прості запити
www.aplana.ru

37.

Наша навчальна БД
373

38.

Data Retrieval Language
SELECT – вибірка даних. Цей розділ є обов'язковим у запиті та
дозволяє:
SELECT поле1,…полеN FROM таблиця1, .., таблицяN WHERE умова
383
Визначити список вихідних стовпців
Включити обчислювані стовпці
Включити константи
Перейменувати вихідні стовпці
Вказати принцип обробки дублів рядків
Увімкнути агрегатні функції

39.

Data Retrieval Language
1.
Визначення списку вихідних стовпців
Список вихідних стовпців може бути вказаний декількома способами:
. Вказати символ *, що означає включення до результатів запиту всіх колонок
запиту у природній послідовності.
. Перелічити в бажаному порядку лише потрібні<имена столбцов>.
SELECT person_code, first_name, last_name FROM person;
--Можемо міняти порядок стовпців
SELECT first_name, last_name, person_code FROM person;
393

40.

Конкатенація
З'єднання двох та більше частин тексту.
SELECT product_name + "was sold by" + salesperson FROM purchase;
403

41.

Data Retrieval Language
2. Включення обчислюваних стовпців
Як обчислювані стовпці запиту можуть виступати:
. Результати найпростіших арифметичних виразів (+, -, /, *_ або
конкатенації рядків (+).
. Результати функцій агрегування (AVG | SUM | MAX | MIN |
COUNT)
413

42.

Data Retrieval Language
3. Вмикання констант
Як стовпці можуть виступати константи числового і
символьних типів.
SELECT "Є такий код",person_code, "для", first_name, last_name
FROM person
423

43.

Data Retrieval Language
4.
Перейменування вихідних стовпців
Обчислюваним, а також будь-яким іншим стовпцям, за бажанням,
можна присвоїти унікальне ім'я за допомогою ключового слова
AS:<выражение>AS<новое имя>
SELECT product_name + " was sold by " + salesperson AS SOLDBY
FROM purchase;
Можна задавати псевдоніми без використання ключового слова, але з обмеженнями:
SELECT product_name + " was sold by " + salesperson SOLDBY
FROM purchase;
SELECT product_name + " was sold by " + salesperson "Sold By"
FROM purchase;
433

44.

Data Retrieval Language
5. Вказування принципу обробки дублів
DISTINCT – забороняє появу рядків-дублів у вихідному
безлічі. Його можна встановити один раз для оператора SELECT.
На практиці спочатку формується вихідна множина,
упорядковується, а потім з нього видаляються повторювані
значення. Зазвичай це займає багато часу і не слід цим
зловживати.
SELECT DISTINCT * FROM person
ALL (діє за замовчуванням) – забезпечує включення до
результати запиту та повторюваних значень
443

45.

Data Retrieval Language
6.
Увімкнення агрегатних функцій
Функції агрегування (функції над множинами, статистичні або
базові) призначені для обчислення деяких значень для
заданої множини рядків. Використовуються такі агрегатні
функції:
AVG | SUM (<выражение) – подсчитывает среднее значение | сумму от
<выражение>.
MIN | MAX (<выражение>) - знаходить максимальне | мінімальне значення.
COUNT(*|<имя столбца>) – підраховує кількість рядків
Але про це далі
453

46.

Data Retrieval Language
WHERE – вибірка даних, які задовольняють певним
умов.
SELECT поле1,...полеN FROM таблиця1, .., таблицяM WHERE
умова1, ... умова Y
463

47.

Data Retrieval Language
Приклади:
SELECT * FROM product WHERE laststockdate IS NULL;
SELECT * FROM product WHERE laststockdate IS NOT NULL;
SELECT product_name, product_price, quantity_on_hand
FROM product WHERE quantity_on_hand > 150;
SELECT product_name, product_price FROM product
WHERE product_name<>"Square Zinculator";
473

48.

Data Retrieval Language
Є й складніші умови:
Попадання в безліч
<конструктор значений строки>IN (<подзапрос>|<набор конструкторов
значень рядка>)
Визначається безліч значень, якому об'єкт порівняння, записаний до
ключового слова IN може належати або не належати. Якщо підзапит не
повертає рядків, то предикат набуває значення FALSE.
Приклади на роботу з безліччю:
SELECT * FROM purchase WHERE salesperson IN ("CA", "BB");
SELECT * FROM purchase WHERE salesperson NOT IN ("CA", "BB");
SELECT * FROM purchase WHERE (salesperson + product_name) in (("CA" + "Small
Widget"), ("GA" + "Chrome Phoobar"))
483

49.

Data Retrieval Language
Приладдя діапазону
<конструктор значений строки>BETWEEN<конструктор
значень рядка 1> AND<конструктор значений строки 2>
Предикат BETWEEN подібний до предикату IN, але замість елементів
безліч він задає включають межі, в які [не] повинно
потрапляти значення, що перевіряється.
WHERE
product_price NOT BETWEEN 1 AND 80;
493

50.

Data Retrieval Language
Бульові оператори
<предикат>(AND|OR|NOT)<предикат>
Примітки: булеви оператора пов'язують один або кілька предикатів, утворюючи
єдине логічне значення TRUE | False. Використовуючи предикати з
булевими операторами, можна значно збільшити і вибіркову
здатність по відбору рядків на результат запиту.
При використанні булевих операторів, особливо оператора NOT, слідує
застосовувати круглі дужки для правильного складання умов (AND
виконується раніше OR).

"%Widget" OR product_price< 20;
SELECT product_name, product_price FROM product WHERE product_name LIKE
"% Widget" AND product_price< 20;
503

51.

Data Retrieval Language

SELECT список полів FROM список таблиць WHERE перевірене значення LIKE
(шаблон) (ESCAPE (ім'я перепустки));
Один будь-який символ - _
SELECT person_code, first_name, last_name FROM person WHERE person_code LIKE "_A";
Будь-який підрядок - %
SELECT product_name FROM product WHERE product_name LIKE "%Chrome%";
Якщо потрібно знайти текст із символом % (наприклад, назва продукту
ab%cdef):
WHERE product_name LIKE "ab$%c%" ESCAPE "$";
Перший % читається як символ у назві, другий – як будь-який рядок.
513

52.

Data Retrieval Language
Оператор зразкового пошуку LIKE
… where по батькові like '%%'
… where по батькові like ‘І%’
… where по-батькові like ‘%вич’
… where Прізвище like ‘____ов’
523

53.

Data Retrieval Language
Оператор зразкового пошуку LIKE

select product_name from purchase
where product_name like "%Widget"
select product_name from purchase
where product_name like "%$%Widget"
escape "$"
533

54.

Data Retrieval Language
Сортування
SELECT список стовпців FROM список таблиць WHERE умова
ORDER BY список стовпців ASC (DESC);
За зменшенням:
SELECT product_name, product_price FROM product
ORDER BY product_price DESC;
За зростанням:
SELECT product_name, product_price FROM product
ORDER BY product_name ASC;
543

55.

Практичне завдання №2
1. Напишіть запит, який повністю показує таблицю purchase.
2. Напишіть запит, який вибирає стовпці product_name і quantity з
таблиці Purchase.
3. Напишіть запит, який вибирає ці стовпці у зворотному порядку.
4. Напишіть запит, який виводить для кожного рядка таблиці person
наступний текст:
started work *. Отриманому стовпцю
присвоїти псевдонім "Started Work".
5. Напишіть запит, що виводить найменування продуктів product_name
(Таблиця product), для яких ціна не визначена (NULL).
6. Напишіть запит, що виводить назву продуктів product_name
(Таблиця purchase), яких продали від 3 до 23 штук.
* MSSQL не підтримує об'єднання стовпців з типами даних varchar та date. Використовуйте оператор
конветації: CONVERT(VARCHAR, hiredate)
3

56.

Практичне завдання №2 (продовження)
7. Напишіть запит, який виводить прізвища співробітників, яких
прийняли на роботу 1го, 15го та 28го лютого 2010 року.
8. Напишіть запит, який виводить назву продуктів.
product_name (таблиця purchase), проданих співробітниками, прізвища яких починаються "B".
9. Напишіть запит, який виводить найменування продуктів
product_name (таблиця purchase), проданих співробітниками, прізвища яких не починаються на
"B".
10. Напишіть запит, який виводить прізвища та дату прийому на роботу
співробітників, прізвища яких починаються на “B” та яких прийняли
на роботу раніше ніж 1 березня 2010 року.
11. Напишіть запит, який виводить найменування продуктів
product_name і дату останнього постачання останньогоданогочасу (таблиця
product), найменування яких Small Widget, Medium Widget та Large
Widget або ті, для яких не вказано дату останнього постачання.
Сортуйте за спаданням дати останнього постачання.
563

57.

SQL. Базовий курс
Частина 5. Вибірка даних із кількох таблиць
www.aplana.ru

58.

Вибірка даних із кількох таблиць
SELECT ім'я_таблиці_1.ім'я_стовпця, ім'я_таблиці_2.
ім'я_стовпця
FROM ім'я_таблиці_1, ім'я_таблиці_2;

FROM purchase, person;
Декартове твір (Cartesian product) - з'єднання без
конструкції WHERE, в результаті якого кожен рядок
однієї таблиці комбінується з кожним рядком інший
таблиці.
583

59.

Вибірка даних із кількох таблиць з умовою
SELECT ім'я_таблиці_1.ім'я_стовпця, ім'я_таблиці_2. ім'я_стовпця
FROM ім'я_таблиці_1, ім'я_таблиці_2
WHERE ім'я_головної_таблиці.первинний_ключ =
ім'я_підпорядкованої_таблиці.зовнішній_ключ;
SELECT purchase.product_name, person.first_name, person.last_name
FROM purchase, person
WHERE person.person_code = purchase.salesperson;
593

60.

Типи з'єднання
Існують також інші способи з'єднання таблиць за ключами:
<таблица А> [<тип соединения>] JOIN<таблица B>ON<предикат>
<тип соединения>є одним з аргументів: INNER | (LEFT | RIGHT |
FULL)
INNER – включає рядки, в яких є стовпці з даними, що збігаються
таблиць, що об'єднуються. Використовується за промовчанням.
LEFT – включає всі рядки таблиці А (ліва таблиця) та всі збігаються
значення з таблиці B. Стовпці рядків, що не збігаються, заповнюються NULL-значеннями.
RIGHT – включає всі рядки таблиці B (права таблиця) та всі збігаються
значення таблиці А. обернений варіант для лівого об'єднання.
FULL – включає усі рядки обох таблиць. Стовпці рядків, що збігаються
заповнені реальними значеннями, а рядків – NULL-значеннями.
OUTER (зовнішній) – уточнююче слово, що означає, що рядки, що не збігаються з
провідної таблиці включаються разом із збігаються.
603

61.

Варіанти з'єднання таблиць
address
phone
INNER JOIN
SELECT * FROM address INNER JOIN phone ON address.ClientID=phone.ClientID
613

62.

Варіанти з'єднання таблиць
address
phone
SELECT * FROM address, phone WHERE address.clientID=phone.ClientID
623

63.

Варіанти з'єднання таблиць
address
phone
LEFT JOIN
SELECT * FROM address LEFT JOIN phone ON address.ClientID=phone.ClientID
633

64.

Варіанти з'єднання таблиць
address
phone
RIGHT JOIN
SELECT * FROM address RIGHT JOIN phone ON address.ClientID=phone.ClientID
643

65.

Варіанти з'єднання таблиць
address
phone
FULL JOIN
SELECT * FROM address FULL JOIN phone ON address.ClientID=phone.ClientID
653

66.

Оператори з'єднання
UNION повертає всі рядки з обох операторів SELECT; повторювані
значення видаляються.
UNION ALL повертає всі рядки з обох операторів SELECT; повторювані
Значення показуються.
INTERSECT повертає рядки, які повернуті і першим, і другим
оператором SELECT.
EXCEPT повертає рядки, які повернуто першим оператором SELECT,
виключаючи ті, які повернуто другим оператором.
Кількість та порядок стовпців, що повертаються SELECT з обох таблиць, повинні
збігатися.
663

67.

Оператори з'єднання
SELECT product_name
FROM purchase
ORDER BY product_name
673
SELECT product_name
FROM purchase_archive
ORDER BY product_name
SELECT product_name
FROM purchase
UNION
SELECT product_name
FROM purchase_archive
ORDER BY product_name

68.

Оператори з'єднання
SELECT product_nam e
FRO M purchase
U N IO N ALL
SELECT product_nam e
FRO M purchase_archive
O RD ER BY 1
683
SELECT product_nam e
FRO M purchase
EXCEPT
SELECT product_nam e
FRO M purchase_archive
O RD ER BY 1
SELECT product_nam e
FRO M purchase
IN TERSECT
SELECT product_nam e
FRO M purchase_archive
O RD ER BY 1

69.

Псевдонім у галузі FROM
При використанні великих баз із схемами прийнято
використання псевдонімів:
SELECT purc.product_name, prod.laststockdate, pers.first_name,
pers.last_name
FROM purchase as purc,
Person as pers,
Product prod
WHERE pers.person_code = purc.salesperson AND
prod.product_name = purc.product_name;
693

70.

Практичне завдання №3
1. Напишіть запит, що виводить декартове твір таблиць product
та purchase.
2. Напишіть запит, який виводить найменування проданого товару
product_name, кількість quantity (таблиця purchase) та
quantity_on_hand (таблиця product).
3.Напишіть запит, що виводить найменування товару product_name
(таблиця purchase);
product) та прізвище продавця last_name (таблиця person).
4. Напишіть запит, що виводить стовпці product_name, first_name,
last_name зовнішнього об'єднання таблиць purchase та person. Використовуйте для таблиць
короткі псевдоніми.
703

71.

Практичне завдання №3 (продовження)
5. Напишіть запит, який виводить усі неповторні в purchase коди
продавців
salesperson з таблиці purchase_archive.
6. Напишіть запит, який виводить коди тільки тих продавців salesperson з
таблиці purchase, які також містяться в таблиці purchase_archive.
7. Напишіть запит, який виводить усі (у тому числі повторювані) коди
продавців salesperson з таблиць purchase і purchase_archive.
713

72.

SQL. Базовий курс
Частина 6. Агрегатні функції. Групування
даних.
www.aplana.ru

73.

Математичні оператори
Математичний оператор - символи, що позначають операції (+, -, *, /)
Обчислення з використанням даних із таблиць.
SELECT product_name, product_price * 1.07 FROM product;
SELECT product_name, product_price * quantity_on_hand
FROM product;
SELECT product_name, product_price * 1.07 * quantity_on_hand product_price * quantity_on_hand
FROM product;
SELECT product_name, product_price* (quantity_on_hand + 10)
FROM product;
733

74.

Математичні оператори
Функції агрегування (функції над множинами, статистичні чи базові)
призначені для обчислення деяких значень для заданої множини
рядків.
1. SUM - підсумовує значення та повертає підсумок.
SELECT SUM(quantity)
FROM purchase;
2. AVG – повертає середнє значення за вказаним
стовпцю.
SELECT AVG(product_price)
FROM product;
743

75.

Математичні оператори
3. MIN – повертає мінімальне значення із зазначеного стовпця.
SELECT MIN(product_price)
FROM product;
4. MAX – повертає максимальне значенняіз зазначеного стовпця.
SELECT MAX(product_price)
FROM product;
753

76.

Математичні оператори
5. COUNT – підраховує записи.
SELECT COUNT(*)
FROM purchase; - число рядків з урахуванням NULL значень
SELECT COUNT(product_name)
FROM purchase;--значень у стовпці, ігноруючи NULL
763

77.

GROUP BY
Цей розділ призначений для об'єднання результатів запиту до груп та розрахунку
кожної з них статистичних значень. Іноді використовують термін
"згрупована таблиця".
SELECT product_name, SUM(quantity)
FROM purchase
GROUP BY product_name;
До оператора SELECT можна ввімкнути кілька групових функцій.
SELECT product_name, SUM(quantity) "Total Sold", COUNT(quantity) Transactions
FROM purchase
GROUP BY product_name;
773

78.

HAVING
HAVING – є підрозділом, призначеним для обмеження числа
рядків у згрупованій таблиці та є частиною розділу GROUP BY.
Предикат цього розділу будується за тими ж семантичними правилами, що й
у розділі WHERE, проте безпосередньо у предикаті можуть брати участь тільки
ті стовпці, які вказані в розділі GROUP BY. Інші можна
використовувати лише усередині функцій агрегування. Цей розділ
обмежує склад груп (підгруп) рядків, на які розбивається
результат запиту. До груп (підгруп) включаються тільки ті з
безлічі можливих рядків, для значень яких виконуються умови
предикату розділу HAVING. Всередині розділу HAVING можна використовувати
вкладені запити з функціями агрегування, а також пов'язані
підзапити.
783

79.

HAVING
Тобто, підбивши підсумок вище описаного, можна звузити
призначення підрозділу до:
За допомогою конструкції HAVING можна
фільтрувати групи.
HAVING працює для груп, як і WHERE для окремих записів.
SELECT product_name, SUM(quantity) "Total Sold",
COUNT(quantity) Transactions
FROM purchase
GROUP BY product_name
HAVING SUM(quantity)< 5;
793

80.

Практичне завдання №4
1. Напишіть запит, який показує, якою буде ціна продукту product_price після
збільшення на 15%.
2. Напишіть запит, який показує, скільки всього є товарів у таблиці product.
3.Напишіть запит, який показує, для якої кількості товарів (таблиця product) не
вказано ціну.
4. Напишіть запит, що виводить мінімальну та максимальну ціну товарів product_price.
5. Напишіть запит, який показує, яка сума була виручена з продажу товарів кожного
найменування.
6. Напишіть запит, який показує, яка сума була виручена з продажу товарів кожного
найменування. Вивести лише ті записи, для яких сума продажів більша за 125.
803

81.

SQL. Базовий курс
Частина 7. Підзапити
www.aplana.ru

82.

Підзапити
Підзапит – це звичайний запит SELECT, вкладений в оператор
SELECT,
UPDATE
або
DELETE.
Він використовується як джерело даних для розділу FROM або
WHERE батьківського оператора.
823

83.

Підзапити
Є деякі обмеження використання підзапитів:
833
Підзапит повинен вибирати лише один стовпець (за винятком підзапиту з
предикатом EXISTS), і тип даних його результату має відповідати типу
даних значення, вказаному у предикаті.
У ряді випадків можна використовувати ключове слово DISTINCT для гарантії
отримання єдиного значення.
У вкладеному запиті не можна вмикати розділ ORDER BY та UNION.
Підзапит може бути ліворуч і праворуч від умови пошуку.
У підзапитах можуть використовуватися функції агрегування без розділу
GROUP BY

84.

Однорядкові підзапити
Однорядковий підзапит - це підзапит, який повертає лише 1 значення.
Використовуються символи порівняння з результатом вкладеного запиту (=,<>, <, <=,
>, >=)
SELECT * FROM product
WHERE laststockdate = (SELECT laststockdate
FROM product WHERE product_name = "Small Widget");
Приклад (використання агрегатної функції в однорядковому підзапиті):
SELECT * FROM product WHERE product_price >
(SELECT AVG(product_price) FROM product);
843

85.

Багаторядкові підзапити
Багаторядковий підзапит – це підзапит, який повертає лише >=1
значення.
Для таких підзапитів не можна порівняти з
допомогою знаків рівності/нерівності; необхідно використовувати функцію
IN.
SELECT * FROM product
WHERE product_name IN
(SELECT DISTINCT product_name FROM purchase);
UPDATE product SET product_price = product_price * 0.9
WHERE product_name NOT IN (SELECT DISTINCT product_name
FROM purchase);
853

86.

EXISTS
EXISTS використовує підзапит як аргумент і
оцінює його як істинний, якщо у підзапиті є
вихідні дані, а інакше як помилковий.
Виконується підзапит один раз і може містити
кілька стовпців, оскільки їх значення не перевіряються,
а просто фіксується результат наявності рядків.
Примітки щодо предикату EXISTS:
EXISTS - предикат, що повертає значення TRUE або FALSE, та
його можна застосовувати окремо або разом з іншими булевими
виразами.
863

87.

EXISTS
SELECT * FROM product
WHERE EXISTS
(SELECT * FROM purchase
WHERE product.product_name = purchase.product_name);
873

88.

ALL - порівняння буде проводитися з усіма записами, які
повертає підзапит (або просто з усіма значеннями у наборі). True
повернеться лише у тому випадку, якщо всі записи, які повертає
підзапит, будуть задовольняти вказану вами умову.
SELECT * FROM product
WHERE product_price >= ALL (SELECT product.product_price
FROM purchase, продукт


Запит поверне всі товари з таблиці product, ціна яких більша або
дорівнює ціні кожного товару, проданого співробітником із кодом "GA".
883

89.

Групові умови (оператори порівняння).
ANY - порівняння поверне true, якщо умові задовольнятиме хоча б одна
запис із підзапиту (або набору).
SELECT * FROM product WHERE product_price > ANY (SELECT
product.product_price
FROM purchase, продукт
WHERE purchase.product_name = product.product_name
AND purchase.salesperson = "GA");
Запит поверне всі записи з таблиці product, для яких ціна товару
більше ціни будь-якого продукту, проданого співробітником із кодом "GA".
SOME - робить те саме, що ANY. Повністю взаємозамінні.
893

90.

Практичне завдання №5
1. Напишіть запит, який повертає всіх співробітників, яких взяли на роботу в той же
день, що й співробітника John Smith.
2. Напишіть запит, який повертає всі товари, ціна яких нижча за середню ціну.
3. Напишіть запит, який повертає всі товари, які продавали більше одного
рази.
4. Виведіть збільшену на 15% ціну товарів, що продавалися більше одного разу.
5. Використовуючи умову EXISTS, напишіть запит, який повертає всіх працівників,
які хоча б один раз щось продали.
6. Напишіть запит, який повертає всі товари з таблиці product, ціна яких
менше ціни будь-якого товару, проданого співробітником із кодом "GA".
7. напишіть запит, який поверне всі товари з таблиці product, ціна яких менша
ціни хоча б одного товару, проданого співробітником із кодом "GA". Впевніться, що
оператори SOME та ANY взаємозамінні.
903

91.

SQL. Базовий курс
Частина 8. Функції для роботи з рядками, датами та
числами
www.aplana.ru

92.

Функції для роботи з числами
ROUND – округлює числа з будь-якою заданою точністю.
ROUND (вхідне значення, число знаків після десяткової точки)
SELECT product_name, ROUND(product_price, 0)
FROM product;
SELECT ROUND(1234.5678, 3) --MSSQL
SELECT ROUND(1234.5678, 3) FROM DUAL;
Функція ROUND
ROUND(1234.5678,4)
ROUND(1234.5678, 3)
ROUND(1234.5678, 2)
ROUND(1234.5678,1)
ROUND(1234.5678,0)
ROUND(1234.5678, -1)
ROUND(1234.5678,-2)
ROUND(1234.5678,-3)
923
Значення, що повертається
1234.5678
1234.568
1234.57
1234.6
1235
1230
1200
1000
--Oracle

93.

Функції для роботи з числами
TRUNC - усікає число, знижуючи його точність.
Функція TRUNC
Значення, що повертається
TRUNC(1234.5678,4)
1234.5678
TRUNC(1234.5678,3)
1234.567
TRUNC(1234.5678, 2) 1234.56
TRUNC(1234.5678,1)
1234.5
TRUNC(1234.5678,0)
1234
TRUNC(1234.5678,-1)
1230
TRUNC(1234.5678, -2) 1200
TRUNC(1234.5678, -3) 1000
933

94.

Допоміжні таблиці
Допоміжні (dummy) таблиці
Для виконання функцій, без прив'язки до конкретних таблиць у ряді СУБД
необхідно вказувати службову таблицю, оскільки SQL має на увазі
конструкцію select … from.
Oracle – DUAL
DB2 – SYSDUMMY1
SYBASE – DUMMY
MySQL – DUAL
MSSQL – відсутня. MSSQL розпізнає службові запити без
необхідності вказувати dummy-таблицю.
943

95.

Функції для роботи з датами
GETDATE – повертає поточну дату.
select getdate();
DATEADD – Повертає дату, отриману як суму вихідної дати
date та інтервалу, доданого до заданого компонента datepart дати
date.
АDD_МONTHS(величина, кількість, початкова дата)
SELECT DATEADD(month, 1, GETDATE());
SELECT DATEADD(year, -2, GETDATE());
953

96.

Функції для роботи з датами
EOMONTH – повертає останній день будь-якого місяця, вказаного
у переданій їй дати (MSSQL 2012+).
EOMONTH(дата)
SELECT EOMONTH(GETDATE());
SELECT EOMONTH("2015-03-15");
SELECT first_name, last_name, hiredate, EOMONTH(hiredate)+1
FROM person;
963

97.

Функції для роботи з датами
DATEDIFF – повертає кількість одиниць, що поділяють дві дати.
DATEDIFF(величина, початкова дата, кінцева дата)
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
SELECT DATEDIFF(MONTH, "17-AUG-2012", GETDATE());
973

98.

Функції для роботи з текстом
UPPER – ставить усі символи рядка у верхній регістр.
LOWER – ставить усі символи рядка в нижній регістр.
INITCAP (oracle) – змінює регістр рядка на змішаний (перша літера кожного
слова буде в верхньому регістрі, інше слово – у нижньому).
SELECT UPPER(product_name) FROM product;
SELECT LOWER(product_name) FROM product;
SELECT INITCAP("this TEXT hAd UNpredictABLE caSE") FROM DUAL;
983

99.

Функції для роботи з текстом
LEN – визначає довжину рядка.
SELECT product_name, LEN(product_name) LENGTH
FROM product
WHERE LEN(product_name) > 15;
993

100.

Функції для роботи з текстом
SUBSTRING – обрізає значення параметра.
SUBSTRING(початковий_текст, позиція початкового символу,
кількість символів)
SUBSTRING (рядок 1, a, [, b])
Повертає частину «Рядок 1», що починається із символу з номером a,
та має довжину b символів. Якщо a = 0, це рівнозначно тому, що
a = 1 (початок рядка) якщо b позитивно повертаються символи
зліва направо. Якщо b негативно, починаючи з кінця рядка і
вважаються праворуч наліво! Якщо b відсутня, то за замовчуванням
повертаються всі символи, до кінця рядка
100
3

101.

Функції для роботи з текстом
SELECT SUBSTRING(item_id, 1, 3) LOCATION,
SUBSTRING(item_id, 5, 3) ITEM_NUMBER
FROM old_item;
101
3

102.

Функції для роботи з текстом
CHARINDEX-знаходить позицію символу (або символів), що розділяє елементи
Рядок.
CHARINDEX (рядок 1, рядок 2, [, a])
Повертає розташування "рядок 1", в "рядок 2". "рядок 2" проглядається
ліворуч, починаючи з позиції a. Якщо a негативно, то "рядок 2", проглядається
праворуч. Значенням за a є 1, що дає в результаті позицію,
першого входження, "рядок 1", в "рядок 2". Якщо при заданій a, "рядок 1" не
знайдена, повертається 0
102
3

103.

Функції для роботи з текстом
CHARINDEX(шуканий_символ, текст для пошуку, позиція
_початкового_символу)
SELECT item_desc, CHARINDEX(",", item_desc, 1)
FROM old_item;
103
3

104.

Вкладення функцій
SELECT item_desc, SUBSTRING(item_desc, 1, CHARINDEX(",", item_desc, 1))
CATEGORY
FROM old_item;
104
3

105.

Вкладення функцій
105
3
SELECT item_desc,
SUBSTRING(item_desc, 1, CHARINDEX(",", item_desc, 1)-1) CATEGORY,
SUBSTRING(item_desc, CHARINDEX(",", item_desc, 1)+2, 99) ITEM_SIZE
FROM old_item;

106.

Практичне завдання №6
1. Використовую функції для роботи з датами та числами, порахуйте,
скільки вам повних літ.
2. Виведіть рядок "я знаю текстові функції" у верхньому і
нижньому регістрі.
3. Дізнайтеся довжину цього рядка.
4. Працюючи зі стовпцем purchase.product_name, виведіть:
перші три символи
всі символи, що залишилися, починаючи з четвертого
повний рядок
106
3

107.

Корисні ресурси
http://sqlfiddle.com/ - інструмент, що емулює порожню БД:
дозволяє виконувати значну частину DML, DDR та DR
запитів. Підтримує 5 основних діалектів
http://www.sql-tutorial.ru/ - інтерактивний підручник з SQL на
російською
http://www.sql-ex.ru/ - інтерактивний портал для вирішення
задач на SQL
https://dev.mysql.com/downloads/mysql/ - безкоштовний SQL
сервер під різні ОС
https://www.mysql.com/products/workbench/ - безкоштовний
інструмент для роботи з сервером MySql
107
3

108.

Дякую за увагу!
Ваші запитання?
Компанія «Аплана»
Сергій Воробйов
Провідний інженер-тестувальник
+7-917-556-13-49
www.aplana.ru