База SQLite и основы SQL. Часть 2
09-11-2021Время чтения ~ 9 мин.SQL 2679
Для работы с любой базой (речь про реляционные) используется структурированный язык запросов — SQL. Сам по себе этот язык очень простой. Во всяком случае намного проще любого языка программирования. Однако многие программисты стараются избегать прямого использования SQL и вместо него применяют различные Active Records, Query Builder или даже такого монстра, как ORM. Обычно SQL-запросы очень простые, буквально несколько строчек кода, который читается как обычный english-текст. Но вместо этого они используют различные надстройки, которые имеют свой особенный синтаксис, хотя на выходе всё равно будет все те же несколько строк обычного SQL. :-)
Почему так происходит?
Мне видится проблема в том, что любой SQL — это не просто набор «функций», а точная инструкция, где указывается что и откуда нужно получить. Невозможно составить SQL-запрос не зная названия таблиц и их полей. А во многих случаях нужно знать ещё и структуру всей базы: какие таблицы, в них поля, а также связи между таблицами. Поэтому, когда программист не знает этих вещей, он будет довольствоваться готовыми функциями, которые, хотя внутри и формируют SQL-запрос, но внешне управляются через обычные параметры.
Если говорить о сложных системах, например CMS, то скорее всего они уже имеют готовые функции, которые «знают» устройство базы. Но на самом деле действительно сложных систем не так много, как может показаться. В большинстве случаев они имеют довольно простую схему таблиц и полей, в которых разобраться можно буквально за 15 минут. Поэтому в реальности, любая CMS — это всего лишь обслуживание базы данных. Например вы можете взять какую-то CMS, посмотреть как устроена её база и написать свой код, который будет её обслуживать (скажем создание и вывод записей сайта).
Всё это делается с помощью SQL. При этом, заметьте, не имеет большой разницы с какой базой работать. SQL — по сути уже стандартный язык, поэтому большинство SQL-запросов будут одинаково работать в SQLite и MySQL. Хотя, да, отличия есть, поскольку стандарт SQL достаточно «скромный» и разработчики СУБД добавляют различные «фишки», делающие их несовместимыми. Но, если говорить о базовом синтаксисе, то различия минимальны.
Для начала рассмотрим базовые операции.
Создание базы
Всё начинается с создания базы. В SQLite — это обычный файл, но для его создания можно использовать командную строку. Вместо этого лично я использую SQLiteStudio, которая делает тоже самое, только в более удобном виде.
Вы можете использовать любой вариант на свой вкус.
База SQLite делается в один клик — просто указываете имя файла. Здесь не нужно создавать юзера и его права и пароли, как в других базах. То есть доступ к базе будет совпадать с пользовательскими правами операционной системы. Как только база создана, она автоматом будет открыта и с ней можно работать. При этом одновременно может быть открыто множество БД.
Изначально база будет пустой, поэтому нужно создать внутри неё хотя бы одну таблицу.
Таблица базы представляет собой массив данных, где есть столбцы и строки. Очень похоже на таблицы Экселя. При создании таблицы базы нужно подумать какие будут столбцы и какие в них будут храниться данные.
Откройте окно для ввода SQL-запросов и введите:
CREATE TABLE firstTable ( id INTEGER, name TEXT );
Этот запрос создаст новую таблицу firstTable. Здесь сработала команда CREATE TABLE. В скобках указываются параметры таблицы — её поля. В данном примере это поле с именем id, который будет иметь тип INTEGER (целые числа) и поле name с текстовым типом (строки).
Такая структура базы позволяет хранить например имена сотрудников и их номера.
Каждый SQL-запрос обязательно закрывается точкой с запятой «;».
В SQLiteStudio в редакторе запросов вы можете разместить сразу множество SQL-запросов, но выполняться будет тот, на котором курсор или который выделен.
Ключевые слова в SQL принято писать в верхнем регистре, хотя на самом деле они регистронезависимы.
Теперь давайте научимся удалять созданные таблицы. Для этого используем запрос:
DROP TABLE firstTable;
Команда DROP TABLE полностью удаляет таблицу, как будто бы её и не было. Если же нужно только очистить все данные, то используется команда DELETE:
DELETE FROM firstTable;
А как удалить всю базу? Для этого достаточно удалить её файл любым способом. Нет файла — нет базы.
Добавление данных
У нас есть определённая структура таблицы, поэтому, когда мы добавляем в неё данные, то должны придерживаться этой же структуры. Для вставки данных используется команда INSERT INTO.
INSERT INTO firstTable (id, name) VALUES (1, 'Вася');
В первых скобках мы перечисляем порядок полей, а во вторых (после VALUES) непосредственно данные. При этом, заметьте, нужно соблюдать тип данных. Поле id — число, а name — текст/строка, поэтому используем одинарные кавычки.
Команда INSERT INTO кажется несколько усложнённой, но в этом есть особый смысл: мы можем менять порядок добавляемых данных, в зависимости от задачи. Например:
INSERT INTO firstTable (name, id) VALUES ('Петя', 2);
Такой подход позволяет использовать произвольный порядок данных. Это особенно актуально, если поле имеет дефолтное значение (об этом речь пойдет позже). При этом, если порядок добавляемых данных совпадает с тем, как они объявлены в таблице, то INSERT INTO может использоваться в сокращённом варианте.
INSERT INTO firstTable VALUES (3, 'Саша');
На самом деле у INSERT INTO есть и другие возможности, но для начала этого будет достаточно.
Получение данных
Для получения данных (называется выборка) используется команда SELECT. Это очень мощная команда и наверное 99% задач с базой будут производиться именно с SELECT. Пока рассмотрим простое применение — получение всех данных таблицы.
SELECT * FROM firstTable; id name -------- 1 Вася 2 Петя 3 Саша
Символ «*» указывает на «все поля». Команда FROM указывает из какой таблицы нужно вывести данные. Данный пример можно перевести как «выбрать все записи из таблицы firstTable».
Если мы хотим вывести только одно поле, например name, то указываем его явно:
SELECT name FROM firstTable; name ---- Вася Петя Саша
Или поменять порядок полей (столбцов) — перечисляем их через запятую:
SELECT name, id FROM firstTable; name id -------- Вася 1 Петя 2 Саша 3
Часто стоит задача при выводе поменять название поля. Например вместо «name» нужно вывести «Имя», а вместо «id» — «Номер». Для этого используется ключевое слово AS:
SELECT id AS 'Номер', name AS 'Имя' FROM firstTable; Номер Имя ---------- 1 Вася 2 Петя 3 Саша
Команда AS часто используется в запросах. Она как бы создаёт синоним поля, таблицы и т.п. По смыслу это наверное даже ближе к понятию переменной в языках программирования.
Удаление дублей
Давайте добавим ещё одну запись:
INSERT INTO firstTable (id, name) VALUES (4, 'Вася');
То есть у нас будет два Васи. И теперь мы хотим получить список всех имён. Если мы сделаем так:
SELECT name FROM firstTable; name ---- Вася Петя Саша Вася
- то получим два Васи. Чтобы исключить из результата выборки дубли, используется ключевое слово DISTINCT:
SELECT DISTINCT name FROM firstTable; name ---- Вася Петя Саша
Теперь дублей не будет. Команда SELECT, получив данные выборки, проверит в них дубли и исключит их. Важный момент — SELECT работает уже с полученными данными. Поскольку мы указали только поле name, то получили данные по одному столбцу. А что будет если сделать выборку по нескольким?
Проверим.
SELECT DISTINCT * FROM firstTable; id name -------- 1 Вася 2 Петя 3 Саша 4 Вася
Здесь мы получаем все поля и для SELECT они представляют собой уже массив данных вида (id, name). Поскольку значения id в таблице разные, то и результаты (id, name) будут уникальными. Мы получим два Васи, но с разными номерами.
Чтобы проверить как работает DISTINCT, добавим например ещё одного Петю с тем же номером:
INSERT INTO firstTable VALUES (2, 'Петя');
Проверить что он добавился можно так:
SELECT * FROM firstTable; id name -------- 1 Вася 2 Петя 3 Саша 4 Вася 2 Петя
Но если мы используем DISTINCT, то дублирующийся Петя не попадёт в выборку.
SELECT DISTINCT * FROM firstTable; id name -------- 1 Вася 2 Петя 3 Саша 4 Вася
Сортировка
Чаще всего результат выборки нужно отсортировать по какому-то полю (или полям). Сортировка бывает по возрастанию и убыванию. Для этого в конце запроса используют ключевое слово ORDER BY.
SELECT * FROM firstTable ORDER BY name; id name -------- 1 Вася 4 Вася 2 Петя 2 Петя 3 Саша
По умолчанию используется прямой порядок или ключевое слово ASC. Для обратного DESC:
SELECT * FROM firstTable ORDER BY name DESC; id name -------- 3 Саша 2 Петя 2 Петя 1 Вася 4 Вася
Допускается сортировка по нескольким полям. Например
SELECT * FROM firstTable ORDER BY name ASC, id DESC; id name -------- 4 Вася 1 Вася 2 Петя 2 Петя 3 Саша
То есть вначале сортируем по имени в прямом порядке, а если значения совпали, то дополнительно сортируем по номеру в обратном порядке.
Эти примеры показывают, что в базе данных все записи («строки») изначально не имеют какого-то порядка. На практике, если не указывать ORDER BY, то база может вернуть данные произвольно. Это принципиально отличает базу от обычной таблицы, где есть жесткий порядок строк и столбцов. При работе с базой нужно явно указывать что именно мы хотим получить и в какой сортировке.
Ограничение количества записей
Чаще всего таблица будет иметь намного больше записей, например сто, тысяча или миллион. Очевидно, что выводить их все одновременно не самое лучшее решение. Поэтому используют ограничение на количество записей с помощью ключевого слова LIMIT. Эта команда указывается в самом конце SQL-запроса.
SELECT * FROM firstTable LIMIT 2; id name -------- 1 Вася 2 Петя
В данном случае мы выводим всего 2 записи. Поскольку у нас не указана сортировка, то SQLite выберет порядок на свой вкус. Поэтому, чтобы гарантировать результат выборки, обычно используется ORDER BY.
SELECT name FROM firstTable ORDER BY name DESC LIMIT 2; name ---- Саша Петя
Как работает этот запрос? Вначале база получит все данные из таблицы (команда FROM). После этого она их отсортирует (ORDER BY) по полю name в обратном порядке (DESC) и возьмёт две строки, которые и выведет с помощью SELECT. Многие программисты подобные запросы оформляют в несколько строк:
SELECT name FROM firstTable ORDER BY name DESC LIMIT 2;
Такое оформление более читабельно.
Команда LIMIT может быть расширена на OFFSET, где указывается смещение — количество записей, которое нужно пропустить. Например:
SELECT * FROM firstTable ORDER BY id LIMIT 2 OFFSET 3; id name -------- 3 Саша 4 Вася
Здесь мы вначале пропускаем 3 записи, а потом оставляем 2.
Часто используют упрощённую форму записи без OFFSET. В этом случае значения указываются через запятую. Данный запрос можно сократить так:
SELECT * FROM firstTable ORDER BY id LIMIT 3, 2;
То есть вначале указывается смещение, а потом кол-во записей. При этом, заметьте, что смещение — это именно количество записей, а нее какой-то её номер по порядку.
Например у нас есть база с сотней записей. Мы хотим их выводить на сайте по 20 штук за раз. Таким образом запросы для каждой страницы будут примерно такими:
SELECT * FROM myPages ORDER BY id LIMIT 0, 20; -- для первой страницы SELECT * FROM myPages ORDER BY id LIMIT 21, 20; -- для второй страницы SELECT * FROM myPages ORDER BY id LIMIT 41, 20; -- для третьей страницы ... и т.д.
Именно так работает пагинация любого сайта: в URL страницы указывается смещение, которое преобразуется в число OFFSET в sql-запросе.
Итого
Команды создания таблиц, их удаления и очистки — это команды администрирования. Они используются достаточно редко.
Команда SELECT — это основа основ. Почти всё программирование будет происходить именно вокруг неё. Поэтому важно с самого начала разобраться с примитивами: ограничение выборки и сортировка. Когда программист не разбирается в SQL, то его код становится, мягко говоря, странным. Я встречал код, когда запрос из базы возвращал все записи, а потом в пытаются их отсортировать, убрать дубли и ограничить количество на php-функциях. То есть вместо того, чтобы поправить SQL-запрос под свою задачу, кое-кто пытается переложить работу на PHP. Так, конечно, делать не стоит. :-)