1.2 Выборка данных¶

Инициализация БД¶

In [30]:

%load_ext sql
import sqlalchemy
engine = sqlalchemy.create_engine('mysql://user:pass@localhost:3306/stepik')
%sql mysql://user:pass@localhost:3306/stepik

Out[30]:

'Connected: user@stepik'

Заполнение таблицы из CSV файла¶

In [31]:

# Чтение файла в DataFrame
import pandas as pd
file = 'tables/book.csv'
df = pd.read_csv(file)
df

Out[31]:

	book_id	title	author	price	amount
0	1	Мастер и Маргарита	Булгаков М.А.	670.99	3
1	2	Белая гвардия	Булгаков М.А.	540.50	5
2	3	Идиот	Достоевский Ф.М.	460.00	10
3	4	Братья Карамазовы	Достоевский Ф.М.	799.01	2
4	5	Стихотворения и поэмы	Есенин С.А.	650.00	15

Создание схемы таблицы:

In [37]:

%%sql

  DROP TABLE IF EXISTS book;
CREATE TABLE IF NOT EXISTS book (
       book_id INT PRIMARY KEY AUTO_INCREMENT,
       title   VARCHAR(50),
       author  VARCHAR(30),
       price   DECIMAL(8, 2), #
       amount  INT
);

 * mysql://user:***@localhost:3306/stepik
0 rows affected.
0 rows affected.

Out[37]:

[]

In [38]:

# Запись данных в таблицу из DataFrame
types = {
    'book_id': sqlalchemy.Integer(),
    'price'  : sqlalchemy.Numeric(precision=8, scale=2),
    'amount' : sqlalchemy.Integer()
}
df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')

Упражнения¶

Выборка всех данных из таблицы¶

In [42]:

%%sql
SELECT * 
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[42]:

book_id	title	author	price	amount
1	Мастер и Маргарита	Булгаков М.А.	670.99	3
2	Белая гвардия	Булгаков М.А.	540.50	5
3	Идиот	Достоевский Ф.М.	460.00	10
4	Братья Карамазовы	Достоевский Ф.М.	799.01	2
5	Стихотворения и поэмы	Есенин С.А.	650.00	15

Выборка отдельных столбцов¶

In [47]:

%%sql
SELECT author, title, price 
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[47]:

author	title	price
Булгаков М.А.	Мастер и Маргарита	670.99
Булгаков М.А.	Белая гвардия	540.50
Достоевский Ф.М.	Идиот	460.00
Достоевский Ф.М.	Братья Карамазовы	799.01
Есенин С.А.	Стихотворения и поэмы	650.00

Выборка новых столбцов и присвоение им псевдонимов `AS`¶

In [46]:

%%sql
SELECT title  AS Название,
       author AS Автор 
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[46]:

Название	Автор
Мастер и Маргарита	Булгаков М.А.
Белая гвардия	Булгаков М.А.
Идиот	Достоевский Ф.М.
Братья Карамазовы	Достоевский Ф.М.
Стихотворения и поэмы	Есенин С.А.

Выборка данных с созданием вычисляемого столбца¶

In [49]:

%%sql
SELECT title, amount, 
       amount * 1.65 AS pack        
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[49]:

title	amount	pack
Мастер и Маргарита	3	4.95
Белая гвардия	5	8.25
Идиот	10	16.50
Братья Карамазовы	2	3.30
Стихотворения и поэмы	15	24.75

Выборка данных, вычисляемые столбцы, математические функции¶

В SQL реализовано множество математических функций для работы с числовыми данными. В таблице приведены некоторые из них.

Функция	Описание	Пример
`CEILING(x)`	Возвращает наименьшее целое число, большее или равное X (округляет до целого числа в большую сторону)	`CEILING(4.2)=5<br>CEILING(-5.8)=-5`
`ROUND(x, k)`	Округляет значение X до K знаков после запятой, если K не указано – X округляется до целого	`ROUND(4.361)=4<br>ROUND(5.86592,1)=5.9`
`FLOOR(x)`	Возвращает наибольшее целое число, меньшее или равное X (округляет до целого числа в меньшую сторону)	`FLOOR(4.2)=4<br>FLOOR(-5.8)=-6`
`POWER(x, y)`	Возведение X в степень Y	`POWER(3,4)=81.0`
`SQRT(x)`	Квадратный корень из X	`SQRT(4)=2.0<br>SQRT(2)=1.41...`
`DEGREES(x)`	Конвертирует значение X из радиан в градусы	`DEGREES(3) = 171.8...`
`RADIANS(x)`	Конвертирует значение X из градусов в радианы	`RADIANS(180)=3.14...`
`ABS(x)`	Модуль числа X	`ABS(-1) = 1<br>ABS(1) = 1`
`PI()`	PI = 3.1415926...

