Uvod
SQL (Structured Query Language) je jezik koji se koristi pri upravljanju relacionim bazama. Ovaj jezik je dizajniran da korisniku omogući čuvanje, pronalaženje, upravljanje ili manipulisanje podacima unutar sistema za upravljanje bazama podataka (DBMS).
U ovome članku će biti reči o naredbama koje su sastavni deo SQL jezika a koji se koristi za rad sa “relacionim sistemom za upravljanje bazama” (DBMS) pod nazivom “SQLite” (trenutna verzija ovog DBMS je 3.0 tj. sqllite3). Pored ovog “Database Management System”-a naredbe se mogu primeniti uz manju korekciju i za druge relacione sisteme (MySql, PostgreSQL, Microsoft SQL Server…). Osnovne naredbe za rad sa SQL bazama su veoma slične u svim DBMS-ma mada svaki sistem ima i svoje specifičnosti (npr. Microsoft SQL server je statički tipiziran dok SQLite dinamički…). Više o sistemima za upravljanje bazama pročitajte u članku “Sistemi za upravljanje bazama (DBMS)”.
Šta je sqlite3?
sqlite3 je frontend alat u Command Prompt-u ili terminalu za rad sa SQLite bibliotekom. Koristeći ga možemo vršiti upiti (query) i prikazati rezultate nezavisno od neke druge aplikacije. Instaliranje ovoga alata možete pogledati ovde.
Naredbe u sqlite3
U sqlite3 postoje dve vrste naredbi: one koje započinju sa tačkom pa iza njih nije potrebano davati znak SQL engine-u da je kraj naredbe i one koje ne počinju tačkom ali moraju da daju engine-u znak gde je kraj naredbe sa tačka-zarezom. Za izlazak iz sqlite3 je neophodno da u terminalu koristimo .quit ili .exit ili eventualno “CTRL + C”.
Kreiranje i otvaranje baze
Naredba za kreiranje ili otvaranje baze podataka je ista. Da li će se tabela kreirati ili samo otvoriti zavisi od toga dali postoji ili ne. Ako ne postoji onda će se kreirati, a ako u datom folderu postoji takva baza onda će se otvoriti. Sintaksa je sledeća: prvo napišemo sqlite3 a zatim i ime baze zajedno sa ekstenzijom (.db):
|
1 |
sqlite3 DatabaseName.db |
Nakon čega će se kreirati baza u trenutnom direktorijumu a u Command Prompt-u se pojavljuje:
|
1 |
sqlite> |
Verifikovanje baze podataka se vrši sa naredbom:
|
1 |
sqlite> .databases |
Osnovne naredbe za rad sa tabelama
Kreiranje tabele (CREATE TABLE)
Sintaksa za kreiranje tabele je sledeća:
|
1 2 3 4 5 6 7 |
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype ); |
Podrazumevano, kolona može da sadrži NULL vrednosti (tj.neki član može da nema vrednost u nekoj od kolona). Ograničenje NOT NULL obavezuje da član da NE prihvati da nema vrednost u nekom polju tj. da ima NULL vrednost. Ovim se zahteva da polje uvek sadrži vrednost, što znači da ne možete ubaciti novi član ili ažurirati stari bez dodavanja vrednosti u polju koje ima definisano “NOT NULL”.
Primer
|
1 2 3 4 5 6 7 |
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); |
U prethodnom primeru su ID, NAME, i AGE obavazna polja.
Izlistavanje svih tabela baze (.tables)
Izlistavanje svih tabla baze se vrši sa naredbom:
|
1 |
.tables. |
Pregled šeme tabele (.schema)
Da bi smo videli šemu po kojoj je napravljna tabela koristimo:
|
1 |
sqlite>.schema NAZIV_TABELE |
Brisanje tabele (DROP TABLE)
Brisanje odredjene tabele vršimo prema sledećoj sintaksi:
|
1 |
DROP TABLE database_name.table_name; |
Ubacivanje člana tabele (INSERT INTO)
Ubacivanje novog člana tabele podrazumeva dodavanje novog reda ne kraj tabele i vrši se sa naredbom INSERT INTO.
Postoje dve vrste sintaksi:
- tzv. “Detaljana” sintaksa
- tzv. “Jednostavna” sintaksa
Detaljna sintaksa:
|
1 2 |
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN); |
I kod nje je potrebno navesti nazive kolona te tabele pa teko onda ubaciti nove vrednosti.
Primer
U prethodnom primeru smo napravili tabelu pod nazivom COMPANY čije su kolone (ID,NAME,AGE,ADDRESS,SALARY), pa bi ubacivanje svakog novog člana ovako izgledalo:
|
1 2 3 4 5 |
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); |
Jednostavna sintaksa
Pojednostavljena sintaksa podrazumeva da se ne pominju nazivi kolona, već samo vrednosti:
|
1 |
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 ); |
Ažuriranje člana tabele (UPDATE)
Ovaj upit se koristi za modifikovanje postojećeg reda tabele. Sintaksa izgleda ovako:
|
1 2 3 |
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition]; |
Primer
Naredba u ovome primeru će u okviru tabele “COMPANY” članu tabele čiji ID je 6 promeniti adresu kompanije u “Texas”
|
1 |
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6; |
NAPOMENA:
Ukoliko ne bi korisili klauzulu WHERE onda bi svim članovima tabele bila promenjena adresa u “Texas”.
Brisanje člana tabele (DELETE)
Brisanje odredjenog člana/reda tabele se vrši uz sintaksu:
|
1 2 |
DELETE FROM table_name WHERE [condition]; |
Primer
U ovome primeru će biti obrisan član tabele čiji ID je 6:
|
1 |
DELETE FROM COMPANY WHERE ID = 6; |
Dobijanje podataka (upit) iz tabele (SELECT)
Sintaksa ove naredbe koja selektuje podatke je sledeća:
|
1 |
SELECT column1, column2, columnN FROM table_name; |
Primer
|
1 |
sqlite> SELECT NAME, ID FROM COMPANY; |
Pa ovaj izraz vraća samo dve kolone:
|
1 2 3 4 5 6 7 8 |
NAME ID ---------- ---------- Paul 1 Allen 2 Teddy 3 Mark 4 David 5 Kim 6 |
NAPOMENA:
Redosled kolona u okviru naredbe ne mora da prati redosled u bazi!
Primer
Često želimo da prikažemo sve kolone jedne tabele i to možemo da uradimo navodeći baš sve nazive kolona:
|
1 |
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY; |
Medjutim to možemo uraditi i na jednostavniji način koristeći wildcard * (zvezdu):
|
1 |
sqlite> SELECT * FROM COMPANY; |
Što vraća rezultat:
|
1 2 3 4 5 6 7 8 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 |
WHERE klauzula
Ova klauzula se koristi da definiše uslov.
|
1 2 3 |
SELECT column1, column2, columnN FROM table_name WHERE [condition] |
Primer
|
1 |
SELECT * FROM COMPANY WHERE AGE >= 25; |
Ovaj uslov vraća sve redove gde je AGE veći ili jednak od 25:
|
1 2 3 4 5 6 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 |
LIMIT & OFFSET klauzula
LIMIT ograničava broj redova koji su selektovani, dok OFFSET preskače odredjeni broj redova. Sintaksa izgleda ovako:
|
1 2 3 |
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] |
Primer
|
1 |
sqlite> SELECT * FROM COMPANY LIMIT 3; |
Vraća sledeće:
|
1 2 3 4 5 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 |
A ako dodamo OFFSET koji će da preskoči ova prva tri reda:
|
1 |
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 3; |
Dobijamo nastavak tabele:
|
1 2 3 4 5 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 |
ORDER BY klauzula
Ova klauzula se koristi da bi poredjala članove tabele u opadajućem ili rastućem redosledu. Stavlja se uvek na kraj izraza a sintaksa izgleda ovako:
|
1 2 3 |
SELECT column-list FROM table_name [ORDER BY column1, column2, .. columnN] [ASC | DESC]; |
Primer
|
1 |
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC; |
Ovaj izraz vraća sve članove tabele poredjane po rastućem redosledu kolone SALARY:
|
1 2 3 4 5 6 7 8 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 |
AND operator
Ovaj operator omugućavava da se koriste višestruki uslovi koji trebaju da budu zadovoljeni.
|
1 2 3 |
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; |
Primer
|
1 |
SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY > 25000; |
|
1 2 3 4 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 |
OR operator
Ovaj operator takodje omugućavava da se sastavi višestruki uslov ali je dovoljno da samo jedan uslov bude zadovoljen.
|
1 2 3 |
SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN] |
Primer
|
1 |
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; |
|
1 2 3 4 5 6 |
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 |
LIKE operator
Operator LIKE se koristi za pronalaženja rezulta kod kojih se podudaraju tekstualne vrednosti sa zahtevanim (nije case sensitive). Ako se tekst za pretragu podudara sa izrazom uzorka, operator LIKE će vratiti true. Postoje dva džoker znaka koja se koriste zajedno sa operatorom LIKE:
- % (procenat) – predstavlja nula, jedan ili više karaktera
- _ (underscore) – predstavlja samo jedan karakter
Primer
|
1 |
WHERE SALARY LIKE '200%' |
Vraća rezultate koji počinju sa 200 (2005, 20035, 200559…)
Primer
|
1 |
WHERE SALARY LIKE '%200%' |
Vraća rezultate koji u sebi sadrže 200 (152005, 1420035…)
Primer
|
1 |
WHERE SALARY LIKE '%2' |
Vraća rezultate koji na kraju sadrže 2(152, 14205552…)
Primer
|
1 |
WHERE SALARY LIKE '_2%3' |
Vraća rezultate kojima je druga cifra 2 a poslednja 3(123, 1205553…)
Primer
|
1 |
WHERE SALARY LIKE '2_%_%' |
Vraća rezultate kojima je prva cifra 2 a ima minimum još dve cifre.
Primer
|
1 |
WHERE ADDRESS LIKE '%-%'; |
Vraća rezultate koji u tekstu imaju srednju crtu (South-Hall)
GLOB operator
Ovaj operator je skoro isti kao LIKE stim što je “case sensitive” i koristi druge oznake za wildcard iako imaju isto značenje
- * (zvezda) – predstavlja nula, jedan ili više karaktera (procenat kod LIKE)
- ? (znak pitanja) – predstavlja samo jedan karakter (underscore kod LIKE)
Primer
|
1 |
WHERE SALARY GLOB '?2*3' |
Vraća rezultate kojima je druga cifra 2 a poslednja 3(123, 1205553…)
DISTINCT
Ova ključna reč uz SELECT omogućava da izlaz koji vraća izraz nema “duplikate”. Sintaksa je ovakva:
|
1 2 |
SELECT DISTINCT column1, column2,.....columnN FROM table_name |
Primer
|
1 |
sqlite> SELECT DISTINCT NAME, AGE FROM COMPANY; |
Ovaj izraz vraća kolonu NAME i AGE ali samo sa jedinstvenim članovima u kolinii NAME:
|
1 2 3 4 5 6 7 8 |
NAME AGE ---------- ---------- Paul 32 Allen 25 Teddy 23 Mark 25 David 27 Kim 22 |
Export i formatiranje izlaza
Nesredjen izlaz tj. prikaz tabele izgleda ovako:
|
1 2 3 4 5 6 |
1|Paul|32|California|20000.0 2|Allen|25|Texas|15000.0 3|Teddy|23|Norway|20000.0 4|Mark|25|Rich-Mond |65000.0 5|David|27|Texas|85000.0 6|Kim|22|South-Hall|45000.0 |
Medjutim postoje odredjene naredbe koje mogu da poboljšaju ovaj izgled i da doprinesu boljoj čitljivosti.
.header (prikazivanje naziva kolona)
Prikazivanje header-a sa nazivima kolona se omogućava sa naredbom:
|
1 |
sqlite>.header on |
.mode (izgled tabele)
Izgled prikazane tabele zavisi od izabranog moda:
- csv − Comma-separated values
- column − Left-aligned columns.
- html − HTML <table> code
- insert − SQL insert statements for TABLE
- line − One value per line
- list − Values delimited by .separator string
- tabs − Tab-separated values
- tcl − TCL list elements
Najčešće se koristi mod column jer je najpregledniji:
|
1 |
sqlite>.mode column |
.width (širina kolona)
Ponekad se dešava da defaultn-a širina kolona (10 slova) nije dovoljna pa možemo da predefinišemo širinu sa naredbom .width.
|
1 |
sqlite>.width 10, 30, 20 |
Nakon ovog prve tri kolone će imati prosledjene širine.
Primer
Videli smo kako izgleda ne sredjeni izlaz tabele iz prethodnih primera, sad ćemo to poboljšati sledećim naredbama:
|
1 2 3 4 |
sqlite> .header on sqlite> .mode column sqlite> .width 5 30 20 sqlite> SELECT * FROM COMPANY; |
Sada sredjeni izlaz izgleda ovako:
|
1 2 3 4 5 6 7 8 |
ID NAME AGE ADDRESS SALARY ----- ------------------------------ -------------------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 |
NAPOMENA:
Jednom definisana pravila za izgled izlaza ostaju sve dok ne promenimo bazu.
Eksportovanje u excel
Za eksportovanje tabele u eksel je prvo potrebno da definišemo tip izlaza:
|
1 |
sqlite> .excel |
A zatim da defišnemo šta želimo da uključimo u taj izlaz:
|
1 |
sqlite> SELECT * FROM COMPANY; |
Instaliranje sqlite3 alata
- Otići na stranicu za download “SQLite download page”, i izabrati za download tamo gde je “Precompiled Binaries for …” uz sekciju gde piše ” bundle of command-line tools for managing SQLite database files….”
- Kreirati novi folder (npr. pod nazivom sqlite) i u njega staviti unzip-ovan sadržaj iz downloadovanog foldera (sqlite3.def, sqlite3.dll i sqlite3.exe). Nakon ovoga ukoliko želimo možemo da u Command Prompt-u da koristimo sqlite3 ali naša putanja mora da ukazuje baš na ovaj folder C:\>sqlite.
-
Da bi omogućili pristup ovoj alatki sa bilo kog mesta, potrebno je da sačuvamo putanju do novo-kreiranog foldera gde smo stavili izvršne fajlove u “Environment Variables”.
- Control Panel > System > Advanced System Settings > Environment Variables
- U okviru “User variables” se potraži da li postoji već variabla “Path”
- Ako postoji onda je editovati, a ako ne potrebno je praviti novu sa “New”
- Variabla treba da bude “Path” a Value putanja do foldera gde je prebačeni instalacioni sqlite3 fajlovi (najčešće C:\sqlite)
- Sačuva se na dugme OK
NAPOMENA:
Potrebno je restartovati “Command Prompt” (terminal) da bi videli izmene!
Da bi smo bili sigurni da je sve Ok, dovoljno je u Command Prompt-u napisati sqlite3, nakon čega bi trebali da dobijemo ovakav odgovor:
|
1 2 3 4 5 6 |
λ sqlite3 SQLite version 3.32.2 2020-06-04 12:58:43 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> . |