лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 38 из 47

SQL Server 2008
chr
vchr
3
3
Итак, будет вставлена только одна строка, содержащая по одному символу для каждого из столбцов. При вставке остальных строк получаем сообщение об ошибке:
String or binary data would be truncated. The statement has been terminated. которое означает, что следует уменьшить размер строки.
Хотя здесь выдержано соответствие стандарту, мне кажется, что есть некое противоречие в том, что не работает явное приведение к типу столбца таблицы:
1.
INSERT
INTO
Test_char

2.
VALUES
(
CAST
(
'111111111122222222223333333333'
AS
CHAR
)
,
'2'
)
;
PostgreSQL 8.3
chr
vchr
1
11111111112222222222333333333344444444445555555555
2
111111111122222222223333333333
1
2
3
3
Можно отметить последовательность в поведении: VARCHAR имеет произвольный размер; вторая строка не была вставлена из-за ошибки превышения размера (ERROR: value too long for type character(1)); явное преобразование значения к типу столбца таблицы работает, отсекая лишние символы справа.
MySQL 5.0
Не поддерживается тип VARCHAR без указания размера строки. CHAR соответствует CHAR(1) – по стандарту. Поскольку явное преобразование к
CHAR оставляет длину строки без изменения, то в таблицу, определенную как
1.
CREATE
TABLE
Test_char
(
chr CHAR, vchr VARCHAR
(
1
)
)
; в итоге, как и в SQL Server, добавится единственная строка:

chr
vchr
3
3
Выводы. По моему скромному мнению, ни одна из упомянутых СУБД не отвечает стандартному поведению в тех случаях, когда размер типа не указывается. Наиболее последовательной в «особенностях реализации» является, на мой взгляд, PostgreSQL. В целях переносимости кода я бы рекомендовал всегда явно указывать размер.
Рассмотрим следующий код.
1. DECLARE @ch
AS
VARCHAR
(
2
)
=
'1'
;
2. DECLARE @t
TABLE
(
ch VARCHAR
(
2
))
;
3.
INSERT
INTO
@t
VALUES
(
'123'
)
;
4.
SELECT
@ch=CONCAT
(
@ch,
'2'
,
'3'
)
;
5.
SELECT
@ch
"var"
,
(
SELECT
ch
FROM
@t
)
"col"
;
var
col
12
NULL
В этом коде мы определяем простую переменную типа VARCHAR(2) и таблицу (табличную переменную) с единственным столбцом того же типа данных - VARCHAR(2).
В результате данные в переменной усекаются до требуемого размера - 2 символа, а при вставке в столбец таблицы значения, превышающего допустимый размер, возникает ошибка:
Символьные или двоичные данные могут быть усечены.
Этим объясняется результат - NULL в столбце col, т.е. данные не были вставлены в таблицу.
По поводу символьных данных следует сделать еще одно замечание, которое касается их допустимого размера.

1.
SELECT
LEN
(
REPLICATE
(
'a'
,
100000
))
"varchar"
2. ,LEN
(
REPLICATE
(
CAST
(
'a'
AS
NVARCHAR
)
,
100000
))
"nvarchar"
3. ,LEN
(
REPLICATE
(
CAST
(
'c'
AS
VARCHAR
(
MAX
))
,
100000
))
"varchar_max"
;
varchar
nvarchar
varchar_max
8000
4000 100000
Данный пример показывает, что простые символьные типы усекаются до размера страницы SQL Server - 8000 байтов (тип NVARCHARиспользует 2 байта на символ). Тип VARCHAR(MAX) позволяет хранить данные до 2Гб.
Это особенно следует иметь в виду при конкатенации данных, когда заранее трудно или невозможно узнать размер получаемой в результате строки, что может привести к неверному анализу данных:
1.
SELECT
LEN
(
CONCAT
(
REPLICATE
(
'a'
,
7000
)
, REPLICATE
(
'b'
,
7000
)))
ch;
ch
8000
Здесь мы соединяем две строки по 7000 символов в каждой. В результате получаем строку размером 8000 символов, остальное отсекается без получения сообщения об ошибке.
Float(n)
Как-то в одной социальной сети спросили, как убрать концевые нули в десятичных числах. Это было связано с

подготовкой некоего отчета, где денежные суммы конкатенировались с текстом. В постановке задачи указывалось ограничение на два десятичных знака. Вот пример данных и требуемый результат:
дано
получить
0.00
0
10.00
10
2.50
2.5
100.00 100
11.33
11.33
Предлагались решения, построенные на разборе строки. Я тоже впал в подобную ересь и предложил следующее решение.
1.
SELECT
num,
2.
CASE
WHEN
CAST
(
num
AS
INT
)
= num
3.
THEN
CAST
(
CAST
(
num
AS
INT
)
AS
VARCHAR
)
4.
WHEN
CAST
(
num*
10
AS
INT
)
= num*
10 5.
THEN
LEFT
(
CAST
(
num
AS
VARCHAR
)
, LEN
(
CAST
(
num
AS
VARCHAR
))
-
1
)
6.
WHEN
CAST
(
num*
100
AS
INT
)
=num*
100 7.
THEN
CAST
(
num
AS
VARCHAR
)
8.
END
fnum
9.
FROM
(
10.
SELECT
0.00
AS
num
11.
UNION
ALL
SELECT
10.00 12.
UNION
ALL
SELECT
2.50 13.
UNION
ALL
SELECT
100 14.
UNION
ALL
SELECT
11.33 15.
)
X;

