Изучите SQL Server Management Studio  — Часть 3. Операции CRUD, первичные и внешние ключи

Набор навыков, который сделает вас веселее на вечеринках! Шаг за шагом.

В последних сериях…

Здравствуйте, и добро пожаловать в нашу третью часть серии руководств по SQL и SQL Server Studio. Цель? Чтобы вы были знакомы и чувствовали себя комфортно с инструментом и языком. — Почему это вообще имеет значение? Я вижу, ты спрашиваешь. Что ж, оказывается, любопытство и второстепенные проекты часто мешают тому, чтобы вас выбрали в новых проектах или даже наняли на новую работу. Тот факт, что вы уже использовали такой важный инструмент, как SQL Server Studio, и написали несколько SQL-запросов, может и даст вам четкое преимущество.

Если вы пропустили Эпизод 1 о том, как настроить нашу среду и локальный сервер, перейдите к этой статье:

Если вы пропустили Эпизод 2, посвященный созданию баз данных и таблиц с помощью инструмента проектирования или написанию запросов, а также важности соглашения об именах, перейдите к этой статье, не забудьте вернуться 😉.

Что ожидать?

Сегодня мы рассмотрим операции CRUD, общее табличное выражение WITH, первичные и внешние ключи и то, как построить нашу первую простую табличную связь. Мы рассмотрим два способа удаления записей, используя общие табличные выражения и операторы удаления. Также будут конкретные примеры, несколько полезных ярлыков и мем. Давайте прыгать прямо в!

Создание вашей первой записи

В начале не было ничего. Потом наступил свет, первая запись. Создание или добавление новой записи — это C операций CRUD.

1. Мы предполагаем, что запустили SQL Server Studio и подключились к нашему экземпляру нашего локального сервера.
2. Давайте откроем окно запроса, это можно сделать либо:

• CTRL+Н
• Щелкните правой кнопкой мыши имя базы данных или имя таблицы, затем «новый запрос».

3. Структура для создания новой записи в таблице:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

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

Например, попытка использовать строковое значение для Department_ID возвратит ошибку. Посмотреть на себя:

Так что вместо этого напишем это, советуется написать самому, а не копипастить, работать на память пальцами 😉

INSERT INTO HR_data.dbo.Departments
VALUES (1, 'Finance', '[email protected]');

Поскольку Department_Name и Department_Email являются строками, мы помещаем эту информацию в одинарные кавычки.

4. Давайте проверим результаты, запустив

SELECT * FROM HR_data.dbo.Departments

Это сработало. Мы «читали» или запрашивали нашу таблицу, R из аббревиатуры CRUD. Два, чтобы идти. Добавим еще несколько записей.

5. Можем запустить несколько подряд:

INSERT INTO HR_data.dbo.Departments
VALUES 
(2, 'HR', '[email protected]'),
(3, 'IT', '[email protected]'),
(4, 'Sales', '[email protected]');

6. Давайте посмотрим, как это было:

Как видите, я пошел дальше и добавил даже несколько больше, чем упомянуто. В своем поспешном энтузиазме я по ошибке дважды добавил Финансы. Что теперь? Давайте воспользуемся этой возможностью, чтобы изучить еще одну операцию из CRUD. Удаление.

Удалить запись

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

Удалить запись в заданной строке

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

with cte(rownum) as (
select row_number () over(partition by Department_ID order by Department_ID) from HR_data.dbo.Departments
)
delete from cte where rownum = 2

СТРОКА 1

• Использование «WITH» означает, что мы будем использовать «общее табличное выражение» (CTE). Это позволяет нам определить временный именованный результат, на который мы можем ссылаться позже. Синтаксис:

WITH expression_name[(column_name [,...])] AS (CTE_definition) SQL_statement;

В нашем случае мы назвали expression_name «cte», а column_name — «rownum».

СТРОКА 2

• Мы ВЫБИРАЕМ «номер_строки», где номер_строки — это системная функция SQL, что объясняет, почему она становится розовой/пурпурной.
• Мы используем предложения OVER и PARTITION BY, чтобы указать столбец, для которого нам нужно выполнить агрегацию. В нашем случае мы сосредоточимся на Department_ID и ORDER BY Department_ID. Обратите внимание, что «ORDER BY» является обязательным, его явное имя предотвращает любые недоразумения при работе с row_numbers.

СТРОКА 3 и 4

Мы уточняем нашу исходную таблицу и закрываем предложение OVER.

СТРОКА 5

Здесь записывается SQL_statement из приведенного выше синтаксиса. Прелесть SQL в том, что он часто не требует пояснений. Напоминаем, что у нас есть:

delete from cte where rownum = 2

Мы удалим из нашего выражения ‘cte’, где rownum(ber) равен 2, потому что запись, которую мы хотели удалить, находилась во второй строке.

Удалить запись с помощью DELETE

Вышеизложенное могло показаться утомительным. Все становится проще, если записи можно однозначно идентифицировать по первичному ключу (PK). Несколько слов о PK и внешних ключах (FK) позже. В этом случае мы могли бы удалить запись, написав:

DELETE FROM HR_data.dbo.Departments
WHERE Department_ID = 6

И наша запись «Управление» исчезла; извините народ 😁. Как всегда, давайте запустим приведенный ниже запрос, чтобы убедиться, что все прошло как положено.

SELECT * FROM HR_data.dbo.Departments

Обновить запись

Теперь, когда мы рассмотрели части создания, чтения и удаления аббревиатуры CRUD, давайте рассмотрим последнюю и «обновить» запись.

1. Представьте, что ИТ-отдел меняет свой адрес электронной почты. Мы хотим убедиться, что это правильно отражено в нашей таблице.
2. Синтаксис, которому нужно следовать,

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

3. Таким образом, наш запрос должен выглядеть следующим образом, так как мы установим адрес электронной почты отдела от ИТ на «[email protected]».

UPDATE HR_data.dbo.Departments
SET Department_Email = '[email protected]'
WHERE Department_Name = 'IT'

4. Снова повторим нашу добрую привычку проверять наши результаты

SELECT * FROM HR_data.dbo.Departments

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

Ключи полезны для определения уникальных записей и построения отношений между таблицами.

Установка первичного ключа

Первичные ключи (PK) создают ограничение таким образом, что оно однозначно идентифицирует каждую запись в таблице.

1. Мы определим Department_ID как наш ПК. Хотя вы ожидаете, что будет простой способ, MS Server немного поборется. Поэтому проще всего УДАЛИТЬ столбец Department_ID, а затем создать его заново.

ALTER TABLE Departments DROP COLUMN Department_ID

2. Затем мы воссоздаем его и используем IDENTITY(1,1).

ALTER TABLE Departments ADD Department_ID INT IDENTITY(1, 1)

Это предоставит Department_ID возможность автоинкремента. Каждая новая запись будет иметь свой уникальный идентификатор, и этот идентификатор будет автоматически увеличиваться на 1.

3. Давайте вставим фиктивную запись, чтобы проверить это. Возможно, вы помните, как:

INSERT INTO Departments
VALUES ('test', 'test')

Здесь, как видите, мы не определили никакого «Department_ID», в отличие от того, что мы делали ранее:

INSERT INTO HR_data.dbo.Departments
VALUES (1, 'Finance', '[email protected]');

Это благодаря возможностям автоинкремента нашей таблицы и ее первичному ключу.

4. Запускаем быструю проверку «SELECT * FROM Departments», там наша фиктивная запись.

5. Давайте уберем за собой

DELETE FROM Departments
where Department_ID = 6

Установите ПК с самого начала

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

CREATE TABLE Departments (
    Department_id int NOT NULL IDENTITY(1,1),
    Department_Name nvarchar(50),
    Department_Email nvarchar(50),
    PRIMARY KEY (Department_Id)
);

Как мы видели в Части 2, создается таблица Departments, затем определяются имена столбцов и типы данных. Вы заметите, что мы установили необходимые настройки, чтобы сделать Department_id совместимым с первичным ключом, с NOT NULL и автоинкрементом через Identity(1,1).

Создание внешнего ключа (FK)

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

1. Представьте себе вторую таблицу «Сотрудники». Он содержит данные об имени, фамилии, рабочем адресе электронной почты. Кроме того, каждый сотрудник — это номер (упс, а вот и мем).

Я имел в виду, что у каждого сотрудника есть идентификатор номера, Employee_ID. И каждый сотрудник также является частью отдела. Здесь вам пригодится Department_ID.

2. Наша таблица выглядит так

CREATE TABLE Employees (
Employee_ID int NOT NULL IDENTITY(1,1),
Employee_Name nvarchar(MAX),
Employee_Email nvarchar(MAX),
PRIMARY KEY (Employee_ID),
Department_ID int
);

3. Давайте проверим, что наша таблица существует, для этого может потребоваться обновление, затем откройте панель «Дизайн» — щелкните правой кнопкой мыши имя таблицы, затем «Дизайн».

Это структура. Как видите, Employee_ID — это первичный ключ.

4. Добавим в эту таблицу запись:

INSERT INTO [HR_data].[dbo].[Employees]
VALUES ('Max', '[email protected]', 1)

Поскольку Employee_ID имеет параметр IDENTITY(1,1), он автоматически создается и увеличивается с новой записью. Это объясняет, почему мы не упоминаем никакие Employee_ID в скобках VALUES. Давайте теперь посмотрим на установку Department_ID в качестве внешнего ключа. Есть несколько способов сделать это.

5. Сначала воспользуемся запросом:

ALTER TABLE [dbo].[Employees]
ADD FOREIGN KEY ([Department_ID]) 
REFERENCES Departments([Department_ID])

Что происходит выше?

LINE 1 — уточняем, какую таблицу нужно изменить

СТРОКА 2 — мы указываем, что хотим добавить внешний ключ, и уточняем, как он называется

СТРОКА 3 — мы предоставляем ссылку, этот внешний ключ также будет связан, в нашем случае с нашей таблицей отделов.

