From e5abc1fe99340b979b18864d978179f71fe8f5c5 Mon Sep 17 00:00:00 2001 From: vlapa Date: Sat, 13 Jun 2026 20:33:26 +0300 Subject: First --- SQL/SQLite.md | 556 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 556 insertions(+) create mode 100644 SQL/SQLite.md (limited to 'SQL/SQLite.md') 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; + +# КУРСОРЫ (шаги) + +``` + -- cgit v1.2.3