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

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

В даний час поширені такі специфікації SQL:

Бази даних та специфікації SQL

Тип бази даних	Специфікація SQL
Microsoft SQL	Transact-SQL
Microsoft Jet/Access	Jet SQL
MySQL	SQL/PSM (SQL/Persistent Stored Module)
Oracle	PL/SQL (Procedural Language/SQL)
IBM DB2	SQL PL (SQL Procedural Language)
InterBase/Firebird	PSQL (Procedural SQL)

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

Визначення

Transact-SQL – це процедурне розширення мови SQL компаній Microsoft. SQL був розширений такими додатковими можливостямияк:

• керуючі оператори,
• локальні та глобальні змінні,
• різні додаткові функції для обробки рядків, дат, математики тощо,
• підтримка автентифікації Microsoft Windows

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

Дослідна база даних

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

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

Створення бази даних USE master CREATE DATABASE TestDatabase GO -- Створення таблиць USE TestDatabase CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40), UserSurname nvarchar(40), DepartmentID int, PositionID int) CREATE , DepartmentName nvarchar(40)) CREATE TABLE Позиції (PositionID int PRIMARY KEY, PositionName nvarchar(40), BaseSalary money) CREATE TABLE (CustomerID int PRIMARY KEY, CustomerName nvarchar(40), CustomerAddress n PRIMARY KEY, CustomerID int, UserID int, text) GO -- Заповнення таблиць USE TestDatabase INSERT Users VALUES (1, "Ivan", "Petrov", 1, 1) INSERT Users VALUES (2, "Ivan", "Sidorov", 1, 2) INSERT Users VALUES (3, "Petr", "Ivanov", 1, 2) INSERT Users VALUES (4, "Nikolay", "Petrov", 1, 3) INSERT Users VALUES (5, "Nikolay", "Іванов", 2, 1) INSERT Users VALUES (6, "Sergey", "Sidorov", 2, 3) INSERT Users VALUES (7, "Andrey", "Bukin", 2, 2) INSERT Users VALUES (8 , "Viktor", "Rybakov", 4, 1) INSERT Departments VALUES (1, "Production") INSERT Departments VALUES (2, "Distribution") INSERT Departments VALUES (3, "Purchasing") INSERT Positions VALUES (1, " Manager", 1000) INSERT Positions VALUES (2, "Senior analyst", 650) INSERT VALUES (1, "Alex Company", "606443, Russia, Bor, Lenina str., 15") INSERT VALUES (2, "Potrovka" , "115516, Москв, Промислова стор., 1") INSERT VALUES (1, 1, 1, "Special parts") GO

Примітка. У Microsoft SQL Server 2000 запити виконуються у Query Analyzer. У Microsoft SQL Server 2005 запити виконуються в SQL Server Management Studio.

В результаті роботи сценарію на SQL сервері буде створена база даних TestDatabase з п'ятьма таблицями користувача: Users, Departments, Positions, Local Customers, Local Orders.

Users

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
3	Петр	Ivanov	1	3
4	Nikolay	Петров	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	3
8	Viktor	Rybakov	4	1

Positions

PositionID	PositionName	BaseSalary
1	Manager	1000
2	Senior analyst	650
3	Analyst	400

Local Orders

OrderID	CustomerID	UserID	Description
1	1	1	Special parts

Departments

DepartmentID	DepartmentName
1	Production
2	Distribution
3	Purchasing

Local Customers

CustomerID	CustomerName	CustomerAddress
1	Alex Company	606443, Росія, Bor, Lenina str., 15
2	Potrovka	115516, Moscow, Promyshlennaya str., 1

Елементи синтаксису

Директиви сценарію

Директиви сценарію – це специфічні команди, які використовуються лише у MS SQL. Ці команди допомагають серверу визначати правила роботи зі скриптом та транзакціями. Типові представники: GO – сигналізує SQL-серверу про закінчення сценарію, EXEC (або EXECUTE) – виконує процедуру або скалярну функцію.

Коментарі

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

• -- - рядковий коментар виключає з виконання лише один рядок, перед яким стоять два мінуси.
• /* */ - блоковий коментар виключає із виконання цілий блок команд, укладений у вказану конструкцію.

Типи даних

Як і в мовах програмування, SQL існують різні типиданих для зберігання змінних:

• Числа - для зберігання числових змінних (int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Дати - для зберігання дати та часу (datetime, smalldatetime).
• Символи для зберігання символьних даних (char, nchar, varchar, nvarchar).
• Двійкові – для зберігання бінарних даних (binary, varbinary, bit).
• Більшеоб'ємні - типи даних зберігання великих бінарних даних (text, ntext, image).
• Спеціальні – покажчики (cursor), 16-байтове шістнадцяткове число, яке використовується для GUID (uniqueidentifier), штамп зміни рядка (timestamp), версія рядка (rowversion), таблиці (table).

Примітка. Для використання російських символів (не ASCII кодування) використовуються типи даних з приставкою "n" (nchar, nvarchar, ntext), які кодують символи двома байтами. Інакше висловлюючись, для роботи з Unicode використовуються типи даних із " n " .

Примітка. Для даних змінної довжини використовуються типи даних із приставкою "var". Типи даних без приставки "var" мають фіксовану довжину області пам'яті, невикористана частина якої заповнюється пробілами або нулями.

Ідентифікатори

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

• @ - ідентифікатор локальної змінної (користувача).
• @@ - Ідентифікатор глобальної змінної (вбудованої).
• # - ідентифікатор локальної таблиці чи процедури.
• ## - Ідентифікатор глобальної таблиці чи процедури.
• - Ідентифікатор угруповання слів у змінну.

Змінні

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

Оголошення змінної виконується командою DECLARE, завдання змінної здійснюється або командою SET, або SELECT:

USE TestDatabase -- Оголошення змінних . Виведення змінної @EmpName в результаті запиту SELECT @EmpName AS GO

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

Оператори

Оператори – це спеціальні команди, призначені для виконання простих операцій над змінними:

• Арифметичні оператори: "*" - помножити, "/" - ділити, "%" - модуль від розподілу, "+" - скласти, "-" - відняти, "()" - дужки.
• Оператори порівняння: "=" - одно, ">" - більше, "<" - меньше, ">=" - більше чи одно, "<=" меньше или равно, "<>" - не дорівнює.
• Оператори з'єднання: "+" - з'єднання рядків.
• Логічні оператори: "AND" - та, "OR" - або, "NOT" - не.

Системні функції

Специфікація Transact-SQl значно розширює стандартні можливості SQL завдяки вбудованим функціям:

• Агрегативні функції-функції, які працюють з колекціями значень та видають одне значення. Типові представники: AVG – середнє значення колонки, SUM – сума колонки, MAX – максимальне значення колонки, COUNT – кількість елементів колонки.
• Скалярні функції-це функції, які повертають одне значення, працюючи зі скалярними даними або взагалі без вхідних даних. Типові представники: DATEDIFF – різниця між датами, ABS – модуль числа, DB_NAME – ім'я бази даних, USER_NAME – ім'я поточного користувача, LEFT – частина рядка зліва.
• Функції-вказівники-функції, які використовуються як посилання на інші дані. Типові представники: OPENXML – покажчик на джерело даних у вигляді XML-структури, OPENQUERY – покажчик на джерело даних у вигляді іншого запиту.

Примітка. Повний список функцій можна знайти у довідці до сервера SQL.

Примітка. До скалярних функцій можна віднести і глобальні змінні, які в тексті сценарію викликаються подвійним собакою "@@".

USE TestDatabase -- Використання агрегативної функції для підрахунку середньої зарплати SELECT AVG(BaseSalary) AS FROM Positions GO -- Використання скалярної функції для отримання імені бази даних SELECT DB_NAME() AS GO -- Використання скалярної функції для отримання імені поточного користувача DECLARE @MyUser char (30) SET @MyUser = USER_NAME() SELECT "The current user""s database username is: "+ @MyUser GO -- Використання функції-вказівника для отримання даних з іншого сервера SELECT * FROM OPENQUERY(OracleSvr, "SELECT name, id FROM owner.titles") GO

Вирази

Вираз - це комбінація символів та операторів, яка отримує на вхід скалярну величину, а на виході дає іншу величину або виконує якусь дію. У Transact-SQL вирази поділяються на 3 типи: DDL, DCL та DML.

• DDL (Data Definition Language) – використовуються для створення об'єктів у базі даних. Основні представники даного класу: CREATE – створення об'єктів, ALTER – зміна об'єктів, DROP – видалення об'єктів.
• DCL (Data Control Language)- призначені призначення прав на об'єкти бази даних. Основні представники даного класу: GRANT – дозвіл на об'єкт, DENY – заборона на об'єкт, REVOKE – скасування дозволів та заборон на об'єкт.
• DML (Data Manipulation Language)- використовуються для запитів та зміни даних. Основні представники даного класу: SELECT – вибірка даних, INSERT – вставка даних, UPDATE – зміна даних, DELETE – видалення даних.

USE TestDatabase -- Використання DDL CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int) GO -- Використання DCL GRANT SELECT ON Users TO public GO -- Використання DML SELECT UserID, UserName + " " + UserSurname AS F GO -- Використання DDL DROP TABLE TempUsers GO

Управління виконанням сценарію

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

