Как заполнить таблицу в sql server

О чем данный учебник

  • DDL – Data Definition Language (язык описания данных)
  • DML – Data Manipulation Language (язык манипулирования данными), который содержит следующие конструкции:
    SELECT – выборка данныхINSERT – вставка новых данныхUPDATE – обновление данныхDELETE – удаление данныхMERGE – слияние данных
  • 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/Новый запрос»:

Как заполнить таблицу в sql server

Для смены текущей базы данных можно использовать выпадающий список:

Как заполнить таблицу в sql server

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

Как заполнить таблицу в sql server

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

Как заполнить таблицу в sql server

Собственно, это все, что нам необходимо будет знать для выполнения приведенных здесь примеров. Остальное по утилите SSMS несложно изучить самостоятельно.

Немного теории

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

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

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

Таблица – это главный объект РБД, все данные РБД хранятся построчно в столбцах таблицы. Строки, записи – тоже синонимы.

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

Для справки – в БД ORACLE наименования объектов могут иметь максимальную длину 30 символов. Поэтому для конкретной БД нужно вырабатывать свои правила для наименования объектов, чтобы уложиться в лимит по количеству символов.

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

DDL и DML — подмножество языка SQL:

  • Язык DDL служит для создания и модификации структуры БД, т.е. для создания/изменения/удаления таблиц и связей.
  • Язык DML позволяет осуществлять манипуляции с данными таблиц, т.е. с ее строками. Он позволяет делать выборку данных из таблиц, добавлять новые данные в таблицы, а так же обновлять и удалять существующие данные.

В языке SQL можно использовать 2 вида комментариев (однострочный и многострочный):

Собственно, все для теории этого будет достаточно.

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

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

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

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)

Где «PK_Employees» это имя ограничения, отвечающего за первичный ключ. Обычно для наименования первичного ключа используется префикс «PK_» после которого идет имя таблицы.

Если первичный ключ состоит из нескольких полей, то эти поля необходимо перечислить в скобках через запятую:

Стоит отметить, что в MS SQL все поля, которые входят в первичный ключ, должны иметь характеристику NOT NULL.

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

DROP TABLE Employees

А затем создадим ее, используя следующий синтаксис:

CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar(30) NOT NULL,
Birthday date,
Email nvarchar(30),
Position nvarchar(30),
Department nvarchar(30),
CONSTRAINT PK_Employees PRIMARY KEY(ID) — описываем PK после всех полей, как ограничение
)

После создания зальем в таблицу данные:

INSERT Employees(ID,Position,Department,Name) VALUES
(1000,N’Директор’,N’Администрация’,N’Иванов И. ‘),
(1001,N’Программист’,N’ИТ’,N’Петров П. ‘),
(1002,N’Бухгалтер’,N’Бухгалтерия’,N’Сидоров С. ‘),
(1003,N’Старший программист’,N’ИТ’,N’Андреев А

Если первичный ключ в таблице состоит только из значений одного столбца, то можно использовать следующий синтаксис:

CREATE TABLE Employees(
ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, — указываем как характеристику поля
Name nvarchar(30) NOT NULL,
Birthday date,
Email nvarchar(30),
Position nvarchar(30),
Department nvarchar(30)
)

На самом деле имя ограничения можно и не задавать, в этом случае ему будет присвоено системное имя (наподобие «PK__Employee__3214EC278DA42077»):

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

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

Но я бы рекомендовал для постоянных таблиц всегда явно задавать имя ограничения, т. по явно заданному и понятному имени с ним впоследствии будет легче проводить манипуляции, например, можно произвести его удаление:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees

Но такой краткий синтаксис, без указания имен ограничений, удобно применять при создании временных таблиц БД (имя временной таблицы начинается с # или ##), которые после использования будут удалены.

Подытожим

На данный момент мы рассмотрели следующие команды:

  • CREATE TABLE имя_таблицы (перечисление полей и их типов, ограничений) – служит для создания новой таблицы в текущей БД;
  • DROP TABLE имя_таблицы – служит для удаления таблицы из текущей БД;
  • ALTER TABLE имя_таблицы 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 ID,Name
INTO #Temp
FROM Employees

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

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

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

Второй недостаток заключается в объеме хранения данной информации и ее дублированием, т. для каждого сотрудника указывается полное наименование отдела, что требует в БД места для хранения каждого символа из названия отдела.

Третий недостаток – сложность обновления данных полей, в случае если изменится название какой-то должности, например, если потребуется переименовать должность «Программист», на «Младший программист». В данном случае нам придется вносить изменения в каждую строчку таблицы, у которой Должность равняется «Программист».

Чтобы избежать данных недостатков и применяется, так называемая, нормализация базы данных – дробление ее на подтаблицы, таблицы справочники. Не обязательно лезть в дебри теории и изучать что из себя представляют нормальные формы, достаточно понимать суть нормализации.

Давайте создадим 2 таблицы справочники «Должности» и «Отделы», первую назовем Positions, а вторую соответственно Departments:

CREATE TABLE Positions(
ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY,
Name nvarchar(30) NOT NULL
)

CREATE TABLE Departments(
ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY,
Name nvarchar(30) NOT NULL
)

Заметим, что здесь мы использовали новую опцию IDENTITY, которая говорит о том, что данные в столбце ID будут нумероваться автоматически, начиная с 1, с шагом 1, т. при добавлении новых записей им последовательно будут присваиваться значения 1, 2, 3, и т. Такие поля обычно называют автоинкрементными. В таблице может быть определено только одно поле со свойством IDENTITY и обычно, но необязательно, такое поле является первичным ключом для данной таблицы.

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

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

— заполняем поле Name таблицы Positions, уникальными значениями из поля Position таблицы Employees
INSERT Positions(Name)
SELECT DISTINCT Position
FROM Employees
WHERE Position IS NOT NULL — отбрасываем записи у которых позиция не указана

То же самое проделаем для таблицы Departments:

INSERT Departments(Name)
SELECT DISTINCT Department
FROM Employees
WHERE Department IS NOT NULL

Если теперь мы откроем таблицы Positions и Departments, то увидим пронумерованный набор значений по полю ID:

SELECT * FROM Positions

SELECT * FROM Departments

Данные таблицы теперь и будут играть роль справочников для задания должностей и отделов. Теперь мы будем ссылаться на идентификаторы должностей и отделов. В первую очередь создадим новые поля в таблице Employees для хранения данных идентификаторов:

— добавляем поле для ID должности
ALTER TABLE Employees ADD PositionID int
— добавляем поле для ID отдела
ALTER TABLE Employees ADD DepartmentID int

Тип ссылочных полей должен быть каким же, как и в справочниках, в данном случае это int.

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

ALTER TABLE Employees ADD PositionID int, DepartmentID int

Теперь пропишем ссылки (ссылочные ограничения — FOREIGN KEY) для этих полей, для того чтобы пользователь не имел возможности записать в данные поля, значения, отсутствующие среди значений ID находящихся в справочниках.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID
FOREIGN KEY(PositionID) REFERENCES Positions(ID)

И то же самое сделаем для второго поля:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID
FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)

Теперь пользователь в данные поля сможет занести только значения ID из соответствующего справочника. Соответственно, чтобы использовать новый отдел или должность, он первым делом должен будет добавить новую запись в соответствующий справочник. должности и отделы теперь хранятся в справочниках в одном единственном экземпляре, то чтобы изменить название, достаточно изменить его только в справочнике.

Имя ссылочного ограничения, обычно является составным, оно состоит из префикса «FK_», затем идет имя таблицы и после знака подчеркивания идет имя поля, которое ссылается на идентификатор таблицы-справочника.

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

Так же в некоторых случаях ссылку можно организовать по нескольким полям:

Собственно, теперь обновим поля 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

IDNameBirthdayEmailPositionDepartmentPositionIDDepartmentID
1000Иванов И. NULLNULLДиректорАдминистрация21
1001Петров П. NULLNULLПрограммистИТ33
1002Сидоров С. NULLNULLБухгалтерБухгалтерия12
1003Андреев А. NULLNULLСтарший программистИТ43

Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице Employees теперь нет, можно удалить эти поля:

ALTER TABLE Employees DROP COLUMN Position,Department

Теперь таблица у нас приобрела следующий вид:

IDNameBirthdayEmailPositionIDDepartmentID
1000Иванов И. NULLNULL21
1001Петров П. NULLNULL33
1002Сидоров С. NULLNULL12
1003Андреев А. NULLNULL43

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

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

IDNamePositionNameDepartmentName
1000Иванов И. ДиректорАдминистрация
1001Петров П. ПрограммистИТ
1002Сидоров С. БухгалтерБухгалтерия
1003Андреев А. Старший программистИТ

В инспекторе объектов мы можем увидеть все объекты, созданные для в данной таблицы. Отсюда же можно производить разные манипуляции с данными объектами – например, переименовывать или удалять объекты.

Как заполнить таблицу в sql server

Так же стоит отметить, что таблица может ссылаться сама на себя, т. можно создать рекурсивную ссылку. Для примера добавим в нашу таблицу с сотрудниками еще одно поле 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)

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

