Що означає SQL. Мова програмування SQL. Типи привілеїв, які існують на цьому сервері

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

Що таке база даних SQL?

Структурована мовазапитів ( S tructured Q uery L anguage) – стандарт комунікації з базою даних, підтриманий ANSI. Остання версія - SQL-99, хоча новий стандартвже знаходиться у розробці. Більшість баз даних твердо дотримуються стандарту ANSI-92. Було багато обговорень з приводу введення більш сучасних стандартів, але виробники комерційних баз даних відхиляються від цього, розвиваючи свої нові концепції маніпуляції даних, що зберігаються. Майже кожна окрема база даних використовує деякий унікальний набірсинтаксису, хоч і дуже подібного до стандарту ANSI. У більшості випадків цей синтаксис є розширенням базового стандарту, хоча бувають випадки, коли такий синтаксис призводить до різних результатів для різних баз даних. Завжди непоганою ідеєю буде перегляд документації до бази даних, особливо якщо виходять несподівані результати.

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

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

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

Кожен стовпець є атрибутом або сукупністю атрибутів об'єктів, наприклад ідентифікаційні номери службовців, зростання, колір машин тощо. Часто щодо стовпця використовується термін полеіз зазначенням імені, наприклад "у полі Name". Поле рядка є мінімальним елементомтаблиці. Кожен стовпець у таблиці має певне ім'я, тип даних та розмір. Імена стовпців мають бути унікальні в межах таблиці.

Кожен рядок (або запис) є сукупністю атрибутів конкретного об'єкта, наприклад, у рядку може міститися ідентифікаційний номер службовця, розмір його зарплати, рік народження і т.д. Рядки таблиць не мають назв. Щоб звернутися до конкретного рядка, користувач повинен вказати якийсь атрибут (або набір атрибутів), що унікально його ідентифікує.

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

Тепер розглянемо основні типи запитів до бази даних, які зосереджені на маніпуляції даними в межах бази. Для наших цілей всі приклади наведені в стандартному SQL, щоб відповідати будь-якому середовищу.

Типи запитів даних

Є чотири основні типи запитів даних у SQL, які відносяться до так званого мови маніпулювання даними(Data Manipulation Language або DML):

SELECT- Вибрати рядки з таблиць;
INSERT– додати рядки до таблиці;
UPDATE- Змінити рядки в таблиці;
DELETE- Видалити рядки в таблиці;

Кожен із цих запитів має різні оператори та функції, які використовуються для того, щоб зробити якісь дії з даними. Запит SELECT має саме велика кількістьопцій. Існують також додаткові типизапитів, що використовуються разом із SELECT, типу JOIN та UNION. Але поки що, ми зосередимося лише на основних запитах.

Використання запиту SELECT для вибору потрібних даних

Щоб отримати інформацію, що зберігається в базі даних використовується запит SELECT. Базова діяцього запиту обмежено однією таблицею, хоча існують конструкції, що забезпечують вибірку з кількох таблиць одночасно. Для того, щоб отримати всі рядки даних для специфічних стовпців, використовується запит такого виду:

SELECT column1, column2 FROM table_name;

Також можна отримати всі стовпці з таблиці, використовуючи підстановковий знак *:

SELECT * FROM table_name;

Це може бути корисним у тому випадку, коли ви збираєтеся вибрати дані з певною умовою WHERE. Наступний запит поверне всі стовпці з усіх рядків, де "column1" містить значення "3":

Крім = (рівно), існують такі умовні оператори:

Додатково можна використовувати умови BITWEEN і LIKE для порівняння з умовою WHERE, а також комбінації операторів AND та OR.

Що в перекладі російською мовою означає: вибрати всі рядки з таблиці table_name, де значення стовпця age більше або дорівнює 18, а також значення стовпця LastName знаходиться в алфавітному проміжку від Іванів до Сидорів включно, або значенням стовпця Company є Motorola.

Використання запиту INSERT для вставлення нових даних

Запит INSERT використовується для створення нового рядкаданих. Для оновлення вже існуючих даних або порожніх полів рядка потрібно використовувати запит UPDATE.

Зразковий синтаксис запиту INSERT:

INSERT INTO table_name (column1, column2, column3) VALUES ("data1", "data2", "data3");

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

Змінюється вже існуюча інформаціяу базі даних дуже схожим чином.

Запит UPDATE та умова WHERE

UPDATE використовується для того, щоб змінити існуючі значення або звільнити поле у рядку, тому нові значення мають відповідати існуючого типуданих та забезпечувати прийнятні значення. Якщо ви не хочете змінити значення у всіх рядках, потрібно використовувати умову WHERE.

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

Будьте обережні! Запит DELETE видаляє цілі рядки

Запит DELETE повністю видаляє рядок із бази даних. Якщо ви хочете видалити одне єдине поле, потрібно використовувати запит UPDATE і встановити для цього поля значення, яке буде аналогом NULL у вашій програмі. Будьте уважні і обмежуйте ваш запит DELETE умовою WHERE, інакше ви можете втратити весь вміст таблиці.

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

Тепер ви знаєте основи SQL запитів

SQL – мова баз даних, і ми розглянули найважливіші та базові команди, які у запитах даних. Багато основних концепцій не були порушені (SUM і COUNT наприклад), але ті небагато команд, які вдалося перерахувати вище, повинні спонукати вас до активних дій і глибшого вивчення чудової мови запитів під ім'ям SQL.

Переклад: Alexandr Pyramidin

Ця стаття знайомить вас із структурою мови SQL, а також з деякими загальними поняттями, такі як типи даних, які поля можуть містити, та деякі неоднозначності, які існують в SQL. Ви не повинні запам'ятовувати будь-які подробиці, згадані в цій статті. Тут дано лише короткий огляд; багато подробиць дано, щоб згодом звертатися до них у міру оволодіння мовою.

Як працює SQL?

SQL це мова, орієнтована спеціально на реляційні бази даних (РБД). Він виконує велику роботу, яку ви мали б робити, якби використовували універсальна мовапрограмування, наприклад C. Щоб сформувати РБД на C вам потрібно було б почати з нуля. Ви повинні були б визначити об'єкт, званий таблицею, яка могла б збільшуватися, щоб мати будь-яке число рядків, а потім поступово створювати процедури для вставки і вилучення значень.

Якби ви захотіли знайти деякі конкретні рядки, вам необхідно було б виконати по кроках процедуру, подібну до наступної:

Розглянути рядок таблиці.
Виконати перевірку: чи є цей рядок одним із рядків, який вам потрібний.
Якщо це так, зберегти її десь, поки вся таблиця не буде перевірена.
Перевірити, чи є інші рядки у таблиці.
Якщо є, повернутись на крок 1.
Якщо рядків немає, вивести всі значення, збережені в кроці 3.

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

Що робить ANSI?

Як ми вже сказали у вступі, стандарт SQL визначається за допомогою коду ANSI (Американський Національний Інститут Стандартів). SQL не винаходив ANSI. Це, по суті, винахід IBM. Але інші компанії підхопили SQL відразу. Принаймні одна компанія (Oracle) відвоювала IBM право на ринковий продаж SQL-продуктів.

Після того, як з'явився ряд конкуруючих програм SQLна ринку, ANSI визначив стандарт, до якого вони мають бути наведені. (Визначення таких стандартів є функцією ANSI). Однак після цього виникли деякі проблеми. Виникли вони внаслідок стандартизації ANSI у вигляді деяких обмежень. Так як не завжди ANSI визначає те, що є найкориснішим, то програми намагаються відповідати стандарту ANSI, не дозволяючи йому обмежувати їх надто сильно. Це, своєю чергою, веде до випадкових неузгодженостей. Програми Баз даних зазвичай надають ANSI SQL додаткові особливостіі часто послаблюють багато обмежень. Тому розповсюджені різновиди ANSI будуть також розглянуті. Хоча ми, очевидно, не зможемо розглянути кожен виняток чи різновид, вдалі ідеїмають тенденцію до впровадження та використання у різних програмах, навіть коли вони не визначені стандартом ANSI. ANSI це вид мінімального стандарту, і ви можете робити більше, ніж він дозволяє, хоч і повинні виконувати його вказівки при виконанні завдань, які він визначає.

Інтерактивний та вкладений SQL

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

Вкладений SQL складається з команд SQL, розміщених усередині програм, які зазвичай написані іншою мовою (типу КОБОЛу або ПАСКАЛю). Це робить такі програми більш потужними та ефективними.

Однак, допускаючи ці мови, доводиться мати справу зі структурою SQL та стилем управління даних, який потребує деяких розширень інтерактивного SQL. Передача SQL-команд у вкладений SQL є пропускається ("passed off") для змінних або параметрів, що використовуються програмою, в яку вони були вкладені.

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

Підрозділи SQL

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

DDL (Мова визначення даних) - так звана Мова Опису Схеми в ANSI - складається з команд, які створюють об'єкти (таблиці, індекси, перегляди і так далі) в базі даних.
DML (Мова маніпулювання даними) - це набір команд, які визначають, які значення представлені в таблицях у будь-який момент часу.
DCD (Мова Управління Даними) складається із засобів, які визначають, чи дозволити користувачеві виконувати певні дії, чи ні. Вони є складовими частинами DDL в ANSI.

Не забувайте ці назви. Це не різні мови, а розділи команд SQL, згруповані за їх функціями.

