Основні оператори SQL

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

Введення

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


Визначають декілька категорій операторів SQL: визначення об'єктів бази даних; маніпулювання значеннями; захист і управління; параметри сеансу; інформація про базу; статичний SQL; динамічний SQL.

Оператори SQL для маніпулювання даними

До цієї категорії відносяться ключові слова, за допомогою яких можна керувати розміщенням значень в базі. INSERT. Вставляє рядки в існуючу таблицю. Може використовуватися як для одного значення, так і декількох, визначених по якомусь критерію. Наприклад: INSERT INTO ім'я таблиці (ім'я стовпця 1 ім'я стовпець 2) VALUES (значення 1 значення 2). Для використання оператора SQL запиту INSERT при декількох значеннях, застосовується такий синтаксис: INSERT INTO ім'я таблиці 1 (ім'я стовпця 1 ім'я стовпця 2) SELECT ім'я стовпця 1 ім'я стовпця 2 FROM ім'я таблиці 2 WHERE ім'я таблиці 2.ім'я стовпця 1>2 Цей запит вибере всі дані з таблиці 2 які більше 2 по стовпцю 1 і вставити їх в першу. UPDATE. Як видно з назви, цей оператор SQL запиту оновлює дані в існуючої таблиці за певною ознакою. Приклад: UPDATE ім'я таблиці 1 SET ім'я стовпця 2 = «Василь» WHERE ім'я таблиці 1.ім'я стовпця 1 = 1 Дана конструкція заповнить значенням Василь всі рядки, в яких зустріне цифру 1 в першому стовпці. DELETE. Видаляє дані з таблиці. Можна вказати яке-небудь умова або ж прибрати всі рядки. DELETE FROM ім'я таблиці WHERE ім'я таблиці.ім'я стовпця 1 = 1 Наведений запит видалить з бази всі дані зі значенням один у першому стовпці. А ось так можна очистити всю таблицю: DELETE FROM ім'я таблиці. Далі варто розповісти про операторі SELECT. Він є одним з найважливіших, тому йому доведеться присвятити окрему главу.

Оператор SELECT

Головне призначення SELECT — вибірка даних за певним умовам. Результатом його роботи завжди є нова таблиця з відібраними даними. Оператор MS SQL SELECT може бути використаний в масі різних запитів. Тому поряд з ним можна розглянути й інші суміжні ключові слова.


Для вибору всіх даних з певної таблиці використовується знак «*». SELECT * FROM ім'я таблиці 1 Результатом роботи даного запиту буде точна копія таблиці 1. А тут відбувається вибірка по умові WHERE, яке дістає з таблиці 1 всі значення, більше 2 стовпець 1. SELECT * FROM ім'я таблиці 1 WHERE ім'я таблиці 1.ім'я стовпця 1 > 2 Також можна вказати у вибірці, що потрібні тільки певні стовпці. SELECT ім'я таблиці 1.ім'я стовпці 1 FROM ім'я таблиці 1 Результатом даного запиту будуть всі рядки, зі значеннями стовпця 1. З допомогою операторів MS SQL можна скласти власну таблицю, на ходу замінивши, обчисливши і підставивши певні значення. SELECT ім'я таблиці 1.ім'я стовпця 1 ім'я таблиці 1.ім'я стовпця 2 ім'я таблиці 1.ім'я стовпця 3 «=» AS EQ ім'я таблиці 1.ім'я стовпця 2 * ім'я таблиці 1.ім'я стовпця 3 AS SUMMA FROM ім'я таблиці 1 Цей, на перший погляд, складний запит виконує вибірку всіх значень з таблиці 1 потім створює нові колонки EQ і SUMMA. В першу заносить знак «+», у другу твір даних стовпці 2 і 3. Отриманий результат можна подати у вигляді таблиці, для розуміння як це працює:


Стовпець 1



Стовпець 2



Стовпець 3



EQ



SUMMA



Ім'я товару 1



10



50



+



500



Ім'я товару 2



15



100



+



1500

При використанні оператора SELECT, можна відразу провести впорядкування даних за будь-якою ознакою. Для цього використовується слово ORDER BY. SELECT ім'я таблиці 1.ім'я стовпця 1 ім'я таблиці 1.ім'я стовпця 2 ім'я таблиці 1.ім'я стовпця 3 FROM ім'я таблиці 1 ORDER BY ім'я стовпця 2 Результуюча таблиця буде виглядати таким чином:

Стовпець 1



Стовпець 2



Стовпець 3



1



1



54



3



2



12



7



3



100



2



5



1

Тобто всі рядки були встановлені в такому порядку, щоб у стовпці 2 значення йшли за зростанням. Дані можна отримувати і з декількох таблиць. Для наочності спочатку треба уявити, що їх в базі є дві, приблизно такі: Таблиця «Співробітники»