Как заполнить таблицу в sql server

Как заполнить таблицу в sql server

В результате мы должны увидеть следующую картину (таблица Employees связана с таблицами Positions и Depertments, а так же ссылается сама на себя):

Как заполнить таблицу в sql server

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

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

DROP TABLE Employees

CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar(30),
Birthday date,
Email nvarchar(30),
PositionID int,
DepartmentID int,
ManagerID int,
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
ON DELETE CASCADE,
CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),
CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
)

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

Удалим отдел с идентификатором 3 из таблицы Departments:

Посмотрим на данные таблицы Employees:

IDNameBirthdayEmailPositionIDDepartmentIDManagerID
1000Иванов И. 1955-02-19NULL21NULL
1002Сидоров С. 1976-06-07NULL121000

Как видим, данные по отделу 3 из таблицы Employees так же удалились.

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

UPDATE Departments
SET
ID=30
WHERE ID=3

Главное понять суть этих 2-х опций ON DELETE CASCADE и ON UPDATE CASCADE. Я применяю эти опции очень в редких случаях и рекомендую хорошо подумать, прежде чем указывать их в ссылочном ограничении, т. при нечаянном удалении записи из таблицы справочника это может привести к большим проблемам и создать цепную реакцию.

Восстановим отдел 3:

— даем разрешение на добавление/изменение IDENTITY значения
SET IDENTITY_INSERT Departments ON

INSERT Departments(ID,Name) VALUES(3,N’ИТ’)

— запрещаем добавление/изменение IDENTITY значения
SET IDENTITY_INSERT Departments OFF

Полностью очистим таблицу Employees при помощи команды TRUNCATE TABLE:

TRUNCATE TABLE Employees

И снова перезальем в нее данные используя предыдущую команду INSERT:

На данным момент к нашим знаниям добавилось еще несколько команд DDL:

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

Прочие ограничения – UNIQUE, DEFAULT, CHECK

При помощи ограничения UNIQUE можно сказать что значения для каждой строки в данном поле или в наборе полей должно быть уникальным. В случае таблицы Employees, такое ограничение мы можем наложить на поле Email. Только предварительно заполним Email значениями, если они еще не определены:

А теперь можно наложить на это поле ограничение-уникальности:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)

Теперь пользователь не сможет внести один и тот же E-Mail у нескольких сотрудников.

Ограничение уникальности обычно именуется следующим образом – сначала идет префикс «UQ_», далее название таблицы и после знака подчеркивания идет имя поля, на которое накладывается данное ограничение.

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

При помощи добавления к полю ограничения DEFAULT мы можем задать значение по умолчанию, которое будет подставляться в случае, если при вставке новой записи данное поле не будет перечислено в списке полей команды INSERT. Данное ограничение можно задать непосредственно при создании таблицы.

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 в перечне добавляемых значений:

Посмотрим, что получилось:

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

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)