• Блок угруповання - структура, що поєднує перелік виразів в один логічний блок (BEGIN … END).
• Блок умови - структура, яка перевіряє виконання певної умови (IF...ELSE).
• Блок циклу - структура, що організує повторення виконання логічного блоку (WHILE...BREAK...CONTINUE).
• Перехід - команда, яка переходить потоку виконання сценарію на вказану мітку (GOTO).
• Затримка – команда, що затримує виконання сценарію (WAITFOR)
• Виклик помилки – команда, що генерує помилку виконання сценарію (RAISERROR)

Динамічне конструювання виразів

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

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

USE master -- Завдання динамічних даних DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30) SET @dbname = "TestDatabase" SET @tablename = "Positions" SET @column = "BaseSalary" - - Використання динамічних даних EXECUTE ("USE" + @dbname + "SELECT AVG(" + @column + ") AS FROM " + @tablename) GO

Вибірка даних

У мовах SQL вибірка даних із таблиць здійснюється за допомогою команди SELECT:

SELECT<названия колонок или *>FROM<название таблицы>

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

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

Вибрати всі записи з таблиці Local Customers SELECT * FROM -- Вибрати унікальні записи колонки UserName із таблиці Users SELECT DISTINCT UserName FROM Users

UserName
Andrey
Ivan
Nikolay
Петр
Sergey
Viktor

Фільтрування даних здійснюється за допомогою команди WHERE, в якій використовуються наступні оператори та команди порівняння: =,<, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, NULL, IS NOT NULL. У загальному вигляді команда SELECT із фільтром виглядає так:

SELECT<названия колонок или *>FROM<название таблицы>WHERE<условие>

У рядку порівняння дозволяється використовувати символи підстановки:

• % – будь-яка кількість символів;
• _ – один символ;
• - будь-який символ, вказаний у дужках;
• [^] - будь-який символ, не вказаний у дужках.

-- Вибрати всі записи з таблиці Users, де DepartmentID = 1 SELECT * FROM Users WHERE DepartmentID = 1

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
3	Петр	Ivanov	1	2
4	Nikolay	Петров	1	3

-- Вибрати всі записи з таблиці Users, у кого в імені є буква A SELECT * FROM Users WHERE UserName LIKE "%a%"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Петров	1	3
5	Nikolay	Ivanov	2	1
7	Andrey	Bukin	2	2

-- Вибрати всі записи з таблиці Users, у кого в імені друга літера не V SELECT * FROM Users WHERE UserName LIKE "_[^v]%"

Фільтрування дозволяє використовувати підзапити, тобто конструювати запит із кількох підзапитів:

Вибрати записи колонки PositionID з таблиці Positions, де BaseSalary< 600 SELECT PositionID FROM Positions WHERE BaseSalary < 600 -- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey SELECT * FROM Users WHERE UserName IN ("Ivan", "Andrey")

Для сортування даних у вибірці використовується команда ORDER BY, але слід врахувати, що ця команда не сортує дані типу text, ntext та image. За умовчанням сортування проводиться за зростанням, тому параметр ASC у цьому випадку можна не вказувати:

SELECT<названия колонок или *>FROM<название таблицы>WHERE<условие>ORDER BY<названия колонок>

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

SELECT TOP [кількість рядків]<названия колонок или *>FROM<название таблицы>WHERE<условие>ORDER BY<названия колонок>

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

• AVG(колонка) – середнє значення колонки;
• COUNT(колонка) – кількість не NULL елементів колонки;
• COUNT(*) – кількість елементів запиту;
• MAX(колонка) – максимальне значення в колонці;
• MIN(колонка) – мінімальне значення в колонці;
• SUM(колонка) – сума значень у колонці.

Приклади використання команд ORDER, TOP та функцій агрегування:

Вибрати 3 перші унікальні записи колонки UserName з таблиці Users, - відсортованих за зростанням UserName SELECT DISTINCT TOP 3 UserName FROM Users оклад в організації SELECT * FROM Users WHERE PositionID IN (SELECT PositionID FROM Positions WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions))

Угруповання даних

SQL дозволяє проводити групування даних за певними полями таблиці. Щоб згрупувати дані за яким-небудь параметром, у SQL-запиті необхідно написати команду GROUP BY, у якій вказати ім'я колонки, за якою проводиться угруповання. Колонки, згадані в команді GROUP BY, повинні бути присутніми у команді SELECT, а також команда SELECT повинна містити функцію агрегування, яка буде застосована до згрупованих даних.

Знайти кількість працівників у кожному відділі (згрупувати працівників по - ідентифікатору відділів та порахувати кількість записів у кожній групі) SELECT DepartmentID, COUNT(UserID) AS "Number of users"

DepartmentID	Number of users
1	4
2	3
4	1

Щоб відфільтрувати рядки в запиті з групуванням, застосовується спеціальна команда HAVING, в якій вказується умова фільтрації. Колонки, за якими здійснюється фільтрація, повинні бути присутніми у команді GROUP BY. Команда HAVING може використовуватися і без GROUP BY, у цьому випадку вона працює аналогічно до команди WHERE, але вона дозволяє застосовувати в умовах фільтрації тільки функції агрегування.

