Wyświetlanie zawartości tabeli poleceniem SELECT

Stronę tą wyświetlono już: 192 razy

Zanim przejdę do omawiania zagadnień związanych z wyświetleniem danych znajdujących się w podanej tabeli, konieczne jest wprowadzenie jakiegoś nieco większego zbioru danych. Ja podeprę się utworzoną już wcześniej bazą danych o nazwie library z jedną tabelką o nazwie books. Do tej tabelki należy dodać dane w następującym zapytaniem:

Listing 1
  1. INSERT INTO library.books (title, author, pages) VALUES
  2. ('Hobbit, czyli tam i z powrotem', 'J. R. R. Tolkien', 300),
  3. ('Drużyna Pierścienia', 'J. R. R. Tolkien', 300),
  4. ('Dwie wieże', 'J. R. R. Tolkien', 300),
  5. ('Powrót króla', 'J. R. R. Tolkien', 300),
  6. ('Odyseja kosmiczna 2001', 'Arthur C. Clark', 300),
  7. ('Odyseja kosmiczna 2010', 'Arthur C. Clark', 300),
  8. ('Odyseja kosmiczna 2061', 'Arthur C. Clark', 300),
  9. ('Odyseja kosmiczna 3001', 'Arthur C. Clark', 300);

Podstawowe zapytania z użyciem polecenia SELECT

Zapytanie SELECT daje dość szerokie pole do popisu jeżeli chodzi o możliwość sterowania wyświetlanymi danymi. Najlepiej jednak jest zacząć od prostych przykładów przygodę z tym poleceniem, tak więc aby wyświetlić wszystkie dane, należy wysłać następujące zapytanie:

mysql> USE library;
Database changed
mysql> SELECT * FROM books;
+----+--------------------------------+------------------+-------+
| id | title                          | author           | pages |
+----+--------------------------------+------------------+-------+
|  1 | Tytuł 1                        | Autor 1          |     0 |
|  2 | Tytuł 2                        | Autor 2          |     0 |
|  3 | Tytuł 3                        | Autor 3          |     0 |
|  4 | Hobbit, czyli tam i z powrotem | J. R. R. Tolkien |   300 |
|  5 | Drużyna Pierścienia            | J. R. R. Tolkien |   300 |
|  6 | Dwie wieże                     | J. R. R. Tolkien |   300 |
|  7 | Powrót króla                   | J. R. R. Tolkien |   300 |
|  8 | Odyseja kosmiczna 2001         | Arthur C. Clark  |   300 |
|  9 | Odyseja kosmiczna 2010         | Arthur C. Clark  |   300 |
| 10 | Odyseja kosmiczna 2061         | Arthur C. Clark  |   300 |
| 11 | Odyseja kosmiczna 3001         | Arthur C. Clark  |   300 |
+----+--------------------------------+------------------+-------+
11 rows in set (0.03 sec)

mysql>

Tak więc w zapytaniu:

Listing 2
  1. SELECT * FROM books;

po poleceniu SELECT znajduje się znak * oznaczający, że wyświetlone (pobrane) zostaną wartości wszystkich pól tabeli books, której nazwę należy podać po FROM. Chociaż powyższa metoda jest jak najbardziej poprawna, to jednak przy pobieraniu danych np. za pomocą PHP, Pythona czy innego języka programowania powinno się to robić podając nazwy wszystkich pól, a to dlatego, że jest to nieco szybszy sposób realizacji zapytania. A więc w tym przypadku zapytanie wyglądałoby tak:

Listing 3
  1. SELECT id, title, author, pages FROM books;

Oczywistym wydaje się fakt, że można ograniczyć się do wyświetlania dowolnej liczby pól tabeli, podając nazwy tylko niektóre z nich, np w taki sposób:

mysql> SELECT title, author FROM books;
+--------------------------------+------------------+
| title                          | author           |
+--------------------------------+------------------+
| Tytuł 1                        | Autor 1          |
| Tytuł 2                        | Autor 2          |
| Tytuł 3                        | Autor 3          |
| Hobbit, czyli tam i z powrotem | J. R. R. Tolkien |
| Drużyna Pierścienia            | J. R. R. Tolkien |
| Dwie wieże                     | J. R. R. Tolkien |
| Powrót króla                   | J. R. R. Tolkien |
| Odyseja kosmiczna 2001         | Arthur C. Clark  |
| Odyseja kosmiczna 2010         | Arthur C. Clark  |
| Odyseja kosmiczna 2061         | Arthur C. Clark  |
| Odyseja kosmiczna 3001         | Arthur C. Clark  |
+--------------------------------+------------------+
11 rows in set (0.00 sec)

mysql>

Filtrowanie wyświetlanych danych

Dzięki dodatkowemu słowu kluczowemu WHERE można filtrować dane, jakie będą wyświetlane. W tym przypadku interesują mnie tylko książki J. R. R. Tolkiena, a więc zapytanie będzie wyglądało tak:

Listing 4
  1. SELECT author, title FROM books WHERE author = 'J. R. R. Tolkien';

Sprawdźmy, czy działa w konsoli:

mysql> SELECT author, title FROM books WHERE author = 'J. R. R. Tolkien';
+------------------+--------------------------------+
| author           | title                          |
+------------------+--------------------------------+
| J. R. R. Tolkien | Hobbit, czyli tam i z powrotem |
| J. R. R. Tolkien | Drużyna Pierścienia            |
| J. R. R. Tolkien | Dwie wieże                     |
| J. R. R. Tolkien | Powrót króla                   |
+------------------+--------------------------------+
4 rows in set (0.00 sec)

mysql>

Jak widać, zmiana kolejności wypisywania pól zmieniła kolejność ich wyświetlania na ekranie.

W powyższym zapytaniu zamieszczony został warunek:

Listing 5
  1. author = 'J. R. R. Tolkien'

mówiący, żeby wyświetlić tylko te wpisy z tabeli books, dla których wartość pola author jest równa 'J. R. R. Tolkien'. Istnieją inne operatory porównania, których zestawienie pozwoliłem sobie zapisać w poniższej tabelce.

OperatorTypPrzykład
=równeauthor='J. R. R. Tolkien'
<mniejsze niżpages < 200
>większe niżpages > 200
<=mniejsze równepages <= 200
>=większe równepages >= 200
!= lub <>różne niżpages != 200
<=mniejsze równepages <= 200
ORlubpages <= 200 OR author REGEXP 'Tolkien'
ANDorazpages <= 200 AND author REGEXP 'Tolkien'
IS NOT NULLnie jest NULL-empages IS NOT NULL
IS NULLjest NULL-empages IS NULL
BETWEEN ANDpomiędzy X a Ypages BETWEEN 100 AND 400
INjest w zbiorzeauthor IN ('J. R. R. Tolkien', 'Thomas Harris')
NOT INnie występuje w zbiorzeauthor IN ('J. R. R. Tolkien', 'Thomas Harris')
LIKEczy pasuje do określonego wzorcaauthor LIKE '%Tolkien%'
NOT LIKEczy nie pasuje do określonego wzorcaauthor NOT LIKE '%Tolkien%'
REGEXPczy pasuje do określonego wyrażenia regularnegoauthor REGEXP 'Tolkien'

Jak widać SQL ma dość rozbudowane mechanizmy wyszukiwania warunkowego.

Zapytanie SELECT z użyciem słowa ORDER BY do sortowania danych

Rozszeżmy działalność naszego zapytania o kolejny element, jakim jest sortowanie. Jak nie trudno zauważyć wynik poprzedniego zapytania nie wyświetlił tytułów w kolejności alfabetycznej, czas najwyższy coś z tym zrobić! Oto zapytanie rozwiązujące ten palący problem:

Listing 6
  1. SELECT author, title FROM books WHERE author = 'J. R. R. Tolkien' ORDER BY title ASC;

W konsoli będzie to wyglądało tak:

mysql> SELECT author, title FROM books WHERE author = 'J. R. R. Tolkien' ORDER BY title ASC;
+------------------+--------------------------------+
| author           | title                          |
+------------------+--------------------------------+
| J. R. R. Tolkien | Drużyna Pierścienia            |
| J. R. R. Tolkien | Dwie wieże                     |
| J. R. R. Tolkien | Hobbit, czyli tam i z powrotem |
| J. R. R. Tolkien | Powrót króla                   |
+------------------+--------------------------------+
4 rows in set (0.00 sec)
mysql>

Po słowie kluczowym ORDER BY podana została nazwa pola, po którym odbywać się będzie sortowanie danych, jeżeli zachodzi konieczność sortowania po kilku polach, to najpierw podawana jest nazwa pola, po którym w pierwszej kolejności dane będą sortowane a następnie kolejno po przecinkach nazwy pozostałych pól. Na końcu zapytania zostało dodane słowo kluczowe ASC, które oznacza sortowanie od najmniejszej do największej wartości, aby sortować w drugą stronę wystarczy zamiast ASC użyć DESC.

Sterowanie liczbą wyświetlanych danych rekordów w zapytaniu SELECT z słowem kluczowym LIMIT

Dzięki dodatkowemu słowu kluczowemu LIMIT możliwe jest ograniczenie liczby wyświetlanych elementów. Oto przykład:

SELECT author, title FROM books WHERE author REGEXP 'Tolkien' ORDER BY title ASC LIMIT 2;
+------------------+--------------------------------+
| author           | title                          |
+------------------+--------------------------------+
| J. R. R. Tolkien | Hobbit, czyli tam i z powrotem |
| J. R. R. Tolkien | Drużyna Pierścienia            |
+------------------+--------------------------------+
2 rows in set (0.01 sec)
mysql>

Powyższe zapytanie wyświetla w kolejności alfabetycznej pierwsze dwa rekordy, jakie będą zawierać w polu author słowo Tolkien. Istnieje też możliwość wyświetlenie określonej liczby rekordów zaczynając od rekordu o podanym indeksie. Oto przykład:

SELECT author, title FROM books WHERE author REGEXP 'Tolkien' ORDER BY title ASC LIMIT 2,2;
+------------------+--------------------------------+
| author           | title                          |
+------------------+--------------------------------+
| J. R. R. Tolkien | Hobbit, czyli tam i z powrotem |
| J. R. R. Tolkien | Powrót króla                   |
+------------------+--------------------------------+
2 rows in set (0.00 sec)

mysql>

Powyższe polecenie wyświetliło dwa rekordy tablicy books zaczynając od drugiego rekordu, jaki pasował do warunków zapytania.

Agregowanie i grupowanie danych w zapytaniu SELECT

Istnieje szereg funkcji agregujących w SQL-u, które umożliwiają wyświetlanie np. sumy wartości z danego pola. Oto przykładowe zapytanie:

mysql> SELECT SUM(pages) FROM books;
+------------+
| SUM(pages) |
+------------+
|       2400 |
+------------+
1 row in set (0.00 sec)

mysql>

Jak widać wynikiem powyższego zapytania jest suma stron wszystkich książek zawartych w tabeli books. Teraz mała sztuczka z grupowaniem:

mysql> SELECT author, SUM(pages) FROM books WHERE id > 3 GROUP BY author;
+------------------+------------+
| author           | SUM(pages) |
+------------------+------------+
| Arthur C. Clark  |       1200 |
| J. R. R. Tolkien |       1200 |
+------------------+------------+
2 rows in set (0.00 sec)
 
mysql>

W powyższym zapytaniu pominąłem wszystkie rekordy o numerach pola id mniejszych niż 4 i wyświetliłem informacje o tym, ile stron mają wszystkie książki danego autora.

Do dyspozycji są i inne przydatne funkcje agregujące, które wypisałem w poniższej tabeli.

Funkcja agragujacaDziałanie
Listing 7
  1. AVG(pages)
oblicza średnią wszystkich wartości
Listing 8
  1. COUNT(pages)
oblicza średnią wszystkich wartości. Jeżeli podane zostanie słowo kluczowe DISTINCT po nazwie kolumny to zwrócona zostanie liczba różnych wpisów w tej kolumnie, natomiast w przypadku, gdy zamiast nazwy kolumny wpisany zostanie symbol * zwrócona zostanie liczba rekordów w tabeli.
Listing 9
  1. MIN(pages)
znajduje najmniejszą wartość w kolumnie
Listing 10
  1. MAX(pages)
znajduje największą wartość w kolumnie
Listing 11
  1. STD(pages)
oblicza odchylenie standardowe
Listing 12
  1. SUM(pages)
sumuje wartości w kolumnie

Komentarze