{ "cells": [ { "cell_type": "markdown", "id": "9f1d852c", "metadata": {}, "source": [ "# 1.2 Выборка данных" ] }, { "cell_type": "markdown", "id": "6d9ebcfc", "metadata": {}, "source": [ "---" ] }, { "cell_type": "markdown", "id": "cf7887ef", "metadata": {}, "source": [ "## Инициализация БД" ] }, { "cell_type": "code", "execution_count": 30, "id": "df1514b0", "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/plain": [ "'Connected: user@stepik'" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "import sqlalchemy\n", "engine = sqlalchemy.create_engine('mysql://user:pass@localhost:3306/stepik')\n", "%sql mysql://user:pass@localhost:3306/stepik" ] }, { "cell_type": "markdown", "id": "717c73d1", "metadata": {}, "source": [ "## Заполнение таблицы из CSV файла" ] }, { "cell_type": "code", "execution_count": 31, "id": "4b367e3a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
book_idtitleauthorpriceamount
01Мастер и МаргаритаБулгаков М.А.670.993
12Белая гвардияБулгаков М.А.540.505
23ИдиотДостоевский Ф.М.460.0010
34Братья КарамазовыДостоевский Ф.М.799.012
45Стихотворения и поэмыЕсенин С.А.650.0015
\n", "
" ], "text/plain": [ " book_id title author price amount\n", "0 1 Мастер и Маргарита Булгаков М.А. 670.99 3\n", "1 2 Белая гвардия Булгаков М.А. 540.50 5\n", "2 3 Идиот Достоевский Ф.М. 460.00 10\n", "3 4 Братья Карамазовы Достоевский Ф.М. 799.01 2\n", "4 5 Стихотворения и поэмы Есенин С.А. 650.00 15" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Чтение файла в DataFrame\n", "import pandas as pd\n", "file = 'tables/book.csv'\n", "df = pd.read_csv(file)\n", "df" ] }, { "cell_type": "markdown", "id": "4ac0753a", "metadata": {}, "source": [ "Создание схемы таблицы:" ] }, { "cell_type": "code", "execution_count": 37, "id": "84c3ffc6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "0 rows affected.\n", "0 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", " DROP TABLE IF EXISTS book;\n", "CREATE TABLE IF NOT EXISTS book (\n", " book_id INT PRIMARY KEY AUTO_INCREMENT,\n", " title VARCHAR(50),\n", " author VARCHAR(30),\n", " price DECIMAL(8, 2), #\n", " amount INT\n", ");" ] }, { "cell_type": "code", "execution_count": 38, "id": "589c0198", "metadata": {}, "outputs": [], "source": [ "# Запись данных в таблицу из DataFrame\n", "types = {\n", " 'book_id': sqlalchemy.Integer(),\n", " 'price' : sqlalchemy.Numeric(precision=8, scale=2),\n", " 'amount' : sqlalchemy.Integer()\n", "}\n", "df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')" ] }, { "cell_type": "markdown", "id": "12012e67", "metadata": {}, "source": [ "***" ] }, { "cell_type": "markdown", "id": "0447ce94", "metadata": {}, "source": [ "# Упражнения" ] }, { "cell_type": "markdown", "id": "802cc8a6", "metadata": {}, "source": [ "### Выборка всех данных из таблицы" ] }, { "cell_type": "code", "execution_count": 42, "id": "fa98d2d1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
book_idtitleauthorpriceamount
1Мастер и МаргаритаБулгаков М.А.670.993
2Белая гвардияБулгаков М.А.540.505
3ИдиотДостоевский Ф.М.460.0010
4Братья КарамазовыДостоевский Ф.М.799.012
5Стихотворения и поэмыЕсенин С.А.650.0015
" ], "text/plain": [ "[(1, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n", " (2, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n", " (3, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n", " (4, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2),\n", " (5, 'Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15)]" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * \n", " FROM book;" ] }, { "cell_type": "markdown", "id": "706a99ce", "metadata": {}, "source": [ "### Выборка отдельных столбцов" ] }, { "cell_type": "code", "execution_count": 47, "id": "c1b66a7d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
authortitleprice
Булгаков М.А.Мастер и Маргарита670.99
Булгаков М.А.Белая гвардия540.50
Достоевский Ф.М.Идиот460.00
Достоевский Ф.М.Братья Карамазовы799.01
Есенин С.А.Стихотворения и поэмы650.00
" ], "text/plain": [ "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('670.99')),\n", " ('Булгаков М.А.', 'Белая гвардия', Decimal('540.50')),\n", " ('Достоевский Ф.М.', 'Идиот', Decimal('460.00')),\n", " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01')),\n", " ('Есенин С.А.', 'Стихотворения и поэмы', Decimal('650.00'))]" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT author, title, price \n", " FROM book;" ] }, { "cell_type": "markdown", "id": "b5a550d2", "metadata": {}, "source": [ "### Выборка новых столбцов и присвоение им псевдонимов `AS`" ] }, { "cell_type": "code", "execution_count": 46, "id": "f9b24d92", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
НазваниеАвтор
Мастер и МаргаритаБулгаков М.А.
Белая гвардияБулгаков М.А.
ИдиотДостоевский Ф.М.
Братья КарамазовыДостоевский Ф.М.
Стихотворения и поэмыЕсенин С.А.
" ], "text/plain": [ "[('Мастер и Маргарита', 'Булгаков М.А.'),\n", " ('Белая гвардия', 'Булгаков М.А.'),\n", " ('Идиот', 'Достоевский Ф.М.'),\n", " ('Братья Карамазовы', 'Достоевский Ф.М.'),\n", " ('Стихотворения и поэмы', 'Есенин С.А.')]" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title AS Название,\n", " author AS Автор \n", " FROM book;" ] }, { "cell_type": "markdown", "id": "fad5bc77", "metadata": {}, "source": [ "### Выборка данных с созданием вычисляемого столбца" ] }, { "cell_type": "code", "execution_count": 49, "id": "d8ffa1c3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleamountpack
Мастер и Маргарита34.95
Белая гвардия58.25
Идиот1016.50
Братья Карамазовы23.30
Стихотворения и поэмы1524.75
" ], "text/plain": [ "[('Мастер и Маргарита', 3, Decimal('4.95')),\n", " ('Белая гвардия', 5, Decimal('8.25')),\n", " ('Идиот', 10, Decimal('16.50')),\n", " ('Братья Карамазовы', 2, Decimal('3.30')),\n", " ('Стихотворения и поэмы', 15, Decimal('24.75'))]" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, amount, \n", " amount * 1.65 AS pack \n", " FROM book;" ] }, { "cell_type": "markdown", "id": "7a15ad90", "metadata": {}, "source": [ "### Выборка данных, вычисляемые столбцы, математические функции" ] }, { "cell_type": "markdown", "id": "7fb75fa5", "metadata": {}, "source": [ "В SQL реализовано множество [математических функций](https://docs.microsoft.com/ru-ru/sql/t-sql/functions/mathematical-functions-transact-sql?view=sql-server-ver15) для работы с числовыми данными. В таблице приведены некоторые из них." ] }, { "cell_type": "markdown", "id": "df744aa9", "metadata": {}, "source": [ "| Функция | Описание | Пример |\n", "|---|---|---|\n", "| `CEILING(x)` | Возвращает наименьшее целое число, большее или равное X
(округляет до целого числа в большую сторону) | `CEILING(4.2)=5
CEILING(-5.8)=-5` |\n", "| `ROUND(x, k)` | Округляет значение X до K знаков после запятой,
если K не указано – X округляется до целого | `ROUND(4.361)=4
ROUND(5.86592,1)=5.9` |\n", "| `FLOOR(x)` | Возвращает наибольшее целое число, меньшее или равное X
(округляет до целого числа в меньшую сторону) | `FLOOR(4.2)=4
FLOOR(-5.8)=-6` |\n", "| `POWER(x, y)` | Возведение X в степень Y | `POWER(3,4)=81.0` |\n", "| `SQRT(x)` | Квадратный корень из X | `SQRT(4)=2.0
SQRT(2)=1.41...` |\n", "| `DEGREES(x)` | Конвертирует значение X из радиан в градусы | `DEGREES(3) = 171.8...` |\n", "| `RADIANS(x)` | Конвертирует значение X из градусов в радианы | `RADIANS(180)=3.14...` |\n", "| `ABS(x)` | Модуль числа X | `ABS(-1) = 1
ABS(1) = 1` |\n", "| `PI()` | PI = 3.1415926... | |" ] }, { "cell_type": "markdown", "id": "087e35ab", "metadata": {}, "source": [ "**Пример** \n", "Для каждой книги из таблицы book вычислим НДС (имя столбца _tax_) , который включен в цену и составляет _k_ = 18%, а также цену книги (_price_tax_) без него. \n", "Формула НДС:" ] }, { "cell_type": "markdown", "id": "f499d839", "metadata": {}, "source": [ "$$ tax = \\frac{price*\\frac{k}{100}}{1+\\frac{k}{100}} $$" ] }, { "cell_type": "markdown", "id": "6604db92", "metadata": {}, "source": [ "Формула ниже отвечает на вопрос _\"Какую сумму увеличили на 18%, чтобы получить текущее значение\"_" ] }, { "cell_type": "markdown", "id": "84cf04d3", "metadata": {}, "source": [ "$$ price\\_tax = \\frac{price}{1+\\frac{k}{100}} $$" ] }, { "cell_type": "code", "execution_count": 31, "id": "2ba83a05", "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titlepricetaxprice_tax
Мастер и Маргарита670.99102.35568.64
Белая гвардия540.5082.45458.05
Идиот460.0070.17389.83
Братья Карамазовы799.01121.88677.13
Стихотворения и поэмы650.0099.15550.85
" ], "text/plain": [ "[('Мастер и Маргарита', Decimal('670.99'), Decimal('102.35'), Decimal('568.64')),\n", " ('Белая гвардия', Decimal('540.50'), Decimal('82.45'), Decimal('458.05')),\n", " ('Идиот', Decimal('460.00'), Decimal('70.17'), Decimal('389.83')),\n", " ('Братья Карамазовы', Decimal('799.01'), Decimal('121.88'), Decimal('677.13')),\n", " ('Стихотворения и поэмы', Decimal('650.00'), Decimal('99.15'), Decimal('550.85'))]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "-- Вычисление НДС\n", "SELECT title, price, \n", " ROUND((price*18/100)/(1+18/100),2) AS tax, \n", " ROUND(price/(1+18/100),2) AS price_tax \n", " FROM book;" ] }, { "cell_type": "markdown", "id": "ce131c38", "metadata": {}, "source": [ "**Задание** \n", "В конце года цену всех книг на складе пересчитывают – снижают ее на 30%. \n", "Написать SQL запрос, который из таблицы book выбирает названия, авторов, количества и вычисляет новые цены книг. \n", "Столбец с новой ценой назвать new_price, цену округлить до 2-х знаков после запятой." ] }, { "cell_type": "code", "execution_count": 57, "id": "8d28b92c", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthoramountnew_price
Мастер и МаргаритаБулгаков М.А.3469.69
Белая гвардияБулгаков М.А.5378.35
ИдиотДостоевский Ф.М.10322.00
Братья КарамазовыДостоевский Ф.М.2559.31
Стихотворения и поэмыЕсенин С.А.15455.00
" ], "text/plain": [ "[('Мастер и Маргарита', 'Булгаков М.А.', 3, Decimal('469.69')),\n", " ('Белая гвардия', 'Булгаков М.А.', 5, Decimal('378.35')),\n", " ('Идиот', 'Достоевский Ф.М.', 10, Decimal('322.00')),\n", " ('Братья Карамазовы', 'Достоевский Ф.М.', 2, Decimal('559.31')),\n", " ('Стихотворения и поэмы', 'Есенин С.А.', 15, Decimal('455.00'))]" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author, amount,\n", " ROUND(price * 0.7, 2) AS new_price\n", " FROM book;" ] }, { "cell_type": "markdown", "id": "43319ba3", "metadata": {}, "source": [ "### Выборка данных, вычисляемые столбцы, логические функции `IF()`" ] }, { "cell_type": "markdown", "id": "85d749da", "metadata": {}, "source": [ "**Пример** \n", "Для каждой книги из таблицы `book` установим скидку следующим образом: \n", "если количество книг меньше 4, то скидка будет составлять 50% от цены, в противном случае 30%." ] }, { "cell_type": "code", "execution_count": 35, "id": "e64554a7", "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleamountpricesale
Мастер и Маргарита3670.99335.50
Белая гвардия5540.50378.35
Идиот10460.00322.00
Братья Карамазовы2799.01399.51
Стихотворения и поэмы15650.00455.00
" ], "text/plain": [ "[('Мастер и Маргарита', 3, Decimal('670.99'), Decimal('335.50')),\n", " ('Белая гвардия', 5, Decimal('540.50'), Decimal('378.35')),\n", " ('Идиот', 10, Decimal('460.00'), Decimal('322.00')),\n", " ('Братья Карамазовы', 2, Decimal('799.01'), Decimal('399.51')),\n", " ('Стихотворения и поэмы', 15, Decimal('650.00'), Decimal('455.00'))]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, amount, price,\n", " ROUND(IF(amount < 4, price * 0.5, price * 0.7),2) AS sale\n", " FROM book;" ] }, { "cell_type": "markdown", "id": "f3797387", "metadata": {}, "source": [ "**Пример 2** \n", "Усложним вычисление скидки в зависимости от количества книг. \n", "Если количество книг меньше 4 – то скидка 50%, меньше 11 – 30%, в остальных случаях – 10%. \n", "И еще укажем какая именно скидка на каждую книгу." ] }, { "cell_type": "code", "execution_count": 48, "id": "deecc3eb", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleamountpricesaleВаша_скидка
Мастер и Маргарита3670.99335.5050%
Белая гвардия5540.50378.3530%
Идиот10460.00322.0030%
Братья Карамазовы2799.01399.5150%
Стихотворения и поэмы15650.00585.0010%
" ], "text/plain": [ "[('Мастер и Маргарита', 3, Decimal('670.99'), Decimal('335.50'), '50%'),\n", " ('Белая гвардия', 5, Decimal('540.50'), Decimal('378.35'), '30%'),\n", " ('Идиот', 10, Decimal('460.00'), Decimal('322.00'), '30%'),\n", " ('Братья Карамазовы', 2, Decimal('799.01'), Decimal('399.51'), '50%'),\n", " ('Стихотворения и поэмы', 15, Decimal('650.00'), Decimal('585.00'), '10%')]" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, amount, price,\n", " ROUND(IF(amount < 11, \n", " IF(amount < 4, \n", " price * 0.5, \n", " price * 0.7), \n", " price * 0.9),2\n", " ) AS sale,\n", " IF(amount < 11, \n", " IF(amount < 4, \n", " '50%',\n", " '30%'), \n", " '10%'\n", " ) AS Ваша_скидка\n", " FROM book;" ] }, { "cell_type": "markdown", "id": "970b2cde", "metadata": {}, "source": [ "**Задание** \n", "При анализе продаж книг выяснилось, что наибольшей популярностью пользуются книги Михаила Булгакова, на втором месте книги Сергея Есенина. \n", "Исходя из этого решили поднять цену книг Булгакова на 10%, а цену книг Есенина - на 5%. \n", "Написать запрос, куда включить автора, название книги и новую цену, последний столбец назвать new_price. \n", "Значение округлить до двух знаков после запятой." ] }, { "cell_type": "code", "execution_count": 57, "id": "ab9cfcd4", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
authortitlenew_price
Булгаков М.А.Мастер и Маргарита738.09
Булгаков М.А.Белая гвардия594.55
Достоевский Ф.М.Идиот460.00
Достоевский Ф.М.Братья Карамазовы799.01
Есенин С.А.Стихотворения и поэмы682.50
" ], "text/plain": [ "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('738.09')),\n", " ('Булгаков М.А.', 'Белая гвардия', Decimal('594.55')),\n", " ('Достоевский Ф.М.', 'Идиот', Decimal('460.00')),\n", " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01')),\n", " ('Есенин С.А.', 'Стихотворения и поэмы', Decimal('682.50'))]" ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT author, title,\n", " ROUND(\n", " IF(author = 'Булгаков М.А.', \n", " price * 1.1,\n", " IF(author = 'Есенин С.А.',\n", " price * 1.05,\n", " price)\n", " ),2\n", " ) AS new_price\n", " FROM book;" ] }, { "cell_type": "markdown", "id": "95b9d24c", "metadata": {}, "source": [ "### Выборка данных по условию `WHERE`" ] }, { "cell_type": "markdown", "id": "f6a5f9a4", "metadata": {}, "source": [ "**Пример** \n", "Вывести название и цену тех книг, цены которых меньше 600 рублей." ] }, { "cell_type": "code", "execution_count": 58, "id": "4850d8df", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleprice
Белая гвардия540.50
Идиот460.00
" ], "text/plain": [ "[('Белая гвардия', Decimal('540.50')), ('Идиот', Decimal('460.00'))]" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, price\n", " FROM book\n", " WHERE price < 600;" ] }, { "cell_type": "markdown", "id": "9700c2e8", "metadata": {}, "source": [ "**Пример 2** \n", "Вывести название, автора и стоимость (цена умножить на количество) тех книг, стоимость которых больше 4000 рублей" ] }, { "cell_type": "code", "execution_count": 62, "id": "e30f8c3f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthortotal
ИдиотДостоевский Ф.М.4600.00
Стихотворения и поэмыЕсенин С.А.9750.00
" ], "text/plain": [ "[('Идиот', 'Достоевский Ф.М.', Decimal('4600.00')),\n", " ('Стихотворения и поэмы', 'Есенин С.А.', Decimal('9750.00'))]" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author, price * amount AS total\n", " FROM book\n", " WHERE price * amount > 4000;" ] }, { "cell_type": "markdown", "id": "3be681f4", "metadata": {}, "source": [ "> **Пояснение** \n", "В логическом выражении после `WHERE` нельзя использовать названия столбцов, присвоенные им с помощью `AS`, \n", "так как при выполнении запроса сначала вычисляется логическое выражение для каждой строки исходной таблицы, \n", "выбираются строки, для которых оно истинно. А только после этого формируется \"шапка запроса\" – столбцы, включаемые в запрос." ] }, { "cell_type": "markdown", "id": "6e5c4e45", "metadata": {}, "source": [ "**Задание** \n", "Вывести автора, название и цены тех книг, количество которых меньше 10." ] }, { "cell_type": "code", "execution_count": 63, "id": "8db1b709", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
authortitleprice
Булгаков М.А.Мастер и Маргарита670.99
Булгаков М.А.Белая гвардия540.50
Достоевский Ф.М.Братья Карамазовы799.01
" ], "text/plain": [ "[('Булгаков М.А.', 'Мастер и Маргарита', Decimal('670.99')),\n", " ('Булгаков М.А.', 'Белая гвардия', Decimal('540.50')),\n", " ('Достоевский Ф.М.', 'Братья Карамазовы', Decimal('799.01'))]" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT author, title, price\n", " FROM book\n", " WHERE amount < 10;" ] }, { "cell_type": "markdown", "id": "63bc80b9", "metadata": {}, "source": [ "### Выборка данных, логические операции `NOT`, `AND`, `OR`" ] }, { "cell_type": "markdown", "id": "a884ab6b", "metadata": {}, "source": [ "**Задание** \n", "Вывести название, автора, цену и количество всех книг, цена которых меньше 500 или больше 600, а стоимость всех экземпляров этих книг больше или равна 5000." ] }, { "cell_type": "code", "execution_count": 65, "id": "b40ba82a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthorpriceamount
Стихотворения и поэмыЕсенин С.А.650.0015
" ], "text/plain": [ "[('Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15)]" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author, price, amount\n", " FROM book\n", " WHERE (price < 500 OR price > 600) \n", " AND amount * price >= 5000;" ] }, { "cell_type": "markdown", "id": "161aa52f", "metadata": {}, "source": [ "### Выборка данных, операторы `BETWEEN`, `IN`" ] }, { "cell_type": "markdown", "id": "f7917935", "metadata": {}, "source": [ "**Задание** \n", "Вывести название и авторов тех книг, цены которых принадлежат интервалу от 540.50 до 800 (включая границы), \n", "а количество или 2, или 3, или 5, или 7 ." ] }, { "cell_type": "code", "execution_count": 71, "id": "df674231", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "3 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthor
Мастер и МаргаритаБулгаков М.А.
Белая гвардияБулгаков М.А.
Братья КарамазовыДостоевский Ф.М.
" ], "text/plain": [ "[('Мастер и Маргарита', 'Булгаков М.А.'),\n", " ('Белая гвардия', 'Булгаков М.А.'),\n", " ('Братья Карамазовы', 'Достоевский Ф.М.')]" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author\n", " FROM book\n", " WHERE price BETWEEN 540.50 AND 800\n", " AND amount IN (2, 3, 5, 7);" ] }, { "cell_type": "markdown", "id": "8b186925", "metadata": {}, "source": [ "### Выборка данных с сортировкой `ORDER BY`" ] }, { "cell_type": "markdown", "id": "bbacee9e", "metadata": {}, "source": [ "Если указан второй столбец, сортировка осуществляется только для тех строк, у которых значения первого столбца одинаковы. \n", "По умолчанию `ORDER BY` выполняет сортировку по возрастанию. \n", "Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово `ASC` (по возрастанию) или `DESC` (по убыванию). " ] }, { "cell_type": "markdown", "id": "ebc5da57", "metadata": {}, "source": [ "Логический порядок операций для запроса SQL следующий:\n", "1. FROM\n", "1. WHERE\n", "1. SELECT\n", "1. ORDER BY\n", "\n", "Поскольку сортировка выполняется позже `SELECT`, для указания столбцов, по которым выполняется сортировка, \n", "**можно** использовать имена, присвоенные им после `SELECT`, а также порядковый номер столбца в перечислении." ] }, { "cell_type": "markdown", "id": "aae65641", "metadata": {}, "source": [ "**Пример** \n", "Вывести название, автора и цены книг. \n", "Информацию отсортировать по названиям книг в алфавитном порядке." ] }, { "cell_type": "code", "execution_count": 83, "id": "5e385cb3", "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthorprice
Белая гвардияБулгаков М.А.540.50
Братья КарамазовыДостоевский Ф.М.799.01
ИдиотДостоевский Ф.М.460.00
Мастер и МаргаритаБулгаков М.А.670.99
Стихотворения и поэмыЕсенин С.А.650.00
" ], "text/plain": [ "[('Белая гвардия', 'Булгаков М.А.', Decimal('540.50')),\n", " ('Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01')),\n", " ('Идиот', 'Достоевский Ф.М.', Decimal('460.00')),\n", " ('Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99')),\n", " ('Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'))]" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author, price\n", " FROM book\n", " ORDER BY title;" ] }, { "cell_type": "markdown", "id": "b4a5f6eb", "metadata": {}, "source": [ "Аналогичный результат получится при использовании запроса \n", "`ORDER BY 1;`" ] }, { "cell_type": "markdown", "id": "08b688ae", "metadata": {}, "source": [ "**Задание** \n", "Вывести автора и название книг, количество которых принадлежит интервалу от 2 до 14 (включая границы). \n", "Информацию отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту)." ] }, { "cell_type": "code", "execution_count": 87, "id": "98426879", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "4 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
authortitle
Достоевский Ф.М.Братья Карамазовы
Достоевский Ф.М.Идиот
Булгаков М.А.Белая гвардия
Булгаков М.А.Мастер и Маргарита
" ], "text/plain": [ "[('Достоевский Ф.М.', 'Братья Карамазовы'),\n", " ('Достоевский Ф.М.', 'Идиот'),\n", " ('Булгаков М.А.', 'Белая гвардия'),\n", " ('Булгаков М.А.', 'Мастер и Маргарита')]" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT author, title\n", " FROM book\n", " WHERE amount BETWEEN 2 AND 14\n", " ORDER BY author DESC, title;" ] }, { "cell_type": "markdown", "id": "cb0a5314", "metadata": {}, "source": [ "### Выборка данных, оператор` LIKE`" ] }, { "cell_type": "markdown", "id": "a24727e2", "metadata": {}, "source": [ "| Символ-шаблон | Описание | Пример |\n", "|---|---|---|\n", "| `%` | Любая строка,
содержащая ноль или более символов | `SELECT * FROM book WHERE author LIKE '%М.%'`
выполняет поиск и выдает все книги,
инициалы авторов которых содержат _М._ |\n", "| `_` (подчеркивание) | Любой одиночный символ | `SELECT * FROM book WHERE title LIKE 'Поэм_'`
выполняет поиск и выдает все книги,
названия которых либо _Поэма_, либо _Поэмы_ и пр. |" ] }, { "cell_type": "markdown", "id": "4c4d9fbe", "metadata": {}, "source": [ "**Пример 1** \n", "Вывести названия книг, начинающихся с буквы «Б». \n", "_Строчные и прописные буквы в строках эквивалентны._" ] }, { "cell_type": "code", "execution_count": 95, "id": "03699ade", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
Белая гвардия
Братья Карамазовы
" ], "text/plain": [ "[('Белая гвардия',), ('Братья Карамазовы',)]" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title\n", " FROM book\n", " WHERE title LIKE 'б%';" ] }, { "cell_type": "markdown", "id": "12182061", "metadata": {}, "source": [ "**Пример 2** \n", "Вывести название книг, состоящих ровно из 5 букв." ] }, { "cell_type": "code", "execution_count": 96, "id": "1f039b88", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
Идиот
" ], "text/plain": [ "[('Идиот',)]" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title\n", " FROM book\n", " WHERE title LIKE '_____';" ] }, { "cell_type": "markdown", "id": "34658679", "metadata": {}, "source": [ "**Пример 3** \n", "Вывести книги, название которых длиннее 5 символов:" ] }, { "cell_type": "code", "execution_count": 97, "id": "3e78f63c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "4 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
Мастер и Маргарита
Белая гвардия
Братья Карамазовы
Стихотворения и поэмы
" ], "text/plain": [ "[('Мастер и Маргарита',),\n", " ('Белая гвардия',),\n", " ('Братья Карамазовы',),\n", " ('Стихотворения и поэмы',)]" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title\n", " FROM book\n", " WHERE title LIKE '______%';" ] }, { "cell_type": "markdown", "id": "4a788a80", "metadata": {}, "source": [ "**Пример 4** \n", "Вывести названия книг, которые содержат букву \"и\" как отдельное слово, если считать, \n", "что слова в названии отделяются друг от друга пробелами и не содержат знаков препинания.\n", "\n", ">В качестве обязательного символа ( \"_\"), может быть и пробел, \n", "но, к сожалению, шаблоны для `LIKE` не позволяют исключить какой-то символ. \n", "Это можно сделать только с помощью регулярных выражений (будут рассмотрены в уроке 3.5)" ] }, { "cell_type": "code", "execution_count": 98, "id": "8f9b43e0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
Мастер и Маргарита
Стихотворения и поэмы
" ], "text/plain": [ "[('Мастер и Маргарита',), ('Стихотворения и поэмы',)]" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title\n", " FROM book\n", " WHERE title LIKE '_% и _%'\n", " OR title LIKE 'и _%'\n", " OR title LIKE '_% и'\n", " OR title LIKE 'и';" ] }, { "cell_type": "markdown", "id": "3199c7bb", "metadata": {}, "source": [ "**Пример 5** \n", "Вывести названия книг, которые состоят ровно из одного слова, \n", "если считать, что слова в названии отделяются друг от друга пробелами ." ] }, { "cell_type": "code", "execution_count": 99, "id": "992c6dc7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
title
Идиот
" ], "text/plain": [ "[('Идиот',)]" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title\n", " FROM book\n", " WHERE title NOT LIKE '% %';" ] }, { "cell_type": "markdown", "id": "abf32d28", "metadata": {}, "source": [ "### Задание \n", "*Для следующего задания необходимо добавить новые записи. (book_id: 6-10)*" ] }, { "cell_type": "markdown", "id": "df9da461", "metadata": {}, "source": [ "#### Создание дополнительных записей" ] }, { "cell_type": "code", "execution_count": 119, "id": "5029f17c", "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
book_idtitleauthorpriceamount
1Мастер и МаргаритаБулгаков М.А.670.993
2Белая гвардияБулгаков М.А.540.505
3ИдиотДостоевский Ф.М.460.0010
4Братья КарамазовыДостоевский Ф.М.799.012
5Стихотворения и поэмыЕсенин С.А.650.0015
6NoneИванов С.С.50.0010
7Дети полуночиРушди Салман950.005
8ЛирикаГумилев Н.С.460.0010
9ПоэмыБехтерев С.С.460.0010
10Капитанская дочкаПушкин А.С.520.507
" ], "text/plain": [ "[(1, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n", " (2, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n", " (3, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n", " (4, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2),\n", " (5, 'Стихотворения и поэмы', 'Есенин С.А.', Decimal('650.00'), 15),\n", " (6, None, 'Иванов С.С.', Decimal('50.00'), 10),\n", " (7, 'Дети полуночи', 'Рушди Салман', Decimal('950.00'), 5),\n", " (8, 'Лирика', 'Гумилев Н.С.', Decimal('460.00'), 10),\n", " (9, 'Поэмы', 'Бехтерев С.С.', Decimal('460.00'), 10),\n", " (10, 'Капитанская дочка', 'Пушкин А.С.', Decimal('520.50'), 7)]" ] }, "execution_count": 119, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Чтение файла в DataFrame\n", "import pandas as pd\n", "file = 'tables/book_extra.csv'\n", "df = pd.read_csv(file)\n", "\n", "# Запись данных в таблицу из DataFrame\n", "types = {\n", " 'book_id': sqlalchemy.Integer(),\n", " 'price' : sqlalchemy.Numeric(precision=8, scale=2),\n", " 'amount' : sqlalchemy.Integer()\n", "}\n", "df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')\n", "\n", "%sql SELECT * FROM book;" ] }, { "cell_type": "markdown", "id": "917708ca", "metadata": {}, "source": [ "---" ] }, { "cell_type": "markdown", "id": "8271e668", "metadata": {}, "source": [ "Вывести название и автора тех книг, название которых состоит из двух и более слов, а инициалы автора содержат букву «С». \n", "Считать, что в названии слова отделяются друг от друга пробелами и не содержат знаков препинания, \n", "между фамилией автора и инициалами обязателен пробел, \n", "инициалы записываются без пробела в формате: буква, точка, буква, точка. \n", "Информацию отсортировать по названию книги в алфавитном порядке." ] }, { "cell_type": "code", "execution_count": 120, "id": "442071f3", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titleauthor
Капитанская дочкаПушкин А.С.
Стихотворения и поэмыЕсенин С.А.
" ], "text/plain": [ "[('Капитанская дочка', 'Пушкин А.С.'),\n", " ('Стихотворения и поэмы', 'Есенин С.А.')]" ] }, "execution_count": 120, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT title, author\n", " FROM book\n", " WHERE title LIKE '_% _%'\n", " AND author LIKE '_% %С.%'\n", " ORDER BY title;" ] }, { "cell_type": "markdown", "id": "d2cc4788", "metadata": {}, "source": [ "#### Удаление дополнительных записей" ] }, { "cell_type": "code", "execution_count": 123, "id": "97c45c04", "metadata": { "scrolled": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * mysql://user:***@localhost:3306/stepik\n", "0 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 123, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "DELETE FROM book\n", " WHERE book_id >= 6;" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.12" } }, "nbformat": 4, "nbformat_minor": 5 }