[Bazy danych] 11.01

[NOTATKI]
Tworzenie tabeli
Insert into wprowadzenie danych
Selectem pokazać tych co mają pesel null np
Utwórz kopie np jako wynik select
(na podstawie kryteriów)
Join w każdej postaci
Plus złączenia zwykłe, bez join. Uzyskać dane z 2 tabel np
Funkcje average min max count
Zapytania typu (głównie)from, where
Np. "ile było zamówień płaskowników"
jak masz towar-id to trzeba `towar-id`
Różnice w joinach, null zamiast nazwy to wartość
Group by


[KOD]

student@linux:~> mysql -u stud1
ERROR 1045 (28000): Access denied for user 'stud1'@'localhost' (using password: NO)
student@linux:~> mysql -u stud1 -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.31-MariaDB SLE 12 SP1 package

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use hw1
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
MariaDB [hw1]> show tables
    -> ;
+---------------+
| Tables_in_hw1 |
+---------------+
| Miasta        |
| Osoby         |
| UczelniaA     |
| UczelniaB     |
| klienci       |
| pracownicy    |
| pracownicyN   |
| stanowiskaN   |
| towary        |
| zamowienia    |
+---------------+
10 rows in set (0.00 sec)

MariaDB [hw1]> select * from zamowienia;
+----+-----------+----------+------------+---------+
| id | klient-id | towar-id | data       | wartosc |
+----+-----------+----------+------------+---------+
|  1 |         1 |        2 | 2012-01-01 |   12.44 |
|  2 |         1 |        4 | 2012-01-01 |   10.22 |
|  3 |         1 |        2 | 2012-02-12 |   15.88 |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |
|  6 |         2 |        4 | 2012-03-01 |    2.28 |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |
|  8 |         3 |        4 | 2012-01-01 |   12.44 |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |
| 10 |         5 |        4 | 2012-03-02 |    6.11 |
+----+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

MariaDB [hw1]> select * from zamowienia where wartosc>avg(wartosc);
ERROR 1111 (HY000): Invalid use of group function
MariaDB [hw1]> select * from zamowienia where wartosc> select avg(wartosc) from zamowienia);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select avg(wartosc) from zamowienia)' at line 1
MariaDB [hw1]> select * from zamowienia where wartosc> (select avg(wartosc) from zamowienia);
+----+-----------+----------+------------+---------+
| id | klient-id | towar-id | data       | wartosc |
+----+-----------+----------+------------+---------+
|  3 |         1 |        2 | 2012-02-12 |   15.88 |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |
+----+-----------+----------+------------+---------+
5 rows in set (0.01 sec)

MariaDB [hw1]> select * from zamowienia;
+----+-----------+----------+------------+---------+
| id | klient-id | towar-id | data       | wartosc |
+----+-----------+----------+------------+---------+
|  1 |         1 |        2 | 2012-01-01 |   12.44 |
|  2 |         1 |        4 | 2012-01-01 |   10.22 |
|  3 |         1 |        2 | 2012-02-12 |   15.88 |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |
|  6 |         2 |        4 | 2012-03-01 |    2.28 |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |
|  8 |         3 |        4 | 2012-01-01 |   12.44 |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |
| 10 |         5 |        4 | 2012-03-02 |    6.11 |
+----+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

MariaDB [hw1]> select * from klienci;
+----+-----------+------------+
| id | imie      | nazwisko   |
+----+-----------+------------+
|  1 | Jan       | Kowalski   |
|  2 | Andrzej   | Nowak      |
|  3 | Janusz    | Malinowski |
|  4 | Adam      | Kowalski   |
|  5 | Krzysztof | Nowicki    |
+----+-----------+------------+
5 rows in set (0.01 sec)

MariaDB [hw1]> select * from towary;
+----+--------------+-------+-------+
| id | nazwa        | grupa | cena  |
+----+--------------+-------+-------+
|  1 | Śruby        |     1 |  2.00 |
|  2 | Nakrętki     |     1 |  3.00 |
|  3 | Kątowniki    |     2 |  8.00 |
|  4 | Płaskowniki  |     2 |  9.00 |
|  5 | Gwoździe     |     1 |  1.00 |
|  6 | Panele       |     3 | 15.00 |
|  7 | Wkręty       |     1 |  4.00 |
|  8 | Deski        |     3 | 12.00 |
|  9 | Płyty        |     3 | 19.00 |
+----+--------------+-------+-------+
9 rows in set (0.00 sec)

