Основні команди SQL, які має знати кожен програміст. Мова запитів SQL Що можна за допомогою мови sql

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-арними кортежами, або записами) і відповідних рядків таблиці.

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

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

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

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

У своїй вихідній роботі д-р Кодд визначив набір із восьми операторів, який отримав назву реляційної алгебри. Чотири оператори - об'єднання, логічне множення, різницю і Декартів твір - були перенесені з традиційної теорії множин; Інші оператори створили спеціально для обробки відносин. У подальших роботах д-ра Кодда, Кріса Дейта та інших дослідників було запропоновано додаткових операторів. Далі в цій статті будуть розглянуті три реляційні оператори — продукція (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. Його використовували як програмний інтерфейс для проектування першої реляційної системи БД фірми ibm - system/r. З міркувань, пов'язаних із правовими нюансами, в ibm вирішили змінити назву: замість sequel/2 використовувати sql (structured query language). Цю абревіатуру часто вимовляють як "сі-ку-ел".

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

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

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

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

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

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

Що таке SQL

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

І тут постає головне питання: а як до неї звернутися та отримати необхідну нам інформацію?

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

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

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

Корисні матеріали на тему:

Створення бази даних у Microsoft SQL Server - інструкція для новачків;
Додавання даних до таблиці Microsoft SQL Server – інструкція INSERT INTO .

Що таке СУБД

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

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

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

Таким інструментом якраз і виступає СУБД – це система управління базами даних, скорочено СУБД

Які СУБД бувають

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

Серед усіх за своїми можливостями та популярністю можна виділити такі системи:

Microsoft SQL Server- Це система управління базами даних від компанії Microsoft. Вона дуже популярна у корпоративному секторі, особливо у великих компаніях. І це не просто СУБД – це цілий комплекс додатків, що дозволяє зберігати та модифікувати дані, аналізувати їх, здійснювати безпеку цих даних та багато іншого;
Oracle Database- Це система управління базами даних від компанії Oracle. Це також дуже популярна СУБД, а також серед великих компаній. За своїми можливостями та функціональністю Oracle Database та Microsoft SQL Server можна порівняти, тому є серйозними конкурентами один одному, і вартість їх повнофункціональних версій дуже висока;
MySQL– це система управління базами даних також від компанії Oracle, але вона поширюється безкоштовно. MySQL отримала дуже широку популярність у інтернет сегменті, тобто. саме на MySQL працюють чи не всі сайти в інтернеті, іншими словами більшість сайтів в інтернеті використовують цю СУБД як засіб зберігання даних;
PostgreSQL- Ця система управління базами даних також є безкоштовною, і вона дуже популярна та функціональна.

Корисні матеріали на тему:

Встановлення Microsoft SQL Server 2016 Express – приклад встановлення безкоштовної редакції Microsoft SQL Server на Windows;
Встановлення Microsoft SQL Server 2017 Express на Ubuntu Server – приклад встановлення безкоштовної редакції Microsoft SQL Server на Linux;
Установка PostgreSQL 11 на Windows – приклад встановлення PostgreSQL на Windows;
Встановлення MySQL на Windows – приклад встановлення MySQL на Windows;
Встановлення та налаштування MySQL на Linux Mint – приклад встановлення MySQL на Linux;
Встановлення Oracle Database Express Edition 11g – приклад встановлення безкоштовної редакції Oracle на Windows ( стаття писалася давно, але все одно буде корисною).

Діалекти мови SQL (розширення SQL)

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

Ось деякі діалекти мови SQL:

Transact-SQL(скорочено T-SQL) – використовується у Microsoft SQL Server;
PL/SQL(Procedural Language / Structured Query Language) - використовується в Oracle Database;
PL/pgSQL(Procedural Language/PostGres Structured Query Language) – використовується в PostgreSQL.

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

SELECT ProductId, ProductName FROM Goods

то, безумовно, у всіх СУБД такі запити працюватимуть, адже SQL — це стандарт.

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

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

Наприклад, особисто я спеціалізуюся на мові T-SQL, і, відповідно, працюю з Microsoft SQL Server, ось уже більше 8 років!

Хоча, звичайно ж, з іншими СУБД я також працював, один час я супроводжував дві програми, одна з яких працювала з PostgreSQL, а друга, напевно, вже зрозуміло, з Microsoft SQL Server.