Номер



Ім'я



Прізвище



1



Вася



Васін



2



Петя



Петін

Таблиця «Зарплата»

Номер



Ставка



Нараховано



1



1



10000



2



05



3500

Тепер потрібно, як-то поєднавши ці дві таблиці отримати загальні значення. Використовуючи основні оператори SQL зробити це можна так: SELECT Співробітники.Номер Співробітники.Ім'я Зарплата.Ставка Зарплата.Нараховано FROM Працівники, Зарплата WHERE Співробітники.Номер = Зарплата.Номер Тут відбувається вибірка з двох різних таблиць значень, об'єднаних за номером. Результатом буде наступний набір даних:

Номер



Ім'я



Ставка



Нараховано



1



Вася



1



10000



2



Петя



05



3500

Ще трохи про SELECT. Використання агрегатних функцій

Один з основних операторів SQL SELECT може проводити деякі обчислення при вибірці. Для цього він використовує певні функції і формули.
Приміром, щоб отримати кількість записів з таблиці «Співробітники», потрібно використовувати запит: SELECT COUNT (*) AS N FROM Співробітники В результаті вийде таблиця з одним значенням і стовпцем.

N



2

В запитах можна використовувати функції, які обчислюють суму, максимальні і мінімальні значення, а також середнє. Для цього застосовуються ключові слова SUM, MAX, MIN, AVG. Наприклад, потрібно провести вибірку з уже відомої таблиці «Зарплата»:

Номер



Ставка



Нараховано



1



1



10000



2



05



3500

Можна застосувати такий запит і подивитися що вийде: SELECT SUM(Зарплата.Нараховано) AS SUMMA MAX(Зарплата.Нараховано) AS MAX MIN(Зарплата.Нараховано) AS MIN AVG(Зарплата.Нараховано) AS SRED FROM Зарплата Підсумкова таблиця буде такою:

SUMMA



MAX



MIN



SRED



13500



10000



3500



6750

Ось таким чином, можна вибрати з бази даних потрібні значення, на льоту виконавши обчислення різних функцій. Об'єднання, перетин і різницю Об'єднати декілька запитів в SQL SELECT Співробітники.Ім'я FROM Співробітники WHERE Співробітники.Номер = 1 UNION SELECT Співробітники.Ім'я FROM Працівники, Зарплата WHERE Зарплата.Номер = 1 При цьому варто враховувати, що при такому об'єднанні таблиці повинні бути сумісні. Тобто мати однакову кількість стовпців.

Синтаксис оператора SELECT і порядок його обробки

Першим ділом SELECT визначає область, з якої він буде брати дані. Для цього використовується ключове слово FROM. Якщо не зазначено, що саме вибрати. Потім може бути оператор SQL WHERE. З його допомогою SELECT пробігає по всіх рядках таблиці і перевіряє дані на відповідність умові. Якщо в запиті є GROUP BY, то відбувається групування значень за вказаними параметрами.

Оператори порівняння даних

Їх є кілька типів. У SQL оператори порівняння можуть перевіряти різні типи значень. «=». Позначає, як можна здогадатися, рівність двох виразів. Наприклад, він вже використовувався в прикладах вище - WHERE Зарплата.Номер = 1. «>». Знак більше. Якщо значення лівої частини виразу більше, то повертається логічне TRUE і умова вважається виконаним. « <». Знак меньше. Обратный предыдущему оператор. Знаки « =». Відрізняється від простих операторів більше і менше, тим, що при рівності операндів умова також буде істинним. « <> ». Не одно. Умова буде вважатися TRUE, тільки якщо один операнд не дорівнює іншому. У нього є ще одна інтерпретація - «!=».

LIKE

Перекласти дане ключове слово можна як «схожий». Оператор LIKE SQL використовується приблизно за таким же принципом — виконує запит за шаблоном. Тобто він дозволяє розширити вибірку даних з бази використовуючи регулярні вирази. Наприклад, поставлена така задача: із вже відомої бази «Працівники» отримати всіх людей, чиє ім'я закінчується на «я». Тоді запит можна скласти так: SELECT * FROM Співробітники WHERE Ім'я LIKE `%я` Знак відсотка в даному випадку означає маску, тобто будь-який символ і їх кількість. А по літері «я» SQL визначить що останній символ повинен бути саме таким.

CASE

Даний оператор SQL Server являє собою реалізацію множинного вибору. Він нагадує конструкцію switch у багатьох мовах програмування. Оператор CASE SQL виконує дію за кількома критеріями. Наприклад, потрібно вибрати з таблиці «Зарплата» максимальне і мінімальне значення.

Номер



Ставка



Нараховано



1



1



10000



2



05



3500

Тоді запит можна скласти так: SELECT * FROM Зарплата WHERE CASE WHEN SELECT MAX(Нараховано) THEN Максимум WHEN SELECT MIN(Нараховано) THEN Мінімум END підсумок У цьому контексті система шукає максимальне і мінімальне значення в стовпці «Нараховано». Потім за допомогою END створюється поле «підсумок», яке буде заноситися «Максимум» або «Мінімум» в залежності від результату виконання умови. До речі, в SQL є і більш компактна форма CASE — COALESCE.

Оператори визначення даних

Це вид дозволяє проводити різноманітне зміна таблиць — створення, видалення, модифікації і роботу з індексами. Перший з них, який варто розглянути — CREATE TABLE. Він робить не що інше, як створює таблицю. Якщо просто набрати запит CREATE TABLE, нічого не трапиться, так як потрібно ще вказати кілька параметрів. Наприклад, для створення вже знайомої таблиці «Співробітники» потрібно використовувати команди: CREATE TABLE Співробітники (Номер number(10) NOT NULL Ім'я varchar(50) NOT NULL Прізвище varchar(50) NOT NULL) В цей запиті, у дужках відразу ж визначаються імена полів та їх типи, а також чи може він бути дорівнює NULL.

DROP TABLE

Виконує одну просту задачу — видалення таблиці. Має додатковий параметр IF EXISTS. Він поглинає помилку при видаленні, якщо шукана таблиця не існує. Приклад використання: DROP TABLE Співробітники IF EXISTS.

CREATE INDEX

В SQL є система індексів, яка дозволяє прискорити доступ до даних. Загалом, він являє собою посилання вказує на певний стовпець. Створити індекс можна простим запитом: CREATE INDEX название_индекса ON название_таблицы(название_столбца) Використовується даний оператор T-SQL, Oracle, SQL і PL багатьох інших інтерпретаціях технологіях.

ALTER TABLE

Дуже функціональний оператор, що володіє численними варіантами. У загальному випадку виробляє зміна структури, визначення та розміщення таблиць. Використовується оператор в Oracle SQL, Postgres і багатьох інших. Далі будуть представлені різні варіанти використання ALTER TABLE. ADD. Здійснює додавання стовпця у таблицю. Синтаксис його такий: ALTER TABLE название_таблицы ADD название_столбца тип_хранимых_данных. Може мати параметр IF NOT EXISTS, що придушити помилку, якщо створюваний стовпець вже є; DROP. Видаляє стовпець. Також має ключ IF EXISTS, без якого сгенерируется помилка, що говорить про те, що потрібний стовпець відсутня; CHANGE. Служить для перейменування імені поля у вказане. Приклад використання: ALTER TABLE название_таблицы CHANGE старое_имя новое_имя; MODIFY. Дана команда допоможе змінити тип і додаткові атрибути певного стовпця. А використовується він ось так: ALTER TABLE название_таблицы MODIFY название_столбца тип_данных атрибути;

CREATE VIEW

В SQL є таке поняття, як подання. Коротенько, це якась віртуальна таблиця з даними. Утворюється вона в результаті вибірки з допомогою мови SQL оператора SELECT. Подання можуть обмежувати доступ до бази даних, приховувати їх, заміняти реальні імена стовпців. Процес створення відбувається з допомогою простого запиту: CREATE VIEW назва подання AS SELECT FROM * назва таблиці Вибірка може відбуватися як всієї бази цілком, так і за певною умовою.

Трохи про функції

В SQL-запитах дуже часто використовуються різні вбудовані функції, які дозволяють взаємодіяти з даними і перетворювати їх на льоту. Варто розглянути їх, так як вони складають невід'ємну частину структурованого мови. COUNT. Виробляє підрахунок записів або рядків в конкретній таблиці. В якості параметра можна вказати ім'я стовпця, тоді дані будуть взяті з нього. SELECT COUNT * FROM Співробітники; AVG. Дана функція застосовується тільки на стовпці з числовими даними. Її результатом є визначення середнього арифметичного всіх значень; MIN і MAX. Ці функції вже використовувалися в цій статті. Вони визначають максимальне і мінімальне значення вказаного стовпця; SUM. Все просто — функція обчислює суму значень стовпця. Застосовується виключно для числового виду даних. Додавши в запит параметр DISTINCT, будуть додаватися тільки унікальні значення; ROUND. Функція округлення десяткових дробових чисел. У синтаксисі використовується назва стовпця і кількість знаків після коми; LEN. Проста функція, вычисляющая довжину значень стовпця. Результатом буде нова таблиця з зазначенням кількості символів; NOW. Це ключове слово використовується для обчислення поточної дати і часу.

Додаткові оператори

Багато приклади з операторами SQL мають ключові слова, які виконують невеликі завдання, але тим не менш сильно спрощують вибір або дії з базами даних. AS. Застосовується, коли потрібно візуально оформити результат, привласнюючи вказане ім'я отриманої таблиці. BETWEEN. Дуже зручний інструмент для вибірки. Він визначає область значень, серед яких потрібно отримати дані. На вхід приймає параметр від і до якого числа використовується діапазон;. NOT. Оператор надає протилежність висловом. TRUNCATE. Видаляє дані з вказаного ділянки бази. Відрізняється від аналогічних операторів тим, що відновити дані після його використання неможливо. Варто врахувати, що реалізація даного ключового слова в різних інтерпретаціях SQL може відрізнятися. Тому перед тим як пробувати використовувати TRUNCATE, краще ознайомитися з довідковою інформацією. LIMIT. Встановлює кількість рядків для виведення. Особливість оператора в тому, що він завжди розташовується в кінці. Приймає один обов'язковий параметр і один опціональний. Перший вказує, скільки рядків з обраними даними потрібно показати. А якщо використовується другий, то оператор спрацьовує як для діапазону значень. UNION. Дуже зручний оператор для об'єднання декількох запитів. Він вже зустрічався серед прикладів в цій статті. Можна вивести потрібні рядки з декількох таблиць, об'єднавши їх UNION для більш зручного використання. Синтаксис його такий: SELECT name FROM ім'я_таблиці UNION SELECT имя_другого_столбца FROM имя_другой таблиці. В результаті вийде зведена таблиця з об'єднаними запитами. PRIMARY KEY. Перекладається як «первинний ключ». Власне, саме така термінологія і використовується в довідкових матеріалах. Він означає унікальний ідентифікатор рядка. Застосовується, як правило, при створенні таблиці для визначення поля, яке і буде містити його. DEFAULT. Так само, як і попередній оператор, використовується в процесі виконання створює запиту. Він визначає значення за замовчуванням, яким буде заповнено поле при його створенні.

Кілька порад при розробці платформи для роботи з SQL

NULL. Початківці і не тільки програмісти при складанні запитів дуже часто забувають про можливості отримання значення NULL. У підсумку в код закрадається помилка, яку важко відстежити в процесі налагодження. Тому при створенні таблиць, вибірці або перерахунку значень потрібно зупинитися і подумати, а чи враховано виникнення NULL це ділянці запиту. Пам'ять. У цій статті були показані кілька функцій, здатні виконувати деякі завдання. При розробці оболонки для роботи з базою, можна «переважити» обчислення простих виразів на систему управління базами даних. У деяких випадках це дає значний приріст в продуктивності. Обмеження. Якщо потрібно отримати з бази з тисячами рядків всього лише двох, то варто використовувати оператори типу LIMIT або TOP. Не потрібно витягувати дані засобами мови розробки оболонки. З'єднання. Після отримання даних з декількох таблиць багато програмісти починають зводити їх докупи засобами пам'яті оболонки. Але навіщо? Адже можна скласти один запит, в якому це все буде присутній. Не доведеться писати зайвий код і резервувати додаткову пам'ять в системі. Сортування. Якщо є можливість застосовувати упорядкування у запиті, тобто силами СУБД, то потрібно її використовувати. Це дозволить значно заощадити на ресурсах при роботі програми або сервісу. Багато запитів. Якщо доводиться вставляти безліч записів послідовно, то для оптимізації слід задуматися про пакетної вставці даних одним запитом. Це також дозволить збільшити продуктивність всієї системи в цілому. Продумане розміщення даних. Перед складанням структури бази потрібно задуматися про те, а чи потрібно таку кількість таблиць і полів. Може є спосіб об'єднати їх або відмовитися від деяких. Дуже часто програмісти застосовують надлишкову кількість даних, які ніде і ніколи не будуть використовуватися. Типи. Для економії місця і ресурсів треба чуйно ставитися до видів використовуваних даних. Якщо є можливість скористатися менш «важким» для типом пам'яті, то треба застосовувати саме його. Наприклад, якщо відомо, що в даному полі числове значення що не буде перевищувати 255 то навіщо використовувати 4-байтний INT, якщо є TINYINT в 1 байт.

Висновок

На закінчення потрібно відзначити, що мова структурованих запитів SQL зараз використовується практично повсюдно — сайти, веб-сервіси, програми для ПК, додатки для мобільних пристроїв. Тому знання SQL допоможе всім галузям розробки. Разом з тим модифікації споконвічного стандарту мови іноді відрізняються один від одного. Наприклад, оператори PL SQL можуть мати інший синтаксис, ніж в SQL Server. Тому перед тим як почати розробку з цією технологією, варто ознайомитися з керівництвами по ній. В майбутньому аналоги, які могли б перевершити по функціональності і продуктивності SQL, навряд чи з'являться, тому ця сфера є досить перспективною нішею для будь-якого програміста.