Різні типи даних

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

Оскільки системи з РБД базуються на зв'язках між фрагментами інформації, різні типиданих повинні відрізнятися один від одного так, щоб відповідні процеси та порівняння могли бути в них виконані. SQL це робиться за допомогою призначення кожному полю типу даних, що вказує тип значення, яке це поле може містити. Усі значення у цьому полі повинні мати однаковий тип. У таблиці Замовників, наприклад cname і city містять рядки тексту для оцінки, а snum і cnum це числа. З цієї причини ви не можете ввести значення Highest (Найвищий) або None (Ніякий) у полі rating, яке має числовий тип даних. Це обмеження успішне, оскільки воно накладає деяку структурність на ваші дані. Ви часто порівнюєте деякі або всі значення в даному полі, тому ви можете виконувати дію тільки на певних рядкаха не на всіх. Ви не можете зробити це, якби значення полів мали змішаний тип даних.

На жаль, визначення цих типів даних є основною областю, де більшість комерційних програмБД та офіційний стандарт SQL не завжди збігаються. ANSI SQL-стандарт розпізнає лише text і number, тоді як більшість комерційних програм використовують інші спеціальні типи. Такі як DATA (ДАТА) та TIME (ЧАС) - фактично, майже стандартні типи (хоча точний форматїх змінюється). Деякі пакети також підтримують такі типи, як, наприклад, MONEY (ГРОШІ) та BINARY (ДВОЇЧНИЙ). (MONEY це спеціальна "валютна" система обчислення, що використовується комп'ютерами.)

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

ANSI визначає кілька числових типів, різницю між якими досить тонкі, і іноді їх плутають. Дозволені ANSI-типи даних перелічені в Додатку B. Складність числових типів ANSI можна, принаймні частково, пояснити зусиллям зробити вкладений SQL сумісним з іншими мовами. Два типи чисел ANSI - INTEGER (ЦЕЛЕ ЧИСЛО) і DECIMAL (ДЕСЯТЕРИЧНЕ ЧИСЛО) (які можна скорочувати як INT і DEC, відповідно), будуть адекватні для наших цілей, так само як і для більшості практичних ділових прикладних програм. Природно, що тип ЦІЛОЕ можна уявити як ДЕСЯТЕРИЧНЕ ЧИСЛО, яке містить ніяких цифр праворуч від десяткової точки.

Тип для тексту – CHAR (або СИМВОЛ), що відноситься до рядка тексту. Поле типу CHARмає довжину, що визначається максимальним числомсимволів, які можуть бути введені в поле. Більшість реалізацій також мають нестандартний тип, званий VARCHAR (ЗМІННЕ ЧИСЛО СИМВОЛІВ), який є текстовим рядкомі може мати будь-яку довжину до певного реалізацією максимуму (зазвичай 254 символи). Значення CHARACTER і VARCHAR включаються в одиночні лапки як "текст". Відмінність між CHAR та VARCHAR у тому, що CHAR повинен резервувати достатню кількість пам'яті для максимальної довжинирядки, а VARCHAR розподіляє пам'ять у міру потреби.

Символьні типи складаються з усіх друкованих символів, включаючи числа. Однак число 1 не те саме, що символ "1". Символ "1" - лише друкований фрагмент тексту, який не визначається системою як числове значення 1. Наприклад 1 + 1 = 2, але "1" + "1" не дорівнює "2". Символьні значення зберігаються в комп'ютері як двійкові значення, але вони відображаються як друкований текст.

Перетворення виконується за форматом, який визначається системою, яку ви використовуєте. Цей формат перетворення буде одним із двох стандартних типів(можливо, з розширеннями), що використовуються в комп'ютерні системи: ASCII-код (використовується у всіх персональних та малих комп'ютерах) та EBCDIC-код (Розширеному Двійково-Десятирічному Код Обміну Інформації) (використовується в великих комп'ютерах). Певні операції, такі як впорядкування в алфавітному порядку значень поля, змінюватимуться разом із форматом.

Ми повинні стежити за ринком, а не за ANSI у використанні типу DATE (ДАТА). (У системі, яка не розпізнає тип ДАТА, ви, звичайно, можете оголосити дату як символьне чи числове поле, але це зробить більшість операцій більш трудомісткими.)

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

SQL. Неузгодженості.

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

ЩО ТАКЕ "КОРИСТУВАЧ"?

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

Зазвичай у системі кожен користувач має певний вид коду перевірки прав, який ідентифікує його (термінологія змінюється). На початку сеансу з комп'ютером користувач входить до системи (реєструється), повідомляючи комп'ютера, хто цей користувач ідентифікується за допомогою певного ID (Ідентифікатора). Будь-яка кількість людей, які використовують ID доступу, є окремими користувачами; і, аналогічно, одна людина може представляти велику кількість користувачів (у різний час), використовуючи різні ідентифікатори доступу до SQL. Дії в більшості середовищ SQL приведені до спеціального Ідентифікатора доступу, який точно відповідає певному користувачеві. Таблиця або інший об'єкт належить користувачеві, який має повну владу. Користувач може або може не мати привілеїв на виконання дії над об'єктом. Для наших цілей ми домовимося, що будь-який користувач має необхідні привілеїдля виконання будь-якої дії, доки ми не повернемось спеціально до обговорення привілеїв у Розділі 22. Спеціальне значення USER (КОРИСТУВАЧ) може використовуватись як аргумент у команді. Воно вказує на доступний Ідентифікатор користувача, який видав команду.

Умовні позначення та термінологія

Ключові слова це слова, які мають особливе значення в SQL. Вони може бути командами, але з текстом і іменами об'єктів. Ми виділятимемо ключові слова, друкуючи їх ВЕЛИКИМИ ЛІТЕРАМИ. Ви повинні бути уважними, щоб не плутати ключові слова з термінами.

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

Команди або пропозиції є інструкціями, за допомогою яких ви звертаєтеся до SQL SQL.

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

Пропозиції починаються ключовим словом і складаються з ключових слів та аргументів. Наприклад, пропозиції, з якими ви можете стикатися, це "FROM Salespeope" та "WHERE city = "London"". Аргументи завершують чи змінюють значення пропозиції. У прикладах вище, Salespeople - аргумент, а FROM - ключове словопропозиції FROM. Аналогічно " city = " London " " - аргумент пропозиції WHERE.

Об'єкти це структури БД, яким дано імена і які зберігаються у пам'яті. Сюди відносяться базові таблиці, уявлення та індекси.

Щоб показати вам, як формуються команди, ми робитимемо це на прикладах. Є, однак, більш формальний метод опису команд, що використовує стандартизовані умовні позначення. Ми будемо використовувати його в наступних розділах для зручності, щоб розуміти ці умовні позначення, якщо ви зіткнетеся з ними в інших SQL-документах.

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

Резюме

Ми коротко розглянули у цьому розділі основи. Але нашим наміром і було – побіжно розглянути основи SQLтак, щоб ви змогли охопити весь обсяг інформації.

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

Програми клієнти

Протокол TFTP

TFTP -теж протокол FTP, але поверх протоколу UDP(тобто протокол без гарантованої доставки). Може використовуватися в локальній мережі, де швидкість передачі важливіша. Насправді використовується рідко.

FTP -програма запускається з командного рядка.

Windows Comander- може працювати як FTP-клієнт. Дозволяє працювати з віддаленими каталогами як і з локальними.

NetVampire -Спеціалізований FTP-клієнт, який дозволяє качати великі файли та качати поганими каналами.

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

Запити виконуються системою управління базою даних (СУБД). Якщо ви не є фахівцем з розробки та адміністрування баз даних, то цілком можете бути їх користувачем, який переглядає або змінює дані в наявних таблицях. У багатьох випадках ці та інші операції з базою даних виконуються за допомогою спеціальних програм, що надають користувачеві зручний інтерфейс. Зазвичай програми пишуться спеціальними мовами програмування (С, Pascal, Visual Basicі т. п.) і найчастіше створюються за допомогою інтегрованих середовищ розробки, наприклад, Delphi, C++ Builder та ін. Однак доступ до бази даних можна отримати і без них - лише за допомогою SQL. Слід зазначити також, як і спеціалізовані додатки зазвичай використовують SQL-фрагменты коду при зверненнях до бази даних.

Таким чином, SQL - поширений стандартна мовароботи з реляційними базами даних Синтаксис цієї мови досить простий, щоб його могли використовувати рядові користувачі, а не лише програмісти. В даний час звичайний користувач комп'ютера повинен володіти принаймні текстовим редактором (наприклад, Microsoft Word) та електронними таблицями(наприклад, Microsoft Excel). Непогано, якщо він також вміє скористатися базами даних. Різних СУБД існує багато, а універсальний засіб роботи з базами даних один - SQL. Знання SQL, хоча його основ, і вміння його застосовувати для пошуку та аналізу даних є фундаментальної частиною комп'ютерної грамотності навіть пересічних користувачів.

Перші розробки систем управління реляційними базами даних (реляційних СУБД) були виконані в IBM на початку 1970-х років. Тоді ж була створена мова даних, призначена для роботи в цих системах. Експериментальна версія цієї мови називалася SEQUEL – від англ. Structured English QUEry Language (структурована англійська запитів). Однак офіційна версія була названа коротшою - SQL (Structured Query Language). Точніше, SQL - це підмова даних, оскільки СУБД містить інші мовні засоби.

У 1981 році IBM випускає реляційну СУБД SQL/DS. На той час компанія Relation Software Inc. (Сьогодні це Oracle Corporation) вже випустила свою реляційну СУБД. Ці продукти відразу стали стандартом систем, призначених для управління базами даних. До складу цих продуктів увійшов і SQL, який фактично став стандартом для підмов даних. Виробники інших СУБД випустили свої версії SQL. Вони були як основні можливості продуктів IBM. Щоб отримати деяку перевагу для "своєї" СУБД, виробники вводили деякі розширення SQL. Разом з тим почалися роботи зі створення загальновизнаного стандарту SQL.

У 1986 році Американський національний інститут стандартів (American National Standards Institute, ANSI) випустив офіційний стандарт SQL-86, який у 1989 році був оновлений і отримав нову назву SQL-89. 1992 року цей стандарт був названий SQL-92 (ISO/IEC 9075:1992). Останньою версією стандарту SQL є SQL:2003 (ISO/IEC 9075X:2003).

Будь-яка реалізація SQL у конкретній СУБД дещо відрізняється від стандарту, відповідність якому оголошено виробником. Так, багато СУБД (наприклад, Microsoft Access 2003, PostgreSQL 7.3) підтримують SQL-92 не повною мірою, а лише з деяким рівнем відповідності. Крім того, вони підтримують елементи, які не входять до стандарту. Однак розробники СУБД прагнуть того, щоб нові версії їх продуктів якомога більшою мірою відповідали стандарту SQL.

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

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

На практиці з базою даних зазвичай працюють за допомогою додатків, написаних програмістами на процедурних мовах, наприклад, С, Visual Basic, Pascal, Java і ін. Часто програми створюються в спеціальних середовищах візуальної розробки, таких як Delphi, Microsoft Access, Visual dBase і т.д. п. При цьому розробнику програми практично не доводиться писати коди програм, оскільки за нього це робить система розробки. У всякому разі, робота з програмним кодомвиявляється мінімальною. Ці програми мають зручний графічний інтерфейс, що не змушує користувача безпосередньо вводити запити на мові SQL. Замість нього це робить програму. Втім, додаток може використовувати, так і не використовувати SQL для звернення до бази даних. SQL не єдине, хоч і дуже ефективний засіботримання, додавання та зміни даних, і якщо є можливість використовувати його в додатку, це слід робити.

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

Мова SQL вважають декларативною (описовою) мовою, на відміну від мов, якими пишуться програми. Це означає, що вирази на мові SQL описують, що потрібно зробити, а чи не якимось чином.

Наприклад, для того щоб вибрати з таблиці співробітники відомості про прізвища та посади співробітників 102 відділу, достатньо виконати наступний запит:

SELECT Прізвище, Посада FROM Співробітники WHERE Відділ = 102;

По російськи даний вираззвучить так:

ВИБРАТИ Прізвище, Посада З Співробітники ЗА УМОВИ, ЩО Відділ = 102;

Щоб змінити значення " Іванов "на" Петров стовпця Прізвище , достатньо виконати наступний запит:

UPDATE Співробітники SET Прізвище = "Петрів" WHERE Прізвище = "Іванів";

По-російськи цей вираз виглядає так:

ОБНОВИТИ Співробітники УСТАНОВИВ Прізвище РІВНИМ " Петров "ДЕ Прізвище = "Іванов" ;

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

Однак останні версії SQL підтримують оператори управління обчисленнями, властиві процедурним мовам управління (оператори умовного переходу та циклу). Тому SQL зараз це не суто декларативна мова.

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

· DML (Data Manipulation Language - мова маніпулювання даними ) призначений для підтримки бази даних: вибору ( SELECT ), додавання ( INSERT ), зміни ( UPDATE ) та видалення ( DELETE ) даних із таблиць. Ці оператори (команди) можуть містити вирази, у тому числі обчислювані, а також підзапити - запити, що містяться всередині іншого запиту. Загалом вираз запиту може бути настільки складним, що й не скажеш, що він робить. Однак складний запит можна розбити подумки на частини, які легше аналізувати. Аналогічно, складні запити створюються відносно простих для розуміння виразів (підзапитів).

· DDL (Data Definition Language - мова визначення даних ) призначений для створення, модифікації та видалення таблиць і всієї бази даних. Прикладами операторів, що входять до DDL, є CREATE TABLE (Створити Таблицю)," CREATE VIEW (Створити уявлення), CREATE SHEMA (Створити схему), ALTER TABLE (Змінити таблицю), DROP (Видалити) та ін.

· DCL (Data Control Language - мова управління даними ) призначений для забезпечення захисту бази даних від різноманітних пошкоджень. СУБД передбачає деякий захист даних автоматично. Однак у ряді випадків слід передбачити додаткові заходи, що надаються DCL.

Мова програмування

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

Мова була створена в 1970-х роках під назвою "SEQUEL" для системи управління базами даних (СУБД) System R. Пізніше вона була перейменована в "SQL", щоб уникнути конфлікту торгових марок. В 1979 SQL був вперше опублікований у вигляді комерційного продукту Oracle V2.

Перший офіційний стандарт мови було прийнято ANSI у 1986 році та ISO — у 1987. З того часу було створено ще кілька версій стандарту, деякі з них повторювали попередні з незначними варіаціями, інші набували нових суттєвих рис.

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

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

SQL складається з чотирьох окремих частин:

мова визначення даних (DDL) використовується визначення структур даних, які у базі даних. Оператори DDL дозволяють створювати, змінювати та видаляти окремі об'єктиу БД. Допустимі типи об'єктів залежать від СУБД і зазвичай включають бази даних, користувачів, таблиці і ряд дрібніших допоміжних об'єктів, наприклад, ролі та індекси.
Мова маніпуляції даними (DML) використовується для вилучення та зміни даних у БД. Оператори DML дозволяють вилучати, вставляти, змінювати та видаляти дані в таблицях. Іноді оператори selectВилучення даних не розглядаються як частина DML, оскільки вони не змінюють стан даних. Усі оператори DML мають декларативний характер.
мова визначення доступу до даних (DCL) використовується контролю доступу до даних у БД. Оператори DCL застосовуються до привілеїв та дозволяють видавати та відбирати права на застосування певних операторів DDL та DML до певних об'єктів БД.
мова управління транзакціями (TCL) використовується контролю обробки транзакцій в БД. Зазвичай оператори TCL включають commit для підтвердження змін, зроблених у ході транзакції, rollback для їх скасування та savepoint для розбиття транзакції на кілька менших частин.

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

Приклади:

Hello, World!:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

Рядок 'Hello, World!' вибирається із вбудованої таблиці dual , що використовується для запитів, що не вимагають звернення до цих таблиць.

select "Hello, World!" from dual ;

Факторіал:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

SQL не підтримує цикли, рекурсії або користувальницькі функції. Цей прикладдемонструє можливий обхідний шлях, який використовує:

псевдостовпець level для створення псевдотаблиць t1 і t2 , що містять числа від 1 до 16,
агрегатну функцію sum , що дозволяє підсумовувати елементи множини без явного використання циклу,
і математичні функції ln і exp , що дозволяють замінити твір (необхідне для обчислення факторіалу) на суму (що надається SQL).

Рядок “0! = 1” не ввійде у набір рядків, отриманий результаті, т.к. спроба обчислити ln(0) призводить до виключення.

Числа Фібоначчі:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

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

формулу Біне та математичні функції ROUND, POWER та SQRT для обчислення n-ого числа Фібоначчі;
псевдостовпець level для створення псевдотаблиці t1, що містить числа від 1 до 16;
вбудовану функцію SYS_CONNECT_BY_PATH для впорядкованої конкатенації отриманих чисел.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (fib || ", ", "/")), "/", "") || "..." fiblist FROM ( SELECT n , fib , ROW_NUMBER () OVER (ORDER BY n ) r FROM (select n , round ((power ((1 + sqrt (5 )) * 0 . 5 , n ) - power) ((1 - sqrt (5 )) * 0 . 5 , n )) / sqrt (5 )) fib from (select level n from dual connect by level<= 16 ) t1 ) t2 ) START WITH r = 1 CONNECT BY PRIOR r = r - 1 ;

