SQL

1. SQL The questing beast Sir Thomas Mallory

2. Codds krav 5. Krav om omfattende språk for datahåndtering Det må finnes et relasjonelt språk som støtter • datadefinisjon, • datamanipulering, • sikkerhet • integritetsbeskrankninger (constraints) • transaksjonshåndteringsoperasjoner.

3. SQL • En standard • ANSI • ISO • SQL kompetanse er etterspurt • Utviklet av IBM • Objektorienterte utvidelser (extensions)

4. SQL • Et komplett databasespråk • Datadefinisjon • Definisjon av tabeller og view • Datamanipulasjon • Spesifikasjon av spørringer • Vedlikehold av databasen • INSERT • UPDATE • DELETE

5. SQL • Ikke et komplett programmeringsspråk • Brukes sammen med fullstendige programmeringsspråk • For eksempel, COBOL, vb.net, C++ og Java • Embedded SQL

6. Datadefinisjon • Tabeller, viewer og indekser kan defineres mens systemet er i drift • Basistabell • En frittstående, navngitt tabell • CREATE TABLE

7. Beskrankninger (Constraints) • Primary key CONSTRAINT pk_stock PRIMARY KEY(stkcode); • Foreign key CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation; • Unique CONSTRAINT unq_stock_stkname UNIQUE(stkname);

8. Check constraintTabell og kolonne • Tabell CREATE TABLE item ( itemcode INTEGER, CONSTRAINT chk_item_itemcode CHECK(itemcode <500)); • Kolonne CREATE TABLE item ( itemcode INTEGER CONSTRAINT chk_item_itemcode CHECK(itemcode <500), itemcolor VARCHAR(10));

9. Check constraintDomene CREATE DOMAIN valid_color AS CHAR(10) CONSTRAINT chk_qitem_color CHECK( VALUE IN ('Bamboo',’Black',’Brown',Green', 'Khaki',’White')); CREATE TABLE item ( itemcode INTEGER, itemcolor VALID_COLOR);

10. Datatyper

11. Datatyper • BOOLEAN • INTEGER • 31 binary digits • SMALLINT • 15 binary digits • FLOAT • Naturvitenskapelig- og ingeniørarbeid • DECIMAL • Kommersielle anvendelser • CHAR and VARCHAR • Character strings • DATE, TIME, TIMESTAMP, and INTERVAL • BLOB and CLOB • I Oracle: NUMBER for INTEGER, SMALLINT, FLOAT og DECIMAL

12. Endre en tabell • DROP TABLE • Sletter en tabell • ALTER TABLE • Legge til én ny kolonne av gangen • Legge til eller slette en constraint • Kan ikke brukes for å • Endre en kolonnes lagringsformat • Slette en uønsket kolonne

13. Beskrankninger (Constraints) ALTER TABLE dept ADD CONSTRAINT fk_dept_emp FOREIGN KEY(empno) REFERENCES emp; ALTER TABLE dept DROP CONSTRAINT fk_dept_emp;

14. Et view • CREATE VIEW • DROP VIEW

15. An index • CREATE INDEX • DROP INDEX CREATE [UNIQUE] INDEX indexname ON base-table(column[order] [,column [order]]...) [CLUSTER];

16. Datamanipuleringssetninger • INSERT • UPDATE • DELETE • SELECT

17. INSERT • En rad • Mange rader • Med subquery – som kopiering INSERT INTO STOCK (stkcode, stkfirm, stkprice, stkdiv, stkpe) SELECT code, firm, price, div, pe FROM download WHERE code IN ('FC','PT','AR','SLG','ILZ','BE','BS','NG', 'CS','ROF');

18. UPDATE • En rad • Mange rader • Alle rader UPDATE table SET column=expression [,column=expression] [WHERE condition]

19. DELETE • En rad • Mange rader • Alle rader • Ikke det samme som DROP TABLE

20. Produkt • Alle rader fra den første tabellen kombinert med alle mulige rader av den andre tabellen. • Å lage produktet mellom aksje og nasjon: SELECT * FROM stock, nation;

