Datamanipulation I Sql

Indsættelse, opdatering, og slettelse i en database foregår med SQL kommandoer. Som ved dataudtræk kan phpmyadmin benyttes til at indsætte data.

Indsættelse af data

Indsættelse af data foregår med INSERT INTO kommandoen. Lad os kigge på et hvordan SQL udtrykket for indsættelse af en blognyhed ser ud.

INSERT INTO posts
    (title, body, created_at, is_published, user_id)
VALUES
    ('Sidste nyt fra iftek', 'bla bla', '2011-11-30', 1, 1);

INSERT INTO tager 3 slags argumenter: 1) Tabellen der skal indsættes i, 2) et tupel af søjlerne der skal indsættes data i, samt 3) et tupel med værdierne af disse søjler. Det ovenstående SQL udtryk indsætter en publiseret blognyhed i posts tabellen med titllen 'Sidste nyt fra iftek' og indholdet 'bla bla'. Værdien af user_id er en reference til users tabellen. Da vi oprettede databasen satte vi den op sådan, at brugeren *skal* findes for at data overhovedet kan indsættes.

Opdatering af data

Man opdaterer data ved at benytte UPDATE kommandoen. Se eksempel herunder.

UPDATE posts
SET title='Sidste nyt fra iftek2', body='bla bla bla'
WHERE id=1

UPDATE tager op til 3 argumenter, hvor de to første er påkrævet: 1) Tabellen der skal opdateres, 2) hvilke værdier, man ønsker at opdatere samt deres nye værdi, 3) er en eventuel WHERE clause, hvis man kun ønsker at opdatere nogle specifikke rækker i tabellen. I dette eksempel opdaterer vi tabellen posts, hvor vi vælger at sætte title og body til nogle nye værdier. Vores WHERE clause sikrer, at vi kun opdaterer det blogindlæg, der har Id = 1.

Slettelse af data

Sletning af data gøres ved at benytte DELETE FROM kommandoen.

DELETE FROM posts
WHERE id = 1

Kommandoen tager to argumenter: 1) Tabellen der skal slettes data fra, 2) WHERE clause til at filtrere, hvilke rækker der skal slettes. Feltet er valgfri, hvis man fx ønsker at slette alt data i tabellen.

Oprettelse af tabeller

"Vi vil have mulighed for at kommetere på vores blog!" har iftek eleverne sagt. Vi vil derfor gøre vores database parat til at understøtte kommentarer. Vi modellerer først hvad en kommentar er. Udover selve teksten indeholder en kommentar de følgende oplysninger:

  • brugeren der skrev kommentaren;
  • datoen kommentaren blev skrevet; udover dette har det vist sig nødvendigt at
  • administratorer kan markere indholdet som upassende da elever tidligere har skrevet upassende indhold på andres sider!

Der er de følgende typer relationer involveret i understøttelsen af kommentarer:

  • En kommentar hører til præcist et indlæg.
  • Et indlæg kan have mange kommentarer.
  • En kommentar tilhører præcist en bruger.
  • En bruger kan have mange kommentarer.

Dvs. der er tale om en en-til-mange relation mellem indlæg og kommentarer, en en-til-mange relation mellem brugere og kommentarer, og en en-til-mange relation mellem brugere og indlæg. E/R diagrammet nedenfor viser resultatet af modelleringen.

blog_er.png

Fra E/R diagram til Database

Vi er nu klar til at implementere vores design i databasen.

  • Entiteter i E/R diagrammet svarer direkte til tabeller i databasen.

Repræsentationen af E/R forhold i databasen, er afhængig af typen af forholdet:

  • En-til-en: I mindst en af tabellerne inkluderes en reference til den anden tabel i form af en fremmednøgle.
  • En-til-mange: I tabellen på mangesiden inkluderes en reference til den anden tabel i form af en fremmednøgle.
  • Mange-til-mange: Udover de to tabeller for entiteterne, oprettes en tabel for selve forholdet. Denne ekstra tabel indeholder fremmednøgler til de to andre tabeller.

