Введение в язык SQL

Язык, который умудрен знаниями, не будет запинаться. 

Менандр

Что такое SQL?

SQL (англ. structured query language)  — язык структурированных запросов, применяемый для создания, модификации и управления данными в реляционной базе данных. 

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

Изначально SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций:

• создание в базе данных новой таблицы;
• добавление в таблицу новых записей;
• изменение записей;
• удаление записей;
• выборка записей из одной или нескольких таблиц (в соответствии с заданным условием);
• изменение структур таблиц.

Со временем SQL усложнился — обогатился новыми конструкциями, обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры) — и стал приобретать черты, свойственные языкам программирования.

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

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

Таблица 1. Операторы SQL

Реляционные базы данных

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

Подробно реляционные БД данных рассматриваются в разделе “Реляционные базы данных“, где объясняются такие понятия как: 

• первичный ключ;
• внешний ключ;
• отношения “один-к-одному”, “один-ко-многим”, “многие-ко-многим”;
• поддержка целостности данных;
• порядок строк;
• нормализация;
• первая, вторая и третья нормальные формы;
• операции над табличными данными;
• реляционная модель данных.

Диалекты языка. SQLite

Несмотря на наличие международного стандарта ANSI SQL-92, многие разработчики СУБД вносят изменения в язык SQL, применяемый в разрабатываемой СУБД, тем самым отступая от стандарта. Таким образом появляются специфичные для каждой конкретной СУБД диалекты языка SQL.

Одной из популярных СУБД является SQLite, у которой также имеется свой диалект SQL.

Полную информацию о данном диалекте можно получить на сайте https://sqlite.org/lang.html  Частично данная информация доступна и на русском языке: https://oracleplsql.ru/sqlite-manual.html 

Типы данных

SQLite имеет динамическую типизацию. Это означает, что тип данных привязывается не к полю таблицы, а к хранимому значению. 

Таблица 2. Классы хранения

📝 В базах, созданных My Visual Database, используется кодировка UTF-8.

☝ Если в ходе любой операции один из операндов принимает значение NULL, то все выражение принимает значение NULL

SQLite не имеет отдельного логического класса хранения. Вместо этого, логические значения хранятся как целые числа 0 (false) и 1 (true).

SQLite не имеют классов, предназначенных для хранения дат и/или времени. Вместо этого, встроенные функции даты и времени в SQLite способны работать с датами и временем, сохраненными в виде значений TEXT, REAL и INTEGER в следующих форматах:

Таблица 3. Форматы хранения даты/времени

📝 В My Visual Database используется текстовый формат хранения даты/времени по шаблону “YYYY-MM-DD HH:MM:SS.SSS”

📝 В My Visual Database имеется тип данных “ДЕНЬГИ”, который реализуется на уровне визуальных компонентов. Для хранения значений в базе данных используется класс REAL.

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

Таблица 4. Аффинированные типы

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

1. Если объявление типа содержит строку “INT”, то столбец ассоциируется с аффинированным INTEGER.

2. Если объявление типа столбца содержит любую из строк “CHAR”, “CLOB”, или “TEXT”, то аффинированность определяется как TEXT. 

3. Если объявление типа столбца содержит строку “BLOB” или если тип не указан, то столбец аффинируется с NONE.

4. Если объявление типа столбца содержит любую из строк “REAL”, “FLOA” или “DOUB”, аффинированность определяется как REAL.

5. В остальных случаях столбцу сопоставляется аффинированный NUMERIC.

☝ Последовательность правил для определения родства столбца имеет важное значение. Столбец с заявленным типом “CHARINT” будет соответствовать как правилу 1, так и правилу 2, но первое правило имеет преимущество, и поэтому родство столбца определится как INTEGER.

При использовании встроенных механизмов MVDB для доступа к данным никакой путаницы не возникнет, а вот при использовании SQL-запросов для записи данных вам нужно быть внимательным и следить за используемыми типами.

Таблица 4. Соответствие типов данных MVD и классов хранения SQLite

Литералы

Строковые литералы всегда заключаются в одинарные кавычки (‘) или двойные кавычки (”).

‘Строка’
“Тоже строка”

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

43
-43
4.3
0.43E01Code language: CSS (css)

Для обозначения даты и времени используются строковые литералы, отформатированные по шаблону “YYYY-MM-DD” или “YYYY-MM-DD HH:MM:SS”

‘2020-03-12’
“2020-02-21 12:34:18”Code language: CSS (css)

Экранирование

Если литерал должен включать в себя кавычки, то необходимо удвоить их количество, например:

‘Кот-д’’Ивуар’
‘ООО “”Рога и копыта””’

Алиасы

Псевдонимы (алиасы) SQLite можно использовать для создания временного имени для столбцов или таблиц.

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

<выражение> AS <имя>Code language: HTML, XML (xml)

Псевдонимы таблиц используются для сокращения вашего SQL-кода, чтобы его было легче читать или когда вы выполняете self join (т.е. перечисляете одну и ту же таблицу более одного раза в операторе FROM).

<источник данных> AS <имя>Code language: HTML, XML (xml)

Сравните две записи SQL запроса, без использования алиасов

SELECT
  PrimaryTarget.Coord_X,
  SecondaryTarget.Coord_X,
  (PrimaryTarget.Coord_X - SecondaryTarget.Coord_X)
FROM PrimaryTarget
LEFT JOIN SecondaryTarget on SecondaryTarget.ID = PrimaryTarget.ID

и с алиасами

SELECT
  pt.Coord_X as Prim_X,
  st.Coord_X as Sec_X,
  (pt.Coord_X - st.Coord_X) as Distance
FROM PrimaryTarget pt
LEFT JOIN SecondaryTarget st on st.ID = pt.IDCode language: JavaScript (javascript)

Комментарии

Для того, чтобы добавить примечания или пояснения к тексту SQL-запроса, можно использовать комментарий. Комментарии бывают двух типов: идущий до конца строки и многострочный. Два знака “минус” подряд превращают весь последующий текст до окончания строки в комментарий. А парные ограничители “/*” и “*/” позволяют комментировать любой текст.

--  это комментарий, который идет до конца строки
/* а этот комментарий ограничивается специальными символами и может занимать несколько строк */  Code language: JavaScript (javascript)

Выборка данных. SELECT

Для получения набора данных из базы служит команда SELECT. Рассмотрим упрощенный вариант синтаксиса данной команды, который позволит нам решать большинство практических задач (полный синтаксис данной команды можно получить по ссылке в главе 1.3.).

SELECT <данные> FROM <источник> [ <условие отбора> ] [ <условие группировки> ] [ <условие сортировки> ] [ <ограничение выборки> ]

После ключевого слова SELECT идет список данных, которые нужно извлечь из базы данных. Потом следует ключевое слово FROM, за которым следует указание, откуда именно необходимо извлекать данные. Остальные составляющие запроса являются необязательными и подробно будут рассмотрены в главе 3. Анатомия запроса SELECT.

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

Допустим, у нас есть таблица nameList, состоящая из четырёх колонок и содержащая имена людей и дату их рождения:

id, name,   surname,   birthday
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1,  Вася,   Иванов,    21.02.1986
2,  Коля,   Петров,    17.08.1989
3,  Наташа, Сидорова,  31.03.1990
4,  Света,  Кузнецова, 02.12.1986
5,  Петя,   Михайлов,  08.07.1981Code language: CSS (css)

тогда запрос

SELECT name FROM nameList

 вернет следующий набор данных:

Вася
Коля
Наташа
Света
Петя

А теперь давайте поближе познакомимся со средой разработки My Visual Database.