Що можна за допомогою мови sql. Access SQL. Основні поняття, лексика і синтаксис. SELECT - як показуємо дані

sql часто називають мовою есперанто для систем управління базами даних (СКБД). Дійсно, в світі немає іншої мови для роботи з базами даних (БД), який би настільки широко використовувався в програмах. Перший стандарт sol з'явився в 1986 р і до теперішнього часу завоював загальне визнання. Його можна використовувати навіть при роботі з нереляціоннимі СУБД. На відміну від інших програмних засобів, таких, як мови Сі і Кобол, що є прерогативою програмістів-професіоналів, sql застосовується фахівцями з самих різних областей. Програмісти, адміністратори СУБД, бізнес-аналітики - всі вони з успіхом обробляють дані за допомогою sql. Знання цієї мови корисно всім, кому доводиться мати справу з БД.

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

Чи важко вивчити sql? Це залежить від того, наскільки глибоко ви збираєтеся вникати в суть. Для того щоб стати професіоналом, доведеться вивчити дуже багато. Мова sql з'явився в 1974 році як предмет невеликою дослідної роботи, що складалася з 23 сторінок, і з тих пір пройшов довгий шлях розвитку. Текст чинного нині стандарту - офіційного документа "the international standard database language sql" (зазвичай званого sql-92) - містить понад шести сотень сторінок, однак в ньому нічого не говориться про конкретні особливості версій sol, реалізованих в СУБД фірм microsoft, oracle, sybase та ін. Мова настільки розвинений і різноманітний, що лише простий перелік його можливостей зажадає декількох журнальних статей, а якщо зібрати все, що написано на тему sol, то вийде багатотомна бібліотека.

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

Що таке sql?

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

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

Найбільш істотним властивістю sql є можливість доступу до реляційних БД. Багато хто навіть вважають, що вислови "БД, що обробляється засобами sql" і "реляційна БД" - синоніми. Однак скоро ви переконаєтеся, що між ними є різниця. У стандарті sql-92 навіть немає терміна відношення (relation).

Що таке реляційна СУБД?

Якщо не вдаватися в подробиці, то реляційна СУБД - це система, заснована на реляційної моделі управління даними.

Поняття реляційної моделі було вперше запропоновано в роботі д-ра Е. Ф. Кодда, опублікованій в 1970 р У ній був описаний математичний апарат для структуризації даних і управління ними, а також запропонована абстрактна модель для подання будь-якої реальної інформації. До цього при використанні БД потрібно враховувати конкретні особливості зберігання в ній інформації. Якщо внутрішня структура БД змінювалася (наприклад, з метою підвищення швидкодії), доводилося переробляти прикладні програми, навіть якщо на логічному рівні ніяких змін не відбувалося. Реляційна модель дозволила відокремити приватні особливості зберігання даних від рівня прикладної програми. Справді, модель ніяк не описує способи зберігання інформації та доступу до неї. Враховується лише те, як ця інформація сприймається користувачем. Завдяки появі реляційної моделі якісно змінився підхід до управління даними: з мистецтва воно перетворилося в науку, що призвело до революційного розвитку галузі.

Основні поняття реляційної моделі

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

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

Давайте розглянемо приклад. Нехай є домен ДніНеделі, що містить значення від понеділка до неділі. Якщо відношення має атрибут ДеньНеделі, що відповідає цьому домену, то в будь-якому кортежі відносини в стовпці ДеньНеделі має зазначатися одне з перерахованих значень. Поява значень Сiчень чи Кішка не допускається.

Зверніть увагу: атрибут обов'язково повинен мати одне з допустимих значень. Завдання відразу декількох значень заборонено. Таким чином, крім вимоги приналежності значень атрибуту деякому домену, має дотримуватися умова його атомарности. Це означає, що для цих значень неприпустима декомпозиція, т. Е. Не можна розбити їх на більш дрібні частини, не втративши основного сенсу. Наприклад, якби значення атрибута одночасно містило понеділок і Середа, то можна було б виділити дві частини, зберігши первісний зміст - ДеньНеделі; отже, це значення ознаки не є атомарним. Однак якщо спробувати розбити значення "Понедельник" на частини, то вийде набір з окремих букв - від "П" до "К"; вихідний сенс втрачено, тому значення "Понедельник" є атомарним.

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

