СУБД SQLite
Что такое СУБД и реляционные БД
Подключение к БД, создание и удаление таблиц
Команды SELECT и INSERT
Команды UPDATE и DELETE
Агрегирование и группировка GROUP BY
Оператор JOIN для формирования сводного отчета
Оператор UNION объединения нескольких таблиц
Вложенные SQL-запросы
Методы execute, executemany, executescript, commit, rollback
методы fetchall, fetchmany, fetchone, iterdump
Обучающие курсы
Python Python ООП Структуры данных Django C/C++
Телеграм-каналы
Канал selfedu_rus Python Django Машинное обучение Java и С++
Наш канал

Команды SELECT и INSERT

На предыдущем занятии мы с вами научились создавать БД и таблицы. Теперь пришло время узнать как создавать SQL-запросы для добавления записей и выборки данных. Для этого используются две команды:

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

INSERT

Начнем с первой команды – добавления записей. Ее синтаксис следующий:

INSERT INTO <table_name> (<column_name1>, <column_name2>, ...) VALUES (<value1>, <value2>, …)

или так:

INSERT INTO <table_name> VALUES (<value1>, <value2>, …)

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

Давайте посмотрим на работу SQL-запроса в программе DB Browser. Откроем нашу БД saper.db и выполним следующий запрос:

INSERT INTO users VALUES('Михаил', 1, 19, 1000)

В таблице появится запись с соответствующими данными. Или же, учитывая, что для поля sex установлен ограничитель DEFAULT 1, можно его не указывать при добавлении записи:

INSERT INTO users (name, old, score) VALUES('Федор', 32, 200)

Получим еще одну запись со значением sex = 1, которое автоматически было добавлено СУБД при создании записи.

SELECT

Следующая команда наиболее часто используется при составлении SQL-запросов. Она отвечает за выборку данных из таблицы. В самом простом случае она записывается по такому синтаксису:

SELECT col1, col2, … FROM <table_name>

Например, так:

SELECT name, old, score FROM users

На выходе получим выборку из трех столбцов и набора всех записей из таблицы users:

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

SELECT * FROM users

Оператор WHERE

Если нам нужно добавить фильтр для выбираемых записей, то это делается с помощью ключевого слова WHERE, которое записывается после имени таблицы:

SELECT col1, col2, … FROM <table_name> WHERE <условие>

Например, отберем все записи со значением очков меньше 1000:

SELECT * FROM users WHERE score < 1000

На выходе будет вот такая таблица:

Как видите из этого примера, после слова WHERE записывается логическое выражение и в качестве сравнения можно использовать следующие операторы:

= или ==, >, <, >=, <=, !=, BETWEEN

Например, так:

SELECT * FROM users WHERE score BETWEEN 500 AND 1000

Будут выбраны все записи с числом очков в диапазоне от 500 до 1000. Или, так:

SELECT * FROM users WHERE old = 32

Выбираются записи с игроками возрастом 32 года. Кстати, вместо обычного равно также можно записывать и два равно. Это будет одно и то же. Два равно введено для привычки программистов записывать оператор сравнения в таком виде, т.к. одно равно во многих языках программирования – это оператор присваивания. Но в SQL = и == это операторы сравнения и можно использовать любой из них.

Составные условия

Часто при описании фильтра требуется учитывать значения сразу нескольких столбцов. Например, выбрать всех игроков старше 20 лет и с числом очков более 300. Здесь уже нужно использовать составное условие. Для этого дополнительно используются следующие ключевые слова:

AND – условное И: exp1 AND exp2. Истинно, если одновременно истинны exp1 и exp2.
OR – условное ИЛИ: exp1 OR exp2. Истинно, если истинно exp1 или exp2 или оба выражения.
NOT – условное НЕ: NOT exp. Преобразует ложное условие в истинное и, наоборот, истинное – в ложное.
IN – вхождение во множество значений: col IN (val1, val2, …)
NOT IN – не вхождение во множество значений: col NOT IN (val1, val2, …)

Например:

SELECT * FROM users WHERE old > 20 AND score < 1000

Выбирает игроков возрастом более 20 лет и с числом очков менее 1000. Или, так:

SELECT * FROM users WHERE old IN(19, 32) AND score < 1000

Создается выборка из игроков возрастом 19 или 32 года и числом очков менее 1000. Следующий запрос:

SELECT * FROM users WHERE old IN(19, 32) AND score > 300 OR sex = 1

Выберет все записи из таблицы users. Здесь фильтр будет работать так: выбираются игроки возрастом 19 или 32 года и числом очков более 300 или те, у которых мужской пол (sex = 1). И, так как у нас все игроки имеют мужской пол, то все они и будут отображены в результатах отбора. Этот пример показывает важность приоритетов: приоритет у операции AND выше, чем у OR, поэтому AND выполняется раньше OR.

Если нам нужно записать условие выбора игроков возрастом 19 или 32 года или мужского пола и, вместе с тем, чтобы число очков у них было более 300, то следует использовать круглые скобки для OR:

SELECT * FROM users WHERE (old IN(19, 32) OR sex = 1) AND score > 300

На выходе получим таблицу:

То есть, круглые скобки меняют приоритет и сначала выполняется проверка по OR, а затем, результат сравнивается по AND.

Самый высокий приоритет имеет операция NOT, она выполняется в первую очередь. Например:

SELECT * FROM users WHERE old IN(19, 32) AND NOT score > 300

Выборка игроков с числом очков меньше, или равным 300.

Сортировка ORDER BY

После условия в команде SELECT можно дополнительно указывать сортировку записей по определенному столбцу. Предположим, что мы хотим выбрать всех игроков с числом очков менее 1000 и отсортировать их по возрастанию возраста. Это можно сделать так:

SELECT * FROM users WHERE score < 1000 ORDER BY old

Здесь после оператора ORDER BY указывается поле, по которому производится сортировка записей в выборке. По умолчанию сортировка делается по возрастанию:

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

SELECT * FROM users WHERE score < 1000 ORDER BY old DESC

Получим значения:

Кстати, если нужно явно показать, что сортировка производится по возрастанию, то можно записать флаг ASC:

SELECT * FROM users WHERE score < 1000 ORDER BY old ASC

Ограничение выборки LIMIT

В реальных таблицах число записей может быть очень большим. Например, число игроков может достигать тысяч или даже десятков тысяч. И, представьте, что из всего этого числа нам нужно взять первых 10 игроков с наибольшим числом очков, чтобы сформировать ТОП-10 лучших. Для этого в команде SELECT используется еще один оператор – LIMIT, который записывает в самом конце и имеет следующие синтаксисы:

LIMIT <max> [OFFSET offset]

LIMIT <offset, max>

Для демонстрации работы этого оператора, добавим в таблицу еще несколько записей:

Мария        2       18     400
Сергей       1       33     2000
Владимир 1       43     100
Елена         2       17     500
Юля 2       23     700

И, сформируем запрос на выбор лучших пяти игроков:

SELECT * FROM users WHERE score > 100 ORDER BY score DESC LIMIT 5

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

Дополнительный параметр OFFSET позволяет пропускать несколько первых записей. Например, если записать запрос в таком виде:

SELECT * FROM users WHERE score > 100 ORDER BY score DESC LIMIT 5 OFFSET 2

То первые две записи с очками 2000 и 1000 будут пропущены и отобраны следующие пять:

Или, этот же запрос можно записать так:

SELECT * FROM users WHERE score > 100 ORDER BY score DESC LIMIT 2, 5

Работа с выборкой в программе на Python

Все приведенные SQL-запросы можно выполнять непосредственно из программы:

import sqlite3 as sq
 
with sq.connect("saper.db") as con:
    cur = con.cursor()
 
    cur.execute("SELECT * FROM users WHERE score > 100 ORDER BY score DESC LIMIT 5")
    result = cur.fetchall()
    print(result)

Здесь используется метод fetchall для получения результатов отбора SQL-запроса. В результате, result будет ссылаться на упорядоченный список, состоящий из кортежей с данными таблицы:

[('Сергей', 1, 33, 2000), ('Михаил', 1, 19, 1000), ('Юля', 2, 23, 700), ('Михаил', 1, 19, 500), ('Елена', 2, 17, 500)]

Или, перебрать последовательно, используя Cursor в качестве итерируемого объекта:

for result in cur:
    print(result)

Этот последний вариант более предпочтителен когда число выбираемых записей может быть велико. Тогда мы не формируем список, а последовательно выбираем из БД и тут же их обрабатываем. Такой подход существенно экономит память.

Есть еще два метода, которые выдают результат выборки из таблицы:

fetchmany(size) – возвращает число записей не более size;
fetchone() – возвращает первую запись.

В нашем случае их можно вызвать так:

result = cur.fetchone()
result2 = cur.fetchmany(2)
print(result)
print(result2)

В консоли увидим строки:

('Сергей', 1, 33, 2000)
[('Михаил', 1, 19, 1000), ('Юля', 2, 23, 700)]

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