SQL: быстрое погружение

Рис. 10

Поле ProductId — первичный ключ для этой таблицы (обозначено значком ключа). Каждая запись в таблице будет содержать уникальный идентификационный номер товара. Фактически основная цель этой таблицы — каталогизировать атрибуты различных товаров базы данных. Теперь давайте проанализируем связи между таблицами products и suppliers.

Рис. 11

Рис. 12

Между таблицами suppliers и products существует связь «один-ко-многим», основанная на данных поля SupplierId. В таблице suppliers каждая запись будет иметь уникальный идентификационный номер для каждого поставщика. В таблице products может быть несколько записей с одним и тем же идентификационным номером поставщика.

Значок ключа, расположенный рядом с полем SupplierId в таблице suppliers, означает, что SupplierId — это первичный ключ для этой таблицы. В базе данных, безусловно, может содержаться большое количество разных товаров (каждый будет иметь уникальный идентификационный номер), поступающих от одного поставщика и каталогизированных в таблице products. Взгляните на таблицу suppliers, где должен быть только один уникальный идентификационный номер поставщика для каждой записи.

Рис. 13

Примечание

Данные в поле supplierId могут повторяться в нескольких записях в таблице products, но не в таблице suppliers.

Давайте проанализируем взаимосвязь между таблицами products, order_details и orders (рис. 14).

Рис. 14

Таблица order_details имеет два первичных ключа (обозначено значками ключей). Можно трактовать это как составной ключ, то есть для определения первичного ключа используются два или более поля. Хотя технически в примере задействовано два ключа, стоит рассматривать их как единый элемент — собственно первичный ключ.

Комбинация данных в полях, используемая для формирования составного ключа, работает как уникальный идентификатор для любой записи в таблице. Другими словами, если запись OrderId в таблице order_details равна 101, а ProductId той же записи — P006, то мы можем предположить, что никакая другая запись в таблице не будет иметь такую же комбинацию данных этих двух полей. Могут существовать одна или несколько других записей с OrderId, равным 101, а также одна или несколько других записей с ProductId, равным P006, но только одна запись может иметь и 101 в качестве своего OrderId, и P006 в качестве своего ProductId. Эта комбинация данных работает как составной ключ, который, как и любой первичный ключ, обеспечивает уникальный идентификатор для каждой записи в таблице.

Рис. 15

В связи «один-ко-многим» стандартный первичный ключ находится на стороне «один». Например, в таблице orders мы видим, что первичный ключ, поле OrderId, предоставляет уникальный идентификатор для каждой записи в таблице. Сторона связи «ко-многим» находится в таблице order_details. Как вы думаете, почему?

Давайте рассуждать логически. Можно сделать вывод, что роль таблицы order_details — предоставить информацию о различных заказанных товарах. Также допустимо, что любой товар может быть заказан несколько раз несколькими заказчиками при самых разных обстоятельствах, с разными ценами и т. д. Следовательно, поле ProductId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Кроме того, любой заказ может включать в себя несколько товаров, и если мы проанализируем другие поля, используемые в таблице order_details — UnitPrice, Quantity и Discount, тогда мы четко поймем, что эти поля обозначают свойства определенного товара, а не заказ в целом. Также поле OrderId не может использоваться само по себе в качестве первичного ключа для таблицы order_details. Решение состоит в том, чтобы объединить ProductId и OrderId в составной ключ, тем самым гарантируя, что данные, содержащиеся в столбцах UnitPrice, Quantity и Discount, будут соответствовать уникальному и определенному заказу и уникальному и определенному товару в этом заказе.

Типы данных

Ранее в этой главе мы описывали концепцию метаданных, которые представляют собой детальную информацию обо всех объектах системы. При разработке базы данных с использованием SQL для каждого используемого столбца должен быть назначен определенный тип данных. Типы данных могут незначительно отличаться в зависимости от используемой версии SQL. Однако, как правило, у вас будут числовые, символьные или текстовые типы данных, дата и время, а также логические значения. Рассмотрим каждый из них.

Числовые типы данных

В состав числовых данных входят целочисленные данные, которые служат для отображения целых чисел. Обычно, когда используется целочисленный тип данных, он имеет ограничения на длину. Напомним, что изображенная на рис. 7 таблица содержала сведения о пациентах. Для столбца Weight (Вес) разумно использовать целочисленный тип данных с ограничением до трех цифр. Почему?

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

2. Вряд ли нам понадобится более трех цифр для указания значения веса в фунтах.

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

Рис. 16*

Примечание

Для типов данных, допускающих более длинные диапазоны цифр, символов и т. д., требуется больший объем памяти в байтах. SQL также допускает денежные (финансовые) типы данных.

Символьные, или текстовые, типы данных

Символьные, или текстовые, типы данных (еще их называют «строковые») могут хранить строки символов как фиксированной, так и переменной длины. Например, если один из столбцов вашей базы данных содержит стандартные шестизначные почтовые индексы Канады (которые включают как цифры, так и буквы), то вам необходимо использовать символьный, или текстовый, тип данных со строкой фиксированной длины из шести символов. Если вы создавали столбец для хранения имени или фамилии клиента, вам необходимо использовать строку переменной длины с ограничением по максимальной и минимальной длине.

Рис. 17

Примечание

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

Дата и время

Дата и время — эти данные важны во многих случаях. SQL предлагает пользователям различные форматы даты и времени: YYYY-MM-DD (ГГГГ-ММ-ДД), YYYY-MM-DD HH: MI:SS (ГГГГ-ММ-ДД ЧЧ: МИ:СС), YY-MM-DD (ГГ-ММ-ДД). Вы также можете отформатировать столбец так, чтобы он содержал только год, в четырех- или двухзначном формате. Например, 2019 или просто 19. На рис. 18 показан пример использования данных даты и времени.

Примечание

В SQL форматы даты/времени содержат встроенные числовые значения, позволяющие базе данных обрабатывать запросы, где в качестве условия задан интервал времени. Например, если вы хотите узнать, сколько клиентов приобрели определенный товар в период с 1 октября 2020 г. по 31 декабря 2020 г., то с помощью SQL вы сможете сгенерировать и отсортировать эти данные.

Рис. 18

Логический тип данных

Логические значения — это данные, принимающие значения True (Правда) или False (Ложь). Например, если вы отвечаете за секретную операцию для правительства или частного лица, вы можете использовать базу данных для отслеживания уровня допуска сотрудников. Если вам необходимо найти список сотрудников с допусками A, B и D, но не обязательно C, то использование логического анализа данных может значительно упростить процесс. На рис. 19 показан пример использования логических данных.

Рис. 19

Примечание

В разных версиях SQL — разные списки распознаваемых типов данных. Некоторые версии SQL, такие как SQL Server и MySQL (описаны далее в этой главе), не дают пользователю возможности присвоить данным тип Boolean. Вместо этого они предоставляют тип данных Bit, который может быть легко преобразован в логический формат.

Системы управления реляционными базами данных

SQL применяется в целом ряде программных пакетов, известных как реляционные системы управления базами данных(РСУБД). Эти системы упрощают применение SQL, когда пользователь дает команды и задает вопросы базе данных. Наиболее распространенные СУБД — это Oracle Database, Microsoft SQL Server, MySQL, IBM Db2 и SQLite.

Рис. 20

Примечание

Программное обеспечение РСУБД часто называют базой данных. Это не совсем верно. Правильнее сказать, что РСУБД предоставляет интерфейс (обычно известный как SQL-браузер) для взаимодействия пользователя с данными, хранящимися в базе данных.

Некоторые РСУБД изначально представлены в графическом виде, другие — в текстовом. РСУБД также различают по подходу к SQL. Ранее в этой главе мы уже упоминали об одной такой аномалии при обработке логических данных. РСУБД действительно различаются по способу представления информации базы данных.

Тот факт, что мы сообщаем РСУБД, какую информацию нам предоставлять, определяет SQL как декларативный язык программирования. Это отличает его от таких языков программирования, как C++, Java и т. д. Они являются более процедурными, так как с их помощью программа создается от начала до конца (распределяется память, в том числе для существующих справочных файлов, и т. д.). В случае SQL все распределение памяти и другие действия выполняются РСУБД.

Оператор SELECT

Как вы уже знаете, SQL — это язык структурированных запросов, и в течение нескольких десятилетий он сформировал стандарт взаимодействия с реляционными базами данных. SELECT — самая распространенная команда SQL. Мы будем с ней работать в главе 4 и далее — до конца книги. SQL-запрос обычно состоит из оператора SELECT в сочетании с другими операторами SQL и ссылок на данные, участвующие в запросе. Как и в случае с другими языками программирования, правильная последовательность и выбор операторов SQL важны для создания запроса, который будет правильно интерпретирован браузером SQL. Эта строго заданная структура называется синтаксисом запроса.

В следующем примере мы увидим, что синтаксис запроса несколько отличается в различных реализациях РСУБД. Это два очень простых запроса, которые по сути делают одно и то же (возвращают первые десять записей из таблицы products), но сформулированы они немного по-разному.

В SQL Server необходимо ввести следующее:

SELECT TOP 10 *

FROM

products;

В MySQL это будет выглядеть следующим образом:

SELECT *

FROM

products