Hello, World!:

Приклад для версій Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012, MySQL 5, PostgreSQL 8.4, PostgreSQL 9.1, sqlite 3.7.3

select "Hello, World!" ;

Факторіал:

Приклад для версій Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012

Використовується рекурсивне визначення факторіалу, реалізоване через рекурсивний запит. Кожен рядок запиту містить два числові поля — n і n!, і кожен наступний рядок обчислюється з використанням даних з попереднього.

Можна обчислити цілочисленні факторіали лише до 20! При спробі вирахувати 21! з'являється помилка “Arithmetic overflow error”, тобто. відбувається переповнення розрядної сітки.

Для дійсних чисел обчислюється факторіал 100! (Для цього в прикладі необхідно замінити bigint на float у 3-му рядку)

Числа Фібоначчі:

Приклад для версій Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012

Використовується ітеративне визначення чисел Фібоначчі, реалізоване через рекурсивний запит. Кожен рядок запиту містить два сусідні числа послідовності, і наступний рядок обчислюється як (останнє число, сума чисел) попереднього рядка. Таким чином, всі числа, крім першого і останнього, зустрічаються двічі, тому в результат входять тільки перші числа кожного рядка.

Факторіал:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

select n || "!=" || f factorial from dual model return all rows dimension by (0 d) measures (0 f, 1 n) rules iterate (17) , n [iteration_number] = iteration_number);

Числа Фібоначчі:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

Цей приклад демонструє використання оператора model , доступного починаючи з версії Oracle 10g і дозволяє обробляти рядки запиту як елементів масиву. Кожен рядок містить два поля — саме число Фібоначчі та конкатенація всіх чисел, менших або рівних йому. Ітеративна конкатенація чисел у тому ж запиті, в якому вони генеруються, виконується простіше та швидше, ніж агрегація як окрема дія.

select max (s) || ", ..." from (select s from dual model return all rows dimension by ( 0 d ) measures ( cast (" " as varchar2 (200 )) s , 0 f ) rules iterate (16 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , 1 , 1 , f [ iteration_number - 1 ] + f [ iteration_number - 2 ]), s [ iteration_number ] = decode (iteration_number , 0 , to_char ( f [ iteration 1] ||", "||to_char(f[iteration_number])))));

Факторіал:

Приклад для версій MySQL 5

select concat (cast (t2 . n as char ), "! = " , cast (exp (sum (log (t1 . n ) ))) as char )) from ( select @ i : = @ i + 1 AS n from TABLE , (select @ i : = 0 ) as sel1 limit 16 ) t1 , ( select @ j : = @ j + 1 AS від TABLE , ( select @ j : = 0 ) as sel1 limit 16 ) t2 , де t1 . n<= t2 . n group by t2 . n

Числа Фібоначчі:

Приклад для версій MySQL 5

Замініть TABLE на будь-яку таблицю, до якої є доступ, наприклад mysql.help_topic .

select concat (group_concat (f separator ", "), ", ...") from (select @ f : = @ i + @ j as f , @ i : = @ j , @ j : = @ f from TABLE , (select @ i: = 1, @ j: = 0) sel1 limit 16) t

Hello, World!:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

У цьому прикладі використовується анонімний блок PL/SQL, який виводить повідомлення стандартний потік виведення за допомогою пакета dbms_output .

begin dbms_output . put_line ("Hello, World!"); end;

Факторіал:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

Цей приклад демонструє ітеративне обчислення факторіалу засобами PL/SQL.

declare n number: = 0; f number: = 1; begin while (n<= 16 ) loop dbms_output . put_line (n || "! = " || f ); n : = n + 1 ; f : = f * n ; end loop ; end ;

Числа Фібоначчі:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

Цей приклад використовує ітеративне визначення чисел Фібоначчі. Вже обчислені числа зберігаються в структурі даних varray - аналог масиву.

declare type vector is varray (16 ) of number ; fib vector : = vector(); i number; s varchar2 (100); begin fib. extend (16); fib (1): = 1; fib (2): = 1; s: = fib (1) | "," || fib (2) || ","; for i in 3 .. 16 loop fib (i): = fib (i - 1) + fib (i - 2); s: = s || fib (i) || ","; end loop; dbms_output. put_line (s | | "..."); end;

Квадратне рівняння:

Приклад для версій Oracle 10g SQL, Oracle 11g SQL

Цей приклад тестувався в SQL*Plus, TOAD та PL/SQL Developer.

Чистий SQL дозволяє вводити змінні у процесі виконання запиту у вигляді змінних змінних. Для визначення такої змінної її ім'я (в даному випадку A, B та C) слід використовувати з амперсандом & перед ним щоразу, коли потрібно послатися на цю змінну. Коли запит виконується, користувач отримує запит на введення значень всіх змінних змінних, які були використані в запиті. Після введення значень кожне посилання на таку змінну замінюється її значення, і отриманий запит виконується.

Існує кілька способів ввести значення для змінних змінних. У цьому прикладі перше посилання на кожну змінну передує не одинарним, а подвійним амперсандом && . Таким чином значення для кожної змінної вводиться тільки один раз, а всі наступні посилання на неї будуть замінені тим самим значенням (при використанні одиночного амперсанда в SQL * Plus значення для кожного посилання на одну і ту ж змінну доводиться вводити окремо). У PL/SQL Developer посилання на всі змінні повинні передувати одиночний знак & , інакше виникатиме помилка ORA-01008 "Not all variables bound".

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

Сам запит складається із чотирьох різних запитів. Кожен запит повертає рядок, що містить результат обчислень, в одному з випадків (A=0, D=0, D>0 та D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.

alter session set NLS_NUMERIC_CHARACTERS = "."; select "Не quadratic equation." ans from dual where && A = 0 union select "x = " || to_char (-&& B / 2 /& A ) від dual where & A != 0 and & B *& B - 4 *& A *&& C = 0 union select "x1 = " || to_char ((-& B + sqrt (& B *& B - 4 *& A *& C )) / 2 /& A ) || ", x2 = "| to_char (-& B - sqrt (& B *& B - 4 *& A *& C )) / 2 /& A from dual where & A != 0 and & B *& B - 4 *& A *& C > 0 union select "x1 = (" || to_char (-& B / 2 /& A ) || "," || to_char (sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || "), " || "x2 = (" || to_char (-& B / 2 /& A ) || "," || to_char (- sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || ")" від dual where & A != 0 and & B *& B - 4 *& A *& C< 0 ;

Leran2002 9 квітня 2015 о 12:31

Підручник з мови SQL (DDL, DML) з прикладу діалекту MS SQL Server. Частина перша

Microsoft SQL Server ,
SQL

Tutorial

Про що цей підручник

Цей підручник є щось на кшталт «штампу моєї пам'яті» з мови SQL (DDL, DML), тобто. це інформація, яка накопичилася в процесі професійної діяльності і постійно зберігається в моїй голові. Це для мене достатній мінімум, який застосовується при роботі з базами даних найчастіше. Якщо виникає потреба застосовувати більш повні конструкції SQL, то я зазвичай звертаюся за допомогою в бібліотеку MSDN розташовану в інтернет. На мій погляд, утримати все в голові дуже складно, та й немає особливої потреби в цьому. Але знати основні конструкції дуже корисно, т.к. вони застосовні практично у такому вигляді у багатьох реляційних базах даних, як-от Oracle, MySQL, Firebird. Відмінності в основному полягають у типах даних, які можуть відрізнятись у деталях. Основних конструкцій мови SQL не так багато, і за постійної практики вони швидко запам'ятовуються. Наприклад, для створення об'єктів (таблиць, обмежень, індексів тощо) достатньо мати під рукою текстовий редактор середовища (IDE) для роботи з базою даних, і немає потреби вивчати візуальний інструментарій заточений для роботи з конкретним типом баз даних (MS SQL) , Oracle, MySQL, Firebird, …). Це зручно і тим, що весь текст знаходиться перед очима, і не потрібно бігати по численних вкладках, щоб створити, наприклад, індекс або обмеження. При постійній роботі з базою даних створити, змінити, а особливо перестворити об'єкт за допомогою скриптів виходить у рази швидше, ніж якщо це робити у візуальному режимі. Також у скриптовому режимі (відповідно, за належної акуратності), простіше ставити і контролювати правила найменування об'єктів (моя суб'єктивна думка). До того ж скрипти зручно використовувати у разі, коли зміни, що робляться в одній базі даних (наприклад, тестової), необхідно перенести в такому вигляді в іншу базу (продуктивну).

Мова SQL підрозділяється на кілька частин, тут я розгляну 2 найважливіші його частини:

DML – Data Manipulation Language (мова маніпулювання даними), що містить такі конструкції:
- SELECT – вибірка даних
- INSERT – вставка нових даних
- UPDATE – оновлення даних
- DELETE – видалення даних
- MERGE - злиття даних

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

Цей підручник створено за принципом Step by Step, тобто. необхідно читати його послідовно та бажано відразу ж виконуючи приклади. Але якщо у вас виникає потреба дізнатися про якусь команду більш детально, то використовуйте конкретний пошук в інтернет, наприклад, у бібліотеці MSDN.

Під час написання цього підручника використовувалася база даних MS SQL Server версії 2014, для виконання скриптів я використовував MS SQL Server Management Studio (SSMS).

Коротко про MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) – утиліта для Microsoft SQL Server для конфігурування, управління та адміністрування компонентів бази даних. Ця утиліта містить редактор скриптів (який в основному і буде нами використовуватися) та графічну програму, яка працює з об'єктами та налаштуваннями сервера. Головним інструментом SQL Server Management Studio є Object Explorer, який дозволяє користувачеві переглядати, витягувати об'єкти сервера, а також керувати ними. Цей текст частково запозичений із вікіпедії.

Для створення нового редактора скрипта використовуйте кнопку New Query/Новий запит:

Для зміни поточної бази даних можна використовувати список, що випадає:

Для виконання певної команди (або групи команд) виділіть її та натисніть кнопку «Execute/Виконати» або клавішу «F5». Якщо в редакторі в даний момент знаходиться тільки одна команда, або вам необхідно виконати всі команди, нічого виділяти не потрібно.

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

Власне це все, що нам необхідно буде знати для виконання наведених тут прикладів. Решта за утилітою SSMS неважко вивчити самостійно.

Трохи теорії

Реляційна база даних (РБД, або далі в контексті просто БД) є сукупністю таблиць, пов'язаних між собою. Якщо говорити грубо, то БД – файл у якому дані зберігаються у структурованому вигляді.

СУБД – Система Управління цими Базами даних, тобто. це комплекс інструментів до роботи з конкретним типом БД (MS SQL, Oracle, MySQL, Firebird, …).

Примітка
Т.к. в житті, в розмовній мові, ми здебільшого говоримо: «БД Oracle», або навіть просто «Oracle», насправді маючи на увазі «СУБД Oracle», то в контексті даного підручника іноді вживатиметься термін БД. З контексту, на мою думку, буде зрозуміло, про що саме йдеться.

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

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

Для кожної таблиці, як і її стовпців задаються найменування, якими згодом до них йде звернення.
Найменування об'єкта (ім'я таблиці, ім'я стовпця, ім'я індексу тощо) у MS SQL може мати максимальну довжину 128 символів.

