Функции CAST и CONVERT (Transact-SQL)
Эти функции преобразуют выражение одного типа данных в другой.
Синтаксис
CAST ( expression AS data_type [ ( length ) ] ) CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
выражение
data_type
Целевой тип данных. Это может быть xml, bigint и sql_variant. Нельзя использовать типы данных псевдонима.
length
Указываемое дополнительно целое число, обозначающее длину целевого типа данных, который допускает указание длины пользователем. Значение по умолчанию — 30.
style
Целочисленное выражение, указывающее способ CONVERT преобразования выражения функции. Для значения стиля NULL возвращается NULL. Аргумент data_type определяет диапазон.
Типы возвращаемых данных
Возвращает значение аргумента expression, преобразованное в тип data_type.
стили даты и времени
Если аргумент expression принадлежит к типу данных даты или времени, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0. Начиная с SQL Server 2012 (11.x), поддерживаются только стили, которые поддерживаются при преобразовании типов даты и времени в datetimeoffset, 0 или 1. Все другие стили преобразования возвращают ошибку 9809.
SQL Server поддерживает формат даты в арабском стиле с алгоритмом Кувейта.
| Без века (гг) 1 | С веком (гггг) | Standard | Входные и выходные данные 3 |
|---|---|---|---|
| — | 0 или 100 1,2 | Значение по умолчанию для datetime и smalldatetime | mon dd yyyy hh:miAM (или PM ) |
| 1 | 101 | США | 1 = mm/dd/yy 101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd 102 = yyyy.mm.dd |
| 3 | 103 | Британский/французский | 3 = dd/mm/yy 103 = dd/mm/yyyy |
| 4 | 104 | немецкий | 4 = dd.mm.yy 104 = dd.mm.yyyy |
| 5 | 105 | Итальянский | 5 = dd-mm-yy 105 = dd-mm-yyyy |
| 6 | 106 1 | — | 6 = dd mon yy 106 = dd mon yyyy |
| 7 | 107 1 | — | 7 = Mon dd, yy 107 = Mon dd, yyyy |
| 8 или 24 | 108 | — | hh:mi:ss |
| — | 9 или 109 1,2 | По умолчанию + миллисекунды | mon dd yyyy hh:mi:ss:mmmAM (или PM ) |
| 10 | 110 | USA | 10 = мм-дд-гг 110 = mm-dd-yyyy |
| 11 | 111 | Япония | 11 = гг/мм/дд 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = ггммдд 112 = yyyymmdd |
| — | 13 или 113 1,2 | Европейский по умолчанию + миллисекунды | dd mon yyyy hh:mi:ss:mmm (24-час) |
| 14 | 114 | — | hh:mi:ss:mmm (24-час) |
| — | 20 или 120 2 | Канонический формат ODBC | yyyy-mm-dd hh:mi:ss (24-час) |
| — | 21 или 25 или 121 2 | Канонический ODBC (с миллисекундами) по умолчанию для времени, даты, даты , даты и времени и datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24-час) |
| 22 | — | США | mm/dd/yy hh:mi:ss AM (или PM ) |
| — | 23 | ISO8601 | yyyy-mm-dd |
| — | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (без пробелов) 6 |
| — | 127 8, 9 | ISO8601 с часовыми поясами Z | yyyy-MM-ddThh:mm:ss.fffZ (без пробелов) 6 |
| — | 130 1,2 | Хиджри 5 | dd mon yyyy hh:mi:ss:mmmAM 7 |
| — | 131 2 | Хиджри 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
1 Эти значения стилей возвращают недетерминированные результаты. Включает все ( yy без века) стили и подмножество стилей ( yyyy с веком).
2 Значения по умолчанию (0 или 100, 9 или 109, 13 или 113, 20 или 120, 23 и 21 или 25 или 121) всегда возвращают век (). yyyy
По умолчанию SQL Server интерпретирует двухзначные годы на основе отсечения 2049 года. Это означает, что SQL Server интерпретирует двухзначное значение года 49 как 2049, а двухзначное значение 50 — как 1950. В большинстве клиентских приложений, основанных, в частности, на объектах автоматизации, 2030 год используется в качестве порогового значения. SQL Server предоставляет параметр конфигурации отсечения двухзначного года, чтобы изменить отсеченный год, используемый SQL Server. Это позволяет обеспечить согласованную обработку дат. Рекомендуется использовать четырехзначные года.
3 Вход при преобразовании в тип datetime; выход при преобразовании в символьные данные.
4 Для использования в формате XML. Для преобразования из datetime или smalldatetime в символьные данные формат вывода должен быть таким, как описано в предыдущей таблице.
5 Хиджра — календарная система с несколькими вариантами. SQL Server использует алгоритм Кувейта.
6 Для миллисекунда ( mmm ) значения 0 значение десятичной дроби миллисекунда не отображается. Например, значение 2022-11-07T18:26:20.000 отображается как 2022-11-07T18:26:20 .
7 В этом стиле mon представляет представление юникода Хиджри с несколькими токенами полного имени месяца. Это значение не отображается правильно при установке SSMS по умолчанию в США.
8 поддерживается только при приведение символьных данных к дате или smalldatetime. При приведение символьных данных, представляющих только компоненты даты или времени к типам данных datetime или smalldatetime , компонент времени не указан 00:00:00.000 , а компонент 1900-01-01 даты не указан.
9 . Используйте необязательный индикатор Z часового пояса, чтобы упростить сопоставление значений даты и времени XML, имеющих сведения о часовом поясе с значениями даты и времени SQL Server, которые не имеют часового пояса. Z указывает часовой пояс в формате UTC-0. Смещение HH:MM в + направлении или — направлении указывает на другие часовые пояса. Например: 2022-12-12T23:45:12-08:00 .
При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.
При преобразовании в тип данных datetimeoffset из символьных данных со стилем, включающим время, смещение часового пояса добавляется к результату.
Стили данных float и real
Если аргумент expression принадлежит к типу данных float или real, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
Стили данных money и smallmoney
Если аргумент expression принадлежит к типу данных money или smallmoney, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
Стили данных XML
Если аргумент expression принадлежит к типу данных xml, аргумент style может иметь одно из значений, приведенных в таблице ниже. Другие значения обрабатываются как 0.
При этом для выполнения операций синтаксического анализа без проверки действительности сервер может пользоваться следующей информацией, предоставляемой внутренним подмножеством DTD.
— Применяются атрибуты по умолчанию.
— Ссылки на внутренние сущности разрешаются и раскрываются.
— Проверяется синтаксическая правильность модели содержимого DTD.
Стили двоичных данных
Для двоичного(n), char(n ) , varbinary( n)или varchar(n)выражения стиль может иметь одно из значений, показанных в следующей таблице. При использовании значений стиля, отсутствующих в этой таблице, возвращается ошибка.
Если длина преобразованного выражения превышает длину типа данных data_type, результат усекается справа.
При использовании значений аргумента data_type фиксированной длины, превышающей длину преобразованного результата, к результату справа добавляются нули.
Аргумент data_type символьного типа требует двоичного выражения. Каждый двоичный символ преобразуется в два шестнадцатеричных символа. Предположим, длина преобразованного выражения превышает длину типа данных data_type. В этом случае оно усекается.
Если для параметра data_type используется значение символьного типа фиксированного размера и длина преобразованного результата меньше длины типа данных data_type, к преобразованному выражению справа добавляются символы пробела, чтобы сохранить четность числа шестнадцатеричных знаков.
Неявные преобразования
Неявные преобразования не требуют спецификации CAST функции или CONVERT функции. Явные преобразования требуют спецификации CAST функции или CONVERT функции. На следующем рисунке показаны все явные и неявные преобразования типов данных, разрешенные для системных типов данных SQL Server. Это могут быть типы bigint, sql_variant и xml. При присваивании неявного преобразования из типа sql_variant не происходит, но неявное преобразование в тип sql_variant производится.
В Центре загрузки Майкрософт эта диаграмма представлена в виде PNG-файла, который можно скачать.
На приведенной выше диаграмме показаны все явные и неявные преобразования, которые разрешены в SQL Server, но результирующий тип данных преобразования зависит от выполняемой операции:
- При явных преобразованиях сам оператор определяет результирующий тип данных.
- При неявных преобразованиях операторы назначения, такие как установка значения переменной или вставка значения в столбец, дают в результате тип данных, определенный в объявлении переменной или в определении столбца.
- Для операторов сравнения или других выражений результирующий тип данных зависит от правил приоритета типов данных.
Практический пример применения приоритета типов данных при преобразованиях приводится далее в этом разделе.
При преобразовании между datetimeoffset и типами символов char, nchar, nvarchar и varchar преобразованная часть смещения часового пояса всегда должна иметь двойные цифры для обоих HH и MM . Например, -08:00 .
Так как у данных в Юникоде всегда четное число байтов, будьте осторожны при преобразовании значений типа binary или varbinary в типы данных, поддерживающие Юникод, и наоборот. Например, следующее преобразование не возвращает шестнадцатеричное значение 41. Возвращает шестнадцатеричное значение 4100:
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary); Дополнительные сведения см. в статье Collation and Unicode Support.
Типы данных больших значений
Типы данных большого объема демонстрируют то же поведение при явных и неявных преобразованиях, что и их аналоги меньшего объема, а именно типы данных nvarchar, varbinary и varchar. Тем не менее необходимо учитывать следующие правила:
- Преобразование из image в varbinary(max) и обратно неявное, как и преобразования между text и varchar(max), а также ntext и nvarchar(max).
- Преобразование из типов данных большого объема, например varchar(max), в аналогичный тип данных меньшего объема, например varchar, неявное, но если объем данных слишком велик, будет произведено усечение данных до указанной длины конкретного типа данных меньшего объема.
- Преобразование из nvarchar, varbinary или varchar в соответствующие им типы данных большого объема выполняется неявно.
- Преобразование из типа данных sql_variant в типы данных большого объема выполняется явно.
- Типы данных большого значения нельзя преобразовать в тип данных sql_variant .
Дополнительные сведения о преобразовании из типа данных xml см. в разделе Создание экземпляров XML-данных.
Тип данных XML
При явном или неявном приведении типа данных xml к строковому или двоичному типу данных содержимое типа данных xml сериализуется согласно набору определенных правил. Сведения об этих правилах см. в разделе Определение сериализации XML-данных. Дополнительные сведения о преобразовании других типов данных в тип данных xml см. в разделе Создание экземпляров XML-данных.
Типы данных text и image
Типы данных текста и изображения не поддерживают автоматическое преобразование типов данных. Можно явно преобразовать text в символьные данные, а image — в binary или varbinary, но длиной не более 8000 байт. При попытке неправильного преобразования, например при попытке преобразовать символьное выражение, включающее буквы в int, SQL Server возвращает сообщение об ошибке.
Параметры сортировки выходных данных
CAST CONVERT Когда или функции выводят символьную строку и получают входные данные строки символов, выходные данные имеют ту же метку сортировки и сортировки, что и входные данные. Если входные данные не является строкой символов, выходные данные имеют параметры сортировки по умолчанию базы данных и метку сортировки для принудительного использования по умолчанию. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).
Чтобы назначить другую сортировку выходным данным, примените предложение COLLATE к результирующем CAST выражению или CONVERT функции. Например:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS; Усечение и округление результатов
При преобразовании символьных или двоичных выражений (binary, char, nchar, nvarchar, varbinary или varchar) в выражение другого типа данных операция преобразования может усекать выходные данные, отображать их лишь частично или возвращать ошибку. Это происходит в тех случаях, когда результат имеет слишком малую длину для отображения. Результаты преобразований в binary, char, nchar, nvarchar, varbinary или varchar усекаются всегда, за исключением случаев, перечисленных в таблице ниже.
| Из типа данных | В тип данных | Результат |
|---|---|---|
| int, smallint или tinyint | char varchar |
Слишком короткое для отображения |
| nchar nvarchar |
Ошибка 1 | |
| money, smallmoney, numeric, decimal, float или real | char varchar |
Ошибка 1 |
| nchar nvarchar |
Ошибка 1 |
1 Ошибка, возвращенная, так как длина результата слишком коротка для отображения.
SQL Server гарантирует, что только преобразования округления, иными словами, преобразования типа данных из исходного типа данных и обратно возвращают те же значения из версии в версию. В следующем примере показано обратимое преобразование:
DECLARE @myval DECIMAL(5, 2); SET @myval = 193.57; SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5)); -- Or, using CONVERT SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval)); GO Не создавайте двоичные значения, а затем преобразуйте их в тип данных категории числовых типов данных. SQL Server не гарантирует, что результат преобразования десятичного или числового типа данных в двоичный файл будет одинаковым между версиями SQL Server.
В следующем примере показано результирующее выражение, которое слишком мало для отображения:
USE AdventureWorks2022; GO SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave] FROM HumanResources.Employee e INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID WHERE NOT e.BusinessEntityID > 5; GO FirstName LastName Title Sick Leave --------- ------------- ------- --------` Ken Sanchez NULL * Terri Duffy NULL * Roberto Tamburello NULL * Rob Walters NULL * Gail Erickson Ms. * (5 row(s) affected) При преобразовании между типами данных с разными длинами дробных частей SQL Server может усекать или округлять результат. В следующей таблице описано это поведение.
| С дт. | По | Поведение |
|---|---|---|
| numeric | numeric | Round |
| numeric | int | Truncate |
| numeric | money | Round |
| money | int | Round |
| money | numeric | Round |
| float | int | Truncate |
| float | numeric | Раунд 1 |
| float | datetime | Round |
| datetime | int | Round |
1 Преобразование значений с плавающей запятой , использующее научное нотация в десятичное или числовое значение, ограничено только значениями точности 17 цифр. Любое значение с точностью, превышающей 17 знаков, округляется до нуля.
Например, значения 10,6496 и –10,6496 могут усекаться или округляться при преобразовании в тип int или numeric:
SELECT CAST(10.6496 AS INT) AS trunc1, CAST(-10.6496 AS INT) AS trunc2, CAST(10.6496 AS NUMERIC) AS round1, CAST(-10.6496 AS NUMERIC) AS round2; Результаты запроса показаны в приведенной ниже таблице.
| trunc1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | -10 | 11 | -11 |
При преобразовании к типам данных, у которых дробная часть короче, чем у исходного типа, значение округляется. Например, это преобразование возвращает $10.3497 :
SELECT CAST(10.3496847 AS money); SQL Server возвращает сообщение об ошибке при преобразовании ненумерных символов, nchar, nvarchar или varchar данных в десятичные, float, int, числовые. SQL Server также возвращает ошибку при преобразовании пустой строки («») в числовой или десятичный.
некоторые преобразования типа данных даты и времени являются недетерминированными
В следующих стилях преобразование строк в тип datetime недетерминировано:
1 За исключением стилей 20 и 21.
Дополнительные символы (суррогатные пары)
Начиная с SQL Server 2012 (11.x), при использовании дополнительных параметров сортировки символов CAST (SC) операция из nchar или nvarchar в nchar или nvarchar типа меньшей длины не усечена внутри суррогатной пары. Вместо этого усечение происходит перед дополнительным символом. Например, выполнение следующего фрагмента кода приведет к тому, что в @x останется лишь ‘ab’ . Недостаточно места для хранения дополнительного символа.
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000); SELECT CAST(@x AS NVARCHAR(3)); При использовании параметров сортировки SC поведение CONVERT аналогично CAST . Дополнительные сведения см. в статье Поддержка параметров сортировки и Юникода — дополнительные символы.
Поддержка совместимости
В более ранних версиях SQL Server стиль по умолчанию для CAST типов данных time и CONVERT datetime2 составляет 121, за исключением случаев, когда любой тип используется в вычисляемом выражении столбца. Для вычисляемых столбцов используемый по умолчанию стиль — 0. Это поведение влияет на вычисляемые столбцы при их создании и использовании в запросах с автоматической параметризацией, а также при использовании в определениях ограничений.
При уровне совместимости 110 и более поздних CONVERT CAST версий и операциях с типами данных datetime2 всегда есть 121 в качестве стиля по умолчанию. Если запрос основан на прежнем поведении, следует использовать уровень совместимости ниже 110 либо явно задать в затрагиваемом запросе стиль 0.
| Значение уровня совместимости | Стиль по умолчанию для CAST и CONVERT 1 | Стиль по умолчанию для вычисляемого столбца |
|---|---|---|
| 110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 Кроме вычисляемых столбцов
Обновление базы данных до уровня совместимости 110 и более поздних версий не изменит пользовательские данные, хранящиеся на диске. Следует исправить эти данных соответствующим образом вручную. Например, если бы вы использовали предложение SELECT INTO для создания таблицы на основе источника, содержащего описанное выше выражение вычисляемого столбца, то сохранялись бы данные (благодаря стилю 0), а не само определение вычисляемого столбца. В таком случае необходимо вручную обновлять эти данные в соответствии со стилем 121.
Примеры
А. Использование CAST и CONVERT
Эти примеры извлекают имя продукта, для тех продуктов, которые имеют 3 первую цифру цены на список, и преобразует их ListPrice значения в int.
Используйте CAST в следующих случаях:
USE AdventureWorks2022; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS INT) LIKE '33%'; GO Используйте CONVERT в следующих случаях:
USE AdventureWorks2022; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CONVERT(INT, ListPrice) LIKE '33%'; GO Результирующий набор: Пример результирующий набор одинаков для обоих CAST и CONVERT .
ProductName ListPrice ------------------------------ --------------------- LL Road Frame - Black, 58 337.22 LL Road Frame - Black, 60 337.22 LL Road Frame - Black, 62 337.22 LL Road Frame - Red, 44 337.22 LL Road Frame - Red, 48 337.22 LL Road Frame - Red, 52 337.22 LL Road Frame - Red, 58 337.22 LL Road Frame - Red, 60 337.22 LL Road Frame - Red, 62 337.22 LL Road Frame - Black, 44 337.22 LL Road Frame - Black, 48 337.22 LL Road Frame - Black, 52 337.22 Mountain-100 Black, 38 3374.99 Mountain-100 Black, 42 3374.99 Mountain-100 Black, 44 3374.99 Mountain-100 Black, 48 3374.99 HL Road Front Wheel 330.06 LL Touring Frame - Yellow, 62 333.42 LL Touring Frame - Blue, 50 333.42 LL Touring Frame - Blue, 54 333.42 LL Touring Frame - Blue, 58 333.42 LL Touring Frame - Blue, 62 333.42 LL Touring Frame - Yellow, 44 333.42 LL Touring Frame - Yellow, 50 333.42 LL Touring Frame - Yellow, 54 333.42 LL Touring Frame - Yellow, 58 333.42 LL Touring Frame - Blue, 44 333.42 HL Road Tire 32.60 (28 rows affected) B. Использование CAST с арифметическими операторами
В этом примере вычисляется столбец значений ( Computed ) путем деления суммарных продаж за год ( SalesYTD ) на проценты комиссионных ( CommissionPCT ). Это значение округляется до ближайшего целого числа, а затем CAST — до типа данных int.
USE AdventureWorks2022; GO SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed FROM Sales.SalesPerson WHERE CommissionPCT != 0; GO Computed ------ 379753754 346698349 257144242 176493899 281101272 0 301872549 212623750 298948202 250784119 239246890 101664220 124511336 97688107 (14 row(s) affected) C. Использование CAST для объединения
В этом примере сцепляется нехарактерные выражения с помощью CAST . В нем используется база данных AdventureWorksDW2022 .
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice FROM dbo.DimProduct WHERE ListPrice BETWEEN 350.00 AND 400.00; ListPrice ------------------------ The list price is 357.06 The list price is 364.09 The list price is 364.09 The list price is 364.09 The list price is 364.09 D. Использование CAST для создания более удобочитаемого текста
Этот пример используется CAST в списке SELECT для преобразования столбца Name в столбец char(10). В нем используется база данных AdventureWorksDW2022 .
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name, ListPrice FROM dbo.DimProduct WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M'; GO Name ListPrice ---------- --------- Long-Sleev 31.2437 Long-Sleev 32.4935 Long-Sleev 49.99 Д. Использование CAST с предложением LIKE
В этом примере значения столбцов SalesYTD преобразуются money в int типа данных, а затем в тип данных char(20), чтобы LIKE предложение ему можно было использовать.
USE AdventureWorks2022; GO SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID FROM Person.Person AS p INNER JOIN Sales.SalesPerson AS s ON p.BusinessEntityID = s.BusinessEntityID WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%'; GO FirstName LastName SalesYTD BusinessEntityID ---------------- ------------------- ---------------- ------------- Tsvi Reiter 2811012.7151 279 Syed Abbas 219088.8836 288 Rachel Valdez 2241204.0424 289 (3 row(s) affected) F. Использование CONVERT или CAST с типизированным XML
В этих примерах показано, как CONVERT преобразовать данные в типизированный XML с помощью типа данных XML и столбцов (SQL Server).
В этом примере строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором удаляются все незначащие пробельные символы (пробелы, разделяющие узлы):
SELECT CONVERT(XML, ' В этом примере похожая строка, содержащая пробельные символы, текст и разметку, преобразуется в типизированный XML, в котором сохраняются все незначащие пробельные символы (пробелы, разделяющие узлы):
SELECT CONVERT(XML, ' ', 1) В этом примере строка, содержащая пробельные символы, текст и разметку, приводится к типизированному XML:
SELECT CAST('Carol Elliot Дополнительные примеры см. в статье Создание экземпляров XML-данных.
G. Использование CAST и CONVERT с данными datetime
Начиная со значений GETDATE() этот пример показывает текущие дату и время, использует функцию CAST для изменения текущей даты и времени в символьный тип данных и затем использует CONVERT для отображения даты и времени в формате ISO 8601 .
SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast, CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601; GO UnconvertedDateTime UsingCast UsingConvertTo_ISO8601 ----------------------- ---------------------- ------------------------------ 2022-04-18 09:58:04.570 Apr 18 2022 9:58AM 2022-04-18T09:58:04.570 (1 row(s) affected) Этот пример — частичная противоположность предыдущему примеру. В этом примере отображаются данные даты и времени в качестве символьных данных, которые используются CAST для изменения символьных данных на тип данных datetime , а затем используются CONVERT для изменения символьных данных на тип данных datetime .
SELECT '2006-04-25T15:50:59.997' AS UnconvertedText, CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast, CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601; GO UnconvertedText UsingCast UsingConvertFrom_ISO8601 ----------------------- ----------------------- ------------------------ 2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997 (1 row(s) affected) H. Использование CONVERT с двоичными и символьными данными
В этих примерах показаны результаты преобразования двоичных и символьных данных с использованием различных стилей.
--Convert the binary value 0x4E616d65 to a character value. SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character]; Style 0, binary to character ---------------------------- Name (1 row(s) affected) В этом примере показано, что стиль 1 может привести к принудительному усечению результата. К этому приводит наличие символов 0x в результате.
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character]; Style 1, binary to character ------------------------------ 0x4E616D (1 row(s) affected) В этом примере показано, что стиль 2 не усечен результат, так как результат не включает символы 0x.
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character]; Style 2, binary to character ------------------------------ 4E616D65 (1 row(s) affected) Преобразуйте символьное значение «Name» в двоичное значение.
SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary]; Style 0, character to binary ---------------------------- 0x4E616D6500000000 (1 row(s) affected) SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary]; Style 1, character to binary ---------------------------- 0x4E616D65 (1 row(s) affected) SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary]; Style 2, character to binary ---------------------------------- 0x4E616D65 (1 row(s) affected) I. Преобразование типов данных даты и времени
В этом примере показано преобразование типов данных даты, времени и даты и времени .
DECLARE @d1 DATE, @t1 TIME, @dt1 DATETIME; SET @d1 = GETDATE(); SET @t1 = GETDATE(); SET @dt1 = GETDATE(); SET @d1 = GETDATE(); -- When converting date to datetime the minutes portion becomes zero. SELECT @d1 AS [DATE], CAST(@d1 AS DATETIME) AS [date as datetime]; -- When converting time to datetime the date portion becomes zero -- which converts to January 1, 1900. SELECT @t1 AS [TIME], CAST(@t1 AS DATETIME) AS [time as datetime]; -- When converting datetime to date or time non-applicable portion is dropped. SELECT @dt1 AS [DATETIME], CAST(@dt1 AS DATE) AS [datetime as date], CAST(@dt1 AS TIME) AS [datetime as time]; Убедитесь, что значения находятся в совместимом диапазоне при рассмотрении преобразования с даты на дату или datetime2 . Минимальное значение года для даты и времени 1753 , а минимальное значение года — 0001 для даты и даты и времени 2.
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2 SET @d1 = '1492-08-03' --This is okay; Minimum YYYY for DATE is 0001 SET @dt2 = CAST(@d1 AS DATETIME2) --This is okay; Minimum YYYY for DATETIME2 IS 0001 SET @dt1 = CAST(@d1 AS DATETIME) --This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value." --Minimum YYYY for DATETIME is 1753 J. Использование CONVERT с данными datetime в различных форматах
Начиная со значений GETDATE() этот пример использует функцию CONVERT для отображения всех стилей даты и времени в разделе Стили даты и времени этой статьи.
| Номер формата | Пример запроса | Пример результата |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) | 23 авг 2019 13:39 |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) | 08/23/19 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) | 19.08.23 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) | 23/08/19 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) | 23.08.19 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) | 23-08-19 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) | 23 авг 19 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) | Авг 23, 19 |
| 8 или 24 или 108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) | 13:39:17 |
| 9 или 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) | 23 авг 2019 13:39:17:090 |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) | 08-23-19 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) | 19/08/23 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) | 190823 |
| 13 или 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) | 23 авг 2019 13:39:17:090 |
| 14 или 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) | 13:39:17:090 |
| 20 или 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) | 2019-08-23 13:39:17 |
| 21 или 25 или 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) | 2019-08-23 13:39:17.090 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) | 23/08/19 13:39:17 |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) | 2019-08-23 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) | 08/23/2019 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) | 2019.08.23 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) | 23/08/2019 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) | 23.08.2019 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) | 23-08-2019 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) | 23 авг 2019 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) | Авг 23, 2019 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) | 08-23-2019 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) | 2019/08/23 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) | 20190823 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) | 23 авг 2019 13:39:17.090 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) | 2019-08-23 13:39:17 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) | 2019-08-23 13:39:17.090 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) | 2019-08-23T13:39:17.090 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) | 2019-08-23T13:39:17.090 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) | 22 ذو الحجة 1440 1:39:17.090P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) | 22/12/1440 13:39:17.090 |
K. Результат применения приоритета типов данных при допустимых преобразованиях
В следующем примере определяется переменная типа varchar(10),назначается целочисленное значение переменной, а затем выбирается объединение переменной со строкой.
DECLARE @string VARCHAR(10); SET @string = 1; SELECT @string + ' is a string.' AS Result Result ----------------------- 1 is a string. Значение int 1 было преобразовано в varchar.
В этом примере показан аналогичный запрос с помощью переменной int :
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + ' is not a string.' AS Result В этом случае инструкция SELECT выдает следующую ошибку:
Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ' is not a string.' to data type int. Чтобы оценить выражение @notastring + ‘ is not a string.’ , SQL Server должен следовать правилам приоритета типа данных, чтобы завершить неявное преобразование до вычисления результата выражения. Так как int имеет более высокий приоритет, чем varchar, SQL Server пытается преобразовать строку в целое число и завершается ошибкой, так как эта строка не может быть преобразована в целое число.
Если предоставить строку, которую можно преобразовать, инструкция завершается успешно, как показано в следующем примере:
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + '1' В этом случае строка ‘1’ может быть преобразована в целочисленное значение 1, поэтому инструкция SELECT возвращает значение 2. Если предоставленные типы данных являются целочисленными, оператор + становится математическим оператором сложения, а не объединения строк.
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
L. Использование CAST и CONVERT
В этом примере извлекается имя продукта для тех продуктов, которые имеют первую 3 цифру их цены на список, и преобразует ListPrice эти продукты в int. Она использует AdventureWorksDW2022 базу данных.
SELECT EnglishProductName AS ProductName, ListPrice FROM dbo.DimProduct WHERE CAST(ListPrice AS int) LIKE '3%'; В этом примере показан тот же запрос, который используется CONVERT вместо CAST . В нем используется база данных AdventureWorksDW2022 .
SELECT EnglishProductName AS ProductName, ListPrice FROM dbo.DimProduct WHERE CONVERT(INT, ListPrice) LIKE '3%'; M. Использование CAST с арифметическими операторами
В этом примере вычисляется отдельное значение столбца путем деления цены единицы товара ( UnitPrice ) на процент скидки ( UnitPriceDiscountPct ). Затем этот результат округляется до ближайшего целого числа и, наконец, преобразуется в тип данных int . В этом примере используется база данных AdventureWorksDW2022 .
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct, CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice FROM dbo.FactResellerSales WHERE SalesOrderNumber = 'SO47355' AND UnitPriceDiscountPct > .02; ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice ---------- --------- -------------------- ------------- 323 430.6445 0.05 22 213 18.5043 0.05 1 456 37.4950 0.10 4 456 37.4950 0.10 4 216 18.5043 0.05 1 О. Использование CAST с предложением LIKE
В этом примере столбец ListPrice типа money преобразуется в тип int, а затем в тип char(20) так, чтобы его можно было использовать в предложении LIKE. В этом примере используется база данных AdventureWorksDW2022 .
SELECT EnglishProductName AS Name, ListPrice FROM dbo.DimProduct WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%'; O. Использование CAST и CONVERT с данными datetime
В этом примере отображается текущая дата и время, используется CAST для изменения текущей даты и времени на тип данных символов, а затем используется CONVERT отображение даты и времени в формате ISO 8601. В этом примере используется база данных AdventureWorksDW2022 .
SELECT TOP(1) SYSDATETIME() AS UnconvertedDateTime, CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast, CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601 FROM dbo.DimCustomer; UnconvertedDateTime UsingCast UsingConvertTo_ISO8601 --------------------- --------------------------- --------------------------- 07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025 Этот пример — частичная противоположность предыдущему примеру. В этом примере отображаются данные даты и времени в качестве символьных данных, которые используются CAST для изменения символьных данных на тип данных datetime , а затем используются CONVERT для изменения символьных данных на тип данных datetime . В этом примере используется база данных AdventureWorksDW2022 .
SELECT TOP(1) '2010-07-25T13:50:38.544' AS UnconvertedText, CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast, CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601 FROM dbo.DimCustomer; UnconvertedText UsingCast UsingConvertFrom_ISO8601 ----------------------- ----------------------- ------------------------ 2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM См. также
- FORMAT (Transact-SQL)
- STR (Transact-SQL)
- SELECT (Transact-SQL)
- Системные функции (Transact-SQL)
- Поддержка параметров сортировки и Юникода
Следующие шаги
- Приоритет типа данных (Transact-SQL)
- Преобразование типов данных (ядро СУБД)
- Написание инструкций Transact-SQL, адаптированных к международному использованию
Топ-65 вопросов по SQL с собеседований, к которым вы должны подготовиться в 2019 году. Часть I
Реляционные базы данных являются одними из наиболее часто используемых баз данных по сей день, и поэтому навыки работы с SQL для большинства должностей являются обязательными. В этой статье с вопросами по SQL с собеседований я познакомлю вас с наиболее часто задаваемыми вопросами по SQL (Structured Query Language — язык структурированных запросов). Эта статья является идеальным руководством для изучения всех концепций, связанных с SQL, Oracle, MS SQL Server и базой данных MySQL.
Наша статья с вопросами по SQL — универсальный ресурс, с помощью которого вы можете ускорить подготовку к собеседованию. Она состоит из набора из 65 самых распространенных вопросов, которые интервьюер может задать во время собеседования. Оно обычно начинается с базовых вопросов по SQL, а затем переходит к более сложным на основе обсуждения и ваших ответов. Эти вопросы по SQL с собеседований помогут вам извлечь максимальную выгоду на различных уровнях понимания.
Давайте начнем!
Вопросы по SQL с собеседований
Вопрос 1. В чем разница между операторами DELETE и TRUNCATE?
| DELETE | TRUNCATE |
|---|---|
| Используется для удаления строки в таблице | Используется для удаления всех строк из таблицы |
| Вы можете восстановить данные после удаления | Вы не можете восстановить данные (прим. перевод.: операции логируются по разному, но в SQL Server есть возможность сделать откат) транзакции) |
| DML-команда | DDL-команда |
| Медленнее, чем оператор TRUNCATE | Быстрее |
№ Вопрос 2. Из каких подмножеств состоит SQL?
- DDL (Data Definition Language, язык описания данных) — позволяет выполнять различные операции с базой данных, такие как CREATE (создание), ALTER (изменение) и DROP (удаление объектов).
- DML (Data Manipulation Language, язык управления данными) — позволяет получать доступ к данным и манипулировать ими, например, вставлять, обновлять, удалять и извлекать данные из базы данных.
- DCL (Data Control Language, язык контролирования данных) — позволяет контролировать доступ к базе данных. Пример — GRANT (предоставить права), REVOKE (отозвать права).
Вопрос 3. Что подразумевается под СУБД? Какие существуют типы СУБД?
База данных — структурированная коллекция данных. Система управления базами данных (СУБД) — программное обеспечение, которое взаимодействует с пользователем, приложениями и самой базой данных для сбора и анализа данных. СУБД позволяет пользователю взаимодействовать с базой данных. Данные, хранящиеся в базе данных, могут быть изменены, извлечены и удалены. Они могут быть любых типов, таких как строки, числа, изображения и т. д.
Существует два типа СУБД:
- Реляционная система управления базами данных: данные хранятся в отношениях (таблицах). Пример — MySQL.
- Нереляционная система управления базами данных: не существует понятия отношений, кортежей и атрибутов. Пример — Mongo.
Вопрос 4. Что подразумевается под таблицей и полем в SQL?
Таблица — организованный набор данных в виде строк и столбцов. Поле — это столбцы в таблице. Например:
Таблица: Student_Information
Поле: Stu_Id, Stu_Name, Stu_Marks
Вопрос 5. Что такое соединения в SQL?
Для соединения строк из двух или более таблиц на основе связанного между ними столбца используется оператор JOIN. Он используется для объединения двух таблиц или получения данных оттуда. В SQL есть 4 типа соединения, а именно:
- Inner Join (Внутреннее соединение)
- Right Join (Правое соединение)
- Left Join (Левое соединение)
- Full Join (Полное соединение)
Вопрос 6. В чем разница между типом данных CHAR и VARCHAR в SQL?
И Char, и Varchar служат символьными типами данных, но varchar используется для строк символов переменной длины, тогда как Char используется для строк фиксированной длины. Например, char(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как varchar(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.
Вопрос 7. Что такое первичный ключ (Primary key)?
- Первичный ключ — столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.
- Однозначно идентифицирует одну строку в таблице
- Нулевые (Null) значения не допускаются
_Пример: в таблице Student StuID является первичным ключом.
Вопрос 8. Что такое ограничения (Constraints)?
Ограничения (constraints) используются для указания ограничения на тип данных таблицы. Они могут быть указаны при создании или изменении таблицы. Пример ограничений:
Вопрос 9. В чем разница между SQL и MySQL?
SQL — стандартный язык структурированных запросов (Structured Query Language) на основе английского языка, тогда как MySQL — система управления базами данных. SQL — язык реляционной базы данных, который используется для доступа и управления данными, MySQL — реляционная СУБД (система управления базами данных), также как и SQL Server, Informix и т. д.
Вопрос 10. Что такое уникальный ключ (Unique key)?
- Однозначно идентифицирует одну строку в таблице.
- Допустимо множество уникальных ключей в одной таблице.
- Допустимы NULL-значения (прим. перевод.: зависит от СУБД, в SQL Server значение NULL может быть добавлено только один раз в поле с UNIQUE KEY).
Вопрос 11. Что такое внешний ключ (Foreign key)?
- Внешний ключ поддерживает ссылочную целостность, обеспечивая связь между данными в двух таблицах.
- Внешний ключ в дочерней таблице ссылается на первичный ключ в родительской таблице.
- Ограничение внешнего ключа предотвращает действия, которые разрушают связи между дочерней и родительской таблицами.
Вопрос 12. Что подразумевается под целостностью данных?
Целостность данных определяет точность, а также согласованность данных, хранящихся в базе данных. Она также определяет ограничения целостности для обеспечения соблюдения бизнес-правил для данных, когда они вводятся в приложение или базу данных.
Вопрос 13. В чем разница между кластеризованным и некластеризованным индексами в SQL?
- Различия между кластеризованным и некластеризованным индексами в SQL:
Кластерный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из некластеризованного индекса происходит относительно медленнее. - Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в некластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске.
- Одна таблица может иметь только один кластеризованный индекс, тогда как некластеризованных у нее может быть много.
Вопрос 14. Напишите SQL-запрос для отображения текущей даты.
В SQL есть встроенная функция GetDate (), которая помогает возвращать текущий timestamp/дату.
Вопрос 15. Перечислите типы соединений
Существуют различные типы соединений, которые используются для извлечения данных между таблицами. Принципиально они делятся на четыре типа, а именно:
Inner join (Внутреннее соединение): в MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие соединения.
Left Join (Левое соединение): в MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.
Right Join (Правое соединение): в MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.
Full Join (Полное соединение): возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.
Вопрос 16. Что вы подразумеваете под денормализацией?
Денормализация — техника, которая используется для преобразования из высших к низшим нормальным формам. Она помогает разработчикам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Она добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.
Вопрос 17. Что такое сущности и отношения?
Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например — база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.
Отношения: отношения или связи между сущностями, которые имеют какое-то отношение друг к другу. Например — имя клиента связано с номером учетной записи клиента и контактной информацией, которая может быть в той же таблице. Также могут быть отношения между отдельными таблицами (например, клиент к счетам).
Вопрос 18. Что такое индекс?
Индексы относятся к методу настройки производительности, позволяющему быстрее извлекать записи из таблицы. Индекс создает отдельную структуру для индексируемого поля и, следовательно, позволяет быстрее получать данные.
Вопрос 19. Опишите различные типы индексов.
Есть три типа индексов, а именно:
- Уникальный индекс (Unique Index): этот индекс не позволяет полю иметь повторяющиеся значения, если столбец индексируется уникально. Если первичный ключ определен, уникальный индекс может быть применен автоматически.
- Кластеризованный индекс (Clustered Index): этот индекс меняет физический порядок таблицы и выполняет поиск на основе значений ключа. Каждая таблица может иметь только один кластеризованный индекс.
- Некластеризованный индекс (Non-Clustered Index): не изменяет физический порядок таблицы и поддерживает логический порядок данных. Каждая таблица может иметь много некластеризованных индексов.
Вопрос 20. Что такое нормализация и каковы ее преимущества?
Нормализация — процесс организации данных, цель которого избежать дублирования и избыточности. Некоторые из преимуществ:
- Лучшая организация базы данных
- Больше таблиц с небольшими строками
- Эффективный доступ к данным
- Большая гибкость для запросов
- Быстрый поиск информации
- Проще реализовать безопасность данных
- Позволяет легко модифицировать
- Сокращение избыточных и дублирующихся данных
- Более компактная база данных
- Обеспечивает согласованность данных после внесения изменений
Вопрос 21. В чем разница между командами DROP и TRUNCATE?
Команда DROP удаляет саму таблицу, и нельзя сделать Rollback команды, тогда как команда TRUNCATE удаляет все строки из таблицы (прим. перевод.: в SQL Server Rollback нормально отработает и откатит DROP).
Вопрос 22. Объясните различные типы нормализации.
Существует много последовательных уровней нормализации. Это так называемые нормальные формы. Каждая последующая нормальная форма включает предыдущую. Первых трех нормальных форм обычно достаточно.
- Первая нормальная форма (1NF) — нет повторяющихся групп в строках
- Вторая нормальная форма (2NF) — каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа
- Третья нормальная форма (3NF) — каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца
Вопрос 23. Что такое свойство ACID в базе данных?
ACID означает атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), долговечность (Durability). Он используется для обеспечения надежной обработки транзакций данных в системе базы данных.
Атомарность. Гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных. Это означает, что при сбое одной части любой транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.
Согласованность. Гарантирует, что данные должны соответствовать всем правилам валидации. Проще говоря, вы можете сказать, что ваша транзакция никогда не оставит вашу базу данных в недопустимом состоянии.
Изолированность. Основной целью изолированности является контроль механизма параллельного изменения данных.
Долговечность. Долговечность подразумевает, что если транзакция была подтверждена (COMMIT), произошедшие в рамках транзакции изменения сохранятся независимо от того, что может встать у них на пути (например, потеря питания, сбой или ошибки любого рода).
Вопрос 24. Что вы подразумеваете под «триггером» в SQL?
Триггер в SQL — особый тип хранимых процедур, которые предназначены для автоматического выполнения в момент или после изменения данных. Это позволяет вам выполнить пакет кода, когда вставка, обновление или любой другой запрос выполняется к определенной таблице.
Вопрос 25. Какие операторы доступны в SQL?
В SQL доступно три типа оператора, а именно:
- Арифметические Операторы
- Логические Операторы
- Операторы сравнения
Вопрос 26. Совпадают ли значения NULL со значениями нуля или пробела?
Значение NULL вовсе не равно нулю или пробелу. Значение NULL представляет значение, которое недоступно, неизвестно, присвоено или неприменимо, тогда как ноль — это число, а пробел — символ.
Вопрос 27. В чем разница между перекрестным (cross join) и естественным (natural join) соединением?
Перекрестное соединение создает перекрестное или декартово произведение двух таблиц, тогда как естественное соединение основано на всех столбцах, имеющих одинаковое имя и типы данных в обеих таблицах.
Вопрос 28. Что такое подзапрос в SQL?
Подзапрос — это запрос внутри другого запроса, в котором определен запрос для извлечения данных или информации из базы данных. В подзапросе внешний запрос называется основным запросом, тогда как внутренний запрос называется подзапросом. Подзапросы всегда выполняются первыми, а результат подзапроса передается в основной запрос. Он может быть вложен в SELECT, UPDATE или любой другой запрос. Подзапрос также может использовать любые операторы сравнения, такие как >, < или =.
Вопрос 29. Какие бывают типы подзапросов?
Существует два типа подзапросов, а именно: коррелированные и некоррелированные.
- Коррелированный подзапрос: это запрос, который выбирает данные из таблицы со ссылкой на внешний запрос. Он не считается независимым запросом, поскольку ссылается на другую таблицу или столбец в таблице.
- Некоррелированный подзапрос: этот запрос является независимым запросом, в котором выходные данные подзапроса подставляются в основной запрос.
Вопрос 30. Перечислите способы получить количество записей в таблице?
Для подсчета количества записей в таблице вы можете использовать следующие команды:
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE AND indid < 2
Ещё 35 вопросов с ответами опубликуем в следующей части… Следите за новостями!
Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть первая
Данный учебник создан по принципу 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 можно использовать 2 вида комментариев (однострочный и многострочный):
-- однострочный комментарий /* многострочный комментарий */ Собственно, все для теории этого будет достаточно.
DDL – Data Definition Language (язык описания данных)
Для примера рассмотрим таблицу с данными о сотрудниках, в привычном для человека не являющимся программистом виде:
| Табельный номер | ФИО | Дата рождения | Должность | Отдел | |
|---|---|---|---|---|---|
| 1000 | Иванов И.И. | 19.02.1955 | i.ivanov@test.tt | Директор | Администрация |
| 1001 | Петров П.П. | 03.12.1983 | p.petrov@test.tt | Программист | ИТ |
| 1002 | Сидоров С.С. | 07.06.1976 | s.sidorov@test.tt | Бухгалтер | Бухгалтерия |
| 1003 | Андреев А.А. | 17.04.1982 | a.andreev@test.tt | Старший программист | ИТ |
В данном случае столбцы таблицы имеют следующие наименования: Табельный номер, ФИО, Дата рождения, 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, [E-mail] nvarchar(30), [Должность] nvarchar(30), [Отдел] nvarchar(30) ) В данном случае нам придется заключать имена в квадратные скобки […].
Но в базе данных для большего удобства все наименования объектов лучше задавать на латинице и не использовать в именах пробелы. В MS SQL обычно в данном случае каждое слово начинается с прописной буквы, например, для поля «Табельный номер», мы могли бы задать имя PersonnelNumber. Так же в имени можно использовать цифры, например, PhoneNumber1.
На заметку
В некоторых СУБД более предпочтительным может быть следующий формат наименований «PHONE_NUMBER», например, такой формат часто используется в БД ORACLE. Естественно при задании имя поля желательно чтобы оно не совпадало с ключевыми словами используемые в СУБД.
По этой причине можете забыть о синтаксисе с квадратными скобками и удалить таблицу [Сотрудники]:
DROP TABLE [Сотрудники] - ID – Табельный номер (Идентификатор сотрудника)
- Name – ФИО
- Birthday – Дата рождения
- Email – E-mail
- Position – Должность
- Department – Отдел
Теперь создадим нашу таблицу:
CREATE TABLE Employees( ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30) ) Для того, чтобы задать обязательные для заполнения столбцы, можно использовать опцию NOT NULL.
Для уже существующей таблицы поля можно переопределить при помощи следующих команд:
-- обновление поля ID ALTER TABLE Employees ALTER COLUMN ID int NOT NULL -- обновление поля Name ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL На заметку
Общая концепция языка SQL для большинства СУБД остается одинаковой (по крайней мере, об этом я могу судить по тем СУБД, с которыми мне довелось поработать). Отличие DDL в разных СУБД в основном заключаются в типах данных (здесь могут отличаться не только их наименования, но и детали их реализации), так же может немного отличаться и сама специфика реализации языка SQL (т.е. суть команд одна и та же, но могут быть небольшие различия в диалекте, увы, но одного стандарта нет). Владея основами SQL вы легко сможете перейти с одной СУБД на другую, т.к. вам в данном случае нужно будет только разобраться в деталях реализации команд в новой СУБД, т.е. в большинстве случаев достаточно будет просто провести аналогию.
Чтобы не быть голословным, приведу несколько примеров тех же команд для СУБД ORACLE:
-- создание таблицы CREATE TABLE Employees( ID int, -- в ORACLE тип int - это эквивалент(обертка) для number(38) Name nvarchar2(30), -- nvarchar2 в ORACLE эквивалентен nvarchar в MS SQL Birthday date, Email nvarchar2(30), 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 и снова зальем данные:
INSERT Employees(ID,Position,Department,Name) VALUES (1000,N'Директор',N'Администрация',N'Иванов И.И.'), (1001,N'Программист',N'ИТ',N'Петров П.П.'), (1002,N'Бухгалтер',N'Бухгалтерия',N'Сидоров С.С.'), (1003,N'Старший программист',N'ИТ',N'Андреев А.А.') Так же опцию 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(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 имя_таблицы 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 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 | ID | Name |
|---|---|
| 1 | Бухгалтер |
| 2 | Директор |
| 3 | Программист |
| 4 | Старший программист |
SELECT * FROM Departments | ID | Name |
|---|---|
| 1 | Администрация |
| 2 | Бухгалтерия |
| 3 | ИТ |
Данные таблицы теперь и будут играть роль справочников для задания должностей и отделов. Теперь мы будем ссылаться на идентификаторы должностей и отделов. В первую очередь создадим новые поля в таблице 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 | ID | Name | Birthday | Position | Department | PositionID | DepartmentID | |
|---|---|---|---|---|---|---|---|
| 1000 | Иванов И.И. | NULL | NULL | Директор | Администрация | 2 | 1 |
| 1001 | Петров П.П. | NULL | NULL | Программист | ИТ | 3 | 3 |
| 1002 | Сидоров С.С. | NULL | NULL | Бухгалтер | Бухгалтерия | 1 | 2 |
| 1003 | Андреев А.А. | NULL | NULL | Старший программист | ИТ | 4 | 3 |
Всё, поля PositionID и DepartmentID заполнены соответствующие должностям и отделам идентификаторами надобности в полях Position и Department в таблице Employees теперь нет, можно удалить эти поля:
ALTER TABLE Employees DROP COLUMN Position,Department Теперь таблица у нас приобрела следующий вид:
SELECT * FROM Employees | ID | Name | Birthday | 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 | ID | Name | PositionName | DepartmentName |
|---|---|---|---|
| 1000 | Иванов И.И. | Директор | Администрация |
| 1001 | Петров П.П. | Программист | ИТ |
| 1002 | Сидоров С.С. | Бухгалтер | Бухгалтерия |
| 1003 | Андреев А.А. | Старший программист | ИТ |
В инспекторе объектов мы можем увидеть все объекты, созданные для в данной таблицы. Отсюда же можно производить разные манипуляции с данными объектами – например, переименовывать или удалять объекты.
Так же стоит отметить, что таблица может ссылаться сама на себя, т.е. можно создать рекурсивную ссылку. Для примера добавим в нашу таблицу с сотрудниками еще одно поле 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 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:
DELETE Departments WHERE > Посмотрим на данные таблицы Employees:
SELECT * FROM Employees | ID | Name | Birthday | 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 > Главное понять суть этих 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:
INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N'Иванов И.И.','19550219',2,1,NULL), (1001,N'Петров П.П.','19831203',3,3,1003), (1002,N'Сидоров С.С.','19760607',1,2,1000), (1003,N'Андреев А.А.','19820417',4,3,1000) Подытожим
- Добавление свойства IDENTITY к полю – позволяет сделать это поле автоматически заполняемым (полем-счетчиком) для таблицы;
- ALTER TABLE имя_таблицы ADD перечень_полей_с_характеристиками – позволяет добавить новые поля в таблицу;
- ALTER TABLE имя_таблицы DROP COLUMN перечень_полей – позволяет удалить поля из таблицы;
- ALTER TABLE имя_таблицы ADD CONSTRAINT имя_ограничения FOREIGN KEY(поля) REFERENCES таблица_справочник(поля) – позволяет определить связь между таблицей и таблицей справочником.
Прочие ограничения – UNIQUE, DEFAULT, CHECK
При помощи ограничения UNIQUE можно сказать что значения для каждой строки в данном поле или в наборе полей должно быть уникальным. В случае таблицы Employees, такое ограничение мы можем наложить на поле Email. Только предварительно заполним Email значениями, если они еще не определены:
UPDATE Employees SET Email='i.ivanov@test.tt' WHERE Employees SET Email='p.petrov@test.tt' WHERE Employees SET Email='s.sidorov@test.tt' WHERE Employees SET Email='a.andreev@test.tt' WHERE > А теперь можно наложить на это поле ограничение-уникальности:
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'Сергеев С.С.','s.sergeev@test.tt') Посмотрим, что получилось:
SELECT * FROM Employees | ID | Name | Birthday | PositionID | DepartmentID | ManagerID | HireDate | |
|---|---|---|---|---|---|---|---|
| 1000 | Иванов И.И. | 1955-02-19 | i.ivanov@test.tt | 2 | 1 | NULL | 2015-04-08 |
| 1001 | Петров П.П. | 1983-12-03 | p.petrov@test.tt | 3 | 4 | 1003 | 2015-04-08 |
| 1002 | Сидоров С.С. | 1976-06-07 | s.sidorov@test.tt | 1 | 2 | 1000 | 2015-04-08 |
| 1003 | Андреев А.А. | 1982-04-17 | a.andreev@test.tt | 4 | 3 | 1000 | 2015-04-08 |
| 1004 | Сергеев С.С. | NULL | s.sergeev@test.tt | 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,'test@test.tt') А теперь изменим вставляемое значение на 1500 и убедимся, что запись вставится:
INSERT Employees(ID,Email) VALUES(1500,'test@test.tt') Можно так же создать ограничения 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 я сделаю исключение 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) ) Напоследок вставим в таблицу наших сотрудников:
INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N'Иванов И.И.','19550219','i.ivanov@test.tt',2,1), (1001,N'Петров П.П.','19831203','p.petrov@test.tt',3,3), (1002,N'Сидоров С.С.','19760607','s.sidorov@test.tt',1,2), (1003,N'Андреев А.А.','19820417','a.andreev@test.tt',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 | PositionID | DepartmentID | HireDate | |
|---|---|---|---|---|---|---|
| 1003 | Андреев А.А. | 1982-04-17 | a.andreev@test.tt | 4 | 3 | 2015-04-08 |
| 1000 | Иванов И.И. | 1955-02-19 | i.ivanov@test.tt | 2 | 1 | 2015-04-08 |
| 1001 | Петров П.П. | 1983-12-03 | p.petrov@test.tt | 3 | 3 | 2015-04-08 |
| 1002 | Сидоров С.С. | 1976-06-07 | s.sidorov@test.tt | 1 | 2 | 2015-04-08 |
До этого, когда кластерным индексом был индекс PK_Employees, записи по умолчанию сортировались по полю ID.
Но в данном случае это всего лишь пример, который показывает суть кластерного индекса, т.к. скорее всего к таблице Employees будут делаться запросы по полю ID и в каких-то случаях, возможно, она сама будет выступать в роли справочника.
Для справочников обычно целесообразно, чтобы кластерный индекс был построен по первичному ключу, т.к. в запросах мы часто ссылаемся на идентификатор справочника для получения, например, наименования (Должности, Отдела). Здесь вспомним, о чем я писал выше, что кластерный индекс имеет прямой доступ к строкам таблицы, а отсюда следует, что мы можем получить значение любого столбца без дополнительных накладных расходов.
Кластерный индекс выгодно применять к полям, по которым выборка идет наиболее часто.
Иногда в таблицах создают ключ по суррогатному полю, вот в этом случае бывает полезно сохранить опцию CLUSTERED индекс для более подходящего индекса и указать опцию NONCLUSTERED при создании суррогатного первичного ключа.
Подытожим
- PRIMARY KEY – первичный ключ;
- FOREIGN KEY – настройка связей и контроль ссылочной целостности данных;
- UNIQUE – позволяет создать уникальность;
- CHECK – позволяет осуществлять корректность введенных данных;
- DEFAULT – позволяет задать значение по умолчанию;
- Так же стоит отметить, что все ограничения можно удалить, используя команду «ALTER TABLE имя_таблицы DROP CONSTRAINT имя_ограничения».
Создание самостоятельных индексов
Под самостоятельностью здесь имеются в виду индексы, которые создаются не для ограничения 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 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) ) Напоследок вставим в таблицу наших сотрудников:
INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N'Иванов И.И.','19550219','i.ivanov@test.tt',2,1,NULL), (1001,N'Петров П.П.','19831203','p.petrov@test.tt',3,3,1003), (1002,N'Сидоров С.С.','19760607','s.sidorov@test.tt',1,2,1000), (1003,N'Андреев А.А.','19820417','a.andreev@test.tt',4,3,1000) Дополнительно стоит отметить, что в некластерный индекс можно включать значения при помощи указания их в INCLUDE. Т.е. в данном случае INCLUDE-индекс чем-то будет напоминать кластерный индекс, только теперь не индекс прикручен к таблице, а необходимые значения прикручены к индексу. Соответственно, такие индексы могут очень повысить производительность запросов на выборку (SELECT), если все перечисленные поля имеются в индексе, то возможно обращений к таблице вообще не понадобится. Но это естественно повышает размер индекса, т.к. значения перечисленных полей дублируются в индексе.
Вырезка из MSDN. Общий синтаксис команды для создания индексов
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON ( column [ ASC | DESC ] [ . n ] ) [ INCLUDE ( column_name [ . n ] ) ] Подытожим
Индексы могут повысить скорость выборки данных (SELECT), но индексы уменьшают скорость модификации данных таблицы, т.к. после каждой модификации системе будет необходимо перестроить все индексы для конкретной таблицы.
Желательно в каждом случае найти оптимальное решение, золотую середину, чтобы и производительность выборки, так и модификации данных была на должном уровне. Стратегия по созданию индексов и их количества может зависеть от многих факторов, например, насколько часто изменяются данные в таблице.
Заключение по DDL
Как можно увидеть, язык DDL не так сложен, как может показаться на первый взгляд. Здесь я смог показать практически все его основные конструкции, оперируя всего тремя таблицами.
Главное — понять суть, а остальное дело практики.
Удачи вам в освоении этого замечательного языка под названием SQL.
История языков программирования: SQL- стандартизация длиною в жизнь
По мнению аналитиков CodingDojo, SQL — самый важный и нужный язык запросов среди языков программирования, как бы странно это ни звучало. Рейтинг CodingDojo учитывает статистику востребованности языков программирования на рынке труда.
Ведь СУБД – MySQL, PostgreSQL и Microsoft SQL Server – распространены повсеместно: в крупном и малом бизнесе, в больницах, банках, университетах и так далее. В принципе, SQL не ограничивается только настольными девайсами: СУБД SQLite с успехом заняла свое место на Android-смартфонах и мобильных устройствах Apple. Соответственно, такие приложения, как Skype и Dropbox, постоянно к ней обращаются.
Однако были времена, когда не было смартфонов, а этот язык уже существовал. История SQL – это не годы, но десятилетия. Поверили в него не сразу.
System R и IBM
Первые упоминания об этом языке датируются 1974 годом. SQL создавался в рамках проекта экспериментальной реляционной СУБД System R. Занималась этим проектом компания IBM.
Первоначально язык назывался SEQUEL (Structured English Query Language), но потом слово «English» пропало из этого словосочетания, а аббревиатура приобрела тот вид, к которому мы давно уже привыкли. С одной стороны, SQL был ориентирован на удобную и понятную пользователям формулировку запросов к реляционным БД. С другой стороны, практически с самого начала он был так называемым «полным языком БД». Это означает, что SQL включал:
• средства определения и манипулирования схемой БД;
• средства определения ограничений целостности и триггеров;
• средства определения представлений БД;
• средства определения структур физического уровня, поддерживающих эффективное выполнение запросов;
• средства авторизации доступа к отношениям и их полям;
• средства определения точек сохранения транзакции и выполнения фиксации и откатов транзакций.
Правда, в нем не были реализованы средства синхронизации доступа к объектам БД со стороны параллельно выполняемых транзакций. Дело в том, что разработчики изначально рассчитывали, что необходимую синхронизацию неявно выполняет СУБД.
Язык реализован в подавляющем большинстве СУБД – как в реляционных, так и нереляционных. Целью разработки было создание простого непроцедурного языка, которым мог воспользоваться любой пользователь, даже не имеющий навыков программирования.
Разработкой языка запросов занимались Дональд Чэмбэрлин (Donald D. Chamberlin) и Рэй Бойс (Ray Boyce).
SEQUEL был не единственным языком подобного назначения. В Калифорнийском Университете Беркли была разработана некоммерческая СУБД Ingres, которая являлась реляционной СУБД, но использовала свой собственный язык QUEL, который, однако, не выдержал конкуренции по количеству поддерживающих его СУБД по сравнению с языком SQL.
В System R была реализована наиболее сложная и полная версия SQL. Чуть меньше функциональности было в SQL/DS и DB2 от той же IBM. Из SQL System R были удалены только те части, которые были недостаточно проработаны (например, точки сохранения) или реализация которых вызывала слишком большие технические трудности (например, ограничения целостности и триггеры).
Коммерческий успех
Поэтому путь к коммерческой реализации SQL, который прошла IBM, называют движением «сверху вниз».
Oracle, Informix и Sybase пошли по другому пути – «снизу вверх»: в первых версиях этих систем, выпущенных на рынок, использовалось существенно ограниченное подмножество SQL System R. А далее они начали постепенно расширяться. Однако в первой коммерческой реализации SQL в СУБД Oracle в операторах выборки не допускалось использование вложенных подзапросов и отсутствовала возможность формулировки запросов с соединениями нескольких отношений.
Распеределение рыночных долей по состоянию на 2011 год
Растущая заинтересованность рынка в скорейшем переходе к реляционным системам управления базами данных позволила разработчикам перечисленных выше компаний добиться коммерческого успеха. Это произошло, скорее, вопреки тому, что СУБД были тогда очень далеки от совершенства. Ну а теперь Oracle, Informix, Sybase и Microsoft SQL Server поддерживают достаточно мощные диалекты SQL.
Стандартизация
Появление многочисленных диалектов SQL и их разрастание должно было привести к проблемам совместимости и прочим противоречиям.
Однако деятельность по стандартизации языка SQL началась очень вовремя – практически одновременно с появлением его первых коммерческих реализаций. В 1982 году комитету по базам данных Американского национального института стандартов (ANSI) было поручено разработать спецификацию стандартного языка реляционных баз данных.
После отклонения ряда неудачных версий стандарта в 1986 году эксперты пришли к единому знаменателю. А в 1987 году стандарт SQL/86 был одобрен Международной организацией по стандартизации (ISO).
За основу стандарта нельзя было брать SQL System R. Во-первых, этот вариант языка был недостаточно проработан технически. Во-вторых, его слишком сложно было бы реализовать. Поэтому за основу был взят диалект языка SQL, сложившийся в IBM к началу 1980-х годов. В сущности, этот диалект представлял собой подмножество SQL System R.
Стандарт SQL1
К 1989 году стандарт SQL/86 был несколько расширен, после чего появился следующий стандарт, получивший название ANSI/ISO SQL/89.
SQL/89 стал первым всемирно принятым стандартом языка SQL. У этого языка имеется масса недостатков: многие важные понятия не определены, много отдано на откуп реализациям. В этом стандарте полностью отсутствуют такие важные разделы, как манипулирование схемой БД и динамический SQL.
Но тем не менее он сыграл свою роль в становлении действительно стандартизованных реляционных систем управления базами данных. Более того, с появлением стандарта SQL/89 стало возможно проектировать, разрабатывать и сопровождать информационные системы, не слишком привязанные к конкретному производителю СУБД. В некотором смысле появление SQL/89 явилось продвижением технологии баз данных в сторону открытых систем.
Возможно, наиболее важными достижениями стандарта SQL/89 являются четкая стандартизация синтаксиса, семантики операторов выборки данных и манипулирования данными, а также фиксация средств ограничения целостности БД.
В стандарте определяются два уровня языка и отдельное средство поддержания целостности. Уровень 2 — это полный язык баз данных SQL, не включающий средство поддержания целостности. Уровень 1 — это специфицированное подмножество уровня 2.
Средство поддержания целостности включает возможности определения:
• требуемых ограничений на ссылки между таблицами;
• проверочных ограничений на строки таблицы;
• значений столбца по умолчанию при занесении строки в таблицу.
Средства определения внешних ключей позволяют легко формулировать требования так называемой ссылочной целостности БД. Это распространенное в реляционных БД требование можно было сформулировать и на основе общего механизма ограничений целостности SQL System R, но формулировка на основе понятия внешнего ключа более проста и понятна.
Возможности операции Join в разных стандартах
Стандарт SQL2 и его дополнения
Осознавая неполноту стандарта SQL, специалисты различных компаний начали работу над очередным стандартом, который получил название SQL2. Эта работа также длилась несколько лет, было выпущено множество проектов стандарта, пока наконец в марте 1992 года не был принят окончательный проект стандарта (SQL/92). Этот стандарт существенно полнее стандарта SQL/89 и охватывает практически все аспекты, необходимые для реализации приложений: манипулирование схемой БД, управление транзакциями (появились точки сохранения) и сессиями (сессия – это последовательность транзакций, в пределах которой сохраняются временные отношения), подключения к БД, динамический SQL. Наконец, были стандартизованы отношения-каталоги БД, что вообще-то не связано непосредственно с языком, но очень сильно влияет на реализацию.
В 1995 году стандарт был дополнен спецификацией интерфейса уровня вызова (Call-Level Interface – SQL/CLI). SQL/CLI представляет собой набор спецификаций интерфейсов процедур, вызовы которых позволяют выполнять динамически задаваемые операторы SQL. По сути дела, SQL/CLI представляет собой альтернативу динамическому SQL.
Стандарт SQL/CLI послужил основой для создания повсеместно распространенных сегодня интерфейсов ODBC (Open Database Connectivity) и JDBC (Java Database Connectivity).
В 1996 году к стандарту SQL/92 был добавлен еще один компонент – SQL/PSM (Persistent Stored Modules). Основная цель этой спецификации – стандартизировать способы определения и использования хранимых процедур, то есть специальным образом оформленных программ, включающих операторы SQL, которые сохраняются в базе данных, могут вызываться приложениями и выполняются внутри СУБД.
Oracle является одной из наиболее популярных СУБД. Более того, именно там впервые была реализована совместимость со стандартом SQL/92.
А изначально первой СУБД, поддерживающей язык SQL, стала Oracle V2, разработанная для машин VAX. Это было еще в в 1979 году.
Oracle поддерживает ряд различных платформ, включая Windows, Linux, Max OS X и Sun Solaris.
Процедурное расширение SQL, разработанное Oracle, называется PL/SQL (Procedural Language/Structured Query Language) и основано на синтаксисе языков Ada и Pascal. Третьим ключевым языком, использующийся в СУБД Oracle наравне с SQL и PL/SQL, является Java.
PL/SQL поддерживает программные блоки, а также разнообразные типы данных для хранения чисел, строк и дат, операторы управления потоком вычислений (в том числе условные переходы и циклы) и три типа контейнеров (коллекций) — массивы переменной длины, ассоциативные массивы и вложенные таблицы.
Стандарт SQL3
Первоначально планировалось закончить работу над новым стандартом в 1995 году. Реально работу над новым стандартом удалось частично завершить только в 1999 году, и по этой причине стандарт получил название SQL:1999.
Каждый новый вариант стандарта языка SQL был существенно объемнее предыдущих версий. Так, если стандарт SQL/89 занимал около 600 страниц, то объем SQL/92 составлял на 300 с лишним страниц больше.
Самые первые проекты SQL3 занимали около 1500 страниц.
Однако разработчики SQL3 пришли к выводу, что при таких объемах стандарта вероятность его принятия и последующей успешной поддержки заметно уменьшается. Поэтому они решили разбить стандарт на относительно независимые части, которые можно было бы разрабатывать и поддерживать по отдельности.
В 1999 году были приняты пять частей стандарта SQL:1999.
Первая часть (SQL/Framework) посвящена описанию концептуальной структуры стандарта. В этой части приводится развернутая аннотация следующих четырех частей и формулируются требования к реализациям, претендующим на соответствие стандарту.
Вторая часть SQL:1999 (SQL/Foundation) образует базис стандарта. Вводится система типов языка, формулируются правила определения функциональных зависимостей и возможных ключей, определяются синтаксис и семантика основных операторов SQL:
• операторов определения и манипулирования схемой базы данных;
• операторов манипулирования данными;
• операторов управления транзакциями;
• операторов управления подключениями к базе данных и т. д.
Третью часть занимает уточненная по сравнению с SQL/92 спецификация SQL/CLI. В четвертой части специфицируется SQL/PSM – синтаксис и семантика языка определения хранимых процедур. Наконец, в пятой части – SQL/Bindings – определяются правила связывания SQL для стандартных версий языков программирования.
В стандарт SQL:1999 должны были войти еще несколько частей. Среди них спецификации следующих средств:
• управление распределенными транзакциями (SQL/Transaction);
• поддержка темпоральных свойств данных (SQL/Temporal);
• управление внешними данными (SQL/MED);
• связывание с объектно-ориентированными языками программирования (SQL/OLB);
• поддержка оперативной аналитической обработки (SQL/OLAP).
SQL в XXI веке
В конце 2003 года был принят и опубликован новый вариант международного стандарта SQL:2003. Многие специалисты считали, что в варианте стандарта, следующем за SQL:1999, будут всего лишь исправлены неточности SQL:1999. Но на самом деле, в SQL:2003 специфицирован ряд новых и важных свойств, с небольшими модификациями, внесёнными позже в 2008 году.
Наиболее серьезные изменения языка SQL, специфицированные в части 2 стандарта SQL:2003, касаются следующих аспектов:
• типы данных;
• подпрограммы, вызываемые из SQL;
• расширенные возможности оператора CREATE TABLE;
• новый объект схемы – генератор последовательностей;
• новые виды столбцов – идентифицирующие столбцы (identity column) и генерируемые столбцы (generated column);
• новый оператор MERGE;
Претерпела некоторые изменения общая организация стандарта. Стандарт SQL:2003 состоит из следующих частей:
• 9075-1, SQL/Framework;
• 9075-2, SQL/Foundation;
• 9075-3, SQL/CLI;
• 9075-4, SQL/PSM;
• 9075-9, SQL/MED;
• 9075-10, SQL/OLB;
• 9075-11, SQL/Schemata;
• 9075-13, SQL/JRT;
• 9075-14, SQL/XML.
Части 1-4 и 9-10 с необходимыми изменениями остались такими же, как и в SQL:1999. Часть 5 (SQL/Bindings) перестала существовать; соответствующие спецификации включены в часть 2.
Раздел части 2 SQL:1999, посвященный информационной схеме, выделен в отдельную часть 11. Появились две новые части – 13 и 14.
Часть 13 полностью называется «SQL Routines and Types Using the Java Programming Language» («Использование подпрограмм и типов SQL в языке программирования Java»). Появление такой части стандарта оправдано повышенным вниманием к языку Java со стороны ведущих производителей SQL-ориентированных СУБД.
Наконец, последняя часть SQL:2003 посвящена спецификациям языковых средств, позволяющих работать с XML-документами в среде SQL.
Несмотря на старания разработчиков, процесс стандартизации явно не поспевает за происходящими изменениями.
Основные моменты в истории SQL
Тем не менее, можно сказать, что базовый набор операторов SQL, включающий операторы определения схемы БД, выборки и манипулирования данными, авторизации доступа к данным, поддержки встраивания SQL в языки программирования и операторы динамического SQL, в коммерческих реализациях устоялся и более или менее соответствует стандарту.
P.S. SQL нельзя в полной мере отнести к традиционным языкам программирования, он не содержит традиционные операторы, управляющие ходом выполнения программы, операторы описания типов и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных. Однако эти «недостатки» ему прощают, учитывая его место в истории ИТ.