MariaDB [hw1]>
MariaDB [hw1]>
MariaDB [hw1]>
MariaDB [hw1]>
MariaDB [hw1]> select * from zamowienia join towary AS (select * from towary join klienci AS wartosc > (select avg(wartosc) from zamowienia));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select * from towary join klienci AS wartosc > (select avg(wartosc) from zamowi' at line 1
MariaDB [hw1]> select * from zamowienia, towary, klienci where wartosc > (select avg(wartosc) from zamowienia) AND `towar-id` = towary.id AND `klient-id` = klienci.id;
+----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
| id | klient-id | towar-id | data       | wartosc | id | nazwa     | grupa | cena | id | imie    | nazwisko   |
+----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
|  3 |         1 |        2 | 2012-02-12 |   15.88 |  2 | Nakrętki  |     1 | 3.00 |  1 | Jan     | Kowalski   |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |  1 | Śruby     |     1 | 2.00 |  2 | Andrzej | Nowak      |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |  1 | Śruby     |     1 | 2.00 |  2 | Andrzej | Nowak      |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |  1 | Śruby     |     1 | 2.00 |  3 | Janusz  | Malinowski |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |  1 | Śruby     |     1 | 2.00 |  4 | Adam    | Kowalski   |
+----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
5 rows in set (0.00 sec)

MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select 0.5*max(wartosc) from zamowienia) AND `towar-id` = towary.id AND `klient-id` = klienci.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select 0.5*max(wartosc) from z' at line 1
MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamow' at line 1
MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamow' at line 1
MariaDB [hw1]> select * from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
+----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
| id | klient-id | towar-id | data       | wartosc | id | nazwa        | grupa | cena | id | imie      | nazwisko   |
+----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
|  1 |         1 |        2 | 2012-01-01 |   12.44 |  2 | Nakrętki     |     1 | 3.00 |  1 | Jan       | Kowalski   |
|  2 |         1 |        4 | 2012-01-01 |   10.22 |  4 | Płaskowniki  |     2 | 9.00 |  1 | Jan       | Kowalski   |
|  6 |         2 |        4 | 2012-03-01 |    2.28 |  4 | Płaskowniki  |     2 | 9.00 |  2 | Andrzej   | Nowak      |
|  8 |         3 |        4 | 2012-01-01 |   12.44 |  4 | Płaskowniki  |     2 | 9.00 |  3 | Janusz    | Malinowski |
| 10 |         5 |        4 | 2012-03-02 |    6.11 |  4 | Płaskowniki  |     2 | 9.00 |  5 | Krzysztof | Nowicki    |
+----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
5 rows in set (0.00 sec)

MariaDB [hw1]> select * from towary;
+----+--------------+-------+-------+
| id | nazwa        | grupa | cena  |
+----+--------------+-------+-------+
|  1 | Śruby        |     1 |  2.00 |
|  2 | Nakrętki     |     1 |  3.00 |
|  3 | Kątowniki    |     2 |  8.00 |
|  4 | Płaskowniki  |     2 |  9.00 |
|  5 | Gwoździe     |     1 |  1.00 |
|  6 | Panele       |     3 | 15.00 |
|  7 | Wkręty       |     1 |  4.00 |
|  8 | Deski        |     3 | 12.00 |
|  9 | Płyty        |     3 | 19.00 |
+----+--------------+-------+-------+
9 rows in set (0.00 sec)

MariaDB [hw1]> slect avg(cena), grupa from towary group by grupa
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'slect avg(cena), grupa from towary group by grupa' at line 1
MariaDB [hw1]> select avg(cena), grupa from towary group by grupa
    -> ;
