aug 292010
 

Flattr this!

Ibland kan det vara bra att även ta bort data ur en databas. Lägger man till villkor för vilka poster som ska tas bort så slipper man leta upp dom manuellt. Jag utnyttjar en tabell med dessa fält inklusive några poster för att exemplet ska fungera:

Det handlar alltså om ett adressregister där tabellen heter ”Adressregister”.

Gå in i menyn Verktyg/SQL och kopiera in allting som står inom citatet för att få tabellen Adressregister med medföljande data:

CREATE TABLE ”Adressregister”
(”ID” Integer PRIMARY KEY, ”Förnamn” char(50), ”Efternamn” char(50), ”Adress” char(50), ”Postnummer” char(50), ”Postadress” char(50));

INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(0, ’Peter’, ’Andersson’, ’Storgatan 4’, ’678 65’, ’Göteborg’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(1, ’Sven’, ’Gustavsson’, ’Allmänna stigen 36’, ’566 56’, ’Örnsköldsvik’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(2, ’Algot’, ’Blomkvist’, ’Västra gatan 25’, ’678 65’, ’Stockholm’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(3, ’Emma’, ’Hultkvist’, ’Gränden 3’, ’765 56’, ’Göteborg’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(4, ’Johanna’,’Evertsson’, ’Höjden 3’, ’566 88’, ’Falköping’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(5, ’Evelina’, ’Jonasson’, ’Östra gatan 87’, ’766 65’, ’Stockholm’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(6, ’Finn’,’Rutgersson’,’Skojarbacken 44′,’655 67′,’Linköping’);
INSERT INTO ”Adressregister”
(”ID”, ”Förnamn”, ”Efternamn”, ”Adress”, ”Postnummer”, ”Postadress”)VALUES(7, ’Anders’,’Andersson’,’Storgatan 12′,’678 65′,’Göteborg’);

Glöm inte att gå in i menyn Visa och ”uppdatera tabeller”.

Nu skulle jag vilja ta bort alla personer som bor i Stockholm från registret.

Som vanligt går vi in i menyn Verktyg/SQL och skriver sen in följande text:

DELETE FROM ”Adressregister”
WHERE ”Postadress”=’Stockholm’

Texten behöver knappast någon närmare förklaring mer än att jag återigen måste nämna att tabellnamn och fältnamn kräver dubbelfnuttar och värden enkelfnutt, dvs. det som hamnar efter lika-med-tecknet. Detta skiljer sig från program till program och Base är uppbyggt på detta sätt.

Så här ser tabellen ut efter man kört sql-kommandot:

Vi har fått bort alla personer från Stockholm från tabellen.

Nu försvårar vi det hela en smula. Jag vill nu ta bort alla personer som bor på Storgatan i Göteborg. Som vi kan se så finns här tre personer i nuläget. Problemet är att vi har två Storgatan med olika nummer på gatan. Alltså kan vi inte sätta ett lika-med-tecken.

Nu skriver vi därför in följande sql-kommando:

DELETE FROM ”Adressregister”
WHERE ”Postadress”=’Göteborg’ AND ”Adress” LIKE ’Storgatan*’

och tabellen ser ut på följande sätt efter vi kört kommandot:

I denna fråga använde vi oss av LIKE och satte sen värdet Storgatan med jokertecknet * efteråt. Detta betyder att alla poster som innehåller Göteborg från kolumnen Postadress samt där adressen börjar med Storgatan kommer att tas bort.

aug 112010
 

Flattr this!

Nu ska jag visa hur man kan komplettera sin databas via ett sql-kommando.

Jag utgår från filmdatabasen återigen som man finner här.

Om jag dubbelklickar på tabellikonen för filmerna så finner jag dessa filmer:

Fälten vi har att tillgå i mitt fall är FilmID, Filmnamn och År.

Gå in under Verktyg/SQL och skriv följande sql-kommando:

INSERT INTO ”Filmtabell”(”FilmID”,”Filmnamn”,”År”)
VALUES(9,’Alien’,1979);

Namnen måste stämma överens med fältnamnen som finns i databasen och dessutom är fnuttar och dubbelfnuttar ett måste på sättet jag visar.

INSERT INTO ”Filmtabell”(”FilmID”,”Filmnamn”,”År”)

Den här raden anger vilken tabell som gäller och inom parentesena står fältnamnen.

VALUES(9,’Alien’,1979);

Posten vi vill lägga till är Alien som är gjord år 1979. FilmID kommer att bli 9 för denna filmen. Notera att man inte kan ha samma id som redan finns bland befintliga poster.

Nu kan man fråga sig vad nyttan är med ett krångligt sql-kommando som är jobbigare än att skriva in posten på vanligt sätt.

I detta exempel kommer detta att framgå.

Skapa en ny tabell. Jag väljer att skapa ”Filmtabell2”. Denna tabell ger jag fältnamnen FilmID2, Movie2 och Year. Att jag väljer engelska språket för två fältnamn är med vilje för att demonstrationen ska bli bättre. I denna tabell ska FilmID2 vara nyckel och det är integer som gäller här på samma sätt som i den andra tabellen. Skillnaden är att vi inte vill ha en räknare.

Lägg nu in lite poster i databasen:

Notera att jag själv matat in nyckeln eftersom vi inte har en räknare och jag väljer då värden som inte finns i den andra tabellen.

För övrigt så såg den andra tabellen ut så här för mig:

Gå nu upp i Verktyg/SQL igen och skriv in följande:

INSERT INTO ”Filmtabell”(”FilmID”,”Filmnamn”,”År”)
SELECT  ”FilmID2″,”Movies2″,”Year” from ”Filmtabell2”;

och resultatet blir detta i tabellen Filmtabell:

Vi har med annat ord sammanfogat data från två tabeller. Ett mycket värdefullt redskap om man har tusentals poster fördelat på två tabeller.

INSERT INTO ”Filmtabell”(”FilmID”,”Filmnamn”,”År”)

Denna raden säger att vi vill lägga in data i Filmtabell och det är fältnamnen FilmID, Filmnamn och År som datan ska matas in i.

SELECT  ”FilmID2″,”Movies2″,”Year” from ”Filmtabell2”;

Här väljer vi var datan ska hämtas ifrån och det är Filmtabell2 som gäller och då är det fältnamnen FilmID2, Movies2 och Year datan ska hämtas ifrån. Fältnamnen i FilmID2 måste stå i samma ordning som i Filmtabell. Skulle vi möblera om och skriva ”FilmID2″,”Year”,”Movies2″ på andra raden så hamnar filmnamnen i År-fältet.

jun 062010
 

Flattr this!

Ibland kan det vara intressant att få fram lite statistik från en databas, kanske räkna summan av flera poster, göra uträkningar av olika slag. I mitt exempel utgår jag från exemplet med filmdatabasen som jag visade här.

Jag vill räkna hur många olika genrer varje film har.

Ett formulär är inte nödvändigt för denna uppgift men kan underlätta för att knappa in uppgifter i databasen. I så fall kan denna också behövas.

Jag valde att knappa in en hel del data i min filmdatabas för att få lite utslag:

Dessutom med genre som passar dessa filmer:

I filmen Den gode, den onde, den fule som bilden ovan visar valde jag att sätta tre olika genrer på för att få lite bättre resultat i slutändan.

När allting är klart så vill vi räkna ut hur många genrer varje film har.

I huvudfönstret väljer vi ”Sökningar” till vänster och sen ”Skapa sökning i SQL-vy”.

Där skriver vi in följande:

eller klistra in följande:

SELECT ”Filmtabell”.”Filmnamn”, COUNT(”Genretabell”.”Genre”) FROM ”FilmGenre”, ”Filmtabell”, ”Genretabell”
WHERE ”FilmGenre”.”FilmID” = ”Filmtabell”.”FilmID” AND ”FilmGenre”.”GenreID” = ”Genretabell”.”GenreID”
GROUP BY ”Filmtabell”.”Filmnamn”

och när vi väljer kör så får vi detta resultat:

I resultatet så har vi först Die Hard där det står en 2:a, alltså action och komedi. En 3:a på den gode, den onde, den fule som står för västern, komedi och action, alltså 3 stycken.

Här är förklaringen till vårt sql-kommando:

SELECT ”Filmtabell”.”Filmnamn”,

Vi vill få fram filmnamnen i sökningen och som ni kan notera så har jag tabellnamn.fältnamn. Detta är ett måste då vi rotar i flera tabeller i samma fråga. Två tabeller kan nämligen ha fältnamn som är samma och detta skiljer dessa åt.

COUNT(”Genretabell”.”Genre”)

Vi vill ha en uträkning baserat på fältet genrer i tabellen Genretabell.

FROM ”FilmGenre”, ”Filmtabell”, ”Genretabell”

Tabellerna som används i frågan deklareras. I detta fallet är det samtliga tre som används.

WHERE ”FilmGenre”.”FilmID” = ”Filmtabell”.”FilmID” AND ”FilmGenre”.”GenreID” = ”Genretabell”.”GenreID”

Vi kopplar Filmgenre med respektive i Filmtabell och Genretabell. Detta måste göras för att kunna utnyttja alla tabeller.

GROUP BY ”Filmtabell”.”Filmnamn”

Eftersom vi håller på med en gruppfunktion så behövs GROUP BY.

Nu vill vi ha listan sorterad. Jag skulle vilja ha listan sorterad på detta viset:

alltså med högsta numret överst och sen fallande. Som jag visade i sql-skolan del 1 så kan man köra med ORDER BY Filmtabell.Filmnamn om man vill ha listan sorterad efter film. Det är bara att skriva denna rad under GROUP BY-raden.

Nu vill vi att uträkningen ska sorteras och då är det COUNT(”Genretabell”.”Genre”). Detta går inte genom att skriva ORDER BY COUNT(”Genretabell”.”Genre”) utan vi måste fixa ett alias.

COUNT(”Genretabell”.”Genre”) AS räkna

Nu motsvarar ”räkna” hela den långa raden. Därefter är det bara att skriva detta under GROUP BY-raden:

ORDER BY räkna DESC

Desc för att vi vill ha fallande sortering.

Därför blir den kompletta frågan så här:

SELECT ”Filmtabell”.”Filmnamn”, COUNT(”Genretabell”.”Genre”) AS räkna FROM ”FilmGenre”, ”Filmtabell”, ”Genretabell”
WHERE ”FilmGenre”.”FilmID” = ”Filmtabell”.”FilmID” AND ”FilmGenre”.”GenreID” = ”Genretabell”.”GenreID”
GROUP BY ”Filmtabell”.”Filmnamn”
ORDER BY räkna DESC

Jag kommer att fortsätta med statistik i senare bloggar. I denna har jag valt COUNT (räkna) men det finns även summa, medelvärde och annat smått och gott.

maj 162010
 

Flattr this!

Tänkte visa hur man skriver ett sql-kommando för att åstadkomma samma sak som vi gjorde här.

Tanken är med andra ord att göra en ny tabell med fältnamnen ID, Namn, Regissör, År och Beskrivning som man kan se här.

Tabellen har primärnyckeln ID och ska ha autovärde så den räknar upp automatiskt från 0, 1, 2 osv.

Så här åstadkommer vi detta:

Skapa en ny databas eller ta en befintlig, spelar ingen roll och stå i huvudfönstret:

Tryck på verktygsmenyn och välj SQL som bilden visar.

I följande fönster som poppar upp står det ”Kommando att utföra”. Här ska vi ange vårt kommando för att skapa tabellen. Jag väljer att kalla den ”Filmdatabas_SQL” för enkelhetens skull.

Skriv in följande kommando:

eller kopiera in detta:

CREATE TABLE ”Filmdatabas_SQL” (
”ID” INTEGER IDENTITY,
”Namn” VarChar(50),
”Regissör” VarChar(50),
”År” VarChar(50),
”Beskrivning” LongVarChar,
PRIMARY KEY (”ID”)
);

Tryck sen på ”Utför” för att köra kommandot.

Man ser tydligt om kommandot har fungerat som bilden visar. Är det någon felstavning eller fel kommando så blir det ett felmeddelande istället.

När detta är gjort så kan man stänga fönstret. Gå sen upp i menyn ”Visa” och välj ”Uppdatera tabeller” och tabellen kommer att dyka upp i huvudfönstret.

Högerklicka på tabellen och välj ”redigera” för att se hur fälten blev i tabellen.

Som ni kan se så har samtliga fält skapats som vi ville precis som när vi skapade tabellen via guiden. Notera även dom fälten jag ringat in. Fältet ”ID” har en nyckel till vänster om sig, alltså en primär nyckel och dessutom är AutoVärde satt till ”Ja”.

Förklaring:

CREATE TABLE ”Filmdatabas_SQL” (

Här skapar vi tabellen Filmdatabas_SQL. Efter tabellnamnet så börjar fältnamnen med ett parentes. Efter sista fältet är inskrivet så blir det slutparentes.

”ID” INTEGER IDENTITY,

Här skapar vi fältet ID som ska vara ett numeriskt fält, alltså sätter vi INTEGER. Nyckeln är inte satt än men ”IDENTITY” gör att AutoValue sätts till ja. Dessutom ska det vara ett kommatecken efter varje fältnamn så i detta fallet sätts kommatecknet efter IDENTITY.

”Namn” VarChar(50),
”Regissör” VarChar(50),
”År” VarChar(50),

Fältnamnen ”Namn”, ”Regissör” och ”År skapas och här är det character som gäller, alltså bokstäver och jag har satt att namnet maximalt får innehålla 50 tecken.

”Beskrivning” LongVarChar,

Fältnamnet ”Beskrivning” skapas och här är det LongVarChar som är satt. Från början så kunde VarChar bara innehålla 255 tecken medans LongVarChar klarade 32700 tecken. Nu klarar dock VarChar av 32672 tecken så det spelar ingen roll för denna saken.

Förklaringen på detta går jag inte in på men man kan läsa mer här.

PRIMARY KEY (”ID”)

Här sätter vi att fältet ”ID” ska vara en primär nyckel.

Sen avslutas det hela med en slutparentes och semikolon.

maj 082010
 

Flattr this!

Sist visade jag om hur man gjorde en sökning via guiden och detta är ett bra sätt att göra en sökning. Ändå bättre är om man lär sig att använda sql-språket för att skapa frågan. Man kan säga att det är en backend till guiden vi använde här. Skillnaden är att möjligheterna är obegränsade om man kan skriva frågan manuellt.

Detta är med annat ord ett programmeringsspråk där SQL står för ”Structured Query Language” och med detta menas att man kan göra de mesta, dvs. skapa tabeller och frågor till dessa.

Allt jag visar kommer att baseras på ett exempel så det inte finns några oklarheter. Dessutom förklarar jag rad för rad vad koden gör.

Jag tänker visa exakt samma sökning som jag gjorde med denna guide.

Vi hade med annat ord en databas där vi ville söka efter alla filmer i databasen som hade Alfred Hitchcock som regissör men vi ville utesluta fåglarna. Dessutom ska listan vi får fram ordnas så Namn visas i bokstavsordning.

Så här går vi till väga. Markera ”Sökningar” till vänster och välj alternativet ”Skapa sökning i SQL-vy”:

och man kommer till ett blankt fönster:

Skriv in denna kod här:

SELECT ”Namn”, ”Regissör”, ”År”, ”Beskrivning” FROM ”Filmer”
WHERE ”Regissör”=’Alfred Hitchcock’
AND ”Namn”<>’Fåglarna’
ORDER BY ”Namn”

Notera att allting visas med olika färger. Kommandon visas med blå text och allt annat med orange färg, detta för att underlätta vid inskrivandet. Skulle man skriva fel någonstans så är det lättare att felsöka.

och tryck på denna knapp efteråt för att utföra sökningen:

Resultatet blir precis samma som när vi använde guiden. Skillnaden är att vi har skrivit in fyra rader kod istället.

SELECT ”Namn”, ”Regissör”, ”År”, ”Beskrivning” FROM ”Filmer”

Denna rad säger att vi vill ha fälten Namn, Regissör, År och Beskrivning med i Sökresultatet och att det är tabellen Filmer som gäller. Glöm inte att ha kommatecken mellan varje fältnamn.

WHERE ”Regissör”=’Alfred Hitchcock’

Detta är första kriteriet vi vill ha. Regissören ska vara Alfred Hitchcock. Notera enkelfnuttarna kring namnet.

AND ”Namn”<>’Fåglarna’

Det andra kriteriet är att vi inte vill få med filmen Fåglarna i sökningen. Notera <> som betyder skilt från, alltså skilt från Fåglarna.

ORDER BY ”Namn”

Slutligen vill vi sortera listan efter filmerna från A-Ö och väljer då fältnamnet Namn.

ORDER BY Namn DESC

Denna rad skulle få listan sorterad från Ö-A. DESC står för Descending (fallande ordning). Vi hade lika gärna kunna skriva ASC som står för ascending (stigande ordning) men det är inte nödvändigt. Skriver man inget så blir det automatiskt stigande ordning.

Som regel är det bra att sätta dubbelfnuttar kring fältnamnen och enkelfnuttar kring sökkriterier. Enkelfnuttarna måste finnas men dubbelfnuttarna sätts dit automatiskt så fort man sparat sökningen.

Stäng nu frågan och spara med lämpligt namn och vi har två olika sökningar mot vår databas:

För att redigera frågan så är det bara att högerklicka på ikonen och välja antingen ”redigera” eller ”redigera i sql-vy”. Väljer vi ”redigera” så kommer vi till den hederliga grafiska vyn som jag visade med guiden:

Väljer vi istället ”redigera i sql-vy” så kommer vi tillbaks till vår kod och kan ändra där istället.