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 вираховується і зберігається