Baze podataka SQL1
description
Transcript of Baze podataka SQL1
1
1
Baze podataka
2. pred – Tipovi podataka, model baze
select i where dio
Tehničko veleučilište u ZagrebuInformatičko-računarski odjel
2
Baza podataka sa labosa
2
3
Brzi tečaj ER modela
4
Objašnjenja sintakse
� Kad god je bilo moguće, sintaksa SQL naredbe je opisana na najjednostavniji mogući način
� ključne su riječi napisane velikim slovima
� "varijabilni" dio naredbe otisnut je kosim slovima
� Primjer:
� DROP DATABASE databaseName
3
5
Objašnjenja sintakse
� CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification ...]
� Kada iza varijable stoji oznaka "..." tada je kasnije opisano što može stajati na tom mjestu
� Parametri unutar {} su obvezni
� Parametri unutar [ ] su opcionalni
� Znak "|" zamjenjuje riječ "ili"
� create_specification: [DEFAULT] CHARACTER SET charset_name| [DEFAULT] COLLATE collation_name
6
Osnovni objekti u SQL jeziku
� Osnovni objekti u SQL-u
� Baza podataka (Database)
� Tablica (Table) -relacija
� Stupac (Column) - atribut, kolona, stupac
� Pogled ili virtualna tablica (View)
� Sinonim (Synonym)
� Pravilo integriteta (Constraint)
� Indeks (Index)
� Pohranjena procedura (Stored Procedure)
� Okidač (Trigger)
4
7
Vrijednosti -stringovi
� String je niz znakova omeđen jednostrukim navodnicima ' ' ili dvostrukim navodnicima " "
� Uz string je uvijek moguće opisati kojeg je znakovnog tipa i kolotacije
� [_charset_name]'string' [COLLATE collation_name]
� Postoji mogućnost korištenja slijedećih kontrolnih znakova (escape character) \n \' \"" \% \b \t \\ \_
� SELECT "hello", “’hello’”, ‘”hello”’, "\"hello";
� hello 'hello' ''hello'' "hello
8
Vrijednosti -brojevi
� Standardni brojevi se pišu normalno sa predznakom - ili bez predznaka za +
� Decimalni brojevi se mogu pisati sa točkom sa ili bez potencije: 1.96e+10
� Heksadecimalne vrijednosti započinju sa 0x
� SELECT 0x41 ispisuje 'A'
� Boolean vrijednosti su TRUE ili true koji iznosi 1 i FALSE ili false koji iznosi 0
� Binarne vrijednosti pišu se kao b'vrijednost' ili 0 bvrijednost
� SELECT b'1000001' ispisuje 'A'
� Nepostojeća vrijednost se piše kao NULL (uvijek veliko!)
5
9
Vrijednosti - imena objekata
� Ime ne smije sadržavati:� null znak 0x00
� navodnike
� ne smije završavati sa razmakom
� '\' '/' i '.' je zabranjeno
� Svaki objekat može biti dohvaćen direktno ili preko drugog objekta� puna staza navodi se kada postoje više objekata istog imena (npr klijent i radnik imaju vjerojatno isti atribut ime)
� col_name
� table_name.col_name
� database_name.table_name.col_name
� Prema SQL standardu svi nazivi objekata bi se trebali pisati malim slovima
� UNIX ima datotečni sustav koji razlikuje mala i velika slova pa tamo treba pripaziti da se uvijek koristi jednako ime u odnosu na mala/velika slova
10
Vrijednosti - Rezervirane riječi
� U principu za ime atributa ili tablice je dozvoljeno koristiti rezervirane riječi ali nije poželjno budući da zadaje problema kod pisanja naredbi i razumijevanja napisane naredbe
6
11
Vrijednosti - Rezervirane riječi
12
Vrijednosti -varijable
� Korisničke varijable uvijek počinju sa @ i mogu sadržavati sve prirodne zankove iz latin1 seta te znakove . _ i $
� Za definiranje korisničke varijable koristimo naredbu SET
� SET @var_name := expr [, @var_name := expr] ...� SET @ime:='Ivica';
� SELECT @ime;
� expr može biti broj, decimalni broj, string ili NULL
� Nije poželjno koristiti = umjesto := jer kod pojedinih sintaksi = znači usporedbu, a ne pridruživanje
� Komentari
� # i -- komentira do kraja linije
� /* */ komentar kroz više linija
7
13
Podrška znakovima� Character set (kodiranje znakova) - označava koji ASCII kod zamjenjuje koje slovo
� latin2 (iso8859-2) - ISO standard za naše znakove
� cp1250 - Microsoft standard za naše znakove
� latin1 - obično osnovni standard
� Collation (kolotacija) - način usporedbe znakova , odnosno sortiranja zakova po redu, ne po ASCII vrijednostima
� latin1_swedish_ci - obično osnovni standard
� cp1250_croatian_ci - po MS standardu
� latin2_general_ci - po ISO standardu
� Tablica za laboratorijske vježbe ide po cp1250 i cp1250_croatian_ci standardu
� Obje stvari su važne da budu definirane u mogućnostima poslužitelja
� Raspoloživ način kodiranja znakova SHOW CHARACTER SET
� Raspoložive kolotacije SHOW COLLATION
� Kolotacije i kodiranje znakova se obično definiraju kod stvaranja tablice
� Naknadno postavljanje kodiranja znakova SET CHARACTER SET charset_name
� Obično se kod spajanja na bazu definira character set
14
Tipovi podataka - numerički� M - označava broj bitova koji mogu biti pohranjeni
� BIT[(M)]
� TINYINT [(M)] [UNSIGNED] [ZEROFILL]
� Vrlo mali cijeli broj od -128 do 127. Ako je unsigned onda od 0 do 255. Veličine 1 byte.
� BOOL, BOOLEAN,TINYINT(1)
� sinonim za BIT[1]
� SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
� Mali cijeli broj od -32768 do 32767. Ako je unsigned onda od 0 do 65535. Veličine 2 byte-a.
� MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
� Cijeli broj srednje veličine od -8388608 do 8388607. Ako je unsigned onda od 0 do 16777215.
Veličine 3 byte-a.
� INT[(M)] [UNSIGNED] [ZEROFILL]
� bez predznaka 0-4294967295
� INTEGER[(M)] [UNSIGNED] [ZEROFILL]
� sinonim za int
� Cijeli broj od -2147483648 do 2147483647. Ako je unsigned onda od 0 do 4294967295. Veličine 4
byte-a.
� BIGINT[(M)] [UNSIGNED] [ZEROFILL]
� Veliki cijeli broj od -9223372036854775808 do 9223372036854775807. Ako je unsigned onda od 0
do 18446744073709551615. Veličine 8 byte-ova.
8
15
Tipovi podataka - numerički� M - označava broj bitova koji mogu biti pohranjeni
� D - označava broj decimalni mjesta
� SERIAL
� sinonim za BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
� FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
� Decimalni broj, jednostruke preciznosti, m ukupan broj prikazanih znamenaka, a d broj decimalnih zanmenaka. Može poprimiti vrijednosti od -3.402823466E+38 do -1.175494351E-38, 0, i od 1.175494351E-38 do 3.402823466E+38. Veličine 4 byte-a
� DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
� -1.7976931348623157E+308 do 2.2250738585072014E-308, 0, 2.2250738585072014E-308 do 1.7976931348623157E+308, Veličine 8 byte-ova.
� DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
� sinonim za DOUBLE
� DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
� Decimalni broj koji se u bazi čuva kao niz znakova (jedna znamenka - jedan znak plus oznaka decimalne točke). M je ukupan broj znamenaka, a D broj decimalnih znamenaka.
� DEC[(M[,D])]
� sinonim za decimal
16
Tipovi podataka – datum i vrijeme� DATE
� Datum. Podržava period od '1000-01-01' do '9999-12-31'. MySQL prikazuje vrijednosti datuma u formatu 'YYYY-MM-DD'.
� DATETIME
� Datum i vrijeme. Podržava period od '1000-01-01 00:00:00' do '9999-12-31 23:59:59'. MySQLprikazuje vrijednosti datuma i vremena u formatu 'YYYY-MM-DD HH:MM:SS'.
� TIME
� Vrijeme. Podržava vrijednosti od '-838:59:59' do '838:59:59'. MySQL Prikazuje vrijednosti vremena u formatu 'HH:MM:SS'.
� TIMESTAMP[(M)]
� Broj sekundi od 1.1.1970. Ovaj tip podataka najčešće koristi za zapisivanje vremena zadnje promjene podataka. U slučaju bilo kakve operacije dodavanja ili promjene zapisa, vrijednost prve TIMESTAMP kolone u tablici podesit će se na trenutnu vrijednost. Vrijednost zapisa u toj koloni možete postaviti na trenutno vrijeme na način da zapišete NULL vrijednost. Od verzije MySQL-a 4.1, TIMESTAMP se prikazuje kao string u formatu 'YYYY-MM-DD HH:MM:SS'. Ako želite dobiti brojčanu vrijednost, morate dodati +0 toj koloni. U verzijama MySQL 4.0 i prije, TIMESTAMP vrijednosti su prikazane u formatu YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, ili YYMMDD format, ovisno o vrijednosti parametra M - 14 (ili bez), 12, 8, ili 6.
9
17
Tipovi podataka – datum i vrijeme
� YEAR[(2|4)]
� Godina u dvoznamenkastom ili četveroznamenkastom formatu. Default je četveroznamenkasti format. U četveroznamenkastom formatu dozvoljene vrijednosti su od 1901 do 2155, i 0000. U dvoznamenkastom formatu dozvoljene vrijednosti su od 70 to 69,što predstavlja godine od 1970 do 2069.
18
Tipovi podataka - String� [NATIONAL] CHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
� Niz znakova fiksne duljine (M) od 0 do najviše 255 znakova. Znakovi razmaka na kraju niza se izbacuju prilikom prikaza.
� CHAR
� Sinonim za CHAR(1).
� [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
� Niz znakova varijabilne duljine. M označava najveću moguću duljinu. Mora biti između 0 i 255 (1 i 255 prije MySQL verzije 4.0.2). Znakovi razmaka na kraju niza se izbacuju prilikom upisa u tablicu.
� BINARY(M)
� za binarni tip podataka (nema definiranu kodnu stranicu ili kolotaciju)
� TINYBLOB
� Vrlo mali binarni objekt. Najviše 255 byte-ova. Sličan TINYTEXT-u s razlikom da se pretraživanje obavlja case sensitively. U većini situacija umjesto njega koristite VARCHAR BINARY zbog brzine.
� TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
� Najviše 255 znakova. Sličan TINYBLOB-u s razlikom da se pretraživanje obavlja caseinsensitively. U većini situacija umjesto njega koristite VARCHAR zbog brzine.
� BLOB[(M)]
� Binarni objekt. Najviše 65,535 byte-ova. Sličan TEXT-u s razlikom da se pretraživanje obavlja casesensitively.
10
19
Tipovi podataka - String� TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
� Najviše 65,535 byte-ova. Sličan BLOB-u s razlikom da se pretraživanje obavlja case
insensitively.
� MEDIUMBLOB
� 16777215 (224)
� MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
� LONGBLOB
� 4,294,967,295 or 4GB (232)
� LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
� ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
� može sadržavati jednu od navedenih 65535 vrijednosti maksimalno
� Odabire se ponuđena vrijednost
� SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
� Može sadržavati jednu ili više vrijednosti (do 64) s liste. Lista se kreira prilikom kreiranja kolone u tablici.
20
Tipovi podataka
Usporedba tipova podataka
11
21
OperatoriBETWEEN ... AND ... Da li unutar zadanih okvira<=> Usporedba sigurna u odnosu na NULL=,>=,>,<,<=, != ili <>GREATEST() Vraća veći argumentIN Da li je unutar okviraINTERVAL() Vraća indeks elementa koji je manji od prvog argumentaIS NULL Da li je NULL vrijednostIS Da li je true ili false za bool tipISNULL() Da li je argument NULL vrijednostLEAST() Vrati najmanji argumentLIKE Jednostavna usporedba stringovaNOT BETWEEN ... AND ... Negacija BETWEEN ... AND....NOT IN Negacija INNOT LIKE Ne zadovoljava jednostavnu usporedbu stringovaSOUNDS LIKE(v4.1.0) Uspoređivanje zvukovaAND ili &&, OR ili || Logičko povezivanjeNOT ili ! NegacijaRLIKE ili REGEXP Složenija provjera da li je niz sadržan u nizuCASE i IF Ostavimo za kasnije
22
Operatori prema prednosti� BINARY, COLLATE - pretvara nizove znakova u binarni niz ili kolotaciju
� !
� - (unary minus), ~ (unary bit inversion)
� ^ (XOR)
� *, / ili DIV, % ili MOD
� -, +
� <<, >> & | =, <=>, >=, >, <=, <, <> ili !=, IS, LIKE, REGEXP, IN
� BETWEEN, CASE, WHEN, THEN, ELSE
� NOT
� && ili AND
� XOR
� || ili OR
� :=
� Konverzija tipova podataka
� CAST() ili CONCAT()
� SELECT 38.8, CAST(38.8 AS CHAR); daje 38.8 '38.8'
12
23
Numeričke funkcije - izvadak
� Aritmetičke funkcije
� + - * / (DIV) % (MOD)
� Matematičke funkcije
zaokružuje na gornju vrijednost
zaokružuje na donju vrijednostexponent
smanji broj decimalnih mjesta
24
Data manipulation statement
� SELECT [ALL | DISTINCT | DISTINCTROW ][HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [FROM table_references[WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_countOFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options| INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]
13
25
Uvjet kod SELECT-a
� WHERE se koristi kad se želi ispisati samo one zapise koji su točno određeni nekim uvjetom
� SQL WHERE Sintaksa
� SELECT naziv_stupca(s)FROM ime_tabliceWHERE naziv_stupca operator vrijednost
26
Operatori u WHERE dijelu
Operator Opis
= Jednako
<> ili != Različito
> Veće od
< Manje od
>= Veće ili jednako
<= Manje ili jednako
BETWEEN Između sa uključenim rasponom
LIKE Traži se ponavljanje uzorka
IN Za točno te vrijednosti koje se traže
14
27
Projekcije - uvjeti
� Vratimo se na projekcije , uvodimo ključnu riječ WHERE� Ispišimo sve zapise iz tablice klijent imena 'Davor'
� SELECT * FROM klijent WHERE imeKlijent='Davor'
28
Projekcije - uvjeti
� ispišimo sve zapise iz tablice mjesto čiji je poštanski broj 10000� SELECT * FROM mjesto WHERE pbrMjesto=10000
15
29
Projekcije - uvjeti
� Ispisati sve zapise iz mjesta čiji je poštanski broj između 10000 i 20000� SELECT * FROM mjesto WHERE pbrMjesto>10000 AND pbrMjesto<20000
� ili
� SELECT * FROM mjesto WHERE pbrMjesto BETWEEN 10001 AND 19999
30
Projekcije - uvjeti
� Ispisati sve zapise iz tablice mjesto za koje ne vrijedi da je poštanski broj između 10000 i 20000� SELECT * FROM mjesto WHERE NOT(pbrMjesto>10000 AND pbrMjesto<20000)
� ili
� SELECT * FROM mjesto WHERE pbrMjesto NOT BETWEEN 10001 AND 19999
16
31
Projekcije - uvjeti
� Ispisati sve zapise iz tablice mjesto za koje vrijedi da je poštanski broj 10000 ili 10020 ili 20000� SELECT * FROM mjesto WHERE pbrMjesto=10000 OR pbrMjesto=10020 OR pbrMjesto=20000
� ili
� SELECT * FROM mjesto WHERE pbrMjesto IN(10000,10020,20000)
32
Projekcije
� Ispisimo iz tablice kvar atribute satiKvar i satiKvara uvećan za 3 za sve kvarove kod kojih je sifra odjela 9,10 i 11. � SELECT satiKvar, satiKvar+3 FROM kvar WHERE sifOdjel IN (9,10,11);
17
33
Podaci
� Stvoriti tablicu mjesto i unijeti jedan zapis.CREATE TABLE mjesto1 (
pbrMjesto int(11) DEFAULT NULL,
nazivMjesto varchar(255) COLLATE cp1250_croatian_ci DEFAULT NULL,
sifZupanija int(11) DEFAULT NULL,
UNIQUE KEY nazivMjesto (`nazivMjesto`),
UNIQUE KEY pbrMjesto (pbrMjesto)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
INSERT INTO mjesto1 VALUES ('00000043280','Garešnica','7');
34
Podaci
� Stvoriti tablicu mjesto i unijeti jedan zapis.CREATE TABLE mjesto2 (
pbrMjesto int(11) ZEROFILL DEFAULT NULL,
nazivMjesto varchar(255) COLLATE cp1250_croatian_ci DEFAULT NULL,
sifZupanija int(11) DEFAULT NULL,
UNIQUE KEY nazivMjesto (`nazivMjesto`),
UNIQUE KEY pbrMjesto (pbrMjesto)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
INSERT INTO mjesto2 VALUES ('00000043280','Garešnica','7');
18
35
Podaci
� Izbrisati podatak iz tablice mjesto kod kojeg je poštanski broj 43280
� DELETE FROM mjesto WHERE pbrMjesto=43280;
� Izbrisati sve podatke iz tablica mjesto1 i mjesto2� DELETE FROM mjesto1;
� DELETE FROM mjesto2;
� Obrisati tablice mjesto1 i mjesto2� DROP TABLE mjesto1;
� DROP TABLE mjesto2;
Podaci
36
Razlika između INT i INT(3)
create table t (t int(3) zerofill);insert into t set t = 10;create table t (t int zerofill);insert into t set t = 10;create table t (t int);insert into t set t = 10;
19
Podaci
CREATE TABLE korisnik
(
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
JMBG CHAR(13) NOT NULL DEFAULT '0000000000000',
ime NCHAR(30) NOT NULL,
prezime VARCHAR(30) NOT NULL,
adresa TEXT DEFAULT NULL,
pbr INT(11) NOT NULL,
spol ENUM ('M','Ž') NOT NULL DEFAULT 'M',
tezina DECIMAL(5,2) NOT NULL,
PRIMARY KEY (ID)
);
37
Podaci
INSERT INTO korisnik VALUES (1,’2806978300003’,’Željko’,’Željkić’,’Trg
bana Josipa Jelačića’,10000,’M’,80.4);
(1 row(s) affected, 2 warning(s))
Execution Time : 00:00:00:140
Transfer Time : 00:00:00:000
Total Time : 00:00:00:140
Warning Code : 1366
Incorrect string value: '\xC4\x87' for column 'prezime' at row 1
Warning Code : 1366
Incorrect string value: '\xC4\x8Di\xC4\x87a' for column 'adresa' at row 1
38
20
PodaciALTER TABLE korisnik
ENGINE=MYISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
Bez obzira na promjenu cijele tablice insert za podatke koji imaju hrvatske znakove vraća upozorenje
ALTER TABLE korisnik
CHANGE COLUMN ime ime VARCHAR(30) CHARACTER SET cp1250 DEFAULT NULL,
CHANGE COLUMN prezime prezime VARCHAR(30) CHARACTER SET cp1250 DEFAULT NULL,
CHANGE COLUMN adresa adresa TEXT CHARACTER SET cp1250 DEFAULT NULL;
ili ako želimo i sortiranje po hrvatskoj abecedi (o ovome više kod sortiranja podataka)
ALTER TABLE korisnik
CHANGE COLUMN ime ime VARCHAR(30) CHARACTER SET cp1250 COLLATE cp1250_croatian_ciDEFAULT NULL,
CHANGE COLUMN prezime prezime VARCHAR(30) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
CHANGE COLUMN adresa adresa TEXT CHARACTER SET cp1250 COLLATE cp1250_croatian_ciDEFAULT NULL;
Tek nakon promjene podataka na adekvatan set znakova insert prolazi bez upozorenja
DELETE FROM korisnik;
DROP TABLE korisnik;39
Podaci
CREATE TABLE `korisnik` (
ID int(10) unsigned NOT NULL AUTO_INCREMENT,
JMBG char(13) CHARACTER SET latin1 NOT NULL DEFAULT '0000000000000',
ime varchar(30) COLLATE cp1250_croatian_ci DEFAULT NULL,
prezime varchar(30) COLLATE cp1250_croatian_ci DEFAULT NULL,
adresa text COLLATE cp1250_croatian_ci,
pbr int(11) NOT NULL,
spol enum('M','Ž') CHARACTER SET latin1 NOT NULL DEFAULT 'M',
tezina decimal(5,2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci;
Izgled tablice nakon svi promjena
40
21
Podaci
ALTER TABLE korisnik
CHANGE COLUMN tezina tezina DECIMAL(5,4) DEFAULT NULL;
INSERT INTO korisnik VALUES (2,'2806978300003','Željko','Željkić','Trg bana Josipa Jelačića',10000,'M',80.4);
(1 row(s) affected, 1 warning(s))
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
Warning Code : 1264
Out of range value for column 'tezina' at row 1
41