Не знаю, сколько бы это еще продолжалось, если бы один участник дискуссии не заметил, что все проблемы решает приведение к типу данных
float
. Действительно,
1.
SELECT
num,
CAST
(
num
AS
FLOAT
)
fnum
2.
FROM
(
3.
SELECT
0.00
AS
num
4.
UNION
ALL
SELECT
10.00 5.
UNION
ALL
SELECT
2.50 6.
UNION
ALL
SELECT
100 7.
UNION
ALL
SELECT
11.33 8.
)
X;
Однако при этом нужно помнить о приближенном характере данного типа, а именно о величине мантиссы в научном представлении числа.
В соответствии со стандартом, этот тип данных имеет аргумент - FLOAT(n), который может принимать значения от 1 до 53.
SQL
Server значения аргумента в диапазоне 1 - 24 трактует как 24, что соответствует точности 7 цифр, а в диапазоне 25 - 53 как 53, что соответствует точности 15 цифр. По умолчанию принимается значение 53.
Продемонстрируем сказанное следующим примером.
1.
SELECT
num,
2.
CAST
(
num
AS
FLOAT
(
24
))
num_24,
3.
CAST
(
num
AS
FLOAT
(
53
))
num_53 4.
FROM
(
5.
SELECT
1234567.80
AS
num
6.
UNION
ALL
SELECT
12345678.90 7.
UNION
ALL
SELECT
123456789012345.60 8.
UNION
ALL
SELECT
1234567890123456.70 9.
)
x;
num
num_24
num_53
1234567.80
1234568 1234567.8
12345678.90
1.234568E+07 12345678.9

123456789012345.60
1.234568E+14 123456789012346
1234567890123456.70
1.234568E+15 1.23456789012346E+15
MySQL (версия 5.0)
Не поддерживается преобразование к типу FLOAT.
PostgreSQL (версия 8.3.6)
Практически аналогичное поведение, за исключением того, что для параметра в диапазоне 1 – 24 точность составляет 6 цифр. Соответственно последние результаты будут выглядеть так:
num
num_24
num_53
1234567.80
1.23457e+006 1234567.8
12345678.90
1.23457e+007 12345678.9
123456789012345.60
1.23457e+014 123456789012346
1234567890123456.70
1.23457e+015 1.23456789012346e+015
Целочисленное
деление
Иногда недоумение у начинающих работать с
SQL
Server вызывают результаты подобных запросов:
1.
SELECT
1
/
3
AS
a,
5
/
3
AS
b;

Одни (подозреваю, что это пользователи MySQL или Oracle) ожидают результаты типа
a
b
0.3333
1.6667 т.е. вещественное число, другие –
a
b
0
2 т.е. округления к ближайшему целому. В то время как SQL Server дает
a
b
0
1
Чтобы развеять это недоумение, скажу, что операция "/" просто обозначает целочисленное деление (а именно, дает в результате неполное частное
), если операнды являются целыми числами. Т.е. отдельного обозначения для этой операции нет, и используется символ "обычного" деления. Если же вы хотите получить десятичное число, то нужно привести хотя бы один операнд к вещественному типу явно (первый столбец) или неявно (второй столбец):
1.
SELECT
CAST
(
1
AS
DEC
(
12
,
4
))
/
3
AS
a,
5
./
3
AS
b;
a
b
0.333333
1.666666

Операция получения остатка от деления в SQL Server обозначается "%":
1.
SELECT
1
%
3
AS
a,
5
%
3
AS
b;
a
b
1
2
Теперь что касается некоторых других СУБД.
PostgreSQL ведет себя аналогично SQL Server.
В MySQL для получения неполного частного используется специальный оператор DIV:
1.
SELECT
1
DIV
3
AS
a,
5
DIV
3
AS
b;
Остаток от деления можно также получить в стиле а-ля Паскаль:
1.
SELECT
1
MOD
3
AS
a,
5
MOD
3
AS
b;
Хотя будет работать и "общепринятое"
1.
SELECT
1
%
3
AS
a,
5
%
3
AS
b;

В Oracle вообще нет операции для получения неполного частного, поэтому результат деления
1.
SELECT
1
/
3
AS
a,
5
/
3
AS
b
FROM
dual;
a
b
.333333333333333 1.66666666666667 потребуется вручную приводить к целому типу с желаемым результатом, например, так:
1.
SELECT
CEIL
(
1
/
3
)
AS
a, CEIL
(
5
/
3
)
AS
b
FROM
dual;
a
b
1
2 или так
1.
SELECT
FLOOR
(
1
/
3
)
AS
a, FLOOR
(
5
/
3
)
AS
b
FROM
dual;
a
b
0
1
Для получения остатка от деления в Oracle используется функция MOD:
1.
SELECT
MOD
(
1
,
3
)
AS
a, MOD
(
5
,
3
)
AS
b
FROM
dual;
Наконец, если делитель равен нулю:
1.
SELECT
1
/
0
AS
a;