+-----------+-------+
| avg(cena) | grupa |
+-----------+-------+
|  2.500000 |     1 |
|  8.500000 |     2 |
| 15.333333 |     3 |
+-----------+-------+
3 rows in set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select cena from towary where cena > 10);
ERROR 1242 (21000): Subquery returns more than 1 row
MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select cena from towary where cena > cena=10);
Empty set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > 10;
+--------+-------+-------+
| nazwa  | cena  | grupa |
+--------+-------+-------+
| Panele | 15.00 |     3 |
| Deski  | 12.00 |     3 |
| Płyty  | 19.00 |     3 |
+--------+-------+-------+
3 rows in set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > 10 group by grupa;
+--------+-------+-------+
| nazwa  | cena  | grupa |
+--------+-------+-------+
| Panele | 15.00 |     3 |
+--------+-------+-------+
1 row in set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary as XX where cena > (select avg(cena) from towary where towary.grupa = XX.grupa);
+--------------+-------+-------+
| nazwa        | cena  | grupa |
+--------------+-------+-------+
| Nakrętki     |  3.00 |     1 |
| Płaskowniki  |  9.00 |     2 |
| Wkręty       |  4.00 |     1 |
| Płyty        | 19.00 |     3 |
+--------------+-------+-------+
4 rows in set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary as towaryNew where cena > (select avg(cena) from towary where towary.grupa = towaryNew.grupa);
+--------------+-------+-------+
| nazwa        | cena  | grupa |
+--------------+-------+-------+
| Nakrętki     |  3.00 |     1 |
| Płaskowniki  |  9.00 |     2 |
| Wkręty       |  4.00 |     1 |
| Płyty        | 19.00 |     3 |
+--------------+-------+-------+
4 rows in set (0.00 sec)

MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select avg(cena) from towary as towaryNew where towary.grupa = towaryNew.grupa);
+--------------+-------+-------+
| nazwa        | cena  | grupa |
+--------------+-------+-------+
| Nakrętki     |  3.00 |     1 |
| Płaskowniki  |  9.00 |     2 |
| Wkręty       |  4.00 |     1 |
| Płyty        | 19.00 |     3 |
+--------------+-------+-------+
4 rows in set (0.00 sec)

MariaDB [hw1]> select * from zamowienia;
+----+-----------+----------+------------+---------+
| id | klient-id | towar-id | data       | wartosc |
+----+-----------+----------+------------+---------+
|  1 |         1 |        2 | 2012-01-01 |   12.44 |
|  2 |         1 |        4 | 2012-01-01 |   10.22 |
|  3 |         1 |        2 | 2012-02-12 |   15.88 |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |
|  6 |         2 |        4 | 2012-03-01 |    2.28 |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |
|  8 |         3 |        4 | 2012-01-01 |   12.44 |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |
| 10 |         5 |        4 | 2012-03-02 |    6.11 |
+----+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

MariaDB [hw1]> select count(*) as ilosc from zamowienia where wartosc > (select avg(wartosc) from zamowienia);
+-------+
| ilosc |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

MariaDB [hw1]> select * from zamowienia;
+----+-----------+----------+------------+---------+
| id | klient-id | towar-id | data       | wartosc |
+----+-----------+----------+------------+---------+
|  1 |         1 |        2 | 2012-01-01 |   12.44 |
|  2 |         1 |        4 | 2012-01-01 |   10.22 |
|  3 |         1 |        2 | 2012-02-12 |   15.88 |
|  4 |         2 |        1 | 2012-01-01 |   22.35 |
|  5 |         2 |        1 | 2012-02-12 |   28.00 |
|  6 |         2 |        4 | 2012-03-01 |    2.28 |
|  7 |         3 |        1 | 2012-02-11 |   18.48 |
|  8 |         3 |        4 | 2012-01-01 |   12.44 |
|  9 |         4 |        1 | 2012-03-11 |   15.26 |
| 10 |         5 |        4 | 2012-03-02 |    6.11 |
+----+-----------+----------+------------+---------+
10 rows in set (0.00 sec)

MariaDB [hw1]> select `towar-id`, id, cena from zamowienia where (id,cena) in (select id, min(wartosc) from zamowienia);
ERROR 1054 (42S22): Unknown column 'cena' in 'field list'
MariaDB [hw1]> select `towar-id`, id, cena from zamowienia where (id,wartosc) in (select id, min(wartosc) from zamowienia);
ERROR 1054 (42S22): Unknown column 'cena' in 'field list'
MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (id,wartosc) in (select id, min(wartosc) from zamowienia);
Empty set (0.00 sec)

MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (6,wartosc) in (select id, min(wartosc) from zamowienia);
Empty set (0.00 sec)

MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (1,wartosc) in (select id, min(wartosc) from zamowienia);
+----------+----+---------+
| towar-id | id | wartosc |
+----------+----+---------+
|        4 |  6 |    2.28 |
+----------+----+---------+
1 row in set (0.00 sec)

MariaDB [hw1]>