SQL
This presentation is the property of its rightful owner.
Sponsored Links
1 / 57

SQL PowerPoint PPT Presentation


  • 59 Views
  • Uploaded on
  • Presentation posted in: General

SQL. The questing beast Sir Thomas Mallory. 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. SQL.

Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql

SQL

The questing beast

Sir Thomas Mallory


Codds krav

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.


Sql

SQL

  • En standard

    • ANSI

    • ISO

  • SQL kompetanse er etterspurt

  • Utviklet av IBM

  • Objektorienterte utvidelser (extensions)


Sql

SQL

  • Et komplett databasespråk

  • Datadefinisjon

    • Definisjon av tabeller og view

  • Datamanipulasjon

    • Spesifikasjon av spørringer

    • Vedlikehold av databasen

      • INSERT

      • UPDATE

      • DELETE


Sql

SQL

  • Ikke et komplett programmeringsspråk

  • Brukes sammen med fullstendige programmeringsspråk

    • For eksempel, COBOL, vb.net, C++ og Java

    • Embedded SQL


Datadefinisjon

Datadefinisjon

  • Tabeller, viewer og indekser kan defineres mens systemet er i drift

  • Basistabell

    • En frittstående, navngitt tabell

    • CREATE TABLE


Beskrankninger constraints

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


Check constraint tabell og kolonne

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


Check constraint domene

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


Datatyper

Datatyper


Datatyper1

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


Endre en tabell

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


Beskrankninger constraints1

Beskrankninger (Constraints)

ALTER TABLE dept ADD CONSTRAINT

fk_dept_emp FOREIGN KEY(empno) REFERENCES emp;

ALTER TABLE dept DROP CONSTRAINT fk_dept_emp;


Et view

Et view

  • CREATE VIEW

  • DROP VIEW


An index

An index

  • CREATE INDEX

  • DROP INDEX

    CREATE [UNIQUE] INDEX indexname

    ON base-table(column[order] [,column [order]]...)

    [CLUSTER];


Datamanipuleringssetninger

Datamanipuleringssetninger

  • INSERT

  • UPDATE

  • DELETE

  • SELECT


Insert

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');


Update

UPDATE

  • En rad

  • Mange rader

  • Alle rader

    UPDATE table

    SET column=expression

    [,column=expression]

    [WHERE condition]


Delete

DELETE

  • En rad

  • Mange rader

  • Alle rader

    • Ikke det samme som DROP TABLE


Produkt

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;


Produkt1

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;


Sql

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;


Variasjoner av join

Variasjoner av Join

  • SELECT * FROM stock INNER JOIN nation USING (natcode);

  • SELECT * FROM stock NATURAL JOIN nation;


Outer join

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


Right outer join

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


Outer join1

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


Theta join

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 [=, <>, >, ≥, <, ≤]


Korrelert undersp rring

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


Korrelert undersp rring1

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


  • Aggregatfunksjoner

    Aggregatfunksjoner

    • COUNT

    • SUM

    • AVG

    • MAX

    • MIN


    Sql routines

    SQL Routines

    • Functions

    • Procedures

    • Introdusert med SQL-99

      • Ikke alle leverandører følger standarden

    • Forbedre fleksibilitet, produktivitet og håndheving av forretningsregler.


    Sql function

    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;


    Sql prosedyre

    SQL prosedyre

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

    • Regnskapseksempel


    Sql procedure

    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;


    Sql procedure1

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


    Trigger

    Trigger

    • A set of actions set off by an SQL statement that changes the state of the database

      • UPDATE

      • INSERT

      • DELETE


    Trigger1

    Trigger

    • Automatically log all updates to a log file

      • Create a table for storing log rows

      • Create a trigger

        CREATE TABLE stock_log (

        stkcodeCHAR(3),

        old_stkpriceDECIMAL(6,2),

        new_stkpriceDECIMAL(6,2),

        old_stkqtyDECIMAL(8),

        new_stkqtyDECIMAL(8),

        update_stktimeTIMESTAMP NOT NULL,

        user_nameVARCHAR2(30)

        PRIMARY KEY(update_stktime));


    Trigger2

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


    Nulls

    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?


    Sikkerhet

    Sikkerhet

    • Data er verdifulle ressurser

      • Hvorfor

    • Tilgang bør kontrolleres

    • SQL sikkerhetsprosedyrer

      • CREATE VIEW

      • Autoriseringskommandoer


    Autorisering

    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


    Grant

    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.


    Grant1

    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


    Bruke grant

    Å 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;


    Revoke

    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


    Bruke revoke

    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;


    Beskyttelse koster

    Beskyttelse koster

    • Å vedlikeholde privilegier tar tid og innsats

    • Begrensninger gjør arbeid mer tungvint

      • Må spørre noen som har tilgang


    Katalogen

    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


    Utsp rring av katalogen

    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';


    Sp rringer fra naturlig spr k

    Spørringer fra naturlig språk


    Open database connectivity odbc

    Open Database Connectivity (ODBC)


    Embedded sql

    Embedded SQL

    • SQL er ikke et frittstående programmeringsspråk

    • SQL setninger kan bygges inn i applikasjonsprogrammer som deretter prekompileres (noe som oversetter den innebygde SQLen til prosedyrekall mot et databasebibliotek)

    • Gammel teknologi, aktuell for noen systemer som fortsatt kjører, spesielt COBOL

    • Inkompatibiliteten mellom tabellprosesseringen til SQL og en post av gangen prosesseringen til COBOL håndteres ved hjelp av en cursor


    Ms access vs sql

    Styrker

    Grensesnitt

    SQL DML

    Referanseintegritet

    Rask eksekvering

    Viewer (spørringer)

    Oppdaterbare viewer

    Svakheter

    Ikke støtte for GRANT og REVOKE

    Domener

    Ikke støtte for COMMIT og ROLLBACK

    Begrenset kontroll over samtidige transaksjoner

    MS Access vs SQL


    Sqls framtid

    En av de mest suksessrike standardiseringene

    Svært portabel

    Objekter har ført til vansker med standardiseringen ettersom leverandørene av databasemotorer har lagt til utvidelser før standarden ble satt

    SQLs framtid


    Sql 99

    SQL-99

    • Bedre støtte for Java og andre objektorienterte språk

    • Støtte for multimedia utvidelser

    • Opprettholder portabiliteten ved å fastlegge standarder for objektorienterte utvidelser av relasjonsmodellen

    • Utvidelsene øker funksjonalitet på bekostning av enkelhet.


    Brukerdefinerte datatyper

    Brukerdefinerte datatyper

    • Kan brukes på samme måte som innebygde datatyper

    • En brukerdefinert datatype er definert ved å

      • Spesifisere deklarasjoner av de lagrede attributtene som represneterer verdien av UDTen

      • Spesifisere operasjoner som definerer likhet og rekkefølgesammenhenger i UDTen

      • Spesifisere operasjoner og deriverte attributter som representerer oppførselen til UDTen


    Sqlj og jdbc

    SQLJ og JDBC

    • SQLJ er embedded SQL for java

    • SQLJ er en ANSI standard

    • SQLJ-integrasjonen mellom SQL og Java styrker Java som alternativ for dataintensive virksomhetsapplikasjoner

    • SQLJ har enkel syntaks for statiske SQL-programmeringsoppgaver.

    • SQLJ tillater sjekk av syntaks og semantikk på SQL-setningene før kjøring.

    • SQLJ krever oversettelse før java-kompilering

    • Bruk JDBC for å løse dynamiske oppgaver.


  • Login