📦 Репозиторий занятия 80

/* Позволяет создавать запрос к базе данных
SELECT
    * или (имена полей через запятую)
FROM
    имя таблицы (обычно имя_базы.имя_таблицы)
WHERE
    условие (условия)
; - точка с запятой, обязательный элемент завершения каждого запроса


 Синтаксис              |        Функционал
========================|===============================================================
=, <, >, <> (!=), <=, >=|    равен, меньше,больше,неравен,меньше или равно,больше или равно
                        |
                        |
    BETWEEN ... AND,    |    значение в диапазоне, включая границы
NOT BETWEEN ... AND     |    (или значения ВНЕ диапазона)
                        |    salary BETWEEN 2000 AND 3000
                        |
                        |
IN, NOT IN              |    значение в списке              id IN (10, 20, 30)
                        |    (или любые значения, кроме тех, что в списке)
                        |
                        |
IS NULL, IS NOT NULL    |    проверка наличия (или отсутствия) записей со значением NULL
                        |
                        |
LIKE, NOT LIKE          |    значение подходит по маске
                        |    % - множество символов, включая пустую строку
                        |    _ - строго один символ
                        |       name LIKE '%k%' - все имена с символом k или K в середине name
                        |
                        |    Если нужен фильтр с учётом регистра, используйте BINARY
                        |       BINARY name LIKE 'K%'  - вернёт имена, начинающиеся с заглавной K
                        |
                        |
AND, OR, NOT            |    логические И, ИЛИ, НЕ
                        |
()                      |    указание приоритета
========================|===============================================================


                 ТАБЛИЦА ПРИОРИТЕТОВ

 ===========|============================|===============================
  Приоритет |        Операция            |          Описание
 ===========|============================|===============================
      1	    |           NOT              |   Логическое отрицание
            |                            |
      2	    |   Арифметические операции  |   Выполнение математических
            |   (+, -, *, /, %)	         |     вычислений
            |                            |
      3	    |   Операторы сравнения      |   Сравнение значений
            |   (=, !=, <, >, <=, >=)	 |
            |                            |
      4	    |           AND              |   Логическое "и"
            |                            |
      5	    |           OR               |   Логическое "или"
            |                            |
      6	    |           XOR              |   Исключающее "или"
 ===========|============================|==================================
 */

/* 
Синтаксис формирования таблицы

CREATE TABLE <table_name> (
	<column1_name> <data_type>,
	<column2_name> <data_type>,
	<column3_name> <data_type>,
	...
    <columnN_name> <data_type>
);
 */

CREATE DATABASE IF NOT EXISTS 060326_ptm_ClassWork;

USE 060326_ptm_ClassWork;

CREATE TABLE IF NOT EXISTS Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    BirthDate DATE,
    HireDate DATE DEFAULT (CURRENT_DATE),
    Salary DECIMAL(9, 2) CHECK (Salary > 0),
    Email VARCHAR(100) UNIQUE,
    Position ENUM('manager', 'developer', 'designer') DEFAULT 'manager'
);

/*
INSERT INTO TableName (Column1, Column2, Column3, ...)
VALUES (Value1, Value2, Value3, ...);
*/

INSERT INTO Employees (FirstName, LastName, BirthDate,  Salary, Email)
VALUES
	('John', 'Smith', '2000-02-03', 5000, 'john.smith@asdasd.com'),
	('Jane', 'Smith', '2002-02-03', 5000, 'jane.smith@asdasd.com');

SELECT * FROM Employees;


/* Создать копию таблицы (сохранить результат выборки как таблицу) */
CREATE TABLE Employees_2 AS
SELECT * FROM Employees;

/* В MySQL и некоторых других БД - атрибут AUTO_INCREMENT, 
то есть поле, значение которого увеличивается базой данных автоматически 
при добавлении каждой новой записи в таблицу. */

USE 060326_ptm_ClassWork;

CREATE TABLE toys1 (
    toy_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    toy_name VARCHAR(100),
    weight INTEGER
);

