Untitled Notes

Univerza v Ljubljani, Fakulteta za računalništvo in informatiko

Osnove podatkovnih baz

Modul: Poizvedovalni jezik SQL

Gradivo: v.2017

Vsebina

Splošno o SQL ter zgodovina razvoja
Sintaksa SQL
Ukazi iz skupine DML: SELECT, INSERT, UPDATE, DELETE
Ukazi iz skupine DDL: CREATE, ALTER, SET TRANSACTION,…
Sprožilci, funkcije in shranjeni programi
Dostopanje do MySQL iz programskih jezikov

Uvod v SQL

SQL je razdeljen na dve glavni skupini ukazov:
- DDL (Data Definition Language): ukazi za opredelitev strukture podatkovne baze.
- DML (Data Manipulation Language): ukazi za poizvedovanje in ažuriranje podatkov.
Do leta 1999 SQL ni podpiral ukazov za kontrolni tok; ta del lahko obvladamo s programskimi jeziki ali interaktivno.

Primeri osnovnih SQL ukazov

Ukaz za ustvarjanje tabele:
```sql
CREATE TABLE oseba (
EMŠO NUMBER(13),
ime CHAR(20),
priimek CHAR(20)
);

- Ukaz za poizvedbo tabele:

sql
SELECT ime, priimek FROM oseba WHERE ime = 'Tine' ORDER BY priimek;

# Lastnosti SQL  
- **Enostavni in nepogojni**: SQL funkcije so zasnovane tako, da so enostavne za razumevanje.  
- **Funkcionalna vs. proceduralna**: SQL opisuje, kaj želi, ne kako to doseči.  
- **Krepitev brezplačne uporabe**: SQL se uporablja v različnih vlogah (skrbniki podatkovnih baz, vodstvo, razvijalci IT rešitev, končni uporabniki).  
- **Standardizacija**: SQL je uradni standard za delo z relacijskimi podatkovnimi bazami.  

# Zgodovina SQL  
- V 1970-ih IBM razvija sistem **System R**, ki temelji na relacijskem modelu.  
- Leta 1974 D. Chamberlin in F. Boyce definirata jezik **SEQUEL** (Structured English Query Language), ki se kasneje preimenuje v SQL.  
- V poznih 1970-ih razvije podjetje Relational Software (danes Oracle) svoj sistem SUPB, ki implementira SQL.  
- Poleti leta 1979 Oracle izda prvo komercialno različico SQL, nekaj tednov pred IBM-ovo implementacijo **System/38**  

# Standardizacija SQL  
- **SQL:2011**: Standard podrobno opisuje različne vidike SQL, kot so:  
  - Framework Foundation  
  - Call-Level Interface  
  - Persistent Stored Modules  
  - Management of External Data  
  - Object Language Bindings  
  - Information and Definition Schemas  
  - SQL Routines and Types (Java)  
  - XML-Related Specifications  
- Dostopni standardi: ISO/ANSI SQL:2011 standard na voljo za približno $450.  

# Implementacije SQL  
- Razlike med standardi SQL-92, SQL:1999, SQL:2008,...  
- Implementacije dobaviteljev SUPB se razlikujejo in vsebujejo različne dialekte.  
- Pri primerjavi implementacij SQL se lahko sklicujemo na spletno stran http://troels.arvin.dk/db/rdbms/.  

# SQL DML (Data Manipulation Language)  
## DML ukazi  
- DML ukazi vključujejo SQL stavke za manipulacijo s podatki.  
  - **SELECT**: Izbira podatkov.  
  - **INSERT**: Dodajanje podatkov.  
  - **DELETE**: Brisanje podatkov.  
  - **UPDATE**: Spreminjanje podatkov.  
- Sintaksa za SELECT stavek je najbolj kompleksna.  

# Sintaksa SELECT

sql
SELECT [DISTINCT | ALL] (* | [columnExpression [AS newName]] [, …])
FROM TableName [alias] [, …]
[WHERE condition]
[GROUP BY columnList]
[HAVING condition]
[ORDER BY columnList];

## Pojasnila za sestavne dele SELECT  
- **SELECT**: Določa stolpce, ki se pojavijo v izhodni relaciji.  
- **FROM**: Določa tabele za poizvedbo.  
- **WHERE**: Filtrira vrstice v izhodu.  
- **GROUP BY**: Združuje vrstice po vrednostih izbranih stolpcev.  
- **HAVING**: Filtrira skupine glede na določene pogoje.  
- **ORDER BY**: Določa vrstni red vrstic v izhodu.  

Vrstnega reda sklopov ni možno spreminjati! Obvezna sta samo SELECT in FROM sklopa.  

# Primeri učenja z uporabo MySQL  
- Testni podatki:  
  - Uporabimo bazo **Employees.db**.  
  - Spletna povezava za prenos: https://launchpad.net/test-db/+download.  
  - Tabele v bazi: **Employees**, **Salaries**, **Titles**, **Dept_emp**, **Departments**, **Dept_manager**.  
  - Skupaj 3.919.015 zapisov.  
- Uporabljena shema “employees”:  
  - Fields: emp_no, birth_date, first_name, last_name, gender, hire_date.  
  - Fields for titles: emp_no, title, from_date, to_date.  
  - Fields for salaries: emp_no, salary, from_date, to_date.  
  - Fields for dept_emp: emp_no, dept_no, from_date, to_date.  
  - Fields for departments: dept_no, dept_name.  
  - Fields for dept_manager: dept_no, emp_no, from_date, to_date.  

## Ali je shema popolna? Ji kaj manjka?  

# Predstavitev SQL primerov  
- **SQL pravila:**  
  - Izrazi v SQL so neobčutljivi na velikost črk.  
  - Rezervirane besede SQL ne smemo uporabljati za definicije podatkovnih objektov (tabel, stolpcev itd.).  
- **Sintaksa, ki jo bomo uporabljali za primere:**

sql
SELECT * FROM employees WHERE ime = 'Marko';

- Barvna označitev:  
  - **Modra**: Deli, ki so del sintakse jezika SQL ali konkretne vrednosti.  
  - **Rdeča**: Predmeti podatkovne baze (imena tabel, stolpcev, indeksov...).  
  - **Oranžni okvir**: Pomembni deli, ki so ključni za obravnavani primer.  

# Enostaven izpis podatkov  
- **Izpiši vse podatke o zaposlenih:**

sql
SELECT * FROM employees;

- **Izpiši imena in priimke zaposlenih:**

sql
SELECT firstname, lastname FROM employees;

# Uporaba DISTINCT  
- **Izpiši vse različne vrednosti v stolpcu gender:**

sql
SELECT DISTINCT gender FROM employees;

# Izračunana polja in funkcije  
- **Izpiši starost delavcev ob zaposlitvi:**

sql
SELECT firstname, lastname, birthdate, hiredate, DATEDIFF(hiredate, birthdate)/365 AS 'Starost ob zaposlitvi' FROM employees;

- **Alternativa:**

sql
SELECT firstname, lastname, YEAR(hiredate)-YEAR(birthdate) AS 'Starost ob zaposlitvi' FROM employees;

# Iskalni kriteriji  
- **Izpiši vse zaposlene moškega spola, rojene pred 1953:**

sql
SELECT * FROM employees WHERE YEAR(birth_date)<1953 AND gender = 'M';

- **Iskanje z uporabo BETWEEN:**

sql
SELECT * FROM employees WHERE birth_date BETWEEN '1955-12-30' AND '1955-12-31';

- **Iskanje s členstvom množice:**

sql
SELECT * FROM titles WHERE title IN ('Staff', 'Senior Staff');

- **Iskanje z vzorcem:**

sql
SELECT * FROM employees WHERE lastname LIKE 'B%' AND birthdate LIKE ' _ 6 _ _ _ _01';

- **Iskanje za NULL vrednostmi:**

sql
SELECT * FROM employees WHERE hire_date IS NULL;

# Sortiranje vrstic v izhodni relaciji  
- **Izpiši podatke o zaposlenih po priimku naraščajoče in po datumu rojstva padajoče:**

sql
SELECT empno, lastname, birthdate FROM employees ORDER BY lastname, birth_date DESC;

# Agregiranje podatkov  
- **ISO standard definira pet agregatnih operacij:**  
  - **COUNT**: vrne število vrednosti v določenem stolpcu.  
  - **SUM**: vrne seštevek vrednosti v določenem stolpcu.  
  - **AVG**: vrne povprečje vrednosti v določenem stolpcu.  
  - **MIN**: vrne najmanjšo vrednost v določenem stolpcu.  
  - **MAX**: vrne največjo vrednost v določenem stolpcu.  
- **Agregiranje deluje na enem stolpcu, vrne le eno vrednost.**  
- **COUNT**, **MIN**, in **MAX** uporabimo za numerične in ne-numerične vrednosti, **SUM** in **AVG** pa zahtevata numerične vrednosti.  
- **COUNT(*)** prešteje vse vrstice, ne glede na NULL vrednosti ali duplikate.  

# Uporaba COUNT z DISTINCT  
- **Število različnih delovnih mest v tabeli titles:**

sql
SELECT COUNT(DISTINCT title) FROM titles;

# Uporaba več agregatov  
- **Izpiši minimalno, maksimalno in povprečno plačo:**

sql
SELECT MIN(salary), MAX(salary), AVG(salary) FROM salaries;

# Združevanje podatkov  
- **Uporaba GROUP BY:**  
  - **Združevanje podatkov v skupine**.  
  - Ukazi SELECT in GROUP BY so tesno povezani.

sql
SELECT empno, AVG(salary) FROM salaries GROUP BY empno;

- **Pravila za GROUP BY:**  
  - Stolpci v SELECT tudi v GROUP BY (razen stolpci, ki nastopajo le v agregatnih operacijah).  
  - Prvi se izvaja WHERE, nato se združi preostale vrstice.  

# Omejitev skupin  
- **HAVING** sklopa postavlja pogoje za skupine v rezultatih.  
- Stolpci v HAVING sklopa morajo biti tudi v SELECT sklopa ali agregatih.

sql
SELECT YEAR(birthdate), COUNT() FROM employees WHERE gender = 'F' GROUP BY YEAR(birthdate)
HAVING COUNT() > 9200;

# Gnezdenje poizvedb  
- **Vnesteni SELECT stavki** se uporabijo v WHERE ali HAVING sklopih drugega SELECT stavka.

sql
SELECT firstname, lastname FROM employees
WHERE empno IN (SELECT empno FROM titles WHERE title = 'manager');

- **Primer gnezdenega stavka:**

sql
SELECT firstname, lastname FROM employees
WHERE empno IN (SELECT empno FROM titles GROUP BY emp_no HAVING COUNT(*) > 2);

# Uporaba ANY/SOME in ALL  
- **Primer uporabe ANY:**  
- Uporaba operatorjev ANY/SOME in ALL v gnezdnih poizvedbah, ki vračajo en sam stolpec.  
  - **ANY/SOME**: Pogoj se izpolni, če velja za vsaj eno vrednost poizvedbe.  
  - **ALL**: Pogoj se izpolni, če velja za vse vrednosti poizvedbe. 
- V primeru praznega rezultata:  
  - **ALL** vrne true,  
  - **ANY/SOME** vrne false.  

# Uporaba EXISTS  
- **EXISTS lahko uporabimo le v gnezdnih poizvedbah in vrača logični rezultat true/false.**  
- True: če obstaja vsaj ena vrstica v tabeli, ki je rezultat gnezdne poizvedbe.  
- False: če gnezdna poizvedba vrne prazno množico.

sql
SELECT E.empno, E.firstname, E.lastname, E.hiredate
FROM employees E
WHERE NOT EXISTS (SELECT E1.empno FROM employees E1 WHERE E1.hiredate < E.hire_date);

# Poizvedbe po več tabelah  
- Poizvedbe po več tabelah izvajamo z uporabo gnezdnih  SELECT stavkov.  
- V poizvedbah, ki vračajo stolpce različnih tabel, moramo uporabljati stike.  
- V klavzuli FROM navedemo tabele, v klavzuli WHERE določimo stolpce za stik.  
- **Sinonimi (alias) za ločevanje med istoimenskimi stolpci:**

sql
SELECT E.empno, E.firstname, E.lastname FROM employees E, deptmanager DM, departments D
WHERE E.empno = DM.empno AND DM.deptno = D.deptno AND D.dept_name = 'marketing';

# Alternativni načini stika več tabel  
- **FROM employees E JOIN titles T ON E.emp_no = T.emp_no**  
- **FROM employees JOIN titles USING emp_no**  
- **FROM employees NATURAL JOIN titles**  
- Zgornji zapisi nadomestijo sklopa FROM in WHERE.  
- V prvem primeru rezultat vsebuje dva identična stolpca emp_no.  

# Primer naravnega stika

sql
SELECT * FROM employees NATURAL JOIN titles;

# Zunanji stik  
- **Primer LEFT JOIN:**

sql
SELECT E.empno, E.firstname, E.lastname FROM employees E LEFT JOIN deptmanager DM ON E.empno = DM.empno;

- Dodatne oblike: RIGHT JOIN, FULL OUTER JOIN.  

# Uporaba operacij nad množicami  
- Rezultate dveh ali več poizvedb lahko združujemo z ukazi:  
  - **UNION** (unija)  
  - **INTERSECTION** (presek)  
  - **DIFFERENCE (EXCEPT)** (razlika)  
- Da lahko izvajamo te operacije, morata biti tabeli A in B skladni (domene atributov morajo biti enake).  
- Različne implementacije imajo glede tega zelo različna pravila!  

# Uporaba unije  
- **Primer unije:**

sql
SELECT EM.firstname, EM.lastname, 'Moški'
FROM (SELECT * FROM employees WHERE gender = 'M' ORDER BY hiredate DESC LIMIT 3) EM UNION SELECT EF.firstname, EF.lastname, 'Ženske' FROM (SELECT * FROM employees WHERE gender = 'F' ORDER BY hiredate DESC LIMIT 3) EF;

# INSERT stavek  
- **Sintaksa:**

sql
INSERT INTO TableName [(columnList)] VALUES (dataValueList);

- Seznam columnList ni obvezen; pri vnosu moramo vpisati najmanj vse obvezne vrednosti (not null), razen za stolpce s privzeto vrednostjo (DEFAULT).  
- Seznam dataValueList mora ustrezati seznamu columnList.  

# Primeri INSERT stavkov  
- **Vnos nove vrstice v tabelo departments:**

sql
INSERT INTO departments (deptno, deptname) VALUES ('d010', 'Education');

- **Posebnosti:**  
  - Obravnava neobveznih polj.  
  - Privzete vrednosti so neobvezne.  

# UPDATE stavek  
- **Sintaksa:**

sql
UPDATE TableName SET columnName1 = dataValue1 [, columnName2 = dataValue2…] [WHERE searchCondition];

- **Primer za dvig plače za vse zaposlene:**

sql
UPDATE salaries SET salary = salary * 1.1 WHERE to_date = '9999-01-01';

# DELETE stavek  
- **Sintaksa:**

sql
DELETE FROM TableName [WHERE searchCondition];

- **Primer:**

sql
DELETE FROM salaries WHERE empno NOT IN (SELECT empno FROM salaries WHERE to_date = '9999-01-01');

# DDL (Data Definition Language)  
## Ukazi DDL  
- **DDL skupina** zajema SQL stavke za manipulacijo s strukturo podatkovne baze.  
- Ključni DDL ukazi vključujejo:  
  - **CREATE**, **ALTER**, **DROP**  
  - Oblikovanje in prilagajanje tabel, shem in drugih podatkovnih objektov.  

# Podatkovni tipi v SQL  
- **Numeric Integer Types**: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT  
- **Fixed-Point Types**: DECIMAL, NUMERIC  
- **Floating-Point Types**: FLOAT, DOUBLE  
- **Bit-Value Type**: BIT  
- **String Types**: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET.  
- **Date & Time**: DATE, TIME, DATETIME, TIMESTAMP, YEAR.  

# Zagotavljanje kakovosti podatkov  
- **Integrity Constraints**: obveznost podatkov, omejitve domene in pravila za celovitost podatkov.  
  - **Celovitost entitet (Entity Integrity)**: zagotavlja unikatnost zapisov.  
  - **Celovitost povezav (Referential Integrity)**: zagotavlja, da tuji ključi obstajajo v povezani tabeli.  
- **Omejitve so lahko definirane v CREATE in ALTER TABLE stavkih.**  

# Uporaba sprožilcev  
- Sprožilci se prožijo pred ali po operacijah INSERT, UPDATE ali DELETE.  
- **Sintaksa v MySQL:**

sql
CREATE TRIGGER triggername triggertime
triggerevent ON tblname
FOR EACH ROW
[triggerorder] triggerbody;

# Shranjeni programi  
- **Sintaksa za shranjene procedure in funkcije v MySQL:**

sql
CREATE PROCEDURE spname ([procparameter[,…]])
[characteristic …] routinebody CREATE FUNCTION spname ([funcparameter[,…]]) RETURNS type [characteristic …] routinebody;

- Primer shranjene funkcije:

sql
CREATE FUNCTION hello (empno INT(5)) RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Pozdravljen ', (SELECT firstname FROM employees WHERE empno = empno));

# Dostopanje do MySQL iz programskih jezikov  
- **Primer v jeziku Java**:

java
try {
//open connection
Connection dbconn = getConnection();
System.out.println("povezava odprta");
}
catch (SQLException e) {
e.printStackTrace();
}
```

Primeri v jeziku Ruby in Python so podobni s specifičnimi knjižnicami.

Koristne povezave

MySQL SUPB: http://www.mysql.com
SQL checker: http://developer.mimer.com/validator/parser92/index.tml
Orodja:
- SQLeo: http://sourceforge.net/projects/sqleo/
- Sequel Pro: http://www.sequelpro.com
- Mac SQL studio: http://macsqlstudio.com