{
"cells": [
{
"cell_type": "markdown",
"id": "9f1d852c",
"metadata": {},
"source": [
"# 1.1 Отношение (таблица)"
]
},
{
"cell_type": "markdown",
"id": "6d9ebcfc",
"metadata": {},
"source": [
"---"
]
},
{
"cell_type": "markdown",
"id": "cf7887ef",
"metadata": {},
"source": [
"## Инициализация БД"
]
},
{
"cell_type": "markdown",
"id": "344f4082",
"metadata": {},
"source": [
"[Инструкция](https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259) \n",
"[Пример](https://github.com/IsFilimonov/LearningPath/blob/main/2021/Stepik_Interactive-SQL-Simulator/Chapter_1a.ipynb)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "df1514b0",
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: user@stepik'"
]
},
"execution_count": 3,
"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": "code",
"execution_count": 4,
"id": "acf76c03",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * mysql://user:***@localhost:3306/stepik\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | version() | \n",
"
\n",
" \n",
" \n",
" \n",
" | 8.0.27 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
"[('8.0.27',)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT version();"
]
},
{
"cell_type": "markdown",
"id": "8233b777",
"metadata": {},
"source": [
"## Создание базы данных"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8cc0e913",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"%%sql\n",
"\n",
"CREATE DATABASE IF NOT EXISTS stepik;"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "dcc37ee6",
"metadata": {
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * mysql://user:***@localhost:3306/stepik\n",
"7 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | Database | \n",
"
\n",
" \n",
" \n",
" \n",
" | information_schema | \n",
"
\n",
" \n",
" | mysql | \n",
"
\n",
" \n",
" | performance_schema | \n",
"
\n",
" \n",
" | sakila | \n",
"
\n",
" \n",
" | stepik | \n",
"
\n",
" \n",
" | sys | \n",
"
\n",
" \n",
" | world | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
"[('information_schema',),\n",
" ('mysql',),\n",
" ('performance_schema',),\n",
" ('sakila',),\n",
" ('stepik',),\n",
" ('sys',),\n",
" ('world',)]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SHOW DATABASES;"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f5e9af90",
"metadata": {},
"outputs": [],
"source": [
"sqlalchemy.create_engine('mysql://user:pass@localhost:3306/stepik')\n",
"%sql mysql://user:pass@localhost:3306/stepik"
]
},
{
"cell_type": "markdown",
"id": "8b3a957d",
"metadata": {},
"source": [
"## Создание таблицы"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "56808414",
"metadata": {
"scrolled": false
},
"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": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\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",
");\n",
"\n",
"# DECIMAL(8, 2) \n",
"# Десятичное число с 8 цифрами с обеих сторон от запятой и 2 цифрами после неё"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "44172371",
"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": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"CREATE TABLE IF NOT EXISTS genre(\n",
" genre_id INT PRIMARY KEY AUTO_INCREMENT, \n",
" name_genre VARCHAR(30)\n",
");"
]
},
{
"cell_type": "markdown",
"id": "d570306c",
"metadata": {},
"source": [
"## Вставка записи в таблицу"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "dba23503",
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"%%sql\n",
"INSERT book (title, author, price, amount)\n",
"VALUES ('TEST2', 'authDr', 143.535, 4);"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7a764cec",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"%%sql\n",
"INSERT genre (name_genre)\n",
"VALUES ('Роман');"
]
},
{
"cell_type": "markdown",
"id": "60d24ee9",
"metadata": {},
"source": [
"Количество полей должно совпадать. \n",
"Код ниже выдаст ошибку:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "87824aab",
"metadata": {
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * mysql://user:***@localhost:3306/stepik\n",
"(MySQLdb._exceptions.OperationalError) (1136, \"Column count doesn't match value count at row 1\")\n",
"[SQL: INSERT book (title, author, price, amount)\n",
" VALUES ('TEST2', 'authDr', 143.535);]\n",
"(Background on this error at: https://sqlalche.me/e/14/e3q8)\n"
]
}
],
"source": [
"%%sql\n",
"INSERT book (title, author, price, amount)\n",
" VALUES ('TEST2', 'authDr', 143.535);\n",
"\n",
"%%sql\n",
"INSERT book (title, author, price)\n",
" VALUES ('TEST2', 'authDr', 143.535, 4);"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "e5308c3f",
"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",
" | book_id | \n",
" title | \n",
" author | \n",
" price | \n",
" amount | \n",
"
\n",
" \n",
" \n",
" \n",
" | 1 | \n",
" Мастер и Маргарита | \n",
" Булгаков М.А. | \n",
" 670.99 | \n",
" 3 | \n",
"
\n",
" \n",
" | 2 | \n",
" Белая гвардия | \n",
" Булгаков М.А. | \n",
" 540.50 | \n",
" 5 | \n",
"
\n",
" \n",
" | 3 | \n",
" Идиот | \n",
" Достоевский Ф.М. | \n",
" 460.00 | \n",
" 10 | \n",
"
\n",
" \n",
" | 4 | \n",
" Братья Карамазовы | \n",
" Достоевский Ф.М. | \n",
" 799.01 | \n",
" 2 | \n",
"
\n",
" \n",
" | 5 | \n",
" Стихотворения и поэмы | \n",
" Есенин С.А. | \n",
" 650.00 | \n",
" 15 | \n",
"
\n",
" \n",
"
"
],
"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": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM book"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "16eaf668",
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"%%sql\n",
"SELECT * FROM genre"
]
},
{
"cell_type": "markdown",
"id": "8257a61d",
"metadata": {},
"source": [
"### Задание"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f0a84523",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"%%sql\n",
"INSERT book (title, author, price, amount)\n",
"VALUES ('Мастер и Маргарита', 'Булгаков М.А.', 670.99, 3);\n",
"SELECT * FROM book"
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "5baf4eb3",
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * mysql://user:***@localhost:3306/stepik\n",
"3 rows affected.\n",
"12 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | book_id | \n",
" title | \n",
" author | \n",
" price | \n",
" amount | \n",
"
\n",
" \n",
" \n",
" \n",
" | 1 | \n",
" TEST | \n",
" authr | \n",
" 143.50 | \n",
" 2 | \n",
"
\n",
" \n",
" | 2 | \n",
" TEST2 | \n",
" authDr | \n",
" 143.53 | \n",
" 4 | \n",
"
\n",
" \n",
" | 3 | \n",
" TEST2 | \n",
" authDr | \n",
" 143.54 | \n",
" 4 | \n",
"
\n",
" \n",
" | 4 | \n",
" TEST2 | \n",
" authDr | \n",
" 143.54 | \n",
" 4 | \n",
"
\n",
" \n",
" | 5 | \n",
" Мастер и Маргарита | \n",
" Булгаков М.А. | \n",
" 670.99 | \n",
" 3 | \n",
"
\n",
" \n",
" | 6 | \n",
" Белая гвардия | \n",
" Булгаков М.А. | \n",
" 540.50 | \n",
" 5 | \n",
"
\n",
" \n",
" | 7 | \n",
" Белая гвардия | \n",
" Булгаков М.А. | \n",
" 540.50 | \n",
" 5 | \n",
"
\n",
" \n",
" | 8 | \n",
" Идиот | \n",
" Достоевский Ф.М. | \n",
" 460.00 | \n",
" 10 | \n",
"
\n",
" \n",
" | 9 | \n",
" Братья Карамазовы | \n",
" Достоевский Ф.М. | \n",
" 799.01 | \n",
" 2 | \n",
"
\n",
" \n",
" | 10 | \n",
" Белая гвардия | \n",
" Булгаков М.А. | \n",
" 540.50 | \n",
" 5 | \n",
"
\n",
" \n",
" | 11 | \n",
" Идиот | \n",
" Достоевский Ф.М. | \n",
" 460.00 | \n",
" 10 | \n",
"
\n",
" \n",
" | 12 | \n",
" Братья Карамазовы | \n",
" Достоевский Ф.М. | \n",
" 799.01 | \n",
" 2 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
"[(1, 'TEST', 'authr', Decimal('143.50'), 2),\n",
" (2, 'TEST2', 'authDr', Decimal('143.53'), 4),\n",
" (3, 'TEST2', 'authDr', Decimal('143.54'), 4),\n",
" (4, 'TEST2', 'authDr', Decimal('143.54'), 4),\n",
" (5, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n",
" (6, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
" (7, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
" (8, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n",
" (9, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2),\n",
" (10, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
" (11, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n",
" (12, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"INSERT INTO book (title, author, price, amount)\n",
" VALUES ('Белая гвардия', 'Булгаков М.А.', 540.50, 5),\n",
" ('Идиот', 'Достоевский Ф.М.', 460.00, 10),\n",
" ('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 2);\n",
"SELECT * FROM book"
]
},
{
"cell_type": "markdown",
"id": "7b475a1d",
"metadata": {},
"source": [
"## Удаление дубликатов"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "13fcec44",
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * mysql://user:***@localhost:3306/stepik\n",
"0 rows affected.\n",
"6 rows affected.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" | book_id | \n",
" title | \n",
" author | \n",
" price | \n",
" amount | \n",
"
\n",
" \n",
" \n",
" \n",
" | 1 | \n",
" TEST | \n",
" authr | \n",
" 143.50 | \n",
" 2 | \n",
"
\n",
" \n",
" | 2 | \n",
" TEST2 | \n",
" authDr | \n",
" 143.53 | \n",
" 4 | \n",
"
\n",
" \n",
" | 5 | \n",
" Мастер и Маргарита | \n",
" Булгаков М.А. | \n",
" 670.99 | \n",
" 3 | \n",
"
\n",
" \n",
" | 6 | \n",
" Белая гвардия | \n",
" Булгаков М.А. | \n",
" 540.50 | \n",
" 5 | \n",
"
\n",
" \n",
" | 8 | \n",
" Идиот | \n",
" Достоевский Ф.М. | \n",
" 460.00 | \n",
" 10 | \n",
"
\n",
" \n",
" | 9 | \n",
" Братья Карамазовы | \n",
" Достоевский Ф.М. | \n",
" 799.01 | \n",
" 2 | \n",
"
\n",
" \n",
"
"
],
"text/plain": [
"[(1, 'TEST', 'authr', Decimal('143.50'), 2),\n",
" (2, 'TEST2', 'authDr', Decimal('143.53'), 4),\n",
" (5, 'Мастер и Маргарита', 'Булгаков М.А.', Decimal('670.99'), 3),\n",
" (6, 'Белая гвардия', 'Булгаков М.А.', Decimal('540.50'), 5),\n",
" (8, 'Идиот', 'Достоевский Ф.М.', Decimal('460.00'), 10),\n",
" (9, 'Братья Карамазовы', 'Достоевский Ф.М.', Decimal('799.01'), 2)]"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DELETE b1 \n",
" FROM book AS b1,\n",
" book AS b2\n",
" WHERE b1.book_id > b2.book_id\n",
" AND b1.title = b2.title;\n",
"\n",
"SELECT * FROM book"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "4b367e3a",
"metadata": {},
"outputs": [],
"source": []
}
],
"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
}