З MySQL я працював, як, напевно, і багато хто, в рамках супроводу сайтів та сервісів. Ну, а з Oracle Database мені доводилося працювати в рамках інших проектів.

Весь свій накопичений досвід у частині мови T-SQL я згрупував в одному місці та оформив у вигляді книг, тому, якщо у Вас є бажання вивчити мову Transact-SQL (T-SQL), рекомендую почитати мої книги:

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

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

Мова запитів до баз даних SQL виникла 70-ті роки. Його прототип був розроблений фірмою IBM та відомий під назвою SEQUEL (Structured English QUEry Language). SQL увібрав у собі переваги реляційної моделі, зокрема те, що в її основі лежить математичний апарат реляційної алгебри та реляційного обчислення, використовуючи при цьому порівняно невелику кількість операторів та простий синтаксис.

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

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

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

8.1. Відмінність SQL від процедурних мов програмування

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

8.2. Форми та складові частини SQL

Існують і використовуються дві форми мови SQL: інтерактивний SQL

та вбудований SQL.

Інтерактивний SQLвикористовується для безпосереднього введення та отримання результату SQL запитів користувачем в інтерактивному режимі.

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

І інтерактивний, і вбудований SQL зазвичай поділяють такі складові.

Мова Визначення Даних– DDL (Data Definition Language), дає можливість створення, зміни та видалення різних об'єктів бази даних (таблиць, індексів, користувачів, привілеїв тощо).

До додаткових функцій мови визначення даних DDL можуть бути включені також засоби визначення обмежень цілісності даних,

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

Мова обробки даних- DML (Data Manipulation Language),

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

Тим не менш, це не дві різні мови, а компоненти єдиного SQL.

8.3. Умови та термінологія

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

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

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

8.4. Вибір даних. Оператор SELECT

Найпростіші SELECT-запити

Оператор SELECT (ВИБРАТИ ) мови SQL є найважливішим і найчастіше використовуваним оператором. Він призначений вибірки інформації з таблиць бази даних. Спрощений синтаксис оператора SELECT має такий вигляд.

SELECT< список атрибутів>

FROM< список таблиц>

У квадратних дужках вказані елементи, які можуть бути відсутніми.

Ключове слово SELECT повідомляє СУБД, що ця пропозиція є запитом на отримання інформації. Після слова SELECT через кому перераховуються найменування полів (список атрибутів), вміст яких запитується.

Обов'язковим ключовим словом у реченні-запиті SELECT є слово FROM (з). За ключовим словом FROM вказується список розділених ком імен таблиць, з яких витягується інформація.

Наприклад,

SELECT NAME, SURNAME FROM STUDENT;

SQL-запит повинен закінчуватися символом "точка з комою". Наведений запит здійснює вибірку всіх значень полів NAME та

SURNAME з таблиці STUDENT.

Його результатом є таблиця наступного виду

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

STUDENT.

Звернімо увагу, що отримані в результаті SQL-запиту таблиці не повною мірою відповідають визначенню реляційного відношення. У

зокрема в них можуть виявитися дублікати кортежів з однаковими значеннями атрибутів.

Наприклад, запит: “Отримати список назв міст, в яких живуть студенти, відомості про які перебувають у таблиці STUDENT ”, можна записати у такому вигляді

SELECT CITY FROM STUDENT;

Його результатом буде таблиця

Білгород

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

Для виключення з результату SELECT -запиту записів, що повторюються використовується ключове слово DISTINCT (відмінний). Якщо запит SELECT витягує безліч полів, DISTINCT виключає дублікати рядків, у яких значення всіх вибраних полів ідентичні.

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

Написати запит, який виконує вибірку імен (NAME ) всіх студентів з прізвищем (SURNAME ) Петров, відомості про які знаходяться в таблиці

SELECT SURNAME, NAME

FROM STUDENT

WHERE SURNAME = 'Петров';

У умовах, що задаються в пропозиції WHERE, можуть використовуватися операції порівняння, що задаються наступними операторами: = (рівно), > (більше),< (меньше), >= (Більше або одно),<= (меньше или равно), <>(Не дорівнює), а також логічні оператори AND, OR та NOT.

Наприклад, запит для отримання імен та прізвищ студентів, які навчаються на третьому курсі та отримують стипендію (розмір стипендії більший за нуль) буде виглядати таким чином

SELECT NAME, SURNAME FROM STUDENT

WHERE KURS = 3 AND STIPEND > 0;

8.5. Реалізація операцій реляційної алгебри засобами мови SQL. Реляційна повнота SQL

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

Оператор об'єднання

Реляційна алгебра: A UNION B Оператор SQL:

SELECT * FROM A

SELECT * FROM B;

Оператор перетину

Реляційна алгебра: A INTERSECT B

Оператор SQL:

SELECT A. ПОЛЕ1, A. ПОЛЕ2, …,

FROM A, B

WHERE A. ПОЛЕ1=B. ПОЛЕ1 AND A. ПОЛЕ2=B. ПОЛЕ2 AND …;

SELECT A.* FROM A, B

WHERE A.pk = B.pk;

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

Реляційна алгебра: A MINUS B Оператор SQL:

SELECT * FROM A

WHERE A.pk NOT IN (SELECT pk FROM B);

де A.pk та B.pk первинні ключі таблиць A та B

Оператор декартового твору

Реляційна алгебра: A TIMES B Оператор SQL:

FROM A, B;

SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …

FROM A CROSS JOIN B;

Оператор проекції

Реляційна алгебра: A Оператор SQL:

SELECT DISTINCT X, Y, …, Z FROM A;

Оператор вибірки

Реляційна алгебра: A WHERE θ Оператор SQL:

SELECT * FROM A

WHERE θ;

Оператор θ-з'єднання

Реляційна алгебра: (A TIMES B) WHERE θ Оператор SQL:

SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …

FROM A, B

WHERE θ;

SELECT A. ПОЛЕ1, A. ПОЛЕ2, …, B. ПОЛЕ1, B. ПОЛЕ2, …

FROM A CROSS JOIN B WHERE θ;

Оператор поділу

Реляційна алгебра: A(X,Y) DEVIDE BY B(Y) Оператор SQL:

SELECT DISTINCT A. X FROM A

(SELECT *

(SELECT * FROM A A1

A1. X=A. X AND A1. Y=B. Y));

Таким чином, наведені вирази доводять, що мова SQL, як реляційна алгебра, є реляційно повним.

Слід звернути увагу, якщо у наведених запитах у таблицях будуть присутні NULL -значення (див. нижче розділ 9.1), всі вищеперелічені запити можуть відпрацювати неправильно, т.к. NULL< >NULL та NULL = NULL – є брехня.

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

Цей підручник є щось на кшталт «штампу моєї пам'яті» з мови 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 IGN 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 ,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
Повністю очистимо таблицю 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_ yees_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 es PRIMARY KEY (ID ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) loyees(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), якщо всі перелічені поля є в індексі, то можливо звернень до таблиці взагалі не знадобиться. Але це природно підвищує обсяг індексу, т.к. Значення перелічених полів дублюються в індексі.

Вирізка із MSDN.Загальний синтаксис команди для створення індексів
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON

id	title
2	The Lost Symbol
4	Inferno

Title	Return Date
The Lost Symbol	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
The Lost Symbol	2016-04-19 00:00:00

First Name	Last Name
Міке	Willis
Ellen	Horton
Ellen	Horton

First Name	Last Name	Номер books borrowed
Міке	Willis	1
Ellen	Horton	2

author	sum
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

title	bookid
The Lost Symbol	2
Who Will Cry When You Die?	3
Inferno	4

Що таке SQL?

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

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

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

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

Як вивчати sql

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

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

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

Що таке SQL

Що таке СУБД

Які СУБД бувають

Діалекти мови SQL (розширення SQL)

8.1. Відмінність SQL від процедурних мов програмування

8.2. Форми та складові частини SQL

8.3. Умови та термінологія

Найпростіші SELECT-запити

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

Трохи теорії

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

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

Підсумуємо

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

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

Підсумуємо

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

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

Підсумуємо

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

Підсумуємо

Висновок щодо DDL

1. Три чарівні слова

2. Наша база

3. Простий запит

3.1 FROM - звідки беремо дані

3.2 WHERE – які дані показуємо

3.3 SELECT – як показуємо дані

4. З'єднання (джойни)

5. Агрегування

6. Підзапити

6.1 Двовимірна таблиця

6.3 Окремі значення

7. Операції запису

7.1 Update

7.2 Delete

7.3 Insert

8. Перевірка

Вам також може сподобатися