Naučite se MySQL/MariaDB za začetnike - 1. del
V tem članku bomo prikazali, kako ustvariti bazo podatkov (znano tudi kot shema), tabele (s podatkovnimi vrstami) in razložiti, kako izvajati operacije upravljanja podatkovnega jezika (DML) s podatki na strežniku MySQL/MariaDB.
Predpostavlja se, da ste predhodno 1) v sistem Linux namestili potrebne pakete in 2) izvedli mysql_secure_installation za izboljšanje varnosti strežnika baz podatkov. V nasprotnem primeru sledite spodnjim navodilom za namestitev strežnika MySQL/MariaDB.
- Namestite najnovejšo bazo podatkov MySQL v sisteme Linux
- Namestite najnovejšo zbirko podatkov MariaDB v sisteme Linux
Za kratkost se bomo v celotnem članku sklicevali izključno na MariaDB, toda tukaj opisani koncepti in ukazi veljajo tudi za MySQL.
Ustvarjanje baz podatkov, tabel in pooblaščenih uporabnikov
Kot veste, lahko zbirko podatkov preprosto definiramo kot organizirano zbiranje informacij. Zlasti je MariaDB sistem za upravljanje relacijskih baz podatkov (RDBMS) in uporablja strukturni poizvedbeni jezik za izvajanje operacij v bazah podatkov. Poleg tega ne pozabite, da MariaDB uporablja bazo izrazov in shemo zamenljivo.
Za shranjevanje trajnih informacij v zbirki podatkov bomo uporabili tabele, ki shranjujejo vrstice podatkov. Pogosto sta dve ali več tabel na nek način povezani. To je del organizacije, ki označuje uporabo relacijskih baz podatkov.
Če želite ustvariti novo bazo podatkov z imenom BooksDB , vnesite poziv MariaDB z naslednjim ukazom (pozvani boste, da vnesete geslo za korenskega uporabnika MariaDB):
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)]>
Ko je baza podatkov ustvarjena, moramo v njej ustvariti vsaj dve tabeli. Najprej pa raziščimo koncept podatkovnih vrst.
Predstavljamo tipe podatkov MariaDB
Kot smo že pojasnili, so tabele objekti baze podatkov, kjer bomo hranili trajne podatke. Vsaka tabela je sestavljena iz dveh ali več polj (znanih tudi kot stolpci) danega podatkovnega tipa (vrste informacij), ki ga lahko shrani takšno polje.
Najpogostejši tipi podatkov v MariaDB so naslednji (celoten seznam si lahko ogledate v uradni spletni dokumentaciji MariaDB):
- BOOLEAN šteje 0 kot false in vse druge vrednosti kot resnične.
- TINYINT, če se uporablja skupaj s SIGNED, zajema območje od -128 do 127, medtem ko je območje UNSIGNED od 0 do 255.
- SMALLINT, če se uporablja skupaj s SIGNED, zajema območje od -32768 do 32767. UNSIGNED obseg je od 0 do 65535.
- INT, če se uporablja skupaj z UNSIGNED, zajema območje od 0 do 4294967295, v nasprotnem primeru pa od -2147483648 do 2147483647.
Opomba: V TINYINT, SMALLINT in INT je predpostavljena privzeta SIGNED.
DOUBLE (M, D), kjer je M skupno število števk in D število števk za decimalno vejico, predstavlja število s plavajočo vejico z dvojno natančnostjo. Če je podano UNSIGNED, negativne vrednosti niso dovoljene.
- VARCHAR (M) predstavlja niz spremenljive dolžine, pri čemer je M največja dovoljena dolžina stolpca v bajtih (v teoriji 65.535). V večini primerov je število bajtov enako številu znakov, razen nekaterih znakov, ki lahko zavzamejo tudi do 3 bajte. Na primer, španska črka - predstavlja en znak, vendar zavzame 2 bajta.
- BESEDILO (M) predstavlja stolpec z največjo dolžino 65.535 znakov. Vendar pa se, tako kot pri VARCHAR (M), dejanska največja dolžina zmanjša, če so shranjeni večbajtni znaki. Če je podano M, je stolpec ustvarjen kot najmanjši tip, ki lahko shrani takšno število znakov.
- MEDIUMTEXT (M) in LONGTEXT (M) sta podobni TEXT (M), le da sta največji dovoljeni dolžini 16.777.215 oziroma 4.294.967.295 znakov.
- DATUM predstavlja datum v obliki LLLL-MM-DD.
- TIME predstavlja čas v obliki HH: MM: SS.sss (ura, minute, sekunde in milisekunde).
- DATETIME je kombinacija DATUMA IN ČASA v LLLL-MM-DD HH: MM: SS.
- TIMESTAMP se uporablja za določitev trenutka dodajanja ali posodobitve vrstice.
Po pregledu teh podatkovnih vrst boste v boljšem položaju, da določite, kateri podatkovni tip morate dodeliti določenemu stolpcu v tabeli.
Na primer, ime osebe se lahko zlahka prilega VARCHAR (50), medtem ko bo objava v spletnem dnevniku potrebovala BESEDILO (izberite M glede na vaše posebne potrebe).
Preden se poglobimo v ustvarjanje tabel, obstajata dva temeljna koncepta o relacijskih zbirkah podatkov, ki ju moramo pregledati: primarni in tuji ključ.
Primarni ključ vsebuje vrednost, ki enolično identificira vsako vrstico ali zapis v tabeli. Po drugi strani se tuji ključ uporablja za ustvarjanje povezave med podatki v dveh tabelah in za nadzor podatkov, ki jih je mogoče shraniti v tabelo, v kateri je tuji ključ. Tako primarni kot tuji ključ sta na splošno INT.
Za ponazoritev uporabimo BookstoreDB in ustvarimo dve tabeli z imenom AuthorsTBL in BooksTBL , kot sledi. Omejitev NOT NULL pomeni, da povezano polje zahteva vrednost, ki ni NULL.
Tudi AUTO_INCREMENT se uporablja za povečanje za eno vrednost stolpcev primarnega ključa INT, ko je v tabelo vstavljen nov zapis.
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]>
Zdaj lahko nadaljujemo in začnemo vstavljati zapise v AuthorsTBL in BooksTBL .
Najprej bomo izpolnili tabelo AuthorsTBL . Zakaj? Ker moramo imeti vrednosti za AuthorID , preden vstavimo zapise v BooksTBL.
Iz poziva MariaDB izvedite naslednjo poizvedbo:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Po tem bomo izbrali vse zapise iz AuthorsTBL. Ne pozabite, da bomo za vsak zapis potrebovali AuthorID, da bomo ustvarili poizvedbo INSERT za BooksTBL.
Če želite naenkrat pridobiti en zapis, lahko s stavkom WHERE označite pogoj, ki ga mora vrniti vrstica. Na primer,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Lahko pa hkrati izberete vse zapise:
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]>
Zdaj ustvarimo poizvedbo INSERT za BooksTBL z uporabo ustreznega ID-ja avtorja, da se ujema z avtorjem vsake knjige. Vrednost 1 v BookIsAvailable pomeni, da je knjiga na zalogi, sicer 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
Na tej točki bomo naredili SELECT za ogled zapisov v BooksTBL. Nato nadgradimo ceno\"Alkimista" Paula Coelha in še enkrat IZBERIMO ta poseben zapis.
Upoštevajte, kako polje BookLastUpdated zdaj prikazuje drugačno vrednost. Kot smo že pojasnili, polje TIMESTAMP prikazuje vrednost, ko je bil zapis vstavljen ali nazadnje spremenjen.
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]>
Čeprav tega ne bomo storili tukaj, lahko zapis tudi izbrišete, če ga ne uporabljate več. Denimo, da želimo iz Knjige TBL izbrisati\"Alkimista".
Za to bomo uporabili izjavo DELETE, kot sledi:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Kot v primeru UPDATE, je dobro, da najprej naredite SELECT, da si ogledate zapise, na katere lahko DELETE vpliva.
Ne pozabite dodati tudi stavka WHERE in pogoja (BookID = 6), da izberete določen zapis, ki ga želite odstraniti. V nasprotnem primeru tvegate izbrisati vse vrstice v tabeli!
Če želite združiti dve (ali več) polj, lahko uporabite stavek CONCAT. Recimo na primer, da želimo vrniti nabor rezultatov, ki je sestavljen iz enega polja z imenom knjige in avtorja v obliki\"Alkimist (Paulo Coelho)" in drugega stolpca s ceno.
To bo zahtevalo PRIDRUŽITEV med AuthorsTBL in BooksTBL na skupnem polju, ki ga delita obe tabeli (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Kot lahko vidimo, nam CONCAT omogoča združevanje več nizovnih izrazov, ločenih z vejicami. Opazili boste tudi, da smo za vzorec združevanja izbrali vzdevek Opis.
Rezultat zgornje poizvedbe je prikazan na spodnji sliki:
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)
Uporaba root za izvajanje vseh operacij DML v zbirki podatkov je slaba ideja. Da bi se temu izognili, lahko ustvarimo nov uporabniški račun MariaDB (poimenovali ga bomo bookstoreuser) in dodelimo vsa potrebna dovoljenja za BookstoreDB:
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)
Če imate namenskega, ločenega uporabnika za vsako bazo podatkov, boste preprečili škodo celotni zbirki podatkov, če bo en račun ogrožen.
Če želite počistiti poziv MariaDB, vnesite naslednji ukaz in pritisnite Enter:
MariaDB [BookstoreDB]> \! clear
Če želite preveriti konfiguracijo dane tabele, naredite:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Na primer,
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)
Hiter pregled razkrije, da polje BookIsAvailable dopušča NULL vrednosti. Ker tega ne želimo dovoliti, bomo tabelo spremenili na naslednji način:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Ne pozabite znova prikazati stolpcev - označeno DA na zgornji sliki bi zdaj moralo biti NE).
Na koncu, da si ogledate vse zbirke podatkov na strežniku, naredite:
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)
Naslednja slika prikazuje rezultat zgornjega ukaza po dostopu do poziva MariaDB kot uporabnika knjigarne (upoštevajte, kako ta račun ne more\"videti" nobene druge zbirke podatkov razen BookstoreDB in information_schema (na voljo vsem uporabnikom):
Povzetek
V tem članku smo razložili, kako zagnati operacije DML in kako ustvariti bazo podatkov, tabele in namenske uporabnike v zbirki podatkov MariaDB. Poleg tega smo delili nekaj nasvetov, ki vam bodo morda olajšali življenje kot skrbnik sistema/baze podatkov.
- MySQL Database Administration Part - 1
- MySQL Database Administration Part - 2
- Nastavitev in optimizacija zmogljivosti MySQL - 3. del
Če imate kakršna koli vprašanja o tem članku, nam to sporočite! Za stik z nami uporabite spodnji obrazec za komentar.