Пример
Для каждой книги из таблицы book вычислим НДС (имя столбца tax) , который включен в цену и составляет k = 18%, а также цену книги (price_tax) без него.
Формула НДС:

$$ tax = \frac{price*\frac{k}{100}}{1+\frac{k}{100}} $$

Формула ниже отвечает на вопрос "Какую сумму увеличили на 18%, чтобы получить текущее значение"

$$ price\_tax = \frac{price}{1+\frac{k}{100}} $$

In [31]:

%%sql
-- Вычисление НДС
SELECT title, price, 
       ROUND((price*18/100)/(1+18/100),2) AS tax, 
       ROUND(price/(1+18/100),2) AS price_tax 
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[31]:

title	price	tax	price_tax
Мастер и Маргарита	670.99	102.35	568.64
Белая гвардия	540.50	82.45	458.05
Идиот	460.00	70.17	389.83
Братья Карамазовы	799.01	121.88	677.13
Стихотворения и поэмы	650.00	99.15	550.85

Задание
В конце года цену всех книг на складе пересчитывают – снижают ее на 30%.
Написать SQL запрос, который из таблицы book выбирает названия, авторов, количества и вычисляет новые цены книг.
Столбец с новой ценой назвать new_price, цену округлить до 2-х знаков после запятой.

In [57]:

%%sql
SELECT title, author, amount,
       ROUND(price * 0.7, 2) AS new_price
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[57]:

title	author	amount	new_price
Мастер и Маргарита	Булгаков М.А.	3	469.69
Белая гвардия	Булгаков М.А.	5	378.35
Идиот	Достоевский Ф.М.	10	322.00
Братья Карамазовы	Достоевский Ф.М.	2	559.31
Стихотворения и поэмы	Есенин С.А.	15	455.00

Выборка данных, вычисляемые столбцы, логические функции `IF()`¶

Пример
Для каждой книги из таблицы book установим скидку следующим образом:
если количество книг меньше 4, то скидка будет составлять 50% от цены, в противном случае 30%.

In [35]:

%%sql
SELECT title, amount, price,
       ROUND(IF(amount < 4, price * 0.5, price * 0.7),2) AS sale
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[35]:

title	amount	price	sale
Мастер и Маргарита	3	670.99	335.50
Белая гвардия	5	540.50	378.35
Идиот	10	460.00	322.00
Братья Карамазовы	2	799.01	399.51
Стихотворения и поэмы	15	650.00	455.00

Пример 2
Усложним вычисление скидки в зависимости от количества книг.
Если количество книг меньше 4 – то скидка 50%, меньше 11 – 30%, в остальных случаях – 10%.
И еще укажем какая именно скидка на каждую книгу.

In [48]:

%%sql
SELECT title, amount, price,
       ROUND(IF(amount < 11, 
                IF(amount < 4, 
                   price * 0.5, 
                   price * 0.7), 
                price * 0.9),2
            ) AS sale,
       IF(amount < 11, 
          IF(amount < 4, 
             '50%',
             '30%'), 
          '10%'
         ) AS Ваша_скидка
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[48]:

title	amount	price	sale	Ваша_скидка
Мастер и Маргарита	3	670.99	335.50	50%
Белая гвардия	5	540.50	378.35	30%
Идиот	10	460.00	322.00	30%
Братья Карамазовы	2	799.01	399.51	50%
Стихотворения и поэмы	15	650.00	585.00	10%

Задание
При анализе продаж книг выяснилось, что наибольшей популярностью пользуются книги Михаила Булгакова, на втором месте книги Сергея Есенина.
Исходя из этого решили поднять цену книг Булгакова на 10%, а цену книг Есенина - на 5%.
Написать запрос, куда включить автора, название книги и новую цену, последний столбец назвать new_price.
Значение округлить до двух знаков после запятой.

In [57]:

%%sql
SELECT author, title,
       ROUND(
             IF(author = 'Булгаков М.А.', 
                price * 1.1,
                IF(author = 'Есенин С.А.',
                   price * 1.05,
                   price)
               ),2
            ) AS new_price
  FROM book;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[57]:

author	title	new_price
Булгаков М.А.	Мастер и Маргарита	738.09
Булгаков М.А.	Белая гвардия	594.55
Достоевский Ф.М.	Идиот	460.00
Достоевский Ф.М.	Братья Карамазовы	799.01
Есенин С.А.	Стихотворения и поэмы	682.50

Выборка данных по условию `WHERE`¶

Пример
Вывести название и цену тех книг, цены которых меньше 600 рублей.

In [58]:

%%sql
SELECT title, price
  FROM book
 WHERE price < 600;

 * mysql://user:***@localhost:3306/stepik
2 rows affected.

Out[58]:

title	price
Белая гвардия	540.50
Идиот	460.00

Пример 2
Вывести название, автора и стоимость (цена умножить на количество) тех книг, стоимость которых больше 4000 рублей

In [62]:

%%sql
SELECT title, author, price * amount AS total
  FROM book
 WHERE price * amount > 4000;

 * mysql://user:***@localhost:3306/stepik
2 rows affected.

Out[62]:

title	author	total
Идиот	Достоевский Ф.М.	4600.00
Стихотворения и поэмы	Есенин С.А.	9750.00

Пояснение

В логическом выражении после WHERE нельзя использовать названия столбцов, присвоенные им с помощью AS,
так как при выполнении запроса сначала вычисляется логическое выражение для каждой строки исходной таблицы,
выбираются строки, для которых оно истинно. А только после этого формируется "шапка запроса" – столбцы, включаемые в запрос.

Задание
Вывести автора, название и цены тех книг, количество которых меньше 10.

In [63]:

%%sql
SELECT author, title, price
  FROM book
 WHERE amount < 10;

 * mysql://user:***@localhost:3306/stepik
3 rows affected.

Out[63]:

author	title	price
Булгаков М.А.	Мастер и Маргарита	670.99
Булгаков М.А.	Белая гвардия	540.50
Достоевский Ф.М.	Братья Карамазовы	799.01

Выборка данных, логические операции `NOT`, `AND`, `OR`¶

Задание
Вывести название, автора, цену и количество всех книг, цена которых меньше 500 или больше 600, а стоимость всех экземпляров этих книг больше или равна 5000.

In [65]:

%%sql
SELECT title, author, price, amount
  FROM book
 WHERE (price < 500 OR price > 600) 
   AND amount * price >= 5000;

 * mysql://user:***@localhost:3306/stepik
1 rows affected.

Out[65]:

title	author	price	amount
Стихотворения и поэмы	Есенин С.А.	650.00	15

Выборка данных, операторы `BETWEEN`, `IN`¶

Задание
Вывести название и авторов тех книг, цены которых принадлежат интервалу от 540.50 до 800 (включая границы),
а количество или 2, или 3, или 5, или 7 .

In [71]:

%%sql
SELECT title, author
  FROM book
 WHERE price BETWEEN 540.50 AND 800
   AND amount IN (2, 3, 5, 7);

 * mysql://user:***@localhost:3306/stepik
3 rows affected.

Out[71]:

title	author
Мастер и Маргарита	Булгаков М.А.
Белая гвардия	Булгаков М.А.
Братья Карамазовы	Достоевский Ф.М.

Выборка данных с сортировкой `ORDER BY`¶

Если указан второй столбец, сортировка осуществляется только для тех строк, у которых значения первого столбца одинаковы.
По умолчанию ORDER BY выполняет сортировку по возрастанию.
Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово ASC (по возрастанию) или DESC (по убыванию).

Логический порядок операций для запроса SQL следующий:

FROM
WHERE
SELECT
ORDER BY

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

Пример
Вывести название, автора и цены книг.
Информацию отсортировать по названиям книг в алфавитном порядке.

In [83]:

%%sql
SELECT title, author, price
  FROM book
 ORDER BY title;

 * mysql://user:***@localhost:3306/stepik
5 rows affected.

Out[83]:

title	author	price
Белая гвардия	Булгаков М.А.	540.50
Братья Карамазовы	Достоевский Ф.М.	799.01
Идиот	Достоевский Ф.М.	460.00
Мастер и Маргарита	Булгаков М.А.	670.99
Стихотворения и поэмы	Есенин С.А.	650.00

Аналогичный результат получится при использовании запроса
ORDER BY 1;

Задание
Вывести автора и название книг, количество которых принадлежит интервалу от 2 до 14 (включая границы).
Информацию отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту).

In [87]:

%%sql
SELECT author, title
  FROM book
 WHERE amount BETWEEN 2 AND 14
 ORDER BY author DESC, title;

 * mysql://user:***@localhost:3306/stepik
4 rows affected.

Out[87]:

author	title
Достоевский Ф.М.	Братья Карамазовы
Достоевский Ф.М.	Идиот
Булгаков М.А.	Белая гвардия
Булгаков М.А.	Мастер и Маргарита

Выборка данных, оператор`LIKE`¶

Символ-шаблон	Описание	Пример
`%`	Любая строка, содержащая ноль или более символов	`SELECT * FROM book WHERE author LIKE '%М.%'` выполняет поиск и выдает все книги, инициалы авторов которых содержат М.
`_` (подчеркивание)	Любой одиночный символ	`SELECT * FROM book WHERE title LIKE 'Поэм_'` выполняет поиск и выдает все книги, названия которых либо Поэма, либо Поэмы и пр.

