Léiert MySQL/MariaDB fir Ufänger - Deel 1
An dësem Artikel wäerte mir weisen wéi Dir eng Datebank erstellt (och bekannt als Schema), Dëscher (mat Datentypen), an erkläre wéi Dir Data Manipulation Language (DML) Operatioune mat Daten op engem MySQL/MariaDB Server ausféiert.
Et gëtt ugeholl datt Dir virdru 1) déi néideg Packagen op Ärem Linux System installéiert hutt, an 2) mysql_secure_installation ausgefouert hutt fir d'Sécherheet vum Datebankserver ze verbesseren. Wann net, befollegt ënnert Guiden fir MySQL/MariaDB Server z'installéieren.
- Installéiert déi lescht MySQL Datebank op Linux Systemer
- Installéiert déi lescht MariaDB Datebank a Linux Systemer
Fir Kuerzegkeet bezéie mir op MariaDB exklusiv an dësem Artikel, awer d'Konzepter a Kommandoen, déi hei beschriwwe ginn, gëllen och fir MySQL.
Erstelle Datenbanken, Dëscher an autoriséiert Benotzer
Wéi Dir wësst, kann eng Datebank an einfache Begrëffer als eng organiséiert Sammlung vun Informatioun definéiert ginn. Besonnesch MariaDB ass e relationalen Datebankmanagement System (RDBMS) a benotzt d'Struktur Query Language fir Operatiounen op Datenbanken auszeféieren. Zousätzlech, bedenkt datt MariaDB d'Begrëffer Datebank a Schema austauschbar benotzt.
Fir persistent Informatioun an enger Datebank ze späicheren, benotze mir Dëscher déi Zeilen vun Daten späicheren. Dacks ginn zwee oder méi Dëscher op iergendeng Manéier matenee verbonnen. Dat ass en Deel vun der Organisatioun déi d'Benotzung vu relationalen Datenbanken charakteriséiert.
Fir eng nei Datebank mam Numm BooksDB ze kreéieren, gitt d'MariaDB-Prompt mat dem folgenden Kommando (Dir gitt opgefuerdert d'Passwuert fir de root MariaDB Benotzer anzeginn):
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)]>
Wann d'Datebank erstallt ass, musse mir op d'mannst zwee Dëscher drop erstellen. Awer loosst eis als éischt d'Konzept vun Datentypen entdecken.
Aféierung vun MariaDB Datentypen
Wéi mir virdru erkläert hunn, sinn Dëscher Datebankobjekter wou mir persistent Informatioun behalen. All Dësch besteet aus zwee oder méi Felder (och bekannt als Spalten) vun engem bestëmmten Datetyp (den Typ vun Informatioun) déi esou Feld ka späicheren.
Déi meescht üblech Datentypen an MariaDB sinn déi folgend (Dir kënnt déi komplett Lëscht an der offizieller MariaDB Online Dokumentatioun konsultéieren):
- BOOLEAN betruecht 0 als falsch an all aner Wäerter als wouer.
- TINYINT, wa se mat SIGNED benotzt gëtt, deckt d'Bereich vun -128 bis 127, wärend den UNSIGNED Gamme 0 bis 255 ass.
- SMALLINT, wa se mat SIGNED benotzt gëtt, deckt d'Gamme vun -32768 bis 32767. D'UNSIGNED Gamme ass 0 bis 65535.
- INT, wa se mat UNSIGNED benotzt gëtt, deckt d'Gamme vun 0 bis 4294967295, an -2147483648 bis 2147483647 anescht.
Bemierkung: An TINYINT, SMALLINT an INT gëtt de Standard SIGNED ugeholl.
DOUBLE(M, D), wou M d'Gesamtzuel vun den Zifferen ass an D d'Zuel vun den Zifferen nom Dezimalpunkt ass, stellt eng duebelpräzis Schwemmpunktzuel duer. Wann UNSIGNED uginn ass, sinn negativ Wäerter net erlaabt.
- VARCHAR(M) stellt eng String mat variabelen Längt duer, wou M déi maximal zulässlech Spaltlängt a Bytes ass (65.535 an der Theorie). An deene meeschte Fäll ass d'Zuel vun de Bytes identesch mat der Unzuel vun den Zeechen, ausser e puer Zeechen, déi bis zu 3 Bytes ophuelen. Zum Beispill, de spuenesche Buschtaf ñ stellt ee Charakter duer, awer hëlt 2 Bytes op.
- TEXT(M) representéiert eng Kolonn mat enger maximaler Längt vu 65.535 Zeechen. Wéi och ëmmer, wéi et geschitt mat VARCHAR (M), gëtt déi aktuell maximal Längt reduzéiert wann Multi-Byte Charaktere gespäichert ginn. Wann M spezifizéiert ass, gëtt d'Kolonn als de klengste Typ erstallt, deen esou Unzuel vun Zeechen späichere kann.
- MEDIUMTEXT(M) a LONGTEXT(M) sinn ähnlech wéi TEXT(M), nëmmen datt déi maximal erlaabt Längt 16.777.215 respektiv 4.294.967.295 Zeechen sinn.
- DATUM representéiert den Datum am Format JJJJ-MM-DD.
- ZEIT representéiert d'Zäit am HH:MM:SS.sss-Format (Stonn, Minutten, Sekonnen a Millisekonnen).
- DATETIME ass d'Kombinatioun vun DATE an TIME am Format JJJJ-MM-DD HH:MM:SS.
- TIMESTAMP gëtt benotzt fir de Moment ze definéieren wou eng Zeil bäigefüügt oder aktualiséiert gouf.
Nodeems Dir dës Datentypen iwwerpréift hutt, sidd Dir an enger besserer Positioun fir ze bestëmmen wéi eng Dateart Dir un eng bestëmmte Kolonn an enger Tabell zouzeweisen musst.
Zum Beispill kann den Numm vun enger Persoun einfach an e VARCHAR (50) passen, wärend e Blog Post en TEXT-Typ brauch (wielt M no Äre spezifesche Besoinen).
Ier mir an d'Schafe vun Dëscher tauchen, ginn et zwee fundamental Konzepter iwwer relational Datenbanken, déi mir iwwerpréiwen: primär an auslännesch Schlësselen.
E primäre Schlëssel enthält e Wäert deen all Zeil, oder Rekord, an der Tabell eenzegaarteg identifizéiert. Op der anerer Säit gëtt en auslännesche Schlëssel benotzt fir e Link tëscht den Donnéeën an zwou Tabellen ze kreéieren an d'Donnéeën ze kontrolléieren déi an der Tabell gespäichert kënne ginn wou den auslännesche Schlëssel läit. Béid primär an auslännesch Schlësselen sinn allgemeng INTs.
Fir ze illustréieren, loosst eis de BookstoreDB benotzen an zwou Tabellen erstellen mam Numm AuthorsTBL an BooksTBL wéi follegt. D'NET NULL Constraint weist datt dat assoziéiert Feld e Wäert anescht wéi NULL erfuerdert.
Och AUTO_INCREMENT gëtt benotzt fir de Wäert vun den INT Primärschlësselkolonnen ëm een ze erhéijen wann en neie Rekord an den Dësch agebaut gëtt.
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]>
Elo kënne mir virugoen an ufänken records an AuthorsTBL an BooksTBL anzeginn.
Mir populéieren als éischt d'Tabelle AuthorsTBL. Firwat? Well mir musse Wäerter fir AutorID hunn, ier mir records an de BooksTBL setzen.
Fëllt déi folgend Ufro aus Ärem MariaDB Prompt aus:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Duerno wäerte mir all records vun AuthorsTBL auswielen. Denkt drun datt mir d'AutorID fir all Rekord brauchen fir d'INSERT Ufro fir BooksTBL ze kreéieren.
Wann Dir gläichzäiteg ee Rekord wëllt recuperéieren, kënnt Dir eng WHERE Klausel benotzen fir eng Bedingung unzeweisen datt eng Rei muss erfëllen fir zréckzekommen. Zum Beispill,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Alternativ kënnt Dir all Rekorder gläichzäiteg auswielen:
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]>
Loosst eis elo d'INSERT Ufro fir BooksTBL erstellen, mat der entspriechender AutorID fir den Auteur vun all Buch ze passen. E Wäert vun 1 am BookIsAvailable weist datt d'Buch op Lager ass, soss 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
Zu dësem Zäitpunkt wäerte mir e SELECT maachen fir d'Records an BooksTBL ze gesinn. Da loosst eis de Präis vum The Alchemist vum Paulo Coelho UPDATEN an dee spezifesche Rekord erëm auswielen.
Notéiert wéi de BookLastUpdated Feld elo en anere Wäert weist. Wéi mir virdru erkläert hunn, weist en TIMESTAMP Feld de Wäert wann de Rekord agefouert gouf oder lescht geännert gouf.
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]>
Och wa mir et net hei maachen, kënnt Dir och e Rekord läschen wann et net méi benotzt gëtt. Zum Beispill, ugeholl mir wëllen \The Alchemist\ aus BooksTBL läschen.
Fir dat ze maachen, benotze mir d'DELETE Ausso wéi follegt:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Wéi am Fall vun UPDATE, ass et eng gutt Iddi fir d'éischt e SELECT ze maachen fir d'Rekord(en) ze gesinn, déi potenziell vun der DELETE beaflosst kënne ginn.
Vergiesst och net d'WHERE Klausel an eng Bedingung (BookID = 6) derbäi ze setzen fir de spezifesche Rekord ze wielen fir ze läschen. Soss riskéiert Dir all d'Zeilen an der Tabell ze läschen!
Wann Dir wëllt zwee (oder méi) Felder zesummenzéien, kënnt Dir d'CONCAT Ausso benotzen. Zum Beispill, loosst eis soen, mir wëllen e Resultat-Set zréckginn, deen aus engem Feld mat dem Buchnumm an dem Auteur an der Form vum The Alchemist (Paulo Coelho) an enger anerer Kolonn mam Präis besteet.
Dëst erfuerdert e JOIN tëscht AuteurenTBL a BooksTBL um gemeinsame Feld gedeelt vu béiden Dëscher (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Wéi mir kënne gesinn, erlaabt CONCAT eis fir verschidde Stringausdréck matzemaachen, getrennt vu Kommaen. Dir wäert och bemierken datt mir den Alias Beschreiwung gewielt hunn fir de Resultatset vun der Konkatenatioun ze representéieren.
D'Ausgab vun der uewe genannter Ufro gëtt am Bild hei ënnen gewisen:
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)
Root ze benotzen fir all DML Operatiounen an enger Datebank auszeféieren ass eng schlecht Iddi. Fir dëst ze vermeiden, kënne mir en neie MariaDB Benotzerkont erstellen (mir nennen et Bookstoreuser) an all néideg Permissiounen fir BookstoreDB zouginn:
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)
En dedizéierten, getrennten Benotzer fir all Datebank ze hunn wäert Schied un der ganzer Datebank verhënneren, wann en eenzege Kont kompromittéiert gëtt.
Fir d'MariaDB-Prompt ze läschen, gitt de folgende Kommando an dréckt Enter:
MariaDB [BookstoreDB]> \! clear
Fir d'Konfiguratioun vun enger bestëmmter Tabell z'iwwerpréiwen, maacht:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Zum Beispill,
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)
Eng séier Inspektioun weist datt de BookIsAvailable Feld NULL Wäerter zouginn. Well mir dat net wëllen erlaben, verännere mir den Dësch wéi follegt:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Fir d'Saile nach eng Kéier ze weisen - de markéierten JO am Bild hei uewen soll elo e NEE sinn).
Schlussendlech, fir all Datenbanken op Ärem Server ze gesinn, maacht:
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)
Déi folgend Bild weist d'Resultat vum uewe genannte Kommando nom Zougang zu der MariaDB Prompt als de Librairie Benotzer (notéiert wéi dëse Kont keng Datenbanken ausser BookstoreDB an Informatiounsschema kann \gesinn (fir all Benotzer verfügbar):
Resumé
An dësem Artikel hu mir erkläert wéi Dir DML Operatiounen ausféiert a wéi Dir eng Datebank, Dëscher an engagéierten Benotzer op enger MariaDB Datebank erstellt. Zousätzlech hu mir e puer Tipps gedeelt, déi Äert Liewen als System/Datebank Administrateur méi einfach maachen.
- MySQL Datebankverwaltung Deel – 1
- MySQL Datebank Administratioun Deel - 2
- MySQL Performance Tunning an Optimiséierung - Deel 3
Wann Dir Froen iwwer dësen Artikel hutt, zéckt net eis ze wëssen! Fillt gratis de Kommentarformular hei ënnen ze benotzen fir eis z'erreechen.