MySQL by Leonid Flegantov

Система управління базами даних

методичні вказівки до лабораторних занять

Фрагмент методического пособия представлен для ознакомления с продукцией Электронного магазина МЕТОДИЧКА http://metodichka.com.ua/ МЕТОДИЧКА - Лучшие пособия для самостоятельного практического обучения!

ЗАНЯТТЯ 2. СТВОРЕННЯ ПРОЕКТУ БАЗИ ДАНИХ...................................................................................................4 ПРОЕКТ БАЗИ ДАНИХ BOOK_SHOP..........................................................................................................................................4 ПОЛЯ ТАБЛИЦЬ, ЇХ ПРИЗНАЧЕННЯ І ВЛАСТИВОСТІ................................................................................................................6 ІТОГИ 2. ЩО МИ УЗНАЛИ ПРО ПРОЕКТУВАННЯ БАЗИ ДАНИХ................................................................................................7 ЗАНЯТТЯ 3. СТВОРЕННЯ СТРУКТУРИ БАЗИ ДАНИХ..............................................................................................7 СТВОРЕННЯ НОВОЇ БАЗИ ДАНИХ.............................................................................................................................................8 СТВОРЕННЯ ТАБЛИЦЬ У БАЗІ ДАНИХ......................................................................................................................................9 СТВОРЕННЯ ТАБЛИЦІ CUSTOMERS ТА ПЕРЕГЛЯД ЇЇ СТРУКТУРИ.............................................................................................9 ЗАНЯТТЯ 4. ВСТАВКА ЗАПИСІВ У ТАБЛИЦІ............................................................................................................12 ВСТАВКА ЗАПИСІВ У ТАБЛИЦЮ CUSTOMERS........................................................................................................................13 ВСТАВКА ЗАПИСІВ У ТАБЛИЦЮ BOOKS................................................................................................................................15 ВСТАВКА ЗАПИСІВ У ТАБЛИЦЮ BOOK_REVIEWS..................................................................................................................16 ЗАНЯТТЯ 7. ВИБОРКА ДАНИХ З КІЛЬКОХ ТАБЛИЦЬ (СКЛАДНІ ЗАПИТИ НА ВИБОРКУ).......................16 ПРОСТЕ ОБ’ЄДНАННЯ ДВОХ ТАБЛИЦЬ..................................................................................................................................17 ОБ’ЄДНАННЯ ТРЬОХ І БІЛЬШЕ ТАБЛИЦЬ...............................................................................................................................19 ОБ’ЄДНАННЯ ТАБЛИЦЬ ЗА ОСТАЧЕЮ...................................................................................................................................22 ІТОГИ 7. ЩО МИ УЗНАЛИ ПРО ВИБОРКУ ДАНИХ З КІЛЬКОХ ТАБЛИЦЬ.................................................................................24

Приобретайте наше методическое пособие, Вы без труда отработаете все занятия и хорошо освоите материал! МЕТОДИЧКА http://metodichka.com.ua/

Заняття 2. Створення проекту бази даних Ми будемо працювати над створенням бази даних на прикладі оптового книжкового магазину – книгарні. Така база даних повинна містити основні відомості про книги, про клієнтів-замовників та їх замовлення, а також забезпечувати можливість швидкого пошуку і перегляду інформації, що міститься у базі даних. Перед тим, як перейти до створення комп’ютерної бази даних, необхідно скласти її проект “на папері”, а саме необхідно: 1. Визначити назву бази даних; 2. Визначити перелік всіх необхідних таблиць та назву кожної таблиці; 3. Визначити структуру кожної окремої таблиці: кількість стовпців (полів) у таблиці, назву і властивості кожного поля; 4. Продумати зв’язки, які мають бути між таблицями, і створити у кожній таблиці ключові поля, які необхідні для утворення цих зв’язків. Проект бази даних book_shop Ознайомтеся з проектом майбутньої бази даних. 1. Назва бази даних буде такою: book_shop (“книгарня”). 2. У базі даних має бути п’ять таблиць. Назви таблиць: customers (“користувачі” або “клієнти”); orders (“замовлення”); order_items (“замовлені_екземпляри”); books (“книги”); book_reviews (“анотації_до_книг” або “короткий зміст”). 3. Структура таблиць бази даних: customers customerID

name

address

1 Іван Іваненко 2 Петро Петренко 3 Сидор Сидоренко

orders orderID

customerID 1 2 3 4

м. Полтава, вул. Калініна 1 кв. 1 м. Полтава, вул. Фрунзе 2, кв. 2 м. Полтава, вул. Жовтнева 3, кв. 3

amount 3 1 2 3

60.00 144.00 42.00 222.00

date 01-09-2003 10-09-2003 15-09-2003 25-09-2003

order_items ISBN

orderID

966-7393-98-4 5-8459-0291-6 966-539-320-0 966-7393-98-4 5-8459-0291-6 966-539-320-0 966-7393-98-4

books ISBN 966-7393-98-4 5-8459-0291-6 966-539-320-0

quantity 1 2 2 3 4 4 4

author

1 2 1 1 1 2 1

title

year

Томсон Лаура, Веллінг Дюк Аткинсон Леон

Разработка Web-приложений на PHP и MySQL MySQL. Библиотека профессионала Каратыгин С.А., Visual FoxPro 7. Тихонов А.Ф., Руководство пользователя с Тихонова Л.Н. примерами

price

2001

60.00

2002

42.00

2003

60.00

book_reviews ISBN review 966-7393-98-4 5-8459-0291-6

966-539-320-0

Руководство по совместному применению PHP и MySQL для профессиональных программистов. Ориентировано на решение реальных задач. В книге описана программа MySQL версии 3.23 - самый последний стабильный выпуск, доступный на момент написания книги. Содержит примеры проектирования и реализации систем корпоративного уровня. Описана новая версия системы управления базами данных Visual FoxPro 7. Содержит большое количество примеров и иллюстраций.

4. Схема зв’язків між таблицями: customers customerID name address

orders ∞ orderID customerID amount date

∞

order_items ∞ ISBN orderID quantity

books ISBN author title price

book_reviews

1 ISBN

review

Поля таблиць, їх призначення і властивості Таблиця customers Поле

Призначення поля

Властивості

customerID унікальний реєстраційний номер клієнта, ключове поле для зв’язку з таблицею orders name П.І.Б. клієнта address адреса кліента

ключове поле, первинний ключ типу лічильник (autoincrement) текстове поле текстове поле

Таблиця orders Поле

Призначення поля

Властивості

orderID

унікальний реєстраційний номер замовлення, ключове поле для зв’язку з таблицею order_items customerID для зв’язку з таблицею customers amount поле містить рахунок – загальну вартість замовлення date дата замовлення

ключове поле, первинний ключ типу лічильник (autoincrement) числове поле цілочисельного типу числове поле, десяткові числа, 2 знака після коми поле типу “дата”

Таблиця order_items Поле

Призначення поля

ISBN orderID quantity

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

Властивості

ключове поле – зовнішній ключ, текстового (символьного) типу числове поле цілочисельного типу поле цілочисельного типу

Таблиця books Поле

Призначення поля

ISBN

унікальний ISBN-номер книги, ключове поле для зв’язку з таблицями order_items та book_reviews author П.І.Б. автора книги title

назва книги

year

рік видання книги

price

ціна книги

Властивості

ключове поле – зовнішній ключ, текстового (символьного) типу поле текстового (символьного) типу поле текстового (символьного) типу поле текстового (символьного) типу десяткове число, 2 знака після коми

Таблиця book_reviews Поле

Призначення поля

ISBN

унікальний ISBN-номер замовленої книги – для зв’язку з таблицею books review короткий опис змісту книги (анотація)

Властивості

ключове поле – зовнішній ключ, текстового типу поле текстового (символьного) типу

Ітоги 2. Що ми узнали про проектування бази даних 1. Перед тим, як перейти до створення комп’ютерної бази даних, необхідно скласти її проект “на папері”, а саме: 1.1.Визначити назву бази даних; 1.2.Визначити перелік всіх необхідних таблиць та назву кожної таблиці; 1.3.Визначити структуру кожної окремої таблиці: кількість стовпців (полів) у таблиці, назву і властивості кожного стовпця; 1.4.Продумати зв’язки, які мають бути між таблицями, і створити у кожній таблиці ключові поля, які необхідні для утворення цих зв’язків. 2. Назву бази даних MySQL, а також назви полів і стовпців слід задавати латиницею. Назви таблиць в ОС Windows не чутливі до регістру, тобто їх можна набирати як великими, так і малими літерами. В інших операційних системах назви таблиць можуть бути чутливими до регістру. Назви таблиць та стовпців – чутливі до регістру, їх прийнято набирати малими літерами. Назви не повинні містити відстані.

Заняття 3. Створення структури бази даних Процедура створення бази даних складається з двох етапів: • спочатку необхідно створити структуру бази даних, тобто порожню базу даних та таблиці всередині неї, які готові до введення даних; • потім треба ввести дані у базу даних, тобто заповнити таблиці даними. Створення структури бази даних включає: • створення самої бази даних, • створення таблиць та визначення їх структури, • організацію зв'язків між таблицями через завдання ключових полів у таблицях. Для створення структури і роботи з базами даних в MySQL використовують спеціальні команди – інструкції SQL. SQL – скорочення від англ.: Structured Query Language – “мова структурованих запитів” – це особлива мова програмування, яка

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

SHOW DATABASES

Створення нової бази даних

CREATE DATABASE ім’я_бази_даних

Знищення бази даних

DROP DATABASE ім’я_бази_даних Примітка. Будьте обережні! Інструкція DROP DATABASE знищує базу даних разом з усіма таблицями та даними. Знищену базу даних відновити неможливо. Створіть нову базу даних book_shop. Для цього: 1. Запустіть сервер і монітор MySQL. 2. У моніторі MySQL виконайте команду mysql> create database book_shop; Має з’явитися повідомлення: Query OK, 1 row affected (0.00 sec) Це означає, що база даних створена успішно. Якщо база даних не була створена, то можливе таке повідомлення: ERROR 1007: Can't create database 'book_shop'. Database exists Воно означає, що базу даних з такою назвою створити неможливо, тому, що вона вже існує. Тоді необхідно створити базу даних з іншою назвою. 3. Після успішного створення бази даних перегляньте список існуючих баз даних. Команда: mysql> show databases; У відповідь маєте одержати список баз даних, у якому має бути і нова щойно створена вами база даних:

+------------+ | Database | +------------+ | book_shop | | mysql | | test | +------------+ 3 rows in set (0.05 sec) Створення таблиць у базі даних Для того, щоб створити таблиці у базі даних book_shop необхідно: • увійти у базу даних. Команда: mysql> use назва_бази_даних; • створити по черзі кожну таблицю. Створення нової таблиці (інструкція SQL): CREATE TABLE назва_таблиці (

стовпець1, стовпець2, . . . ) Після інструкції CREATE TABLE необхідно вказати назву таблиці, і далі, у круглих дужках ввести через кому визначення всіх стовпців (полів) таблиці, вказати ключове поле. Визначення стовпця починається з імені стовпця та містить опис його властивостей (тип даних, розмір тощо). Інші важливі інструкції SQL: Перегляд списку таблиць у поточній базі SHOW TABLES даних: Перегляд структури таблиці:

DESCRIBE назва_таблиці

Знищення таблиці у поточній базі даних:

DROP TABLE назва_таблиці

Примітка. Будьте обережні! Інструкція DROP TABLE знищує таблицю разом з усіма даними. Знищену таблицю відновити неможливо. Створення таблиці customers та перегляд її структури Таблиця customers створюється такою інструкцією SQL: CREATE TABLE customers ( CustomerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(32) NOT NULL,

Address CHAR(128) NOT NULL ) Ця інструкція створює у поточній базі даних таблицю customers, яка містить три поля: CustomerID Name Address

(Ідентифікатор – код_користувача) (Ім’я – ім’я_користувача), (Адреса – адреса_користувача),

Поле CustomerID – цілочисельного типу INT, має властивість Лічильник (AUTO_INCREMENT), є ключовим полем (PRIMARY KEY). Поле Name – символьного типу, або текстове поле (CHAR), довжина поля 32 символи (CHAR(32)), воно не може бути порожнім, тобто це обов’язкове поле (NOT NULL). Поле Address – символьного типу, або текстове поле (CHAR), довжина поля 128 символи (CHAR(128)), воно не може бути порожнім (NOT NULL). Зауваження. Інструкції SQL можна виконувати двома способами. 1 спосіб. Можна просто ввести інструкцію у командний рядок монітору MySQL. Якщо при цьому буде зроблено хоч одну помилку, то всю інструкцію доведеться вводити спочатку. Цей спосіб застосовуть лише для виконання простих інструкцій. Наприклад, щоб створити таблицю customers першим способом у моніторі MySQL необхідно виконати таку команду: mysql> CREATE TABLE customers ( CustomerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(32) NOT NULL, Address CHAR(128) NOT NULL ); 2 спосіб. При виконанні складних інструкцій SQL, зручно використовувати другий спосіб – виконання інструкцій SQL записаних у текстовому файлі. Послідовність дій: • необхідні інструкції SQL наберіть у простому текстовому редакторі, наприклад, у Блокноті, і збережіть у файлі з розширенням *.txt у своєму робочому каталозі. Краще, якщо ім’я файлу буде написано англійськими літерами. У разі необхідності, цей файл можна буде відкрити, щоб виправити помилки і внести необхідні зміни; • щоб виконати інструкції SQL, що зберігаються у текстовому файлі, у моніторі MySQL введіть одну з наступних команд: mysql> source шлях_до_sql-файлу;

або mysql> \. шлях_до_sql-файлу; Створіть таблицю customers у базі даних book_shop. Для цього: 1. Увійдіть у базу даних book_shop. Команда: mysql> use book_shop; У відповідь маєте одержати: Database changed Це означае, що ви увійшли у вказану базу даних. 2. Створіть таблицю customers. Для цього у моніторі MySQL виконайте команду: mysql> CREATE TABLE customers ( CustomerID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(32) NOT NULL, Address CHAR(128)NOT NULL); З’явиться повідомлення Query OK, 0 rows affected (0.05 sec) Це означає, що інструкції SQL із файлу tabl1.txt виконані успішно. Можливе й інше повідомлення: ERROR 1050: Table 'customers' already exists Воно означає, що таблиця customers вже існує.  Перегляньте список таблиць у поточній book_shop. Команда: mysql> show tables; Відповідь сервера має бути такою: +---------------------+ | Tables_in_book_shop | +---------------------+ | customers | +---------------------+ 1 row in set (0.06 sec)

 Перегляньте структуру таблиці customers. Команда: mysql> describe customers; Відповідь сервера:

Пользуясь методическим пособием, Вы детально изучите и отработаете конфигурацию программы. Приобретайте наше методическое пособие – лучший помощник и консультант! МЕТОДИЧКА http://metodichka.com.ua/

Заняття 4. Вставка записів у таблиці Перш ніж перейти до роботи з базою даних необхідно зберегти в ній які-небудь дані, тобто вставити у таблиці бази даних певну кількість записів. Вставка нового запису (рядка) у таблицю виконується інструкцією INSERT INTO назва_таблиці

VALUES (значення_поля1,значення_поля2,...) У круглих дужках після інструкції VALUES необхідно через кому вказати зміст записів – значення, які необхідно внести в усі поля таблиці. Важливо, щоб ці значення відповідали властивостям полів і йшли у тому самому порядку, у якому йдуть поля у таблиці. Порада. Порядок полів у таблиці та їх властивості можна переглянути за допомогою інструкції DESCRIBE назва_таблиці, яка показує структуру таблиці. Вставка кількох записів: зміст записів треба вказати після інструкції VALUES. Записи необхідно заключити у круглі дужки та відокремити їх комами:

INSERT INTO назва_таблиці

VALUES (значення_поля1,значення_поля2,...), (значення_поля1,значення_поля2,...), . . . Вставка значень в окремі поля (використовується якщо інші поля таблиці заповнювати не обов’язково, тобто якщо вони не мають властивості NOT NULL): INSERT INTO назва_таблиці (поле1, поле2, . . .) VALUES (запис1, запис2, . . .)

або INSERT INTO назва_таблиці SET назва_поля1=”значення_поля1”, назва_поля2=”значення_поля2”, . . .

Перегляд даних у таблиці здійснюють інструкцією SELECT * FROM назва_таблиці; Видалення всіх записів з таблиці: DELETE FROM назва_таблиці; Видалення записів які мають певне значення: DELETE FROM назва_таблиці WHERE назва_поля=“значення”; Вставка записів у таблицю customers Відомості про клієнтів містяться у таблиці customers: customerID name address 1 Іван Іваненко 2 Петро Петренко 3 Сидор Сидоренко

м. Полтава, вул. Калініна 1 кв. 1 м. Полтава, вул. Фрунзе 2, кв. 2 м. Полтава, вул. Жовтнева 3, кв. 3

phone 1-23-45 2-34-56 3-45-67

 Вставьте у таблицю customers один запис – відомості про клієнта Іваненка. Для цього: 1. Увійдіть у базу даних book_shop. Команда: mysql> use book_shop; 2. Уточніть структуру таблиці і властивості полів. Команда: mysql> describe customers; 3. Вставьте запис про клієнта Іваненка у таблицю. Для цього у моніторі MySQL виконайте команду:

mysql> INSERT INTO customers VALUES (NULL,”Иван Иваненко”,“г. Полтава, ул. Калинина 1 кв. 1”,“1-23-45”); 4. Перегляньте вміст таблиці customers. Команда: mysql> SELECT * FROM customers; Відповідь сервера: +------------+---------------+----------------------------------+---------+ | CustomerID | Name | Address | Phone | +------------+---------------+----------------------------------+---------+ | 1 | Иван Иваненко | г. Полтава, ул. Калинина 1 кв. 1 | 1-23-45 | +------------+---------------+----------------------------------+---------+

1 row in set (0.11 sec)

 Додайте у таблицю customers ще два записи – відомості про клієнтів Петренка та Сидоренка. Для цього: 1. У моніторі MySQL виконайте команду: mysql> INSERT INTO customers VALUES (NULL,”Петро Петренко”,“г. Полтава, ул. Фрунзе 2 кв. 2”,“2-34-56”), (NULL,” Сидор Сидоренко ”,“г. Полтава, ул. Жовтнева 3 кв. 3”,“3-45-67”); Відповідь сервера має бути такою: Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 2. Перегляньте вміст таблиці customers. Команда: mysql> select * from customers;

Відповідь сервера: +------------+-----------------+------------------------------------+---------+ | CustomerID | Name

| Address

| Phone

+------------+-----------------+------------------------------------+---------+ |

1 | Иван Иваненко

| г. Полтава, вул. Калинина 1 кв. 1

| 1-23-45 |

2 | Петро Петренко

| г. Полтава, вул. Фрунзе 2, кв. 2

| 2-34-45 |

3 | Сидор Сидоренко | г. Полтава, вул. Жовтнева 3, кв. 3 | 3-45-67 |

+------------+-----------------+------------------------------------+---------+

3 rows in set (0.00 sec)  Завдання для самостійного виконання. Додайте у таблицю customers ще 7 (сім) нових записів на власний розсуд. Перегляньте вміст таблиці customers, вона повинна містити 10 записів. Вставка записів у таблицю books  Вставьте у таблицю books такі записи: books ISBN author title 966-7393-98-4 5-8459-0291-6 966-539-320-0

Томсон Лаура, Веллінг Дюк Аткинсон Леон

year

price

2001

60.00

2002

42.00

2003

60.00

Для цього: 1. Уточніть структуру таблиці books і властивості її полів. Команда: mysql> describe books; 2. Вставьте записи у таблицю. У моніторі MySQL виконайте команду: mysql> INSERT INTO books VALUES (“966-7393-98-4”,”Томсон Лаура, Веллінг Дюк”,“Разработка Web-приложений на PHP и MySQL”,“2001”,60.00), (“5-8459-0291-6”,”Аткинсон Леон”,“MySQL. Библиотека профессионала”,“2002”,42.00),

(“966-539-320-0”,”Каратыгин С.А., Тихонов А.Ф., Тихонова Л.Н.”,“ Visual FoxPro 7. Руководство пользователя с примерами ”,“2003”,60.00) Відповідь сервера: Query OK, 3 rows affected (0.11 sec) Records: 3

Duplicates: 0

Warnings: 1

3. Перегляньте вміст таблиці books. Команда: mysql> select * from books;  Завдання для самостійного виконання. Додайте у таблицю books ще 2 (два) нових записи на власний розсуд. Перегляньте вміст таблиці books, вона повинна містити 5 записів. Вставка записів у таблицю book_reviews  Вставьте у таблицю book_reviews такі записи:

Пользуясь методическим пособием, Вы получите ответы на все свои вопросы. Приобретайте наше методическое пособие – лучший помощник и консультант! МЕТОДИЧКА http://metodichka.com.ua/

Заняття 7. Виборка даних з кількох таблиць (складні запити на виборку) Якщо виникає необхідність одержати дані з кількох таблиць одночасно, то для цього виконується операція, яка називається об’єднанням таблиць. Фактично, об’єднання таблиць – це виборка даних з кількох таблиць одночасно. Для об’єднання таблиць створюється складний запит, де після інструкції SELECT вказують перелік полів у формі назва_таблиці.назва_поля, після команди FROM йде перелік таблиць, що об’єднуються, а після WHERE – умова об’єднання таблиць у вигляді назва_таблиці.назва_поля=”значення”. Тобто загальний вигляд простої інструкції на виборку даних з кількох таблиць такий:

SELECT таблиця_1.потрібне_поле, таблиця_2.потрібне_поле, …

FROM таблиця_1, таблиця_2, … WHERE умова_об’єднання_таблиць; Зауваження. Створення і використання умов об'єднання таблиць подібне до використання кількох умов відбору записів при перегляді таблиць. Це питання докладно розглядалося на занятті 5. У складних інструкціях на виборку даних з кількох таблиць можна також групувати записи, задавати критерії пошуку записів, порядок сортування та кількість записів. Для цього до інструкції необхідно додатково включити наступні команди: GROUP BY порядок_групування_записів

HAVING критерії_пошуку_записів ORDER BY порядок_сортування_записів LIMIT критерії_обмеження_кількості_записів; Просте об’єднання двох таблиць Безпосередньо об’єднувати можна лише ті таблиці, між якими існує прямий зв’язок (тобто ті, що мають однойменні ключові поля). Наприклад, такі: customers customerID name address

orders 1 orderID customerID ∞ amount date

 Ознайомтеся з наступним прикладом. Приклад: необхідно одержати повні відомості про замовлення клієнта Іваненка. Відповідна інструкція на запит матиме наступний вид: mysql> select customers.name, orders.* from customers, orders where customers.name="Иван Иваненко" and customers.customerID=orders.customerID; Ця інструкція вибирає відомості з двох таблиць – customers та orders. Причому з таблиці customers вибирається тільки ім’я користувача (поле name), це записується у вигляді customers.name, а з таблиці orders вибираються всі поля, відповідний запис – orders.*. (Примітка. Якщо немає потреби виводити на монітор всі поля таблиці, то у переліку полів треба вказати кожне поле окремо).

Умова об’єднання таблиць має вид customers.name="Иван Иваненко" and customers.customerID=orders.customerID Вона складається з двох умов, які повинні виконуватися одночасно. Ця умова об’єднання означає, що з обох таблиць відбираються лише записи, які стосуються клієнта Іваненка, а саме: спочатку з першої таблиці customers відбираються записи щодо Іваненка (умова customers.name="Иван Иваненко"), а потім з таблиці orders відбираються лише ті записи, у яких ідентифікатор customerID співпадає з customerID записів, вже відібраних з таблиці customers.  Виконайте наведений вище запит на одержання повних відомостей про замовлення клієнта Іваненка. Відповідь сервера: +---------------+---------+------------+--------+------------+ | name | OrderID | CustomerID | Amount | Date | +---------------+---------+------------+--------+------------+ | Иван Иваненко | 2 | 1 | 144.00 | 2003-09-10 | +---------------+---------+------------+--------+------------+ 1 row in set (0.05 sec)

 Якщо у запиті немає необхідності виводити всі поля на екран монітору, то зайве поле не треба включати у перелік полів після інструкції SELECT. Виконайте наступний запит і переконайтеся, що у цьому запиті сustomerID на монітор не виводиться. mysql> select customers.name, orders.orderID, orders.amount, orders.date from customers, orders where customers.name like ("%Иван%")and customers.customerID=orders.customerID; Результат запиту: +-------------------+---------+--------+-------------+ | name

| orderID | amount | date

+-------------------+---------+---------+------------+ | Иван Иваненко

1 |

60.00 | 2003-09-01|

+--------------------+---------+--------+------------+ 1 row in set (0.00 sec)

 Самостійно. 1. Створіть і виконайте запит: Список всіх замовлень клієнта Петренка. 2. Створіть і виконайте запит: Повний список книг з назвами та анотаціями. Вказівки. Цей запит повинен об’єднувати дані з двох таблиць – books та book_reviews. З таблиці books виберіть поле books.title, а з таблиці book_reviews – поле book_reviews.review. Умова об’єднання таблиць – books.ISBN=book_reviews.ISBN Отже, запит має бути таким

select books.title, book_reviews.review from books, book_reviews where books.ISBN=book_reviews.ISBN; 3. Створіть і виконайте запит: Список авторів, книги яких були замовлені лише по одному екземпляру. Вказівки. Запит повинен об’єднувати дані з таблиць books та order_items. З таблиці books необхідно вибрати поле books.author, а з таблиці book_reviews – поле order_items.quantity Умова об’єднання таблиць: books.ISBN=order_items.ISBN and order_items.quantity=1 Отже, запит має бути таким select books.author, order_items.quantity from books, order_items

where books.ISBN=order_items.ISBN and order_items.quantity=1;

4. Створіть і виконайте запит: Перелік дат, коли клієнт на прізвище “Петренко” замовляв книги. Об’єднання трьох і більше таблиць Об’єднання трьох і більше таблиць виконують у двох випадках:  коли необхідно вибрати дані одночасно з двох таблиць, які не мають безпосереднього прямого зв'язку через однойменні ключові поля, але вони зв’язані між собою через одну або декілька інших таблиць;  коли необхідно вибрати дані з трьох і більше таблиць одночасно. Об’єднання трьох і більше таблиць виконується аналогічно простому об’єднанню двох таблиць. Головне правило: таблиці треба об’єднувати попарно через однойменні ключові поля, враховуючи умови об’єднання.  Приклад. Нехай необхідно одержати список клієнтів, які замовляли книгу по FoxPro.

