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

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

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:

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.

Complement (obligatori)

Base de dades d'oficines.

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

$ 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.

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.


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. A load_classicalmodels.sql hem de ficar el nom dels arxius amb la primera amb majúscula, que és com estan: ../datafiles/Customers.txt (per Linux això és obligatori, per Windows no caldria).

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ò és fàcil de solucionar tocant el fitxer de configuració /etc/mysql/my.cnf:

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
local-infile=1

Un altre problema que podem tenir é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.

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

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines