Курс по SQL

[rockclimber]

О курсе

Примечание 1 (для тех, кто не в теме)
Курс рассчитан на практическое применение и быстрое вхождение в курс дела, поэтому большинство второстепенных нюансов будут опускаться (просто их слишком много). Углубляться буду, если будут уточняющие вопросы.
Примечание 2 (для тех, кто в теме)
Практически про любое предложение ниже можно сказать “оно конечно да, но есть ряд нюансов…” Я в курсе, а для остальных есть пункт выше.
Примечание 3
Целевая аудитория - люди, у которых есть необходимость работать с данными, и познания в SQL могут сильно облегчить им жизнь.

(Небольшой тест:)

1. Вам по роду деятельности приходится работать с большими массивами более-менее однородной информации, и возможностей Excel вам не хватает? У вас есть доступ в том или ином виде к рабочей БД, но незнание SQL ограничивает вашу продуктивность? Или, возможно, вы хотите быть готовым к чему-то подобному в будущем?
- Да, этот курс для вас.
2. Ваш начальник выиграл тендер на разработку ПО для управления АЭС, а так как программистов у вас нет, то он решил, что лучше всего будет заставить вас пройти курс по SQL на dxdy.ru?
- Нет, этот курс не для вас, лучше пройдите более длинный, но более систематический курс обучения.

Примечание 4
В силу исторических нюансов разные СУБД довольно сильно отличаются. Я более-менее нормально знаю Oracle и чуть-чуть - PostgreSQL, а про остальные знаю только то, что они есть. Поэтому ответы на многие вопросы придется искать в другом месте. Я буду помечать такие места пометкой "см. Прим. 4".

Общая информация

1. Что это?
SQL - это язык запросов. Считается, что это не язык программирования, хотя не все с этим согласны. Лично мне - все равно.
Это язык запросов для реляционных баз данных. Реляционные базы данных имеют в своей основе реляционную алгебру - математический аппарат, разработанный Эдгаром Коддом сотоварищи в IBM в 60 - 70-е годы.
2. Где это?
Для выполнения запросов вам понадобится система управления базами данных (СУБД) - программный продукт, который берет на себя работу по размещению данных на диске, извлечению и прочим манипуляциям. СУБД - это посредник между вами и вашими данными в том виде, в каком они лежат на диске и в оперативной памяти. SQL - это язык, на котором вы общаетесь с этим посредником. Термины СУБД и БД часто путают (первое часто называют вторым). БД - это ваши данные, СУБД - программный комплекс для их обработки.
Наиболее известные представители:
Клиент-серверные: Oracle, MS SQL Server, MySQL (и ее клон Maria DB), PostgreSQL, DB2, Firebird, Sybase.
Файл-серверные: SQLite, MS Access.
3. Зачем это?
Изначально SQL назывался SEQUEL (Structured English QUery Language, читается “сиквел”) и предназначался для конечных пользователей. Потом, по мере развития и появления новых фич, язык превратился в полноценный инструмент разработки. Теперь он называется SQL и произносится как “эс-кью-эл” (канонично, нейтив инглиш спикерами) или “эскуэль” (эльфийский акцент, распространенный в русскоговорящей среде). А если вы слышите “сиквел” - то перед вами либо человек, заставший еще те времена (времена SEQUEL), либо (скорее всего) чайник, повторяющий модное словечко.
4. Как это?
Существуют стандарты SQL, однако нет большого смысла читать что-то, кроме SQL-92 и может быть SQL-99. Более ранние совсем старые, а более новые поддерживаются производителями СУБД как бог на душу положит, или даже хуже. В любом случае имеет смысл в первую очередь читать документацию по вашей СУБД. Кроме того, производители СУБД часто придумывают собственные синтаксические конструкции. “Стандартный” синтаксис часто называют “ANSI SQL” или “ANSI синтаксис”.
5. Как это работает?
SQL - декларативный язык. Это значит, что вы не говорите “возьми такое-то значение, положи в такую-то ячейку памяти, потом возьми из вон той ячейки значение, умножь на эту константу и покрась вот этот пиксель в получившийся цвет”. Вы говорите “а покажи-ка мне все данные вот с такими-то свойствами, которые у тебя есть”. Управление "ячейками памяти" и выбор последовательности действий остается за СУБД.
Как попробовать?
Простые способы:
sqlfiddle.com - время от времени лежащий сайт, доступны более-менее популярные СУБД
MS Access - думаю, в представлении не нуждается
Oracle APEX - как Access, только СУБД - Oracle, а вместо форм для Windows - веб-странички (на нем сделан интерфейс для моей статистики). Попробовать можно на apex.oracle.com.

Сложные способы:
Установить конкретную СУБД
Установить какой-нибудь инструмент для работы с ней или освоить командную строку
Инструменты для Oracle: SQL*Plus (командная строка), IDE - Oracle SQL Developer, PL/SQL Developer, TOAD. На сайте Oracle также можно (бесплатно, но после регистрации) скачать Virtual Box и образы Oracle Linux с установленным ПО (самые последние и навороченные версии любых оракловых продуктов), все готовое к использованию.
Инструменты для postgreSQL: pgAdmin
Остальные - см. Прим. 4.
См. также Comparison of database tools (enWiki)


Основные понятия.

Самое наверно главное понятие - это Table (таблица). Это абстракция для места хранения данных. Когда вы записываете данные, вы записываете их в таблицу. И читаете из таблицы. И удаляете. Удаляя таблицу целиком, вы удаляете вообще все, что в ней было. Сама таблица при этом физически не обязательно находится в одном месте на диске, или в одном файле, и даже не обязательно на одном компьютере.
View (нет общепринятого перевода, иногда говорят просто “вью" или “вьюха”, иногда - “представление” (как в цирке, ага)) - это, фактически, хранящийся на сервере текст запроса, который выполняется каждый раз, когда вы к нему обращаетесь. С точки зрения пользователя, вьюхи неотличимы от таблиц.
Таблицы/вью состоят из столбцов (column). Каждый столбец имеет имя и тип.
Основные типы: число, строка, дата, интервал (времени), и "большие объекты” (LOB, Large OBject). В “лобах” часто хранят файлы, например, но можно и просто длинный текст. Реализации Лобов сильно отличаются в разных СУБД. Плюс каждая СУБД имеет 100500 своих собственных типов.
Constraint (ограничение целостности, иногда просто “констрейнт”) - особые правила, указывающие, что можно и что нельзя хранить в данной таблице или столбце.
Index (индекс) - особая структура данных, служащая для оптимизации быстродействия. У каждой СУБД в этом месте свои тараканы.
Trigger (триггер) - кусок процедурного кода, привязанный к некоторому событию и выполняющийся каждый раз при его наступлении.
Schema (схема) - контейнер для объектов, перечисленных выше. Каждая таблица, вью, индекс и т. п. находится в какой-то схеме. Это одинаково для всех СУБД, а вот конкретные детали этого дела в разных СУБД выглядят сильно по-разному.

И еще надо упомянуть такую штуку, как transaction (транзакция). Это неделимый набор операций. Для любого внешнего наблюдателя транзакция либо выполнена, либо нет. Например, если транзакция состоит из 5 вставок и трех удалений, то вы никогда не увидите, что сделаны только первые три вставки и все. За подробностями см. cтатью в википедии про ACID, а для особо продвинутых есть еще теорема CAP.

Немного о типах данных
Печальная новость - во всех СУБД всё по-разному (см. прим. 4).
Числа.
Например, в оракле общепринятого в обычных языках деления на целые и дробные нет (точнее, есть, но ими никто не пользуется). Есть тип number, который надо "уточнять". Он хранит до 38 значащих цифр, number(38, 0) - это целое число, имеющее до 38 десятичных цифр, number(12,2) - число с 10 знаками до запятой и 2 после. В postgreSQL куча целых типов (разной степени точности) и куча дробных.
С числами можно производить обычные арифметические операции.
Строки. Строки должны иметь фиксированный размер. Объявляя строковый тип, нужно обязательно указывать длину строки. Например, в Oracle поле таблицы, описанное как varchar2(100) может хранить строку до 100 символов длиной (и конечно же, вся эта мозговыносящая бадяга с юникодными символами размером больше одного байта и т. п. тут тоже присутствует в полный рост, желающие могут погрузиться в документацию).
Строки можно конкатенировать. Кроме того, в каждой СУБД есть какой-то глобальный лимит на длину строкового типа, надо следить, чтобы при конкатенации за него не вылезти.
Дата. Отдельный тип данных. Многие часто путают дату и ее строковое представление (которое может выглядеть по-разному в зависимости от настроек). Хоть это и отдельный тип, с ней можно работать как с числом (в оракле и постгресе точно), за единицу принимаются одни сутки. Дробная часть числа - время.
Интервал. Имеется в виду временной интервал. Это еще один отдельный тип данных. В оракле их целых два. Один - INTERVAL YEAR TO MONTH и второй - INTERVAL DAY TO SECOND. Без понятия, зачем так.
Операции с датами и интервалами.
К дате можно прибавить (или вычесть) число, целая часть числа будет расцениваться как число дней, дробная - как соответствующая доля от 24 часов. Результат - дата.
К дате можно прибавить или вычесть интервал. Результат - дата.
Можно вычесть одну дату из другой и получить интервал (который можно также трактовать как число).

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

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

[iifat]

rockclimber в сообщении #1321940 писал(а):

NULL и пустая строка - теоретически, не одно и то же. Тем не менее, в оракле не так

Тут бы уточнить: что, действительно так? А чему равна конкатенация строки с пустой строкой?

[EUgeneUS]

rockclimber в сообщении #1321940 писал(а):

Базовое правило: если в какой-то операции одно из полей имеет значение NULL, результат тоже должен быть NULL

Чему будет равно Null OR True ?

[upgrade]

Подскажите, какие есть приемы реализации связности таблиц и проверки целостности данных. Например, в цепи |один|-|многие-один|-|многие-многие|-|один.
И что за такой зверь странный - объектная база данных. Как оттуда получить таблицу.

[iifat]

EUgeneUS в сообщении #1321959 писал(а):

Чему будет равно Null OR True?

В Postgres — True

[EUgeneUS]

iifat
Так же и в Оракле.
Насколько знаю, троичная логика в логических операциях с Null включена в стандарт SQL.

[iifat]

EUgeneUS в сообщении #1321976 писал(а):

Так же и в Оракле

Обычно, так и бывает. Есть у меня ни на чём не основанное чувство, что Postgres делается по следам Оракла.

upgrade в сообщении #1321962 писал(а):

что за такой зверь странный - объектная база данных

В, опять же, Postgres это наследование таблиц. Можно объявить таблицу потомком некой другой, при этом скопируются описания столбцов и простейшие условия корректности, но можно и добавить. В запросе на выборку можно указать выборку из всего дерева наследования; впрочем, в отсутствие единых индексов штука не слишком-то, имхо, полезная.

[gevaraweb]

rockclimber в сообщении #1321940 писал(а):

Как попробовать?
Простые способы:
sqlfiddle.com
- время от времени лежащий сайт, доступны более-менее популярные СУБД
MS Access - думаю, в представлении не нуждается
Oracle APEX - как Access, только СУБД - Oracle, а вместо форм для Windows - веб-странички (на нем сделан интерфейс для моей статистики). Попробовать можно на apex.oracle.com.

Можно наверно еще упомянуть сайт http://sql-ex.ru/ - позиционирует как практическое владение языком SQL

[rockclimber]

gevaraweb в сообщении #1322100 писал(а):

Можно наверно еще упомянуть сайт http://sql-ex.ru/ - позиционирует как практическое владение языком SQL

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

[rockclimber]

iifat в сообщении #1321974 писал(а):

EUgeneUS в сообщении #1321959 писал(а):

Чему будет равно Null OR True?

В Postgres — True

iifat в сообщении #1321944 писал(а):

rockclimber в сообщении #1321940 писал(а):

NULL и пустая строка - теоретически, не одно и то же. Тем не менее, в оракле не так

Тут бы уточнить: что, действительно так? А чему равна конкатенация строки с пустой строкой?

Ну вот и тараканы повылазили, долго ждать не пришлось

Логические операции:
В Oracle нет типа boolean в SQL. В postgreSQL - есть. В других - я погуглил, всё сложно
Соответственно, в Oracle запрос

select true from dual

выдает "ORA-00904: "TRUE": invalid identifier"
В postgreSQL, видимо, есть некоторая оптимизация логических выражений, которая частично перекрывает логику работы с NULL, в результате имеем следующий набор тараканов:
null or true дает true
null and true дает null

Операции со строками:
'abc' || '' - конкатенация с пустой строкой
'abc' || null - конкатенация с null
В postgreSQL первая дает 'abc', вторая - null, в Oracle обе дают 'abc'.
Функция length - длина строки. length('') в postgreSQL дает 0, в Oracle - null.

Операции с числами.
Специально не проверял, но по памяти помню, что все должно быть правильно в обеих.

Объектные базы данных.
Не знаю, что за зверь. Самый известный представитель - Caché, как я понимаю, но руками не трогал. Что-то вроде попытки сделать хранилище для объектов в том виде, как они понимаются в объектно-ориентированном программировании. Наследование таблиц в postgreSQL - шаг в ту же сторону, но как по мне - это попытка натянуть сову на глобус. Способов как-то связать объектную модель и реляционную - вагон, но обычно они плохо клеятся друг с другом. Ну и оффтоп это тут, даже с учетом того, что тот же Caché декларирует возможность использовать SQL.

До остального потом дойдем.

[realeugene]

Всё замечательно, кроме одного: что скорее может потребоваться "людям, у которых есть потребность работать с данными", Oracle/PostgreSQL, или локальный SQLight, вызываемый из Python?

[iifat]

(Оффтоп)

rockclimber в сообщении #1322177 писал(а):

вот и тараканы повылазили

Ну вот, спасибо, теперь я знаю, по крайней мере, к какому отряду отношусь.

realeugene в сообщении #1322179 писал(а):

что скорее может потребоваться "людям, у которых есть потребность работать с данными", Oracle/PostgreSQL, или локальный SQLight, вызываемый из Python?

Скажем так, целевая аудитория, по-моему, вполне точно определена ТС. Питон — это уже программёж со всеми прелестями. Sequel предназначался изначально для бухгалтера (ну, среднего американского бухгалтера тех времён).

[EUgeneUS]

Про Null в Оракле рекомендуется к прочтению https://habr.com/post/127327/

[rockclimber]

realeugene в сообщении #1322179 писал(а):

Всё замечательно, кроме одного: что скорее может потребоваться "людям, у которых есть потребность работать с данными", Oracle/PostgreSQL, или локальный SQLight, вызываемый из Python?

Вы бы еще спросили, Linux или Windows вам выбрать Если же вопрос стоит как "файловая vs клиент-серверная", то тут все относительно просто. Если вам надо написать приложение, где с каждым экземпляром БД будет работать только один пользователь, а общих данных у пользователей не будет (например, приложение для ведения домашней бухгалтерии), то можно брать файловую, а если это многопользовательское приложение - то клиент-сервер. Кроме того, у файловых в принципе нет такой проблемы, как "администрирование сервера БД".

(Оффтоп)

iifat в сообщении #1322185 писал(а):

rockclimber в сообщении #1322177 писал(а):

вот и тараканы повылазили

Ну вот, спасибо, теперь я знаю, по крайней мере, к какому отряду отношусь.

Ну я не про вас лично, конечно же, а про

rockclimber в сообщении #1321940 писал(а):

У каждой СУБД в этом месте свои тараканы.

В таком виде эта мысль фигурировала в разделе про индексы, но обо всём остальном можно так же сказать.

[rockclimber]

rockclimber в сообщении #1322249 писал(а):

Если же вопрос стоит как "файловая vs клиент-серверная", то тут все относительно просто.

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