SQL osnovne naredbe (upiti)

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):

sqlite3 DatabaseName.db

Nakon čega će se kreirati baza u trenutnom direktorijumu a u Command Prompt-u se pojavljuje:

sqlite>

Verifikovanje baze podataka se vrši sa naredbom:

sqlite> .databases

Osnovne naredbe za rad sa tabelama

Kreiranje tabele (CREATE TABLE)

Sintaksa za kreiranje tabele je sledeća:

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

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:

.tables.

Pregled šeme tabele (.schema)

Da bi smo videli šemu po kojoj je napravljna tabela koristimo:

sqlite>.schema NAZIV_TABELE

Brisanje tabele (DROP TABLE)

Brisanje odredjene tabele vršimo prema sledećoj sintaksi:

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:

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:

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:

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:

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”

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:

DELETE FROM table_name
WHERE [condition];

Primer

U ovome primeru će biti obrisan član tabele čiji ID je 6:

DELETE FROM COMPANY WHERE ID = 6;

Dobijanje podataka (upit) iz tabele (SELECT)

Sintaksa ove naredbe koja selektuje podatke je sledeća:

SELECT column1, column2, columnN FROM table_name;

Primer

sqlite> SELECT  NAME, ID FROM COMPANY;

Pa ovaj izraz vraća samo dve kolone:

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:

sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY;

Medjutim to možemo uraditi i na jednostavniji način koristeći wildcard * (zvezdu):

sqlite> SELECT * FROM COMPANY;

Što vraća rezultat:

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.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

Primer

SELECT * FROM COMPANY WHERE AGE >= 25;

Ovaj uslov vraća sve redove gde je AGE veći ili jednak od 25:

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:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

Primer

sqlite> SELECT * FROM COMPANY LIMIT 3;

Vraća sledeće:

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:

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 3;

Dobijamo nastavak tabele:

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:

SELECT column-list
FROM table_name
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Primer

sqlite>  SELECT * FROM COMPANY ORDER BY SALARY ASC;

Ovaj izraz vraća sve članove tabele poredjane po rastućem redosledu kolone SALARY:

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.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Primer

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY > 25000;

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.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Primer

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

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

WHERE SALARY LIKE '200%'

Vraća rezultate koji počinju sa 200 (2005, 20035, 200559…)

Primer

WHERE SALARY LIKE '%200%'

Vraća rezultate koji u sebi sadrže 200 (152005, 1420035…)

Primer

WHERE SALARY LIKE '%2'

Vraća rezultate koji na kraju sadrže 2(152, 14205552…)

Primer

WHERE SALARY LIKE '_2%3'

Vraća rezultate kojima je druga cifra 2 a poslednja 3(123, 1205553…)

Primer

WHERE SALARY LIKE '2_%_%'

Vraća rezultate kojima je prva cifra 2 a ima minimum još dve cifre.

Primer

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

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:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name

Primer

sqlite> SELECT DISTINCT NAME, AGE FROM COMPANY;

Ovaj izraz vraća kolonu NAME i AGE ali samo sa jedinstvenim članovima u kolinii NAME:

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|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:

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:

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.

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:

sqlite> .header on
sqlite> .mode column
sqlite> .width 5 30 20
sqlite> SELECT * FROM COMPANY;

Sada sredjeni izlaz izgleda ovako:

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:

sqlite> .excel

A zatim da defišnemo šta želimo da uključimo u taj izlaz:

sqlite> SELECT * FROM COMPANY;