то MySQL возвращает NULL, в то время как другие рассматриваемые здесь
СУБД дают ошибку деления на ноль.
Методы типа
данных XML
Тип данных XML впервые появился в
SQL
Server
2005.
Он может содержать до 2 Гб данных.
В языке
Transact-SQL
имеется пять методов для работы с типом данных XML:

query() – используется для извлечения XML фрагментов из XML документов;

value() – используется для извлечения значений конкретных узлов или атрибутов XML документов;

exist()
– используется для проверки существования узла или атрибута. Возвращает
1, если узел или атрибут найден, и 0, если не найден;

modify() – изменяет XML документ;

nodes() – разделяет XML документ на несколько строк по узлам.
Методы типа данных XML принимают на вход выражение
XPath или запрос
XQuery
Для примеров в данном учебнике будем использовать базу данных с таблицами, содержащими столбцы типа данных XML.
Таблица tArtist содержит информацию о музыкальных группах, исполнителях и их альбомах.

1.
CREATE
TABLE
dbo.tArtist
(
2. artistId INT
NOT
NULL
PRIMARY
KEY
3. , name VARCHAR
(
100
)
NOT
NULL
4. , xmlData XML
NOT
NULL
5.
)
;
Заполним таблицы тестовыми данными.
1.
INSERT
INTO
dbo.tArtist
(
artistId, name, xmlData
)
VALUES
2.
(
1
,
'Radiohead'
,
3.
'
4.

5.

6.
Self-released
7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.
The King of Limbs is the eighth studio album by English rock band Radiohead,
18. produced by Nigel Godrich. It was self- released on 18 February 2011 as a
19. download in MP3 and WAV formats, followed by physical CD and 12" vinyl
20. releases on 28 March, a wider digital release via AWAL, and a special
21.
"newspaper" edition on 9 May 2011. The physical editions were released
22. through the band''s Ticker Tape imprint on
XL in the United Kingdom,

23.
TBD in the United States, and Hostess
Entertainment in Japan.
24.

25.

26.

27.

28.
Parlophone
29.
Capitol
30.

31.

32.

33.

34.

35.

36.

37.

38.

39.

40.

41.

42.

43.

44.
OK Computer is the third studio album by the English alternative rock band
45.
Radiohead, released on 16 June 1997 on
Parlophone in the United Kingdom and
46.
1 July 1997 by Capitol Records in the United
States. It marks a deliberate
47. attempt by the band to move away from the introspective guitar-oriented
48. sound of their previous album The Bends.
Its layered sound and wide range
49. of influences set it apart from many of the
Britpop and alternative rock
50. bands popular at the time and laid the groundwork for Radiohead''s later,
51. more experimental work.
52.

53.

54.
'
)
,
55.
(
2
,
'Guns N'' Roses'
,
56.
'
57.

58.

59.
Geffen Records
60.

61.

62.

63.
64. length="3:04"/>
65.

66.

67.

68.

69.

70.

71.

72.
73. length="5:22"/>
74.

75.

76.

77.

78.

79.

80.
Use Your Illusion I is the third studio album by GnR. It was the first of two
81. albums released in conjunction with the Use
Your Illusion Tour, the other
82. being Use Your Illusion II. The two are thus sometimes considered a double album.

83.
In fact, in the original vinyl releases, both Use Your Illusion albums are
84. double albums. Material for all two/four discs (depending on the medium) was
85. recorded at the same time and there was some discussion of releasing a
86.
''quadruple album''. The album debuted at
No. 2 on the Billboard charts, selling
87.
685,000 copies in its first week, behind
Use Your Illusion II''s first week sales
88. of 770,000. Use Your Illusion I has sold
5,502,000 units in the U.S. as of 2010,
89. according to Nielsen SoundScan. It was nominated for a Grammy Award in 1992.
90.

91.

92.

93.

94.
Geffen Records
95.

96.

97.

98.

99.

100.

101.

102.

103.

104.

105.

106.

107.

108.

109.

110.
>
111.
Use Your Illusion II is the fourth studio album by GnR. It was one of two albums

112. released in conjunction with the Use Your
Illusion Tour, and as a result the two
113. albums are sometimes considered a double album. Bolstered by lead single ''You
114.
Could Be Mine'', Use Your Illusion II was the slightly more popular of the two
115. albums, selling 770,000 copies its first week and debuting at No. 1 on the U.S.
116. charts, ahead of Use Your Illusion I''s first week sales of 685,000.
117.

118.

119.
'
)
;
Метод query()
В общем случае этот метод принимает на вход выражение XPath и возвращает новый XML документ.
Выражение XPath '/albums/album[2]/labels/label[1]' указывает, что мы хотим получить первый лейбл второго альбома для каждого исполнителя.
Метод