У своїй початковій роботі д-р Кодд визначив набір з восьми операторів, який отримав назву реляційної алгебри. Чотири оператора - об'єднання, логічне множення, різниця і Декартово твір - були перенесені з традиційної теорії множин; інші оператори були створені спеціально для обробки відносин. У наступних роботах д-ра Кодда, Кріса Дейта та інших дослідників були запропоновані додаткові оператори. Далі в цій статті будуть розглянуті три реляційних оператора - продукція (project), обмеження (select, або restrict) і злиття (join).

sql і реляційна модель

Тепер, коли ви познайомилися з реляційної моделлю, давайте забудемо про неї. Звичайно, не назавжди, а лише для того, щоб пояснити наступне: хоча саме запропонована д-ром Коддом реляційна модель була використана при розробці sql, між ними немає повного або буквального відповідності (це одна з причин, чому в стандарті sql-92 немає такого терміну як відношення). Наприклад, поняття таблиця sql і ставлення не є рівнозначними, тому що в таблицях може бути відразу кілька однакових рядків, тоді як у відносинах поява ідентичних кортежів заборонено. До того ж в sql не передбачено використання реляційних доменів, хоча в деякій мірі їх роль відіграють типи даних (деякі впливові прихильники реляційної моделі роблять зараз спробу домогтися включення в майбутній стандарт sql реляційних доменів).

На жаль, невідповідність між sql і реляційної моделлю породило безліч непорозумінь і суперечок за минулі роки. Але так як основна тема статті - вивчення sql, а не реляційної моделі, ці проблеми тут не розглядаються. Просто слід запам'ятати, що між термінами, що вживаються в sql і в реляційної моделі, є відмінності. Далі в статті будуть використовуватися тільки терміни, прийняті в sql. Замість відносин, атрибутів і кортежів будемо застосовувати їх sql-аналоги: таблиці, стовпці і рядки.

Статичний і динамічний sql

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

Як вивчати sql

Тепер ви готові до написання своїх перших sql-запитів. Якщо у вас є доступ до БД через sql і ви захочете скористатися нашими прикладами на практиці, то врахуйте наступне: ви повинні входити в систему як користувач з необмеженими повноваженнями і вам будуть потрібні програмні засоби інтерактивної обробки sql-запитів (якщо мова йде про мережевий БД, слід переговорити з адміністратором БД про надання вам відповідних прав). Якщо доступу до БД через sql немає - не засмучуйтеся: все приклади дуже прості і в них можна розібратися "всуху", без виходу на машину.

Для того щоб виконати будь-які дії в sql, слід виконати вираз на мові sql. Зустрічається кілька типів виразів, однак серед них можна виділити три основні групи: ddl-команди (data definition language - мова опису даних), dml-команди (data manipulation language - мова маніпуляцій з даними) і засоби контролю за даними. Таким чином, в sql в якомусь сенсі об'єднані три різних мови.

Команди мови опису даних

Почнемо з однією з основних ddl-команд - create table (Створити таблицю). У sql бувають таблиці декількох типів, основними є два типи: базові (base) і вибіркові (views). Базовими є таблиці, що відносяться до реально існуючими даними; вибіркові - це "віртуальні" таблиці, які створюються на основі інформації, одержуваної з базових таблиць; але для користувачів форми виглядають як звичайні таблиці. Команда create table призначена для створення базових таблиць.

У команді create table слід задати назву таблиці, вказати список стовпців і типи що містяться в них даних. Як параметри можуть бути присутніми також інші необов'язкові елементи, проте спочатку давайте розглянемо тільки основні параметри. Покажемо найпростішу синтаксичну форму для цієї команди:

create table ІмяТабліци (Стовпець ТіпДанних);

create і table - це ключові слова sql; ІмяТабліци, Стовпець і ТіпДанних - це формальні параметри, замість яких користувач кожен раз вводить фактичні значення. Параметри Стовпець і ТіпДанних укладені в круглі дужки. У sql круглі дужки зазвичай використовуються для групування окремих елементів. В даному випадку вони дозволяють об'єднати визначення для стовпця. Що стоїть в кінці знак "крапка з комою" є роздільником команд. Він повинен завершувати будь-який вираз на мові sql.

Розглянемо приклад. Нехай потрібно створити таблицю для зберігання даних про всі зустрічах (appointments). Для цього в sql слід ввести команду:

create table appointments (appointment_date date);

Після виконання цієї команди буде створена таблиця з ім'ям appointments, де є один стовпець appointment_date, в якому можуть записуватися дані типу date. Оскільки на поточний момент дані ще не вводилися, кількість рядків в таблиці дорівнює нулю (за допомогою команди create table тільки дається визначення таблиці; реальні значення вводяться командою insert, яка розглядається далі).

Параметри appointments і appointment_date називаються ідентифікаторами, оскільки вони задають імена для конкретних об'єктів БД, в даному випадку - імена для таблиці і стовпці відповідно. У sql зустрічаються ідентифікатори двох типів: звичайні (regular) і виділені (delimited). Виділені ідентифікатори полягають в подвійні лапки, і в них враховується регістр використовуваних символів. Звичайні ідентифікатори не виділяються ніякими обмеженими символами, в їх написанні регістр не враховується. У цій статті будуть застосовуватись тільки звичайні ідентифікатори.

