diff options
Diffstat (limited to 'SQL')
| -rw-r--r-- | SQL/MySQL.md | 86 | ||||
| -rw-r--r-- | SQL/SQL Cheatsheet.md | 313 | ||||
| -rw-r--r-- | SQL/SQL. Базы Данных.md | 172 | ||||
| -rw-r--r-- | SQL/SQLite Commands.md | 384 | ||||
| -rw-r--r-- | SQL/SQLite.md | 556 | ||||
| -rw-r--r-- | SQL/НОРМАЛЬНЫЕ ФОРМЫ.md | 41 | ||||
| -rw-r--r-- | SQL/ШПАРГАЛКА SQLite.md | 4139 |
7 files changed, 5691 insertions, 0 deletions
diff --git a/SQL/MySQL.md b/SQL/MySQL.md new file mode 100644 index 0000000..1fb4179 --- /dev/null +++ b/SQL/MySQL.md @@ -0,0 +1,86 @@ +```bash +# Удаление: +sudo apt remove --purge mysql\* -y +sudo apt autoremove +sudo apt autoclean + +# проверка "хвостов": +sudo find / -name mysql + +# Установка: +sudo apt install mysql mysql-server + +sudo apt update +sudo apt upgrad -y +``` + +### Создадим базу данных в MySQL + +Войдем в командную строку MySQL: + +``` +sudo mysql +``` + +Создадим новую базу данных: + +``` +CREATE DATABASE НАЗВАНИЕ_САЙТА; +``` + +Создадим нового пользователя: + +``` +CREATE USER 'НАЗВАНИЕ_САЙТА'@'localhost' IDENTIFIED BY 'aef@234R345t2iJhfvar'; +``` + +«aef@234R345t2iJhfvar» — это пароль пользователя НАЗВАНИЕ_САЙТА. + +Дадим пользователю НАЗВАНИЕ_САЙТА права на базу данных НАЗВАНИЕ_САЙТА: + +``` +GRANT ALL ON НАЗВАНИЕ_САЙТА.* TO 'НАЗВАНИЕ_САЙТА'@'localhost'; +``` + +Выйдем из командной строки MySQL: + +``` +exit +``` + +Теперь давайте войдем в командную строку MySQL под новым MySQL-пользователем: + +``` +mysql -u НАЗВАНИЕ_САЙТА -p +``` + +Вас попросят ввести пароль, введите его. Если Вы не меняли его в примере выше, то он будет такой: + +``` +aef@234R345t2iJhfvar +``` + +Вот мы и вошли под новым пользователем. Посмотрим, какие базы данных ему видны, введем: + +``` +SHOW DATABASES; +``` + +Результат будет такой: + +``` ++---------------------------+ +| Database | ++---------------------------+ +| information_schema | +| НАЗВАНИЕ_САЙТА | ++---------------------------+ +2 rows in set (0.06 sec) +``` + +Видим нашу базу НАЗВАНИЕ_САЙТА, отлично, выйдем из командной строки MySQL: + +``` +exit +``` + diff --git a/SQL/SQL Cheatsheet.md b/SQL/SQL Cheatsheet.md new file mode 100644 index 0000000..175b0a0 --- /dev/null +++ b/SQL/SQL Cheatsheet.md @@ -0,0 +1,313 @@ +# Quick SQL Cheatsheet + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#quick-sql-cheatsheet) + +A quick reminder of all relevant SQL queries and examples on how to use them. + +This repository is constantly being updated and added to by the community. Pull requests are welcome. Enjoy! + +# Table of Contents + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#table-of-contents) + +1. [Finding Data Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#find) +2. [Data Modification Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#modify) +3. [Reporting Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#report) +4. [Join Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#joins) +5. [View Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#view) +6. [Altering Table Queries.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#alter) +7. [Creating Table Query.](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#create) + +# 1. Finding Data Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#1-finding-data-queries) + +### **SELECT**: used to select data from a database + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#select-used-to-select-data-from-a-database) + +- `SELECT` * `FROM` table_name; + +### **DISTINCT**: filters away duplicate values and returns rows of specified column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#distinct-filters-away-duplicate-values-and-returns-rows-of-specified-column) + +- `SELECT DISTINCT` column_name; + +### **WHERE**: used to filter records/rows + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#where-used-to-filter-recordsrows) + +- `SELECT` column1, column2 `FROM` table_name `WHERE` condition; +- `SELECT` * `FROM` table_name `WHERE` condition1 `AND` condition2; +- `SELECT` * `FROM` table_name `WHERE` condition1 `OR` condition2; +- `SELECT` * `FROM` table_name `WHERE NOT` condition; +- `SELECT` * `FROM` table_name `WHERE` condition1 `AND` (condition2 `OR` condition3); +- `SELECT` * `FROM` table_name `WHERE EXISTS` (`SELECT` column_name `FROM` table_name `WHERE` condition); + +### **ORDER BY**: used to sort the result-set in ascending or descending order + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#order-by-used-to-sort-the-result-set-in-ascending-or-descending-order) + +- `SELECT` * `FROM` table_name `ORDER BY` column; +- `SELECT` * `FROM` table_name `ORDER BY` column `DESC`; +- `SELECT` * `FROM` table_name `ORDER BY` column1 `ASC`, column2 `DESC`; + +### **SELECT TOP**: used to specify the number of records to return from top of table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#select-top-used-to-specify-the-number-of-records-to-return-from-top-of-table) + +- `SELECT TOP` number columns_names `FROM` table_name `WHERE` condition; +- `SELECT TOP` percent columns_names `FROM` table_name `WHERE` condition; +- Not all database systems support `SELECT TOP`. The MySQL equivalent is the `LIMIT` clause +- `SELECT` column_names `FROM` table_name `LIMIT` offset, count; + +### **LIKE**: operator used in a WHERE clause to search for a specific pattern in a column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#like-operator-used-in-a-where-clause-to-search-for-a-specific-pattern-in-a-column) + +- % (percent sign) is a wildcard character that represents zero, one, or multiple characters +- _ (underscore) is a wildcard character that represents a single character +- `SELECT` column_names `FROM` table_name `WHERE` column_name `LIKE` pattern; +- `LIKE` ‘a%’ (find any values that start with “a”) +- `LIKE` ‘%a’ (find any values that end with “a”) +- `LIKE` ‘%or%’ (find any values that have “or” in any position) +- `LIKE` ‘_r%’ (find any values that have “r” in the second position) +- `LIKE` ‘a_%_%’ (find any values that start with “a” and are at least 3 characters in length) +- `LIKE` ‘[a-c]%’ (find any values starting with “a”, “b”, or “c” + +### **IN**: operator that allows you to specify multiple values in a WHERE clause + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#in-operator-that-allows-you-to-specify-multiple-values-in-a-where-clause) + +- essentially the IN operator is shorthand for multiple OR conditions +- `SELECT` column_names `FROM` table_name `WHERE` column_name `IN` (value1, value2, …); +- `SELECT` column_names `FROM` table_name `WHERE` column_name `IN` (`SELECT STATEMENT`); + +### **BETWEEN**: operator selects values within a given range inclusive + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#between-operator-selects-values-within-a-given-range-inclusive) + +- `SELECT` column_names `FROM` table_name `WHERE` column_name `BETWEEN` value1 `AND` value2; +- `SELECT` * `FROM` Products `WHERE` (column_name `BETWEEN` value1 `AND` value2) `AND NOT` column_name2 `IN` (value3, value4); +- `SELECT` * `FROM` Products `WHERE` column_name `BETWEEN` #01/07/1999# AND #03/12/1999#; + +### **NULL**: values in a field with no value + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#null-values-in-a-field-with-no-value) + +- `SELECT` * `FROM` table_name `WHERE` column_name `IS NULL`; +- `SELECT` * `FROM` table_name `WHERE` column_name `IS NOT NULL`; + +### **AS**: aliases are used to assign a temporary name to a table or column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#as-aliases-are-used-to-assign-a-temporary-name-to-a-table-or-column) + +- `SELECT` column_name `AS` alias_name `FROM` table_name; +- `SELECT` column_name `FROM` table_name `AS` alias_name; +- `SELECT` column_name `AS` alias_name1, column_name2 `AS` alias_name2; +- `SELECT` column_name1, column_name2 + ‘, ‘ + column_name3 `AS` alias_name; + +### **UNION**: set operator used to combine the result-set of two or more SELECT statements + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#union-set-operator-used-to-combine-the-result-set-of-two-or-more-select-statements) + +- Each SELECT statement within UNION must have the same number of columns +- The columns must have similar data types +- The columns in each SELECT statement must also be in the same order +- `SELECT` columns_names `FROM` table1 `UNION SELECT` column_name `FROM` table2; +- `UNION` operator only selects distinct values, `UNION ALL` will allow duplicates + +### **INTERSECT**: set operator which is used to return the records that two SELECT statements have in common + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#intersect-set-operator-which-is-used-to-return-the-records-that-two-select-statements-have-in-common) + +- Generally used the same way as **UNION** above +- `SELECT` columns_names `FROM` table1 `INTERSECT SELECT` column_name `FROM` table2; + +### **EXCEPT**: set operator used to return all the records in the first SELECT statement that are not found in the second SELECT statement + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#except-set-operator-used-to-return-all-the-records-in-the-first-select-statement-that-are-not-found-in-the-second-select-statement) + +- Generally used the same way as **UNION** above +- `SELECT` columns_names `FROM` table1 `EXCEPT SELECT` column_name `FROM` table2; + +### **ANY|ALL**: operator used to check subquery conditions used within a WHERE or HAVING clauses + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#anyall-operator-used-to-check-subquery-conditions-used-within-a-where-or-having-clauses) + +- The `ANY` operator returns true if any subquery values meet the condition +- The `ALL` operator returns true if all subquery values meet the condition +- `SELECT` columns_names `FROM` table1 `WHERE` column_name operator (`ANY`|`ALL`) (`SELECT` column_name `FROM` table_name `WHERE` condition); + +### **GROUP BY**: statement often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#group-by-statement-often-used-with-aggregate-functions-count-max-min-sum-avg-to-group-the-result-set-by-one-or-more-columns) + +- `SELECT` column_name1, COUNT(column_name2) `FROM` table_name `WHERE` condition `GROUP BY` column_name1 `ORDER BY` COUNT(column_name2) DESC; + +### **HAVING**: this clause was added to SQL because the WHERE keyword could not be used with aggregate functions + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#having-this-clause-was-added-to-sql-because-the-where-keyword-could-not-be-used-with-aggregate-functions) + +- `SELECT` `COUNT`(column_name1), column_name2 `FROM` table `GROUP BY` column_name2 `HAVING` `COUNT(`column_name1`)` > 5; + +### **WITH**: often used for retrieving hierarchical data or re-using temp result set several times in a query. Also referred to as "Common Table Expression" + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#with-often-used-for-retrieving-hierarchical-data-or-re-using-temp-result-set-several-times-in-a-query-also-referred-to-as-common-table-expression) + +- `WITH RECURSIVE` cte `AS` ( + `SELECT` c0.* `FROM` categories `AS` c0 `WHERE` id = 1 `# Starting point` + `UNION ALL` + `SELECT` c1.* `FROM` categories `AS` c1 `JOIN` cte `ON` c1.parent_category_id = cte.id + ) + `SELECT` * + `FROM` cte + +# 2. Data Modification Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#2-data-modification-queries) + +### **INSERT INTO**: used to insert new records/rows in a table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#insert-into-used-to-insert-new-recordsrows-in-a-table) + +- `INSERT INTO` table_name (column1, column2) `VALUES` (value1, value2); +- `INSERT INTO` table_name `VALUES` (value1, value2 …); + +### **UPDATE**: used to modify the existing records in a table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#update-used-to-modify-the-existing-records-in-a-table) + +- `UPDATE` table_name `SET` column1 = value1, column2 = value2 `WHERE` condition; +- `UPDATE` table_name `SET` column_name = value; + +### **DELETE**: used to delete existing records/rows in a table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#delete-used-to-delete-existing-recordsrows-in-a-table) + +- `DELETE FROM` table_name `WHERE` condition; +- `DELETE` * `FROM` table_name; + +# 3. Reporting Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#3-reporting-queries) + +### **COUNT**: returns the # of occurrences + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#count-returns-the--of-occurrences) + +- `SELECT COUNT (DISTINCT` column_name`)`; + +### **MIN() and MAX()**: returns the smallest/largest value of the selected column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#min-and-max-returns-the-smallestlargest-value-of-the-selected-column) + +- `SELECT MIN (`column_names`) FROM` table_name `WHERE` condition; +- `SELECT MAX (`column_names`) FROM` table_name `WHERE` condition; + +### **AVG()**: returns the average value of a numeric column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#avg-returns-the-average-value-of-a-numeric-column) + +- `SELECT AVG (`column_name`) FROM` table_name `WHERE` condition; + +### **SUM()**: returns the total sum of a numeric column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#sum-returns-the-total-sum-of-a-numeric-column) + +- `SELECT SUM (`column_name`) FROM` table_name `WHERE` condition; + +# 4. Join Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#4-join-queries) + +### **INNER JOIN**: returns records that have matching value in both tables + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#inner-join-returns-records-that-have-matching-value-in-both-tables) + +- `SELECT` column_names `FROM` table1 `INNER JOIN` table2 `ON` table1.column_name=table2.column_name; +- `SELECT` table1.column_name1, table2.column_name2, table3.column_name3 `FROM` ((table1 `INNER JOIN` table2 `ON` relationship) `INNER JOIN` table3 `ON` relationship); + +### **LEFT (OUTER) JOIN**: returns all records from the left table (table1), and the matched records from the right table (table2) + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#left-outer-join-returns-all-records-from-the-left-table-table1-and-the-matched-records-from-the-right-table-table2) + +- `SELECT` column_names `FROM` table1 `LEFT JOIN` table2 `ON` table1.column_name=table2.column_name; + +### **RIGHT (OUTER) JOIN**: returns all records from the right table (table2), and the matched records from the left table (table1) + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#right-outer-join-returns-all-records-from-the-right-table-table2-and-the-matched-records-from-the-left-table-table1) + +- `SELECT` column_names `FROM` table1 `RIGHT JOIN` table2 `ON` table1.column_name=table2.column_name; + +### **FULL (OUTER) JOIN**: returns all records when there is a match in either left or right table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#full-outer-join-returns-all-records-when-there-is-a-match-in-either-left-or-right-table) + +- `SELECT` column_names `FROM` table1 `FULL OUTER JOIN` table2 `ON` table1.column_name=table2.column_name; + +### **Self JOIN**: a regular join, but the table is joined with itself + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#self-join-a-regular-join-but-the-table-is-joined-with-itself) + +- `SELECT` column_names `FROM` table1 T1, table1 T2 `WHERE` condition; + +# 5. View Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#5-view-queries) + +### **CREATE**: create a view + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#create-create-a-view) + +- `CREATE VIEW` view_name `AS SELECT` column1, column2 `FROM` table_name `WHERE` condition; + +### **SELECT**: retrieve a view + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#select-retrieve-a-view) + +- `SELECT` * `FROM` view_name; + +### **DROP**: drop a view + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#drop-drop-a-view) + +- `DROP VIEW` view_name; + +# 6. Altering Table Queries + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#6-altering-table-queries) + +### **ADD**: add a column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#add-add-a-column) + +- `ALTER TABLE` table_name `ADD` column_name column_definition; + +### **MODIFY**: change data type of column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#modify-change-data-type-of-column) + +- `ALTER TABLE` table_name `MODIFY` column_name column_type; + +### **DROP**: delete a column + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#drop-delete-a-column) + +- `ALTER TABLE` table_name `DROP COLUMN` column_name; + +# 7. Creating Table Query + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#7-creating-table-query) + +### **CREATE**: create a table + +[](https://github.com/enochtangg/quick-SQL-cheatsheet?tab=readme-ov-file#create-create-a-table) + +- `CREATE TABLE` table_name `(` + `column1` `datatype`, + `column2` `datatype`, + `column3` `datatype`, + `column4` `datatype`, + `);`
\ No newline at end of file diff --git a/SQL/SQL. Базы Данных.md b/SQL/SQL. Базы Данных.md new file mode 100644 index 0000000..48e9bc5 --- /dev/null +++ b/SQL/SQL. Базы Данных.md @@ -0,0 +1,172 @@ +```sh +sudo apt install postgres +sudo apt install-contrib # Debian +sudo systemctl start posgresql.service +sudo systemctl status posgresql.service + +sudo -u postgres createuser --interactive +# вводим имя +# даем права sudo + +man createuser + +sudo -u postgres createdb metall +sudo adduser metall +sudo -u metall psql + +# переключиться на другую БД: +psql -d postgres +``` +### Установка из исходников: +```sh +# качаем с сайта нужную версию: +# https://www.postgresql.org/ftp/source/v16.2/postgresql-16.2.tar.gz + +mkdir Downloads +cd Downloads + +# Качаем: +wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz +# Скачать скрипт и сразу выполнить: +wget -O - http://www.tecmint.com/wp-content/scripts/Colorfull.sh | bash +# Также можно использовать curl: +curl -O https://github.com/torvalds/linux/archive/v4.11-rc6.tar.gz + +tar -xzvf postgresql-16.2.tar.gz +cd postgresql-16.2 +./configure --prefix=/var/lib/postgresql + +# Поиск пакетов в Linux: +sudo apt-cache search lib<пакет> # нужен пакет <name>-dev +sudo apt install <name>-dev + +brew install pkg-config + +./configure --prefix=/var/lib/postgresql +sudo make install-world +cd /var/lib/postgresql +sudo chown -R vlapa ../postgresql +./bin/pg_ctl initdb +export PGDATA=/var/lib/postgresql/main +./bin/pg_ctl initdb +./bin/pg_ctl -D /var/lib/postgresql/main start +./bin/psql -h localhost -U vlapa. postgres +# Создать БД: +CREATE DATABASE <name_db> + +#=========================================================== +# создадим конфигурацию перед сборкой. Для этого используем команду configure с тремя опциями: + +# prefix — укажем каталог для установки PostgreSQL; +# libdir — укажем каталог с библиотеками PostgreSQL, это необходимо, так как PostgreSQL устанавливается в нестандартное расположение и не будет знать где ему искать библиотеки; +# with-pgport — укажем нестандартный порт на котором будет работать этот экземпляр PostgreSQL. + +./configure --prefix=/home/alex/pgsql-16 --libdir=/home/alex/pgsql-16/lib/ --with-pgport=5555 +mkdir ~/pgsql-16/data/ +echo "export PGDATA=~/pgsql-16/data/" >> .bashrc +. .bashrc +ls -d $PGDATA + +echo "export PGDATA=~/pgsql-16/data/" >> .bashrc +. .bashrc +whereis psql + +initdb -k + +pg_ctl -l ~/pgsql-16/postgres.log start # -l -файл логов +pg_ctl status +psql -d postgres -c 'select now()' +pg_ctl stop +``` + +### Создание и запуск второго кластера +```sh +mkdir ~/pgsql-16/data2/ +initdb -k -D ~/pgsql-16/data2/ + +pg_ctl -D /home/alex/pgsql-16/data2/ -l logfile start +# При инициализация кластера, если путь к каталогу PGDATA отличается от переменной $PGDATA или если такая переменная не задана. То нужно с помощью ключа -D указать путь к этому каталогу. + +# Чтобы запустить второй кластер одновременно с первым, нужно поменять порт, на котором работает PostgreSQL. Изменим этот порт для второго кластера: +echo port = 5556 >> ~/pgsql-16/data2/postgresql.conf +pg_ctl -D ~/pgsql-16/data2/ -l ~/pgsql-16/data2/postgres.log start + +ps ax | grep postgresql +# Первый кластер запущен без ключа -D и используется переменная $PGDATA, а во втором кластере путь к PGDATA прописан в строке запуска. +# Перед дальнейшей работой выключим оба кластера: +pg_ctl stop + +pg_ctl -D /home/alex/pgsql-16/data2 stop +``` + +# 📑 Шпаргалка по основным командам PostgreSQL + +Вся работа с PostgreSQL осуществляется под пользователем `postgres` + +`$sudo su postgres` + +Работать с `PosgreSQL` можно как в интерактивном режиме, так и из командной строки. Программа — `psql` + +## Основные команды PostgreSQL в интерактивном режиме: + +- `\connect db_name` – подключиться к базе с именем db_name +- `\du` – список пользователей +- `\dp` (или `\z`) – список таблиц, представлений, последовательностей, прав доступа к ним +- `\di` – индексы +- `\ds` – последовательности +- `\dt` – список таблиц +- `\dt+` — список всех таблиц с описанием +- `\dt *s*` — список всех таблиц, содержащих s в имени +- `\dv` – представления +- `\dS` – системные таблицы +- `\d+` – описание таблицы +- `\o` – пересылка результатов запроса в файл +- `\l` – список баз данных +- `\i` – читать входящие данные из файла +- `\e` – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной _EDITOR_, то будет использоваться по умолчанию vi) +- `\d` “table_name” – описание таблицы +- `\i` запуск команды из внешнего файла, например \i /my/directory/my.sql +- `\pset` – команда настройки параметров форматирования +- `\echo` – выводит сообщение +- `\set` – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет). +- `\?` – справочник psql +- `\help` – справочник SQL +- `\q` (или `Ctrl+D`) – выход с программы + +## Удаленное подключение к Postgres: +- **postgresql.conf** +- **pg_hba.conf** + +Вы можете найти эти два файла в папке: +- **/etc/postgresql/13/main** (Linux) +- **C:/Program Files/PostgreSQL/13/data** (Windows) + + + + +![[Снимок экрана 2024-03-14 в 13.21.49.png]] + +![[Снимок экрана 2024-03-14 в 13.22.17.png]] + + +![[Снимок экрана 2024-03-14 в 13.25.59.png]] + +![[Снимок экрана 2024-03-14 в 13.27.58.png]] + + +![[Снимок экрана 2024-03-14 в 13.29.48.png]] +![[Снимок экрана 2024-03-14 в 13.32.01.png]] + +###################################################################### + +### Первичный ключ: + +_должен быть: +- уникальным +- заполненным (not Null) +_бывают: +- простые +- составные +- естественные +- искусственные +- diff --git a/SQL/SQLite Commands.md b/SQL/SQLite Commands.md new file mode 100644 index 0000000..16f4cc5 --- /dev/null +++ b/SQL/SQLite Commands.md @@ -0,0 +1,384 @@ +Ray Ferrell + +September 9, 2023 + +SQLite is an open-source, embedded relational database management system. It is a popular choice for local data storage in applications due to its lightweight nature, simplicity, and robustness. + +SQLite uses SQL syntax and provides developers with powerful commands to create, query, and manage databases. + +This comprehensive guide covers the most important SQLite commands for creating and managing database schemas, manipulating data, running queries, and handling connections. + +We’ll explore the SQL standard commands supported by SQLite and special dot commands offered by the SQLite3 command line shell. + +**_Also read: [Create SQLite Database – Step-by-Step Guide for Beginners](https://sqldocs.org/sqlite/sqlite-create-database/)_** + +1. [SQLite Data Definition Language (DDL) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-definition-language-ddl-commands "SQLite Data Definition Language (DDL) Commands") + 1. [CREATE](https://sqldocs.org/sqlite/sqlite-commands/#create "CREATE") + 2. [ALTER](https://sqldocs.org/sqlite/sqlite-commands/#alter "ALTER") + 3. [DROP](https://sqldocs.org/sqlite/sqlite-commands/#drop "DROP") +2. [SQLite Data Manipulation Language (DML) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-manipulation-language-dml-commands "SQLite Data Manipulation Language (DML) Commands") + 1. [INSERT](https://sqldocs.org/sqlite/sqlite-commands/#insert "INSERT") + 2. [UPDATE](https://sqldocs.org/sqlite/sqlite-commands/#update "UPDATE") + 3. [DELETE](https://sqldocs.org/sqlite/sqlite-commands/#delete "DELETE") + 4. [REPLACE](https://sqldocs.org/sqlite/sqlite-commands/#replace "REPLACE") +3. [SQLite Data Query Language (DQL) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-query-language-dql-commands "SQLite Data Query Language (DQL) Commands") + 1. [SELECT](https://sqldocs.org/sqlite/sqlite-commands/#select "SELECT") +4. [SQLite Transaction Control Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-transaction-control-commands "SQLite Transaction Control Commands") + 1. [BEGIN TRANSACTION](https://sqldocs.org/sqlite/sqlite-commands/#begin-transaction "BEGIN TRANSACTION") + 2. [COMMIT](https://sqldocs.org/sqlite/sqlite-commands/#commit "COMMIT") + 3. [ROLLBACK](https://sqldocs.org/sqlite/sqlite-commands/#rollback "ROLLBACK") +5. [SQLite Administration Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-administration-commands "SQLite Administration Commands") +6. [SQLite Dot Commands for sqlite3](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-dot-commands-for-sqlite3 "SQLite Dot Commands for sqlite3") +7. [Using Transactions in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#using-transactions-in-sqlite "Using Transactions in SQLite") +8. [Making SQLite Backups](https://sqldocs.org/sqlite/sqlite-commands/#making-sqlite-backups "Making SQLite Backups") +9. [Importing and Exporting Data](https://sqldocs.org/sqlite/sqlite-commands/#importing-and-exporting-data "Importing and Exporting Data") + 1. [Importing Data](https://sqldocs.org/sqlite/sqlite-commands/#importing-data "Importing Data") + 2. [Exporting Data](https://sqldocs.org/sqlite/sqlite-commands/#exporting-data "Exporting Data") +10. [Full-text Search in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#full-text-search-in-sqlite "Full-text Search in SQLite") +11. [User Authentication Methods in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#user-authentication-methods-in-sqlite "User Authentication Methods in SQLite") +12. [Statistical and Diagnostic Functions](https://sqldocs.org/sqlite/sqlite-commands/#statistical-and-diagnostic-functions "Statistical and Diagnostic Functions") + 1. [Statistical Aggregates](https://sqldocs.org/sqlite/sqlite-commands/#statistical-aggregates "Statistical Aggregates") +13. [Advanced Topics](https://sqldocs.org/sqlite/sqlite-commands/#advanced-topics "Advanced Topics") + 1. [SQLite Extensions](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-extensions "SQLite Extensions") + 2. [JSON Support](https://sqldocs.org/sqlite/sqlite-commands/#json-support "JSON Support") + 3. [Database Encryption](https://sqldocs.org/sqlite/sqlite-commands/#database-encryption "Database Encryption") + 4. [Multi-Threading](https://sqldocs.org/sqlite/sqlite-commands/#multi-threading "Multi-Threading") + 5. [And More](https://sqldocs.org/sqlite/sqlite-commands/#and-more "And More") + +## SQLite Data Definition Language (DDL) Commands + +Data Definition Language (DDL) commands allow you to create and modify database structures. The key DDL commands in SQLite are: + +### CREATE + +The CREATE command creates new database objects like tables, indexes, views, and triggers. + +For example, to [create a new table](https://sqldocs.org/sqlite/sqlite-create-table/): + +```sql +CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ); +``` + +You can also use CREATE to define indexes, views, and triggers. + +### ALTER + +The ALTER command modifies the structure of existing database objects. For example, to add a new column to a table: + +```sql +ALTER TABLE users ADD COLUMN phone TEXT; +``` + +Other usages include renaming or dropping columns, adding table constraints, and renaming tables. + +### DROP + +The DROP command removes objects from the database. For example, to remove a table: + +```sql +DROP TABLE users; +``` + +You can also DROP indexes, views, and triggers. + +## SQLite Data Manipulation Language (DML) Commands + +Data Manipulation Language (DML) commands allow inserting, modifying, and deleting data within schema objects like tables. Key commands include: + +### INSERT + +INSERT adds new rows of data into a table. For example: + +```sql +INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com'); +``` + +And you can insert multiple rows at once: + +```sql +INSERT INTO users (name, email) VALUES ('Mary', 'mary@example.com'), ('Lee', 'lee@example.com'); +``` + +### UPDATE + +UPDATE modifies existing data in a table. For example: + +```sql +UPDATE users SET name = 'John Taylor' WHERE id = 123; +``` + +Updates can affect multiple table rows at once. + +### DELETE + +DELETE removes rows from a table. For example: + +```sql +DELETE FROM users WHERE id = 123; +``` + +Similar to UPDATE, DELETE can remove multiple rows in one statement. + +### REPLACE + +The REPLACE command does an INSERT or UPDATE depending on whether the row already exists. From the SQLite documentation: + +> For each row proposed for insertion, if a row with the same primary key already exists in the database, delete it first and then insert the new row. + +This provides UPSERT functionality in a single statement. + +**_Also read: [SQLite Operators: A Comprehensive Guide](https://sqldocs.org/sqlite/sqlite-operators/)_** + +## SQLite Data Query Language (DQL) Commands + +Data Query Language (DQL) commands allow you to query, search, and filter data stored in the database. The most important command is: + +### SELECT + +SELECT retrieves data from one or more tables. For example: + +```sql +SELECT id, name FROM users; +``` + +This query selects the id and name columns from the users table. + +SELECT supports powerful filtering capabilities using WHERE clauses: + +```sql +SELECT * FROM users WHERE age >= 18 AND city = 'Boston'; +``` + +And advanced JOIN operations allow selecting data across multiple related tables. + +SQLite supports nearly all standard [SQL SELECT](https://sqldocs.org/sqlite/sqlite-select-statement/) syntax like subqueries, UNIONs, GROUP BY, ORDER BY, [LIMIT](https://sqldocs.org/sqlite/sqlite-limit/) and much more. Familiarity with SELECT is a must for working effectively with SQLite. + +**_Also read: [SQLite Data Types: An In-Depth Guide](https://sqldocs.org/sqlite/sqlite-data-types/)_** + +## SQLite Transaction Control Commands + +Transactions allow you to bundle multiple related operations into an atomic unit. The key commands are: + + + +### BEGIN TRANSACTION + +This starts a new transaction block. All subsequent DML commands execute within the context of the transaction. + +### COMMIT + +This commits (saves) the current transaction. All changes within the block become permanent. + +### ROLLBACK + +This rolls back (undoes) the current transaction. All changes within the block are discarded. + +Transactions provide consistency, atomicity, isolation, and durability. They are crucial for the correct operation of SQLite databases in concurrent environments. + +## SQLite Administration Commands + +Administrative commands handle tasks like user management, backups, and database maintenance. Important commands include: + +- **CREATE USER**: Creates a new user account for authentication. Users can be granted privileges and roles. +- **ALTER USER**: Modifies properties of an existing user, such as changing their password. +- **DROP USER**: Deletes a user account. +- **PRAGMA**: The PRAGMA command controls various configuration settings of the SQLite library. For example, `PRAGMA journal_mode` sets the journaling mode. +- **VACUUM**: The VACUUM command rebuilds and cleans the database file, reclaiming unused space. Recommended for occasional maintenance. +- **BACKUP**: Makes a backup copy of an SQLite database. Useful for tasks like archiving transaction logs. More details below. + +## SQLite Dot Commands for sqlite3 + +The sqlite3 command line shell provides special “dot commands” that facilitate common tasks. These commands are prefixed with a `.` and are not standard SQL. Some important sqlite3 dot commands include: + +- **.databases**: Lists names and files of attached databases; allows viewing of database connections. +- **.mode**: Sets output modes for displaying query results (e.g., list, column, csv). +- **.headers**: Toggles column headers on or off for query output. +- **.tables**: Displays names of tables in the connected database; useful for exploring schemas. +- **.schema**: Outputs the CREATE statements for database schemas; accepts regex patterns. +- **.indexes**: Lists names of indexes for connected databases; optional filtering available. +- **.dump**: Converts the database or specific tables to an SQL text file; useful for backups. +- **.output FILE**: Redirects output to a specified file. +- **.once FILE**: Similar to .output, but applies only to one query before reverting to stdout. +- **.open FILE**: Closes current database and opens specified file; facilitates database switching. +- **.backup**: Creates a backup copy of the opened database. +- **.exit**: Exits the sqlite3 shell program. + +There are many more handy dot commands and `.help` will show you a full list. The dot commands provide useful functionality beyond plain SQL. + +## Using Transactions in SQLite + +As introduced earlier, transactions are key to managing consistency in SQLite. Let’s look at a simple example: + +```sql +-- Start a transaction BEGIN TRANSACTION; -- Insert transfer amount in accounts UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Commit changes COMMIT; +``` + +This safely transfers $100 from account 1 to account 2 as a single atomic operation. If either UPDATE fails, the whole transaction will roll back and protect data integrity. + +Transactions have huge benefits for performance, safety, and correctness in concurrent systems. Use them liberally when multiple operations need to act as one. + +## Making SQLite Backups + +It’s important to routinely backup critical SQLite databases in case of corruption, hardware failure, or human error. The `.backup` and `BACKUP` commands help automate this. + +For example, from within the sqlite3 shell: + +```sql +sqlite> .backup main main.bak +``` + +This creates a copy of the currently open “main” database to a file named main.bak. + +You can also backup within a live application using the BACKUP command: + +```sql +BACKUP main TO 'main.bak'; +``` + +This copies the “main” database to the named backup file. + +Make sure to periodically backup SQLite databases using either of these approaches. The database files are portable and easily restored. + +## Importing and Exporting Data + +SQLite provides handy ways to move data in and out of databases. + +### Importing Data + +The `.import` command loads data into a table from a file. For example: + +```sql +sqlite> .mode csv sqlite> .import users.csv users +``` + +This imports the CSV file into the “users” table. + +Within an application, you can also import data using the sqlite3_load_extension() function to load a custom import module. + +### Exporting Data + +To export data, switch to the desired output mode like CSV and run a query: + +```sql +sqlite> .headers on sqlite> .mode csv sqlite> .once dump.csv sqlite> SELECT * FROM users; +``` + +This exports the users table to a CSV file. + +The `.once` dot command sends the output to a file instead of `stdout`. + +You can also implement custom export handlers by calling sqlite3_export_extensions() and registering callback functions. + +## Full-text Search in SQLite + +SQLite’s FTS modules enable powerful full-text searches on text data. For example: + +```sql +-- Create virtual FTS table CREATE VIRTUAL TABLE docs USING fts4(title, content); -- Populate the FTS table INSERT INTO docs(title, content) VALUES ('SQLite Guide', 'Intro to using SQLite commands...'), ('SQLite Tips', 'Optimizing performance of SQLite queries...'); -- Full text search SELECT * FROM docs WHERE docs MATCH 'guide tips'; +``` + +This performs a full-text search across the title and content columns, making it easy to build search functionality within applications. + +SQLite FTS provides fast and flexible textual searches. Refer to the documentation for the available FTS versions and customization options. + +## User Authentication Methods in SQLite + +SQLite provides ways to implement user authentication when opening database connections. For example: + +- Using the sqlite3_user_authenticate() interface within a custom authentication handler. This allows username/password checking. +- Attaching a user-defined authorization function using sqlite3_set_authorizer(). Can authorize on a per-command basis. +- Encrypting the database file with SQLite Encryption Extension (SEE). Decryption requires user-supplied passphrase, providing authentication. + +Depending on your specific needs, one of these approaches can be used to restrict and authenticate database access properly. + +## Statistical and Diagnostic Functions + +SQLite offers several functions to support statistical analysis and database diagnostics: + +### Statistical Aggregates + +Aggregates like STATS, STAT4 provide median, variance, mode calculations. Useful for analysis. + +- **rowid**: Special hidden column returning 64-bit signed row ID integer; useful for diagnostics. +- **last_insert_rowid()**: Function that returns last inserted row ID; captures autogenerated IDs. +- **total_changes()**: Shows the number of row changes from the most recent SQL statement; aids in auditing. +- **integrity_check**: PRAGMA command for database integrity check; detects corruption. +- **And more**: Includes additional statistical, diagnostic, and introspection capabilities. + +## Advanced Topics + +There are a few more advanced topics worth mentioning: + +### SQLite Extensions + +SQLite allows developers to create custom SQL functions, aggregates, collating sequences, and virtual table implementations. These “extensions” can be compiled into shared libraries and loaded at runtime to expand SQLite’s capabilities. + +### JSON Support + +SQLite has native support for JavaScript Object Notation (JSON) with functions like json_extract(), json_array(), and json_object(). This allows efficient storage and querying of JSON documents within an SQLite database. + +### Database Encryption + +SQLite Encryption Extension (SEE) can encrypt database files to protect sensitive data. Makes full-disk encryption easy. + + + +### Multi-Threading + +SQLite supports multi-threaded access with proper locking and isolation. This allows concurrent use by multiple processes and threads. + +### And More + +Spatialite support, alternative storage engines like LMDB, external content tables, C/C++ usage guides… the list goes on! SQLite is a versatile database. + +This covers the most essential SQLite commands and features, but there is always more to learn. SQLite’s documentation is excellent and fills in many additional details. With this foundation, you should feel comfortable using SQLite in your own projects. + + + +The key is to start simple, learn by example, and gradually work up to more advanced capabilities over time. Mastering both the SQL standard and sqlite3 dot commands will make you a pro at working with SQLite databases. + + + + + +Ray Ferrell + +Ray is a data nerd and a technical content creator for multiple websites. He enjoys writing on topics related to SQL databases, data science, machine learning, and more. When not working, you can find him spending quality time with his family and friends. + +**Previous:** [SQLite Create Table](https://sqldocs.org/sqlite/sqlite-create-table/) + +**Read next:** [SQLite Attach Database](https://sqldocs.org/sqlite/sqlite-attach-database/) + +**Important Links** + +[Terms of Use](https://sqldocs.org/terms/) + +[Privacy Policy](https://sqldocs.org/privacy) + +[Advertise](https://sqldocs.org/advertise/) + +**Company** + +[Home](https://sqldocs.org/) + +[About Us](https://sqldocs.org/about/) + +[Write for Us](https://sqldocs.org/write-for-us) + +[Careers](https://www.linkedin.com/company/sql-docs/jobs/) + +**Resources** + +[SQLite](https://sqldocs.org/sqlite/) + +[SQLite vs MySQL](https://sqldocs.org/sqlite/sqlite-vs-mysql/) + +[SQLite vs. PostgrSQL](https://sqldocs.org/sqlite/sqlite-vs-postgresql/) + +**Follow SQLDocs** + +[Twitter (X)](https://x.com/SQLDocsOrg) + +[LinkedIn](https://www.linkedin.com/company/sql-docs/) + +Copyright © 2022 – [SQL Documentation](https://sqldocs.org/) 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; + +# КУРСОРЫ (шаги) + +``` + diff --git a/SQL/НОРМАЛЬНЫЕ ФОРМЫ.md b/SQL/НОРМАЛЬНЫЕ ФОРМЫ.md new file mode 100644 index 0000000..5a18e5e --- /dev/null +++ b/SQL/НОРМАЛЬНЫЕ ФОРМЫ.md @@ -0,0 +1,41 @@ +#### *==Реляционная модель + +- Отношения -- таблицы +- Атрибуты -- поля таблицы +- Кортежи -- строки таблицы + + +#### *==Первая нормальная форма + +- нет дублирующихся строк +- все атрибуты атомарны +- нет повторяющихся атрибутоов с одинаковым смыслом + +![[Снимок экрана 2024-09-15 в 18.55.25.png]] + +![[Снимок экрана 2024-09-16 в 21.31.06.png]] + +#### *==Вторая нормальная форма + +- отношение находится в первой НФ +- есть первичный ключ +- все неключевые атрибуты функционально зависят от ключа целиком, но не от его части + +![[Снимок экрана 2024-09-15 в 19.02.02.png]] + +![[Снимок экрана 2024-09-16 в 21.36.18.png]] + +#### *==Третья нормальная форма + +- отношение находится во второй НФ +- неключевые атрибуты напрямую зависят только от РК, но не от других атрибутов + +![[Снимок экрана 2024-09-15 в 19.10.29.png]] + + +=========================================== + +![[Снимок экрана 2024-09-16 в 21.45.31.png]] + + +![[Снимок экрана 2024-09-16 в 21.45.54.png]]
\ No newline at end of file diff --git a/SQL/ШПАРГАЛКА SQLite.md b/SQL/ШПАРГАЛКА SQLite.md new file mode 100644 index 0000000..d6107c2 --- /dev/null +++ b/SQL/ШПАРГАЛКА SQLite.md @@ -0,0 +1,4139 @@ +- 09.02.2024 +- [DATA ANALYST](https://uproger.com/category/data-analyst/), [SQL](https://uproger.com/category/sql/) + + + +Все примеры изначально писались для СУБД SQLite, но почти всё из этого применимо также и к другим СУБД. + +Вначале идут очень простые запросы, с них можно начать новичкам. Если хочется чего-то более интересного — листайте вниз. Здесь есть и примеры довольно сложных запросов с агрегирующими функциями, триггерами, длинными подзапросами, с оконными функциями. Помимо этого, часть примеров посвящена работе с SQL в Python, используя `sqlite3`, `pandas`, `polars`. Этот список запросов с комментариями можно использовать как наглядное пособие для изучения SQL. + +Большинство советов я публиковал в своем [канале по анализу данных](https://t.me/+dXZpK8lQ0lY4YjAy), где вы найдете большое количество советов, инструментов и примеров с кодом. А [здесь большая полезная папка,](https://t.me/addlist/qht-ouKSGaQwNjcy) которую я собрал в которой куча полезного для работы с данными. + +Кстати, все эти примеры SQL заботливо собраны в одном архиве, вы можете [скачать](https://github.com/gvwilson/sql-tutorial/raw/main/sql-tutorial.zip) его и экспериментировать локально. После скачивания и разархивирования, у вас будет 3 группы файлов: + +- `./db/*.db` — базы данных SQLite, которые используются в примерах ниже +- `./src/*.*` — SQL-запросы, сценарии Python +- `./out/*.*` — ожидаемый результат для примеров + +### Выбираем все значения из таблички + +``` +SELECT * +FROM little_penguins; +``` + +``` +Adelie|Dream|37.2|18.1|178|3900|MALE +Adelie|Dream|37.6|19.3|181|3300|FEMALE +Gentoo|Biscoe|50|15.3|220|5550|MALE +Adelie|Torgersen|37.3|20.5|199|3775|MALE +Adelie|Biscoe|39.6|17.7|186|3500|FEMALE +Gentoo|Biscoe|47.7|15|216|4750|FEMALE +Adelie|Dream|36.5|18|182|3150|FEMALE +Gentoo|Biscoe|42|13.5|210|4150|FEMALE +Adelie|Torgersen|42.1|19.1|195|4000|MALE +Gentoo|Biscoe|54.3|15.7|231|5650|MALE +``` + +- ничего особенного, выбираем все записи из таблички `little_penguins` + +### Дополнительные команды SQL + +[src/admin_commands.sql](https://gvwilson.github.io/sql-tutorial/src/admin_commands.sql) + +``` +.headers on +.mode markdown +SELECT * +FROM little_penguins; +``` + +[out/admin_commands.out](https://gvwilson.github.io/sql-tutorial/out/admin_commands.out) + +``` +| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | +|---------|-----------|----------------|---------------|-------------------|-------------|--------| +| Adelie | Dream | 37.2 | 18.1 | 178 | 3900 | MALE | +| Adelie | Dream | 37.6 | 19.3 | 181 | 3300 | FEMALE | +| Gentoo | Biscoe | 50 | 15.3 | 220 | 5550 | MALE | +| Adelie | Torgersen | 37.3 | 20.5 | 199 | 3775 | MALE | +| Adelie | Biscoe | 39.6 | 17.7 | 186 | 3500 | FEMALE | +| Gentoo | Biscoe | 47.7 | 15 | 216 | 4750 | FEMALE | +| Adelie | Dream | 36.5 | 18 | 182 | 3150 | FEMALE | +| Gentoo | Biscoe | 42 | 13.5 | 210 | 4150 | FEMALE | +| Adelie | Torgersen | 42.1 | 19.1 | 195 | 4000 | MALE | +| Gentoo | Biscoe | 54.3 | 15.7 | 231 | 5650 | MALE | +``` + +- включаем заголовки и режим markdown; в `SQLite` подобные команды начинаются с `.`, а в PostgreSQL с `\` +- кстати, для просмотра дополнительной инфы или чтобы узнать, какие команды есть, используйте `.help` + +### Выбираем нужные столбцы + +[src/specify_columns.sql](https://gvwilson.github.io/sql-tutorial/src/specify_columns.sql) + +``` +SELECT species, + island, + sex +FROM little_penguins; +``` + +[out/specify_columns.out](https://gvwilson.github.io/sql-tutorial/out/specify_columns.out) + +``` +| species | island | sex | +|---------|-----------|--------| +| Adelie | Dream | MALE | +| Adelie | Dream | FEMALE | +| Gentoo | Biscoe | MALE | +| Adelie | Torgersen | MALE | +| Adelie | Biscoe | FEMALE | +| Gentoo | Biscoe | FEMALE | +| Adelie | Dream | FEMALE | +| Gentoo | Biscoe | FEMALE | +| Adelie | Torgersen | MALE | +| Gentoo | Biscoe | MALE | +``` + +- выбираем колонки `species`, `island`, `sex` из таблички `little_penguins` + +### Сортировка + +[src/sort.sql](https://gvwilson.github.io/sql-tutorial/src/sort.sql) + +``` +SELECT species, + sex, + island +FROM little_penguins +ORDER BY island ASC, + sex DESC; +``` + +[out/sort.out](https://gvwilson.github.io/sql-tutorial/out/sort.out) + +``` +| species | sex | island | +|---------|--------|-----------| +| Gentoo | MALE | Biscoe | +| Gentoo | MALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +| Adelie | MALE | Dream | +| Adelie | FEMALE | Dream | +| Adelie | FEMALE | Dream | +| Adelie | MALE | Torgersen | +| Adelie | MALE | Torgersen | +``` + +- выбираем столбцы `species`, `island`, `sex` из таблички `little_penguins` +- сортируем все значения из `island` в возрастающем порядке (от A к Z) +- строки с одинаковыми значениями `island` дополнительно сортируем по их значениям `sex` в обратном порядке, от большего к меньшему (от Z к A) + +### Ограничение выводимых записей + +- Full dataset has 344 rows + +[src/limit.sql](https://gvwilson.github.io/sql-tutorial/src/limit.sql) + +``` +SELECT species, + sex, + island +FROM penguins +ORDER BY species, + sex, + island +LIMIT 10; +``` + +[out/limit.out](https://gvwilson.github.io/sql-tutorial/out/limit.out) + +``` +| species | sex | island | +|---------|--------|-----------| +| Adelie | | Dream | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +``` + +- выбираем столбцы `species`, `sex`, `island` из таблички `penguins` +- сортируем по `species` в порядке возрастания, строки с одинаковым значением `species` сортируются по `sex`, с одинаковым `sex` дополнительно сортируются по `island` +- ну и выводим только первые 10 строк + +### Ещё некоторые параметры вывода + +[src/page.sql](https://gvwilson.github.io/sql-tutorial/src/page.sql) + +``` +SELECT species, + sex, + island +FROM penguins +ORDER BY species, + sex, + island +LIMIT 10 +OFFSET 3; +``` + +[out/page.out](https://gvwilson.github.io/sql-tutorial/out/page.out) + +``` +| species | sex | island | +|---------|--------|-----------| +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +| Adelie | FEMALE | Biscoe | +``` + +- `OFFSET` указывается после `LIMIT` и позволяет пропустить сколько-то первых строк, в данном случае пропущены 3 первых строки + +### Удаляем дубликаты + +[src/distinct.sql](https://gvwilson.github.io/sql-tutorial/src/distinct.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins; +``` + +[out/distinct.out](https://gvwilson.github.io/sql-tutorial/out/distinct.out) + +``` +| species | sex | island | +|-----------|--------|-----------| +| Adelie | MALE | Torgersen | +| Adelie | FEMALE | Torgersen | +| Adelie | | Torgersen | +| Adelie | FEMALE | Biscoe | +| Adelie | MALE | Biscoe | +| Adelie | FEMALE | Dream | +| Adelie | MALE | Dream | +| Adelie | | Dream | +| Chinstrap | FEMALE | Dream | +| Chinstrap | MALE | Dream | +| Gentoo | FEMALE | Biscoe | +| Gentoo | MALE | Biscoe | +| Gentoo | | Biscoe | +``` + +- `SELECT DISTINCT` — выбираем уникальные комбинации из столбцов `species`, `sex`, `island` + +### Фильтруем результаты + +[src/filter.sql](https://gvwilson.github.io/sql-tutorial/src/filter.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins +WHERE island = 'Biscoe'; +``` + +[out/filter.out](https://gvwilson.github.io/sql-tutorial/out/filter.out) + +``` +| species | sex | island | +|---------|--------|--------| +| Adelie | FEMALE | Biscoe | +| Adelie | MALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +| Gentoo | MALE | Biscoe | +| Gentoo | | Biscoe | +``` + +- выбираем уникальные комбинации значений `species`, `sex`, `island` из `penguins`, где значения поля `island` равно `Biscoe` + +### Более сложные условия фильтрации + +[src/filter_and.sql](https://gvwilson.github.io/sql-tutorial/src/filter_and.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins +WHERE island = 'Biscoe' + AND sex != 'MALE'; +``` + +[out/filter_and.out](https://gvwilson.github.io/sql-tutorial/out/filter_and.out) + +``` +| species | sex | island | +|---------|--------|--------| +| Adelie | FEMALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +``` + +- выбираем уникальные комбинации значений `species`, `sex`, `island` из `penguins`, где значения поля `island` равно `Biscoe`, а значения поля `sex` не равно `MALE` + +### Некоторые математические действия + +[src/calculations.sql](https://gvwilson.github.io/sql-tutorial/src/calculations.sql) + +``` +SELECT flipper_length_mm / 10.0, + body_mass_g / 1000.0 +FROM penguins +LIMIT 3; +``` + +[out/calculations.out](https://gvwilson.github.io/sql-tutorial/out/calculations.out) + +``` +| flipper_length_mm / 10.0 | body_mass_g / 1000.0 | +|--------------------------|----------------------| +| 18.1 | 3.75 | +| 18.6 | 3.8 | +| 19.5 | 3.25 | +``` + +- выводим 3 первых строки значений `flipper_length_mm`, делённых на 10.0, и значений `body_mass_g`, делённых на 1000.0 + +### Переименовываем столбцы + +[src/rename_columns.sql](https://gvwilson.github.io/sql-tutorial/src/rename_columns.sql) + +``` +SELECT flipper_length_mm / 10.0 AS flipper_cm, + body_mass_g / 1000.0 AS weight_kg, + island AS where_found +FROM penguins +LIMIT 3; +``` + +[out/rename_columns.out](https://gvwilson.github.io/sql-tutorial/out/rename_columns.out) + +``` +| flipper_cm | weight_kg | where_found | +|------------|-----------|-------------| +| 18.1 | 3.75 | Torgersen | +| 18.6 | 3.8 | Torgersen | +| 19.5 | 3.25 | Torgersen | +``` + +- делим значения `flipper_length_mm` на 10.0, делим значения `body_mass_g` на 1000.0 +- переименовываем столбцы `flipper_length_mm` — в `flipper_cm`, `body_mass_g` — в `weight_kg`, `island` — в `where_found` +- выводим первые 3 строки + +**Взаимосвязь рассмотренных понятий SQL можно показать так:** + + + +concept map: selection + +### Подсчёт с пропущенными значениями + +[src/show_missing_values.sql](https://gvwilson.github.io/sql-tutorial/src/show_missing_values.sql) + +``` +SELECT flipper_length_mm / 10.0 AS flipper_cm, + body_mass_g / 1000.0 AS weight_kg, + island AS where_found +FROM penguins +LIMIT 5; +``` + +[out/show_missing_values.out](https://gvwilson.github.io/sql-tutorial/out/show_missing_values.out) + +``` +| flipper_cm | weight_kg | where_found | +|------------|-----------|-------------| +| 18.1 | 3.75 | Torgersen | +| 18.6 | 3.8 | Torgersen | +| 19.5 | 3.25 | Torgersen | +| | | Torgersen | +| 19.3 | 3.45 | Torgersen | +``` + +- делим значения из `flipper_length_mm` на 10, затем присваиваем результаты новому столбцу `flipper_cm` +- делим значения из столбца `body_mass_g` на 1000 и затем присваивание результатов новому столбцу `weight_kg` +- переименовываем `island` в `where_found` + +### Вывод с условием при помощи WHERE + +- Repeated from above so it doesn’t count against our query limit + +[src/filter.sql](https://gvwilson.github.io/sql-tutorial/src/filter.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins +WHERE island = 'Biscoe'; +``` + +[out/filter.out](https://gvwilson.github.io/sql-tutorial/out/filter.out) + +``` +| species | sex | island | +|---------|--------|--------| +| Adelie | FEMALE | Biscoe | +| Adelie | MALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +| Gentoo | MALE | Biscoe | +| Gentoo | | Biscoe | +``` + +- выбираем столбцы `species`, `sex`, `island` +- выводим все записи из `penguins`, где значение `island` равно `'Biscoe'` + +[src/null_equality.sql](https://gvwilson.github.io/sql-tutorial/src/null_equality.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins +WHERE island = 'Biscoe' + AND sex = 'FEMALE'; +``` + +[out/null_equality.out](https://gvwilson.github.io/sql-tutorial/out/null_equality.out) + +``` +| species | sex | island | +|---------|--------|--------| +| Adelie | FEMALE | Biscoe | +| Gentoo | FEMALE | Biscoe | +``` + +- выводим все записи из `penguins`, где значение `island` равно `'Biscoe'` и значение `sex` равно `'FEMALE'` + +### Условие с отрицанием + +- условие с оператором отрицания `!=` тоже без проблем работает + +[src/null_inequality.sql](https://gvwilson.github.io/sql-tutorial/src/null_inequality.sql) + +``` +SELECT DISTINCT species, + sex, + island +FROM penguins +WHERE island = 'Biscoe' + AND sex != 'FEMALE'; +``` + +[out/null_inequality.out](https://gvwilson.github.io/sql-tutorial/out/null_inequality.out) + +``` +| species | sex | island | +|---------|------|--------| +| Adelie | MALE | Biscoe | +| Gentoo | MALE | Biscoe | +``` + +### Выбираем NULL значения + +[src/safe_null_equality.sql](https://gvwilson.github.io/sql-tutorial/src/safe_null_equality.sql) + +``` +SELECT species, + sex, + island +FROM penguins +WHERE sex IS NULL; +``` + +[out/safe_null_equality.out](https://gvwilson.github.io/sql-tutorial/out/safe_null_equality.out) + +``` +| species | sex | island | +|---------|-----|-----------| +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Torgersen | +| Adelie | | Dream | +| Gentoo | | Biscoe | +| Gentoo | | Biscoe | +| Gentoo | | Biscoe | +| Gentoo | | Biscoe | +| Gentoo | | Biscoe | +``` + +- выбираем строки со значениями `species`, `sex`, `island` из таблички `penguins`, где значения `sex` нет (`NULL`) + +**Вот так можно показать связь понятий SQL, которые мы рассмотрели выше:** + + + +concept map: null + +### Агрегирование в SQL + +[src/simple_sum.sql](https://gvwilson.github.io/sql-tutorial/src/simple_sum.sql) + +``` +SELECT sum(body_mass_g) AS total_mass +FROM penguins; +``` + +[out/simple_sum.out](https://gvwilson.github.io/sql-tutorial/out/simple_sum.out) + +``` +| total_mass | +|------------| +| 1437000 | +``` + +- суммируем все значения колонки `body_mass_g`, сохраняем в новый столбец `total_mass` + +### Распространённые агрегирующие функции в SQL + +[src/common_aggregations.sql](https://gvwilson.github.io/sql-tutorial/src/common_aggregations.sql) + +``` +SELECT MAX(bill_length_mm) AS longest_bill, + MIN(flipper_length_mm) AS shortest_flipper, + AVG(bill_length_mm) / AVG(bill_depth_mm) AS weird_ratio +FROM penguins; +``` + +[out/common_aggregations.out](https://gvwilson.github.io/sql-tutorial/out/common_aggregations.out) + +``` +| longest_bill | shortest_flipper | weird_ratio | +|--------------|------------------|------------------| +| 59.6 | 172 | 2.56087082530644 | +``` + +- находим максимальное значение из столбца `bill_length_mm`, записываем это значение как `longest_bill` +- аналогично находим минимальное из `flipper_length_mm`, находим среднее из `bill_length_mm`, среднее из `bill_depth_mm` + +### Подсчёт значений при помощи COUNT + +[src/count_behavior.sql](https://gvwilson.github.io/sql-tutorial/src/count_behavior.sql) + +``` +SELECT COUNT(*) AS count_star, + COUNT(sex) AS count_specific, + COUNT(DISTINCT sex) AS count_distinct +FROM penguins; +``` + +[out/count_behavior.out](https://gvwilson.github.io/sql-tutorial/out/count_behavior.out) + +``` +| count_star | count_specific | count_distinct | +|------------|----------------|----------------| +| 344 | 333 | 2 | +``` + +- `COUNT(*)` — считаем все значения из `count_star` +- `COUNT(sex)` — считаем все значения из столбца `sex` +- `COUNT(DISTINCT sex)` — считаем уникальные значения из `sex` (очевидно их 2: `MALE`, `FEMALE`) +- записываем эти 3 числа как `count_star`, `count_specific`, `count_distinct` соответственно + +### Группировка + +[src/simple_group.sql](https://gvwilson.github.io/sql-tutorial/src/simple_group.sql) + +``` +SELECT AVG(body_mass_g) AS average_mass_g +FROM penguins +GROUP BY sex; +``` + +[out/simple_group.out](https://gvwilson.github.io/sql-tutorial/out/simple_group.out) + +``` +| average_mass_g | +|------------------| +| 4005.55555555556 | +| 3862.27272727273 | +| 4545.68452380952 | +``` + +- из таблички `penguins` находим среднее всех значений `body_mass_g`, сохраняем как `average_mass_g` +- группируем по значениям `sex` (группы `FEMALE`, `MALE`, `NULL`) + +### Как себя ведут неагрегированные столбцы + +[src/unaggregated_columns.sql](https://gvwilson.github.io/sql-tutorial/src/unaggregated_columns.sql) + +``` +SELECT sex, + AVG(body_mass_g) AS average_mass_g +FROM penguins +GROUP BY sex; +``` + +[out/unaggregated_columns.out](https://gvwilson.github.io/sql-tutorial/out/unaggregated_columns.out) + +``` +| sex | average_mass_g | +|--------|------------------| +| | 4005.55555555556 | +| FEMALE | 3862.27272727273 | +| MALE | 4545.68452380952 | +``` + +- для того, чтобы было видно названия отдельных групп, выбираем не только среднее `AVG(body_mass_g)`, но и `sex` +- видим 3 группы: `NULL`, `FEMALE`, `MALE` + +### Выбор нужных столбцов для агрегирования + +[src/arbitrary_in_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/arbitrary_in_aggregation.sql) + +``` +SELECT sex, + body_mass_g +FROM penguins +GROUP BY sex; +``` + +[out/arbitrary_in_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/arbitrary_in_aggregation.out) + +``` +| sex | body_mass_g | +|--------|-------------| +| | | +| FEMALE | 3800 | +| MALE | 3750 | +``` + +- здесь у нас популярная ошибка, мы просто выбираем `body_mass_g`, а не находим среднее, поэтому SQL выбирает любые значения из `body_mass_g`. Аккуратнее) + +### Фильтрация агрегированных значений + +[src/filter_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/filter_aggregation.sql) + +``` +SELECT sex, + AVG(body_mass_g) AS average_mass_g +FROM penguins +GROUP BY sex +HAVING average_mass_g > 4000.0; +``` + +[out/filter_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/filter_aggregation.out) + +``` +| sex | average_mass_g | +|------|------------------| +| | 4005.55555555556 | +| MALE | 4545.68452380952 | +``` + +- здесь мы используем `HAVING` вместо `WHERE` (эффект тот же самый), оставляем только те значения из `average_mass_g`, которые больше 4000 + +### Читабельный вывод + +[src/readable_aggregation.sql](https://gvwilson.github.io/sql-tutorial/src/readable_aggregation.sql) + +``` +SELECT sex, + ROUND(AVG(body_mass_g), 1) AS average_mass_g +FROM penguins +GROUP BY sex +HAVING average_mass_g > 4000.0; +``` + +[out/readable_aggregation.out](https://gvwilson.github.io/sql-tutorial/out/readable_aggregation.out) + +``` +| sex | average_mass_g | +|------|----------------| +| | 4005.6 | +| MALE | 4545.7 | +``` + +- округляем среднее `AVG(body_mass_g` до 1 знака после запятой, используя `ROUND` + +### Фильтрация входных данных + +[src/filter_aggregate_inputs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_aggregate_inputs.sql) + +``` +SELECT sex, + ROUND( + AVG(body_mass_g) FILTER (WHERE body_mass_g < 4000.0), + 1) + AS average_mass_g +FROM penguins +GROUP BY sex; +``` + +[out/filter_aggregate_inputs.out](https://gvwilson.github.io/sql-tutorial/out/filter_aggregate_inputs.out) + +``` +| sex | average_mass_g | +|--------|----------------| +| | 3362.5 | +| FEMALE | 3417.3 | +| MALE | 3752.5 | +``` + +- при помощи `FILTER` мы находим среднее только тех значений `body_mass_g`, которые меньше 4000 +- округляем до 1 знака после запятой, сохраняем в столбец `average_mass_g` +- группируем по `sex` + +**Вот так выглядит связь основных понятий, которые мы только что обсуждали:** + + + +concept map: aggregation + +**Кстати, вот так выглядит создание БД в оперативной памяти:** + +[src/in_memory_db.sh](https://gvwilson.github.io/sql-tutorial/src/in_memory_db.sh) + +``` +sqlite3 :memory: +``` + +- запускаем интерактивную оболочку SQLite, создаём новую базу данных в оперативной памяти для более быстрой работы + +### Создание табличек + +[src/create_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/create_work_job.sql) + +``` +CREATE TABLE job (name text NOT NULL, + billable real NOT NULL); + +CREATE TABLE work (person text NOT NULL, + job text NOT NULL); +``` + +- создаём таблицу `job` со столбцами: `name` — столбец текстовых значений, не может быть пустым (`NOT NULL`), `billable` — содержит вещественные числа, не может быть пустым +- создаём табличку `work` со столбцами: `person` — текстовый, не может быть пустым, `job` — текстовый, не может быть пустым + +### Вставляем данные + +[src/populate_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/populate_work_job.sql) + +``` +INSERT INTO job +VALUES ('calibrate', 1.5), + ('clean', 0.5); + +INSERT INTO work +VALUES ('mik', 'calibrate'), + ('mik', 'clean'), + ('mik', 'complain'), + ('po', 'clean'), + ('po', 'complain'), + ('tay', 'complain'); +``` + +[out/insert_values.out](https://gvwilson.github.io/sql-tutorial/out/insert_values.out) + +``` +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +| clean | 0.5 | + +| person | job | +|--------|-----------| +| mik | calibrate | +| mik | clean | +| mik | complain | +| po | clean | +| po | complain | +| tay | complain | +``` + +- ничего особенного, заполняем табличку `job` парами `name`–`billable`, и так же заполняем табличку `work` парами `person`–`job` + +### Обновляем строки + +[src/update_work_job.sql](https://gvwilson.github.io/sql-tutorial/src/update_work_job.sql) + +``` +UPDATE work +SET person = "tae" +WHERE person = "tay"; +``` + +[out/update_rows.out](https://gvwilson.github.io/sql-tutorial/out/update_rows.out) + +``` +| person | job | +|--------|-----------| +| mik | calibrate | +| mik | clean | +| mik | complain | +| po | clean | +| po | complain | +| tae | complain | +``` + +- меняем все записи `"tay"` на `"tae"` + +### Удаляем строки + +[src/delete_rows.sql](https://gvwilson.github.io/sql-tutorial/src/delete_rows.sql) + +``` +DELETE FROM work +WHERE person = "tae"; +SELECT * +FROM work; +``` + +[out/delete_rows.out](https://gvwilson.github.io/sql-tutorial/out/delete_rows.out) + +``` +| person | job | +|--------|-----------| +| mik | calibrate | +| mik | clean | +| mik | complain | +| po | clean | +| po | complain | +``` + +- удаляем все строки, где значение `person` равно `"tae"` + +### Резервное копирование + +[src/backing_up.sql](https://gvwilson.github.io/sql-tutorial/src/backing_up.sql) + +``` +CREATE TABLE backup (person text NOT NULL, + job text NOT NULL); + +INSERT INTO backup +SELECT person, + job +FROM work +WHERE person = 'tae'; + +DELETE FROM work +WHERE person = 'tae'; + +SELECT * +FROM backup; +``` + +[out/backing_up.out](https://gvwilson.github.io/sql-tutorial/out/backing_up.out) + +``` +| person | job | +|--------|----------| +| tae | complain | +``` + +- создаём табличку `backup` c текстовыми столбцами `person` и `job` +- помещаем внутрь `backup` значения столбцов `person` и `job` из таблицы `work`, где значения столбца `person` равно `'tae'` +- удаляем из `work` все записи со значением `person` равным `'tae'` +- отображаем записи таблички `backup` + +**Вот так выглядит связь основных понятий, которые мы только что обсуждали:** + + + +concept map: data definition and modification + +### Объединение табличек при помощи JOIN + +[src/cross_join.sql](https://gvwilson.github.io/sql-tutorial/src/cross_join.sql) + +``` +SELECT * +FROM work +CROSS JOIN job; +``` + +[out/cross_join.out](https://gvwilson.github.io/sql-tutorial/out/cross_join.out) + +``` +| person | job | name | billable | +|--------|-----------|-----------|----------| +| mik | calibrate | calibrate | 1.5 | +| mik | calibrate | clean | 0.5 | +| mik | clean | calibrate | 1.5 | +| mik | clean | clean | 0.5 | +| mik | complain | calibrate | 1.5 | +| mik | complain | clean | 0.5 | +| po | clean | calibrate | 1.5 | +| po | clean | clean | 0.5 | +| po | complain | calibrate | 1.5 | +| po | complain | clean | 0.5 | +| tay | complain | calibrate | 1.5 | +| tay | complain | clean | 0.5 | +``` + +- делаем `CROSS JOIN` для 2 таблиц `work` и `job` — все возможные комбинации строк из этих таблиц (если в `work` 3 строки, а в `job` 4 строки, то результат будет иметь 4 ⋅ 3 = 12 строк) + +### INNER JOIN + +[src/inner_join.sql](https://gvwilson.github.io/sql-tutorial/src/inner_join.sql) + +``` +SELECT * +FROM work +INNER JOIN job ON work.job = job.name; +``` + +[out/inner_join.out](https://gvwilson.github.io/sql-tutorial/out/inner_join.out) + +``` +| person | job | name | billable | +|--------|-----------|-----------|----------| +| mik | calibrate | calibrate | 1.5 | +| mik | clean | clean | 0.5 | +| po | clean | clean | 0.5 | +``` + +- объединяем 2 таблицы `work` и `job` — берём те записи, где значение `job` из `work` совпадает со значением `name` из `job` + +### Агрегирование объединённых через JOIN записей + +[src/aggregate_join.sql](https://gvwilson.github.io/sql-tutorial/src/aggregate_join.sql) + +``` +SELECT work.person, + SUM(job.billable) AS pay +FROM work +INNER JOIN job ON work.job = job.name +GROUP BY work.person; +``` + +[out/aggregate_join.out](https://gvwilson.github.io/sql-tutorial/out/aggregate_join.out) + +``` +| person | pay | +|--------|-----| +| mik | 2.0 | +| po | 0.5 | +``` + +- объединяем те строки таблиц `work` и `job`, где значение `job` в таблице `work` соответствует значению `name` в `job` +- суммируем значения `billable` из таблицы `job` для каждого значения `person` из таблицы `work` +- группируем результаты по значениям `person` из `work` + +### LEFT JOIN + +[src/left_join.sql](https://gvwilson.github.io/sql-tutorial/src/left_join.sql) + +``` +SELECT * +FROM work +LEFT JOIN job ON work.job = job.name; +``` + +[out/left_join.out](https://gvwilson.github.io/sql-tutorial/out/left_join.out) + +``` +| person | job | name | billable | +|--------|-----------|-----------|----------| +| mik | calibrate | calibrate | 1.5 | +| mik | clean | clean | 0.5 | +| mik | complain | | | +| po | clean | clean | 0.5 | +| po | complain | | | +| tay | complain | | | +``` + +- склеиваем таблицы `work` и `job` по соответствующим значениям столбца `job` +- если в таблице `work` есть строки, для которых нет совпадений в таблице `job`, то они все равно будут включены в результат с пустыми (`NULL`) значениями +- использование `LEFT JOIN` гарантирует, что все строки из левой таблицы `work` будут включены в результат, независимо от наличия совпадающих строк в правой таблице `job` + +### Агрегирование данных, собранных через LEFT JOIN + +[src/aggregate_left_join.sql](https://gvwilson.github.io/sql-tutorial/src/aggregate_left_join.sql) + +``` +SELECT work.person, + sum(job.billable) AS pay +FROM work +LEFT JOIN job ON work.job = job.name +GROUP BY work.person; +``` + +[out/aggregate_left_join.out](https://gvwilson.github.io/sql-tutorial/out/aggregate_left_join.out) + +``` +| person | pay | +|--------|-----| +| mik | 2.0 | +| po | 0.5 | +| tay | | +``` + +- вычисляем сумму значений столбца `billable` из `job`, сохраняем как `pay` +- используем `LEFT JOIN`, чтобы гарантированно включить все строки из `work` в `job` +- группируем по столбцу `person` из `work` + +**Вот так выглядит связь основных понятий, которые мы только что обсуждали:** + + + +concept map: join + +### Объединение значений + +[src/coalesce.sql](https://gvwilson.github.io/sql-tutorial/src/coalesce.sql) + +``` +SELECT work.person, + COALESCE(SUM(job.billable), 0.0) AS pay +FROM work +LEFT JOIN job ON work.job = job.name +GROUP BY work.person; +``` + +[out/coalesce.out](https://gvwilson.github.io/sql-tutorial/out/coalesce.out) + +``` +| person | pay | +|--------|-----| +| mik | 2.0 | +| po | 0.5 | +| tay | 0.0 | +``` + +- `COALESCE` используется для замены `NULL` на 0.0, если сумма `billable` для данного `person` равна `NULL` +- `LEFT JOIN` включает все записи из `work` и только соответствующие записи из `job` +- группируем по значениям столбца `person` из `work` + +### SELECT DISTINCT и условие WHERE + +[src/negate_incorrectly.sql](https://gvwilson.github.io/sql-tutorial/src/negate_incorrectly.sql) + +``` +SELECT DISTINCT person +FROM work +WHERE job != 'calibrate'; +``` + +[out/negate_incorrectly.out](https://gvwilson.github.io/sql-tutorial/out/negate_incorrectly.out) + +``` +| person | +|--------| +| mik | +| po | +| tay | +``` + +- выбираем уникальные значения из столбца `person`, где поле `job` не равно `calibrate` + +### Использование набора в условии WHERE при помощи IN + +[src/set_membership.sql](https://gvwilson.github.io/sql-tutorial/src/set_membership.sql) + +``` +SELECT * +FROM work +WHERE person NOT IN ('mik', + 'tay'); +``` + +[out/set_membership.out](https://gvwilson.github.io/sql-tutorial/out/set_membership.out) + +``` +| person | job | +|--------|----------| +| po | clean | +| po | complain | +``` + +- выбираем все строки из `work`, где `person` не равно `'mik'` и не равно `'tay'` + +### Подзапросы + +[src/subquery_set.sql](https://gvwilson.github.io/sql-tutorial/src/subquery_set.sql) + +``` +SELECT DISTINCT person +FROM work +WHERE person not in + (SELECT DISTINCT person + FROM work + WHERE job = 'calibrate'); +``` + +[out/subquery_set.out](https://gvwilson.github.io/sql-tutorial/out/subquery_set.out) + +``` +| person | +|--------| +| po | +| tay | +``` + +- внутренний подзапрос выбирает уникальные значения столбца `person` из `work`, где в поле `job` стоит `'calibrate'` +- внешний, главный запрос выбирает те уникальные значения `person`, где `person` не равно значениям из внутренного подзапроса + +### Автоикремент и PRIMARY KEY + +[src/autoincrement.sql](https://gvwilson.github.io/sql-tutorial/src/autoincrement.sql) + +``` +CREATE TABLE person (ident integer PRIMARY KEY autoincrement, + name text NOT NULL); +INSERT INTO person +VALUES (NULL, 'mik'), + (NULL, 'po'), + (NULL, 'tay'); + +SELECT * +FROM person; +INSERT INTO person +VALUES (1, "prevented"); +``` + +[out/autoincrement.out](https://gvwilson.github.io/sql-tutorial/out/autoincrement.out) + +``` +| ident | name | +|-------|------| +| 1 | mik | +| 2 | po | +| 3 | tay | +Runtime error near line 12: UNIQUE constraint failed: person.ident (19) +``` + +- создаём табличку `person` с 2 столбцами: `ident` с целочисленными значениями, `name` с текстовыми значениями; столбец `ident` устанавливаем как `PRIMARY KEY`, включаем автоматическое инкрементирование значений +- помещаем в таблицу `person` 3 пары `ident`–`name` +- при попытке добавить ещё одну пару `(1, "prevented")` возникает ошибка, поскольку уже существует строка с `indent` равным 1 + +**Внутренняя табличка:** + +[src/sequence_table.sql](https://gvwilson.github.io/sql-tutorial/src/sequence_table.sql) + +``` +SELECT * +FROM sqlite_sequence; +``` + +[out/sequence_table.out](https://gvwilson.github.io/sql-tutorial/out/sequence_table.out) + +``` +| name | seq | +|--------|-----| +| person | 3 | +``` + +- выводим все текущие значения автоинкрементных счетчиков для таблиц в БД SQLite + +### Изменение таблички при помощи ALTER + +[src/alter_tables.sql](https://gvwilson.github.io/sql-tutorial/src/alter_tables.sql) + +``` +ALTER TABLE job ADD ident integer NOT NULL DEFAULT -1; + +UPDATE job +SET ident = 1 +WHERE name = 'calibrate'; + +UPDATE job +SET ident = 2 +WHERE name = 'clean'; + +SELECT * +FROM job; +``` + +[out/alter_tables.out](https://gvwilson.github.io/sql-tutorial/out/alter_tables.out) + +``` +| name | billable | ident | +|-----------|----------|-------| +| calibrate | 1.5 | 1 | +| clean | 0.5 | 2 | +``` + +- добавляем новый столбец `ident` в табличку `job`; столбец заполняется целыми числами, не может быть пустым; ставим значение по умолчанию `-1` для этого столбца +- делаем значение столбца `ident` равным 1 там, где `name` равен `'calibrate'` +- устанавливаем значение `ident` равным 2 для строки, где `name` равен `clean` + +### Создание новой таблички на базе старой + +[src/insert_select.sql](https://gvwilson.github.io/sql-tutorial/src/insert_select.sql) + +``` +CREATE TABLE new_work (person_id integer NOT NULL, + job_id integer NOT NULL, + FOREIGN key(person_id) REFERENCES person(ident), + FOREIGN key(job_id) REFERENCES job(ident)); + +INSERT INTO new_work +SELECT person.ident AS person_id, + job.ident AS job_id +FROM (person + JOIN work + ON person.name = work.person) + +JOIN job ON job.name = work.job; + +SELECT * +FROM new_work; +``` + +[out/insert_select.out](https://gvwilson.github.io/sql-tutorial/out/insert_select.out) + +``` +| person_id | job_id | +|-----------|--------| +| 1 | 1 | +| 1 | 2 | +| 2 | 2 | +``` + +- создаём таблицу `new_work` с 2 целочисленными столбцами: `person_id` и `job_id`; оба столбца не могут быть пустыми +- 2 `FOREIGN KEY` ограничения добавляются, чтобы связать столбцы `person_id` и `job_id` новой таблицы `new_work` с соответствующими столбцами `ident` в таблицах `person` и `job` +- добавляем данные в таблицу `new_work`, используя результат запроса `SELECT` +- `FROM (person JOIN work ON person.name = work.person)` — данные будут выбраны из результатов соединения таблиц `person` и `work` по условию равенства значений столбца `name` в таблице `person` и столбца `person` в таблице `work` +- `JOIN job ON job.name = work.job` — результаты предыдущего соединения будут дополнительно соединены с таблицей `job` по условию равенства значений столбца `name` в таблице `job` и столбца `job`в `work` + +### Удаление таблички + +[src/drop_table.sql](https://gvwilson.github.io/sql-tutorial/src/drop_table.sql) + +``` +DROP TABLE work; +ALTER TABLE new_work RENAME TO work; +``` + +- удаляем `work` из БД +- изменяем имя таблички `new_work` на `work` + +[out/drop_table.out](https://gvwilson.github.io/sql-tutorial/out/drop_table.out) + +``` +CREATE TABLE job (ident integer PRIMARY KEY autoincrement, + name text NOT NULL, + billable real NOT NULL); +CREATE TABLE sqlite_sequence(name, + seq); + +CREATE TABLE person (ident integer PRIMARY KEY autoincrement, + name text NOT NULL); + +CREATE TABLE IF NOT EXISTS "work" (person_id integer NOT NULL, + job_id integer NOT NULL, + FOREIGN key(person_id) REFERENCES person(ident), + FOREIGN key(job_id) REFERENCES job(ident)); +``` + +- создаём таблицу `job` с 3 колонками: `ident` хранит целые числа, используется в качестве первичного ключа (`PRIMARY KEY`) и автоматически увеличивается (autoincrement);`name` текстовый столбец, не может быть пустым (`NOT NULL`);`billable` — столбец вещественных чисел, не может быть пустым +- создаём `sqlite_sequence` с 2 колонками: `name` и `seq` +- создаём таблицу `person` с 2 колонками: `ident` — хранит целые числа, используется в качестве первичного ключа и автоматически увеличивается (autoincrement), `name` — хранит текст, не может быть пустым +- создаём `work` с 4 колонками: `person_id` – хранит целые числа, не может быть пустым; аналогичный столбец `job_id` +- устанавливаем внешние ключи, связывающие `person_id` с `ident` в таблице `person` и `job_id` с `ident` в таблице `job` + +### Сравнение отдельных значений с агрегированными + +[src/compare_individual_aggregate.sql](https://gvwilson.github.io/sql-tutorial/src/compare_individual_aggregate.sql) + +``` +SELECT body_mass_g +FROM penguins +WHERE body_mass_g > (SELECT AVG(body_mass_g) + FROM penguins) +LIMIT 5; +``` + +[out/compare_individual_aggregate.out](https://gvwilson.github.io/sql-tutorial/out/compare_individual_aggregate.out) + +``` +| body_mass_g | +|-------------| +| 4675 | +| 4250 | +| 4400 | +| 4500 | +| 4650 | +``` + +- выбираем только те строки, где значение в столбце `body_mass_g` больше, чем среднее значение `body_mass_g` по всем строкам в таблице `penguins` +- ну и выводим только первые 5 строк + +### Сравнение отдельных значений с агрегированными внутри групп + +[src/compare_within_groups.sql](https://gvwilson.github.io/sql-tutorial/src/compare_within_groups.sql) + +``` +SELECT penguins.species, + penguins.body_mass_g, + Round(averaged.avg_mass_g, 1) AS avg_mass_g +FROM penguins + JOIN (SELECT species, + Avg(body_mass_g) AS avg_mass_g + FROM penguins + GROUP BY species) AS averaged + ON penguins.species = averaged.species +WHERE penguins.body_mass_g > averaged.avg_mass_g +LIMIT 5; +``` + +[out/compare_within_groups.out](https://gvwilson.github.io/sql-tutorial/out/compare_within_groups.out) + +``` +| species | body_mass_g | avg_mass_g | +|---------|-------------|------------| +| Adelie | 3750 | 3700.7 | +| Adelie | 3800 | 3700.7 | +| Adelie | 4675 | 3700.7 | +| Adelie | 4250 | 3700.7 | +| Adelie | 3800 | 3700.7 | +``` + +- выбираем столбцы `species` и `body_mass_g` из таблицы `penguins` +- вычисляем среднюю массу для каждого вида пингвина, округляем до 1 знака после запятой, используя подзапрос, который связывается с исходной таблицей `penguins` по полю `species` +- используя результаты подзапроса, фильтруем только те записи, где масса пингвина больше средней массы для его вида +- выводим только первые 5 записей + +### CTE — табличные выражения + +[src/common_table_expressions.sql](https://gvwilson.github.io/sql-tutorial/src/common_table_expressions.sql) + +``` +WITH grouped AS + (SELECT species, + avg(body_mass_g) AS avg_mass_g + FROM penguins + GROUP BY species) +SELECT penguins.species, + penguins.body_mass_g, + round(grouped.avg_mass_g, 1) AS avg_mass_g +FROM penguins +JOIN grouped +WHERE penguins.body_mass_g > grouped.avg_mass_g +LIMIT 5; +``` + +[out/common_table_expressions.out](https://gvwilson.github.io/sql-tutorial/out/common_table_expressions.out) + +``` +| species | body_mass_g | avg_mass_g | +|---------|-------------|------------| +| Adelie | 3750 | 3700.7 | +| Adelie | 3800 | 3700.7 | +| Adelie | 4675 | 3700.7 | +| Adelie | 4250 | 3700.7 | +| Adelie | 3800 | 3700.7 | +``` + +- создаём табличку `grouped` (с помощью `WITH`), которая содержит среднюю массу тела пингвинов (`AVG(body_mass_g)`) для каждого вида из `penguins` (`GROUP BY species`) +- из `penguins` выбираем такие столбцы: `species`, `body_mass_g`; и из из общей таблицы `grouped`выбираем `avg_mass_g`, округлённое до 1 знака +- объединяем `penguins` с общей таблицей `grouped` (через `JOIN`); для каждого пингвина будет найдена соответствующая средняя масса тела для его вида +- `WHERE` — фильтруем; оставляем только тех, у которых масса тела больше средней массы их вида +- выводим только первые 5 строк + +### Смотрим план запроса с помощью EXPLAIN + +[src/explain_query_plan.sql](https://gvwilson.github.io/sql-tutorial/src/explain_query_plan.sql) + +``` +EXPLAIN query PLAN +SELECT species, + AVG(body_mass_g) +FROM penguins +GROUP BY species; +``` + +[out/explain_query_plan.out](https://gvwilson.github.io/sql-tutorial/out/explain_query_plan.out) + +``` +QUERY PLAN +|--SCAN penguins +`--USE TEMP B-TREE FOR GROUP BY +``` + +- `EXPLAIN query PLAN` — получаем план выполнения запроса, как будет выполнен запрос в базе данных +- выбираем столбец `species`, вычисляем среднее значение столбца body_mass_g для каждого вида из `penguins` +- `GROUP BY species` — группируем результаты по столбцу `species` + +### Нумеруем строки + +- каждая таблица имеет специальный столбец `rowid` с уникальными числовыми идентификаторами + +[src/rowid.sql](https://gvwilson.github.io/sql-tutorial/src/rowid.sql) + +``` +SELECT rowid, species, island +FROM penguins +LIMIT 5; +``` + +[out/rowid.out](https://gvwilson.github.io/sql-tutorial/out/rowid.out) + +``` +| rowid | species | island | +|-------|---------|-----------| +| 1 | Adelie | Torgersen | +| 2 | Adelie | Torgersen | +| 3 | Adelie | Torgersen | +| 4 | Adelie | Torgersen | +| 5 | Adelie | Torgersen | +``` + +### Условия if-else + +[src/if_else.sql](https://gvwilson.github.io/sql-tutorial/src/if_else.sql) + +``` +WITH sized_penguins AS + (SELECT species, + iif(body_mass_g < 3500, 'small', 'large') AS size + FROM penguins) +SELECT species, + size, + count(*) AS num +FROM sized_penguins +GROUP BY species, + size +ORDER BY species, + num; +``` + +[out/if_else.out](https://gvwilson.github.io/sql-tutorial/out/if_else.out) + +``` +| species | size | num | +|-----------|-------|-----| +| Adelie | small | 54 | +| Adelie | large | 98 | +| Chinstrap | small | 17 | +| Chinstrap | large | 51 | +| Gentoo | large | 124 | +``` + +- создаём временную таблицу `sized_penguins`, которая содержит два столбца: `species` и `size` +- `size` определяется на основе условия: если `body_mass_g` меньше 3500, то он считается `'small'`, в противном случае – `'large'` +- выбираем столбцы `species` и `size` из временной таблицы `sized_penguins`, а подсчитываем количество записей для каждой комбинации `species` и `size`, используя функцию `count(*)` +- группируем данные (`GROUP BY`) по `species` и `size` + +### Выбираем с помощью SELECT и CASE + +А если нам нужны маленькие, средние и большие? + +Можно вложить if, но он быстро становится нечитаемым + +[src/case_when.sql](https://gvwilson.github.io/sql-tutorial/src/case_when.sql) + +``` +WITH sized_penguins AS + (SELECT species, + CASE + WHEN body_mass_g < 3500 THEN 'small' + WHEN body_mass_g < 5000 THEN 'medium' + ELSE 'large' + END AS SIZE + FROM penguins) +SELECT species, + SIZE, + count(*) AS num +FROM sized_penguins +GROUP BY species, + SIZE +ORDER BY species, + num; +``` + +[out/case_when.out](https://gvwilson.github.io/sql-tutorial/out/case_when.out) + +``` +| species | size | num | +|-----------|--------|-----| +| Adelie | large | 1 | +| Adelie | small | 54 | +| Adelie | medium | 97 | +| Chinstrap | small | 17 | +| Chinstrap | medium | 51 | +| Gentoo | medium | 56 | +| Gentoo | large | 68 | +``` + +- в блоке `WITH` создаём набор данных с именем `sized_penguins`, где находится `species` и `size`, определенные на `body_mass_g` +- `CASE` разделяет пингвинов на 3 категории: `'small'`, `'medium'` и `'large'` в зависимости от их массы +- в основном блоке `SELECT` выбираются вид пингвина, его размер и количество пингвинов каждого размера (`num`) из набора `sized_penguins` +- результаты группируются по виду пингвина и их размеру с помощью `GROUP BY` +- в конце запроса результаты сортируются сначала по `species` в алфавитном порядке, а затем по `num` + +### Работаем с диапазоном значений + +[src/check_range.sql](https://gvwilson.github.io/sql-tutorial/src/check_range.sql) + +``` +WITH sized_penguins AS + (SELECT species, + CASE + WHEN body_mass_g BETWEEN 3500 AND 5000 THEN 'normal' + ELSE 'abnormal' + END AS SIZE + FROM penguins) +SELECT species, + SIZE, + count(*) AS num +FROM sized_penguins +GROUP BY species, + SIZE +ORDER BY species, + num; +``` + +[out/check_range.out](https://gvwilson.github.io/sql-tutorial/out/check_range.out) + +``` +| species | size | num | +|-----------|----------|-----| +| Adelie | abnormal | 55 | +| Adelie | normal | 97 | +| Chinstrap | abnormal | 17 | +| Chinstrap | normal | 51 | +| Gentoo | abnormal | 62 | +| Gentoo | normal | 62 | +``` + +- создаём общую таблицу выражений (CTE) `sized_penguins`, она выбирает вид пингвина и определяет его размер в зависимости от массы тела; если масса в диапазоне от 3500 до 5000 г, это размер `normal`, в противном случае – `abnormal` +- затем из этой CTE извлекаем данные с указанием видов пингвинов, их размеров и количества пингвинов каждого вида и размера, используя `SELECT` с агрегирующей функцией `COUNT(*)` +- группируем по виду и размеру пингвина с помощью `GROUP BY` +- сортируем результат по виду и количеству пингвинов в порядке возрастания с помощью `ORDER BY` + +**Ещё одна БД: ** + +ER-диаграмма показывает отношения между отдельными табличками и выглядит так: + + + +assay database table diagram + + + +assay ER diagram + +[src/assay_staff.sql](https://gvwilson.github.io/sql-tutorial/src/assay_staff.sql) + +``` +SELECT * +FROM staff; +``` + +[out/assay_staff.out](https://gvwilson.github.io/sql-tutorial/out/assay_staff.out) + +``` +| ident | personal | family | dept | age | +|-------|----------|-----------|------|-----| +| 1 | Kartik | Gupta | | 46 | +| 2 | Divit | Dhaliwal | hist | 34 | +| 3 | Indrans | Sridhar | mb | 47 | +| 4 | Pranay | Khanna | mb | 51 | +| 5 | Riaan | Dua | | 23 | +| 6 | Vedika | Rout | hist | 45 | +| 7 | Abram | Chokshi | gen | 23 | +| 8 | Romil | Kapoor | hist | 38 | +| 9 | Ishaan | Ramaswamy | mb | 35 | +| 10 | Nitya | Lal | gen | 52 | +``` + +### Ищем по фрагменту с помощью LIKE + +[src/like_glob.sql](https://gvwilson.github.io/sql-tutorial/src/like_glob.sql) + +``` +SELECT personal, + family +FROM staff +WHERE personal LIKE '%ya%' + OR family GLOB '*De*'; +``` + +[out/like_glob.out](https://gvwilson.github.io/sql-tutorial/out/like_glob.out) + +``` +| personal | family | +|----------|--------| +| Nitya | Lal | +``` + +- `SELECT personal, family` — хотим выбрать столбцы `personal` и `family` из таблицы `staff` +- `FROM staff` — ну понятно, запрос будет выполнен в таблице `staff` +- `'%ya%'` — хотим выбрать строки, в которых значение столбца `personal` содержит подстроку `ya` (с помощью `LIKE`) или значение столбца `family` содержит `De` (с помощью `GLOB`) + +### Выбираем первую и последнюю строки + +[src/union_all.sql](https://gvwilson.github.io/sql-tutorial/src/union_all.sql) + +``` +SELECT * +FROM + (SELECT * + FROM + (SELECT * + FROM experiment + ORDER BY started ASC + LIMIT 5) + UNION ALL SELECT * + FROM + (SELECT * + FROM experiment + ORDER BY started DESC + LIMIT 5)) +ORDER BY started ASC ; +``` + +[out/union_all.out](https://gvwilson.github.io/sql-tutorial/out/union_all.out) + +``` +| ident | kind | started | ended | +|-------|-------------|------------|------------| +| 17 | trial | 2023-01-29 | 2023-01-30 | +| 35 | calibration | 2023-01-30 | 2023-01-30 | +| 36 | trial | 2023-02-02 | 2023-02-03 | +| 25 | trial | 2023-02-12 | 2023-02-14 | +| 2 | calibration | 2023-02-14 | 2023-02-14 | +| 40 | calibration | 2024-01-21 | 2024-01-21 | +| 12 | trial | 2024-01-26 | 2024-01-28 | +| 44 | trial | 2024-01-27 | 2024-01-29 | +| 34 | trial | 2024-02-01 | 2024-02-02 | +| 14 | calibration | 2024-02-03 | 2024-02-03 | +``` + +- выбираем 5 самых старых записей из таблицы `experiment`, отсортированных по возрастанию даты начала (`started ASC`) с помощью подзапроса (внутренний `SELECT`) +- выбираем 5 самых новых записей из `experiment`, отсортированных по убыванию даты начала (`started DESC`) с помощью другого подзапроса +- объединяем эти 2 подзапроса с помощью `UNION ALL`, так мы получаем временную таблицу, содержащую 10 записей (5 самых старых и 5 самых новых) +- из временной таблицы выбираем все столбцы для каждой записи (`SELECT *`) и окончательно сортируем записи по возрастанию даты начала (`started ASC`) с помощью внешнего `ORDER BY` + +### Пересечение отдельных табличек + +[src/intersect.sql](https://gvwilson.github.io/sql-tutorial/src/intersect.sql) + +``` +SELECT personal, + family, + dept, + age +FROM staff +WHERE dept = 'mb' INTERSECT + SELECT personal, + family, + dept, + age + FROM staff WHERE age < 50 ; +``` + +[out/intersect.out](https://gvwilson.github.io/sql-tutorial/out/intersect.out) + +``` +| personal | family | dept | age | +|----------|-----------|------|-----| +| Indrans | Sridhar | mb | 47 | +| Ishaan | Ramaswamy | mb | 35 | +``` + +- здесь мы используем `INTERSECT` для объединения результатов двух отдельных запросов +- вначале выбираем данные из таблицы `staff`, в которых значение поля `dept` равно `'mb'` +- потом выбираем данные из таблицы `staff`, в которых значение поля `age` меньше 50 +- с помощью `INTERSECT` объединяем результаты этих двух запросов +- в результате будут выбраны строки, которые присутствуют в обоих результатах, то есть записи из `staff`, где значение `dept` равно `'mb'` и значение `age` меньше 50 + +### Исключение + +[src/except.sql](https://gvwilson.github.io/sql-tutorial/src/except.sql) + +``` +SELECT personal, + family, + dept, + age +FROM staff +WHERE dept = 'mb' +EXCEPT +SELECT personal, + family, + dept, + age +FROM staff +WHERE age < 50 ; +``` + +[out/except.out](https://gvwilson.github.io/sql-tutorial/out/except.out) + +``` +| personal | family | dept | age | +|----------|--------|------|-----| +| Pranay | Khanna | mb | 51 | +``` + +- при помощи `SELECT` извлекаем 4 поля из `staff`: `personal`, `family`, `dept` и `age` +- затем используем `WHERE`, чтобы отфильтровать только те строки, в которых значение `dept` равно `'mb'` +- после этого при помощи `EXCEPT` удаляем из исходного результата любые строки, которые также присутствуют в результате второго запроса +- второй запрос `SELECT` также извлекает четыре поля из `staff`: `personal`, `family`, `dept` и `age` +- используем `WHERE`, чтобы отфильтровать только те строки, где значение `age` меньше 50 + +### Случайные значения в SQL + +[src/random_numbers.sql](https://gvwilson.github.io/sql-tutorial/src/random_numbers.sql) + +``` +WITH decorated AS + (SELECT random() AS rand, + personal || ' ' || family AS name + FROM staff) +SELECT rand, + abs(rand) % 10 AS selector, + name +FROM decorated +WHERE selector < 5; +``` + +[out/random_numbers.out](https://gvwilson.github.io/sql-tutorial/out/random_numbers.out) + +``` +| rand | selector | name | +|----------------------|----------|-----------------| +| 7176652035743196310 | 0 | Divit Dhaliwal | +| -2243654635505630380 | 2 | Indrans Sridhar | +| -6940074802089166303 | 5 | Pranay Khanna | +| 8882650891091088193 | 9 | Riaan Dua | +| -45079732302991538 | 5 | Vedika Rout | +| -8973877087806386134 | 2 | Abram Chokshi | +| 3360598450426870356 | 9 | Romil Kapoor | +``` + +- создаём временную таблицу `decorated` + +- в этой таблице извлекается случайное число с помощью `random()` +- конкатенируем значения `personal` и `family` под именем `name` с помощью `' '` для разделения +- таким образом создаём временную таблицу, содержащую столбцы `rand` с случайными числами и `name` со значениями из столбцов `personal` и `family` таблицы `staff` +- делаем выборку из временной таблицы `decorated`; в выборку включаем столбцы `rand`, `name` +- `abs(rand) % 10` — это мы вычисляем остаток от деления абсолютного значения `rand` на 10 +- ну и в конце оставляем только строки, где `selector` меньше 5 + +### Создание индексов + +[src/create_use_index.sql](https://gvwilson.github.io/sql-tutorial/src/create_use_index.sql) + +``` +EXPLAIN query PLAN +SELECT filename +FROM plate +WHERE filename like '%07%'; + +CREATE INDEX plate_file ON plate(filename); + +EXPLAIN query PLAN +SELECT filename +FROM plate +WHERE filename like '%07%'; +``` + +[out/create_use_index.out](https://gvwilson.github.io/sql-tutorial/out/create_use_index.out) + +``` +QUERY PLAN +`--SCAN plate USING COVERING INDEX sqlite_autoindex_plate_1 +QUERY PLAN +`--SCAN plate USING COVERING INDEX plate_file +``` + +- выбираем все значения столбца `filename` из таблицы `plate`, где значение столбца `filename` содержит подстроку `07` +- создаём индекс с именем `plate_file` для столбца `filename` в таблице `plate` +- запрашиваем план выполнения запроса (`EXPLAIN query PLAN`) + +### Генерация последовательности значений + +[src/generate_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/generate_sequence.sql) + +``` +SELECT value +FROM generate_series(1, 5); +``` + +[out/generate_sequence.out](https://gvwilson.github.io/sql-tutorial/out/generate_sequence.out) + +``` +| value | +|-------| +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | +``` + +- `generate_series(1, 5)` — генерируем ряд чисел от 1 до 5 +- `SELECT value` — выбираем этот столбец `value` со сгенерированными числами от 1 до 5 + +### Генерируем последовательность на основе данных + +[src/data_range_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/data_range_sequence.sql) + +``` +CREATE TABLE temp (num integer NOT NULL); + +INSERT INTO temp +VALUES (1), + (5); + +SELECT value +FROM generate_series ((SELECT min(num) + FROM TEMP), + (SELECT max(num) + FROM TEMP)); +``` + +[out/data_range_sequence.out](https://gvwilson.github.io/sql-tutorial/out/data_range_sequence.out) + +``` +| value | +|-------| +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | +``` + +- создаём временную таблицу `temp`, которая содержит 1 столбец с именем `num` типа integer; этот столбец не может быть пустым +- помещаем в `temp` значения 1 и 5 в столбец `num` +- используем `generate_series` для создания последовательности чисел между минимальным и максимальным значениями из столбца `num` в таблице `temp` + +### Генерация последовательностей дат + +[src/date_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/date_sequence.sql) + +``` +SELECT date( + (SELECT julianday(min(started)) + FROM experiment) + value) AS some_day +FROM + (SELECT value + FROM generate_series( + (SELECT 0), + (SELECT count(*) - 1 + FROM experiment))) +LIMIT 5; +``` + +[out/date_sequence.out](https://gvwilson.github.io/sql-tutorial/out/date_sequence.out) + +``` +| some_day | +|------------| +| 2023-01-29 | +| 2023-01-30 | +| 2023-01-31 | +| 2023-02-01 | +| 2023-02-02 | +``` + +- `SELECT julianday(min(started)) FROM experiment` — находим минимальную дату в столбце `started` из `experiment`, преобразуем её в `Julian` день (числовое представление даты) и возвращаем этот `Julian` день +- внешним подзапросом вычисляем разницу между этим минимальным `Julian` днем и каждым `value` из `generate_series` +- затем складываем эти разницы с минимальным `Julian` днем, и конвертируем обратно в дату с помощью `date()` +- ну и выбираем только первые 5 результатов этого вычисления с помощью `LIMIT 5` + +### Подсчитываем количество значений за день, без пропусков + +[src/experiments_per_day.sql](https://gvwilson.github.io/sql-tutorial/src/experiments_per_day.sql) + +``` + WITH -- complete sequence of days with 0 as placeholder for number of experiments + all_days AS ( + SELECT DATE ( + ( + SELECT julianday (MIN(started)) + FROM experiment + ) + VALUE + ) AS some_day, + 0 AS zeroes + FROM ( + SELECT VALUE + FROM generate_series ( + ( + SELECT 0 + ), + ( + SELECT COUNT(*) - 1 + FROM experiment + ) + ) + ) + ), -- sequence of actual days with actual number of experiments started + actual_days AS ( + SELECT started, + COUNT(started) AS num_exp + FROM experiment + GROUP BY started + ) -- combined by joining on day and taking actual number (if available) or zero + SELECT all_days.some_day AS DAY, + COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp + FROM all_days +LEFT JOIN actual_days ON all_days.some_day = actual_days.started + LIMIT 5; +``` + +[out/experiments_per_day.out](https://gvwilson.github.io/sql-tutorial/out/experiments_per_day.out) + +``` +| day | num_exp | +|------------|---------| +| 2023-01-29 | 1 | +| 2023-01-30 | 1 | +| 2023-01-31 | 0 | +| 2023-02-01 | 0 | +| 2023-02-02 | 1 | +``` + +- создаём последовательность всех дней с нулевым значением в качестве заполнителя для количества экспериментов +- создаём последовательность фактических дней с реальным числом экспериментов +- объединяем эти последовательности, соединяя их по дням и беря реальное количество (если доступно) или ноль +- выводит результат, показывая дни (`all_days.some_day`) и соответствующее количество экспериментов (`COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp`), при этом используется функция `COALESCE`, чтобы использовать фактическое количество экспериментов, если оно доступно, или ноль, если нет + +### JOIN таблички с собой же + +[src/self_join.sql](https://gvwilson.github.io/sql-tutorial/src/self_join.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS +LEFT JOIN person AS RIGHT + LIMIT 10; +``` + +[out/self_join.out](https://gvwilson.github.io/sql-tutorial/out/self_join.out) + +``` +| name | name | +|--------------|------------------| +| Kartik Gupta | Kartik Gupta | +| Kartik Gupta | Divit Dhaliwal | +| Kartik Gupta | Indrans Sridhar | +| Kartik Gupta | Pranay Khanna | +| Kartik Gupta | Riaan Dua | +| Kartik Gupta | Vedika Rout | +| Kartik Gupta | Abram Chokshi | +| Kartik Gupta | Romil Kapoor | +| Kartik Gupta | Ishaan Ramaswamy | +| Kartik Gupta | Nitya Lal | +``` + +- создаём временную общую таблицу `person` с помощью WITH +- объединяем столбцы `personal` и `family` в один столбец `name` +- при помощи `SELECT` выбираем из `person` значения столбца `name` через алиасы `left` и `right` +- после этого происходит объединение `person` с собой с помощью оператора `LEFT JOIN`, при этом таблица алиасируется как `RIGHT` + +Этот SQL-код, однако, содержит ошибку, правильный синтаксис должен быть следующим: + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS LEFT +LEFT JOIN person AS RIGHT ON < условие соединения > + LIMIT 10; +``` + +В исходном примере условие соединения (`ON`) не было указано + +### Генерируем уникальные пары значений + +[src/unique_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/unique_pairs.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS +LEFT JOIN person AS RIGHT ON LEFT.ident < RIGHT.ident + WHERE LEFT.ident <= 4 + AND RIGHT.ident <= 4; +``` + +[out/unique_pairs.out](https://gvwilson.github.io/sql-tutorial/out/unique_pairs.out) + +``` +| name | name | +|-----------------|-----------------| +| Kartik Gupta | Divit Dhaliwal | +| Kartik Gupta | Indrans Sridhar | +| Kartik Gupta | Pranay Khanna | +| Divit Dhaliwal | Indrans Sridhar | +| Divit Dhaliwal | Pranay Khanna | +| Indrans Sridhar | Pranay Khanna | +``` + +- создаём временную таблицу `person`, которая содержит результат выбора из таблицы `staff` +- из `person` выбираем значения `left.name` и `right.name` с использованием операции слияния (`JOIN`). В этом случае происходит слияние `person` с собой, причем каждая копия `person` используется в качестве левой и правой таблиц соответственно. Слияние выполняется по условию, что идентификатор слева меньше идентификатора справа. +- затем применяем дополнительное условие с помощью `WHERE`, которое фильтрует результаты `JOIN`-операции. Это условие проверяет, что идентификаторы слева и справа меньше или равны 4 + +### Фильтрация пар + +[src/filter_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_pairs.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ), + together AS ( + SELECT LEFT.staff AS left_staff, + RIGHT.staff AS right_staff + FROM performed AS + LEFT JOIN performed AS RIGHT ON LEFT.experiment = RIGHT.experiment + WHERE left_staff < right_staff + ) + SELECT LEFT.name AS person_1, + RIGHT.name AS person_2 + FROM person AS +LEFT JOIN person AS + RIGHT JOIN together ON LEFT.ident = left_staff + AND RIGHT.ident = right_staff; +``` + +[out/filter_pairs.out](https://gvwilson.github.io/sql-tutorial/out/filter_pairs.out) + +``` +| person_1 | person_2 | +|-----------------|------------------| +| Kartik Gupta | Vedika Rout | +| Pranay Khanna | Vedika Rout | +| Indrans Sridhar | Romil Kapoor | +| Abram Chokshi | Ishaan Ramaswamy | +| Pranay Khanna | Vedika Rout | +| Kartik Gupta | Abram Chokshi | +| Abram Chokshi | Romil Kapoor | +| Kartik Gupta | Divit Dhaliwal | +| Divit Dhaliwal | Abram Chokshi | +| Pranay Khanna | Ishaan Ramaswamy | +| Indrans Sridhar | Romil Kapoor | +| Kartik Gupta | Ishaan Ramaswamy | +| Kartik Gupta | Nitya Lal | +| Kartik Gupta | Abram Chokshi | +| Pranay Khanna | Romil Kapoor | +``` + +- во временной табличке `person` выбираем имена сотрудников из таблицы `staff`; используем `personal ' ' family AS name`, чтобы объединить значения из столбцов `personal` и `family` +- временная табличка `together` использует оператор `LEFT JOIN` для объединения таблицы `performed` с собой на основе столбца `experiment`. Затем выбираются пары сотрудников, участвовавших в одном и том же эксперименте, исключая случаи, когда идентификатор левого сотрудника (`left_staff`) больше идентификатора правого сотрудника (`right_staff`) +- затем выполняется основной `SELECT`, который использует `person` и `together` для объединения имен сотрудников на основе их идентификаторов. Он выполняет два `LEFT JOIN`, чтобы объединить `person` с самим собой и затем объединить результат с `together` на основе идентификаторов сотрудников. +- затем выбираются имена сотрудников для отображения в итоговом результате. + +### EXISTS + +[src/correlated_subquery.sql](https://gvwilson.github.io/sql-tutorial/src/correlated_subquery.sql) + +``` +SELECT name, + building +FROM department +WHERE EXISTS + (SELECT 1 + FROM staff + WHERE dept = department.ident ) +ORDER BY name; +``` + +[out/correlated_subquery.out](https://gvwilson.github.io/sql-tutorial/out/correlated_subquery.out) + +``` +| name | building | +|-------------------|------------------| +| Genetics | Chesson | +| Histology | Fashet Extension | +| Molecular Biology | Chesson | +``` + +- выбираем столбцы `name` и `building` из таблицы `department` +- `WHERE EXISTS (SELECT 1 FROM staff WHERE dept = department.ident )` — используем подзапрос, который проверяет существование хотя бы одной записи в таблице `staff`, для которой значение столбца `dept` совпадает с значением столбца `ident` из таблицы `department` +- `ORDER BY name` — устанавливаем порядок сортировки результатов по столбцу `name` в алфавитном порядке + +### NOT EXISTS в SQL + +[src/nonexistence.sql](https://gvwilson.github.io/sql-tutorial/src/nonexistence.sql) + +``` +SELECT name, + building +FROM department +WHERE NOT EXISTS + (SELECT 1 + FROM staff + WHERE dept = department.ident ) +ORDER BY name; +``` + +[out/nonexistence.out](https://gvwilson.github.io/sql-tutorial/out/nonexistence.out) + +``` +| name | building | +|---------------|----------| +| Endocrinology | TGVH | +``` + +- выбираем столбцы `name` и `building` из таблицы `department` +- `WHERE NOT EXISTS` — выбираем только те записи из `department`, для которых не существует записей в таблице `staff` +- `SELECT 1 FROM staff WHERE dept = department.ident` — проверяем, существуют ли записи в таблице `staff`, связанные с отделом из таблицы `department` +- `ORDER BY name` — сортируем результат по столбцу name + +**Избегание коррелированных подзапросов** + +[src/avoid_correlated_subqueries.sql](https://gvwilson.github.io/sql-tutorial/src/avoid_correlated_subqueries.sql) + +``` +SELECT DISTINCT department.name AS name, + department.building AS building +FROM department +JOIN staff ON department.ident = staff.dept +ORDER BY name; +``` + +[out/avoid_correlated_subqueries.out](https://gvwilson.github.io/sql-tutorial/out/avoid_correlated_subqueries.out) + +``` +| name | building | +|-------------------|------------------| +| Genetics | Chesson | +| Histology | Fashet Extension | +| Molecular Biology | Chesson | +``` + +- `SELECT DISTINCT` — выбираем уникальные значения `name` и `building` из таблицы `department` +- `JOIN staff ON department.ident = staff.dept` — объединяем таблицы `department` и `staff` на основе условия, что значение столбца `ident` из `department` равно значению `dept` из `staff` +- `ORDER BY name` — результаты выборки сортируем в алфавитном порядке по столбцу `name` + +### Опережение и отставание + +[src/lead_lag.sql](https://gvwilson.github.io/sql-tutorial/src/lead_lag.sql) + +``` + WITH ym_num AS ( + SELECT strftime ('%Y-%m', started) AS ym, + COUNT(*) AS num + FROM experiment + GROUP BY ym + ) + SELECT ym, + lag (num) OVER ( + ORDER BY ym + ) AS prev_num, + num, + lead (num) OVER ( + ORDER BY ym + ) AS next_num + FROM ym_num + ORDER BY ym; +``` + +- в этой таблице извлекается случайное число с помощью `random()` +- конкатенируем значения `personal` и `family` под именем `name` с помощью `' '` для разделения +- таким образом создаём временную таблицу, содержащую столбцы `rand` с случайными числами и `name` со значениями из столбцов `personal` и `family` таблицы `staff` +- делаем выборку из временной таблицы `decorated`; в выборку включаем столбцы `rand`, `name` +- `abs(rand) % 10` — это мы вычисляем остаток от деления абсолютного значения `rand` на 10 +- ну и в конце оставляем только строки, где `selector` меньше 5 + +### Создание индексов + +[src/create_use_index.sql](https://gvwilson.github.io/sql-tutorial/src/create_use_index.sql) + +``` +EXPLAIN query PLAN +SELECT filename +FROM plate +WHERE filename like '%07%'; + +CREATE INDEX plate_file ON plate(filename); + +EXPLAIN query PLAN +SELECT filename +FROM plate +WHERE filename like '%07%'; +``` + +[out/create_use_index.out](https://gvwilson.github.io/sql-tutorial/out/create_use_index.out) + +``` +QUERY PLAN +`--SCAN plate USING COVERING INDEX sqlite_autoindex_plate_1 +QUERY PLAN +`--SCAN plate USING COVERING INDEX plate_file +``` + +- выбираем все значения столбца `filename` из таблицы `plate`, где значение столбца `filename` содержит подстроку `07` +- создаём индекс с именем `plate_file` для столбца `filename` в таблице `plate` +- запрашиваем план выполнения запроса (`EXPLAIN query PLAN`) + +### Генерация последовательности значений + +[src/generate_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/generate_sequence.sql) + +``` +SELECT value +FROM generate_series(1, 5); +``` + +[out/generate_sequence.out](https://gvwilson.github.io/sql-tutorial/out/generate_sequence.out) + +``` +| value | +|-------| +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | +``` + +- `generate_series(1, 5)` — генерируем ряд чисел от 1 до 5 +- `SELECT value` — выбираем этот столбец `value` со сгенерированными числами от 1 до 5 + +### Генерируем последовательность на основе данных + +[src/data_range_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/data_range_sequence.sql) + +``` +CREATE TABLE temp (num integer NOT NULL); + +INSERT INTO temp +VALUES (1), + (5); + +SELECT value +FROM generate_series ((SELECT min(num) + FROM TEMP), + (SELECT max(num) + FROM TEMP)); +``` + +[out/data_range_sequence.out](https://gvwilson.github.io/sql-tutorial/out/data_range_sequence.out) + +``` +| value | +|-------| +| 1 | +| 2 | +| 3 | +| 4 | +| 5 | +``` + +- создаём временную таблицу `temp`, которая содержит 1 столбец с именем `num` типа integer; этот столбец не может быть пустым +- помещаем в `temp` значения 1 и 5 в столбец `num` +- используем `generate_series` для создания последовательности чисел между минимальным и максимальным значениями из столбца `num` в таблице `temp` + +### Генерация последовательностей дат + +[src/date_sequence.sql](https://gvwilson.github.io/sql-tutorial/src/date_sequence.sql) + +``` +SELECT date( + (SELECT julianday(min(started)) + FROM experiment) + value) AS some_day +FROM + (SELECT value + FROM generate_series( + (SELECT 0), + (SELECT count(*) - 1 + FROM experiment))) +LIMIT 5; +``` + +[out/date_sequence.out](https://gvwilson.github.io/sql-tutorial/out/date_sequence.out) + +``` +| some_day | +|------------| +| 2023-01-29 | +| 2023-01-30 | +| 2023-01-31 | +| 2023-02-01 | +| 2023-02-02 | +``` + +- `SELECT julianday(min(started)) FROM experiment` — находим минимальную дату в столбце `started` из `experiment`, преобразуем её в `Julian` день (числовое представление даты) и возвращаем этот `Julian` день +- внешним подзапросом вычисляем разницу между этим минимальным `Julian` днем и каждым `value` из `generate_series` +- затем складываем эти разницы с минимальным `Julian` днем, и конвертируем обратно в дату с помощью `date()` +- ну и выбираем только первые 5 результатов этого вычисления с помощью `LIMIT 5` + +### Подсчитываем количество значений за день, без пропусков + +[src/experiments_per_day.sql](https://gvwilson.github.io/sql-tutorial/src/experiments_per_day.sql) + +``` + WITH -- complete sequence of days with 0 as placeholder for number of experiments + all_days AS ( + SELECT DATE ( + ( + SELECT julianday (MIN(started)) + FROM experiment + ) + VALUE + ) AS some_day, + 0 AS zeroes + FROM ( + SELECT VALUE + FROM generate_series ( + ( + SELECT 0 + ), + ( + SELECT COUNT(*) - 1 + FROM experiment + ) + ) + ) + ), -- sequence of actual days with actual number of experiments started + actual_days AS ( + SELECT started, + COUNT(started) AS num_exp + FROM experiment + GROUP BY started + ) -- combined by joining on day and taking actual number (if available) or zero + SELECT all_days.some_day AS DAY, + COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp + FROM all_days +LEFT JOIN actual_days ON all_days.some_day = actual_days.started + LIMIT 5; +``` + +[out/experiments_per_day.out](https://gvwilson.github.io/sql-tutorial/out/experiments_per_day.out) + +``` +| day | num_exp | +|------------|---------| +| 2023-01-29 | 1 | +| 2023-01-30 | 1 | +| 2023-01-31 | 0 | +| 2023-02-01 | 0 | +| 2023-02-02 | 1 | +``` + +- создаём последовательность всех дней с нулевым значением в качестве заполнителя для количества экспериментов +- создаём последовательность фактических дней с реальным числом экспериментов +- объединяем эти последовательности, соединяя их по дням и беря реальное количество (если доступно) или ноль +- выводит результат, показывая дни (`all_days.some_day`) и соответствующее количество экспериментов (`COALESCE(actual_days.num_exp, all_days.zeroes) AS num_exp`), при этом используется функция `COALESCE`, чтобы использовать фактическое количество экспериментов, если оно доступно, или ноль, если нет + +### JOIN таблички с собой же + +[src/self_join.sql](https://gvwilson.github.io/sql-tutorial/src/self_join.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS +LEFT JOIN person AS RIGHT + LIMIT 10; +``` + +[out/self_join.out](https://gvwilson.github.io/sql-tutorial/out/self_join.out) + +``` +| name | name | +|--------------|------------------| +| Kartik Gupta | Kartik Gupta | +| Kartik Gupta | Divit Dhaliwal | +| Kartik Gupta | Indrans Sridhar | +| Kartik Gupta | Pranay Khanna | +| Kartik Gupta | Riaan Dua | +| Kartik Gupta | Vedika Rout | +| Kartik Gupta | Abram Chokshi | +| Kartik Gupta | Romil Kapoor | +| Kartik Gupta | Ishaan Ramaswamy | +| Kartik Gupta | Nitya Lal | +``` + +- создаём временную общую таблицу `person` с помощью WITH +- объединяем столбцы `personal` и `family` в один столбец `name` +- при помощи `SELECT` выбираем из `person` значения столбца `name` через алиасы `left` и `right` +- после этого происходит объединение `person` с собой с помощью оператора `LEFT JOIN`, при этом таблица алиасируется как `RIGHT` + +Этот SQL-код, однако, содержит ошибку, правильный синтаксис должен быть следующим: + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS LEFT +LEFT JOIN person AS RIGHT ON < условие соединения > + LIMIT 10; +``` + +В исходном примере условие соединения (`ON`) не было указано + +### Генерируем уникальные пары значений + +[src/unique_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/unique_pairs.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ) + SELECT LEFT.name, + RIGHT.name + FROM person AS +LEFT JOIN person AS RIGHT ON LEFT.ident < RIGHT.ident + WHERE LEFT.ident <= 4 + AND RIGHT.ident <= 4; +``` + +[out/unique_pairs.out](https://gvwilson.github.io/sql-tutorial/out/unique_pairs.out) + +``` +| name | name | +|-----------------|-----------------| +| Kartik Gupta | Divit Dhaliwal | +| Kartik Gupta | Indrans Sridhar | +| Kartik Gupta | Pranay Khanna | +| Divit Dhaliwal | Indrans Sridhar | +| Divit Dhaliwal | Pranay Khanna | +| Indrans Sridhar | Pranay Khanna | +``` + +- создаём временную таблицу `person`, которая содержит результат выбора из таблицы `staff` +- из `person` выбираем значения `left.name` и `right.name` с использованием операции слияния (`JOIN`). В этом случае происходит слияние `person` с собой, причем каждая копия `person` используется в качестве левой и правой таблиц соответственно. Слияние выполняется по условию, что идентификатор слева меньше идентификатора справа. +- затем применяем дополнительное условие с помощью `WHERE`, которое фильтрует результаты `JOIN`-операции. Это условие проверяет, что идентификаторы слева и справа меньше или равны 4 + +### Фильтрация пар + +[src/filter_pairs.sql](https://gvwilson.github.io/sql-tutorial/src/filter_pairs.sql) + +``` + WITH person AS ( + SELECT ident, + personal || ' ' || family AS name + FROM staff + ), + together AS ( + SELECT LEFT.staff AS left_staff, + RIGHT.staff AS right_staff + FROM performed AS + LEFT JOIN performed AS RIGHT ON LEFT.experiment = RIGHT.experiment + WHERE left_staff < right_staff + ) + SELECT LEFT.name AS person_1, + RIGHT.name AS person_2 + FROM person AS +LEFT JOIN person AS + RIGHT JOIN together ON LEFT.ident = left_staff + AND RIGHT.ident = right_staff; +``` + +[out/filter_pairs.out](https://gvwilson.github.io/sql-tutorial/out/filter_pairs.out) + +``` +| person_1 | person_2 | +|-----------------|------------------| +| Kartik Gupta | Vedika Rout | +| Pranay Khanna | Vedika Rout | +| Indrans Sridhar | Romil Kapoor | +| Abram Chokshi | Ishaan Ramaswamy | +| Pranay Khanna | Vedika Rout | +| Kartik Gupta | Abram Chokshi | +| Abram Chokshi | Romil Kapoor | +| Kartik Gupta | Divit Dhaliwal | +| Divit Dhaliwal | Abram Chokshi | +| Pranay Khanna | Ishaan Ramaswamy | +| Indrans Sridhar | Romil Kapoor | +| Kartik Gupta | Ishaan Ramaswamy | +| Kartik Gupta | Nitya Lal | +| Kartik Gupta | Abram Chokshi | +| Pranay Khanna | Romil Kapoor | +``` + +- во временной табличке `person` выбираем имена сотрудников из таблицы `staff`; используем `personal ' ' family AS name`, чтобы объединить значения из столбцов `personal` и `family` +- временная табличка `together` использует оператор `LEFT JOIN` для объединения таблицы `performed` с собой на основе столбца `experiment`. Затем выбираются пары сотрудников, участвовавших в одном и том же эксперименте, исключая случаи, когда идентификатор левого сотрудника (`left_staff`) больше идентификатора правого сотрудника (`right_staff`) +- затем выполняется основной `SELECT`, который использует `person` и `together` для объединения имен сотрудников на основе их идентификаторов. Он выполняет два `LEFT JOIN`, чтобы объединить `person` с самим собой и затем объединить результат с `together` на основе идентификаторов сотрудников. +- затем выбираются имена сотрудников для отображения в итоговом результате. + +### EXISTS + +[src/correlated_subquery.sql](https://gvwilson.github.io/sql-tutorial/src/correlated_subquery.sql) + +``` +SELECT name, + building +FROM department +WHERE EXISTS + (SELECT 1 + FROM staff + WHERE dept = department.ident ) +ORDER BY name; +``` + +[out/correlated_subquery.out](https://gvwilson.github.io/sql-tutorial/out/correlated_subquery.out) + +``` +| name | building | +|-------------------|------------------| +| Genetics | Chesson | +| Histology | Fashet Extension | +| Molecular Biology | Chesson | +``` + +- выбираем столбцы `name` и `building` из таблицы `department` +- `WHERE EXISTS (SELECT 1 FROM staff WHERE dept = department.ident )` — используем подзапрос, который проверяет существование хотя бы одной записи в таблице `staff`, для которой значение столбца `dept` совпадает с значением столбца `ident` из таблицы `department` +- `ORDER BY name` — устанавливаем порядок сортировки результатов по столбцу `name` в алфавитном порядке + +### NOT EXISTS в SQL + +[src/nonexistence.sql](https://gvwilson.github.io/sql-tutorial/src/nonexistence.sql) + +``` +SELECT name, + building +FROM department +WHERE NOT EXISTS + (SELECT 1 + FROM staff + WHERE dept = department.ident ) +ORDER BY name; +``` + +[out/nonexistence.out](https://gvwilson.github.io/sql-tutorial/out/nonexistence.out) + +``` +| name | building | +|---------------|----------| +| Endocrinology | TGVH | +``` + +- выбираем столбцы `name` и `building` из таблицы `department` +- `WHERE NOT EXISTS` — выбираем только те записи из `department`, для которых не существует записей в таблице `staff` +- `SELECT 1 FROM staff WHERE dept = department.ident` — проверяем, существуют ли записи в таблице `staff`, связанные с отделом из таблицы `department` +- `ORDER BY name` — сортируем результат по столбцу name + +**Избегание коррелированных подзапросов** + +[src/avoid_correlated_subqueries.sql](https://gvwilson.github.io/sql-tutorial/src/avoid_correlated_subqueries.sql) + +``` +SELECT DISTINCT department.name AS name, + department.building AS building +FROM department +JOIN staff ON department.ident = staff.dept +ORDER BY name; +``` + +[out/avoid_correlated_subqueries.out](https://gvwilson.github.io/sql-tutorial/out/avoid_correlated_subqueries.out) + +``` +| name | building | +|-------------------|------------------| +| Genetics | Chesson | +| Histology | Fashet Extension | +| Molecular Biology | Chesson | +``` + +- `SELECT DISTINCT` — выбираем уникальные значения `name` и `building` из таблицы `department` +- `JOIN staff ON department.ident = staff.dept` — объединяем таблицы `department` и `staff` на основе условия, что значение столбца `ident` из `department` равно значению `dept` из `staff` +- `ORDER BY name` — результаты выборки сортируем в алфавитном порядке по столбцу `name` + +### Опережение и отставание + +[src/lead_lag.sql](https://gvwilson.github.io/sql-tutorial/src/lead_lag.sql) + +``` + WITH ym_num AS ( + SELECT strftime ('%Y-%m', started) AS ym, + COUNT(*) AS num + FROM experiment + GROUP BY ym + ) + SELECT ym, + lag (num) OVER ( + ORDER BY ym + ) AS prev_num, + num, + lead (num) OVER ( + ORDER BY ym + ) AS next_num + FROM ym_num + ORDER BY ym; +``` + +[out/lead_lag.out](https://gvwilson.github.io/sql-tutorial/out/lead_lag.out) + +``` +| ym | prev_num | num | next_num | +|---------|----------|-----|----------| +| 2023-01 | | 2 | 5 | +| 2023-02 | 2 | 5 | 5 | +| 2023-03 | 5 | 5 | 1 | +| 2023-04 | 5 | 1 | 6 | +| 2023-05 | 1 | 6 | 5 | +| 2023-06 | 6 | 5 | 3 | +| 2023-07 | 5 | 3 | 2 | +| 2023-08 | 3 | 2 | 4 | +| 2023-09 | 2 | 4 | 6 | +| 2023-10 | 4 | 6 | 4 | +| 2023-12 | 6 | 4 | 5 | +| 2024-01 | 4 | 5 | 2 | +| 2024-02 | 5 | 2 | | +``` + +- создаём временную таблицу `ym_num` из 2 столбцов: `ym` (год-месяц ‘YYYY-MM’) и `num` (количество записей в каждом месяце) +- используем SQLite `strftime` для извлечения года и месяца из `started`, агрегируем результаты с помощью `GROUP BY` +- в основном запросе выбираем данные из `ym_num`, выполняем следующие операции и получаем год-месяц `ym`, количество записей в предыдущем месяце `(lag)num`, текущее количество записей `num` и количество записей в следующем месяце `(lead)num` +- результаты упорядочиваем по столбцу `ym` (год-месяц) + +### Оконные функции + +[src/window_functions.sql](https://gvwilson.github.io/sql-tutorial/src/window_functions.sql) + +``` + WITH ym_num AS ( + SELECT strftime ('%Y-%m', started) AS ym, + COUNT(*) AS num + FROM experiment + GROUP BY ym + ) + SELECT ym, + num, + SUM(num) OVER ( + ORDER BY ym + ) AS num_done, + CUME_DIST() OVER ( + ORDER BY ym + ) AS progress + FROM ym_num + ORDER BY ym; +``` + +[out/window_functions.out](https://gvwilson.github.io/sql-tutorial/out/window_functions.out) + +``` +| ym | num | num_done | progress | +|---------|-----|----------|--------------------| +| 2023-01 | 2 | 2 | 0.0769230769230769 | +| 2023-02 | 5 | 7 | 0.153846153846154 | +| 2023-03 | 5 | 12 | 0.230769230769231 | +| 2023-04 | 1 | 13 | 0.307692307692308 | +| 2023-05 | 6 | 19 | 0.384615384615385 | +| 2023-06 | 5 | 24 | 0.461538461538462 | +| 2023-07 | 3 | 27 | 0.538461538461538 | +| 2023-08 | 2 | 29 | 0.615384615384615 | +| 2023-09 | 4 | 33 | 0.692307692307692 | +| 2023-10 | 6 | 39 | 0.769230769230769 | +| 2023-12 | 4 | 43 | 0.846153846153846 | +| 2024-01 | 5 | 48 | 0.923076923076923 | +| 2024-02 | 2 | 50 | 1.0 | +``` + +- создаём временную таблицу `ym_num`, которая содержит: `ym` — год и месяц, извлеченные из `started` в `experiment` с помощью `strftime('%Y-%m')`; `num` — количество записей в `experiment` для каждого сочетания года и месяца +- выбираем `ym` и `num` из таблицы `ym_num`, добавляем 2 дополнительных столбца: `num_done` — сумма количества экспериментов по всем предыдущим годам и месяцам (`sum(num) OVER (ORDER BY ym)`); `progress` — кумулятивное распределение количества экспериментов по всем предыдущим годам и месяцам (`cume_dist() OVER (ORDER BY ym)`) +- упорядочиваем результаты по столбцу `ym` (год и месяц) + +**Внезапно небольшое задание: объясните, что делает запрос ниже** + +[src/explain_window_function.sql](https://gvwilson.github.io/sql-tutorial/src/explain_window_function.sql) + +``` +EXPLAIN query PLAN + WITH ym_num AS ( + SELECT strftime ('%Y-%m', started) AS ym, + COUNT(*) AS num + FROM experiment + GROUP BY ym + ) + SELECT ym, + num, + SUM(num) OVER ( + ORDER BY ym + ) AS num_done, + CUME_DIST() OVER ( + ORDER BY ym + ) AS progress + FROM ym_num + ORDER BY ym; +``` + +[out/explain_window_function.out](https://gvwilson.github.io/sql-tutorial/out/explain_window_function.out) + +``` +QUERY PLAN +|--CO-ROUTINE (subquery-3) +| |--CO-ROUTINE (subquery-4) +| | |--CO-ROUTINE ym_num +| | | |--SCAN experiment +| | | `--USE TEMP B-TREE FOR GROUP BY +| | |--SCAN ym_num +| | `--USE TEMP B-TREE FOR ORDER BY +| `--SCAN (subquery-4) +`--SCAN (subquery-3) +``` + +- создаём временную табличку `ym_num` с результатами агрегирования по месяцам, где данные из `started`преобразуются в формат год-месяц (`strftime('%Y-%m', started) AS ym`) и подсчитываем количество событий (`count(*) AS num`) +- группируем результаты по полю `ym` +- выбираем поля `ym` и `num` из `ym_num` и добавляем 2 дополнительных поля: `num_done` и `progress`; `num_done` — общее количество событий/мес, сгруппированных в порядке увеличения месяца; поле `progress` — прогресс в процентном соотношении относительно общего числа записей (`cume_dist()`) +- в итоге выводим данные в порядке увеличения значения `ym` (год-месяц) + +### Используем PARTITION BY в SQL + +[src/partition_window.sql](https://gvwilson.github.io/sql-tutorial/src/partition_window.sql) + +``` +WITH y_m_num AS + (SELECT strftime('%Y', started) AS YEAR, + strftime('%m', started) AS MONTH, + count(*) AS num + FROM experiment + GROUP BY YEAR, + MONTH) +SELECT YEAR, + MONTH, + num, + sum(num) OVER (PARTITION BY YEAR + ORDER BY MONTH) AS num_done +FROM y_m_num +ORDER BY YEAR, + MONTH; +``` + +[out/partition_window.out](https://gvwilson.github.io/sql-tutorial/out/partition_window.out) + +``` +| year | month | num | num_done | +|------|-------|-----|----------| +| 2023 | 01 | 2 | 2 | +| 2023 | 02 | 5 | 7 | +| 2023 | 03 | 5 | 12 | +| 2023 | 04 | 1 | 13 | +| 2023 | 05 | 6 | 19 | +| 2023 | 06 | 5 | 24 | +| 2023 | 07 | 3 | 27 | +| 2023 | 08 | 2 | 29 | +| 2023 | 09 | 4 | 33 | +| 2023 | 10 | 6 | 39 | +| 2023 | 12 | 4 | 43 | +| 2024 | 01 | 5 | 5 | +| 2024 | 02 | 2 | 7 | +``` + +- создаём временную таблицу `y_m_num` с тремя столбцами: `YEAR,` `MONTH` и `num`. +- временную табличку заполняем записями из `experiment`. Для каждой записи определяем год и месяц даты в столбце `started` (через `strftime`), считаем количество записей (`count(*)`) для каждого года и месяца, группируем результаты по году и месяцу +- выбираем данные из `y_m_num`, добавляем столбец `num_done` — накопительное значение для `num` в пределах каждого года `sum(num) OVER (PARTITION BY YEAR ORDER BY MONTH)` — суммируем значение `num` для каждого месяца при сортировке по месяцам внутри каждого года +- в итоге сортируем результаты по году и месяцу с помощью `ORDER BY YEAR, MONTH` + +### Данные типа blob + +[src/blob.sql](https://gvwilson.github.io/sql-tutorial/src/blob.sql) + +``` +CREATE TABLE images (name text NOT NULL, + content blob); + +INSERT INTO images(name, content) +VALUES ("biohazard", readfile("img/biohazard.png")), + ("crush", readfile("img/crush.png")), + ("fire", readfile("img/fire.png")), + ("radioactive", readfile("img/radioactive.png")), + ("tripping", readfile("img/tripping.png")); + +SELECT name, + length(content) +FROM images; +``` + +[out/blob.out](https://gvwilson.github.io/sql-tutorial/out/blob.out) + +``` +| name | length(content) | +|-------------|-----------------| +| biohazard | 19629 | +| crush | 15967 | +| fire | 18699 | +| radioactive | 16661 | +| tripping | 17208 | +``` + +- создаём таблицу `images` со столбцами: `name` — текстовый столбец, не может быть пустым; `content` — столбец двоичных данных (`blob`) +- вставляем 5 пар `name`–`blob` в `images` с помощью `INSERT INTO` +- `readfile` читает содержимое файла `name` и возвращает его как двоичные данные `blob` +- выполняем выборку данных из `images` с помощью `SELECT`, получая значения `name` и вычисляя количество байт двоичных данных в `content` + +**Ещё одна БД** + +[src/lab_log_db.sh](https://gvwilson.github.io/sql-tutorial/src/lab_log_db.sh) + +``` +sqlite3 data/lab_log.db +``` + +[src/lab_log_schema.sql](https://gvwilson.github.io/sql-tutorial/src/lab_log_schema.sql) + +``` +.schema +``` + +[out/lab_log_schema.out](https://gvwilson.github.io/sql-tutorial/out/lab_log_schema.out) + +``` +CREATE TABLE sqlite_sequence(name, + seq); + +CREATE TABLE person(ident integer PRIMARY KEY autoincrement, + details text NOT NULL); + +CREATE TABLE machine(ident integer PRIMARY KEY autoincrement, + name text NOT NULL, + details text NOT NULL); + +CREATE TABLE usage(ident integer PRIMARY KEY autoincrement, + log text NOT NULL); +``` + +- создаём `sqlite_sequence` со столбцами `name` и `seq` (для значения счетчика, он используется в качестве `AUTOINCREMENT`) +- создаём `person` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется) и `details` (текстовый столбец, не может иметь значение `NULL`) +- создаём `machine` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется), `name` (текстовый, не может иметь значение `NULL`), `details`(текстовый, не может иметь значение `NULL`) +- создаём `usage` со столбцами `ident` (целочисленный, является первичным ключом (`PRIMARY KEY`), автоматически инкрементируется) и `log` (текстовый столбец, не может иметь значение `NULL`) + +### Сохранение JSON + +[src/json_in_table.sql](https://gvwilson.github.io/sql-tutorial/src/json_in_table.sql) + +``` +SELECT * +FROM machine; +``` + +[out/json_in_table.out](https://gvwilson.github.io/sql-tutorial/out/json_in_table.out) + +``` +| ident | name | details | +|-------|----------------|---------------------------------------------------------| +| 1 | WY401 | {"acquired": "2023-05-01"} | +| 2 | Inphormex | {"acquired": "2021-07-15", "refurbished": "2023-10-22"} | +| 3 | AutoPlate 9000 | {"note": "needs software update"} | +``` + +### Выбираем отдельные поля в JSON + +[src/json_field.sql](https://gvwilson.github.io/sql-tutorial/src/json_field.sql) + +``` +SELECT details->'$.acquired' AS single_arrow, + details->>'$.acquired' AS double_arrow +FROM machine; +``` + +[out/json_field.out](https://gvwilson.github.io/sql-tutorial/out/json_field.out) + +``` +| single_arrow | double_arrow | +|--------------|--------------| +| "2023-05-01" | 2023-05-01 | +| "2021-07-15" | 2021-07-15 | +| | | +``` + +- `details->'$.acquired' AS single_arrow` — с помощью `->` извлекаем значение JSON поля `acquired` из столбца `details` для каждой строки из `machine`, обозначаем его как `single_arrow` +- `details->>'$.acquired' AS double_arrow` — оператор `->>` также используется для извлечения JSON по указанному пути, но возвращает текст, в отличие от `->`, который возвращает JSON значение; здесь мы извлекаем значение JSON поля `acquired` из столбца `details` для каждой строки из `machine`, обозначаем его как `double_arrow` + +### Доступ к JSON-объекту + +[src/json_array.sql](https://gvwilson.github.io/sql-tutorial/src/json_array.sql) + +``` +SELECT ident, + json_array_length(log->'$') AS LENGTH, + log->'$[0]' AS FIRST +FROM USAGE; +``` + +[out/json_array.out](https://gvwilson.github.io/sql-tutorial/out/json_array.out) + +``` +| ident | length | first | +|-------|--------|--------------------------------------------------------------| +| 1 | 4 | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]} | +| 2 | 5 | {"machine":"Inphormex","person":["Marianne","Richer"]} | +| 3 | 2 | {"machine":"sterilizer","person":["Josette","Villeneuve"]} | +| 4 | 1 | {"machine":"sterilizer","person":["Maude","Goulet"]} | +| 5 | 2 | {"machine":"AutoPlate 9000","person":["Brigitte","Michaud"]} | +| 6 | 1 | {"machine":"sterilizer","person":["Marianne","Richer"]} | +| 7 | 3 | {"machine":"WY401","person":["Maude","Goulet"]} | +| 8 | 1 | {"machine":"AutoPlate 9000"} | +``` + +- `json_array_length(log->'$') AS LENGTH` — вычисляем длину массива, находящегося внутри JSON-объекта в столбце `log`; используем оператор `->`, чтобы получить массив из корневого уровня JSON-объекта, и `json_array_length` для подсчета количества элементов в этом массиве; результат помещаем в столбец с именем `LENGTH` +- `log->'$[0]' AS FIRST` — извлекаем первый элемент из массива, указанного в корневом уровне JSON-объекта в столбце `log`; используем оператор `->`, чтобы получить доступ к массиву, и указываем индекс элемента в квадратных скобках; результат сохраняем в столбец `FIRST` + +### Распаковка JSON + +[src/json_unpack.sql](https://gvwilson.github.io/sql-tutorial/src/json_unpack.sql) + +``` +SELECT ident, + json_each.key AS KEY, + json_each.value AS value +FROM USAGE, + json_each(usage.log) +LIMIT 10; +``` + +[out/json_unpack.out](https://gvwilson.github.io/sql-tutorial/out/json_unpack.out) + +``` +| ident | key | value | +|-------|-----|--------------------------------------------------------------| +| 1 | 0 | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]} | +| 1 | 1 | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]} | +| 1 | 2 | {"machine":"WY401","person":["Gabrielle","Dub\u00e9"]} | +| 1 | 3 | {"machine":"Inphormex","person":["Gabrielle","Dub\u00e9"]} | +| 2 | 0 | {"machine":"Inphormex","person":["Marianne","Richer"]} | +| 2 | 1 | {"machine":"AutoPlate 9000","person":["Marianne","Richer"]} | +| 2 | 2 | {"machine":"sterilizer","person":["Marianne","Richer"]} | +| 2 | 3 | {"machine":"AutoPlate 9000","person":["Monique","Marcotte"]} | +| 2 | 4 | {"machine":"sterilizer","person":["Marianne","Richer"]} | +| 3 | 0 | {"machine":"sterilizer","person":["Josette","Villeneuve"]} | +``` + +- `SELECT ident, json_each.key AS KEY, json_each.value AS value` — определяем, что нужно выбрать из таблицы `usage` и JSON-объектов, распарсенных с помощью функции `json_each`; из каждой строки выбираем идентификатор, а также ключ и его значение из каждого JSON-объекта в столбце `log` +- `FROM usage, json_each(usage.log)` — указываем источник данных для выборки; `usage` указывается после ключевого слова FROM, а `json_each` вызывается перед `log`, чтобы разобрать JSON-объекты из этого столбца +- `LIMIT 10` — выбираем только первые 10 строк + +### Последний элемент в массиве + +[src/json_array_last.sql](https://gvwilson.github.io/sql-tutorial/src/json_array_last.sql) + +``` +SELECT ident, + log->'$[#-1].machine' AS FINAL +FROM USAGE +LIMIT 5; +``` + +[out/json_array_last.out](https://gvwilson.github.io/sql-tutorial/out/json_array_last.out) + +``` +| ident | final | +|-------|--------------| +| 1 | "Inphormex" | +| 2 | "sterilizer" | +| 3 | "Inphormex" | +| 4 | "sterilizer" | +| 5 | "sterilizer" | +``` + +- `SELECT ident, log->'$[#-1].machine' AS FINAL` — выбираем 2 столбца из `machine`; `ident`возвращается как есть, а столбец `log` обрабатывается так: + - `log->'$[#-1].machine` — извлекаем данные из столбца `log` (`->` используется для доступа к JSON-полю в столбце `log`) + - `$[#-1]` — обращаемся к последнему элементу массива, который хранится в `log` + - `.machine` — хотим извлечь значение поля `machine` из объекта, находящегося в последнем элементе массива + +### Модифицируем JSON + +[src/json_modify.sql](https://gvwilson.github.io/sql-tutorial/src/json_modify.sql) + +``` +SELECT ident, + name, + json_set(details, '$.sold', json_quote('2024-01-25')) AS updated +FROM machine; +``` + +[out/json_modify.out](https://gvwilson.github.io/sql-tutorial/out/json_modify.out) + +``` +| ident | name | updated | +|-------|----------------|--------------------------------------------------------------| +| 1 | WY401 | {"acquired":"2023-05-01","sold":"2024-01-25"} | +| 2 | Inphormex | {"acquired":"2021-07-15","refurbished":"2023-10-22","sold":" | +| | | 2024-01-25"} | +| 3 | AutoPlate 9000 | {"note":"needs software update","sold":"2024-01-25"} | +``` + +- `SELECT ident, name, ... FROM machine;` — выбираем значения столбцов `ident` и `name` из таблицы `machine` +- `json_set(details, '$.sold', json_quote('2024-01-25')) AS updated` — при помощи `json_set`обновляем JSON-объект в столбце `details`; функция добавляет/изменяет свойство `sold` в JSON-объекте в столбце `details`, присваивая ему новое значение, полученное с помощью функции `json_quote`; результат сохраняем как `updated` + +**Обновляем табличку penguins:** + +[src/count_penguins.sql](https://gvwilson.github.io/sql-tutorial/src/count_penguins.sql) + +``` +SELECT species, + count(*) AS num +FROM penguins +GROUP BY species; +``` + +[out/count_penguins.out](https://gvwilson.github.io/sql-tutorial/out/count_penguins.out) + +``` +| species | num | +|-----------|-----| +| Adelie | 152 | +| Chinstrap | 68 | +| Gentoo | 124 | +``` + +### Immediate If в SQL + +[src/make_active.sql](https://gvwilson.github.io/sql-tutorial/src/make_active.sql) + +``` +ALTER TABLE penguins ADD active integer NOT NULL DEFAULT 1; + +UPDATE penguins +SET active = iif(species = 'Adelie', 0, 1); +``` + +- изменяем таблицу `penguins`, добавляя новый столбец `active` типа `integer`, который не может содержать значение `NULL`, и устанавливаем значение по умолчанию 1 для всех строк +- обновляем значения в столбце `active` в `penguins`; значение столбца `active` устанавливается на 0, если значение в `species` равно `'Adelie'`, иначе устанавливается на 1 +- функция `IIF` (Immediate If) используется здесь для реализации условного выражения (1 аргумент – условие, 2 – результат, если условие истинно, и 3 – результат, если условие ложно) + +[src/active_penguins.sql](https://gvwilson.github.io/sql-tutorial/src/active_penguins.sql) + +``` +SELECT species, + count(*) AS num +FROM penguins +WHERE active +GROUP BY species; +``` + +[out/active_penguins.out](https://gvwilson.github.io/sql-tutorial/out/active_penguins.out) + +``` +| species | num | +|-----------|-----| +| Chinstrap | 68 | +| Gentoo | 124 | +``` + +### Представление VIEW в SQL + +[src/views.sql](https://gvwilson.github.io/sql-tutorial/src/views.sql) + +``` +CREATE VIEW IF NOT EXISTS active_penguins (species, island, bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g, sex) AS +SELECT species, + island, + bill_length_mm, + bill_depth_mm, + flipper_length_mm, + body_mass_g, + sex +FROM penguins +WHERE active; + +SELECT species, + count(*) AS num +FROM active_penguins +GROUP BY species; +``` + +[out/views.out](https://gvwilson.github.io/sql-tutorial/out/views.out) + +``` +| species | num | +|-----------|-----| +| Chinstrap | 68 | +| Gentoo | 124 | +``` + +- создаём представление (`VIEW`) с именем `active_penguins`, если его еще не существует +- представление содержит столбцы `species`, `island`, `bill_length_mm`, `bill_depth_mm`, `flipper_length_mm`, `body_mass_g`, и `sex`; данные для представления берутся из `penguins`, при условии, что пингвины являются активными (`WHERE active`) +- выполняем выборку из представления `active_penguins`: выбираем вид пингвина (`species`) и количество таких пингвинов (`num`), удовлетворяющих условиям, заданным в представлении `active_penguins` + +**Напоминание о часах работы:** + +[src/all_jobs.sql](https://gvwilson.github.io/sql-tutorial/src/all_jobs.sql) + +``` +CREATE TABLE job (name text NOT NULL, + billable real NOT NULL); + +INSERT INTO job +VALUES ('calibrate', 1.5), + ('clean', 0.5); + +SELECT * +FROM job; +``` + +[out/all_jobs.out](https://gvwilson.github.io/sql-tutorial/out/all_jobs.out) + +``` +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +| clean | 0.5 | +``` + +### Добавляем проверку CHECK + +[src/all_jobs_check.sql](https://gvwilson.github.io/sql-tutorial/src/all_jobs_check.sql) + +``` +CREATE TABLE job (name text NOT NULL, + billable real NOT NULL, CHECK (billable > 0.0)); + +INSERT INTO job +VALUES ('calibrate', 1.5); + +INSERT INTO job +VALUES ('reset', -0.5); + +SELECT * +FROM job; +``` + +[out/all_jobs_check.out](https://gvwilson.github.io/sql-tutorial/out/all_jobs_check.out) + +``` +Runtime error near line 9: CHECK constraint failed: billable > 0.0 (19) +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +``` + +- создаём таблицу `job` с 2 столбцами, которые не могут быть пустыми: `name` (текстовый тип данных) и `billable` (вещественные тип данных) +- ограничение (`CHECK`) гарантирует, что значение столбца `billable` должно быть больше чем 0.0 +- добавляем новую запись в `job` с указанными значениями `'calibrate'` для столбца `name` и 1.5 для столбца `billable` — сейчас под условие `CHECK` это попадает +- пытаемся добавить еще одну запись в таблицу `job` с указанными значениями `'reset'` для столбца `name`и -0.5 для столбца `billable`. Однако, так как -0.5 меньше либо равно 0.0, то это нарушает условие `CHECK` + +**ACID** + +ACID — это акроним, который описывает набор свойств транзакций баз данных, предназначенных для обеспечения целостности данных в случае ошибок, сбоев питания и других непредвиденных ситуаций: + +1. **Атомарность (Atomicity)**: Транзакция должна быть атомарной, что означает, что она должна быть выполнена целиком или не выполнена вообще. Если одна часть транзакции не может быть выполнена, то все изменения, сделанные в рамках этой транзакции, должны быть отменены. +2. **Согласованность (Consistency)**: Транзакция должна приводить базу данных из одного согласованного состояния в другое согласованное состояние. Это означает, что все правила и ограничения, установленные на данные, должны быть соблюдены во время выполнения транзакции. +3. **Изолированность (Isolation)**: Транзакции должны быть изолированы друг от друга, чтобы предотвратить взаимное влияние. Каждая транзакция должна быть выполнена так, как если бы она была единственной выполняемой транзакцией в базе данных. Это гарантирует, что результаты одной транзакции не будут видны другим транзакциям до их завершения. +4. **Долговечность (Durability)**: Результаты выполненной транзакции должны быть постоянными и доступными даже в случае сбоя системы или перезагрузки. Это достигается путем записи изменений в постоянное хранилище, например SSD. + +### TRANSACTION в SQL + +[src/transaction.sql](https://gvwilson.github.io/sql-tutorial/src/transaction.sql) + +``` +CREATE TABLE job (name text NOT NULL, + billable real NOT NULL, CHECK (billable > 0.0)); + +INSERT INTO job +VALUES ('calibrate', 1.5); +BEGIN TRANSACTION; + +INSERT INTO job +VALUES ('clean', 0.5); +ROLLBACK; + +SELECT * +FROM job; +``` + +[out/transaction.out](https://gvwilson.github.io/sql-tutorial/out/transaction.out) + +``` +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +``` + +- создаём таблицу `job` с 2 колонками, которые не могут быть пустыми: + - `name` текстового типа + - `billable` с типом данных `real` (вещественное число) и условием `CHECK (billable > 0.0)`, что гарантирует, что значение `billable` больше 0.0 +- добавляем в `job` запись: `('calibrate', 1.5)` +- начинаем новую транзакцию. +- добавляем другую запись в таблицу `job`: `('clean', 0.5)` +- откатываем последнюю транзакцию, добавляя `'clean', 0.5`, поэтому данная строка не сохраняется + +### ROLLBACK в SQL + +[src/rollback_constraint.sql](https://gvwilson.github.io/sql-tutorial/src/rollback_constraint.sql) + +``` + CREATE TABLE job ( + name text NOT NULL, + billable real NOT NULL, + CHECK (billable > 0.0) ON CONFLICT ROLLBACK + ); + + INSERT INTO job + VALUES ('calibrate', 1.5); + + INSERT INTO job + VALUES ('clean', 0.5), + ('reset', -0.5); + + SELECT * + FROM job; +``` + +[out/rollback_constraint.out](https://gvwilson.github.io/sql-tutorial/out/rollback_constraint.out) + +``` +Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19) +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +``` + +- создаём новую таблицу с именем `job` и 2 непустыми столбцами: текстовым `name` и вещественным `billable` +- значение в `billable` должно быть больше 0 (`CHECK (billable > 0.0)`) +- добавляем в `job` запись с именем `calibrate` со значением `billable` 1.5 +- вторая запись с именем `clean` имеет значение `billable` равное 0.5 +- третья запись с именем `reset` имеет значение `billable` равное -0.5 — тут возникает проблема с записью третьей строки, так как это нарушает ограничение `CHECK (billable > 0.0)` + +### Откат с помощью ROLLBACK + +[src/rollback_statement.sql](https://gvwilson.github.io/sql-tutorial/src/rollback_statement.sql) + +``` +CREATE TABLE job (name text NOT NULL, + billable real NOT NULL, + CHECK (billable > 0.0)); +INSERT OR ROLLBACK INTO job +VALUES ('calibrate', 1.5); + +INSERT OR ROLLBACK INTO job +VALUES ('clean', 0.5), + ('reset', -0.5); + +SELECT * +FROM job; +``` + +[out/rollback_statement.out](https://gvwilson.github.io/sql-tutorial/out/rollback_statement.out) + +``` +Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19) +| name | billable | +|-----------|----------| +| calibrate | 1.5 | +``` + +- создаём таблицу `job` с 2 непустыми столбцами: + - текстовым столбцом `name` + - вещественнозначным `billable` с ограничением `CHECK (billable > 0.0)` — значение в этом столбце всегда будет больше нуля +- вставляем данные в `job` с помощью оператора `INSERT OR ROLLBACK`, а именно одну запись с названием `calibrate` и значением `billable` равным 1.5 +- вставляем ещё 2 записи в таблицу `job` с помощью оператора `INSERT OR ROLLBACK`: `clean` со значением 0.5 для `billable`, `reset` со значением -0.5 `billable` (что не подходит по условию `CHECK`) + +### Вставка значений + +[src/upsert.sql](https://gvwilson.github.io/sql-tutorial/src/upsert.sql) + +``` +CREATE TABLE jobs_done (person text UNIQUE, + num integer DEFAULT 0); + +INSERT INTO jobs_done +VALUES("zia", 1); + +.print "after first" +SELECT * +FROM jobs_done; + +.print +INSERT INTO jobs_done +VALUES("zia", 1); + +.print "after failed" +SELECT * +FROM jobs_done; + +INSERT INTO jobs_done +VALUES("zia", 1) ON conflict(person) DO +UPDATE +SET num = num + 1; + +.print "\nafter upsert" +SELECT * +FROM jobs_done; +``` + +[out/upsert.out](https://gvwilson.github.io/sql-tutorial/out/upsert.out) + +``` +after first +| person | num | +|--------|-----| +| zia | 1 | + +Runtime error near line 14: UNIQUE constraint failed: jobs_done.person (19) +after failed +| person | num | +|--------|-----| +| zia | 1 | + +after upsert +| person | num | +|--------|-----| +| zia | 2 | +``` + +- создаём `jobs_done` со столбцами `person` (текстовый тип данных с уникальными значениями) и `num`(целочисленный тип, по умолчанию равен 0) +- вставляем в `jobs_done` запись с именем `"zia"` и числом 1 +- пытаемся снова вставить строку с тем же именем `"zia"` и числом 1 и снова выводим результаты запроса `SELECT` +- вставляем строку с тем же именем `"zia"` и числом 1 но уже указываем, чтобы в случае конфликта по столбцу `person`, обновить значение столбца `num`, увеличив его на 1 + +### Создание триггера + +[src/trigger_setup.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_setup.sql) + +``` +-- Track hours of lab work. +CREATE TABLE job (person text NOT NULL, + reported real NOT NULL CHECK (reported >= 0.0)); + +-- Explicitly store per-person total rather than using sum(). +CREATE TABLE total (person text UNIQUE NOT NULL, + hours real); + +-- Initialize totals. +INSERT INTO total +VALUES ("gene", 0.0), + ("august", 0.0); + +-- Define a trigger. +CREATE TRIGGER total_trigger +BEFORE +INSERT ON job BEGIN -- Check that the person exists. + +SELECT CASE + WHEN NOT EXISTS + (SELECT 1 + FROM total + WHERE person = new.person) THEN raise + (ROLLBACK, 'Unknown person ') + END; -- Update their total hours (or fail if non-negative constraint violated). + +UPDATE total +SET hours = hours + new.reported +WHERE total.person = new.person; END; +``` + +- создаём таблицу `job` со столбцами `person` и `reported` +- создаём `total` со столбцами `person` и `hours` +- устанавливаем значения `gene` и `august` в 0.0 +- создаём триггер `total_trigger`, который срабатывает перед вставкой новых записей в таблицу `job`. Этот триггер: + - проверяет, существует ли человек в таблице `total`, прежде чем разрешить вставку новых записей в таблицу `job` + - обновляет общее количество отработанных часов для соответствующего человека в таблице `total`путем добавления нового количества отработанных часов из таблицы `job` + +[src/trigger_successful.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_successful.sql) + +``` +INSERT INTO job +VALUES ('gene', 1.5), + ('august', 0.5), + ('gene', 1.0); +``` + +[out/trigger_successful.out](https://gvwilson.github.io/sql-tutorial/out/trigger_successful.out) + +``` +| person | reported | +|--------|----------| +| gene | 1.5 | +| august | 0.5 | +| gene | 1.0 | + +| person | hours | +|--------|-------| +| gene | 2.5 | +| august | 0.5 | +``` + +**Срабатывание триггера** + +[src/trigger_firing.sql](https://gvwilson.github.io/sql-tutorial/src/trigger_firing.sql) + +``` +INSERT INTO job +VALUES ('gene', 1.0), + ('august', -1.0) ; +``` + +[out/trigger_firing.out](https://gvwilson.github.io/sql-tutorial/out/trigger_firing.out) + +``` +Runtime error near line 6: CHECK constraint failed: reported >= 0.0 (19) + +| person | hours | +|--------|-------| +| gene | 0.0 | +| august | 0.0 | +``` + +**Графическое представление** + +[src/lineage_setup.sql](https://gvwilson.github.io/sql-tutorial/src/lineage_setup.sql) + +``` +CREATE TABLE lineage (parent text NOT NULL, + child text NOT NULL); +INSERT INTO lineage +VALUES ('Arturo', 'Clemente'), + ('Darío', 'Clemente'), + ('Clemente', 'Homero'), + ('Clemente', 'Ivonne'), + ('Ivonne', 'Lourdes'), + ('Soledad', 'Lourdes'), + ('Lourdes', 'Santiago'); +``` + +[src/represent_graph.sql](https://gvwilson.github.io/sql-tutorial/src/represent_graph.sql) + +``` +SELECT * +FROM lineage; +``` + +[out/represent_graph.out](https://gvwilson.github.io/sql-tutorial/out/represent_graph.out) + +``` +| parent | child | +|----------|----------| +| Arturo | Clemente | +| Darío | Clemente | +| Clemente | Homero | +| Clemente | Ivonne | +| Ivonne | Lourdes | +| Soledad | Lourdes | +| Lourdes | Santiago | +``` + + + +lineage diagram + +### Рекурсивный запрос + +[src/recursive_lineage.sql](https://gvwilson.github.io/sql-tutorial/src/recursive_lineage.sql) + +``` + WITH RECURSIVE descendent AS ( + SELECT 'Clemente' AS person, + 0 AS generations + UNION ALL + SELECT lineage.child AS person, + descendent.generations + 1 AS generations + FROM descendent + JOIN lineage ON descendent.person = lineage.parent + ) + SELECT person, + generations + FROM descendent; +``` + +[out/recursive_lineage.out](https://gvwilson.github.io/sql-tutorial/out/recursive_lineage.out) + +``` +| person | generations | +|----------|-------------| +| Clemente | 0 | +| Homero | 1 | +| Ivonne | 1 | +| Lourdes | 2 | +| Santiago | 3 | +``` + +- определяем общий термин `descendent` (потомок) как рекурсивное общее выражение. Начинаем с одной записи, где `'Clemente'` – это начальное имя, а 0 – это количество поколений. +- далее мы выполняем рекурсивное объединение с самим собой (с `descendent`) и таблицей `lineage`, чтобы найти всех потомков для каждого найденного человека. Выбираем потомка из таблицы `lineage`, увеличиваем количество поколений на 1 и продолжаем делать это для всех найденных потомков, пока они находятся +- если новых потомков больше не найдено, используем `SELECT` для выбора столбцов `person` и `generations` из `descendent` + +**База данных отслеживания контактов** + +[src/contact_person.sql](https://gvwilson.github.io/sql-tutorial/src/contact_person.sql) + +``` +SELECT * +FROM person; +``` + +[out/contact_person.out](https://gvwilson.github.io/sql-tutorial/out/contact_person.out) + +``` +| ident | name | +|-------|-----------------------| +| 1 | Juana Baeza | +| 2 | Agustín Rodríquez | +| 3 | Ariadna Caraballo | +| 4 | Micaela Laboy | +| 5 | Verónica Altamirano | +| 6 | Reina Rivero | +| 7 | Elias Merino | +| 8 | Minerva Guerrero | +| 9 | Mauro Balderas | +| 10 | Pilar Alarcón | +| 11 | Daniela Menéndez | +| 12 | Marco Antonio Barrera | +| 13 | Cristal Soliz | +| 14 | Bernardo Narváez | +| 15 | Óscar Barrios | +``` + +[src/contact_contacts.sql](https://gvwilson.github.io/sql-tutorial/src/contact_contacts.sql) + +``` +SELECT * +FROM contact; +``` + +[out/contact_contacts.out](https://gvwilson.github.io/sql-tutorial/out/contact_contacts.out) + +``` +| left | right | +|-------------------|-----------------------| +| Agustín Rodríquez | Ariadna Caraballo | +| Agustín Rodríquez | Verónica Altamirano | +| Juana Baeza | Verónica Altamirano | +| Juana Baeza | Micaela Laboy | +| Pilar Alarcón | Reina Rivero | +| Cristal Soliz | Marco Antonio Barrera | +| Cristal Soliz | Daniela Menéndez | +| Daniela Menéndez | Marco Antonio Barrera | +``` + + + +contact diagram + +### Продолжаем работать с bi_contact + +[src/bidirectional.sql](https://gvwilson.github.io/sql-tutorial/src/bidirectional.sql) + +``` +CREATE TEMPORARY TABLE bi_contact (LEFT text, RIGHT text); + + INSERT INTO bi_contact + SELECT LEFT, + RIGHT + FROM contact +UNION ALL + SELECT RIGHT, + LEFT + FROM contact; +``` + +[out/bidirectional.out](https://gvwilson.github.io/sql-tutorial/out/bidirectional.out) + +``` +| original_count | +|----------------| +| 8 | + +| num_contact | +|-------------| +| 16 | +``` + +- создаём временную табличку `bi_contact` с 2 столбцами: `LEFT` и `RIGHT`, оба текстовые +- вставляем в `bi_contact` данные из другой таблицы при помощи `SELECT` +- используем `UNION ALL` для объединения результатов 2 операций `SELECT` в один набор данных; данные из столбца `LEFT` и `RIGHT` таблицы `contact` вставляем в таблицу `bi_contact`. Первый набор данных берёт значения из столбцов `LEFT` и `RIGHT` таблицы `contact`, а второй набор данных берёт значения из столбцов `RIGHT` и `LEFT` таблицы `contact` +- в общем, вставляем в `bi_contact` комбинацию значений из столбцов `LEFT` и `RIGHT` таблицы `contact`и их перевёрнутые комбинации + +### Обновляем идентификаторы групп + +[src/update_group_ids.sql](https://gvwilson.github.io/sql-tutorial/src/update_group_ids.sql) + +``` + SELECT left.name AS left_name, + left.ident AS left_ident, + right.name AS right_name, + right.ident AS right_ident, + min(left.ident, right.ident) AS new_ident + FROM ( + person AS +LEFT JOIN bi_contact ON left.name = bi_contact.left + ) + JOIN person AS RIGHT ON bi_contact.right = right.name; +``` + +[out/update_group_ids.out](https://gvwilson.github.io/sql-tutorial/out/update_group_ids.out) + +``` +| left_name | left_ident | right_name | right_ident | new_ident | +|-----------------------|------------|-----------------------|-------------|-----------| +| Juana Baeza | 1 | Micaela Laboy | 4 | 1 | +| Juana Baeza | 1 | Verónica Altamirano | 5 | 1 | +| Agustín Rodríquez | 2 | Ariadna Caraballo | 3 | 2 | +| Agustín Rodríquez | 2 | Verónica Altamirano | 5 | 2 | +| Ariadna Caraballo | 3 | Agustín Rodríquez | 2 | 2 | +| Micaela Laboy | 4 | Juana Baeza | 1 | 1 | +| Verónica Altamirano | 5 | Agustín Rodríquez | 2 | 2 | +| Verónica Altamirano | 5 | Juana Baeza | 1 | 1 | +| Reina Rivero | 6 | Pilar Alarcón | 10 | 6 | +| Pilar Alarcón | 10 | Reina Rivero | 6 | 6 | +| Daniela Menéndez | 11 | Cristal Soliz | 13 | 11 | +| Daniela Menéndez | 11 | Marco Antonio Barrera | 12 | 11 | +| Marco Antonio Barrera | 12 | Cristal Soliz | 13 | 12 | +| Marco Antonio Barrera | 12 | Daniela Menéndez | 11 | 11 | +| Cristal Soliz | 13 | Daniela Menéndez | 11 | 11 | +| Cristal Soliz | 13 | Marco Antonio Barrera | 12 | 12 | +``` + +### Рекурсивно устанавливаем метки + +[src/recursive_labeling.sql](https://gvwilson.github.io/sql-tutorial/src/recursive_labeling.sql) + +``` + WITH recursive labeled AS ( + SELECT person.NAME AS NAME, + person.ident AS label + FROM person + UNION -- not 'union all' + SELECT person.NAME AS NAME, + labeled.label AS label + FROM ( + person + JOIN bi_contact ON person.NAME = bi_contact.LEFT + ) + JOIN labeled ON bi_contact.RIGHT = labeled.NAME + WHERE labeled.label < person.ident + ) + SELECT NAME, + min(label) AS group_id + FROM labeled + GROUP BY NAME + ORDER BY label, + NAME; +``` + +[out/recursive_labeling.out](https://gvwilson.github.io/sql-tutorial/out/recursive_labeling.out) + +``` +| name | group_id | +|-----------------------|----------| +| Agustín Rodríquez | 1 | +| Ariadna Caraballo | 1 | +| Juana Baeza | 1 | +| Micaela Laboy | 1 | +| Verónica Altamirano | 1 | +| Pilar Alarcón | 6 | +| Reina Rivero | 6 | +| Elias Merino | 7 | +| Minerva Guerrero | 8 | +| Mauro Balderas | 9 | +| Cristal Soliz | 11 | +| Daniela Menéndez | 11 | +| Marco Antonio Barrera | 11 | +| Bernardo Narváez | 14 | +| Óscar Barrios | 15 | +``` + +- определяем рекурсивное выражение `labeled` — оно начинается с базового запроса, который выбирает имена и идентификаторы из таблицы person, + - затем используется `UNION` для объединения с другим запросом, который соединяет таблицы `person`и `bi_contact`, используя столбцы `name` и `left` в `bi_contact` и имена и метки из `person` + - затем объединяет результаты этого соединения с ранее помеченными записями из `labeled` + - `WHERE` устанавливает условие, что метка предыдущей записи должна быть меньше, чем идентификатор текущей записи `person` +- выполняем основной запрос — выбираем имена из `labeled` и вычисляем минимальную метку для каждого имени как `group_id` с помощью функции `min()` +- результат группируем по именам и сортируем сначала по метке, а затем по имени +- attention: только не используйте тут `UNION ALL`, иначе возникнет бесконечная рекурсия) + +### Работа с SQL в Python при помощи sqlite3 + +[src/basic_python_query.py](https://gvwilson.github.io/sql-tutorial/src/basic_python_query.py) + +``` +import sqlite3 + +connection = sqlite3.connect("db/penguins.db") +cursor = connection.execute("SELECT count(*) FROM penguins;") +rows = cursor.fetchall() +print(rows) +``` + +[out/basic_python_query.out](https://gvwilson.github.io/sql-tutorial/out/basic_python_query.out) + +``` +[(344,)] +``` + +- импортируем библиотечку `sqlite3` (к слову, она является одной из стандартных библиотек) для работы с SQLite +- устанавливаем соединение с БД, расположенной в файле `"db/penguins.db"`, используя метод `sqlite3.connect()`. Если этого файл не существует, то он будет создан +- создаём объект `cursor` для выполнения SQL-запросов +- `select count(*) from penguins;` — подсчитываем количество всех записей в таблице `penguins` +- `fetchall()` — получаем результат выполнения запроса, сохраняем его в переменную `rows` + +### Инкрементная выборка + +[src/incremental_fetch.py](https://gvwilson.github.io/sql-tutorial/src/incremental_fetch.py) + +``` +import sqlite3 + +connection = sqlite3.connect("db/penguins.db") +cursor = connection.cursor() +cursor = cursor.execute("SELECT species, island FROM penguins LIMIT 5;") +while row := cursor.fetchone(): + print(row) +``` + +[out/incremental_fetch.out](https://gvwilson.github.io/sql-tutorial/out/incremental_fetch.out) + +``` +('Adelie', 'Torgersen') +('Adelie', 'Torgersen') +('Adelie', 'Torgersen') +('Adelie', 'Torgersen') +('Adelie', 'Torgersen') +``` + +- коннектимся к БД с помощью `sqlite3.connect("db/penguins.db")` +- `connection.cursor()` — создаём объект `cursor`, это указатель на результат выполнения запросов +- `select species, island from penguins limit 5;` — выбираем первые 5 записей из таблицы `penguins`, возвращая значения столбцов `species` и `island` +- пока переменная `row` из `cursor.fetchone()` непустая, печатаем её (мы сразу создаём переменную `row` и тут же используем её при помощи `:=` ) + +### Простые операции CREATE, INSERT, DELETE и другие с помощью sqlite3 + +[src/insert_delete.py](https://gvwilson.github.io/sql-tutorial/src/insert_delete.py) + +``` +import sqlite3 + +connection = sqlite3.connect(":memory:") +cursor = connection.cursor() +cursor.execute("CREATE TABLE example(num integer);") + +cursor.execute("INSERT INTO example VALUES (10),(20);") +print("after insertion", cursor.execute("SELECT * FROM example;").fetchall()) + +cursor.execute("DELETE FROM example WHERE num < 15;") +print("after deletion", cursor.execute("SELECT * FROM example;").fetchall()) +``` + +[out/insert_delete.out](https://gvwilson.github.io/sql-tutorial/out/insert_delete.out) + +``` +after insertion [(10,), (20,)] +after deletion [(20,)] +``` + +- `connection = sqlite3.connect(":memory:")` — создаём подключение к БД SQLite, созданной в оперативной памяти +- `cursor = connection.cursor()` — создаём объект курсора, который используется для выполнения операций в БД +- `cursor.execute("CREATE TABLE example(num integer);")` — создаём новую таблицу с именем `example` и одним столбцом `num` для хранения целых чисел +- `cursor.execute("INSERT INTO example VALUES (10),(20);")` — вставляем 2 строки в `example` с числами 10 и 20 в столбец `num` +- `print("after insertion", cursor.execute("SELECT * FROM example;").fetchall())` — выводим содержимое таблицы `example` после вставки строк; выполняем операцию `SELECT`, чтобы выбрать все строки из таблицы, используя метод `.fetchall()` для извлечения результатов запроса +- `cursor.execute("DELETE FROM example WHERE num < 15;")` — удаляем строки из таблицы `example`, в которых значение столбца `num` меньше 15 +- ну и в конце выводим содержимое таблицы `example` после удаления строк; также выполняем `SELECT`, чтобы выбрать все строки из таблицы, используя метод `.fetchall()` для извлечения результатов запроса + +### Интерполируем значения + +[src/interpolate.py](https://gvwilson.github.io/sql-tutorial/src/interpolate.py) + +``` +import sqlite3 + +connection = sqlite3.connect(":memory:") +cursor = connection.cursor() +cursor.execute("CREATE TABLE example(num integer);") + +cursor.executemany("insert into example values (?);", [(10,), (20,)]) +print("after insertion", cursor.execute("SELECT * FROM example;").fetchall()) +``` + +[out/interpolate.out](https://gvwilson.github.io/sql-tutorial/out/interpolate.out) + +``` +after insertion [(10,), (20,)] +``` + + + +XKCD Exploits of a Mom + +- `connection = sqlite3.connect(":memory:")` — устанавливаем соединение с БД SQLite в оперативной памяти +- `cursor = connection.cursor()` — создаём объект курсора, который используется для выполнения операций БД +- `cursor.execute("create table example(num integer);")` — создаём таблицу `example` с одним столбцом `num` типа integer +- `cursor.executemany("insert into example values (?);", [(10,), (20,)])` — вставляем значения 10 и 20 в столбец `num` таблицы `example` с использованием параметризованного запроса + +### Выполнение полноценных SQL-запросов в Python + +[src/script_execution.py](https://gvwilson.github.io/sql-tutorial/src/script_execution.py) + +``` +import sqlite3 + +SETUP = """\ +DROP TABLE IF EXISTS example; +CREATE TABLE example(num integer); +INSERT INTO example +VALUES (10), + (20); +""" + +connection = sqlite3.connect(":memory:") +cursor = connection.cursor() +cursor.executescript(SETUP) +print("after insertion", cursor.execute("SELECT * FROM example;").fetchall()) +``` + +[out/script_execution.out](https://gvwilson.github.io/sql-tutorial/out/script_execution.out) + +``` +after insertion [(10,), (20,)] +``` + +- удаляем таблицу `example`, если она существует +- создаём таблицу `example` с одним столбцом `num` типа integer +- вставляем 2 записи в таблицу `example` с числами 10 и 20 +- выполняем `SETUP` с помощью метода курсора `executescript()`, который создает новую таблицу и вставляет данные +- выводим `after insertion` для обозначения того, что последующий запрос к базе данных будет относиться к состоянию после вставки данных +- выполняем запрос к БД для выбора всех записей из таблицы `example` с помощью метода `execute()` и `fetchall()` для извлечения результатов + +### Исключения SQLite в Python + +[src/exceptions.py](https://gvwilson.github.io/sql-tutorial/src/exceptions.py) + +``` +import sqlite3 + +SETUP = """\ +CREATE TABLE example(num integer check(num > 0)); + +INSERT INTO example +VALUES (10); + +INSERT INTO example +VALUES (-1); + +INSERT INTO example +VALUES (20); +""" + +connection = sqlite3.connect(":memory:") +cursor = connection.cursor() +try: + cursor.executescript(SETUP) +except sqlite3.Error as exc: + print(f"SQLite exception: {exc}") +print("after execution", cursor.execute("SELECT * FROM example;").fetchall()) +``` + +[out/exceptions.out](https://gvwilson.github.io/sql-tutorial/out/exceptions.out) + +``` +SQLite exception: CHECK constraint failed: num > 0 +after execution [(10,)] +``` + +- устанавливаем соединение с БД в оперативной памяти с помощью `sqlite3.connect(":memory:")` +- создаём курсор для выполнения операци +- создаём таблицу `example` и вставляем в нее 3 значения с помощью `executescript()` +- в блоке `try-except` обрабатывается исключение `sqlite3.Error`, если произойдет какая-либо ошибка при выполнении запросов +- выводим содержимое таблицы `example` после выполнения запросов с помощью метода `fetchall()` + +### Python и SQLite, ещё некоторые возможности + +[src/embedded_python.py](https://gvwilson.github.io/sql-tutorial/src/embedded_python.py) + +``` +import sqlite3 + +SETUP = """\ +CREATE TABLE example(num integer); +INSERT INTO example +VALUES (-10), + (10), + (20), + (30); +""" + +def clip(value): + if value < 0: + return 0 + if value > 20: + return 20 + return value + +connection = sqlite3.connect(":memory:") +connection.create_function("clip", 1, clip) +cursor = connection.cursor() +cursor.executescript(SETUP) +for row in cursor.execute("SELECT num, clip(num) FROM example;").fetchall(): + print(row) +``` + +[out/embedded_python.out](https://gvwilson.github.io/sql-tutorial/out/embedded_python.out) + +``` +(-10, 0) +(10, 10) +(20, 20) +(30, 20) +``` + +- создаём БД SQLite в оперативной памяти, создаём табличку `example`, заполняем её таблицу значениями (-10, 10, 20, 30) +- затем определяем функцию `clip`, которая принимает один аргумент и возвращает этот аргумент, если он находится между 0 и 20, или возвращает 0, если аргумент меньше 0, или возвращает 20, если аргумент больше 20 +- выбираем значения из столбца `num` таблицы `example` и применяет функцию `clip` к каждому значению + +### Работа с датой и временем + +[src/dates_times.py](https://gvwilson.github.io/sql-tutorial/src/dates_times.py) + +``` +from datetime import date +import sqlite3 + +# Convert date to ISO-formatted string when writing to database +def _adapt_date_iso(val): + return val.isoformat() + +sqlite3.register_adapter(date, _adapt_date_iso) + +# Convert ISO-formatted string to date when reading from database +def _convert_date(val): + return date.fromisoformat(val.decode()) + +sqlite3.register_converter("date", _convert_date) + +SETUP = """\ +CREATE TABLE events(happened date NOT NULL, + description text NOT NULL); +""" + +connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES) +cursor = connection.cursor() +cursor.execute(SETUP) + +cursor.executemany( + "insert into events values (?, ?);", + [(date(2024, 1, 10), "started tutorial"), (date(2024, 1, 29), "finished tutorial")], +) + +for row in cursor.execute("SELECT * FROM EVENTS;").fetchall(): + print(row) +``` + +[out/dates_times.out](https://gvwilson.github.io/sql-tutorial/out/dates_times.out) + +``` +(datetime.date(2024, 1, 10), 'started tutorial') +(datetime.date(2024, 1, 29), 'finished tutorial') +``` + +- определяем функцию `_adapt_date_iso(val)` — она принимает дату и возвращает ее строковое представление в формате `ISO` +- определяем функцию `_convert_date(val)` — она принимает строку в формате `ISO` и возвращает объект типа `date` +- затем эти функции регистрируются в SQLite, чтобы обеспечить корректное преобразование данного типа данных при записи и чтении из базы данных +- после этого создается строка `SETUP`, которая содержит SQL-команду для создания таблицы `events` с двумя столбцами: `happened` типа `date` и `description` типа `text` +- с помощью `cursor.executemany` в таблицу `events` вставляются 2 записи с использованием значений типа `date` и строк +- с помощью `select * from events` и `cursor.execute` извлекаем значения всех строк из таблицы `events` + +### SQL в Jupyter Notebooks + +``` +pip install jupysql +``` + +``` +%load_ext sql +``` + +``` +%sql sqlite:///data/penguins.db +``` + +``` +Connecting to 'sqlite:///data/penguins.db' +``` + +- Подключение к БД: + - `sqlite://` — протокол с 2 слэшами в конце + - `/data/penguins.db` — 1 слэш спереди, это путь к локальной БД +- 1 знак процента `%sql` — для выполнения однострочных SQL-запросов +- 2 знака процента `%%sql` показывает, что вся ячейка будет восприниматься как один SQL-запрос + +``` +%%sql +SELECT species, + count(*) AS num +FROM penguins +GROUP BY species; +``` + +``` +Running query in 'sqlite:///data/penguins.db' +``` + +|species|num| +|---|---| +|Adelie|152| +|Chinstrap|68| +|Gentoo|124| + +### Pandas и SQL + +[src/install_pandas.sh](https://gvwilson.github.io/sql-tutorial/src/install_pandas.sh) + +``` +pip install pandas +``` + +[src/select_pandas.py](https://gvwilson.github.io/sql-tutorial/src/select_pandas.py) + +``` +import pandas as pd +import sqlite3 + +connection = sqlite3.connect("db/penguins.db") +query = "SELECT species, count(*) AS num FROM penguins GROUP BY species;" +df = pd.read_sql(query, connection) +print(df) +``` + +[out/select_pandas.out](https://gvwilson.github.io/sql-tutorial/out/select_pandas.out) + +``` +species num +0 Adelie 152 +1 Chinstrap 68 +2 Gentoo 124 +``` + +- `select species, count(*) as num from penguins group by species;` — извлекаем информацию о количестве пингвинов каждого вида из `penguins` и группируем результаты по видам +- выполняем запрос к БД с использованием метода `read_sql` библиотеки `pandas`, который читает результаты запроса и преобразует их в объект `DataFrame` (df) + +### Polars и SQL + +[src/install_polars.sh](https://gvwilson.github.io/sql-tutorial/src/install_polars.sh) + +``` +pip install polars pyarrow adbc-driver-sqlite +``` + +[src/select_polars.py](https://gvwilson.github.io/sql-tutorial/src/select_polars.py) + +``` +import polars as pl + +query = "SELECT species, count(*) AS num FROM penguins GROUP BY species;" +uri = "sqlite:///db/penguins.db" +df = pl.read_database_uri(query, uri, engine="adbc") +print(df) +``` + +[out/select_polars.out](https://gvwilson.github.io/sql-tutorial/out/select_polars.out) + +``` +shape: (3, 2) +┌───────────┬─────┐ +│ species ┆ num │ +│ --- ┆ --- │ +│ str ┆ i64 │ +╞═══════════╪═════╡ +│ Adelie ┆ 152 │ +│ Chinstrap ┆ 68 │ +│ Gentoo ┆ 124 │ +└───────────┴─────┘ +``` + +- импортирует библиотеку Polars – она похожа на pandas, но с фокусом на параллельную обработку данных +- выбираем столбец `species` и вычисляем количество записей для каждого вида пингвинов из таблицы `penguins`; результат группируем по столбцу `species` +- устанавливаем строку подключения к базе данных SQLite в переменной `uri` +- используем `pl.read_database_uri` для выполнения SQL-запроса `query` к БД, указанной в `uri`, используя движок `adbc` +- выводим результат выполнения запроса в виде таблицы данных + +### ORM + +[src/orm.py](https://gvwilson.github.io/sql-tutorial/src/orm.py) + +``` +from sqlmodel import Field, Session, SQLModel, create_engine, select + +class Department(SQLModel, table=True): + ident: str = Field(default=None, primary_key=True) + name: str + building: str + +engine = create_engine("sqlite:///db/assays.db") +with Session(engine) as session: + statement = select(Department) + for result in session.exec(statement).all(): + print(result) +``` + +[out/orm.out](https://gvwilson.github.io/sql-tutorial/out/orm.out) + +``` +building='Chesson' name='Genetics' ident='gen' +building='Fashet Extension' name='Histology' ident='hist' +building='Chesson' name='Molecular Biology' ident='mb' +building='TGVH' name='Endocrinology' ident='end' +``` + +- создаём класс `Department`, который представляет модель данных для отделов; каждый атрибут класса соответствует столбцу в таблице БД +- создаём объект `engine`, который представляет собой подключение к SQLite БД, где `assays.db` – это имя файла БД +- создаём `Session` для взаимодействия с базой данных через созданный `engine` +- формируем SQL-запрос с помощью `select(Department)`, который выбирает все данные из таблицы, представленной моделью `Department` +- выполняем запрос к БД через `session.exec(statement).all()`, который возвращает все строки, удовлетворяющие условию запроса + +### Продолжаем работать с ORM + +[src/orm_relation.py](https://gvwilson.github.io/sql-tutorial/src/orm_relation.py) + +``` +class Staff(SQLModel, table=True): + ident: str = Field(default=None, primary_key=True) + personal: str + family: str + dept: Optional[str] = Field(default=None, foreign_key="department.ident") + age: int + +engine = create_engine("sqlite:///db/assays.db") +SQLModel.metadata.create_all(engine) +with Session(engine) as session: + statement = select(Department, Staff).where(Staff.dept == Department.ident) + for dept, staff in session.exec(statement): + print(f"{dept.name}: {staff.personal} {staff.family}") +``` + +[out/orm_relation.out](https://gvwilson.github.io/sql-tutorial/out/orm_relation.out) + +``` +Histology: Divit Dhaliwal +Molecular Biology: Indrans Sridhar +Molecular Biology: Pranay Khanna +Histology: Vedika Rout +Genetics: Abram Chokshi +Histology: Romil Kapoor +Molecular Biology: Ishaan Ramaswamy +Genetics: Nitya Lal +``` + +- объявляем класс `Staff`; он использует SQLModel, что позволяет использовать этот класс как схему для создания таблицы в БД. Указание `table=True` в качестве аргумента класса говорит SQLModel о том, что данный класс должен отображаться в базу данных как таблица. У `Staff` есть несколько атрибутов : + - `ident` – строковое поле, которое будет использоваться в качестве первичного ключа в базе данных. Оно имеет значение по умолчанию `None` и задается как первичный ключ (`primary_key=True`) + - `personal` – строковое поле + - `family` – строковое поле + - `dept` – опциональное строковое поле; имеет значение по умолчанию `None` и устанавливается как внешний ключ (`foreign_key="department.ident"`) + - `age` – целочисленное поле +- после определения `Staff`, создается экземпляр движка для работы с БД SQLite с помощью вызова функции `create_engine` из библиотеки SQLAlchemy +- затем вызываем метод `create_all` у метаданных SQLModel, что приводит к созданию всех таблиц, определенных в виде классов с помощью SQLModel, на основе ранее созданного движка базы данных +- далее устанавливаем сессия БД с использованием созданного ранее движка +- формируется SQL-запрос, который выбирает данные из таблиц `Department` и `Staff`, объединяя их по условию, что поле `Staff.dept` равно полю `Department.ident` +- выполняем этот запрос в сессии БД, и для каждой строки результата выводится название отдела и персональные данные сотрудника
\ No newline at end of file |