Apreneu MySQL/MariaDB per a principiants - Part 1
En aquest article mostrarem com crear una base de dades (també coneguda com a esquema), taules (amb tipus de dades) i explicarem com realitzar operacions de llenguatge de manipulació de dades (DML) amb dades en un servidor MySQL/MariaDB.
Se suposa que prèviament heu 1) instal·lat els paquets necessaris al vostre sistema Linux i 2) heu executat mysql_secure_installation per millorar la seguretat del servidor de bases de dades. Si no, seguiu les guies següents per instal·lar el servidor MySQL/MariaDB.
- Instal·leu la darrera base de dades MySQL als sistemes Linux
- Instal·leu la darrera base de dades MariaDB als sistemes Linux
Per a la brevetat, ens referirem a MariaDB exclusivament al llarg d'aquest article, però els conceptes i ordres descrits aquí també s'apliquen a MySQL.
Creació de bases de dades, taules i usuaris autoritzats
Com sabeu, una base de dades es pot definir en termes senzills com una col·lecció organitzada d'informació. En particular, MariaDB és un sistema de gestió de bases de dades relacionals (RDBMS) i utilitza el llenguatge de consulta d'estructura per realitzar operacions sobre bases de dades. A més, tingueu en compte que MariaDB utilitza els termes base de dades i esquema de manera intercanviable.
Per emmagatzemar informació persistent en una base de dades, utilitzarem taules que emmagatzemen files de dades. Sovint, dues o més taules estaran relacionades entre si d'alguna manera. Això forma part de l'organització que caracteritza l'ús de bases de dades relacionals.
Per crear una nova base de dades anomenada BooksDB, introduïu l'indicador MariaDB amb l'ordre següent (se us demanarà que introduïu la contrasenya per a l'usuari root de 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)]>
Un cop creada la base de dades, hem de crear-hi almenys dues taules. Però primer anem a explorar el concepte de tipus de dades.
Presentació dels tipus de dades MariaDB
Com hem explicat anteriorment, les taules són objectes de base de dades on guardarem informació persistent. Cada taula consta de dos o més camps (també coneguts com a columnes) d'un tipus de dades determinat (el tipus d'informació) que aquest camp pot emmagatzemar.
Els tipus de dades més habituals a MariaDB són els següents (podeu consultar la llista completa a la documentació en línia oficial de MariaDB):
- BOOLEAN considera 0 com a fals i qualsevol altre valor com a cert.
- TINYINT, si s'utilitza amb SIGNED, cobreix l'interval de -128 a 127, mentre que l'interval SIGNED és de 0 a 255.
- SMALLINT, si s'utilitza amb SIGNED, cobreix l'interval de -32768 a 32767. L'interval SIGNED és de 0 a 65535.
- INT, si s'utilitza amb UNSIGNED, cobreix l'interval de 0 a 4294967295 i de -2147483648 a 2147483647 en cas contrari.
Nota: a TINYINT, SMALLINT i INT, s'assumeix el SIGNED per defecte.
DOUBLE(M, D), on M és el nombre total de dígits i D és el nombre de dígits després del punt decimal, representa un nombre de coma flotant de doble precisió. Si s'especifica UNSIGNED, no es permetran valors negatius.
- VARCHAR(M) representa una cadena de longitud variable on M és la longitud màxima de columna permesa en bytes (65.535 en teoria). En la majoria dels casos, el nombre de bytes és idèntic al nombre de caràcters, excepte alguns caràcters que poden ocupar fins a 3 bytes. Per exemple, la lletra espanyola ñ representa un caràcter però ocupa 2 bytes.
- TEXT(M) representa una columna amb una longitud màxima de 65.535 caràcters. Tanmateix, com passa amb VARCHAR(M), la longitud màxima real es redueix si s'emmagatzemen caràcters de diversos bytes. Si s'especifica M, la columna es crea com el tipus més petit que pot emmagatzemar aquest nombre de caràcters.
- MEDIUMTEXT(M) i LONGTEXT(M) són similars a TEXT(M), només que les longituds màximes permeses són 16.777.215 i 4.294.967.295 caràcters, respectivament.
- DATE representa la data en format AAAA-MM-DD.
- TIME representa l'hora en format HH:MM:SS.sss (hora, minuts, segons i mil·lisegons).
- DATETIME és la combinació de DATE i TIME en format AAAA-MM-DD HH:MM:SS.
- TIMESTAMP s'utilitza per definir el moment en què s'ha afegit o actualitzat una fila.
Després d'haver revisat aquests tipus de dades, estareu en una millor posició per determinar quin tipus de dades heu d'assignar a una columna determinada d'una taula.
Per exemple, el nom d'una persona pot encaixar fàcilment en un VARCHAR(50), mentre que una publicació de bloc necessitarà un tipus de TEXT (trieu M segons les vostres necessitats específiques).
Abans de submergir-nos en la creació de taules, hi ha dos conceptes fonamentals sobre les bases de dades relacionals que hem de revisar: les claus primàries i les foranes.
Una clau primària conté un valor que identifica de manera única cada fila o registre de la taula. D'altra banda, una clau estrangera s'utilitza per crear un enllaç entre les dades de dues taules, i per controlar les dades que es poden emmagatzemar a la taula on es troba la clau estrangera. Tant les claus primàries com les foranes són generalment INT.
Per il·lustrar-ho, utilitzem BookstoreDB i creem dues taules anomenades AuthorsTBL i BooksTBL de la següent manera. La restricció NOT NULL indica que el camp associat requereix un valor diferent de NULL.
A més, AUTO_INCREMENT s'utilitza per augmentar en un el valor de les columnes de clau primària INT quan s'insereix un registre nou a la taula.
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]>
Ara podem continuar i començar a inserir registres a AuthorsTBL i BooksTBL.
Primer omplirem la taula AuthorsTBL. Per què? Perquè hem de tenir valors per a AuthorID abans d'inserir registres al BooksTBL.
Executeu la consulta següent des del vostre indicador de MariaDB:
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
Després d'això, seleccionarem tots els registres d'AuthorsTBL. Recordeu que necessitarem l'AuthorID per a cada registre per crear la consulta INSERT per a BooksTBL.
Si voleu recuperar un registre a la vegada, podeu utilitzar una clàusula WHERE per indicar una condició que ha de complir una fila per ser retornada. Per exemple,
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
Alternativament, podeu seleccionar tots els registres simultàniament:
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]>
Ara creem la consulta INSERT per a BooksTBL, utilitzant l'AuthorID corresponent perquè coincideixi amb l'autor de cada llibre. Un valor d'1 a BookIsAvailable indica que el llibre està en estoc, 0 en cas contrari:
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
En aquest punt farem una SELECT per veure els registres a BooksTBL. Aleshores, actualitzem el preu de \The Alchemist de Paulo Coelho i tornem a seleccionar aquest disc específic.
Tingueu en compte que el camp BookLastUpdated ara mostra un valor diferent. Com hem explicat anteriorment, un camp TIMESTAMP mostra el valor quan es va inserir o modificar el registre per darrera vegada.
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]>
Tot i que no ho farem aquí, també podeu suprimir un registre si ja no l'utilitzeu. Per exemple, suposem que volem suprimir \The Alchemist de BooksTBL.
Per fer-ho, utilitzarem la instrucció DELETE de la següent manera:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
Com en el cas de UPDATE, és una bona idea fer primer SELECT per veure els registres que poden veure's afectats per DELETE.
A més, no oblideu afegir la clàusula WHERE i una condició (BookID=6) per seleccionar el registre específic que voleu eliminar. En cas contrari, corre el risc d'esborrar totes les files de la taula!
Si voleu concatenar dos (o més) camps, podeu utilitzar la instrucció CONCAT. Per exemple, suposem que volem retornar un conjunt de resultats que consta d'un camp amb el nom del llibre i l'autor en forma de \The Alchemist (Paulo Coelho) i una altra columna amb el preu.
Això requerirà un JOIN entre AuthorsTBL i BooksTBL al camp comú compartit per ambdues taules (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
Com podem veure, CONCAT ens permet unir múltiples expressions de cadena separades per comes. També notareu que vam triar l'àlies Description per representar el conjunt de resultats de la concatenació.
La sortida de la consulta anterior es mostra a la imatge següent:
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)
Utilitzar root per realitzar totes les operacions DML en una base de dades és una mala idea. Per evitar-ho, podem crear un nou compte d'usuari de MariaDB (l'anomenarem usuari de llibreria) i assignar tots els permisos necessaris per a 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)
Tenir un usuari dedicat i separat per a cada base de dades evitarà danys a tota la base de dades si un sol compte es veu compromès.
Per esborrar el missatge de MariaDB, escriviu l'ordre següent i premeu Intro:
MariaDB [BookstoreDB]> \! clear
Per inspeccionar la configuració d'una taula determinada, feu:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
Per exemple,
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)
Una inspecció ràpida revela que el camp BookIsAvailable admet valors NULL. Com que no volem permetre això, ALTERAREM la taula de la següent manera:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(No dubteu a tornar a mostrar les columnes: el SÍ ressaltat a la imatge de dalt hauria de ser ara un NO).
Finalment, per veure totes les bases de dades del vostre servidor, feu:
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)
La imatge següent mostra el resultat de l'ordre anterior després d'accedir a l'indicador de MariaDB com a usuari de la llibreria (tingueu en compte com aquest compte no pot \veure cap base de dades que no sigui BookstoreDB i information_schema (disponible per a tots els usuaris):
Resum
En aquest article hem explicat com executar operacions DML i com crear una base de dades, taules i usuaris dedicats en una base de dades MariaDB. A més, hem compartit alguns consells que us poden facilitar la vida com a administrador de sistemes/bases de dades.
- Part d'administració de bases de dades MySQL - 1
- Part d'administració de bases de dades MySQL - 2
- Ajustament i optimització del rendiment de MySQL: part 3
Si teniu cap pregunta sobre aquest article, no dubteu a fer-nos-ho saber! No dubteu a utilitzar el formulari de comentaris a continuació per contactar amb nosaltres.