День 25. Знакомлюсь с Microsoft SQL Server и изучаю T-SQL
Автор статьи никого не призывает к правонарушениям и отказывается нести ответственность за ваши действия. Вся информация предоставлена исключительно в ознакомительных целях. Все действия происходят на виртуальных машинах и внутри локальной сети автора. Спасибо!
Вчера разбирался с MongoDB. Достаточно простая и дружелюбная система. Сегодня перехожу к Microsoft SQL Server и T-SQL. Изучать буду по тому же сайту, что и MongoDB (https://metanit.com/sql/sqlserver/1.1.php). Как ни странно, хоть это и продукт Microsoft, эта система доступна и под Linux.
Только сейчас узнал о разновидностях SQL. Оказывается, ранее использовал только PL-SQL (в MySQL). Немного вник, это не совсем разновидности, это «процедурные расширения». Сейчас буду рассматривать T-SQL. Сам T-SQL может принадлежать к различным типам: DDL (язык определения данных), DML (язык манипуляции данными), DCL (язык управления доступом к данным).
Теперь скачаем SQL Server 2019 Developer Edition, после скачиваем SQL Server Management Studio (SSMS).
База данных в Microsoft SQL Server представляет хранилище объектов: таблицы, представления, хранимые процедуры и функции. Если открыть SSMS, то можно увидеть четыре системных базы данных: master (главная база данных для сервера), model (шаблон, на основе которого создаются другие базы данных), msdb (хранит информацию о работе планировщика SQL и другую информацию) и tempdb (хранилище для временных объектов). Находятся они в Databases -> System Databases.
Теперь попробуем создать свою базу данных. Кликаем правой кнопкой мыши по Databases и выбираем пункт New Database. Создадим бд test. Чуть ниже таблица со следующими полями: Logical Name (логическое имя), File Type (тип файла), Filegroup (группа файлов для разбиения базы данных на части), Initial Size (начальный размер файлов при создании), Autogrowth (авторасширение размера файла), Path (путь для хранения бд), File Name (имя физического файла).
Базу данных создали, теперь создадим таблицу. Выбираем нашу бд test, находим вкладку Tables и нажимаем правой кнопкой мыши, далее New -> Table. Чтобы открыть Properties, кликаем правой кнопкой мыши по свободному полю и находим этот пункт. Там пишем название таблицы и выбираем столбик, который станет идентификатором. Далее кликаем по столбцу id и выбираем пункт Set Primary Key.
Наша таблица появилась в общем списке вместе с префиксом dbo, который представляет схему. Это позволяет логически разграничить базу данных. По умолчанию все новые объекты принадлежать схеме dbo.
Если кликнуть правой кнопкой мыши по таблице и нажать «Edit Top 200 Rows», то можно добавить новые данные. Так же можно отобразить первые 1000 строк при помощи «Select To 1000 Rows», а скрипт можно отредактировать и нажать «Execute» для выполнения.
Запросы на T-SQL
Простую базу данных с одной таблицей создали. Теперь потренируемся в запросах к ней. Нажимаем правой кнопкой мыши по названию сервера (самый верхний в дереве) и выбираем пункт «New Query». Попробуем получить все данные из таблицы users базы данных test:
SELECT * FROM test.dbo.users
После написания скрипта нажимаем F5 или кнопку «Execute». Чтобы постоянно не указывать базу данных, можно применить команду USE.
USE test
SELECT * FROM users
Для создания базы данных используется команда CREATE DATABASE. Так же может быть ситуация, когда у нас уже есть база данных созданная на другом компьютере. Она имеет расширение mdf. Этот файл мы можем переносить и прикреплять на актуальном сервере. Чтобы прикрепить бд, необходимо выполнить следующую команду:
CREATE DATABASE имя_бд
ON PRIMARY(FILENAME='путь_к_файлу_бд')
FOR ATTACH;
Для удаления бд используется команда DROP DATABASE.
Для создания таблиц используется команда CREATE TABLE со следующим синтаксисом:
USE test
CREATE TABLE files
(
id int,
field varchar(20)
);
После указания всех столбцов (внутри круглых скобок) можно указать атрибуты таблицы. После типа данных столбца есть возможность указать атрибут столбца.
Для удаления таблицы используется команда DROP TABLE, а для переименования есть системная процедура sp_rename:
USE test
EXEC sp_rename 'files', 'no_files';
Типы данных в T—SQL
Их огромное количество. Почитать можно здесь: https://metanit.com/sql/sqlserver/3.3.php
Кстати, для хранения строк нужно использовать nvarchar, а не varchar, так как первый хранит строку в кодировке Unicode.
Атрибуты и ограничения столбцов и таблиц
Когда создал таблицу files при помощи T-SQL, я не указал атрибут PRIMARY KEY для id, поэтому правильнее сделать так:
USE test
CREATE TABLE files
(
id int PRIMARY KEY,
field nvarchar(20)
);
Или так:
USE test
CREATE TABLE files
(
id int,
field nvarchar(20),
PRIMARY KEY(id)
);
Первичный ключ может быть составным. Поэтому можно указать несколько полей в атрибуте таблицы PRIMARY KEY (внутри круглых скобок).
Атрибут IDENTITY позволяет сделать столбец идентификатором, поэтому еще скорректируем создание таблицы files.
USE test
CREATE TABLE files
(
id int PRIMARY KEY IDENTITY,
field nvarchar(20)
);
Идентификатор инкрементируется на единицу от последней записи при добавлении новой.
Чтобы в столбце были только уникальные значения, следует использовать для них атрибут UNIQUE.
Атрибуты NULL и NOT NULL определяет для столбца, может ли он хранить нулевое значение.
Атрибут DEFAULT определяет значение по умолчанию для столбца. А атрибут CHECK задает ограничения для диапазона значений.
Внешние ключи
Внешние ключи устанавливаются для указания связи между таблицами. Создам таблицу documents из трех столбцов (id, documents, id_user). Последний буду использовать как внешний ключ.
CREATE TABLE documents
(
id int PRIMARY KEY IDENTITY,
documents nvarchar(20),
id_user int REFERENCES users(id)
);
В таблицу documents добавил новую запись и сделал ссылку на первого пользователя. Теперь мы не можем удалить первого пользователя, так как на него ссылается строка из таблицы documents.
Про выражения ON DELETE и ON UPDATE
При помощи этих выражений можно установить действия, которые выполняются при удалении и изменении связанной строки из родительской таблицы. Действия могут быть следующими:
CASCADE – автоматически удаляет или изменяет строки дочерней таблицы, при удалении связанных строк;NO ACTION – отсутствуют какие-либо действия;SET NULL – при удалении связанной строки из родительской таблицы ставит для столбца внешнего ключа значение NULL;SET DEFAULT – при удалении из родительской таблицы, выставляет в дочерней значение по умолчанию.
Пока не сильно понятно, поэтому попробую на практике. У меня так и остались две таблицы user и document. Для начала попробую SET NULL.
Пришлось поискать где редактировать внешний ключ. Итак, нажимаем правой кнопкой мыши по структуре таблицы (после того как перешли в конструктор), далее нажимаем «relationship» (почитать можно здесь: https://docs.microsoft.com/ru-ru/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver16).
Далее находим «INSERT and UPDATE Specification», а там выставляем «Set Null». Как я понимаю, при удалении пользователя, этот пункт в documents должен ссылаться на NULL, а не на id пользователя.
Что касается CASCADE. Если попробуем удалить строку, на которую ссылается внешний ключ, то вылезет ошибка. Здесь то и помогает CASCADE. Если такая опция стоит у внешнего ключа, то при удалении записи из главной таблицы, на которую ссылаются, все внешние ключи (строки) будут удалены.
Изменение таблицы
При помощи конструктора достаточно просто изменять таблицу. Теперь попробуем при помощи выражения ALTER TABLE. Синтаксис такой (скопировал от сюда https://metanit.com/sql/sqlserver/3.6.php):
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
{ ADD название_столбца тип_данных_столбца [атрибуты_столбца] |
DROP COLUMN название_столбца |
ALTER COLUMN название_столбца тип_данных_столбца [NULL|NOT NULL] |
ADD [CONSTRAINT] определение_ограничения |
DROP [CONSTRAINT] имя_ограничения}
В целом все понятно. Кстати, манипуляции по изменению внешнего ключа, которую делал через конструктор, можно сделать как-то так:
ALTER TABLE Orders
ADD FOREIGN KEY(id_user) REFERENCES users(id) ON DELETE SET NULL
Ключи можно удалить в разделе Keys, который находится в узле таблицы. Там ключи, начинающиеся с PK – первичные, а FK – внешние. В плане визуализации в Microsoft SQL Server все хорошо.
Пакеты. Команда GO
В одном запросе можно совместить несколько команд. Для этого есть команда GO. Отдельные наборы команд называются пакетами. Смысл разбития на пакеты – быть уверенным, чтобы последующий пакеты выполнится, при условии выполнения предыдущего. Например, создать таблицу, далее написать команду GO и добавить в эту таблицу какие-то столбцы.
Основы T-SQL. DML
Для добавления данных используется команда INSERT:
INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, ... значениеN)
Работаю все с той же таблицей documents (столбцы: id, название документа, внешний ключ на users). Так как id первичный ключ с автоинкрементом, то его не указываем:
USE test
INSERT documents VALUES ('Важный документ', 6)
Или используем INSERT INTO с указанием конкретных столбцов:
USE test
INSERT INTO documents (documents, id_us) VALUES ('Еще один документ', 6)
Для получения данных используется SELECT:
SELECT список_столбцов FROM имя_таблицы
Вместо «список_столбцов» необязательно должны быть перечислены столбцы через запятую. Можно так же производить арифметические операции и даже конкатенацию строк.
Следующий запрос не прокатил, так как я пытался конкатенировать число и строку:
USE test
SELECT documents + ' (' + id_us + ')'
FROM documents
Поэтому пришлось заглянуть как конвертировать число в строку:
USE test
SELECT documents + ' (' + CONVERT(varchar(10), id_us) + ')'
FROM documents
Кстати, у столбца нет имени. Применим оператор AS, чтобы задать название столбца.
USE test
SELECT documents + ' (' + CONVERT(varchar(10), id_us) + ')' AS doc,
'(' + CONVERT(varchar(10), id_us) + ') ' + documents AS reverse_doc
FROM documents
Чтобы найти уникальные значения в столбце, следует использовать оператор DISTINCT:
SELECT DISTINCT id_us
FROM documents
А при помощи SELECT INTO можно сделать выборку из одной таблицы, в другую, которая будет создана автоматически.
Теперь разберемся с сортировкой. Чтобы отсортировать извлеченные данные при помощи SELECT, применим ORDER BY:
SELECT *
FROM users
ORDER BY age
Таким образом отсортировали по возрасту. Чтобы отсортировать по убыванию, применим оператор DESC:
SELECT *
FROM users
ORDER BY age DESC
Оператор ASC противоположен DESC. А еще можно комбинировать. Например, отсортировать сначала по возрасту, и, если возраст одинаковый, то отсортировать по столбцу «Имя»:
SELECT *
FROM users
ORDER BY age DESC, first_name DESC
Так же в критериях сортировки можно использовать не просто столбцы, а арифметические операции с их участием.
Следующие операторы предназначены больше для какой-нибудь постраничной навигации, чтобы выгружать не всю БД сразу, а по частям. Например, при помощи оператора TOP можно выгрузить определенное количество строк или при помощи связки TOP и PERCENT выбрать процентное количество строк из таблицы. Но это начиная с начала таблицы.
Для извлечения из любого места, применяются операторы OFFSET и FETCH. Так же важно перед использованием этих операторов отсортировать набор данных. Не будем изгаляться и отсортируем по id:
USE test
SELECT *
FROM users
ORDER BY id
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY
Более подробно можно посмотреть на том же сайте https://metanit.com/sql/sqlserver/4.4.php. Кстати, пока не совсем понятно где хранится указатель или какое-то состояние, чтобы реализовать постраничную навигацию. Вероятно, это реализуется на стороне приложения, но не факт. Нужно пометить себе и позже разобраться.
При выборке SELECT можно так же устанавливать фильтрацию при помощи оператора WHERE. Об этом здесь https://metanit.com/sql/sqlserver/4.5.php.
Операторы фильтрации
При помощи оператора IN можно получить набор значений, которые имеют определенные значения в определенном столбце. Синтаксис:
WHERE выражение [NOT] IN (выражение)
Например, из таблицы documents получим все строки, принадлежащие пользователям с id равным 6 и 7:
USE test
SELECT * FROM documents
WHERE id_us IN (6, 7)
Перед оператором IN можно добавить NOT. Работает противоположно.
Про оператор BETWEEN, для определения диапазона значений и оператор LIKE, для выгрузки по шаблону написано здесь: https://metanit.com/sql/sqlserver/4.6.php
На этом достаточно. Завтра продолжу изучение Microsoft SQL Server и T-SQL.