Ограничение обычно именуется так же, сначала идет префикс «CK_», затем имя таблицы и имя поля, на которое наложено это ограничение.

Попробуем вставить недопустимую запись для проверки, что ограничение работает (мы должны получить соответствующую ошибку):

А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:

Можно так же создать ограничения UNIQUE и CHECK без указания имени:

ALTER TABLE Employees ADD UNIQUE(Email)
ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)

Но это не очень хорошая практика и лучше задавать имя ограничения в явном виде, т. чтобы разобраться потом, что будет сложнее, нужно будет открывать объект и смотреть, за что он отвечает.

Как заполнить таблицу в sql server

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

И, соответственно, все эти ограничения можно создать сразу же при создании таблицы, если ее еще нет. Удалим таблицу:

И пересоздадим ее со всеми созданными ограничениями одной командой CREATE TABLE:

CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar(30),
Birthday date,
Email nvarchar(30),
PositionID int,
DepartmentID int,
HireDate date NOT NULL DEFAULT SYSDATETIME(), — для DEFAULT я сделаю исключение
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID),
CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),
CONSTRAINT UQ_Employees_Email UNIQUE (Email),
CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999)
)

Напоследок вставим в таблицу наших сотрудников:

Немного про индексы, создаваемые при создании ограничений PRIMARY KEY и UNIQUE

Как можно увидеть на скриншоте выше, при создании ограничений PRIMARY KEY и UNIQUE автоматически создались индексы с такими же названиями (PK_Employees и UQ_Employees_Email). По умолчанию индекс для первичного ключа создается как CLUSTERED, а для всех остальных индексов как NONCLUSTERED. Стоит сказать, что понятие кластерного индекса есть не во всех СУБД. Таблица может иметь только один кластерный (CLUSTERED) индекс. CLUSTERED – означает, что записи таблицы будут сортироваться по этому индексу, так же можно сказать, что этот индекс имеет непосредственный доступ ко всем данным таблицы. Это так сказать главный индекс таблицы. Если сказать еще грубее, то это индекс, прикрученный к таблице. Кластерный индекс – это очень мощное средство, которое может помочь при оптимизации запросов, пока просто запомним это. Если мы хотим сказать, чтобы кластерный индекс использовался не в первичном ключе, а для другого индекса, то при создании первичного ключа мы должны указать опцию NONCLUSTERED:

Для примера сделаем индекс ограничения 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:

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

Но в данном случае это всего лишь пример, который показывает суть кластерного индекса, т. скорее всего к таблице Employees будут делаться запросы по полю ID и в каких-то случаях, возможно, она сама будет выступать в роли справочника.

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

Кластерный индекс выгодно применять к полям, по которым выборка идет наиболее часто.

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

  • 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.

Для примера снова удалим таблицу:

И пересоздадим ее со всеми созданными ограничениями и индексами одной командой CREATE TABLE:

CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar(30),
Birthday date,
Email nvarchar(30),
PositionID int,
DepartmentID int,
HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(),
ManagerID int,
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID),
CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID),
CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID),
CONSTRAINT UQ_Employees_Email UNIQUE(Email),
CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999),
INDEX IDX_Employees_Name(Name)
)

Дополнительно стоит отметить, что в некластерный индекс можно включать значения при помощи указания их в INCLUDE. в данном случае INCLUDE-индекс чем-то будет напоминать кластерный индекс, только теперь не индекс прикручен к таблице, а необходимые значения прикручены к индексу. Соответственно, такие индексы могут очень повысить производительность запросов на выборку (SELECT), если все перечисленные поля имеются в индексе, то возможно обращений к таблице вообще не понадобится. Но это естественно повышает размер индекса, т. значения перечисленных полей дублируются в индексе.

Вырезка из MSDN. Общий синтаксис команды для создания индексов

Индексы могут повысить скорость выборки данных (SELECT), но индексы уменьшают скорость модификации данных таблицы, т. после каждой модификации системе будет необходимо перестроить все индексы для конкретной таблицы.

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

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др. ) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

  • диспетчер запросов (Query Dispatcher)
  • движок оптимизации (Optimization Engines)
  • классический движок запросов (Classic Query Engine)
  • движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

