Елементарні методи керування базами даних за допомогою запитів
Елементарні методи керування базами даних за допомогою запитів
СУБД має гнучкий метод іменації тобто якщо ви забажаєте здійснити доступ до поля «поле1» в таблиці «таблиця1» які знаходяться в базі «база1», то ім’я буде мати вигляд «база1.таблиця1.поле1». Але якщо ці імена є унікальними (неповторюваними) тоді можна не використовувати ім’я таблиці та бази. Але в подальшому для полегшення логічної структури ми будемо вживати розіменування виду «таблиця.поле». Чому ми не будемо посилатися на БД? Посилатися на БД ми не будемо, тому, що посилання на БД ми можемо здійснити тільки якщо користувач під яким ми працюємо має право «продивлятись» всі створені і наявні в системі БД. Зазвичай це адміністратор. Для роботи додатка ж таких повноважень забагато, так як один сервер БД може оперувати не однією БД. А тому простий користувач матиме доступ до суміжних БД, що є недопустимим так як суттєво зменшується безпека даних.
Ми ж будемо вивчати СУБД з метою написання АРМ (Автоматичне Робоче Місце), тобто ми абстрагуємося від питань створення і адміністрування БД. Цю частину ми перекладаємо на візуальний додаток MySQL-Front, яка за допомогою інтуїтивного інтерфейсу дозволяє створити БД, таблиці в ній, індексні та ключові поля. Та на MyODBC Driver який звільняє нас від вибору база та від процедури авторизації користувача. Тобто ми будемо акцентувати увагу на доданні даних в таблиці, вилучення їх, та представлені даних в безпосередньо в зручному для нас вигляді (сортування, фільтрація, зв’язки таблиць…).
Але все-таки в базових поняттях, а саме аким чином відбувається початок роботи до того як нам «видадуть» повний доступ до таблиць певної БД розберемося.
В СУБД MySQL є один головний користувач, по замовченню це root. І саме він має права на редагування властивостей бази на низькому рівні. Іншим користувачам root може надавати різні повноваження, наприклад один може тільки продивлюватись таблицю інший може тільки в неї записувати, третій може робити обидві речі. Інших три можуть робити все те саме тільки з іншою таблицею. І так почали…
Користувачі розмежовуються за двома параметрами, а саме логіком і паролем (так званими реквізитами доступу). Після авторизації користувач мусить вибрати БД на якій бажає працювати, реалізується це так:
USE «ім’я_БД»
Такий запит завершиться вдало тільки якщо користувач має право доступу до БД. Після вдалого виконання запиту виразом БД в записі «БД.Таблиця.поле» можна нехтувати так, як СУБД автоматично її підставлятиме.
Якщо це адміністратор тоді йому потрібно створити БД, робиться це так:
CREATE DATABASE «ім’я_БД»
В кінці цього виразу раціонально дописати тип кодування тексту який буде використовуватись в таблицях. Тобто кінцевим запит буде ось таким:
CREATE DATABASE «ім’я_БД» DEFAULT CHARACTER SET cp1251
Cp1251 – в О/С Віндовс інтерпретується я к кирилиця.
Слід пам’ятати, що СУБД є нечутливою до реєстру символів, тому ім’я «слово» і «слОВо» є ідентичними в розумінні СУБД.
Створити таблицю теж не складно, використовується оператор CREATE TABLE. Наприклад:
CREATE TABLE «ім’я_таблиці»
(«ім’я_поля» «тип_поля»
…
«ім’я_поля» «тип_поля»)
Наприклад
CREATE TABLE mytbl
(id INT NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY
name VARCHAR(20),
Age INT NOT NULL,
Weight INT,
INDEX (Age)) TYPE = MYISAM;
Приклад створить таблицю mytbl, де поле id буде головним ключовим полем, яке може бути тільки унікальним, може заповнюватись автоматично, та буде встановлений допоміжний індекс на поле Age. Таблиця матиме тип MYISAM. Графічно таблиця буде представлятися так:
|
id |
name |
Age |
Weight |
|
|
|
|
|
|
… |
… |
… |
… |
|
|
|
|
|
Видалення бази даних матиме вигляд:
DROP DATABASE «ім’я_БД»
Видалення таблиці по аналогії матиме вигляд:
DROP TABLE «ім’я_таблиці»
Переіменування таблиці:
ALTER TABLE «старе_ім’я» RENAME AS «нове_ім’я»
Переназве таблицю «старе_ім’я» на «нове_ім’я», за умови, що «нове_ім’я» є вільним.
Зміна типу поля може відбутися таким чином:
ALTER TABLE «ім’я_таблиці» MODIFY «ім’я_поля» «новий_тип»
ALTER TABLE t1 MODIFY name CHAR(15)
Приклад змінить в таблиці t1 поле name в тип CHAR(15). СУБД підтримує широкий ряд перетворень типів данних наприклад якщо нова довжина типу дозволяє то СУБД може перетворити тип дат в текст, чи навіть в число, але якщо довжини нового типу виявиться замало, то завершення запиту буде помилковим, або ми отримаємо на виході пустий стовпець.
Оператор вибору даних SELECT.
SELECT — оператор мови SQL, котрий повертає рядки з однієї чи багатьох таблиць. Повний синтаксис оператора SELECT є складним, проте його можна описати наступним чином:
SELECT [Straight_join] [DISTINCT | ALL] select_expression...
[FROM tables... [WHERE where_definition] [GROUP BY column...]
[ORDER BY column [ASC | DESC] ...] HAVING full_where_definition
[LIMIT [offset] rows] [PROCEDURE procedure_name]]
[INTO OUTFILE 'file_name'... ]
Повертає нуль або більше рядків з однієї або більше таблиць. По кількості застосувань, SELECT — найчастіша команда Data Manipulation Language (DML). Оскільки, SQL не є процедурною мовою, запит SELECT описує кінцеві дані, однак, не вказує, які саме операції слід виконати для отримання цих даних: завдання покладається на систему керування базами даних, яка здатна самостійно оптимізовувати необхідні для отримання результату операції.
Як видно з наведеного вище, разом з командою Select використовуються ключові слова, використання яких дуже впливає на відповідь сервера.Розглянемо кожне з них.
DISTINCT..
Пропускає рядки, в яких всі вибрані поля ідентичні, тобто усуває дублювання даних.
WHERE.
Оператор команди Select, який дозволяє встановлювати предикати, умова яких може бути вірною або невірною для будь-якого рядка таблиці. Витягуються тільки ті рядки, для яких таке твердження вірне. Наприклад:
SELECT u_id,lname from publishers WHERE city ='New York';
Виводить колонки u_id і lname з таблиці publishers для яких значення в стовпці city-new York. Це дає можливість зробити запит конкретнішим.
Реляційні оператори.
Реляційний оператор - математичний символ який вказує на певний тип порівняння між двома значеннями. Реляційні оператори які розуміє MYSQL :
= Рівно
> Більше
< Менше
>= Більше або рівне
<= Менше або рівне
< > Не рівне
Припустимо що ви хочете побачити всіх замовників з оцінкою(rating) вище 200. Оскільки 200 - це числове значення, так як і тип стовпця, для їх порівняння ви можете використовувати реляційного оператора.
SELECT * FROM Customers WHERE rating > 200;
Булеві оператори.
Основні Булеві оператори також розпізнаються в MYSQL. Вирази Буля - є або вірними або невірними, подібно до предикатів. Булеві оператори зв'язують одне або більш правдивих/неправдивих значень і повертають єдине вірне або невірне значення. Стандартними операторами Буля розпізнаваними в SQL є: and,or і NOT.
Припустимо ви хочете отримати всіх замовників в Далласі, які мають рейтинг вище 200:
SELECT * FROM Customers WHERE city = 'Dallas' AND rating > 200;
При використанні оператора And, повинні бути виконані обидві умови, тобто повинні бути вибрані всі замовники з Далласа, рейтинг яких більше 200.
При використанні оператора Or, повинне виконуватися одна з умов. Наприклад:
SELECT * FROM Customers WHERE city = 'Dallas ' OR rating > 200;
В даному випадку будуть вибрані всі замовники з Далласа і ті що мають рейтинг більше 200, навіть якщо вони і не з Далласа.
NOT може використовуватися для інвертування значень Буля. Приклад запиту з NOT:
SELECT * FROM Customers WHERE city = 'Dallas' OR NOT rating > 200;
При такому запиті будуть вибрані всі замовники з Далласа і всі замовники, рейтинг яких менше 200. В цьому запиті оператор NOT застосовується тільки до виразу rating >200. Можна зробити складніший запит:
SELECT * FROM Customers WHERE NOT( city = 'Dallas' OR rating > 200 );
У цьому запиті NOT застосований до обох виразів в дужках. В даному випадку, сервер зчитує вирази в дужках, визначає, чи відповідає істині рівність city = 'Dallas' або рівність rating > 200. Якщо будь-яка умова вірна, вираз Буля всередині дужок теж вірний. Але, якщо вираз Буля усередині дужок вірний, предикат є невірним, тому що NOT перетворить вірність в невірність і навпаки. Тобто, будуть вибрані всі замовники що не знаходяться в Далласі і рейтинг яких менше 200.
IN.
Оператора IN визначає набір значень в яке дане значення може або не може бути включене. Наприклад запит
SELECT * FROM Salespeople WHERE city = 'Barcelona' OR city = 'London';
може бути переписаний простіше:
SELECT * FROM Salespeople WHERE city IN ( 'Barcelona', 'London' );
IN визначає набір значень за допомогою параметрів набору значень в дужках які відокремлені комами. Потім він перевіряє значення стовпця зі вказаними значеннями, намагаючись знайти збіг. Якщо це трапляється, то предикат вірний. Коли набір містить значення номерів а не символів, одинарні лапки не використовуються.
BETWEEN.
Оператор BETWEEN схожий на оператора IN. Відмінність полягає в тому, що IN перераховує всі значення, BETWEEN визначає діапазон, тобто предикат стане вірним тілько тоді, коли значення стовпця, пополо у вказаний діапазон. В запиті вказуємо BETWEEN з початковим значенням, потім AND і кінцеве значення. На відміну від IN, BETWEEN чутливий до порядку, і перше значення в пропозиції повинне бути першим по алфавітному або числовому порядку. Наприклад:
SELECT * FROM Salespeople WHERE comm BETWEEN 10 AND 12;
SELECT * FROM Salespeople WHERE city BETWEEN 'Berlin' AND 'London';
LIKE.
LIKE застосовується тільки до полів типу CHAR або VARCHAR, використовується для знаходження підрядків. Тобто він шукає в символьному полі, умовну частину рядка. Як умову він використовує групові символи (wildkards) - спеціальні символи які можуть заміняти символ, або послідовність символів. Є два типи групових символів які використовуються з LIKE:
символ підкреслення ( _ ) замінює будь-який символ (один).
знак '%', замінює будь-яку послідовність символів.
Якщо ми задамо наступні умови:
SELECT * FROM Customers WHERE fname LIKE 'J%';
то будуть відібрані всі замовники, чиї імена починаються на J:john,jerry,james і
так далі.
COUNT.
Функція яка робить підрахунок кількості записів в стовпці або кількості рядків у таблиці. При роботі із стовпцем використовується аргумент DISTINCT:
SELECT COUNT ( DISTINCT snum ) FROM Orders;
При підрахунку рядків має синтаксис:
SELECT COUNT (*) FROM Customers;
GROUP BY.
Вираз GROUP BY дозволяє виділити підмножини значень в полі і застосовувати функцію СУБД до підмножини. Це дає можливість об'єднувати поля і функції в єдиному виразі SELECT. Наприклад, припустимо що ви хочете знайти найбільшу суму від продажу отриману кожним продавцем. Ви можете зробити окремий запит для кожного з продавців, вибравши MAX () з таблиці для значень поля. GROUP BY дозволить помістити всіх їх в одну команду:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum;
HAVING.
HAVING відфільтровує значення за умовою, відображаються лише ті значення які перетворюють умову в вірну рівність. Наприклад:
SELECT cid,cname,price,max(price)
FROM customers HAVING max(price) >500;
HAVING працює аналогічно до WHERE, але з WHERE не можна використовувати функції СУБД.
ORDER BY.
Ця команда упорядковує виведення значень запиту згідно значенням в тому або іншому вибраному стовпцю. СУБД може впорядковувати по декількох стовпцях одночасно.
EXISTS.
Використовується в підзапитах.
SELECT cnum, cname, city FROM Customers WHERE EXISTS
(SELECT * FROM Customers WHERE city = ’San Jose’);
Він бере підзапит як аргумент і оцінює його як вірний якщо той повертає хоча б одне значення або як невірний якщо не повертає жодного. В прикладі запит відображає номер клієнта, ім’я клієнта, місто, з таблиці клієнтів, але тільки тоді коли в таблиці з клієнтами є хоча б один з міста San Jose.
Union.
Uniоn відрізняється від інших виразів тим, що не керує двума підзапитами (або більше). Всі запити виконуються незалежно один від одного, а вже вивід їх - об'єднується. Наприклад:
SELECT snum, sname FROM Salespeople WHERE city = 'London' Uniоn
SELECT cnum, cname FROM Customers
WHERE city = 'London';
Вираз Uniоn об'єднує вивід двух або більше SQL запитів в єдиний набір рядків і стовпців.
DESC, ASC.
Desc-descedent, вивід даних в зворотньому порядку (за абеткою і чисельним значенням). По замовчанню використовується ASC.
Ну ось стисло і все.