First

1 files changed, 556 insertions, 0 deletions

diff --git a/SQL/SQLite.md b/SQL/SQLite.md
new file mode 100644
index 0000000..fa98f5d
--- /dev/null
+++ b/SQL/SQLite.md
@@ -0,0 +1,556 @@
+```python
+# Создание таблицы
+with con:
+    con.execute("""
+        CREATE TABLE USER (
+            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+            name TEXT,
+            age INTEGER
+        );
+    """)
+    
+# Вставка записей в таблицу
+sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
+data = [
+    (1, 'Alice', 21),
+    (2, 'Bob', 22),
+    (3, 'Chris', 23)
+]
+
+# Выполнение запросов к базе данных
+with con:
+    data = con.execute("SELECT * FROM USER WHERE age <= 22")
+    for row in data:
+        print(row)
+```
+### Интеграция с pandas
+```python
+# Объявим датафрейм:  
+df_skill = pd.DataFrame({
+    'user_id': [1,1,2,2,3,3,3],
+    'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
+})
+
+# Сохранение датафрейма в БД
+df_skill.to_sql('SKILL', con)
+```
+Вот и всё! Нам даже не нужно заранее создавать таблицу. Типы данных и характеристики полей будут настроены автоматически, на основании характеристик датафрейма. Конечно, вы, если надо, можете настроить всё самостоятельно.  
+  
+Теперь, предположим, нам нужно получить объединение таблиц `USER` и `SKILL` и записать полученные данные в датафрейм pandas. Это тоже очень просто:  
+```python
+df = pd.read_sql('''
+    SELECT s.user_id, u.name, u.age, s.skill 
+    FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
+''', con)
+```
+Здесь нам нужно определить SQL-выражение со знаками вопроса (`?`) в виде местозаполнителей. Учитывая то, что в нашем распоряжении есть объект подключения к базе данных, мы, подготовив выражение и данные, можем вставить записи в таблицу:  
+```python
+with con:
+    con.executemany(sql, data)
+```
+Сообщений об ошибках после выполнения этого кода не поступает, а это значит, что данные успешно добавлены в таблицу.
+
+Замечательно! А теперь давайте запишем то, что у нас получилось, в новую таблицу с именем `USER_SKILL`:  
+```python
+df.to_sql('USER_SKILL', con)
+```
+#### Чтение данных SQLite с помощью Pandas 
+
+С pandas управление базой данных SQLite превращается в увлекательный процесс. Она предоставляет функцию `read_sql`, позволяющую напрямую выполнять инструкции SQL, не заботясь о внутренней инфраструктуре. 
+
+```python
+import pandas as pd
+df = pd.read_sql("select * from transcript", con)
+df    name  grade0   Mike     951   Jane     922  Bella     98
+```
+#### Обратная запись DataFrame в SQLite
+
+После обработки данных с помощью pandas самое время осуществить обратную запись DataFrame в БД SQLite для долгосрочного хранения. На этот случай в pandas есть метод `to_sql`. Обратимся к соответствующему примеру: 
+
+```python
+df['gpa'] = [4.0, 3.8, 3.9]
+df.to_sql("transcript", con, if_exists="replace", index=False)
+list(cur.execute("select * from transcript order by grade desc"))[('Bella', 98, 3.9), ('Mike', 95, 4.0), ('Jane', 92, 3.8)]
+```
+
+- В отличие от ==`read_sql`==, функции из библиотеки pandas, ==`to_sql`== является методом класса ==`DataFrame`==, вследствие чего он непосредственно вызывается объектом ==`DataFrame`==. 
+- В методе ==`to_sql`== указывается таблица, в которую сохраняется ==`DataFrame`==. 
+- Отметим важность параметра ==`if_exists`==, так как по умолчанию ему задается значение ==`“fail”`==. Это значит, что если таблица уже существует, вы не сможете записать в нее текущий `DataFrame`, поскольку будет вызвана ошибка ==`ValueError`==. В рассматриваемом примере требуется заменить существующую таблицу из-за изменения средних баллов оценок, поэтому параметру ==`if_exisits`== устанавливается значение ==`“replace”`==.  
+- В результате установки ==`index=False`== индекс объекта ==`DataFrame`== просто игнорируется при сохранении в таблицу. По своему принципу данное действие аналогично методу ==`to_csv`==, с которым вы наверняка знакомы лучше.
+
+
+#############################################
+```bash
+import sqlite3
+```
+### Подключение к базе данных (или создание новой)
+```python
+import sqlite3
+
+try:
+    with sqlite3.connect('file:sensors.db?mode=rw', uri=True) as conn:
+        print('sqlite3.db is OK !')
+except:
+    with sqlite3.connect('sensors.db') as conn:
+        cursor = conn.cursor()
+        query = """CREATE TABLE sensors (
+                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+                sens_loc TEXT(32) NOT NULL,
+                sens_name TEXT(32) NOT NULL,
+                sens_date TEXT(10) NOT NULL,
+                sens_time TEXT(8) NOT NULL,
+                sens_temp TEXT(8) NOT NULL,
+                sens_pres TEXT(8) NOT NULL,
+                sens_hum TEXT(3) NOT NULL,
+                sens_vcc TEXT(8) NOT NULL,
+                sens_other TEXT(255)
+                )"""
+        cursor.execute(query)        
+        conn.commit()
+        print('sqlite3.db is NOT !')
+
+
+with sqlite3.connect('example.db') as db
+    cursor = db.cursor()
+    query = """ CREATE TABLE IF NOT EXISTS expenses(id INTEGER, name TEXT)"""
+    cursor.execute(query)
+```
+
+
+```bash
+conn = sqlite3.connect('example.db')
+cursor = conn.cursor()
+
+# Создание таблицы
+cursor.execute('''
+CREATE TABLE IF NOT EXISTS users (
+    id INTEGER PRIMARY KEY,
+    name TEXT,
+    age INTEGER
+)
+''')
+
+# Вставка данных
+cursor.execute('''
+INSERT INTO users (name, age) VALUES (?, ?)
+''', ('Alice', 30))
+
+# Сохранение изменений
+conn.commit()
+
+# Получение данных
+cursor.execute('SELECT * FROM users')
+rows = cursor.fetchall()
+for row in rows:
+    print(row)
+
+# Закрытие соединения
+conn.close()
+```
+Этот скрипт создает базу данных SQLite, создает таблицу users, добавляет в неё запись и выводит все строки таблицы. Команда cursor.execute используется для выполнения SQL-запросов, а conn.commit сохраняет изменения.
+### Обновление возраста пользователя с именем Alice
+```python
+cursor.execute('''
+UPDATE users SET age = ? WHERE name = ?
+''', (31, 'Alice'))
+conn.commit()
+```
+В этом примере возраст пользователя с именем Alice обновляется до 31 года. После выполнения запроса изменения сохраняются с помощью conn.commit.
+### Удаление пользователя с именем Alice ###
+```python
+cursor.execute('''
+DELETE FROM users WHERE name = ?
+''', ('Alice',))
+conn.commit()
+```
+Этот пример показывает, как удалить запись из таблицы по условию. В данном случае удаляется пользователь с именем Alice.
+### Работа с параметризованными запросами: ###
+Для защиты от SQL-инъекций рекомендуется использовать параметризованные запросы.
+```bash
+name = 'Bob'
+cursor.execute('SELECT * FROM users WHERE name = ?', (name,))
+rows = cursor.fetchall()
+```
+Использование параметризованных запросов помогает избежать уязвимостей, связанных с SQL-инъекциями, передавая параметры запроса через кортеж или список.
+
+========================================
+```python
+# извлечь один/три/все столбец
+SELECT prod_name FROM Products
+SELECT prod_id, prod_name, prod price FROM Products
+SELECT * FROM Products;
+
+# извлечь уникальные строки
+SELECT DISTINCT vend_id 
+FROM Products;
+
+# извлечь 5 строк начиная с 2ой
+SELECT prod_name
+FROM Products 
+LIMIT 5 OFFSET 2;
+
+# комментарии
+# этокомментарий
+/*
+	это комментарий
+*/
+SELECT DISTINCT vend_id   -- это комментарий
+FROM Products;
+
+# сортировка 1 или нескольким выбранным/невыбранным столбцам
+SELECT prod_name 
+FROM Products
+ORDER BY prod_name, prod_price; (DESC) -- сортировка наоборот
+
+# можно сортировать по положению столбца (только выбранному)
+SELECT prod_name 
+FROM Products
+ORDER BY 2, 3; (DESC) -- сортировка наоборот
+
+# критерий отбора
+SELECT DISTINCT vend_id
+FROM Products
+WHERE prod_price = 3.49;
+
+/*
+	если оба: WHERE и ( ORDER BY - должно быть последним )
+ */
+
+# сравнение с диапазоном значений
+SELECT DISTINCT vend_id
+FROM Products
+WHERE prod_price = 3.49 BETWEEN 5 AND 10
+( WHERE prod_price IS NULL ) --проверка на NULL
+
+# фильтр по нескольким столбцам (+ можно объединять и много + скобки)
+SELECT prod_id, prod_price, prod_name
+FROM Products
+WHERE vend_id = 'DLL01' AND prod_price <= 4  -- И то И то
+( WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'  -- ИЛИ то ИЛИ то )
+
+# сравнение
+SELECT prod_name, prod_price
+FROM Products
+WHERE vend_id IN ('DLL01', 'BRS01') -- это лучше!
+( WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ) -- то же самое
+ORDER BY prod_name;
+
+# выбор все КРОМЕ
+SELECT prod_name
+FROM Products
+WHERE NOT vend_id = 'DLL01'
+ORDER BY prod_name;
+
+# поиск с использованием МЕТАСИМВОЛОВ (только текстовые поля) и любое кол-во
+SELECT prod_id, prod_name
+FROM Products
+WHERE prod_name LIKE 'Fish%'; -- %
+( WHERE email LIKE 'b%@forta.com'; ) -- % может стоять в любом месте
+WHERE prod_name LIKE '__ inch teddy bear%'; -- __(2) % (_один символ)
+
+# конкатенация (+) или (||)
+SELECT vend_name || ' ()' || vend_country || ')'
+FROM Vendors
+ORDER BY vend_name;
+
+# убрать все пробелы справа
+SELECT vend_name || ' ()' || 
+RTRIM(vend_country) || ')'
+FROM Vendors
+ORDER BY vend_name; 
+
+# псевдонимы (переименовать столбец (если недопустимые символы, или длинное))
+SELECT RTRIM(vend_name) || ' ()' || 
+	   RTRIM(vend_country) || ')'
+	   AS vend_title   -- псевдоним
+FROM Vendors
+ORDER BY vend_name;
+
+# арифметические операции
+SELECT prod_id, 
+	   quantity, 
+	   item_price
+	   quantity*item_price AS expanded_price -- нов колонка (*)
+FROM OrderItems
+WHERE order_nam = 20008;
+
+# функции обработки данных
+# DATE()
+SELECT order_num
+FROM Orders
+WHERE strftime('%Y', order_date) = 2020; -- извлекается только год
+-- добавить AND для сравнения месяца и года
+
+# ИТОГОВЫЕ ФУНКЦИИ
+# AVG()
+SELECT AVG(prod_price) AS avg_price -- строки NULL - игнорируются
+FROM Products
+WHERE vend_id = 'DLL01'; 
+
+SELECT AVG(DISTINCT prod_price) AS avg_price -- только уникальные цены
+FROM Products
+WHERE vend_id = 'DLL01';
+
+# COUNT()
+SELECT COUNT(*) AS num_cust -- подсчет всех строк независимо от значений
+FROM Customers;
+
+SELECT COUNT(cust_email) AS num_cust -- подсчет только где есть email
+FROM Customers;
+
+# MAX()
+# MIN()
+
+# SUM() - строки с NULL - игнорируются !
+SELECT SUM(item_price*quantity) AS total_price
+FROM OrderItems
+WHERE order_item = 20005;
+
+# комбинирование итоговых функций
+SELECT COUNT(*) AS num_items,
+	   MIN(prod_price) AS price_min,
+	   MAX(prod_price) AS price_max,
+	   AVG(prod_price) AS price_avg
+FROM Products;
+
+# СОЗДАНИЕ ГРУПП
+SELECT vend_id, COUNT(*) AS num_prods
+FROM Products
+GROUP BY vend_id;
+# WHERE -> GROUP BY -> ORDER BY -- только такой порядок !
+
+# HAVING - фильтр группы. (WHERE - фильтр строк)
+SELECT cust_id, COUNT(*) AS orders
+FROM Orders
+GROUP BY cust_id
+HAVING COUNT (*) >= 2;
+
+SELECT order_num, COUNT(*) AS items
+FROM OrderItems
+GROUP BY order_num
+HAVING COUNT (*) >= 3
+ORDER BY items, order_num;
+
+# ПОРЯДОК СЛЕДОВАНИЯ: ###################################
+SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
+#########################################################
+
+# ФИЛЬТРАЦИЯ с помощью ПОДЗАПРОСОВ :
+SELECT cust_name, cust_contact
+FROM Customers
+WHERE cust_id IN (SELECT cust_id
+				  FROM Orders
+				  WHERE order_num IN (SELECT order_num
+								      FROM OrderItems
+								      WHERE prod_id = 'RGAN01'));
+
+# SELECT в подзапросах могут возвращать только один столбец !!!
+
+# СОЗДАНИЕ СОЕДИНЕНИЯ:
+SELECT vend_name, prod_name, prod_price
+FROM Vendors, Products -- ( Vendors INNER JOIN Products )
+WHERE Vendors.vend_id = Products.vend_id;
+
+SELECT vend_name, prod_name, prod_price
+FROM Vendors INNER JOIN Products
+					 ON Vendors.vend_id = Products.vend_id;
+
+SELECT vend_name, prod_name, prod_price, quantity
+FROM OrderItems, Products, Vendors
+WHERE Products.vend_id = Vendors.vend_id
+AND OrderItems.prod_id = Products.prod_id
+AND order_num = 20007;
+
+SELECT cust_name, cust_contact
+FROM Customers, Orders, OrderItems
+WHERE Customers.cust_id = Orders.cust_id
+AND OrderItems.order_num = Orders.order_num
+AND prod_id = 'RGAN01'));
+
+# Псевдонимы ТАБЛИЦ - только во время выполнения запроса
+SELECT cust_name, cust_contact
+FROM Customers AS C, Orders AS O, OrderItems AS OI
+WHERE C.cust_id = O.cust_id
+AND OI.order_num = O.order_num
+AND prod_id = 'RGAN01'));
+
+# САМО_СОЕДИНЕНИЯ
+SELECT c1.cust_id, c1.cust_name, c1.cust_contact
+FROM Customers AS c1, Customers AS c2
+WHERE c1.cust_name = c2.cust_name
+  AND c2.cust_contact = 'Jim Jones';
+
+# ЕСТЕСТВЕННЫЕ СОЕДИНЕНИЯ
+SELECT C.*, O.order_num, O.order_date,
+	   OI.prod_id, OI.quantity, OI.item_price
+FROM Customers AS C, Orders AS O, OrderItems AS OI
+WHERE C.cust_id = O.cust_id
+  AND OI.order_num = O.order_num
+  AND prod_id = 'RGAN01';
+
+# ВНЕШНИЕ СОЕДИНЕНИЯ
+SELECT Customers.cust_id, Orders.order_num
+FROM Customers
+INNER JOIN Orders -- LEFT OUTER JOIN
+		ON Customers.cust_id = Orders.cust_id;
+
+# ИТОГОВЫЕ СОЕДИНЕНИЯ
+SELECT Customers.cust_id, 
+	COUNT(Orders.order_num) AS num_ord
+FROM Customers
+INNER JOIN Orders -- LEFT OUTER JOIN
+		ON Customers.cust_id = Orders.cust_id
+GROUP BY Customers.cust_id;
+
+# КОМБИНИРОВАННЫЕ ЗАПРОСЫ 
+# -- UNION - повторяющиеся строки - удаляются!!! UNION ALL - НЕТ !!!
+SELECT cust_name, cust_contact, cust_email
+FROM Customers
+WHERE cust_state IN ('IL', 'IN', 'MI')
+UNION
+SELECT cust_name, cust_contact, cust_email
+FROM Customers
+WHERE cust_name = 'Fun4All';
+
+# тот же самый запрос:
+SELECT cust_name, cust_contact, cust_email
+FROM Customers
+WHERE cust_state IN ('IL', 'IN', 'MI')
+				 OR cust_name = 'Fun4All';
+		
+# сортировка результатов КОМБИНИРОВАННЫХ ЗАПРОСОВ
+SELECT cust_name, cust_contact, cust_email
+FROM Customers
+UNION
+SELECT cust_name, cust_contact, cust_email
+FROM Customers
+WHERE cust_name = 'Fun4All'
+ORDER BY cust_name, cust_contact; -- сортировка ОБОИХ инструкций !!!
+
+#=================================================
+# ДОБАВЛЕНИЕ ДАННЫХ -- INSERT
+INSERT INTO Customers
+VALUES(100000006,
+	  'Toy Land',
+	  '123 Any Street',
+	  'New York',
+	  'NY',
+	  '11111',
+	  'USA',
+	  'NULL',
+	  'NULL');
+
+# то же самое только более правильно:
+# столбцы можно пропускать если он:
+# по умолчанию = NULL, или = какое-то значение по умолчанию
+# добавляется только ОДНА ! строка:
+INSERT INTO Customers(cust_id,
+					 cust_name,
+					 cust_address,
+					 cust_city,
+					 cust_state,
+					 cust_zip,
+					 cust_country,
+					 cust_contact,
+					 cust_email)
+VALUES(100000006,
+	  'Toy Land',
+	  '123 Any Street',
+	  'New York',
+	  'NY',
+	  '11111',
+	  'USA',
+	  'NULL',
+	  'NULL');
+
+# можно брать значения из другой таблицы с помощью SELECT
+# учитывается только положение столбцов инструкции INSERT, 
+# а SELECT - нет !
+# добавляются ВСЕ! строки из SELECT:
+INSERT INTO Customers(cust_id,
+					 cust_name,
+					 cust_address,
+					 cust_city,
+					 cust_state,
+					 cust_zip,
+					 cust_country,
+					 cust_contact,
+					 cust_email)
+SELECT  cust_id,
+		cust_name,
+		cust_address,
+		cust_city,
+		cust_state,
+		cust_zip,
+		cust_country,
+		cust_contact,
+		cust_email
+FROM CustNew;
+
+# ОБНОВЛЕНИЕ ЗАПИСЕЙ в БД
+UPDATE Customers
+SET cust_email = 'kim@thetoystore.com',
+	cust_contact = 'Sam Roberts'
+WHERE cust_id = 1000000005;
+
+# удаление значение из столбца
+UPDATE Customers
+SET cust_email = NULL -- отсутствие хоть какого-то значения
+WHERE cust_id = 1000000005;
+
+# УДАЛЕНИЕ ЗАПИСЕЙ (СТРОК полностью, не удаляет таблицу)
+DELETE FROM Customers
+WHERE cust_id = 1000000005; -- если WHERE нет - удаление ВСЕХ! строк таблицы
+
+# СОЗДАНИЕ ТАБЛИЦ (значение NULL - по умолчанию)
+CREATE TABLE Products
+(
+ prod_id  CHAR(10)   NOT NULL PRIMARY KEY,
+ vend_id  CHAR(10)   NOT NULL,
+ prod_name  CHAR(254)   NOT NULL,
+ prod_price  DECIMAL(8, 2)   NOT NULL,
+ prod_desk  VARCHAR(1000)   NULL
+);
+
+CREATE TABLE Orders
+(
+ order_num  INTEGER   NOT NULL,
+ order_date DATETIME NOT NULL DEFAULT date('now'),
+ cust_id  CHAR(10)   NOT NULL  DEFAULT 1
+);
+
+# ОБНОВЛЕНИЕ ТАБЛИЦ
+# добавление столбца:
+ALTER TABLE Vendors
+ADD vend_phone CHAR(20);
+
+# удаление столбца:
+ALTER TABLE Vendors
+DROP COLUMN vend_phone;
+
+# удаление таблицы (безвозвратно !!!):
+DROP TABLE CustCopy;
+
+# ПРЕДСТАВЛЕНИЯ (ВИРТУАЛЬНЫЕ ТАБЛИЦЫ - запросы вместо данных)
+CREATE VIEW ProductCustomers AS
+SELECT cust_name, cust_contact, prod_id
+FROM Customers, Orders, OrderItems
+WHERE Customers.cust_id = Orders.cust_id
+	AND OrderItems.order_num = Orders.order_num;
+
+SELECT * FROM ProductCustomers; -- вернет список всех клиентов сделавших заказы
+WHERE prod_id = 'RGAN01'; -- все клиенты заказавшие товар 'RGAN01'
+
+.............
+
+# ТРАНЗАКЦИИ - единый набор SQL запросов (механизм для управления наборами SQL запросов, которые должны быть выполнены ТОЛЬКО целиком)
+BEGIN TRANSACTION
+......
+COMMIT TRANSACTION
+......
+ROLLBACK;
+
+# КУРСОРЫ (шаги)
+
+```
+