Lær MySQL/MariaDB for nybegynnere - del 1


I denne artikkelen vil vi vise hvordan du oppretter en database (også kjent som et skjema), tabeller (med datatyper) og forklarer hvordan du utfører Data Manipulation Language (DML ) operasjoner med data på en MySQL/MariaDB-server.

Det antas at du tidligere har 1) installert de nødvendige pakkene på Linux-systemet ditt, og 2) utført mysql_secure_installation for å forbedre databaseserverens sikkerhet . Hvis ikke, følg veiledningene nedenfor for å installere MySQL/MariaDB-serveren.

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

For korthets skyld vil vi referere til MariaDB utelukkende gjennom denne artikkelen, men konseptene og kommandoene som er skissert her, gjelder også for MySQL.

Opprette databaser, tabeller og autoriserte brukere

Som du vet kan en database på en enkel måte defineres som en organisert samling av informasjon. Spesielt MariaDB er et relasjonsdatabaseadministrasjonssystem (RDBMS) og bruker Structure Query Language for å utføre operasjoner på databaser. Husk i tillegg at MariaDB bruker begrepene database og skjema om hverandre.

For å lagre vedvarende informasjon i en database, vil vi bruke tabeller som lagrer rader med data. Ofte vil to eller flere tabeller være relatert til hverandre på en eller annen måte. Det er en del av organisasjonen som kjennetegner bruken av relasjonsdatabaser.

Opprette en ny database

For å opprette en ny database med navnet BooksDB, skriv inn MariaDB-ledeteksten med følgende kommando (du vil bli bedt om å skrive inn passordet for MariaDB-rotbrukeren):

[root@TecMint ~]# 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 opprettet, må vi lage minst to tabeller på den. Men la oss først utforske konseptet med datatyper.

Vi introduserer MariaDB-datatyper

Som vi forklarte tidligere, er tabeller databaseobjekter der vi oppbevarer vedvarende informasjon. Hver tabell består av to eller flere felt (også kjent som kolonner) av en gitt datatype (informasjonstypen) som et slikt felt kan lagre.

De vanligste datatypene i MariaDB er følgende (du kan se den fullstendige listen i den offisielle MariaDB-dokumentasjonen på nett):

Numerisk:
  1. BOOLEAN anser 0 som usann og alle andre verdier som sanne.
  2. TINYINT, hvis det brukes med SIGNED, dekker området fra -128 til 127, mens UNSIGNED-området er 0 til 255.
  3. SMALLINT, hvis brukt med SIGNED, dekker området fra -32768 til 32767. USIGNED-området er 0 til 65535.
  4. INT, hvis brukt med UNSIGNED, dekker området fra 0 til 4294967295, og -2147483648 til 2147483647 ellers.

Merk: I TINYINT, SMALLINT og INT antas standard SIGNED.

DOUBLE(M, D), der M er det totale antallet sifre og D er antall sifre etter desimaltegnet, representerer en flyttall med dobbel presisjon. Hvis UNSIGNED er spesifisert, tillates ikke negative verdier.

streng:
  1. VARCHAR(M) representerer en streng med variabel lengde der M er den maksimalt tillatte kolonnelengden i byte (65 535 i teorien). I de fleste tilfeller er antall byte identisk med antall tegn, bortsett fra noen tegn som kan ta så mye som 3 byte. For eksempel representerer den spanske bokstaven ñ ett tegn, men tar opp 2 byte.
  2. TEKST(M) representerer en kolonne med en maksimal lengde på 65 535 tegn. Men som det skjer med VARCHAR(M), reduseres den faktiske maksimale lengden hvis flerbyte-tegn lagres. Hvis M er spesifisert, opprettes kolonnen som den minste typen som kan lagre et slikt antall tegn.
  3. MEDIUMTEXT(M) og LONGTEXT(M) ligner på TEXT(M), bare at de maksimalt tillatte lengdene er 16 777 215 og 4 294 967 295 tegn, hhv.
Dato og tid:
  1. DATO representerer datoen i formatet ÅÅÅÅ-MM-DD.
  2. TID representerer tiden i TT:MM:SS.sss-format (time, minutter, sekunder og millisekunder).
  3. DATETIME er kombinasjonen av DATE og TIME i formatet ÅÅÅÅ-MM-DD TT:MM:SS.
  4. TIMESTAMP brukes til å definere øyeblikket en rad ble lagt til eller oppdatert.

Etter å ha gjennomgått disse datatypene, vil du være i en bedre posisjon til å bestemme hvilken datatype du må tilordne til en gitt kolonne i en tabell.

For eksempel kan en persons navn lett passe inn i en VARCHAR(50), mens et blogginnlegg trenger en TEKST-type (velg M som etter dine spesifikke behov).

Lage tabeller med primær- og fremmednøkler

Før vi dykker ned i å lage tabeller, er det to grunnleggende konsepter om relasjonsdatabaser som vi må gjennomgå: primær og fremmed nøkler.

En primærnøkkel inneholder en verdi som unikt identifiserer hver rad eller post i tabellen. På den annen side brukes en fremmednøkkel for å lage en kobling mellom dataene i to tabeller, og for å kontrollere dataene som kan lagres i tabellen der fremmednøkkelen befinner seg. Både primær- og fremmednøkler er vanligvis INT-er.