Команды SQL

Стандартными командами для взаимодействия с РБД являются CREATE, SELECT, INSERT, UPDATE, DELETE и DROP. Эти команды могут быть классифицированы следующим образом:

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

  • NOT NULL — колонка не может иметь нулевое значение
  • DEFAULT — значение колонки по умолчанию
  • UNIQUE — все значения колонки должны быть уникальными
  • PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
  • FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
  • CHECK — все значения в колонке должны удовлетворять определенному условию
  • INDEX — быстрая запись и извлечение данных

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

  • целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
  • целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
  • целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены

Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:

  • предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
  • обеспечение «оправданной» связи между данными

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

Синтаксис SQL

Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW и т. и заканчиваться точкой с запятой (;) (точка с запятой не входит в синтаксис SQL, но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т. SELECT, select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL, где учитывается регистр в названии таблицы.

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Дата и время

Строковые символьные (юникод)

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10, а b — 20.

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

Существуют различные типы выражений: логические, числовые и выражения для работы с датами.

Логические выражения извлекают данные на основе совпадения с единичным значением.

SELECT col1, col2,. colN
FROM tableName
WHERE выражение для поиска совпадения с единичным значением;

Выполняем поиск активных пользователей:

Используются для выполнения арифметических операций в запросе.

Простой пример использования числового выражения:

SELECT (10 + 5) AS addition;

Существует несколько встроенных функций, таких как count(), sum(), avg(), min(), max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

  • AVG — вычисляет среднее значение
  • SUM — вычисляет сумму значений
  • MIN — вычисляет наименьшее значение
  • MAX — вычисляет наибольшее значение
  • COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

  • CONCAT — объединение строк
  • LENGTH — возвращает количество символов в строке
  • TRIM — удаляет пробелы в начале и конце строки
  • SUBSTRING — извлекает подстроку из строки
  • REPLACE — заменяет подстроку в строке
  • LOWER — переводит символы строки в нижний регистр
  • UPPER — переводит символы строки в верхний регистр и т.д.
  • ROUND — округляет число
  • TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
  • CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
  • FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
  • POWER — возводит число в указанную степень
  • SQRT — возвращает квадратный корень числа
  • RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Эти выражения, как правило, возвращают текущую дату и время.

CURRENT_TIMESTAMP — это и выражение, и функция (CURRENT_TIMESTAMP()). Другая функция для получения текущей даты и времени — NOW().

Другие функции для получения текущей даты и времени:

  • CURDATE/CURRENT_DATE — возвращает текущую дату
  • CURTIME/CURRENT_TIME — возвращает текущее время и т.д.

Функции для разбора даты и времени:

  • DAYOFMONTH(date) — возвращает день месяца в виде числа
  • DAYOFWEEK(date) — возвращает день недели в виде числа
  • DAYOFYEAR(date) — возвращает номер дня в году
  • MONTH(date) — возвращает месяц
  • YEAR(date) — возвращает год
  • LAST_DAY(date) — возвращает последний день месяца в виде даты
  • HOUR(time) — возвращает час
  • MINUTE(time) — возвращает минуты
  • SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

  • DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
  • DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
  • DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
  • TO_DAYS(date) — возвращает количество дней с 0-го дня года
  • TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format), соответственно.

Создание БД

Для создания БД используется инструкция CREATE DATABASE.

CREATE DATABASE dbName;
— или
CREATE DATABASE IF NOT EXISTS dbName;

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Создаем БД testDB:

CREATE DATABASE testDB;

Получаем список БД:

Удаление БД

Для удаления БД используется инструкция DROP DATABASE.

DROP DATABASE dbName;
— или
DROP DATABASE IF EXISTS dbName;

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

DROP DATABASE testDB;

Обратите внимание: при удалении БД уничтожаются все данные, которые в ней хранятся, так что будьте предельно внимательны при использовании данной команды.

Проверяем, что БД удалена:

Для получения списка таблиц используется инструкция SHOW TABLES.

Создание таблицы

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE.

CREATE TABLE tableName (
col1 datatype,
col2 datatype,. colN datatype,
PRIMARY KEY (хотя бы одна колонка)
);

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT.

Проверяем, что таблица была создана:

Удаление таблицы

Для удаления таблицы используется инструкция DROP TABLE.

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

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO.

INSERT INTO tableName (col1, col2,. colN)
VALUES (val1, val2,. valN);

Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.

INSERT INTO tableName VALUES (val1, val2,. valN);

Во избежание ошибок, рекомендуется всегда перечислять названия колонок.

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

Также, как было отмечено, при добавлении строки названия полей можно опускать:

Заполнение таблицы с помощью другой таблицы

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT. Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

SELECT col1, col2,. colN
FROM tableName;

Для выборки всех полей используется такой синтаксис:

SELECT * FROM tableName;

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT, UPDATE, DELETE и другими инструкциями.

SELECT col1, col2,. col2
FROM tableName
WHERE condition;

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа >, <, =, NOT, LIKE и т.

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки (»), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

SELECT col1, col2,. colN
FROM tableName
WHERE condition1 AND condition2. AND conditionN;

Возвращаемые записи должны удовлетворять всем указанным условиям.

SELECT col1, col2,. colN
FROM tableName
WHERE condition1 OR condition2. OR conditionN;

Возвращаемые записи должны удовлетворять хотя бы одному условию.

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

Обновление полей

Для обновления полей используется инструкция UPDATE. SET. Эта инструкция, обычно, используется в сочетании с предложением WHERE.

Обновим возраст пользователя с именем Igor:

Если в данном случае опустить WHERE, то будет обновлен возраст всех пользователей.

Удаление записей

Для удаления записей используется инструкция DELETE. Эта инструкция также, как правило, используется в сочетании с предложением WHERE.

Удалим неактивных пользователей:

Предложения LIKE и REGEX

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

  • проценты (%)
  • нижнее подчеркивание (_)

% означает 0, 1 или более символов. _ означает точно 1 символ.

SELECT col1, col2,. colN FROM tableName
WHERE col LIKE ‘xxx%’
— или
WHERE col LIKE ‘%xxx%’
— или
WHERE col LIKE ‘%xxx’
— или
WHERE col LIKE ‘xxx_’
— и т.

Сделаем выборку неактивных пользователей:

Сделаем выборку пользователей 30 лет и старше:

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

SELECT col1, col2,. colN FROM tableName
WHERE colName REGEXP регулярное выражение;

В регулярное выражении могут использоваться следующие специальные символы:

  • ^ — начало строки
  • $ — конец строки
  • . — любой символ

Сделаем выборку пользователей с именами Igor и Vika:

Предложение TOP/LIMIT/ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

Предложения ORDER BY и GROUP BY

Предложение ORDER BY используется для сортировки данных по возрастанию (ASC) или убыванию (DESC). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY.

SELECT col1, col2,. colN
FROM tableName
WHERE condition
GROUP BY col1, col2,. colN
ORDER BY col1, col2,. colN;

Сгруппируем активных пользователей по городам:

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

При соединении таблиц могут использоваться такие операторы, как =, <, >, <>, <=, >=, !=, BETWEEN, LIKE и NOT, однако наиболее распространенным является =.

Существуют разные типы объединений:

  • INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
  • LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
  • RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
  • FULL JOIN — возвращает все записи объединяемых таблиц
  • CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
  • SELF JOIN — используется для объединения таблицы с самой собой

Предложение UNION

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT. При этом, возвращаются только уникальные записи.

В случае с UNION, каждая инструкция SELECT должна иметь:

  • одинаковый набор колонок для выборки
  • одинаковое количество выражений
  • одинаковые типы данных колонок и
  • одинаковый порядок колонок

Однако, они могут быть разной длины.

Предложение UNION ALL

Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT. При этом, возвращаются все записи, включая дубликаты.

Существует еще два предложения, похожих на UNION:

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.