Lär dig SQL

Back to All Courses

Lesson 5

Modifiera tabeller

by Ted Klein Bergman

Lägga till nya tuples

Nu när vi har våra tabeller är det dags att lägga in data i dem. Detta kan vi göra med genom kommandot:

INSERT INTO <table> (<attributes>) VALUES (<values>);

  • <table> tabellen vi vill lägga in värdena i.

  • <attributes> namnen på de attribut vi vill tilldela värden.

  • <values> de värdena vi vill attributen ska ha.

Ett exempel kan vara att lägga in en ny användare i vår tabell users.

INSERT INTO users (username, email, age) 
VALUES ('First', 'example@hotmail.com', 18);

Om kommandot lyckas kommer det komma upp en text "INSERT". Ni kan använda er av kommandot SELECT * FROM users; för att se vad som finns i tabellen. Mer om detta i nästa kapitel.

Dock finns det många saker som kan få kommandot att misslyckas. Tänk på alla restriktioner som vi har lagt till i tabellen! Om ni testar köra exakt samma kommando igen kommer ett felmeddelande komma upp. Det är förstås för att vi inte tillåter identiska emailadresser i tabellen; varje email måste vara unik.

En annan sak att notera är att vi inte valde att tilldela ett värde till user_id. Detta fungerar eftersom datatypen var GENERATED ALWAYS AS IDENTITY, vilket automatiskt tilldelar attributet med ett unikt heltal.

Det går även att lägga till flera tuples samtidigt genom att ange flera grupper av data separerade med kommatecken.

INSERT INTO users (username, email) 
VALUES  ('supreme hotdog', 'spicySpice22@hotmail.com'),
        ('super trooper1', 'carChaserTwo@gmail.com'),
        ('summer of 1968', 'rockerPopper@hotmail.com');

Om allt går som det ska så ska ni få meddelandet "INSERT". Notera även att det går att ignorera attributet age. Då kommer attributet att automatiskt bli tilldelad värdet NULL. Dock skulle det inte vara okej med de andra attributen eftersom de har restriktionen NOT NULL, d.v.s. att de inte får bli tilldelade värdet NULL.

En sak som kan vara värt att veta är att attributnamnen inte behöver komma i den ordning som de är definierade i tabellen. Alltså är detta okej:

INSERT INTO users (age, username, email) 
VALUES (85, 'opinionated potato', 'estdev@kth.com');

Det är också okej att strunta i att lista upp attributen. Då förväntas värdena att vara i samma ordning som de är definierade i tabellen. Exempelvis är det följande ekvivalenta:

INSERT INTO users
VALUES (33, 12);

INSERT INTO users (user_id, username, email, age)
VALUES (33, 'redrum', 'gge_retsae@liamtoh.moc', 125);

Detta fungerar dock inte för vår tabell users, då vi hade varit tvungna att ange ett ID för användare, vilket inte går då användarens ID är auto-genererat.

Uppdatera tuples

Vi vill nu byta åldern på användaren vi lade till. Tydligen hade användaren ljugit om sin ålder för att komma åt tweets som är skadliga för barn, exempelvis från MetaSpexet. För att uppdatera ett attribut används syntaxen:

UPDATE <table>
SET <attribute> = <value> 
WHERE <condition>;

  • <table> tabellen vi vill uppdatera värden i.

  • <attribute> attributet vi vill uppdatera.

  • <value> värdet vi vill tilldela attributet.

  • <condition> det villkor som ska vara uppfyllt för att en tuples attribut ska tilldelas ett nytt värde.

WHERE-satsen är valfri att ha, men om vi inte använder oss av den kommer alla tuples att påverkas, vilket oftast inte är önskvärt. I vårat fall vill vi ändra åldern på användaren med ID 5 från 85 till 15. Det gör vi på detta vis:

UPDATE users 
SET age = 15 
WHERE user_id = 5;

Här kommer alla användare med user_id = 5 (vilket i vårat fall endast är en användare då varje ID är unikt) att få åldern 15. Det går att ändra flera attribut samtidigt genom att separera tilldelningen med ett kommatecken:

UPDATE users 
SET age = 15, email = 'troller1337@gmail.com'
WHERE user_id = 5;

Om allt går som det ska kommer texten "UPDATE 1" komma upp.

Ta bort tuples

Den användaren vi har lagt till har använt vårat nätverk för att trolla genom att skriva att Chalmers är bättre än KTH. Det är inte acceptabelt. Det är dags att ta bort användaren. Det kan göras med hjälp av syntaxen:

DELETE FROM <table> 
WHERE <condition>;

  • <table> tabellen vi vill ta bort tuples ifrån.

  • <condition> villkoret som måste vara sant för en tuple ska tas bort.

Detta är användbart för att ta bort flera tuples i ett kommando, t.ex. om vi vill ta bort alla användare under en viss ålder eller som använder "msn.com" som emaildomän. I detta fall vill vi bara ta bort en användare; användaren med ID 5. Det skulle se ut så här:

DELETE FROM users 
WHERE user_id = 5;

Om allt gått rätt till kommer meddelandet "DELETE 1" komma upp.

Fylla våra tabeller

INSERT-satsen kräver att man skriver in allt manuellt, vilket kan vara väldigt jobbigt när man ska fylla in stora tabeller. Lättare vore att kopiera in data från en fil som var genererad från ett program eller skapad av någon annan.

I mappen data i Tweeter projektet som ni laddade ned finns filer med värden för vardera tabell. Varje fil är en CSV-fil, d.v.s. att varje rad representerar en tuple och varje attribut är separerade med ett kommatecken. För att kopiera in datan till tabellerna använder vi följande kod.

\copy <table> (<attribute_list>) FROM <path> USING DELIMITERS ',';

  • <table> är namnet på tabellen vi vill fylla.

  • <attribute_list> är de attributen vi vill tilldela värden (från filen).

  • <path> är platsen där filen ligger (använd absolute path).

Om vi exempelvis vill fylla vår Users tabell, skulle det kunna se ut så här.

\copy users (username, email, age) FROM 'Users/Me/PycharmProjects/Tweeter/data/users_data.txt' USING DELIMITERS ',';

Notera att forward-slash ('/') ska användas för din path, även om du använder Windows!

Anledningen till att vi använder ett snedstreck framför copy är för att detta är ett psql-kommando, inte ett SQL-kommando. Detta för att SQL inte kan interagera med användares datorer (såsom öppna filer), utan det interagerar bara med databashanteringssystemet. Något annat att notera är att pqsl-kommandon körs när det stöter på ett radslut, vilket betyder att allt måste skrivas på en rad.

Några användbara psql-kommandon att kunna är:

  • \d : Se vilka tabeller (med mera) databasen har.

  • \connect <database> : Koppla dig till databasen <database>.

  • \i <file>  : Kör SQL-kommandon från en fil.

  • \! <command>  : Kör ett bash kommando.

  • \? : Se vad för psql-kommandon som finns tillgängliga.

DROP TABLE

En vanlig företeelse i sociala nätverk är att man får nog och vill bara bli av med allt och alla. För att göra oss av med en skapad tabell kan vi använda oss av nyckelordet DROP TABLE följt av namnet på tabellen vi vill ta bort. Om vi kör följande kod:

DROP TABLE users;

Detta kommer att ta bort tabellen users. Dock kommer detta inte fungera för våran databas då bägge tabellerna tweets och followers refererar till värden i tabellen users och skulle då inte ha någonstans att referera till. Vad vi kan göra är att först radera tweets och followers innan vi raderar users, eller så kan vi använda oss av CASCADE. CASCADE betyder i detta fall "radera även tabellerna som är beroende av denna tabellen".

DROP TABLE users CASCADE;

Nu bör alla tabeller vara borta. Om du testkörde detta behöver du skapa om dem igen inför kommande kapitel. Det finns ett färdigt script i  Tweeter, som heter create.sql, som skapar och fyller alla tabeller åt dig. Det kan köras genom följande kommando:

\i /Users/Me/PycharmProjects/Tweeter/data/create.sql

Detta kan du köra varje gång du vill återställa databasen med de tabeller och data vi kommer behöva senare.

Exercise

Vi har en tabell som är skapade enligt följande:

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name         TEXT,
    manufacturer TEXT NOT NULL,
    stock	     INTEGER
);

Vi vill nu sätta in data i denna tabell. Vilken av dessa kommer att fungera, och vilka kommer ge ett felmeddelande?

  1. INSERT INTO products VALUES (1, 'Mugg', 'Icea', 5);

  2. INSERT INTO products (product_id, name, manufacturer, stock) VALUES (2, 'Glas', 'Lidel', 10);

  3. INSERT INTO products VALUES ('Sked', '11-Seven');

  4. INSERT INTO products (manufacturer, name) VALUES ('Hemskköp', 'Tallrik');

  5. INSERT INTO products (stock, name) VALUES (4, 'Tallrik');

Solution

  1. Fungerar inte då product_id är auto-genererat.

  2. Fungerar inte då product_id är auto-genererat.

  3. Fungerar inte då vi inte anger vilka attribut vi vill sätta. Därför kommer det tolkas som INSERT INTO products (product_id, name, manufacturer, stock) VALUES ('Sked', '11-Seven'), vilket inte är lagligt då product_id är auto-genererat

  4. Fungerar, då stock får vara NULL.

  5. Fungerar inte då manufacturer inte får vara NULL.

Exercise

Med DELETE FROM och UPDATE kan man ha en valfri WHERE sats. Vad händer om man inte har en WHERE sats?

Solution

Hela tabellen kommer att raderas eller så kommer alla attribut man listar upp uppdateras.