Символи, які використовуються для побудови ідентифікаторів, повинні відповідати певним правилам. У звичайних ідентифікаторах можуть використовуватися тільки літери (не обов'язково латинські, а й інших алфавітів), цифри і символ підкреслення. Ідентифікатор не повинен містити знаків пунктуації, прогалин або спеціальних символів (#, @,% або!); крім того, він не може починатися з цифри або знака підкреслення. Для ідентифікаторів можна використовувати окремі ключові слова sql, але робити це не рекомендується. Ідентифікатор призначений для позначення деякого об'єкта, тому у нього має бути унікальне (в рамках певного контексту) ім'я: не можна створити таблицю з ім'ям, яке вже зустрічається в БД; в одній таблиці не можна мати стовпці з однаковими іменами. До речі, майте на увазі, що appointments і appointments - це однакові імена для sql. Одним лише зміною регістра букв створити новий ідентифікатор не можна.

Хоча таблиця може мати всього один стовпець, на практиці звичайно потрібні таблиці з декількома стовпцями. Команда для створення такої таблиці в загальному вигляді виглядає так:

create table ІмяТабліци (Стовпець ТіпДанних [(, Стовпець ТіпДанних)]);

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

create table appointments2 (appointment_date date, appointment_time time, description varchar (256));

Дана команда створює таблицю appointments2 (нова таблиця повинна мати інше ім'я, так як таблиця appointments вже присутній в БД). Як і в першій таблиці, в ній є стовпець appointment_date для запису дати зустрічей; крім того, з'явився стовпець appointment_time для запису часу цих зустрічей. Параметр description (опис) є текстовим рядком, де може міститися до 256 символів. Для цього параметра вказано тип varchar (скорочення від character varying), оскільки заздалегідь не відомо, скільки місця буде потрібно для запису, але ясно, що опис займе не більше 256 символів. При описі параметрів в типу символьний рядок (і деяких інших типів) вказується довжина параметра. Її значення задається в круглих дужках праворуч від назви типу.

Можливо, ви звернули увагу, що в двох розглянутих прикладах запис команди оформлена по-різному. Якщо в першому випадку команда повністю розміщена в одному рядку, то в другому після першої відкритої круглої дужки запис продовжена з нового рядка, і визначення кожного наступного стовпця починається з нового рядка. У sql немає спеціальних вимог до оформлення запису. Розбиття записи на рядки робить її читання зручніше. Мова sql дозволяє при написанні команд не тільки розбивати команду по рядках, але і вставляти відступи на початку рядків і пробіли між елементами запису.

Тепер, коли ви знаєте основні правила, давайте розглянемо більш складний приклад створення таблиці з кількома стовпчиками. На початку статті була показана таблиця employees (Співробітники). У ній містяться такі стовпці: прізвище, ім'я, дата прийому на роботу, підрозділ, категорія і зарплата за рік. Для визначення цієї таблиці використовується наступна команда sql:

create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date, branch_office character (15), grade_level smallint, salary decimal (9, 2));

У команді зустрічаються кілька нових елементів. Перш за все, цей вислів not null, що стоїть в кінці визначення стовпців last_name і first_name. За допомогою подібних конструкцій задаються вимоги, що підлягають обов'язковому дотриманню. В даному випадку зазначено, що поля last_name і first_name повинні обов'язково заповнюватися при введенні; залишати ці стовпці порожніми не можна (це цілком логічно: як можна ідентифікувати співробітника, не знаючи його імені?).

Крім того, в прикладі присутні три нові типи даних: character, smallint і decimal. До сих пір ми майже не говорили про типах. Хоча в sql немає реляційних доменів, проте є набір основних типів даних. Ця інформація використовується при виділенні пам'яті і порівнянні величин; певною мірою звужує список можливих значень при введенні, проте контроль типів в sql менш суворий, ніж в інших мовах.

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

До речі, якщо ви подумали, що датовремя - це помилка, то помилилися. До даної групи (datetime) відноситься більшість використовуваних в sql типів даних, пов'язаних з часом (такі параметри, як тимчасові інтервали, виділені в окрему групу). У попередньому прикладі вже зустрічалися два типи даних з групи датовремя - date і time.

Наступний тип даних, з яким ви вже знайомі, - character varying (або просто varchar); він відноситься до групи символьних рядків. Якщо varchar служить для зберігання рядків змінної довжини, то зустрівся в третьому прикладі тип char призначений для запису рядків, що мають фіксоване число символів. Наприклад, в стовпці last_name будуть записуватися рядки з 13 символів незалежно від реально вводяться прізвищ, будь то poe або penworth-chickering (у випадку з poe залишилися 10 символів заповняться пробілами).

З точки зору користувача, varchar і char мають однаковий сенс. Навіщо потрібно було вводити два типи? Справа в тому, що на практиці зазвичай доводиться шукати компроміс між швидкодією і економією простору на диску. Як правило, застосування рядків з фіксованою довжиною дає певний виграш в швидкості доступу, однак при надто великій довжині рядків простір на диску витрачається неекономно. Якщо в appointments2 для кожного рядка коментаря резервувати по 256 символів, то це може виявитися нераціонально; найчастіше рядки будуть значно коротше. З іншого боку, прізвища також мають різну довжину, але для них, як правило, потрібно близько 13 символів; в цьому випадку втрати будуть мінімальними. існує гарне правило: Якщо відомо, що довжина рядка змінюється незначно або вона порівняно невелика, то використовуйте char; в інших випадках - varchar.

Наступні два нові типи даних - smallint і decimal - відносяться до групи точних числових значень. smallint - це скорочена назва від small integer (мале ціле). У sql також передбачений тип даних integer. Наявність двох схожих типів і в цьому випадку пояснюється міркуванням економії простору. У нашому прикладі значення параметра grade_level можуть бути представлені за допомогою двозначного числа, тому використаний тип smallint; однак на практиці не завжди відомо, які максимальні значенняможуть бути у параметрів. Якщо такої інформації немає, то застосовуйте integer. Реальний обсяг, що виділяється для зберігання параметрів типу smallint і integer, і відповідний діапазон значень для цих параметрів індивідуальні для кожної платформи.

Тип даних decimal, що часто використовується для обліку фінансових показників, дозволяє задати шаблон з потрібним числом десяткових знаків. Оскільки цей тип служить для точної числової записи, він гарантує точність при виконанні математичних операцій над десятковими даними. Якщо для десяткових значень використовувати типи даних з групи наближеною числовий запису, наприклад float (floating point number - число з плаваючою точкою), це призведе до погрішностей округлення, тому для фінансових розрахунків цей варіант не підходить. Для визначення параметрів типу decimal використовується наступна форма запису:

де p - це число десяткових знаків, d - кількість розрядів після коми. Замість p слід записувати загальна кількість значущих цифр у використовуваних значеннях, а замість d - кількість цифр після коми.

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

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

Перша частина статті завершена. Друга буде присвячена вивченню dml-команд insert, select, update і delete. Також будуть розглянуті умови вибірки даних, оператори порівняння та логічні оператори, використання null-значень і тризначна логіка.

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

Секрет назви sql

На початку 1970-х рр. в ibm приступили до практичного втілення моделі реляційних БД, запропонованої д-ром Коддом. Дональд Чамберлін і група інших співробітників підрозділу перспективних досліджень створили прототип мови, що отримав назву structured english query language (мова структурованих англомовних запитів), або просто sequel. Надалі він був розширений і підданий доопрацювання. Новий варіант, запропонований ibm, отримав назву sequel / 2. Його використовували як програмний інтерфейс (api) для проектування першої реляційної системи БД фірми ibm - system / r. З міркувань, пов'язаних з правовими нюансами, в ibm вирішили змінити назву: замість sequel / 2 використовувати sql (structured query language). Цю абревіатуру часто вимовляють як "сі-ку-ел".

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

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) (f [iteration_number] = decode (iteration_number, 0, 1, f [iteration_number - 1] * iteration_number) , 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_number]), s [iteration_number - 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 n from TABLE, (select @ j: = 0) as sel1 limit 16) t2 where 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 "Not a quadratic equation." ans from dual where && A = 0 union select "x =" || to_char (- && B / 2 / & A) from 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 ) || ")" from dual where & A! = 0 and & B * & B - 4 * & A * & C< 0 ;