Vi har udelukkende en-til-mange forhold i vores E/R diagram. Entiteten kommentar findes på mange-siden i forholdet til både brugere og indlæg; derfor skal kommentar tabellen indeholde fremmednøgler til bruger tabellen og til indlægs tabellen. Designet af vores nye kommentar tabel ser ud som følger:

Comments(id, user_id, post_id)

Jeg har valgt user_id som navn for søjlen der er fremmednøgle til users, og post_id som navn for søjlen der er fremmednøgle til posts.

Vi er ikke færdige endnu! Indtil videre har vi kun indfanget forholdet mellem entiteterne. Vi har endnu ikke defineret hvilke konkrete data kommentartabellen skal indeholde. Vi ønsker en søjle der skal indeholde kommentaren, tidspunktet den er blevet oprettet, og et felt der indikerer om kommentaren er fjernet af administratoren.

Comments(id, user_id, post_id, comment, created_at, is_removed)

I SQL har hver søjle tilknyttet et bestemt format som data skal være på. Formatet af en søjle kan fx være et tidspunkt, en tekst, en tekst af begrænset længde og et tal af en begrænset længde. I MySQL hhv. TIMESTAMP, TEXT, VARCHAR, og INT.

SQL kommandoen til at oprette tabeller er CREATE TABLE tabelnavn. Herefter følger en definition af søjler der indgår i tabellen, og en definition af hvad der er primærnøgle. En søjle defineres ved søjlenavn datatype fx:

  user_id int

Lad os kigge på hele SQL kommandoen til at oprette tabellen:

CREATE TABLE comments (
  id int,
  user_id int,  
  post_id int,
  created_at timestamp,
  comment text,
  is_removed int(1)
)

Primærnøglerne i hhv. users og posts har dataformatet INT, derfor er fremmednøglerne i comments også i dette format. is_removed er en sandt eller falsk værdi, dette modelleres i MySQL ved 0 eller 1, da der ikke findes en boolsk datatype. int(1) angiver altså at formatet af data i søjlen er et tal af max længde 1.

Oprettelse af tabeller med phpmyadmin

Frygt ikke hvis du ikke kan huske SQLen til at oprette tabeller, jeg benyttede selv phpmyadmin som hjælp til at oprette tabellen, og det samme kan du gøre. I phpmyadmin er det også meget nemmere hurtigt at gøre formatet af søjler mere restriktivt. Dette kan fx være at begrænse tal til en bestemt størrelse og at angive at en søjlen skal have en værdi, og angivelse af hvilken søjle der agerer som primærnøgle.

opret_tabel.png

For at oprette en ny tabel angives navnet på tabellen, og hvor mange søjler den skal indeholde, se billedet ovenover. Herefter, defineres søjlernes datatyper og deres navne.

comments_table_definition.png

Med hjælp fra phpmyadmin:

CREATE TABLE comments (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
post_id INT NOT NULL ,
user_id INT NOT NULL ,
comment TEXT NOT NULL ,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
is_removed INT( 1 ) NOT NULL
)

NOT NULL angiver at en søjle skal have en værdi. PRIMARY KEY angiver at attributten id agerer som primærnøgle. AUTO_INCREMENT angiver at værdien automatisk bliver indsat og at den er en større end sidst. DEFAULT CURRENT_TIMESTAMP angiver at søjlen får tidspunktet nu som værdi, hvis der netop ikke er blev angivet en værdi for denne søjle fra brugerens side.

Restriktion på fremmednøgler

I øjeblikket kan vi godt indsætte en kommentar med fremmednøgler til hhv. brugere og indlæg der ikke eksisterer. Det er nyttigt at få databasen til at opretholde at fremmednøgler skal henvise til noget der eksisterer, i vores tilfælde en bruger og et indlæg.

I phpmyadmin gøres dette fra 'Relation View', se skærmbilledet der viser kommentar tabellen.

comments_tbl.png

Nedenunder vises hvordan det angives at når en kommentar oprettes skal user_id og post_id eksisterer i hhv. users og posts tabellen.

references.png
Medmindre andet er angivet, er indholdet af denne side licenseret under Creative Commons Attribution-NonCommercial 3.0 License