Язык запросов SQL
Если у вас есть похоже данные, то их удобно представлять в виде таблицы. Даже школьный журнал — это таблица с перечнем имен и оценками.
Пример данных, представленных в табличном виде:
| Продукт | Количество | Куплен | Отдел |
|---|---|---|---|
| Морковка | 2 большие | Нет | 🍏/🥦 |
| Молоко | 1 литр | Нет | 🥛 |
| Масло | 1 пачка | Нет | 🥛 |
| Бананы | 1-2 кг | Да | 🍏/🥦 |
| Сыр | 200 грамм | Нет | 🥛 |
| Яблоки | 500 грамм | Да | 🍏/🥦 |
| Куриные ножки | 500 грамм | Нет | 🥩 |
| Лук | 4 луковицы | Да | 🍏/🥦 |
Мы часто используем данные в виде списков или таблиц. Если данных становится очень много, то возникает вопрос как их эффективно хранить. Например, список продуктов в примере не очень большой и им все еще можно пользоваться. Если внимательно присмотреться к данным, то можно предположить, что покупатель уже успел зайти в отдел фруктов и овощей, но еще не был в молочном и мясном отделе. И, к сожалению, в овощном отделе не было морковки.
Но если надо было бы закупить множество вещей перед путешествием, то он спокойно мог бы разрастись на 150 пунктов (особенно если с вами едут дети). А если представить себе склад магазина или учет продаж товаров за день. То таблица получится очень и очень большой.
Для более удобного учета используют таблицы, например Microsoft Excel. И если будет задача проверить какие еще товары нужно докупить в овощном отделе, то можно применить фильтрацию. Например, если бы мы хотели увидеть из списка только еще не купленные товары из овощного отдела, то пришлось бы добавить фильтр на "Отдел" и "Куплен".
Для того чтобы решать задачи, связанные с поиском и обработкой табличных данных и существует язык запросов SQL.
Создание таблиц
Прежде чем обрабатывать данные в базах данных необходимо описать какие данные будут храниться. Или говоря профессиональным языком "описать структуру и взаимоотношения данных".
Продолжая пример со списком продуктов видно, что столбцы — это характеристики каждого из добавленных товаров. То есть можно сказать, что у каждого товара есть:
- Название, это обычно строка
- Количество или вес
- Статус куплен товар или нет
- Отдел супермаркета, в котором лежит товар
Для описания таблиц существует специальный синтаксис команды, которая позволяет ее создать. Он выглядит следующим образом:
CREATE TABLE table_name (
column1 datatype(length) params,
column2 datatype(length) params,
column3 datatype(length) params,
....
);
Ключевая команда CREATE TABLE говорит базе какое действие будет совершаться. После этого следует название таблицы и в скобках перечень столбцов с указанием типа и дополнительными параметрами (если требуются).
Типы столбцов
В базах данных очень строго ведется учет типов значений, которые положить в базу. В отличии от таблицы в Excel в каждой ячейке строки можно добавить только то значение указанного типа.
Ограничения сделаны не случайно, благодаря им получается оптимизировать и ускорять работу с большим объемом данных. А также применять групповые операции.
Для разных баз данных есть общие типы данных, которые используются повсеместно. Согласно стандарту SQL базы должны поддерживать следующие типы значений:
- Пустое значение NULL
- Целые INTEGER и вещественные числа REAL
- Дата DATE и время TIME
- Булевые типы BOOL
- Строки CHAR и текст TEXT
- BLOB бинарные данные, которые хранятся без обработки
Разны СУБД поддерживают разные типы данных. Например:
- PostgreSQL имеет даже типы для записи IP адреса или геометрических фигур.
- SQLite наоборот стремится к максимальному упрощению и имеет всего несколько типов к которым сводятся все остальные
Выбор типа столбца
Для баз данных очень важно знать какие реальные значения будут храниться, и лучше всего подбирать тип данных, максимально близко подходящий к реальной ситуации. Например, для хранения цены товара в таблице не может быть отрицательного значения. Поэтому правильно указывать тип столбца, который не может принимать отрицательные значения, то есть хранить тип чисел без знака. Для учета температур пациентов не может быть больших значений, и если база позволяет, то лучше выбрать тип столбца со значениями в минимальном диапазоне. База сможет оптимизировать такие данные, будет использоваться меньше памяти, места на диске и в целом система будет работать быстрее.
Преобразование типов в SQLite
В SQLite для оптимизации возможностей движка и сохранения его простоты количество типов данных сокращено до очень маленького списка. Хотя в стандарте SQL описано их гораздо больше.
То есть если при создании таблицы вы укажете тип столбца из стандарта, то в реальности в таблице будет использоваться только один из перечисленных типов
- INTEGER, для всех числовых типов. Альтернативное написание INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8
- TEXT, для всех текстовых и строковых данных. Обратите внимание, что в SQLite нет ограничения на длину строки, значение в скобках не будет проверяться. Альтернативное написание CHARACTER(N), VARCHAR(N), VARYING CHARACTER(N), NCHAR(N), NATIVE CHARACTER(N), NVARCHAR(N), TEXT, CLOB
- BLOB, для хранения бинарных данных. Например, картинок
- REAL, для вещественных чисел (с дробной частью). Альтернативное написание REAL, DOUBLE, DOUBLE PRECISION, FLOAT
- NUMERIC, для чисел с фиксированной структурой. Альтернативное написание NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME
То, что база хранит у себя внутри под капотом нас интересует с исследовательской точки зрения. Но основная целевая аудитория кода, который пишет человек — это человек. Поэтому лучше всего указывать тип данных, который будет лучше всего понятен человеку.
Запрос на создание таблицы
Когда мы обсудили теорию давайте разберем пример запроса для создания таблицы:
CREATE TABLE groceries (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL UNIQUE,
amount TEXT NOT NULL,
is_done BOOL NOT NULL
);
Для хранения списка покупок мы создаем таблицу groceries в которой будут пронумерованы строки. За это отвечает столбец id. Тип данных столбца числовой, а параметр PRIMARY KEY указывает что это поле будет первичным ключем в таблице. Заодно при добавлении новых строк таблица сама будет вписывать новое уникальное значение числа, что сильно упростит учет.
Поля title и amount текстовые. К сожалению, в базе нет типа который бы хорошо подошел к значению количество товара. Можно было бы использовать вес, но для яиц такой вариант бы не подошел. Чтобы случайно не ввести дважды один и тот же товар с одинаковым именем поле title имеет параметр UNIQUE, а значит значения этого поля могут быть только уникальными. Мы не сможем указать дважды кукурузу. Правда поскольку база чувствительна к регистру, то "кукуруза", "Кукуруза" и "ВКУСНАЯ КУКУРУЗА" будут разными значениями.
Поле is_done отвечает за признак "куплено" и может принимать значение TRUE или FALSE.
Как хранить отдел мы разберемся в следующих уроках.
Добавление записей в таблицу
Для того чтобы добавить запись к базе надо отправить запрос на внесение данных. Синтаксис запроса:
INSERT INTO table (column1, column2, ...)
VALUES (value1, value2 , ...);
Одна строка добавляет одно значение. Обратите внимание на то, что каждая команда к базе обязательно заканчивается на знак точка с запятой ;.
Для того чтобы добавить товары в базу можно отправить такие запросы:
INSERT INTO groceries (title, amount, is_done)
VALUES ("Морковка", "2 большие", FALSE);
INSERT INTO groceries (title, amount, is_done)
VALUES ("Молоко", "1 литр", FALSE);
INSERT INTO groceries (title, amount, is_done)
VALUES ("Масло", "1 пачка", FALSE);