SELECT * FROM toys1;

INSERT INTO toys1 
	(toy_name, weight) 
VALUES 
	("Велосипед", 10);


INSERT INTO toys1 
	(toy_name, weight) 
VALUES 
	("Самокат", 10),
    ("Скейтборд", 10);


-- =================================================================================

CREATE TABLE toys2 (
    toy_id INT PRIMARY KEY,
    toy_name VARCHAR(100),
    weight INTEGER
);

SELECT * FROM toys2;

INSERT INTO toys2
	(toy_id, toy_name, weight) 
VALUES 
	(NULL, "Велосипед", 10);

/*  Представление (view) - это виртуальная таблица, 
созданная на основе одной или нескольких таблиц в базе данных. 
Оно предоставляет удобный способ доступа к данным, а также может 
использоваться для упрощения выполнения сложных запросов. 

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


CREATE VIEW v_table AS
	SELECT
		<column1_name>,
		<column2_name>, 
		<column3_name>
	FROM 
		<table_name>
	WHERE
		<condition1> AND <condition2>;
*/

USE training_db;

CREATE VIEW v_table AS
    SELECT 
        *
    FROM
        toys1
    WHERE
        toy_name = 'Велосипед' OR weight > 0;
        
/* Проверям результат */         
SELECT * FROM v_table;

/* Меняем данные исзодной таблицы */         
UPDATE toys1 
SET 
    weight = 15
WHERE
    toy_name = 'Скутер' LIMIT 1;
    
/* Проверям результат изменений в исходной таблицы и затем во view*/        
SELECT * FROM toys1;

/*  Представление (view) - это виртуальная таблица, 
созданная на основе одной или нескольких таблиц в базе данных. 
Оно предоставляет удобный способ доступа к данным, а также может 
использоваться для упрощения выполнения сложных запросов. 

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


CREATE VIEW v_table AS
	SELECT
		<column1_name>,
		<column2_name>, 
		<column3_name>
	FROM 
		<table_name>
	WHERE
		<condition1> AND <condition2>;
*/

USE 060326_ptm_ClassWork;

CREATE VIEW v_table AS
    SELECT 
        *
    FROM
        toys1
    WHERE
        toy_name = 'Велосипед' OR weight > 2;
        
SELECT * FROM v_table;

/*    ИЗМЕНЕНИЕ ДАННЫХ
Для изменения данных таблицы можно использовать команду UPDATE,
которая позволяет изменить данные в какой-либо таблице.

(ВНИМАНИЕ: В Workbench по умолчанию стоит ограничение,
    которое не позволяет изменять данные с помощью
    "неограниченных" запросов (без WHERE и / или LIMIT)
)

UPDATE <table_name>
SET
    <column1_name> = <value1>,
    <column2_name> = <value2>
WHERE
    <condition1>' LIMIT N;
*/

USE 060326_ptm_ClassWork;

UPDATE toys1 
SET 
    weight = 15
WHERE
    weight > 0 LIMIT 100;

SELECT * FROM toys1;

UPDATE toys1 
SET 
    weight = 20 LIMIT 100;

/*MySQL ENUM — это специальный тип данных, который хранит одно значение из предопределенного списка строковых значений. Используется для хранения ограниченного набора возможных значений, что делает его удобным для статусов, категорий и других фиксированных вариантов.
Синтаксис:*/

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('active', 'inactive', 'banned') NOT NULL
);

/* Особенности ENUM:
    Хранение и производительность
        MySQL хранит ENUM как числовое значение (индекс в списке).
        ENUM('small', 'medium', 'large') хранится как 1, 2, 3 соответственно.
    Поиск быстрее, чем с VARCHAR, так как сравниваются числа, а не строки.
    Добавление новых значений требует ALTER TABLE, что может быть неудобно.
*/

/* Сравнение значений
        ENUM можно сравнивать как строки: */
SELECT * FROM users WHERE status = 'active';

/*      Или как числа (по индексу): */

