Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 20: все разновидности запросов на объединение в трёх СУБД
MS SQL Решение 2.2.10.n
1SELECT [r_id],
2[r_name],
3[r_space],
4[c_id],
5[c_room],
6[c_name]
7 |
|
FROM |
[rooms] |
|
8 |
|
|
OUTER APPLY (SELECT |
TOP ([r_space]) |
9 |
|
|
|
[c_id], |
10 |
|
|
|
[c_room], |
11 |
|
|
|
[c_name] |
12 |
|
|
FROM |
[computers] |
13 |
|
|
WHERE [c_room] = [r_id] |
|
14 |
|
|
ORDER |
BY [c_name] ASC) AS [cross_apply_data] |
|
|
|
|
|
15ORDER BY [r_id],
16[c_id]
Oracle Решение 2.2.10.n
1SELECT "r_id",
2"r_name",
3"r_space",
4"c_id",
5"c_room",
6"c_name"
7 |
|
FROM "rooms" |
8 |
|
LEFT JOIN (SELECT "c_id", |
9 |
|
"c_room", |
10 |
|
"c_name", |
11 |
|
( CASE |
12 |
|
WHEN "c_room" IS NULL THEN 1 |
13 |
|
ELSE ROW_NUMBER() |
14 |
|
OVER ( |
15 |
|
PARTITION BY "c_room" |
16 |
|
ORDER BY "c_name" ASC) |
17 |
|
END ) AS "position" |
18 |
|
FROM "computers") "cross_apply_data" |
19ON "r_id" = "c_room"
20WHERE "position" <= "r_space" OR "position" IS NULL
21ORDER BY "r_id",
22"c_id"
Если бы мы не добавили в строки 20 запросов для MySQL и Oracle вторую часть условия (OR position IS NULL), пустые комнаты не попали бы в итоговую выборку, т.к. им нет соответствия компьютеров, т.е. «номер» любого компьютера для них равен NULL, а сравнение NULL со значением поля r_space даёт FALSE.
Поясним ещё раз на графическом примере логику работы и разницу CROSS APPLY и OUTER APPLY.
|
В задаче 2.2.10.n{175} (CROSS APPLY): |
|
|
|
|
|
|
|
c_id |
c_room |
c_name |
|
|
≤5 |
1 |
1 |
Компьютер A в комнате 1 |
|
|
2 |
1 |
Компьютер B в комнате 1 |
|
|
|
|
|||
r_id |
r_name |
r_space |
|
|
|
1 |
Комната с двумя компьютерами |
5 |
c_id |
c_room |
c_name |
|
|
|
|||
2 |
Комната с тремя компьютерами |
5 |
3 |
2 |
Компьютер A в комнате 2 |
|
|
≤5 |
4 |
2 |
Компьютер B в комнате 2 |
|
|
|
5 |
2 |
Компьютер C в комнате 2 |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 180/545
Пример 20: все разновидности запросов на объединение в трёх СУБД |
|
|
|
|||||
|
В задаче 2.2.10.m{179} (OUTER APPLY): |
|
|
|
|
|
||
|
|
|
|
c_id |
c_room |
|
c_name |
|
|
|
|
≤5 |
1 |
1 |
Компьютер A в комнате 1 |
||
|
|
|
2 |
1 |
Компьютер B в комнате 1 |
|||
|
|
|
|
|||||
r_id |
r_name |
r_space |
|
|
|
|
|
|
1 |
Комната с двумя компьютерами |
5 |
|
c_id |
c_room |
|
c_name |
|
|
|
|
|
|
|
|||
2 |
Комната с тремя компьютерами |
5 |
|
3 |
2 |
Компьютер A в комнате 2 |
||
|
|
|
|
|
|
|||
3 |
Пустая комната 1 |
2 |
≤5 |
4 |
2 |
Компьютер B в комнате 2 |
||
4 |
Пустая комната 2 |
2 |
|
5 |
2 |
Компьютер C в комнате 2 |
||
5 |
Пустая комната 3 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
c_id |
c_room |
c_name |
|
|
|
≤2 |
|
|
NULL |
NULL |
NULL |
|
|
|
|
|
|
c_id |
c_room |
c_name |
|
|
|
≤2 |
|
|
NULL |
NULL |
NULL |
|
|
|
|
|
|
c_id |
c_room |
c_name |
|
|
|
≤2 |
|
|
NULL |
NULL |
NULL |
Задание 2.2.10.TSK.A: показать информацию о том, кто из читателей и когда брал в библиотеке книги.
Задание 2.2.10.TSK.B: показать информацию обо всех читателях и датах выдачи им в библиотеке книг.
Задание 2.2.10.TSK.C: показать информацию о читателях, никогда не бравших в библиотеке книги.
Задание 2.2.10.TSK.D: показать книги, которые ни разу не были взяты никем из читателей.
Задание 2.2.10.TSK.E: показать информацию о том, какие книги в принципе может взять в библиотеке каждый из читателей.
Задание 2.2.10.TSK.F: показать информацию о том, какие книги (при условии, что он их ещё не брал) каждый из читателей может взять в библиотеке.
Задание 2.2.10.TSK.G: показать информацию о том, какие изданные до 2010-го года книги в принципе может взять в библиотеке каждый из читателей.
Задание 2.2.10.TSK.H: показать информацию о том, какие изданные до 2010-го года книги (при условии, что он их ещё не брал) может взять в библиотеке каждый из читателей.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 181/545
Пример 21: вставка данных
2.3. Модификация данных
2.3.1. Пример 21: вставка данных
Очень полезным источником хороших примеров выполнения вставки данных для любой СУБД является изучение дампов баз данных (там же вы увидите множество готовых примеров SQL-конструкций по созданию таблиц, связей, триггеров и т.д.)
Все дальнейшие рассуждения относительно способа передачи строковых данных построены на предположении, что базы данных созданы в следующих кодировках:
•MySQL: utf8 / utf8_general_ci;
•MS SQL Server: UNICODE / Cyrillic_General_CI_AS;
•Oracle: AL32UTF8 / AL16UTF16.
Тема кодировок и работы с ними огромна, очень специфична для каждой СУБД и почти не будет рассмотрена в этой книге (кроме задач 7.3.2.a{525} и 7.3.2.b{525}) — обратитесь к документации по соответствующей СУБД.
Задача 2.3.1.a{182}: добавить в базу данных информацию о том, что читатель с идентификатором 4 взял 15-го января 2016-го года в библиотеке книгу с идентификатором 3 и обещал вернуть её 30-го января 2016-го года.
Задача 2.3.1.b{185}: добавить в базу данных информацию о том, что читатель с идентификатором 2 взял 25-го января 2016-го года в библиотеке книги с идентификаторами 1, 3, 5 и обещал вернуть их 30-го апреля 2016го года.
Ожидаемый результат 2.3.1.a: в таблице subscriptions должна появиться такая новая запись.
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
|
101 |
4 |
3 |
2016-01-15 |
2016-01-30 |
N |
|
|
Ожидаемый результат 2.3.1.b в таблице subscriptions должны появиться |
|||||
|
три таких новых записи. |
|
|
|
|
|
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
102 |
2 |
1 |
2016-01-25 |
2016-04-30 |
N |
103 |
2 |
3 |
2016-01-25 |
2016-04-30 |
N |
104 |
2 |
5 |
2016-01-25 |
2016-04-30 |
N |
Решение 2.3.1.a{182}.
Во всех трёх СУБД в таблице subscriptions у нас созданы автоинкрементируемые первичные ключи, потому их значение указывать не надо. Остаётся только передать известные нам данные.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 182/545
Пример 21: вставка данных
MySQL |
Решение 2.3.1.a |
||
1 |
INSERT INTO `subscriptions` |
||
2 |
|
|
(`sb_id`, |
3 |
|
|
`sb_subscriber`, |
4 |
|
|
`sb_book`, |
5 |
|
|
`sb_start`, |
6 |
|
|
`sb_finish`, |
7 |
|
|
`sb_is_active`) |
8 |
VALUES |
(NULL, |
|
9 |
|
|
4, |
10 |
|
|
3, |
11 |
|
|
'2016-01-15', |
12 |
|
|
'2016-01-30', |
13 |
|
|
'N') |
Если вставка выполняется во все поля таблицы, часть запроса в строках 2-7 можно не писать, в противном случае эта часть является обязательной.
Если мы не хотим явно указывать значение автоинкрементируемого первичного ключа, в MySQL можно вместо его значения передать NULL или исключить это поле из списка передаваемых полей и не передавать никаких данных (т.е. убрать имя поля в строке 2 и значение NULL в строке 8).
MS SQL |
Решение 2.3.1.a |
||
1 |
INSERT INTO [subscriptions] |
||
2 |
|
|
([sb_subscriber], |
3 |
|
|
[sb_book], |
4 |
|
|
[sb_start], |
5 |
|
|
[sb_finish], |
6 |
|
|
[sb_is_active]) |
7 |
VALUES |
(4, |
|
8 |
|
|
3, |
9 |
|
|
CAST(N'2016-01-15' AS DATE), |
10 |
|
|
CAST(N'2016-01-30' AS DATE), |
11 |
|
|
N'N') |
В MS SQL Server автоинкрементация первичного ключа осуществляется за счёт того, что соответствующее поле помечается как IDENTITY. Вставка значений NULL и DEFAULT в такое поле запрещена, потому мы обязаны исключить его из списка полей и не передавать в него никаких значений.
Даже если бы мы точно знали значение первичного ключа этой вставляемой записи, всё равно для его вставки нам пришлось бы выполнить две дополнительных операции:
•перед вставкой данных выполнить команду SET IDENTITY_INSERT [subscriptions] ON;
•после вставки данных выполнить команду SET IDENTITY_INSERT [subscriptions] OFF.
Эти команды соответственно разрешают и снова запрещают вставку в IDEN- TITY-поле явно переданных значений.
В строках 9-10 запроса производится явное преобразование строки, содержащей дату, к типу данных DATE. MS SQL Server позволяет этого не делать (конвертация происходит автоматически), но из соображений надёжности рекомендуется использовать явное преобразование.
Теми же соображениями надёжности вызвана необходимость ставить букву N перед строковыми константами (строки 9-11 запроса), чтобы явно указать СУБД на то, что строки представлены в т.н. «национальной кодировке» (и юникоде как форме представления символов). Для символов английского алфавита и символов, из которых состоит дата, этим правилом можно пренебречь, но как только в строке
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 183/545
Пример 21: вставка данных
появится хотя бы один символ, по-разному представленный в разных кодировках, вы рискуете повредить данные.
Интересен тот факт, что даже в нашем конкретном случае (формат поля sb_is_active — CHAR(1), а не NCHAR(1)) в создаваемом средствами MS SQL Server Management Studio дампе базы данных буква N присутствует перед значениями поля sb_is_active. Краткий вывод: если есть сомнения, использовать букву N перед строковыми константами, или нет, — лучше использовать.
Oracle |
|
Решение 2.3.1.a |
|
1 |
INSERT INTO "subscriptions" |
||
2 |
|
|
("sb_id", |
3 |
|
|
"sb_subscriber", |
4 |
|
|
"sb_book", |
5 |
|
|
"sb_start", |
6 |
|
|
"sb_finish", |
7 |
|
|
"sb_is_active") |
8 |
VALUES |
(NULL, |
|
9 |
|
|
4, |
10 |
|
|
3, |
11 |
|
|
TO_DATE('2016-01-15', 'YYYY-MM-DD'), |
12 |
|
|
TO_DATE('2016-01-30', 'YYYY-MM-DD'), |
13 |
|
|
'N') |
ВOracle обязательно нужно преобразовывать строковое представление дат
ксоответствующему типу.
Вотличие от MS SQL Server здесь нет необходимости указывать букву N пе-
ред строковыми константами со значениями дат и поля sb_is_active (можно и указать — это не приведёт к ошибке). Но для большинства остальных данных (например, текста на русском языке) букву N лучше указывать.
Особый интерес представляет передача значения автоинкрементируемого первичного ключа. В Oracle такой ключ реализуется нетривиальным способом — созданием SEQUENCE как «источника чисел» и триггера, который получает очередное число из SEQUENCE и использует его в качестве значения первичного ключа вставляемой записи.
Из этого следует, что в качестве значения автоинкрементируемого ключа вы можете передавать… что угодно: NULL, DEFAULT, число — триггер всё равно заменит переданное вами значение на очередное полученное из SEQUENCE число.
Если же вам необходимо явно указать значение поля sb_id, нужно выполнить две дополнительных операции:
•перед вставкой данных выполнить команду ALTER TRIGGER
"TRG_subscriptions_sb_id" DISABLE;
•после вставки данных выполнить команду ALTER TRIGGER
"TRG_subscriptions_sb_id" ENABLE.
Эти команды соответственно отключают и снова включают триггер, устанавливающий значение автоинкрементируемого первичного ключа.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 184/545