Пример 1
Вывести названия книг, начинающихся с буквы «Б».
Строчные и прописные буквы в строках эквивалентны.

In [95]:

%%sql
SELECT title
  FROM book
 WHERE title LIKE 'б%';

 * mysql://user:***@localhost:3306/stepik
2 rows affected.

Out[95]:

title
Белая гвардия
Братья Карамазовы

Пример 2
Вывести название книг, состоящих ровно из 5 букв.

In [96]:

%%sql
SELECT title
  FROM book
 WHERE title LIKE '_____';

 * mysql://user:***@localhost:3306/stepik
1 rows affected.

Out[96]:

title
Идиот

Пример 3
Вывести книги, название которых длиннее 5 символов:

In [97]:

%%sql
SELECT title
  FROM book
 WHERE title LIKE '______%';

 * mysql://user:***@localhost:3306/stepik
4 rows affected.

Out[97]:

title
Мастер и Маргарита
Белая гвардия
Братья Карамазовы
Стихотворения и поэмы

Пример 4
Вывести названия книг, которые содержат букву "и" как отдельное слово, если считать,
что слова в названии отделяются друг от друга пробелами и не содержат знаков препинания.

В качестве обязательного символа ( "_"), может быть и пробел,

но, к сожалению, шаблоны для LIKE не позволяют исключить какой-то символ.
Это можно сделать только с помощью регулярных выражений (будут рассмотрены в уроке 3.5)

In [98]:

%%sql
SELECT title
  FROM book
 WHERE title LIKE '_% и _%'
    OR title LIKE 'и _%'
    OR title LIKE '_% и'
    OR title LIKE 'и';

 * mysql://user:***@localhost:3306/stepik
2 rows affected.

Out[98]:

title
Мастер и Маргарита
Стихотворения и поэмы

Пример 5
Вывести названия книг, которые состоят ровно из одного слова,
если считать, что слова в названии отделяются друг от друга пробелами .

In [99]:

%%sql
SELECT title
  FROM book
 WHERE title NOT LIKE '% %';

 * mysql://user:***@localhost:3306/stepik
1 rows affected.

Out[99]:

title
Идиот

Задание¶

Для следующего задания необходимо добавить новые записи. (book_id: 6-10)

Создание дополнительных записей¶

In [119]:

# Чтение файла в DataFrame
import pandas as pd
file = 'tables/book_extra.csv'
df = pd.read_csv(file)

# Запись данных в таблицу из DataFrame
types = {
    'book_id': sqlalchemy.Integer(),
    'price'  : sqlalchemy.Numeric(precision=8, scale=2),
    'amount' : sqlalchemy.Integer()
}
df.to_sql('book', con=engine, index=False, if_exists='append', dtype=types, method='multi')

%sql SELECT * FROM book;

 * mysql://user:***@localhost:3306/stepik
10 rows affected.

Out[119]:

book_id	title	author	price	amount
1	Мастер и Маргарита	Булгаков М.А.	670.99	3
2	Белая гвардия	Булгаков М.А.	540.50	5
3	Идиот	Достоевский Ф.М.	460.00	10
4	Братья Карамазовы	Достоевский Ф.М.	799.01	2
5	Стихотворения и поэмы	Есенин С.А.	650.00	15
6	None	Иванов С.С.	50.00	10
7	Дети полуночи	Рушди Салман	950.00	5
8	Лирика	Гумилев Н.С.	460.00	10
9	Поэмы	Бехтерев С.С.	460.00	10
10	Капитанская дочка	Пушкин А.С.	520.50	7

Вывести название и автора тех книг, название которых состоит из двух и более слов, а инициалы автора содержат букву «С».
Считать, что в названии слова отделяются друг от друга пробелами и не содержат знаков препинания,
между фамилией автора и инициалами обязателен пробел,
инициалы записываются без пробела в формате: буква, точка, буква, точка.
Информацию отсортировать по названию книги в алфавитном порядке.

In [120]:

%%sql
SELECT title, author
  FROM book
 WHERE title  LIKE '_% _%'
   AND author LIKE '_% %С.%'
 ORDER BY title;

 * mysql://user:***@localhost:3306/stepik
2 rows affected.

Out[120]:

title	author
Капитанская дочка	Пушкин А.С.
Стихотворения и поэмы	Есенин С.А.

Удаление дополнительных записей¶

In [123]:

%%sql
DELETE FROM book
 WHERE book_id >= 6;

 * mysql://user:***@localhost:3306/stepik
0 rows affected.

Out[123]:

[]