SQLite

(Data Manipulation Language, Data Definition Language, Transaction Control Language , Data Conrol Language)

Для навчання на убунту встановив собі sqlite3 та sqlite3 browser:

sudo apt install sqlite3
sudo apt install sqlitebrowser

Для гарного відображення:

.headers on
.mode column

(Це в рамках навчання, для андроіду це робити не треба, а також в SQLite не має концепції логін/пароль)

1) Створити базу даних:

sqlite3 anyDBName.db

2) Створюємо таблицю:

CREATE TABLE anyTableName (
FirstFieldName INTEGER PRIMARY KEY AUTOINCREMENT,
SecondFieldName TEXT,
ThirdFieldName INTEGER);

PrimaryKey означає що дане поле є головним. Autoincrement означає що дане поле кожної нової строки таблиці буде мати наступне значення.

3) Створиться файл бази даних (скоріш за все на робочому столі), якщо є бажання , можна спробувати його відкрити графічно через sqliebrowser та переглянути ці поля.

4) Додати данні в таблицю:

INSERT INTO AnyTableName (SecondFieldName, ThirdFieldName) VALUES ('blabla', 123);


INSERT INTO artists DEFAULT VALUES;

5) Переглянути записи таблиці: -- Всі записи:

SELECT * FROM anyTableName;

--Вивести всі id

SELECT id FROM anyTableName;

--Вивести всі id та name

SELECT id, name FROM anyTableName;

--Вивести всі id+name+age

SELECT id, name, age FROM anyTableName;

--distinct - унікальні. Вивоить всі унікальні записи в полі Quantity таблиці invoice_items

SELECT DISTINCT Quantity FROM invoice_items;

--Обрати стовбчики invoicelineid, invoiceid, trackid,quantity змінивши invoiceid+100 та trackid*3 з таблиці invoice_items)

SELECT invoicelineid, invoiceid+100, trackid*3,quantity FROM invoice_items;

-- Вивести 5 шт із здвигом у 4

SELECT trackid FROM invoice_items LIMIT 5 OFFSET 4

--Конкатенація строк

SELECT firstname || lastname FROM customers;

SELECT 'My name is '||firstname||' and surname is '|| lastname FROM customers;

ALIAS: Для гарного виводу назви колонки

SELECT 'My name is '||firstname||' and surname is '|| lastname as "Знайомство  з іменами" FROM customers;


SELECT 'Мар''яна' as "Знайомство з іменами";

(колонка буде називатись Знайомство з іменами) (одинарні кавички двічі означають апостроф)

6) Фільтрування WHERE:

SELECT name FROM myFirstTable WHERE id>=3;

(вивести поля name з таблиці myFirstTable в яких поле id більше-дорівнює 3)

SELECT * FROM employees WHERE EmployeeId BETWEEN 2 AND 7;

(Значення між) **BETWEEN '2010-01-01' AND '2010-01-31' - для дат. Також можна NOT BETWEEN

SELECT * FROM employees WHERE EmployeeId IN (2,4,5,8);

(Обирає рядки в яких EmployeeId рівний 2,4,5,8)

SELECT * FROM invoices WHERE BillingState IS NULL;

(Обрати всі рядки з таблиці invoices де колонка BillingState рівна null)

SELECT * FROM employees WHERE LastName LIKE 'P_';

SELECT * FROM employees WHERE LastName LIKE 'P%';

--Вивести всі рядки з таблиці employees в яких колонка LastNa me складається з 2 літер і починається на Р) --Вивести всі рядки з таблиці employees в яких колонка Lastname починається на Р)

SELECT * FROM employes WHERE first_name='Steven' AND last_name='King';

(Умви можна комбінувати за допомогою OR та AND)

--Екранувати % та _ можна наступним чином:

SELECT * FROM customers WHERE Email LIKE 'isabelle\_%' escape '\'; )

(Замість LIKE можна використовувати GLOB 'Unixlike_patters'

7) СОРТУВАННЯ!

SELECT firstName, LastName, fax FROM customers ORDER BY fax(або вираз,функція, або число-номер стовбчика, наприклад 2) DESC(або ASC) NULLS FIRST(або LAST);

(сортувати можна по колонці, виразу або числу, за спаданням чи зростанням, та виводити NULL спочатку або в кінці)

SELECT first,Name, lastName,salary, id order by id asc ,lastName desc, salary desc;

(Композитне сортування: сортує по id, для тих в кого id однакові, сортувати їх по lastName, для тих в кого id та lastNmae однакові - сортувати по salary)

8) Видалення рядку таблиці:

DELETE FROM myFirstTable WHERE id=6;

(видалення рядку в якому id=6)

8) Оновлення данних у таблиці:

UPDATE myFirstTable SET name="new Name" WHERE id=5;

(В таблиці myFirstTable поставити поле name у значеня new Name для запису в якому id=5)

9) Зміна імені таблиці:

ALTER TABLE  myFirstTable RENAME TO newName;

10) Видалення таблиці:

DROP TABLE myFirstTable;

Робота з датами: https://www.sqlite.org/lang_datefunc.html Всі доступні функції гарно задокументовані

Типи даних: https://www.sqlite.org/datatype3.html

Всі функції SQLite: https://www.sqlite.org/lang_corefunc.html

Функції агрегації: https://www.sqlite.org/lang_aggfunc.html (ВАЖЛИВО: в селектах з функціями агрегації неможна разом використовувати звичайні(single row функції)

AVG, SUM, COUNT, MIN, MAX (серднє арифметичне, сумма всіх int значень у стовпцях, підрахунок рядків, мінімальне і максимальне значення у стовпчику

Про REPLACE можна прочитати тут: https://www.sqlitetutorial.net/sqlite-replace-statement/

The idea of the REPLACE statement is that when a UNIQUE or PRIMARY KEY constraint violation occurs, it does the following:

  • First, delete the existing row that causes a constraint violation.
  • Second, insert a new row.

In the second step, if any constraint violation e.g., NOT NULL constraint occurs, the REPLACE statement will abort the action and roll back the transaction.

Example:

REPLACE INTO table(column_list) VALUES(value_list);

(по синтаксису схоже на INSERT)

Додати до бази даних ще одну:

ATTACH DATABASE 'c:\sqlite\db\contacts.db' AS contacts;

***ALTER TABLE

Використовується для 3 речей:

  1. Перейменувати таблицю
  2. Додати стовбчик
  3. Перейменувати стовбчитк

Приклад:

ALTER TABLE devices RENAME TO equipment;
ALTER TABLE table_name ADD COLUMN column_definition;
ALTER TABLE table_name RENAME COLUMN current_name TO new_name;

***ДИНАІЧНО РАХУЄМІ стовпчики:

Якщо якийсь стовбчик має вираховуватись в залежності від того які значення в інших стовпчиках, то

column_name data_type [GENERATED ALWAYS] AS expression [VIRTUAL | STORED]

Приклад:

За замовчуванням якщо в кінці не вказати STORED то буде VIRTUAL.

VIRTUAL вираховується кожен раз за зверненням до стовбчика, а STORED вираховується і зберігається