Uzziniet MySQL/MariaDB iesācējiem - 1. daļa
Šajā rakstā mēs parādīsim, kā izveidot datu bāzi (sauktu arī par shēmu), tabulas (ar datu tipiem), un paskaidrosim, kā veikt datu apstrādes valodas (DML) darbības ar datiem MySQL/MariaDB serverī.
Tiek pieņemts, ka iepriekš esat 1) instalējis nepieciešamās pakotnes savā Linux sistēmā un 2) veicis mysql_secure_installation, lai uzlabotu datu bāzes servera drošību. Ja nē, izpildiet tālāk sniegtos norādījumus, lai instalētu MySQL/MariaDB serveri.
- Linux sistēmās instalējiet jaunāko MySQL datu bāzi
- Linux sistēmās instalējiet jaunāko MariaDB datu bāzi
Īsuma labad šajā rakstā mēs atsaucamies tikai uz MariaDB, taču šeit izklāstītie jēdzieni un komandas attiecas arī uz MySQL.
Datu bāzu, tabulu un pilnvaroto lietotāju veidošana
Kā jūs zināt, datu bāzi var vienkārši definēt kā organizētu informācijas apkopojumu. Jo īpaši MariaDB ir relāciju datu bāzes pārvaldības sistēma (RDBMS), un tā izmanto struktūras vaicājumu valodu, lai veiktu darbības datubāzēs. Turklāt ņemiet vērā, ka MariaDB terminu datu bāze un shēma tiek aizstāta.
Lai saglabātu pastāvīgu informāciju datu bāzē, mēs izmantosim tabulas, kurās glabājas datu rindas. Bieži vien divas vai vairākas tabulas kaut kādā veidā ir saistītas viena ar otru. Tā ir daļa no organizācijas, kas raksturo relāciju datu bāzu izmantošanu.
Lai izveidotu jaunu datu bāzi ar nosaukumu BooksDB , ievadiet uzvedni MariaDB ar šādu komandu (jums tiks lūgts ievadīt paroli root MariaDB lietotājam):
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)]>
Kad datu bāze ir izveidota, mums tajā jāizveido vismaz divas tabulas. Bet vispirms izpētīsim datu veidu jēdzienu.
Iepazīstinām ar MariaDB datu tipiem
Kā mēs iepriekš paskaidrojām, tabulas ir datu bāzes objekti, kur mēs saglabāsim pastāvīgu informāciju. Katra tabula sastāv no diviem vai vairākiem laukiem (kurus sauc arī par kolonnām) ar noteiktu datu tipu (informācijas veidu), ko šāds lauks var saglabāt.
Visizplatītākie datu tipi MariaDB ir šādi (pilnu sarakstu varat skatīt oficiālajā MariaDB tiešsaistes dokumentācijā):
- BOOLEAN 0 uzskata par nepatiesu un visas citas vērtības par patiesām.
- TINYINT, ja to lieto kopā ar SIGNED, aptver diapazonu no -128 līdz 127, savukārt UNSIGNED diapazons ir no 0 līdz 255.
- SMALLINT, ja to lieto kopā ar SIGNED, attiecas uz diapazonu no -32768 līdz 32767. UNSIGNED diapazons ir no 0 līdz 65535.
- INT, ja to lieto kopā ar UNSIGNED, attiecas uz diapazonu no 0 līdz 4294967295 un pretējā gadījumā no -2147483648 līdz 2147483647.
Piezīme: TINYINT, SMALLINT un INT tiek pieņemts noklusējuma SIGNED.
DOUBLE (M, D), kur M ir kopējais ciparu skaits un D ir ciparu skaits aiz komata, apzīmē dubultprecīzu peldošā komata skaitli. Ja ir norādīts UNSIGNED, negatīvās vērtības nav atļautas.
- VARCHAR (M) apzīmē mainīga garuma virkni, kur M ir maksimālais atļautais kolonnas garums baitos (teorētiski 65 535). Vairumā gadījumu baitu skaits ir identisks rakstzīmju skaitam, izņemot dažas rakstzīmes, kas var aizņemt pat 3 baitus. Piemēram, spāņu burts ñ apzīmē vienu rakstzīmi, bet aizņem 2 baitus.
- TEXT (M) apzīmē kolonnu, kuras maksimālais garums ir 65 535 rakstzīmes. Tomēr, kā tas notiek ar VARCHAR (M), faktiskais maksimālais garums tiek samazināts, ja tiek saglabātas daudzbaitu rakstzīmes. Ja ir norādīts M, kolonna tiek izveidota kā mazākais tips, kas var uzglabāt šādu rakstzīmju skaitu.
- MEDIUMTEXT (M) un LONGTEXT (M) ir līdzīgas TEXT (M), tikai maksimālais atļautais garums ir attiecīgi 16 777 215 un 4 294 967 295 rakstzīmes.
- DATE norāda datumu GGGG-MM-DD formātā.
- TIME norāda laiku formātā HH: MM: SS.sss (stunda, minūtes, sekundes un milisekundes).
- DATETIME ir DATE un TIME kombinācija GGGG-MM-DD HH: MM: SS formātā.
- TIMESTAMP tiek izmantots, lai definētu rindas pievienošanas vai atjaunināšanas brīdi.
Pēc šo datu tipu pārskatīšanas jums būs labākas iespējas noteikt, kurš datu tips jums jāpiešķir konkrētai tabulas kolonnai.
Piemēram, personas vārds var viegli iekļauties VARCHAR (50), savukārt emuāra ziņai būs nepieciešams TEXT tips (izvēlieties M atbilstoši savām īpašajām vajadzībām).
Pirms ķeramies pie tabulu veidošanas, mums ir jāpārskata divi pamatjēdzieni par relāciju datu bāzēm: primārā un svešā atslēga.
Primārā atslēga satur vērtību, kas unikāli identificē katru tabulas rindu vai ierakstu. No otras puses, ārējā atslēga tiek izmantota, lai izveidotu saikni starp datiem divās tabulās un kontrolētu datus, kurus var saglabāt tabulā, kur atrodas ārējā atslēga. Gan primārā, gan svešā atslēga parasti ir INT.
Lai to ilustrētu, izmantosim BookstoreDB un izveidosim divas tabulas ar nosaukumu AuthorsTBL un BooksTBL šādi. Ierobežojums NOT NULL norāda, ka saistītajam laukam ir nepieciešama cita vērtība nekā NULL.
Arī AUTO_INCREMENT tiek izmantots, lai par vienu palielinātu INT primārās atslēgas kolonnu vērtību, kad tabulā tiek ievietots jauns ieraksts.
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]>
Tagad mēs varam turpināt iesākt ierakstu ievietošanu AuthorsTBL un BooksTBL .
Vispirms mēs aizpildīsim tabulu AuthorsTBL . Kāpēc? Tā kā pirms ierakstu ievietošanas BooksTBL mums ir jābūt AuthorID vērtībām.
Izpildiet šo vaicājumu no savas MariaDB uzvednes:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Pēc tam mēs atlasīsim visus ierakstus no AuthorsTBL. Atcerieties, ka mums būs nepieciešams katra ieraksta AuthorID, lai izveidotu vaicājumu INSERT BooksTBL.
Ja vēlaties izgūt vienu ierakstu vienlaikus, varat izmantot klauzulu WHERE, lai norādītu nosacījumu, kuram jāatbilst rindai, lai to atgrieztu. Piemēram,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Varat arī vienlaikus atlasīt visus ierakstus:
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]>
Tagad izveidosim vaicājumu INSERT BooksTBL, izmantojot atbilstošo AuthorID, lai tas atbilstu katras grāmatas autoram. BookIsAvailable vērtība 1 norāda, ka grāmata ir noliktavā, pretējā gadījumā 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
Šajā brīdī mēs veiksim SELECT, lai skatītu ierakstus BooksTBL. Tad atjaunināsim Paulo Koelju “Alķīmiķa” cenu un vēlreiz atlasīsim šo konkrēto ierakstu.
Ņemiet vērā, kā tagad laukā BookLastUpdated tiek parādīta cita vērtība. Kā mēs iepriekš paskaidrojām, lauks TIMESTAMP parāda vērtību, kad ieraksts tika ievietots vai pēdējo reizi modificēts.
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]>
Lai gan mēs to nedarīsim šeit, varat arī izdzēst ierakstu, ja tas vairs netiek izmantots. Piemēram, pieņemsim, ka mēs vēlamies izdzēst “Alķīmiķi” no BooksTBL.
Lai to izdarītu, mēs izmantosim DELETE paziņojumu šādi:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Tāpat kā UPDATE gadījumā, ieteicams apskatīt ierakstu (-us), kuru (-us) potenciāli var ietekmēt DELETE, ieteicams vispirms veikt SELECT.
Neaizmirstiet pievienot arī klauzulu WHERE un nosacījumu (BookID = 6), lai atlasītu konkrēto noņemamo ierakstu. Pretējā gadījumā jūs riskējat izdzēst visas tabulas rindas!
Ja vēlaties apvienot divus (vai vairāk) laukus, varat izmantot CONCAT paziņojumu. Piemēram, pieņemsim, ka mēs vēlamies atgriezt rezultātu kopu, kas sastāv no viena lauka ar grāmatas nosaukumu un autoru “Alķīmiķis (Paulo Koelju)” un no citas slejas ar cenu.
Tam būs nepieciešams JOIN starp AuthorsTBL un BooksTBL kopējā laukā, kuru koplieto abas tabulas (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Kā redzam, CONCAT ļauj mums pievienoties vairākiem virkņu izteicieniem, kas atdalīti ar komatiem. Jūs ievērosiet arī to, ka mēs izvēlējāmies aizstājvārdu Apraksts, lai attēlotu savienojuma rezultātu kopu.
Iepriekš minētā vaicājuma izvade ir parādīta zemāk esošajā attēlā:
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)
Saknes izmantošana visu DML darbību veikšanai datu bāzē ir slikta ideja. Lai no tā izvairītos, mēs varam izveidot jaunu MariaDB lietotāja kontu (mēs to nosauksim par grāmatnīcas lietotāju) un piešķirt visas nepieciešamās atļaujas 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)
Ja katrai datu bāzei ir īpašs, atsevišķs lietotājs, tiks novērsta visas datubāzes bojājumi, ja tiks apdraudēts viens konts.
Lai notīrītu uzvedni MariaDB, ierakstiet šādu komandu un nospiediet taustiņu Enter:
MariaDB [BookstoreDB]> \! clear
Lai pārbaudītu norādītās tabulas konfigurāciju, rīkojieties šādi:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Piemēram,
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)
Ātra pārbaude atklāj, ka lauks BookIsAvailable atzīst NULL vērtības. Tā kā mēs nevēlamies to atļaut, tabulu mainīsim šādi:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Jūtieties brīvi atkal parādīt kolonnas - iezīmētajam JĀ augšējā attēlā tagad jābūt NĒ).
Visbeidzot, lai apskatītu visas servera datu bāzes, rīkojieties šādi:
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)
Šajā attēlā parādīts iepriekš minētās komandas rezultāts pēc piekļuves MariaDB uzvednei kā grāmatnīcas lietotājam (ņemiet vērā, kā šis konts nevar “redzēt” citas datu bāzes, izņemot BookstoreDB un information_schema (pieejamas visiem lietotājiem):
Kopsavilkums
Šajā rakstā mēs esam paskaidrojuši, kā palaist DML darbības un kā MariaDB datu bāzē izveidot datu bāzi, tabulas un īpašus lietotājus. Turklāt mēs dalījāmies ar dažiem padomiem, kas var atvieglot jūsu kā sistēmas/datu bāzes administratora dzīvi.
- MySQL datu bāzes administrēšana - 1. daļa
- MySQL datu bāzes administrēšana - 2. daļa
- MySQL veiktspējas iestatīšana un optimizācija - 3. daļa
Ja jums ir kādi jautājumi par šo rakstu, nevilcinieties par to informēt! Lai sazinātos ar mums, izmantojiet zemāk esošo komentāru veidlapu.