Proste zapytania SQL

pt., 09/17/2010 - 10:09 — ciebie

Ponieważ wiemy już do czego służą bazy danych, to sprawdźmy jak wyglądają one w praktyce. W tym celu musimy zainstalować silnik do obsługi baz danych. Silników takich jest bardzo wiele, a duża część z nich używa do komunikacji języka SQL. W ramach tego kursu będziemy używać darmowego PostgreSQL. Ale większość przykładów będzie działać równie dobrze w:

MySQL
SQLite
Oracle
DB2
.. wikipedia..

Zacznijmy od tego, że stworzymy tabelkę

CREATE TABLE osoby (login varchar(100), imie varchar(100), nazwisko varchar(100), mail varchar(100));
-- CREATE TABLE

Użyliśmy polecenia create table (pamiętajmy o średniku) do stworzenia tabeli. Nasza tabela ma cztery kolumny:

login,
imie,
nazwisko,
mail,

a każda z kolumn ma typ varchar(100). Ale co to znaczy, że kolumna ma typ? To znaczy, że w tej kolumnie będą dane określonego typu. Ale jakie typy możemy mieć:

liczby,
napisy,
obrazki,
pliki,
i wiele innych.

Typ varchar(100) to są napisy, które mają nie więcej niż 100 znaków. Podobnie varchar(200) to napisy, które mają nie więcej niż 200 znaków. Czyli "Adam" jest całkiem poprawną wartością w kolumnie imie, ale zdjęcie Adama nie było by dobrą wartością.

Stworzyliśmy tabelę, ale nic się nie stało. System napisał nam CREATE TABLE i koniec. Co dalej? Warto by zobaczyć co jest w tej tabeli. Jak to zrobić?

SELECT * FROM osoby;
 login | imie | nazwisko | mail 
-------+------+----------+------
(0 rows)

Instrukcja SELECT * FROM wyświetla wszystkie wiersze z tabeli. W tym wypadku nie wybrała żadnych wierszy, bo żadnych wierszy do tej tabeli nie wstawiliśmy. No więc wstawmy jakieś wiersze. Do tego służy trzecia z instrukcji, którymi się zajmiemy na tej lekcji.

INSERT INTO osoby VALUES ('a', 'Adam', 'Kowalski', 'adam.kowalski@mail.pl');
-- INSERT 0 1

Instrukcja INSERT INTO wstawia wartości do tabeli. Zauważmy, że napisy zapisujemy w pojedynczych cudzysłowach. Wstawiliśmy jeden wiersz, jak to sprawdzić?

SELECT * FROM osoby;
 login | imie | nazwisko |         mail          
-------+------+----------+-----------------------
 a     | Adam | Kowalski | adam.kowalski@mail.pl
(1 row)

Jak widać komputer wypisał jedną osobę. Wstawmy jeszcze kilka osób (z przykładu z poprzedniej lekcji)

INSERT INTO osoby VALUES ('b', 'Basia', 'Nowak', 'basia.nowak@mail.pl');
-- INSERT 0 1
INSERT INTO osoby VALUES ('c', 'cYPRIAN', 'pan', 'cyprian.pl');
-- INSERT 0 1
INSERT INTO osoby VALUES ('e', 'A to mój znajomy');
-- INSERT 0 1
INSERT INTO osoby VALUES ('f', ':)', 'nasza-klasa', 'tralalala');
-- INSERT 0 1
INSERT INTO osoby VALUES ('d', 'Ewa', 'Piątek', 'Basia na belwederskiej');
-- INSERT 0 1
INSERT INTO osoby VALUES ('d', 'Basia', 'Piątek', 'Ewa na belwederskiej');
-- INSERT 0 1

i sprawdźmy czy wszystkie zostały poprawnie wstawione

SELECT * FROM osoby;
 login |       imie       |  nazwisko   |          mail          
-------+------------------+-------------+------------------------
 a     | Adam             | Kowalski    | adam.kowalski@mail.pl
 b     | Basia            | Nowak       | basia.nowak@mail.pl
 c     | cYPRIAN          | pan         | cyprian.pl
 e     | A TO mój znajomy |             | 
 f     | :)               | nasza-klasa | tralalala
 d     | Ewa              | Piątek      | Basia na belwederskiej
 d     | Basia            | Piątek      | Ewa na belwederskiej
(7 rows)

Dla potrzeb tych zajęć stwórzmy dodatkowe trzy tabele (na zdjęcia, znajomych i oceny)

CREATE TABLE zdjecia (nazwa varchar(100), miejsce varchar(100));
-- CREATE TABLE
CREATE TABLE znajomi (login_a varchar(100), login_b varchar(100));
-- CREATE TABLE
CREATE TABLE oceny(login varchar(100), zdjecie varchar(100), ocena numeric(2,0));
-- CREATE TABLE

