E.14: Tworzenie aplikacji internetowych i baz danych oraz administrowanie bazami.

Cześć! Mamy już bazę, wprowadzone przykładowe dane.
Dziś spróbujemy sobie te dane poprzeglądać.
Odnaleźć informacje po zadanych kryteriach.

NADSZEDŁ CZAS POSZUKIWAŃ

  • WHERE .
  • GROUP BY .
  • HAVING .
  • ORDER BY .
  • AS .

Język SQL - Tworzenie

X

SELECT * FROM pasazer;
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                             | adres_id |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz  | Nowak     | 46120023232  | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl   |        1 |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE adres_id = 1;
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                           | adres_id |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl       |        1 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer ORDER by id DESC LIMIT 1;
+----+----------+-----------+--------------+-----------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon   | mail                           | adres_id |
+----+----------+-----------+--------------+-----------+--------------------------------+----------+
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-----------+--------------------------------+----------+

SELECT * FROM pasazer ORDER by id ASC LIMIT 1;
+----+------+----------+-------------+-------------+--------------------------------+----------+
| id | imie | nazwisko | pesel       | telefon     | mail                           | adres_id |
+----+------+----------+-------------+-------------+--------------------------------+----------+
|  1 | Jan  | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
+----+------+----------+-------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer WHERE imie LIKE 'Jan';
+----+------+----------+-------------+-------------+--------------------------------+----------+
| id | imie | nazwisko | pesel       | telefon     | mail                           | adres_id |
+----+------+----------+-------------+-------------+--------------------------------+----------+
|  1 | Jan  | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
|  2 | Jan  | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl |        2 |
+----+------+----------+-------------+-------------+--------------------------------+----------+

SELECT * FROM pasazer WHERE imie LIKE 'JAN';
+----+------+----------+-------------+-------------+--------------------------------+----------+
| id | imie | nazwisko | pesel       | telefon     | mail                           | adres_id |
+----+------+----------+-------------+-------------+--------------------------------+----------+
|  1 | Jan  | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
|  2 | Jan  | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl |        2 |
+----+------+----------+-------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer WHERE id >=3;
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                             | adres_id |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
|  3 | Tadeusz  | Nowak     | 46120023232  | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl   |        1 |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE id BETWEEN 2 AND 4;
+----+----------+----------+-------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko | pesel       | telefon     | mail                             | adres_id |
+----+----------+----------+-------------+-------------+----------------------------------+----------+
|  2 | Jan      | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz  | Nowak    | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  4 | Zdzisław | Konopka  | 7602334213  | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
+----+----------+----------+-------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE id <> 3;
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                           | adres_id |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl |        2 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl       |        1 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer WHERE id IN (1,2,5);
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                           | adres_id |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl |        2 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer ORDER by pesel;
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                             | adres_id |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl   |        1 |
|  3 | Tadeusz  | Nowak     | 46120023232  | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE telefon LIKE '1%';
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                           | adres_id |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl |        2 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-------------+--------------------------------+----------+

X

SELECT * FROM pasazer WHERE pesel LIKE '_6%';
+----+----------+----------+-------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko | pesel       | telefon     | mail                             | adres_id |
+----+----------+----------+-------------+-------------+----------------------------------+----------+
|  1 | Jan      | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan      | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz  | Nowak    | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  4 | Zdzisław | Konopka  | 7602334213  | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
+----+----------+----------+-------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE imie LIKE '%a';
+----+----------+-----------+--------------+-----------+--------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon   | mail                           | adres_id |
+----+----------+-----------+--------------+-----------+--------------------------------+----------+
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl |        1 |
+----+----------+-----------+--------------+-----------+--------------------------------+----------+

X

SELECT count(*) as ile FROM pasazer WHERE nazwisko LIKE '%a';
+-----+
| ile |
+-----+
|   2 |
+-----+

X

SELECT DISTINCT imie FROM pasazer;
+----------+
| imie     |
+----------+
| Jan      |
| Tadeusz  |
| Zdzisław |
| Wiesława |
+----------+

X

SELECT * FROM rezerwacja;
+----+-----------------+------------------------------+------------+----------------+----------------------+
| id | numer_siedzenia | bagaz                        | pasazer_id | lot_numer_lotu | status_rezerwacji_id |
+----+-----------------+------------------------------+------------+----------------+----------------------+
|  1 |              13 | Owca                         |          5 |              1 |                    4 |
|  2 |             123 | Cztery ogórki                |          3 |              2 |                    2 |
|  3 |             911 | Materiały wybuchowe + granat |          2 |              1 |                    3 |
+----+-----------------+------------------------------+------------+----------------+----------------------+

SELECT * FROM pasazer;
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
| id | imie     | nazwisko  | pesel        | telefon     | mail                             | adres_id |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+
|  1 | Jan      | Kowalski  | 66110012121  | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan      | Nowak     | 86110045455  | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz  | Nowak     | 46120023232  | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
|  4 | Zdzisław | Konopka   | 7602334213   | 3424325434  | zdzichu@ilovethissite.pl         |        1 |
|  5 | Wiesława | Zzagrobna | 201005123456 | 123456123   | podziemie@thissiteisamazing.pl   |        1 |
+----+----------+-----------+--------------+-------------+----------------------------------+----------+

SELECT * FROM adres;
+----+-------------+----------+-------------+-----------------+------------+--------------+
| id | wojewodztwo | powiat   | miejscowosc | ulica           | numer_domu | numer_lokalu |
+----+-------------+----------+-------------+-----------------+------------+--------------+
|  1 | pomorskie   | lęborski | Lębork      | Marcinkowskiego | 1          | NULL         |
|  2 | pomorskie   | lęborski | Lębork      | Warszawska      | 112        | NULL         |
|  3 | pomorskie   | lęborski | Lębork      | Pionierów       | 997        | 999          |
+----+-------------+----------+-------------+-----------------+------------+--------------+

