Знакомство с командой SELECT

Знание как получить данные используя язык запросов – это первый шаг к пониманию возможностей команды SELECT. Описание используемых взаимоотношений данных предоставляет ощутимую связь между теорией о том как данные хранятся в таблицах и практической визуализацией этих данных. Эти темы очень важны для обсуждения возможностей команды SELECT. Мы рассмотрим темы в следующем порядке

• Знакомство с командой SELECT
• Команда DESCRIBE
• Возможности команды SELECT

Команда SELECT языка SQL  — это гибкий, понятный и расширяемый механизм созданный для получения информации из базы данных. БД были бы гораздо менее ценными если бы мы не могли писать запросы для получения ответов на наши вопросы. Например у вас может быть база данных в которой хранится ваша финансовая информация: платажи в банке, счета и зарплата. Вы можете легко спросить базу данных, чтобы она предоставила список упорядоченный по дате о счетах за электричество за последние пол года или ваши платежи с определённого счёта за тот же срок. Красота SQL команды спрятана в её простоте, формате похожем на обычный язык, с помощью которого можно задавать вопросы базе данных в формате диалога.

 

Команда DESCRIBE

 

Чтобы получить ответ на вопрос, надо задавать правильный вопрос. Понимание предмета обсуждения, в нашем случае реляционных таблиц, это эссенция для формулировки правильных вопросов. Описание структуры таблицы полезно для понимания какие вопросы можно задать таблице. Oracle хранит информацию о всет таблицах в специальном наборе таблиц, называемых словарём данных, для управления ими. Словарь данных это почти тоже самое что обычный словарь. В нём хранятся определения объектов базы данных в централизованном, упорядоченном и отсортированном формате.

Мы должны чётко разделять определение таблицы и её содержимое. Определение таблицы включает в себя такую информацию как имя, владелец, сведения о столбцах, физическом расположении на диске и т.п. Эта информация является метаданными. Содержимое таблиц хранится в виде строк и называется непосредственно данными.

Метаданные структуры таблицы можно узнать запросив список столбцов этой таблицы. Для этого можно использовать команду DESCRIBE. Синтаксис команды

 

DESCRIBE <schema>.tablename

 

Эта команду можно сократить до DESC. Все таблицы принадлежат владельцу – схеме. Если вы хотите узнать описание таблицы из схемы текущего аккаунта, то можно не указывать явно имя схемы. На рисунке 9-1 отображено выполнение команд DESCRIBE из SQL *Plus подключенного как пользователь HR.

 

В первой команде запрашивается описание таблицы EMPLOYEES. Во втором запросе используется сокращённый псевдоним команды и явно указывается схема, хотя можно было не указывать имя схемы явно, так как таблица DEPARTMENTS принадлежит схеме HR. Также пользователь HR (и все другие пользователи) имеет доступ к специальной таблице с именем DUAL, которая принадлежит схеме SYS. Описание этой таблицы можно получить выполнив третий запрос.

Описание таблицы даёт интерестный и полезный результат. Вы узнаете какие столбцы таблицы можно выбрать, так как видите их имена. Вы также понимаете природу данных в столбцах, так как отображаются типы данных.

Обязательные столбцы, которые обязаны хранить данные для каждой строки, можно узнать взглянув на столбец Null? в результате команды DESCRIBE. Т.е. вам гарантируется что для каждой строки в этом столбце будет какое-либо значение. Важно отметить что у Oracle значение NULL – это особое значение, которое указывает на отсутствие данных. Пробелы это не NULL, так как значение пробела присутствует в строке и может иметь некоторую длину, несмотря на то что эти данные не видимы. Пустой строке нет в Oracle.

 

Возможности команды SELECT

 

Таблицы реляционных баз данных построены на разделе математики известной как реляционная теория (relational theory). В этом разделе, отношения или таблицы обрабатываются специальным формальным языком называемым реляционная алгебра. В реляционной алгебре используются специальные термины, например: отношения содержат кортежи, у которых есть аттрибуты. А на языке Oracle это звучит как строки состоящие из столбцов хранятся в таблицах. SQL это коммерческая реализация принципов реляционной аглебры. Три концепции из реляционной теории заключают в себе возможности команды SELECT: проекция (projection), выборка (selection) и объединение (joining).

Проекция отображена в возможности ограничения выбираемых столбцов из таблицы. Когда запрашивается информация из таблицы, вы можете выбрать для просмотра все столбцы. Вы можете выбрать все данные из таблицы HR.DEPARTMENTS с помощью простой команды SELECT – SELECT * FROM HR.DERAPTMENTS. Эта команда вернёт вам DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID и LOCATION_ID для всех записей хранящихся в таблице. Но что если вы хотите список только из имени департамента и менеджера? Тогда вам нужно запросить только эти два столбца из таблицы. Ограничение столбцов и есть проекция.

Выборка связана с ограничением строк получаемых из таблицы. Обычно не требуется получить все строки из таблицы. В таблицах может храниться много строк и вместо запроса на все строки, выборка позволяет ограничить возвращаемые строки. Например вам нужно найти всех сотрудников которые работают в департаменте с номером 30. С помощью операции выборки вы можете ограничить результат только теми строками где значение DEPARTAMENT_ID равно 30.

