Opi MySQL/MariaDB aloittelijoille - osa 1
Tässä artikkelissa näytetään, miten luodaan tietokanta (tunnetaan myös nimellä skeema), taulukoita (tietotyypeillä) ja kerrotaan, kuinka suoritetaan DML (Data Manipulation Language) -operaatiot MySQL/MariaDB-palvelimen tiedoilla.
Oletetaan, että olet aiemmin 1) asentanut tarvittavat paketit Linux-järjestelmääsi ja 2) suorittanut mysql_secure_installation tietokantapalvelimen turvallisuuden parantamiseksi. Jos ei, asenna MySQL/MariaDB-palvelin alla olevien ohjeiden mukaan.
- Asenna uusin MySQL-tietokanta Linux-järjestelmiin
- Asenna uusin MariaDB-tietokanta Linux-järjestelmiin
Lyhyesti sanottuna viittaamme MariaDB: hen yksinomaan tässä artikkelissa, mutta tässä esitetyt käsitteet ja komennot koskevat myös MySQL: ää.
Tietokantojen, taulukoiden ja valtuutettujen käyttäjien luominen
Kuten tiedät, tietokanta voidaan määritellä yksinkertaisesti järjestetyksi tietokokoelmaksi. Erityisesti MariaDB on relaatiotietokantojen hallintajärjestelmä (RDBMS), joka käyttää Structure Query -kieltä suorittamaan toimintoja tietokannoissa. Muista lisäksi, että MariaDB käyttää termejä tietokanta ja skeema keskenään.
Pysyvien tietojen tallentamiseksi tietokantaan käytämme taulukoita, jotka tallentavat tietorivejä. Usein vähintään kaksi taulukkoa liittyy toisiinsa jollakin tavalla. Se on osa organisaatiota, joka luonnehtii relaatiotietokantojen käyttöä.
Jos haluat luoda uuden tietokannan nimeltä BooksDB , kirjoita MariaDB-kehote seuraavalla komennolla (sinua pyydetään antamaan MariaDB-pääkäyttäjän salasana):
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 pidämme pysyvää tietoa. Jokainen taulukko koostuu kahdesta tai useammasta kentästä (tunnetaan myös nimellä sarakkeet) tietystä tietotyypistä (tietotyypistä), jonka kyseinen kenttä voi tallentaa.
MariaDB: n yleisimmät tietotyypit ovat seuraavat (voit tutustua täydelliseen luetteloon virallisessa MariaDB: n online-dokumentaatiossa):
- BOOLEAN pitää 0 vääränä ja muita arvoja tosi.
- TINYINT, jos sitä käytetään SIGNEDin kanssa, kattaa alueen välillä -128-127, kun taas UNSIGNED-alue on 0-255.
- SMALLINT, jos sitä käytetään SIGNEDin kanssa, kattaa alueen välillä -32768 - 32767. UNSIGNED-alue on 0-65535.
- INT, jos sitä käytetään UNSIGNEDin kanssa, kattaa alueen 0 - 4294967295 ja muuten -2147483648 - 2147483647.
Huomaa: TINYINT, SMALLINT ja INT oletetaan oletusarvoiseksi SIGNED.
DOUBLE (M, D), jossa M on numeroiden kokonaismäärä ja D on desimaalipilkun jälkeinen numeroiden määrä, edustaa kaksitarkkaa liukulukulukua. Jos UNSIGNED on määritetty, negatiivisia arvoja ei sallita.
- VARCHAR (M) edustaa vaihtelevan pituista merkkijonoa, jossa M on suurin sallittu sarakkeen pituus tavuina (teoriassa 65 535). Useimmissa tapauksissa tavujen lukumäärä on sama kuin merkkien lukumäärä, lukuun ottamatta joitain merkkejä, jotka voivat viedä jopa 3 tavua. Esimerkiksi espanjalainen kirjain ñ edustaa yhtä merkkiä, mutta vie 2 tavua.
- TEXT (M) edustaa saraketta, jonka enimmäispituus on 65 535 merkkiä. Kuten VARCHAR (M): n kohdalla tapahtuu, todellista enimmäispituutta pienennetään, jos monitavuisia merkkejä on tallennettu. Jos M on määritetty, sarake luodaan pienimmäksi tyypiksi, joka voi tallentaa tällaisen määrän merkkejä.
- MEDIUMTEXT (M) ja LONGTEXT (M) ovat samanlaisia kuin TEXT (M), vain suurin sallittu pituus on vastaavasti 16 777 215 ja 4 294 967 295 merkkiä.
- DATE edustaa päivämäärää VVVV-KK-PP-muodossa.
- TIME edustaa aikaa HH: MM: SS.sss-muodossa (tunti, minuutti, sekunti ja millisekunti).
- DATETIME on päivämäärän ja kellonajan yhdistelmä VVVV-KK-PP PP: MM: SS-muodossa.
- TIMESTAMP käytetään määrittämään hetki, jolloin rivi lisättiin tai päivitettiin.
Tarkastettuasi nämä tietotyypit voit paremmin määrittää, mikä tietotyyppi sinun on määritettävä tietylle taulukon sarakkeelle.
Esimerkiksi henkilön nimi mahtuu helposti VARCHARiin (50), kun taas blogikirjoitus tarvitsee TEXT-tyypin (valitse M omien tarpeidesi mukaan).
Ennen kuin sukellamme taulukoiden luomiseen, relaatiotietokannoista on kaksi peruskäsitettä, jotka meidän on tarkistettava: ensisijainen ja vieras avain.
Ensisijainen avain sisältää arvon, joka yksilöi taulukon jokaisen rivin tai tietueen. Toisaalta ulkomaista avainta käytetään luomaan linkki kahden taulukon tietojen välille ja hallitsemaan tietoja, jotka voidaan tallentaa taulukkoon, johon vieras avain sijaitsee. Sekä ensisijaiset että ulkomaiset avaimet ovat yleensä INT-koodeja.
Käytetään havainnollistamiseksi BookstoreDB -sovellusta ja luodaan kaksi taulukkoa nimeltä AuthorsTBL ja BooksTBL seuraavasti. EI NULL -raja tarkoittaa, että liitetty kenttä vaatii muun arvon kuin NULL.
Myös AUTO_INCREMENT-arvoa käytetään lisäämään INT-avaimen sarakkeiden arvo 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 jatkaa ja lisätä tietueita AuthorsTBL - ja BooksTBL -tietoihin.
Ensin täytetään taulukko AuthorsTBL . Miksi? Koska meillä on oltava arvot koodille AuthorID ennen tietueiden lisäämistä BooksTBL: ään.
Suorita seuraava kysely MariaDB-kehotteestasi:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Sen jälkeen valitsemme kaikki tietueet AuthorsTBL: ltä. Muista, että tarvitsemme jokaisen tietueen AuthorID: n, jotta voimme luoda INSERT-kyselyn BooksTBL: lle.
Jos haluat hakea yhden tietueen kerrallaan, voit käyttää WHERE-lauseketta ehdon ilmoittamiseksi, jonka rivin on täytettävä palautettavaksi. 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 BooksTBL: lle käyttämällä vastaavaa AuthorID-tunnusta vastaamaan jokaisen kirjan kirjoittajaa. BookIsAvailable-arvon arvo 1 tarkoittaa, että kirjaa on varastossa, muuten 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
Tässä vaiheessa teemme SELECT nähdäksesi tietueet BooksTBL: ssä. Päivitä sitten Paulo Coelhon The Alchemist -hinta ja VALITSE kyseinen ennätys uudelleen.
Huomaa, kuinka BookLastUpdated-kenttä näyttää nyt toisen arvon. Kuten aiemmin selitimme, TIMESTAMP-kenttä näyttää arvon, kun tietue lisättiin tai 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, ellei sitä enää käytetä. Oletetaan esimerkiksi, että haluamme poistaa "Alkemisti" BooksTBL: stä.
Tätä varten käytämme DELETE-käskyä seuraavasti:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Kuten UPDATE: n tapauksessa, kannattaa ensin tehdä SELECT, jotta voidaan tarkastella tietueita, joihin DELETE saattaa vaikuttaa.
Älä myöskään unohda lisätä WHERE-lauseketta ja ehtoa (BookID = 6) valitaksesi poistettava tietue. Muuten olet vaarassa poistaa kaikki taulukon rivit!
Jos haluat liittää 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ä "Alkemistin (Paulo Coelho)" muodossa, ja toisesta sarakkeesta, jossa on hinta.
Tämä vaatii JOINin AuthorsTBL: n ja BooksTBL: n välillä molempien taulukkojen yhteisellä kentällä (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 liittää useita merkkilausekkeita pilkuilla erotettuna. Huomaa 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)
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 (nimeämme sen kirjakaupan käyttäjälle) ja antaa kaikki tarvittavat oikeudet BookstoreDB: lle:
MariaDB [BookstoreDB]> CREATE USER [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)
Oma, erillinen käyttäjä jokaiselle tietokannalle estää koko tietokannan vahingoittumisen, jos yksi tili vaarantuu.
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ä, muutamme taulukkoa seuraavasti:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Voit näyttää sarakkeet uudelleen - korostetun KYLLÄ-arvon yllä olevassa kuvassa pitäisi nyt olla EI).
Lopuksi voit tarkastella kaikkia palvelimen tietokantoja seuraavasti:
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)
Seuraava kuva näyttää yllä olevan komennon tuloksen, kun olet käyttänyt MariaDB-kehotetta kirjakauppakäyttäjänä (huomaa, kuinka tämä tili ei voi "nähdä" muita tietokantoja kuin BookstoreDB ja information_schema (saatavana kaikille käyttäjille):
Yhteenveto
Tässä artikkelissa olemme selittäneet, kuinka DML-toiminnot suoritetaan ja kuinka luoda tietokanta, taulukot ja omistautuneet käyttäjät MariaDB-tietokantaan. Jaoimme lisäksi muutamia vinkkejä, jotka voivat helpottaa elämääsi järjestelmän/tietokannan järjestelmänvalvojana.
- MySQL-tietokannan hallinnan osa - 1
- MySQL-tietokannan hallinnan osa - 2
- MySQL-suorituskyvyn hallinta ja optimointi - osa 3
Jos sinulla on kysyttävää tästä artikkelista, älä epäröi ilmoittaa siitä meille! Voit vapaasti käyttää alla olevaa kommenttilomaketta päästäksesi meihin.