Современная жизнь немыслима без web-сервисов, ежедневно приносящих дивиденды в самых разных областях человеческой деятельности. А значит, профессия web-разработчика еще долго будет оставаться высокооплачиваемой и востребованной на рынке IT-специалистов. Существует множество решений и инструментов на разных языках программирования, упрощающих и ускоряющих web-разработку. В рамках данного курса освещается построение web-приложений на языке Python. Курс читают разработчики, применяющие Python в проектах, которыми ежедневно используют миллионы людей. В данном курсе вы сможете приобрести как базовые знания о функционировании современного интернета в целом, так и практические навыки создания интернет-приложений на языке Python.
Сложные запросы
Loading...
En provenance du cours de Moscow Institute of Physics and Technology
Создание Web-сервисов на Python
48 notes
Moscow Institute of Physics and Technology
48 notes
Cours 3 sur 4 dans Specialization Программирование на Python
À partir de la leçon
Хранение данных. SQL / NoSQL
В этом модуле нас ждёт знакомство с системами управления базами данных. Мы узнаем, зачем и когда их лучше использовать, в чём отличие реляционных баз данных от нереляционных? После этого глубже погрузимся в MySQL, как пример реляционной базы данных, и познакомимся с Redis, как примером нереляционной базы данных.
Rencontrer les enseignants
Абрамов Тимур
Пестров Никита
Опрышко Александр
Партилов Алексей
[БЕЗ_ЗВУКА]
[БЕЗ_ЗВУКА] Всем привет!
В прошлом видео мы разобрали часть возможностей запроса
SELECT и научились отбирать данные по набору условий.
А сегодня мы научимся объединять несколько таблиц в запросе, группировать данные,
считать агрегатные функции по группам и даже объединять несколько запросов в один.
Давайте посмотрим на более расширенный синтаксис оператора SELECT.
Во-первых, я выделил здесь table_references,
потому что мы поговорим подробнее как раз о том случае, когда в table_references не
одна таблица, а несколько, и дописал сюда синтаксис GROUP BY и HAVING.
Давайте подробнее.
Итак, что такое table_references?
Это table_reference, и еще table_reference, и еще и так далее.
То есть их может быть несколько.
Что такое table_reference?
Это либо table_factor, либо join_table.
Сейчас разберем подробнее.
Соответственно, table_factor — это таблица, возможно, с alias ее,
или таблица, созданная под запросом, или несколько table_reference опять-таки.
Что такое join_table?
Это table_reference, после которой идет либо JOIN с другой таблицей,
причем здесь в скобочках указано INNER и CROSS,
в квадратных скобочках JOIN — это одно и то же.
То есть можно записать INNER JOIN,
CROSS JOIN, а можно написать просто JOIN, и это одно и то же.
Также может быть table_reference LEFT или RIGHT OUTER JOIN,
слово OUTER можно опускать, и бывает еще table_reference NATURAL JOIN.
Есть еще STRAIGHT_JOIN, в принципе, он похож на обычный JOIN,
единственное, что если обычный JOIN может читать таблицы не в том порядке,
в котором вы указали их в JOIN, то есть если вы указали «таблица A JOIN B»,
это не значит, что он сначала прочтет таблицу A и присоединит к ней таблицу B.
Может на самом деле оптимизатор решить по-другому.
STRAIGHT_JOIN работает так же, как обычный JOIN, но он действительно будет читать
таблицы слева направо, поэтому отдельно мы его не будем рассматривать.
Так. Что такое join_condition,
который у нас фигурирует здесь в JOIN?
Это либо ON и список каких-то условий,
очень похожих на where, либо USING и список колонок.
Рассмотрим подробнее позже.
Давайте посмотрим какие-то примеры.
Допустим, у нас есть наша таблица books, которую мы рассматривали раньше,
которую мы создавали.
В ней есть, как мы помним, книги, а publisher,
то есть издателя, издательства, мы вынесли в отдельную табличку.
Если нам надо просто получить все заголовки книг,
мы можем сделать SELECT title FROM books.
Но если мы захотим получить заголовок книги и издателя,
который ее издал, нам придется как раз соединять две таблицы.
И мы напишем SELECT b.title, p.name FROM books AS b, то есть переименовываем
таблицу books в b и выбираем из нее title, а из таблицы publisher мы выбираем name.
Но эти две таблицы надо соединить.
И для этого мы пишем INNER JOIN ON b.publisher_id = p.publisher_id,
то есть мы соединяем по колонке publisher.
Причем INNER JOIN соединяет эти строки,
и если у какой-то левой строки не найдется соответствия, то есть не будет вдруг
publisher_id такого в publisher, то эта строка не попадет в запрос вообще.
Если не найдется соответствия, строка не попадет.
Но у нас такого быть не может, потому что у нас publisher FOREIGN KEY,
и мы бы не смогли проставить в таблицу books такой publisher_id,
которого нет в publisher на самом деле.
В MySQL также JOIN, CROSS JOIN и INNER JOIN — это одно и то же.
Мы можем даже упростить этот запрос и написать SELECT b.title,
p.name FROM books b JOIN publisher p USING(publisher_id).
В USING можно перечислить названия колонок, по которым соединяете.
То есть на равенство, как в ON, а какой-то более упрощенный синтаксис.
Также мы можем написать SELECT b.title,
p.name FROM books b NATURAL JOIN publisher p.
Это то же самое, NATURAL JOIN — то же самое,
что JOIN с использованием USING, в котором перечислены все колонки,
которые одновременно есть и в левой, и в правой таблице.
Мы даже можем написать SELECT b.title, p.name FROM books b, publisher p,
здесь нет явного JOIN, WHERE b.publisher_id = p.publisher_id.
И это, на самом деле, будет то же самое.
Хотя здесь, как вы видите, нет JOIN.
Мы просто SELECT из двух таблиц, перечисляя их, а в условии WHERE
указываем способ соединения, можно даже и так.
То есть INNER JOIN и запятая между названиями таблиц семантически
эквивалентны, если нет join_condition, то уж точно.
Если не указывать способ соединения, а просто перечислить две таблицы,
то это будет декартово произведение.
То есть каждый ряд первой таблицы соединится со всеми рядами,
то есть продублируется по количество рядов второй,
потом второй ряд продублируется по количеству рядов во второй таблице.
То есть к каждому ряду первой таблицы будет присоединен каждый ряд второй
таблицы, и получится их произведение.
Внутри условия ON, по которому соединяются таблицы, если мы его указываем,
могут быть те же самые выражения, что внутри WHERE.
И, соответственно, WHERE и ON в некоторых случаях могут работать одинаково,
как мы выше видели, когда мы соединили две таблицы через WHERE.
Но разница в том, что ON указывает, как соединять таблицы,
а WHERE указывает, какие ряды оставить в результате запроса.
То есть On — это для условий соединения таблиц,
WHERE — это для фильтрации результата.
И не всегда можно ON поменять на WHERE.
Если, как я уже говорил, в INNER JOIN или просто JOIN без
указания LEFT нет соответствующего ряда в правой таблице, то есть в левой есть,
а ряда, который бы соответствовал условию ON или USING, нету,
то этот ряд вообще не попадает в выборку.
То есть ряд из левой таблицы в выборку не попадет.
А вот если мы соединяем таблицу через LEFT JOINT, то, если в правой таблице есть ряд,
а в левой по условию ON или USING ряда нет, то ряд из левой таблицы
попадет в выборку, а так как справа к нему нечего присоединить,
туда попадет просто строка null'ов по числу колонок в другой таблице.
Таким образом, этот факт можно даже использовать так,
чтобы отобрать ряды из одной таблицы, которым нет соответствий в другой таблице.
Мы можем написать вот такой запрос.
SELECT * из left_tbl, которая LEFT JOIN
с right_tbl по условию, где right_tbl.id IS NULL.
То есть мы соединяем две таблицы, и если у нас у какого-то ряда
присоединился ряд из null'ов, мы можем отфильтровать эти ряды, и таким образом
найти все ряды в левой таблицы, у которых нет соответствия в правой.
Используя USING, мы перечисляем колонки, которые должны быть в обеих таблицах,
и по их строгому равенству эти таблицы соединяем.
Если мы используем NATURAL JOIN, и, кстати, он может быть как NATURAL JOIN,
который INNER, так и NATURAL LEFT JOIN, например, это то же самое,
что использовать JOIN со словом USING, в котором перечислены все колонки,
которые есть одновременно и в левой, и в правой таблицах.
То есть если вдруг вам надо, такое стечение обстоятельств,
соединить таблицы по всем колонкам, которые одинаковы в них обеих,
то можно использовать NATURAL без условия ON или USING.
Так оно работает.
Есть также RIGHT JOIN, он работает как и LEFT, только таблицы меняются местами.
Но рекомендуют использовать LEFT JOIN только.
Дальше давайте поговорим о GROUP BY и о HAVING.
Например, смотрите: мы хотим получить из нашей таблицы книг,
поскольку в ней есть author_id, мы хотим получить id тех авторов,
у которых есть, например, более десяти книг.
То есть, поскольку в таблице у нас записаны книги, у каждой из них есть
автор, нам надо определить как раз, какие авторы написали больше десяти книг.
Смотрите: мы можем выбрать author_id, как id автора,
COUNT — это функция для подсчета строк,
и из таблицы книг, сгруппировав это по author_id.
То есть все книги, написанные одним автором, будут сгруппированы,
и посчитано и количество.
Дополнительно написав HAVING books, вот этот наш COUNT мы обзываем словом books,
HAVING books > 10, мы как раз и выберем тех авторов, у которых больше десяти книг.
Мы не можем здесь использовать WHERE, потому что GROUP BY,
который мы написали, он считается уже после выборки.
То есть сначала выбираются записи, они отфильтровываются с помощью WHERE.
Потом с помощью GROUP BY они группируются, считаются агрегатные функции,
COUNT — это агрегатная функция.
И WHERE уже выполнился раньше, он не может на
своем этапе применить, посчитать агрегатные функции, сравнить их значения.
Поэтому есть HAVING.
Он в принципе похож на WHERE, то есть там могут быть те же самые условия,
но он считается уже после GROUP BY.
Но вместе WHERE в целом его использовать не стоит, потому что WHERE,
он более оптимизирован, а HAVING, соответственно, менее оптимизирован,
он медленнее, но зато он работает с агрегатными функциями.
Поэтому мы можем так посчитать.
Давайте еще один пример.
Допустим, мы хотим отобрать трех авторов, у которых самый большой средний рейтинг.
Допустим, у нас в нашей таблице book не было поля rating, но мы представим,
что мы добавили еще к книге, каждой книге, поле rating,
которое, ну это какой-то читательский рейтинг книги, как нам сделать?
Точно так же мы можем выбрать author_id, сгруппировать по нему,
посчитать (есть агрегатная функция AVG — average) средний
рейтинг и отсортировать по этому полю, по этому рейтингу и взять первые три записи.
Вот и все.
То есть вот опять-таки пригодились нам агрегатные функции.
Вообще, какие есть агрегатные функции?
Их на самом деле много, но основные: AVG() — average, среднее значение,
COUNT() — подсчитывает количество рядов, которые были сжаты в одну при агрегации,
есть COUNT(DISTINCT), он считает количество разных рядов,
есть GROUP_CONCAT(), который позволяет какую-то колонку,
которая вот была в нескольких сжатых рядах,
в результате все эти значения поместить в одну строку через запятую.
То есть как бы все ряды сжимаются в один с помощью GROUP BY,
а все значения в какой-то колонке в этой нашей результирующей выборке записывают
друг за другом через запятую.
Также можно максимальное и минимальное значения внутри группы.
То есть, например, если бы мы хотели найти, какой был максимальный рейтинг хоть
какой-нибудь книги конкретного автора, то есть получить список автор — максимальный
рейтинг его книги, мы могли бы использовать функцию MAX().
Так же есть SUM(), который считает сумму всех колонок внутри группы.
Также, кстати, эти функции можно использовать без группировки,
то есть если мы напишем, например, SELECT SUM() какого-то поля по таблице —
он просто посчитает значения этой колонки по всей таблице, если нет группировки.
А если есть — то, значит, внутри группировки.
Также давайте рассмотрим запрос UNION,
который позволяет объединить несколько SELECT'ов в один.
То есть вот такой у него синтаксис: SELECT...
UNION (можно указать ALL или DISTINCT), еще один SELECT,
еще один UNION и так далее.
Единственное, что во всех этих SELECT'ах должно быть одинаковое количество колонок.
Поскольку формируется общая результирующая табличка из них, общий результат,
количество колонок в каждом SELECT'е должно быть одинаково.
В качестве имен колонок в результате будут взяты названия колонок из первого
SELECT'а.
То есть если во втором SELECT'е и в третьем у нас будут как-то по-другому эти
колонки названы, то значения из них попадут в табличку,
но в качестве названий колонок будут взяты колонки из первого SELECT'а.
И на одних и тех же позициях в каждом SELECT'е должны иметь одинаковый тип
данных данные, которые находятся.
Когда мы пишем union без какой-то спецификации, без all или без distinct,
то имеется в виду distinct, то есть по умолчанию union distinct.
Это означает, что одинаковые ряды будут удалены из выборки.
То есть если первый select и второй select,
которые объединили просто union'ом дадут какие-то ряды с одинаковыми значениями,
то в результирующей выборке будет не два ряда, а один.
Если вы пишете all, то соответственно дубликаты сохранятся.
При этом любой distinct, который встречается,
он отменяет все all'ы, находящиеся левее него.
Ну и соответственно, union без слова distinct — это то же самое,
что union с distinct.
То есть если вы не напишете union all в каком-то месте, то все union all,
которые были перед ним — не имеют смысла, они будут так же, как distinct.
Таким образом, давайте вот на примере.
Вы можете делать select 1 union all select 1 union all select 1.
То есть вы сделали три раза select, который выбирает просто цифру 1,
и по union'у их объединили.
Вы получите три ряда, в каждом из которых будет 1.
Если вы напишете select 1 union all select 1 union select 1,
то у вас будет всего один ряд.
Хотя первый, второй select написаны через union all,
третий union отменил это, и получился в итоге один ряд.
Что еще интересного про union?
Каждый select внутри union может иметь свой ORDER BY и LIMIT.
Но ORDER BY внутри select'ов, которые объединены по union'у,
не имеет значения, потому что union, на самом деле, вернет в своем порядке.
Но если вы просто укажете ORDER BY во вложенных select'ах в union'е,
то он будет даже выкинут оптимизатором и не будет вообще применяться.
Но если вы укажете ORDER BY с LIMIT'ом внутри вложенного select'а — это уже будет
иметь смысл.
То есть вы хотите упорядочить выбранный набор данных каждым select'ом по какой-то
колонке и отрезать от них кусок.
Это имеет смысл, вы так можете делать.
Также вы можете использовать ORDER BY и LIMIT для всего запроса,
который получен с помощью нескольких union'ов, то есть для всего результата.
При этом обязательно указывать круглые скобки внутри, то есть SELECT'ы,
которые объединяют через UNION нужно обрамлять круглыми скобками,
если вы используете там ORDER BY и LIMIT, иначе будет ошибка.
Если вы используете ORDER BY и LIMIT в самом конце для всего запроса,
как бы общей результирующей выборки, то круглые скобки в принципе не обязательны.
Вот и все.
Давайте подведем итоги.
Мы изучили сложные варианты использования select'а — это объединение данных
из нескольких таблиц с помощью внутреннего и внешнего JOIN'а,
то есть INNER JOIN, или просто JOIN, и внешне это LEFT или RIGHT JOIN.
Обычно мы используем LEFT.
Если мы хотим использовать RIGHT,
то мы можем просто поменять таблицы местами и использовать LEFT.
Соответственно, INNER JOIN исключает строки, для которых нет соответствия
в обеих таблицах, а LEFT JOIN присоединяет пустые строки, если нет соответствия.
Также мы изучили группировку и агрегатные функции и фильтрацию по
агрегатным функциям с помощью HAVING.
Также мы научились объединять результаты нескольких запросов в одном.
Но на самом деле мы кое-что и пропустили.
Во-первых, несколько таблиц можно использовать не только в SELECT'е,
но и на самом деле в UPDATE, и в DELETE,
причем часть этих таблиц может использоваться для условий фильтрации,
а часть — может непосредственно удаляться или обновляться.
Далее, мы пройдем тест по теме
SQL и потом бегло поговорим о новой SQL базе данных Redis.
[ЗВУК] [БЕЗ_ЗВУКА]
Explorer notre catalogue
Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.
Coursera propose un accès universel à la meilleure formation au monde,
en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.
© 2019 Coursera Inc. Tous droits réservés.
