SQL osnovne naredbe

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

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

Verifikovanje baze podataka se vrši sa naredbom:

Osnovne naredbe za rad sa tabelama

Kreiranje tabele (CREATE TABLE)

Sintaksa za kreiranje tabele je sledeća:

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

U prethodnom primeru su ID, NAME, i AGE obavazna polja.

Izlistavanje svih tabela baze (.tables)

Izlistavanje svih tabla baze se vrši sa naredbom:

Pregled šeme tabele (.schema)

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

Brisanje tabele (DROP TABLE)

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

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:

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:

Jednostavna sintaksa

Pojednostavljena sintaksa podrazumeva da se ne pominju nazivi kolona, već samo vrednosti:

Ažuriranje člana tabele (UPDATE)

Ovaj upit se koristi za modifikovanje postojećeg reda tabele. Sintaksa izgleda ovako:

Primer

Naredba u ovome primeru će u okviru tabele “COMPANY” članu tabele čiji ID je 6 promeniti adresu kompanije u “Texas”

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:

Primer

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

Dobijanje podataka iz tabele (SELECT)

Sintaksa ove naredbe koja selektuje podatke je sledeća:

Primer

Pa ovaj izraz vraća samo dve kolone:

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:

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

Što vraća rezultat:

WHERE klauzula

Ova klauzula se koristi da definiše uslov.

Primer

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

LIMIT & OFFSET klauzula

LIMIT ograničava broj redova koji su selektovani, dok OFFSET preskače odredjeni broj redova. Sintaksa izgleda ovako:

Primer

Vraća sledeće:

A ako dodamo OFFSET koji će da preskoči ova prva tri reda:

Dobijamo nastavak tabele:

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:

Primer

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

AND operator

Ovaj operator omugućavava da se koriste višestruki uslovi koji trebaju da budu zadovoljeni.

Primer

OR operator

Ovaj operator takodje omugućavava da se sastavi višestruki uslov ali je dovoljno da samo jedan uslov bude zadovoljen.

Primer

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

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

Primer

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

Primer

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

Primer

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

Primer

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

Primer

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

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:

Primer

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

Export i formatiranje izlaza

Nesredjen izlaz tj. prikaz tabele izgleda ovako:

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:

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

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

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:

Sada sredjeni izlaz izgleda ovako:

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:

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

×

Instaliranje sqlite3 alata
  1. 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….”

  2. 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.
  3. 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”.

    1. Control Panel > System > Advanced System Settings > Environment Variables
    2. U okviru “User variables” se potraži da li postoji već variabla “Path”
    3. Ako postoji onda je editovati, a ako ne potrebno je praviti novu sa “New”
    4. Variabla treba da bude “Path” a Value putanja do foldera gde je prebačeni instalacioni sqlite3 fajlovi (najčešće C:\sqlite)
    5. 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:


Sistemi za upravljanje SQL bazama (DBMS)

Uvod

mysql

Pre svega treba napraviti razliku izmedju pojmova: “baza podataka”, “sistem za upravljanje bazama podataka” (tzv.DBMS “Database Management System”) i samog “SQL jezika”.
“Relacijska baza podataka” je digitalna baza podataka na osnovu relacijskog modela podataka i predstavlja skup povezanih podataka koji zamenjuju neki aspekt stvarnog sveta.
“Relaciski sistem za upravljanje bazama podataka” (RDBMS) je softver koji se koristi za održavanje relacijskih baza podataka. On pruža interfejs između podataka i softverske aplikacije tj. prihvata zahtev za podacima iz aplikacije i nalaže operativnom sistemu da pruži određene podatke.
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).

SQLlite

SQLite je “file-based” sistem koji ne zahteva nikakvu instalaciju ili podešavanje izuzetno male veličine oko 700KB. To znači da se aplikacija ne pokreće u okviru odvojenog procesa servera koji treba pokrenuti, zaustaviti ili konfigurisati. Ova arhitektura bez servera omogućava da baza podataka bude kompatibilna sa više platformi.

Kompletna baza podataka SQL sadržana je u jednoj datoteci diska i sva čitanja i upisivanja odvijaju se direktno na ovoj datoteci diska. Budući da baza podataka SQLite ne zahteva administraciju, ona dobro funkcioniše na uređajima koji moraju da rade bez ljudske podrške. SQLite je pogodan za upotrebu u mobilnim telefonima, set-top box uređajima, televizorima, igraćim konzolama… Ovo je jednostavan sistem koji podržava samo pet tipova podataka: BLOB, NULL, INTEGER, TEKST, REAL (dok napreednije dbms podržavaju skoro sve moguće tipove). Upravo njegova jednostavnost omogućava da za razliku od drugih naprednijih dbms-a bude veoma brz.

sqlite

Jedan od glavnih nedostataka SQLite sistema je nedostatak mogućnosti rada sa više korisnika koje se mogu naći u RDBMS sistemima kao što su MySQL i PostgreSQL. Pošto radi samo sa jednim korisnikom SQLite ne pruža sistem za potvrdu identiteta pri pristupu datoteci baze podataka. Još jedan veliki nedostatak SQLite-a je rukovanje operacijama upisivanja koje su serializovane. Ovo može biti glavno usko grlo za aplikacije koje zahtevaju istovremeno izvršavanje operacija.

MySQL

Za razliku od SQLite, MySQL koristi arhitekturu server/klijent koja se sastoji od SQL servera sa više niti. Ova višenitna priroda MySQL-a omogućava veće performanse jer niti jezgra mogu lako koristiti više CPU-a. Ovaj sistem ima sigurnosne funkcije koje omogućavaju autentifikaciju korisnika, sistem upravljanja korisničkim računom i šifrovane veze pomoću SSL-a. Za razliku od SQLlite MySQL podržava rada sa više korisnika. Ovaj sistem je dostupan na svim većim platformama: Windows, Linux i Mac OS X.

Mana MySQL-a je ta da veći broj INSERT-ova može imati negativan uticaj na performanse (u ovome je odličan PostgreSQL). Takođe ne radi dobro sa dugotrajnim SELECT-ima te je u slučaju MySQL-a najbolje koristi manje SELECT-e.

PostgreSQL

postgresql

Kao i MySQL, PostgreSQL koristi model baze podataka tipa klijent/server. PostgreSQL ima mogućnost da obrađuje istovremene klijentske sesije stvaranjem („račvanje“) novog procesa za svaku vezu. Poput MySQL, PostgreSQL takođe ima nekoliko naprednih funkcija vezanih za sigurnosti i replikaciju. Zbog svojih superiornih mogućnosti paralelne obrade, PostgreSQL je odličan (za razliku od MySQL-a) kada pokreće dugačke SELECT-ove. Kada se poredi sa MySQL-om u potrošnji energije tu je značajno lošiji i ima veliku potrošnju. Još jedan veliki nedostatak može se uočiti tokom čestih UPDATE-a, gde zbog nepodržane grupisanih indeksa PostgreSQL može imati negativan uticaj na performanse u poređenju sa MySQL bazama.

Microsoft SQL Server

microsoft sql server

Ovo je Microsoft-ov sistem za upravljanje relacionim bazama koj takodje koristi model baze podataka tipa klijent/server. Prednosti ovog sistema su relativno jednostavno održavanje, lak za upotrebu i razumevanje, a pristup i administracija su vrlo jednostavni. Može se koristiti kod malih i velikih projekata. SQL Server je razvio Microsoft prvobitno samo za operativni sistem Vindovs, mada je Microsoft objavio odluku da RDBMS učini dostupnim i na Linuksu kao i na Mac OS (putem Dockera).

Jedna od mana ovoga sistem je što novije verzije trebaju napredne tehnologije za pokretanje. Dakle, ako se vaš hardver sastoji uglavnom od starijeg hardvera, možda ćete morati da investirate u novije mašine da biste koristili Microsoft SQL Server.