SELECT * FROM users WHERE status = 1;  -- 'active'

/* Значение по умолчанию */

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending'
);


/* Когда использовать?

✅ Если значения фиксированы и изменяются редко (например, status, type).
❌ Если значения могут часто изменяться — лучше использовать отдельную таблицу и FOREIGN KEY.

Если значения могут часто изменяться, лучше использовать таблицу справочник:
 */

CREATE TABLE statuses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status_id INT NOT NULL,
    FOREIGN KEY (status_id) REFERENCES statuses(id)
);

-- Это позволит легко добавлять и изменять статусы без ALTER TABLE.

### DDL (Data Definition Language) — язык определения данных

**DDL** используется для **описания структуры** базы данных:  
создание, изменение и удаление таблиц, схем, индексов и т.д.

**DDL** управляет *“каркасом”* базы данных, а не её содержимым.

#### Основные команды DDL:

| Команда    | Назначение                                                   |
| ---------- | ------------------------------------------------------------ |
| `CREATE`   | Создать объект (таблицу, схему, индекс и т.д.)               |
| `ALTER`    | Изменить структуру объекта                                   |
| `DROP`     | Удалить объект                                               |
| `TRUNCATE` | Очистить таблицу (удалить все строки без возможности отката) |
| `RENAME`   | Переименовать объект                                         |

**Пример:**

```sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);
```

### DML (Data Manipulation Language) — язык манипулирования данными

**DML** используется для **работы с содержимым таблиц** — то есть для  
добавления, изменения, удаления и получения строк данных.

#### Основные команды DML:

| Команда  | Назначение                    |
| -------- | ----------------------------- |
| `SELECT` | Выбор данных                  |
| `INSERT` | Вставка новых строк           |
| `UPDATE` | Изменение существующих данных |
| `DELETE` | Удаление строк                |

**Пример:**

```sql
INSERT INTO employees (id, name, salary)
VALUES (1, 'Иван', 55000.00);
```

### Сравнение DDL и DML:

| Критерий       | DDL (Data Definition Language) | DML (Data Manipulation Language)      |
| -------------- |--------------------------------|---------------------------------------|
| Что делает     | Определяет структуру БД        | Управляет данными                     |
| Автокоммит     | Да (операции необратимы)       | Нет (можно откатить через `ROLLBACK`) |
| Примеры команд | CREATE, ALTER, DROP            | SELECT, INSERT, UPDATE, DELETE        |

DROP TABLE IF EXISTS tmp;

CREATE TABLE IF NOT EXISTS tmp (
	id INT PRIMARY KEY AUTO_INCREMENT,
    fixed_date DATETIME,
    flex_date TIMESTAMP
);

INSERT INTO tmp (fixed_date, flex_date)
VALUE (NOW(), NOW());

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS offset;
-- fixed_date ничем не отличается от flex_date
SELECT * FROM tmp;

-- меняем системное время в пределах сессии
SELECT @@session.time_zone;
SET time_zone = '+00:00';

-- fixed_date не изменилась, в отличии от flex_date
SELECT * FROM tmp;

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP()) AS offset;

HOST = ''
USER = ''
PASSWORD = '********'
DATABASE = 'sakila'

HOST_WRITE = ''
USER_WRITE = ''
PASSWORD_WRITE = '********'

# ВНИМАНИЕ!!! Заменить на СВОЮ БД!!!
DATABASE_WRITE = ''

dbconfig = {
'host': HOST,
'user': USER,
'password': PASSWORD,
'database': DATABASE,
}

dbconfig_write = {
'host': HOST_WRITE,
'user': USER_WRITE,
'password': PASSWORD_WRITE,
'database': DATABASE_WRITE,
}

# Установка DBeaver