Знайти кількість працівників у першому відділі (згрупувати працівників по - ідентифікатору відділів, порахувати кількість записів у кожній групі та - вивести в результат лише відділ з ідентифікатором рівним 1) SELECT DepartmentID, COUNT(UserID) AS "Number of users" BY DepartmentID HAVING DepartmentID = 1 -- Знайти кількість працівників з певною посадою у кожному відділі -- (згрупувати працівників за ідентифікатором посад та відділів та -- порахувати кількість записів у кожній групі), а також порахувати -- кількість працівників у кожному відділі та загальну кількість працівників SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8

Команда угруповання також може доповнюватися оператором WITH CUBE, який доповнює формує всілякі комбінації з колонок, що групуються: якщо є N колонок, то вийде 2^N комбінацій.

Знайти кількість працівників з певною посадою в кожному відділі - (згрупувати працівників за ідентифікатором посад та відділів і - порахувати кількість записів у кожній групі), а також порахувати - кількість працівників за кожною посадою, по кожному відділу та - загальну кількість працівників SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8
NULL	1	3
NULL	2	3
NULL	3	2

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

Знайти кількість працівників у кожному відділі (згрупувати працівників по - ідентифікатору відділів і порахувати кількість записів у кожній групі) - а також позначити додаткові рядки, що не існують у джерелі даних SELECT DepartmentID, COUNT(UserID) AS "Number of users" (DepartmentID) AS "Added row" FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID	Number of users	Added row
1	4	0
2	3	0
4	1	0
NULL	8	1

Ще одна команда угруповання COMPUTE дозволяє групувати дані та виводити за ними звіт у різні таблиці. Тобто команда GROUP BY з операторами ROLLUP та CUBE групує дані та дописує до таблиці додаткові рядки зі звітом, а команда COMPUTE групує дані, розриваючи вихідну таблицю на кілька підтаблиць, а також формує підтаблиці зі звітами. Команда COMPUTE може використовуватись у двох режимах:

• як проста функція агрегування, що виводить результат окрему таблицю;
• з параметром BY як команда угруповання, що розрізає таблицю на кілька підтаблиць

Команда COMPUTE з параметром BY може використовуватися лише спільно з командою ORDER BY, причому стовпці сортування повинні співпадати зі стовпцями угруповання.

Вивести таблицю користувачів компанії, а також порахувати їхню кількість SELECT * FROM Users COMPUTE COUNT(UserID)

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
3	Петр	Ivanov	1	2
4	Nikolay	Петров	1	3
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2
8	Viktor	Rybakov	4	1

cnt
8

-- Знайти кількість працівників у кожному відділі (згрупувати працівників за ідентифікатором відділів і порахувати кількість записів у кожній групі) SELECT * FROM Users ORDER BY

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
3	Петр	Ivanov	1	2
4	Nikolay	Петров	1	3

cnt
4

UserID	UserName	UserSurname	DepartmentID	PositionID
5	Nikolay	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2

cnt
3

UserID	UserName	UserSurname	DepartmentID	PositionID
8	Viktor	Rybakov	4	1

cnt
1

З'єднання таблиць

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

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

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

Знайти всіх користувачів з ім'ям Ivan і з'єднати результат з - результатом від запиту "Знайти всіх користувачів з прізвищем Petrov" - дублюючі записи виключити SELECT * FROM Users WHERE UserName = "Ivan"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
4	Nikolay	Петров	1	3

-- Знайти всіх користувачів з ім'ям Ivan і з'єднати результат із -- результатом від запиту "Знайти всіх користувачів з прізвищем Petrov" -- дублюючі записи зберегти SELECT * FROM Users WHERE UserName = "Ivan" UNION ALL SELECT * FROM Users WHERE UserSurname = " Петров"

UserID	UserName	UserSurname	DepartmentID	PositionID
1	Ivan	Петров	1	1
2	Ivan	Sidorov	1	2
1	Ivan	Петров	1	1
4	Nikolay	Петров	1	3

Горизонтальне з'єднання здійснюється шляхом зчеплення кількох таблиць за ключовими колонками. Найпростіше горизонтальне з'єднання виконується за допомогою команди INNER JOIN, яка зчіплює таблиці, вибираючи рядки ключового поля, яке зустрічається в обох таблицях.

SELECT<названия колонок или *>FROM<таблица_1>INNER JOIN таблиця_2 ON таблиця_1.ключове_поле = таблиця_2.ключове_поле

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

SELECT<названия колонок или *>FROM<таблица_1>LEFT JOIN таблиця_2 ON таблиця_1.ключове_поле = таблиця_2.ключове_поле

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

SELECT<названия колонок или *>FROM<таблица_1>RIGHT JOIN таблиця_2 ON таблиця_1.ключове_поле = таблиця_2.ключове_поле

