Самовчитель SQL від простих запитів до складних. Справжнім «Чайникам» присвячується або MySQL для початківців

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

Виявляється, що для створення професійного інтернет-проекту цього буде замало. Sql використовується для роботи з БД і створення додатків для Вордпрес. Розглянемо як використовувати запити докладніше.

Що це таке

Sql – мова структурованих запитів. Створено визначення типу даних, надання доступу до них та обробці інформації за короткі проміжки часу. Він описує компоненти або результати, які ви хочете бачити на інтернет-проекті.

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

Що може робити

Мова sql дозволяє:

• створювати таблиці;
• змінювати отримувати та зберігати різні дані;
• об'єднувати інформацію у блоки;
• захистити дані;
• створювати запити у access.

Важливо! Розібравшись із sql ви зможете писати програми для Вордпрес будь-якої складності.

Яка структура

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

У неї є ім'я, колонки та ряд з якоюсь інформацією. Створювати подібні таблиці можна за допомогою запитів sql.

Що потрібно знати

Основні моменти щодо Sql

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

Create database ‘bazaname’

У лапках пишемо ім'я БД на латиниці. Намагайтеся вигадати для неї зрозуміле ім'я. Не створюйте бази типу «111», «www» тощо.

Після створення БД встановлюємо:

SET NAMES 'utf-8'

Це потрібно, щоб контент на сайті правильно відображатися.

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

CREATE TABLE 'bazaname'. ‘table’ (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

У другому рядку ми прописали три атрибути. Подивимося, що вони означають:

• Атрибут NOT NULL означає, що осередок не буде порожнім (поле обов'язкове для заповнення);
• Значення AUTO_INCREMENT - автозаповнення;
• PRIMARY KEY – первинний ключ.

Як додати інформацію

Для заповнення поля створеної таблиці значеннями використовується оператор INSERT. Пишемо такі рядки коду:

INSERT INTO ‘table’

(login, pass, date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

У дужках вказуємо назву стовпців, а наступної - значення.

Важливо! Дотримуйтесь послідовності назв і значень стовпців.

Як оновити інформацію

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

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Тепер поміняйте пароль '12345678'. Зміни відбуваються у рядку з id=1. Якщо не писати команду WHERE – поміняються всі рядки, а не конкретні.

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

Як видалити запис

Якщо ви написали щось не так, виправте це за допомогою DELETE. Працює так само, як і UPDATE. Пишемо такий код:

DELETE FROM 'table' WHERE id = '1'

Вибірка інформації

Для отримання значень з БД використовується команда SELECT. Пишемо такий код:

SELECT * FROM 'table' WHERE id = '1'

У цьому прикладі в таблиці вибираємо всі наявні поля. Це відбувається, якщо прописати в команді зірочку «*». Якщо потрібно вибрати якесь вибіркове значення, пишемо так:

SELECT log , pass FROM table WHERE id = ‘1’

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

Видалення таблиці

Відбувається за допомогою DROP. Для цього напишемо такі рядки:

DROP TABLE table;

Виведення запису з таблиці за певною умовою

Розглянемо такий код:

SELECT id, countri, city FROM table WHERE people>150000000

Він відобразить записи країн, де населення більше ста п'ятдесяти мільйонів.

Об'єднання

Зв'язати разом кілька таблиць можна за допомогою Join. Як це працює подивіться докладніше у цьому відео:

PHP та MySQL

Ще раз хочу наголосити, що запити при створенні інтернет-проекту – це звичайна справа. Щоб їх використовувати у php-документах, виконайте такий алгоритм дій:

• Поєднуємося з БД за допомогою команди mysql_connect();
• Використовуючи mysql_select_db(), вибираємо потрібну БД;
• Обробляємо запит за допомогою mysql_fetch_array();
• Закриваємо з'єднання командою mysql_close().

Важливо! Працювати з БД не складно. Головне – правильно написати запит.

Початківці вебмайстра подумають. А що почитати з цієї теми? Хотілося б порекомендувати книгу Мартіна Грабера « SQL для простих смертних ». Вона написана так, що новачкам все буде зрозуміло. Використовуйте її як настільну книгу.

Але це теорія. Яка ж справа на практиці? Насправді інтернет-проект потрібно не тільки створити, а ще й вивести в ТОП Гугла та Яндекса. У цьому вас допоможе відеокурс Створення та розкрутка сайту ».

Відео інструкція

Чи залишилися ще питання? Перегляньте детальніше онлайн відео.

Висновок

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

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

Цей підручник створено за принципом 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 – рядок
• Посада – рядок
• Відділ – рядок

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

Для виконання прикладів створимо тестову базу під назвою 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 – ​​Відділ

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

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)

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

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

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.

Для створення тимчасової таблиці можна використати команду 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 із зазначенням специфіки зберігання в ній даних, далі користувач бачить її серед основних таблиць і працює з нею як зі звичайною таблицею.

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

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

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

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

Давайте створимо 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

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 PRIMARYKE ) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (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

Опція 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)

Підсумуємо

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

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

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

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_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_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

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

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

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

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

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

Підсумуємо

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

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

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

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 ), 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), якщо всі перелічені поля є в індексі, то можливо звернень до таблиці взагалі не знадобиться. Але це природно підвищує обсяг індексу, т.к. Значення перелічених полів дублюються в індексі.

Вирізка із MSDN.Загальний синтаксис команди для створення індексів

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON