Lær hvordan du bruker flere funksjoner i MySQL og MariaDB - Del 2


Dette er den andre delen av en serie med to artikler om det vesentlige av MariaDB/MySQL-kommandoer. Vennligst se vår forrige artikkel om dette emnet før du fortsetter.

  1. Lær MySQL/MariaDB Grunnleggende for nybegynnere – Del 1

I denne andre delen av MySQL/MariaDB-nybegynnerserien vil vi forklare hvordan man begrenser antall rader som returneres av en SELECT-spørring, og hvordan man bestiller resultatsettet basert på en gitt betingelse.

I tillegg vil vi lære å gruppere postene og utføre grunnleggende matematisk manipulasjon på numeriske felt. Alt dette vil hjelpe oss å lage et SQL-skript som vi kan bruke til å produsere nyttige rapporter.

Forutsetninger

For å begynne, følg disse trinnene:

1. Last ned eksempeldatabasen medarbeidere, som inkluderer seks tabeller som består av totalt 4 millioner poster.

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjf employees_db-full-1.0.6.tar.bz2
cd employees_db

2. Skriv inn MariaDB-ledeteksten og opprett en database med navnet ansatte:

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 employees;
Query OK, 1 row affected (0.00 sec)

3. Importer den til MariaDB-serveren din som følger:

MariaDB [(none)]> source employees.sql

Vent 1-2 minutter til prøvedatabasen er lastet (husk at vi snakker om 4M-poster her!).

4. Bekreft at databasen ble importert riktig ved å liste tabellene:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Opprett en spesiell konto for bruk med ansatte-databasen (velg gjerne et annet kontonavn og passord):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

Logg nå på som empadmin-bruker i Mariadb-ledeteksten.

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Sørg for at alle trinnene som er skissert i bildet ovenfor er fullført før du fortsetter.

Ordne og begrense antall rader i resultatsettet

Lønnstabellen inneholder alle inntektene til hver ansatt med start- og sluttdato. Vi ønsker kanskje å se lønnene til emp_no=10001 over tid. Dette vil hjelpe deg med å svare på følgende spørsmål:

  1. Fikk han/hun noen høyninger?
  2. Hvis ja, når?

Utfør følgende spørring for å finne ut:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

Hva om vi trenger å se de siste 5 høyningene? Vi kan gjøre ORDER BY from_date DESC. Søkeordet DESC indikerer at vi ønsker å sortere resultatsettet i synkende rekkefølge.

I tillegg lar LIMIT 5 oss bare returnere de øverste 5 radene i resultatsettet:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

Du kan også bruke ORDER BY med flere felt. For eksempel vil følgende spørring sortere resultatsettet basert på ansattes fødselsdato i stigende form (standard) og deretter etter etternavn i alfabetisk synkende form:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

Du kan se mer informasjon om LIMIT her.

Gruppering av poster/MAX, MIN, AVG og ROUND

Som vi nevnte tidligere, inneholder tabellen lønn inntektene til hver ansatt over tid. I tillegg til LIMIT kan vi bruke søkeordene MAX og MIN for å bestemme når maksimalt og minimum antall ansatte ble ansatt:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

Basert på resultatsettene ovenfor, kan du gjette hva spørringen nedenfor vil returnere?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

Hvis du godtar at den vil returnere gjennomsnittslønnen (som spesifisert av AVG) over tid avrundet til 2 desimaler (som indikert med AVGANG), har du rett.

Hvis vi ønsker å se summen av lønningene gruppert etter ansatt og returnere toppen 5, kan vi bruke følgende spørring:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

I spørringen ovenfor blir lønn gruppert etter ansatt og deretter utføres summen.

Å bringe det hele sammen

Heldigvis trenger vi ikke å kjøre spørring etter spørring for å produsere en rapport. I stedet kan vi lage et skript med en rekke SQL-kommandoer for å returnere alle nødvendige resultatsett.

Når vi kjører skriptet, vil det returnere den nødvendige informasjonen uten ytterligere intervensjon fra vår side. La oss for eksempel lage en fil med navnet maxminavg.sql i gjeldende arbeidskatalog med følgende innhold:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

Linjer som begynner med to bindestreker ignoreres, og de individuelle spørringene utføres etter hverandre. Vi kan kjøre dette skriptet enten fra Linux-kommandolinjen:

mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

eller fra MariaDB-ledeteksten:

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Sammendrag

I denne artikkelen har vi forklart hvordan du bruker flere MariaDB-funksjoner for å avgrense resultatsett som returneres av SELECT-setninger. Når de har definert, kan flere individuelle spørringer settes inn i et skript for å utføre det enklere og redusere risikoen for menneskelige feil.

Har du spørsmål eller forslag til denne artikkelen? Send oss gjerne en melding ved å bruke kommentarskjemaet nedenfor. Vi ser frem til å høre fra deg!