Команда FULL JOIN поєднує в собі ліве та праве зчеплення, тобто вона з'єднує таблиці, вибираючи рядки з обох таблиць, а відсутні дані заповнюються значенням NULL.

SELECT<названия колонок или *>FROM<таблица_1>FULL JOIN таблиця_2 ON таблиця_1.ключове_поле = таблиця_2.ключове_поле

Остання і команда, що рідко використовується, з'єднання таблиць - це CROSS JOIN. Ця команда зчіплює таблиці без використання ключового поля, а результат - це комбінація з різних рядків вихідних таблиць.

SELECT<названия колонок или *>FROM<таблица_1>CROSS JOIN таблиця_2

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

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Петров	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Петр	Ivanov	1	2	1	Production
4	Nikolay	Петров	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution

SELECT * FROM Users LEFT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Петров	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Петр	Ivanov	1	2	1	Production
4	Nikolay	Петров	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users RIGHT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Петров	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Петр	Ivanov	1	2	1	Production
4	Nikolay	Петров	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing

SELECT * FROM Users FULL JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Петров	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Петр	Ivanov	1	2	1	Production
4	Nikolay	Петров	1	3	1	Production
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing
8	Viktor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users CROSS JOIN Departments

UserID	UserName	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Петров	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Петр	Ivanov	1	2	1	Production
4	Nikolay	Петров	1	3	1	Production
5	Nikolay	Ivanov	2	1	1	Production
6	Sergey	Sidorov	2	3	1	Production
7	Andrey	Bukin	2	2	1	Production
8	Viktor	Rybakov	4	1	1	Production
1	Ivan	Петров	1	1	2	Distribution
2	Ivan	Sidorov	1	2	2	Distribution
3	Петр	Ivanov	1	2	2	Distribution
4	Nikolay	Петров	1	3	2	Distribution
5	Nikolay	Ivanov	2	1	2	Distribution
6	Sergey	Sidorov	2	3	2	Distribution
7	Andrey	Bukin	2	2	2	Distribution
8	Viktor	Rybakov	4	1	2	Distribution
1	Ivan	Петров	1	1	3	Purchasing
2	Ivan	Sidorov	1	2	3	Purchasing
3	Петр	Ivanov	1	2	3	Purchasing
4	Nikolay	Петров	1	3	3	Purchasing
5	Nikolay	Ivanov	2	1	3	Purchasing
6	Sergey	Sidorov	2	3	3	Purchasing
7	Andrey	Bukin	2	2	3	Purchasing
8	Viktor	Rybakov	4	1	3	Purchasing

SELECT dpt.DepartmentName AS "Department", usr.UserName + " " + usr.UserSurname AS "User name", pos.PositionName AS "Position" FROM Users AS usr LEFT JOIN Departments AS dpt ON usr.DepartmentID = dpt.DepartmentID LEFT JOIN Positions AS pos ON usr.PositionID = pos.PositionID ORDER BY dpt.DepartmentID, pos.PositionID

Department	User name	Position
NULL	Viktor Rybakov	Manager
Production	Іван Петров	Manager
Production	Ivan Sidorov	Senior analyst
Production	Petr Ivanov	Senior analyst
Production	Nikolay Petrov	Analyst
Distribution	Nikolay Ivanov	Manager
Distribution	Andrey Bukin	Senior analyst
Distribution	Sergey Sidorov	Analyst

Зміна даних

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

За транзакції Transact-SQL відповідає структура BEGIN TRANSACTION ... COMMIТ TRANSACTION. Цю структуру використовувати необов'язково, але тоді всі команди сценарію є незворотними, тобто не можна зробити відкат до попереднього стану. Повна структура блоку транзакцій:

BEGIN TRANSACTION [ім'я транзакції] [операції] COMMIТ TRANSACTION [ім'я транзакції] або ROLLBACK TRANSACTION [ім'я транзакції]

Нижче наведено приклад використання цього блоку:

Встановити всім співробітникам новий оклад BEGIN TRANSACTION TR1 UPDATE Positions SET BaseSalary = 2500000000000000 IF @@ERROR<>0 BEGIN RAISERROR("Error, transaction no completed!",16,-1) ROLLBACK TRANSACTION TR1 END ELSE COMMIT TRANSACTION TR1

Для вставки даних у таблиці SQL-сервера використовується команда INSERT INTO:

INSERT INTO [назва таблиці] (колонки) VALUES ([значення колонок])

Друга частина комнади є необов'язковою для MS SQL Server 2003, але MS JET SQL без цього слова видаватиме помилку синтаксису. Вставка зазвичай проводиться цілорядно, тобто в кімнаті вказуються всі колонки таблиці і значення, які потрібно в них занести. Якщо колонка має значення за замовчуванням або дозволяє порожнє значення, то в команді вставки цю колонку можна не вказувати. Команда INSERT INTO також дозволяє вказувати внесені дані за порядку прямування колонок, але у разі необхідно позначити використовуваний порядок колонок.

У таблицю Users вставити рядок з даними UserID = 9, UserName = "Nikolay", -- UserSurname = "Gryzlov", DepartmentID = 4, PositionID = 2. INSERT INTO Users VALUES (9, "Nikolay", "Gryzlov", 4, 2) -- У таблицю Users вставити рядок з даними UserID = 10, UserName = "Nikolay", -- UserSurname = "Kozin", DepartmentID - значення за промовчанням, PositionID - не вказано. INSERT Users VALUES (10, "Nikolay", "Kozin", DEFAULT, NULL) -- У таблицю Users вставити рядок з даними UserName = "Angrey", UserSurname = "Medvedev", -- UserID = 11, інші значення за замовчуванням INSERT INTO Users (UserName, UserSurname, UserID) VALUES ("Angrey", "Medvedev", 11)

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

UPDATE [назва таблиці] SET [ім'я колонки]=[значення колонок] WHERE [умова]

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

Встановити всім посадам зарплату 2000 одиниць. UPDATE Positions SET BaseSalary = 2000 -- Посадам з ідентифікатором 1 встановити зарплату 2500 одиниць. UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1 -- Посад з ідентифікатором 2 зменшити зарплату на 30%. UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2 -- Встановити всім посадам зарплату, що дорівнює (30 000 поділити на кількість -- співробітників в організації) UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

Видалення даних здійснюється командою DELETE:

DELETE FROM [назва таблиці] WHERE [умова]

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

Видалити користувача з ідентифікатором 10 -- У режимі налагодження рекомендується використовувати команду SELECT , -- щоб знати, які дані будуть стерті: -- SELECT UserID FROM Users WHERE UserID = 10 DELETE FROM Users WHERE UserID = 10 -- Видалити всіх польувальників відділу Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID WHERE Departments.DepartmentName = "Production" -- Видалити всіх користувачів DELETE FROM Users

Примітка! У прикладі для фільтрації даних використано зчеплення таблиць. Хоча в команді перераховуються кілька таблиць, видалення даних буде зроблено лише з таблиці, яка вказана після слова DELETE.

Швидша команда для очищення таблиці – це TRUNCATE TABLE.

TRUNCATE TABLE [назва таблиці]

Приклад видалення всіх даних:

Очистити таблицю Users TRUNCATE TABLE Users

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

Створити тимчасову таблицю #TempTable, в яку скопіювати зміст - колонки UserName таблиці Users SELECT UserName INTO #TempTable FROM Users - Вибрати всі записи тимчасової таблиці #TempTable SELECT *

Збережені процедури та функції

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

• Численні клієнтські програми написані різними мовами або працюють на різних платформах, але повинні виконувати однакові операції з базами даних.
• Безпека відіграє першорядну роль. Збережені процедури використовуються для всіх стандартних операцій, що забезпечує сумісність та безпеку середовища, а процедури гарантують належну реєстрацію кожної операції. При такому типі установки програми та користувачі не отримують безпосередній доступ до таблиць бази даних і можуть виконувати лише конкретні процедури, що зберігаються.
• Необхідно знизити мережний трафік між клієнтом та сервером. Обсяг інформації, що пересилається між сервером і клієнтом істотно знижується, але збільшується навантаження на систему сервера баз даних, так як в цьому випадку на стороні сервера виконується більша частина роботи з обробки даних.

Приклад створення процедури, що зберігається, і функції, що зберігається:

Створення функції оновлення зарплат CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000 GO -- Створення функції отримання імені користувача CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255) + " " + UserSurname FROM Users WHERE UserID = @UserID) END GO -- Оновлення зарплат EXEC TestDatabase.dbo.usp_UpdateSalary -- Отримання імені користувача з ідентифікатором 2 SELECT TestDatabase.dbo.usf_GetName(2)

Отже, процедури і функції, що зберігаються, дають наступні переваги:

• продуктивність;
• загальна логіка для всіх запитів;
• зменшення трафіку;
• безпека – доступ користувачеві дається не до таблиці, а до процедури;

Продуктивність

Для збільшення продуктивності, тобто для швидкого виконання запитів слід пам'ятати деякі правила складання рядків запитів:

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

Віталій Бочкарьов

Мова Т-SQL призначена для управління наборами даних. З цієї причини він не має деяких характерних рис традиційних мов, які необхідні для програмування додатків. Якщо ви вже давно займаєтеся створенням додатків, то, напевно, протиставте мислення програмування в Т-SQL та інших мовах, таких як VB, C# і Java.

Пакети T-SQL

Запитом називають одну інструкцію Т-SQL, а пакетом – їх набір. Вся послідовність інструкцій пакета надсилається серверу з клієнтських програм як одна цілісна одиниця.

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

Переривання виконання пакету

Файл сценарію SQL та вікно аналізатора запитів (Query Analyzer) може містити кілька пакетів. У разі всі пакети поділяють ключові слова термінаторів. За замовчуванням цим ключовим словом є GO, і воно має бути єдиним у рядку. Всі інші символи (навіть коментарі) нейтралізують роздільник пакету.

Розділювач пакетів насправді є функцією Management Studio, а не сервера. Його можна змінити на сторінці Query Execution діалогового вікна властивостей програми, але я не рекомендував би це робити (принаймні друзям).

Інструкції DDL

Деякі інструкції DDL мови Т-SQL, такі як Create Procedure, повинні бути першими інструкціями пакета. Дуже довгі сценарії, які створюють безліч об'єктів, часто потребують кількох розділювачів пакетів. Оскільки SQL Server окремо розбирає синтаксис за пакетами, така наявність безлічі роздільників допомагає локалізувати помилки.

Перемикання між базами даних

В інтерактивному режимі поточна база даних завжди відображається на панелі інструментів і може бути змінена будь-якої миті. У програмному коді поточна база визначається ключовим словом USE. Це ключове слово в пакеті вказує, з якою базою даних буде виконуватися робота, починаючи з поточної точки:

Виконання пакетів

Пакет може бути виконаний кількома способами.

Сценарій SQL у повному обсязі (тобто всі пакети, що входять до нього) може бути виконаний шляхом відкриття файлу. sql у редакторі SQL утиліти Manage ment Stu dio та натискання клавіші (або натискання кнопки! Execute панелі інструментів, або вибору в меню пункту Query 1 ^Execute). (Я налаштував свою операційну систему Windows так, щоб при подвійному клацанні на файлі. SQL автоматично запускався аналізатор запитів.)

У редакторі SQL утиліти Management Studio можуть бути виконані окремі інструкції SQL. Для цього їх потрібно виділити та натиснути клавішу (або клацнути на кнопці! Execute панелі інструментів або вибрати в меню пункт Query 1 ^Execute).

У програмі пакет Т-SQL можна виконати за допомогою ADO або ODBC.

Про Сценарій Т-SQL може бути виконаний за допомогою утиліти командного рядка SQLCmd з передачею імені файлу. SQL як параметр.

Утиліта SQLCmd має кілька параметрів і може бути легко налаштована практично для будь-яких потреб.

Додаткові відомості про утиліту SQLCmd див. у розділі 6, присвяченому Management Studio.

інформація

Виконання процедури, що зберігається

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

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

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

ЄХЕС sp_help;

У цьому розділі ми розглянули лише використання команди ехес у пакеті. Докладнішу інформацію про творче використання ключового слова єхес міститься в розділі “Динамічний SQL”.

Форматування у T-SQL

Протягом усієї цієї книги програмний код відформатовано для покращення наочності; У цьому розділі ми розглянемо ключові моменти форматування.

Завершення інструкції

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

Не розміщуйте її після оператора try end.

Не розміщуйте її після умови if.

Обов'язково розміщуйте її після загальнотабличних виразів СТЕ.

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

Продовження рядків

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

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

Коментарі

Мова Т-SQL допускає використання в одному пакеті коментарів двох стилів: ANCI і мови С. Перший починається з двох дефісів і закінчується в кінці рядка:

- Це коментар стилю ANSI

Також коментарі стилю ANSI можуть вставлятися наприкінці рядка інструкції:

Select FirstName, LastName – стовпці FROM Persons, що виймаються – вихідна таблиця

Where LastName Like ‘Hal%”; — обмеження на рядки

Редактор SQL може застосовувати та видаляти коментарі у всіх виділених рядках. Для цього потрібно вибрати команду меню Edit^Advanced^Comment Out ( або ) або Edit ^ Advanced 1 ^ Remove Comments ( або ).

Коментарі стилю мови С починаються з косої риси та зірочки (/*) і закінчуються тими самими символами у зворотній послідовності. Цей тип коментарів краще використовувати для коментування блоків рядків, як-от заголовки або великі тестові запити. /*

Тригер вставки таблиці Order Пол Нільсен

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

Налагодження T-SQL

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

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

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

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

(1 row(s) affected)

Іноді корисно призупиняти виконання програми, щоб побачити блокування чи вміст об'єктів. Команда pause дає змогу призупинити виконання пакета на заданий час. Наприклад, при виконанні наступного коду його другий рядок виводу відобразиться після двосекундної паузи:

Print 1 Початок";

waitfor delay *00:00:02′;

Print 'Кінець 1;

Результат виконання коду:

Ключовим моментом є те, що в утиліту Management Studio версії SQL Новинка Server 2005 не включений відладчик мови Т-SQL, він присутній у пакеті

2005 Visual Studio 2005. Якщо в майбутньому відладчик буде включений в будь-який пакет

оновлень, я повідомлю про це на сайті www. SQLServerBible. com.

Змінні

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

Значення за замовчуванням та область визначення змінних

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

У наступному сценарії створюються дві тестові змінні, при цьому продемонстровані їх область визначення та значення за замовчуванням. Весь сценарій є одним виконуваним файлом, хоча з технічного погляду складається з двох пакетів (розділених командою GO). Відразу після сценарію продемонстровано три його інструкції SELECT:

DECLARE @Test INT,

@TestTwo NVARCHAR(25);

SELECT @ Test, @ Test Two;

SET @ TestTwo = 'значення";

SELECT @ Test, @ Test Two ;

SELECT @Test as BatchTwo, @TestTwo;

(1 row(s) affected)

1 значення

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable “@Test”.

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

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

Використання команд set та select

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

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

Declare ©TempID INT,

@TempLastName VARCHAR(25);

SET @ TempID = 99;

@TempID = PersonID,

@TempLastName = LastName

ORDER BY PersonID;

Результат виконання пакету:

32 @code останній:Campbell

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

Якщо інструкція SELECT не повертає жодного рядка, то на змінні не впливає. Наступний запит не повертає значень, оскільки запису з ідентифікатором 100 у таблиці Person не існує. З цієї причини змінній @TempIDvariable надається значення останнього існуючого рядка, при цьому змінна прізвища зберігає початкове порожнє значення:

Declare @TempID INT,

@TempLastName VARCHAR(25);

SET @ TempID = 99;

SELECT @TempID = PersonID,

@TempLastName = LastName FROM Person WHERE PersonID = 100 ORDER BY PersonID;

SELECT @ TempID, @ TempLastName;

99 @code last:NULL

Умовний відбір

Наступна інструкція SELECT містить пропозицію WHERE і її синтаксис правильний, хоча для деяких може виглядати незвично:

SELECT @ змінна = вираз WHERE булево_вираз;

У разі пропозиція WHERE функціонує як умовний оператор if. Якщо булеве вираз істинно, то змінної присвоюється значення, інакше інструкція SELECT однаково виконується, але значення змінної не змінюється.

Використання змінних у запитах SQL

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

Скрізь, де у запиті може використовуватися вираз, може використовуватись і змінна. У наступному прикладі продемонстровано використання змінної у пропозиції WHERE: USE OBXKites;

DECLARE @ProductCode CHAR(10);

SET @Code = '1001';

SELECT ProductName FROM Product

WHERE Code = @ProductCode;

Буде отримано наступний результат:

Basic Box Kite 21 inch

Змінні з множинним присвоєнням

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

У цьому розділі буде продемонстровано низку прикладів із реального життя. Оскільки це дещо незвичайне використання інструкції SELECT, дозвольте навести її базову форму:

SELECT @ змінна = @ змінна + d. стовпець FROM (керована_таблиця) as d;

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

Цей тип вилучення даних часто використовується практично. Іноді вертикальний список значень краще перетворити на розділений комами горизонтальний список, розтягуючи підзаголовок на кілька дюймів. Короткі горизонтальні списки легко сприймаються людиною, а також економлять простір звіту.

У наступному прикладі створюється список дат подій туру Outer Banks Lighthouses, що пропонується у навчальній базі даних Cape Hatter as Ad ventures:

@EventDates VARCHAR(1024);

SET @EventDates = '';

SELECT ©EventDates = @EventDates + CONVERT(VARCHAR(15), a.d,107) + ‘

FROM (select DateBegin as [d] from Event join Tour

на Event.TourID = Tour.TourlD WHERE Tour. = 'Outer Banks Lighthouses') as a;

SELECT Left(@EventDates, Len(@EventDates)-1)

AS 'Outer Banks Lighthouses Events";

Результат виконання пакету:

Outer Banks Lighthouses Events

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

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

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

• SQL,
• Microsoft SQL Server

• Tutorial

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Трохи теорії

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оновлення поля ID ALTER TABLE Employees ALTER COLUMN nvarchar(30) NOT NULL

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

Створення таблиці CREATE TABLE Employees(ID int, -- в ORACLE тип int - це еквівалент(обгортка) для number(38) Name nvarchar2(30), -- nvarchar2 в ORACLE еквівалентний nvarchar в MS SQL Birthday date, Email 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, Birthday date, 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 таблиці Employees INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- відкидаємо записи, у яких позиція не вказана
Те саме проробимо для таблиці Departments:

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

SELECT * FROM Positions

SELECT * FROM Departments

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM Employees

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

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

SELECT * FROM Employees

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

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

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

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

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

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

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

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

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

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

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

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees 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

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_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

Як можна побачити на скріншоті вище, при створенні обмежень 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 ), 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