Объединение, это концпеция, связанная с взаимодействием таблиц друг с другом в запросе. Третья нормальная форма представляет собой идею об разделении разных данных в отдельные таблицы для избегания дублирования информации и аномалий при управлении, а также для связывания данных с помощью первичных и внешних ключей (отношения один-к-). Эти отношенияпредоставляют механимз для объединения таблиц между собой. Предположим что нам нужно получить адреса почты для сотрудников кто работает в департаменте продаж. Столбец EMAIL хранится в таблице EMPLOYEES, а DEPARTAMENT_NAME в таблице DEPARTAMENTS. Проекция и выборка используются для получения значение DEPARTAMENT_ID соответствующего значению Sales в названии. Соответсвующие строки из таблицы EMPLOYEE можно присоединить к таблице DEPARTAMENTS основываясь на этом общем значении столбца DEPARTMENT_ID. Столбец EMAIL может быть спроецирован из результата объединения. Команда SELECT математически управляется этими тремя догмами. Неограниченное комбинирование проекций, выборки и присоединения предоставляют возможность выбрать именно требуемые данные из реляционной модели.

 

Нормализация данных

 

Процесс моделирования данных в реляционные таблицы называется нормализацией. Обычно рассматриваются три уровня нормализации: первая, вторая и третья нормальные формы. Существуют также более высокие уровни нормализации: четвертая и пятая нормальные формы, но они не используются часто. Возможно использоваться SQL для выборки данных из ненормализованных данных, но обычно это неэффективно и это не то зачем разрабатывался язык SQL. В большинстве случаев, данные хранящиеся в базе данных и используемые с помощью языка SQL должны быть нормализованы к третье нормальной форме.

 

Tip

Всегда существует несколько нормализованных моделей для системы. Очень важно использовать наиболее подходящую – если системный анализ будет некорректным, то возможными последствиями будут проблемы с производительностью и хранением данных, а также потребуются гораздо большие усилия для разработки.

 

Как пример нормализации рассмотрим де-нормализованную таблицу с названием BOOKS, где хранятся данные о книгах, авторах и издательствах, использующую ISBN как первичный ключ. Первичный ключ это аттрибут (или аттрибуты) которые уникально определяют кортеж. В таблице две записи

Хранение данных в такой таблице может привести к нескольким проблемам. Во-первых,тут существует аномалия вставки: невозможно ввести данных об авторе который ещё не публиковался, потому что нет ISBN для идентификации этой информации. Во-вторых, книга не может быть удалена без потери информации об издательстве: аномалия удаления. В-третьих, если адрес издательства изменяется, необходимо обновить все строки для всех книг этого издательства: аномалия обновления. Тот факт что у книги может быть несколько адресов значит что поле «Author” должно иметь возможность хранить несколько значения, и для поиска конкретного автора необходимо будет просматривать все значения. Также с этой проблемой связана проблема потенциальной необходимости изменять структуру если у книги будет больше авторов чем может поместиться в значении атрибута. И использования места очень неэффективно так как адрес издательства дублируется для строк и вероятность ошибки в заполнении этого поля (так как значение необходимо всегда вводить) высока. Нормализация должна решить все эти проблемы.

Первая нормальная форма требует удаления повторяющихся групп, в данном примере, наличие нескольких авторов: поместим их в отдельную таблицу с именем AUTHORS. После этого изменения структура данных будет выглядеть так

Две строки в таблице BOOKS

И три строки в таблице AUTHORS

Одна строка в таблице BOOKS теперь связана с двумя строками в таблице AUTHORS. Такое изменение решает аномалию вставки (теперь можно добавлять авторов у которых нет публикаций), проблему поиска книг автора (возможен поиск по одному значению поля NAME в таблице) и проблему максимально возможного количества авторов для одной книги (добавляйте сколько угодно авторов). Это первая нормальная форма – нет повторяющихся групп.

Вторая нормальная форма требует убрать столбцы из таблицы которые не зависят от первичного ключа. В нашем примере это адрес издательства: адрес зависит от издателя, а не от ISBN книги. Таблица BOOKS и новая таблица PUBLISHERS после этого изменения будут выглядеть так

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

Третья нормальная форма требует убрать все столбцы которые взаимозависимы. В таблице PUBLISHERS таким аттрибутом будет адрес: улица должна быть в конкретном городе,а город существует в определённом районе. Т.е. адресс должен определяться одним столбцом, а не тремя. Можно заменить три поля одним – кодом адреса, указывающим на отдельную таблицу адресов

Одним из свойств нормализованных данных которые можно выделить является использование первичных и внешних ключей. Первичный ключ это уникальный идентификатор строки в таблице, им может быть как столбец так и несколько столбцов (составной ключ/composite key). У каждой таблицы должен быть определён первичный ключ. Это требование реляционной парадигмы. Oracle расширяет стандарт: возможно определить таблицу без первичного ключа – хотя это обычно плохая идея и некоторые другие РСУБД не позволяют это сделать.

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

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

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

• Первичный ключ отображается как #
• Вторичный ключ отображается как \
• Обязательный столбец обозначается *
• Необязательный столбец обозначается как o

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

Это очень простой пример нормализации и реально модель не до конца нормализована. Если один автор напишет несколько книг, тогда у нас необходимо будет хранить несколько значений ISBN в таблице авторов. Это будет повторяющаяся группа, которую необходимо вынести в отдельную сущность, чтобы не нарушать первую нормальную форму. При нормализации модели необходимо убедиться что рассмотрены все возможные варианты событий. В реальной системе может быть сотник столбцов и десятки внешних ключей. Построение диграммы сущность-связь для системы с сотнями или тысячами отношений достаточно сложная задача.