ASIX-M10-UF2. MySQL I. Instal.lació,configuració i ús del Mysql

De wikijoan
Salta a la navegació Salta a la cerca

Objectius

NOTA. Aquesta pràctica també s'aprofita en l'assignatura de IAW. Per tant, com que la fem al mòdul M10, no cal fer-la al mòdul M6.

Ara és el moment d'instal.lar el servidor mysql (en el cas de què no ho hagis fet fins ara). Recorda que fins ara no havies necessitat el servidor (paquet mysql-server), sinó que amb el client n'havies tingut prou (mysql-client).

En aquesta pràctica instal.laràs el mysql-server i aprofundiràs en l'ús del mysql des de la línia de comandes. Aprendràs les beceroles del que un administrador ha de saber:

  • creació de taules i inserts
  • selects bàsiques i avançades
  • execució de scripts
  • còpies de seguretat i restauració

Aquesta pràctica és prèvia per començar a l'administració de bases de dades MySQL. Treballaràs sobre una base de dades d'exemple per aprendre vocabulari d'anglès.

Desenvolupament

La teoria està explicada a classe i resumida en el següent document:

Bàsicament en aquesta pràctica has de seguir el document i realitzar totes les proves descrites. La idea del document és que sigui una xuleta a consultar en cas de dubte, i una manera ràpida i eficaç d'entrar en el mysql.

Concretament se't demana:

1) Instal.lar el mysql (servidor)

2) estudiar les taules de la base de dades langtrainer. Entendre les relacions entre les taules, així com les paraules d'exemple. Veure que la relació entre word i translation és una relació 1:M.

3) Accedir a la base de dades pròpia com a root. Crear un usuari alumne (amb el password de classe), i donar permís a aquest usuari perquè pugui accedir a la base de dades langtrainer.

4) Fer que els teus companys de classe puguin accedir a la teva base de dades (si la configuració dels firewall és la correcta).

5) crearàs en un script el teu propi vocabulari (com a mínim 25 paraules). L'entregaràs en format de script SQL. Introduiràs algun error expressament (o involuntàriament) per tal de poder analitzar el log

6) Practicar sentències SQL vàries.

7) Faràs una còpia de seguretat de la bd langtrainer. Esborraràs la bd langtrainer. Restauraràs la bd langtrainer.

Base de dades classicmodels

Base de dades d'oficines.

Estudia aquesta base de dades. L'estudies, la instal.les, i practica amb selects.

Primer de tot hem de crear la base de dades classicmodels:

mysql> CREATE DATABASE classicmodels CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Recorda que si vols treballar amb l'usuari alumne en comptes de l'usuari root, has de donar permisos a l'usuari alumne sobre la base de dades (amb GRANT, això ja està explicat).

Els scripts que necessites els trobarem dins de la carpeta scripts/:

$ mysql -u root -p classicmodels -f < create_classicmodels.sql
$ mysql -u root -p classicmodels -f < load_classicmodels.sql

Utilitzem l'opció -f per tal de què l'execució no s'aturi a pesar dels errors.

Per tal de què funcioni el script hauràs de fer uns petits canvis.

Primer de tot és que la ruta i el nom dels arxius a què es refereix el mètode siguin correctes. Si ens fixem bé,

a load_classicalmodels.sql els nom dels arxius estan amb la primera en minúsucla, per exemple: ../datafiles/customers.txt. Per tant, renombrarem els arxius de la carpeta datafiles a minúsucla (per Linux això és obligatori, per Windows no caldria).

Segon. Si obtenim l'error:

The used command is not allowed with this MySQL version

és perquè no sap executar les sentències com aquesta:

LOAD DATA LOCAL INFILE '../datafiles/customers.txt' INTO TABLE Customers
          FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

i això ho solucionem de la següent manera:

https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client

mysql> show global variables like 'local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  |  OFF  |
+---------------+-------+
(this means local_infile is disable)

mysql> set global local_infile=true;

mysql> show global variables like 'local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  |  ON  |
+---------------+-------+

Ara hem de fer un canvi en el fitxer de configuració:

$ cd /etc/mysql/mysql.conf.d
$ sudo joe mysqld.cnf

Al final de tot:

[client]
local-infile=1

Fem el reload per tal de què els canvis en el fitxer de configuració siguin efectius:

$ sudo /etc/init.d/mysql reload

Tercer. Un altre problema que tindrem és que la codificació dels fitxers que estan a la carpeta datafiles/ no estigui en UTF-8, sinó en una altra codificació. En aquest cas, obre els fitxers amb el teu editor de text i fés guardar com... amb codificació UTF-8.

NOTA: En el script no hi ha les claus forànies. No és obligatori tenir-les per tal de fer l'exercici i les consultes proposades. Obligatori: De totes maneres millora el script afegint les claus forànies. T'ajudarà a entendre l'esquema de la base de dades.

Després de tots aquests canvis ja podràs crear l'estructura de la base de dades, i importar totes les dades sense problemes:

