Материал: Лабораторна робота6

Лабораторна робота 6

Тема: Робота з таблицями. Внесення, витягання, пошук і видалення даних.

Мета: розглянути команди внесення даних, їх модифікації та видалення. Набути практичних навичок маніпуляції з даними бази.

Теоретична частина

Запис даних в таблиці

Оператор INSERT заповнює таблицю даними. Ось загальна форма INSERT.

INSERT into table_name (column1, column2 ...)

values (value1, value2...);

де table_name є ім'ям таблиці, в яку треба внести дані; column1, column2 і т.д. є іменами стовпців, а value1, value2 і т.д. є значеннями для відповідних стовпців.

Наступний оператор вносить перший запис в таблицю employee_data, яку ми розглядали в роботі №4.

INSERT INTO employee_data

(f_name, l_name, title, age, уоs, salary, реrks, email)

values

("Михайло", "Петров", "директор", 28, 4, 200000, 50000, "misha@yandex.ru");

Як і інші оператори MySQL, цю команду можна вводити на одному рядку або розмістити її на декількох рядках. Важливо пам’ятати наступне – значення символьних полів необхідно брати в подвійні лапки, а кожну з команд закінчувати крапкою з комою.

Декілька важливих моментів:

Значеннями для стовпців f_name, l_name, title і email є текстові рядки, і вони записуються в лапках.

Значеннями для age, уоs, salary і реrks є числа (цілі), і вони не мають лапок.

Можна бачити, що дані задані для всіх стовпців окрім emp_id. Значення для цього стовпця задає система MySQL, яка знаходить в стовпці найбільше значення, збільшує його на одиницю, і вставляє нове значення.

Якщо приведена вище команда правильно введена в запрошенні клієнта mysql, то програма виведе повідомлення про успішне виконання, як показано на мал. 6.1.

Мал. 6.1. Введення даних в таблицю.

Створення додаткових записів вимагає використовування окремих операторів INSERT. Щоб полегшити цю роботу можна помістити всі оператори INSERT у файл. Це повинен бути звичайний текстовий файл з оператором INSERT в кожному рядку.

Заповнення таблиці employee_data даними за допомогою файлу employee.dat

1) Помістіть файл в каталог \mysql\bin.

2) Перевірте, що MySQL працює.

3) Виконайте команду

mysql employees <employee.dat

Таблиця містить тепер 21 запис (20 з файлу employee.dat і один, вставлений оператором INSERT на початку заняття).

Обрання даних з таблиці MySQL

Таблиця employee_data містить тепер достатньо даних, щоб можна було почати з нею працювати. Запит на вибірку даних виконується за допомогою команди MySQL SELECT. Оператор SELECT має наступний формат:

SELECT імена_колонок from ім'я_таблиці [WHERE ...умови];

Частина оператора з умовами є необов'язковою (ми розглянемо її пізніше). По суті, вимагається знати імена стовпців і ім'я таблиці, з якої витягуються дані.

Наприклад, щоб витягнути імена і прізвища всіх співробітників, виконайте наступну команду.

SELECT f_name, l_name from employee_data;

Оператор MySQL виводить всі дані із стовпців f_name і l_name. Результат роботи оператора представлений на мал. 6.2.

Мал. 6.2. Виведення даних з таблиці

При найближчому розгляді можна помітити, що дані представлені в тому порядку, в якому вони були введені. Більш того, останній рядок указує число рядків в таблиці - 21.

Щоб вивести всю таблицю, можна або ввести імена всіх стовпців, або скористатися спрощеною формою оператора SELECT.

SELECT * from employee_data;

Символ * в цьому виразі означає 'ВСІ колонки'. Тому цей оператор виводить всі рядки всіх стовпців.

Розглянемо ще один приклад.

SELECT f_name, l_name, age from employee_data;

Вибірка стовпців f_name, l_name і age представлена на мал. 6.3.

Мал. 6.3. Вибірка стовпців f_name, l_name і age

Завдання

1. Напишіть оператор SQL для створення нової бази даних з ім'ям addressbook

2. Який оператор використовується для отримання інформації про таблицю? Як використовується цей оператор?

3. Як одержати список всіх баз даних, доступних в системі?

4. Напишіть оператор для запису наступних даних в таблицю employee_data

Ім'я: Рудольф

Прізвище: Курочкин

Посада: Програміст

Вік: 34

Стаж роботи в компанії: 2

Зарплата: 95000

Надбавки: 17000

email: rudolf@yandex.ru

5. Приведіть дві форми оператора SELECT, які виводитимуть всі дані з таблиці employee_data.

6. Як витягнути дані стовпців f_name, email з таблиці employee_data?

7. Напишіть оператор для виведення даних із стовпців salary, реrks і уоs таблиці employee_data.

8. Як дізнатися число рядків в таблиці за допомогою оператора SELECT?

9. Як витягнути дані стовпців salary, l_name з таблиці employee_data?

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

Примітка: Оператор DESCRIBE дозволяє отримати структуру таблиці.

Вибірка даних за допомогою умов

Тепер більш детально розглянемо формат оператора SELECT. Його повний формат має вигляд:

SELECT імена_колонок from ім'я_таблиці [WHERE ...умови];

В операторі SELECT умови є необов'язковими.

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

Тепер перейдемо до розгляду операторів порівняння.

Оператори порівняння = і !=

SELECT f_name, l_name from employee_data where f_name = 'Иван';

Результат запиту приведений на мал. 6.4.

Мал. 6.4. Вибірка стовпців з умовою для поля "ім'я".

Цей оператор виводить імена і прізвища всіх співробітників, які мають ім'я Іван. Відзначимо, що слово Іван в умові укладено в одиночні лапки. Можна використовувати також подвійні лапки. Лапки є обов'язковими, оскільки MySQL породжуватиме помилку при їх відсутності. Окрім того порівняння MySQL не розрізняють регістр символів, що означає, що з рівним успіхом можна використовувати "Иван", "иван" і навіть "ИвАн".

SELECT f_name,l_name from employee_data where title="программист";

Результат запиту приведений на мал. 6.5.

Мал. 6.5. Вибірка стовпців з умовою для поля "посада"

Вибирає імена і прізвища всіх співробітників, які є програмістами.

SELECT f_name, l_name from employee_data where age = 32;

Результат запиту приведений на мал. 6.6.

Мал. 6.6. Вибірка стовпців з умовою для поля "вік"

Це список імен і прізвищ всіх співробітників з віком 32 року. Пригадайте, що тип стовпця age був заданий як int, тому лапки навкруги 32 не потрібні. Це - незначна відмінність між текстовим і цілочисельним типами стовпців.

Оператор != означає 'не дорівнює і є протилежним оператору рівності.

Оператори більше і менше

Давайте одержимо імена і прізвища всіх співробітників, які старше за 32 роки.

SELECT f_name, l_name from employee_data where age > 32;

Результат запиту приведений на мал. 6.7.

Мал. 6.7. Вибірка стовпців з умовою "більше" для поля "вік"

Спробуємо знайти співробітників, які одержують зарплату більше 120000.

SELECT f_name, l_name from employee_data where salary > 120000;

Результат запиту приведений на мал. 6.8.

Мал. 6.8. Вибірка стовпців з умовою "більше" для поля "зарплата"

Тепер перерахуємо всіх співробітників, які мають стаж роботи в компанії менше 3 років.

SELECT f_name, l_name from employee_data where уоs < 3;

Результат запиту приведений на мал. 6.9.

Мал. 6.9. Вибірка стовпців з умовою "менше" для поля "стаж"

Оператори <= і >=

Оператори, що використовуються в основному з цілочисельними даними, менше або рівно ( <= ) і більше або рівно ( >= ) забезпечують додаткові можливості.

select f_name, l_name, age, salary

from employee_data where age >= 32;

Результат запиту приведений на мал. 6.10.

Мал. 6.10. Вибірка стовпців з умовою "більше або рівно" для поля "вік"

Вибірка містить імена, вік і зарплати співробітників, яким більше 32 років.

select f_name, l_name from employee_data where уоs <= 2;

Результат запиту приведений на мал. 6.11.

Мал. 6.11. Вибірка стовпців з умовою "менше або рівно" для поля "стаж"

Запит виводить імена співробітників, які працюють в компанії не більше 2 років.

Завдання

1. Напишіть оператор SELECT для витягання ідентифікаційного номера співробітників, які старше 30 років.

2. Напишіть оператор SELECT для витягання імен і прізвищ всіх Web-розробників.

3. Що виведе наступний оператор SELECT:

SELECT * from employee_data where salary <=100000;

4. Як вивести зарплати і надбавки співробітників, які одержують як надбавки більше 16000?

5. Перерахуйте імена всіх співробітників (прізвище, а потім ім'я), які посідають посаду бухгалтера.

Пошук текстових даних за шаблоном

В даній частині ми розглянемо пошук текстових даних за шаблоном за допомогою пропозиції where і оператора LIKE.

Оператор порівняння на рівність ( = ) допомагає вибрати однакові рядки. Так, щоб перерахувати імена співробітників, яких звуть Іван, можна скористатися наступним оператором SELECT.

select f_name, l_name from employee_data where f_name = "Иван";

Результат запиту приведений на мал. 6.12.

Мал. 6.12. Результат пошуку співробітників, яких звуть Іван

Як бути, якщо треба вивести дані про співробітників, ім'я яких починається з букви В? Мова SQL дозволяє виконати пошук рядкових даних за шаблоном. Для цього в пропозиції where використовується оператор LIKE таким чином.

select f_name, l_name from employee_data where f_name LIKE "В%";

Результат запиту приведений на мал. 6.13.

Мал. 6.13. Результат пошуку співробітників, ім'я яких починається з букви В

Можна бачити, що тут в умові замість знака рівності використовується LIKE і знак відсотка в шаблоні.

Знак % діє як символ-заступник (аналогічно використовуванню * в системах DOS і Linux). Він замінює собою будь-яку послідовність символів. Таким чином "В%" позначає всі рядки, які починаються з букви В. Аналогично "%В" вибирає рядки, які закінчуються символом В, а "%В%" рядки, які містять букву В.

Давайте виведемо, наприклад, всіх співробітників, які мають в назві посади рядок "про".

select f_name, l_name, title from employee_data

where title like '%про%';

Результат запиту приведений на мал. 6.14.

Мал. 6.14. Результат пошуку співробітників, в назві посади яких міститься рядок "про"

Перерахуємо всіх співробітників, імена яких закінчуються буквою 'а'. Це дуже просто зробити.

mysql> select f_name, l_name from employee_data

where f_name like '%a';

Результат запиту приведений на мал. 6.15.

Мал. 6.15. Результат пошуку співробітників, імена яких закінчуються буквою 'а'

Завдання

1. Перерахувати всіх співробітників, прізвища яких починаються з букви P.

2. Вивести імена всіх співробітників у відділі продажів.

3. Що виведе наступний оператор

SELECT f_name, l_name, salary from

employee_data where f_name like '%к%';

4. Перерахувати прізвища і посади всіх програмістів

Пропозиція HAVING

Щоб вивести середню зарплату співробітників в різних підрозділах (посадах), використовується пропозиція GROUP, наприклад:

select title, AVG(salary)

from employee_data

GROUP title;

Результат запиту приведений на мал. 6.16.

Мал. 6.16. Виведення середньої зарплати співробітників по підрозділах

Припустимо тепер, що вимагається вивести тільки ті підрозділи, де середня зарплата більше 100000. Це можна зробити за допомогою пропозиції HAVING.

select title, AVG(salary)

from employee_data

GROUP title

HAVING AVG(salary)> 100000;

Результат запиту приведений на мал. 6.17.

Мал. 6.17. Виведення середньої зарплати певного діапазону по підрозділах

Завдання

Вивести підрозділи і середній вік, де середній вік більше 30.

Видалення записів з таблиці

Для видалення записів з таблиці можна використовувати оператор DELETE.

Оператор видалення DELETE вимагає завдання імені таблиці і необов'язкових умов.

DELETE from ім'я_таблиці [WHERE умови];

Примітка: Якщо ніякі умови не будуть задані, то віддаляються всі дані в таблиці.

Припустимо, один з фахівців по мультимедіа 'Василий Пупкин' звільнився з компанії. Треба видалити його запис.

DELETE from employee_data

WHERE l_name = 'Пупкин';

Результат запиту приведений на мал. 6.18.

Мал. 6.18. Результат видалення запису з таблиці

Контрольні питання

1. Яким чином можна додати дані до таблиці? Опишіть додавання даних двома способами.

2. В чому полягає додавання символьних даних? Числових?

3. Як можна обрати дані з таблиці?

4. В чому особливість використання символу * в запитах вибірки?

5. Як додається умова в запит вибірки? В чому особливість умовних запитів на вибірку?

6. Опишіть використання предикатів дорівнює та не дорівнює.

7. Опишіть використання предикатів більше та менше.

8. Опишіть використання предикатів більше або дорівнює та менше або дорівнює.

9. Що таке шаблон пошуку (вибірки)? Як він виглядає?

10. В чому особливість пошуку за шаблоном? Які шаблони можна використати?