Давайте посмотрим на второй метод создания внешнего ключа.

6. Использование диаграммы базы данных. Щелкните правой кнопкой мыши Диаграмму базы данных.

7. Выберите две таблицы, затем «Добавить» и «Закройте окно».

8. Они появляются на нашей игровой площадке, мы можем их перетаскивать.

9. Теперь мы создадим связь между ними, связав первичный ключ, Department_ID (таблица «Отделы») с нашим будущим внешним ключом, «Department_ID» (таблица «Сотрудники»).

10. Выберите строку Department_ID одной таблицы и появившуюся маленькую стрелку.

11. Выберите и перетащите на другую таблицу. Появится пунктирная линия. Отпустите, и появится новое окно. Он соединит PK и FK для таблиц «Отделы» и «Сотрудники». Нажимаем ОК.

12. Появится второе окно. Это позволяет нам настроить внешний ключ и связь с таблицей PK.

Мы пропустим это сейчас и обсудим это позже.

13. Диаграмма дополнена связью между таблицами.

14. Давайте сохраним нашу диаграмму, прежде чем двигаться дальше, выбрав

Затем «Да» и даем имя нашей диаграмме.

Изменение настроек отношения (PK-FK)

1. Щелкните правой кнопкой мыши связь между двумя таблицами. Мы можем решить удалить отношение или получить доступ к его свойствам.

2. Опция «удалить» проста. В свойствах открывается набор опций, которые мы недавно видели в предыдущей части.

3. Верхняя часть информирует нас о деталях соединения этого отношения:

4. В конструкторе баз данных мы можем настроить четыре параметра:

• Применить для репликации и принудительного ограничения внешнего ключа

По умолчанию для них установлено значение ДА, и рекомендуется оставить его таким. Когда они должны быть установлены на НЕТ? Вот краткое пояснение от StackOverflow.

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

• Удалить и обновить правила

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

• No
• Каскад
• Установить по умолчанию
• Установить нуль

Если выбран параметр «Каскад» или «Установить значение по умолчанию/нулевой», а изменение применяется к нашей таблице «Отделы», это повлияет на подключенную таблицу «Сотрудники».

5. Давайте поработаем на примере. Давайте установим настройки для правил удаления/обновления на «Каскад». Затем добавьте нового сотрудника, Боба из администрации.

INSERT INTO [dbo].[Employees]
VALUES ('Bob', '[email protected]', 5)

Запустите этот запрос, чтобы убедиться, что Боб входит в список наших сотрудников.

SELECT * FROM [dbo].[Employees]

6. Сейчас мы на 2 года в будущем. Руководство компании приняло решение передать административный отдел на аутсорсинг. В результате этот отдел будет удален. И, к сожалению, сотрудники этого отдела не смогут остаться. Это плохие новости для Боба.

7. Удаляем Административный отдел

DELETE FROM Departments WHERE Department_Name = 'Administration'

8. Если вы помните, мы установили «Каскад» для удаления и обновления. Что вы ожидаете? Давайте посмотрим, как наша таблица «Сотрудники» отреагировала на удаление нашего отдела «Администрация».

SELECT * FROM [HR_data].[dbo].[Employees]

Боб ушел. Хотя это печально, это помогло нам увидеть, как работает правило «Каскад» и связь между таблицами. Если соединения установлены правильно, это позволяет нам одним действием позаботиться о связанных записях в других таблицах. Это может быть полезно, если нам нужно удалить пользователя из наших записей.

Заключительные слова и что будет дальше

Это все на данный момент! Я надеюсь, что теперь вы чувствуете себя более уверенно, работая с SQL Server и:

• Используйте операции CRUD для создания-чтения-обновления-удаления записей,
• Установите первичные ключи и узнайте, почему они важны (чит-код: они помогают сделать записи уникальными, обеспечивают первую базу для соединения таблиц путем создания логического идентификатора). Вы видели подходы как к запросам, так и к дизайну.
• Определить внешние ключи, запросив или используя диаграмму, и
• Поймите полезные свойства реляционных таблиц, например, как каскадировать изменения между таблицами.

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

Удачного кодирования!

Спасибо за чтение! Понравилась эта история? Присоединиться к Medium, чтобы получить полный доступ ко всем моим историям.

Продолжите путешествие с Эпизодом 4 о схемах и нормализации!

Или выберите другой выпуск по вашему выбору

В выпуске 5 мы расскажем о хранимых процедурах и планировании, это настоящий зверь. Я не могу не подчеркнуть, как это облегчит и автоматизирует вашу повседневную (информационную) жизнь. Часть 5. Хранимые процедуры и планирование

В эпизоде 6 мы представляем пакеты служб SSIS для ETL и анализируем возможности импорта и экспорта данных между двумя базами данных, а также между базой данных и Excel. Часть 6. Введение в пакеты SSIS

В седьмом эпизоде мы подключаем SQL Studio к PowerBI и создаем первые визуальные элементы. Часть 7. Подключение к PowerBI и первые визуальные элементы