Для довідки– у БД ORACLE найменування об'єктів можуть мати максимальну довжину 30 символів. Тому для конкретної БД потрібно виробляти свої правила для найменування об'єктів, щоб укластися в ліміт за кількістю символів.

SQL - мова що дозволяє здійснювати запити в БД у вигляді СУБД. У конкретній СУБД, мова SQL може мати специфічну реалізацію (свій діалект).

DDL і DML - підмножина мови SQL:

Мова DDL служить до створення та модифікації структури БД, тобто. для створення/зміни/видалення таблиць та зв'язків.
Мова DML дозволяє здійснювати маніпуляції із даними таблиць, тобто. з її рядками. Він дозволяє робити вибірку даних із таблиць, додавати нові дані в таблиці, а також оновлювати та видаляти існуючі дані.

У мові SQL можна використовувати 2 види коментарів (однорядковий та багаторядковий):

Однорядковий коментар
і

/* багаторядковий коментар */

Власне, все для теорії цього буде достатньо.

DDL – Data Definition Language (мова опису даних)

Для прикладу розглянемо таблицю з даними про співробітників, у звичному для людини програмістом, що не є:

У разі стовпці таблиці мають такі наименования: Табельний номер, ПІБ, Дата народження, E-mail, Посада, Відділ.

Кожен з цих стовпців можна охарактеризувати за типом даних, що містяться в ньому:

Табельний номер – ціле число
ПІБ – рядок
Дата народження – дата
E-mail – рядок
Посада – рядок
Відділ – рядок

Тип стовпця - характеристика, яка говорить про те, які дані можуть зберігати даний стовпець.

Для початку буде достатньо запам'ятати тільки такі основні типи даних, що використовуються в MS SQL:

Значення	Позначення у MS SQL	Опис
Рядок змінної довжини	varchar(N) і nvarchar(N)	За допомогою числа N ми можемо вказати максимально можливу довжину рядка для відповідного стовпця. Наприклад, якщо хочемо сказати, що значення стовпця «ПІБ» може містити максимум 30 символів, необхідно задати їй тип nvarchar(30). Відмінність varchar від nvarchar полягає в тому, що varchar дозволяє зберігати рядки у форматі ASCII, де один символ займає 1 байт, а nvarchar зберігає рядки у форматі Unicode, де кожен символ займає 2 байти. Тип varchar варто використовувати тільки в тому випадку, якщо ви на 100% впевнені, що в даному полі не потрібно зберігати символи Unicode. Наприклад, varchar можна використовуватиме зберігання адрес електронної пошти, т.к. вони зазвичай містять лише символи ASCII.
Рядок фіксованої довжини	char(N) і nchar(N)	Від рядка змінної довжини цей тип відрізняється тим, що й довжина рядок менше N символів, вона завжди доповнюється праворуч до довжини N пробілами і зберігається у БД у вигляді, тобто. у базі даних вона займає рівно N символів (де один символ займає 1 байт для char і 2 байта для типу nchar). На практиці цей тип дуже рідко знаходить застосування, і якщо використовується, він використовується переважно у форматі char(1), тобто. коли поле визначається одним символом.
Ціле число	int	Даний тип дозволяє нам використовувати в стовпці лише цілі числа, як позитивні, і негативні. Для довідки (зараз це не так актуально для нас) – діапазон чисел, що дозволяє тип int від -2 147 483 648 до 2 147 483 647. Зазвичай це основний тип, який використовується для завдання ідентифікаторів.
Речове чи дійсне число	float	Якщо говорити простою мовою, то це числа, в яких може бути десяткова точка (кома).
Дата	date	Якщо у стовпці необхідно зберігати лише Дату, яка складається з трьох складових: Числа, Місяця та Року. Наприклад, 15.02.2014 (15 лютого 2014 року). Даний тип можна використовувати для стовпця "Дата прийому", "Дата народження" і т.п. у тих випадках, коли нам важливо зафіксувати лише дату, або коли складова часу нам не важлива і її можна відкинути або якщо вона не відома.
Час	time	Даний тип можна використовувати, якщо у стовпці необхідно зберігати лише дані час, тобто. Годинники, Хвилини, Секунди та Міллісекунди. Наприклад, 17:38:31.3231603 Наприклад, щоденний «Час відправлення рейсу».
дата та час	datetime	Цей тип дозволяє одночасно зберегти і Дату, і Час. Наприклад, 15.02.2014 17:38:31.323 Для прикладу це може бути дата та час якоїсь події.
Прапор	bit	Даний тип зручно застосовувати для зберігання значень виду «Так»/«Ні», де «Так» зберігатиметься як 1, а «Ні» зберігатиметься як 0.

Так само значення поля, якщо це не заборонено, може бути не вказано, для цієї мети використовується ключове слово NULL.

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

Просту базу даних (без вказівки додаткових параметрів) можна створити, виконавши таку команду:

CREATE DATABASE Test
Видалити базу даних можна командою (варто бути дуже обережним із цією командою):

DROP DATABASE Test
Для того, щоб перейти на нашу базу даних, можна виконати команду:

USE Test
Або ж виберіть базу даних Test у списку в області меню SSMS. При роботі мною найчастіше використовується саме цей спосіб перемикання між базами.

Тепер у нашій БД ми можемо створити таблицю, використовуючи описи у тому вигляді як вони є, використовуючи пробіли та символи кирилиці:

CREATE TABLE [Співробітники]([Табельний номер] int, [ПІБ] nvarchar(30), [Дата народження] date, nvarchar(30), [Посада] nvarchar(30), [Відділ] nvarchar(30))
У цьому випадку нам доведеться укладати імена у квадратні дужки […].

Але в базі даних для більшої зручності всі назви об'єктів краще задавати на латиниці і не використовувати в іменах прогалини. У MS SQL зазвичай у разі кожне слово починається з великої літери, наприклад, для поля «Табельний номер», ми могли б задати ім'я PersonnelNumber. Також у імені можна використовувати цифри, наприклад, PhoneNumber1.

На замітку
У деяких СУБД кращим може бути наступний формат найменувань «PHONE_NUMBER», наприклад, такий формат часто використовується в БД ORACLE. Природно при завданні ім'я поля бажано щоб воно не збігалося з ключовими словами, що використовуються в СУБД.

З цієї причини можете забути про синтаксис із квадратними дужками та видалити таблицю [Співробітники]:

DROP TABLE [Співробітники]
Наприклад, таблицю зі співробітниками можна назвати «Employees», а її полям можна задати такі назви:

ID – Табельний номер (Ідентифікатор співробітника)
Name – ПІБ
Birthday – Дата народження
Email – E-mail
Position – Посада
Department – Відділ

Найчастіше для назви поля ідентифікатора використовується слово ID.

Тепер створимо нашу таблицю:

CREATE TABLE Employees(ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Для того, щоб встановити обов'язкові для заповнення стовпці, можна використовувати опцію NOT NULL.

Для вже існуючої таблиці поля можна перевизначити за допомогою наступних команд:

Оновлення поля ID ALTER TABLE Employees ALTER COLUMN ID int NOT NULL

На замітку
Загальна концепція мови SQL для більшості СУБД залишається однаковою (принаймні про це я можу судити з тих СУБД, з якими мені довелося попрацювати). Відмінність DDL у різних СУБД в основному полягають у типах даних (тут можуть відрізнятися не тільки їх найменування, а й деталі їх реалізації), так само може трохи відрізнятися і сама специфіка реалізації мови SQL (тобто суть команд одна і та ж, але можуть бути невеликі відмінності в діалекті, на жаль, але одного стандарту немає). Володіючи основами SQL ви можете перейти з однієї СУБД в іншу, т.к. вам у разі потрібно лише розібратися у деталях реалізації команд у новій СУБД, тобто. в більшості випадків достатньо буде просто провести аналогію.
Створення таблиці CREATE TABLE Employees(ID int, -- в ORACLE тип int - це еквівалент(обгортка) для number(38) Name nvarchar2(30), -- nvarchar2 в ORACLE еквівалентний nvarchar у MS SQL Birthday date, Email n , Position nvarchar2(30), Department nvarchar2(30)); -- оновлення полів ID та Name (тут замість ALTER COLUMN використовується MODIFY(…)) ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- додавання PK (у цьому випадку конструкція виглядає як і в MS SQL, вона буде показана нижче) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
Для ORACLE є відмінності в плані реалізації типу varchar2, його кодування залежить від налаштувань БД і текст може зберігатися, наприклад, у кодуванні UTF-8. Крім цього, довжину поля в ORACLE можна задати як у байтах, так і в символах, для цього використовуються додаткові опції BYTE і CHAR, які вказуються після довжини поля, наприклад:
NAME varchar2(30 BYTE) -- місткість поля дорівнюватиме 30 байтам NAME varchar2(30 CHAR) -- місткість поля дорівнюватиме 30 символам
Яка опція буде використовуватися за замовчуванням BYTE або CHAR, у разі простої вказівки в ORACLE типу varchar2(30), залежить від налаштувань БД, так само вона може іноді задаватися в налаштуваннях IDE. Загалом часом можна легко заплутатися, тому у випадку ORACLE, якщо використовується тип varchar2 (а це тут часом виправдано, наприклад, при використанні кодування UTF-8) я волію явно прописувати CHAR (бо зазвичай довжину рядка зручніше вважати саме в символах ).

Але в разі якщо в таблиці вже є які-небудь дані, то для успішного виконання команд необхідно, щоб у всіх рядках таблиці поля ID і Name були обов'язково заповнені. Продемонструємо це на прикладі, вставимо в таблицю дані в поля ID, Position та Department, це можна зробити наступним скриптом:

INSERT Employees(ID,Position,Department) VALUES (1000,N"Директор",N"Адміністрація"), (1001,N"Програміст",N"ІТ"), (1002,N"Бухгалтер",N"Бухгалтерія" ), (1003,N"Старший програміст",N"ІТ")
У разі, команда INSERT також видасть помилку, т.к. при вставці ми не зазначили значення обов'язкового поля Name.
Якби у нас у початковій таблиці вже були ці дані, то команда ALTER TABLE Employees ALTER COLUMN ID int NOT NULL виконалася б успішно, а команда ALTER TABLE Employees ALTER COLUMN Name int NOT NULL видала повідомлення про помилку, що поле Name є NULL (не зазначені) значення.

Додамо значення для поля Name і знову заллємо дані:

Також опцію NOT NULL можна використовувати безпосередньо під час створення нової таблиці, тобто. у контексті команди CREATE TABLE.

Спочатку видалимо таблицю за допомогою команди:

DROP TABLE Employees
Тепер створимо таблицю з обов'язковими для заповнення стовпцями ID та Name:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Можна також після імені стовпця написати NULL, що означатиме, що в ньому будуть допустимі значення NULL (не вказані), але цього робити не обов'язково, так як дана характеристика мається на увазі за умовчанням.

Якщо потрібно зробити існуючий стовпець необов'язковим для заповнення, то використовуємо наступний синтаксис команди:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Або просто:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
Також даною командою ми можемо змінити тип поля на інший сумісний тип, або змінити його довжину. Наприклад, розширимо поле Name до 50 символів:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

Первинний ключ

При створенні таблиці бажано, щоб вона мала унікальний стовпець або сукупність стовпців, яка є унікальною для кожного її рядка – за даним унікальним значенням можна однозначно ідентифікувати запис. Таке значення називається первинним ключем таблиці. Для нашої таблиці Employees таким унікальним значенням може бути стовпець ID (який містить «Табельний номер співробітника» – нехай у нашому випадку це значення унікальне для кожного співробітника і не може повторюватися).

Створити первинний ключ до вже існуючої таблиці можна за допомогою команди:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
Де "PK_Employees" це ім'я обмеження, що відповідає за первинний ключ. Зазвичай найменування первинного ключа використовується префікс «PK_» після якого йде ім'я таблиці.

Якщо первинний ключ складається з декількох полів, то ці поля необхідно перерахувати в дужках через кому:

ALTER TABLE ім'я_таблиці ADD CONSTRAINT ім'я_обмеження PRIMARY KEY(поле1,поле2,…)
Варто зазначити, що в MS SQL усі поля, які входять до первинного ключа, повинні мати характеристику NOT NULL.

Також первинний ключ можна визначити безпосередньо під час створення таблиці, тобто. у контексті команди CREATE TABLE. Видалимо таблицю:

DROP TABLE Employees
А потім створимо її, використовуючи наступний синтаксис:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Віртуальний час, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY всіх полів, як обмеження)
Після створення заллємо в таблицю дані:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Директор",N"Адміністрація",N"Іванов І.І."), (1001,N"Програміст",N"ІТ",N" Петров П.П."), (1002,N"Бухгалтер",N"Бухгалтерія",N"Сідорів С.С."), (1003,N"Старший програміст",N"ІТ",N"Андрєєв А. А.")
Якщо первинний ключ у таблиці складається лише з значень одного стовпця, можна використовувати наступний синтаксис:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, - вказуємо як характеристику поля Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department n
Насправді ім'я обмеження можна і не ставити, у цьому випадку йому буде присвоєно системне ім'я (на зразок PK__Employee__3214EC278DA42077):

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), PRIMARY KEY(ID))
Або:

CREATE TABLE Employees(ID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Але я рекомендував би для постійних таблиць завжди явно ставити ім'я обмеження, т.к. за явно заданим і зрозумілим ім'ям з ним згодом буде легше проводити маніпуляції, наприклад, можна зробити його видалення:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
Але такий короткий синтаксис, без зазначення імен обмежень, зручно застосовувати під час створення тимчасових таблиць БД (ім'я тимчасової таблиці починається з # чи ##), які після використання буде видалено.

Підсумуємо

Наразі ми розглянули наступні команди:

CREATE TABLEимя_таблицы (перерахування полів та його типів, обмежень) – служить до створення нової таблиці в поточної БД;
DROP TABLEимя_таблицы – служить видалення таблиці з поточної БД;
ALTER TABLEім'я_таблиці ALTER COLUMNім'я_стовпця … – служить для оновлення типу стовпця або зміни його налаштувань (наприклад для завдання характеристики NULL або NOT NULL);
ALTER TABLEім'я_таблиці ADD CONSTRAINTім'я_обмеження PRIMARY KEY(поле1, поле2,…) – додавання первинного ключа до вже існуючої таблиці;
ALTER TABLEім'я_таблиці DROP CONSTRAINTимя_обмеження – видалення обмеження з таблиці.

Трохи про тимчасові таблиці

Вирізка із MSDN.У MS SQL Server існує два види тимчасових таблиць: локальні (#) та глобальні (##). Локальні тимчасові таблиці видно лише їхнім творцям до завершення сеансу з'єднання з екземпляром SQL Server, як тільки вони створені вперше. Локальні часові таблиці автоматично видаляються після відключення користувача від екземпляра SQL Server. Глобальні часові таблиці видно всім користувачам протягом будь-яких сеансів з'єднання після створення цих таблиць і видаляються, коли всі користувачі, які посилаються на ці таблиці, відключаються від екземпляра SQL Server.

Тимчасові таблиці створюються у системній базі tempdb, тобто. створюючи їх ми не засмічуємо основну базу, в іншому ж тимчасові таблиці повністю ідентичні звичайним таблицям, їх можна видалити з допомогою команди DROP TABLE. Найчастіше використовуються локальні (#) часові таблиці.

Для створення тимчасової таблиці можна використати команду CREATE TABLE:

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Так як тимчасова таблиця в MS SQL аналогічна звичайній таблиці, її відповідно також можна видалити самому командою DROP TABLE:

DROP TABLE #Temp

Так само тимчасову таблицю (як і звичайну таблицю) можна створити і відразу заповнити даними повертаються запитом використовуючи синтаксис SELECT … INTO:

SELECT ID,Name INTO #Temp FROM Employees

На замітку
У різних СУБД реалізація тимчасових таблиць може відрізнятись. Наприклад, у СУБД ORACLE і Firebird структура тимчасових таблиць повинна бути визначена заздалегідь командою CREATE GLOBAL TEMPORARY TABLE із зазначенням специфіки зберігання даних, далі вже користувач бачить її серед основних таблиць і працює з нею як зі звичайною таблицею.

Нормалізація БД – дроблення на підтаблиці (довідники) та визначення зв'язків

Наша поточна таблиця Employees має недолік у тому, що в полях Position і Department користувач може ввести будь-який текст, що в першу чергу загрожує помилками, так як він у одного співробітника може вказати як відділ просто «ІТ», а у другого співробітника, наприклад , ввести "ІТ-відділ", у третього "IT". У результаті незрозуміло, що мав на увазі користувач, тобто. чи є дані співробітники працівниками одного відділу, чи користувач описався і це 3 різних відділу? А тим більше, у цьому випадку, ми не зможемо правильно згрупувати дані для якогось звіту, де, можливо, потрібно показати кількість співробітників у розрізі кожного відділу.

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

Третій недолік – складність оновлення даних полів, якщо зміниться назва якоїсь посади, наприклад, якщо потрібно перейменувати посаду «Програміст», на «Молодший програміст». У цьому випадку нам доведеться вносити зміни до кожного рядка таблиці, в якій Посада дорівнює «Програмісту».

Щоб уникнути цих недоліків і застосовується так звана нормалізація бази даних - дроблення її на підтаблиці, таблиці довідники. Не обов'язково лізти в нетрі теорії і вивчати, що з себе представляють нормальні форми, достатньо розуміти суть нормалізації.

Давайте створимо 2 таблиці довідники «Посади» та «Відділи», першу назвемо Positions, а другу відповідно Departments:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY ) NOT NULL)
Зауважимо, що тут ми використовували нову опцію IDENTITY, яка говорить про те, що дані в стовпці ID нумеруватимуться автоматично, починаючи з 1, з кроком 1, тобто. при додаванні нових записів їм послідовно присвоюватимуться значення 1, 2, 3, і т.д. Такі поля зазвичай називають автоінкрементними. У таблиці може бути визначено лише одне поле з властивістю IDENTITY і, як правило, але необов'язково, таке поле є первинним ключем для даної таблиці.

На замітку
У різних СУБД реалізація полів із лічильником може робитися за своїм. У MySQL, наприклад, таке поле визначається за допомогою опції AUTO_INCREMENT. У ORACLE і Firebird раніше цю функціональність можна було емулювати за допомогою використання послідовностей (SEQUENCE). Але наскільки я знаю у ORACLE зараз додали опцію GENERATED AS IDENTITY.

Давайте заповнимо ці таблиці автоматично, виходячи з поточних даних записаних у полях Position і Department таблиці Employees:

Заповнюємо поле Name таблиці Positions, унікальними значеннями з поля Position таблиці .
Те саме проробимо для таблиці Departments:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
Якщо ми відкриємо таблиці Positions і Departments, то побачимо пронумерований набір значень по полю ID:

SELECT * FROM Positions

SELECT * FROM Departments

Дані таблиці тепер і відіграватимуть роль довідників для завдання посад та відділів. Тепер ми посилатимемося на ідентифікатори посад та відділів. Насамперед створимо нові поля в таблиці Employees для зберігання даних ідентифікаторів:

Додаємо поле для ID посади ALTER TABLE Employees ADD PositionID int -- додаємо поле для ID відділу ALTER TABLE Employees ADD DepartmentID int
Тип посилальних полів повинен бути яким самим, як і в довідниках, в даному випадку це int.

Також додати в таблицю відразу кілька полів можна однією командою, перерахувавши поля через кому:

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Тепер пропишемо посилання (посилальні обмеження - FOREIGN KEY) для цих полів, для того, щоб користувач не мав можливості записати в дані поля, значення, відсутні серед значень ID, що знаходяться в довідниках.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
І те саме зробимо для другого поля:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Тепер користувач у дані поля зможе занести лише значення ID із відповідного довідника. Відповідно, щоб використовувати новий відділ або посаду, він насамперед повинен буде додати новий запис до відповідного довідника. Т.к. посади та відділи тепер зберігаються в довідниках в одному єдиному примірнику, то щоб змінити назву, достатньо змінити її тільки в довіднику.

Ім'я посилального обмеження, зазвичай, є складовим, воно складається з префікса «FK_», потім йде ім'я таблиці і після знака підкреслення йде ім'я поля, яке посилається на ідентифікатор таблиці-довідника.

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

ALTER TABLE таблиця ADD CONSTRAINT ім'я_обмеження FOREIGN KEY(поле1,поле2,…) REFERENCES таблиця_довідник(поле1,поле2,…)
В даному випадку в таблиці "таблиця_довідник" первинний ключ представлений комбінацією з декількох полів (поле1, поле2, ...).

Власне, тепер оновимо поля PositionID та DepartmentID значеннями ID із довідників. Скористайтеся для цього DML командою UPDATE:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
Подивимося, що вийшло, виконавши запит:

SELECT * FROM Employees

Все, поля PositionID та DepartmentID заповнені відповідними посадами та відділами ідентифікаторами потреби у полях Position і Department у таблиці Employees тепер немає, можна видалити ці поля:

ALTER TABLE Employees DROP COLUMN Position,Department
Тепер таблиця у нас набула такого вигляду:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID
1000	Іванов І.І.	NULL	NULL	2	1
1001	Петров П.П.	NULL	NULL	3	3
1002	Сидоров С.С.	NULL	NULL	1	2
1003	Андрєєв А.А.	NULL	NULL	4	3

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

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Positions p ON p.ID=e.PositionID

В інспекторі об'єктів ми можемо побачити всі об'єкти, створені для даної таблиці. Звідси можна проводити різні маніпуляції з цими об'єктами – наприклад, перейменовувати чи видаляти об'єкти.

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

ALTER TABLE Employees ADD ManagerID int
У цьому полі допустиме значення NULL, поле буде порожнім, якщо, наприклад, над співробітником немає вищестоящих.

Тепер створимо FOREIGN KEY на таблицю Employees:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Давайте тепер створимо діаграму і подивимося, як виглядають на ній зв'язки між нашими таблицями:

В результаті ми повинні побачити наступну картину (таблиця Employees пов'язана з таблицями Positions і Depertments, а також посилається сама на себе):

Насамкінець варто сказати, що ключі посилання можуть включати додаткові опції ON DELETE CASCADE і ON UPDATE CASCADE, які говорять про те, як поводитися при видаленні або оновленні запису, на яку є посилання в таблиці-довіднику. Якщо ці опції не вказані, то ми не можемо змінити ID в таблиці довіднику у того запису, на який є посилання з іншої таблиці, так само ми не зможемо видалити такий запис з довідника, поки не видалимо всі рядки, що посилаються на цей запис або ж оновимо у цих рядках посилання інше значення.

Для прикладу перестворимо таблицю із зазначенням опції ON DELETE CASCADE для FK_Employees_DepartmentID:

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARYKEY KEY(DepartmentID ) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (Manager) sitionID,DepartmentID,ManagerID )VALUES (1000,N"Іванов І.І.","19550219",2,1,NULL), (1001,N"Петров П.П.","19831203",3,3,1003), (1002 ,N"Сідоров С.С.","19760607",1,2,1000), (1003,N"Андрєєв А.А.","19820417",4,3,1000)
Видалимо відділ з ідентифікатором 3 з таблиці Departments:

DELETE Departments WHERE ID=3
Подивимося дані таблиці Employees:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID
1000	Іванов І.І.	1955-02-19	NULL	2	1	NULL
1002	Сидоров С.С.	1976-06-07	NULL	1	2	1000

Як бачимо, дані відділу 3 з таблиці Employees так само віддалилися.

Опція ON UPDATE CASCADE веде себе аналогічно, але діє при оновленні значення ID у довіднику. Наприклад, якщо ми змінимо ID посади в довіднику посад, то в цьому випадку буде проводитись оновлення DepartmentID у таблиці Employees на нове значення ID, яке ми задали в довіднику. Але в даному випадку це продемонструвати не вийде, т.к. у колонки ID у таблиці Departments стоїть опція IDENTITY, яка не дозволить нам виконати наступний запит (змінити ідентифікатор відділу 3 на 30):

UPDATE Departments SET ID=30 WHERE ID=3
Головне зрозуміти суть цих 2-х опцій ON DELETE CASCADE та ON UPDATE CASCADE. Я застосовую ці опції дуже рідко і рекомендую добре подумати, як вказувати в посилальному обмеження, т.к. при ненавмисному видаленні запису з таблиці довідника це може призвести до великих проблем і створити ланцюгову реакцію.

Відновимо відділ 3:

Даємо дозвіл на додавання/зміну IDENTITY значення SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"ІТ") -- забороняємо додавання/зміну IDENTITY значення SET
Повністю очистимо таблицю Employees за допомогою команди TRUNCATE TABLE:

TRUNCATE TABLE Employees
І знову перезаллємо в неї дані, використовуючи попередню команду INSERT:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Іванов І.І.","19550219",2,1,NULL), (1001,N"Петров П.П." ,"19831203",3,3,1003), (1002,N"Сідорів С.С.","19760607",1,2,1000), (1003,N"Андрєєв А.А.","19820417" ,4,3,1000)

Підсумуємо

На даний момент до наших знань додалося ще кілька команд DDL:

Додавання якості IDENTITY до поля – дозволяє зробити це поле автоматично заповнюваним (полем-лічильником) для таблиці;
ALTER TABLEім'я_таблиці ADDперелік_полей_з_характеристиками – дозволяє додати нові поля до таблиці;
ALTER TABLEім'я_таблиці DROP COLUMNсписок_полів – дозволяє видалити поля з таблиці;
ALTER TABLEім'я_таблиці ADD CONSTRAINTім'я_обмеження FOREIGN KEY(поля) REFERENCESтаблиця_довідник(поля) – дозволяє визначити зв'язок між таблицею та таблицею довідником.

Інші обмеження – UNIQUE, DEFAULT, CHECK

За допомогою обмеження UNIQUE можна сказати, що значення для кожного рядка в даному полі або в наборі полів має бути унікальним. У випадку таблиці Employees таке обмеження ми можемо накласти на поле Email. Тільки попередньо заповнимо Email значеннями, якщо вони ще не визначені:

UPDATE Employees SET Email=" [email protected]" WHERE ID=1000 UPDATE Employees SET Email=" [email protected]" WHERE ID=1001 UPDATE Employees SET Email=" [email protected]" WHERE ID=1002 UPDATE Employees SET Email=" [email protected] WHERE ID=1003
А тепер можна накласти на це поле обмеження-унікальності:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Тепер користувач не зможе внести той самий E-Mail у кількох співробітників.

Обмеження унікальності зазвичай називається так – спочатку йде префікс «UQ_», далі назва таблиці і після знака підкреслення йде ім'я поля, яке накладається дане обмеження.

Відповідно якщо унікальною в розрізі рядків таблиці має бути комбінація полів, то перераховуємо їх через кому:

ALTER TABLE ім'я_таблиці ADD CONSTRAINT ім'я_обмеження UNIQUE(поле1,поле2,…)
За допомогою додавання до поля обмеження DEFAULT ми можемо задати значення за замовчуванням, яке буде підставлятись у випадку, якщо при вставці нового запису це поле не буде перераховане у списку полів команди INSERT. Це обмеження можна встановити безпосередньо при створенні таблиці.

Давайте додамо в таблицю Employees нове поле «Дата прийому» і назвемо його HireDate і скажемо, що значення за замовчуванням у даного поля буде поточна дата:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
Або якщо стовпець HireDate вже існує, то можна використовувати наступний синтаксис:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Тут не вказав ім'я обмеження, т.к. у випадку DEFAULT у мене склалася думка, що це не так критично. Але якщо робити по-доброму, то, гадаю, не треба лінуватися і варто задати нормальне ім'я. Робиться це так:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Оскільки цього стовпця раніше не було, то при його додаванні до кожного запису в поле HireDate буде вставлено поточне значення дати.

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

INSERT Employees(ID,Name,Email)VALUES(1004,N"Сергєєв С.С."," [email protected]")
Подивимося, що вийшло:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Іванов І.І.	1955-02-19	[email protected]	2	1	NULL	2015-04-08
1001	Петров П.П.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Сидоров С.С.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Андрєєв А.А.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Сергєєв С.С.	NULL	[email protected]	NULL	NULL	NULL	2015-04-08

Перевірочне обмеження CHECK використовується в тому випадку, коли необхідно здійснити перевірку значень, що вставляються в поле. Наприклад, накладемо дане обмеження на поле табельний номер, яке ми є ідентифікатором співробітника (ID). За допомогою цього обмеження скажемо, що табельні номери повинні мати значення від 1000 до 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
Обмеження зазвичай називається так само, спочатку йде префікс "CK_", потім ім'я таблиці та ім'я поля, на яке накладено це обмеження.

Спробуємо вставити неприпустимий запис для перевірки, що обмеження працює (ми маємо отримати відповідну помилку):

INSERT Employees(ID,Email) VALUES(2000," [email protected]")
А тепер змінимо значення, що вставляється на 1500 і переконаємося, що запис вставиться:

INSERT Employees(ID,Email) VALUES(1500," [email protected]")
Можна також створити обмеження UNIQUE і CHECK без вказівки імені:

ALTER TABLE Employees ADD UNIQUE (Email) ALTER TABLE Employees ADD CHECK (ID BETWEEN 1000 AND 1999)
Але це дуже хороша практика і краще ставити ім'я обмеження у явному вигляді, т.к. щоб розібратися потім, що буде важче, потрібно буде відкривати об'єкт і дивитися, за що він відповідає.

При хорошому найменуванні багато інформації про обмеження можна дізнатися безпосередньо на його ім'я.

І, відповідно, всі ці обмеження можна створити відразу при створенні таблиці, якщо її ще немає. Видалимо таблицю:

DROP TABLE Employees
І перестворимо її з усіма створеними обмеженнями однією командою CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- для DEFAULT я з (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_ _ID CHECK (ID BETWEEN 1000 AND 1999))

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N"Іванов І.І.","19550219"," [email protected]",2,1), (1001,N"Петров П.П.","19831203"," [email protected]",3,3), (1002,N"Сідоров С.С.","19760607"," [email protected]",1,2), (1003,N"Андрєєв А.А.","19820417"," [email protected]",4,3)