LIMIT 10;

Если бы мы сформулировали запрос в MySQL так, как в примере для SQL Server, браузер SQL выдал бы синтаксическую ошибку и запрос не был бы выполнен. Единственное различие между этими двумя реализациями SQL заключается в том, каким образом мы указываем браузеру SQL ограничить наши результаты десятью первыми записями. В остальном запросы одинаковы. Различия между РСУБД обычно весьма незначительны. Если выражать в процентах, то эти различия составляют менее 10 %. Простые декларативные принципы SQL работают в большинстве РСУБД. Следовательно, если вы будете изучать базовую логику SQL в рамках какой-либо определенной РСУБД, то вы сможете быстро адаптировать свои знания основ SQL к любой другой РСУБД.

Запросы, операторы, условия и ключевые слова

Если ранее вы уже работали с SQL, вы, возможно, встречались с терминами «запрос», «оператор», «условие» и «ключевое слово». SELECT — это специальное ключевое слово в SQL, также его называют оператором SELECT, условием SELECT или запросом SELECT. Так в чем же разница? Давайте двигаться от наиболее широкого толкования к более узкому.

Запрос — это набор команд, который возвращает информацию из базы данных в виде записей. Запрос может состоять из нескольких операторов SQL (будут рассмотрены в главе 8 в форме подзапросов). Оператор SQL — это выполняемый РСУБД любой допустимый фрагмент кода. Рассмотренные примеры кода являются как действительными операторами SQL (поскольку СУБД позволяет их выполнять), так и запросами (поскольку они возвращают набор записей). Условие — это часть запроса, содержащая по крайней мере одно ключевое слово и соответствующую информацию, которая будет использоваться вместе с этим ключевым словом (в данном случае ссылки на поля и таблицы).

Рис. 21

Слова, написанные ЗАГЛАВНЫМИ буквами, — ключевые слова SQL.

Как показано на рис. 21, оператор SQL может состоять из множества условий, каждое из которых содержит по крайней мере одно ключевое слово, а также ссылки на поля и таблицы.

Примечание

На рис. 21 показан пример использования оператора SQL и полного запроса. Запрос может содержать несколько условий, каждое из которых начинается с ключевого слова.

Введение в SQLite

Теперь, когда вы уже знакомы с основами архитектуры базы данных и способами взаимодействия с ней, давайте перейдем к практике решения реальных задач. Существуют различные версии РСУБД. Нам кажется неэффективным рассказывать о функциях применительно к каждой РСУБД в отдельности. В качестве стандартной РСУБД для этой книги мы выбрали SQLite, которую считаем имеющей практическую ценность и доступной для новичков. SQLite — это программный продукт с открытым исходным кодом, поэтому он доступен для использования. Примерно 99 % изученного материала, касающегося SQLite, применимо к большинству других РСУБД. Кроме того, SQLite считается одной из наиболее широко используемых систем РСУБД в мире. Она применяется в компьютерах, мобильных устройствах и даже автомобилях [9]. Более подробную информацию можно найти на сайте: https://www.sqlite.org/index.html.

Рис. 22

Примечание

Окончание Lite («легкий», «облегченный») не относится к возможностям данного программного обеспечения, а скорее к его настройке, административным накладным расходам и использованию ресурсов.

Резюме

• Таблица — это двумерная сетка строк и столбцов, содержащая данные.

• Данные могут быть представлены в виде множества различных типов, например строк, чисел или специальных символов.

• Метаданные описывают характер и формат данных, включая минимальную/максимальную длину строки или допустимые числа, буквы или специальные символы.

• Реляционные базы данных могут содержать множество таблиц. Каждая таблица в реляционной базе данных содержит первичный ключ, служащий уникальным идентификатором для таблицы.

• Внешний ключ — это столбец или комбинация столбцов в таблице, значения которых соответствуют первичному ключу в другой таблице.

• Взаимосвязь между таблицами и их первичными и внешними ключами называется схемой базы данных и может быть изображена с помощью схемы «сущность — связь» (ERD).

• Существует множество различных реляционных систем управления базами данных (РСУБД), например Oracle Database, Microsoft SQL Server, MvSQL, IBM Db2 и SQLite. Хотя они во многом отличаются, все они в качестве основы используют язык структурированных запросов.

• SELECT — это наиболее распространенная команда SQL.

• Операторы SQL могут содержать несколько условий с использованием разных операторов SQL.

• В этой книге мы взяли за основу SQLite. Однако все полученные навыки можно легко использовать на других платформах РСУБД.

* В англоязычных странах целая часть отделяется от дробной точкой, а в России — запятой. — Примеч. ред.

Глава 2. Инструменты и стратегии SQL