21. Produkt Finn prosentandelen av Australske aksjer i porteføljent CREATE VIEW austotal (auscount) AS SELECT COUNT(*) FROM stock WHERE natcode = 'AUS'; CREATE VIEW TOTAL (totalcount) AS SELECT COUNT(*) FROM stock; SELECT DECIMAL((FLOAT(auscount)/ FLOAT(totalcount)*100),5,2) AS percentage FROM austotal, total;

22. Join • Join lager en ny tabell fra to eksisterende tabeller ved å matche på en kolonne som er felles for begge tabellene • Equijoin • Den nye tabellen inneholder to identiske kolonner SELECT * FROM stock, nation WHERE stock.natcode = nation.natcode;

23. Variasjoner av Join • SELECT * FROM stock INNER JOIN nation USING (natcode); • SELECT * FROM stock NATURAL JOIN nation;

24. Outer join • Left outer join • En indre join pluss radene fra t1 som ikke er inkludert i den indre joinen. • SELECT * FROM t1 LEFT JOIN t2 USING (id);

25. Right outer join • En indre join pluss de radene fra t2 som ikke er inkludert i den indre joinen. SELECT * FROM t1 RIGHT JOIN t2 USING (id);

26. Outer join • Full outer join • En indre join pluss alle radene i t1 og t2 som ikke deltar i joinen. SELECT * FROM t1 FULL JOIN t2 USING (id);

27. Theta join • En join er et produkt med et vilkår • Vilkåret er ikke avgrenset til likhet.. • En theta join er den generelle versjonen • Theta er en variabel som kan ta en hvilket som helst verdi fra mengden [=, <>, >, ≥, <, ≤]

28. Korrelert underspørring • Den indre spørringen er evaluert mange ganger heller enn en. Finn alle aksjer hvor antallet er større enn gjennomsnittet for det aktuelle landet. SELECT natname, stkfirm, stkqty FROM stock, nation WHERE stock.natcode = nation.natcode AND stkqty > (SELECT AVG(stkqty) FROM stock WHERE stock.natcode = nation.natcode);

29. Korrelert underspørring Clue • Behovet for å sammenlikne hver rad av tabellen med en funksjon (for eksempel avg eller count) for noen rader av en kolonne. • Må brukes sammen med EXISTS eller NOT EXISTS

30. Aggregatfunksjoner • COUNT • SUM • AVG • MAX • MIN

31. SQL Routines • Functions • Procedures • Introdusert med SQL-99 • Ikke alle leverandører følger standarden • Forbedre fleksibilitet, produktivitet og håndheving av forretningsregler.

32. SQL function • Samme hensikt som innebygde funksjoner CREATE FUNCTION km_to_miles() RETURNS FLOAT CONTAINS SQL RETURN 0.6213712; • Bruk i SQL SELECT distance*km_to_miles FROM travel;

33. SQL prosedyre • En lagret prosedyre (stored procedure) er SQL kode som dynamisk lastes og utføres ved et CALL statement • Regnskapseksempel

34. SQL procedure CREATE PROCEDURE overfør ( IN betkonto INTEGER, IN motkonto INTEGER, IN beløp DECIMAL(9,2), IN transnr INTEGER) LANGUAGE SQL BEGIN INSERT INTO transaksjon VALUES (transno, amt, current date); UPDATE konto SET kontobalanse = kontobalanse + beløp WHERE kontonr = motkonto; INSERT INTO bevegelse VALUES(transnr, motkonto, ’inn'); UPDATE konto SET kontobalanse=kontobalanse-beløp WHERE kontonr = betkonto; INSERT INTO bevegelse VALUES (transno, betkonto, ’ut'); END;

35. SQL procedure • Utføre (Execute) CALL transfer(cracct, dbacct, amt, transno); • Eksempel • Transaksjon 1005 overfører $100 fra konto 1 (betalerens konto) til konto 2 (mottakerens konto) CALL transfer(1,2,100,1005);

36. Trigger • A set of actions set off by an SQL statement that changes the state of the database • UPDATE • INSERT • DELETE

37. Trigger • Automatically log all updates to a log file • Create a table for storing log rows • Create a trigger CREATE TABLE stock_log ( stkcode CHAR(3), old_stkprice DECIMAL(6,2), new_stkprice DECIMAL(6,2), old_stkqty DECIMAL(8), new_stkqty DECIMAL(8), update_stktime TIMESTAMP NOT NULL, user_name VARCHAR2(30) PRIMARY KEY(update_stktime));

