Ett exempel

För att omsätta teorin från SQL avsnittet kommer vi nu att skapa en två tabeller och välja data ur dem. Exemplet kommer vara en tabell för användare, users, och en för kortare meddelanden. Tabellerna kommer att länkas med användarens id.

Det första som behöver skapas är en databas.

SQL

 CREATE DATABASE exempel CHARACTER SET UTF8mb4 COLLATE utf8mb4_0900_ai_ci;
 USE exempel;

Användare

Användartabellen kommer att kallas users, vi använder engelska för namnet och tabellen kommer att innehålla flera rader av typen user, därför blir det plural, users. Vilka kolumner behöver en tabell innehålla då?

• id, för att identifiera varje rad.

• name, användarnamnet för att identifiera användaren med. Maxlängd 32 tecken.

• email, för att kunna kontakta användaren på något sätt. En email kan teoretiskt sätt vara upp till 254 tecken lång, därför används datatypen varchar med en längd på 255 tecken.

Den första SQL-frågan skapar tabellen och lägger till id kolumnen.

SQL

CREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(id)) 
ENGINE = innodb
DEFAULT CHARSET = utf8mb4;

De andra kolumnerna läggs till efter att tabellen skapats. Ingen av kolumnerna får lämnas tomma och de ska vara unika för att undvika att flera personer använder samma användarnamn och eller email.

SQL

ALTER TABLE users ADD name VARCHAR(32) NOT NULL UNIQUE;
ALTER TABLE users ADD email VARCHAR(255) NOT NULL UNIQUE;

Den resulterande tabellen kan kontrolleras med DESCRIBE frågan.

SQL

DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)  | NO   | UNI | NULL    |                |
| email | varchar(255) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Innan tabellens struktur är klar skapar du två kolumner med tidsstämplar för att underlätta administrationen av databasen.

• created_at, en timestamp för när användaren skapades.

• updated_at, en timestamp för senaste uppdatering.

SQL

ALTER TABLE users ADD created_at timestamp, ADD updated_at timestamp;

Efter den sista ändringen är tabellen klar.

Testdata

För att lägga till data i tabellen så använder vi INSERT. Värdet för id kolumnen ska aldrig sättas med insert, det sköts av databasens räknare(auto_increment). Notera även att vi använder SQL-serverns inbyggda metod now() för att skapa tidsstämpeln för created_at kolumnen.

SQL

INSERT INTO users (name, email, created_at) VALUES ('jens', '[e-mail]', now());

Om frågan är ok så kan tabellens rader sedan väljas med SELECT frågan. * väljer all data från den namngivna tabellen.

SQL

SELECT * FROM users;
+----+------+----------------------------+---------------------+------------+
| id | name | email                      | created_at          | updated_at |
+----+------+----------------------------+---------------------+------------+
|  1 | jens | [e-mail] | 2020-09-25 12:37:51 | NULL       |
+----+------+----------------------------+---------------------+------------+

Meddelanden

Systemet vi skapar ska förutom användare ha en tabell för korta meddelanden, vi kallar de meeps. Precis som för users så är tabellnamnet i plural och tabellen skapas med en id kolumn.

SQL

CREATE TABLE meeps (id INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(id)) 
ENGINE = innodb
DEFAULT CHARSET = utf8mb4;

Efter att tabellen skapats så ändrar vi tabellen för att lägga till fler kolumner.

• author, användaren som skrivit meddelandet.

• title, meddelandets titel med en maxlängd på 128 tecken.

• body, själva meddelandet, ett text fält.

• created_at och updated_at kolumner med tidsstämplar.

SQL

ALTER TABLE meeps ADD author VARCHAR (128) NOT NULL;
ALTER TABLE meeps ADD title VARCHAR (128) NOT NULL;
ALTER TABLE meeps ADD body TEXT NOT NULL;
ALTER TABLE meeps ADD created_at TIMESTAMP NULL, ADD updated_at TIMESTAMP NULL;

Resultatet blir som följer efter en DESCRIBE fråga.

SQL

describe meeps;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int unsigned | NO   | PRI | NULL    | auto_increment |
| author     | varchar(128) | NO   |     | NULL    |                |
| title      | varchar(128) | NO   |     | NULL    |                |
| body       | text         | NO   |     | NULL    |                |
| created_at | timestamp    | YES  |     | NULL    |                |
| updated_at | timestamp    | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Testdata

Innan vi fortsätter att arbeta med tabellen behöver vi skapa testdata. Detta görs precis som tidigare med INSERT. Kör följande fråga några gånger, om du vill ändra så kan du ändra på innehållet, men behåll värdet i author kolumnen.

SQL

INSERT INTO meeps (author, title, body, created_at)
VALUES ('Jens', 'Hello world', 'Lorem ipsum...', now());

När du är klar så bör det finnas ett antal rader i tabellen med data.

SQL

select * from meeps;
+----+--------+-----------------------------+--------------------------------------------+---------------------+------------+
| id | author | title                       | body                                       | created_at          | updated_at |
+----+--------+-----------------------------+--------------------------------------------+---------------------+------------+
|  1 | Jens   | Hello world                 | Lorem ipsum...                             | 2020-09-25 13:02:13 | NULL       |
|  2 | Jens   | Hello there                 | Lorem ipsum dolor sit...                   | 2020-09-25 13:04:34 | NULL       |
|  3 | Jens   | Hello                       | Lorem ipsum dolor sit amet...              | 2020-09-25 13:04:49 | NULL       |
|  4 | Jens   | Hello is anybody out there? | Lorem ipsum dolor sit amet, consectetur... | 2020-09-25 13:05:08 | NULL       |
+----+--------+-----------------------------+--------------------------------------------+---------------------+------------+

Author

Om du studerar tabellens data så ser du att författaren, author, kolumnen på varje inlägg hittills är densamma. Att upprepa data på det sättet, när det rör samma värde är onöskat och bryter mot normaliseringen av en databas. Vad sker tillexempel om användaren Jens byter användarnamn till Snej, då måste alla fält uppdateras, det finns kanske inte heller en tydlig koppling till vem Jens är i systemet. Här kommer arbetet med relationerna i databasen in och hjälper oss att lösa detta.

Tidigare skapade vi tabellen users och skapa en användare i den. Vi kan nu använda detta för att ange författare i meeps tabellen. Första steget blir att ta bort author kolumnen.

SQL

ALTER TABLE meeps DROP author;

Efter att author kolumnen tagits bort så skapar vi en kolumn för användarens id med ett index (för att snabba upp sökningar). Kolumnen skapas först och sedan skapas ett index.

SQL

ALTER TABLE meeps ADD user_id INT UNSIGNED;
CREATE INDEX user_id ON meeps (user_id);

Efter denna uppdatering så har vi normaliserat tabellen, De existerande raderna behöver uppdateras för att koppla varje rad med rätt användar-id. Innan du uppdaterar raderna behöver du kontrollera vilket id som den användare du skapade har, detta så att följande fråga blir korrekt. Detta gäller även kolumnen meeps.id som måste anges och ändras vid frågorna.

SQL

UPDATE meeps 
SET updated_at = now(), user_id = 1
WHERE meeps.id = 1;

UPDATE meeps 
SET updated_at = now(), user_id = 1
WHERE meeps.id = 2;

# och så vidare...

SQL

select * from meeps;
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+
| id | title                       | body                                       | created_at          | updated_at          | user_id |
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+
|  1 | Hello world                 | Lorem ipsum...                             | 2020-09-25 13:02:13 | 2020-09-25 13:21:55 |       1 |
|  2 | Hello there                 | Lorem ipsum dolor sit...                   | 2020-09-25 13:04:34 | 2020-09-25 13:19:53 |       1 |
|  3 | Hello                       | Lorem ipsum dolor sit amet...              | 2020-09-25 13:04:49 | 2020-09-25 13:21:41 |       1 |
|  4 | Hello is anybody out there? | Lorem ipsum dolor sit amet, consectetur... | 2020-09-25 13:05:08 | 2020-09-25 13:21:42 |       1 |
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+

Join

För att koppla ihop tabellerna i en relationsdatabas så används JOIN i SELECT frågan. Det låter dig välja specifik data ur flera tabeller. Det är med hjälp av detta vi kan få tillgång till författarens namn för varje meddelande med hjälp av user_id kolumnen.

SQL

SELECT meeps.*, users.name FROM meeps JOIN users ON meeps.user_id = users.id;
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+------+
| id | title                       | body                                       | created_at          | updated_at          | user_id | name |
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+------+
|  1 | Hello world                 | Lorem ipsum...                             | 2020-09-25 13:02:13 | 2020-09-25 13:21:55 |       1 | jens |
|  2 | Hello there                 | Lorem ipsum dolor sit...                   | 2020-09-25 13:04:34 | 2020-09-25 13:19:53 |       1 | jens |
|  3 | Hello                       | Lorem ipsum dolor sit amet...              | 2020-09-25 13:04:49 | 2020-09-25 13:21:41 |       1 | jens |
|  4 | Hello is anybody out there? | Lorem ipsum dolor sit amet, consectetur... | 2020-09-25 13:05:08 | 2020-09-25 13:21:42 |       1 | jens |
+----+-----------------------------+--------------------------------------------+---------------------+---------------------+---------+------+

Notera att vi specificerar vilka kolumner vi väljer från respektive tabell och syntaxen för detta, tabellnamn.column. * är fortfarande en wildcard och betyder alla kolumner.

Exportera och importera

Vi kan använda programmet mysqldump för att exportera databasen. Det fungerar som en backup och formatet går utmärkt att spara på GitHub. Gör en dump av databasen och studera sedan filen.

En mysqldump är en lång lista på SQL frågor för att återskapa en databas.

Bash

mysqldump -u USERNAME -p DATABASENAME > FILENAME.sql

För att läsa in en dump så används mysql klienten.

Bash

mysql -u USERNAME -p DATABASENAME < FILENAME.sql