X

SELECT nazwisko FROM pasazer WHERE adres_id IN( SELECT id FROM adres WHERE ulica LIKE 'Marcinkowskiego' );
+-----------+
| nazwisko  |
+-----------+
| Kowalski  |
| Konopka   |
| Zzagrobna |
+-----------+

X

SELECT bagaz FROM rezerwacja WHERE pasazer_id IN ( 
	SELECT id FROM pasazer WHERE adres_id IN ( 
		SELECT id FROM adres WHERE ulica LIKE 'Warszawska' 
	) 
);
+------------------------------+
| bagaz                        |
+------------------------------+
| Materiały wybuchowe + granat |
+------------------------------+

X

SELECT * FROM samolot;
+----+---------------+------------+
| id | producent     | typ        |
+----+---------------+------------+
|  1 | Ąty           | Ęście      |
|  2 | Boink         | Drimlajner |
|  3 | Garaż Konrada | Awionurek  |
|  4 | MakroHard     | Doors      |
+----+---------------+------------+

SELECT * FROM lot;
+------------+---------------------+------------+
| numer_lotu | data_wylotu         | samolot_id |
+------------+---------------------+------------+
|          1 | 2027-12-27 20:20:20 |          3 |
|          2 | 2016-10-04 00:00:00 |          3 |
|          3 | 2012-12-12 11:11:00 |          4 |
|          4 | 2019-05-24 23:03:00 |          2 |
+------------+---------------------+------------+

X

SELECT  numer_lotu FROM lot WHERE data_wylotu > '2015-01-21 22:13:15';
+------------+
| numer_lotu |
+------------+
|          1 |
|          2 |
|          4 |
+------------+

X

SELECT typ FROM samolot where id IN ( 
	SELECT samolot_id FROM lot WHERE data_wylotu BETWEEN '2014-01-30 14:15:55' AND '2018-09-29 10:15:55'
);
+-----------+
| typ       |
+-----------+
| Awionurek |
+-----------+

X

SELECT pasazer.imie AS Imie, pasazer.nazwisko AS Nazwisko,
       adres.ulica AS Ulica
 FROM  pasazer
 JOIN  adres
   ON  pasazer.adres_id = adres.id;
   
+----------+-----------+-----------------+
| Imie     | Nazwisko  | Ulica           |
+----------+-----------+-----------------+
| Jan      | Kowalski  | Marcinkowskiego |
| Zdzisław | Konopka   | Marcinkowskiego |
| Wiesława | Zzagrobna | Marcinkowskiego |
| Jan      | Nowak     | Warszawska      |
| Tadeusz  | Nowak     | Pionierów       |
+----------+-----------+-----------------+

X

select imie, nazwisko, miejscowosc, ulica, numer_domu FROM pasazer INNER JOIN adres ON pasazer.adres_id = adres.id;
+----------+-----------+-------------+-----------------+------------+
| imie     | nazwisko  | miejscowosc | ulica           | numer_domu |
+----------+-----------+-------------+-----------------+------------+
| Jan      | Kowalski  | Lębork      | Marcinkowskiego | 1          |
| Zdzisław | Konopka   | Lębork      | Marcinkowskiego | 1          |
| Wiesława | Zzagrobna | Lębork      | Marcinkowskiego | 1          |
| Jan      | Nowak     | Lębork      | Warszawska      | 112        |
| Tadeusz  | Nowak     | Lębork      | Pionierów       | 997        |
+----------+-----------+-------------+-----------------+------------+

X

SELECT * FROM pasazer WHERE imie = 'Jan' UNION Select * FROM pasazer WHERE imie = 'Tadeusz';
+----+---------+----------+-------------+-------------+----------------------------------+----------+
| id | imie    | nazwisko | pesel       | telefon     | mail                             | adres_id |
+----+---------+----------+-------------+-------------+----------------------------------+----------+
|  1 | Jan     | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan     | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz | Nowak    | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
+----+---------+----------+-------------+-------------+----------------------------------+----------+
SELECT * FROM pasazer WHERE imie = 'Jan' UNION Select * FROM pasazer WHERE nazwisko = 'Nowak';
+----+---------+----------+-------------+-------------+----------------------------------+----------+
| id | imie    | nazwisko | pesel       | telefon     | mail                             | adres_id |
+----+---------+----------+-------------+-------------+----------------------------------+----------+
|  1 | Jan     | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan     | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz | Nowak    | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
+----+---------+----------+-------------+-------------+----------------------------------+----------+

SELECT * FROM pasazer WHERE imie = 'Jan' UNION ALL  Select * FROM pasazer WHERE nazwisko = 'Nowak';
+----+---------+----------+-------------+-------------+----------------------------------+----------+
| id | imie    | nazwisko | pesel       | telefon     | mail                             | adres_id |
+----+---------+----------+-------------+-------------+----------------------------------+----------+
|  1 | Jan     | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl   |        1 |
|  2 | Jan     | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  2 | Jan     | Nowak    | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl   |        2 |
|  3 | Tadeusz | Nowak    | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl |        3 |
+----+---------+----------+-------------+-------------+----------------------------------+----------+

X

SELECT * FROM pasazer WHERE imie = 'Jan' AND id NOT IN ( Select id FROM pasazer WHERE nazwisko = 'Nowak');
+----+------+----------+-------------+-------------+--------------------------------+----------+
| id | imie | nazwisko | pesel       | telefon     | mail                           | adres_id |
+----+------+----------+-------------+-------------+--------------------------------+----------+
|  1 | Jan  | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl |        1 |
+----+------+----------+-------------+-------------+--------------------------------+----------+