For å illustrere, la oss bruke BookstoreDB og lage to tabeller kalt AuthorsTBL og BooksTBL som følger. IKKE NULL-begrensningen indikerer at det tilknyttede feltet krever en annen verdi enn NULL.

AUTO_INCREMENT brukes også til å øke verdien av INT primærnøkkelkolonner med én når en ny post settes inn 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]> 

Nå kan vi gå videre og begynne å sette inn poster i AuthorsTBL og BooksTBL.

Velge, sette inn, oppdatere og slette rader

Vi vil først fylle ut AuthorsTBL-tabellen. Hvorfor? Fordi vi må ha verdier for AuthorID før vi setter inn poster i BooksTBL.

Utfør følgende spørring fra MariaDB-ledeteksten din:

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

Etter det vil vi velge alle poster fra AutorsTBL. Husk at vi trenger AuthorID for hver post for å lage INSERT-søket for BooksTBL.

Hvis du ønsker å hente én post om gangen, kan du bruke en WHERE-klausul for å indikere en betingelse som en rad må oppfylle for å bli returnert. For eksempel,

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

Alternativt kan du velge alle postene samtidig:

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]>

La oss nå lage INSERT-søket for BooksTBL, ved å bruke den tilsvarende AuthorID for å matche forfatteren til hver bok. En verdi på 1 i BookIsAvailable indikerer at boken er på lager, 0 ellers:

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 tidspunktet vil vi gjøre en SELECT for å se postene i BooksTBL. La oss deretter OPPDATERE prisen på «Alkymisten » av Paulo Coelho og VELG den spesifikke posten igjen.

Legg merke til hvordan BookLastUpdated-feltet nå viser en annen verdi. Som vi forklarte tidligere, viser et TIMESTAMP-felt verdien da posten ble satt inn eller sist endret.

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]> 

Selv om vi ikke vil gjøre det her, kan du også slette en post hvis den ikke brukes lenger. Anta for eksempel at vi ønsker å slette «Alkymisten » fra BooksTBL.

For å gjøre det bruker vi DELETE-setningen som følger:

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

Som i tilfellet med OPPDATERING, er det en god idé å gjøre en VELG først for å se posten(e) som potensielt kan bli påvirket av SLETT.

Ikke glem å legge til WHERE-klausulen og en betingelse (BookID=6) for å velge den spesifikke posten som skal fjernes. Ellers risikerer du å slette alle radene i tabellen!

Hvis du ønsker å sette sammen to (eller flere) felt, kan du bruke CONCAT-setningen. La oss for eksempel si at vi ønsker å returnere et resultatsett som består av ett felt med boknavnet og forfatteren i form av «Alkymisten (Paulo Coelho)» og en annen kolonne med prisen.

Dette vil kreve en JOIN mellom AuthorsTBL og BooksTBL på fellesfeltet som deles av begge tabellene (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, lar CONCAT oss slå sammen flere strenguttrykk atskilt med komma. Du vil også legge merke til at vi valgte aliaset Beskrivelse for å representere resultatsettet til sammenkoblingen.

Utdataene fra spørringen ovenfor vises i bildet nedenfor:

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)

Opprett bruker for å få tilgang til BookstoreDB-databasen

Å bruke root til å utføre alle DML-operasjoner i en database er en dårlig idé. For å unngå dette kan vi opprette en ny MariaDB-brukerkonto (vi kaller den bookstoreuser) og tildele alle nødvendige tillatelser for BookstoreDB:

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

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

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

Å ha en dedikert, separat bruker for hver database vil forhindre skader på hele databasen dersom en enkelt konto blir kompromittert.

Ekstra MySQL-tips

For å fjerne MariaDB-ledeteksten, skriv inn følgende kommando og trykk Enter:

MariaDB [BookstoreDB]> \! clear

For å inspisere konfigurasjonen av en gitt tabell, gjør du:

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 rask inspeksjon avslører at BookIsAvailable-feltet tillater NULL-verdier. Siden vi ikke vil tillate det, ENDRE tabellen som følger:

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

(Vis gjerne kolonnene igjen – det uthevede JA i bildet ovenfor skal nå være et NEI).

Til slutt, for å se alle databasene på serveren din, gjør du:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
[root@TecMint ~]# 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)

Følgende bilde viser resultatet av kommandoen ovenfor etter tilgang til MariaDB-ledeteksten som bokhandelbruker (merk at denne kontoen ikke kan "se" andre databaser enn BookstoreDB og informasjonsskjema (tilgjengelig for alle brukere):

Sammendrag

I denne artikkelen har vi forklart hvordan du kjører DML-operasjoner og hvordan du oppretter en database, tabeller og dedikerte brukere på en MariaDB-database. I tillegg delte vi noen tips som kan gjøre livet ditt som system-/databaseadministrator enklere.

  1. MySQL-databaseadministrasjonsdel – 1
  2. MySQL Database Administration Del – 2
  3. MySQL ytelsesjustering og optimalisering – del 3

Hvis du har spørsmål om denne artikkelen, ikke nøl med å gi oss beskjed! Bruk gjerne kommentarskjemaet nedenfor for å nå oss.