$ mysql -u root -p classicmodels
Enter password: 

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| Customers               |
| Employees               |
| Offices                 |
| OrderDetails            |
| Orders                  |
| Payments                |
| Products                |
+-------------------------+
7 rows in set (0.01 sec)


Un cop t'hagis familiaritzat amb l'estructura de la base de dades podràs respondre a aquestes preguntes:

  1. Quin client ha fet més comandes?
  2. Quin client és el que ha facturat més?
  3. Quin empleat és el que s'emporta més comissió?
  4. Quin producte s'ha venut més. Per quin valor ha facturat aquest producte.

SOLUCIONS

1. Quin client ha fet més comandes?

SELECT customerNumber, count(*) as num_orders 
FROM Orders 
GROUP BY customerNumber 
ORDER BY num_orders desc;

SELECT customerNumber, count(*) as num_orders 
FROM Orders 
GROUP BY customerNumber 
ORDER BY num_orders desc limit 1;

SELECT O.customerNumber, customerName, count(*) as num_orders 
FROM Orders O, Customers C
WHERE O.customerNumber=C.customerNumber 
GROUP BY customerNumber 
ORDER BY num_orders desc limit 1;

2. Quin client és el que ha facturat més?

SELECT O.customerNumber, O.orderNumber, priceEach*quantityOrdered as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber;

només ens interessa el client i la suma total de les comandes:

SELECT O.customerNumber, priceEach*quantityOrdered as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber;

agrupem:

SELECT O.customerNumber, ROUND(SUM(priceEach*quantityOrdered), 2) as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber
GROUP BY O.customerNumber ORDER BY tot DESC;

SELECT O.customerNumber, ROUND(SUM(priceEach*quantityOrdered), 2) as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber
GROUP BY O.customerNumber ORDER BY tot DESC LIMIT 1;

i ara trobem el nom de l'empresa:

SELECT customerName, O.customerNumber, ROUND(SUM(priceEach*quantityOrdered), 2) as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber AND C.customerNumber =O.customerNumber 
GROUP BY O.customerNumber ORDER BY tot DESC LIMIT 1;

+------------------------+----------------+-----------+
| customerName           | customerNumber | tot       |
+------------------------+----------------+-----------+
| Euro+ Shopping Channel |            141 | 912294.11 |
+------------------------+----------------+-----------+

3. Quin empleat és el que s'emporta més comissió?

SELECT O.orderNumber, priceEach*quantityOrdered as tot
FROM Orders O,OrderDetails OD
WHERE O.orderNumber=OD.orderNumber;

SELECT O.orderNumber as ordnumber, ROUND(SUM(priceEach*quantityOrdered),2) as tot
FROM Orders O,OrderDetails OD
WHERE O.orderNumber=OD.orderNumber
GROUP BY ordnumber;

SELECT salesRepEmployeeNumber, O.customerNumber, O.orderNumber, priceEach*quantityOrdered as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber;

simplifiquem:

SELECT salesRepEmployeeNumber, priceEach*quantityOrdered as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber order by salesRepEmployeeNumber ASC;

agrupem:

SELECT salesRepEmployeeNumber, ROUND(SUM(priceEach*quantityOrdered),2) as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber
GROUP BY salesRepEmployeeNumber ORDER BY tot DESC;

SELECT salesRepEmployeeNumber, ROUND(SUM(priceEach*quantityOrdered),2) as tot
FROM Orders O,OrderDetails OD, Customers C
WHERE O.orderNumber=OD.orderNumber AND O.customerNumber=C.customerNumber
GROUP BY salesRepEmployeeNumber ORDER BY tot DESC LIMIT 1;

+------------------------+------------+
| salesRepEmployeeNumber | tot        |
+------------------------+------------+
|                   1370 | 1401412.66 |
+------------------------+------------+

L'empleat que ha fet guanyar més diners a l'empresa és el 1370, que és

SELECT CONCAT(firstName, " ", lastName) AS empleat FROM Employees WHERE employeeNumber=1370;

+------------------+
| empleat          |
+------------------+
| Gerard Hernandez |
+------------------+

Obtenir el diagrama a partir del script: enginyeria inversa

Classicmodels.png

Com es discuteix a:

el millor és utilitzar MySQL workbench (TBD)

Entrega

Tota la documentació generada (fitxers de text, scripts, còpies de seguretat, captures de pantalla que creguis importants, etc) la inclouràs en un fitxer comprimit i l'entregaràs en el Moodle. El professor també demana incloure el fitxer LLEGEIX-ME.txt on expliquis les dificultats i descobertes que has realitzat.

Recorda la normativa per entregar les pràctiques al Google Classroom: ASIX-M10-UF2#Normativa_d.27entrega_de_les_pr.C3.A0ctiques_al_Google_Classroom

Durarda

2 hores



creat per Joan Quintana Compte, octubre 2018