38. Trigger CREATE TRIGGER stock_update AFTER UPDATE ON stock REFERENCING old AS old_row new AS new_row FOR EACH ROW MODE db2sq1 INSERT INTO stock_log VALUES (old_row.stkcode, old_row.stkprice, new_row.stkprice, old_row.stkqty, new_row.stkqty, CURRENT TIMESTAMP, USER);

39. Nulls • Ikke bland sammen med blank eller O • Mange betydninger • Ukjente data (bevisstløs pasient) • Data som ikke passer i denne raden (gravid for mann) • Data ikke oppgitt • Verdien er ikke definert (skadesum før takst er holdt) • Nuller skaper forvirring fordi man ikke vet hva de innebærer • Date foreslår at NOT NULL brukes på alle kolonner for å unngå forvirring. • Er dette et godt råd?

40. Sikkerhet • Data er verdifulle ressurser • Hvorfor • Tilgang bør kontrolleres • SQL sikkerhetsprosedyrer • CREATE VIEW • Autoriseringskommandoer

41. Autorisering • Basert på privilegie-konseptet • Du kan ikke gjennomføre en operasjon i databasehåndteringssystemet uten de nødvendige privilegier. • DBA (databaseadministrator) har ALLE privilegier

42. GRANT • Definerer en brukers privilegier • Format GRANT privilegier ON objekt TO brukere [WITH GRANT OPTION]; • Et objekt er en basetabell eller et view • Privilegiet kan være ALL PRIVILEGES eller valgt fra • SELECT • UPDATE • DELETE • INSERT • Privilegier kan gis til alle ved hjelp av nøkkelordet PUBLIC eller til utvalgte brukere ved å oppgi deres brukeridentifikator.

43. GRANT • UPDATE privilegiet kan spesifisere hvilke kolonner som kan oppdateres i en basistabell eller et view • Noe privilegier gjelder bare for basistabeller • ALTER • INDEX • WITH GRANT OPTION • Tillater en bruker å gi privilegier videre til en annen bruker

44. Å bruke GRANT • Gi Alice alle rettigheter til AKSJE-tabellen GRANT ALL PRIVILEGES ON AKSJE TO alice; • Tillate regnskapspersonalet, Todd og Nancy, to å oppdatere en aksjepris GRANT UPDATE (stkprice) ON stock TO todd, nancy; • Gi hele staben privilegiet til å hente rader fra VARE. GRANT SELECT ON VARE TO PUBLIC; • Gi Alice alle rettigheter til viewet STK. GRANT SELECT, UPDATE, DELETE, INSERT ON stk TO alice;

45. REVOKE • Trekker tilbake rettigheter • Format REVOKE privilegier ON objekt FROM brukere; • Cascading REVOKE • Tilbakestiller bruk av WITH GRANT OPTION • Når en brukers rettigheter trekkes tilbake, taper alle brukere som har fått rettigheter via WITH GRANT OPTION sine rettigheter

46. Bruke REVOKE • Trekke tilbake Sofies mulighet til å se en VARE. REVOKE SELECT ON VARE FROM sophie; • Nancy har ikke lenger rett til å oppdatere aksjepriser. REVOKE UPDATE ON stock FROM nancy;

47. Beskyttelse koster • Å vedlikeholde privilegier tar tid og innsats • Begrensninger gjør arbeid mer tungvint • Må spørre noen som har tilgang

48. Katalogen • En relasjonsdatabase som inneholder definisjonene av tabeller, viewer osv. • Kan utspørres ved hjelp av SQL • Kalles systemtabeller heller enn basistabeller • Hovedtabeller er • syscatalog • syscolumns • Sysindexes • For Oracle-spesifikk informasjon, sehttp://www.sqlzoo.net/howto/x12metaoracle.htm

49. Utspørring av katalogen • Finne tabellen med flest kolonner. SELECT tname FROM system.syscatalog WHERE ncols = (SELECT MAX(ncols) FROM system.syscatalog); • Hvilke kolonner i hvilke tabeller lagrer dato? SELECT tname, cname FROM system.syscolumns WHERE coltype = 'date';

50. Spørringer fra naturlig språk