Трохи про індекси, які створюються при створенні обмежень PRIMARY KEY та UNIQUE

Як можна побачити на скріншоті вище, при створенні обмежень PRIMARY KEY та UNIQUE автоматично створилися індекси з такими ж назвами (PK_Employees та UQ_Employees_Email). За замовчуванням індекс для первинного ключа створюється як CLUSTERED, а решти індексів як NONCLUSTERED. Варто сказати, що поняття кластерного індексу не в усіх СУБД. Таблиця може мати лише один кластерний (CLUSTERED) індекс. CLUSTERED - означає, що записи таблиці будуть сортуватися за цим індексом, так само можна сказати, що цей індекс має безпосередній доступ до всіх даних таблиці. Це головний індекс таблиці. Якщо сказати ще грубіше, це індекс, прикручений до таблиці. Кластерний індекс - це дуже потужний засіб, який може допомогти при оптимізації запитів, поки просто запам'ятаємо це. Якщо ми хочемо сказати, щоб кластерний індекс використовувався не в первинному ключі, а для іншого індексу, то при створенні первинного ключа ми повинні вказати опцію NONCLUSTERED:

ALTER TABLE ім'я_таблиці ADD CONSTRAINT ім'я_обмеження PRIMARY KEY NONCLUSTERED(поле1,поле2,…)
Наприклад зробимо індекс обмеження PK_Employees некластерним, а індекс обмеження UQ_Employees_Email кластерним. Насамперед видалимо дані обмеження:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
А тепер створимо їх з опціями CLUSTERED та NONCLUSTERED:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (ID) ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (Email)
Тепер, виконавши вибірку з таблиці Employees, ми побачимо, що записи відсортувалися за кластерним індексом UQ_Employees_Email:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	HireDate
1003	Андрєєв А.А.	1982-04-17	[email protected]	4	3	2015-04-08
1000	Іванов І.І.	1955-02-19	[email protected]	2	1	2015-04-08
1001	Петров П.П.	1983-12-03	[email protected]	3	3	2015-04-08
1002	Сидоров С.С.	1976-06-07	[email protected]	1	2	2015-04-08

До цього, коли кластерним індексом був індекс PK_Employees, стандартні записи сортувалися по полю ID.

Але в даному випадку це лише приклад, який показує суть кластерного індексу, т.к. швидше за все до таблиці Employees будуть робитися запити з полю ID і у якихось випадках, можливо, вона сама виступатиме у ролі довідника.

Для довідників зазвичай доцільно, щоб кластерний індекс був побудований первинним ключем, т.к. у запитах ми часто посилаємось на ідентифікатор довідника для отримання, наприклад, найменування (Посади, Відділу). Тут згадаємо, про що я писав вище, що кластерний індекс має прямий доступ до рядків таблиці, а звідси випливає, що ми можемо отримати значення будь-якого стовпця без додаткових витрат.

Кластерний індекс вигідно застосовувати до полів, якими вибірка йде найчастіше.

Іноді в таблицях створюють ключ по сурогатному полю, ось у цьому випадку буває корисно зберегти опцію CLUSTERED індекс для відповідного індексу і вказати опцію NONCLUSTERED при створенні сурогатного первинного ключа.

Підсумуємо

На даному етапі ми познайомилися з усіма видами обмежень, у їхньому найпростішому вигляді, які створюються командою виду «ALTER TABLE ім'я_таблиці ADD CONSTRAINT ім'я_обмеження...»:

PRIMARY KEY– первинний ключ;
FOREIGN KEY- Налаштування зв'язків і контроль цілісності даних;
UNIQUE- дозволяє створити унікальність;
CHECK- дозволяє здійснювати коректність введених даних;
DEFAULT– дозволяє встановити значення за замовчуванням;
Також слід зазначити, що всі обмеження можна видалити, використовуючи команду « ALTER TABLEім'я_таблиці DROP CONSTRAINTім'я_обмеження».

Також ми частково торкнулися теми індексів і розібрали поняття кластерний ( CLUSTERED) та некластерний ( NONCLUSTERED) індекс.

Створення самостійних індексів

Під самостійністю тут маються на увазі індекси, які створюються задля обмеження PRIMARY KEY чи UNIQUE.

Індекси по полю або полям можна створювати наступною командою:

CREATE INDEX IDX_Employees_Name ON Employees(Name)
Також тут можна вказати опції CLUSTERED, NONCLUSTERED, UNIQUE, а також можна вказати напрямок сортування кожного окремого поля ASC (за замовчуванням) або DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
Під час створення некластерного індексу опцію NONCLUSTERED можна відпустити, т.к. вона мається на увазі за умовчанням, тут вона показана просто, щоб вказати позицію опції CLUSTERED чи NONCLUSTERED у команді.

Видалити індекс можна наступною командою:

DROP INDEX IDX_Employees_Name ON Employees
Прості індекси, як і обмеження, можна створити в контексті команди CREATE TABLE.

Наприклад знову видалимо таблицю:

DROP TABLE Employees
І перестворимо її з усіма створеними обмеженнями та індексами однією командою CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT MARY KEY (ID ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) es(ID), CONSTRAINT UQ_Employees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Насамкінець вставимо в таблицю наших співробітників:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Іванов І.І.","19550219"," [email protected]",2,1,NULL), (1001,N"Петров П.П.","19831203"," [email protected]",3,3,1003), (1002,N"Сідорів С.С.","19760607"," [email protected]",1,2,1000), (1003,N"Андрєєв А.А.","19820417"," [email protected]",4,3,1000)
Додатково варто зазначити, що до некластерного індексу можна включати значення за допомогою вказівки їх у INCLUDE. Тобто. в даному випадку INCLUDE-індекс чимось нагадуватиме кластерний індекс, тільки тепер не індекс прикручений до таблиці, а необхідні значення прикручені до індексу. Відповідно такі індекси можуть дуже підвищити продуктивність запитів на вибірку (SELECT), якщо всі перелічені поля є в індексі, то можливо звернень до таблиці взагалі не знадобиться. Але це природно підвищує обсяг індексу, т.к. Значення перелічених полів дублюються в індексі.