W tabeli z ocenami użyliśmy nowego typu danych numeric(2,0). Jest to liczba, która ma dwie cyfry z czego zero cyfr po przecinku. Numeric (6,2) to liczba która ma cztery cyfry przed i dwie cyfry po przecinku.

Wypełnijmy tabele danymi.

INSERT INTO zdjecia VALUES ('z1', 'z1.png');
-- INSERT 0 1
INSERT INTO zdjecia VALUES ('z2', 'z2.png');
-- INSERT 0 1
INSERT INTO zdjecia VALUES ('z3', 'z3.png');
-- INSERT 0 1
INSERT INTO znajomi VALUES ('a', 'b');
-- INSERT 0 1
INSERT INTO znajomi VALUES ('a', 'c');
-- INSERT 0 1
INSERT INTO znajomi VALUES ('e', 'f');
-- INSERT 0 1
INSERT INTO oceny VALUES ('a', 'z1', 10);
-- INSERT 0 1
INSERT INTO oceny VALUES ('b', 'z1', 3);
-- INSERT 0 1
INSERT INTO oceny VALUES ('a', 'z2', 7);
-- INSERT 0 1
INSERT INTO oceny VALUES ('b', 'z2', 1);
-- INSERT 0 1

Sprawdź samodzielnie czy udało Ci się wstawić wiersze do tabel (select * from ...)

Jak na razie mieliśmy do czynienia z banalnymi operacjami. Wstawianie wierszy do tabeli i usuwanie wierszy z tabeli. Spróbujmy czegoś ciekawszego. Np. posortujmy tabelę.

SELECT * FROM osoby ORDER BY nazwisko;
 login |       imie       |  nazwisko   |          mail          
-------+------------------+-------------+------------------------
 a     | Adam             | Kowalski    | adam.kowalski@mail.pl
 f     | :)               | nasza-klasa | tralalala
 b     | Basia            | Nowak       | basia.nowak@mail.pl
 c     | cYPRIAN          | pan         | cyprian.pl
 d     | Ewa              | Piątek      | Basia na belwederskiej
 d     | Basia            | Piątek      | Ewa na belwederskiej
 e     | A TO mój znajomy |             | 
(7 rows)

W ten sposób (korzystając z ORDER BY) możemy wypisać osoby posortowane według jakiejś kolumny. Ale jeśli przyglądniemy się wynikowi zapytania dokładniej, to zauważymy pewien problem. Jeśli go nie widzisz, to spróbujmy wypisać nie wszystkie kolumny, ale jedynie nazwisko i imię. Zamiast SELECT * napiszemy SELECT nazwisko, imie

SELECT nazwisko, imie FROM osoby ORDER BY nazwisko;
  nazwisko   |       imie       
-------------+------------------
 Kowalski    | Adam
 nasza-klasa | :)
 Nowak       | Basia
 pan         | cYPRIAN
 Piątek      | Ewa
 Piątek      | Basia
             | A TO mój znajomy

Teraz już chyba widać problem. Mamy dwie osoby o nazwisku piątek i one są posortowane w sposób dość dziwny w kolejności Ewa, Basia. Spróbujmy to zmienić i powiedzieć komputerowi, żeby posortował osoby po nazwisku, a jak kilka osób ma takie samo nazwisko, to po imieniu.

SELECT nazwisko, imie FROM osoby ORDER BY nazwisko, imie;
  nazwisko   |       imie       
-------------+------------------
 Kowalski    | Adam
 nasza-klasa | :)
 Nowak       | Basia
 pan         | cYPRIAN
 Piątek      | Basia
 Piątek      | Ewa
             | A TO mój znajomy
(7 rows)

Teraz jest lepiej.

A w jaki sposób wypisać wszystkich, którzy mają nazwisko Piątek? Służy do tego polecenie WHERE.

SELECT * FROM osoby WHERE nazwisko = 'Piątek';
 login | imie  | nazwisko |          mail          
-------+-------+----------+------------------------
 d     | Ewa   | Piątek   | Basia na belwederskiej
 d     | Basia | Piątek   | Ewa na belwederskiej

Co ciekawe polecenia można łączyć. A więc można wypisać tylko imiona i nazwiska osób, które mają nazwisko Piątek posortowane po imieniu.

SELECT imie, nazwisko FROM osoby WHERE nazwisko = 'Piątek' ORDER BY imie;
 imie  | nazwisko 
-------+----------
 Basia | Piątek
 Ewa   | Piątek
(2 rows)

Teraz już umiesz bardzo dużo :)

Proste zapytania SQL

dla autorów