slide1
Download
Skip this Video
Download Presentation
SQL

Loading in 2 Seconds...

play fullscreen
1 / 57

SQL - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL' - matthew-russo


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
slide1

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.
slide3
SQL
  • En standard
    • ANSI
    • ISO
  • SQL kompetanse er etterspurt
  • Utviklet av IBM
  • Objektorienterte utvidelser (extensions)
slide4
SQL
  • Et komplett databasespråk
  • Datadefinisjon
    • Definisjon av tabeller og view
  • Datamanipulasjon
    • Spesifikasjon av spørringer
    • Vedlikehold av databasen
      • INSERT
      • UPDATE
      • DELETE
slide5
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);

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;

slide22
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 (

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

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

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