Основы SQL - первое знакомство

Вступление

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

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

Как вам, например, такой подход – чтобы получить первые 100 записей, сначала несколько сотен тысяч записей считываются в DataSet (или объектную модель), а потом на экран в цикле выводятся первые 100? И речь здесь, к сожалению, не о выдуманной ситуации, а о реальном коде. Причем авторы кода не любимые многими абстрактные “индусы”, а, в том числе, наши соотечественники. Скажу по секрету, приходилось сталкиваться с исходным кодом многих проектов разных контор – проблемы с ним мало зависят от национальности разработчиков, скорее от уровня и ответственности конкретных исполнителей и отношения к качеству внутри компании.

Предвидя один из самых распространенных комментариев на эту тему, скажу, что ORM тоже не панацея – в любом случае, даже совсем не используя SQL-запросы имеет смысл представлять, как все работает на более низком уровне и как, исходя из этого, проектировать БД и профилировать/оптимизировать приложения. И да, я также считаю что, скажем, .NET или java-разработчику не помешает иметь хотя бы начальное представление о неуправляемых средах выполнения (например, о программировании на чистом C или C++), а в идеале – и об ассемблере :)

Немного про историю и стандарты

SQL – это стандарт де-факто для работы с реляционными БД. В 1969-1970 годах Эдгар Кодд сформулировал принципы реляционной модели, которые легли в основу (хотя они не всегда строго выполняются) всех существующих на данный момент реляционных СУБД. Разумеется, есть не только реляционные СУБД, но, раз уж речь идет об SQL, оставим их за кадром.
Затем, в середине семидесятых, в рамках исследовательского проекта IBM, была создана реляционная СУБД System R, реализующая язык SEQUEL (Structured English QUEry Language), который затем (из-за того, что SEQUEL был зарегистрированной торговой маркой) был переименован в SQL. С тех пор осталась мода на произношение “сиквел”, хотя после переименования правильнее говорить “эс-кью-эл”. System R позднее трансформировалась в IBM DB2, которая здравствует и поныне. Однако основная ее заслуга в том, что это была первая (пусть и экспериментальная) СУБД, реализующая SQL (естественно, о стандартах ANSI SQL в то время никто не слышал).

ANSI SQL – это отдельная песня… С одной стороны, есть стандарт, причем развивающийся – начиная с 1986 года было выпущено 7 версий, последняя – в 2008. Это хорошо. С другой стороны, большинство разработчиков наиболее распространенных СУБД не всегда строго следуют стандартам.

На мой взгляд, утверждение о том, что можно писать приложения, общающиеся посредством SQL с любыми реляционными СУБД за счет стандарта ANSI, больше напоминает миф. В принципе, это возможно. Только придется использовать очень ограниченный набор возможностей СУБД. Поэтому, если отсутствие зависимости от поставщика БД важно, а запросы предполагаются нетривиальными, имеет смысл подумать об использовании ORM. И, даже в случае использования ORM, рекомендую тестировать приложение на нескольких СУБД, предполагаемых к использованию заказчиком.

Кстати, далее в примерах я буду использовать диалект Transact-SQL, используемый в СУБД Microsoft SQL Server (просто потому, что он мне знаком намного лучше остальных реализаций SQL).

Базовые понятия

Есть много вариантов терминологии, я далее буду пользоваться той, к которой привык. Для начала, напомню, что, хотя термины БД и СУБД часто смешивают, первый означает одну конкретную базу данных, а СУБД – систему управления БД (то есть программное обеспечение, обеспечивающее работу многих БД).

Если упростить правила Кодда, реляционная БД это БД, в которой все данные структурированы в виде таблиц и все операции с данными – это операции над этими таблицами.
Таблица – это структура данных, представляющая собой столбцы и строки, а на их пересечении содержатся конкретные значения данных (см. также null). Простая аналогия – любая таблица с уникальными заголовками (они будут соответствовать именам столбцов) и не объединенными ячейками – скажем, таблица, хранящая ФИО сотрудников (три столбца) и дату рождения:


Если подойти формально, говоря о конкретных значениях данных я допустил небольшую неточность – реляционная СУБД обязана поддерживать недействительные значения (null). Несмотря на то, что null часто подвергается критике, о нем и его особенностях необходимо знать. Точнее, особенность у него одна – он обозначает отсутствующие данные и не равен никакому значению, в том числе, другому значению null. Последнее логично, но иногда приводит к неожиданным (особенно для игнорирующих обучение) эффектам. Об этом подробнее в следующих сериях…

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

Внешний ключ – это столбец таблицы, значения которого совпадают со значениями первичного (в некоторых СУБД может быть альтернативным) ключа другой (родительской) таблицы. Иными словами, если проводить аналогию между таблицами и классами, то внешние ключи это аналог ссылки на экземпляр другого класса (например, город может ссылаться на регион, а регион на страну).

Скрипт – набор SQL-операторов. Не уверен, насколько распространен этот термин применительно к СУБД (особенно, отличных от MS SQL Server), но я его часто использую, поэтому считаю нужным объяснить.

Нормальные формы

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

Допустим, если для приложения удобно хранить данные через разделители или в виде JSON, то почему бы так и не сделать, даже если это противоречит первой нормальной форме? Единственная оговорка, на мой взгляд – такие данные не должны предназначаться для обработки средствами SQL (например, при соединении таблиц или для поиска индивидуального значения в строке, хранящей значения через запятую). Если же обработка средствами SQL для таких данных требуется – это, скорее всего, “тревожный сигнал” о том, что с дизайном вашей БД не все в порядке.

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

Для начала о нормальных формах достаточно, более подробно и на примерах я рассмотрю этот вопрос позже. А теперь слайды немного конкретики о синтаксисе [Transact-]SQL, а точнее о DDL.

DDL (Data Definition Language)

DDL это то, что позволяет нам создавать или изменять структуру данных. Операторы DDL бывают трех типов – CREATE, ALTER и DROP. Они, как нетрудно догадаться, позволяют создавать, изменять и удалять объекты БД. Причем объекты БД, это не только таблицы, что первым приходит в голову, но и сама БД, представления, процедуры, пользователи.
Часто, при изучении SQL, операторы DDL вкратце упоминают в начале и далее подробно не останавливаются. Лично я считаю, что полезно представлять себе их возможности, дабы на распространенный вопрос “Подскажите как создать таблицу в MS SQL” не было советов вида “Используйте SQL SMO (или DMO)” – это не то что “из пушки по воробьям” – это скорее “ракетой по комарам” :)

Рассмотрим простой пример набора операторов, создающих БД и простую таблицу (ее мы будем использовать в дальнейших примерах). Заранее оговорюсь, что операторы USE (переключает в контекст указанной БД) и GO (признак окончания пакета операторов) необязательно будут работать в СУБД, отличной от MS SQL Server, но сейчас они не критичны для понимания.

 

Что мы сделали этим скриптом?

1. Создали новую БД FirstTest с настройками по умолчанию (для MS SQL Server это, прежде всего, размещение файлов данных и лога, первоначальный их размер и политика увеличения размера, collation).
2. В БД FirstTest создали таблицу Persons (которую еще рефакторить и рефакторить), содержащую часть информации о некоторых людях. Кстати, в проектировании БД я, как и в программировании, стараюсь применять принцип YAGNI и не добавлять столбцы, пока они действительно не понадобятся. Правда, я не стал делать совсем простой (и, в большинстве случаев, неправильный) дизайн и сразу разделил ФИО на составные части.
3. Первые три столбца – это ФИО, каждый столбец представляет собой строку переменной длины с максимальной длиной 100 символов. При этом имя и фамилия обязательны, а отчество нет. Обратите внимание – БД это хорошее место для проверки бизнес-правил. Я не говорю о том, что это лучшее и единственно правильное место, но это последняя линия обороны корректности наших данных и не стоит ей пренебрегать (если это только не слишком больно бьет по производительности, что, чаще всего, не происходит).
4. Последний столбец – предполагает список телефонов через разделитель (максимальный размер, чтобы сильно не задумываться – 4ГБ). Те, кто внимательно прочитал раздел про нормальные формы уже догадываются, что мы нарушили первую нормальную форму, что намекает на небольшой рефакторинг в дальнейшем.

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

• Продолжаем изучение DDL и занимаемся рефакторингом БД
• Манипулируем данными – DML или “а кому нужна БД без данных?”
• Рефакторинг с учетом данных
• Подробнее о запросах
• Управляем доступом и транзакциями