1. Зверніться до схеми зв’язків між таблицями. Згідно цієї схеми, для побудови такого запиту необхідно об’єднати дані з таблиць customers та books. З таблиці customers необхідно буде вибрати поле customers.name, яке містить імена клієнтів, а з таблиці books – поле books.title, яке містить назви книг. Отже, запит має починатися такою інструкцією: select customers.name, books.title . . . Зауваження. Поле books.title не обов’язково виводити на монітор. Тому початок запиту може бути таким: select customers.name . . . 2. Зі схеми зв’язків можна побачити, що таблиці customers та books не можна об’єднати безпосередньо тому, що між ними не має прямого зв’язку – вони зв’язані між собою через таблиці orders та order_items. Тобто, для побудови запиту необхідно виконати об’єднання чотирьох таблиць, а саме: customers, orders, order_items та books. Продовження інструкції запиту: . . . from customers, orders, order_items, books . . . 3. Таблиці об’єднуються попарно через однойменні ключові поля. Крім того, з останньої таблиці books необхідно вибрати лише ті книги, у назві яких міститься слово “FoxPro”. Тому інструкція на запит завершується такою умовою об’єднання: . . . where customers.customerID=orders.customerID and orders.orderID=order_items.orderID and order_items.ISBN=books.ISBN and books.title like “%FoxPro%”; 4. Повний текст інструкції на запит: select customers.name from customers, orders, order_items, books where customers.customerID=orders.customerID and orders.orderID=order_items.orderID

and order_items.ISBN=books.ISBN and books.title like “%FoxPro%”; Ця інструкція вибирає з бази даних імена лише тих клієнтів, хто замовляв книгу по FoxPro.  Виконайте інструкцію на запит, створену у попередньому прикладі. Результат запиту має бути таким: +-----------------+ | name | +-----------------+ | Иван Иваненко | | Сидор Сидоренко | +-----------------+ 2 rows in set (0.22 sec)  Ознайомтеся з інструкцією і виконайте запит: Список книг, які замовляв клієнт Иван Иваненко. Інструкція на запит: select books.title from customers, orders, order_items, books where customers.customerID=orders.customerID and orders.orderID=order_items.orderID and order_items.ISBN=books.ISBN and customers.name="Иван Иваненко"; Результат запиту:

+------------------------------------------------------+ | title +------------------------------------------------------+ | MySQL. Библиотека профессионала | Visual FoxPro 7. Руководство пользователя с примерами +------------------------------------------------------+ 2 rows in set (0.00 sec)  Самостійно. Створіть і виконайте запити: 1. Список імен та адрес всіх клієнтів, які замовляли книгу по MySQL. 2. Список всіх книг, які замовляв клієнт на прізвище “Сидоренко”. 3. Перелік дат, коли клієнт на прізвище “Петренко” замовляв книги по PHP. 4. Відомості про ті замовлення клієнта Сидоренка, коли сума замовлення первищувала 100. 5. Відомості про всі замовлення клієнта Сидоренка, з указанням кількості замовлених екземплярів, ціни замовлених книг та загальної суми замовлення.

Об’єднання таблиць за остачею У наведених вище прикладах з таблиць відбиралися лише ті записи, для яких існують відповідні записи в інших таблицях. Але, на практиці може виникнути необхідність знайти й ті записи, для яких в інших таблицях немає відповідних записів – наприклад, необхідно знайти клієнтів, які не зробили жодного замовлення, або товари (книги), які ніхто не замовляв. У таких випадках для побудови запитів застосовують об’єднання таблиць за остачею. Нехай необхідно одержати перелік всіх записів, що містяться у полі поле_А таблиці таблиця_1, для яких у полі поле_В таблиці таблиця_2 немає відповідних записів. Для цього необхідно виконати об’єднання таблиць 1 і 2 за остачею. Відповідний запит на об’єднання таблиць за остачею має вид: SELECT таблиця_1.поле_А, таблиця_2.поле_В FROM таблиця_1 LEFT JOIN таблиця_2 ON таблиця_1.ID = таблиця_2.ID;

Цей запит SQL виконує об’єднання за остачею для двох таблиць – це таблиця_1 та таблиця_2. Він дозволяє переглянути записи у полі таблиця_1.поле_А та відповідні їм записи у полі таблиця_2.поле_В, для яких виконується умова об’єднання: таблиця_1.ID = таблиця_2.ID (тобто значення поля таблиця_1.ID співпадає із значенням у полі таблиця_2.ID). Якщо у полі таблиця_2.поле_В немає відповідного запису, то замість нього у результат запиту додається значення NULL.  Щоб випробувати запит на об’єднання з остачею додайте у таблицю customers двох нових клієнтів з такими даними: Михайло Михайленко, м. Полтава вул. Половка 12 кв. 12, тел. 56-34-12 Федор Федоренко, м. Лубни вул. Перемоги 10 кв. 1, тел. (063) 5-66-77  Визначіть, хто з клієнтів не зробив жодного замовлення. Для цього виконайте відповідний запит на об’єднання з остачею двох таблиць customers та orders. Інструкція на запит: mysql> SELECT customers.name, orders.orderID -> FROM customers LEFT JOIN orders -> ON customers.customerID=orders.customerID;

Результат запиту: +--------------------+---------+ | name

| orderID |

+--------------------+---------+ | Иван Иваненко

2 |

| Петро Петренко

3 |

| Сидор Сидоренко

1 |

| Сидор Сидоренко

4 |

| Михайло Михайленко |

NULL |

| Федор Федоренко

NULL |

+--------------------+---------+ 6 rows in set (0.11 sec)

Результат запиту показує, що клієнтам Михайло Михайленко та Федор Федоренко не відповідає жоден номер замовлення orderID, оскільки відповідні їм orderID мають значення NULL. Тобто вони не зробили жодного замовлення.  Виведіть список тільки тих клієнтів, хто не зробив жодного замовлення. Для цього у попередній запит необхідно додати інструкцію WHERE orders.orderID is NULL. Ця інструкція використовує умову відбору, яка дозволяє з усього списку клієнтів, створеного у попередньому запиті вибрати лише тих, які мають значення orders.orderID рівне NULL (тобто тих, хто не зробив жодного замовлення). Отже, інструкція на запит має бути такою: mysql> select customers.name -> from customers left join orders -> on customers.customerID = orders.customerID -> where orders.orderID is NULL; Результат запиту: +--------------------+ | name

+--------------------+ | Михайло Михайленко | | Федор Федоренко

+--------------------+

2 rows in set (0.00 sec)

 Самостійно. Створіть і виконайте запити: 1. Список імен та телефонів всіх клієнтів, які не замовили жодної книги. 2. Список всіх книг, які не замовив жоден клієнт. Ітоги 7. Що ми узнали про виборку даних з кількох таблиць 1. Виборку даних з кількох таблиць одночасно називають об’єднанням таблиць. 2. Для об’єднання таблиць створюється складний запит, де після інструкції SELECT вказують перелік полів у формі назва_таблиці.назва_поля, після команди FROM йде перелік таблиць, що об’єднуються, а після WHERE – умова об’єднання таблиць у вигляді назва_таблиці.назва_поля=”значення”. 3. Проста інструкція на вибірку даних з кількох таблиць має такий вид SELECT перелік_полів

FROM перелік_таблиць WHERE умова_об’єднання_таблиць; 4. Складна інструкція на вибірку даних з кількох таблиць може містити команди групування записів, критерії пошуку записів, порядок сортування та обмеження кількість записів. Її повний вигляд: SELECT перелік_полів

FROM перелік_таблиць WHERE умова_об’єднання_таблиць GROUP BY порядок_групування_записів HAVING критерії_пошуку_записів ORDER BY порядок_сортування_записів LIMIT критерії_обмеження_кількості_записів; 5. Безпосередньо можна об’єднувати лише ті таблиці, між якими існує прямий зв’язок (тобто ті, що мають однойменні ключові поля). 6. Об’єднання трьох і більше таблиць виконується аналогічно простому об’єднанню двох таблиць, але таблиці у запиті об’єднуються попарно через однойменні ключові поля з урахуванням умов об’єднання. 7. Для пошуку у таблицях бази даних тих записів, які не зв’язані з жодним записом в іншій таблиці, застосовують об’єднання таблиць за остачею. Приклад запиту на об’єднання таблиць за остачею: SELECT таблиця_1.поле_А, таблиця_2.поле_В FROM таблиця_1 LEFT JOIN таблиця_2 ON таблиця_1.ID = таблиця_2.ID WHERE умова_відбору_записів;

Ваш надёжный помощник и учитель http://metodichka.com.ua/ МЕТОДИЧКА – Электронный магазин учебно-методической литературы