Даний підручник є щось типу «штампа моєї пам'яті» з мови 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 - Email
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 - оновлення поля Name ALTER TABLE Employees ALTER COLUMN Name nvarchar (30) 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 nvarchar2 (30) , 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, Birthday date, Email nvarchar (30), Position nvarchar (30), Department nvarchar (30), CONSTRAINT PK_Employees PRIMARY KEY (ID) - описуємо PK після всіх полів, як обмеження)
Після створення заллємо в таблицю дані:

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 nvarchar (30))
Насправді ім'я обмеження можна і не ставити, в цьому випадку йому буде присвоєно системне ім'я (на зразок «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 KEY, Name nvarchar (30 ) 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 таблиці Employees INSERT Positions (Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL - відкидаємо записи у яких позиція не вказана
Те ж саме проробимо для таблиці 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 PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID ) REFERENCES Departments (ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions (ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees (ID)) 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)
Видалимо відділ з ідентифікатором 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 IDENTITY_INSERT Departments OFF
Повністю очистимо таблицю 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 = 1 003
А тепер можна накласти на це поле обмеження-унікальності:

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 я зроблю виняток CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments (ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions (ID), CONSTRAINT UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN тисячі AND 1999))

INSERT Employees (ID, Name, Birthday, Email, PositionID, DepartmentID) VALUES (1000, N "Іванов І.І.", "19550219", "i.ivan [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 SYSDATETIME (), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments (ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions (ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees (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), якщо всі перераховані поля є в індексі, то можливо звернень до таблиці взагалі не знадобиться. Але це природно підвищує розмір індексу, тому що значення перерахованих полів дублюються в індексі.