[https://dbeaver.io/download/](https://dbeaver.io/download/)

***

## Windows 

Скачайте `.exe` файл

***

## macOS

Скачайте `.dmg` файл

***

## Ubuntu (Установка через .deb - рекомендуется)

### 1. Скачайте `.deb`-пакет с официального сайта
```
wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
```
### 2. Установите .deb-пакет
```
sudo apt install ./dbeaver-ce_latest_amd64.deb
```
### 3. Запустите DBeaver:
```
dbeaver
```

## Полезные DBeaver
### 1. Изменить настройки цвета фона и стиля:
```
Window -> Preferences -> User Interface -> Appearance -> Theme
```

### 2. Изменить размер шрифта
- шрифт интерфейса
```
Window -> Preferences -> User Interface -> Appearance -> Colors and Fonts -> DBeaver Fonts -> Main font.
```
- шрифт SQL редактора
```
Window > Preferences > User Interface > Appearance > Colors and Fonts > DBeaver Fonts > Monospace font.
```

### 3. Установка UpperCase для команд SQL

UpperCase установлен по умолчанию.
Если требуется корректировка: 
```
Window -> Preferences -> Editors -> SQL Editor -> Formatting -> KewwordCase: Upper
```

### Установка pandas на Jupyter Notebook

```
pip install pandas
```

***


### Установка mysql-connector на Jupyter Notebook
```
pip install mysql-connector-python
```

***


### Установка pymongo на Jupyter Notebook

(Заодно, чтобы, как говорится "два раза не вставать")
```
pip install pymongo
```

***

**SQLite** — это лёгкая встроенная система управления базами данных (СУБД), которая хранит всю базу в одном файле.

SQLite прекрасно подходит для 
* небольших проектов, 
* обучения SQL, 
* локального хранения данных без настройки отдельного сервера.


Особенности:

* **Не требует установки сервера** — работает прямо внутри программы.
* **Данные хранятся в файле** (например, `my_database.db`).
* **Поддерживает SQL-запросы** (`SELECT`, `INSERT`, `UPDATE`, `DELETE` и др.).
* **Входит в стандартную библиотеку Python** через модуль `sqlite3`.
* **`SQLite` не имеет встроенной системы пользователей и паролей** 
  * Доступ к базе определяется правами доступа к файлу БД.


### Подключение к базе данных

Создаём подключение к SQLite.  
* Пакет `sqlite3` уже в базовой конфигурации Python (ничего дополнительно инсталлировать не надо!)
* БД тоже специально создавать не надо:
  * Если файл БД `my_database.db` ещё не существует, он будет создан автоматически.

```python
import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('my_database.db')

# Создание курсора для выполнения SQL-запросов
cursor = conn.cursor()
```

### Создание таблицы

Создаём таблицу `users` для хранения информации о пользователях.

```python
cursor.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);
""")

# Сохраняем изменения в базе данных
conn.commit()
```

### Добавление данных в БД

Добавляем новую запись в таблицу `users`.

```python
cursor.execute(
    "INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
…

import sqlite3
from typing import Iterator

# Создаём коннектор - подключение к БД
conn = sqlite3.connect('my_database.db')

# Создание курсора для выполнения SQL-запросов
cursor = conn.cursor()


# Создаём таблицу `users` для хранения информации о пользователях.
q_create_table = """
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );"""

cursor.execute(q_create_table)

# Сохраняем изменения в базе данных
conn.commit()

# Добавляем новую запись в таблицу `users`.

q_insert = "INSERT INTO users (name, age, email) VALUES (?, ?, ?);"
cursor.execute(q_insert,("John Doe", 25, "johndoe@example.com"))

# Сохраняем изменения
conn.commit()


# Получаем и выводим все записи из таблицы `users`.
cursor.execute("SELECT * FROM users;")

# Курсор - это итератор?
print("Курсор - это итератор?", isinstance(cursor, Iterator))
# Курсор - это итератор? True

print(cursor.fetchall())  # [(1, 'John Doe', 25, 'johndoe@example.com')]
print(cursor.fetchall())  # []

# То же самое с помощью цикла for
cursor.execute("SELECT * FROM users;")

for row in cursor:
    print(row)

# --- "Красивый" вывод таблицы в терминал: -------------------
cursor.execute(q_insert,("Jane Doe", 25, "janedoe@example.com"))
cursor.execute("SELECT * FROM users;")
rows = cursor.fetchall()

headers = [col[0] for col in cursor.description]

from tabulate import tabulate  # pip install tabulate

print(tabulate(rows, headers=headers, tablefmt="psql"))

## Создание SQL таблицы с помощью DataFrame (pandas + SQLite)

```python
import pandas as pd
import sqlite3
```

### Что такое `pandas`?:

* `pandas` — библиотека для работы с табличными данными (DataFrame)
* `sqlite3` — встроенный модуль Python для работы с SQLite
* В отличии от `sqlite3`, `pandas` является внешней библиотекой. 
  * Поэтому, требует предварительной инсталляции:`pip install pandas`
* Вместе они позволяют легко:
  * создавать таблицы из DataFrame
  * сохранять данные в базу данных
  * читать данные обратно в DataFrame

---

### Создание DataFrame

```python
# Создаём словарь с данными
data = {
    'column1': ['value1', 'value2', 'value3'],
    'column2': ['value1', 'value2', 'value3'],
    'column3': ['value1', 'value2', 'value3'],
}

# Преобразуем словарь в DataFrame
df = pd.DataFrame(data)

# Выводим таблицу
print(df)
```

### Что такое `DataFrame`?

* `DataFrame` — это таблица (как в Excel)
* ключи словаря → названия колонок
* списки → строки значений
* все столбцы должны быть одинаковой длины

---

### Загрузка DataFrame в SQLite

#### 1. Создаём подключение к СУБД SQLite

```python
# Подключение к базе данных (файл создаётся автоматически, если его нет)
conn = sqlite3.connect('students.db')
```

#### 2. `pandas` содержит встроенный метод `to_sql`, благодаря чему

* без использования команды `CREATE TABLE` создаётся файл базы `students.db`
  * если файл уже есть — просто открывается
  * это “точка входа” в базу данных
…

import pandas as pd
import sqlite3

# Создаём словарь с данными
data = {
    'column1': ['value1', 'value2', 'value3'],
    'column2': ['value1', 'value2', 'value3'],
    'column3': ['value1', 'value2', 'value3'],
}

# Преобразуем словарь в DataFrame
df = pd.DataFrame(data)

# Выводим таблицу
print(df)

#   column1 column2 column3
# 0  value1  value1  value1
# 1  value2  value2  value2
# 2  value3  value3  value3


# 1. Создаём подключение к СУБД SQLite
conn = sqlite3.connect('students.db')

# 2. `pandas` содержит встроенный метод `to_sql`, благодаря чему
df.to_sql(
    'new_table',   # имя таблицы в БД
    conn,          # подключение к базе
    if_exists='replace',  # заменить таблицу, если она уже существует
    index=False    # не сохранять индекс pandas как отдельный столбец
)

# Выполняем SQL-запрос и сразу получаем DataFrame
df_from_db = pd.read_sql_query(
    'SELECT * FROM new_table',
    conn
)

# Вывод результата
print(df_from_db)

#   column1 column2 column3
# 0  value1  value1  value1
# 1  value2  value2  value2
# 2  value3  value3  value3

"""
Пакет mysql.connector внешней, поэтому требует предварительной инсталляции:

pip install mysql-connector-python

Кроме того, требуется сохранить в отдельном файле local_settings.py параметры подключения
"""

import mysql.connector
from local_settings import dbconfig


connection = mysql.connector.connect(**dbconfig)

cursor = connection.cursor()


# Получение списка таблиц
cursor.execute("SHOW TABLES;")
tables = cursor.fetchall()

# Вывод списка таблиц
print("Список таблиц в базе данных sakila:")
for table in tables:
    print(table[0])


print("Список жанров из БД sakila:")
cursor.execute("SELECT * FROM category;")
for id, genre, *_ in cursor:
    print(id, genre)