Разибраемся с написанием простых запросов
Сравниваем данные на сайте с данными в БД
Давайте теперь попробуем найти связь между расписанием на сайте https://www.istu.edu/schedule/ и данным в таблице.
Откроем страничку https://www.istu.edu/schedule/ и увидим там список факультетов.
теперь давайте откроем табличку faculties в базе данных, если присмотреться, то список факультетов — это те самые значения, которые мы видим в столбце title
давайте попробуем теперь кликнуть на любой институт, например, на “Институт архитектуры, строительства и дизайна”
и взглянем на то что мы увидели в адресной строке:
сопоставим это со значением в столбце id у строки где в title “Институт архитектуры, строительства и дизайна”,
то есть в адресной строке находится ровно тот номер который указан в этом поле id.
Если глянуть страничку дальше, то на сайте мы увидим список групп. Каким же образом сайт выводит список групп?
Давайте откроем таблицу groups и глянем что в ней есть
давайте попробуем найти какую-нибудь группу из списка, который виден на сайте в нашей табличке:
попробуем другую группу:
Этот номер называется уникальным идентификатором института. Скоро мы увидим, что у любой записи есть некий уникальный идентификатор, который уникально идентифицирует запись в таблице.
В принципе, получается, что мы можем отфильтровать по этому номеру, чтобы получить список групп данного факультета.
но если использовать фильтры, которые дает нам ввести DB Browser, то такой список как правило будет содержать много лишних данных. Что если нам, например, просто хочется получить список групп?
И тут на помощь приходят SQL-запросы.
SQL-запрос — это некая специальная последовательность ключевых слов, названий таблиц и столбцов, которая позволяет попросить вернуть некий конкретный набор данных.
Эти самые запросы пишутся на специальном языке программирования, который называется SQL. Он в разы проще того же питона, и мы уже скоро научимся писать вполне себе солидные запросы к данным в базе.
Так как наш основной инструмент для программирования это Visual Studio Code, давайте создадим Visual Studio Code проект и поставим себе специальный плагин для работы с базами данных.
Создаем проект для запросов
Открываем папку через Visual Studio Code
и ставим плагин
вернемся обратно к списку файлов (которых пока у нас нет):
и подключимся теперь к базе данных. Для удобство предлагаю положить ее в папку с проектом:
и так, чтобы подключится тыкаем на базу правой кнопкой мыши и выбираем Open Database
внизу появится вкладка, которую можно раскрыть и увидеть содержимое базы
в принципе тот же DB Browser только чуть менее функциональный.
Пробуем писать запросы
Давайте теперь попробуем написать запрос. Тыкаем правой кнопкой на файл базы и выбираем New Query
появится новый файл вверху у которого будет написано
-- SQLite
тыкаем Ctrl+S и сохраняем его под именем query01.sql
вот он у нас появился
теперь можно писать в него запросы.
Самый простой запрос имеет вид
SELECT *
FROM название_таблицы
это запрос просит у базы данных выдать все строки таблицы с именем название таблицы.
Попробуем вытащить все строки из таблицы groups. Пишем запрос
SELECT *
FROM groups
и жмем Ctrl+Shift+Q, появится окно которое предложит нам выбрать базу, выбираем наш файлик
и запрос проходит
если по какой-то причине вы не видите результат в окне справа, то скорее всего он вывел его в отдельное окно SQLite,
просто закройте его
и снова нажмите Ctrl+Shift+Q. Теперь точно должен сработать.
И так мы видим, что нам вернулось из базы список строк таблицы groups
давайте теперь посмотрим, что позволяют нам делать запросы.
Примеры простых запросов
Указание какие столбцы выводить
Часто в табличках есть куча данных, которые нам не хотелось бы видеть. Например, какой нам смысл от поля id и от поля faculty_id. Если глянуть что выводится на сайте, то увидим, что там выводится только курс и название группы.
мы тоже можем явно указать какие столбцы мы хотим увидеть. Для этого надо запрос переписать следующим образом
SELECT title, kurs
FROM groups;
то есть получим теперь только нужные нам данные, названия тех столбцов, которые мы не написали он не выводит.
Мы можем менять порядок
SELECT kurs, title
FROM groups;
и даже дублировать столбцы
SELECT kurs, title, kurs
FROM groups;
правда не понятно зачем… но мало ли =О
Фильтрация по одному курсу
Указывать конкретные столбцы это не ахти какая полезная штука. Намного полезнее иметь возможность убрать лишние данные и оставить только нужные.
Например, запросы, которые мы писали выше выводят все строки их таблицы groups
SELECT *
FROM groups
а что если мы хотим получить только группы, которые учатся на первом курсе.
Тут нам пригодится новое ключевое слово WHERE. Например, если мы хотим оставить только группы первого курса нам надо взять те записи у которых в поле kurs стоит значение 1. Пишется это вот так:
SELECT *
FROM groups
WHERE kurs = 1
получим такое
кстати мы можем явно указать какие столбцы нам нужны. Скажем так, соединить прошлый вид запроса с новым. Например, чтобы просто получить список названий групп 1-го курса
SELECT title
FROM groups
WHERE kurs = 1
и хотя столбца kurs теперь не видно, внутри у себя он оставил там только те строки у которых в поле kurs стояло значение 1
Фильтрация по нескольким курсам
Фильтровать по одному курсу это конечно здорово, но что если мы хотим вывести группы 1-го и 4-го курсов. Как это написать?
Тут подход похож на тот, который мы применяли в python когда строили сложные условия. В SQL есть операторы AND, OR и IN
Что же значит, строки с группами 1-го и 4-го курсов?
Это значит, что в столбце kurs должно быть или 1, или 4.
По-английски ИЛИ это OR. Таким образом запрос можно написать вот так:
SELECT *
FROM groups
WHERE kurs = 1 OR kurs = 4
а что если нам захочется добавить еще один курс? Например, 5-ый (у нас же есть специалитет есть в университете между прочим). Это можно написать так:
SELECT *
FROM groups
WHERE kurs = 1 OR kurs = 4 OR kurs = 5
действительно работает. Только запрос становится уже не так удобно читать.
Поэтому, для удобства, когда надо отфильтровать по нескольким значением, используется оператор IN.
Он работает почти так же как в питоне и позволяет переписать запрос сильно проще, вот так:
SELECT *
FROM groups
WHERE kurs in (1, 4, 5)
Фильтрация по институту
Мы уже выше пробовали фильтровать по институту внутри программы DB Browser, но там мы делали это прямо через интерфейс. Что с одной стороны, очевидно, удобнее чем писать запросы, но дальше когда мы начнем писать более хитрые запросы мы бы уперлись в ограничения возможностей интерфейса.
Поэтому научимся фильтровать по институту без интерфейса.
И так, сначала нам надо узнать уникальный идентификатор института. Запросим список всех институтов:
SELECT *
FROM faculties
найдите в списке тот, который вам самый любый, и возьмите его номер. Я возьму номер Института информационных технологий и анализа данных вот он:
чтобы не забыть его вы можете вставить его прямо в запрос:
теперь напишем запрос к таблице groups
SELECT *
FROM groups
и обратим внимание на этот столбец
в этом столбце указан уникальный номер института, к которому принадлежит группа. А значит, чтобы получить список групп конкретного института надо просто отфильтровать по этому номеру.
SELECT *
FROM groups
WHERE faculty_id = 683
Фильтрация по точному названию группы
Часто не хочется иметь дело со всякими цифрами, идентификаторами, а хочется просто взять и отфильтровать по имени. Взять и найти группу с конкретным названием. А как сделать?
Тут в принципе все достаточно банально. Мы так же берем и фильтруем по совпадению значения как и раньше, единственное надо название обернуть в ОДИНАРНЫЕ КАВЫЧКИ. Хочу я, например, АСУб-21-1, я пишу тогда
SELECT *
FROM groups
WHERE title = 'АСУб-21-1'
сработало, красота! =)
Фильтрация по частичному названию группы
Вообще, если так подумать запросить одну строку не очень-то и умно. Хотя на самом деле существует достаточно много практических ситуаций, когда это может потребоваться. Но мы столкнемся с ними как-нибудь потом.
Что если мы допустим захотим вывести список групп конкретной специальностей, не просто АСУб-21-1, а всех АСУб которые есть в университете?
Как такое написать?
Для этого есть специальный оператор LIKE. Вы передаете ему кусочек строки оформленный особым образом, и он выдает вам записи у которых значения в поле соответствовали этому кусочку.
Если написать вот так:
SELECT *
FROM groups
WHERE title LIKE 'АСУб-21-1'
в этом случае он сработает как простое равенство.
Попробуем написать кусочек названия, оставим только АСУб
SELECT *
FROM groups
WHERE title LIKE 'АСУб'
хм, чего-то ничего не выдал…
Дело в том, что когда вы фильтруете с помощью LIKE, и хотите частичного соответствия, надо указывать дополнительный символ %.
Работает он следующим образом:
- Если написать
LIKE 'АСУб%', это значит искать все строки который НАЧИНАЮТСЯ сАСУб - Если написать
LIKE '%АСУб', это значит искать все строки который ЗАКАНЧИВАЮТСЯ наАСУб - Если написать
LIKE '%АСУб%', это значит искать все строки который СОДЕРЖАТ ВНУТРИ, либо НАЧИНАЮТСЯ, либо ЗАКАНЧИВАЮТСЯ наАСУб
В большинстве случаев используется третий вариант, как самый универсальный, то есть вы просто дополнительно к одинарным кавычкам еще и оборачиваете строку в проценты. Получается такой запрос:
SELECT *
FROM groups
WHERE title LIKE '%АСУб%'
пробуем
видим, что тут вылези дополнительные записи, которые мы не хотели бы видеть
я то хотел чисто АСУб, а тут заочники вылезли. Их можно убрать путем применения негативных условий
Негативные условия
Вариант 1
Первый способ избавится от лишних записей добавить фильтр по институту. Заочники принадлежат Заочно-вечернему институту у которого номер 680, а стало быть можно написать запрос следующим образом
SELECT *
FROM groups
WHERE title LIKE '%АСУб%' AND faculty_id != 680
он действительно выдаст то что надо.
Вариант 2
Еще вариант, добавить условие, что название не должно содержать маленькую букву з, вот так:
SELECT *
FROM groups
WHERE title LIKE '%АСУб%' AND title NOT LIKE '%з%'
тоже сработает как надо.
В принципе для начала думаю хватит =) Можно идти пилить задания!)