Opi MySQL/MariaDB aloittelijoille - Osa 1

Tässä artikkelissa näytämme, kuinka luodaan tietokanta (tunnetaan myös nimellä skeema), taulukoita (tietotyypeineen) ja kerrotaan, kuinka tiedonkäsittelykieli (DML) ) -toiminnot tietojen kanssa MySQL/MariaDB-palvelimella.

Oletetaan, että olet aiemmin 1) asentanut tarvittavat paketit Linux-järjestelmääsi ja 2) suorittanut mysql_secure_installation tietokantapalvelimen suojauksen parantamiseksi. . Jos ei, seuraa alla olevia ohjeita MySQL/MariaDB-palvelimen asentamiseksi.

Asenna uusin MySQL-tietokanta Linux-järjestelmiin
Asenna uusin MariaDB-tietokanta Linux-järjestelmiin

Lyhytyyden vuoksi viittaamme tässä artikkelissa yksinomaan MariaDB:hen, mutta tässä kuvatut käsitteet ja komennot koskevat myös MySQL:ää.

Tietokantojen, taulukoiden ja valtuutettujen käyttäjien luominen

Kuten tiedät, tietokanta voidaan määritellä yksinkertaisella tavalla organisoiduksi tiedon kokoelmaksi. Erityisesti MariaDB on relaatiotietokannan hallintajärjestelmä (RDBMS) ja käyttää rakennekyselykieltä tietokantojen toimintojen suorittamiseen. Muista lisäksi, että MariaDB käyttää termejä tietokanta ja skeema vaihtokelpoisesti.

Pysyvien tietojen tallentamiseksi tietokantaan käytämme taulukoita, jotka tallentavat tietorivejä. Usein kaksi tai useampi taulukko liittyy jollain tavalla toisiinsa. Se on osa organisaatiota, joka luonnehtii relaatiotietokantojen käyttöä.

Uuden tietokannan luominen

Luodaksesi uuden tietokannan nimeltä BooksDB, kirjoita MariaDB-kehote seuraavalla komennolla (sinua pyydetään antamaan MariaDB-juurikäyttäjän salasana):

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

Kun tietokanta on luotu, meidän on luotava siihen vähintään kaksi taulukkoa. Mutta ensin tutkitaan tietotyyppien käsitettä.

Esittelyssä MariaDB-tietotyypit

Kuten aiemmin selitimme, taulukot ovat tietokantaobjekteja, joissa säilytämme pysyviä tietoja. Jokainen taulukko koostuu kahdesta tai useammasta kentästä (tunnetaan myös nimellä sarakkeet), jotka ovat tietyn tietotyypin (tiedon tyypin) mukaisia, jotka kenttä voi tallentaa.

MariaDB:n yleisimmät tietotyypit ovat seuraavat (voit katsoa täydellisen luettelon virallisesta MariaDB-verkkodokumentaatiosta):

Numero:

BOOLEAN pitää arvoa 0 epätosi ja kaikki muut arvot tosina.
TINYINT, jos sitä käytetään SIGNED:n kanssa, kattaa alueen -128–127, kun taas UNIGNED-alue on 0–255.
SMALLINT, jos sitä käytetään SIGNEDin kanssa, kattaa alueen -32768–32767. UNsignED-alue on 0–65535.
INT kattaa alueen 0–4294967295 ja muussa tapauksessa -2147483648–2147483647, jos sitä käytetään UNSINGNEDIN kanssa.

Huomaa: TINYINT-, SMALLINT- ja INT-tiloissa oletusarvoksi oletetaan SIGNED.

DOUBLE(M, D), jossa M on numeroiden kokonaismäärä ja D on desimaalipilkun jälkeen olevien numeroiden määrä, edustaa kaksinkertainen tarkkuus liukuluku. Jos määritetään UNSIIGNEETTÖMÄN, negatiivisia arvoja ei sallita.

merkkijono:

VARCHAR(M) on muuttuvapituinen merkkijono, jossa M on suurin sallittu sarakkeen pituus tavuina (65 535 teoriassa). Useimmissa tapauksissa tavujen määrä on sama kuin merkkien määrä, lukuun ottamatta joitakin merkkejä, jotka voivat viedä jopa 3 tavua. Esimerkiksi espanjalainen kirjain ñ edustaa yhtä merkkiä, mutta vie 2 tavua.
TEKSTI(M) edustaa saraketta, jonka enimmäispituus on 65 535 merkkiä. Kuitenkin, kuten VARCHAR(M):n kohdalla tapahtuu, todellinen enimmäispituus pienenee, jos monitavuisia merkkejä tallennetaan. Jos M on määritetty, sarake luodaan pienimmäksi tyypiksi, joka voi tallentaa tällaisen määrän merkkejä.
KESKIPÄINEN TEKSTI(M) ja PITKÄ TEKSTI(M) ovat samanlaisia kuin TEKSTI(M), mutta suurimmat sallitut pituudet ovat 16 777 215 ja 4 294 967 295 merkkiä, vastaavasti.

Päivämäärä ja aika:

DATE tarkoittaa päivämäärää muodossa VVVV-KK-PP.
AIKA edustaa aikaa HH:MM:SS.sss-muodossa (tuntia, minuuttia, sekuntia ja millisekuntia).
DATETIME on yhdistelmä DATE ja TIME muodossa VVVV-KK-PP HH:MM:SS.
TIMESTAMP määrittää ajankohdan, jolloin rivi lisättiin tai päivitettiin.

Kun olet tarkistanut nämä tietotyypit, voit paremmin määrittää, mikä tietotyyppi sinun on määritettävä taulukon tiettyyn sarakkeeseen.

Esimerkiksi henkilön nimi mahtuu helposti VARCHAR(50)-kenttään, kun taas blogiteksti tarvitsee tyypin TEXT (valitse M erityistarpeidesi mukaan).

Taulukoiden luominen ensisijaisilla ja vierailla avaimilla

Ennen kuin sukeltaamme taulukoiden luomiseen, meidän on tarkasteltava kaksi relaatiotietokantojen peruskäsitettä: ensisijaiset ja vieraat-avaimet.

Ensisijainen avain sisältää arvon, joka yksilöi taulukon jokaisen rivin tai tietueen. Toisaalta vieraan avaimen avulla luodaan linkki kahden taulukon tietojen välille ja ohjataan tietoja, jotka voidaan tallentaa taulukkoon, jossa vierasavain sijaitsee. Sekä ensisijainen että vierasavaimet ovat yleensä INT:itä.

Esimerkkinä käytetään BookstoreDB-tietokantaa ja luodaan kaksi taulukkoa nimeltä AuthorsTBL ja BooksTBL seuraavasti. NOT NULL -rajoite osoittaa, että liitetty kenttä vaatii muun arvon kuin NULL.

Lisäksi AUTO_INCREMENT-komentoa käytetään lisäämään INT-ensiavainsarakkeiden arvoa yhdellä, kun uusi tietue lisätään taulukkoon.

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

Nyt voimme aloittaa tietueiden lisäämisen AuthorsTBL- ja BooksTBL-tiedostoihin.

Rivien valitseminen, lisääminen, päivittäminen ja poistaminen

Täytämme ensin AuthorsTBL-taulukon. Miksi? Koska meillä on oltava arvot AuthorID:lle, ennen kuin lisäämme tietueita BooksTBL:ään.

Suorita seuraava kysely MariaDB-kehotteeltasi:

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

Sen jälkeen valitsemme kaikki tietueet AuthorsTBL:stä. Muista, että tarvitsemme kunkin tietueen AuthorID-tunnuksen, jotta voimme luoda INSERT-kyselyn BooksTBLille.

Jos haluat hakea yhden tietueen kerrallaan, voit käyttää WHERE-lausetta osoittamaan ehdon, joka rivin on täytettävä, jotta se voidaan palauttaa. Esimerkiksi,

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

Vaihtoehtoisesti voit valita kaikki tietueet samanaikaisesti:

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

Luodaan nyt INSERT-kysely BooksTBLille käyttämällä vastaavaa AuthorID-tunnusta vastaamaan kunkin kirjan tekijää. Arvo 1 kohdassa BookIsAvailable osoittaa, että kirja on varastossa, 0 muussa tapauksessa:

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

Tässä vaiheessa teemme SELECT-valinnan nähdäksemme tietueet BooksTBL:ssä. Sitten PÄIVITETÄÄN Paulo Coelhon kappaleen "The Alchemist" hinta ja VALITSE kyseinen levy uudelleen.

