Lær MySQL/MariaDB for begyndere - Del 1


I denne artikel viser vi, hvordan du opretter en database (også kendt som et skema), tabeller (med datatyper) og forklarer, hvordan du udfører DML-operationer (Data Manipulation Language) med data på en MySQL/MariaDB-server.

Det antages, at du tidligere har 1) installeret de nødvendige pakker på dit Linux-system, og 2) udført mysql_secure_installation for at forbedre databaseserverens sikkerhed. Hvis ikke, skal du følge nedenstående vejledninger for at installere MySQL/MariaDB-serveren.

  1. Installer nyeste MySQL-database i Linux-systemer
  2. Installer nyeste MariaDB-database i Linux-systemer

For kortfattethed vil vi udelukkende henvise til MariaDB i hele denne artikel, men de begreber og kommandoer, der er beskrevet her, gælder også for MySQL.

Oprettelse af databaser, tabeller og autoriserede brugere

Som du ved, kan en database defineres i enkle termer som en organiseret informationssamling. Især er MariaDB et relationsdatabasesystem (RDBMS) og bruger Structure Query Language til at udføre operationer på databaser. Derudover skal du huske, at MariaDB bruger udtrykkene database og skema om hverandre.

For at gemme vedvarende information i en database bruger vi tabeller, der gemmer datarækker. Ofte vil to eller flere tabeller være relateret til hinanden på en eller anden måde. Det er en del af den organisation, der karakteriserer brugen af relationsdatabaser.

For at oprette en ny database med navnet BooksDB skal du indtaste MariaDB-prompten med følgende kommando (du bliver bedt om at indtaste adgangskoden til root MariaDB-brugeren):

 mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 

Når databasen er oprettet, skal vi oprette mindst to tabeller på den. Men lad os først undersøge begrebet datatyper.

Introduktion til MariaDB-datatyper

Som vi forklarede tidligere, er tabeller databaseobjekter, hvor vi vil beholde vedvarende information. Hver tabel består af to eller flere felter (også kendt som kolonner) af en given datatype (typen af information), som et sådant felt kan gemme.

De mest almindelige datatyper i MariaDB er følgende (du kan se den komplette liste i den officielle MariaDB online-dokumentation):

  1. BOOLEAN betragter 0 som falsk og andre værdier som sande.
  2. TINYINT, hvis det bruges med SIGNED, dækker området fra -128 til 127, mens det UNSIGNED-interval er 0 til 255.
  3. SMALLINT, hvis det bruges sammen med SIGNED, dækker området fra -32768 til 32767. Det UNSIGNED-interval er 0 til 65535.
  4. INT, hvis det bruges med UNSIGNED, dækker området fra 0 til 4294967295 og ellers -2147483648 til 2147483647.

Bemærk: I TINYINT, SMALLINT og INT antages standard SIGNED.

DOBBELT (M, D), hvor M er det samlede antal cifre, og D er antallet af cifre efter decimaltegnet, repræsenterer et flydepunktnummer med dobbelt præcision. Hvis UNSIGNED er angivet, er negative værdier ikke tilladt.

  1. VARCHAR (M) repræsenterer en streng med variabel længde, hvor M er den maksimalt tilladte kolonnelængde i byte (65.535 i teorien). I de fleste tilfælde er antallet af byte identisk med antallet af tegn bortset fra nogle tegn, der kan tage op til 3 byte. For eksempel repræsenterer det spanske bogstav ñ et tegn, men optager 2 bytes.
  2. TEKST (M) repræsenterer en kolonne med en maksimal længde på 65.535 tegn. Men som det sker med VARCHAR (M), reduceres den faktiske maksimale længde, hvis der er gemt multi-byte-tegn. Hvis M er angivet, oprettes kolonnen som den mindste type, der kan gemme et sådant antal tegn.
  3. MEDIUMTEXT (M) og LONGTEXT (M) svarer til TEXT (M), kun at de maksimalt tilladte længder er henholdsvis 16.777.215 og 4.294.967.295 tegn.

  1. DATE repræsenterer datoen i ÅÅÅÅ-MM-DD-format.
  2. TID repræsenterer tiden i HH: MM: SS.sss-format (time, minutter, sekunder og millisekunder).
  3. DATETIME er kombinationen af DATE og TIME i ÅÅÅÅ-MM-DD HH: MM: SS-format.
  4. TIMESTAMP bruges til at definere det øjeblik, en række blev tilføjet eller opdateret.

Efter at have gennemgået disse datatyper, vil du være i en bedre position til at bestemme, hvilken datatype du skal tildele til en given kolonne i en tabel.

For eksempel kan en persons navn nemt passe ind i en VARCHAR (50), mens et blogindlæg har brug for en TEKST-type (vælg M efter dine specifikke behov).

Før vi dykker ned i oprettelse af tabeller, er der to grundlæggende begreber om relationsdatabaser, som vi skal gennemgå: primære og udenlandske nøgler.

En primær nøgle indeholder en værdi, der entydigt identificerer hver række eller post i tabellen. På den anden side bruges en fremmed nøgle til at oprette et link mellem dataene i to tabeller og til at kontrollere de data, der kan lagres i tabellen, hvor den fremmede nøgle er placeret. Både primære og udenlandske nøgler er generelt INT'er.

For at illustrere, lad os bruge BookstoreDB og oprette to tabeller med navnet AuthorsTBL og BooksTBL som følger. IKKE NULL-begrænsningen angiver, at det tilknyttede felt kræver en anden værdi end NULL.

AUTO_INCREMENT bruges også til at øge værdien af INT primære nøglekolonner med en, når en ny post indsættes i tabellen.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 

Nu kan vi gå videre og begynde at indsætte poster i AuthorsTBL og BooksTBL .

Vi udfylder først tabellen AuthorsTBL . Hvorfor? Fordi vi skal have værdier for AuthorID før vi indsætter poster i BooksTBL.

Udfør følgende forespørgsel fra din MariaDB-prompt:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

Derefter vælger vi alle poster fra AuthorsTBL. Husk, at vi har brug for AuthorID for hver post for at oprette INSERT-forespørgslen til BooksTBL.

Hvis du vil hente en post ad gangen, kan du bruge en WHERE-klausul til at angive en betingelse, som en række skal opfylde for at blive returneret. For eksempel,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternativt kan du vælge alle poster samtidigt:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Lad os nu oprette INSERT-forespørgslen til BooksTBL ved hjælp af den tilsvarende AuthorID til at matche forfatteren af hver bog. En værdi på 1 i BookIsAvailable angiver, at bogen er på lager, ellers 0:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

På dette tidspunkt foretager vi en SELECT for at se optegnelserne i BooksTBL. Lad os derefter opdatere prisen på "The Alchemist" af Paulo Coelho og VÆLG den specifikke rekord igen.

Bemærk, hvordan BookLastUpdated-feltet nu viser en anden værdi. Som vi forklarede tidligere, viser et TIMESTAMP-felt værdien, da posten blev indsat eller sidst ændret.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> 

Selvom vi ikke gør det her, kan du også slette en post, hvis den ikke bruges mere. Antag for eksempel, at vi vil slette “Alkymisten” fra BooksTBL.

For at gøre dette bruger vi DELETE-sætningen som følger:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

Som i tilfælde af UPDATE er det en god ide at foretage en SELECT først for at se den eller de poster, der potentielt kan blive påvirket af DELETE.

Glem ikke at tilføje WHERE-klausulen og en betingelse (BookID = 6) for at vælge den specifikke post, der skal fjernes. Ellers risikerer du at slette alle rækkerne i tabellen!

Hvis du ønsker at sammenkæde to (eller flere) felter, kan du bruge CONCAT-sætningen. Lad os f.eks. Sige, at vi vil returnere et resultatsæt, der består af et felt med bognavnet og forfatteren i form af "Alkymisten (Paulo Coelho)" og en anden kolonne med prisen.

Dette kræver en JOIN mellem AuthorsTBL og BooksTBL på det fælles felt, der deles af begge tabeller (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

Som vi kan se, giver CONCAT os mulighed for at slutte sig til flere strengudtryk adskilt af kommaer. Du vil også bemærke, at vi valgte aliaset Beskrivelse til at repræsentere resultatsættet for sammenkædningen.

Outputtet fra ovenstående forespørgsel vises i nedenstående billede:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Brug af root til at udføre alle DML-operationer i en database er en dårlig idé. For at undgå dette kan vi oprette en ny MariaDB-brugerkonto (vi navngiver den boghandelbruger) og tildele alle nødvendige tilladelser til BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstore[email  IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email ;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER [email  IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to [email ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

At have en dedikeret, separat bruger til hver database forhindrer skader på hele databasen, hvis en enkelt konto bliver kompromitteret.

For at rydde MariaDB-prompten skal du skrive følgende kommando og trykke på Enter:

MariaDB [BookstoreDB]> \! clear

For at inspicere konfigurationen af en given tabel skal du gøre:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

For eksempel,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

En hurtig inspektion afslører, at BookIsAvailable-feltet tillader NULL-værdier. Da vi ikke vil tillade det, ÆNDRER vi tabellen som følger:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Du er velkommen til at vise kolonnerne igen - det fremhævede JA i ovenstående billede skal nu være NEJ).

Endelig skal du gøre for at se alle databaser på din server:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
 mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

Det følgende billede viser resultatet af ovenstående kommando efter adgang til MariaDB-prompt som boghandelbruger (bemærk, hvordan denne konto ikke kan "se" andre databaser end BookstoreDB og information_schema (tilgængelig for alle brugere):

Resumé

I denne artikel har vi forklaret, hvordan man kører DML-operationer, og hvordan man opretter en database, tabeller og dedikerede brugere på en MariaDB-database. Derudover delte vi et par tip, der kan gøre dit liv som system/database administrator lettere.

  1. MySQL-databaseadministrationsdel - 1
  2. MySQL-databaseadministrationsdel - 2
  3. MySQL Performance Tunning and Optimization - Part 3

Hvis du har spørgsmål om denne artikel, tøv ikke med at fortælle os! Du er velkommen til at bruge kommentarformularen nedenfor for at nå os.