Huomaa, että BookLastUpdated-kentässä näkyy nyt eri arvo. Kuten aiemmin selitimme, TIMESTAMP-kenttä näyttää arvon, jolloin tietue lisättiin tai sitä viimeksi muokattiin.

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

Vaikka emme tee sitä täällä, voit myös poistaa tietueen, jos sitä ei enää käytetä. Oletetaan esimerkiksi, että haluamme poistaa "The Alchemist" BooksTBL:stä.

Käytämme tähän DELETE-käskyä seuraavasti:

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

Kuten PÄIVITYS:n tapauksessa, on hyvä idea VALITSE ensin nähdäksesi tietueet, joihin saattaa vaikuttaa. POISTA.

Älä myöskään unohda lisätä WHERE-lauseketta ja ehtoa (BookID=6) valitaksesi poistettavan tietueen. Muussa tapauksessa vaarana on poistaa kaikki taulukon rivit!

Jos haluat ketjuttaa kaksi (tai useampaa) kenttää, voit käyttää CONCAT-käskyä. Oletetaan esimerkiksi, että haluamme palauttaa tulosjoukon, joka koostuu yhdestä kentästä, jossa on kirjan nimi ja tekijä muodossa "Alkemisti (Paulo Coelho)", ja toisesta sarakkeesta, jossa on hinta.

Tämä vaatii JOIN-merkinnän AuthorsTBL ja BooksTBL välillä molempien taulukoiden yhteisessä kentässä (AuthorID):

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

Kuten näemme, CONCAT antaa meille mahdollisuuden yhdistää useita pilkuilla erotettuja merkkijonolausekkeita. Huomaat myös, että valitsimme aliaksen Kuvaus edustamaan ketjutuksen tulosjoukkoa.

Yllä olevan kyselyn tulos näkyy alla olevassa kuvassa:

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)

Luo käyttäjä käyttääksesi BookstoreDB-tietokantaa

Rootin käyttäminen kaikkien DML-toimintojen suorittamiseen tietokannassa on huono idea. Tämän välttämiseksi voimme luoda uuden MariaDB-käyttäjätilin (nimemme sen kirjakaupan käyttäjäksi) ja määrittää kaikki tarvittavat käyttöoikeudet BookstoreDB:lle:

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)

Erillinen käyttäjä jokaiselle tietokannalle estää koko tietokannan vahingoittumisen, jos yksittäinen tili vaarantuu.

Ylimääräisiä MySQL-vinkkejä

Tyhjennä MariaDB-kehote kirjoittamalla seuraava komento ja painamalla Enter:

MariaDB [BookstoreDB]> \! clear

Voit tarkistaa tietyn taulukon kokoonpanon seuraavasti:

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

Esimerkiksi,

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)

Nopea tarkastus paljastaa, että BookIsAvailable-kenttä hyväksyy NULL-arvot. Koska emme halua sallia sitä, MUUTTAMME taulukkoa seuraavasti:

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

(Voit näyttää sarakkeet uudelleen – korostetun KYLLÄ-merkinnän yläpuolella olevassa kuvassa pitäisi nyt olla EI).

Lopuksi voit tarkastella kaikkia palvelimesi tietokantoja seuraavasti:

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)

Seuraava kuva näyttää yllä olevan komennon tuloksen, kun olet käyttänyt MariaDB-kehotetta kirjakaupan käyttäjänä (huomaa, että tämä tili ei voi "nähdä" muita tietokantoja kuin BookstoreDB ja information_schema (saatavilla kaikille käyttäjille):

Yhteenveto

Tässä artikkelissa olemme selittäneet, kuinka DML-toimintoja suoritetaan ja kuinka luodaan tietokanta, taulukoita ja omistettuja käyttäjiä MariaDB-tietokantaan. Lisäksi jaoimme muutamia vinkkejä, jotka voivat helpottaa elämääsi järjestelmän/tietokannan ylläpitäjänä.

MySQL-tietokannan hallintaosa – 1
MySQL-tietokannan hallintaosa – 2
MySQL:n suorituskyvyn viritys ja optimointi – Osa 3

Jos sinulla on kysyttävää tästä artikkelista, älä epäröi kertoa meille! Käytä alla olevaa kommenttilomaketta ottaaksesi meihin yhteyttä.